MySql（二十）常见约束和标识列
文章目录
常见约束约束含义约束分类约束的添加分类添加约束的时机标识列
常见约束
约束含义
用于显示表中的数据，从而保证表中数据的准确性和可靠性
约束分类
not null、default、primary key、unique、check、foreign key
- not null
含义：非空；用于保证该字段的值不能为空。比如：姓名、学号等
- defalut
含义：默认；用于保证该字段有默认值。比如：性别
- primary key
含义：主键；用于保证该字段的具有唯一性，并且非空。比如：学号、员工编号等
- unique
含义：唯一；用于保证该字段的值具有唯一性，但是可以为空(只能有一个为null)。比如：座位号
- check
含义：检查约束(mysql中不支持，即使用语法虽然不报错但是没有任何效果)；
作用：比如sex列一般定义为char类型；‘男’和’女’都可以添加；但事实上’中’或’1’也都可以添加进去，即只要是符合char类型范围内的都可以添加进去。但是要求性别只可能是’男’和’女’，这个时候就可以用检查约束：即只有满足sex=’男’或sex=’女’才可以将数据添加进去
- foreign key
含义：外键约束；用于限制两个表的关系，用于保证该字段的值必须来自于某主表的关联列的值。
（在从表中添加外键约束，用于引入主表中某列的值）
比如：员工表的部门编号、学生表的专业编号、员工表的工种编号…
约束的添加分类
- 列级约束:六大约束语法上都支持，但是“外键约束”没有效果
表级约束:除了not null 和 default其他都支持
CREATE TABLE 表名(
字段名1 字段类型 列级约束,...字段名n 字段类型 列级约束,表级约束
);
添加约束的时机
- 创建表时
- 修改表时
①：创建表时添加列级约束
列级约束:六大约束语法上都支持，但是”外键约束“没有效果
语法：
直接在字段名和字段类型的后面添加：约束类型
列级约束相当于只支持：默认、非空、唯一、主键
表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);
- 列级约束:六大约束语法上都支持，但是“外键约束”没有效果
表级约束:除了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));
- ‘primary key’和’unique’的区别
| 保证唯一性 | 是否允许为空 | 一个表是允许有多个 | 是否允许组合 | |
|---|---|---|---|---|
| primariy key | ✔ | ✘ | ✘ | ✔（不推荐使用） |
| unique | ✔ | ✔（只允许一个为空） | ✔ | ✔（不推荐使用） |
外键特点：
- 要求在从表设置外键关系
- 从表的外键列的类型和主表的关联列的类型要求一致或兼容。（名称无要求）
- 主表的关联列必须是一个key(一般是主键或唯一)
- 插入数据时：先插入主表，再插入从表；删除数据时：先删除从表，再删除主表
列级约束和表级约束的区别：
| 位置 | 支持的约束类型 | 是否可以器约束名 | |
|---|---|---|---|
| 列级约束 | 列的后面 | 语法上都支持，但是外键约束没有效果 | 不可以 |
| 表级约束 | 所有列的下面 | “默认”和“非空”不支持，其他都支持 | 可以（主键没有效果） |
标识列
定义：标识列又称为自增长列
含义：可以不用手动的插入数值，系统提供默认的序列值
创建表时设置标识列
/创建表并添加标识列/
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：auto_increment_increment 默认值为：1 即默认“步长”
- 有关于标识列的变量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');
- 标识列必须和“主键”进行搭配使用吗？
答案：不一定；但是标识列要求必须和一个key进行搭配使用（key:主键、唯一、外键…）
- 一个表中可以有多个标识列吗？
答案：至多一个
- 标识列的类型必须是数值型吗？
答案：是的
修改表时设置标识列
ALTER TABLE student MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
3 修改表时删除标识列
ALTER TABLE student MODIFY COLUMN id INT;
本文原文地址：https://blog.csdn.net/qq_43952245/article/details/90612305
我就是我
系统管理员
ゝ一世哀愁。
分手后的思念是犯贱
忘是亡心i
还没有评论，来说两句吧...