自用笔记：MySQL——7.多表查询

云朵朵

DQL
一、多表关系

表结构之间的各种联系。

• 一对多（多对一）
  

在多的一方建立外键，指向一的一方主键。
eg:年级和班级，一个年级对应多个班级，一个班级只能归属一个年级（班级建立外键，关联年纪这个主键）。

• 多对多
  

建立中间表，表中至少包含两个外键，分别关联两方主键。
eg：学生和课程，一个学生可以选择多门课程，一个课程可以有多个学生学习（中间表建立两个外键，一个关联课程，一个关联学生）。

• 一对一
  

在任意一方加入外键，关联另外一方的主键，并设置外键为唯一的（unique）（类似于一对多）
常用于做单表拆分。
二、多表查询概述

1.笛卡尔积

笛卡尔积是指在数学中，两个集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积。）
2.查询方式

形如单表查询，表可以输入多个，但要有where进行匹配，消除掉无效的笛卡尔积
如果不满足where条件，就不会被显示。
三、多表查询分类

1.连接查询

1）内连接：相当于查询多张表的交集部分数据。

• 隐式内连接
  

select 字段列表 from 表1，表2 where 条件…;eg:查询同一个老师教授的学生的姓名、成绩。




多表查询时，多个表间用逗号分隔，并且在where条件查询时，字段名前要跟上表名。
如果表名复杂，可以简化表名


注意：如果起了别名，字段名必须加上别名才会显示，因为select语句先执行的from，也就是先改了表名。

• 显式内连接
  

select 字段列表 from 表1 [inner] join 表2 on 连接条件…；eg:查询同一个老师教授的学生的姓名、成绩。




inner可以省略。
2）外连接
左外连接：查询左表所有数据，以及交集部分的数据。
select 字段列表 from 表1 left [outer] join 表2 on 条件…；相当于查询左表（表1）的所有数据，包含表1 表2的交集部分的数据。
eg:查询score的所有信息，以及对应课程名称。






右外连接：查询右表所有数据，以及交集部分的数据。
select 字段列表 from 表1 right [outer] 表2 on 条件…；相当于查询右表（表2）的所有数据，包含表1 表2的交集部分的数据。
eg:查询course表的所有信息和所对应的得分。




注意：左连接和右连接可以相互转换。
3）自连接：当前表与自身的连接查询，自连接必须使用表别名。
select 字段列表 from 表1 别名1 join 表1 别名2 on 条件…;自连接查询，可以是内自连接查询（只包含交集），也可以是外自连接查询（单独的一张表）。
2.联合查询

union查询，就是把多次查询的结果合并起来，形成一个新的查询结果。
select 字段列表 from 表1…
union [all]
select 字段列表 from 表2…；注意：直接合并会出现重复值，去掉all就会把查复结果去掉。
查询 字段不同是不会进行合并的（要求字段列表和字段类型保持一致）。
3.子查询

sql语句中嵌套select 语句，成为嵌套查询，又称为子查询。
select * from t1 where column1 =(select column1 from t2);子查询外部的语句可以是insert/update/delete/select的任何一个。
根据子查询结果不同，可分为

• 标量子查询（子查询的结果为单个值）（常用> < =等符号）
  

eg：查询赵高老师学生的成绩信息。
a 查询赵高老师的课程代码
b 依据代码查询信息。


进行子查询

• 列子查询（子查询结果为一列）（常用in , not in ,any,some,all ）
  

操作符	描述
In	在指定范围内，多选一
not in	不再指定范围内
any	子查询返回列表中，有任意一个满足即可
some	与any等同，使用some的地方都可以是有any
all	子查询返回列表的所有值都必须满足

eg查询赵高和马悦老师的学生成绩。




（in 可替换成其他操作符）

• 行子查询（子查询结果为一行）
  

查询和马小燕部门和班级相同的同学

• 表子查询（子查询结果为多行多列）（主要用in）
  

查询和马小燕、马蓉 部门和班级相同的学生
（sdept 和sclass 要么和马小燕一样，要么和马蓉一样）


根据子查询位置可分为：where之后，from之后，select之后
注意：查询后的表可以作为一张表再次被链接查询。