本文深入探讨了MySQL数据库中的子查询与连接技术，包括子查询的基础、比较运算符、INSERT...SELECT、CREATE...SELECT等高级用法，以及各种连接类型如内连接、左外连接和右外连接的应用场景。
MySQL数据库基础学习（四）-子查询与连接
1 子查询概述
子查询（SubQuery）是指出现在其他SQL语句内的SELECT子句
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
SELECT * FROM t1 称为 Outer Query / Outer Statement
SELECT col2 FROM t2 称为SubQuery
子查询指嵌套在查询内部，且必须始终出现在圆括号内
子查询可以包含多个关键字或条件 如DISTINY、GROUP BY、LIMIT、函数等。
子查询的外层查询可以是：SELECT、SELECT、UPDATE、SET、DO。
子查询可以返回标量，一行、一列或子查询。
采用gbk编码形式显示数据：
mysql>SET NAMES gbk;
2 由比较运算符引发的子查询
= 、>、<、>=、<=、<>、！=、<=>
语法结构
operand comparision_operator subquery
案例分析
对数据表tdb_name中的goods_price字段求平均值
mysql>SELECT AVG(goods_price) FROM tdb_goods;
对所求平均值采取四拾伍入，保留2位小数
mysql>SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
查找good_price字段大于平均值的所有记录
mysql>SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= 5636.36; %以上所求字段平均值
使用子查询合并两条语句
mysql>SELECT goods_id，goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price), 2) FROM tdb_goods);
2.1 用ANY、SOME或ALL修饰的比较运算符
operand comparison_operator ANY(subquery) (返回多条结果)
operand comparison_operator SOME(subquery)
operand comparison_operator ALL(subquery)
运算符|关键字 ANY SOME ALL >、>= 最小值 最小值 最大值 <、<= 最大值 最大值 最小值 = 任意值 任意值 <> != 任意值 案例分析
查询某一类的价格
mysql>SELECT goods_price FROM tdb_name WHERE goods_cate = '超极本'; %返回三条记录
mysql>SELECT * FROM tdb_name WHERE goods_cate = '超极本'\G;
查询哪些商品的价格大于这些超极本
mysql>SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');
错误，原因是子查询返回三条记录，系统未知大于哪一条记录，因此需要使用另外关键字加以说明。
ANY 大于 子查询返回记录中的最小值
mysql>SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');
ALL 大于 子查询返回记录中的最大值
mysql>SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');
=ALL 不可行 =ANY / =SOME可行
2.2 使用[NOT] IN的子查询
operand comprison_operator [NOT] IN
=ANY运算符与IN等效
!=ALL 或 <>ALL 运算符与NOT IN等效
mysql>SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price != ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');
2.3 使用[NOT] EXISTS的子查询
如果子查询返回任何行，EXISTS将返回TRUE;否则为FALSE。
3 使用INSERT...SELECT插入记录
将查询结果写入数据表
INSERT [INTO] tdb_name [(col_name,...)] SELECT ...
案例分析
商品中存在很多重复信息（例：brand_name）
创建一张数据表用来分类，（例brand_name 品牌名）
mysql>CREATE TABLE IF NOT EXISTS tdb_goods_cates(
cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
cate_name VARCHAR(40) NOT NULL);
mysql>SELECT goods_cate FROM tdb_goods GROUP BY goods_cate；
将分组信息写入到新创建的数据表中
mysql>INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
mysql>SELECT * FROM tdb_goods_cates;
4 使用CREATE...SELECT插入数据
创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement
案例分析
查找商品的品牌
mysql>SELECT brand_name FROM tdb_goods GROUP BY brand_name;
mysql>CREATE TABLE tdb_goods_brands(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL) SELECT brand_name FROM tdb_goods GROUP BY brand_name;
mysql>SELECT * FROM tdb_goods_brands; %已写入数据
参照品牌表更新商品表
mysql>UPDATE tdb_goods INNER JOIN tdb_goods_brands ON brand_name = brand_name SET brand_name = brand_id;
错误，两表中都存在brand_name ，产生歧义
对表取别名解决上述问题（另一种方法是tbl_name.* )
mysql>UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;
mysql>SELECT * FROM tdb_goods\G;
以上方法只是修改内容，未修改表的内部结构
即要修改表名称，又要修改表定义，则使用CHANGE
mysql>ALTER TABLE tdb_goods CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
不一定要使用物理外键，常用的是事实外键
当任意的往分类表或品牌表插入记录时
mysql>INSERT tdb_goods_cates(cate_name) VALUES('路由器'，‘交换机’，‘网卡’)；
mysql>INSERT tdb_goods_brands(brand_name) VALUES('海尔'，‘清华同方，‘神舟’)；
在tdb_goods数据表写入任意记录
mysql>INSERT ttdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES('LaserJet Pro 黑白激光打印机'，'12','4',1849);
能写入成功
mysql>SELECT * FROM tdb_goods_cates ; //可以发现并不存在cates_id = 12的分类
此时查看商品表示应显示出明确的品牌和明确的分类，此时就需要连接
5 多表更新
UPDATE table_references
SET col_name1 = {expr1 | DEFAULT}
[, col_name2 = {expr2 | DEFAULT}] ...
[WHERE where_condition]
5.1 表的参照关系
table_reference
{ [INNER|CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr %连接条件
5.2 连接类型
INNER JOIN 内连接 在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的
LEFT [OUTER] JOIN 左外连接
RIGHT [OUTER] JOIN 右外连接
案例分析
mysql>UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
6 连接
MySQL在SELECT语句，多表更新，多表删除语句中支持JOIN操作
table_reference
{[INNER|CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_reference
ON condition_expr
6.1 数据表参照
table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias
数据表可以使用tbl_name AS alias_name 或 tbl_name alias_name赋予别名
table_query可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名
6.2 连接类型
6.2.1 内连接
显示左表及右表符合连接条件的记录
INNER JOIN 内连接 在MySQL中，JOIN，CROSS和INNER JOIN是等价的
案例分析
mysql>SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
只找到两张表所共有的
6.2.2 左外连接
显示左表的全部记录及右表符合连接条件的记录
LEFT [OUTER] JOIN
案例分析
mysql>SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
6.2.3 右外连接
显示右表全部记录及左表符合连接条件的记录
RIGHT [OUTER] JOIN
案例分析
mysql>SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
6.2.4多表连接
三张表连接
mysql>SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id, INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id \G;
6.3连接条件
使用ON关键字来设定连接条件，也可以使用WHERE来代替
通常使用ON关键字来设定连接条件
使用WHERE关键字进行结果集记录的过滤
连接时外键的逆向操作
7 外链接
A LEFT JOIN B join_condition
数据表B的结果集依赖数据表A
数据表A的结果集根据左连接条件依赖所有数据表（B表除外）
左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）
如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行。
如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试一下操作：col_name IS NULL时，如果col_name被定义为NULL，MySQL将在找到符合连接条件的记录后停止搜索更多的条件。
8 无限级分类表设计
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0);
写入一系列记录
查看字段
mysql>SHOW COLUMNS FROM tdb_goods_types;
查看记录
mysql>SELECT * FROM tdb_goods_types;
9 自身连接
同一个数据表对其自身进行连接
案例分析
子类id 子类名称 父类名称
mysql>SELECT s.type_id, s.type_name, p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;
父类id 父类名称 子类名称
mysql>SELECT p.type_id, p.type_name, s.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;
父类id 父类名称 子类数目
mysql>SELECT p.type_id, p.type_name, count(s.type_name) child_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;
10 多表删除
DELETE tbl_name[.*]
[,tbl_name[.*]]...
FROM table_references
[WHERE where_condition]
案例分析
查找重复记录
SELECT goods_id, goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;
mysql>DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id, goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;
小结
子查询
连接
多表删除
扫一扫
614
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
