6. DQL-多表查询

       本小节主要讲解数据库的多表查询功能，可实现从多个表中查询数据，多表查询内容包括内连接，外连接，笛卡尔积，等值连接，非等值连接，SQL99标准链接语法以及联合查询。

6.1.1 多表查询的介绍

• 从多个表中获取数据
• 思考如下问题？

• 写一条查询语句，查询员工姓名、部门名称、工作地点？

6.1.2 连接的含义

       连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。

       语法为：

select table1.column, table2.column
form table1, table2
where table1.column1 = table2.column2;

        1、在where子句中书写连接条件；

        2、如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀；

        3、n个表相连时，至少需要N－1个连接条件。

6.1.3 多表连接分类

按连接条件分：

      等值连接

      非等值连接

按其他连接方法分：

      外连接

      内连接

多表连接包含多种写法，我们主要介绍：

      基本写法：绝大多数符合SQL标准，其他关系型数据库也适用；

      ANNSI99写法：ANNSI标准提供的写法，所有关系型数据库必须支持。

6.2 笛卡尔积

6.2.1 笛卡尔积定义

      笛卡尔积，在数据库中表示将A表中每条记录与B表中的每条记录进行连接，连接后的查询结果就是笛卡尔积，也叫交叉连接。//了解即可看不懂也没事

6.2.2 笛卡尔积产生情况

      在实际应用中，笛卡尔积本身大多没有什么实际用处，而且还有一个附加问题：产生一个巨表。

      笛卡尔积在下列情况产生：

            链接条件被省略

            连接条件是无效的

      为了避免笛卡尔积的产生，通常要在where子句中包含一个有效连接条件

6.2.3 笛卡尔积写法

      笛卡尔积的写法

select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.loc 
from emp,dept;

      笛卡尔积查询出的 记录总数=A表记录数*B表的记录数。

6.3 等值连接

      在实际应用中，笛卡尔积本身大多没有什么实际用处，只有在两个表连接时加上限制条件，才会有实际意义。

      连接的本质就是过滤掉或者避免产生无意义的两个表的组合数据。等值连接就是对连接条件进行有效的等值判断。

6.3.1 等值连接的介绍

      查询所有员工编号，姓名，部门编号，工作地点

select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.loc
from emp,dept
where emp.deptno=dept.deptno;

6.4 限制歧义列名

      在用到多个表时可以使用表名作前缀来限定列；

      通过使用表前缀可以提高性能；

      通过使用列的别名可以区分来自不同表但字段名相同的列；

6.5 使用表的别名

      通过使用表的别名来简化查询语句

      简化前 

select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.loc
from emp,dept
where emp.deptno=dept.deptno;

      简化后 

select e.empno,e.ename,e.deptno,d.deptno,d.loc
from emp e,dept d
where e.deptno=d.deptno;

6.7 非等值连接

      查询每个员工的姓名，工资，工资等级

select e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal
between s.losal and s.hisal;

                     emp表

                      salgrade表 

      "emp表中的薪水实在salgrade表所规定的最低和最高范围内。"

6.8 多于两个表的连接

      如果想从员工表，部门表，工资级别表中同时检索数据，那么就会涉及三张表做连接。这个时候就需要至少两个连接条件。

      查询每个员工的员工姓名，员工工资，部门名称，工资级别。

select e.ename,e.sal,d.dname,s.grade 
from emp e,dept d,salgrade s 
where e.deptno = d.deptno and e.sal between s.losal and s.hisal;

6.9 多表连接的语法分析

      分析要查询的列都来自于哪些表，构成from子句;

      分析这些表之间的关联关系，构建各表之间的连接条件，通常N个表，至少要有N-1个连接条件;

      分析是否还有其它限制条件，补充到where子句的表关联关系之后，作为限制条件;

      根据用户想要显示的信息，补充select子句。

6.11 自连接

6.11.1 什么是自连接

      思考：查询每个员工的姓名和直接上级姓名？

      自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样。

select worker.ename ‘WNAME’,manager.ename ‘LNAME’
from emp worker, emp manager
where worker.mgr = manager.empno;

      在自连接中,就将自身表起n个名字(n代表你要连接几次)然后将这n个名字当作n个表,剩下的操作就和多表连接是一样的.

6.12 ANSI SQL：标准的连接语法

6.12.1 ANSI SQL：1999标准的连接语法

      除了上述已经讲过的连接语法外，多表查询还有美国国家标准协会（ANSI）的SQL标准语法：1999标准的连接语法。

select table1.column, table2.column
from table1
[join table2 
 on (table1.column_name = table2.column_name)]  | 
[left | right outer join table2 
 on (table1.column_name = table2.column_name)];

6.12.2 外连接介绍

      在多表连接时，可以使用外部连接来查看没有匹配连接条件的数据行

      左外连接以LEFT OUTER JOIN关键字左边的表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来。

      右外连接以RIGHT OUTER JOIN子句中的右边表为基表，该表所有行数据按照连接条件无论是否与左边表能匹配上，都会被显示出来。

6.12.3 左外连接写法

      查询所有雇员姓名，部门编号，部门名称，包括没有部门的员工也要显示出来.

select e.ename,e.deptno,d.loc 
from emp e 
left outer join dept d 
on (e.deptno = d.deptno);

6.12.4 右外连接写法

      查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来.

select e.ename,e.deptno,d.loc 
from emp e 
right outer join dept d 
on (e.deptno = d.deptno);

6.13 联合查询

6.13.1 UNION查询

      //本小节了解即可 

      对于union查询，就是把多个查询结果合并起来，形成一个新的查询结果集（并集），ALL表示包含两个结果集中重复数据是否消除

      注：目前MySQL暂时不支持全外连接，所以可以使用union/union on进行实现

语法：

      select字段列表 from 表A

      union [all]

      select 字段列表 from 表B

查询所有部门（包括没有员工的部门）及所有员工（包括没有部门的员工）的   

select e.empno,e.ename,d.deptno,d.dname 
from emp e left outer join dept d on (e.deptno = d.deptno)
union 
select e.empno,e.ename,d.deptno,d.dname 
from emp e right outer join dept d on (e.deptno = d.deptno);