四、数据操作（CRUD）
4.1 INSERT（插入数据）
-- 插入单条数据
INSERT INTO users (username, email, password, age)
VALUES ('张三', 'zhang@example.com', 'hashed_pwd', 25);
-- 插入多条数据
INSERT INTO users (username, email, password, age) VALUES
('李四', 'li@example.com', 'hashed_pwd', 30),
('王五', 'wang@example.com', 'hashed_pwd', 28);
-- 插入部分字段
INSERT INTO users (username, email, password)
VALUES ('赵六', 'zhao@example.com', 'hashed_pwd');
-- 插入并返回自增ID
INSERT INTO users (username, email, password) VALUES ('test', 'test@example.com', 'pwd');
SELECT LAST_INSERT_ID();
-- REPLACE（如果主键存在则替换，否则插入）
REPLACE INTO users (id, username, email, password)
VALUES (1, '新张三', 'new@example.com', 'new_pwd');
-- INSERT IGNORE（忽略重复键错误）
INSERT IGNORE INTO users (id, username, email, password)
VALUES (1, '重复', 'dup@example.com', 'pwd');
-- INSERT ON DUPLICATE KEY UPDATE（存在则更新）
INSERT INTO users (id, username, email, password)
VALUES (1, '更新', 'update@example.com', 'pwd')
ON DUPLICATE KEY UPDATE
username = VALUES(username),
email = VALUES(email);
4.2 SELECT（查询数据）
-- 基础查询
SELECT * FROM users;
SELECT id, username, email FROM users;
SELECT username AS 用户名, email AS 邮箱 FROM users;
-- 条件查询
SELECT * FROM users WHERE age > 20;
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE username LIKE '张%'; -- 以张开头的
SELECT * FROM users WHERE username LIKE '%三'; -- 以三结尾的
SELECT * FROM users WHERE username LIKE '%三%'; -- 包含三的
SELECT * FROM users WHERE email IN ('a@b.com', 'c@d.com');
SELECT * FROM users WHERE age IS NULL;
SELECT * FROM users WHERE age IS NOT NULL;
-- 排序
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY age DESC, username ASC;
-- 分页
SELECT * FROM users LIMIT 10 OFFSET 0; -- 第1页
SELECT * FROM users LIMIT 10 OFFSET 10; -- 第2页
SELECT * FROM users LIMIT 10, 10; -- 另一种写法
-- 去重
SELECT DISTINCT age FROM users;
-- 聚合函数
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT age) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age), SUM(age) FROM users;
-- 分组查询
SELECT gender, COUNT(*) AS count, AVG(age) AS avg_age
FROM users
GROUP BY gender;
-- 分组后筛选
SELECT gender, COUNT(*) AS count
FROM users
GROUP BY gender
HAVING count > 1;
4.3 UPDATE（更新数据）
-- 更新单条
UPDATE users SET age = 26 WHERE id = 1;
-- 更新多条
UPDATE users SET status = 'active' WHERE age >= 18;
-- 使用表达式更新
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';
UPDATE users SET age = age + 1 WHERE birthday = CURDATE();
-- 多字段更新
UPDATE users
SET username = '新名字', email = 'new@example.com'
WHERE id = 1;
-- 限制更新行数
UPDATE users SET status = 'inactive' LIMIT 10;
4.4 DELETE（删除数据）
-- 删除指定记录
DELETE FROM users WHERE id = 1;
-- 条件删除
DELETE FROM users WHERE age > 60;
-- 删除所有记录
DELETE FROM users;
TRUNCATE TABLE users; -- 更快
-- 限制删除行数
DELETE FROM users WHERE status = 'inactive' LIMIT 100;
-- 多表删除
DELETE u, o FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 'deleted';
五、查询高级技巧
5.1 连接查询（JOIN）
-- 准备示例数据
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2)
);
-- INNER JOIN（内连接，只返回匹配的记录）
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- LEFT JOIN（左连接，返回左表所有记录）
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- RIGHT JOIN（右连接，返回右表所有记录）
SELECT u.name, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- FULL OUTER JOIN（MySQL 不支持，用 UNION 模拟）
SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.amount FROM users u RIGHT JOIN orders o ON u.id = o.user_id;
-- CROSS JOIN（笛卡尔积）
SELECT * FROM users CROSS JOIN orders;
-- 自连接（同一张表连接自己）
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- 多表连接
SELECT u.name, o.order_date, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE u.id = 1;
5.2 子查询
-- WHERE 子句中的子查询
SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);
-- IN 子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- EXISTS 子查询
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- FROM 子句中的子查询（派生表）
SELECT AVG(total_amount)
FROM (
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
) AS user_totals;
-- SELECT 子句中的子查询（标量子查询）
SELECT name,
(SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;
-- 相关子查询
SELECT * FROM products p1
WHERE price > (SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category);
5.3 联合查询（UNION）
-- UNION（去重）
SELECT name, email FROM users WHERE age < 18
UNION
SELECT name, email FROM users WHERE age > 60;
-- UNION ALL（不去重，效率更高）
SELECT user_id FROM orders WHERE amount > 1000
UNION ALL
SELECT user_id FROM orders WHERE amount < 100;
-- 实际应用：合并不同来源的数据
SELECT 'vip' AS type, name, email FROM vip_users
UNION ALL
SELECT 'normal', name, email FROM normal_users;
5.4 窗口函数（MySQL 8.0+）
-- ROW_NUMBER() - 行号
SELECT name, department, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;
-- RANK() - 排名（有并列）
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;
-- DENSE_RANK() - 密集排名
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
-- LAG/LEAD - 前后行
SELECT name, salary,
LAG(salary, 1, 0) OVER (ORDER BY salary) AS prev_salary,
LEAD(salary, 1, 0) OVER (ORDER BY salary) AS next_salary
FROM employees;
-- 聚合窗口函数
SELECT name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg,
SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;
-- NTILE - 分桶
SELECT name, salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;
六、索引与优化
6.1 索引类型
-- 普通索引
CREATE INDEX idx_name ON users(name);
ALTER TABLE users ADD INDEX idx_name(name);
-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
-- 主键索引（特殊的唯一索引）
ALTER TABLE users ADD PRIMARY KEY (id);
-- 复合索引
CREATE INDEX idx_name_age ON users(name, age);
-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content, title);
-- 空间索引
CREATE SPATIAL INDEX idx_location ON stores(location);
6.2 索引使用原则
-- 最左前缀原则
-- 索引 (a, b, c)
-- 能使用索引的查询：
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
-- 不能使用索引的查询：
WHERE b = 2
WHERE c = 3
WHERE a = 1 AND c = 3
-- 索引失效的情况
-- 1. 使用函数
WHERE YEAR(created_at) = 2023 -- 改为 created_at BETWEEN '2023-01-01' AND '2023-12-31'
-- 2. 隐式类型转换
WHERE phone = 13800138000 -- phone 是 VARCHAR，改为字符串
-- 3. 使用 != 或 <>
WHERE status != 'active' -- 索引效果差
-- 4. LIKE 以 % 开头
WHERE name LIKE '%张三' -- 失效
WHERE name LIKE '张三%' -- 有效
-- 5. OR 条件
WHERE age = 18 OR name = '张三' -- 除非两个字段都有索引
-- 6. NOT IN 或 NOT EXISTS
WHERE id NOT IN (SELECT ...)
6.3 EXPLAIN 执行计划
-- 分析查询语句
EXPLAIN SELECT * FROM users WHERE age > 20;
-- 输出字段说明：
-- id: 执行顺序
-- select_type: 查询类型（SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION）
-- table: 表名
-- type: 访问类型（性能从高到低）
-- system > const > eq_ref > ref > range > index > ALL
-- possible_keys: 可能使用的索引
-- key: 实际使用的索引
-- key_len: 索引长度
-- rows: 扫描行数
-- Extra: 额外信息（Using index, Using where, Using filesort, Using temporary）
-- 详细分析
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 20;
-- 优化建议
-- 1. 避免 ALL 全表扫描，至少达到 range 级别
-- 2. 注意 Extra 中的 Using filesort 和 Using temporary
-- 3. 关注 rows 扫描行数
6.4 查询优化技巧
-- 1. 只查询需要的字段
-- 错误
SELECT * FROM users;
-- 正确
SELECT id, name, email FROM users;
-- 2. 避免使用 SELECT DISTINCT，用 GROUP BY 替代
-- 错误
SELECT DISTINCT user_id FROM orders;
-- 正确
SELECT user_id FROM orders GROUP BY user_id;
-- 3. 使用 EXISTS 替代 IN
-- 当子查询结果集很大时
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- 4. 优化分页查询
-- 深分页问题：LIMIT 100000, 10 很慢
-- 优化方案1：使用子查询
SELECT * FROM users
WHERE id > (SELECT id FROM users ORDER BY id LIMIT 100000, 1)
LIMIT 10;
-- 优化方案2：使用 JOIN
SELECT * FROM users
INNER JOIN (
SELECT id FROM users ORDER BY id LIMIT 100000, 10
) AS tmp ON users.id = tmp.id;
-- 5. 批量操作
-- 错误
for (id in ids) {
UPDATE users SET status = 1 WHERE id = id;
}
-- 正确
UPDATE users SET status = 1 WHERE id IN (1,2,3,4,5);
-- 6. 使用覆盖索引
-- 索引包含查询所需的所有字段，避免回表
CREATE INDEX idx_name_age_email ON users(name, age, email);
SELECT name, age, email FROM users WHERE name = '张三';
-- 7. 批量插入优化
INSERT INTO users (name, email) VALUES
('a', 'a@example.com'),
('b', 'b@example.com'),
-- ... 批量插入，减少事务开销
