day04 MYSQL多表查询操作

多表关系

一对多(多对一)

案例：部门与员工的关系

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，关联另一方的主键

多对多

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

案例：用户与用户详细的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。

实现：在任意一方加入一个外键，关联另一方的主键，并且设置外键为唯一约束

多表查询

前面我们使用的查询，只是对单表进行查询，在具体的应用中，经常需要实现在一个查询语句中显示多张数据表的数据，这就是所谓的多表联合查询。

在具体实现连接操作时，首先将两个或两个以上的表按照某个条件连接起来，然后再查询到所要求的数据记录。

连接查询分为交叉连接、内连接、外连接查询三种方式。

交叉连接

交叉连接不带WHERE子句，它返回被连接的两个表所有数据行的笛卡尔积

• 查询员工及员工所在的部门信息（笛卡尔积）

SELECT * FROM emp,dept;　
SELECT * FROM emp CROSS JOIN dept;　

如示例所示，直接插入两个表进行查询，会导致一个表中的一条数据，对应另一个表中的所有数据。
如果不加条件直接进行查询，则数据条数是两个表记录条数的乘积，这种结果我们称之为 笛卡尔乘积。

笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积

如果两张表的数据量都很大，那么这种庞大时很可怕的，所以现在必须想办法消除掉笛卡尔积的无效记录。

想要消除笛卡尔积的无效记录，需要使用关联字段。

范例：利用等值条件来处理笛卡尔积

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

如图所示，我们使用等值条件WHERE指定了emp的deptno和dept的deptno一致，所以第一个表的数据只对应一条另一个表的数据。

内连接

在表关系的笛卡尔积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。按匹配的条件可以分成等值连接和不等值连接。

有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行。（所谓的链接表就是数据库在做查询形成的中间表）。

• 隐式内连接

SELECT * FROM 表1，表2 WHERE 条件;

• 显式内连接（使用关键字INNER JOIN）

SELECT * FROM 表1 [INNER] JOIN 表2 ON 条件; 

等值连接

在连接条件中使用等于号(=)运算符比较被连接列的列值，

• 查询员工及员工部门信息

#隐式内连接
SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;
#显式内连接
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

INNER可以省略

• 等值连接可以使用USING来自动关联两表中相同的列

SELECT * FROM emp INNER JOIN dept USING(deptno);

非等值连接

在连接条件使用除等于运算符以外的其它比较运算符比较被连接的 列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>等。

• 查询员工工资级别

#隐式连接
SELECT * FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
#显式连接
SELECT * FROM emp e INNER JOIN salgrade s ON  e.sal BETWEEN s.losal AND s.hisal;

外连接

在表关系的笛卡尔积中，不仅保留表关系中所有匹配的数据记录，而且还保留部分不匹配的记录。按照保留不匹配条件数据记录来源可以分为
左外连接(LEFT OUTER JOIN)、
右外连接(RIGHT OUTER JOIN)
全外连接(FULL OUTER JOIN)。

外连接使用语法如下：

SELECT * FROM 表1 LEFT|RIGHT|FULL [OUTER] JOIN 表2 ON 条件; 

左外连接

在表关系的笛卡尔积中，出了选择相匹配的数据记录，还包含关联左边表中不匹配的数据记录。

插入一条部门编号为NULL的数据(关于插入后面会讲)

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES(999,'ylh','学生',7369,CURDATE(),5000,-500,NULL);

查询员工及对应的部门信息（没有部门的员工也显示出来，没有员工的部门不显示）

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

左外连接：如果人没有deptno，则显示前面emp，不显示后面dept表的内容，即显示左边，不显示右边。

右外连接

在表关系的笛卡尔积中，出了选择相匹配的数据记录，还包含关联右边表中不匹配的数据记录。

查询员工及对应的部门信息（没有部门的员工不显示，没有员工的部门显示）

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

右外连接：如果没有对应的数据，则只显示右边，不显示左边。

全连接

在表关系的笛卡尔积中，出了选择相匹配的数据记录，还包含关联左右两边表中不匹配的数据记录。

查询员工及对应的部门信息（没有部门的员工显示，没有员工的部门显示）

#mysql不支持全连接，但是可以用过集合运算来实现
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

自连接

自连接就是指表与其自身进行连接

查询每个员工对应的领导姓名。

SELECT e.ename,e.mgr,me.ename 领导 FROM emp e,emp me WHERE e.mgr=me.empno;

自连接：把一个表分成两个表，可以对自身进行操作。

七种JOINS实现

1. 左上图

#左外连接
SELECT * FROM 
emp e LEFT JOIN dept d ON e.deptno=d.deptno;

2. 右上图

#右外连接
SELECT * FROM
emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

3. 左中图

#左外连接 去掉符合条件的
SELECT * FROM
emp e LEFT JOIN dept d ON e.deptno=d.deptno
WHERE e.deptno IS NULL;

4. 右中图

#右外连接 去掉符合条件的
SELECT * FROM
emp e RIGHT JOIN dept d ON e.deptno=d.deptno
WHERE e.deptno IS NULL;

5. 左下图

# 使用右上图和左中图实现全外连接
SELECT * FROM
emp RIGHT JOIN dept ON emp.deptno=dept.deptno
UNION 		# 求并集
SELECT * FROM
emp LEFT JOIN dept ON emp.deptno=dept.deptno
WHERE emp.deptno IS NULL;

6. 右下图

#使用左中图和右中图实现左右外连接
SELECt * FROM 
emp LEFT JOIN dept ON emp.deptno=dept.deptno
WHERE emp.deptno IS NULL
UNION
SELECT * FROM
emp RIGHT JOIN dept ON emp.deptno=dept.deptno
WHERE emp.deptno IS NULL;

集合运算

MySQL支持并集运算。

并集即两个集合所有部分（即上图最中间的全封闭的图）

UNION DISTINCT

相同的行在结果中只出现一次。
示例： A={1，2，3，4}    	B={3，4，5，6}
 C={1，2，3，4，5，6}
会删除重复行

SELECT * FROM emp
UNION DISTINCT
SELECT * FROM emp WHERE deptno=10;

UNION ALL

相同的行在结果中会出现多次。
示例： A={1，2，3，4}    	B={3，4，5，6}
 C={1，2，3，3，4，4，5，6}
不会删除重复行

SELECT * FROM emp
UNION ALL
SELECT * FROM emp WHERE deptno=10;

多表查询综合项目练习

1. 查询出雇佣日期在1981年的所有员工的编号、姓名、雇佣日期、工作、领导姓名、雇佣月工资、雇佣年工资(基本工资+奖金)，工资等级、部门编号、部门名称、部门位置，并且求这些员工的月基本工资在1500~3500之间，将最后的结果按照年工资的降序排列，如果年工资相等，则按照工作进行排序。

需求很多，逐步分析

• 确定所需要的数据表
  • emp e：编号、姓名、雇佣日期，工作、月工资、年薪
  • emp m：领导姓名
  • dept d：部门编号、名称、位置
  • salgrade s：工资等级
• 确定一致的关联字段
  • 员工和领导：e.mgr=m.empno
  • 员工和部门：e.deptno=d.deptno
  • 员工和工资等级：e.sal BETWEEN s.losal AND s.hisal

步骤一：查询出所有在1981年雇佣的雇员编号、姓名、御用日期、工作、月工资、年工资，并且月薪在1500~3500之间。只需要emp单张表即可。

SELECT e.empno,e.ename,e.hiredate,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪
FROM emp e
WHERE DATE_FORMAT(e.hiredate,'%Y')='1981' AND e.sal BETWEEN  1500 AND 3500;

步骤二：加入领导信息，使用自身关联。

SELECT e.empno,e.ename,e.hiredate,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪,m.ename 领导
FROM emp e,emp m
WHERE DATE_FORMAT(e.hiredate,'%Y')='1981' 
AND e.sal BETWEEN  1500 AND 3500
AND e.mgr=m.empno;

步骤三：加入部门信息。

SELECT e.empno,e.ename,e.hiredate,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪,m.ename 领导,d.deptno,d.dname,d.loc
FROM emp e,emp m,dept d
WHERE DATE_FORMAT(e.hiredate,'%Y')='1981' 	# 年份限定
AND e.sal BETWEEN  1500 AND 3500			# 薪资限定
AND e.mgr=m.empno 							# 领导确定
AND e.deptno=d.deptno;						# 部门确定

步骤四：加入工资等级

SELECT e.empno,e.ename,e.hiredate,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪,m.ename 领导,d.deptno,d.dname,d.loc,
s.grade
FROM emp e,emp m,dept d,salgrade s
WHERE DATE_FORMAT(e.hiredate,'%Y')='1981'	# 年份限定
AND e.sal BETWEEN  1500 AND 3500			# 薪资限定
AND e.mgr=m.empno							# 领导确定
 AND e.deptno=d.deptno						# 部门确定
AND e.sal BETWEEN s.losal AND s.hisal;		# 工资评级

步骤五：排序

SELECT e.empno,e.ename,e.hiredate,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪,m.ename 领导,d.deptno,d.dname,d.loc,
s.grade
FROM emp e,emp m,dept d,salgrade s
WHERE DATE_FORMAT(e.hiredate,'%Y')='1981' 	# 年份限定
AND e.sal BETWEEN  1500 AND 3500			# 薪资限定
AND e.mgr=m.empno 							# 领导确定
AND e.deptno=d.deptno						# 部门确定
AND e.sal BETWEEN s.losal AND s.hisal		# 工资评级
ORDER BY 年薪 DESC,e.job ASC;				# 薪资排序