MySql（二十）常见约束和标识列

常见约束

约束含义

用于显示表中的数据，从而保证表中数据的准确性和可靠性

约束分类

not null、default、primary key、unique、check、foreign key

1. not null

含义：非空；用于保证该字段的值不能为空。比如：姓名、学号等

2. defalut

含义：默认；用于保证该字段有默认值。比如：性别

3. primary key

含义：主键；用于保证该字段的具有唯一性，并且非空。比如：学号、员工编号等

4. unique

含义：唯一；用于保证该字段的值具有唯一性，但是可以为空(只能有一个为null)。比如：座位号

5. check

含义：检查约束(mysql中不支持，即使用语法虽然不报错但是没有任何效果)；

作用：比如sex列一般定义为char类型；‘男’和’女’都可以添加；但事实上’中’或’1’也都可以添加进去，即只要是符合char类型范围内的都可以添加进去。但是要求性别只可能是’男’和’女’，这个时候就可以用检查约束：即只有满足sex='男’或sex='女’才可以将数据添加进去

f6. foreign key

含义：外键约束；用于限制两个表的关系，用于保证该字段的值必须来自于某主表的关联列的值。

（在从表中添加外键约束，用于引入主表中某列的值）

比如：员工表的部门编号、学生表的专业编号、员工表的工种编号…

约束的添加分类

1. 列级约束:六大约束语法上都支持，但是“外键约束”没有效果
2. 表级约束:除了not null 和 default其他都支持

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

添加约束的时机

1. 创建表时
2. 修改表时

①：创建表时添加列级约束

列级约束:六大约束语法上都支持，但是”外键约束“没有效果

语法：

直接在字段名和字段类型的后面添加：约束类型

列级约束相当于只支持：默认、非空、唯一、主键

表student

CREATE TABLE student(
	id INT PRIMARY KEY, /**主键**/
    name VARCHAR(4) NOT NULL, /**非空**/
    gender CHAR(1) CHECK(gender='男'OR gender='女'), /**检查**/
    age INT DEFAULT 20, /**默认**/
    seatNumber INT UNIQUE, /**唯一**/
    majorid INT FOREIGN KEY REFERENCES major(id) /**外键**/
);

表major

CREATE TABLE major(
	id INT PRIMARY KEY, /**主键**/
    majorName VARCHAR(10)
);

desc 表名; //查看表结构

show index from 表名; //查看表中所有索引

②：创建表时添加表级约束

表级约束:除了not null 和 default其他都支持

语法：

在各个字段的下面添加：

constraint 约束名 约束类型(字段名);或者

约束类型(字段名); // 此时约束名默认为对应字段的名字

表student

CREATE TABLE student(
	id INT, 
    name VARCHAR(4) NOT NULL,
    gender CHAR(1), 
    age INT DEFAULT 20, 
    seatNumber INT, 
    majorid INT,
    
    CONSTRAINT pk PRIMARY KEY(id), /**主键**/
    CONSTRAINT uq UNIQUE(seat), /**唯一**/
    CONSTRAINT ck CHECK(gender='男'OR gender='女'), /**检查**/
    CONSTRAINT fk_student_major FOREIGN KEY (majorid) REFERENCES major(id) /**外键**/
);

③：修改表时添加约束

表student

CREATE TABLE student(
	id INT, 
    name VARCHAR(4), 
    gender CHAR(1), 
    age INT, 
    seatNumber INT, 
    majorid INT 
);

1. 列级约束:六大约束语法上都支持，但是“外键约束”没有效果
2. 表级约束:除了not null 和 default其他都支持

/**添加非空约束**/
ALTER TABLE student MODIFY COLUMN name VARCHAR(4) IS NOT NULL;
/**删除非空约束**/
ALTER TABLE student MODIFY COLUMN name VARCHAR(4) IS NULL;
Alter TABLE student MODIFY COLUMN name  VARCHAR(4);
/**添加默认约束**/
ALTER TABLE student MODIFY COLUMN age INT DEFAULT 18；
/**添加主键约束(列级约束)**/
ALTER TABLE student MODIFY COLUMN id INT PRIMARY KEY；
/**添加主键约束(表级约束)**/
ALTER TABLE student ADD PRIMARY KEY(id)；
/**添加唯一键约束(列级约束)**/
ALTER TABLE student MODIFY COLUMN seat INT UNIQUE；
/**添加唯一键约束(表级约束)**/
ALTER TABLE student ADD UNIQUE(seat)；
ALTER TABLE student ADD CONSTRAINT seat1 UNIQUE(seat)；
/**添加外键约束(由于列级对外键反映，这里指写表级约束)**/
ALTER TABLE student ADD FOREIGN KEY(majorid) REFERENCES major(id);
ALTER TABLE student ADD CONSTRAINT fk_student_major FOREIGN KEY(majorid) REFERENCES major(id);

修改表时添加约束语法总结：

1. 修改表时添加列级约束：

alter table 表名 modify column 字段名 字段类型 新约束类型;

2. 修改表时添加表级约束：

   alter table 表名 add contraint 约束名 新约束类型(字段名);

   alter table 表名 新约束类型(字段名);

④：修改表时删除约束

/**删除非空约束(列的默认约束为null)**/
ALTER TABLE student MODIFY COLUMN name VARCHAR(4);
/**删除默认约束**/
ALTER TABLE student MODIFY COLUMN age INT;
/**删除主键约束**/
ALTER TABLE student MODIFY COLUMN id INT;
ALTER TABLE student DROP PRIMARY KEY;
/**删除唯一键约束**/
ALTER TABLE student MODIFY COLUMN seat INT;
ALTER TABLE student DROP INDEX seat;
/**删除外键约束**/
ALTER TABLE student DROP FOREIGN KEY fk_student_major;

表major

CREATE TABLE major(
	id INT PRIMARY KEY, /**主键**/
    majorName VARCHAR(10)
);

1. 'primary key’和’unique’的区别

	保证唯一性	是否允许为空	一个表是允许有多个	是否允许组合
primariy key	✔	✘	✘	✔（不推荐使用）
unique	✔	✔（只允许一个为空）	✔	✔（不推荐使用）

2. 外键特点：

• 要求在从表设置外键关系
• 从表的外键列的类型和主表的关联列的类型要求一致或兼容。（名称无要求）
• 主表的关联列必须是一个key(一般是主键或唯一)
• 插入数据时：先插入主表，再插入从表；删除数据时：先删除从表，再删除主表

列级约束和表级约束的区别：

	位置	支持的约束类型	是否可以器约束名
列级约束	列的后面	语法上都支持，但是外键约束没有效果	不可以
表级约束	所有列的下面	“默认”和“非空”不支持，其他都支持	可以（主键没有效果）

标识列

定义：标识列又称为自增长列

含义：可以不用手动的插入数值，系统提供默认的序列值

1. 创建表时设置标识列

/**创建表并添加标识列**/
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(4)
);
/**向有标识列的表中天健数据**/
INSERT INTO student(id,name) 
VALUES
(null,'Tom'),
(null,'Join');
/**方法2**/
INSERT INTO student(name)
VALUES
('Tom'),
('Join');

//查看表中有关于标识列的变量

show variables like ‘%auto_increment%’;

1. 有关于标识列的变量1：auto_increment_increment 默认值为：1 即默认“步长”
2. 有关于标识列的变量2：auto_increment_offset 默认值为：1 即"起始值

因此我们可以通过设置标识列的变量来设置它的步长和起始值

（mysql中不支持设置“起始值”；但是支持设置"步长"）

①：设置步长：

​ set auto_increment_increment = 3;

②：设置“起始值”

/**1. 手动设置起始值**/

    insert into student (id,name) values (10,'Tom');

/** 2. 其他列都设置为null,这样就达到设置起始值为10的目的了**/

    insert into student (id,name) values (null,'Join');

1. 标识列必须和“主键”进行搭配使用吗？

答案：不一定；但是标识列要求必须和一个key进行搭配使用（key:主键、唯一、外键…）

2. 一个表中可以有多个标识列吗？

答案：至多一个

3. 标识列的类型必须是数值型吗？

答案：是的

2. 修改表时设置标识列

ALTER TABLE student MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

3 修改表时删除标识列

ALTER TABLE student MODIFY COLUMN id INT;