SQL100条经典，满足日常操作和运维

SQL 语言日常使用 100 条经典命令（收藏级）
无论是开发、运维还是数据库工程师，SQL 语言都是最常用的技能之一。本文整理了 100 条经典 SQL 命令，覆盖 MySQL / PostgreSQL / SQL Server / Oracle 常见方言，几乎囊括了日常工作场景。建议收藏并在日常开发中查阅使用。
一、数据库与表管理（1-15）
创建数据库B
CREATE DATABASE company_db;
删除数据库B
DROP DATABASE company_db;
切换数据库（MySQL）B
USE company_db;
查看所有数据库B
SHOW DATABASES;
创建数据表B
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
age INT,
hire_date DATE
);
删除表B
DROP TABLE employees;
修改表名B
ALTER TABLE employees RENAME TO staff;
增加字段B
ALTER TABLE employees ADD COLUMN email VARCHAR(100);
修改字段类型B
ALTER TABLE employees MODIFY age SMALLINT;
删除字段B
ALTER TABLE employees DROP COLUMN email;
查看表结构B
DESCRIBE employees;
查看建表语句（MySQL）B
SHOW CREATE TABLE employees;
清空表数据（不删除表结构）B
TRUNCATE TABLE employees;
复制表结构B
CREATE TABLE employees_backup LIKE employees;
复制表结构+数据B
CREATE TABLE employees_copy AS SELECT * FROM employees;
二、数据插入与更新（16-30）
插入一行B
INSERT INTO employees (name, age, hire_date) VALUES (‘Tom’, 28, ‘2025-01-01’);
插入多行B
INSERT INTO employees (name, age) VALUES (‘Alice’, 30), (‘Bob’, 35);
根据查询结果插入B
INSERT INTO employees_backup SELECT * FROM employees WHERE age > 30;
更新单行B
UPDATE employees SET age = 29 WHERE id = 1;
更新多行B
UPDATE employees SET age = age + 1 WHERE age < 30;
删除数据B
DELETE FROM employees WHERE age < 20;
防止误删：开启安全模式（MySQL）B
SET SQL_SAFE_UPDATES = 1;
UPSERT（MySQL ON DUPLICATE KEY）B
INSERT INTO employees (id, name) VALUES (1, ‘Tom’)
ON DUPLICATE KEY UPDATE name=‘Tom’;
UPSERT（PostgreSQL ON CONFLICT）B
INSERT INTO employees (id, name) VALUES (1, ‘Tom’)
ON CONFLICT (id) DO UPDATE SET name=‘Tom’;
插入忽略冲突（MySQL）B
INSERT IGNORE INTO employees (id, name) VALUES (1, ‘Tom’);
三、基本查询（31-50）
查询所有列B
SELECT * FROM employees;
查询指定列B
SELECT name, age FROM employees;
加别名B
SELECT name AS employee_name FROM employees;
条件查询B
SELECT * FROM employees WHERE age > 30;
多条件 AND/ORB
SELECT * FROM employees WHERE age > 30 AND name LIKE ‘T%’;
排序B
SELECT * FROM employees ORDER BY age DESC;
限制条数B
SELECT * FROM employees LIMIT 10;
分页查询（MySQL）B
SELECT * FROM employees LIMIT 10 OFFSET 20;
去重查询B
SELECT DISTINCT age FROM employees;
模糊匹配B
SELECT * FROM employees WHERE name LIKE ‘%Tom%’;
范围查询（BETWEEN）B
SELECT * FROM employees WHERE age BETWEEN 25 AND 35;
集合查询（IN）B
SELECT * FROM employees WHERE age IN (25, 30, 35);
NULL 判断B
SELECT * FROM employees WHERE email IS NULL;
聚合函数（COUNT, SUM, AVG, MIN, MAX）B
SELECT COUNT(), AVG(age) FROM employees;
分组查询（GROUP BY）B
SELECT age, COUNT() FROM employees GROUP BY age;
分组过滤（HAVING）B
SELECT age, COUNT() FROM employees GROUP BY age HAVING COUNT() > 1;
子查询（WHERE IN）B
SELECT * FROM employees WHERE id IN (SELECT id FROM employees_backup);
EXISTS 子查询B
SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM employees_backup b WHERE b.id = e.id);
CASE 表达式B
SELECT name,
CASE WHEN age < 30 THEN ‘青年’
WHEN age BETWEEN 30 AND 50 THEN ‘中年’
ELSE ‘老年’ END AS age_group
FROM employees;
JOIN 查询B
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;
四、约束与索引（51-65）
设置主键B
ALTER TABLE employees ADD PRIMARY KEY (id);
唯一约束B
ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);
外键约束B
ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id);
检查约束（CHECK）B
ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);
创建索引B
CREATE INDEX idx_age ON employees(age);
唯一索引B
CREATE UNIQUE INDEX idx_email ON employees(email);
组合索引B
CREATE INDEX idx_name_age ON employees(name, age);
删除索引（MySQL）B
DROP INDEX idx_age ON employees;
查看索引B
SHOW INDEX FROM employees;
聚簇索引（SQL Server）B
CREATE CLUSTERED INDEX idx_id ON employees(id);
五、函数与表达式（66-80）
字符串拼接B
SELECT CONCAT(name, ‘-’, age) FROM employees;
字符串长度B
SELECT LENGTH(name) FROM employees;
子字符串B
SELECT SUBSTRING(name, 1, 3) FROM employees;
转大写/小写B
SELECT UPPER(name), LOWER(name) FROM employees;
去空格B
SELECT TRIM(name) FROM employees;
数学函数B
SELECT ABS(-5), ROUND(3.14159, 2), CEIL(2.1), FLOOR(2.9);
日期函数（MySQL）B
SELECT NOW(), CURDATE(), YEAR(NOW()), MONTH(NOW());
日期加减B
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
DATEDIFFB
SELECT DATEDIFF(NOW(), hire_date) FROM employees;
随机数B
SELECT RAND();
COALESCE（取第一个非空值）B
SELECT COALESCE(email, ‘no-email’) FROM employees;
IFNULLB
SELECT IFNULL(email, ‘N/A’) FROM employees;
CAST 类型转换B
SELECT CAST(age AS CHAR) FROM employees;
分组拼接（MySQL）B
SELECT dept_id, GROUP_CONCAT(name) FROM employees GROUP BY dept_id;
窗口函数 ROW_NUMBER()B
SELECT name, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY age DESC) AS rn FROM employees;
RANK() 排名B
SELECT name, RANK() OVER (ORDER BY age DESC) FROM employees;
NTILE 分组B
SELECT name, NTILE(4) OVER (ORDER BY age) AS quartile FROM employees;
LAG/LEADB
SELECT name, LAG(age, 1) OVER (ORDER BY id) FROM employees;
JSON 解析（MySQL 5.7+）B
SELECT JSON_EXTRACT(’{“a”:1,“b”:2}’, ‘$.a’);
正则匹配B
SELECT * FROM employees WHERE name REGEXP ‘^T.’;
六、事务与锁（81-90）
开启事务B
START transaction;
提交事务B
COMMIT;
回滚事务B
ROLLBACK;
保存点B
SAVEPOINT sp1;
回滚到保存点B
ROLLBACK TO sp1;
设置事务隔离级别B
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
查看事务隔离级别（MySQL）B
SELECT @@transaction_isolation;
加排他锁B
SELECT * FROM employees WHERE id=1 FOR UPDATE;
加共享锁B
SELECT * FROM employees WHERE id=1 LOCK IN SHARE MODE;
死锁检测（MySQL InnoDB）B
SHOW ENGINE INNODB STATUS;
七、权限与安全（91-100）
创建用户B
CREATE USER ‘dev’@‘localhost’ IDENTIFIED BY ‘123456’;
授予权限B
GRANT SELECT, INSERT ON company_db. TO ‘dev’@‘localhost’;
回收权限B
REVOKE INSERT ON company_db.* FROM ‘dev’@‘localhost’;
查看权限B
SHOW GRANTS FOR ‘dev’@‘localhost’;
删除用户B
DROP USER ‘dev’@‘localhost’;
修改密码B
ALTER USER ‘dev’@‘localhost’ IDENTIFIED BY ‘newpwd’;
只读账户B
GRANT SELECT ON company_db.* TO ‘readonly’@’%’;
锁表（MySQL）B
LOCK TABLE employees READ;
解锁表B
UNLOCK TABLES;
审计日志（MySQL 8.0+）B
SELECT * FROM performance_schema.events_statements_history;
总结
本文整理了 SQL 日常工作最常用的 100 条命令，涵盖：
数据库/表管理B
数据增删改查B
约束与索引B
常用函数B
窗口函数B
事务与锁B
权限管理与安全B
学会并灵活使用这些命令，基本能覆盖 90% 以上的开发与运维场景。