05.多表查询

1. 多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多（多对一）
多对多
一对一

1.1 一对多

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

1.2 多对多

案例: 学生与课程的关系
关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

-- 建表SQL
DROP TABLE IF EXISTS `student`;
CREATE TABLE student(
    `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `name` VARCHAR(10) COMMENT '姓名',
    `no` VARCHAR(10) COMMENT '学号'
) COMMENT '学生表';
INSERT INTO student VALUES (NULL, '黛绮丝', '2000100101'),(NULL, '谢逊','2000100102'),(NULL, '殷天正', '2000100103'),(NULL, '韦一笑', '2000100104');

DROP TABLE IF EXISTS `course`;
CREATE TABLE course(
    `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `name` VARCHAR(10) COMMENT '课程名称'
) COMMENT '课程表';
INSERT INTO course VALUES (NULL, 'JAVA'), (NULL, 'PHP'), (NULL , 'MYSQL') ,(NULL, 'HADOOP');

DROP TABLE IF EXISTS `student_course`;
CREATE TABLE student_course(
    `id` INT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
    `student` INT NOT NULL COMMENT '学生ID',
    `courseid` INT NOT NULL COMMENT '课程ID',
    CONSTRAINT fk_course_id FOREIGN KEY (courseid) REFERENCES course (id),
    CONSTRAINT fk_student_id FOREIGN KEY (studentid) REFERENCES student (id)
)COMMENT '学生课程中间表';
INSERT INTO student_course VALUES (NULL,1,1),(NULL,1,2),(NULL,1,3),(NULL,2,2),(NULL,2,3),(NULL,3,4);

1.3 一对多

案例: 用户与用户详情的关系
关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

-- 建表SQL
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE tb_user(
    `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `name` VARCHAR(10) COMMENT '姓名',
    `age` INT COMMENT '年龄',
    `gender` CHAR(1) COMMENT '1: 男 , 2: 女',
    `phone` CHAR(11) COMMENT '手机号'
) COMMENT '用户基本信息表';
INSERT INTO tb_user(id, name, age, gender, phone)
VALUES
    (NULL,'黄渤',45,'1','18800001111'),
    (NULL,'冰冰',35,'2','18800002222'),
    (NULL,'码云',55,'1','18800008888'),
    (NULL,'李彦宏',50,'1','18800009999')
;

DROP TABLE IF EXISTS `tb_user_edu`;
CREATE TABLE tb_user_edu(
    `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    `degree` VARCHAR(20) COMMENT '学历',
    `major` VARCHAR(50) COMMENT '专业',
    `primaryschool` VARCHAR(50) COMMENT '小学',
    `middleschool` VARCHAR(50) COMMENT '中学',
    `university` VARCHAR(50) COMMENT '大学',
    `userid` INT UNIQUE COMMENT '用户ID',
    CONSTRAINT fk_userid FOREIGN KEY (userid) REFERENCES tb_user(id)
) COMMENT '用户教育信息表';
INSERT INTO tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid)
VALUES
    (NULL,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
    (NULL,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
    (NULL,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
    (NULL,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4)
;

3. 多表查询概述

3.1 数据准备

-- 建表SQL
DROP TABLE IF EXISTS `dept`;
CREATE TABLE dept(
    `id` INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL COMMENT '部门名称'
)COMMENT '部门表';
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办'), (6, '人事部');

DROP TABLE IF EXISTS `emp`;
CREATE TABLE emp(
    `id` INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL COMMENT '姓名',
    `age` INT COMMENT '年龄',
    `job` VARCHAR(20) COMMENT '职位',
    `salary` INT COMMENT '薪资',
    `entrydate` DATE COMMENT '入职时间',
    `managerid` INT COMMENT '直属领导ID',
    `dept_id` INT COMMENT '部门ID'
)COMMENT '员工表';

-- 添加外键
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES
dept(id);

INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES
    (1, '金庸', 66, '总裁',20000, '2000-01-01', NULL,5),
    (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
    (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
    (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
    (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
    (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
    (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
    (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
    (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
    (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
    (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
    (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
    (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
    (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
    (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
    (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
    (17, '陈友谅', 42, NULL,2000, '2011-10-12', 1,NULL)
;

3.2 概述

多表查询就是指从多张表中查询数据，原来查询单表数据，执行的SQL形式为：select * from emp，要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept; 具体的执行结果如下:

此时，可以看到查询结果中包含了大量的结果集，总共102条记录，而这其实就是员工表emp所有的记录(17) 与部门表dept所有记录(6) 的所有组合情况，这种现象称之为笛卡尔积。

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合和 B集合的所有组合情况。

而在多表查询中，是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。

在SQL语句中，去除无效的笛卡尔积可以给多表查询加上连接查询的条件。
SELECT * FROM emp, dept WHERE emp.dept_id = dept.id;
而由于id为17的员工，没有dept_id字段值，所以在多表查询时，根据连接查询的条件并没有查询到。

3.3 分类

连接查询
- 内连接：相当于查询A、B交集部分数据
- 外连接：
  - 左外连接：查询左表所有数据，以及两张表交集部分数据
  - 右外连接：查询右表所有数据，以及两张表交集部分数据
- 自连接：当前表与自身的连接查询，自连接必须使用表别名子查询
子查询

4. 内连接

内连接查询的是两张表交集部分的数据。
内连接的语法分为两种: 隐式内连接、显式内连接。

语法：

-- 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ... ;

-- 显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
-- 一般查询
SELECT emp.name,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;

-- 别名查询
SELECT e.name,d.name FROM emp e,dept d WHERE e.dept_id = d.id;
查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)
-- 一般查询
SELECT emp.name,dept.name FROM emp INNER JOIN dept ON emp.dept_id = dept.id;

-- 别名查询
SELECT e.name,d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id;

一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

5. 外连接

外连接分为两种

左外连接
右外连接

语法：

左外连接：SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据
右外连接：SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接相当于查询表1(右表)的所有数据，当然也包含表1和表2交集部分的数据

查询emp表的所有数据, 和对应的部门信息
SELECT e.*,d.name FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
查询dept表的所有数据, 和对应的员工信息
SELECT e.*,d.* FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;

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

6. 自连接

6.1 自连接查询

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

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

语法：

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

查询员工及其所属领导的名字
SELECT e.name,m.name  FROM emp e INNER JOIN emp m ON e.managerid = m.id;
查询所有员工emp及其领导的名字emp , 如果员工没有领导, 也需要查询出来
SELECT e.name,m.name FROM emp e LEFT OUTER JOIN emp m ON e.managerid = m.id;

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

6.2 联合查询

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

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

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

UNION ALL会将全部的数据直接合并在一起，UNION 会对合并之后的数据去重。

查询薪资低于5000的员工和年龄大于50岁的员工（全部结果）
SELECT * FROM emp WHERE salary < 5000 UNION ALL SELECT * FROM emp WHERE age > 50;
union all查询出来的结果，仅仅进行简单的合并，并未去重。
SELECT * FROM emp WHERE salary < 5000 UNION SELECT * FROM emp WHERE age > 50;
union 联合查询，会对查询出来的结果进行去重处理。

注意：
如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all联合查询时，将会报错。

7. 子查询

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

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

根据子查询结果不同，分为：
- 标量子查询（子查询结果为单个值）
- 列子查询（子查询结果为一列）
- 行子查询（子查询结果为一行）
- 表子查询（子查询结果为多行多列）

7.1 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

常用的操作符：= < > > >= < <=

查询 "销售部" 的所有员工信息

完成这个需求时，将需求分解为两步：
查询 "销售部" 部门ID
SELECT `id` FROM dept WHERE `name` = "销售部";
根据 "销售部" 部门ID, 查询员工信息
SELECT * FROM emp WHERE `dept_id` = 查询的部门ID;
合并查询
SELECT * FROM emp WHERE `dept_id` = (SELECT `id` FROM dept WHERE `name` = "销售部");

查询在 "方东白" 入职之后的员工信息
SELECT * FROM emp WHERE entrydate < (SELECT entrydate FROM emp WHERE `name` = "方东白");

7.2 列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

操作符	描述
`IN`	在指定的集合范围之内，多选一
`NOT IN`	不在指定的集合范围之内
`ANY`	子查询返回列表中，有任意一个满足即可
`SOME`	与`ANY`等同，使用`SOME`的地方都可以使用`ANY`
`ALL`	子查询返回列表的所有值都必须满足

查询 "销售部" 和 "市场部" 的所有员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT `id` FROM dept WHERE `name` IN ("销售部", "市场部"));
查询比财务部所有人工资都高的员工信息
SELECT * FROM emp WHERE salary > ALL (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` = "研发部"));

7.3 行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

查询与 "张无忌" 的薪资及直属领导相同的员工信息
查询 "张无忌" 的薪资及直属领导
SELECT e.salary,m.`name` FROM emp e INNER JOIN emp m ON e.managerid = m.id WHERE e.`name` = "张无忌";
查询与 "张无忌" 的薪资及直属领导相同的员工信息
SELECT * FROM emp WHERE (salary, managerid) = (查询到的薪资，查询刀的直属领导ID);
合并
SELECT * FROM emp WHERE (salary, managerid) = (SELECT salary,managerid FROM emp WHERE `name` = "张无忌");

7.4 表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符：IN

查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
SELECT * FROM emp WHERE (job,salary) IN (SELECT job,salary FROM emp WHERE `name` IN ("鹿杖客", "宋远桥"));
查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
SELECT e.`name`,e.entrydate,d.`name` FROM (SELECT * FROM emp WHERE entrydate > "2006-01-01") e LEFT JOIN dept d ON e.dept_id = d.id;

8. 实操

环境准备

SET foreign_key_checks = 0;  -- 临时关闭外键约束
DROP TABLE IF EXISTS `dept`;
CREATE TABLE dept(
    `id` INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL COMMENT '部门名称'
)COMMENT '部门表';
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办'), (6, '人事部');

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

DROP TABLE IF EXISTS `salgrade`;
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);

SET foreign_key_checks = 1;  -- 开启外键约束

查询员工的姓名、年龄、职位、部门信息（隐式内连接）

SELECT e.`name` AS "姓名",e.age AS "年龄",e.job AS "职位", d.`name` AS "部门" FROM emp e, dept d WHERE e.dept_id = d.id;

查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）

SELECT e.`name` AS "姓名",e.age AS "年龄",e.job AS "职位", d.`name` AS "部门" FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.age < 30;

查询拥有员工的部门ID、部门名称

SELECT e.*, d.`name` AS "部门" FROM emp e, dept d WHERE e.dept_id = d.id;

查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)
```
SELECT e.age AS "年龄", d.`name` AS "部门" FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.age > 40;
```

查询所有员工的工资等级

SELECT e.`name` AS "姓名", e.salary AS "工资", s.grade AS "等级" FROM emp e, salgrade s WHERE e.salary BETWEEN s.losal AND s.hisal;

查询 "研发部" 所有员工的信息及工资等级

SELECT e.`name` AS "姓名", e.salary AS "工资", s.grade AS "等级", d.`name` AS "部门" FROM emp e, salgrade s, dept d WHERE (e.salary BETWEEN s.losal AND s.hisal) AND (d.`name` = "研发部");

查询 "研发部" 员工的平均工资

SELECT d.`name` AS "部门", AVG(e.salary) AS "平均工资" FROM emp e, dept d WHERE e.dept_id = d.id AND d.`name` = "研发部";

查询工资比 "灭绝" 高的员工信息

SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE `name` = "灭绝");

查询比平均薪资高的员工信息

SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

查询低于本部门平均工资的员工信息

SELECT e.* FROM emp e WHERE salary < (SELECT AVG(m.salary) FROM emp m WHERE m.dept_id = e.dept_id);

查询所有的部门信息, 并统计部门的员工人数

SELECT d.`name` AS "部门",(SELECT COUNT(*) FROM emp e WHERE e.dept_id = d.id) AS "总人数" FROM dept d;

posted @ 2023-03-23 00:15 ChanceySolo 阅读(22) 评论(0) 编辑收藏举报

刷新页面返回顶部

素心

人生苦短，我用Python。