mysql基础语法与进阶#

数值函数#

雷点：

1. ROUND 和 TRUNCATE 的天壤之别： 这是面试和实际开发中最容易踩的坑！ROUND(2.5) 结果是 3（四舍五入），但 TRUNCATE(2.5, 0) 结果是 2（直接截断）。涉及金额计算、精度要求高的场景，务必搞清楚业务到底要不要“四舍五入”，用错了函数，财务找你就寄了！！！
2. RAND() 的性能陷阱： 想随机抽 10 条数据？很多人写 SELECT * FROM table ORDER BY RAND() LIMIT 10;。数据量少时没问题，一旦表里有几十万行，MySQL 会给每一行都算一遍随机数再排序，直接把你 CPU 跑满，查询慢到超时！ 大数据量随机抽取，去查查“主键范围随机法”！
3. 负数取余的坑： MOD(-10, 3) 的结果是 -1，而不是 2！MySQL 的 MOD 运算结果符号跟被除数（第一个参数）保持一致。如果你需要数学上严格的正余数，记得自己加个判断或者用 ABS() 包一下！
4. DIV 也是数值函数： 刚才算术运算符里提过 DIV，它其实是 FLOOR(x / y) 的快捷写法。SELECT 10 DIV 3; 结果是 3。做整数除法时，用 DIV 比写 FLOOR(x / y) 更简洁，代码可读性更高！

字符串函数#

雷点：

1. CONCAT 的 NULL 黑洞： 这是生产环境最高频的 Bug！假设你要拼姓名：CONCAT(first_name, last_name)。如果某个人没填 last_name（值为 NULL），整个结果直接变 NULL，前端展示一片空白！ 修正：要么用 IFNULL(last_name, '') 包一下，要么直接用 CONCAT_WS('', first_name, last_name)，让它自动忽略 NULL！
2. LENGTH 和 CHAR_LENGTH 的字节陷阱： 在 UTF8 编码下，LENGTH('好') 返回 3，CHAR_LENGTH('好') 返回 1。 如果你用 SUBSTRING(name, 1, 5) 去截一个包含中文的名字，刚好切在某个汉字的第 2 个字节上，这个汉字就废了，显示出来是个问号 ? 或者乱码！ 建议：涉及中文处理，无脑用 CHAR_LENGTH 和 SUBSTRING，MySQL 的 SUBSTRING 是按字符截取的，相对安全；但千万别用 LENGTH 的结果去控制截取长度！
3. LIKE 与 INSTR 的性能账： 想查包含 “ABC” 的记录？WHERE name LIKE '%ABC%' 和 WHERE INSTR(name, 'ABC') > 0 效果一样。 但注意：只要 % 写在最前面，索引直接失效！ 全表扫描慢到你怀疑人生。如果数据量大，赶紧上 Elasticsearch 或者全文索引，别在 SQL 里硬扛！
4. REPLACE 的连环坑： REPLACE 是全局替换！如果你想把 '12345' 里的 '23' 换成 'X'，结果是 '1X45'。但如果你不小心把 '1' 换成 '2'，原来的 '2' 不会被再次替换，它只扫一遍。但在复杂嵌套替换时，务必小心顺序，别把自己绕晕了！

时间函数#

雷点：

1. NOW() 和 SYSDATE() 的致命区别： 很多人以为它俩一样，大错特错！NOW() 在语句开始执行时就定死了，整个查询过程中值不变；而 SYSDATE() 是动态的，每调用一次就取一次系统时间。 建议：在主从复制、存储过程或者涉及 SLEEP() 的场景下，无脑用 NOW()！用 SYSDATE() 会导致主库和从库记录的时间不一致，数据对不上账别来哭！
2. DATEDIFF 的正负号陷阱： DATEDIFF('2024-05-01', '2024-05-19') 的结果是 -18！它是用第一个参数减去第二个参数。 如果你想算“距离到期还有几天”，一定要把未来时间放在前面，或者外面包个 ABS() 取绝对值。写反了导致给用户发“逾期 -3 天”的短信，场面会非常尴尬！
3. DATE_FORMAT 的大小写敏感： %m 是月份（01-12），%i 才是分钟（00-59）！很多新手想拼 HH:MM:SS，结果写成 %H:%m:%s，把月份当成分钟显示出来了。 记住口诀：分钟是 i (minute 中间那个 i)，秒是小写 s，毫秒是大写 f。格式符写错了，MySQL 不会报错，但会给出一堆莫名其妙的数字！
4. 时间运算的索引失效： 想查“2024 年的所有订单”？千万别写 WHERE YEAR(create_time) = 2024！这会让 create_time 字段上的索引直接失效，全表扫描慢到爆炸！ 正确写法：WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'。用范围查询代替函数计算，索引才能乖乖干活！

流程控制#

函数

语句：下面前两个最重要

1. WHERE 子句里严禁出现聚合函数！#

这是新手最容易犯的语法错误！你想查“工资高于平均工资的员工”，千万别这么写：

1
-- ❌ 绝对报错！ERROR 1111: Invalid use of group function
2
SELECT ename, sal FROM emp WHERE sal > AVG(sal);

为什么？ 因为 SQL 的执行顺序是 FROM -> WHERE -> GROUP BY -> SELECT。执行到 WHERE 的时候，数据还没分组呢，AVG(sal) 根本算不出来！ ✅ 修正： 这种需求必须用子查询或者 HAVING！

1
-- ✅ 正确写法：用子查询先算出平均值
2
SELECT ename, sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

2. COUNT(*)、COUNT(1) 和 COUNT(字段) 到底选哪个？#

• COUNT(\*)：MySQL 官方优化过，统计总行数最快！不管字段是不是 NULL，只要是一行有效记录就 +1。日常统计总数，无脑用它！
• COUNT(字段)：只统计该字段不为 NULL 的行数。比如 COUNT(comm) 能直接告诉你“有多少人拿了提成”。
• COUNT(1)：效果和 COUNT(*) 几乎一样，但在老版本 MySQL 或某些特定引擎下，COUNT(*) 的优化更好。别听网上瞎扯什么 COUNT(1) 更快，现代 MySQL 里它俩没区别，写 COUNT(\*) 语义更清晰！

3. 聚合函数不能嵌套！#

别想着偷懒写 SELECT AVG(SUM(sal)) FROM emp GROUP BY deptno;，MySQL 直接报错。聚合函数只能套一层，想搞复杂统计，乖乖用子查询包起来！

4. HAVING 才是聚合函数的归宿#

如果你想对分组后的统计结果做筛选（比如“找出平均工资大于 2000 的部门”），必须用 HAVING，别用 WHERE！

1
-- ✅ 正确写法：HAVING 在分组后过滤，能用聚合函数
2
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2000;

记住口诀：WHERE 过滤原始行，HAVING 过滤统计结果。搞混了查出来的数据就是错的！

1.非空约束#

1
添加（建表时）
2
create table 表名称(
3
  字段名 类型 Not NULL
4
  字段名 类型 Not NULL
5
)
6

7
建表后修改
8
alter table 表名 modify 字段名 数据类型 not null;
9

10
删除
11
alter table 表名 modify 字段名 数据类型 null
12
或者alter table 表名 modify 字段名 数据类型
13
不加默认允许null。

2.默认值约束#

1
建表添加
2
create table 表名称(
3
  字段名 类型 default 默认值,
4
  字段名 类型 Not NULL default 默认值
5
)
6

7
建表后修改
8
alter table 表名称 modify 字段名 类型 default 默认值;
9
#如果这个字段原来就有非空约束，你还保留非空约束，那么你在修改加默认值时还必须保留非空约束，不然就没了
10
alter table 表名称 modify 字段名 类型 default 默认值 not null;
11

12
删除
13
alter table 表名称 modify 字段名 数据类型;
14
#删除默认值约束，也不保留非空约束
15

16
alter table 表名称 modify 字段名 数据类型 not null;
17
#删除默认值约束，保留非空约束

3.检查约束#

1
添加（建表时）
2
create table 表名称(
3
  字段名 类型 Not NULL,
4
  check(表达式),#check约束属于表级别，不用添加到列后。
5
  字段名 类型 Not NULL default 默认值
6
);
7

8
建表后修改
9
alter table 表名 add constraint 约束名 check（表达式）;#约束名不能重复
10

11
删除
12
alter table 表名 drop constraint 约束名;

查看约束的方法——百度一下都知道😋

1.唯一约束#

1
添加（建表时）
2
create table 表名称(
3
  字段名 类型 unique,
4
  字段名 类型 unique key
5
);
6
create table 表名称(
7
  字段名 类型 ，
8
    [constraint 约束名] unique key(字段名)
9
);
10
建表后修改
11
alter table 表名 add constraint 约束名 unique(列名，列名······)

2.主键约束#

主键作用：确保行数据至少有一列是不重复的，避免了数据整行重复，我们可以把永远不重复且非null定为主键列。如学生号，id。

自定义主键：人为创建的一列，专门用来做标记不重复。

自然主键：实体自带的属性列，并且唯一不为空。比如dna序列。

1
细节说明
2
主键数量：每个表只能有一个主键
3
单一和复合：主键可以由单个列或多个列构成（复合主键）
4
主键列类型：可以是任意的，只要不重复。
5
主键命名：一半采用xx——id。
6
主键索引：创建主键约束时，系统默认在列或列组合上建立对应的主键索引（根据主键查询效率高）。如果删除主键约束了，对应索引也会被删除，主键索引命令primary

主键约束

光有主键只是标识了对象，但对这个对象的要求（不为空，不重复）并没有实现。所以我们必须还要实现主键约束。

1
语法
2
添加（建表时）
3
create table 表名称(
4
  字段名 类型 primary key,#列级模式
5
);
6
create table 表名称(
7
  字段名 类型 ，
8
    [constraint 约束名] primary key(字段名)，#表级模式
9
);
10
建表后修改
11
alter table 表名  add primary key(字段列表)
12

13
#删除
14
alter table 表名称 drop primary  key;

3.自增长约束#

作用：限定某个列插入数据不需要维护，值自动增长。

特点：只能加在主键列，普通的不行。每张表只能有一个。增加自增长的必须是整数类型。如果设置0或者null，他会自己增长。

1
语法
2
添加（建表时）
3
create table 表名称(
4
  字段名 类型 primary key auto_increment,#列级模式
5
);
6
create table 表名称(
7
  字段名 类型 unique key auto_increment，#表级模式
8
);
9
建表后修改
10
alter table 表名  modify 字段名 数据类 auto_increment
11

12
#删除
13
alter table 表名称 modify 字段名 类型

1.内连接#

内连接是一种用于从两个或多个表中检索数据的查询方式。内连接会根据指定的连接条件，将两个表中满足条件的行进行匹配，返回成功的行。

并且内连接必须满足要有主外键！什么意思？假如一个员工没有部门，且部门是外键，它的为null。那么他的数据就不会被查到。

1
内连接： from 表1 [inner] join 表2 on 表1.主键=表2.外键（标准）
2
或者from 表1，表2 where 表1.主键=表2.外键（非标准）
3
#还可以在select 后加上as起别名。

2.外连接#

与内连接不同，外连接会返回所有符合条件的行，同时还会返回未匹配的行。分为左外连接，右外连接。也就是通过左和右来指定一个逻辑主表，逻辑主表的数据一定会查询到。

1
语法：
2
select * from 表1 left [outer] join 表2 on 表1.主键=表2.外键（左外）
3
select * from 表1 right [outer] join 表2 on 表1.主键=表2.外键（右外）

一但选择左或者有右，那后序都是左，右。

3.自然连接#

自然连接是外连接和内连接的升级版，会自动找到两个表中相同的列名判定相等，所以可忽略 on主=外。

1
语法
2
select * from emp natural join dept;#自然内连接
3
select * from emp natural left join dept;#自然左外连接
4
select * from emp natural right join dept;#自然右外连接

4.自连接#

自连接并不是新的语法，是指一个表与自身连接的操作。他在查询中使用相同表的别名来表示两个不同的实例，然后通过连接条件将这两个实例连接。

比如一张表里本来就存在引用关系，要查询关联在同一张表的不同行。

1
有一张员工表，领导也算在内！
2
查询员工的编号，姓名，领导编号——》单表查询
3
查询员工的编号，姓名，领导编号，领导姓名——》自连接，一个表用两次

1
语法select e1.eid, e1.ename ,e1.mid, e3.enmae from t_emp e1
2
      left join t_emp e2 on e1.mid=e2.eid
3
      lefr join t_emp e3 on e2.mid = e3.mid
4
      where e1.eid = 5

1.单行单列#

1
查询每个部门的平均工资和公司的平均工资之差。
2
#1.查询公司的平均工资
3
select avg(salary) from t_emp;
4
#2.分组查询部门工资
5
select did,avg(salary) from t_emp group by did;
6
#3.select did,avg(salary),avg(salary)-(select avg(salary) from t_emp) group by did

2.单行多列#

1
select * from t_emp where(gender.did) in(select gender.did from t_emp where ename ="巴黎")

3.多行单列#

通常使用关键字：in，not in ,all(相当于and，要满足全部),any（相当于or，满足一个即可

1
select * from t_emp where did=any(select aid from t_emp where ename in ("jian","jito"))

4.多行多列#

一般用作一个虚拟表

1
select t_emp.did,dname from t_emp left join (select did,avg(salary) from emp group by did) temp on t_emp.did =temp.did
2
#由于用作虚拟表，所以要起别名！这里是temp！

5.update#

1
#当update的表和子查询的表是同一个表时，会出现引用相同结果导致锁不让修改值的问题。需要将子查询的结果用临时表来表示。即再嵌套一层查询
2

3
update t_emp set salary = (select salary from (select salary from t_emp where ename ="sun") temp ) where ename ="jito"
4
#这里别忘了起别名

6.insert#

1
#创键表tmp，复制某个表结构t_emp
2
create table tmp like t_emp
3
#使用insert复制数据，此时insert不用写values
4
insert into tmp (select * from t_emp)
5
#同时复制表结构和表数据，直接完成上面两个操作
6
create table tmp as (select * from t_emp)

7.delete#

1
delete from t_emp where did = (select did from (selec did from t_emp where ename = 'joe') temp);

事务实现方案1——手动提交#

mysql默认自动提交事务，每一条语句都是一个事务，报错失败就回滚。我们想要把多条语句加到一个事务中，我们可以手动来。

1
#开启手动提交事务（取消自动提交事务）
2
set autocommit = false;或autocommit = 0
3
上面的语句执行后，他之后的所有sql都需要手动提交才能生效，知道恢复提交模式，
4
#恢复自动提交
5
set autocommit = true;或autocommit = 1
6
#查看是否自动提交
7
show variables like 'autocommit'

例如

1
set autocommit =false;#设置手动提交模式
2
update t_emp set salary = 15000 where ename = 'joy'
3
commit#提交
4
或者
5
rollback#回滚。

2.实现方案2——独立事务空间#

1
start transaction;
2
#添加多个sql命令
3
update t_emp set salary =0 where name = “啊发发”
4
#回滚或提交，回滚或提交之前，上面的多个sql都不会生效！
5
commit 或者  rollback;

注意！DDL不支持事务！所以回滚不了，删库跑路不能复原。

3.隔离级别#

先来看看三大异常

核心区分：不可重复读针对的是某一行数据的值被修改（UPDATE）；幻读针对的是结果集的行数发生变化（INSERT/DELETE）。

再来看看隔离级别：级别高越安全但是并发越拉

1
修改隔离级别，通常推荐第二个
2
select transaction_isolation="隔离级别";
3
查看隔离级别
4
select @@transaction_isolation;

第一步：恢复全量备份（建立基准）#

千万别直接在原库上搞！找个临时实例或者新建一个库 temp_db，把最近一次的全量备份导进去。

1
# 假设你有凌晨2点的全量备份文件 full_backup.sql
2
mysql -u root -p temp_db < /backup/full_backup.sql

此时，temp_db 里的数据停留在凌晨 2:00。

第二步：定位误操作位置（最关键！）

你需要找到那条“罪魁祸首”SQL 在 binlog 里的具体位置。

1. 查看 binlog 文件列表：

   1
   SHOW BINARY LOGS;

   找到包含误操作时间段的那个文件（比如 mysql-bin.000023）。

2. 解析 binlog 内容： 使用 mysqlbinlog 工具把二进制日志转成可读的 SQL。推荐加上 --base64-output=DECODE-ROWS -v，这样能看清具体的行变更。

   1
   # 导出指定时间段的日志到文本文件分析
   2
   mysqlbinlog --base64-output=DECODE-ROWS -v \
   3
   --start-datetime="2026-05-22 02:00:00" \
   4
   --stop-datetime="2026-05-22 15:00:00" \
   5
   /var/lib/mysql/mysql-bin.000023 > /tmp/binlog_analysis.sql

   打开 /tmp/binlog_analysis.sql，搜索你的误操作（比如 DROP TABLE 或 DELETE FROM orders）。你会看到类似这样的注释：

   sql

   1
   # at 123456
   2
   #260522 14:30:00 server id 1 end_log_pos 123789 ...
   3
   DELETE FROM orders WHERE ...  <-- 找到这条！

   记下误操作之前的那个 end_log_pos（比如 123456），这就是恢复的终点。

第三步：重放 binlog（精准回滚）#

现在要把从全量备份时刻到误操作前一秒的所有变更，应用到 temp_db。

1
# 方案A：按时间点恢复（简单，但可能跨文件）
2
mysqlbinlog --start-datetime="2026-05-22 02:00:00" \
3
--stop-datetime="2026-05-22 14:29:59" \
4
/var/lib/mysql/mysql-bin.000023 | mysql -u root -p temp_db
5

6
# 方案B：按位置恢复（更精确，推荐！）
7
# 假设全量备份对应的 binlog position 是 107，误操作前的 position 是 123456
8
mysqlbinlog --start-position=107 \
9
--stop-position=123456 \
10
/var/lib/mysql/mysql-bin.000023 | mysql -u root -p temp_db

注意：如果 binlog 跨越了多个文件（比如从 000022 到 000023），必须按文件名顺序依次执行，不能乱序！

第四步：验证并导回生产库

在 temp_db 里查一下数据，确认误删的数据回来了，且后续的正常业务数据也没丢。确认无误后，把这部分差异数据导回原库，或者直接替换表。

“防翻车”终极警告

1. 严禁在生产库直接重放 binlog！ 这是大忌！直接在原库执行 mysqlbinlog | mysql，万一中间报错或者又执行了一遍误操作，数据彻底烂尾，神仙也救不回来。必须在临时实例或从库上恢复，验证好了再导回去。
2. binlog 格式必须是 ROW 模式！ 检查 my.cnf 里的 binlog_format。如果是 STATEMENT 模式，某些函数（如 NOW()、UUID()）在重放时会产生不一样的值，导致数据错乱。生产环境强制设为 ROW，它记录的是每一行的具体变更，恢复最安全。
3. 小心 expire_logs_days 坑！ MySQL 默认会清理过期的 binlog。如果你发现误删是一周前的事，但 binlog 只保留了 7 天，那很抱歉，日志可能已经被自动 purge 掉了。生产环境建议至少保留 7-14 天的 binlog，并且定期把旧日志备份到对象存储（如 OSS/S3）。
4. GTID 环境的特殊处理 如果你的 MySQL 开启了 GTID（全局事务标识），直接用 mysqlbinlog 回放可能会报 GTID already exists 错误。这时候必须加上 --skip-gtids=true 参数，告诉 MySQL 别管事务 ID，只管执行 SQL。

1. 排名函数：解决“并列”与“跳号”的纠葛#

这是面试和实战中最高频的考点！假设一个部门有 4 个人，薪资分别是 20000, 20000, 15000, 10000，这三个函数的表现截然不同：

• ROW_NUMBER()：铁面无私。不管薪资是否相同，强制给出唯一连续序号 1, 2, 3, 4。适合分页、去重（比如每个用户只保留最新一条订单）。
• RANK()：比赛逻辑。两人并列第 1，下一个直接跳到第 3，结果 1, 1, 3, 4。中间会跳号。
• DENSE_RANK()：阶梯逻辑。两人并列第 1，下一个紧挨着是第 2，结果 1, 1, 2, 3。不跳号，适合做等级评定。
• NTILE(n)：均匀分桶。把窗口内的数据强行切成 n 份。比如 NTILE(4) 就是把薪资从高到低切成 4 个四分位，适合做薪资分层分析。

2. 聚合窗口函数：明细与汇总同屏#

普通 SUM() 会把多行压成一行，但加上 OVER() 后，它能在每一行旁边附加一个聚合结果，行数绝不减少。

• 累计求和：SUM(amount) OVER (ORDER BY order_date)。默认范围是从分区第一行累加到当前行，轻松算出每日累计营收。
• 移动平均：AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)。明确指定物理行范围（前2行+当前行），算出近3天的移动平均值，平滑波动。
• 占比计算：amount / SUM(amount) OVER ()。分母不带 PARTITION BY 就是全表总和，直接算出每笔订单占全局总金额的比例。

3. 偏移函数：告别自连接#

以前算“本月比上月增长多少”，得把表自连接一次，还要处理月初月末的边界。现在用偏移函数直接跨行取值：

• LAG(col, 1)：取前 1 行的值。第一行没有前值，默认返回 NULL。
• LEAD(col, 1)：取后 1 行的值。最后一行没有后值，默认返回 NULL。
• 实战公式：(本月销售额 - LAG(本月销售额, 1) OVER (ORDER BY 月份)) / LAG(本月销售额, 1) OVER (ORDER BY 月份)。一行语句直接算出环比增长率，无需任何应用层代码。

4. 首尾值函数的巨坑预警#

FIRST_VALUE() 和 LAST_VALUE() 看起来简单，但 LAST_VALUE() 有个致命陷阱！ 它的默认帧范围是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从起点到当前行）。这意味着，如果你不手动指定范围，当前行的 LAST_VALUE 往往就是它自己！ 正确写法：想取整个分区的最后一个值，必须显式指定范围到无穷远：