SQL 命令速查 - 常用命令大全

查询基础

查询全表（生产慎用）

SELECT * FROM <table>;

条件查询

SELECT * FROM <table> WHERE <col> = <val>;

去重查询

SELECT DISTINCT <col> FROM <table>;

排序查询

SELECT * FROM <table> ORDER BY <col> ASC, <col2> DESC;

分组统计

SELECT <col>, COUNT(*) FROM <table> GROUP BY <col>;

分组后过滤

SELECT <col>, COUNT(*) FROM <table> GROUP BY <col> HAVING COUNT(*) > 1;

内连接

SELECT a.*, b.* FROM a JOIN b ON a.id = b.a_id;

左连接

SELECT a.*, b.* FROM a LEFT JOIN b ON a.id = b.a_id;

结果集与分页

合并结果集

SELECT <col> FROM <table1> UNION SELECT <col> FROM <table2>;

标准分页（前 N 条）

`SELECT * FROM <table> FETCH FIRST 10 ROWS ONLY;` (Oracle 12c+/PG；MySQL 用 LIMIT 见 MySQL 方言)

标准分页（跳过 M 取 N 条）

`SELECT * FROM <table> OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;` (Oracle 12c+/PG)

增删改

插入数据

INSERT INTO <table> (col1, col2) VALUES (v1, v2);

按查询结果插入

INSERT INTO <table> (col1, col2) SELECT col1, col2 FROM <table2> WHERE <cond>;

更新数据 ⚠️ 无 WHERE 将更新全表

UPDATE <table> SET <col> = <val> WHERE <cond>;

删除数据 ⚠️ 无 WHERE 将清空全表

DELETE FROM <table> WHERE <cond>;

表结构与索引视图

创建表（基础列定义）

CREATE TABLE <table> (id BIGINT PRIMARY KEY, name VARCHAR(64) NOT NULL);

添加字段

ALTER TABLE <table> ADD COLUMN <col> VARCHAR(64);

删除字段 ⚠️

ALTER TABLE <table> DROP COLUMN <col>;

删除表 ⚠️

DROP TABLE <table>;

清空表 ⚠️ 不可回滚

TRUNCATE TABLE <table>;

创建普通索引

CREATE INDEX idx_<col> ON <table> (<col>);

创建唯一索引

CREATE UNIQUE INDEX uk_<col> ON <table> (<col>);

删除索引

`DROP INDEX idx_<col>;` (MySQL 用 `ALTER TABLE <table> DROP INDEX idx_<col>;`)

创建视图

CREATE VIEW v_<name> AS SELECT <col> FROM <table> WHERE <cond>;

事务

提交事务

COMMIT;

回滚事务

ROLLBACK;

📖 SQL 命令速查