欢迎各位彦祖与热巴畅游本人专栏与博客
你的三连是我最大的动力
以下图片仅代表专栏特色 [点击箭头指向的专栏名即可闪现]
专栏跑道一
➡️网络空间安全——全栈前沿技术持续深入学习
专栏跑道二
➡️ 24 Network Security -LJS
专栏跑道三
➡️ MYSQL REDIS Advance operation
专栏跑道四
➡️HCIP；H3C-SE;CCIP——LJS[华为、华三、思科高级网络]
专栏跑道五
➡️RHCE-LJS[Linux高端骚操作实战篇]
专栏跑道六
➡️数据结构与算法[考研+实际工作应用+C程序设计]
专栏跑道七
➡️RHCSA-LJS[Linux初级及进阶骚技能]
上节回顾
=
1 MySQL约束
1. 约束(constraint)概述
1.1 为什么需要约束
数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。
它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。
从以下表格四个方面考虑：
| 实体完整性（Entity Integrity) | 例如，同一个表中，不能存在两条完全相同无法区分的记录 |
| 域完整性（Domain Integrity） | 例如：年龄范围0-120，性别范围“男/女” |
| 引用完整性（Referential Integrity） | 例如：员工所在部门，在部门表中要能找到这个部门 |
| 用户自定义完整性（User-defined Integrity) ： | 例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。 |
1.2 什么是约束
可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。
1.3 约束的分类
根据约束数据列的限制，约束可分为：
| 单列约束 | 每个约束只约束一列 |
| 多列约束 | 每个约束可约束多列数据 |
根据约束的作用范围，约束可分为
| 列级约束 | 只能作用在一个列上，跟在列的定义后面 |
| 表级约束 | 可以作用在多个列上，不与列一起，而是单独定义 |
位置 支持的约束类型 是否可以起约束名 列级约束： 列的后面 语法都支持，但外键没有效果 不可以 表级约束： 所有列的下面 默认和非空不支持，其他支持 可以(主键没有效果)
根据约束起的作用，约束可分为：
| NOT NULL 非空约束 | 规定某个字段不能为空 |
| UNIQUE 唯一约束 | 规定某个字段在整个表中是唯一的 |
| PRIMARY KEY 主键 | PRIMARY KEY 主键 |
| FOREIGN KEY | 外键约束 |
| CHECK | 检查约束 |
| DEFAULT | 默认值约束 |
查看某个表已有的约束
//information_schema数据库名（系统库） //table_constraints表名称（专门存储各个表的约束) SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
2. 非空约束
2.1 作用
限定某个字段/某列的值不允许为空
编辑
2.2 关键字NOT NULL
2.3 特点
| 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型 |
| 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空 |
| 一个表可以有很多列都分别限定了非空 |
| 空字符串''不等于NULL，0也不等于NULL |
2.4 添加非空约束
(1)建表时
CREATE TABLE 表名称( 字段名 数据类型, 字段名 数据类型 NOT NULL, 字段名 数据类型 NOT NULL );
举例：
CREATE TABLE emp( id INT(10) NOT NULL, NAME VARCHAR(20) NOT NULL, sex CHAR NULL ); CREATE TABLE student( sid int, sname varchar(20) not null, tel char(11) , cardid char(18) not null ); insert into student values(1,'张三','13710011002','110222198912032545'); //成功 insert into student values(2,'李四','13710011002',null); //身份证号为空 ERROR 1048 (23000): Column 'cardid' cannot be null insert into student values(2,'李四',null,'110222198912032546'); //成功，tel允许为空 insert into student values(3,null,null,'110222198912032547'); //失败 ERROR 1048 (23000): Column 'sname' cannot be null
(2)建表后
alter table 表名称 modify 字段名 数据类型 not null; 举例：
举例：
ALTER TABLE emp MODIFY sex VARCHAR(30) NOT NULL; alter table student modify sname varchar(20) not null;
2.5 删除非空约束
举例：
ALTER TABLE emp MODIFY sex VARCHAR(30) NOT NULL; alter table student modify sname varchar(20) not null;
3. 唯一性约束
3.1 作用
- 用来限制某个字段/某列的值不能重复。
3.2 关键字
- UNIQUE
3.3 特点
|
|
|
|
|
3.4 添加唯一约束
- (1)建表时
create table 表名称( 字段名 数据类型 unique, 字段名 数据类型 unique key, 字段名 数据类型 );
-
- 举例：
create table student( sid int, sname varchar(20), tel char(11) unique, cardid char(18) unique key ); CREATE TABLE t_course( cid INT UNIQUE, cname VARCHAR(100) UNIQUE, description VARCHAR(200) ); CREATE TABLE USER( id INT NOT NULL, NAME VARCHAR(25), PASSWORD VARCHAR(16), -- 使用表级约束语法 CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD) ); //表示用户名和密码组合不能重复 insert into student values(1,'张三','13710011002','101223199012015623'); insert into student values(2,'李四','13710011003','101223199012015624'); mysql> select * from student; +-----+-------+-------------+--------------------+ | sid | sname | tel | cardid | +-----+-------+-------------+--------------------+ | 1 | 张三 | 13710011002 | 101223199012015623 | | 2 | 李四 | 13710011003 | 101223199012015624 | +-----+-------+-------------+--------------------+ 2 rows in set (0.00 sec) insert into student values(3,'王五','13710011004','101223199012015624'); //身份证号重复 ERROR 1062 (23000): Duplicate entry '101223199012015624' for key 'cardid' insert into student values(3,'王五','13710011003','101223199012015625'); ERROR 1062 (23000): Duplicate entry '13710011003' for key 'tel'
-
- (2)建表后指定唯一键约束
- 字段列表中如果是一个字段，表示该列的值唯一。
- 如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
- 方式1：
alter table 表名称 add unique key(字段列表);
-
- 方式2：
alter table 表名称 modify 字段名 字段类型 unique;
-
- 举例：
ALTER TABLE USER ADD UNIQUE(NAME,PASSWORD); ALTER TABLE USER MODIFY NAME VARCHAR(20) UNIQUE; create table student( sid int primary key, sname varchar(20), tel char(11) , cardid char(18) ); alter table student add unique key(tel); alter table student add unique key(cardid);
3.5 关于复合唯一约束
create table 表名称( 字段名 数据类型, 字段名 数据类型, 字段名 数据类型, unique key(字段列表) /*字段列表中写的是多个字段名， 多个字段名用逗号分隔， 表示那么是复合唯一， 即多个字段的组合是唯一的);/*
//学生表 create table student( sid int, //学号 sname varchar(20), //姓名 tel char(11) unique key, //电话 cardid char(18) unique key //身份证号 );
//课程表 create table course( cid int, //课程编号 cname varchar(20) //课程名称 );
#选课表 create table student_course( id int, sid int, cid int, score int, unique key(sid,cid)) //复合唯一
insert into student values(1,'张三','13710011002','101223199012015623'); //成功 insert into student values(2,'李四','13710011003','101223199012015624'); //成功 insert into course values(1001,'Java'),(1002,'MySQL'); //成功
mysql> select * from student; +-----+-------+-------------+--------------------+ | sid | sname | tel | cardid | +-----+-------+-------------+--------------------+ | 1 | 张三 | 13710011002 | 101223199012015623 | | 2 | 李四 | 13710011003 | 101223199012015624 | +-----+-------+-------------+--------------------+ 2 rows in set (0.00 sec)
mysql> select * from course; +------+-------+ | cid | cname | +------+-------+ | 1001 | Java | | 1002 | MySQL | +------+-------+ 2 rows in set (0.00 sec)
insert into student_course values (1, 1, 1001, 89), (2, 1, 1002, 90), (3, 2, 1001, 88), (4, 2, 1002, 56); //成功
mysql> select * from student_course; +----+------+------+-------+ | id | sid | cid | score | +----+------+------+-------+ | 1 | 1 | 1001 | 89 | | 2 | 1 | 1002 | 90 | | 3 | 2 | 1001 | 88 | | 4 | 2 | 1002 | 56 | +----+------+------+-------+ 4 rows in set (0.00 sec) insert into student_course values (5, 1, 1001, 88); //失败 ERROR 1062 (23000): Duplicate entry '1-1001' for key 'sid' //违反sid-cid的复合唯一
3.6 删除唯一约束
- 删除唯一约束
|
|
|
|
- 注意：
- 可以通过 show index from 表名称查看表的索引
//查看都有哪些约束 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; ALTER TABLE USER DROP INDEX uk_name_pwd;
