关于MySQL的多表操作

不管是大型还是小型项目，一个数据库里都会有N张表，表之间也通过一对一、多对一或者多对多关系进行关联：如新闻管理系统

作者表：id、用户名、密码
新闻表：id、标题、内容、发布时间、作者id

显示新闻的时候是肯定需要显示作者姓名的

原始方式：查出新闻–>查出作者ID–>查出作者
##步骤多
##如果是批量显示新闻就更麻烦
*多表操作：使用连接查询一条SQL搞定

一、联合查询

#1、联合查询

联合查询：union，是指将多个查询结果合并成一个结果显示

*联合查询是针对查询结果的合并（多条select语句合并）
*联合查询语法

select 查询【决定字段名字】
	union 查询选项
select 查询
...
1
2
3
4

*联合查询要求：联合查询是结果联合显示
##多个联合查询的字段结果数量一致
##联合查询的字段来源于第一个查询语句的字段
*查询选项：与select选项类似
##all：保留所有记录
##distinct：保留去重记录（默认）

steps
1、确定要进行多个表数据的联合操作
##表结构一致
##数据汇总
2、确定数据的要求：全部保留 or 去重

3、使用联合查询

eg：
1、创建一个表与t_11一样

create table t_13 like t_11;
insert into t_13 values(null,'犬夜叉','男',200,'神妖1班'),
(null,'日暮戈薇','女',16,'现代1班'),
(null,'桔梗','女',88,'法师1班'),
(null,'弥勒','男',28,'法师2班'),
(null,'珊瑚','女',20,'法师2班'),
(null,'七宝','保密',5,'宠物1班'),
(null,'杀生丸','男',220,'神妖1班'),
(null,'铃','女',4,'现代1班'),
(null,'钢牙','男',68,'神妖1班'),
(null,'奈落','男',255,'神妖1班'),
(null,'神乐','女',15,'神妖2班');
1
2
3
4
5
6
7
8
9
10
11
12

2、使用联合查询将两张的数据拼接到一起显示

select * from t_11 
union
select * from t_13;
1
2
3

3、联合查询默认选项是distinct

select * from t_11
union 
select * from t_11;

select * from t_11
union all
select * from t_11;
1
2
3
4
5
6
7

4、联合查询不要求字段类型一致，只对数量要求一致，而且字段与第一条查询语句相关

select name from t_11
union all
select age from t_11;
1
2
3

5、如果使用where对数据进行筛选，where针对的是select指令，而不是针对union结果

select * from t_11 
union all
select * from t_13
where gender = '女';
1
2
3
4

#2、联合查询

联合查询排序：针对联合查询的结果进行排序

*order by本身是对内存结果进行排序，union的优先级高于order by，所以order by默认是对union结果进行排序
*如果想要对单独select的结果进行排序，需要两个步骤
##将需要排序的select指令进行括号包裹（括号里使用order by）
##order by必须配合limit才能生效（limit一个足够大的数值即可）

steps
1、确定需要对联合查询进行排序

2、确定排序内容
#针对union结果排序
#针对union前的select结果进行排序
3、选择合适的排序方式

eg：1、将两个表的结果用年龄降序排

select * from t_11
union all
select * from t_13
order by age desc; #针对的是整个union之后的结果
1
2
3
4

2、t_11表按年龄降序排序，t_13表按年龄升序排序

# 无效方式
(select * from t_11 order by age desc)
union 
(select * from t_13 order by age);

# 正确方式
(select * from t_11 order by age desc limit 99999)
union 
(select * from t_13 order by age desc limit 99999);

1
2
3
4
5
6
7
8
9
10

二、连接查询

连接查询：join，将两张表依据某个条件进行数据拼接

*join左右各一张表：join关键字左边的表叫左表，右边的表叫右表
*连接查询的结果都是记录会保留左右表的所有字段（字段拼接）
##具体字段数据依据查询需求确定
##表字段冲突需要使用表别名和字段别名区分
*不同的连表有不同的连接方式，对于结果的处理也不尽相同
*连接查询不限定表的数量，可以进行多表连接，只是表的连接需要一个一个的连（A join B join C …

#1、交叉连接

交叉连接：cross join，不需要连接条件的连接

*交叉连接产生的结果就是笛卡尔积
###左表的每一条记录都会与右表的所有记录连接并保留
*交叉连接没有实际数据价值，只是丰富了连接查询的完整性

eg：交叉连接t_12,t_13

select * from t_12 cross join t_13; # t_12,t_13
1

#2、内连接

内连接：[inner] join，将两张表根据指定的条件连接起来，严格连接

*内连接是将一张表的每一条记录去另外一张表根据条件匹配
##匹配成功：保留连接的数据
##匹配失败：都不保留
*内连接语法： 左表 join 右表 on 连接条件

steps
1、确定需要从多张表中获取数据组成记录

2、确定连接的要求是保留连接成功的，不成功的数据不要

3、使用内连接

eg：1、设计学生表和专业表：学生对专业多对一关系

# 学生表
create table t_14(
	id int primary key auto_increment,
    name varchar(50) not null,
    course_no int
)charset utf8;
insert into t_14 values(null,'Student1',1),
(null,'Student2',1),
(null,'Student3',2),
(null,'Student4',3),
(null,'Student5',1),
(null,'Student6',default);

# 专业表
create table t_15(
	id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;
insert into t_15 values(null,'Computer'),(null,'Software'),(null,'Network');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2、获取已经选择了专业的学生信息，包括所选专业

# 学生和专业在两个表中，所以需要连表
# 学生必须有专业，而专业也必须存在，所以是内连接
# 连接条件：专业编号
# 两张表有两个字段冲突：id、name，所以需要使用别名
select t_14.*,t_15.name as course_name from t_14 inner join t_15 on t_14.course_no = t_15.id;

# 表名的使用也可以使用别名
select s.*,c.name as c_name from t_14 as s inner join t_15 c on s.course_no = c.id;
1
2
3
4
5
6
7
8

#3、外连接

外连接：outer join，是一种不严格的连接方式

*外连接分为两种
1左外连接（左连接）：left join
2右外连接（右连接）：right join

*外连接有主表和从表之分
1左连接：左表为主表
2右连接：右表为主表

*外连接是将主表的记录去匹配从表的记录
1匹配成功保留
2匹配失败（全表）：也保留，只是从表字段置空

steps
1、确定进行连表操作

2、确定要有数据保护，即表中数据匹配失败也要保留

3、确定主从表

4、选择对应外连接

eg：查出学生信息，包括所在班级

# 主要数据是学生，而且是全部学生：外连接、且学生表是主表
select s.*,c.name c_name from t_14 s left join t_15 c on s.course_no = c.id;
1
2

查出所有班级的所有学生

# 主表是班级
select s.*,c.name c_name from t_14 s right join t_15 c on s.course_no = c.id;
1
2

#4、自然连接

自然连接：natural join，是一种自动寻找连接条件的连接查询

*自然连接不是一种特殊的连接方式，而是一种自动匹配条件的连接

*自然连接包含自然内连接和自然外连接
1自然内连接：natural join
2自然外连接：natural left/right join

*自然连接条件匹配模式：自动寻找相同字段名作为连接条件（字段名相同）

steps
1、需要进行连表查询结果

2、连表查询的表字段能够直接关联（字段名字相同：非常高的表结构设计）

3、选择合适的连接方式：内连接 or 外连接

4、使用自然连接

eg：1、自然连接14和15

select  * from t_14 natural join t_15;
1

2、自然连接是不管字段是否有关系的，只管名字是否相同：如果想要自然连接成功，那么字段的设计就必须非常规范

create table t_16(
	s_id int primary key auto_increment,
    s_name varchar(50) not null,
    c_id int comment '课程id'
)charset utf8;
insert into t_16 select * from t_14;

create table t_17(
    c_id int primary key auto_increment,
    c_name varchar(50) not null unique
)charset utf8;
insert into t_17 select * from t_15;

# 自然连接：条件只有一个相同的c_id
select * from t_16 natural join t_17;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

#5、using关键字

using关键字：连接查询时如果是同名字段作为连接条件，using可以代替on出现（比on更好）

*using是针对同名字段（using(id) === A.id = B.id）
*using关键字使用后会自动合并对应字段为一个
*using可以同时使用多个字段作为条件

steps
1、需要进行连表进行数据查询

2、两个表的连接条件字段同名

3、使用using关键字作为连接条件

eg：查询16中学生信息，包括班级名字

select s.*,c.c_name from t_16 s left join t_17 c using(c_id);
select * from t_16 s left join t_17 c using(c_id);
1
2

三、子查询

子查询：sub query，通过select查询结果当做另外一条select查询的条件或者数据源

例：想查出某个专业的所有学生信息
1查询的目标是学生表
2查询的条件在专业表

#1、子查询分类

子查询分类：根据子查询出现的位置或者产生的数据效果分类

*位置分类
1——from子查询：子查询出现在from后做数据源
2——where子查询：子查询出现在where后做数据条件
*按子查询得到的结果分类
1——标量子查询：子查询返回的结果是一行一列（一个数据）
2——列子查询：子查询返回的结果是一列多行（一列数据）
3——行子查询：子查询返回的结果是一行多列
4——表子查询：子查询返回的结果是一个二维表
5——exists子查询：子查询返回的结果是布尔结果（验证型）
*子查询都需要使用括号 () 进行包裹，必要时需要对子查询结果进行别名处理（from子查询）

#2、标量子查询

标量子查询：子查询返回的结果是一行一列，一个值

标量子查询是用来做其他查询的条件的

steps
1、确定要从一张表中获取数据（可以是多张）

2、确定查询条件在当前查询表中无法实现但是可以从其他表中精确获得（只有一个）

3、使用标量子查询

eg：获取Computer专业的所有学生

# 数据目标：学生表t_16
# 条件：专业名字，不在t_16中，但是t_16中的专业id可以通过专业名字在另外一张表精确获得（一个值）

select * from t_16 where c_id = (select c_id from t_17 where c_name = 'Computer');
1
2
3
4

#3、列子查询

列子查询：子查询返回的结果是一列多行

列子查询通常是用来做查询条件的

steps
1、确定要从一张表中获取数据（可以是多张）

2、确定查询条件在当前查询表中无法实现但是可以从其他表中精确获得（一个字段多个数据）

3、使用列子查询

eg：获取所有有学生的班级信息

# 数据获取目标是班级信息
# 数据获取条件是在学生表中的班级id，是多个

select * from t_17 where c_id in (select distinct c_id from t_16 where c_id is not null);
1
2
3
4

#4、行子查询

行子查询：子查询返回的结果是一行多列

*行子查询需要条件中构造行元素（多个字段组成查询匹配条件）(元素1,元素2,…元素N)
*行子查询通常也是用来作为主查询的结果条件

steps
1、确定获取数据的条件不只是一个字段

2、确定数据条件的来源不在当前表中（也可以在当前表），但是可以通过条件精确获取到（一行多列）

3、使用行子查询

eg：获取学生表中性别和年龄都与弥勒相同的学生信息

# 查询条件有多个：性别和年龄
# 数据的条件的来源在另外一张表中

# 解决思路：两个标量子查询
select * from t_11 where gender = (select gender from t_13 where name = '弥勒') and age = (select age from t_13 where name = '弥勒');

###分析：以上查询解决了问题但是用到了两次子查询（效率降低），而且查询语句是一样的，只是字段不一样，可以使用行子查询解决
# 构建条件行元素(gender,age)
select * from t_11 where (gender,age) = (select gender,age from t_13 where name = '弥勒');
1
2
3
4
5
6
7
8
9

#5、表子查询

表子查询：子查询返回的结果是多行多列（二维表）

*表子查询多出现在from之后当做数据源（from子查询）
*表子查询通常是为了想对数据进行一次加工处理，然后再交给外部进行二次加工处理

steps
1、需要查询的数据通过一次SQL查询不能直接搞定（可能顺序关系导致）

2、如果先把结果加工后（多行多列），外部再来一层结果查询加工可以完成目标

3、使用表子查询

eg：获取学生表中每个班级里年龄最大的学生信息（姓名、年龄、班级名字），然后按年龄降序排序显示

# 尝试直接解决
select any_value(name),max(age) m_age,class_name from t_13 group by class_name order by m_age desc;
1
2

分组统计中any_value取的是分组后的第一条记录数据（犬夜叉），而我们要的是最大

# order by必须在group by之前解决：就要想办法让order by在group by之前而且不在同一条select指令中（同一条无解）
# 必须使用子查询解决在不用SQL中的问题，而子查询的结果应该是全部记录信息，所以应该是表子查询，而且是数据源

select any_value(name),max(age),class_name from 
(select name,age,class_name from t_13 order by age desc) as t
group by class_name;
1
2
3
4
5
6

依然无效：原因是MySQL7以后若要子查询中的order by生效，需要像联合查询那样，让子查询带上limit

select any_value(name),max(age),class_name from 
(select name,age,class_name from t_13 order by age desc limit 99999) as t
group by class_name;
1
2
3

因为order by在子查询的时候已经对结果进行过排序了，所以分组统计后最终结果也就不用再进行排序了，如果需要再进行排序，只要在最终结果后排序即可

select any_value(name),max(age) m_age,class_name from 
(select name,age,class_name from t_13 order by age desc limit 99999) as t
group by class_name order by m_age;
1
2
3

#6、exists子查询

exists子查询：代入查询，将主表（外部查询）的每一行代入到子表（子查询表）进行校验

*子查询返回的结果是布尔结果
1成功返回true
2失败返回false
*exists子查询通常是作为where条件使用
where exists(子查询)

steps
1、确定查询的数据来自主表

2、确定条件是需要去子表（其他表）进行验证：不需要去子表获取数据之类的

3、使用exists子查询

eg：获取所有有学生的班级信息t_17

# 获取的数据是班级表t_17
# 班级是否有学生需要在t_16中确认，并不需要t_16提供任何数据显示

select * from t_17 c where exists(select c_id from t_16 where c.c_id = c_id);
1
2
3
4

#7、比较方式

比较方式：在子查询中可以使用一些特定的比较方式

特定的比较方式都是基于比较符号一起使用

all：满足后面全部条件

all(结果集)：数据要大于结果集中的全部数据
any：满足任意条件

= any(结果集)：数据只要与结果集中的任何一个元素相等
some：满足任意条件（与any完全一样）

结果集：可以是直接的数据也可以是子查询结果（通常是列子查询）

eg：找出t_11表中与t_13表中年龄相同的信息

# 数据获取在t_11表
# 数据条件在t_13表

# 解决方案1：使用in列子查询
select * from t_11 where age in (select distinct age from t_13);

# 解决方案2：使用exists子查询
select * from t_11 t1 where exists(select id from t_13 where t1.age = age);

# 解决方案3：使用any或者some匹配（列子查询）
select * from t_11 where age = some(select age from t_13);
1
2
3
4
5
6
7
8
9
10
11

#8、总结

1、子查询通常使用较多的是标量子查询、列子查询和exists子查询

2、子查询的效率是比连接查询的效率要低的，要适当选择使用
*子查询是在主表的每一次记录匹配时都会执行一次（where子查询）
*主表数据大，子表数据小：影响较小
*主表数据小，子表数据大：影响较大
*from子查询因为只执行一次，影响不大
3、理论上来讲，不限制子查询的嵌套，但是考虑到效率的降低，不建议使用子查询嵌套