关联查询

发表于 | 分类于 ，

多表查询

要想进行关联意味着两张表中必须包含相关联的字段。
多于两张表的连接：
为了连接n个表，至少需要n-1个连接。

sql:1992语法的连接

语法规则
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
▪ 在 WHERE 子句中写入连接条件
▪ 当多个表中有重名列时，必须在列的名字前加上表名作为前缀
▪ 连接的类型：

  1. 等值连接 – Equijoin
  2. 非等值连接 – Non-equijoin
  3. 外连接 – Outer join
  4. 自连接 – Self join

92语法

数据来自于多张表 ,92表连接
▪ 注意: 明确引用同名的列，必须使用表名 或者别名区分

等值连接

等值连接，两个表中包含相同的列名。

例如，雇员表（emp）中的部门编号和部门表（dept）中的部门编号是一样的。
select * from emp,dept where emp.deptno = dept.deptno;
在这里插入图片描述
所以可以查询雇员的名称和所属部门的名称：
select ename,dname from emp,dept where emp.deptno = dept.deptno;

非等值连接

非等值连接，两个表中没有相同的列名，但是某一列在另一张表的列的范围之中【!= > < >= <= between and】

1
2
3
4
--查询雇员名称以及自己的薪水等级
select e.ename, e.sal, sg.grade
  from emp e, salgrade sg
 where e.sal between sg.losal and sg.hisal;

▪ select 字段列表 from 表1,表2,表3….
▪ where 表1.列!=表2.列 and 表1.列!=表3.列

外连接

在等值基础上，确保一张表(主表)的记录都存在。从表满足则匹配，不满足补充null
分类*：
▪ 1、左外连接: 主表在左边（把左表的全部数据显示）
▪ 2、右外连接: 主表在右边（把右表的全部数据显示）

1
2
3
4
5
6
--需要将雇员表中的所有数据都进行显示，利用等值连接只会把关联到的数据显示，
--没有关联到的数据不会显示，此时需要外连接。
select * from emp e,dept d where e.deptno = d.deptno; --等值连接
select * from emp e,dept d where e.deptno = d.deptno(+); --左外连接
select * from emp e,dept d where e.deptno(+) = d.deptno; --右外连接
select * from emp e,dept d where e.deptno(+) = d.deptno(+); --不能同时存在

在这里插入图片描述
在这里插入图片描述

自连接:

自连接,特殊的等值连接。将一张表当成不同的表来看待，自己关联自己

1
2
--将雇员和他经理的名称查出来
select e.ename || ' work for '||m.ename from emp e,emp m where e.mgr = m.empno;

笛卡尔积

当关联多张表，但不指定连接条件的时候，会进行笛卡尔积。
关联后的总记录为m * n，一般不要使用

1
select * from emp e, dept d; -- 16 * 4 = 64条

▪ 92的连接语法有什么问题？？？
 92语法中，多张表的连接条件会放到方法where子句中，同时where需要对表进行条件过滤，因此，相当于将过滤条件和连接条件揉到一起，太乱了，所以出现了99语法。

sql:1999语法的连接

▪ sql1992的语法规则暴露了这样的缺点：语句过滤条件和表连接的条件都放到了where子句中 。当条件过多时，联结条件多，过滤条件多时，就容易造成混淆。
▪ SQL1999修正了整个缺点，把联结条件，过滤条件分开来，包括以下新的TABLE JOIN的句法结构：
– CROSS JOIN
– NATURAL JOIN
– USING子句
– ON子句
 – LEFT OUTER JOIN
 – RIGHT OUTER JOIN
 – FULL OUTER JOIN
 – Inner outer join
▪ 两种语法的SQL语句没有任何限制，再公司中可以随意使用，但是建议使用99语法，不要使用92语法，SQL显得清楚明了。

CROSS JOIN(交叉连接)

等同于92语法中的笛卡尔积。少用它

1
2
select * from emp cross join dept;
select * from emp ,dept;

NATURAL JOIN(自然连接)

相当于是等值连接，但是注意：不需要写连接条件，会从两张表中找到相同的列做连接。
▪ 当两张表中不具有相同的列名的时候，会进行笛卡尔积操作。所以规定上禁止在不具有相同的列名的时候使用NATURAL JOIN。
▪ 自然连接*跟92语法的自连接没有任何关系
▪ 自然连接的结果不保留重复的属性

1
2
3
select * from emp e natural join dept d ;--自然连接的结果不保留重复的属性
select * from emp e,dept d where e.deptno=d.deptno;--保留
select * from emp e natural join salgrade sg;

ON子句

为了设置任意的连接条件或者指定连接的列，需要使用ON子句。
▪ 使用ON 子句可以将连接条件（on）与其它的查询条件（where）分开书写。
▪ on子句，可以添加任意的连接条件。

1
2
3
4
--添加连接条件 相当于92语法中的等值连接
select * from emp e join dept d on e.deptno = d.deptno;
--相当于92语法中的非等值连接
select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;

使用on创建连接三表连接
检索雇员名字、所在单位、薪水等级：这三个信息在三个表里面，
所以只能用多表联结

1
2
3
4
5
6
select ename, dname, grade
  from emp
  join dept
    on emp.deptno = dept.deptno
  join salgrade
    on emp.sal between salgrade.losal and salgrade.hisal;
外连接

left (outer) join（左外连接），会把左表中全部数据正常显示，右表没有对应的数据直接显示空即可。outer可省略
right (outer) join（右外连接），会把右表中全部数据正常显示，左表没有对应的数据直接显示空即可。outer可省略
full (outer) join，相当于左外连接和右外连接的合集

内连接

inner join（内连接），两张表的连接查询，只会查询出有匹配记录的数据。也就是说，必须要有对应才能查出来。

1
2
3
4
5
6
7
8
9
10
11
12
--left (outer) join
select * from emp e left outer join dept d on e.deptno = d.deptno;
select * from emp e,dept d where e.deptno = d.deptno(+);
--right (outer) join
select * from emp e right outer join dept d on e.deptno = d.deptno;
select * from emp e,dept d where e.deptno(+) = d.deptno;
--full (outer) join
select * from emp e full outer join dept d on e.deptno=d.deptno;
--inner join 底下效果相同 不会合并相同的列
select * from emp e inner join dept d on e.deptno=d.deptno;
--默认内连接
select * from emp e join dept d on e.deptno=d.deptno;

USING子句

using，除了可以使用on表示连接条件之外，也可以使用using作为连接条件,但是此时连接条件的列不再归属于任意一张表。
▪ using子句引用的列在sql任何地方不能使用表名或者别名做前缀，基本不使用它。

1
2
3
4
5
select * from emp e join dept d using(deptno); --deptno只显示一列，不保留重复的属性
select * from emp e join dept d on e.deptno=d.deptno;--deptno显示两列
select deptno from emp e join dept d using(deptno); --没问题
select e.deptno from emp e join dept d using(deptno); --报错
select e.deptno from emp e join dept d on e.deptno=d.deptno；--没问题

子查询

SQL允许多层嵌套。子查询，即嵌套在其他查询中的查询
▪ 把子查询当作一张表来看待。外层的语句可以把内嵌的子查询返回的结果当成一张表使用。
 – 子查询要用括号括起来
 – 将子查询放在比较运算符的右边(增强可读性)
▪ 子查询分类：
 1.单行子查询
  返回一行记录
  使用单行记录比较运算符

复杂的sql语句可以拆开来做。

1
2
3
4
5
--查询有哪些人的薪水是在整个雇员的平均薪水之上的？
--1.首先求所有雇员的平均薪水
select avg(e.sal) from emp e;
--2.把所有人的薪水与平均薪水做比较
select * from emp e where e.sal > (select avg(e.sal) from emp e);

 2.多行子查询
  返回多行记录
  使用集合比较运算符

在多行子查询中使用in

1
2
3
4
5
--我们要查在雇员中有哪些人是经理人？
--1.查询所有经理人编号(去重操作)
select distinct e.mgr from emp e;
--2.在雇员表中过滤这些编号即可
select * from emp e where e.empno in (select distinct e.mgr from emp e);

在多行子查询中使用some all

1
2
3
4
5
6
7
8
--找出部门编号为20的所有员工中收入最高的职员?
--1.找出部门编号为20的所有员工的收入
select e.sal from emp e where e.deptno = 20;
--2.求不小于1中求出收入的雇员，且部门编号是20
select *
  from emp e
 where sal >= all (select e.sal from emp e where e.deptno = 20)
   and e.deptno = 20;

在From子句中使用子查询

1
2
3
4
5
6
7
8
--求每个部门平均薪水的等级
--1.首先将每个部门的平均薪水求出来结果可以当成一张虚拟表
select e.deptno,avg(e.sal) from emp e group by e.deptno;
--2.上面的表跟薪水等级表做关联，求得薪水等级
select t.deptno, sg.grade
  from salgrade sg
  join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
    on t.vsal between sg.losal and sg.hisal;

分页

限制输出，limit，mysql中用来做限制输出的，但是Oracle中不是。
在Oracle中，如果需要使用限制输出和分页的功能，必须使用rownum，但是rownum不能直接使用，需要嵌套使用

1
2
3
4
--求薪水最高的前5名雇员
select *
  from (select * from emp e order by e.sal desc) t1
 where rownum <= 5;

理解：select * from emp e order by e.sal desc排好序后形成一张虚拟表，rownum相当于系统默认赋予的一个列名，放着排序的序号，但是不显。也不能直接：select * from emp e where rownum <= 5 order by e.sal desc;，这样只是将前五条进行了一个排序。
如果求薪水最高的第6到10名雇员，不能直接在where中用rownum > 5 and rownum <= 10来限制输出。
原因：因为rownum的值不是固定死的，而是随着变化而变化的。比如这道例子中，rownum>5会先进行一个截取，截取后前五条的编号就不存在了，然后把后面的往前递进（原来第六条变成了第一条…），然后再rownum<=10截取，就会出现问题。
可以进行如下操作：

1
2
3
4
5
6
7
8
9
10
11
--可以多打印出rownum这一列，必须写t1.,不然查不出来
select t1.*,rownum
  from (select * from emp e order by e.sal desc) t1
 where rownum <= 10;
--求薪水最高的第6到10名雇员
select *
  from (select t1.*, rownum rn
          from (select * from emp e order by e.sal desc) t1
         where rownum <= 10) t
 where t.rn > 5
   and t.rn <= 10;

使用rownum的时候必须要在外层添加嵌套，此时才能将rownum作为其中的一个列，然后再进行限制输出。

易错例子

1、找出部门编号为20的所有员工中收入最高的职员

1
2
3
4
5
6
7
--1.找出部门编号为20的所有员工的收入
select e.sal from emp e where e.deptno = 20;
--2.求不小于1中求出收入的雇员，且部门编号是20
select *
  from emp e
 where sal >= all (select e.sal from emp e where e.deptno = 20)
   and e.deptno = 20;

第二步不要忘了约束条件e.deptno = 20

2、求平均薪水最高的部门的部门编号

1
2
3
4
5
6
7
8
9
10
11
--1.部门平均薪水(t1)
select e.deptno, avg(e.sal) vsal from emp e group by e.deptno;
--2.求表1中平均薪水最高的部门薪水
select max(t1.vsal)
  from (select deptno, avg(e.sal) vsal from emp e group by e.deptno) t1;
--3.求平均薪水最高的部门编号
select t1.deptno, t1.vsal
  from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t1
 where t1.vsal =
       (select max(t1.vsal)
          from (select deptno, avg(e.sal) vsal from emp e group by e.deptno) t1);

复杂的sql语句可以拆开来做。

3、求出部门编号为20的雇员名、部门名、薪水等级

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
--方法一
--1.求出部门编号为20的雇员名、部门编号、薪水
select e.ename, e.deptno, e.sal from emp e where e.deptno = 20;
--2.t1表与salgrade表进行关联,求得薪水等级
select t1.ename, t1.deptno, sg.grade
  from salgrade sg
  join (select e.ename, e.deptno, e.sal from emp e where e.deptno = 20) t1
    on t1.sal between sg.losal and sg.hisal;
--3.t2表与dept表进行关联，求得部门名
select t2.ename, d.dname, t2.grade
  from dept d natural
  join (select t1.ename, t1.deptno, sg.grade
          from salgrade sg
          join (select e.ename, e.deptno, e.sal
                 from emp e
                where e.deptno = 20) t1
            on t1.sal between sg.losal and sg.hisal) t2;
--方法二
select ename, dname, grade
  from emp e
  join dept d
    on d.deptno = e.deptno
  join salgrade sg
    on e.sal between sg.losal and sg.hisal
 where e.deptno = 20;

可以使用on创建三表连接，不需要将3个变成2个，再进行关联，比较麻烦