MySQL05-数据操作之多表查询

1. 多表关系

数据库中多表间的关系有一对多/多对一、多对多、一对一三类。

1.1 一对多/多对一

一对多/多对一的关系指 A 表的一行对应 B 表的多行， B 表的一行只对应 A 表的一行，比如一个班级可以有多个学生，一个学生却只能有一个班级。

案例：
部门与员工、班级与学生、商品分类与商品

外键实现：
在多的一方建立外键，指向一的一方的主键。

数据特点：

添加数据时，主表可以随意添加，从表添加数据必须依赖主表，受主表限制；
删除数据时，如果主表某一行的数据受到从表的依赖，则不能删除，从表数据可以随意删除，如果一定要删除主表数据，应该先删除有关联得从表数据后再删除；

1.2 多对多

多对多的关系指 A 表的一行对应 B 表的多行，B 表的一行也对应 A 表的多行，比如一个学生可以选多门课程，一门课程也可以供多个学生选修。

案例：
学生与课程

外键实现：
建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

数据特点：

添加数据时，先添加两张父表记录，再添加中间表记录；
删除数据时，先删除中间表记录，再删除两张父表记录；

1.3 一对一

一对一的关系指 A 表的一行对应 B 表的一行，反之也成立，多用于单表拆分，即将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率，具有一对一关系的两张表是可以合并成一张表的。

案例：
用户与用户详情

外键实现：

在任意一方加入外键，关联另外一方的主键，并且给外键设置唯一约束；
共享主键，即让两张表的主键内容一致，两张表共用一个主键（十分不推荐）；

2. 多表查询概述

当直接对多张表进行合并查询时，查询的数据结果将会是这几张表的笛卡尔积，假如 A 表有 3 条数据，B 表有 4 条数据，结果就会有 3 * 4 = 12 条数据，也即会展示所有组合结果。

# 合并查询，会展示两张表的笛卡尔积
select * from A表, B表;

在多表查询时，为了获得真正需要的数据，就要消除无效的笛卡尔积，由此就有了以下多表查询方法：

多表查询
├─ 连接查询
│	├─ 内连接查询
│	│	├─ 隐式内连接查询
│	│	└─ 显式内连接查询
│	├─ 外连接查询
│	│	├─ 左外连接查询
│	│	└─ 右外连接查询
│	└─ 自连接查询
└─ 子查询

3. 内连接查询-DQL

所谓内连接(inner join)，就是在表关系的笛卡尔积(Cartesian product) 数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录，相当于查询两张表的交集部分。根据不同的语法形式，内连接查询分为隐式内连接查询(SQL86标准)和显式内连接查询(SQL92标准)。

3.1 隐式内连接查询

隐式内连接，就是使用 where 条件，消除不符合条件的无用数据，也即消除笛卡尔积的无用数据，其基本语法如下：

select 字段1,字段2...
from 表1,表2...
where 连接条件/筛选条件

# 例如，查询 student 和 score 表中的学生姓名及分数
select stuname,score
from student,score
where student.stuid = score.stuid;

3.2 显式内连接查询

显式内连接，就是使用连接关键词 join 显式连接多个表数据，显式的性能比隐式高，其基本语法如下：

select 字段1,字段2...
from 表1 [inner] join 表2 on 连接条件
[inner] join 表3 on 连接条件
[where 筛选条件]

# 例如，查询 student 和 score 表中的学生姓名及分数
select stuname,score
from student join score on student.stuid = score.stuid;

3.3 内连接注意事项

隐式内连接中，where 子句后面往往首先是多表之间的连接条件，需要的话也可用 and 运算符将筛选条件与之联合，where 子句之后的筛选条件语法与单表查询语法类似；
隐式内连接中，where 子句后面可以有 GROUP BY、HAVING、ORDER BY 等子句；
显式内连接中，[inner] join 的 inner 可以缺省，仅 join 就代表内连接；
显式内连接中，每个 join 子句之后必须有 on 子句，on 子句用来说明两表之间的内连接关系；
显式内连接中，where 子句在此仅用于控制数据筛选条件的限定，用法与表单查询类似，后面也可以有 GROUP BY、HAVING、ORDER BY 等子句；
显式内连接查询比隐式内连接查询应用更广泛；
内连接条件在很多情况下发生在有关系的数据表主外键之间；
多表查询时，如果关联的两个数据表中存在同名字段，则同名字段前必须加表名限定，其格式为 表名.字段名，此时为了书写方便，往往会给表取一个别名，该别名仅在当前 select 语句内部有效；
在连接查询中常用 表名.字段名 的形式引用字段，主要是引用同名字段的需要，再多表查询中，对于非同名字段也建议采用这种形式，这将有利于提升查询效率；

4. 外连接查询-DQL

内连接是将满足条件的记录进行连接，不满足条件的记录是被排除在结果集之外的，如果需要的数据还涉及不满足条件的记录，就需要进行外连接查询。MySQL目前不支持全外连接查询，但可以用其他方式替代解决，如可通过联合多个查询的方式达到全外连接查询的效果。外连接查询分为左外连接查询和右外连接查询，左外连接指查询左表所有数据，以及两张表交集部分数据，右外连接指查询右表所有数据，以及两张表交集部分数据。

4.1 左外连接查询

左外连接时以左表为基本表，左表的内容全部显示，根据连接条件，右表与左表匹配的内容正常连接(中间重合部分)，无法匹配的内容则用空值与左表连接，其基本语法如下：

select 字段1,字段2...
from 左表 left [inner] join 右表 on 连接条件
[where 筛选条件]

# 例如，查询 student 和 score 表中的学生姓名及分数
select stuname,score
from student left join score on student.stuid = score.stuid;

4.2 右外连接查询

右外连接与左连接查询语法相同，只是以右表为基本表，右表的内容全部显示，根据连接条件，左表与右表匹配的内容正常连接(中间重合部分)，无法匹配的内容则用空值与右表连接，其基本语法如下：

select 字段1,字段2...
from 左表 left [inner] join 右表 on 连接条件
[where 筛选条件]

# 例如，查询 student 和 score 表中的学生姓名及分数
select stuname,score
from score right join student on student.stuid = score.stuid;

4.3 外连接注意事项

左外连接和右外连接的形式和作用基本一样，唯一区别就是所关联数据源的顺序不同，两种类型的外连接查询语句可互换，具体使用哪种形式没有特别要求，根据方便程度决定即可，一般使用左外连接更多一点；
外连接查询时可以通过 where 子句筛选数据，where 子句后面可以有 GROUP BY、HAVING、ORDER BY 等子句；
外连接结果集是基本表的全部内容加上左右表的重复内容，也就是说外连接结果集包含内连接结果集；
左右外连接查询时，将左右表的位置互换，并更改 left join 或 right join 为对方，就可实现左右外连接互换；

5. 自连接查询-DQL

连接操作不仅可以在两个数据表之间进行，也可以是一个数据表与自己进行连接，称为表的自连接，也称为自身连接，自连接查询的语法同内连接查询的语法一样，只是多个数据表都是由同一个物理表形成的，自连接查询必须使用表别名，其基本语法如下：

select 表别名1.字段1, 表别名1.字段2... 表别名2.字段1, 表别名2.字段2...
from 表1 [as] 表别名1 [inner] join 表2 [as] 表别名2 on 连接条件
[where 筛选条件]

# 例如，查询 score 表中两门课成绩都大于 90 的学生学号及分数
select distinct t1.stuid, t1.score 成绩一, t2.score 成绩二
from score t1 join score t2 on t1.stuid = t2.stuid
where t1.cid != t2.cid and t1.score > 90 and t2.score > 90;

注意：

自连接是一种特殊的内连接，只要给数据表取两个不同的别名，就可以在逻辑上将一个物理表当成两个表来使用；
在自连接时，所有列都是两表共有的同名列，对列的运用必须使用 别名.列名 的形式，否则出现二义性；
自连接查询既可以是内连接查询，也可以是外连接查询，查询语法也可以随查询目的在内连接和外连接查询中选择；

6. 联合查询-DQL

联合查询又叫合并查询、连接查询，是用 union 或 union all 语句将两条或两条以上的 select 语句的查询结果集合并，其基本语法如下：

select 字段1,字段2...
from 表1
union / union all
select 字段1,字段2...
from 表2;

# 例如，查询 student 表中学生姓名和学号
select stuid
from student
union all
select stuname
from student;

注意：

UNION ALL 将两个查询结果合并，对于重复的数据不做去重操作；
UNION 将两个查询结果合并，对于重复的数据会做去重操作；
被合并的所有查询列数必须保持一致，对应列的数据类型必须兼容；
联合查询比使用 or 效率高，不会使索引失效；

7. 子查询-DQL

在 SQL 中，一个 select-from-where 查询语句称为一个查询块，将一个查询块嵌套在另一个查询块中的查询称为嵌套查询，嵌套在查询块中的内层查询称为子查询，相应的外层查询称为父查询。SQL 也支持多层嵌套子查询，即一个子查询中还可以嵌套其他子查询。

根据子查询中查询到的结果集规模的不同，可以将子查询分为单值子查询、列子查询、行子查询和表子查询。

子查询使用的位置可以在 where(having)、from 和 select，最常用在父查询的 where 子句或 having 子句中，与比较运算符或者逻辑运算符一起构成 where 筛选条件或 having 筛选条件。

7.1 标量子查询

当子查询的结果集为单个值（单行单列）时，称之为标量子查询，也叫单值子查询。标量子查询中，父查询常用 =、<=>、<、>、<=、>=、!=、<>、!<、!> 等比较运算符与子查询进行连接，其基本语法如下：

select 字段1, 字段2 ...
from 表2
where 字段3 比较运算符 (单值子查询);

# 例如，联合 class 和 teacher 表查询软件1班的班主任的姓名、性别和年龄
select name, sex, age
from teacher
where name = (select teaid from class where classname = '软件1班');

注意：

标量子查询一般表现为父查询与子查询之间用 =、<=>、<、>、<=、>=、!=、<>、!<、!> 等比较运算符进行连接，而且通常子查询的位置在关系比较运算符的右侧；
子查询内容必须用括号 () 包围，否则语法会出错；
子查询一般都可以通过连接查询实现相同功能；

7.2 列子查询

当子查询结果即为一列值（单列多行）时，称为列子查询，列子查询作为外层 select 语句的查询条件，一般通过 in、any、some 和 all 等操作符进行关联，不能用关系比较运算符关联，其基本语法如下：

select 字段1, 字段2 ...
from 表2
where 字段3 [in any some all] (列子查询);

# 例如，联合 student 和 score 表查询软件1班全体学生的姓名、学号、课程编号和分数
select stuid, cid, score from score where stuid in (select stuid from student where class = '软件1班');

注意：

列子查询不能用关系比较运算符与外层查询关联；
列子查询常用操作符及其含义如下：

操作符	描述
IN	在子查询返回的指定集合范围内，多选一
NOT IN	不在子查询返回的指定集合范围内
ANY	子查询返回列表中，满足任意一个值即可
ALL	子查询返回列表的所有值都必须满足
SOME	与ANY等同，使用SOME的地方都可以使用ANY

7.3 行子查询

当子查询结果即为一行值（单行多列）时，称为行子查询，但是这种情况一般很少出现。其基本语法如下：

select 字段1, 字段2 ...
from 表2
where (字段3, 字段4) [= / < / > / IN / NOT IN] (行子查询); 

# 例如，在 student 中查询与学号 24030101 同岁同班的同学的姓名和学号
select name, stuid from student
where (age, classid) = (select age, classid from student where stuid = 24030101);

注意：

标量子查询一般表现为父查询与子查询之间用 =、<、>、in、not in 等进行连接；
行子查询中一般将子查询得到的数据作为一个整体进行处理或者与其他行进行比较；

7.4 表子查询

若子查询结果即为多行多列，则称为表子查询。在 select 语句中所有能使用数据源的地方都可以插入表子查询，表子查询比较常见的嵌入位置为 where 与 from 子句。其基本语法如下：

# 格式一：
select 字段1, 字段2 ...
from 表2
where (字段3, 字段4) [in any some all] (表子查询);
# 格式二：
select 字段1, 字段2
from (表子查询) as 表1别名 join 表2 as 表2别名 on 条件表达式;

# 例一，查询与与学号 24030101、24030102 同岁同班的同学的姓名和学号
select stuname, stuid from student where (age, classid) in
(select age, classid from student where stuid = '24030101' or  stuid = '24030102');
# 例二，查询各科目的考试成绩，包括科目名称、最高分、最低分、平均分
select c.cname, s.maxscore, s.minscore, s.avgscore
from (select cid, max(score) as maxscore, min(score) as minscore, avg(score) as avgscore
      from score group by cid) as s
left join course as c on c.cid = s.cid;

注意：

表子查询嵌入外层查询的 where 子句时，通常用 in、any、some 和 all 等集合运算符与外层查询关联；
表子查询嵌套在外层查询的 from 子句必然要与其他数据表进行连接，为了能在外层查询中引用该虚拟表及其内部各列，必须为虚拟表设置表别名，如果虚拟表内部有统计列或计算列，同样需要设置列别名，否则无法引用；

7.5 子查询注意事项

标量子查询、行子查询嵌入外层查询的 where 子句时，通常用关系运算符与外层查询关联，列子查询、表子查询嵌入外层查询的 where 子句时，通常用 in、any、some 和 all 等集合运算符与外层查询关联；
order by 子句不能用于子查询，只能对最外层的查询结果进行排序；
设计多层嵌套的子查询时建议与测试顺序一样，从最内层值查询开始，逐步向外层延伸；
子查询可以嵌套多层，但嵌套级别越多，效率越低，必要时可以通过连接查询来代替多层嵌套查询；
子查询外部的语句不仅可以是 SELECT 查询语句，还可以是 INSERT / UPDATE / DELETE / 的任何一个
几乎所有多表查询任务都可以通过嵌套查询或连接查询实现，在实际海量数据应用场景中，应该根据效率优先原则确定查询语句；

7.6 子查询的相关性与执行顺序

根据子查询的查询条件是否依赖父查询可以把子查询分为非相关子查询和相关子查询。若子查询不依赖父查询，则这类子查询称为不相关子查询，若依赖父查询，则称为相关子查询，不相关子查询的子查询可以单独执行，相关子查询的子查询不能单独执行。

7.6.1 非相关子查询

非相关子查询的子查询不依赖父查询，独立于外部查询，可以单独执行。

执行过程：

执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用；
执行外部查询，并显示整个结果；

示例：

# 例如，联合 class 和 teacher 表查询软件1班的班主任的姓名、性别和年龄
select name, sex, age
from teacher
where name = (select teaid from class where classname = '软件1班');

# 1. 首先执行子查询 select teaid from class where classname = '软件1班' ；得到软件1班班主任的 id
# 2. 然后将这个 id 传递给外部查询，作为其 where 子句的执行条件
# 3. 根据这个执行条件，找到需要的数据

7.6.2 相关子查询

相关子查询必须依赖父查询，其子查询不能返回独立的结果集，多数情况下是子查询的 WHERE 子句中引用了外部查询的表。

执行过程：

从外层查询中取出一个元组（一行数据），将元组相关列的值传给内层查询；
执行内层查询，得到子查询操作的值；
外查询根据子查询返回的结果或结果集得到满足条件的行；
然后外层查询取出下一个元组重复步骤 1-3，直到外层的元组全部处理完毕；

示例：

# 查询 score 表中分数高于该课程平均分的学生学号和成绩
select stuid, score from score as a
where score > (select avg(score) from score as b where a.cid = b.cid);

# 1. 假设 score 表中的第一条记录的 cid 的值为 2401，将其代入子查询中，使 a.cid 值为 2401
# 2. 由此子查询变为 select avg(score) from score as b where '2401' = b.cid
# 3. 执行上一步得到的子查询，结果为课程编号为 2401 的课程的平均分
# 4. 将该子查询值代回外部查询，变为 select stuid, score from score as a where score > '2401课程平均分'
# 5. 如果外部查询结果成立，则留下该次查询结果，否则舍弃，重复该过程，最后得到最终的结果集

8. 交叉连接查询-DQL

交叉连接是没有任何限制条件的连接，也称为笛卡尔连接，因为其产生的结果集与笛卡尔积范围一样，将产生两个数据源能连接的最大结果集，除了包含内连接，外连接的所有数据外，还有大量没有意义的数据，所以交叉连接几乎没有实际应用价值，其基本语法如下：

select 字段1,字段2...
from 表1 cross [inner] join 表2;

# 例如，将 score 和 student 表交叉连接后展示所有数据
select *
from score cross join student;

注意：

交叉连接查询中，join 关键字后面没有 on 子句，也就是不设连接条件；
交叉连接查询中，最大字段数（列数）= 左表字段数 + 右表字段数，最大记录数（行数）= 左表记录数 * 右表记录数；
交叉连接查询是从左表依次取出每一条记录，与右表中的全部记录逐个、无条件地连接，因此产生出两个数据表连接的最大结果集；

posted @ 2024-02-16 00:28 luomocn 阅读(74) 评论(0) 收藏举报

刷新页面返回顶部

luomo's blog

MySQL05-数据操作之多表查询

1. 多表关系

1.1 一对多/多对一

1.2 多对多

1.3 一对一

2. 多表查询概述

3. 内连接查询-DQL

3.1 隐式内连接查询

3.2 显式内连接查询

3.3 内连接注意事项

4. 外连接查询-DQL

4.1 左外连接查询

4.2 右外连接查询

4.3 外连接注意事项

5. 自连接查询-DQL

6. 联合查询-DQL

7. 子查询-DQL

7.1 标量子查询

7.2 列子查询

7.3 行子查询

7.4 表子查询

7.5 子查询注意事项

7.6 子查询的相关性与执行顺序

7.6.1 非相关子查询

7.6.2 相关子查询

8. 交叉连接查询-DQL

公告