MySQL子查询实战：语法、优化与复杂查询技巧

子查询的基本概念与语法

子查询是指嵌入在另一个查询语句（外查询）内部的 SELECT 语句。子查询通常出现在 WHERE 或 HAVING 子句中，必须用圆括号括起来。根据返回结果的不同，子查询可分为标量子查询（返回单值）、行级子查询（返回单行多列）和集合子查询（返回多行多列）。

-- 1. 基本标量子查询示例
SELECT * FROM t1 
WHERE column1 = (SELECT column1 FROM t2);
-- 此处 outer query 为最外层 SELECT，sub query 为括号内部分

-- 2. 行级子查询示例
-- 方式一：使用逗号列表比较
SELECT * FROM t1 
WHERE (col1, col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

-- 方式二：使用 ROW 关键字（更直观）
SELECT * FROM t1 
WHERE ROW(col1, col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

结合聚合函数的过滤优化

当需要基于统计信息（如平均值、最大值等）进行筛选时，子查询是非常有效的工具。以下通过构建一个商品表 tdb_goods 来演示实际应用场景。

第1步：创建数据表并插入样本数据

首先建立测试用的商品表，包含价格、类别、品牌等信息，并插入若干条记录以便后续查询验证。

-- 创建表结构
CREATE TABLE IF NOT EXISTS tdb_goods (
  goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  goods_name VARCHAR(150) NOT NULL,
  goods_cate VARCHAR(40) NOT NULL,
  brand_name VARCHAR(40) NOT NULL,
  goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
  is_show BOOLEAN NOT NULL DEFAULT 1,
  is_saleoff BOOLEAN NOT NULL DEFAULT 0
);

-- 插入测试数据（示例部分）
INSERT INTO tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff)
VALUES 
('R510VC 15.6英寸笔记本', '笔记本', '华硕', 3399, DEFAULT, DEFAULT),
('Y400N 14.0英寸笔记本电脑', '笔记本', '联想', 4899, DEFAULT, DEFAULT),
('G150TH 15.6英寸游戏本', '游戏本', '雷神', 8499, DEFAULT, DEFAULT);
-- ... 其他数据省略，实际操作请补全完整Insert语句

第2步：使用子查询查询高于平均价的商品

直接比较动态计算出的平均值是子查询的经典用例。先计算出所有商品的平均价格（保留两位小数），再筛选出高于该值的产品。

-- 第一步：查看平均价格，确认为 5845.10
SELECT ROUND(AVG(goods_price), 2) AS avg_price FROM tdb_goods;

-- 第二步：硬编码比较（不推荐，需手动更新数字）
SELECT goods_id, goods_name, goods_price 
FROM tdb_goods 
WHERE goods_price > 5845.10 
ORDER BY goods_price DESC;

-- 第三步：使用子查询自动获取阈值（推荐）
SELECT goods_id, goods_name, goods_price 
FROM tdb_goods 
WHERE goods_price > (SELECT ROUND(AVG(goods_price), 2) AS avg_price FROM tdb_goods)
ORDER BY goods_price DESC;

使用 IN 简化 ANY/SOME 查询

Mysql 支持 = ANY 或 = SOME 操作符，其逻辑等同于 IN。虽然两者功能一致，但 IN 更通用且可读性更强。

-- 需求：查找价格等于任何一款“超级本”价格的电脑

-- 方法一：使用 ANY 或 SOME
SELECT goods_id, goods_name, goods_price 
FROM tdb_goods 
WHERE goods_price = ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')
ORDER BY goods_price DESC;

-- 方法二：使用 IN（等价且推荐）
SELECT goods_id, goods_name, goods_price 
FROM tdb_goods 
WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')
ORDER BY goods_price DESC;

表重构中的子查询与关联更新

在规范化数据库设计时，常需要将分类和品牌从主表中拆分出来，并使用子查询初始化新表。在此过程中，需要注意关联更新时的字段名歧义问题。

第1步：拆分并初始化分类表

将 tdb_goods 中的唯一分类名称提取到新的分类表 tdb_goods_cates 中。

-- 创建分类表
CREATE TABLE IF NOT EXISTS tdb_goods_cates (
  cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  cate_name VARCHAR(40)
);

-- 利用 SELECT 分组结果插入新表
INSERT INTO tdb_goods_cates (cate_name) 
SELECT DISTINCT goods_cate FROM tdb_goods GROUP BY goods_cate;

第2步：正确关联更新以消除歧义

尝试直接用旧字段名匹配新 ID 时容易报错 "Column 'brand_name' in field list is ambiguous"。必须给表指定别名才能明确指代。

-- 错误写法：字段名歧义
UPDATE tdb_goods INNER JOIN tdb_goods_brands ON brand_name = brand_name
SET brand_name = brand_id;

-- 正确写法：使用表别名 g 和 b 区分
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;

第3步：修改表结构并重命名字段

更新完成映射后，修改主表结构，将原有的文本字段替换为 ID 引用。

ALTER TABLE tdb_goods 
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

自连接：实现无限分类层级

对于树状结构的数据（如商品分类），可以通过自连接（Self Join）在同一张表中查询父类与子类的关系。

-- 创建无限分类表
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
);

-- 插入层级数据示例
INSERT INTO tdb_goods_types (type_name, parent_id) VALUES
('家用电器', DEFAULT),
('电脑、办公', DEFAULT),
('大家电', 1),
('平板电视', 3);

-- 查询所有分类及其对应的父类名称
SELECT s.type_id, s.type_name AS current_category, p.type_name AS parent_category 
FROM tdb_goods_types AS s 
LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;

高级技巧：数据复制与清理重复

在处理历史数据迁移或清洗时，INSERT ... SELECT 可用于快速复制数据，而基于分组判断的 DELETE 则可精准去除冗余记录。

-- 1. INSERT ... SELECT 实现特定记录复制
INSERT INTO tdb_goods (goods_name, cate_id, brand_id)
SELECT goods_name, cate_id, brand_id 
FROM tdb_goods 
WHERE goods_id IN (19, 20);

-- 2. 查找完全重复的记录组
SELECT goods_id, goods_name 
FROM tdb_goods 
GROUP BY goods_name 
HAVING COUNT(goods_name) >= 2;

-- 3. 删除重复记录（保留ID最小的一条）
-- 核心逻辑：左连接找出同一组中ID较大的记录，然后删除它们
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;

总结

MySQL 子查询不仅用于简单的过滤条件，还能配合聚合函数、集合操作符（IN/ANY）以及复杂的表关联逻辑解决实际问题。在处理关联更新时务必注意表别名以避免歧义；在处理树状结构时善用自连接；在数据清洗时利用子查询定位并删除冗余数据。以上就是 MySQL 数据库中子查询的详细使用方法与优化技巧，更多关于 SQL 进阶操作的资料请关注本站其它相关文章！