上篇文章我们学习了MySQL基础——约束，这篇文章我们学习MySQL基础——多表查询。

多表关系

在数据表中，各个表结构之间存在着各种关系（一对一、一对多、多对多）。

一对一关系：

示例：学生与学生详情的关系，一个学生对应一个详细情况，一个详细情况对应一个学生。

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的UNIQUE。

一对多关系：

示例：学生与班级的关系，一个班级对应多个学生，一个学生对应一个班级。

实现：在一对多关系中，在多的一方建立外键，指向一的一方的主键。

多对多关系：

示例：学生与课程的关系，一个学生可以选修多门课程，一门课程也可以给多个学生选择。

实现：在多对多关系中，建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

接下来我们通过一对多关系来演示多表查询，演示的数据表结构如下：

建表语句如下：

create table classtype(
	id int auto_increment primary key comment '类型id',
	name varchar(10) comment '类型名'
)comment '班级类型表';

create table students(
	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 '手机号',
    friendid int comment '朋友id',
    classid int comment '班级id',
    constraint fk_students_classtype foreign key(classid) references classtype(id)
)comment '学生表';

数据表数据如下：

添加数据语句如下：

# 添加班级类型数据
insert into classtype values (null,'Java'),(null,'Python'),(null,'MySQL'),(null,'Hadoop');

# 添加学生数据
insert into students values (null,'张三',18,'1','13700000000',2,1),(null,'李四',19,'1','13700000001',3,1),(null,'王五',18,'1','13700000000',3,1),(null,'诸葛亮',16,'1','13730000000',2,3),(null,'孙尚香',18,'2','13769999999',6,2),(null,'马克',16,'1','13755555555',1,2),(null,'武则天',16,'2','13666666666',5,4),(null,'艾琳',16,'2','13444444444',9,3),(null,'公孙离',20,'2','13733333333',7,4),(null,'李元芳',15,'1','1300000000',1,null);

多表查询

多表查询可以分为连接查询、联合查询和子查询。

连接查询

连接查询有：

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

内连接

内连接可以分为隐式内连接和显式内连接，语法格式如下：

# 隐式内连接
select 字段列表 FROM 表1,表2 where 条件.....;

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

查询学生的所有信息，示例代码如下：

# 隐式内连接
select * from students,classtype where students.classid=classtype.id;

# 显式内连接
select * from students inner join classtype on students.classid=classtype.id;

如下图所示：

由于id为10的学生classid为null，所以只显示了9条数据。

当我们的数据表名很长时，可以为数据表其别名，上面的代码可以改写为：

# 隐式内连接
select * from students s,classtype c where s.classid=c.id;

# 显式内连接
select * from students s inner join classtype c on s.classid=c.id;

返回的结果是一样的。

外连接

外连接查询语法格式如下：

# 左外连接
select 字段列表 FROM 表1 left [outer] join 表2 on 条件...;

# 右外连接
select 字段列表 FROM 表1 right [outer] join 表2 on 条件...;

查询学生的所有信息，示例代码如下：

# 左外连接
select * from students left outer join classtype on students.classid=classtype.id;

# 右外连接
select * from students right outer join classtype on students.classid=classtype.id;

如下图所示：

自连接

自连接查询，可以是内连接查询，也可以是外连接查询，语法格式如下：

select 字段列表 from 表A 别名A join 表名A 别名B on 条件...;

示例代码如下：

select a.name,b.name from students a join students b on a.id=b.friendid;

如下图所示：

联合查询

联合查询就是把多次查询的结果合并起来，形成一个新的查询结果集，使用union、union all关键字连接，语法格式如下：

select 字段列表 from 表A union [all] select 字段列表 from 表B

查询学生年龄大于17和学生性别为男的所有信息，示例代码如下：

select * from students where age>17;
select * from students where gender=1;
select * from students where age>17 union all select * from students where gender=1;

如下图所示：

这样就把两个查询的结果合并在一起了。但没有去重，这时我们只需要把all去掉就可以去重了，代码如下：

select * from students where age>17 union select * from students where gender=1;

如下图所示：

注意：在联合查询中，多张表的列数、字段类型必须保持一致。

子查询

在SQL语句中嵌套select语句，称为嵌套查询，又称子查询。语法格式如下：

select * from t1 where column1=(select column1 from t2);

子查询外部的语句可以是insert、update、delete、select的任何一个。

根据子查询结果不同，分为：

• 标量子查询（子查询结果为单个值）；
• 列子查询（子查询结果为一列）；
• 行子查询（子查询结果为一行）；
• 表子查询（子查询结果为多行多列）。

根据子查询位置，可以分为：where之后，from之后，select之后。

标量子查询

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

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

查询班级为Python的所有学生信息，示例代码如下：

select * from students where classid=(select id from classtype where name='Python');

如下图所示：

列子查询

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

常用的操作符：

• IN：在指定的集合范围之内，多选一；
• NOT IN ：不在指定的集合范围之内；
• ANY：子查询返回列表中，有任意一个满足即可；
• SOME：子查询返回列表中，有任意一个满足即可；
• ALL：子查询返回列表的所有值都必须满足。

查询学生班级为Python或Java的所有学生信息，示例代码如下：

select * from students where id in (select id from classtype where name='Python' or name='Java');

如下图所示：

行子查询

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

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

示例代码如下：

select * from students where id in (select id from classtype where friendid='2');

表子查询

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

常用的操作符为IN。

查询与张三、孙尚香年龄和手机号相同的学生信息，示例代码如下：

select * from students where (age,phone) in (select age,phone from students where name='张三' or name='孙尚香');

如下图所示：

好了，MySQL基础——多表查询就学到这里了，下篇文章学习MySQL基础——事务。

– END –