0


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;
  1. 右上图
#右外连接
SELECT * FROM
emp e RIGHT JOIN dept d ON e.deptno=d.deptno;
  1. 左中图
#左外连接 去掉符合条件的
SELECT * FROM
emp e LEFT JOIN dept d ON e.deptno=d.deptno
WHERE e.deptno IS NULL;
  1. 右中图
#右外连接 去掉符合条件的
SELECT * FROM
emp e RIGHT JOIN dept d ON e.deptno=d.deptno
WHERE e.deptno IS NULL;
  1. 左下图
# 使用右上图和左中图实现全外连接
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;
  1. 右下图
#使用左中图和右中图实现左右外连接
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;                # 薪资排序

本文转载自: https://blog.csdn.net/jj6666djdbbd/article/details/126694771
版权归原作者 小萝卜爱吃兔子` 所有, 如有侵权,请联系我们删除。

“day04 MYSQL多表查询操作”的评论:

还没有评论