MySQL之DQL-多表查询

1、 多表查询

1. 多表查询的介绍

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

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

    

2. 连接的含义

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

SELECT    table1.column, table2.column
FROM    table1, table2
WHERE    table1.column1 = table2.column2;

• 在 WHERE子句中书写连接条件。
• 如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。
• N个表相连时，至少需要N－1个连接条件

3. 多表连接分类

• 按连接条件分：

• • 等值连接
  • 非等值连接

• 按其他连接方法分：

• • 外连接
  • 内连接

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

• • 基本写法：绝大多数符合SQL标准，其它关系型数据也适用。
  • ANNSI 99写法：ANSI标准提供的写法，所有关系型数据库必须支持。

2、笛卡尔积

1. 笛卡尔积定义

笛卡尔积，在数据库中表示将A表中每条记录与B表中的每条记录进行连接，连接后的查询结果就是笛卡尔积，也叫交叉连接。

如下图所示：

2. 笛卡尔积产生情况

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

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

• • 连接条件被省略
  • 连接条件是无效的

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

3. 笛卡尔积写法

• 笛卡尔积的写法

SELECT     emp.empno,   emp.ename, emp.deptno,    dept.deptno, dept.loc 
FROM       emp, dept;

• 查询结果可以看出，笛卡尔积查询出的 记录总数=A表记录数 * B表的记录数

EMPNO	ENAME	DEPTNO	DEPTNO	LOC
7369	SMITH	20	40	BOSTON
7369	SMITH	20	30	CHICAGO
7369	SMITH	20	20	DALLAS
7369	SMITH	20	10	NEW YORK
...

3、等值连接

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

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

1. 等值连接的介绍

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

• 

SELECT     emp.empno,   emp.ename, emp.deptno,    dept.deptno, dept.loc
FROM       emp, dept
WHERE      emp.deptno=dept.deptno;

EMPNO	ENAME	MGR	DEPTNO	LOC
7839	KING		10	NEW YORK
7698	BLAKE	30	30	CHICAGO
7782	CLARK	10	10	NEW YORK
7566	JONES	20	20	DALLAS
...

2. 使用AND运算符增加其它查询条件

• 现在只想查询工作地点在NEW YORK的员工编号，姓名，部门编号，工作地点

  

SELECT     emp.empno,   emp.ename, emp.deptno,    dept.deptno, dept.loc
FROM       emp, dept
WHERE      emp.deptno=dept.deptno and loc= ‘NEW YORK’;

EMPNO	ENAME	DEPTNO	DEPTNO	LOC
7839	KING	10	10	NEW YORK
7782	CLARK	10	10	NEW YORK
...

4、限制歧义列名

• 在用到多个表时可以使用表名作前缀来限定列；
• 通过使用表前缀可以提高性能；
• 通过使用列的别名可以区分来自不同表但字段名相同的列；

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、非等值连接

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

SELECT  e.ename, e.sal, s.grade
FROM  emp e,   salgrade s
WHERE  e.sal
BETWEEN  s.losal AND s.hisal;

ENAME	SAL	GRADE
JAMES	950	1
SMITH	800	1
ADAMS	1100	1
...

7、多于两个表的连接

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

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;

查询结果：

ENAME	SAL	DNAME	GRAGE
SMITH	800	RESEARCH	1
ALLEN	1600	SALES	3
WARD	1250	SALES	2
...

8、多表连接的语法分析

1. 分析要查询的列都来自于哪些表，构成FROM子句;
2. 分析这些表之间的关联关系，构建各表之间的连接条件，通常N个表，至少要有N-1个连接条件;
3. 分析是否还有其它限制条件，补充到WHERE子句的表关联关系之后，作为限制条件;
4. 根据用户想要显示的信息，补充SELECT子句。

9、自连接

1. 什么是自连接

• 思考：查询每个员工的姓名和直接上级姓名？
• 自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样。

  

SELECT  worker.ename ‘WNAME’,manager.ename ‘LNAME’
FROM  emp worker, emp manager
WHERE  worker.mgr = manager.empno;

WNAME	LNAME
SMITH	FORD
ALLEN	BLACK
WARD	BLACK
...

10、ANSI SQL：标准的连接语法

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

2. 外连接介绍

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

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

3. 左外连接写法

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

SELECT  e.ename,e.deptno,d.loc 
FROM  emp e 
LEFT OUTER JOIN dept d 
ON  (e.deptno = d.deptno);

ENAME	DEPTNO	LOC
MILLER	10	NEW YORK
KING	10	NEW YORK
CLARK	10	NEW YORK
FORD	20	DALLAS
ZHANGSAN	NULL	NULL
...

4. 右外连接写法

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

SELECT     e.ename,e.deptno,d.loc 
FROM       emp e 
RIGHT  OUTER JOIN dept d 
ON         (e.deptno = d.deptno);

ENAME	DEPTNO	LOC
SMITH	20	DALLAS
ALLEN	30	CHICAGO
JONES	20	DALLAS
MARTIN	30	CHICAGO
NULL	40	BOSTON
....

11、联合查询

1. UNION查询

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

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

语法：

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

EMPNO	ENAME	DEPTNO	DNAME
7369	SMITH	20	RESEARCH
7499	ALLEN	30	SALES
7521	WARD	30	SALES
7566	JONES	20	RESEARCH
....
8888	ZHANGSAN	NULL	NULL
NULL	NULL	40	OPERATIONS