今天再次复习下查询
简单查询
where 条件中的关系表达式需要遵循以下原则
字符类型及日期类型需要在两端用单引号引起来
字符型大小敏感
日期型格式铭感，DD-MON-RR
between and 包括边界
多列排序，首先按照第一列或表达式进行排序，当第一列或表达式的数据相同时，以第二列或表达式进行排序。
排序还可以使用位列编号
分组查询：
在进行多列分组统计时，如果直接使用GROUP BY 字句指定分组列，则只能生成基于所有分组列的统计结果。如果在GROUP BY 字句中使用ROLLUP 或者是CUBE语句，除了生成基于所有指定列的分组外，还可以
生成基于指定列不同子集的统计结果.
查询10.20.30的各个部门中各个职位的平均工资、各个部门的平均工资和所有员工的平均工资
SQL> select deptno ,job,avg(sal) from emp where deptno in (10,20,30) group by rollup(deptno,job);
DEPTNO JOB AVG(SAL)
------ --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 2916.66666
20 CLERK 950
20 ANALYST 3000
20 MANAGER 2975
20 2175
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
30 1566.66666
2073.21428
查询10.20.30的各个部门中各个职位的平均工资、各个部门的平均工资、各个职位的平均工资和所有员工的平均工资
SQL> select deptno ,job,avg(sal) from emp where deptno in (10,20,30) group by cube(deptno,job);
DEPTNO JOB AVG(SAL)
------ --------- ----------
2073.21428
CLERK 1037.5
ANALYST 3000
MANAGER 2758.33333
SALESMAN 1400
PRESIDENT 5000
10 2916.66666
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 2175
20 CLERK 950
20 ANALYST 3000
20 MANAGER 2975
30 1566.66666
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
18 rows selected
可以使用grouping函数来查看这个列有没有参与到统计
SQL> select deptno ,job,avg(sal),grouping(deptno),grouping(job) from emp where deptno in (10,20,30) group by cube(deptno,job);
DEPTNO JOB AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)
------ --------- ---------- ---------------- -------------
2073.21428 1 1
CLERK 1037.5 1 0
ANALYST 3000 1 0
MANAGER 2758.33333 1 0
SALESMAN 1400 1 0
PRESIDENT 5000 1 0
10 2916.66666 0 1
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
20 2175 0 1
20 CLERK 950 0 0
20 ANALYST 3000 0 0
20 MANAGER 2975 0 0
30 1566.66666 0 1
30 CLERK 950 0 0
30 MANAGER 2850 0 0
30 SALESMAN 1400 0 0
18 rows selected
合并分组查询：
在Oracle 11g中，可以将几个单独的分组查询合并成一个分组查询。
查询每个部门的平均工资和各个职位的平均工资
SQL> select deptno,job,avg(sal) from emp group by grouping sets (deptno,job);
DEPTNO JOB AVG(SAL)
------ --------- ----------
CLERK 1037.5
SALESMAN 1400
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000
30 1566.66666
20 2175
10 2916.66666
8 rows selected
grouping sets语句的作用就是使用一个语句得到多个分组统计的结果集。
累积统计查询
1.总体统计累积统计
对员工的工资、人数进行总体累积统计
总体累积的每一个结果都是针对之前的所有记录进行的，在over函数中使用order by 语句指定统计的顺序，如果不指定order by语句。则不进行累积统计
对员工的工资、人数进行总体累积统计
SQL> select empno,sum(sal) over(order by empno) as total_sal,count(*) over(order by empno) as num,count(*) over() total_num from emp; EMPNO TOTAL_SAL NUM TOTAL_NUM ----- ---------- ---------- ---------- 7369 800 1 14 7499 2400 2 14 7521 3650 3 14 7566 6625 4 14 7654 7875 5 14 7698 10725 6 14 7782 13175 7 14 7788 16175 8 14 7839 21175 9 14 7844 22675 10 14 7876 23775 11 14 7900 24725 12 14 7902 27725 13 14 7934 29025 14 14 14 rows selected
2.分组累积统计
使用partition by 字句
对各个部门的员工的工资人数进行统计
SQL> select empno,sum(sal) over(partition by deptno order by empno) as total_sal_dept, count(*) over(partition by deptno order by empno) as num_dept from emp; EMPNO TOTAL_SAL_DEPT NUM_DEPT ----- -------------- ---------- 7782 2450 1 7839 7450 2 7934 8750 3 7369 800 1 7566 3775 2 7788 6775 3 7876 7875 4 7902 10875 5 7499 1600 1 7521 2850 2 7654 4100 3 7698 6950 4 7844 8450 5 7900 9400 6 14 rows selected
连接查询
1.交叉连接：交叉连接又称笛卡尔积连接，是两个或多个表之间的无条件连接。
emp 中有14条记录，dept中4条记录，那么就有64条记录
SQL> select * from emp cross join dept; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- ------------- 7369 SMITH CLERK 7902 1980/12/17 800.00 20 10 ACCOUNTING NEW YORK ............................................... 56 rows selected
2.内连接：就是根据指定的条件进行连接查询
SQL> select * from emp inner join dept on emp.deptno = dept.deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- ------------- 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981/11/17 5000.00 10 10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 ACCOUNTING NEW YORK 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980/12/17 800.00 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 20 RESEARCH DALLAS 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 30 SALES CHICAGO 7900 JAMES CLERK 7698 1981/12/3 950.00 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 30 SALES CHICAGO 14 rows selected
我觉得还是用标准的SQL
自连接：
查询所有员工的员工号、员工姓名和该员工领导的员工号和员工名。
SQL> select t1.ename,t1.empno,t2.ename,t2.empno from emp t1 join emp t2 on t1.mgr = t2.empno ; ENAME EMPNO ENAME EMPNO ---------- ----- ---------- ----- FORD 7902 JONES 7566 SCOTT 7788 JONES 7566 TURNER 7844 BLAKE 7698 ALLEN 7499 BLAKE 7698 WARD 7521 BLAKE 7698 JAMES 7900 BLAKE 7698 MARTIN 7654 BLAKE 7698 MILLER 7934 CLARK 7782 ADAMS 7876 SCOTT 7788 BLAKE 7698 KING 7839 JONES 7566 KING 7839 CLARK 7782 KING 7839 SMITH 7369 FORD 7902 13 rows selected
外连接：
（1）左外连接：在外连接的基础上将连接操作符左侧标红不符合连接条件的记录加入结果集中，与之对应的连接操作符左侧表列用NULL填充。
查询10号部门的部门名称、员工号、员工名称和所有其他部门的名称
SQL> select dept.dname,emp.empno,emp.ename from dept left join emp on dept.deptno = emp.deptno and dept.deptno=10; DNAME EMPNO ENAME -------------- ----- ---------- ACCOUNTING 7782 CLARK ACCOUNTING 7839 KING ACCOUNTING 7934 MILLER OPERATIONS SALES RESEARCH 6 rows selected
右外连接同左。
全外连接：是指在内连接的基础上，将连接操作符两侧表中不符合连接条件的记录加入结果集中。
子查询
1.单行单列子查询
查询比105号员工工资高的员工的员工号，姓名，工资
SQL> select emp.empno,emp.ename ,emp.sal from emp where sal > (select sal from emp where empno>7902); EMPNO ENAME SAL ----- ---------- --------- 7499 ALLEN 1600.00 7566 JONES 2975.00 7698 BLAKE 2850.00 7782 CLARK 2450.00 7788 SCOTT 3000.00 7839 KING 5000.00 7844 TURNER 1500.00 7902 FORD 3000.00 8 rows selected
2.多行单列子查询
查询与50号部门某个员工工资相等的员工信息
SQL> select * from emp where sal in( select sal from emp where deptno=20); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
3.单行多列查询
查询与159号员工的工资、职位都相同的员工的信息
SQL> select * from emp where (sal,job) = (select sal,job from emp where empno=7788); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7902 FORD ANALYST 7566 1981/12/3 3000.00 20
4多行多列子查询
查询与30号部门某个人员工的工资和职位都相同的员工的信息
SQL> select * from emp where (sal,job) in (select sal,job from emp where deptno=30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 7900 JAMES CLERK 7698 1981/12/3 950.00 30 6 rows selected
5.相关子查询，就是子查询需要用到父类查询的信息。
查询没有任何员工的部门信息
SQL> select * from dept where not exists (select deptno from emp where dept.deptno = emp.deptno);
DEPTNO DNAME LOC
------ -------------- -------------
40 OPERATIONS BOSTON
使用exists或者 not exists来实现。
查询比本部门平均工资高的员工信息
select * from emp where sal> (select avg(sal) from emp where dept.deptno = emp.deptno );
6.在from子句中使用子查询
7 在DDL中使用子查询
利用自查询建立一个emp_subquery表
SQL> create table emp_subquery 2 as select * from emp; Table created
8 使用with子句的子查询
如果在SQL语句中多次使用同一个子查询，可以通过with子句给子查询指定一个名字，从而可以实现通过名字引用该子查询，而不必每次都完整写出子查询。
查询人数最多的部门
SQL> select * from dept where deptno in(select deptno from emp group by deptno
having count(*)>=all(select count(*)from emp group by deptno)) ; DEPTNO DNAME LOC ------ -------------- ------------- 30 SALES CHICAGO
层次查询
层次查询，又称树形查询，能够将一个表中的数据按照记录之间联系以树状结构的形式显示出来。
利用分级查询显示emp表员工与领导之间的关系
SQL> select empno ,ename,mgr from emp start with empno=7839 connect by prior empno =mgr; EMPNO ENAME MGR ----- ---------- ----- 7839 KING 7566 JONES 7839 7788 SCOTT 7566 7876 ADAMS 7788 7902 FORD 7566 7369 SMITH 7902 7698 BLAKE 7839 7499 ALLEN 7698 7521 WARD 7698 7654 MARTIN 7698 7844 TURNER 7698 7900 JAMES 7698 7782 CLARK 7839 7934 MILLER 7782 14 rows selected
查询显示工资大于2000且最高领导为jones的员工信息
SQL> select * from emp where sal>2000 start with ename ='JONES' connect by prior empno = mgr; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7902 FORD ANALYST 7566 1981/12/3 3000.00 20
集合操作
在查询过程中，可以使用集合运算符UNION、 union all、intersect、minus将多个查询的结果进行并 交 差 的运算。
其中的注意点：
（1）几个结果集必须具有相同的列数与数据类型
（2）只能在最后一个查询之后使用order by 指明
（3）集合操作后的结果集以第一个查询的列名作为最终的列名。
1.union:用于获取几个查询结果集的并集，将重复记录记录为一个，并且默认第一列进行排序
查询30号部门的员工号，工资、和部门号以及工资大于8000的所有员工的员工、工资、部门号
SQL> select empno,sal,deptno from emp where deptno=30 union select empno,sal,deptno from emp where sal>3000; EMPNO SAL DEPTNO ----- --------- ------ 7499 1600.00 30 7521 1250.00 30 7654 1250.00 30 7698 2850.00 30 7839 5000.00 10 7844 1500.00 30 7900 950.00 30
2.union all:会保留所有重复的记录，而且获得的结果时无序的。
SQL> select empno,sal,deptno from emp where deptno=30 union all select empno,sal,deptno from emp where sal>2000; EMPNO SAL DEPTNO ----- --------- ------ 7499 1600.00 30 7521 1250.00 30 7654 1250.00 30 7698 2850.00 30 7844 1500.00 30 7900 950.00 30 7566 2975.00 20 7698 2850.00 30 7782 2450.00 10 7788 3000.00 20 7839 5000.00 10 7902 3000.00 20 12 rows selected
3 intersect:用于获取所有查询结果的交集，只返回同时存在于几个查询结果集中的记录。
查询30号部门中工资大于2000元的员工号、员工姓名、工资、部门号。
SQL> select empno,ename,sal,deptno from emp where deptno=30 intersect select empno ,ename,sal,deptno from emp where sal>2000; EMPNO ENAME SAL DEPTNO ----- ---------- --------- ------ 7698 BLAKE 2850.00 30
3.minus：用于获取几个查询结果集的差集，即返回在第一个结果集中存在，而在第二个结果集中不存的记录。返回的默认按第一列进行排序
查询50号部门中职位不是MANAGER的员工号、员工名和职位名称
SQL> select empno,ename,job from emp where deptno=30 minus select empno ,ename,job from emp where job = 'MANAGER'; EMPNO ENAME JOB ----- ---------- --------- 7499 ALLEN SALESMAN 7521 WARD SALESMAN 7654 MARTIN SALESMAN 7844 TURNER SALESMAN 7900 JAMES CLERK