MySQL多表操作

示例

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

作者表：id、用户名、密码

新闻表：id、标题、内容、发布时间、作者id

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

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

小结

1、多表操作是实际开发时经常遇到的解决问题的方案

2、多表操作能够在数据库层就实现大量数据的组合或者筛选

一、联合查询

学习目标：了解联合查询的现实意义，掌握联合查询的实际运用

• 联合查询
• 联合查询排序

1、联合查询

目标：了解联合查询的语法，掌握联合查询的作用

概念

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

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

select 查询【决定字段名字】
	union 查询选项
select 查询
...

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

步骤

1、确定要进行多个表数据的联合操作

• 表结构一致
• 数据汇总

2、确定数据的要求：全部保留 or 去重

3、使用联合查询

示例

1、创建一个表与t_40，并插入数据

create table t_42 like t_40;

insert into t_42 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班');

• t_42与t_40结构一样，可以理解为因为数据量大拆分到了两个表中

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

select * from t_40 
union
select * from t_42;

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

select * from t_40
union 
select * from t_40;

select * from t_40
union all
select * from t_40;

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

select name from t_40
union all
select age from t_40;

• 注意：如果数据不能对应，那么查询没有意义

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

select * from t_40 
union all
select * from t_42
where gender = '女';

• where只针对第二条select有效
• 若要全部有效，需要select都使用where

小结

1、union是负责将多次查询的结果统一拼凑显示

• 记录数增加
• 字段数不变（第一条SQL指令决定）

2、union常用方式

• 因为数据量大分表存储，然后统一查看或者统计
• 根据不同维度对数据进行筛选，然后统一查看或者统计

3、union默认是去重的，想要保留全部查询结果，需要使用union all

2、联合查询排序

目标：了解联合查询排序的概念，掌握联合查询排序的方法

概念

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

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

步骤

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

2、确定排序内容

• 针对union结果排序
• 针对union前的select结果进行排序

3、选择合适的排序方式

示例

1、将t_40和t_42表的结果使用年龄降序排序

select * from t_40
union all
select * from t_42
order by age desc; #针对的是整个union之后的结果

2、t_40表按年龄降序排序，t_42表按年龄升序排序

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

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

小结

1、联合排序需要区分排序的内容是select结果还是union结果

• union结果：在最后使用排序即可
• select结构：需要针对select使用排序
  • select必须使用括号包裹
  • select里的排序必须配合limit才会生效

二、连接查询

学习目标：理解连接查询的概念，掌握重点连接方式的使用，运用连接查询解决表关系的问题

• 交叉连接
• 内连接
• 外连接
  • 左外连接
  • 右外连接
• 自然连接
• using关键字

概念

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

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

小结

1、连接查询就是通过字段拼接，把两张表的记录变成一条记录：字段数量增加

2、连接查询的目的是将分散在不同表的数据组合到一起，方便外部使用数据

1、交叉连接

目标：了解交叉连接产生的概念，认识交叉连接的效果

概念

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

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

示例

交叉连接t_41和t_42表

select * from t_41 cross join t_42; # t_41,t_42

小结

1、笛卡尔积无意义，尽量避免出现

2、内连接

目标：理解内连接的概念和原理，掌握内连接的应用场景和解决方法

概念

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