【MySQL】多表查询

文章目录

- 1、单表查询
- 2、表结构
- - 2.1 一对多（多对一）
- 3、多表查询(一对多)
- - 3.1 内连接（where 、inner join）
  - 3.2 外连接（left join、right join）
  - 3.3 自连接(inner join)
  - 3.4 联合查询(union all 、union）
- 4.子查询
- - 4.1 标量子查询
  - 4.2 列子查询
  - 4.3 行子查询
  - 4.4 表子查询

1、单表查询

参考链接：【MySQL基础教程】DQL语句详细介绍------单表

2、表结构

2.1 一对多（多对一）

表：emp

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (`id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'ID',`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',`age` int(0) NULL DEFAULT NULL COMMENT '年龄',`job` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '职位',`salary` int(0) NULL DEFAULT NULL COMMENT '薪资',`entrydate` date NULL DEFAULT NULL COMMENT '入职时间',`managerid` int(0) NULL DEFAULT NULL COMMENT '直属领导ID',`dept_id` int(0) NULL DEFAULT NULL COMMENT '部门ID',PRIMARY KEY (`id`) USING BTREE,INDEX `fk_emp_dept_id`(`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5);
INSERT INTO `emp` VALUES (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1);
INSERT INTO `emp` VALUES (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1);
INSERT INTO `emp` VALUES (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1);
INSERT INTO `emp` VALUES (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1);
INSERT INTO `emp` VALUES (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);
INSERT INTO `emp` VALUES (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3);
INSERT INTO `emp` VALUES (8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3);
INSERT INTO `emp` VALUES (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3);
INSERT INTO `emp` VALUES (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2);
INSERT INTO `emp` VALUES (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2);
INSERT INTO `emp` VALUES (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, NULL);
INSERT INTO `emp` VALUES (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2);
INSERT INTO `emp` VALUES (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4);
INSERT INTO `emp` VALUES (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4);
INSERT INTO `emp` VALUES (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4);
INSERT INTO `emp` VALUES (17, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL);SET FOREIGN_KEY_CHECKS = 1;

表：dept

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (`id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'ID',`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门名称',PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '部门表' ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, '研发部');
INSERT INTO `dept` VALUES (2, '市场部');
INSERT INTO `dept` VALUES (3, '财务部');
INSERT INTO `dept` VALUES (4, '销售部');
INSERT INTO `dept` VALUES (5, '总经办');
INSERT INTO `dept` VALUES (6, '人事部');
INSERT INTO `dept` VALUES (7, '发明部');SET FOREIGN_KEY_CHECKS = 1;

3、多表查询(一对多)

案例: 部门与员工的关系
关系: 一个部门对应多个员工，一个员工对应一个部门
实现: 在多的一方建立外键，指向一的一方的主键

在这里插入图片描述

3.1 内连接（where 、inner join）

内连接查询的是两张表交集部分的数据（也就是绿色部分的数据）
在这里插入图片描述

内连接的语法分为两种: 隐式内连接、显式内连接。
隐式内连接:

SELECT  字段列表   FROM   表1 , 表2   WHERE   条件 ...

显式内连接:

SELECT  字段列表   FROM   表1  [ INNER ]  JOIN 表2  ON  连接条件 ...

对比：

语法：内连接的语法更加明确，使用JOIN关键字和ON子句来指定连接条件。而隐式内连接的语法相对隐晦，连接条件直接放在WHERE子句中。
可读性：内连接的语法更加清晰，能够更容易理解和阅读。而隐式内连接的语法相对复杂，可读性较差。
可扩展性：内连接语法更灵活，可以轻松添加更多的连接条件和连接表。而隐式内连接的语法限制了连接的表数目和条件的复杂性。

案例:

A.查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)表结构: emp , dept连接条件: emp.dept_id = dept.iselect e.name '姓名',d.name '部门'
from emp e , dept d 
where e.dept_id = d.id;(显式内连接实现)  --- INNER JOIN ... select e.name '姓名',d.name '部门'
from emp e INNER JOIN dept d 
on e.dept_id = d.id

查询结果：
在这里插入图片描述

3.2 外连接（left join、right join）

外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为

左外连接:
左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据
在这里插入图片描述

SELECT  字段列表   FROM   表1  LEFT  [ OUTER ]  JOIN 表2  ON  条件 ...

右外连接:
右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。
在这里插入图片描述

SELECT  字段列表   FROM   表1  RIGHT  [ OUTER ]  JOIN 表2  ON  条件 ...

案例1：查询emp表的所有数据, 和对应的部门信息

之所以查询emp表的所有数据和对应的部门信息不能用内连接，因为可能emp表的员工可能没有部门，那么在内查询的情况下，只能查到有部门的员工信息，而没有部门的员工就被忽略了

在这里插入图片描述

查询emp表的所有数据, 和对应的部门信息 由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。表结构: emp, dept连接条件: emp.dept_id = dept.id左外连接：select *from emp e left JOIN dept d on e.dept_id = d.id右外连接：select *from dept d right JOIN emp e on e.dept_id = d.id

无论是左连接还是右连接，（左或右）朝向谁，谁查的数据就多（全数据）

例如：左连接朝向左也就是emp表，就会把emp表的数据查全
在这里插入图片描述

也就是画黑框这部分：
在这里插入图片描述
查出来的数据：emp表的数据不管有没有部门都要查出来

在这里插入图片描述
至于查询结果哪个表排在左边，哪个表排在右边，取决外连接的顺序

案例2：查询dept表的所有数据, 和对应的员工信息

查询dept表的所有数据, 和对应的员工信息(右外连接)由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。表结构: emp, dept
连接条件: emp.dept_id = dept.id-- 右连接select *from dept d left JOIN emp e on e.dept_id = d.id-- 左连接select *from emp e right JOIN dept d on e.dept_id = d.id

查出来的数据：dept表的数据不管有没有员工都要查出来

在这里插入图片描述

注意事项：

左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

3.3 自连接(inner join)

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。
自连接的查询语法：

SELECT  字段列表   FROM   表A   别名A   JOIN  表A    别名B   ON  条件 ... ;

而对于自连接查询，可以是内连接查询，也可以是外连接查询。

案例1：查询员工及其所属领导的名字
在这里插入图片描述

A. 查询员工 及其 所属领导的名字
表结构: emp-- 隐式内连接SELECT e.name '老板',m.name '员工'FROM emp e ,emp mWHERE e.id = m.managerid-- 显示内连接SELECT e.name '老板',m.name '员工'FROM emp e  join emp mon e.id = m.managerid-- 左外连接SELECT m.name '员工',e.name '上级'FROM emp m left join emp eon e.id = m.managerid

在这里插入图片描述
注意事项:

在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

3.4 联合查询(union all 、union）

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT  字段列表   FROM   表A  ...  
UNION [ ALL ]SELECT  字段列表  FROM   表B  ....

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重

案例1: 将薪资低于 5000 的员工 , 和(注意是和，不是并且）年龄大于 50 岁的员工全部查询出来.

 A. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可。 那这里呢，我们也可以通过union/union all来联合查询.-- 多条件查询SELECT *FROM emp eWHERE e.salary < 5000 or e.age>50-- union/union all联合查询select * from emp where salary < 5000unionselect * from emp where age > 50;

如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all联合查询时，将会报错。
如：
在这里插入图片描述

4.子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

 SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 )

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

根据子查询结果不同，分为：
A. 标量子查询（子查询结果为单个值）
B. 列子查询(子查询结果为一列)
C. 行子查询(子查询结果为一行)
D. 表子查询(子查询结果为多行多列)
根据子查询位置，分为：
A. WHERE之后
B. FROM之后
C. SELECT之后

4.1 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=
案例:

 查询 "销售部" 的所有员工信息
完成这个需求时，我们可以将需求分解为两步：①. 查询 "销售部" 部门IDselect id from dept where name = '销售部';②. 根据 "销售部" 部门ID, 查询员工信息select * from emp where dept_id = (select id from dept where name = '销售部');

查询结果：
在这里插入图片描述

案例：

 查询在 "方东白" 入职之后的员工信息
完成这个需求时，我们可以将需求分解为两步：
①. 查询 方东白 的入职日期select entrydate from emp where name = '方东白';
②. 查询指定入职日期之后入职的员工信息select * from emp where entrydate > (select entrydate from emp where name = '方东白');

在这里插入图片描述

4.2 列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL
在这里插入图片描述
案例:

 子查询返回列表的所有值都必须满足查询 "销售部" 和 "市场部" 的所有员工信息
分解为以下两步: 
①. 查询 "销售部" 和 "市场部" 的部门IDselect id from dept where name = '销售部' or name = '市场部';②. 根据部门ID, 查询员工信息select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

在这里插入图片描述
案例：

查询比研发部其中任意一人工资高的员工信息分解为以下两步:①. 查询研发部所有人工资select salary from emp where dept_id = (select id from dept where name = '研发部');②. 比研发部其中任意一人工资高的员工信息select * from emp where salary > any ( select salary from emp where dept_id = 
(select id from dept where name = '研发部') );

注意关键词 任意一人
在这里插入图片描述

 查询比 财务部 所有人工资都高的员工信息分解为以下两步:①. 查询所有 财务部 人员工资select id from dept where name = '财务部';select salary from emp where dept_id = (select id from dept where name = '财务部');②. 比 财务部 所有人工资都高的员工信息select * from emp where salary > all ( select salary from emp where dept_id = 
(select id from dept where name = '财务部') );

注意关键词是工资比财务部都要高的员工信息
在这里插入图片描述

4.3 行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN

案例:

A. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
这个需求同样可以拆解为两步进行:①. 查询 "张无忌" 的薪资及直属领导select salary, managerid from emp where name = '张无忌';②. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');或者select * from emp e ,(select salary, managerid from emp where name = '张无忌') swhere e.salary = s.salary and e.managerid =s.managerid

注意关键词：查询与 “张无忌” 的薪资及直属领导相同的员工信息
薪资和领导都是相同的
在这里插入图片描述

注意：这个where 后面的列数据要与子查询一一对应，否则对应不上

4.4 表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN

案例:

 A. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
分解为两步执行:①. 查询 "鹿杖客" , "宋远桥" 的职位和薪资select job, salary from emp where name = '鹿杖客' or name = '宋远桥';②. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );或者select * from emp  e,(select job, salary from emp where name = '鹿杖客' or name = '宋远桥') dwhere (e.job = d.job and e.salary = d.salary)

在这里插入图片描述

 B. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
分解为两步执行:①. 入职日期是 "2006-01-01" 之后的员工信息select * from emp where entrydate > '2006-01-01';②. 查询这部分员工, 对应的部门信息;select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

在这里插入图片描述

练习案例：

			 create table salgrade(grade int,losal int,hisal int) comment '薪资等级表';insert into salgrade values (1,0,3000);insert into salgrade values (2,3001,5000);insert into salgrade values (3,5001,8000);insert into salgrade values (4,8001,10000);insert into salgrade values (5,10001,15000);insert into salgrade values (6,15001,20000);insert into salgrade values (7,20001,25000);insert into salgrade values (8,25001,30000);--   查询员工的姓名、年龄、职位、部门信息 （隐式内连接）SELECT e.name,e.age,e.job,d.name 'dept'FROM emp e, dept dWHERE e.dept_id = d.id--  查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）SELECT e.name,e.age,e.job,d.name 'dept'FROM emp e INNER JOIN dept dWHERE e.dept_id = d.id AND e.age <30-- 查询拥有员工的部门ID、部门名称(内连接）SELECT DISTINCT d.id,d.nameFROM emp e ,dept dWHERE e.dept_id = d.id-- 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)SELECT e.name,d.name,e.ageFROM emp e LEFT JOIN dept don e.dept_id = d.idwhere e.age > 40 -- 查询所有员工的工资等级SELECT e.name,s.gradeFROM emp e ,salgrade sWHERE e.salary>=s.losal AND e.salary<=s.hisal-- 查询 "研发部" 所有员工的信息及 工资等级SELECT e.*,s.gradeFROM(SELECT e.*,d.name deptFROM emp e,dept dWHERE e.dept_id = d.id and d.name = '研发部') as eleft JOIN(SELECT e.name,s.gradeFROM emp e ,salgrade sWHERE e.salary>=s.losal AND e.salary<=s.hisal) as son  e.name = s.name select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and ( e.salary between s.losal and s.hisal ) and d.name = '研发部';-- 查询 "研发部" 员工的平均工资SELECT AVG(e.salary)FROM emp e ,dept dWHERE d.id  = e.dept_id and d.name ='研发部'-- 查询工资比 "灭绝" 高的员工信息。SELECT e.salaryFROM emp e WHERE e.name ='灭绝'SELECT e.*FROM emp e WHERE e.salary > (SELECT e.salaryFROM emp e WHERE e.name ='灭绝')-- 查询比平均薪资高的员工信息SELECT AVG(e.salary)FROM emp e SELECT e.*FROM emp e WHERE e.salary > (SELECT AVG(e.salary)FROM emp e )-- 查询低于本部门平均工资的员工信息select avg(e1.salary) from emp e1 where e1.dept_id = 1;select avg(e1.salary) from emp e1 where e1.dept_id = 2;select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id )-- 查询所有的部门信息, 并统计部门的员工人数SELECT d.* ,COUNT(e.name)FROM dept d LEFT JOIN emp e ON d.id = e.dept_idGROUP BY d.id;