子查询

最新推荐文章于 2022-09-27 19:31:45 发布

fandatvwt

最新推荐文章于 2022-09-27 19:31:45 发布

阅读量195

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL

本文链接：https://blog.csdn.net/weixin_42545898/article/details/88139125

MySQL 专栏收录该内容

8 篇文章

订阅专栏

本文深入探讨SQL查询技巧，包括平均值计算、子查询、数据表更新与创建、多表连接、无限级分类表的自我连接以及重复数据删除等高级应用，旨在提升数据库操作效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查找goods_price的平均值
SELECT AVG(goods_price) FROM tdb_goods;
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods; //四舍五入，保留小数点后两位round(xx,2)

子查询，直接查询大于平均price的结果，SELECT…WHERE goods_price>(SELECT…);
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>(SELECT ROUND(AVG(goods_price)) FROM tdb_goods);

ANY SOME ALL 查询价格大于超极本的商品，ANY是满足其中一个就好。ALL是满足全部条件
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate=‘超级本’);
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate=‘超级本’);

IN &NOT IN =ANY 与IN 等效 -------- !=ALL 与NOT IN等效
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate=‘超级本’);

【NOT】EXISTS的子查询；如果子查询返回任意行，EXISTS将返回TRUE,否则返回FALSE；
返回全部商品，子查询返回有四行，结果为TRUE，则返回tdb_goods的全部行
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE EXISTS(SELECT goods_price FROM tdb_goods WHERE goods_cate=‘超级本’);

INSERT…SELECT 将查询结果写入数据表
将tdb_goods中goods_cate的分组查询结果写入新表中
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

多表更新

将tdb_goods表的goods_cate 改为tdb_goods_cates表的cate_id ON后面为条件
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;

CREATE SELECT创建数据表同时将查询结果写入数据表
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;
将商品表中的brand_name改为新表中的brand_id 两张表的字段名相同时，需要取别名区别。goods AS a… 后面就用a.xxx代替goods.xxx
UPDATE tdb_goods AS a INNER JOIN tdb_goods_brands AS b ON a.brand_name = b.brand_name SET a.brand_name = b.brand_id;

同时修改表的字段名和类型将连接更新后的表varchar改为smallint
ALTER TABLE tdb_goods
-> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
-> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

INNER JOIN 内连接只显示两张表都符合连接条件的记录。
SELECT goods_id,goods_name,cate_name FROM tdb_goods AS a INNER JOIN tdb_goods_cates AS b ON a.cate_id=b.cate_id;
LEFT JOIN左外连接：显示左表的全部记录和右表符合连接条件的记录。
RIGHT JOIN右外连接：显示右表的全部记录和左表符合连接条件的记录。

三张表同时连接显示。
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS a
-> INNER JOIN tdb_goods_cates AS b ON a.cate_id = b.cate_id
-> INNER JOIN tdb_goods_brands AS C ON a.brand_id = c.brand_id\G;

无限级分类表的单表自我连接。
±--------±-----------±----------+
| type_id | type_name | parent_id |
±--------±-----------±----------+
| 1 | 家用电器 | 0 |
| 2 | 电脑、办公 | 0 |
| 3 | 大家电 | 1 |
| 4 | 生活电器 | 1 |
| 5 | 平板电视 | 3 |
| 6 | 空调 | 3 |
| 7 | 电风扇 | 4 |
| 8 | 饮水机 | 4 |
| 9 | 电脑整机 | 2 |
| 10 | 电脑配件 | 2 |
| 11 | 笔记本 | 9 |
| 12 | 超级本 | 9 |
| 13 | 游戏本 | 9 |
| 14 | CPU | 10 |
| 15 | 主机 | 10 |
tdb_goods_types表与自己连接。父表的parent_id对应子表type_id
SELECT a.type_id,a.type_name,b.type_name AS parentname FROM tdb_goods_types AS a
-> LEFT JOIN tdb_goods_types AS b ON b.type_id=a.parent_id;
查找结果为：
±--------±-----------±-----------+
| type_id | type_name | parentname |
±--------±-----------±-----------+
| 1 | 家用电器 | NULL |
| 2 | 电脑、办公 | NULL |
| 3 | 大家电 | 家用电器 |
| 4 | 生活电器 | 家用电器 |
| 5 | 平板电视 | 大家电 |
| 6 | 空调 | 大家电 |
| 7 | 电风扇 | 生活电器 |
| 8 | 饮水机 | 生活电器 |
| 9 | 电脑整机 | 电脑、办公 |
| 10 | 电脑配件 | 电脑、办公 |
| 11 | 笔记本 | 电脑整机 |
| 12 | 超级本 | 电脑整机 |
| 13 | 游戏本 | 电脑整机 |
| 14 | CPU | 电脑配件 |
| 15 | 主机 | 电脑配件 |
±--------±-----------±-----------+

SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p
-> LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id;
查找结果为：
±--------±-----------±----------+
| type_id | type_name | type_name |
±--------±-----------±----------+
| 1 | 家用电器 | 大家电 |
| 1 | 家用电器 | 生活电器 |
| 2 | 电脑、办公 | 电脑整机 |
| 2 | 电脑、办公 | 电脑配件 |
| 3 | 大家电 | 平板电视 |
| 3 | 大家电 | 空调 |
| 4 | 生活电器 | 电风扇 |
| 4 | 生活电器 | 饮水机 |
| 5 | 平板电视 | NULL |
| 6 | 空调 | NULL |
| 7 | 电风扇 | NULL |
| 8 | 饮水机 | NULL |
| 9 | 电脑整机 | 笔记本 |
| 9 | 电脑整机 | 超级本 |
| 9 | 电脑整机 | 游戏本 |
| 10 | 电脑配件 | CPU |
| 10 | 电脑配件 | 主机 |
| 11 | 笔记本 | NULL |
| 12 | 超级本 | NULL |
| 13 | 游戏本 | NULL |
| 14 | CPU | NULL |
| 15 | 主机 | NULL |

SELECT p.type_id,p.type_name,count(s.type_name)AS 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;
查询结果为：
±--------±-----------±------------+
| type_id | type_name | child_count |
±--------±-----------±------------+
| 1 | 家用电器 | 2 |
| 2 | 电脑、办公 | 2 |
| 3 | 大家电 | 2 |
| 4 | 生活电器 | 2 |
| 5 | 平板电视 | 0 |
| 6 | 空调 | 0 |
| 7 | 电风扇 | 0 |
| 8 | 饮水机 | 0 |
| 9 | 电脑整机 | 3 |
| 10 | 电脑配件 | 2 |
| 11 | 笔记本 | 0 |
| 12 | 超级本 | 0 |
| 13 | 游戏本 | 0 |
| 14 | CPU | 0 |
| 15 | 主机 | 0 |
±--------±-----------±------------+

删除表中两条及两条以上的重复数据，并且删除的是id号大的。利用子查询作为t2表。
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;