MySQL基础：数据操作查询的方式

表中数据的增删改查操作

1. 增（Create）

关键字：INSERT INTO ... VALUES ...
- INSERT INTO：指定插入的目标表
- VALUES：定义要插入的具体值

示例：

INSERT INTO users (name, age) VALUES ('张三', 25);

2. 删（Delete）

关键字：DELETE FROM ... WHERE ...
- DELETE FROM：指定要删除数据的表
- WHERE：条件筛选（⚠️ 不加条件会清空全表！）
示例：

DELETE FROM users WHERE id = 100;

3. 改（Update）

关键字：UPDATE ... SET ... WHERE ...
- UPDATE：指定要修改的表
- SET：设置新值
- WHERE：条件筛选（⚠️ 不加条件会更新全表！）
示例：

UPDATE users SET age = 26 WHERE name = '张三';

4. 查（Read）

关键字：SELECT ... FROM ... WHERE ...
- SELECT：指定要查询的列（* 表示所有列）
- FROM：指定查询的表
- WHERE：条件筛选
- ORDER BY：排序（ASC 升序，DESC 降序）
- LIMIT：限制返回的行数

示例：

SELECT name, age FROM users WHERE age > 20 ORDER BY id DESC LIMIT 10;

清空表（高危操作！）

TRUNCATE TABLE users;  -- 不可回滚，谨慎使用！

基础查询和Where条件查询

一、基本查询操作（SELECT）

语法：

SELECT 列1, 列2, ... 
FROM 表名 
[WHERE 条件] 
[其他子句...];

常见用法

查询所有列：
```
SELECT * FROM employees;
```
查询指定列：
```
SELECT name, salary FROM employees;
```

去重查询（DISTINCT）：

SELECT DISTINCT department FROM employees;

计算列（表达式或函数）：

SELECT name, salary * 1.1 AS new_salary FROM employees;

二、WHERE 子句

WHERE 子句用于筛选满足条件的记录，支持多种运算符和逻辑组合。

1. 比较运算符

=：等于

SELECT * FROM employees WHERE salary = 5000;

<> 或 !=：不等于

>、<、>=、<=：数值或日期比较

SELECT * FROM employees WHERE hire_date > '2020-01-01';

BETWEEN ... AND ...：范围查询

SELECT * FROM employees WHERE salary BETWEEN 4000 AND 6000;

2. 逻辑运算符

AND：同时满足多个条件

SELECT * FROM employees WHERE department = 'IT' AND salary > 5000;

OR：满足任意一个条件

SELECT * FROM employees WHERE department = 'HR' OR department = 'Sales';

NOT：取反条件

SELECT * FROM employees WHERE NOT department = 'IT';

3. 模糊查询（LIKE）

%：匹配任意字符（包括空字符）

SELECT * FROM employees WHERE name LIKE 'J%';  -- 以 J 开头（如 John, Jane）
SELECT * FROM products WHERE name LIKE '%apple%'; -- 包含 "apple"（如 Pineapple, ApplePie）

_：匹配单个字符

SELECT * FROM employees WHERE name LIKE '_a%';  -- 第二个字符是 a（如 Sam, David）
SELECT * FROM users WHERE phone LIKE '138-____-____'; -- 固定格式（如 138-1234-5678）

转义特殊字符（如 % 或 _）：

SELECT * FROM discounts WHERE remark LIKE '%10!%%' ESCAPE '!'; -- 匹配包含 "10%" 的文本

不区分大小写：

SELECT * FROM users WHERE username LIKE '%admin%' COLLATE utf8_general_ci; -- 匹配 Admin, ADMIN

4. 集合查询（IN）

筛选字段值在指定集合中的记录：

SELECT * FROM employees WHERE department IN ('IT', 'HR');

5. 处理 NULL 值

IS NULL：判断为空

SELECT * FROM employees WHERE manager_id IS NULL;

IS NOT NULL：判断非空

SELECT * FROM employees WHERE email IS NOT NULL;

三、WHERE 子句的注意事项

执行顺序：WHERE 在数据筛选阶段生效，早于 GROUP BY、ORDER BY 等子句。
性能优化： • 对索引列使用 WHERE 条件可提高效率。 • 避免在条件中对列做函数操作（如 WHERE YEAR(date) = 2023）。
模糊查询性能： • LIKE '%keyword%' 会导致全表扫描，建议对高频搜索字段使用全文索引。 • 前导通配符（如 LIKE '%abc'）无法利用索引。

排序和分页查询

一、排序（ORDER BY）

基本语法
```
SELECT 列名 FROM 表名 
ORDER BY 列1 [ASC|DESC], 列2 [ASC|DESC], ...;
```
• ASC：升序（默认），DESC：降序。 • 支持多列排序，优先级按列顺序依次降低。

示例

SELECT name, salary FROM employees
ORDER BY salary DESC, hire_date ASC; -- 先按工资降序，再按入职时间升序

注意事项
- 索引优化：排序字段若没有索引，大数据量时可能导致性能问题（需磁盘文件排序）。
- 稳定性：若排序字段有重复值，结果顺序可能不稳定，可添加唯一字段（如主键）作为次要排序条件。

二、分页查询（LIMIT & OFFSET）

基本语法
```
SELECT 列名 FROM 表名 
LIMIT 每页条数 
OFFSET 跳过行数;
```
- 简写形式：LIMIT 跳过行数, 每页条数（如LIMIT 20, 10表示跳过20行，取10行）。

示例

SELECT * FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 20; -- 获取第3页数据（每页10条）

性能优化
- 深分页问题：OFFSET值过大时（如LIMIT 100000, 10），MySQL需扫描大量数据后跳过，效率低下。
- 优化方案：
- 基于游标的分页：记录上一页最后一条数据的唯一标识（如自增ID），下页查询时使用WHERE id > last_id LIMIT 10。
- 子查询优化：
```
SELECT * FROM table 
WHERE id >= (SELECT id FROM table ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 10;
```

三、排序 + 分页的常见场景

典型查询

SELECT id, title, created_at FROM articles
ORDER BY created_at DESC
LIMIT 10 OFFSET 0; -- 首页按时间倒序排列

需注意的问题
- 结果一致性：若数据在分页间变化（如新增/删除），可能导致重复或遗漏。
- 覆盖索引：为排序和过滤字段创建联合索引（如(created_at, id)），避免回表查询。

四、高级用法（MySQL 8.0+）

窗口函数分页 使用ROW_NUMBER()实现灵活分页（适用于复杂排序逻辑）：

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
  FROM employees
) AS tmp
WHERE row_num BETWEEN 21 AND 30;

总结

排序：用ORDER BY控制结果顺序，注意索引优化。
分页：用LIMIT和OFFSET，但深分页需优化。
组合使用：通常先排序再分页，确保结果顺序稳定。
性能关键：尽量通过索引减少全表扫描和文件排序，避免OFFSET过大时的性能瓶颈。

聚合函数

一、常用聚合函数

函数	作用	示例（假设表 `sales`）
`COUNT()`	统计行数（可过滤NULL）	`SELECT COUNT(*) FROM sales;`
`SUM()`	数值列求和	`SELECT SUM(amount) FROM sales;`
`AVG()`	数值列平均值	`SELECT AVG(price) FROM sales;`
`MAX()`	返回列最大值	`SELECT MAX(sale_date) FROM sales;`
`MIN()`	返回列最小值	`SELECT MIN(quantity) FROM sales;`
`GROUP_CONCAT()`	合并分组字符串	`SELECT GROUP_CONCAT(product) FROM sales;`
`STD()`	标准差	`SELECT STD(revenue) FROM sales;`

二、核心使用场景

与 GROUP BY 结合

SELECT product_type, COUNT(*) AS total_sales, AVG(price) -- 使用 AS 关键字 给统计列字段定义别名
FROM sales
GROUP BY product_type; -- 按商品类型分组统计

过滤聚合结果（HAVING）

SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING total_spent > 1000; -- 筛选消费超过1000的用户

去重统计（DISTINCT）

SELECT COUNT(DISTINCT user_id) FROM orders; -- 统计唯一用户数

三、注意事项

NULL 值的处理
- 除 COUNT(*) 外，其他聚合函数默认忽略 NULL 值。
- 示例：AVG(score) 仅计算非 NULL 值的平均数。
GROUP BY 的隐式排序
- MySQL 8.0 前，GROUP BY 默认按分组字段排序；8.0 后取消该特性。若需排序，显式使用 ORDER BY。
WHERE 与 HAVING 的区别
- WHERE：在分组前过滤行（不能使用聚合函数）。
- HAVING：在分组后过滤聚合结果（可使用聚合函数）。
聚合函数嵌套限制
- 聚合函数不能直接嵌套（如 SUM(AVG(price))），需通过子查询实现。

四、性能优化

索引对聚合的影响
- MIN()/MAX() 在索引列上效率极高，几乎无需扫描数据。
- COUNT(列) 比 COUNT(*) 慢（需检查 NULL）。
减少全表扫描
- 尽量通过 WHERE 缩小聚合范围：
```
SELECT AVG(score) FROM users WHERE age > 18;
```
避免 GROUP BY 的临时表
- 使用 EXPLAIN 检查执行计划，确保分组字段有索引。

五、扩展用法

组合聚合函数

SELECT 
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders;

与 WITH ROLLUP 生成小计

SELECT product_type, SUM(quantity)
FROM sales
GROUP BY product_type WITH ROLLUP; -- 添加汇总行

窗口函数中的聚合（MySQL 8.0+）

SELECT 
    order_id, 
    amount,
    SUM(amount) OVER (PARTITION BY user_id) AS user_total
FROM orders;

总结

核心用途：统计、汇总、分组分析数据。
性能关键：合理使用索引，减少全表扫描。
易错点：NULL 值处理、HAVING 与 WHERE 混淆、隐式排序依赖。

分组和HAVING条件查询

一、分组（GROUP BY）

1. 作用

将数据按指定列的值分组，对每组进行聚合统计（如计数、求和、平均值等）。

2. 基本语法

SELECT 分组列, 聚合函数(列) 
FROM 表名 
GROUP BY 分组列1, 分组列2...;

3. 示例

统计每个部门的员工数量和平均工资：

SELECT department_id, 
       COUNT(*) AS employee_count, -- 配合聚合函数使用
       AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department_id;

4. 关键特性

多列分组：可指定多个分组列（如 GROUP BY department_id, job_title）。
隐式去重：分组后每组仅保留唯一值组合。
NULL 值处理：所有 NULL 值会被归为同一组。

二、HAVING 条件查询

1. 作用

过滤分组后的结果（类似 WHERE，但作用于分组后的数据）。

2. 基本语法

SELECT 分组列, 聚合函数(列) 
FROM 表名 
GROUP BY 分组列 
HAVING 条件; -- 条件可包含聚合函数

3. 示例

筛选出员工数量超过 5 人且平均工资高于 8000 的部门：

SELECT department_id, 
       COUNT(*) AS employee_count, 
       AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department_id 
HAVING employee_count > 5 AND avg_salary > 8000;

三、WHERE 与 HAVING 的核心区别

特性	WHERE	HAVING
执行阶段	分组前过滤行	分组后过滤组
是否可用聚合函数	否（直接操作原始列）	是（操作分组后的聚合结果）
性能影响	减少分组处理的数据量	仅过滤最终结果

四、使用场景与技巧

1. 典型场景

统计业务指标（如用户订单数、商品销售额）。
数据分层分析（如按年龄段、地区分组统计）。

2. 优化技巧

索引优化：为分组字段创建索引（如 department_id）可加速分组操作。
减少分组列：分组列越多，计算开销越大。
先 WHERE 再 HAVING：先用 WHERE 过滤无关数据，减少分组计算量。

3. 结合 ORDER BY

分组后排序结果：

SELECT department_id, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department_id 
HAVING avg_salary > 5000 
ORDER BY avg_salary DESC;

五、常见错误与注意事项

SELECT 列未分组或聚合

-- 错误示例：name 未在 GROUP BY 或聚合函数中
SELECT name, AVG(salary) FROM employees GROUP BY department_id;

解决：仅选择分组列或聚合函数结果。

混淆 WHERE 与 HAVING

-- 错误示例：在 WHERE 中使用聚合函数
SELECT department_id FROM employees WHERE AVG(salary) > 5000;

解决：聚合条件必须放在 HAVING 中。

NULL 值的分组统计
- 若分组列包含 NULL，所有 NULL 值会被归为同一组。

六、扩展用法

1. WITH ROLLUP 小计汇总

生成分组小计和总计行：

SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id WITH ROLLUP; 
-- 结果包含每个部门的人数及总人数

2. 多级分组筛选

按多列分组并筛选：

SELECT country, city, COUNT(*) AS user_count 
FROM users 
GROUP BY country, city 
HAVING user_count > 100;

总结

GROUP BY：按列分组并进行聚合计算，核心用于数据汇总。
HAVING：过滤分组后的结果，条件可包含聚合函数。
最佳实践：优先用 WHERE 减少数据量，合理使用索引优化分组性能。

多表查询

一、多表查询类型及语法

1. 内连接（INNER JOIN）

作用：返回两表中匹配的行（交集）。

语法：

SELECT 列名 
FROM 表1 
INNER JOIN 表2 ON 表1.列 = 表2.列;

示例：

SELECT orders.order_id, customers.name 
FROM orders 
INNER JOIN customers ON orders.customer_id = customers.id;

2. 左外连接（LEFT JOIN）

作用：返回左表全部行 + 右表匹配行（不匹配的右表字段为 NULL）。
特点：
- 左边的表是不看条件的，无论条件是否满足，都会返回左表中的所有数据。
- 只有右边的表会看条件，对于右表，只有满足条件的，才会返回。

语法：

SELECT 列名 
FROM 表1 
LEFT JOIN 表2 ON 表1.列 = 表2.列;

示例（查询所有员工及其部门，包括未分配部门的员工）：

SELECT employees.name, departments.dept_name 
FROM employees 
LEFT JOIN departments ON employees.dept_id = departments.id;

3. 右外连接（RIGHT JOIN）

作用：返回右表全部行 + 左表匹配行（不匹配的左表字段为 NULL）。
特点：
- 右边的表是不看条件的，无论条件是否满足，都会返回右表中的所有数据。
- 只有左边的表会看条件，对于左表，只有满足条件的，才会返回。
语法：类似 LEFT JOIN，方向相反。

4. 全外连接（FULL OUTER JOIN）

作用：返回两表所有行（不匹配的字段为 NULL）。
注意：MySQL 不直接支持，需用 LEFT JOIN + RIGHT JOIN + UNION 模拟。

5. 交叉连接（CROSS JOIN）

作用：返回两表的笛卡尔积（所有可能的行组合）。

语法：

SELECT 列名 FROM 表1 CROSS JOIN 表2;

慎用：数据量大时性能极差。

6. UNION

UNION 的作用

将 多个 SELECT 语句的结果集 合并为一个结果集，并自动去重（除非使用 UNION ALL）。

基本语法

SELECT 列1, 列2 FROM 表1
UNION [ALL]
SELECT 列1, 列2 FROM 表2
[UNION [ALL]
SELECT 列1, 列2 FROM 表3 ...];

UNION：合并结果并去重。
UNION ALL：合并结果但保留重复行（性能更高）。
列要求：所有 SELECT 的列数、顺序和数据类型必须一致（列名可以不同）。

二、多表查询场景

1. 关联数据查询

如订单表关联客户表，获取订单对应的客户信息。

2. 数据补全

使用 LEFT JOIN 确保主表数据完整（如统计所有商品，包括未销售的）。

3. 多条件连接

SELECT * 
FROM table1 
JOIN table2 ON table1.id = table2.id AND table1.status = 'active';

4. 自连接

同一表内关联（如员工表查员工及其经理）：

SELECT e.name, m.name AS manager 
FROM employees e 
LEFT JOIN employees m ON e.manager_id = m.id;

三、多表查询的联合使用

1. 多表连接（3张表以上）

SELECT orders.id, customers.name, products.product_name 
FROM orders 
JOIN customers ON orders.customer_id = customers.id 
JOIN products ON orders.product_id = products.id;

2. 结合聚合函数

统计每个客户的订单总金额：

SELECT customers.name, SUM(orders.amount) AS total 
FROM customers 
LEFT JOIN orders ON customers.id = orders.customer_id 
GROUP BY customers.id;

3. 子查询作为临时表

SELECT a.name, b.avg_salary 
FROM employees a 
JOIN (SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id) b 
ON a.dept_id = b.dept_id;

四、性能优化与注意事项

索引优化
- 为连接条件列（如 customer_id）和筛选条件列创建索引。
避免笛卡尔积
- 确保 JOIN 时明确指定 ON 或 WHERE 条件。
优先使用 INNER JOIN
- 比 OUTER JOIN 效率更高，尽量减少 NULL 值处理。
减少连接表的数量
- 多表连接时，剔除无关表或字段。
分阶段查询
- 对复杂查询拆分为多个步骤，用临时表存储中间结果。

五、常见错误

未指定连接条件

-- 错误！产生笛卡尔积（性能灾难）
SELECT * FROM orders, customers;

混淆 ON 和 WHERE
- ON 用于定义连接条件，WHERE 用于过滤结果。

字段歧义

多表存在同名字段时需指定表名前缀：

SELECT orders.id, customers.id -- 明确指定表名
FROM orders JOIN customers ...

总结

核心原则：明确连接类型，写清连接条件，避免笛卡尔积。
优化关键：索引、减少连接表数量、优先 INNER JOIN。
实际应用：结合业务场景选择 JOIN 或子查询。

子查询

一、子查询的定义

子查询是 嵌套在其他 SQL 语句（如 SELECT、WHERE、FROM 等）中的查询，用于辅助主查询完成复杂的数据筛选或计算。

二、子查询的分类

1. 按返回结果类型分类

类型	说明	示例（假设表 `employees`）
标量子查询	返回单个值（一行一列）	`SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);`
列子查询	返回一列数据（多行一列）	`SELECT name FROM employees WHERE id IN (SELECT manager_id FROM departments);`
行子查询	返回一行数据（一行多列）	`SELECT * FROM employees WHERE (salary, age) = (SELECT MAX(salary), MIN(age) FROM employees);`
表子查询	返回多行多列（临时表）	`SELECT * FROM (SELECT name, salary FROM employees) AS tmp WHERE salary > 5000;`

2. 按执行时机分类

非关联子查询：子查询可独立执行，不依赖主查询。

SELECT name FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

关联子查询：子查询依赖主查询的字段值，需与外层查询交互。

SELECT e1.name FROM employees e1 
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id);

三、子查询的使用场景

1. 在 `WHERE` 中过滤数据

-- 查询工资高于平均工资的员工
SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

2. 在 `FROM` 中作为临时表（派生表）

-- 统计每个部门的平均工资
SELECT dept_id, avg_salary 
FROM (SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id) AS dept_stats 
WHERE avg_salary > 8000;

3. 在 `SELECT` 中作为计算字段

-- 查询员工姓名及其部门平均工资
SELECT name, salary, 
       (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id) AS dept_avg 
FROM employees e1;

4. 与 `IN`、`EXISTS` 配合使用

-- 查询有订单的客户
SELECT name FROM customers 
WHERE id IN (SELECT customer_id FROM orders);

-- 使用 EXISTS（推荐高效写法）
SELECT name FROM customers c 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

四、子查询的注意事项

性能问题
- 关联子查询可能效率较低（需多次执行），尽量优化为 JOIN 或非关联子查询。
- 使用 EXISTS 替代 IN 可提升性能（尤其对大数据集）。
子查询结果限制
- 标量子查询必须返回单值，否则会报错（如 WHERE salary = (SELECT ...) 中子查询返回多行）。
避免过度嵌套
- 多层嵌套子查询会降低可读性和维护性，可拆分为临时表或公共表达式（CTE，MySQL 8.0+）。
NULL 值处理
- 子查询中包含 NULL 时，IN 和 NOT IN 可能返回意外结果（如 NOT IN (1, NULL) 始终为 FALSE）。

五、子查询 vs 连接查询（JOIN）

场景	子查询	JOIN
结果需求	需要中间值（如聚合结果）	需要直接关联字段的完整行数据
可读性	适合简单逻辑（如单条件过滤）	适合多表复杂关联
性能	关联子查询可能较慢	通常更高效（尤其有索引时）

六、高级用法（MySQL 8.0+）

公共表达式（CTE）

使用 WITH 子句简化复杂子查询：

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary 
    FROM employees 
    GROUP BY dept_id
)
SELECT * FROM dept_avg WHERE avg_salary > 10000;

总结

核心作用：辅助主查询完成复杂逻辑，如过滤、计算中间值。
优先选择：非关联子查询 > 关联子查询，能用 JOIN 时优先用 JOIN。
性能关键：避免多层嵌套，优化为 EXISTS 或 JOIN。
适用场景：动态条件过滤、数据分阶段处理、结果集复用。