MySQL DCL、函数与约束详解( 安全、效率、完整性的三板斧)_Mysql

学完 dml 的增删改、dql 的查询之后，昨天把 mysql 基础篇剩下的三块硬骨头啃完了： dcl（数据控制语言）、函数、约束。这三块内容看起来零散，其实各守一条主线：

dcl → 安全：谁能连我的数据库？连上能干什么？
函数 → 效率：把计算交给数据库，而不是拿回程序里算；
约束 → 正确性：从源头拦住脏数据，保证数据完整。

这篇笔记把三块内容串起来讲，语法 + 案例 + 我的理解，希望能帮到同样在学 mysql 的朋友。

一、dcl：数据库的 “门禁系统”

dcl 全称 data control language（数据控制语言），用来管理数据库用户、控制访问权限。这类 sql 开发人员用得少，主要是 dba（数据库管理员）在操作，但理解它对你理解 “数据库安全” 很有帮助。

1.1 管理用户：用户 = 用户名 @ 主机名

在 mysql 中，一个用户不是只有 “用户名”，而是由用户名 + 主机名共同唯一标识：

-- 查询所有用户（用户信息存放在 mysql 库的 user 表）
select * from mysql.user;
-- 创建用户：'itcast' 只能在本机（localhost）访问
create user 'itcast'@'localhost' identified by '123456';
-- 创建用户：'heima' 可以在任意主机（%）访问
create user 'heima'@'%' identified by '123456';
-- 修改密码（mysql_native_password 是认证插件）
alter user 'heima'@'%' identified with mysql_native_password by '1234';
-- 删除用户
drop user 'itcast'@'localhost';

关键点：

host 表示该用户允许从哪台主机访问：localhost 只允许本机，% 是通配符，允许任意主机；
所以 'itcast'@'localhost' 和 'itcast'@'%' 是两个不同的用户；
这类操作主要面向 dba，开发人员平时接触不多。

1.2 权限控制：能干什么，细到 “库。表”

-- 查询某用户的权限
show grants for 'heima'@'%';
-- 授予权限：授予 heima 操作 itcast 库所有表的所有权限
grant all on itcast.* to 'heima'@'%';
-- 撤销权限
revoke all on itcast.* from 'heima'@'%';

常见权限有：all / all privileges（所有）、select、insert、update、delete、alter、drop、create 等。

关键点：

多个权限用逗号分隔：grant select, insert, update on itcast.* to ...;
数据库名.表名 支持 * 通配：itcast.* 表示 itcast 库的所有表，*.* 表示所有库的所有表；
权限粒度可以精确到 “某个库的某张表”，这就是 “最小权限” 的基础。

最小权限原则是数据库安全的第一课：给用户只授 “够用” 的权限。业务账号通常只需要 select/insert/update/delete，结构变更（alter/drop）留给 dba 账号。别图省事直接 grant all。

host 就是安全边界：'xxx'@'%' 等于把数据库暴露给任意 ip。生产环境如果业务账号用了 %，风险相当大 —— 最好精确到应用服务器所在的网段或具体 ip。

生产环境不要用 root 跑业务：root 权限无法精细限制，出了问题也没有追溯手段。这也是 “最小权限账号” 存在的意义。

补充两点课件没细说的：

mysql 8 默认认证插件是 caching_sha2_password，老版本客户端连不上时，才需要改成 mysql_native_password；
老版本手册里经常出现 flush privileges，现在 grant/revoke 会立即生效，一般不需要手动刷新。

二、函数：把计算交给数据库

函数是 mysql 内置好的一段可调用代码，业务里直接调用即可。课件用两个场景引入：算入职天数（用 datediff）、判分数等级（用 case when）。mysql 函数分四类：字符串、数值、日期、流程。

2.1 字符串函数

函数	功能
`concat(s1,s2,...)`	字符串拼接
`lower(str)` / `upper(str)`	全部转小写 / 转大写
`lpad(str,n,pad)` / `rpad(str,n,pad)`	左 / 右填充到 n 个字符
`trim(str)`	去掉首尾空格
`substring(str,start,len)`	从 start 起截取 len 个字符

经典案例 —— 工号补零：业务变更要求工号统一为 5 位，不足前面补 0：

update emp set workno = lpad(workno, 5, '0');
-- 1号员工工号 1 → 00001

这个场景在真实业务里非常常见：订单号、学号、流水号要统一长度，lpad/rpad 就是干这个的。

2.2 数值函数

函数	功能
`ceil(x)`	向上取整
`floor(x)`	向下取整
`mod(x,y)`	x/y 的模（余数）
`rand()`	返回 0~1 随机数
`round(x,y)`	四舍五入，保留 y 位小数

经典案例 ——6 位随机验证码：

select lpad(round(rand() * 1000000, 0), 6, '0');

思路拆解：rand() 得到 0~1 随机数 → 乘 1000000 放大 → round(...,0) 取整去掉小数 → lpad(...,6,'0') 不足 6 位补零。三个函数嵌套在一起，从里往外读，逻辑很清晰。

2.3 日期函数

函数	功能
`curdate()` / `curtime()` / `now()`	当前日期 / 当前时间 / 当前日期时间
`year(date)` / `month(date)` / `day(date)`	提取年 / 月 / 日
`date_add(date, interval expr type)`	日期加指定时间间隔
`datediff(date1, date2)`	两个日期相差天数

经典案例 —— 入职天数：查询所有员工的入职天数，按入职天数倒序：

select name, datediff(curdate(), entrydate) as 'entrydays'
from emp
order by entrydays desc;

2.4 流程函数

函数	功能
`if(value, t, f)`	value 为真返回 t，否则返回 f
`ifnull(value1, value2)`	value1 非空返回它，否则返回 value2
`case when 条件 then 结果 ... else 默认 end`	条件判断（可多分支）
`case 表达式 when 值 then 结果 ... end`	等值判断

经典案例 —— 工作地址分级：北京 / 上海 → 一线城市，其他 → 二线城市：

select name,
       (case workaddress when '北京' then '一线城市'
                         when '上海' then '一线城市'
                         else '二线城市' end) as '工作地址'
from emp;

经典案例 —— 成绩分档：≥85 优秀、≥60 及格、否则不及格（注意这里用的是 case when 条件式写法，因为要做范围判断）：

select id, name,
       (case when math >= 85 then '优秀'
             when math >= 60 then '及格'
             else '不及格' end) as '数学',
       (case when english >= 85 then '优秀'
             when english >= 60 then '及格'
             else '不及格' end) as '英语'
from score;

单行函数 vs 聚合函数：今天学的都是单行函数（对一行数据计算，返回一行结果）；之前学的 count/avg/max 是聚合函数（多行压成一行）。搞清楚这个分类，读 sql 心里更有数。

函数可以嵌套：验证码案例就是三层嵌套。嵌套函数的读法是 “从里往外”—— 最里层先算，结果作为外层参数。

几个易错点：

substring 的起始位置从 1 开始，substring('hello mysql', 1, 5) 结果是 hello；
trim 只去首尾空格，不去中间空格；
ifnull 只认 null：ifnull('', 'default') 返回 ''（空字符串不是 null，不会走默认值）；
两种 case 写法别混用：case 字段 when 值 只能做等值判断，范围判断必须用 case when 条件。

补充常用函数（课件没列但很常用）：char_length（字符数）、replace（替换）、date_format（日期格式化）、timestampdiff（更灵活的时间差）、last_day（当月最后一天）。

一个进阶提醒：在 where 里对字段套函数（如 where year(entrydate) = 2020）会导致该字段的索引失效、全表扫描。函数不是随便放哪都行，“函数用在哪” 是 sql 优化的经典考点，进阶篇会展开。

三、约束：数据完整性的守门员

概念：约束是作用于表中字段上的规则，用于限制存储的数据。目的：保证数据库中数据的正确性、有效性和完整性。

3.1 六种约束总览

约束	描述	关键字
非空约束	限制字段不能为 null	`not null`
唯一约束	保证字段值唯一、不重复	`unique`
主键约束	一行数据的唯一标识，非空且唯一	`primary key`
默认约束	未指定值时采用默认值	`default`
检查约束（8.0.16+）	保证字段值满足某个条件	`check`
外键约束	让两张表建立连接，保证一致性	`foreign key`

约束可以在创建表或修改表时添加。

3.2 一个建表演示

create table tb_user(
    id int auto_increment primary key comment 'id唯一标识',
    name varchar(10) not null unique comment '姓名',
    age int check (age > 0 && age <= 120) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
);

验证约束是否生效：

插 name = null → 违反 not null，报错；
插重复 name → 违反 unique，报错；
插 age = -1 或 age = 121 → 违反 check，报错；
不填 status → 自动变成默认值 '1'（default 生效）。

3.3 外键约束：让两张表 “拉上关系”

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。典型场景：员工表 emp.dept_id 关联部门表 dept.id。

课件里做了一个很好的实验：不建外键时，直接删除 dept 表 id=1 的部门会成功，但 emp 表里还有一堆员工挂着 dept_id=1—— 数据 “悬空” 了，这就是不一致。外键就是来解决这个问题的。

-- 建表时添加外键
[constraint 外键名] foreign key (外键字段) references 主表(主键列);
-- 给已有表添加外键（emp.dept_id → dept.id）
alter table emp add constraint fk_emp_dept_id
foreign key (dept_id) references dept(id);
-- 删除外键
alter table emp drop foreign key fk_emp_dept_id;

加了外键之后，再删除 dept 里被引用的记录会直接报错—— 外键生效了。

删除 / 更新行为：父表记录被删 / 改时，子表怎么办？有四种选择（配图见下）：

行为	效果
`no action` / `restrict`	子表有引用就拒绝删除 / 更新（默认，两者一致）
`cascade`	子表级联删除 / 更新
`set null`	子表外键列置为 null（要求外键允许 null）
`set default`	置为默认值（innodb 不支持）

-- 级联示例：父表更新/删除时，子表跟着变
alter table emp add constraint fk_emp_dept_id
foreign key (dept_id) references dept(id)
on update cascade on delete cascade;

约束的本质是把规则交给数据库。没有约束时，数据质量只能靠应用代码 “自觉”；一旦换入口（dba 直接改库、脚本批量导入），脏数据就进来了。约束是数据库层面的守门员，从源头拦截。

主键 vs 唯一约束：

主键 = 非空 + 唯一，一张表只能有一个主键；
唯一约束可以有多个，而且唯一约束允许多个 null（null 之间不算重复）；
两者都会自动创建索引（这也是 “约束顺便建索引” 的知识点）。

check 约束的版本坑：check 语法在 mysql 5.7 就能写，但 8.0.16 之前会被静默忽略—— 不报错、不生效。所以别以为写了 check 就万事大吉，先确认版本。

外键 vs 逻辑外键（面试高频）：

用外键：一致性由数据库强制保证，安全；
不用外键：每次增删改都要检查关联，有性能开销；父表删除受限，业务不灵活；表间强耦合。
现实里很多互联网公司用 “逻辑外键”—— 表里存 dept_id，但不在数据库建外键，由应用层保证一致性。我的观点：数据一致性要求高、写并发低的系统用外键；高并发互联网场景常用逻辑外键 + 应用层校验。面试时能说出这个取舍，比单纯背定义强得多。

cascade 是双刃剑：级联删除确实方便，但 delete from dept where id=1 可能连带删掉几百条员工记录，删错不可逆。生产环境对级联要非常谨慎。课件也提醒：业务系统中一般不会修改主键值。