MySQL 多表查询

这一页了解 SQL 多表的基础操作。

单表查询是 DQL 那一套，多个表之间通过外键关联起来之后，查询也得会同时查多张表。

先说两个大方向：外连接是左右拼接，联合查询是上下拼接。

多表查询的本质就是外键约束的那个关系，用 where 把它复述一遍，把数据筛出来。

一、多表关系和数据准备

1. 多对多

学生表和课程表里，一个学生可以学多门课程，一门课程也可以被多个学生学，这时候光靠两张表之间的外键就不够用了，得再加一张中转表：

建表的时候中转表建外键，剩下两张正常建：

# 中转表建立外键，剩下两张正常建表
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');


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');


create table student_course (
    id        int auto_increment primary key comment '主键',
    studentid int not null comment '学生ID',
    courseid  int not null comment '课程ID',
    constraint fk_studentid foreign key (studentid) references student (id),
    constraint fk_courseid  foreign key (courseid)  references course (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);

建完之后图形化工具里能看到关系视图，箭头就是从外键指向主键的：

2. 一对一

一对一是用在表结构拆分上的。比如一张用户表，把基本信息和教育信息全塞在里面：

这样的大表查询起来很麻烦，有时候只想看基本信息，所以可以拆成两张表，把基础信息和受教育信息分开放，然后通过外键关联：

这时候外键放在哪边无所谓了，因为目的是关联查询，但要加上 unique 约束，这样才能限制成一对一：

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 '用户基本信息表';

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(id, name, age, gender, phone) values
        (null,'黄渤',45,'1','18800001111'),
        (null,'冰冰',35,'2','18800002222'),
        (null,'码云',55,'1','18800008888'),
        (null,'李彦宏',50,'1','18800009999');

insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values
        (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
        (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
        (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
        (null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

3. 多表查询和笛卡尔积

从多张表里查数据，必须消除无效的笛卡尔积，而外键字段就是那个关联字段，用它来消除。

# 单表是
select * from [表]
# 多表就是
select * from [表1],[表2],[表3]...;
# 但是这样查询出来的是 笛卡尔积：多表组合的所有情况

select * from emp,dept where emp.dept_id = dept.id;

值为 null 的数据查不到。

多表查询（特别是外连接）里说的"查不到"，指的是关联键（连接条件里的那个字段）为 null，不是其他字段。

所以本质上还是通过限制条件筛数据，而这个条件就是把外键的关系复述一遍。

多表查询分这么几类：

# 多表查询分类

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

# 子查询

二、内连接

内连接查的是 a、b 交集部分的数据。

# 多表查询的字段列表是[表名.字段名]，因为字段名可能是重复的
# 也就是多表查询某个字段时，需要写明表
# 隐式内连接
select 字段列表 from 表1, 表2 where 连接条件;

# 显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;

# 二者的区别就是是否写了 inner join，查出来的结果是一样的
# 相当于把逗号 变成 inner join

# 查询员工名字和其对应的部门名称
# 注意from后面是表名
# 这后面 . 的意思是[数据库.表名]
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;

一般表多起来的时候就要用别名了，否则容易乱。

而且起了别名就全程用别名，不能一会儿写别名一会儿写原表名 —— 别名一旦定下来，原表名在这个语句里就指不到那张表了。

三、外连接

外连接和内连接差不多，都是把分隔两张表的 , 换成 left/right join。

内连接查交集，外连接查的是交集 + 其中一张表的全部。

# 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件 ...;
# 相当于查询表1(左表)的所有数据，包含表1和表2交集部分的数据


# 右外连接（把左外的两个表换位也可）
select 字段列表 from 表1 right [outer] join 表2 on 条件 ...;
# 相当于查询表2(右表)的所有数据，包含表1和表2交集部分的数据


# 内外连接对比
# 内连接
select * from emp e inner join dept d on e.dept_id = d.id;
# 左外连接
select * from emp e left join dept d on e.dept_id = d.id;

用在哪：emp 里有的人没分部门（dept_id = null），dept 里也有一个部门一个人都没有。

这时候内连接是查不到 dept_id 为 null 的员工的，外连接才可以。

四、自连接

自连接相当于把一张表拆成两张用，共用一套主键，然后套内/外连接的写法。

# 自连接查询
select 字段列表 from 表a 别名a join 表a 别名b on 条件 ...;

# 自连接可以是内连接，也可以是外连接

# 查询员工和领导的名字（员工有id，领导名字用字段managerid代替，对应字段id）
# 在一张表包含了需要做交集的查询信息
# 使用内连接
select a.name,b.name from emp a,emp b where a.managerid = b.id;

# 查询所有员工及其领导名字，没有领导也要查出来
# 此时使用外连接
select a.name,b.name from emp a left join emp b on a.managerid = b.id;

# 为了更清晰观看，可以起一个别名
select a.name '员工',b.name '领导' 
from emp a left join emp b on a.managerid = b.id;

就是把一张表看成两张来查，所以必须起别名。图里左右两张其实是同一张 emp 表，靠 managerid 去找 id：

五、联合查询

把多次查询的结果合并成一个新的结果集。

它不是交集，是取并集。

加 all 不去重合并，不加就自动去重合并。

# 合并查询（union / union all）
select 字段列表 from 表a ...
union [all]
select 字段列表 from 表b ...;

# 查薪资低于5000 和 年龄大于50的（并集）
select * from emp where salary < 5000
union all
select * from emp where age>50;
# 这里仅仅是合并，上下拼接两张表，重复的数据需要去除
# 删去 all 即可
select * from emp where salary < 5000
union
select * from emp where age>50;

# 必须列数一样、类型对得上，才能像叠积木一样拼在一起
# 类型对的上但是字段名不同也可以：
# 第一个查询查 name
# 第二个查询查 dept 表里的 name，起个别名叫 dept_name（类型也是字符串）
select name from emp
union
select name 'dept_name' from dept;

# 只是数据拼了，字段只显示上面的：
select name from emp
union
select name from dept;
# 上面的和这个查到的，都是name字段
select name from emp
union
select name 'aa' from dept;

六、子查询

顾名思义，就是 SQL 查询里套了查询。

按返回结果分四种：

配套的操作符：

# 子查询：sql语句中嵌套select语句
# 子查询外部的语句可以是 insert / update / delete / select 的任何一个

# 查询结果分类
# 标量子查询：子查询结果为单个值（一个字段 值/标量）
# 列子查询：子查询结果为一列（一个字段）
# 行子查询：子查询结果为一行（一条/个 数据）
# 表子查询：子查询结果为多行多列（表）

# 查询位置分类
# where之后
# from之后
# select 之后

重点是会用子查询，而不是判断它属于哪一类，一般先写子查询，再写外层。

1. 标量子查询

返回结果是一行一列，也就是单个值。

# 标量子查询：返回单个值（一个格子）
select * from emp where age = (select max(age) from emp);
# 括号里返回一个数字，比如 88

# 查询销售部的员工信息
select * from emp where dept_id=
(select id from dept where name = '销售部');
# 括号里的语句只返回一个 值，所以叫标量子查询

现在看来这确实把简单的查询复杂化了，但子查询是用在一些更绕的场景上的：

# 查 有员工的部门
select * from dept where id in
(select dept_id from emp where dept_id is not null);

2. 列子查询

子查询返回一列（一个字段），配上面那些操作符用。

操作符都写在子 SQL 的前面，也就是括号前面。

# 列子查询
# 1. 查询 "销售部" 和 "市场部" 的所有员工信息
# a. 查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name = '销售部' or name = '市场部';
得到一列id：2 4

# b. 根据部门ID，查询员工信息
select * from emp where dept_id in (2,4);

# 上面的两步可以化为一步
# 相当于把查询的结果换成子查询的 SQL 语句
select * from emp where dept_id in 
(select id from dept where name = '销售部' or name = '市场部');
# 内部查询的数据是一列，称为列子查询


# 2.查询比 "财务部" 员工工资都高的员工信息
select * from emp where salary > all
(select salary from emp where dept_id = 3);
# 这个句子还能拆分为一个子查询，这里不做演示了

# 前面提到了：操作符都在子 SQL 的前面
# 也就是都在括号前面，中间没东西

3. 行子查询

子查询返回一条数据，也就是一行。

# 查询与 张无忌 薪资直属领导 相同的员工信息
select * from emp where 
(salary,managerid) = 
(select salary,managerid from emp where name = '张无忌');
# 括号内对应字段的位置要和子查询的结果对的上

这里捋一下标量、列和行三种子查询的区别，关键在写条件的时候字段和值谁对谁：

标量子查询返回的是单个值，所以右边直接跟一个值就行，写法是 where 字段 = 值。

列子查询返回的是一列值，一个字段对着一堆值，不可能全都等于，所以要用 in、any、all 这种条件去比，写法是 where 字段 = [条件] 值。

行子查询返回的是多个字段，那就得字段对字段地划等号，写法是 where (字段1, 字段2) = [条件] (值1, 值2)。同列子查询一样，每个字段都可能返回多个值，所以常见的是 in 或者配合 any、all。

判断方法就一句：返回结果是一行就是行子查询，写条件时字段要一起对。

4. 表子查询

子 SQL 返回的是一张表，多行多列。

# 1. 查询与 "鹿杖客" ， "宋远桥" 的职位和薪资相同的员工信息  
# a. 查询 "鹿杖客" ， "宋远桥" 的职位和薪资
select job,salary from emp where name = '鹿杖客' or name = '宋远桥';

# b. 查询与 "鹿杖客" ， "宋远桥" 的职位和薪资相同的员工信息
# 和行子查询的区别在于条件中不使用 =，使用 in
# 因为查到了表，每个字段都会对应多个信息，肯定不能 = 了
# 还是那句话，总要选一个，那就用条件
select * from emp where (job,salary) in
(select job,salary from emp where name = '鹿杖客' or name = '宋远桥');

# 2. 查询入职日期都是 "2006-01-01" 之前的员工信息、部门信息(join)
# a. 在该日期之前的人
# 子查询只取 2006年之前入职的员工，这个结果集比原表小
# 如果派生表和原有的相同就没必要进行子查询了
select * from emp where entrydate < "2006-01-01";

# b. 左右拼接，a的结果相当于表1
select * from 表1
left join
表2 on emp.dept_id = dept.id;

# 必须加括号
select * from (select * from emp where entrydate < "2006-01-01")
left join
dept on emp.dept_id = dept.id;

报错：[42000][1248] Every derived table must have its own alias

[42000][1248]每个派生表必须有自己的别名
# 派生表就是子查询表

数据库引擎处理子查询的时候，需要给临时结果集起个名字，才能引用里面的字段，不然它就不知道自己该用哪张表的哪一列。

加上别名之后：

select * from (select * from emp where entrydate < "2006-01-01") a1
left join
dept on a1.dept_id = dept.id;
# 这里使用 a1 派生表的字段查询，这里不能用其他表
# 因为 from 之后锁定了a1表和dept表（执行顺序）

# select的执行顺序在from之后：
# 这里直接写了 select * 而不是 a1.*,dept.*
# 因为是 先拼接好了，再去查询的一张 大表

再重申一遍：左右拼接只能用 on，不能用 where。

七、练习题

这些题别嫌简单，动手敲一遍。先建一张薪资等级表：

# 薪资等级表
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);

# 1. 查询员工的姓名、年龄、职位、部门信息（隐式内连接）
# 表: emp, dept
# 连接条件: emp.dept_id = dept.id
select
    e.name,
    e.age,
    e.job,
    d.name
from
    emp e,
    dept d
where
    e.dept_id = d.id;


# 2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
# 表: emp, dept
# 连接条件: emp.dept_id = dept.id
select
    e.name,
    e.age,
    e.job,
    d.name
from
    emp e
inner join dept d on e.dept_id = d.id
where
    e.age < 30;


# 3. 查询拥有员工的部门ID、部门名称
# 表: emp, dept
# 连接条件: emp.dept_id = dept.id
# 内连接已经把没人的部门过滤掉了，不用再 distinct
select
    d.id,
    d.name
from
    emp e,
    dept d
where
    e.dept_id = d.id;


# 4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
# 表: emp, dept
# 连接条件: emp.dept_id = dept.id
# 外连接
# 区分连接条件和查询条件
select
    e.*,
    d.name
from
    emp e
left join dept d on e.dept_id = d.id
where
    e.age > 40;


# 5. 查询所有员工的工资等级
# 表: emp, salgrade
# 连接条件: emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select
    e.*,
    s.grade,
    s.losal,
    s.hisal
from
    emp e,
    salgrade s
where
    e.salary >= s.losal and e.salary <= s.hisal;

# 或者
select
    e.*,
    s.grade,
    s.losal,
    s.hisal
from
    emp e,
    salgrade s
where
    e.salary between s.losal and s.hisal;


# 6. 查询 "研发部" 所有员工的信息及工资等级
# 表: emp, salgrade, dept
# 连接条件: emp.salary between salgrade.losal and salgrade.hisal, emp.dept_id = dept.id
# 查询条件: dept.name = '研发部'


# 多表查询和先左右拼接后查询，是一样的，除了显示字段不一样
# 多表查询（from A, B where 条件） 先做笛卡尔积，再用 where 过滤
# 显式连接（from A join B on 条件） 先按条件拼接，再查
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
    e.*,
    s.grade
from
    emp e
inner join dept d on e.dept_id = d.id
inner join salgrade s on e.salary between s.losal and s.hisal
where
    d.name = '研发部';

# 7. 查询 "研发部" 员工的平均工资
# 表: emp, dept
# 连接条件: emp.dept_id = dept.id
select
    avg(e.salary)
from
    emp e,
    dept d
where
    e.dept_id = d.id
    and d.name = '研发部';


# 8. 查询工资比 "灭绝" 高的员工信息
# a. 查询 "灭绝" 的薪资
select salary from emp where name = '灭绝';

# b. 查询比她工资高的员工数据
select *
from emp
where salary > (select salary from emp where name = '灭绝');


# 9. 查询比平均薪资高的员工信息
# a. 查询员工的平均薪资
select avg(salary) from emp;

# b. 查询比平均薪资高的员工信息
select *
from emp
where salary > (select avg(salary) from emp);


# 10. 查询低于本部门平均工资的员工信息
# a. 查询指定部门平均薪资 1
select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary) from emp e1 where e1.dept_id = 2;

# b. 查询低于本部门平均工资的员工信息
select *
from emp e2
where e2.salary < (
    select avg(e1.salary)
    from emp e1
    where e1.dept_id = e2.dept_id
);


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

# 要明确写出要查的字段，这是合乎规范的：
# 比如不能把 d.id,d.name 换成 *
# 理论可行实际不推荐
# 这里 select 后面的子查询是标量子查询，而且是相关子查询

外层每遍历到一行，子查询就跟着执行一次：

• 遍历到部门 1 → 子查询查 dept_id=1 的人数 → 返回人数
• 遍历到部门 2 → 子查询查 dept_id=2 的人数 → 返回人数
• 遍历到部门 5 → 子查询查 dept_id=5 的人数 → 返回人数

select
    d.id,
    d.name,
    (select count(*) from emp e where e.dept_id = d.id) '人数' 
    # 这里相当于直接插入一列（插入一个字段）
from dept d;

# 统计指定部门人数
select count(*) from emp where dept_id = 1;


# 12. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
# 表: student, course, student_course
# 连接条件: student.id = student_course.studentid, course.id = student_course.courseid
select
    s.name,
    s.no,
    c.name
from
    student s,
    student_course sc,
    course c
where
    s.id = sc.studentid
    and sc.courseid = c.id;

上面这些注释一定要看。

八、小结

多表关系就三种：一对多在多的一方设外键；多对多建中间表放两个外键；一对一用于表结构拆分，在任意一方设外键加唯一约束。

多表查询的语法小结：