MySQL 数据库 SQL 语句完全指南

对于 Java 后端开发者来说，SQL 不是加分项，而是核心必备技能。它直接决定你能否高效处理数据、保障系统性能，甚至影响项目的稳定性和你的职业竞争力。

本文从基础到进阶，系统讲解 MySQL 常用 SQL 语句，涵盖 90% 以上的实际开发场景。建议边看边实践，在本地数据库中运行示例代码，才能真正掌握。

基础知识

注释

单行注释（最常用）

语法：-- 注释内容（注意：-- 后面必须加一个空格）

作用：注释单行内容，从 -- 开始到行尾的内容都会被数据库忽略。

多行注释（块注释）

语法：/* 注释内容 */（可跨多行）

作用：注释多行内容，适合详细说明 SQL 的业务逻辑、参数含义等。

SQL 执行顺序

理解 SQL 的执行顺序对于写出正确的查询语句至关重要：

1. FROM, JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
   5.1 窗口函数计算
6. ORDER BY
7. LIMIT

错误示范：在 WHERE 中使用聚合函数

-- 错误：WHERE 执行时聚合函数还未计算
SELECT NAME, salary, AVG(salary)
FROM user
WHERE salary > AVG(salary);

-- 正确写法1：使用子查询
SELECT NAME, salary, (SELECT AVG(salary) FROM user) AS avg_salary
FROM user
WHERE salary > (SELECT AVG(salary) FROM user);

-- 正确写法2：使用窗口函数
SELECT NAME, salary, AVG(salary) OVER () AS avg_salary
FROM user
WHERE salary > (SELECT AVG(salary) FROM user);

一、DML（数据操作）

示例表结构

为了让示例更贴近实际，先创建一个 user 表并插入测试数据：

-- 创建用户表
CREATE TABLE `user` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `age` INT,
  `gender` VARCHAR(10),
  `salary` DECIMAL(10,2),
  `dept` VARCHAR(50),
  `create_time` DATE
);

-- 插入测试数据
INSERT INTO `user` (name, age, gender, salary, dept, create_time) VALUES
('张三', 25, '男', 5000.00, '研发部', '2024-01-10'),
('李四', 30, '男', 8000.00, '研发部', '2024-02-15'),
('王五', 28, '女', 6500.00, '市场部', '2024-01-20'),
('赵六', 35, '男', 9000.00, '销售部', '2024-03-05'),
('孙七', 26, '女', 5500.00, '市场部', '2024-02-28'),
('周八', NULL, '男', 7000.00, '研发部', '2024-01-18'),
('吴九', 32, '女', NULL, '销售部', '2024-03-10');

SELECT 查询

避免使用 SELECT *：生产环境必须指定字段，如 SELECT name, age FROM user，这样可以减少网络传输和磁盘 IO，且便于索引覆盖。

语法点 示例 SQL 说明
字段筛选 SELECT * FROM user; 查询所有字段（开发中尽量避免）
指定字段 SELECT name, age, salary FROM user; 只查询需要的字段
别名 SELECT name AS 姓名, age AS 年龄 FROM user; AS 可省略，给字段起别名
去重 SELECT DISTINCT dept FROM user; 查询所有不重复的部门

INSERT 插入

语法点 示例 SQL 说明
单行插入 INSERT INTO user (name, age, gender, salary, dept) VALUES ('郑十', 29, '男', 7500.00, '研发部'); 指定字段插入
多行插入 INSERT INTO user (name, age, gender, dept) VALUES ('钱十一', 27, '女', '市场部'), ('孙十二', 31, '男', '销售部'); 一次插入多条，效率更高

UPDATE 更新

语法点 示例 SQL 说明
单字段更新 UPDATE user SET salary = 8500.00 WHERE name = '李四'; 必须加 WHERE 避免全表更新
多字段更新 UPDATE user SET age = 26, salary = 5800.00 WHERE name = '孙七'; 同时更新多个字段
危险操作 UPDATE user SET salary = 6000.00; 会更新全表，生产环境禁用

DELETE 删除

语法点 示例 SQL 说明
带条件删除 DELETE FROM user WHERE name = '郑十'; 只删除指定记录
先查后删 SELECT * FROM user WHERE dept = '销售部';
DELETE FROM user WHERE dept = '销售部';		 先确认再删除
危险操作 DELETE FROM user; 会删除全表数据，生产环境禁用

二、条件与排序

WHERE 条件筛选

运算符 示例 SQL 说明
= SELECT * FROM user WHERE gender = '女'; 查询所有女性用户
>, <, >=, <= SELECT * FROM user WHERE age > 30; 查询年龄大于 30 的用户
LIKE SELECT * FROM user WHERE name LIKE '张%'; 匹配以"张"开头的姓名
IN SELECT * FROM user WHERE dept IN ('研发部', '市场部'); 查询指定部门的用户
BETWEEN SELECT * FROM user WHERE salary BETWEEN 5000 AND 8000; 查询薪资在范围内的用户
IS NULL SELECT * FROM user WHERE age IS NULL; 查询年龄为空的用户
AND/OR SELECT * FROM user WHERE dept = '研发部' AND age < 30; 多条件组合查询

ORDER BY 排序

语法点 示例 SQL 说明
升序 SELECT name, age FROM user ORDER BY age ASC; ASC 可省略，默认升序
降序 SELECT name, salary FROM user ORDER BY salary DESC; 按薪资降序排列
多字段排序 SELECT name, dept, age FROM user ORDER BY dept ASC, age DESC; 先按部门升序，再按年龄降序

三、聚合与分组

聚合函数

函数 示例 SQL 说明
COUNT(*) SELECT COUNT(*) AS 总用户数 FROM user; 统计所有用户数量
COUNT(字段) SELECT COUNT(age) AS 年龄非空用户数 FROM user; 统计非空记录数
SUM() SELECT SUM(salary) AS 研发部总薪资 FROM user WHERE dept = '研发部'; 计算总和
AVG() SELECT AVG(salary) AS 市场部平均薪资 FROM user WHERE dept = '市场部'; 计算平均值
MAX() SELECT MAX(salary) AS 最高薪资 FROM user; 查询最大值
MIN() SELECT MIN(age) AS 最小年龄 FROM user WHERE age IS NOT NULL; 查询最小值

GROUP BY 分组

语法点 示例 SQL 说明
基础分组 SELECT dept, COUNT(*) AS 部门人数 FROM user GROUP BY dept; 按部门分组统计
多字段分组 SELECT dept, gender, AVG(salary) AS 平均薪资 FROM user GROUP BY dept, gender; 按多个字段分组
分组规则 SELECT dept, MAX(name), COUNT(*) FROM user GROUP BY dept; SELECT 中的字段要么在 GROUP BY 中，要么被聚合函数包裹

HAVING 分组后过滤

语法点 示例 SQL 说明
HAVING 使用 SELECT dept, AVG(salary) AS 平均薪资 FROM user GROUP BY dept HAVING AVG(salary) > 6000; 筛选平均薪资大于 6000 的部门
WHERE vs HAVING SELECT dept, COUNT(*) AS 人数 FROM user WHERE age > 25 GROUP BY dept HAVING COUNT(*) > 1; WHERE 先过滤，HAVING 后过滤

分组的意义：将零散的单条记录，按指定维度聚合为统计结果，满足业务分析需求。

四、多表连接查询

注意：当 SELECT 的字段在多表中存在时，必须加上表别名，如 u.name 而不是 name。

-- 示例表结构
CREATE TABLE `user` (
  `user_id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_name` VARCHAR(50) NOT NULL,
  `age` INT
);

CREATE TABLE `order` (
  `order_id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT,
  `product_id` INT,
  `create_time` DATE,
  `amount` DECIMAL(10,2)
);

CREATE TABLE `product` (
  `product_id` INT PRIMARY KEY AUTO_INCREMENT,
  `product_name` VARCHAR(100) NOT NULL,
  `price` DECIMAL(10,2)
);

-- 插入测试数据
INSERT INTO `user` (user_name, age) VALUES ('张三', 25), ('李四', 30), ('王五', 28);
INSERT INTO `order` (user_id, product_id, create_time, amount) VALUES
(1, 1, '2024-01-10', 100.00),
(1, 2, '2024-01-15', 200.00),
(2, NULL, '2024-02-01', 150.00);
INSERT INTO `product` (product_name, price) VALUES ('手机', 1000.00), ('耳机', 200.00);

内连接（INNER JOIN）

内连接只返回多个表中满足关联条件的交集记录。

三表内连接示例：

-- 查询有订单且订单关联了商品的用户信息
SELECT u.user_name, o.order_id, p.product_name, o.amount
FROM `user` u
INNER JOIN `order` o ON u.user_id = o.user_id
INNER JOIN `product` p ON o.product_id = p.product_id;

左连接（LEFT JOIN）

以左边的表为主表，主表的所有行都会被保留；右边的表只匹配关联的行，匹配不上的字段显示 NULL。

两表左连接示例：

-- 查询所有用户的订单信息，无订单的用户也会显示
SELECT u.user_name, o.order_id, o.amount
FROM `user` u
LEFT JOIN `order` o ON u.user_id = o.user_id;

三表左连接示例：

-- 查询所有用户的完整订单和商品信息
SELECT u.user_name, o.order_id, p.product_name, o.amount
FROM `user` u
LEFT JOIN `order` o ON u.user_id = o.user_id
LEFT JOIN `product` p ON o.product_id = p.product_id;

右连接（RIGHT JOIN）

右连接与左连接类似，只是基准表在右侧。实际开发中几乎可以完全用左连接替代。

SELECT u.user_name, o.order_id, o.amount
FROM `order` o
RIGHT JOIN `user` u ON o.user_id = u.user_id;

笛卡尔积（CROSS JOIN）

笛卡尔积会生成两表的所有组合，实际开发中很少使用。

-- 无条件笛卡尔积（结果集通常无意义）
SELECT u.user_name, p.product_name
FROM `user` u
CROSS JOIN `product` p;

显式关联 vs 隐式关联

推荐使用显式关联（INNER JOIN），语义更清晰：

-- 显式关联（推荐）
SELECT u.user_name, o.order_id
FROM `user` u
INNER JOIN `order` o ON u.user_id = o.user_id;

-- 隐式关联（不推荐，老项目才有）
SELECT u.user_name, o.order_id
FROM `user` u, `order` o
WHERE u.user_id = o.user_id;

多表关联复杂场景

SELECT 
  u.user_name,
  o.order_id,
  p.product_name,
  o.amount,
  o.create_time
FROM `user` u
LEFT JOIN `order` o ON u.user_id = o.user_id
LEFT JOIN `product` p ON o.product_id = p.product_id
WHERE o.create_time LIKE '2024-01%'
ORDER BY o.amount DESC;

子查询

子查询是嵌套在另一个 SQL 语句内部的查询。

注意：三层及以上的嵌套子查询不推荐使用，可读性差，性能一般，难以维护。

单行子查询（用 =）：

-- 查询订单 id 为 3 的用户信息
SELECT user_id, user_name, age
FROM `user`
WHERE user_id = (SELECT user_id FROM `order` WHERE order_id = 3);

多行子查询（用 IN）：

-- 查询购买了耳机的用户信息
SELECT user_id, user_name, age
FROM `user`
WHERE user_id IN (
   SELECT DISTINCT user_id 
   FROM `order` 
   WHERE product_id = (
       SELECT product_id
       FROM `product`
       WHERE product_name = '耳机'
    )
);

上面的例子可以用内连接完成，性能更好：

-- 内连接实现
SELECT DISTINCT u.user_id, u.user_name, u.age
FROM `user` u
INNER JOIN `order` o ON u.user_id = o.user_id
INNER JOIN `product` p ON o.product_id = p.product_id
WHERE p.product_name = '耳机';

五、高频基础语法

LIMIT 分页

语法点 示例 SQL 说明
基础分页 SELECT * FROM user LIMIT 5; 只返回前 5 条记录
偏移量 SELECT * FROM user LIMIT 10, 5; 跳过前 10 条，取接下来 5 条
分页优化 SELECT * FROM user WHERE id > 20 LIMIT 5; 避免大偏移量性能问题

CASE WHEN 条件分支

-- 将性别编码转中文，按薪资分级
SELECT 
  name,
  CASE gender 
    WHEN '男' THEN '男性'
    WHEN '女' THEN '女性'
    ELSE '未知' 
  END AS 性别_中文,
  CASE 
    WHEN salary >= 8000 THEN '高薪'
    WHEN salary >= 6000 THEN '中薪'
    ELSE '底薪' 
  END AS 薪资等级
FROM user;

自增主键重置

-- 清空表并重置自增主键
TRUNCATE TABLE user;

-- 仅重置自增主键（保留数据）
ALTER TABLE user AUTO_INCREMENT = 1;

NULL 处理

函数 示例 SQL 说明
IFNULL SELECT name, IFNULL(salary, 0) AS 薪资 FROM user; 薪资为 NULL 时显示 0
COALESCE SELECT name, COALESCE(mobile, telephone, email, '无法联系') AS 联系方式 FROM customer; 按顺序取第一个非 NULL 值

COALESCE 比 IFNULL 更常用的原因：

  1. 标准 SQL，跨数据库通用（MySQL、PostgreSQL、SQL Server、Oracle 都支持）
  2. 支持多参数，更灵活
  3. 代码可读性更好
-- COALESCE 多级备选
SELECT 
    COALESCE(mobile, wechat, email, telephone, '无联系方式') AS contact
FROM customers;

-- IFNULL 嵌套很混乱
SELECT IFNULL(a, IFNULL(b, IFNULL(c, '默认'))) FROM table;

-- COALESCE 清晰明了
SELECT COALESCE(a, b, c, '默认') FROM table;

六、常用函数

EXISTS 函数

EXISTS 是一个布尔型判断函数，用来检查子查询是否返回至少一行结果：

  • 子查询返回 ≥1 行 → EXISTS 为 TRUE → 保留该行
  • 子查询返回 0 行 → EXISTS 为 FALSE → 过滤该行

EXISTS 只关心子查询有没有结果，不关心具体返回什么值，所以子查询里写 SELECT 1 或 SELECT * 效果相同，推荐写 SELECT 1，性能略优。

基本语法：

SELECT 外部查询字段
FROM 主表 别名
WHERE EXISTS (
    SELECT 1
    FROM 子查询表 别名
    WHERE 子查询表.关联字段 = 主表.关联字段
);

示例：

-- 查询下过单的用户信息
SELECT user_id, user_name, user_city
FROM user u
WHERE EXISTS (
    SELECT 1
    FROM `order` o
    WHERE o.user_id = u.user_id
);

-- 查询没有下过单的用户信息
SELECT user_id, user_name, user_city
FROM user u
WHERE NOT EXISTS (
    SELECT 1
    FROM `order` o
    WHERE o.user_id = u.user_id
);

EXISTS vs IN 对比：

对比项 IN EXISTS
执行逻辑 先查出子查询所有结果，再和主表匹配 主表遍历一行，子查询只查是否存在，找到即停
大数据量性能 差（子查询结果大时，内存占用高） 优（无需缓存子查询结果）
空值处理 子查询返回 NULL 时，IN 结果为 FALSE 不受空值影响

实战建议：

  • 子查询关联的表是大表时，优先用 EXISTS
  • 子查询是固定小集合时（如 IN (1,2,3)），用 IN 更简洁

窗口函数

窗口函数在不聚合行的情况下，对数据的"窗口"进行计算，保持原始行不变，同时为每行添加计算结果。

基本语法：

函数名() OVER (
    [PARTITION BY 分组字段]
    [ORDER BY 排序字段]
    [ROWS/RANGE 范围子句]
)

三大窗口函数类型：

  1. 聚合类窗口函数（新手用的最多）
函数 说明 示例
AVG() 平均值 AVG(salary) OVER(PARTITION BY dept)
SUM() 求和 SUM(sales) OVER(ORDER BY date)
COUNT() 计数 COUNT(*) OVER(PARTITION BY dept)
MAX()/MIN() 最大/最小值 MAX(salary) OVER() 
-- 为每行添加部门平均工资和公司平均工资
SELECT 
    name, 
    dept, 
    salary,
    AVG(salary) OVER(PARTITION BY dept) AS dept_avg,
    AVG(salary) OVER() AS company_avg
FROM employees;
  1. 排名类窗口函数
函数 特点 示例结果
ROW_NUMBER() 连续唯一排名 1,2,3,4,5
RANK() 相同值同排名，留空位 1,2,3,3,5
DENSE_RANK() 相同值同排名，不留空 1,2,3,3,4
NTILE(n) 分为 n 组 1,1,2,2,3,3 
-- 部门内工资排名
SELECT 
    name, 
    dept, 
    salary,
    ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept,
    RANK() OVER(ORDER BY salary DESC) AS company_rank
FROM employees;
  1. 偏移类窗口函数
函数 作用 说明
LAG(expr, n) 获取前 n 行的值 常用于计算环比
LEAD(expr, n) 获取后 n 行的值 常用于计算同比
FIRST_VALUE(expr) 窗口第一个值
LAST_VALUE(expr) 窗口最后一个值 注意默认范围 
-- 计算工资月度环比增长
SELECT 
    month,
    salary,
    LAG(salary, 1) OVER(ORDER BY month) AS prev_month,
    salary - LAG(salary, 1) OVER(ORDER BY month) AS month_over_month
FROM monthly_salary;

关键子句：

PARTITION BY - 定义窗口分区

-- 按部门分区
AVG(salary) OVER(PARTITION BY department)

-- 多字段分区
ROW_NUMBER() OVER(PARTITION BY year, month)

ORDER BY - 窗口内排序

-- 排序影响排名、累计计算等
SUM(sales) OVER(PARTITION BY dept ORDER BY date)

窗口范围子句 - 定义滑动窗口

-- 常用范围定义
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

重要特性：

  1. 不减少行数（与 GROUP BY 的本质区别）
  2. 可多窗口并存
  3. 支持嵌套
  4. 通常优于多重子查询

字符串函数

函数名 核心用途 示例
CONCAT() 拼接字符串 CONCAT(user_name, '-', user_city)
TRIM() 去除首尾空格 TRIM(product_name)
SUBSTRING() 截取字符串 SUBSTRING(product_name, 1, 5)
LENGTH() 计算字符串长度 LENGTH(user_name) > 4
UPPER()/LOWER() 转大写/小写 UPPER(product_name)

示例：

-- 拼接用户名和城市
SELECT user_id, CONCAT(user_name, '-', user_city) AS user_info 
FROM user LIMIT 10;

-- 清洗商品名首尾空格
UPDATE product SET product_name = TRIM(product_name);

-- 截取商品名前 5 个字符
SELECT product_id, SUBSTRING(product_name, 1, 5) AS short_name 
FROM product LIMIT 10;

-- 筛选用户名长度大于 4 的用户
SELECT user_id, user_name 
FROM user 
WHERE LENGTH(user_name) > 4;

-- 商品名转大写
SELECT product_id, UPPER(product_name) AS upper_name 
FROM product LIMIT 10;

日期时间函数

函数名 核心用途 示例
NOW()/CURDATE() 获取当前时间/日期 DATE(order_time) = CURDATE()
YEAR()/MONTH() 提取年/月 YEAR(order_time) = 2025
DATE_SUB() 日期减 order_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
DATEDIFF() 计算日期差 DATEDIFF(NOW(), register_time)

示例：

-- 查询今日订单
SELECT order_id, user_id, order_time 
FROM `order` 
WHERE DATE(order_time) = CURDATE();

-- 查询 2025 年 3 月的订单
SELECT order_id, order_time, total_amount 
FROM `order` 
WHERE YEAR(order_time) = 2025 AND MONTH(order_time) = 3;

-- 查询近 7 天的订单
SELECT order_id, order_time 
FROM `order` 
WHERE order_time >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 计算用户注册天数
SELECT user_id, user_name, 
       DATEDIFF(NOW(), register_time) AS days_since_register 
FROM user LIMIT 10;

数值函数

函数名 核心用途 示例
ROUND() 四舍五入 ROUND(price, 1)
FLOOR()/CEIL() 向下/向上取整 FLOOR(total_amount)
ABS() 取绝对值 ABS(refund_amount)

示例：

-- 商品价格四舍五入保留 1 位小数
SELECT product_id, product_name, price, 
       ROUND(price, 1) AS price_rounded 
FROM product LIMIT 10;

-- 订单金额向下取整
SELECT order_id, total_amount, 
       FLOOR(total_amount) AS total_amount_floor 
FROM `order` LIMIT 10;

-- 退款金额取绝对值
SELECT order_id, refund_amount, ABS(refund_amount) AS refund_abs 
FROM `order` WHERE order_id = 1;

条件函数

函数名 核心用途 示例
IF() 双条件判断 IF(total_amount > 100, '高', '低')
IFNULL() 替换 NULL 值 IFNULL(SUM(total_amount), 0)
CASE WHEN 多条件判断 CASE WHEN price < 50 THEN '低价' ELSE '高价' END

示例：

-- 判断订单金额是否超过 100 元
SELECT order_id, total_amount, 
       IF(total_amount > 100, '高额订单', '低额订单') AS order_level 
FROM `order` LIMIT 10;

-- 左连接查询用户订单总额，无订单则显示 0
SELECT u.user_id, u.user_name, 
       IFNULL(SUM(o.total_amount), 0) AS total_spend 
FROM user u
LEFT JOIN `order` o ON u.user_id = o.user_id
GROUP BY u.user_id, u.user_name
LIMIT 10;

-- 给商品按价格分级
SELECT product_id, product_name, price,
       CASE 
           WHEN price < 50 THEN '低价商品'
           WHEN price BETWEEN 50 AND 500 THEN '中价商品'
           ELSE '高价商品'
       END AS price_grade
FROM product LIMIT 10;

七、进阶查询

批量操作优化

批量更新/删除：避免一次性操作全表，分批处理（比如每次 1000 条）

-- 分批更新薪资低于 5000 的用户
UPDATE user SET salary = salary * 1.1 WHERE id IN (
  SELECT id FROM user WHERE salary < 5000 LIMIT 1000
);

插入优化：关闭自动提交 + 批量插入

SET autocommit = 0;
INSERT INTO user (...) VALUES (...), (...), ...;
COMMIT;
SET autocommit = 1;

索引

索引的本质

索引就像书籍的目录：

  • 无索引：查数据时数据库要逐行扫描全表
  • 有索引：数据库先查索引，直接定位到数据所在的行

核心作用：

  • 加速查询（SELECT）：这是索引的核心价值，尤其是大表（10 万+行）
  • 减慢增删改（INSERT/UPDATE/DELETE）：因为修改数据时，数据库需要同步更新索引
  • 核心权衡：查询多、修改少的表要建索引；修改多、查询少的表少建索引

MySQL 常用索引类型：

索引类型 核心特点 适用场景 示例
主键索引 自动创建，唯一且非 NULL 表的唯一标识 ALTER TABLE user ADD PRIMARY KEY (user_id);
唯一索引 索引值唯一，允许 NULL 唯一字段（手机号、邮箱） CREATE UNIQUE INDEX idx_user_phone ON user(phone);
普通索引 最常用，无唯一性要求 普通筛选/关联字段 CREATE INDEX idx_order_user_id ON \order`(user_id);`
联合索引 基于多个字段的索引 多字段组合筛选 CREATE INDEX idx_order_uid_ctime ON \order`(user_id, create_time);`
全文索引 针对文本内容的模糊查询 长文本模糊搜索 CREATE FULLTEXT INDEX idx_prod_name ON product(product_name);

最左匹配原则（联合索引的核心）：

联合索引 idx_order_uid_ctime (user_id, create_time) 的生效规则：

  • 生效：WHERE user_id = 1、WHERE user_id = 1 AND create_time > '2024-01-01'
  • 失效：WHERE create_time > '2024-01-01'（跳过了最左的 user_id）
  • 核心：联合索引必须从最左字段开始匹配，中间不能跳过

索引的基本操作：

创建索引：

-- 方式1：CREATE INDEX（推荐）
CREATE INDEX idx_order_amount ON `order`(amount);
CREATE UNIQUE INDEX idx_user_email ON user(email);
CREATE INDEX idx_order_uid_amt ON `order`(user_id, amount);

-- 方式2：ALTER TABLE
ALTER TABLE product ADD PRIMARY KEY (product_id);
ALTER TABLE `user` ADD UNIQUE INDEX idx_user_phone (phone);

-- 方式3：建表时直接指定
CREATE TABLE `order` (
  order_id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10,2),
  create_time DATETIME,
  INDEX idx_order_ctime (create_time),
  UNIQUE INDEX idx_order_uid_oid (user_id, order_id)
);

删除索引：

-- 方式1：DROP INDEX（推荐）
DROP INDEX idx_order_amount ON `order`;

-- 方式2：ALTER TABLE（删除主键索引只能用这个）
ALTER TABLE user DROP PRIMARY KEY;
ALTER TABLE user DROP INDEX idx_user_phone;

查看索引：

-- 查看单表所有索引
SHOW INDEX FROM `order`;

-- 查看建表语句（包含索引）
SHOW CREATE TABLE `order`;

索引生效/失效的核心规则：

索引生效的条件：

  • 筛选字段/关联字段必须是索引字段
  • 联合索引遵循最左匹配
  • 排序字段是索引字段

索引失效的常见场景：

失效场景 错误示例 修正方案
索引字段做函数操作 WHERE YEAR(create_time) = 2024 WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31'
索引字段用运算符（!=/<>） WHERE user_id != 1 WHERE user_id IN (2,3,4)
LIKE 以 % 开头 WHERE product_name LIKE '%手机' LIKE '手机%' 或用全文索引
索引字段类型不匹配 WHERE user_id = '1'（user_id 是 INT） WHERE user_id = 1
OR 连接无索引字段 WHERE user_id = 1 OR amount > 100（amount 无索引） 拆分查询，或给 amount 加索引

视图

视图是基于一条 SELECT 查询语句创建的虚拟表，它不存储任何实际数据，只保存查询逻辑。每次查询视图时，数据库都会重新执行底层的 SELECT 语句，返回实时结果。

视图的作用：

  1. 复用：多处调用同一视图，避免重复写联查 SQL
  2. 简化：对外只暴露视图名，隐藏内部复杂逻辑

基础语法：

-- 创建视图
CREATE [OR REPLACE] VIEW 视图名 AS 
SELECT 查询语句;

-- 查询视图
SELECT * FROM 视图名 [WHERE 条件];

-- 修改视图
CREATE OR REPLACE VIEW 视图名 AS 
新的SELECT查询语句;

-- 删除视图
DROP VIEW [IF EXISTS] 视图名;

示例1：创建用户订单详情视图

-- 创建视图
DROP VIEW IF EXISTS v_user_order_detail;
CREATE VIEW v_user_order_detail AS
SELECT 
    u.user_id,
    u.user_name,
    u.user_city,
    o.order_id,
    o.order_time,
    o.total_amount,
    p.product_name,
    p.category,
    p.price
FROM user u
JOIN `order` o ON u.user_id = o.user_id
JOIN product p ON o.product_id = p.product_id;

-- 查询视图
SELECT * FROM v_user_order_detail 
WHERE user_city = '北京' AND category = '数码'
LIMIT 10;

-- 统计各城市的数码商品销售额
SELECT user_city, SUM(total_amount) AS total_sales
FROM v_user_order_detail
WHERE category = '数码'
GROUP BY user_city;

示例2：创建带筛选的视图（隐藏敏感数据）

DROP VIEW IF EXISTS v_user_sales_simple;
CREATE VIEW v_user_sales_simple AS
SELECT 
    u.user_id,
    u.user_name,
    o.order_id,
    o.total_amount
FROM user u
LEFT JOIN `order` o ON u.user_id = o.user_id;

-- 查询视图
SELECT * FROM v_user_sales_simple LIMIT 10;

注意事项：

  1. 视图是虚拟表，不存数据：原表数据更新后，视图查询结果会自动更新
  2. 视图的更新限制：简单视图可直接更新，复杂视图（多表联查、含聚合/分组）无法直接更新
  3. 视图不提升查询性能：要提升速度，需给底层表的关联字段/筛选字段加索引
  4. 避免过度使用复杂视图：不要创建视图嵌套视图

存储过程

存储过程是预编译并存储在数据库中的一组 SQL 语句的集合，可以理解成数据库里的"自定义函数/脚本"。

优势：

  • 简化操作：一次封装，多次调用
  • 提高效率：预编译后执行更快
  • 减少网络传输：复杂逻辑在数据库端执行
  • 权限控制：可以授予调用权限，但不直接暴露底层表

基本语法：

DELIMITER //
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    -- 要执行的 SQL 逻辑
END //
DELIMITER ;

参数类型：

类型 含义
IN 输入参数（默认）：调用时传入值，存储过程内可使用
OUT 输出参数：存储过程内给参数赋值，调用后能获取到这个值
INOUT 输入输出参数：既可以传入值，也能返回修改后的值

调用语法：

-- 无参数/仅 IN 参数
CALL 存储过程名(参数值);

-- 有 OUT/INOUT 参数
SET @变量名 = 初始值;
CALL 存储过程名(@变量名);
SELECT @变量名;

-- 查看所有存储过程
SHOW PROCEDURE STATUS WHERE db = '你的数据库名';

-- 删除存储过程
DROP PROCEDURE IF EXISTS 存储过程名;

案例1：无参数存储过程

-- 创建：查询 order 表所有数据
DELIMITER //
CREATE PROCEDURE query_orders()
BEGIN
    SELECT * FROM ssmbuild.`order`;
END //
DELIMITER ;

-- 调用
CALL query_orders();

案例2：带 IN 参数的存储过程

-- 创建：根据 order_id 查询订单
DELIMITER //
CREATE PROCEDURE query_order_by_id(IN oid INT)
BEGIN
    SELECT * FROM ssmbuild.`order` WHERE order_id = oid;
END //
DELIMITER ;

-- 调用
CALL query_order_by_id(4);

案例3：带 OUT 参数的存储过程

-- 创建：统计订单总数
DELIMITER //
CREATE PROCEDURE count_order(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM ssmbuild.`order`;
END //
DELIMITER ;

-- 调用
SET @t = 0;
CALL count_order(@t);
SELECT @t AS 订单总数;

案例4：带逻辑的存储过程

-- 创建：给指定订单的 amount 加指定数值
DELIMITER //
CREATE PROCEDURE update_order_amount(IN oid INT, IN add_num INT)
BEGIN
    IF (SELECT COUNT(*) FROM ssmbuild.`order` WHERE order_id = oid) > 0 THEN
        UPDATE ssmbuild.`order` SET amount = amount + add_num WHERE order_id = oid;
        SELECT '更新成功' AS 结果;
    ELSE
        SELECT '订单不存在' AS 结果;
    END IF;
END //
DELIMITER ;

-- 调用
CALL update_order_amount(4, 5);

注意事项：

  1. 务必正确处理语句结束符（DELIMITER）
  2. 严格区分参数类型（IN/OUT/INOUT）
  3. 避免在存储过程中写过于复杂的逻辑
  4. 注意存储过程的预编译特性，修改逻辑需重新创建
  5. 做好存储过程的文档和命名规范

触发器

触发器是与表关联的、自动执行的一段 SQL 逻辑，当你对表执行 INSERT/UPDATE/DELETE 操作时，数据库会自动触发预设的 SQL 逻辑。

优势：

  • 自动执行：无需手动调用
  • 与表绑定：一个触发器只能属于一张表
  • 触发时机固定：只能在操作前（BEFORE）或操作后（AFTER）触发

触发器的核心要素：

触发时机：

时机 含义
BEFORE 在执行操作之前触发（可修改即将插入/更新的数据）
AFTER 在执行操作之后触发（仅能读取操作后的数据）

触发事件：

  • INSERT：插入数据时触发
  • UPDATE：修改数据时触发
  • DELETE：删除数据时触发

特殊变量：

变量 适用场景 含义
OLD.字段名 UPDATE/DELETE 操作前的旧数据
NEW.字段名 INSERT/UPDATE 操作后的新数据

注意：DELETE 只有 OLD，INSERT 只有 NEW，UPDATE 既有 OLD 也有 NEW。

基本语法：

DELIMITER //
CREATE TRIGGER 触发器名
触发时机 触发事件 ON 表名
FOR EACH ROW
BEGIN
    -- 触发器要执行的 SQL 逻辑
END //
DELIMITER ;

查看和删除：

-- 查看所有触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER IF EXISTS 触发器名;

案例1：AFTER INSERT 触发器（插入订单后自动扣库存）

-- 创建 goods 表
CREATE TABLE IF NOT EXISTS ssmbuild.goods (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    stock INT DEFAULT 0
);
INSERT INTO ssmbuild.goods VALUES (1, '华为手机', 100);

-- 创建触发器
DELIMITER //
CREATE TRIGGER tri_order_insert
AFTER INSERT ON `order`
FOR EACH ROW
BEGIN
    UPDATE ssmbuild.goods 
    SET stock = stock - NEW.num 
    WHERE id = NEW.goods_id;
END //
DELIMITER ;

-- 测试
INSERT INTO ssmbuild.`order` (goods_id, num, amount) VALUES (1, 2, 5998);
SELECT * FROM ssmbuild.goods WHERE id = 1;

案例2：BEFORE UPDATE 触发器（限制价格修改）

-- 给 goods 表加 price 字段
ALTER TABLE ssmbuild.goods ADD COLUMN price DECIMAL(10,2);
UPDATE ssmbuild.goods SET price = 2999 WHERE id = 1;

-- 创建触发器
DELIMITER //
CREATE TRIGGER tri_goods_update
BEFORE UPDATE ON goods
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SET NEW.price = 0;
    END IF;
END //
DELIMITER ;

-- 测试
UPDATE ssmbuild.goods SET price = -100 WHERE id = 1;
SELECT price FROM ssmbuild.goods WHERE id = 1;

案例3：AFTER DELETE 触发器（删除用户前备份数据）

-- 创建备份表
CREATE TABLE IF NOT EXISTS ssmbuild.user_backup (
    id INT,
    username VARCHAR(50),
    delete_time DATETIME
);

-- 创建触发器
DELIMITER //
CREATE TRIGGER tri_user_delete
AFTER DELETE ON `user`
FOR EACH ROW
BEGIN
    INSERT INTO ssmbuild.user_backup 
    VALUES (OLD.id, OLD.username, NOW());
END //
DELIMITER ;

-- 测试
DELETE FROM ssmbuild.`user` WHERE id = 1;
SELECT * FROM ssmbuild.user_backup;

注意事项：

  1. 严格控制触发逻辑，避免无限/嵌套触发
  2. 用好 BEFORE/AFTER 时机 + OLD/NEW 变量
  3. 触发器与触发操作共享事务，关注数据一致性
  4. 简化触发器逻辑，降低维护/调试成本

总结

以上就是 MySQL 高频 SQL 语法的核心内容，从基础的 DML 操作到进阶的窗口函数、多表连接，覆盖了 Java 后端开发中 90% 以上的业务场景。

SQL 的核心是"练"，建议结合实际业务场景多写多试。把本文的示例语句在本地数据库中实操验证，才能真正吃透。如果在使用过程中遇到特殊场景的 SQL 难题，可以针对性拆解逻辑，比如：

  • 复杂分组用窗口函数替代嵌套子查询
  • 大数据量用 EXISTS 优化性能
  • 多表关联优先使用显式 JOIN

希望这份教程能帮你夯实 SQL 基础，提升数据处理效率。

关键词：MySQL、SQL、数据库、Java 后端、DML、多表连接、窗口函数、索引优化、存储过程、触发器、视图

# sql # 数据库 # java # ide # 后端 # mybatis
Logo
DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

使用概率图路径规划的机器人路径规划研究Octave（Matlab代码实现）

针对复杂未知环境下传统机器人路径规划算法适应性差、避障稳定性弱、全局搜索效率低的问题，本文开展基于概率图的机器人路径规划方法研究。概率图路径规划依托概率路线图建模思想，通过环境随机采样、节点连通性构建、最优路径检索的核心逻辑，摆脱了传统算法对环境精准建模的依赖，具备强环境适配性与高运算效率。本文系统阐述概率图路径规划的核心理论、运行机制与技术优势，基于Octave仿真平台搭建多场景机器人运动规划仿

avatar DAMO开发者矩阵

使用概率图路径规划的机器人路径规划研究Octave（Matlab代码实现）

针对复杂未知环境下传统机器人路径规划算法适应性差、避障稳定性弱、全局搜索效率低的问题，本文开展基于概率图的机器人路径规划方法研究。概率图路径规划依托概率路线图建模思想，通过环境随机采样、节点连通性构建、最优路径检索的核心逻辑，摆脱了传统算法对环境精准建模的依赖，具备强环境适配性与高运算效率。本文系统阐述概率图路径规划的核心理论、运行机制与技术优势，基于Octave仿真平台搭建多场景机器人运动规划仿

avatar DAMO开发者矩阵

使用概率图路径规划的机器人路径规划研究Octave（Matlab代码实现）

针对复杂未知环境下传统机器人路径规划算法适应性差、避障稳定性弱、全局搜索效率低的问题，本文开展基于概率图的机器人路径规划方法研究。概率图路径规划依托概率路线图建模思想，通过环境随机采样、节点连通性构建、最优路径检索的核心逻辑，摆脱了传统算法对环境精准建模的依赖，具备强环境适配性与高运算效率。本文系统阐述概率图路径规划的核心理论、运行机制与技术优势，基于Octave仿真平台搭建多场景机器人运动规划仿

avatar DAMO开发者矩阵