掌握数据库核心：100条必备SQL语句精讲（附实战指南）

摘要：本文系统梳理100条高频SQL语句，覆盖基础操作、查询优化、高级特性等核心场景。所有语句均标注数据库差异、使用场景与避坑指南，附带安全实践建议，助你从入门到高效实战。
适用人群：开发工程师、数据分析师、DBA、SQL学习者
数据库兼容性：以ANSI SQL为主，关键差异标注（MySQL 8.0+ / PostgreSQL 14+ / SQL Server 2019+）

---

📌 引言：为什么需要这100条？

SQL是数据世界的“通用语”。掌握高频语句不仅能提升开发效率，更是性能优化、安全防护的基石。本文摒弃冗余理论，聚焦可直接复用的实战语句，每条包含：

• ✅ 标准语法示例
• ✅ 核心说明与场景
• ✅ 数据库差异提示
• ✅ ⚠️ 关键注意事项

💡 重要前提：

1. 所有DROP/DELETE/TRUNCATE操作请先在测试环境验证
2. 生产环境务必使用参数化查询防SQL注入
3. 操作前执行BEGIN;（支持事务的数据库）或备份

---

🔑 一、数据库与表结构（1-10）

1. CREATE DATABASE IF NOT EXISTS shop;
   创建数据库（MySQL），PostgreSQL用CREATE DATABASE shop;
2. DROP DATABASE IF EXISTS test_db; ⚠️ 高危操作！
3. USE shop; （MySQL） / \c shop （PostgreSQL psql命令）
4. CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL);
   MySQL自增；PostgreSQL用SERIAL或GENERATED ALWAYS AS IDENTITY
5. DROP TABLE IF EXISTS temp_data; ⚠️ 永久删除表结构与数据
6. ALTER TABLE users ADD COLUMN email VARCHAR(100) UNIQUE;
   添加带唯一约束的列
7. ALTER TABLE orders DROP COLUMN deprecated_field;
   删除列（SQL Server需ALTER TABLE ... DROP COLUMN）
8. ALTER TABLE products MODIFY price DECIMAL(10,2); （MySQL）
   PostgreSQL: ALTER COLUMN price TYPE NUMERIC(10,2)
9. RENAME TABLE old_tbl TO new_tbl; （MySQL） / ALTER TABLE old RENAME TO new; （PG）
10. SHOW COLUMNS FROM users; （MySQL） / \d users （PG） / sp_columns users （SQL Server）

---

🔍 二、基础查询与过滤（11-30）

11. SELECT id, name FROM customers; ✨ 避免SELECT *（减少IO，提升性能）
12. SELECT DISTINCT country FROM customers; — 去重
13. SELECT * FROM orders WHERE amount > 100 AND status = 'paid';
14. SELECT * FROM products WHERE category IN ('book', 'electronics');
15. SELECT * FROM logs WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';
16. SELECT * FROM users WHERE name LIKE '张%'; — %通配符（注意：前导%无法用索引）
17. SELECT * FROM employees WHERE department IS NULL;
18. SELECT * FROM articles WHERE title LIKE '%AI%' ESCAPE '#'; — 处理特殊字符
19. SELECT *, (price * quantity) AS total FROM order_items; — 计算列
20. SELECT name AS 姓名, email AS 邮箱 FROM users; — 中文别名（需数据库支持UTF-8）
21. SELECT * FROM products ORDER BY price DESC, name ASC; — 多字段排序
22. SELECT * FROM logs ORDER BY created_at DESC LIMIT 10 OFFSET 20; — 分页（MySQL/PG）
23. SELECT TOP 10 * FROM sales ORDER BY amount DESC; （SQL Server分页）
24. SELECT * FROM users WHERE age > 18 LIMIT 100; — 限制结果防OOM
25. SELECT COUNT(*) AS total FROM orders; — 总行数（InnoDB中COUNT(*)优于COUNT(1)）
26. SELECT COUNT(DISTINCT user_id) FROM logins; — 去重计数
27. SELECT AVG(score) FROM exams WHERE subject = 'math';
28. SELECT MAX(created_at) FROM sessions; — 最新记录时间
29. SELECT category, SUM(amount) AS total FROM sales GROUP BY category;
30. SELECT dept, AVG(salary) FROM employees GROUP BY dept HAVING AVG(salary) > 5000; — HAVING过滤分组结果

---

🔗 三、连接与子查询（31-50）

31. SELECT o.id, u.name FROM orders o INNER JOIN users u ON o.user_id = u.id; — 内连接
32. SELECT u.name, o.id FROM users u LEFT JOIN orders o ON u.id = o.user_id; — 左连接（查无订单用户）
33. SELECT * FROM table1 RIGHT JOIN table2 ON ...; — 右连接（MySQL/PG支持，较少用）
34. SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id; ⚠️ MySQL不支持，用LEFT JOIN UNION RIGHT JOIN模拟
35. SELECT e1.name, e2.name AS manager FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id; — 自连接
36. SELECT a.*, b.col, c.val FROM t1 a JOIN t2 b ON ... JOIN t3 c ON ...; — 多表连接
37. SELECT * FROM t1 CROSS JOIN t2; — 笛卡尔积（慎用！）
38. SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id; — 统计关联数据
39. SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000); — IN子查询
40. SELECT * FROM products WHERE EXISTS (SELECT 1 FROM inventory WHERE products.id = inventory.prod_id AND stock > 0); — EXISTS高效判断存在性
41. SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count FROM users; — 相关子查询
42. SELECT id, name FROM vip_users UNION SELECT id, name FROM premium_users; — 去重合并
43. SELECT id, name FROM set1 UNION ALL SELECT id, name FROM set2; — 保留重复（性能优于UNION）
44. SELECT category FROM products INTERSECT SELECT category FROM promotions; — 交集（PG/SQL Server，MySQL需用JOIN模拟）
45. WITH active_users AS (SELECT id FROM logins WHERE last_active > NOW() - INTERVAL '7 days') SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users); — CTE（公共表表达式，提升可读性）
46. SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM employees; — 窗口函数（PG/SQL Server/MySQL 8.0+）
47. SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn FROM logs; — 行号（分页/去重场景）
48. SELECT id, LAG(price) OVER (ORDER BY date) AS prev_price FROM stock_prices; — 访问前一行数据
49. SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users) t WHERE rn = 1; — 删除重复记录（保留最早）
50. SELECT generate_series(1, 10) AS num; — 生成序列（PostgreSQL特有，MySQL用递归CTE）

---

✏️ 四、数据操作（DML）（51-65）

51. INSERT INTO users (name, email) VALUES ('张三', 'zhang@example.com');
52. INSERT INTO logs (msg) VALUES ('start'), ('end'); — 多行插入（MySQL/PG）
53. INSERT INTO archive SELECT * FROM orders WHERE created_at < '2020-01-01'; — 插入查询结果
54. UPDATE users SET status = 'inactive' WHERE last_login < '2022-01-01'; ⚠️ 务必带WHERE！
55. UPDATE products p SET price = p.price * 0.9 FROM discounts d WHERE p.id = d.prod_id AND d.active = 1; — 多表更新（PG语法）
56. DELETE FROM sessions WHERE expired_at < NOW(); ⚠️ 先SELECT验证条件
57. TRUNCATE TABLE temp_cache; — 快速清空（重置自增ID，不可回滚，DDL操作）
58. REPLACE INTO config (key, value) VALUES ('theme', 'dark'); — 存在则替换（MySQL特有）
59. INSERT IGNORE INTO tags (name) VALUES ('sql'); — 忽略重复插入错误（MySQL）
60. INSERT INTO users (id, name) VALUES (1, 'test') ON CONFLICT (id) DO NOTHING; — 冲突处理（PostgreSQL UPSERT）
61. MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...; — 合并操作（SQL Server/Oracle）
62. START TRANSACTION; / BEGIN; — 显式开启事务
63. COMMIT; — 提交事务
64. ROLLBACK; — 回滚事务
65. SAVEPOINT sp1; ... ROLLBACK TO sp1; — 保存点（精细控制回滚范围）

---

⚙️ 五、函数、索引与优化（66-85）

66. SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users; — 字符串拼接（PG用||）
67. SELECT LENGTH(name), UPPER(email) FROM users;
68. SELECT DATE_FORMAT(created_at, '%Y-%m') AS month FROM orders; （MySQL） / TO_CHAR(created_at, 'YYYY-MM') （PG）
69. SELECT NOW(), CURRENT_DATE, EXTRACT(YEAR FROM birth_date) FROM users;
70. SELECT CASE WHEN age < 18 THEN 'minor' WHEN age < 60 THEN 'adult' ELSE 'senior' END AS group FROM users;
71. SELECT COALESCE(phone, 'N/A') FROM contacts; — 空值替换
72. CREATE INDEX idx_email ON users(email); — 单列索引
73. CREATE UNIQUE INDEX idx_token ON sessions(token); — 唯一索引
74. CREATE INDEX idx_name_age ON employees(name, age); — 复合索引（注意最左前缀原则）
75. CREATE INDEX idx_desc ON articles(description) USING GIN; — 全文索引（PG）
76. DROP INDEX idx_old ON table_name; （MySQL） / DROP INDEX idx_old; （PG）
77. EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100; — 分析执行计划（PG加ANALYZE）
78. SELECT /*+ INDEX(orders idx_user) */ * FROM orders WHERE user_id = 100; — 强制索引（Oracle/MySQL 8.0+）
79. ANALYZE TABLE users; （MySQL） / ANALYZE users; （PG） — 更新统计信息
80. VACUUM FULL users; — 释放磁盘空间（PostgreSQL）
81. SELECT pg_size_pretty(pg_total_relation_size('users')); — 查看表大小（PG）
82. SHOW INDEX FROM users; （MySQL） / \d+ users （PG）
83. SELECT * FROM large_table WHERE id IN (SELECT id FROM filter_table); — 优化：改用JOIN
84. SELECT id FROM orders WHERE user_id = 100; — 覆盖索引查询（避免回表）
85. SET autocommit = 0; — 关闭自动提交（MySQL，便于事务控制）

---

🛡️ 六、安全、权限与高级技巧（86-100）

86. CREATE USER 'analyst'@'%' IDENTIFIED BY 'StrongPass123!'; （MySQL）
87. GRANT SELECT, INSERT ON shop.* TO 'analyst'@'%'; — 授予权限
88. REVOKE DELETE ON shop.orders FROM 'analyst'@'%'; — 撤销权限
89. CREATE VIEW active_orders AS SELECT id, amount FROM orders WHERE status = 'active'; — 视图封装逻辑
90. CREATE MATERIALIZED VIEW sales_summary AS SELECT dept, SUM(amount) FROM sales GROUP BY dept; — 物化视图（PG，需手动刷新）
91. COMMENT ON TABLE users IS '用户主表'; — 添加注释（PG/Oracle）
92. SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'test_db'; — 终止连接（PG DBA操作）
93. SELECT * FROM users WHERE name = ?; — 参数化查询示例（应用层实现，防注入核心！）
94. SELECT * FROM products WHERE MATCH(name) AGAINST('database' IN NATURAL LANGUAGE MODE); — 全文搜索（MySQL）
95. WITH RECURSIVE tree AS (SELECT id, parent_id FROM nodes WHERE id = 1 UNION ALL SELECT n.id, n.parent_id FROM nodes n INNER JOIN tree t ON n.parent_id = t.id) SELECT * FROM tree; — 递归查询（组织架构/树形结构）
96. SELECT * FROM generate_series(1, 1000) AS id; — 快速生成测试数据（PG）
97. SELECT * FROM users WHERE id = ANY(ARRAY[1,2,3]); — 数组查询（PostgreSQL）
98. SELECT jsonb_pretty(profile) FROM users WHERE id = 1; — JSONB处理（PG 9.4+）
99. mysqldump -u root -p shop > backup.sql — 逻辑备份（命令行，非SQL但必备）
100. pg_dump -U postgres shop | gzip > shop_$(date +%F).sql.gz — PostgreSQL压缩备份（Shell命令）

---

💎 总结与行动建议

1. 刻意练习：在本地搭建MySQL/PG环境，逐条验证语句效果
2. 性能为王：复杂查询必用EXPLAIN，关注type、rows、Extra字段
3. 安全底线：
   • 永远不用字符串拼接SQL（使用PreparedStatement/ORM参数绑定）
   • 最小权限原则分配数据库账号
4. 进阶方向：
   • 深入窗口函数解决排名、累计计算问题
   • 学习执行计划解读与索引设计
   • 掌握数据库监控与慢查询分析工具

🌱 最后赠言：
“SQL不是记忆语法，而是理解数据关系的艺术。
今日熟练10条，明日高效解决100个业务问题。”

✅ 附：学习资源推荐

• 交互练习：SQLZoo、LeetCode Database
• 书籍：《SQL必知必会》《高性能MySQL》
• 工具：DBeaver（跨库客户端）、pgAdmin、MySQL Workbench

---

版权声明：本文内容可自由用于学习分享，请保留出处。生产环境操作前请严格测试！
更新日期：2026年1月（适配主流数据库最新稳定版）
反馈建议：欢迎指出勘误，共同完善这份实用指南 🌟