Ai 摘要：本文汇总了开发中常用的MySQL语句，涵盖基础查询、增删改操作、条件筛选、模糊查询、排序分页、分组统计、多表连接及子查询等核心功能，并提供了典型示例，是数据库操作的实用参考手册。

✅ 1. 基础查询 SELECT

SELECT * FROM users;
SELECT id, name, age FROM users;

带条件

SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE name = 'Tom';

多条件

SELECT * FROM users WHERE age > 18 AND status = 1;
SELECT * FROM users WHERE city = 'SH' OR city = 'BJ';

✅ 2. 增删改（CRUD）

INSERT

INSERT INTO users (name, age, email) VALUES ('Tom', 20, 'a@b.com');

UPDATE

UPDATE users SET age = 25 WHERE id = 1;

DELETE

DELETE FROM users WHERE id = 1;

⚠ 注意 DELETE 要带 WHERE，否则清空整表！

✅ 3. 模糊查询（LIKE）

SELECT * FROM users WHERE name LIKE '%Tom%';
SELECT * FROM users WHERE email LIKE '%.com';

✅ 4. 排序 ORDER BY

SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users ORDER BY created_at ASC;

✅ 5. 分页 LIMIT

SELECT * FROM users LIMIT 10;          -- 前 10 条
SELECT * FROM users LIMIT 20, 10;      -- 从第 20 条开始取 10 条

✅ 6. 分组 GROUP BY + HAVING

SELECT city, COUNT(*) AS total 
FROM users 
GROUP BY city;

带过滤条件（HAVING）：

SELECT city, COUNT(*) AS total 
FROM users 
GROUP BY city
HAVING total > 10;

✅ 7. 去重 DISTINCT

SELECT DISTINCT city FROM users;

✅ 8. JOIN（多表查询）

INNER JOIN（匹配交集）

SELECT u.name, o.order_no
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

LEFT JOIN（左表全部 + 匹配右表）

SELECT u.name, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

RIGHT JOIN（右表全部 + 匹配左表）

SELECT u.name, o.order_no
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

✅ 9. COUNT、SUM、AVG 等聚合函数

SELECT COUNT(*) FROM orders;
SELECT SUM(price) FROM orders;
SELECT AVG(score) FROM students;
SELECT MAX(age), MIN(age) FROM users;

✅ 10. IN / NOT IN

SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE city NOT IN ('BJ', 'SH');

✅ 11. BETWEEN（区间查询）

SELECT * FROM users WHERE age BETWEEN 18 AND 30;

✅ 12. 子查询 SubQuery

例：查询下单次数最多的用户

SELECT * FROM users 
WHERE id = (
    SELECT user_id FROM orders GROUP BY user_id ORDER BY COUNT(*) DESC LIMIT 1
);

✅ 13. 创建表（最常用格式）

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

✅ 14. 修改表

添加列：

ALTER TABLE users ADD COLUMN avatar VARCHAR(255);

修改列类型：

ALTER TABLE users MODIFY age INT DEFAULT 0;

删除列：

ALTER TABLE users DROP COLUMN avatar;

✅ 15. 索引优化相关（最常用）

创建普通索引：

CREATE INDEX idx_user_age ON users(age);

唯一索引：

CREATE UNIQUE INDEX idx_email ON users(email);

删除索引：

DROP INDEX idx_user_age ON users;