一、多表查询
(一)简单多表查询
1、多表查询的机制 1)SQL:
SELECT * FROM emp; --14条记录
SELECT * FROM dept;--4条记录
SELECT * FROM emp,dept;--显示56条数据？？为什么
2)分析：
先从 dept 选择一条记录(deptno=10)，分别于 emp中的14条记录分别匹配，显示14条记录。
再从 dept 选择一条记录(deptno=20)，分别于 emp中的14条记录分别匹配，显示14条记录。
依次类推。
2、笛卡尔集，
在多表查询的时候，如果不带任何条件，则会笛卡尔集。
避免：查询的条件不能少于表个数减一。(2表，必须有1个条件) 3、多表查询实例 1)显示雇员名、雇员工资及所在部门的名称
SELECT a.ename,a.sal,b.dname FROM emp a ,dept b WHERE a.deptno = b.deptno;
部门号匹配即可。 2)显示部门号为10 的部门名、员工名和工资
SELECT a.ename,a.sal,b.dname FROM emp a ,dept b WHERE a.deptno = b.deptno and a.deptno=10;
3)显示各个员工的姓名、工资，及其工资的级别
SELECT a.ename,a.sal,b.grade FROM emp a, salgrade b WHERE a.sal between b.losal and b.hisal;
4)显示雇员名、雇员工资及所在部门的名字，并按部门排序
SELECT a.ename,a.sal,b.dname FROM emp a ,dept b WHERE a.deptno = b.deptno order by b.dname;
(二)自连接
1、含义：自连接是指在同一张表的连接查询，即把一张表看成 2 张表。
2、实例 1)显示员工Ford的上级领导的姓名
SELECT a.* FROM emp a WHERE a.empno = (SELECT a.mgr FROM emp a WHERE a.ename = 'FORD') ;
分析：
先查询出FORD 的领导的员工编号。
然后在根据员工编号，查询其信息
单个可以使用子查询解决，使用容易出错：【单行子查询返回多个行】 2)显示各员工的姓名和他的上级领导姓名
SELECT distinct a.ename,b.ename,b.* FROM emp a, emp b WHERE a.mgr = b.empno;--13条数据
SELECT distinct a.ename,b.ename,b.* FROM emp a, emp b WHERE a.mgr = b.empno(+);--14条数据
【+】写在右边，表明是左外连接，左边的表列全部显示。
分析：
把 emp 一张表看成2张表，然后雇员表的 mgr 等于领导表的 empno。
【King】没有上级，不会显示。要求显示时，需要使用外连接。
使用自连接解决问题。
(三)子查询*
1、含义： 1)子查询是指嵌入其他sql语句中的 select语句，也叫嵌套查询 2)单行子查询：单行子查询是指只返回一行数据的子查询语句。 3)多行子查询：多行子查询指返回多行数据的子查询。
2、实例 1)显示与SMITH同一部门的所有员工(包括SMITH)(单行子查询：“=”)
SELECT a.* FROM emp a WHERE a.deptno = (SELECT a.deptno FROM emp a WHERE a.ename='SMITH' );
分析
SELECT a.deptno FROM emp a WHERE a.ename='SMITH' ，返回一行记录，即单行子查询。
SQL解析默认从右往左，即先解析子查询语句。
注意：where 子查询中,不能使用order by 排序。
SELECT a.* FROM emp a WHERE a.deptno = (SELECT a.deptno FROM emp a WHERE a.ename='SMITH' order by a.empno);
报错，“缺失右括号” 2)查询和10号部门的工作岗位相同的雇员的名字、岗位、工资、部门号 (多行子查询：“in”)
SELECT a.ename,a.job,a.sal,a.deptno FROM emp a WHERE a.job in (SELECT distinct a.job FROM emp a WHERE a.deptno = 10) ;
分析：
先查询出10号部门的所有工作岗位，返回多行结果，为多行子查询。
显示和他的岗位有一个相同的员工。
使用容易出错：【单行子查询返回多个行】 3、all 和 any 操作符 1)显示工资比30号部门的所有员工的工资高的员工的姓名、工资和部门号
方法1：使用max()聚合函数
SELECT a.* FROM emp a WHERE a.sal > (SELECT max(a.sal) FROM emp a WHERE a.deptno=30);
方法2：使用 all
SELECT a.* FROM emp a WHERE a.sal >all (SELECT a.sal FROM emp a WHERE a.deptno=30);
2)显示工资比30号部门的任意一个员工的工资高的员工姓名、工资和部门号，不是任何一个都高
方法1：使用min()聚合函数
SELECT a.* FROM emp a WHERE a.sal > (SELECT min(a.sal) FROM emp a WHERE a.deptno=30);
方法2：使用 any
SELECT a.* FROM emp a WHERE a.sal >any (SELECT a.sal FROM emp a WHERE a.deptno=30);
4、多列子查询 1)差别
单行子查询：单行单列
多行子查询：多行单列
多列子查询：多列 2)实例：查询与SMITH 的部门和岗位完全相同的所有雇员
SELECT a.* FROM emp a WHERE (a.deptno,a.job) = (SELECT a.deptno,a.job FROM emp a WHERE a.ename='SMITH' );
SELECT a.* FROM emp a WHERE (a.deptno,a.job) in (SELECT a.deptno,a.job FROM emp a WHERE a.ename='SMITH' );
这边“=”与 in 效果一致。
5、在 From 子句中使用子查询 1)显示高于自己部门平均工资的员工的信息
SELECT a.ename,a.sal,b.avg_sal,a.deptno FROM emp a ,(SELECT avg(a.sal) as avg_sal,a.deptno as deptno FROM emp a group by a.deptno) b WHERE a.deptno = b.deptno and a.sal > b.avg_sal;
分析：
先要知道各个部门的平均工资是多少，然后作为一个临时表
利用emp 与临时表多表查询 2)查找每个部门工资最高的人的详细资料
方法1：
SELECT a.ename,a.sal,t.max_sal , a.deptno FROM emp a, (SELECT max(a.sal) as max_sal,a.deptno as deptno FROM emp a group by a.deptno) t WHERE a.deptno = t.deptno and a.sal = t.max_sal;
得到各个部门的最高工资，作为一个临时表
利用emp 与临时表多表查询
方法2：
SELECT a.* FROM emp a WHERE a.sal = (SELECT max(b.sal) FROM emp b WHERE b.deptno = a.deptno ) ;
这种方法，无法显示子查询中的列数据，有缺陷。 3)显示每个部门的信息和人员数量
SELECT a.*,t.count_empno FROM dept a, (SELECT count(a.empno) as count_empno,a.deptno FROM emp a group by a.deptno) t WHERE a.deptno = t.deptno(+);
先获取每个部门的人员数量，作为一个临时表
利用dept 表与临时表多表查询
+：表示外连接 6、小技巧
1)行迁移
create table temp# as select empno, ename from emp where ename like 'J%';
2)蠕虫复制
insert into myTest (empno,ename,job) select empno,ename,job from myTest;
3)子查询更新数据
update emp set (empno,ename,job) = (select empno,ename,job from emp where ename = 'SMITH') where ename='SCOTT';
(四)合并查询
1、UNION：并集去重，排序 1)解释：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
SELECT a.* FROM emp a WHERE a.sal> 2500 UNION SELECT b.* FROM emp b WHERE b.job='MANAGER';
2、UNION ALL：并集不去重，并且不排序
SELECT a.* FROM emp a WHERE a.sal> 2500 UNION ALL SELECT b.* FROM emp b WHERE b.job='MANAGER';
3、INTERSECT：交集
4、MINUS：差集，总之不会显示第二个集合中的数据 1)有交集的情况：
2)没有交集的情况：
二、分页
(一)其他数据库的分页
1、MySQL
1)语法：
select * from 表名 where 条件 limit 从第几条，取几条
select * from emp where empno=1234 limit 1，3;
2、Sql server 1)语法：
select top 2 * from 表名 where id not in (select top 4 id from 表名 where 条件)
排除前4条，再取2条，实际上是取5-6条。
(二)Oracle：
1、语法：rownum
SELECT rownum, emp.* FROM emp where rownum <6；
SELECT * FROM (SELECT t.*, rownum num FROM (SELECT * FROM emp) t WHERE rownum < 10) t2 WHERE t2.num > 4；
2、分析：
分析：rownum 不能直接使用大于号(>)
使用三层嵌套，其实就是在from中使用子查询。
rownum三层嵌套，分别在第二层和第三层中使用效率最高。
第一层：SELECT * FROM emp
第二层：SELECT t.*, rownum num FROM (SELECT * FROM emp) t WHERE rownum < 10
第三层：SELECT * FROM (SELECT t.*, rownum num FROM (SELECT * FROM emp) t WHERE rownum < 10) t2 WHERE t2.num > 4； 3、实例 1)按照入职时间的先后顺序，查询从第 7 到第10 个。
SELECT * FROM (SELECT t.*, rownum num FROM (SELECT a.* FROM emp a order by a.hiredate ) t WHERE rownum < 10) t2 WHERE t2.num > 7；
排序等特殊处理等情况，请在最内层处理 2)测试，模拟10万的数据
创建表与数据：
以存在的表为模板创建新表：(行迁移)
create table myTest as select empno,ename,job from emp;
自我复制(蠕虫复制)
insert into myTest (empno,ename,job) select empno,ename,job from myTest;
测试：
SELECT * FROM (SELECT t.*, rownum num FROM (SELECT * FROM myTest ) t WHERE rownum < 51010) t2 WHERE t2.num > 50997;
三、内、外连接
(一)内连接 1、概述：内连接就是利用where 子句对2张表形成的笛卡尔集进行筛选。 2、格式 1)普通：
语法：select 列名... from 表1 表2.. where 条件
SELECT a.ename,a.deptno,b.dname FROM emp a,dept b WHERE a.deptno = b.deptno;
2)【inner】 Join on
语法：select 列名... from 表1 inner Join 表2 on (条件)
SELECT a.ename,a.deptno,b.dname FROM emp a inner join dept b on (a.deptno = b.deptno)
3、内连接的特点：
只有2张表都匹配上的记录，才会显示。 (二)外连接 left|right|full【outer】join on 1、创建2 新表 :
2、左外连 1)特点：left join
左表全部显示
右表匹配显示 2)显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空
SQL：
内连接：
SELECT a.id,a.name,b.grade FROM stu a,exam b WHERE a.id = b.id;
只显示2条数据，没有成绩的学生不会显示
外连接：
SELECT a.id,a.name,b.grade FROM stu a,exam b WHERE a.id = b.id(+);
SELECT a.id,a.name,b.grade FROM stu a left join exam b on( a.id = b.id);
分析： 内连接的时候，需要2张表都匹配上的记录 左外连接：stu(左表) 表中与exam 中没有匹配的，也需要显示。
3、右外连 1)特点：right join
左表匹配显示
右表全部显示 2)显示左右成绩，如果没有名字匹配，显示为空
SQL：
SELECT a.id,a.name,b.grade FROM stu a,exam b WHERE a.id(+) = b.id;
SELECT a.id,a.name,b.grade FROM stu a right join exam b on( a.id = b.id);
分析：
右外连接：exam(右表) 表与 stu 表中没有匹配的，也需要显示。
4、完全外连 1)特点： full outer join
左表全部显示
右表全部显示 2)显示所有成绩和所有人的名字，如果没有相应的匹配值，则显示为空
SQL：
SELECT a.id,a.name,b.grade FROM stu a full join exam b on( a.id = b.id);
SELECT a.id,a.name,b.grade FROM stu a full outer join exam b on( a.id = b.id);
分析：
右外连接：exam(右表) 表与 stu 表中没有匹配的，也需要显示。
5、总结： 1)左、右外连可以相互转换，只需要换表的位置即可。
SELECT a.id,a.name,b.grade FROM stu a right join exam b on(a.id = b.id);
SELECT a.id,a.name,b.grade FROM exam b left join stu a on( a.id = b.id);
2)小结
6、练习：
1)列出部门名称和这些部门员工的员工信息，同时列出那些没有员工的部门
SELECT b.dname,a.* FROM emp a right join dept b on (a.deptno = b.deptno) order by b.deptno;
SELECT b.dname,a.* FROM dept b left join emp a on (a.deptno = b.deptno) order by b.deptno;
SELECT b.dname,a.* FROM emp a, dept b WHERE b.deptno = a.deptno(+) order by b.deptno;
SELECT b.dname,a.* FROM emp a, dept b WHERE a.deptno(+) = b.deptno order by b.deptno;
四、练习
1、显示雇员总数，以及获得补助的雇员数
SELECT count(*) ,count(comm) FROM emp;
2、显示管理者的人数
SELECT count(distinct mgr) FROM emp;
3、显示每个部门每个岗位的平均工资、每个部门的平均工资、每个岗位的平均工资。
SELECT avg(a.sal),a.deptno,a.job FROM emp a GROUP BY cube(a.deptno,a.job );
cube：立方体函数，对deptno分组，job分组，对(deptno、job)分组
