05 MySQL基础（五）
1 约束
1.1 概述
- 为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制。
- 约束是表级的强制规定。
- 可以在创建表的时候添加约束，或者在表创建之后数据添加之前添加约束。
1.2 约束类型
有如下的六大约束：
- NOT NULL，非空约束，规定某个字段不能为空。
- UNIQUE，唯一约束，规定某个字段在整个表中是唯一的。
- PRIMARY KEY，主键，非空且唯一。
- CHECK，检查约束。
- DEFAULT，默认值。
- FOREIGN KEY，外键。
需要注意的是，MySQL不支持check约束，但是可以使用check约束，没有任何效果。
1.3 分类
- 根据约束数据列的限制，约束可以分为：
- 单列约束：每个约束只能约束一列。
- 多列约束：每个约束可以约束多列。
CREATE TABLE 表名(
列名 字段类型 单列约束,
列名 字段类型 单列约束,
多列约束(列名1,列名2)
);
- 根据约束的作用范围，约束可以分为：
- 列级约束：只能作用在一个列上，并且是根据列的定义后面。
- 表级约束：可以作用在多个列上，不和列在一起，而是单独定义。
CREATE TABLE 表名(
列名 字段类型 列级约束,
列名 字段类型 列级约束,
表级约束(列名1,列名2)
);
列级约束，六大约束都支持，但是外键约束没有效果。
表级约束，除了非空、默认约束，其他约束都支持。
1.4 创建表的时候添加列级约束
- 创建学生表：
CREATE TABLE stu_info (
id INT PRIMARY KEY,# 主键
stu_name VARCHAR ( 255 ) NOT NULL,# 非空
gender CHAR ( 1 ) CHECK (
gender IN ( '男', '女' )),# 检查
seat INT UNIQUE,# 唯一
age INT DEFAULT 18 # 默认约束
);
1.5 创建表的时候添加表级约束
- 创建课程表和学生表：
CREATE TABLE major (
id INT PRIMARY KEY,
`name` VARCHAR ( 255 )
);
CREATE TABLE stu_info (
id INT ,
stu_name VARCHAR ( 255 ) not null ,
gender CHAR ( 1 ) ,
seat INT ,
age INT DEFAULT 18,
major_id int,
CONSTRAINT pk_id PRIMARY KEY (id),
CONSTRAINT uk_seat UNIQUE (seat) ,
CONSTRAINT fk_marjor_id FOREIGN KEY (major_id) REFERENCES major(id)
);
1.6 主键和唯一的区别
| 保证唯一性 | 是否允许为空 | 一个表中可以有多少个 | 是否允许组合 | |
|---|---|---|---|---|
| 主键 | √ | × | 最多有1个 | √，但是不推荐 |
| 唯一 | √ | √ | 可以有多个 | √，但是不推荐 |
1.7 外键的特点
- 要求在从表设置外键。
- 要求从表的外键列的类型要和主表的关联列的类型要求一致或兼容，但是名称不要求一致。
- 主表的关联列必须是一个key（一般是主键或唯一键）。
1.8 修改表时添加约束
- 语法：
# 添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名 字段类型 新约束;
# 添加表级约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 (字段名) [外键的引用];
- 示例：
CREATE TABLE major (
id INT PRIMARY KEY,
`name` VARCHAR ( 255 )
);
CREATE TABLE stu_info (
id INT ,
stu_name VARCHAR ( 255 ) NOT NULL,# 非空
gender CHAR ( 1 ) ,
seat INT UNIQUE,# 唯一
age INT ,
major_id int
);
# 修改表时添加约束
ALTER TABLE stu_info MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE stu_info MODIFY COLUMN age INT DEFAULT 18;
ALTER TABLE stu_info ADD CONSTRAINT fk_major_id FOREIGN KEY (major_id) REFERENCES major(id);
1.9 修改表时删除约束
CREATE TABLE major (
id INT PRIMARY KEY,
`name` VARCHAR ( 255 )
);
CREATE TABLE stu_info (
id INT PRIMARY KEY,
stu_name VARCHAR ( 255 ) NOT NULL,# 非空
gender CHAR ( 1 ) ,
seat INT UNIQUE,# 唯一
age INT DEFAULT 18,
major_id int,
CONSTRAINT fk_marjor_id FOREIGN KEY (major_id) REFERENCES major(id)
);
# 修改表时删除非空约束
ALTER TABLE stu_info MODIFY COLUMN stu_name VARCHAR(255) ;
# 修改表时删除默认约束
ALTER TABLE stu_info MODIFY COLUMN age INT;
# 修改表时删除主键
ALTER TABLE stu_info DROP PRIMARY KEY;
# 修改表时删除主键
# 查询唯一键 show index from stu_info
ALTER TABLE stu_info DROP INDEX seat;
# 修改表时删除外键
ALTER TABLE stu_info DROP FOREIGN KEY fk_marjor_id;
2 标识列
2.1 概念
- 又称为自增长列，可以不用手动插入值，系统提供默认的序列值。
2.2 应用示例
- 创建表的时候设置标识列：
# 删除表
DROP TABLE IF EXISTS student;
# 创建表的时候设置标识列
CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
`name` VARCHAR ( 255 )
);
# 新增数据
INSERT INTO student(`name`) VALUES ('xxx');
- 修改表时设置标识列：
# 删除表
DROP TABLE IF EXISTS student;
# 创建表的时候设置标识列
CREATE TABLE student (
id INT PRIMARY KEY ,
`name` VARCHAR ( 255 )
);
# 修改表的时候设置标识列
ALTER TABLE student MODIFY COLUMN id PRIMARY KEY auto_increment;
# 新增数据
INSERT INTO student(`name`) VALUES ('xxx');
- 修改表时删除标识列：
# 删除表
DROP TABLE IF EXISTS student;
# 创建表的时候设置标识列
CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
`name` VARCHAR ( 255 )
);
# 修改表时删除标识列
ALTER TABLE student MODIFY COLUMN id PRIMARY KEY;
2.3 特点
-
标识列必须不一定和主键搭配，但是要求是一个key。
-
一个表中至多有一个标识列。
-
标识列的类型只能是数值型。
-
标识列可以通过
set auto_increment_increment =3;设置步长，也可以通过手动插入值的方式设置标识列的起始值INSERT INTO student(id,name) VALUES (5,'xxx')。
