Skip to main content

多表查询

多表关联(多表查询)是指在数据库查询时，同时从两张或多张表中获取数据，并按某种逻辑(通常是主外键)把这些表的数据关联起来，是实际开发中最常用、最核心的 SQL 能力之一。

多表关联​

通过一个表中的字段关联另一张表的数据。

一对一​

A 表的一行，只能关联 B 表的一行；B 表的一行，也只能关联 A 表的一行。换句话说，每个 A 只配一个 B，每个 B 也只配一个 A。

/*
user                   user_profile
+----+----------+      +----+---------+---------------------+
| id | username |      | id | user_id | bio                 |
+----+----------+      +----+---------+---------------------+
|  1 | alice    | <--> |  1 |   1     | Alice is a coder    |
|  2 | bob      | <--> |  2 |   2     | Bob likes music     |
+----+----------+      +----+---------+---------------------+

- user.id=1 <=> user_profile.user_id=1
- user.id=2 <=> user_profile.user_id=2
*/
  • 外键是关联表中的主键。
  • 一对一外键必须唯一，且值必须在关联主键中存在。
  • 两个表互相之间都是唯一对应关系。

一对多​

A 表的一行，可以关联 B 表的多行；但 B 表的一行只能关联 A 表的一行。

一对多和多对一是相对的，只是视角不同。对于 B 来说， B 表的多行可以关联 A 表的一行。

/*
user                 orders
+----+----------+    +----+---------+----------+
| id | username |    | id | user_id | product  |
+----+----------+    +----+---------+----------+
| 1  | alice    |    | 1  |   1     | 手机     |  ←─┐
| 2  | bob      |    | 2  |   2     | 耳机     |    │
+----+----------+    | 3  |   1     | 电脑     |  ←─┘
                     +----+---------+----------+

- alice（user id=1）下了两个订单（id=1,3）
- bob（user id=2）下了一个订单（id=2）
*/

多对多​

A 表的一行，可以关联 B 表的多行；同时 B 表的一行，也可以关联 A 表的多行。

/*
student            student_course                 course
+----+-------+     +------------+-----------+     +----+------+
| id | name  |     | student_id | course_id |     | id | name |
+----+-------+     +------------+-----------+     +----+------+
|  1 | 明    |     |     1      |     1     |     |  1 | 语文  |
|  2 | 红    |     |     1      |     2     |     |  2 | 数学  |
|  3 | 刚    |     |     2      |     2     |     |  3 | 英语  |
|    |       |     |     1      |     3     |     |    |      |
+----+-------+     +------------+-----------+     +----+------+
*/

多对多通常需要一张中间表来维护，使用主表的主键组成关联表的外键。

外键约束​

在一张表里，某一字段的取值，必须来自另一张表里某个字段的已有取值，通常是对方的主键或唯一键，在两张表之间建立关系，确保引用完整性。

主表也叫左表，通常是被外键引用的一方；子表也叫右表，通常是拥有外键的表。

创建​

创建外键的基本语法为：

constraint <fk_name> foreign key(field) references <reference_table>(reference_field) <on update cascade> <on delete cascade>;

在创建新的数据表时，可直接创建外键约束：

create table departments (
  dpt_id int primary key auto_increment,
  dpt_name varchar(20) not null,
  dpt_number int not null
)

create table employees (
  emp_id char(8) primary key,
  emp_name varchar(20) not null,
  emp_gender char(2) not null,
  emp_age int not null,
  did int,

  /* 给 employees 表加一个外键约束（名为 FK_EMPLOYEES_DEPARTMENTS），约束 employees.did 字段必须对应 departments.dpt_id。
  如果部门表里的部门 ID 被修改或删除，会自动同步/删除员工表中相关数据 */
  constraint FK_EMPLOYEES_DEPARTMENTS foreign key(did) references departments(dpt_id) on update cascade on delete cascade
)

/* 查看外键名称 */
show create table employees;

如果表已经创建，可通过修改的方式进行添加：

alter table employees add constraint FK_EMPLOYEES_DEPARTMENTS foreign key(did) references departments(dpt_id) on update cascade on delete cascade;

删除​

可通过 drop 的方式进行外键删除：

alter table <table_name> drop foreign key <fk_name>;

查看​

如果忘记了外键名称，可通过查看表创建语句进行查看：

show create table <table_name>;

修改​

MySQL 不支持直接修改外键约束的名称，只能采用先删除原外键，再用新名字重新添加的方法实现外键重命名。

连接查询​

inner jon​

通过外键将外表关联的数据一并查询出来，inner join 只能查询到有效外键关联的数据。

inner join 的基本语法为：

-- where
select <column1, column2, ...> from <table_name> inner join <table_name> where condition;

-- on
select <column1, column2, ...> from <table_name> inner join <table_name> on condition;

where 与 on 的区别：

  • on：多表关联时，定义两表之间如何配对(即哪些行彼此关联)，主要负责表之间的连接条件，性能相比 where 较好。
  • where：对查询结果进一步过滤，无论是单表还是多表查询，都会对最终生成的结果集筛选需要的行。
select * from employees inner join departments where employees.did = departments.dpt_id;
select * from departments inner join employees where employees.did = departments.dpt_id;

/*
+--------+-----------------+------------+--------+----------+------------+---------+------+
| dpt_id | dpt_name        | dpt_number | emp_id | emp_name | emp_gender | emp_age | did  |
+--------+-----------------+------------+--------+----------+------------+---------+------+
|      1 | 技术开发部        |         25 | 1001   | w1       | 1          |      26 |    1 |
|    300 | 产品设计部        |         15 | 1002   | w2       | 1          |      30 |  300 |
|      4 | 人事管理部        |         10 | 1003   | w3       | 1          |      28 |    4 |
+--------+-----------------+------------+--------+----------+------------+---------+------+
*/

在 inner join 查询中，主表及子表的位置不会影响查询的结果集，其顺序只影响输出列的顺序。

left join​

left join 可以查询有效与无效外键关联的所有数据，左表中的数据必须全部查询出来。

语法为：

select * from <right_table> left join <left_table> on condition;

以左表为基准，向右表加入，右表记录匹配关联的就设置信息，不能关联的就置空。

select * from employees left join departments on employees.did = departments.dpt_id;
/*
+--------+----------+------------+---------+------+--------+-----------------+------------+
| emp_id | emp_name | emp_gender | emp_age | did  | dpt_id | dpt_name        | dpt_number |
+--------+----------+------------+---------+------+--------+-----------------+------------+
| 1001   | w1       | 1          |      26 |    1 |      1 | 技术开发部        |         25 |
| 1002   | w2       | 1          |      30 |  300 |    300 | 产品设计部        |         15 |
| 1003   | w3       | 1          |      28 |    4 |      4 | 人事管理部        |         10 |
| 1004   | w3       | 0          |      22 | NULL |   NULL | NULL            |       NULL |
+--------+----------+------------+---------+------+--------+-----------------+------------+
*/

right join​

right join 可以查询有效与无效外键关联的所有数据，右表中的数据必须全部查询出来。

语法为：

select * from <right_table> right join <left_table> on condition;

以右表为基准，向左表加入，左表记录匹配关联的就设置信息，不能关联的就置空。

select * from employees right join departments on employees.did = departments.dpt_id;
/*
+--------+----------+------------+---------+------+--------+-----------------+------------+
| emp_id | emp_name | emp_gender | emp_age | did  | dpt_id | dpt_name        | dpt_number |
+--------+----------+------------+---------+------+--------+-----------------+------------+
| 1001   | w1       | 1          |      26 |    1 |      1 | 技术开发部      |         25   |
| NULL   | NULL     | NULL       |    NULL | NULL |      2 | 市场营销部      |         30   |
| 1003   | w3       | 1          |      28 |    4 |      4 | 人事管理部      |         10   |
| 1002   | w2       | 1          |      30 |  300 |    300 | 产品设计部      |         15   |
+--------+----------+------------+---------+------+--------+-----------------+------------+
*/

别名​

在进行查询时可以指定结果要显示的列：

  • table_name.* 表示所有列。
  • table_name.field1,table_name.field2,... 指定具体的列。
select employees.*, departments.dpt_name,departments.dpt_id from employees right join departments on employees.did = departments.dpt_id;
/*
+--------+----------+------------+---------+------+-----------------+--------+
| emp_id | emp_name | emp_gender | emp_age | did  | dpt_name        | dpt_id |
+--------+----------+------------+---------+------+-----------------+--------+
| 1001   | w1       | 1          |      26 |    1 | 技术开发部        |      1 |
| NULL   | NULL     | NULL       |    NULL | NULL | 市场营销部        |      2 |
| 1003   | w3       | 1          |      28 |    4 | 人事管理部        |      4 |
| 1002   | w2       | 1          |      30 |  300 | 产品设计部        |    300 |
+--------+----------+------------+---------+------+-----------------+--------+
*/

此外，在查询时，还可设置表的别名，表名过长的情况下，可简化 SQL：

select e.*, d.dpt_name,d.dpt_id from employees e right join departments d on e.did = d.dpt_id;

如上，e 代表 employees，d 代表 departments。

子查询​

子查询，又叫内查询，是指在一个 SQL 语句中，把另一个查询语句嵌套在 SELECT、INSERT、UPDATE、DELETE 或 WHERE、FROM、HAVING 等子句中，用来辅助主查询的数据筛选和处理。

语法为：

select <fields> from <table_name> where condition (subquery);
-- select * from employees where did = (select dpt_id from departments where dpt_id=1);

select <fields> from (subquery) <table_name> where condition;
-- select * from (select * from employees where emp_id=1001) emp where emp.emp_age=26;
Last updated on