用户管理

创建用户

主机名没指定默认是 % ，不指定密码用户可以直接登录，但是不安全，不推荐使用。

1
2
3
4
# mysql5.7
create user 用户名@主机名 identified by '密码';
# mysql8
create user 用户名@主机名 identified with mysql_native_password by '密码';

案例：在本地localhost创建一个用户名为 test，密码为 123456 的用户

1
create user test@localhost identified by '123456';

查看用户

1
show grants for 用户名@主机名;

修改用户

修改用户名

1
2
3
4
# 方式一
rename user 旧用户名@主机名 to 新用户名@主机名;
# 方式二
update mysql.user set user ='新用户名' where user = '旧用户名' and host ='旧主机名';

修改主机名

1
update mysql.user set host ='新主机名' where user = '旧用户名' and host ='旧主机名';

修改密码

1
2
3
4
5
6
# 使用ALTER语句来修改密码
alter user 用户名@主机名 identified by '新密码';
# 使用SET命令来修改密码
set password for 用户名@主机名 = password('新密码'); 
# 使用UPDATE语句修改密码（不推荐）
update MySQL.user set authentication_string= password("123456") where User = "用户名" and Host = "主机名";

案例：修改当前用户的密码

1
2
# CURRENT_USER() 是 MySQL 内置的一个函数，用于获取当前登录用户的用户名和主机名，并作为结果返回
alter user CURRENT_USER() identified by '新密码';

禁用用户

在 MySQL 中，可以通过修改用户的访问权限来禁止用户登录

1
alter user 用户名@主机名 account lock;

启用用户

1
alter user 用户名@主机名 account unlock;

删除用户

1
2
3
4
# drop user方式：会删除用户以及对应的权限，执行命令后mysql.user表和mysql.db表的相应记录都会消失（推荐） 
drop user 用户名@主机名;
# delete方式：删除后系统会有残留信息保留，只会删除 mysql.user 表中的对应记录，不会影响授权表等其他表中的数据
delete from mysql.user where Host = '主机名' and User = '用户名';

角色管理

创建角色

1
create role 角色名称;

删除角色

1
drop role 角色名称;

为用户分配角色

1
grant 角色名称 to 用户名@主机名;

撤销用户角色

1
revoke 角色名称 from 用户名@主机名;

权限管理

权限列表

查看MySQL有哪些权限

1
show privileges;
权限名作用范围描述
Alter表格修改表格
Alter routine函数、存储过程修改或删除存储的函数/存储过程
Create数据库、表格、索引创建新的数据库和表格
Create routine数据库使用CREATE FUNCTION/PROCEDURE创建存储过程/存储函数
Create role服务器管理员创建新角色
Create temporary tables数据库使用CREATE TEMPORARY TABLE
Create view表格创建新视图
Create user服务器管理员创建新用户
Delete表格删除现有行
Drop数据库、表格删除数据库、表格和视图
Drop role服务器管理员删除角色
Event服务器管理员创建、修改、删除和执行事件
Execute函数、存储过程执行存储的例程
File在服务器上访问文件在服务器上读写文件
Grant option数据库、表格、函数、存储过程将您拥有的权限授予其他用户
Index表格创建或删除索引
Insert表格向表格中插入数据
Lock tables数据库使用LOCK TABLES（与SELECT权限一起使用）
Process服务器管理员查看当前执行的查询的纯文本
Proxy服务器管理员实现代理用户
References数据库、表格在表格上具有引用权
Reload服务器管理员重新加载或刷新表格、日志和权限
Replication client服务器管理员询问从服务器或主服务器的位置
Replication slave服务器管理员从主服务器读取二进制日志事件
Select表格检索表格中的行
Show databases服务器管理员通过SHOW DATABASES查看所有数据库
Show view表格使用SHOW CREATE VIEW查看视图
Shutdown服务器管理员关闭服务器
Super服务器管理员使用KILL线程、SET GLOBAL、CHANGE MASTER等
Trigger表格使用触发器
Create tablespace服务器管理员创建/更改/删除表空间
Update表格更新现有行
Usage服务器管理员没有特权-仅允许连接
AUTHENTICATION_POLICY_ADMIN服务器管理员
GROUP_REPLICATION_STREAM服务器管理员
XA_RECOVER_ADMIN服务器管理员
SHOW_ROUTINE服务器管理员
RESOURCE_GROUP_USER服务器管理员
REPLICATION_APPLIER服务器管理员
PASSWORDLESS_USER_ADMIN服务器管理员
INNODB_REDO_LOG_ENABLE服务器管理员
GROUP_REPLICATION_ADMIN服务器管理员
FLUSH_USER_RESOURCES服务器管理员
PERSIST_RO_VARIABLES_ADMIN服务器管理员
ROLE_ADMIN服务器管理员
BACKUP_ADMIN服务器管理员
CONNECTION_ADMIN服务器管理员
SET_USER_ID服务器管理员
SESSION_VARIABLES_ADMIN服务器管理员
RESOURCE_GROUP_ADMIN服务器管理员
INNODB_REDO_LOG_ARCHIVE服务器管理员
BINLOG_ENCRYPTION_ADMIN服务器管理员
REPLICATION_SLAVE_ADMIN服务器管理员
SYSTEM_VARIABLES_ADMIN服务器管理员
SYSTEM_USER服务器管理员
APPLICATION_PASSWORD_ADMIN服务器管理员
TABLE_ENCRYPTION_ADMIN服务器管理员
SERVICE_CONNECTION_ADMIN服务器管理员
AUDIT_ADMIN服务器管理员
BINLOG_ADMIN服务器管理员
ENCRYPTION_KEY_ADMIN服务器管理员
CLONE_ADMIN服务器管理员
FLUSH_OPTIMIZER_COSTS服务器管理员
FLUSH_STATUS服务器管理员
FLUSH_TABLES服务器管理员

授予权限

给用户授权的方式有 2 种，分别是通过把 角色赋予用户给用户授权 和 直接给用户授权。

1
grant 权限列表 on 数据库名称.表名称 to 用户名@主机名 identified by '密码';

查看权限

查看当前用户权限

1
2
3
4
# 查看特定用户账户及其对应主机名的权限
show grants for 用户名@主机名;
# 查看当前会话的用户账户的权限
show grants for CURRENT_USER();

收回权限

收回权限后，须用户重新登录后才能生效

1
revoke 权限列表 on 数据库名称.表名称 from 用户名@主机名;

约束 (constraint)

约束是作用于表上的规则，用于限制加入表的数据，约束的存在保证了数据库中数据的正确性、有效性和完整性

约束关键字

关键字约束名称描述
not null非空约束限定某个字段/某列的值不允许为空
unique唯一约束用来限制某个字段/某列的值不能重复。
primary key主键约束主键是一行数据的唯一标识，要求非空且唯一
check检查约束检查某个字段的值是否符号xx要求，一般指的是值的范围，保证列中的值满足某一条件
default默认约束保存数据时，未指定值则采用默认值
foreign key外键约束外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性，即其他表中的主键，当做该表的外键。
auto_increment自增约束某个字段的值自增，当列是数字类型并且唯一约束时才可以使用

约束语法

创建表时添加约束

1
2
3
4
5
6
7
create table [if not exists]表名 (
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	……
	[表约束条件]
);

建完表后添加约束

1
create table 表名 modify 字段名 数据类型 约束条件;

查看某个表已有的约束

1
select * from information_schema.table_constraints where table_name = '表名称';

删除约束

1
alter table 表名 modify 字段名 数据类型;

非空约束（not null）

建表时添加非空约束

1
2
3
4
5
create table 表名（
	字段名 类型 primary key，
	字段名 类型 not null，
	……
);

建表后添加非空约束

1
alter table 表名称 modify 字段名 数据类型 not null;

删除非空约束

1
alter table 表名称 modify 字段名 数据类型 [null];

唯一约束（unique）

建表时添加唯一约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 方式一
create table 表名（
	字段名 类型 primary key，
	字段名 类型 unique，
	字段名 类型 unique key，
	……
);
# 方式二
create table 表名（
	字段名 类型 primary key，
	字段名 类型，
	……
[constraint 约束名] unique key(字段名)
);

建表时添加复合唯一约束

1
2
3
4
5
6
create table 表名（
	字段名 类型 primary key，
	字段名 类型，
	……
[constraint 约束名] unique key(字段名,字段名…)
);

建表后添加唯一约束

1
2
3
4
# 方式一
alter table 表名称 modify 字段名 字段类型 unique;
# 方式二：
alter table 表名称 add [constraint 约束名] unique key(字段列表);

删除唯一约束

1
alter table 表名 drop index 索引名;

主键约束（primary key）

建表时添加主键约束

1
2
3
4
5
create table 表名（
	字段名 类型 primary key，
	字段名 类型，
	……
);

建表时添加复合主键

1
2
3
4
5
6
create table 表名（
	字段名 类型，
	字段名 类型，
	……
primary key(字段,字段)
);

建表后添加主键约束

1
alter table 表名 add primary key(字段,字段…)

删除主键约束

1
alter table 表名称 drop primary key;

自增约束（auto_increment）

建表时添加自增约束

1
2
3
4
5
create table 表名（
	字段名 类型 primary key auto_increment，
	字段名 类型 unique key auto_increment，
……
);

建表后添加自增约束

1
alter table 表名称 modify 字段名 字段类型 auto_increment;

删除自增约束

1
alter table 表名称 modify 字段名 字段类型;

外键约束（foreign key）

建表时添加外键约束

1
2
3
4
5
6
7
8
9
10
11
12
# 最好是采用: on update Cascade on delete Restrict 的方式（级联更新，级联删除）
# 表示主表改变从表同步，主表不能删除数据与从表关联的数据，除非先删从表中的再删主表中的
create table 主表名称(
	字段名 类型 primary key,
	字段名 类型
);

create table 从表名称(
	字段名 类型 primary key,
	字段名 类型,
	[constraint  外键名称]   foreign key(从表字段) references 主表名(主表列名称)[on update xx][on delete xx]
);

建表后添加外键约束

1
alter table 从表名 add [constraint 外键名称] foreign key(从表字段) references 主表名(主表列名称)[on update xx][on delete xx];

删除外键约束

1
2
3
4
5
6
7
8
# 1、先查看约束名
select * from information_schema.table_constraints where table_name = '表名称'; 
# 2、删除外键约束
alter table 从表名 drop foreign key 外键名称;
# 3、查看索引名
show index from 表名称; 
# 4、删除索引（注意，只能手动删除）
alter table 从表名 drop index 索引名;

检查约束（check）

建表时添加检查约束

1
2
3
4
5
6
7
# MySQL5.7 可以使用check约束，添加数据时，没有任何错误或警告，但check约束对数据验证没有任何作用。 
# MySQL 8.0 使用check约束有效
create table 表名（
	字段名 类型 primary key auto_increment，
	字段名 类型 check(条件)，
	……
);

默认约束（default）

建表时添加默认约束

1
2
3
4
5
create table 表名（
	字段名 类型 primary key，
	字段名 类型 default 默认值，
	……
);

建表后添加默认约束

1
2
3
4
5
# 添加默认值约束，不保留非空约束
alter table 表名称 modify 字段名 数据类型 default 默认值;

# 添加默认值约束，保留非空约束
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;

删除默认约束

1
2
3
4
5
# 删除默认值约束，不保留非空约束
alter table 表名称 modify 字段名 数据类型;

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