MySQL基础——多表查询
上篇文章我们学习了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基础——事务。
