MySQL 权限、函数与约束

一、SQL-DCL

DCL 管的是数据库的用户和访问权限，日常写业务的同学碰得不多，主要是 DBA（database administrator，数据库管理员）在用。

1. 用户管理

# 查询用户
use mysql;
select * from user;

# 创建用户
create user '用户名'@'主机名' identified by '密码';

# 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

# 删除用户
drop user '用户名'@'主机名';


# 注意：
# 主机名可以使用 % 通配
# 这类sql开发人员操作的比较少
# 主要是dba（database administrator 数据库管理员）使用

几个例子：

# 创建用户 itcast，只能够在当前主机localhost访问，密码123456
create user 'itcast'@'localhost' identified by '123456';

# 创建用户 heima，可以在任意主机访问该数据库，密码123456
create user 'heima'@'%' identified by '123456';

# 修改用户 heima 的访问密码为 1234
alter user 'heima'@'%' identified with mysql_native_password by '1234';

# 删除 itcast@localhost 用户
drop user 'itcast'@'localhost';

2. 权限控制

常见的权限就这几个，具体含义翻官方文档：

# 常见权限列表

# all, all privileges    所有权限
# select                 查询数据
# insert                 插入数据
# update                 修改数据
# delete                 删除数据
# alter                  修改表
# drop                   删除数据库/表/视图
# create                 创建数据库/表

语法就三条：

# 查询权限
show grants for '用户名'@'主机名';

# 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

# 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

# 注意：
# 多个权限之间，使用逗号分隔
# 授权时，数据库名和表名可以使用 * 进行通配，代表所有
# 最大范围是 *.* 所有库的所有表

对应到具体的人：

# 查询权限
show grants for 'heima'@'%';

# 授予权限
grant all on itcast.* to 'heima'@'%';

# 撤销权限
revoke all on itcast.* from 'heima'@'%';

用户管理和权限控制这几条命令放一起就是：

一句话区分：用户管理管的是能不能连上数据库，权限控制管的是连上之后能对哪些库、哪些表做什么。

二、函数

函数可以直接在 select 里调用，用来把查出来的结果顺手加工一下。

1. 字符串函数

用起来就是 select 函数调用()。这里有个容易错的地方：substring 的索引从 1 开始，没有 0，跟 limit 的从 0 开始不是一回事。

常见的就那么几个，concat、upper、trim、substring 用得最多：

select concat('Hello','shengguo');

# 把员工的工号统一变为 5 位数，不足 5 位前面补 0
update emp set workno = lpad(workno,5,'0');

2. 数值函数

随机数括号内不填数值，写 select rand() 就行。

比较常用的是 round，比如拿它拼一个 6 位随机验证码：

# 生成6位数随机验证码
select lpad(round(rand()*1000000,0),6,'0');
返回 069879

3. 日期函数

依旧是 select 调用。

# 当前日期是几号
select day(now());
返回 17

# 当前时间后面 70 天
# interval 译为间隔
select date_add(now(), interval 70 day);
2026-10-26 07:43:45

select datediff('2025-8-31','2029-3-15');
返回 -1292

# 查所有人的入职天数，根据天数倒序排序
select name,datediff(curdate(),entrydate) from emp;

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

4. 流程函数

ifnull 里说的空值是 null，不是空字符串 ' '，这个别弄混。

case 这两种写法看着像，实际是两回事：带 expr 的是拿一个字段去挨个比等值，不带 expr 的才是写条件判断，按场景挑一个用。

# case 的等值写法，when 后面不需要逗号分隔
select
    name,
    case workaddress
        when '北京' then '一线城市'
        when '上海' then '一线城市'
        else '二线城市'
    end as '工作地址'
from emp;


# 条件判断
# 准备数据
create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
) comment '学员成绩表';

insert into score(id, name, math, english, chinese) values
(1, 'Tom', 67, 88, 95),
(2, 'Rose', 23, 66, 90),
(3, 'Jack', 56, 98, 76);


# 范围判断
# 查询语法：本质是把原来表的数值换成字符串
select
    id,
    name,
    # >= 85, 展示优秀
    # >= 60, 展示及格
    # 否则, 展示不及格
    case when math >= 85 then '优秀'
         when math >= 60 then '及格'
         else '不及格'
    end as '数学',
    case when english >= 85 then '优秀'
         when english >= 60 then '及格'
         else '不及格'
    end as '英语',
    case when chinese >= 85 then '优秀'
         when chinese >= 60 then '及格'
         else '不及格'
    end as '语文'
from score;

case 后面如果不写 as，返回的字段名就是一整串表达式，列头长得没法看：

所以一般都要写一个 as 给它起个名。

三、约束

约束是作用在表中字段上的规则，用来限制表结构里能存什么样的数据。

在建表/修改表的时候添加。

检查约束要 MySQL 8.0.16 之后才真正生效，再往前的版本写了 check 会被直接忽略，建表不报错但也不管用。

1. 演示

多个约束之间用空格分开写。

comment 平时看不到也不怎么主动去看，主要用在字段名无法自解释的情况。比如写了一个 type 字段，值有 1 2 这样的，看不懂这是啥就去翻 comment。

下面这张表就是一个字段对着一条约束的例子，五个字段分别用上了主键自增、非空唯一、check 范围、default 默认值：

照着它建出来：

create table user (
  id int primary key auto_increment comment '主键',
  name varchar(10) not null unique comment '姓名',
  age int check(age > 0 and age <= 120) comment '年龄',
  status char(1) default '1' comment '状态(1正常 0禁用)',
  gender char(1) comment '性别'
) comment '用户表';

# id 设置了自动增长不需要插入数据
# 违反了约束规则的值无法插入
# 如果没有插入成功，也会申请一个主键，id自动增长 1，从而不连续
insert into user(name,age,status,gender) values
('Tom1',19,1,'男'),
('mrica1',25,0,'女');


# 对齐版本
create table user (
# primary key auto_increment 是一个完整的整体，中间用空格隔开
# 和 not null、default、check 根本不是一类东西
  id      int primary key auto_increment comment '主键',
  name    varchar(10)  not null unique comment '姓名',
  age     int          check (age > 0 and age <= 120) comment '年龄',
  status  char(1)      default '1' comment '状态(1正常 0禁用)',
  gender  char(1)      comment '性别'
) comment '用户表';
# 在很多公司的 SQL 规范里，要求主键必须写在字段的最前面
# 且单独一行，以突出它的重要性

除了敲命令，用图形化工具建也一样。

2. 外键约束

外键是让两张表的数据之间建立连接，通过主外键关联。外键所在的是子表，主键所在的是父表。

建外键之前先想清楚两件事：

1. 在哪张表建立外键
2. 理清外键的字段和关联主键的字段

比如 fk_emp_dept_id 这个外键名称里就体现了这两点：

• fk 表示 foreign key 外键
• emp 表示外键所在表
• dept 关联主键所在表
• id 关联主键所在表的字段

先把两张表和数据准备好，建表可以用图形化工具先生成框架，再手动微调：

create table dept(
    id   int primary key auto_increment comment 'ID',
    name varchar(20) null unique comment '部门名称'
) comment '部门表';

insert into dept(name) values
('研发部'),('市场部'),('财务部'),('销售部'),('总经办');


# 第二个表
create table emp(
    id         int auto_increment comment 'ID' primary key,
    name       varchar(50) not null comment '姓名',
    age        int comment '年龄',
    job        varchar(20) comment '职位',
    salary     int comment '薪资',
    entrydate  date comment '入职时间',
    managerid  int comment '直属领导ID',
    dept_id    int comment '部门ID'
) comment '员工表';

# 插入数据
insert into emp (id, name, age, job, salary, entrydate, managerid, dept_id) values
(1, '金庸',     66, '总裁',       20000, '2000-01-01', null, 5),
(2, '张无忌',   20, '项目经理',   12500, '2005-12-05', 1, 1),
(3, '杨逍',     33, '开发',       8400,  '2000-11-03', 2, 1),
(4, '韦一笑',   48, '开发',       11000, '2002-02-05', 2, 1),
(5, '常遇春',   43, '开发',       10500, '2004-09-07', 3, 1),
(6, '小昭',     19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

这里别搞混：alter 是改表结构的，update 是改数据的。

外键约束的增删：

# 添加外键（建表时指定）
create table 表名(
    字段名 数据类型,
    ...
    [constraint] [外键名称] foreign key (外键字段名) references 主表(主表列名)
);

# 添加外键（建表后修改）
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);

# 删除外键
alter table 表名 drop foreign key 外键名称;


# 给 表emp 添加外键
# constraint 译为限制
alter table emp add constraint fk_emp_dept_id
foreign key(dept_id) references dept(id);

建完怎么验证生效了？去删一下部门表里那个还被员工用着的部门，删不掉就说明外键起作用了。如果没报错，可以自己删一行、再插一行、再删掉这一行，看外键有没有拦住。

有外键关联的数据，删除是有顺序的，必须先删子表里的数据（或者先删掉外键）。如果表 1 的某条数据和表 2 没有关联就不用管，比如员工所在部门只用了 1 和 2，那部门表里 3~5 的数据可以直接删。

删父表数据时会被外键拦住：

[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`itcast`.`emp`, CONSTRAINT `fk_emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))

补充一句，图形化工具的报错有时候是误报，命令行里再确认一次比较稳。

3. 外键删除更新行为

外键不光限制插入，还限制了关联数据的删除和更新，对数据做操作的时候要检查一下有没有外键牵着。

要改行为不用把外键删了重建整个流程，就是在添加外键的语法后面加上 on update 和 on delete：

# 添加外键（指定级联行为）
# 就是在添加外键的语法后面增加 on update 和 delete
# 规定更新和删除时的操作
alter table 表名 add constraint 外键名称
foreign key (外键字段) references 主表名(主表字段名)
on update cascade on delete cascade;
# cascade 译为串联


# 不需要删了重新建立，直接写

# 1. 先删除原来的外键
alter table emp drop foreign key fk_emp_dept_id;

# 2. 再重新添加，带上级联行为
alter table emp add constraint fk_emp_dept_id
foreign key (dept_id) references dept(id)
on update cascade on delete cascade;

这一页的约束合起来就是：非空 not null、唯一 unique、主键 primary key（自增 auto_increment）、默认 default、检查 check、外键 foreign key，前面那张表随时可以回头看。