BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle Query 基础知识

2018-06-10

Oracle Query 基础知识

汇总Oracle查询基础知识

什么是子查询?

测试Schema-scott

SCOTT@orcl11g> select table_name from user_tables;
TABLE_NAME
------------------------------
SALGRADE
BONUS
EMP
DEPT
SCOTT@orcl11g> select * from dept;

	DEPTNO DNAME          LOC
---------- -------------- -------------
		10 ACCOUNTING     NEW YORK
		20 RESEARCH       DALLAS
		30 SALES          CHICAGO
		40 OPERATIONS     BOSTON
SCOTT@orcl11g> select * from emp;
	EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
	7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
	7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
	7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
	7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
	7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
	7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
	7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
	7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
	7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
	7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
	7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
	7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
	7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
	7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
14 rows selected.
SCOTT@orcl11g> select * from SALGRADE;

	GRADE      LOSAL      HISAL
---------- ---------- ----------
		1        700       1200
		2       1201       1400
		3       1401       2000
		4       2001       3000
		5       3001       9999

SCOTT@orcl11g> 

子查询

oracle中子查询

就是指查询中嵌套查询。
子查询可以出现在很多位置,比如: 当列、当表、当条件等
典型的联接运算,使用像 =  或 <> 之类的比较运算符
包括相等联接和自然联接

语法: SELECT (子查询) FROM (子查询) WHERE (子查询) GROUP BY 子句 HAVING (子查询) ORDER BY 子句

注:子查询要用括号括起来。

Example:

找出跟allen相同部门的员工

分三步:找到allen的部门号,根据部门号找相应的员工,把allen员工去掉..

SCOTT@orcl11g> select deptno from emp where ename = 'ALLEN';
	DEPTNO
----------
		30
SCOTT@orcl11g> select ename from emp where deptno=30;
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
6 rows selected.
SCOTT@orcl11g> select ename from emp where deptno=30 and ename != 'ALLEN';
ENAME
----------
WARD
MARTIN
BLAKE
TURNER
JAMES
SCOTT@orcl11g> 

通过一个SQL:

select ename from emp where deptno = (select deptno from emp where ename = 'ALLEN') and ename != 'ALLEN';

上面就是在where条件中使用了子查询

SCOTT@orcl11g> select d.dname,e.ename from (select deptno,dname from dept) d,(select deptno,ename from emp) e where d.deptno = e.deptno order by 1,2;

DNAME          ENAME
-------------- ----------
ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
RESEARCH       ADAMS
RESEARCH       FORD
RESEARCH       JONES
RESEARCH       SCOTT
RESEARCH       SMITH
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

14 rows selected.

SCOTT@orcl11g>

内连接,左连接,右连接

举例说明:

内连接: inner join

SCOTT@orcl11g> select a.dname,b.ename,b.mgr from dept a, emp b where a.deptno=b.deptno;
DNAME          ENAME             MGR
-------------- ---------- ----------
ACCOUNTING     CLARK            7839
ACCOUNTING     KING
ACCOUNTING     MILLER           7782
RESEARCH       JONES            7839
RESEARCH       FORD             7566
RESEARCH       ADAMS            7788
RESEARCH       SMITH            7902
RESEARCH       SCOTT            7566
SALES          WARD             7698
SALES          TURNER           7698
SALES          ALLEN            7698
SALES          JAMES            7698
SALES          BLAKE            7839
SALES          MARTIN           7698

14 rows selected.
SCOTT@orcl11g> 

外连接 outer join - 左连接 左边集合的全集 left join

注意Operations部门没有员工

SCOTT@orcl11g> select a.dname,b.ename,b.mgr from dept a, emp b where a.deptno=b.deptno(+);
DNAME          ENAME             MGR
-------------- ---------- ----------
ACCOUNTING     CLARK            7839
ACCOUNTING     KING
ACCOUNTING     MILLER           7782
RESEARCH       JONES            7839
RESEARCH       FORD             7566
RESEARCH       ADAMS            7788
RESEARCH       SMITH            7902
RESEARCH       SCOTT            7566
SALES          WARD             7698
SALES          TURNER           7698
SALES          ALLEN            7698
SALES          JAMES            7698
SALES          BLAKE            7839
SALES          MARTIN           7698
OPERATIONS
15 rows selected.
SCOTT@orcl11g> 

外连接 outer join - 右连接 右边集合的全集 right join

SCOTT@orcl11g> select a.dname,b.ename,b.mgr from dept a, emp b where a.deptno(+)=b.deptno;
DNAME          ENAME             MGR
-------------- ---------- ----------
ACCOUNTING     MILLER           7782
ACCOUNTING     KING
ACCOUNTING     CLARK            7839
RESEARCH       FORD             7566
RESEARCH       ADAMS            7788
RESEARCH       SCOTT            7566
RESEARCH       JONES            7839
RESEARCH       SMITH            7902
SALES          JAMES            7698
SALES          TURNER           7698
SALES          BLAKE            7839
SALES          MARTIN           7698
SALES          WARD             7698
SALES          ALLEN            7698
14 rows selected.
SCOTT@orcl11g> 

标量子查询

标量子查询也是子查询。

Oracle允许在select子句中包含单行子查询,这个也就是oracle的标量子查询,标量子查询有点类似于外连接,当使用到外连接时我们可以灵活的将其转化为标量子查询。

select d.dname,e.ename from (select deptno,dname from dept) d,(select deptno,ename from emp) e where d.deptno = e.deptno order by 1,2;
--->
select (select dname from dept b where b.deptno=a.deptno),ename from emp a order by 1,2;

SCOTT@orcl11g> select (select dname from dept b where b.deptno=a.deptno),ename from emp a order by 1,2;
(SELECTDNAMEFR ENAME
-------------- ----------
ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
RESEARCH       ADAMS
RESEARCH       FORD
RESEARCH       JONES
RESEARCH       SCOTT
RESEARCH       SMITH
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD
14 rows selected.
SCOTT@orcl11g> 

关于标量子查询和表关联的性能: 如果主查询返回的数据较多,而子查询中又没有高效的索引,关联列对应的主查询表又没有较多的重复值, 那么这个标量子查询的执行成本是很大的,标量子查询和外连接的sql语句中可以看出外连接IO成本要明显小于标量子查询。 但是标量子查询oracle内部确是有优化的,优化器cache了中间的结果。 如果结果集不大,子查询中又有高效的索引,那么这个标量子查询可能会比常规的表关联更加高效。

with语句

with as 也叫子查询部分(subquery factoring) 在真正进行查询之前预先构造了一个临时表,之后便可多次使用它做进一步的分析和处理 “一次分析,多次使用”

找出工资大于平均工资的员工,不用with

SCOTT@orcl11g> select ename,sal from emp;
ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300
14 rows selected.
SCOTT@orcl11g> select ename, sum(sal) from emp group by ename having sum(sal)>=(select sum(sal)/14 from emp);
ENAME        SUM(SAL)
---------- ----------
JONES            2975
FORD             3000
CLARK            2450
SCOTT            3000
BLAKE            2850
KING             5000
6 rows selected.
SCOTT@orcl11g> 

使用with语句:

SCOTT@orcl11g> with t as (select ename, sal from emp)
2  select ename,sal from t where sal>=(select sum(sal)/14 from emp);
ENAME             SAL
---------- ----------
JONES            2975
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
FORD             3000
6 rows selected.
SCOTT@orcl11g> 

或者

SCOTT@orcl11g> with t as (select ename,sum(sal) sal from emp group by ename)
2  select ename,sal from t where sal>=(select sum(sal)/14 from emp);
ENAME             SAL
---------- ----------
JONES            2975
FORD             3000
CLARK            2450
SCOTT            3000
BLAKE            2850
KING             5000
6 rows selected.
SCOTT@orcl11g> 

行列转换

行列转换之前看看decode和case语句的使用:

case语句:

++++++++++++++++++++++++++++++++++++++++++++++
select 
	case 
		when deptno=10 then 'ACCOUNTING'
		when deptno=20 then 'RESEARCH'
		when deptno=30 then 'SALES'
	end,
	sum(sal) from emp
	group by deptno;
++++++++++++++++++++++++++++++++++++++++++++++

SCOTT@orcl11g> select * from dept;
	DEPTNO DNAME          LOC
---------- -------------- -------------
		10 ACCOUNTING     NEW YORK
		20 RESEARCH       DALLAS
		30 SALES          CHICAGO
		40 OPERATIONS     BOSTON
SCOTT@orcl11g> 
SCOTT@orcl11g> 
select 
case 
when deptno=10 then 'ACCOUNTING'
when deptno=20 then 'RESEARCH'
when deptno=30 then 'SALES'
end,
sum(sal) from emp
8  group by deptno;
CASEWHENDE   SUM(SAL)
---------- ----------
SALES            9400
RESEARCH        10875
ACCOUNTING       8750
SCOTT@orcl11g> 

decode语句:

++++++++++++++++++++++++++++++++++++++++++++++
select 
	decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES'),
	sum(sal) from emp
	group by deptno;
++++++++++++++++++++++++++++++++++++++++++++++

SCOTT@orcl11g> 
select 
decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES'),
sum(sal) from emp
4  group by deptno;
DECODE(DEP   SUM(SAL)
---------- ----------
SALES            9400
RESEARCH        10875
ACCOUNTING       8750
SCOTT@orcl11g> 

行列转换:

++++++++++++++++++++++++++++++++++++++++++++++
select job,ename,sal from emp where job='MANAGER';
select job, 
	sum(decode(ename,'JONES',SAL)) JONES,
	sum(decode(ename,'BLAKE',SAL)) BLAKE,
	sum(decode(ename,'CLARK',SAL)) CLARK
from emp
	where job='MANAGER' group by job;
++++++++++++++++++++++++++++++++++++++++++++++

SCOTT@orcl11g> select job,ename,sal from emp where job='MANAGER';
JOB       ENAME             SAL
--------- ---------- ----------
MANAGER   JONES            2975
MANAGER   BLAKE            2850
MANAGER   CLARK            2450
SCOTT@orcl11g> 
select job, 
sum(decode(ename,'JONES',SAL)) JONES,
sum(decode(ename,'BLAKE',SAL)) BLAKE,
sum(decode(ename,'CLARK',SAL)) CLARK
from emp
6  where job='MANAGER' group by job;
JOB            JONES      BLAKE      CLARK
--------- ---------- ---------- ----------
MANAGER         2975       2850       2450
SCOTT@orcl11g> 

存储过程

写一个打印dept表的全部内容 注意:编译查看存储过程出错进行debug,可以通过show err命令来debug

++++++++++++++++++++++++++++++++++++++++++++++
create or replace procedure print_dept is 
begin
	for i in (select * from scott.dept) loop
		dbms_output.put_line(i.deptno ||','||i.dname||','||i.loc);
	end loop;
end;
/
++++++++++++++++++++++++++++++++++++++++++++++

SCOTT@orcl11g> show err
No errors.
create or replace procedure print_dept() is 
begin
for i in (select * from scott.dept) loop
dbms_output.put_line(i.deptno ||','||i.dname||','||i.loc);
end loop;
end;
7  /
Warning: Procedure created with compilation errors.
SCOTT@orcl11g> show err
Errors for PROCEDURE PRINT_DEPT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/22     PLS-00103: Encountered the symbol ")" when expecting one of the
		following:
		<an identifier> <a double-quoted delimited-identifier>
		current delete exists prior
SCOTT@orcl11g> 
SCOTT@orcl11g> 
create or replace procedure print_dept is 
begin
for i in (select * from scott.dept) loop
dbms_output.put_line(i.deptno ||','||i.dname||','||i.loc);
end loop;
end;
7  /
Procedure created.
SCOTT@orcl11g> set serverout on
SCOTT@orcl11g> exec print_dept
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
PL/SQL procedure successfully completed.
SCOTT@orcl11g> 

写一个删除表的存储过程(传入一个表名)

++++++++++++++++++++++++++++++++++++++++++++++
create or replace procedure drop_table (v_tablename varchar2) is
begin
execute immediate 'drop table ' || v_tablename ||' purge';
end;
/
++++++++++++++++++++++++++++++++++++++++++++++

SCOTT@orcl11g> create table scott.test as select * from emp;

Table created.

create or replace procedure drop_table (v_tablename varchar2) is
begin
execute immediate 'drop table ' || v_tablename ||' purge';
end;
5  /

Procedure created.

SCOTT@orcl11g> exec drop_table('scott.test');

PL/SQL procedure successfully completed.

SCOTT@orcl11g> 

To be continue….

Have a good life! 2018/06 via LinHong


Similar Posts

Comments