MySQL 多表查询

多表关系

在实际开发中，每个表之间都存在一些关系，基本上分为三种：一对一、一对多(多对一)、多对多。

一对一

一对一关系，比如：学生表和档案表，对于学生来说，一个学生只能拥有一份档案，而档案只能属于一个学生，这就是一对一。

▼
sql
复制代码
CREATE TABLE student (
    id   INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名'
) COMMENT '学生表';

CREATE TABLE profile (
    id   INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    phone CHAR(11) UNIQUE COMMENT '电话号',
    address VARCHAR(100) COMMENT '家庭住址',
    -- 一份档案之能属于一个学生，所以必须添加 UNIQUE 关键字
    student_id INT UNIQUE COMMENT '学生ID',
    CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES student (id)
) COMMENT '档案表';

一对多(多对一)

一对多关系，比如：学生表和班级表，对于学生来说，一个学生只能属于一个班级，而班级可以有多个学生，这就是一对多。反过来站在班级这个角度看，一个班级可以有多个学生，但一个学生只能属于一个班级，这就是多对一。

在建表时，需要在多的这一方添加外键，用来保存一的这一方的主键。

▼
sql
复制代码
-- 学生表(多)
-- student: id, name, class_id

-- 班级表(一)
-- class: id, name

▼
sql
复制代码
CREATE TABLE student
(
    id       INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name     VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名',
    class_id INT COMMENT '班级ID'，
    -- 添加外键
    CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES class (id)
) COMMENT '学生表';

CREATE TABLE class
(
    id   INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(10) UNIQUE NOT NULL COMMENT '班级名'
) COMMENT '班级表';

多对多

多对多关系：比如：老师表和班级表，对于老师来说，一个老师可以教多个班级，而班级也可以有多个老师，这就是多对多。

在建表时，需要额外创建一张中间表，该表拥有自己的主键，和这两张表的外键，用来保存两方的主键。

▼
sql
复制代码
-- 教师表(多)
-- teacher: id, name

-- 班级表(多)
-- class: id, name

-- 教师班级中间表
-- teacher_class: id, teacher_id, class_id

▼
sql
复制代码
CREATE TABLE teacher
(
    id   INT PRIMARY KEY AUTO_INCREMENT COMMENT '教师ID',
    name VARCHAR(10) COMMENT '教师姓名'
) COMMENT '教师表';

CREATE TABLE class
(
    id   INT PRIMARY KEY AUTO_INCREMENT COMMENT '班级ID',
    name VARCHAR(10) COMMENT '班级名'
) COMMENT '班级表';

INSERT INTO teacher(name)
VALUES ('张老师'),
       ('李老师'),
       ('王老师'),
       ('赵老师'),
       ('周老师');

INSERT INTO class(name)
VALUES ('一班'),
       ('二班'),
       ('三班'),
       ('四班'),
       ('五班');

CREATE TABLE teacher_class
(
    id         INT PRIMARY KEY AUTO_INCREMENT COMMENT '中间表ID',
    teacher_id INT COMMENT '教师ID',
    class_id   INT COMMENT '班级ID',
    CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher (id),
    CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES class (id)
) COMMENT '教师班级中间表';

INSERT INTO teacher_class(teacher_id, class_id)
VALUES (1, 1),
       (2, 1),
       (3, 2),
       (4, 2),
       (5, 3),
       (1, 3),
       (2, 4);

多表查询

直接查询，默认做笛卡尔积，会展示出两个表中所有数据的组合。

SELECT * FROM student, class;
根据外键查询，消除无效的数据

SELECT * FROM student WHERE student.class_id = class.id;

▼
sql
复制代码
CREATE TABLE class
(
    id   INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(10) UNIQUE NOT NULL COMMENT '班级名'
) COMMENT '班级表';

CREATE TABLE student
(
    id       INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name     VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名',
    class_id INT COMMENT '班级ID',
    -- 添加外键
    CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES class (id)
) COMMENT '学生表';

INSERT INTO class(name)
VALUES ('一班'),
       ('二班'),
       ('三班'),
       ('四班'),
       ('五班');

INSERT INTO student(name, class_id)
VALUES ('李白飞', 1),
       ('孔勇', 2),
       ('韩芬怡', 3),
       ('薛云', 4),
       ('阎菊媛', 5),
       ('郝凤嘉', 1),
       ('蔡彪坚', 2),
       ('史世', 3),
       ('杜德', 4),
       ('余东', 5);

-- 直接查询两个表的数据，不加任何条件，默认做笛卡尔积，五个班级，10个人，一共 5 * 10 = 50 条数据
SELECT *
FROM student,
     class;

-- 使用外键，消除无效的数据
SELECT *
FROM student,
     class
WHERE student.class_id = class.id;

连接查询

连接查询：查询两个表共有的数据

连接类型	作用
内连接	查询两个表共有的数据
左外连接	查询左表所有数据，右表有数据则显示，没有则显示 NULL
右外连接	查询右表所有数据，左表有数据则显示，没有则显示 NULL
自连接	查询自己表中的数据，并且和自身进行比较

内连接

内连接：查询两个表共有的数据

隐式连接

SELECT 字段列表 FROM 表1, 表2 WHERE 连接条件/筛选条件;

显式连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 筛选条件 WHERE 筛选条件;

内连接示例

▼
sql
复制代码
-- 1. 查询所有学生的姓名和班级名称
-- 隐式连接
SELECT student.name, class.name
FROM student,
     class
WHERE student.class_id = class.id;

-- 显式连接
SELECT student.name, class.name
FROM student
INNER JOIN class ON student.class_id = class.id;

外连接

左外连接

左外连接：查询左表所有数据，右表有数据则显示，没有则显示 NULL

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 筛选条件 WHERE 筛选条件;

右外连接

右外连接：查询右表所有数据，左表有数据则显示，没有则显示 NULL

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 筛选条件 WHERE 筛选条件;

外连接示例

▼
sql
复制代码
INSERT INTO class(name)
VALUES ('一班'),
       ('二班'),
       ('三班'),
       ('四班'),
       ('五班'),
       ('六班'),
       ('七班')


INSERT INTO student(name, class_id)
VALUES ('李白飞', 1),
       ('孔勇', 2),
       ('韩芬怡', 3),
       ('薛云', 4),
       ('阎菊媛', 5),
       ('郝凤嘉', 1),
       ('蔡彪坚', 2),
       ('史世', NULL),
       ('杜德', 4),
       ('余东', NULL);

-- 1. 使用内连接查询
-- 因为 史世 和 余东 的 class_id 为 NULL，所以使用 内连接 查询时，他们俩人的信息并不会被检索出来
SELECT *
FROM student
         JOIN class ON class_id = class.id;

-- 2. 使用外连接查询
-- 左外连接会查询出 主表 的全部数据
-- 若右表有满足条件的数据则显示，没有则显示 NULL
-- 因此 史世 和 余东 两个人会查出，但是他们的 class_id、class.id、class.name 都是 NULL
SELECT *
FROM student
         LEFT JOIN class ON class_id = class.id;

-- 右外连接会查询出 右表 的全部数据
-- 若主表有满足条件的数据则显示，没有则显示 NULL
SELECT *
FROM student
         RIGHT JOIN class ON class_id = class.id;

-- 右连接可以改成左连接
SELECT *
FROM class
         LEFT JOIN student ON class_id = class.id;

自连接

自连接：查询自己表中的数据，并且和自身进行比较

SELECT 字段列表 FROM 表1 AS 别名1 JOIN 表1 AS 别名2 ON 筛选条件 WHERE 筛选条件;

▼
sql
复制代码
CREATE TABLE employee
(
    id        INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name      VARCHAR(10) UNIQUE COMMENT '姓名',
    leader_id INT COMMENT '领导ID'
) COMMENT '员工表';

INSERT INTO employee(name, leader_id)
VALUES ('李白飞', 8),
       ('孔勇', 10),
       ('韩芬怡', 8),
       ('薛云', 10),
       ('阎菊媛', 8),
       ('郝凤嘉', 10),
       ('蔡彪坚', 8),
       ('史世', NULL),
       ('杜德', 10),
       ('余东', NULL);

-- 1. 查询全部员工和他对应的 leader 名
SELECT e1.name, e2.name
FROM employee e1
         LEFT JOIN employee e2 ON e1.leader_id = e2.id;

联合查询

联合查询：将多次查询的结果进行合并，关键字：UNION 、 UNION ALL

查询语句1 UNION [ALL] 查询语句2;

查询语句1 和查询语句2 的结果集必须相同，即字段个数相同，字段类型相同。

▼
sql
复制代码
-- 1. 查询出 年龄 低于 24 的员工
SELECT *
FROM employee
WHERE age < 24;

-- 2. 查询 薪资 低于 5000 的员工
SELECT *
FROM employee
WHERE salary < 5000;

-- 3. 查询出 年龄 低于 24 和 薪资 低于 5000 的员工
-- UNION ALL 将两次查询结果直接合并会有重复的部分
SELECT *
FROM employee
WHERE age < 24
UNION ALL
SELECT *
FROM employee
WHERE salary < 5000;

-- UNION 将两次查询结果合并后去重
SELECT *
FROM employee
WHERE age < 24
UNION
SELECT *
FROM employee
WHERE salary < 5000;

子查询

子查询：嵌套查询，嵌套查询的查询结果作为外查询的筛选条件

根据子查询的结果，可将子查询分为三类：

标量子查询：即子查询的返回结果为一个值
列子查询：即子查询的返回结果为一列
行子查询：即子查询的返回结果为一行
表子查询：即子查询的返回结果为一个表（多行多列）

标量子查询

▼
sql
复制代码
-- 1. 查询 一班 的全部学生
SELECT *
FROM student
WHERE class_id = (SELECT id FROM class WHERE class.name = '一班');

-- 2. 查询比平均薪资低的所有员工
SELECT *
FROM employee
WHERE salary < (SELECT AVG(salary) FROM employee);

列子查询

操作符	描述
IN	IN 运算符用于判断某个值是否在指定的列表中。
NOT IN	NOT IN 运算符用于判断某个值是否不在指定的列表中。
ANY	ANY 运算符用于判断某个值是否满足给定条件的任意一个值。
SOME	SOME 运算符用于判断某个值是否满足给定条件的任意一个值。
ALL	ALL 运算符用于判断某个值是否满足给定条件的所有值。

▼
sql
复制代码
-- 1. 查询 市场部 和 营业部 的全体员工
SELECT *
FROM employee
WHERE department_id IN (SELECT id FROM department WHERE department.name IN ('市场部', '营业部'));


-- 2. 查询出薪资比 技术部 全体员工都高的员工
SELECT *
FROM employee
WHERE salary > ALL (SELECT salary
                    FROM employee
                    WHERE department_id IN (SELECT department.id FROM department WHERE department.name = '技术部'));

-- 2. 查询出薪资比 技术部 任意员工高的员工
SELECT *
FROM employee
WHERE salary > SOME (SELECT salary
                    FROM employee
                    WHERE department_id IN (SELECT department.id FROM department WHERE department.name = '技术部'));

-- ANY 效果同 SOME
SELECT *
FROM employee
WHERE salary > ANY (SELECT salary
                    FROM employee
                    WHERE department_id IN (SELECT department.id FROM department WHERE department.name = '技术部'));

行子查询

▼
sql
复制代码
-- 1.查询出和 李白飞 相同领导，相同薪资的员工
SELECT *
FROM employee
WHERE (salary, leader_id) = (SELECT salary, leader_id FROM employee WHERE name = '李白飞');

表子查询

▼
sql
复制代码
-- 1. 查询出与 薛云 和 韩芬怡 两个人相同部门，相同薪资的员工
SELECT *
FROM employee
WHERE (salary, department_id) IN (SELECT salary, department_id FROM employee WHERE name = '薛云' OR name = '韩芬怡');

-- 2. 查询入职时间在 2025-10-02 之后入职的员工及其部门名
SELECT *
FROM (SELECT * FROM employee WHERE join_date > '2025-10-02') AS e1
         LEFT JOIN department ON e1.department_id = department.id;

综合练习

▼
sql
复制代码
-- 1. 查询员工的姓名、年龄、职位、部门信息
SELECT employee.name, employee.age, employee.job, department.name
FROM employee,
     department
WHERE department_id = department.id;

-- 2. 查询年龄小于 24 的员工的姓名、年龄、职位、部门信息(没有部门就不查询出来)
SELECT employee.name, employee.age, employee.job, department.name
FROM employee
         INNER JOIN department
                    ON department_id = department.id
WHERE age < 24;

-- 3. 查询部门信息（该部门下必须有员工）
SELECT *
FROM department
WHERE (SELECT COUNT(department_id) FROM employee WHERE department_id = department.id GROUP BY department_id) > 0;

-- 4. 查询年龄小于 24 的员工的姓名、年龄、职位、部门信息(没有部门也要查询出来)
SELECT employee.name, employee.age, employee.job, department.name
FROM employee
         LEFT OUTER JOIN department
                         ON department_id = department.id
WHERE age < 24;

-- 5. 查询所有员工的薪资等级
SELECT name, salary, grade, min, max
FROM employee
         INNER JOIN salary_grade ON salary BETWEEN min AND max;

-- 6. 查询技术部员工的薪资等级
SELECT employee.name, salary, department.name, grade, min, max
FROM employee
         INNER JOIN department ON department_id = department.id AND department.name = '技术部'
         INNER JOIN salary_grade ON salary BETWEEN min AND max;

-- 7. 查询技术部员工的平均薪资
SELECT AVG(employee.salary)
FROM employee
         INNER JOIN department ON department_id = department.id AND department.name = '技术部';

-- 8. 查询出薪资比 孔勇 高的员工信息
SELECT e1.*
FROM employee e1
WHERE e1.salary > (SELECT e2.salary FROM employee e2 WHERE e2.name = '孔勇');

-- 9. 查询出比平均薪资高的员工信息
SELECT e1.*
FROM employee e1
WHERE e1.salary > (SELECT AVG(e2.salary) FROM employee e2);

-- 10. 查询出低于本部门平均工资的员工
SELECT e1.*
FROM employee e1
WHERE salary < (SELECT AVG(salary) FROM employee e2 WHERE e2.department_id = e1.department_id);

-- 11. 查询部门信息以及部门人数
SELECT d1.*, COUNT(e1.id)
FROM department d1
         LEFT OUTER JOIN employee e1 ON d1.id = e1.department_id
GROUP BY d1.id;

-- 12. 查询出所有老师的课程情况
SELECT teacher.name, class.name
FROM teacher_class
         LEFT JOIN teacher ON teacher_class.teacher_id = teacher.id
         LEFT JOIN class ON teacher_class.class_id = class.id;