SQL语法整理整合（快速使用）

之前经过系统性的学习，有几份很详细的笔记，但是我发现那一份查起来比较麻烦，所以现在这一份重点在于整理常用的SQL语句，而且是方便查询的版本

这份是蓝图，后续可能还会持续更新的

我们将用SQL核心模块功能进行划分

  • 数据库操作
  • 表操作
  • 数据操作
  • 约束操作
  • 索引操作
  • 视图操作
  • 存储过程操作
  • 事务操作
  • 锁机制
  • 性能优化

一、数据库操作（DDL：数据定义语言）

1. 创建数据库

— 功能：创建指定名称的数据库，若数据库已存在则不执行（避免报错）
— create[创建] database[数据库] db_name[数据库名]
— if[如果] not[没有] exists[存在]

1
CREATE DATABASE IF NOT EXISTS shop; -- 实例：创建名为「shop」的电商数据库

2. 查看所有数据库

— 功能：查询当前数据库服务器中所有已存在的数据库
— show[显示] databases[数据库列表]

1
SHOW DATABASES; -- 实例：查看所有数据库

3. 选择（切换）数据库

— 功能：指定后续 SQL 操作的目标数据库
— use[使用] db_name[目标数据库名]

1
USE shop; -- 实例：切换到「shop」数据库，后续操作默认在该库下执行

4. 删除数据库

— 功能：删除指定数据库（谨慎操作！数据不可恢复），若数据库不存在则不执行
— drop[删除] database[数据库] if[如果] exists[存在] db_name[数据库名]

1
DROP DATABASE IF EXISTS test_db; -- 实例：删除名为「test_db」的测试数据库

二、表操作（DDL）

1. 创建表

— 功能：在当前数据库中创建表，定义字段名、数据类型、约束（主键/非空/唯一等）
— create[创建] table[表] if[如果] not[没有] exists[存在] table_name[表名] (
— col1[字段1] type1[数据类型1] constraint1[约束1], — 如：主键、非空
— col2[字段2] type2[数据类型2] constraint2[约束2],
— …
— [额外约束] — 如：外键、联合主键
— )

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS user ( -- 实例：创建「user」用户表
  id INT PRIMARY KEY AUTO_INCREMENT, -- 主键（自增）：用户唯一ID
  username VARCHAR(50) NOT NULL UNIQUE, -- 非空+唯一：用户名
  password VARCHAR(100) NOT NULL, -- 非空：加密后的密码
  phone CHAR(11) NOT NULL UNIQUE, -- 非空+唯一：手机号（固定11位）
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP -- 默认值：创建时间（当前时间）
);

2. 查看表结构

— 功能：查看指定表的字段名、数据类型、约束、默认值等详情
— desc[描述] table_name[表名] / describe[描述] table_name[表名]

1
2
DESC user; -- 实例：查看「user」表的结构
-- 或等价写法：DESCRIBE user;

3. 查看当前数据库所有表

— 功能：查询当前数据库中所有已创建的表
— show[显示] tables[表列表]

1
SHOW TABLES; -- 实例：查看「shop」数据库下的所有表

4. 修改表（添加/修改/删除字段）

（1）添加字段

— 功能：给已存在的表新增字段，并指定数据类型和约束
— alter[修改] table[表] table_name[表名] add[添加] col_name[新字段名] type[数据类型] constraint[约束]

1
ALTER TABLE user ADD email VARCHAR(100) UNIQUE; -- 实例：给「user」表加「email」字段（唯一）

（2）修改字段（数据类型/约束）

— 功能：修改已有字段的数据类型或约束（注意：若字段有数据，修改类型需兼容）
— alter[修改] table[表] table_name[表名] modify[修改字段属性] col_name[字段名] new_type[新数据类型] new_constraint[新约束]

1
ALTER TABLE user MODIFY email VARCHAR(150) UNIQUE; -- 实例：将「email」长度从100改为150

（3）删除字段

— 功能：删除表中指定字段（谨慎操作！字段数据会同步删除）
— alter[修改] table[表] table_name[表名] drop[删除] col_name[字段名]

1
ALTER TABLE user DROP email; -- 实例：删除「user」表的「email」字段

5. 删除表

— 功能：删除指定表（谨慎操作！表结构和数据全量删除），若表不存在则不执行
— drop[删除] table[表] if[如果] exists[存在] table_name[表名]

1
DROP TABLE IF EXISTS test_table; -- 实例：删除名为「test_table」的测试表

三、数据操作（DML：数据操纵语言）

1. 插入数据（INSERT）

（1）单行插入

— 功能：向表中插入一条数据，指定字段名和对应值（推荐：字段名与值顺序一致）
— insert[插入] into[到] table_name[表名] (col1[字段1], col2[字段2], …) values[值] (val1[值1], val2[值2], …)

1
2
INSERT INTO user (username, password, phone) 
VALUES ('zhangsan', '123456abc', '13800138000'); -- 实例：给「user」表插一条用户数据

（2）多行插入

— 功能：一次性插入多条数据，比单行插入更高效
— insert[插入] into[到] table_name[表名] (col1, col2, …) values (val1, val2, …), (val3, val4, …), …

1
2
3
4
INSERT INTO user (username, password, phone) 
VALUES 
  ('lisi', '654321def', '13900139000'),
  ('wangwu', 'abcdef123', '13700137000'); -- 实例：插两条用户数据

2. 查询数据（DQL：数据查询语言）

（1）基础查询（查所有字段/指定字段）

— 查所有字段：select[查询] *[所有字段] from[从] table_name[表名]

1
SELECT * FROM user; -- 实例：查询「user」表所有用户的所有信息

— 查指定字段：select[查询] col1[字段1], col2[字段2], … from[从] table_name[表名]

1
SELECT username, phone FROM user; -- 实例：只查「user」表的用户名和手机号

（2）条件查询（WHERE）

— 功能：筛选符合条件的数据（支持 =、>、<、>=、<=、!=、IN、LIKE 等运算符）
— select[查询] 字段 from[从] 表名 where[条件] 筛选条件

1
2
3
SELECT * FROM user WHERE phone = '13800138000'; -- 实例：查手机号为13800138000的用户
SELECT * FROM user WHERE id > 2; -- 实例：查ID大于2的用户
SELECT * FROM user WHERE username LIKE 'zhang%'; -- 实例：查用户名以「zhang」开头的用户（%通配符）

（3）排序查询（ORDER BY）

— 功能：按指定字段排序（ASC：升序，默认；DESC：降序）
— select[查询] 字段 from[从] 表名 order by[按…排序] col1[排序字段1] 排序方式1, col2[排序字段2] 排序方式2

1
SELECT * FROM user ORDER BY create_time DESC; -- 实例：按创建时间降序查（最新用户在前）

（4）分页查询（LIMIT）

— 功能：限制查询结果的数量（常用于分页，如第1页显示10条）
— select[查询] 字段 from[从] 表名 limit[限制数量] 起始索引[从0开始], 每页条数

1
2
SELECT * FROM user LIMIT 0, 2; -- 实例：查前2条数据（起始索引0，取2条）
SELECT * FROM user LIMIT 2, 2; -- 实例：查第2页数据（起始索引2，取2条）

（5）聚合查询（COUNT/SUM/AVG/MAX/MIN）

— 功能：对数据进行统计计算（聚合函数忽略NULL值）
— count[计数]：统计行数；sum[求和]：字段值总和；avg[平均]：字段平均值；max[最大]：字段最大值；min[最小]：字段最小值

1
2
SELECT COUNT(*) AS user_total FROM user; -- 实例：统计用户总数（AS：给结果列起别名）
SELECT MAX(id) AS max_user_id FROM user; -- 实例：查最大的用户ID

（6）分组查询（GROUP BY + HAVING）

— 功能：按指定字段分组，对每组进行聚合计算（HAVING：过滤分组结果，区别于WHERE过滤行）
— 先创建「order」订单表（用于实例）：

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `order` (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL, -- 关联user表的id（外键）
  total_price DECIMAL(10,2) NOT NULL, -- 订单总金额（保留2位小数）
  FOREIGN KEY (user_id) REFERENCES user(id) -- 外键约束：关联用户表的主键
);

— 实例：按user_id分组，统计每个用户的订单数，且只显示订单数>=2的用户

1
2
3
4
SELECT user_id, COUNT(*) AS order_count 
FROM `order` 
GROUP BY user_id 
HAVING order_count >= 2;

（7）多表连接查询

— 功能：通过连接条件关联多张表，获取更完整的数据视图

① 内连接（INNER JOIN）

— 功能：只返回两表中匹配的行（两表都有对应数据的记录）
— select[查询] 字段 from[从] 表1 [inner] join[连接] 表2 on[在…条件下] 连接条件

1
2
3
SELECT u.username, o.id AS order_id, o.total_price 
FROM user u 
INNER JOIN `order` o ON u.id = o.user_id; -- 实例：查询所有有订单的用户及其订单信息

② 左连接（LEFT JOIN）

— 功能：返回左表所有行，右表匹配则显示，否则显示NULL
— select[查询] 字段 from[从] 左表 left [outer] join[连接] 右表 on[在…条件下] 连接条件

1
2
3
SELECT u.username, o.id AS order_id, o.total_price 
FROM user u 
LEFT JOIN `order` o ON u.id = o.user_id; -- 实例：查询所有用户及其订单信息（无订单用户也显示）

③ 右连接（RIGHT JOIN）

— 功能：返回右表所有行，左表匹配则显示，否则显示NULL
— select[查询] 字段 from[从] 左表 right [outer] join[连接] 右表 on[在…条件下] 连接条件

1
2
3
SELECT u.username, o.id AS order_id, o.total_price 
FROM user u 
RIGHT JOIN `order` o ON u.id = o.user_id; -- 实例：查询所有订单及其用户信息（无用户订单也显示）

④ 自连接（SELF JOIN）

— 功能：表与自身连接，用于查询表内相关数据（如层级关系）
— 先创建有层级关系的表：

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS employee (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  manager_id INT, -- 关联自己的id，表示上级
  FOREIGN KEY (manager_id) REFERENCES employee(id)
);

— 实例：查询每个员工及其直属领导的姓名

1
2
3
SELECT e.name AS employee_name, m.name AS manager_name 
FROM employee e 
LEFT JOIN employee m ON e.manager_id = m.id;

（8）子查询

— 功能：嵌套在主查询中的查询语句，用于更复杂的条件筛选或数据获取

① WHERE子查询

— 在WHERE子句中使用子查询结果作为条件

1
2
3
-- 实例：查询订单总金额大于平均订单金额的订单
SELECT * FROM `order` 
WHERE total_price > (SELECT AVG(total_price) FROM `order`);

② FROM子查询（派生表）

— 将子查询结果作为临时表使用

1
2
3
4
5
6
7
8
-- 实例：查询每个用户的订单数和平均订单金额
SELECT u.username, order_stats.order_count, order_stats.avg_price 
FROM user u 
LEFT JOIN (
  SELECT user_id, COUNT(*) AS order_count, AVG(total_price) AS avg_price 
  FROM `order` 
  GROUP BY user_id
) AS order_stats ON u.id = order_stats.user_id;

③ EXISTS子查询

— 检查子查询是否返回结果，返回布尔值

1
2
3
4
5
-- 实例：查询至少有一个订单的用户
SELECT * FROM user u 
WHERE EXISTS (
  SELECT 1 FROM `order` o WHERE o.user_id = u.id
);

3. 更新数据（UPDATE）

— 功能：修改表中符合条件的数据（必须加WHERE！否则全表数据会被修改）
— update[更新] table_name[表名] set[设置] col1[字段1]=val1[新值1], col2[字段2]=val2[新值2] where[条件] 筛选条件

1
2
3
UPDATE user 
SET password = 'new_password123' 
WHERE id = 1; -- 实例：将ID=1的用户密码改为new_password123

4. 删除数据（DELETE）

— 功能：删除表中符合条件的数据（必须加WHERE！否则全表数据会被删除）
— delete[删除] from[从] table_name[表名] where[条件] 筛选条件

1
2
DELETE FROM user 
WHERE id = 3; -- 实例：删除ID=3的用户数据

四、约束管理（确保数据完整性）

1. 常用约束类型

约束关键字 作用说明 适用场景
PRIMARY KEY 主键（唯一标识表中每行数据，非空+唯一） 用户ID、订单ID
NOT NULL 非空（字段值不能为NULL） 用户名、密码
UNIQUE 唯一（字段值在表中不能重复，可NULL） 手机号、邮箱
FOREIGN KEY 外键（关联另一张表的主键，确保数据一致性） 订单表的user_id（关联用户表id）
DEFAULT 默认值（字段未插入值时，自动填充默认值） 创建时间（默认当前时间）

2. 给已有表添加约束

— （1）添加唯一约束

1
ALTER TABLE user ADD UNIQUE (phone); -- 实例：给「user」表的phone字段加唯一约束

— （2）添加外键约束
— alter[修改] table[表] 子表名 add[添加] foreign key[外键] (子表字段) references[关联] 父表名(父表主键)

1
ALTER TABLE `order` ADD FOREIGN KEY (user_id) REFERENCES user(id); -- 实例：订单表user_id关联用户表id

— （3）添加带级联操作的外键约束
— 级联操作：当父表数据修改/删除时，自动影响子表数据
— ON DELETE CASCADE：删除父表数据时，自动删除相关子表数据
— ON UPDATE CASCADE：更新父表主键时，自动更新相关子表外键

1
2
3
4
ALTER TABLE `order` ADD FOREIGN KEY (user_id) 
REFERENCES user(id) 
ON DELETE CASCADE 
ON UPDATE CASCADE; -- 实例：添加带级联删除和更新的外键约束

3. 删除约束

— （1）删除唯一约束（需先查约束名：SHOW INDEX FROM 表名）

1
ALTER TABLE user DROP INDEX phone; -- 实例：删除「user」表phone字段的唯一约束（约束名=字段名）

— （2）删除外键约束（需先查外键名：SHOW CREATE TABLE 表名）

1
ALTER TABLE `order` DROP FOREIGN KEY order_ibfk_1; -- 实例：删除订单表的外键（外键名=order_ibfk_1）

4. 外键约束的级联操作类型

级联类型 作用说明 适用场景
CASCADE 当父表数据修改/删除时，自动修改/删除子表关联数据 订单与订单项、用户与地址等强关联关系
SET NULL 当父表数据删除时，将子表外键字段设为NULL 用户与评论（用户删除后评论保留但无关联用户）
RESTRICT 若存在子表关联数据，则禁止删除父表数据（默认行为） 重要业务数据，防止误删
NO ACTION 同RESTRICT，但检查时机可能不同（依数据库而定） 同RESTRICT

五、索引操作（提升查询效率）

1. 创建索引

— （1）普通索引（无唯一约束，仅加速查询）
— create[创建] index[索引] 索引名 on[在…上] 表名(字段名)

1
CREATE INDEX idx_user_phone ON user(phone); -- 实例：给user表的phone字段建普通索引

— （2）唯一索引（索引字段值唯一，同时加速查询）
— create[创建] unique[唯一] index[索引] 索引名 on[在…上] 表名(字段名)

1
CREATE UNIQUE INDEX idx_user_username ON user(username); -- 实例：给username字段建唯一索引

— （3）复合索引（多字段组合索引，适合多字段联合查询）

1
CREATE INDEX idx_order_user_price ON `order`(user_id, total_price); -- 实例：订单表的user_id+total_price复合索引

2. 查看索引

— show[显示] index[索引] from[从] 表名

1
SHOW INDEX FROM user; -- 实例：查看「user」表的所有索引

3. 删除索引

— drop[删除] index[索引] 索引名 on[在…上] 表名

1
DROP INDEX idx_user_phone ON user; -- 实例：删除「user」表的idx_user_phone索引

六、视图操作（简化复杂查询，虚拟表）

1. 创建视图

— 功能：将复杂查询的结果保存为视图，后续可直接查视图（视图不存数据，只存查询逻辑）
— create[创建] view[视图] 视图名 as[作为] (复杂查询语句)

1
2
3
4
5
CREATE VIEW view_user_order AS (
  SELECT u.username, o.id AS order_id, o.total_price, o.create_time 
  FROM user u 
  LEFT JOIN `order` o ON u.id = o.user_id
); -- 实例：创建「用户-订单关联视图」，查用户名、订单ID、金额、时间

2. 查询视图

— 与查询表语法一致（视图本质是虚拟表）

1
SELECT * FROM view_user_order WHERE username = 'zhangsan'; -- 实例：查张三的订单信息

3. 删除视图

— drop[删除] view[视图] if[如果] exists[存在] 视图名

1
DROP VIEW IF EXISTS view_user_order; -- 实例：删除「用户-订单关联视图」

七、权限管理（DCL：数据控制语言）

1. 授予权限（GRANT）

— 功能：给用户授予指定数据库/表的操作权限（如SELECT、INSERT、UPDATE）
— grant[授予] 权限1,权限2,… on[在…上] 数据库名.表名 to[给] ‘用户名’@’主机名’ identified by[密码] ‘密码’

1
GRANT SELECT, INSERT ON shop.user TO 'shop_user'@'localhost' IDENTIFIED BY 'shop123'; -- 实例：给shop_user用户授予shop库user表的查询和插入权限

2. 撤销权限（REVOKE）

— 功能：收回用户已有的权限
— revoke[撤销] 权限1,权限2,… on[在…上] 数据库名.表名 from[从] ‘用户名’@’主机名’

1
REVOKE INSERT ON shop.user FROM 'shop_user'@'localhost'; -- 实例：收回shop_user用户的插入权限

八、存储过程（批量执行SQL，类似“函数”）

1. 创建存储过程

— 功能：定义可重复执行的SQL逻辑（支持参数：IN输入/OUT输出/INOUT输入输出）
— delimiter[分隔符] // （临时修改SQL结束符为//，避免与存储过程中的;冲突）
— create[创建] procedure[过程] 存储过程名(参数列表)
— begin[开始]
— 存储过程逻辑（SQL语句）
— end[结束] //
— delimiter[分隔符] ; （恢复SQL结束符为;）

1
2
3
4
5
6
DELIMITER //
CREATE PROCEDURE get_user_by_phone(IN input_phone CHAR(11), OUT output_username VARCHAR(50))
BEGIN
  SELECT username INTO output_username FROM user WHERE phone = input_phone; -- 将查询结果赋值给输出参数
END //
DELIMITER ;

2. 调用存储过程

— call[调用] 存储过程名(参数列表)

1
2
CALL get_user_by_phone('13800138000', @un); -- 实例：调用存储过程，输入手机号，结果存到@un变量
SELECT @un; -- 查看输出结果（@un是MySQL会话变量）

3. 删除存储过程

— drop[删除] procedure[过程] if[如果] exists[存在] 存储过程名

1
DROP PROCEDURE IF EXISTS get_user_by_phone;