MySQL 表关系及多表操作（联合查询、连接查询、子查询）

表关系

表关系：一个表代表一个实体，实体之间都有关联关系的

• 根据 范式的要求 来设计表关系，减少数据冗余
• 根据 实际需求 来设计表关系，提升访问效率

1、表关系是体现数据实际联系的方式

2、表关系的设计好坏直接关联数据维护的准确性、有效性

3、良好的数据库设计有助于后期程序开发

• 一对一关系
• 一对多关系（多对一）
• 多对多关系

一对一关系

一对一关系：一张表中的一条记录与另外一张表中有且仅有一条记录有关系

• 一对一关系通常是用来将一张原本就是一体的表拆分成两张表
  • 频繁使用部分：常用字段
  • 不常使用部分：生僻字段
  • 使用相同的主键对应
• 一对一关系设计较多使用在优化方面
• 因为一对一关系表通常有相同信息作为匹配条件，所以查询方式也比较方便
  • 连表操作：利用共有信息进行匹配，一并查出一条完整信息
  • 多次查询：利用共有信息进行多表查询，利用程序组合成一条完整信息

步骤：

• 一张表的数据字段较多且数据量较大
• 表中有部分字段使用频次较高，而另一部分较少使用
• 将常用字段和不常用字段拆分成两张表，使用同样的主键对应

示例

1、学生信息表

学号（P）	姓名	性别	年龄	身高	体重	籍贯	政治面貌
1	张飞	男	20	178	160	蜀	农民
2	武则天	女	21	168	110	唐	党员

• 以上数据表信息字段较多
• 姓名、性别、年龄属于常用字段，频繁查询

2、一对一关系设计

• 将常用字段取出，与学号组合成一张常用表
• 将不常用字段取出，与学号组合成一张不常用表
• 表与表数据对应关系：基于学号（唯一）是一对一关系

常用表

学号（P）	姓名	性别	年龄
1	张飞	男	20
2	武则天	女	21

不常用表

学号（P）	身高	体重	籍贯	政治面貌
1	178	160	蜀	农民
2	168	110	唐	党员

一对多关系

一对多关系：也叫多对一关系，一张表中的一条记录与另外一张表的多条记录对应，反过来另外一张表的多条记录只能对应当前表的一条记录

• 一对多关系是实体中非常常见的一种关系，实体设计时也应用非常多
• 一对多关系的核心解决方案是如何让记录能够正确匹配到另外表中的数据
  • 一表设计：一表记录在另外一张表中有多条记录，所以无法记录多个字段（违背1NF）
  • 多表设计：多表记录在另外一张表中只有一条记录，可以设置字段记录对应的主属性（通常主键）
• 一对多关系设计是将实体的关系在表结构层进行强制关联（没有关系程序层也可以控制，但是会非常麻烦）
  • 便于连表操作
  • 便于数据分析统计（数据库层）
• 一对多关系的核心在于分析出表与表之间的关系

步骤：

• 确定实体间的关系为一对多（多对一）关系
• 在多表中增加一个字段记录一表中对应的主属性

示例

1、老师与学科间的关系：一个老师只能教一个学科，但是一个学科有多个老师教授，学科与老师形成的关系就是一对多（反过来老师与学科的关系就是多对一关系）

老师表（多表）

老师ID（P）	姓名	年龄	性别
1	张老师	35	男
2	李老师	34	女
3	王老师	30	男

学科表（一表）

学科ID（P）	名字	课时长度
1	PHP	600
2	Java	800

• 以上两个实体没有体现彼此之间的关联关系
• 实际上讲师与学科肯定是有关联的

2、在多表（讲师）中增加字段维护一表（学科）的关系型，形成多对一关系

老师ID（P）	姓名	年龄	性别	学科ID
1	张三	35	男	1
2	李四	34	女	1
3	王五	30	男	2

• 基于新的讲师表与学科表产生了关联关系（多对一）
• 基于讲师表可以知道讲师所属学科
• 基于学科ID可以统计出不同学科的讲师数量

多对多关系

多对多关系：一张表中的一条记录对应另外一个表中多条记录，反过来一样

• 多对多关系在实体中是最常见的关系
• 多对多关系无法在自身表中维护对应表关系（违背1NF），需要通过第三方表来实现将多对多关系变成多个多对一关系
  • 设计一个中间表：记录两张表之间的对应关系（主属性）
  • 中间表与其他表都是多对一的关系
• 多对多关系是否建立主要看业务上是否存在数据要求，如果不存在数据需求，那么就没必要刻意设计

步骤：

• 确定实体间的关系为多对多关系
• 设计中间表，记录两张表的对应关系

示例

1、老师与学生之间的关系：一个老师会教授多个学生，一个学生也会听多个老师的课，所以实体关系是多对多关系

老师表

老师ID（P）	姓名	年龄	性别
1	张老师	35	男
2	李老师	34	女
3	王老师	30	男

学生表

学生ID（P）	姓名	年龄	性别
1	小明	15	男
2	小红	14	女
3	小萌	14	女

• 以上实体没有从结构上体现表之间的关系

2、设计一个中间表：老师与学生关系表，将老师与学生的对应关系对应上（多对一）

中间表

ID（P）	学生ID	老师ID
1	1	1
2	1	2
3	1	3
4	2	1
5	2	2
6	2	3
7	3	1
8	3	3

• 中间表与老师表的对应关系是多对一：通过老师ID可以找到每一个上过课的老师
• 中间表与学生表的对应关系是多对一：通过学生ID可以找到每一个听过课的学生
• 老师找学生：老师表–>中间表（找出老师对应的学生ID）–>学生表（找出学生ID对应的学生信息）
• 学生找老师：学生表–>中间表（找出学生对应的老师ID）–>老师表（找出老师ID对应的老师信息）

总结

1、表关系的设计是要遵循范式规范作为前提

2、表关系的设计是根据实体关系以及业务需求进行设计

• 一对一关系：主要在于优化访问效率、传输效率
• 一对多关系：在于如何让实体间的联系在结构中体现（后期可以使用外键进行相关约束保证数据的有效性）
• 多对多关系：与一对多关系一样，清晰明了的体现实体间的结构联系

3、在设计数据库的时候，要严格使用表关系来进行实体关联设计

• 基于表关系来实现实体间的关联控制
• 在设计和应用表的时候提炼对表关系的认知
• 能够熟练的基于业务控制数据库的关系

MySQL多表操作

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

• 多表操作是实际开发时经常遇到的解决问题的方案
• 多表操作能够在数据库层就实现大量数据的组合或者筛选

联合查询

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

• 联合查询是针对查询结果的合并（多条select语句合并）
• 联合查询语法： select 查询【决定字段名字】union 查询选项 select 查询...
• 联合查询要求：联合查询是结果联合显示
  • 多个联合查询的字段结果数量一致
  • 联合查询的字段来源于第一个查询语句的字段
• 查询选项：
  • all：保留所有记录
  • distinct：保留去重记录 （默认）

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

create table t_2 like t_1;

insert into t_2 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_2与t_1结构一样，可以理解为因为数据量大拆分到了两个表中

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

select * from t_1
union
select * from t_2;

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

select * from t_1
union 
select * from t_1;

select * from t_1
union all
select * from t_1;

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

select name from t_1
union all
select age from t_1;

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

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

select * from t_1
union all
select * from t_2
where gender = '女';

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

联合查询排序

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

• order by本身是对内存结果进行排序，union的优先级高于order by，所以order by 默认是对union结果进行排序
• 联合排序需要区分排序的内容是 select结果 还是 union结果
  • union结果：在最后使用排序即可
  • select结果：需要针对select使用排序
    • ① select必须使用括号包裹（括号里使用order by）
    • ② select里的排序，order by必须配合limit才能生效（limit一个足够大的数值即可）

1、将t_1和t_2表的结果使用年龄降序排序

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

2、t_1表按年龄降序排序，t_2表按年龄升序排序

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

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

连接查询

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

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

交叉连接

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

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

1、交叉连接t_1和t_2表

select * from t_1 cross join t_2; # t_1,t_2

内连接

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

• 内连接是将一张表的每一条记录去另外一张表根据条件匹配
  • 匹配成功：保留连接的数据
  • 匹配失败：都不保留
• 内连接语法： 左表 join 右表 on 连接条件
• 内连接匹配规则就是：必须保证左表和右表同时存储连接关系，这样的数据才会保留
• 可以没有on条件，那么得到的结果就是交叉连接（笛卡尔积），无意义
• on关键字可以换成where，结果是一样（但是不建议使用）

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

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

# 专业表
create table t_4(
	id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;
insert into t_4 values(null,'Computer'),(null,'Software'),(null,'Network');

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

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

# 表名的使用也可以使用别名
select s.*,c.name as c_name from t_3 as s inner join t_4 c on s.course_no = c.id;

• 字段冲突的话在MySQL里倒是不影响，只是会同时存在，但是后续其他地方使用就不方便了

原理分析：

外连接

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

• 外连接分为两种
  • 左外连接（左连接）：left join
  • 右外连接（右连接）：right join
• 外连接有主表和从表之分
  • 左连接：左表为主表
  • 右连接：右表为主表
• 外连接语法： 左表 left/right join 右表 on 连接条件
• 外连接是将主表的记录去匹配从表的记录
  • 匹配成功保留
  • 匹配失败（全表）：也保留，只是从表字段置空
• 外连接与内连接的区别在于数据匹配失败的时候，外连接会保留一条记录
  • 主表数据保留
  • 从表数据置空
• 外连接必须使用on作为连接条件（不能没有或者使用where替代）

1、查出所有的学生信息，包括所在班级（左连接）

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

2、查出所有班级里的所有学生（右连接）

# 主表是班级
select s.*,c.name c_name from t_3 s right join t_4 c on s.course_no = c.id;

自然连接

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

• 自然连接不是一种特殊的连接方式，而是一种自动匹配条件的连接。是基于内连接、外连接和交叉连接实现自动条件匹配而已
  • 没有条件（没有同名字段）：交叉连接
  • 有条件：内连接/外连接（看关键字使用）
• 自然连接包含自然内连接和自然外连接
  • 自然内连接：natural join
  • 自然外连接：natural left/right join
• 自然连接条件匹配模式：自动寻找相同字段名作为连接条件（字段名相同）

1、自然连接t_3和t_4表

select  * from t_3 natural join t_4;

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

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

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

# 自然连接：条件只有一个相同的c_id
select * from t_5 natural join t_6;

• 自然连接会将同名条件合并成一个字段（数据一样）

using关键字

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

• using是针对同名字段（using(id) === A.id = B.id），简化同名条件字段的连接条件行为
• using关键字使用后会自动合并对应字段为一个
• using可以同时使用多个字段作为条件
• using关键字与自然连接相似，但是比自然连接灵活，可以指定有效的同名连接条件，忽略无效的同名字段

1、查询t_5中所有的学生信息，包括所在班级名字

select s.*,c.c_name from t_5 s left join t_6 c using(c_id);
select * from t_5 s left join t_6 c using(c_id);

子查询

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

• 子查询就是能够将一些具有先后顺序的查询组装到一个查询语句中，从而节省操作的过程，降低复杂程度

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

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

1、标量子查询：子查询返回的结果是一行一列（一个值）

• 标量子查询是用来做其他查询的条件的
• 标量子查询通常用简单比较符号来制作条件的

示例：获取Computer专业的所有学生

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

select * from t_5 where c_id = (select c_id from t_6 where c_name = 'Computer');

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

• 列子查询通常是作为外部主查询的条件，而且是使用in来进行判定

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

# 数据获取目标是班级信息
# 数据获取条件是在学生表中的班级id，是多个
select * from t_6 where c_id in (select distinct c_id from t_5 where c_id is not null);

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

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

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

# 构建条件行元素(gender,age)

select * from t_2 where (gender,age) = (select gender,age from t_2 where name = '弥勒');

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

• 表子查询多出现在from之后当做数据源（from子查询）
• 表子查询通常是为了想对数据进行一次加工处理，然后再交给外部进行二次加工处理
• 表子查询出现的业务：
  • 一条select指令中所用到的子句顺序不能满足查询条件
  • 数据的来源可能是多张数据表
• 特别注意：在MySQL7以后，子查询中使用的 order by子句需要配合limit才会生效

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

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

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

解决方案：要是在分组之前将所有班级里的学生本身是降序排序，那么分组的第一条数据就是满足条件的数据。但是问题是：order by必须出现在 group by之后，如何解决？

# 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_2 order by age desc) as t
group by class_name;

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

select any_value(name),max(age),class_name from 
(select name,age,class_name from t_2 order by age desc limit 99999) as t
group by class_name;

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

• 子查询返回的结果是布尔值

  • 成功返回true
  • 失败返回false

• exists子查询通常是作为where条件使用

  • where exists(子查询)

• exists子查询通常用来解决那种不需要数据但是需要去表中确认关系的查询问题

• 在exists子查询中尽量少的选择字段（不建议使用*），因为都是无价值的

示例：获取所有有学生的班级信息t_6

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

select * from t_6 c where exists(select c_id from t_5 where c.c_id = c_id);

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

• 特定的比较方式都是基于比较符号一起使用
• all： 满足后面全部条件
  • > all(结果集)：数据要大于结果集中的全部数据
• any： 满足任意条件
  • = any(结果集)：数据只要与结果集中的任何一个元素相等
• some： 满足任意条件（与any完全一样）
• 结果集：可以是直接的数据也可以是子查询结果（通常是列子查询）
• 比较方式其实很多都可以实现替代，越精准的数据匹配方式效率就越高

示例：找出t_1表中与t_2表中年龄相同的信息

# 数据获取在t_0表
# 数据条件在t_2表

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

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

# 解决方案3：使用any或者some匹配（列子查询）
select * from t_1 where age = some(select age from t_2);