mysql 表连接子查询_MySQL多表记录操作（子查询、连接）

朴灿烈づ我的快乐病毒、 2022-10-27 13:57 279阅读 0赞

子查询：

概念：是指出现在其他 SQL 语句内的 SELECT 语句，可以返回标量、一行、一列或子查询

例如：

SELECT \* FROM t1 WHERE col1 = ( SELECT col2 FROM t2 ) ;

其中，SELECT \* FROM t1 称为 Outer Query/Outer Statement(外层查询、外层声明)

SELECT col2 FROM t2，称为 SubQuery(子查询)

注：

⑴ 子查询指嵌套在查询内部，且必须始终出现在圆括号内

⑵ 子查询可以包含多个关键字或条件，

如 DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等

⑶ 子查询的外层查询可以是：SELECT、INSERT、UPDATE、SET 或 DO

分类：

⑴ 使用比较运算符的子查询

符号：=、>、=、<=、<>、！=、<=>

语法结构：

operand comparison\_operator subquery

⑵ 使用 ANY、SOME 或 ALL 修饰的比较运算符

说明：

当查询记录返回有多个结果的时候，可以使用以上3个关键字来修饰，其中，ANY 和

SOME 是等价的，只需符合其中一个结果即可，而 ALL 则需要符合全部结果

原则：

当 >、>= 结果时，ANY 取最小值，SOME 取最小值，ALL 取最大值

当

当 = 结果时，ANY 取任意值，SOME 取任意值

当 <>、!= 结果时，ALL 取任意值

语法结构：

operand comparison\_operator ANY (subquery)

operand comparison\_operator SOME (subquery)

operand comparison\_operator ALL (subquery)

⑶ 使用 \[ NOT \] IN 的子查询

语法结构：

operand comparison\_operator \[NOT\] IN (subquery)

= ANY 运算符与 IN 等效

!= ANY 或 <> ALL 运算符与 NOT IN 等效

⑷ 使用 \[ NOT \] EXISTS 的子查询

如果子查询返回任何行，EXISTS 将返回 TRUE ; 否则返回 FALSE

案例：

创建一张简单的商品表：

![9c94c912c223][]

其中，goods\_name 为商品名称，goods\_cate 为商品分类，brand\_name 为品牌，

goods\_price 为商品价格，is\_show 为是否上架(1：上架，0：下架)，

is\_saleoff 为是否售罄(0：有货，1：售罄)

插入以下数据：

![9c94c912c223][]

查询所有商品的平均价格：

![9c94c912c223][]

对结果四舍五入并保留2位小数：

![9c94c912c223][]

通过子查询来查询所有大于平均价格的商品：

![9c94c912c223][]

查询所有女装：

![9c94c912c223][]

由于记录较少，可以看出女装的最低价是229，查询下哪些商品的价格大于女装：

![9c94c912c223][]

由图可以得出，使用 ANY (或者 SOME) 所得的结果中最低的价格大于或等于女装的最低价。

当使用 ALL 关键字时：

![9c94c912c223][]

可以看出，所得结果的最低价大于或等于女装中最高的价格。

对于这张表来说，一些商品属于同个分类或者同个品牌，而且这些值都是中文字符，这就导致所占的字节数比数字还多，同时也会引发后续大量记录查询的效率问题。所以最好的办法是使用外键来实现，如果使用外键，至少得有2张数据表。

创建一张商品分类表：

![9c94c912c223][]

我们需要把表 tb\_goods 里的分类数据插入到表 tb\_goods\_cate 中，查看下总共有几种分类：

![9c94c912c223][]

可以得出，商品里总共有3中分类，女装，男装和童装。

INSERT ... SELECT ：

概念：

将查询的结果插入数据表

语法结构：

INSERT \[INTO\] tbl\_name \[(col\_name，...)\] SELECT ...

案例：

将商品中所有的分类记录插入到分类表中：

![9c94c912c223][]

现在我们通过分类表去更新商品表，即把商品表的分类名称修改为对应的分类 id 。

当要同时操作多张表时，这时，我们需要对多张表进行连接。

CREATE .. SELECT：

概念：

创建数据表同时将查询结果写入到数据表中

语法结构：

CREATE TABLE \[ IF NOT EXISTS \] tbl\_name \[ ( create\_definition，...) \] select\_statement

案例：

新建一张商品品牌表，并把商品表中的品牌插入到表中：

![9c94c912c223][]

创建成功，查询下品牌表的记录：

![9c94c912c223][]

多表连接：

关键字：JOIN

概念：MySQL 在 SELECT 语句、多表更新、多表删除语句中支持 JOIN 操作。

语法结构：

table\_reference(起始表)

\{ \[ INNER | CROSS \] JOIN | \{ LEFT | RIGHT \} \[ OUTER \] JOIN \}(连接类型)

table\_reference(连接表)

ON conditional\_expr(连接条件)

类型：

⑴ 内连接(INNER JOIN、JOIN、CROSS JOIN)

表示左表和右表都符合连接条件的记录

⑵ 左外连接(LEFT \[OUTER\] JOIN)

表示左表的全部记录和右表符合连接条件的记录，如果右表没有记录，则显示为 NULL

⑶ 右外连接(RIGHT \[OUTER\] JOIN)

与左外连接相反

数据表参照：

table\_reference tbl\_name \[ \[ AS \] alias \] | table\_subquery \[ AS \] alias

当两张表或多张表之间存在重复的字段名称时，尽量使用别名来进行区分。

比如：tbl\_name AS alias\_name 或 tbl\_name alias\_name 赋予别名。

table\_subquery 可以作为子查询使用在 FROM 字句中，这样的子查询必须为其赋予别名。

连接条件：

使用 ON 关键字来设定连接条件，也可以使用 WHERE 来代替。

通常使用 ON 关键字来设定连接条件，使用 WHERE 关键字进行结果集记录的过滤。

说明：

A LEFT JOIN B join\_condition

数据表 B 的结果集依赖数据表 A

数据表 A 的结果集根据左连接条件依赖所有数据表(B 表除外)。

左外连接条件决定如何检索数据表 B (在没有指定 WHERE 条件的情况下)。

如果数据表 A 的某条记录符合 WHERE 条件，但是在数据表 B 不存在，符合连接条件的记录，将生成一个所有列为空的额外的 B 行。

如果使用内连接查询的记录在连接数据表中不存在，并且在 WHERE 子句中尝试以下操作：col\_name 允许为 NULL 时，如果 col\_name 被定义为 NOT NULL，MySQL 将在找到符合连接条件的记录后停止搜索更多的行。

多表更新：

语法结构：

UPDATE table\_references SET col\_name1 = \{ expr1 | DEFAULT \}

\[ ，col\_name2 = \{ expr2 | DEFAULT \}\] ...

\[ WHERE where\_condition \]

其中， table\_references(表的参照关系)的语法结构对应多表连接的语法结构。

案例：

将商品表的分类名称修改为对应分类表的 id：

![9c94c912c223][]

再查询一下商品表的记录：

![9c94c912c223][]

可以发现，所有的商品分类名称都修改为了分类 id 。

继续，参照刚才修改分类的方法把商品表的品牌名称修改为品牌表中的品牌 id：

![9c94c912c223][]

可以看到系统提示错误，字段 brand\_name 模糊不清，因为在两张表中存在重复的 brand\_name 字段，系统并不知道该字段所隶属的是哪张表，所以这个时候需要给表取别名：

![9c94c912c223][]

对表 tb\_goods 取别名为 g，表 tb\_goods\_brand 取别名为 b，并且在连接条件和修改的值里面指定表的字段。再来查看一下表 tb\_goods 的记录：

![9c94c912c223][]

可以发现，品牌的值全部修改为了品牌表中的品牌 id 了。

查看下商品表的结构：

![9c94c912c223][]

虽然品牌和分类已经修改为了对应的 id ，但是字段类型还是字符型，字段名称也不够清晰明了，所以尽量把字段名称和字段类型修改为对应的 id 和整型：

![9c94c912c223][]

再来查看下商品表的结构：

![9c94c912c223][]

可以发现，原来的 goods\_cate 和 goods\_brand 已经修改为了 cate\_id 和 brand\_id。

多表查询：

分别对商品表、分类表和品牌表添加记录：

![9c94c912c223][]

商品表

![9c94c912c223][]

分类表

![9c94c912c223][]

品牌表

内连接：

![9c94c912c223][]

可以发现，id = 19 的记录并没有返回，因为该记录的分类 id = 8 不存在于商品的分类表中，不符合连接条件，所以该结果没有返回。同样，在分类表和品牌表里新增的记录里，由于在商品表中并没有该分类的记录，所以也没有返回结果。

左外连接：

![9c94c912c223][]

可以发现，左表(商品表)返回了全部记录，右表(分类表)返回符合条件的记录，如果没有符合条件的，则显示为 NULL

右外连接：

![9c94c912c223][]

同理，右表(分类表)返回所有记录，左表(商品表)返回符合条件的记录，没有记录则显示 NULL

以上 3 中连接方式，常用的是内连接。

通过对商品表，分类表和品牌表 3 张表进行连接查询：

![9c94c912c223][]

当我们类目有子类目，子类目又有子类目时，这样的结构可以通过一张表来实现：

![9c94c912c223][]

其中，parent\_id 指该类目的父级类目的 id ，如果 parent\_id = 0 说明该类目为顶级类目。

自身连接：

概念：同一个数据表对其自身进行连接。

准备一张课程类目表(总共有 70 条，部分截图)：

![9c94c912c223][]

场景一，查找所有的子类所隶属的父类：

思路：在右侧想象一张和当前表一样的数据表(子表)，进行多表连接，所以父表中的字段 parent\_id 已经不需要了，因为子表中的 parent\_id 指向的是父表中的 id 。

![9c94c912c223][]

参照表以子表为主，查询一下：

![9c94c912c223][]

其中，s 代表的是子表，p 代表的是父表。

场景二，查找父类下的所有子类：

![9c94c912c223][]

参照表以父表为主。

场景三，查找父类下有多少个子类

先对场景二所查询的表的父级名称进行分组：

![9c94c912c223][]

当出现以上情况时，通过以下操作解决：

mysql >

set global sql\_mode='STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION';

mysql >

set session sql\_mode='STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION';

设置完成后重新分组，返回结果如下：

![9c94c912c223][]

对 id 进行升序排列。

查看父类底下有多少个子类：

![9c94c912c223][]

多表删除：

语法结构：

DELETE tbl\_name\[ .\* \] \[ ，tbl\_name\[ .\* \] ...

FROM table\_references \[ WHERE where\_condition \]

查询表 tb\_goods 里的数据：

![9c94c912c223][]

查找商品表里重复的记录：