17认证网本文基于 MySQL 8.0 稳定版，遵循阿里巴巴《Java 开发手册》数据库规范，所有语法、示例、场景均贴合真实项目开发。
一、前置准备：标准测试表
开发中，数据表必须遵循主键非空、默认值、注释、索引、时间戳规范，我们创建一张用户表 user_info 作为全程测试表：
-- 创建数据库（规范：库名小写，见名知意）
CREATE DATABASE IF NOT EXISTS enterprise_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE enterprise_db;
-- 创建用户表（标准结构：主键、业务字段、状态、时间戳、逻辑删除）
CREATE TABLE IF NOT EXISTS user_info (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID（自增，分布式可改用雪花算法）',
username VARCHAR(32) NOT NULL UNIQUE COMMENT '用户名（唯一约束）',
password VARCHAR(64) NOT NULL COMMENT '密码（加密存储，禁止明文）',
phone VARCHAR(11) COMMENT '手机号',
age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄（无符号，0为默认值）',
gender TINYINT DEFAULT 0 COMMENT '性别：0-未知 1-男 2-女',
status TINYINT DEFAULT 1 COMMENT '状态：1-正常 0-禁用 9-删除',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（自动填充）',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间（自动更新）',
INDEX idx_phone (phone) COMMENT '手机号普通索引（优化查询）'
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT '用户信息表（标准CRUD测试表）';
表结构核心规范
-
存储引擎：InnoDB（支持事务、外键、行锁，唯一选择）； -
字符集：utf8mb4（兼容 emoji，替代不完整的 utf8）； -
必备字段： id、status、create_time、update_time、is_deleted（逻辑删除）； -
约束：非空、唯一、默认值，减少业务层异常。
二、MySQL 新增（INSERT）
新增操作是数据写入的核心，开发中分为单条新增、批量新增、覆盖新增、忽略重复新增，禁止使用无列名插入。
-- ==============================================
-- 一、基础插入注意事项（匹配 user_info 表结构）
-- ==============================================
-- 1. 插入的值类型必须与列类型一致或兼容
INSERT INTO user_info(id,username,password,phone,age,gender)
VALUES(13,'唐艺昕',MD5('123456'),'1898888888',28,1);
-- 2. 非空列必须传值，可为空列两种插入方式
-- 方式一：显式写 NULL
INSERT INTO user_info(id,username,password,phone,age,gender)
VALUES(13,'唐艺昕',MD5('123456'),'1898888888',NULL,NULL);
-- 方式二：省略可为空字段
INSERT INTO user_info(id,username,password)
VALUES(15,'娜扎',MD5('123456'));
-- 3. 列的顺序可以任意调换
INSERT INTO user_info(username,gender,id,password)
VALUES('关晓彤',1,16,MD5('123456'));
-- 4. 列数和值的个数必须严格一致
INSERT INTO user_info(username,age,id,password)
VALUES('关晓彤',25,17,MD5('123456'));
-- 5. 省略列名，必须按表结构全字段传值
INSERT INTO user_info
VALUES(18,'张飞',MD5('123456'),'119',30,1,1,NOW(),NOW());
-- ==============================================
-- 二、两种插入语法
-- ==============================================
-- 方式1：VALUES 方式（标准推荐）
INSERT INTO user_info(username,password,phone)
VALUES('zhangsan',MD5('123456'),'13800138000');
-- 方式2：SET 方式（单条简洁）
INSERT INTO user_info
SET id=19,username='刘涛',password=MD5('123456'),phone='999';
-- 两种方式区别
-- 1. VALUES 支持批量插入
INSERT INTO user_info(username,password,phone)
VALUES
('唐艺昕1',MD5('123'),'189'),
('唐艺昕2',MD5('456'),'188'),
('唐艺昕3',MD5('789'),'187');
-- 2. VALUES 支持子查询插入
INSERT INTO user_info(id,username,phone)
SELECT 26,'宋茜','11809866';
-- ==============================================
-- 三、企业级高频新增（必背）
-- ==============================================
-- 1. 基础单条新增（指定列名，密码加密）
INSERT INTO user_info (username, password, phone, age, gender)
VALUES ('zhangsan', MD5('123456'), '13800138000', 25, 1);
-- 2. 批量新增（性能最优，企业首选）
INSERT INTO user_info (username, password, phone, age, gender)
VALUES
('lisi', MD5('654321'), '13900139000', 23, 2),
('wangwu', MD5('111111'), '13700137000', 28, 1),
('zhaoliu', MD5('222222'), '13600136000', 26, 0);
-- 3. 忽略重复新增（唯一键冲突不报错）
INSERT IGNORE INTO user_info (username, password, phone)
VALUES ('zhangsan', MD5('333333'), '13500135000');
-- 4. 覆盖新增（存在则更新，不存在则插入）
INSERT INTO user_info (username, password, phone)
VALUES ('zhangsan', MD5('123456'), '13888888888')
ON DUPLICATE KEY UPDATE phone = VALUES(phone);
三、MySQL 删除（DELETE）
3.1 单表删除
语法
DELETE FROM 表名 [WHERE 条件] [ORDER BY 字段] [LIMIT 条数];
示例
-- 删除手机号以9结尾的用户
DELETE FROM user_info WHERE phone LIKE '%9';
-- 删除指定ID用户
DELETE FROM user_info WHERE id = 100;
-- 删除18岁以下用户
DELETE FROM user_info WHERE age < 18;
-- 只删除1条
DELETE FROM user_info WHERE status = 0 LIMIT 1;
3.2 多表删除（关联删除）
SQL99 语法（推荐）
DELETE 表1别名, 表2别名
FROM 表1 别名
[INNER/LEFT/RIGHT] JOIN 表2 别名 ON 连接条件
WHERE 筛选条件;
示例
-- 删除张无忌的女朋友（只删user_info）
DELETE u
FROM user_info u
JOIN boys bo ON u.boyfriend_id = bo.id
WHERE bo.boyName = '张无忌';
-- 删除黄晓明及其女朋友（删两张表）
DELETE u, bo
FROM user_info u
JOIN boys bo ON u.boyfriend_id = bo.id
WHERE bo.boyName = '黄晓明';
3.3 truncate
TRUNCATE TABLE boys ; 全删除
3.4 分批删除大表数据
在 Oracle 中删除大表（20GB）中大量历史数据时，如果直接使用 DELETE 语句，会带来以下问题：
-
产生大量 undo 和 redo 日志，可能撑爆 undo 表空间； -
锁表时间长，影响业务； -
速度慢，事务回滚成本高。
推荐策略：分批删除（Batch Delete）
通过循环分批删除，每次只删少量数据（比如每次 10,000 行），提交事务，避免长时间锁表和 undo 膨胀。
✅ SQL 示例（配合 PL/SQL 块）
DECLARE
v_batch_size NUMBER := 5000; -- 有索引后可适当增大批次
v_deleted_rows NUMBER := 1;
v_total NUMBER := 0;
BEGIN
WHILE v_deleted_rows > 0 LOOP
DELETE FROM task
WHERE publishdate < 20250301
AND ROWNUM <= v_batch_size;
v_deleted_rows := SQL%ROWCOUNT;
v_total := v_total + v_deleted_rows;
COMMIT; -- 提交释放 undo
DBMS_LOCK.SLEEP(0.2); -- 轻微休眠，避免冲击系统
-- 可选：每删除 5 万行输出一次进度（少量日志）
IF MOD(v_total, 50000) = 0 THEN
DBMS_OUTPUT.PUT_LINE('已删除 ' || v_total || ' 行...');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('✅ 删除完成，共删除 ' || v_total || ' 行');
END;
方法二：或者是建新表，插入最新数据，然后直接删除旧表（注意备份）
四、MySQL 修改（UPDATE）
开发中，更新操作必须加 WHERE 条件，禁止全表更新；核心分为单字段更新、多字段更新、条件更新。
1. 基础单字段更新
-- 更新指定用户的手机号（WHERE 锁定主键，安全高效）
UPDATE user_info
SET phone = '13999999999'
WHERE id = 1;
2. 多字段批量更新
-- 同时更新年龄、性别、更新时间
UPDATE user_info
SET age = 26, gender = 2, update_time = NOW()
WHERE username = 'lisi';
3. 条件批量更新
-- 禁用年龄大于30的用户
UPDATE user_info
SET status = 0
WHERE age > 30 AND status = 1;
4. 多表关联更新
作用：根据多张表的关联关系，跨表更新数据
SQL99 标准语法（推荐）
UPDATE 表1 别名1
inner|left|right JOIN 表2 别名2 ON 关联条件
SET 别名1.字段 = 值, 别名2.字段 = 值
WHERE 筛选条件;
案例 1：只更新主表（关联查询条件）
-- 根据男神表，更新对应用户的状态
UPDATE user_info u
JOIN boys b ON u.boyfriend_id = b.id
SET u.status = 0
WHERE b.boyName = '黄晓明';
案例 2：同时更新两张表（跨表更新）
-- 同时更新用户状态 + 男神表的人气值
UPDATE user_info u
JOIN boys b ON u.boyfriend_id = b.id
SET u.status = 1, b.userCP = 1000
WHERE b.id = 5;
左关联更新（包含无关联数据）
-- 左连接更新，没有关联男神的用户统一设为无效
UPDATE user_info u
LEFT JOIN boys b ON u.boyfriend_id = b.id
SET u.status = 0
WHERE b.id IS NULL;
五、MySQL 查询（SELECT）
查询是 CRUD 中最复杂、使用最多的操作，本文覆盖基础查询、条件查询、排序、分页、关联查询、聚合查询、高级查询全场景。
1. 基础查询（规范：禁止使用 SELECT *）
-- 错误写法：查询所有字段，浪费IO，表结构变更后易出问题
-- SELECT * FROM user_info;
-- 正确写法：指定查询字段，强制规范
SELECT id, username, phone, age, status FROM user_info;
2. 条件查询（WHERE 子句）
WHERE 的唯一作用：过滤数据
支持=、!=、>、<、BETWEEN、LIKE、IN、AND/OR等条件。
所有比较运算，结果都是布尔值（TRUE/FALSE）
一条记录、一条记录，挨个拿去和 WHERE 条件比对
过滤的依据：条件必须返回 TRUE / FALSE
-- 1. 单条件查询：查询正常状态的男性用户
SELECT id, username FROM user_info WHERE gender = 1 AND status = 1;
-- 2. 范围查询：年龄25-30岁的用户
SELECT * FROM user_info WHERE age BETWEEN 25 AND 30;
-- 3. 模糊查询：用户名以zhang开头（最左匹配，走索引）
SELECT username, phone FROM user_info WHERE username LIKE 'zhang%';
-- 4. IN 查询：查询指定ID的用户
SELECT id, username FROM user_info WHERE id IN (1,2,3);
-- 5. 非空查询：手机号不为空的用户
SELECT username FROM user_info WHERE phone IS NOT NULL;
⚠️ 禁忌：LIKE '%xxx' 全模糊匹配会失效索引，禁止在大数据量表中使用。
3. 排序查询（ORDER BY）
-- 按年龄降序，年龄相同按创建时间升序（多字段排序）
SELECT id, username, age, create_time FROM user_info
WHERE status = 1
ORDER BY age DESC, create_time ASC;
4. 分页查询（LIMIT）
MySQL 分页使用 LIMIT 偏移量, 每页条数，是列表接口的核心：
-- 第1页，每页3条数据（偏移量从0开始）
SELECT id, username, phone FROM user_info WHERE status = 1 LIMIT 0, 3;
-- 第2页
SELECT id, username, phone FROM user_info WHERE status = 1 LIMIT 3, 3;
⚠️ 大数据量优化：LIMIT 100000,10 性能极差，改用主键过滤分页。
5. 聚合查询（统计数据）
常用函数：COUNT()、SUM()、AVG()、MAX()、MIN()
-- 统计正常用户的总数量、平均年龄、最大年龄
SELECT
COUNT(id) AS total_count, -- 统计总数（用主键，效率最高）
AVG(age) AS avg_age, -- 平均年龄
MAX(age) AS max_age -- 最大年龄
FROM user_info
WHERE status = 1;
6. 分组查询（GROUP BY + HAVING）
-- 按性别分组，统计每组用户数量
SELECT gender, COUNT(id) AS gender_count
FROM user_info
WHERE status = 1
GROUP BY gender
HAVING gender_count > 1; -- 分组后过滤（区别于WHERE）
----------------
-- 按性别分组，同时统计：人数、平均年龄、最大年龄
SELECT
gender,
COUNT(id) AS user_count, -- 统计人数
AVG(age) AS avg_age, -- 平均年龄
MAX(age) AS max_age -- 最大年龄
FROM user_info
WHERE status = 1
GROUP BY gender;
---------------
SELECT
gender,
COUNT(id) AS cnt
FROM user_info
WHERE status = 1
GROUP BY gender
HAVING cnt > 1; -- 只保留人数>1的组
注意事项：
-
SELECT 后面的字段，要么在 GROUP BY 里，要么用聚合函数 -
WHERE 是分组前过滤，HAVING 是分组后过滤 -
HAVING 只能用别名或聚合函数，不能用普通字段
7. 关联查询（多表联合）
以用户表 + 订单表为例，演示内连接、左连接：
-- 新建订单表
CREATE TABLE IF NOT EXISTS order_info (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
user_id BIGINT NOT NULL COMMENT '用户ID',
order_no VARCHAR(32) NOT NULL COMMENT '订单号',
order_amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT '订单表';
-- 1. 内连接（INNER JOIN）：查询有订单的用户+订单信息
SELECT u.username, o.order_no, o.order_amount
FROM user_info u
INNER JOIN order_info o ON u.id = o.user_id
WHERE u.status = 1;
-- 2. 左连接（LEFT JOIN）：查询所有用户，无订单则订单字段为NULL
SELECT u.username, o.order_no
FROM user_info u
LEFT JOIN order_info o ON u.id = o.user_id;六、MySQL CRUD 事务控制
InnoDB 引擎支持事务，保证原子性、一致性、隔离性、持久性（ACID），批量增删改必须开启事务。
-- 开启事务
START TRANSACTION;
-- 业务操作1：新增用户
INSERT INTO user_info (username, password) VALUES ('sunqi', MD5('123456'));
-- 业务操作2：更新用户信息
UPDATE user_info SET age = 24 WHERE username = 'sunqi';
-- 提交事务（所有操作成功执行）
COMMIT;
-- 回滚事务（任意操作失败，撤销所有修改）
-- ROLLBACK;
