SQL的进阶（更深入学习DQL）

前言：本篇是在SQL已经入门的基础上，进行更深入的学习（主要针对查询语句）。主要介绍如何进行多表查询、多表查询有多少中情况、多表查询常见问题的原因和解决。

注：SQL基础篇：https://blog.csdn.net/weixin_45764765/article/details/108383635

先从两个表（emp表和dept表）开始探讨：
emp表下载

提取码：uw3u 

———————-

dept表下载
提取码：aah6 

一、为什么要多表查询

为了节约磁盘空间，为了提高查询效率，也为了分类清晰和更易理解，把数据存到数据库时，不是都放在一张表，而是按需要分成许多表，不同的表对应不同的实体。

而很多时候，我们需要查询的数据，是分散在不同的表中的，只有联合多张表才能查询出期望的数据，这时就需要用到“多表查询”。

二、笛卡尔积的产生和消除（在MySQL中，多表查询会产生笛卡尔积）

1、什么是笛卡尔积（Cartesian product）

（1）在数学中，两个集合X和Y的笛卡尔积（又称直积），表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

例如：集合 A = {a, b}，集合 B = {0, 1, 2}，则两个集合的笛卡尔积为 {(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

（2）在MySQL中，多表查询也会产生笛卡尔积。

例如：联合两张表查询 -> SELECT * FROM emp, dept

会查询出76条数据，会出现很多的“鲁班”，很多的“赵云”等等，很明显这不是我们想要的最终结果。

2、如何消除笛卡尔积

答：等值连接。

也就是给多表查询加上有效的连接条件

例如：在联合emp表和dept表查询时，dept表中的列DEPTNO（部门编号）就是等于emp表中的DEPTNO，这样就让两个表有了等值的关联。

那么，SQL语句就可以这样：

SELECT * FROM emp, dept 
WHERE emp.DEPTNO = dept.DEPTNO

查询出的数据只有19条

小结：消除笛卡尔积，使用等值连接。这是多表查询必须考虑到的因素。

三、多表查询的分类

 1、内连接查询

（1）隐式内连接查询

语法： SELECT [DISTINCT] * | 字段 [别名] [,字段 [别名] ,…] FROM 表名称 [别名], [表名称 [别名] ,…] [WHERE 去笛卡尔积条件/筛选条件] [ORDER BY 排序字段 [ASC|DESC] [,排序字段 [ASC|DESC] ,…]];

注：[ ]表示可选

示例：

①查询员工编号，员工名称，员工所属部门的编号和名称

SELECT emp.EMPNO, emp.ENAME, dept.DEPTNO, dept.DNAME FROM emp, dept WHERE emp.DEPTNO = dept.DEPTNO

【解析】emp.EMPNO,emp.ENAME：指明要取emp表的员工编号和员工姓名

同理dept.DEPTNO,dept.DNAME：指名要取dept表的部门编号和部门名称

FROM emp, dept : 查询这两个表

WHERE emp.DEPTNO = dept.DEPTNO : 等值条件是emp表的部门编号等于dept表的部门编号

②查询员工的姓名，工资，所在部门的名称

SELECT e.ENAME, e.SAL, d.DNAME FROM emp e, dept d WHERE  

【解析】

FROM emp e, dept d ：查询这两个表，并且分别取别名e 和 d ，事实上是省略了AS（FROM emp AS e, dept  AS d）。

e.ENAME, e.SAL, d.DNAME 和 e.DEPTNO = d.DEPTNO： 都是使用了别名

AS是可以省略的，取别名更易于区分，性能更高。使用别名要注意注意SQL的执行顺序；跟人不一样，不能说还没出生，名字就已经取好了。

（2）显式内连接查询

语法： SELECT    table1.column, table2.column

FROM    table1 [INNER] JOIN table2  ON(table1.column1 = table2.column2 <连接条件>)

WHERE  条件（不是指连接条件）

示例：

查询员工的姓名，工资，所在部门的名称

SELECT e.ENAME, e.SAL, d.DNAME FROM emp e JOIN dept d ON e.DEPTNO = d.DEPTNO

（：：查询结果等同显示内连接）

补充：若连接条件中的两个列同名，则可以简写使用 USING

 FROM emp  e  JOIN dept d   USING (deptno) 

直接就省了 ON e.DEPTNO = d.DEPTNO

小结：①显式内连接查询与隐式内连接查询相比，查询结果是一样的，区别在于显示内连接可以看到[INNER] JOIN，并且连接条件写在ON子句上。

②当多个表中有重名列时，必须在列的名字前加上表名作为前缀或者表的别名（使用别名更简单，性能更高）。

** 问题：“内连接查询”已经可以实现了多表查询了，为什么还要“外连接查询”呢？

打个比方说，在联合emp表和dept表查询时，连接条件是emp表中的部门编号和dept表中的部门编号（e.DEPTNO = d.DEPTNO），假如有员工是还没有分配部门的，也就是该员工没有部门编号，那么，在查询结果中，就不会有该员工。这就造成了查询出来的数据是不准确的。

这时，就需要用到左外连接查询。

2、外连接查询

（1） 左外连接查询

语法：SELECT    table1.column, table2.column

FROM    table1 LEFT [OUTER] JOIN table2  ON(table1.column1 = table2.column2)

WHERE   条件（不是指等值条件）

注：查询出 JOIN 左边表的全部数据查询出来，JOIN 右边的表不匹配的数据使用 NULL 来填充数据。

示例：

查询员工的姓名，工资，所在部门的名称

SELECT e.ENAME, e.SAL, d.DNAME FROM emp e LEFT JOIN dept d ON e.DEPTNO = d.DEPTNO

（2）右外连接查询

语法：SELECT    table1.column, table2.column

FROM    table1 RIGHT [OUTER] JOIN table2  ON(table1.column1 = table2.column2)

WHERE               条件

注：和左外连接查询差不多，查询出 JOIN 右边表的全部数据查询出来，JOIN 左边的表不匹配的数据使用 NULL 来填充数据。

示例：

查询员工的姓名，工资，所在部门的名称

SELECT e.ENAME, e.SAL, d.DNAME FROM emp e RIGHT JOIN dept d ON e.DEPTNO = d.DEPTNO

（3）全外连接查询（MYSQL 中目前不支持全连接）

语法：SELECT    table1.column, table2.column

FROM    table1 FULL [OUTER] JOIN table2 ON (table1.column_name = table2.column_name)

FULL OUTER JOIN 中会返回所有右边表中的行和所有左边表中的行，即使在左边的表中没有可对应的列值或者右边的表中没有可对应的列。

注意：MYSQL 中不支持全连接，有一种做法是通过 UNION 左右连接来完成： 也就是用左连接加上右连接，然后使用关键字UNION  来完成（左边表和右边表的列需要一致,类型也需要一致）。

例子：

SELECT emp.EMPNO, emp.ENAME, emp.SAL, dept.DNAME FROM emp LEFT JOIN dept ON emp.DEPTNO = dept.DEPTNO

UNION

SELECT emp.EMPNO, emp.ENAME, emp.SAL, dept.DNAME FROM emp RIGHT JOIN dept ON emp.DEPTNO = dept.DEPTNO

3、自连接查询（自己和自己连表查询）

有些时候，在一张表中，数据有特定的联系，而单表查询是无法满足查询需要的，这时就需要用到“自连接查询”。

把同一张表看成多张表，自己和自己连表查询，获取我们期望的结果。

示例：

（1）有这样一张员工表

（2）需求是“我们要知道哪个员工是属于哪个上级管的”，这时我们就要去“查询员工的名字及其上级的名字”，而这些数据都是存在一张表中的，这时就要用到“自连接查询”。

思路：把一张表看成是多张表，自己和自己做连表查询。假装一个表的存储员工的emp表，别名e；另一张表是存储上级领导的emp表（上级领导也是公司的员工，也有员工编号）别名 m；这时就诞生了两张表 e表 和 m表，就可以连表查询了。

小注：先在e表中查到了该员工的名字，和所属领导的员工编号，再通过连表m表，由上级领导的员工编号得到上级的名字，完成最终的查询结果

（3）“查询员工的名字及其上级的名字”

SELECT e.ENAME, m.ENAME FROM emp e JOIN emp m ON e.MGR = m.EMPNO

补充：为了美观，还可以取别名

SELECT e.ENAME 员工名字, m.ENAME 领导名字
FROM emp e JOIN emp m ON e.MGR = m.EMPNO

四、补充：子查询

 1、什么是子查询？

子查询指的就是在一个查询之中嵌套了其他的若干查询。

2、什么情况下需要用到子查询？

查询数据时，select … from … where …，当where中的限定条件不是一个固定的值，而是来自另外一个查询的结果时，就需要使用到子查询。

3、如何用？

语法：

SELECT    <select_list>

FROM     table

WHERE    expr  operator (SELECt select_list FROM table)

示例：

①查询出工资比“诸葛亮”高的全部员工信息

SELECT * FROM emp WHERE SAL > (SELECT SAL FROM emp WHERE ENAME = ‘诸葛亮’)

②查询工资低于公司平均工资的全部员工信息（ps：末位淘汰制公司常用0.0哈哈）

SELECT * FROM emp WHERE SAL < (SELECT AVG(SAL) FROM emp)

当然，子查询还有更多更丰富的内容，之后会单独抽出来解析。