多表查询是关系型数据库中的一项重要操作，它是指通过多个相关联表之间的连接或组合来获取我们需要的数据。在实际的应用场景中，我们通常需要从多个表中获取相互关联的数据来进行综合分析、报表生成等操作。比如，在电商网站中涉及到用户、商品、订单、支付等多个模块，需要通过多个表的信息来生成某个用户的订单信息。因此，掌握多表查询的相关知识是非常重要的。
多表查询的方式有很多，常见的方法包括内连接、左连接、右连接、全外连接、UNION 操作符和交叉连接等。在使用过程中，我们需要根据实际需求，选择合适的查询方式，结合联表、子查询等技术实现所需的数据获取。
同时，在进行多表查询时，为了保证查询准确性和效率，需要了解和掌握涉及到的每个表的结构、数据关系和索引等信息，以便更快速、准确地进行查询操作。虽然多表查询相较于单表查询需要更多的开发成本，但它的查询结果更加全面、准确，具有重要的实战意义。
01 多表关系
在一个关系型数据库中，不同表之间会有不同的关系，这些关系可以通过外键进行定义和维护。以下是一些常见的多表关系：
1. 一对多
这种关系描述的是一个表（主表）中的每个记录可以对应另一个表（从表）中的多个记录。在从表中，会通过外键来指向主表中的记录。
例如，一个部门对应多个员工，一个员工对应一个部门。
在多的一方建立外键，指向一的一方的主键。
2. 多对多
这种关系描述的是一个表中的每个记录可以对应另一个表中的多个记录，同时，另一个表中的每个记录也可以对应该表中的多个记录。在关系型数据库中，需要通过连接表来实现多对多的关系。
例如，一个学生可以选修多门课程，一门课程可以供多个学生选择。
建立第3张中间表，中间表至少包含两个外键，分别关联两方主键。
3. 一对一
这种关系描述的是一个表中的每个记录都只能对应另一个表中的一个记录，反之亦然。在关系型数据库中，需要通过外键来实现一对一的关系。
例如，一个员工表可以和一个薪资表存在一对一的关系，一个人员表可以和一个身份证号码表存在一对一的关系等。
在任意一方加入外键，关联另一方主键，并设置外键为唯一的（UNIQUE）
02 多表查询概述
多表查询：是指从多张表中查询数据。
笛卡尔积：指在数学中，两个集合A和集合B的所有组合情况。多表查询中，一张2行的表与一张3行的表的笛卡尔积，得到的是2×3=6的表，通常查询结果无效。多表查询时，需要消除无效的笛卡尔积。
分类
连接查询
内连接（INNER JOIN）：查询A、B交集部分的数据
外连接
左外连接（LEFT JOIN）：查询满足条件的数据 和 左表中不满足连接条件的数据
右外连接（RIGHT JOIN）：查询满足条件的数据 和 右表中不满足连接条件的数据
全外连接（FULL JOIN）：返回左表和右表中的所有数据（MySQL不支持）
自连接：当前表与自身的连接查询，必须使用别名
交叉连接（CROSS JOIN）：笛卡尔积
联合查询（UNION, UNION ALL）：把多次查询的结果合并起来，形成一个新的查询结果
子查询：SQL语句中嵌套SELECT语句
03 内连接
-- 隐式内连接
SELECT col_list FROM tb_1, tb_2 WHERE condition;
-- 显式内连接
SELECT col_list FROM tb_1 [INNER] JOIN tb_2 ON join_condition;
举个栗子
数据库：world（MySQL自带），数据表：city、country（MySQL自带）。需要回答以下问题：
查询首都不为null的国家名、首都名。
-- 内连接（不显示capital为null的国家名）
-- 1. 隐式内连接
select country.name, city.name as capital
from country, city
where country.capital = city.id
order by country.name;
-- 2. 显式内连接
select country.name, city.name as capital
from country join city on country.capital = city.id
order by country.name;
04 外连接
-- 左外连接
SELECT col_list FROM tb_1 LEFT [OUTER] JOIN tb_2 ON join_condition;
-- 右外连接
SELECT col_list FROM tb_1 RIGHT [OUTER] JOIN tb_2 ON join_condition;
举个栗子
数据库：world（MySQL自带），数据表：city、country（MySQL自带）。需要回答以下问题：
查询所有国家的国家名、首都名。
-- 外连接
-- 1. 左外连接（国家名全部显示，首都名有null值）
select country.name, city.name as capital
from country left join city on country.capital = city.id
order by country.name;
-- 2. 右外连接（左右连接可以互换，得到的结果一致）
select country.name, city.name as capital
from city right join country on country.capital = city.id
order by country.name;
05 自连接
SELECT col_list FROM tb as a JOIN tb as b ON join_condition;自连接查询可以是内连接查询，也可以是外连接查询
举个栗子
创建一张学生信息表并插入数据，代码如下。
查询每个学生对应的班长姓名。
-- 自查询
-- 创建学生信息表 stu_info：id, name, class_id, monitor_id
drop table if exists stu_info;
create table stu_info(
id int primary key auto_increment comment '学生id'
, name varchar(10) not null comment '学生姓名'
, class_id int not null comment '班级id'
, monitor_id int comment '班长id'
) comment '学生信息表';
-- 插入数据
delete from stu_info;
insert into stu_info(name, class_id, monitor_id) values('student_1', 1, 1);
insert into stu_info(name, class_id, monitor_id) values('student_2', 2, 6);
insert into stu_info(name, class_id, monitor_id) values('student_3', 2, 6);
insert into stu_info(name, class_id, monitor_id) values('student_4', 1, 1);
insert into stu_info(name, class_id, monitor_id) values('student_5', 3, 8);
insert into stu_info(name, class_id, monitor_id) values('student_6', 2, 6);
insert into stu_info(name, class_id, monitor_id) values('student_7', 1, 1);
insert into stu_info(name, class_id, monitor_id) values('student_8', 3, 8);
insert into stu_info(name, class_id, monitor_id) values('student_9', 4, null);
-- 查询每个学生对应的班长姓名
select a.name as stu_name, b.name as monitor_name
from stu_info a left join stu_info b
on a.monitor_id = b.id;
06 联合查询
SELECT col_list FROM tb_1 …
UNION [ALL]
SELECT col_list FROM tb_2 …;
- 联合查询的多张表的列数必须保持一致，列的数据类型也要一致。
- union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。
举个栗子
数据库：world（MySQL自带），数据表：city（MySQL自带）。需要回答以下问题：
查询countrycode以A开头或人口数量大于500000的城市名称、国家代码、人口数
-- 联合查询
-- 查询countrycode以A开头或人口数量大于500000的城市名称、国家代码、人口数
select name, countrycode, population from city where countrycode like 'a%'
union all -- 包含重复数据
select name, countrycode, population from city where population > 500000
order by name;
select name, countrycode, population from city where countrycode like 'a%'
union -- 不包含重复数据
select name, countrycode, population from city where population > 500000
order by name;
07 子查询
SELECT * FROM tb_1 WHERE col_1 = (SELECT col_1 FROM tb_2); 子查询外部的语句可以使 INSERT / UPDATE / DELETE / SELECT的任何一个。
分类
根据结果分：
- 标量子查询（子查询的结果为单个值）：常用 =, <>, >, >=, <, <=
- 列子查询（子查询的结果为一列）：常用 IN, NOT IN, ANY, SOME, ALL
- 行子查询（子查询的结果为一行）：常用 =, <>, IN, NOT IN
- 表子查询（子查询的结果为多行多列）：常用 IN
根据位置分：
- WHERE之后
- FROM之后
- SELECT之后
举个栗子
数据库：world（MySQL自带），数据表：city、country（MySQL自带）。需要回答以下问题：
- 查询国家为Brazil的首都名
- 查询国家名为Afghanistan的城市名
- 查询国家为Brazil的国家代码、首都名
- 查询国家名为Afghanistan的国家代码、城市名
- 查询每个国家城市人口数大于500000的国家代码、国家名、城市数量、最大城市人口数
- 查询国家名为Afghanistan的国家代码、城市总数
-- 子查询
-- 1. （标量子查询、where之后）查询国家为Brazil的首都名
select name as capital_name from city
where id = (select capital from country where name = 'Brazil');
-- 2. （列子查询）查询国家名为Afghanistan的城市名
select name from city
where countrycode = (select code from country where name = 'Afghanistan');
-- 3. （行子查询）查询国家为Brazil的国家代码、首都名
select name as capital_name, countrycode from city
where id = (select capital from country where name = 'Brazil');
-- 4. （表子查询）查询国家名为Afghanistan的国家代码、城市名
select name, countrycode from city
where countrycode = (select code from country where name = 'Afghanistan');
-- 5. （from之后）查询每个国家城市人口数大于500000的国家代码、国家名、城市数量、最大城市人口数
select countrycode, country_nm, count(*) as city_cnt, max(city_ppl) as city_ppl_max
from (
select city.countrycode, country.name as country_nm, city.name as city_nm, city.population as city_ppl
from city left join country
on city.countrycode = country.code
where city.population > 500000
) as tb
group by countrycode, country_nm
order by countrycode;
-- 6. （select之后）查询国家名为Afghanistan的国家代码、城市总数
select
code
, (
select count(a.name) as city_cnt
from city a left join country b
on a.countrycode = b.code
where b.name = 'Afghanistan'
group by b.code
) as city_cnt
from country
where name = 'Afghanistan';
不总结=白学
THE END
660
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
