MySQL06-数据控制&约束

1. 数据控制-DCL

数据控制类 SQL 开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用。

1.1 用户管理

1. 查询用户，MySQL服务器的用户信息存储在 mysql 数据库中的 user 表中，可以通过查询该表查询 mysql 服务器的用户信息

# 进入 mysql 数据库
use mysql;
# 查询 user 表
select * from user;

2. 创建用户，其中用户名和用户密码可以自定义，主机名为 localhost 时表示本地主机，该用户只可以从本地主机访问数据库服务器，主机名为 % 时表示任意主机，该用户可以从任意主机访问数据库服务器；

# @ 符号前后不能有空格，否则创建失败
create user '用户名'@'主机名' identified by '用户密码';

# 创建用户test，密码为1234，只能在当前主机localhost访问
create user 'test'@'localhost' identified by '1234';
# 创建用户test，密码为1234，能在任意主机访问
create user 'test'@'%' identified by '123456';

3. 修改用户密码

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

4. 删除用户

drop user '用户名'@'主机名';

1.2 权限控制

1. 查询权限

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

2. 授予权限

grant 权限1,权限2... on 数据库名.表名 to '用户名'@'主机名';

# 授予用户 test 在 student 数据库 user 表的插入和修改权限
grant insert,update on student.user to 'test'@'localhost';
# 授予用户 test 在 student 数据库所有表的所有权限
grant all on student.* to 'test'@'localhost';

3. 撤销权限

revoke 权限1,权限2... on 数据库名.表名 from '用户名'@'主机名';

# 撤销用户 test 在 student 数据库 user 表的插入和修改权限
revoke insert,update on student.user from 'test'@'localhost';

其中，MySQL中常用的权限有以下这些，更多权限可以参考官方文档：

权限	说明
ALL，ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

注意：

1. 授予权限时，多个权限之间用逗号分隔；
2. 授予权限时，数据库名和表名可以用 * 通配表示，代表所有，如授予用户 test 在所有数据库所有表的插入和修改权限：grant insert,update on *.* to 'test'@'localhost';；

2. 约束

约束是为防止错误的数据被插入到数据表，作用于表中字段上的规则，用于限制存储在表中的数据，也即表的约束实际上就是表中数据的限制条件。约束可以保证数据表中数据的正确性、唯一性、有效性和完整性。约束是作用于表中字段上的，可以在创建表或修改表的时候添加约束。常见约束如下：

约束	关键字	描述
非空约束	NOT NULL	限制该字段的数据不能为null
唯一约束	UNIQUE	保证该字段的所有数据都是唯一、不重复的
主键约束	PRIMARY KEY	主键是一行数据的唯一标识，要求非空且唯一
默认约束	DEFAULT	保存数据时，如果未指定该字段的值，则采用默认值
检查约束（8.0.1版本后支持）	CHECK	保证字段值满足某一个条件
外键约束	FOREIGN KEY	用来让两张图的数据之间建立连接，保证数据的一致性和完整性

2.1 列级约束和表级约束

MySQL中的约束分为为列级约束和表级约束：
列级约束有：非空、唯一、主键、默认、检查、外键（外键约束在语法上支持，但没有效果）
表级约束有：唯一、主键、检查、外键

区别：

1. 列级约束是字段级约束，只能应用于一列上，表级约束是字段间约束，可以应用于一列上，也可以应用在一个表中的多个列上；
2. 表级约束可以给约束起名，以主键约束为例，格式为以 constraint 约束名 primary key (字段1, ...) ，添加约束名可以方便以后通过这个名字来删除这个约束；
3. 列级约束和表级约束在添加位置上也有所不同，列级约束直接在 字段名 数据类型 后面追加约束，用空格分隔，表级约束则与字段定义用 , 分隔，在字段定义的最下面，大致位置如下所示；

CREATE TABLE 表名 (
字段1 字段类型 列级约束,
字段2 字段类型 列级约束,
表级约束(字段1, 字段2)
);

2.2 主键约束

主键约束（PRIMARY KEY,PK）是指具有唯一标识表中每一行的值的一列或一组列的主键。被标识为主键的数据具有唯一性、非空性和最小化原则，用于强制实现表的实体完整性，类似于身份证号。

使用主键的时需要注意以下几点：

1. 一个表只能定义一个主键约束（约束只能有一个，但可以作用到好几个字段)；
2. 给某个字段添加主键约束之后，该字段不能重复也不能为空，效果和 not null unique 约束相同，但是本质不同；
3. 主键约束会默认添加索引(index)；

主键也分为单字段主键和多字段联合主键，具体要求如下：

1. 一个字段名只能在联合主键字段表中出现一次；
2. 联合主键不能包含不必要的多余字段，以满足最小化原则；

添加列级主键约束

# 创建表时添加约束
-- 列级主键约束
字段名 数据类型 primary key;
-- 表级主键约束
create table 表名 (
字段名 字段类型,
字段名 字段类型,
...
primary key (字段名,字段名,...)
);

# 创建表后添加约束
-- 方法一
alter table 表名 modify column 字段名 数据类型 primary key;
-- 方法二
alter table 表名 add primary key(字段名);

# 创建表时添加约束
-- 方法一，列级主键
create table student (
id int primary key,
name varchar(20)
);
-- 方法二，表级主键
create table student (
id int,
name varchar(20),
primary key(id)
);
-- 添加表级主键时可设置多字段联合主键，字段间用逗号分隔
create table student (
id int,
name varchar(20),
primary key (id, name)
);

# 创建表后添加约束
-- 方法一
alter table student modify column id int primary key;
-- 方法二
alter table student add primary key(id);

删除主键约束

alter table 表名 drop primary key;
示例：
alter table student drop primary key;

2.2.1 主键自增长

当主键定义为自增长后，主键的值就不需要自己再输入数据了，而是由数据库系统根据定义自动赋值，每增加一条记录，主键就会自动根据设置的步长进行增长。在MySQL中，自增长的关键字是 AUTO_INCREMENT，语法格式为：

字段名 数据类型 auto_increment;

# 例如：
create table student (
id int primary key auto_increment,
name varchar(20)
);

# 还可以设置自增起始数据：
create table student (
id int primary key auto_increment,
name varchar(20)
) auto_increment=0001;

2.3 非空约束

非空约束（NOT NULL）是指强制字段的值不能为空。

添加非空约束：

# 创建表时添加约束
字段名 数据类型 not null;

# 创建表后添加约束
alter table 表名 modify column 字段名 数据类型 not null;

示例：
# 创建表时添加约束
create table student (
id int
name varchar(20) not null
);

# 创建表后添加约束
alter table student modify column name varchar(20) not null;

删除非空约束：

alter table 表名 modify 字段名 数据类型 null;
示例：
alter table student modify name varchar(20) null;

2.4 唯一约束

唯一约束（UNIQUE）用于保证数据表中字段的值具有唯一性，即表中字段的值不能重复出现，允许出现空值，但空值只能出现一次。

添加唯一约束：

# 创建表时添加约束
字段名 数据类型 unique;

# 创建表后添加约束
-- 方法一
alter table 表名 modify column 字段名 数据类型 unique;
-- 方法二
alter table 表名 add unique(字段名);

# 创建表时添加约束
-- 列级唯一约束
create table student (
id int,
name varchar(20) unique
);
-- 表级唯一约束，当在表级唯一约束中设置两个字段名时，新纪录中的两个字段与这两个字段原有数据都相同则报错，只有一个相同不报错
create table student (
id int,
name varchar(20),
unique(id,name)
);

# 创建表后添加约束
-- 方法一
alter table student modify column name varchar(20) unique;
-- 方法二
alter table student add unique(name);

删除唯一约束：

alter table 表名 drop index 字段名;
示例：
alter table student drop index number;

2.5 默认约束

默认值约束（DEFAULT）用于指定数据表中字段的默认值，当向表中插入一条新的记录时没有给该字段赋值，那么数据库系统会自动为这个字段插人默认值，默认约束常常用在某字段中数据存在大量重复值的情况。

添加默认约束：

# 创建表时添加约束
字段名 数据类型 default 默认值;

# 创建表后添加约束
alter table 表名 modify column 字段名 数据类型 default 默认值;

示例：
# 创建表时添加约束
create table student (
id int,
name varchar(20),
gender varchar(10) default 'male'
);

# 创建表后添加约束
alter table student modify column gender varchar(10) default 'male';

删除默认约束：

# 方法一
alter table 表名 modify 字段名 数据类型;
示例：
alter table student modify gender varchar(10);

# 方法二
alter table 表名 alter column 字段名 drop default;
示例：
alter table student alter column gender drop default;

2.6 检查约束

检查约束（CHECK）通过在 check 后面添加限定条件表达式实现对插入数据的检查，在更新表数据的时候，系统会检查更新后的数据行是否满足 CHECK 约束中的限定条件，可以分别对列或表实施 CHECK 约束。

添加检查约束：

# 创建表时添加约束
字段名 数据类型 check(限定条件表达式);

# 创建表后添加约束
-- 方法一
alter table 表名 modify column 字段名 数据类型 check(限定条件表达式);
-- 方法二
alter table 表名 add check(限定条件表达式);

示例：
# 创建表时添加约束
-- 方法一
create table student (
name varchar(20),
age varchar(20) check(age > 0 and age < 120)
);
-- 方法二，表级检查约束，可同时对表中多个字段进行限制，限制条件间用 and 连接
create table student (
id int,
name varchar(20),
age varchar(20)
check(id > 0 and age > 0 and age < 120 )
);

# 创建表后添加约束
-- 方法一
alter table student modify column age varchar(20) check(age > 0 and age < 120);
-- 方法二
alter table student add check(age > 0 and age < 120);

删除检查约束：

alter table 表名 drop check 检查约束名;
示例：
alter table student drop check student_chk_1;

2.7 外键约束

外键约束（FOREIGN KEY）用来在两个表的数据之间建立关联，可以是一列或者多列，一个表可以有一个或多个外键。

添加外键约束：

# 创建表时添加约束
constraint 外键名 foreign key (从表外键字段) references 主表 (主键字段)

# 创建表后添加约束
alter table 从表名 add constraint 外键名 foreign key (从表外键字段) references 主表 (主键字段);

示例：
-- 创建主表student，从表class，使class表的 studentid 字段作为外键关联 student 表的id字段

# 创建表时添加约束
create table class (
classid int primary key,
studentid int
constraint fk_class_studentid foreign key (studentid) references student(id)
);

# 创建表后添加约束
alter table class add constraint fk_class_studentid foreign key (studentid) references student(id);

删除外键约束：

alter table 从表名 drop foreign key 外键名;
示例：
alter table class drop foreign key fk_class_studentid;

2.7.1 外键删除/更新行为

建立外键是为了保证数据的完整性和一致性，如果主表中的数据被删除或修改，从表中对应的数据也会发生相应变化，这些变化就由外键删除/更新行为控制，可以在创建外键时定义其删除/更新行为，如果未特定定义，默认值为 RESTRICT，其具体内容如下：

行为	说明
NO ACTION / RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有也删除/更新外键在子表中的记录
SET NULL	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（要求该外键允许为null）
SET DEFAULT	父表有变更时，子表将外键设为一个默认值（Innodb不支持）

添加外键删除/更新行为：

alter table 从表名 add constraint 外键名 foreign key (从表外键字段) references 主表 (主键字段) on update 更新行为 on delete 删除行为;
例如：
alter table class add constraint fk_class_studentid foreign key (studentid) references student(id) on update cascade on delete set null;

2.7.2 外键约束注意事项

1. 从表里的外键通常是主表的主键；
2. 从表里外键的数据类型必须与主表中主键的数据类型一致；
3. 主表发生变化时应注意主表与从表的数据一致性问题；