【SQL进阶】MySQL高级查询

一、数据筛选：WHERE 子句及其扩展

1. 基础条件与逻辑组合

-- 销售额大于500且日期在2020-12-08之后
SELECT * FROM store_info 
WHERE Sales > 500 AND Date > '2020-12-08';

2. 范围与成员判断

-- BETWEEN 是闭区间，包含边界值
SELECT * FROM store_info 
WHERE Date BETWEEN '2020-12-07' AND '2020-12-09';

-- IN 等价于多个 OR 条件
SELECT * FROM store_info 
WHERE Store_Name IN ('Los Angeles', 'Boston');
-- 等价于：
-- WHERE Store_Name = 'Los Angeles' OR Store_Name = 'Boston'

3. 模糊匹配：LIKE 与通配符

-- 以 'L' 开头
SELECT * FROM store_info WHERE Store_Name LIKE 'L%';

-- 第二个字符是 'o'
SELECT * FROM store_info WHERE Store_Name LIKE '_o%';

-- 包含 'an'（不区分大小写）
SELECT * FROM store_info WHERE Store_Name LIKE '%an%';

注意：LIKE 以 % 开头（如 '%os%'）无法使用索引，大数据量下性能较差。

4. 正则匹配：REGEXP

-- 以 'H' 开头
SELECT * FROM store_info WHERE Store_Name REGEXP '^H';

-- 以 's' 结尾
SELECT * FROM store_info WHERE Store_Name REGEXP 's$';

-- 包含 'a' 或 'o'
SELECT * FROM store_info WHERE Store_Name REGEXP '[ao]';

---

二、结果处理：去重、排序与别名

1. 去重：DISTINCT

-- 返回所有不重复的商店名称
SELECT DISTINCT Store_Name FROM store_info;
-- 结果: Los Angeles, Houston, Boston, Washington, Chicago

2. 排序：ORDER BY

-- 按销售额降序排列
SELECT Store_Name, Sales FROM store_info 
ORDER BY Sales DESC;

3. 别名：提升可读性

-- 字段别名
SELECT Store_Name AS 店铺, Sales AS 销售额 
FROM store_info;

-- 表别名（在多表查询中尤为重要）
SELECT l.Region, s.Sales
FROM location AS l
JOIN store_info AS s ON l.Store_Name = s.Store_Name;

---

三、聚合与分组：GROUP BY 与 HAVING

1. 聚合函数

SELECT 
  COUNT(*) AS 总记录数,
  SUM(Sales) AS 总销售额,
  AVG(Sales) AS 平均销售额,
  MAX(Sales) AS 最高销售额,
  MIN(Sales) AS 最低销售额
FROM store_info;

2. 分组统计：GROUP BY

-- 按店铺分组，计算每个店铺的总销售额
SELECT Store_Name, SUM(Sales) AS Total_Sales
FROM store_info
GROUP BY Store_Name;

3. 关键对比：WHERE vs HAVING

特性	WHERE	HAVING
执行时机	在分组前过滤原始行	在分组后过滤分组结果
能否用聚合函数	❌ 不能	✅ 可以
用途	过滤单条记录	过滤整个分组

示例：

-- ❌ 错误：WHERE 中不能使用聚合函数
-- SELECT Store_Name, SUM(Sales) FROM store_info WHERE SUM(Sales) > 1000 GROUP BY Store_Name;

-- ✅ 正确：使用 HAVING
SELECT Store_Name, SUM(Sales) AS Total_Sales
FROM store_info
GROUP BY Store_Name
HAVING SUM(Sales) > 1000;
-- 结果: Los Angeles (1800)

---

四、多表连接：JOIN 的三种主要类型

假设有两张表：

• location(Region, Store_Name)
• store_info(Store_Name, Sales, Date)

1. 内连接（INNER JOIN）

只返回两表都存在的匹配行。

SELECT l.Region, s.Sales
FROM location l
INNER JOIN store_info s ON l.Store_Name = s.Store_Name;
-- 结果不包含 'Washington' 和 'Chicago'（它们不在 location 表中）

2. 左连接（LEFT JOIN）

返回左表（location）所有行，右表无匹配时为 NULL。

SELECT l.Store_Name, s.Sales
FROM location l
LEFT JOIN store_info s ON l.Store_Name = s.Store_Name;
-- 结果包含 'Boston', 'New York' 等，即使 New York 在 store_info 中无销售记录

3. 右连接（RIGHT JOIN）

返回右表（store_info）所有行，左表无匹配时为 NULL。

SELECT l.Region, s.Store_Name
FROM location l
RIGHT JOIN store_info s ON l.Store_Name = s.Store_Name;
-- 结果包含 'Washington', 'Chicago'，但 Region 为 NULL

建议：优先使用 LEFT JOIN，因其逻辑更直观（“保留左边所有”），RIGHT JOIN 可通过交换表顺序用 LEFT JOIN 替代。

---

五、子查询与集合操作

1. 子查询（嵌套查询）

-- 查询西部地区（West）所有店铺的销售总额
SELECT SUM(Sales) 
FROM store_info 
WHERE Store_Name IN (
    SELECT Store_Name 
    FROM location 
    WHERE Region = 'West'
);
-- 结果: 2050 (Los Angeles 1500+300 + Houston 250)

2. 关键对比：IN vs EXISTS

特性	IN	EXISTS
工作方式	先执行子查询，生成列表，再逐个匹配	对主查询每行，检查子查询是否有结果
空值处理	若子查询含 NULL，可能导致结果为空	不受 NULL 影响
性能	子查询结果集小时更快	主表小、子查询大时更快

EXISTS 示例：

1SELECT * FROM store_info s
2WHERE EXISTS (
3    SELECT 1 FROM location l 
4    WHERE l.Store_Name = s.Store_Name
5);
6-- 功能上等价于 INNER JOIN，但逻辑更清晰表达“存在性”

3. 集合合并：UNION vs UNION ALL

特性	UNION	UNION ALL
去重	✅ 自动去除重复行	❌ 保留所有行
性能	较慢（需排序去重）	更快
列要求	必须相同数量和兼容类型	同左

示例：

-- UNION: 合并并去重
SELECT Store_Name FROM location
UNION
SELECT Store_Name FROM store_info;
-- 结果: 6 行（去除了重复的 Boston, Los Angeles, Houston）

-- UNION ALL: 合并不去重
SELECT Store_Name FROM location
UNION ALL
SELECT Store_Name FROM store_info;
-- 结果: 10 行（4 + 6）

---

六、条件逻辑：CASE 表达式

实现 SQL 中的 “if-else” 逻辑。

1. 简单 CASE（基于字段值）

SELECT Store_Name,
  CASE Store_Name
    WHEN 'Los Angeles' THEN Sales * 2
    WHEN 'Boston' THEN 2000
    ELSE Sales
  END AS Adjusted_Sales
FROM store_info;

2. 搜索 CASE（基于条件）

SELECT Store_Name, Sales,
  CASE 
    WHEN Sales >= 1000 THEN 'High'
    WHEN Sales >= 500 THEN 'Medium'
    ELSE 'Low'
  END AS Sales_Level
FROM store_info;

---

七、视图（View）：虚拟表

创建与使用

-- 创建视图：每个地区的总销售额
CREATE VIEW V_REGION_SALES AS
SELECT l.Region, SUM(s.Sales) AS Total_Sales
FROM location l
INNER JOIN store_info s ON l.Store_Name = s.Store_Name
GROUP BY l.Region;

-- 查询视图
SELECT * FROM V_REGION_SALES;
-- 结果:
-- East | 700
-- West | 2050

优点：简化复杂查询、封装逻辑、提供安全访问层。

---

八、重要概念：NULL 与 空字符串

特性	NULL	空字符串 ''
含义	未知/缺失	一个有效的、长度为0的字符串
判断方式	IS NULL / IS NOT NULL	= '' / != ''
COUNT() 行为	COUNT(col) 会忽略	COUNT(col) 会计入
长度	LENGTH(NULL) 返回 NULL	LENGTH('') 返回 0

示例：

-- 查找 name 为 NULL 的行
SELECT * FROM City WHERE name IS NULL;

-- 查找 name 为空字符串的行
SELECT * FROM City WHERE name = '';