Oracle基础（五）：多表查询

一、多表查询

（一）简单多表查询

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 张表。

SELECT a.* FROM emp a WHERE a.empno = (SELECT a.mgr FROM emp a WHERE a.ename = 'FORD') ;

• 先查询出FORD 的领导的员工编号。
• 然后在根据员工编号，查询其信息
• 单个可以使用子查询解决，使用容易出错：【单行子查询返回多个行】

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】没有上级，不会显示。要求显示时，需要使用外连接。
  • 使用自连接解决问题。

（三）子查询*

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);
  • 报错，“缺失右括号”

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号部门的所有工作岗位，返回多行结果，为多行子查询。
  • 显示和他的岗位有一个相同的员工。
  • 使用容易出错：【单行子查询返回多个行】

• 方法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);

• 方法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);

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 效果一致。

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 与临时表多表查询

• 方法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 ) ;

• 这种方法，无法显示子查询中的列数据，有缺陷。

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 表与临时表多表查询
• +：表示外连接

create table temp# as select empno, ename from emp where ename like 'J%';

insert into myTest (empno,ename,job) select empno,ename,job from myTest;

update emp  set (empno,ename,job) = (select  empno,ename,job from emp where ename = 'SMITH')  where ename='SCOTT';

（四）合并查询

SELECT a.* FROM emp a WHERE a.sal> 2500 UNION SELECT b.* FROM emp b WHERE b.job='MANAGER';

SELECT a.* FROM emp a WHERE a.sal> 2500 UNION ALL SELECT b.* FROM emp b WHERE b.job='MANAGER'; 

2）没有交集的情况：

二、分页

（一）其他数据库的分页

1、MySQL
1）语法：

select * from 表名 where 条件 limit 从第几条，取几条
select * from emp  where empno=1234 limit 1，3;

select top 2 * from 表名 where id not in (select top 4 id from 表名 where 条件)
排除前4条，再取2条，实际上是取5-6条。

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；

• 分析：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；

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；

• 排序等特殊处理等情况，请在最内层处理

• 创建表与数据：
  • 以存在的表为模板创建新表：(行迁移)

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;

三、内、外连接

• 语法：select 列名... from 表1 表2.. where 条件

SELECT a.ename,a.deptno,b.dname FROM emp a,dept b WHERE a.deptno = b.deptno;

• 语法：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) 

• 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 中没有匹配的，也需要显示。

• 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 表中没有匹配的，也需要显示。

• 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 表中没有匹配的，也需要显示。

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); 

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;

四、练习

<span style="font-family:Arial;font-size:18px;">SELECT count(*) ,count(comm) FROM emp;</span>

SELECT count(distinct mgr) FROM emp;

<span style="font-family:Arial;font-size:18px;">SELECT avg(a.sal),a.deptno,a.job FROM emp a GROUP BY cube(a.deptno,a.job );</span>