数据库学习（13）MySQL数据库DML——表连接查询_数据库学习表的各种连接查询-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43360025/article/details/131038391

MySQL数据库DML——表连接查询

从多张表中提取数据
表连接的分类
内连接
外连接

从多张表中提取数据

从多张表中提取数据，必须指定关联的条件。如果不定义关联条件就会出现无条件连接，两张表的数据会较差连接，产生笛卡尔积。例如：

SELECT empno,ename,dname
FROM t_emp JOIN t_dept;

如果t_emp表内有6条记录，t_dept表内有7条记录，那么执行后的结果就会有 $6\times7=42$ 条记录。

规定了连接条件的表连接语句，就不会出现笛卡尔积

SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d
ON e.deptno=d.deptno;

注： ON后面为连接条件。

表连接的分类

表连接分为两种：内连接和外连接
内连接是结果集中只保留符合连接条件的记录
外连接是不管符不符合链接条件，记录都要保留在结果集中

内连接

内连接简介

内连接是最常见的一种表连接，用于查询多张关系表符合连接条件的记录。

用法：

SELECT ...... FROM 表1
[INNER] JOIN 表2 ON 条件
[INNER] JOIN 表3 ON 条件
......

注：这里[INNER]可以不写。

内连接的多种语法形式

(1) JOIN … ON …

SELECT ...... FROM表1 JOIN表2 ON 连接条件;

案例：

SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d
ON e.deptno=d.deptno;

(2) 将ON替换为WHERE

SELECT ...... FROM表1 JOIN表2 WHERE 连接条件;

案例：

SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d
WHERE e.deptno=d.deptno;

(3) 使用 , 替代JOIN

SELECT ...... FROM表1,表2 WHERE 连接条件;

案例：

SELECT e.empno,e.ename,d.dname
FROM t_emp e,t_dept d
WHERE e.deptno=d.deptno;

注： ON不能单独使用，必须要与JOIN配合才能使用。

内连接练习1

查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级（两个表不存在相同字段的情况）

SELECT e.empno,e.ename,d.dname,e.sal,e.job,s.grade
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

内连接的数据表不一定必须有同名字段，只要字段之间符合逻辑关系就可以。

内连接练习2

查询与SCOTT相同部门的员工都有谁？
(1) 方法1
首先查询SCOTT在哪个部门：

SELECT deptno
FROM t_emp WHERE ename="SCOTT";

拿上述查询的结果作为条件去查询符合条件的员工（排除SCOTT自己）：

SELECT ename
FROM t_emp
WHERE deptno=(SELECT deptno FROM t_emp WHERE ename="SCOTT")
AND ename!="SCOTT";

问题：如果遇到有几千条甚至上万条数据，那么每条数据都要执行一遍 deptno=(SELECT deptno FROM t_emp WHERE ename="SCOTT") ，这样效率会非常低。
(2) 方法2
使用员工表连接员工表进行查询：

SELECT e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno
WHERE e1.ename="SCOTT" AND e2.ename!="SCOTT";

相同的数据表也可以做连接！

内连接练习3

查询底薪超过公司平均底薪的员工信息？
思路：将员工平均薪资作为表与员工信息表做链接

SELECT e.empno,e.ename,e.sal
FROM t_emp e JOIN (SELECT AVG(sal) avg FROM t_emp) t 
ON e.sal>=t.avg;

注：聚合函数不能用在WHERE和ON查询子句内！

内连接练习4

查询RESEARCH部门的人数、最高底薪、最低底薪、平均底薪、平均工龄？

SELECT COUNT(*),MAX(e.sal),MIN(e.sal),AVG(e.sal),AVG(DATEDIFF(NOW(),e.hiredate)/365)
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="RESEARCH";

注：不能舍位，而要进位。
如需强制舍位，则需要使用 FLOOR() ；
如需强制进位，则需要使用 CELL() 。

内连接练习5

查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级？

SELECT e.job,MAX(e.sal+IFNULL(e.comm,0)),
MIN(e.sal+IFNULL(e.comm,0)),
AVG(e.sal+IFNULL(e.comm,0)),
MAX(s.grade),
MIN(s.grade)
FROM t_emp e JOIN t_salgrade s
ON (e.sal+IFNULL(e.comm,0)) BETWEEN s.losal AND s.hisal
GROUP BY e.job;

注： IFNULL函数是MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数；否则，IFNULL函数返回第二个参数。

内连接练习6

查询每个底薪超过部门平均底薪的员工信息？
思路：将每个部门的平均底薪查询出来并作为一张表再和员工表进行连接查询

SELECT e.empno,e.ename,e.sal
FROM t_emp e JOIN
(SELECT deptno,AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal>=t.avg;

外连接

外连接简介

为什么要使用外连接
如果小王是一名临时人员，没有固定的部门编制，那么我们想查询每名员工和他的部门名称，使用内连接就会漏掉小王，所以要引入外连接的语法才能解决这个问题。
外连接与内连接的区别在于，除了符合条件的记录之外，结果集中还会保留不符合条件的记录。

SELECT e.empno,e.ename,d.dname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno;

注：这里t_emp表在左侧，因此称为左表；t_dept在右侧，因此称为右表，而LEFT JOIN是保留左表所有的记录再去和右表进行连接，如果右表有符合和左表连接条件的记录，那么正常查询；反之，右表中没有和左表连接条件的记录，那么右表会使用NULL值去和左表连接。举例来说，小王为临时工，无部门，那么查询结果中小王的部门那一列就为NULL（如果使用内连接，那么不会显示小王这条记录）。

左外连接和右外连接

左外连接就是保留左表所有的记录，与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用NULL与左表连接，右外连接也是如此。
右外连接示例：

SELECT e.empno,e.ename,d.dname
FROM t_dept d RIGHT JOIN t_emp e ON e.deptno=d.deptno;

外连接练习1

查询每个部门的名称和部门的人数？

SELECT d.dname,COUNT(e.deptno)
FROM t_dept d LEFT JOIN t_emp e 
ON d.deptno=e.deptno
GROUP BY d.deptno;

注：如果将COUNT(e.deptno)改为COUNT(*)，那么如果这个部门没有人也会有1条记录，这是因为右表使用NULL与左表进行连接，因此产生了1条记录，而我们需要右表的记录，因此需要写成COUNT(e.deptno)。

外连接练习2

查询每个部门的名称和部门的人数？如果没有部门的员工，部门名称用NULL代替。

(SELECT d.dname,COUNT(e.deptno)
FROM t_dept d LEFT JOIN t_emp e 
ON d.deptno=e.deptno
GROUP BY d.deptno)
UNION
(SELECT d.dname,COUNT(*)
FROM t_dept d RIGHT JOIN t_emp e 
ON d.deptno=e.deptno
GROUP BY d.deptno);

注： UNION关键字可以将多个查询语句的结果进行合并（排除重复的内容）。
UNION连接的查询语句返回的字段名称和字段数量要相同！
UNION关键字具体使用方法：

(查询语句) UNION (查询语句) UNION (查询语句) ......

外连接练习3

查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、上司部门？

SELECT
	e.empno,e.ename,d.dname,
	e.sal+IFNULL(e.comm,0),s.grade,
	FLOOR(DATEDIFF(NOW(),e.hiredate)/365),
	t.empno AS mgrno,t.ename AS mgrname,t.dname AS mgrdname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
LEFT JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN
(SELECT
	e1.empno,e1.ename,d1.dname
FROM t_emp e1 JOIN t_dept d1 ON e1.deptno=d1.deptno) t
ON e.mgr=t.empno;

外连接的注意事项

内连接只保留符合条件的记录，所以查询条件写在ON子句和WHERE子句中的效果是相同的。但是在外连接里，条件写在WHERE子句里，不符合条件的记录是会被过滤掉的，而不是保留下来。
例如：

SELECT e.ename,d.deptno,d.dname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
AND e.deptno=10;

上述语句是要统计第10部门的员工，但是结果里NULL部门（也即无部门）的员工也会被统计进来，这不是我们想要的结果。
解决方法：使用WHERE关键字代替AND关键字。

SELECT e.ename,d.deptno,d.dname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
WHERE e.deptno=10;