SQL 语言日常使用 100 条经典命令(收藏级)

无论是开发、运维还是数据库工程师，SQL 语言都是最常用的技能之一。本文整理了 100 条经典 SQL 命令，覆盖 MySQL / PostgreSQL / SQL Server / Oracle 常见方言，几乎囊括了日常工作场景。建议收藏并在日常开发中查阅使用。

一、数据库与表管理（1-15）

1. 创建数据库

CREATE DATABASE company_db;

2. 删除数据库

DROP DATABASE company_db;

3. 切换数据库（MySQL）

USE company_db;

4. 查看所有数据库

SHOW DATABASES;

5. 创建数据表

CREATE TABLE employees (

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(100),

age INT,

hire_date DATE

);

6. 删除表

DROP TABLE employees;

7. 修改表名

ALTER TABLE employees RENAME TO staff;

8. 增加字段

ALTER TABLE employees ADD COLUMN email VARCHAR(100);

9. 修改字段类型

ALTER TABLE employees MODIFY age SMALLINT;

10. 删除字段

ALTER TABLE employees DROP COLUMN email;

11. 查看表结构

DESCRIBE employees;

12. 查看建表语句（MySQL）

SHOW CREATE TABLE employees;

13. 清空表数据（不删除表结构）

TRUNCATE TABLE employees;

14. 复制表结构

CREATE TABLE employees_backup LIKE employees;

15. 复制表结构+数据

CREATE TABLE employees_copy AS SELECT * FROM employees;

二、数据插入与更新（16-30）

16. 插入一行

INSERT INTO employees (name, age, hire_date) VALUES ('Tom', 28, '2025-01-01');

17. 插入多行

INSERT INTO employees (name, age) VALUES ('Alice', 30), ('Bob', 35);

18. 根据查询结果插入

INSERT INTO employees_backup SELECT * FROM employees WHERE age > 30;

19. 更新单行

UPDATE employees SET age = 29 WHERE id = 1;

20. 更新多行

UPDATE employees SET age = age + 1 WHERE age < 30;

21. 删除数据

DELETE FROM employees WHERE age < 20;

22. 防止误删：开启安全模式（MySQL）

SET SQL_SAFE_UPDATES = 1;

23. UPSERT（MySQL ON DUPLICATE KEY）

INSERT INTO employees (id, name) VALUES (1, 'Tom')

ON DUPLICATE KEY UPDATE name='Tom';

24. UPSERT（PostgreSQL ON CONFLICT）

INSERT INTO employees (id, name) VALUES (1, 'Tom')

ON CONFLICT (id) DO UPDATE SET name='Tom';

25. 插入忽略冲突（MySQL）

INSERT IGNORE INTO employees (id, name) VALUES (1, 'Tom');

三、基本查询（31-50）

31. 查询所有列

SELECT * FROM employees;

32. 查询指定列

SELECT name, age FROM employees;

33. 加别名

SELECT name AS employee_name FROM employees;

34. 条件查询

SELECT * FROM employees WHERE age > 30;

35. 多条件 AND/OR

SELECT * FROM employees WHERE age > 30 AND name LIKE 'T%';

36. 排序

SELECT * FROM employees ORDER BY age DESC;

37. 限制条数

SELECT * FROM employees LIMIT 10;

38. 分页查询（MySQL）

SELECT * FROM employees LIMIT 10 OFFSET 20;

39. 去重查询

SELECT DISTINCT age FROM employees;

40. 模糊匹配

SELECT * FROM employees WHERE name LIKE '%Tom%';

41. 范围查询（BETWEEN）

SELECT * FROM employees WHERE age BETWEEN 25 AND 35;

42. 集合查询（IN）

SELECT * FROM employees WHERE age IN (25, 30, 35);

43. NULL 判断

SELECT * FROM employees WHERE email IS NULL;

44. 聚合函数（COUNT, SUM, AVG, MIN, MAX）

SELECT COUNT(*), AVG(age) FROM employees;

45. 分组查询（GROUP BY）

SELECT age, COUNT(*) FROM employees GROUP BY age;

46. 分组过滤（HAVING）

SELECT age, COUNT(*) FROM employees GROUP BY age HAVING COUNT(*) > 1;

47. 子查询（WHERE IN）

SELECT * FROM employees WHERE id IN (SELECT id FROM employees_backup);

48. EXISTS 子查询

SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM employees_backup b WHERE b.id = e.id);

49. CASE 表达式

SELECT name,

CASE WHEN age < 30 THEN '青年'

WHEN age BETWEEN 30 AND 50 THEN '中年'

ELSE '老年' END AS age_group

FROM employees;

50. JOIN 查询

SELECT e.name, d.dept_name

FROM employees e

JOIN departments d ON e.dept_id = d.id;

四、约束与索引（51-65）

51. 设置主键

ALTER TABLE employees ADD PRIMARY KEY (id);

52. 唯一约束

ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

53. 外键约束

ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id);

54. 检查约束（CHECK）

ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);

55. 创建索引

CREATE INDEX idx_age ON employees(age);

56. 唯一索引

CREATE UNIQUE INDEX idx_email ON employees(email);

57. 组合索引

CREATE INDEX idx_name_age ON employees(name, age);

58. 删除索引（MySQL）

DROP INDEX idx_age ON employees;

59. 查看索引

SHOW INDEX FROM employees;

60. 聚簇索引（SQL Server）

CREATE CLUSTERED INDEX idx_id ON employees(id);

五、函数与表达式（66-80）

61. 字符串拼接

SELECT CONCAT(name, '-', age) FROM employees;

62. 字符串长度

SELECT LENGTH(name) FROM employees;

63. 子字符串

SELECT SUBSTRING(name, 1, 3) FROM employees;

64. 转大写/小写

SELECT UPPER(name), LOWER(name) FROM employees;

65. 去空格

SELECT TRIM(name) FROM employees;

66. 数学函数

SELECT ABS(-5), ROUND(3.14159, 2), CEIL(2.1), FLOOR(2.9);

67. 日期函数（MySQL）

SELECT NOW(), CURDATE(), YEAR(NOW()), MONTH(NOW());

68. 日期加减

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);

69. DATEDIFF

SELECT DATEDIFF(NOW(), hire_date) FROM employees;

70. 随机数

SELECT RAND();

71. COALESCE（取第一个非空值）

SELECT COALESCE(email, 'no-email') FROM employees;

72. IFNULL

SELECT IFNULL(email, 'N/A') FROM employees;

73. CAST 类型转换

SELECT CAST(age AS CHAR) FROM employees;

74. 分组拼接（MySQL）

SELECT dept_id, GROUP_CONCAT(name) FROM employees GROUP BY dept_id;

75. 窗口函数 ROW_NUMBER()

SELECT name, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY age DESC) AS rn FROM employees;

76. RANK() 排名

SELECT name, RANK() OVER (ORDER BY age DESC) FROM employees;

77. NTILE 分组

SELECT name, NTILE(4) OVER (ORDER BY age) AS quartile FROM employees;

78. LAG/LEAD

SELECT name, LAG(age, 1) OVER (ORDER BY id) FROM employees;

79. JSON 解析（MySQL 5.7+）

SELECT JSON_EXTRACT('{"a":1,"b":2}', '$.a');

80. 正则匹配

SELECT * FROM employees WHERE name REGEXP '^T.*';

六、事务与锁（81-90）

81. 开启事务

START TRANSACTION;

82. 提交事务

COMMIT;

83. 回滚事务

ROLLBACK;

84. 保存点

SAVEPOINT sp1;

85. 回滚到保存点

ROLLBACK TO sp1;

86. 设置事务隔离级别

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

87. 查看事务隔离级别（MySQL）

SELECT @@transaction_isolation;

88. 加排他锁

SELECT * FROM employees WHERE id=1 FOR UPDATE;

89. 加共享锁

SELECT * FROM employees WHERE id=1 LOCK IN SHARE MODE;

90. 死锁检测（MySQL InnoDB）

SHOW ENGINE INNODB STATUS;

七、权限与安全（91-100）

91. 创建用户

CREATE USER 'dev'@'localhost' IDENTIFIED BY '123456';

92. 授予权限

GRANT SELECT, INSERT ON company_db.* TO 'dev'@'localhost';

93. 回收权限

REVOKE INSERT ON company_db.* FROM 'dev'@'localhost';

94. 查看权限

SHOW GRANTS FOR 'dev'@'localhost';

95. 删除用户

DROP USER 'dev'@'localhost';

96. 修改密码

ALTER USER 'dev'@'localhost' IDENTIFIED BY 'newpwd';

97. 只读账户

GRANT SELECT ON company_db.* TO 'readonly'@'%';

98. 锁表（MySQL）

LOCK TABLE employees READ;

99. 解锁表

UNLOCK TABLES;

100. 审计日志（MySQL 8.0+）

SELECT * FROM performance_schema.events_statements_history;

总结

本文整理了 SQL 日常工作最常用的 100 条命令，涵盖：

• 数据库/表管理
• 数据增删改查
• 约束与索引
• 常用函数
• 窗口函数
• 事务与锁
• 权限管理与安全

学会并灵活使用这些命令，基本能覆盖 90% 以上的开发与运维场景。