MySQL学习笔记-约束

约束

全部笔记🔗
本文记录了学习MySQL的笔记，其中课件资源全部来源于尚硅谷，详细信息请移步b站

1.1 为什么需要约束(来自尚硅谷)

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

• 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
• 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
• 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
• 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

一句话说明：为了保证数据的完整性

什么是约束

对表中的字段进行了限制、比如说用户名是唯一的

约束的分类

• 约束的字段的个数:单行约束、多行约束
• **约束的作用范围：**列级约束：将约束声明在对应的字段后面、表级约束：表中字段声明完，在字段后面的约束
• 约束的作用
  • NOT NULL 非空约束，规定某个字段不能为空
  • UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
  • PRIMARY KEY 主键(非空且唯一)约束
  • FOREIGN KEY 外键约束
  • CHECK 检查约束
  • DEFAULT 默认值约束

怎么添加约束？

CREATE TABLE时添加约束

ALTER TABLE时添加约束、删除约束

怎么查看表中的约束？

SELECT * FROM information_schema.table_constraints
WHERE table_name = 'employees'

非空约束

NOT NULL 比如说我们的员工表中，部门可以是空的，因为可能还暂时没有进行部门的分配

但是名字总是不能为空的吧，因此需要加上约束

特点

• 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型

• 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空

• 一个表可以有很多列都分别限定了非空

• 空字符串’'不等于NULL，0也不等于NULL

#创建一个新的数据库
CREATE DATABASE dbtest13;
USE dbtest13
#创建一个新的表
CREATE TABLE test01(
id INT NOT NULL, 		#员工ID不能为空
last_name VARCHAR(15) NOT NULL, #员工姓名不能为空
email VARCHAR(25),
salary DECIMAL(10,2)		#十进制
)

约束的话主要是在你增删改数据的时候进行体现的

添加数据的样例：
#OK的，各项数据都是正常的
INSERT INTO test01(id, last_name, email, salary) 
VALUES (1, 'Tom', 'tom@126.com',3400)

#错误，插入的名字是空的 1048 - Column 'last_name' cannot be null, Time: 0.000000s
INSERT INTO test01(id, last_name, email, salary)
VALUES (2, NULL, 'tom@126.com', 3400)

#也是错误的，因为没有添加last_name
INSERT INTO test01(id, email)
VALUES(2, 'abc@126.com')

修改数据的样例：
#错误的， 不能够将姓名改为null
UPDATE test01
SET last_name = NULL
WHERE id = 1

同样，我们可以在ALTER TABLE的时候进行添加约、删除约束

现有数据演示：
id		last_name		email			salary							
1			Tom					(NULL)			3400.00

#错误，因为上面表中数据已经存在有null值了，你不能更改字段默认为not null
ALTER TABLE test01
MODIFY email VARCHAR(25) NOT NULL
#如果你给email赋予了一个值（那么所有的email都是非空的），此时再更改约束为not null是成功的


ALTER TABLE test01
MODIFY email VARCHAR(25) NULL

唯一性约束

用来限制某一个字段/某列的值不能够重复 UNIQUE

特点

• 同一个表可以有多个唯一约束。
• 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
• 唯一性约束允许列值为空。
• 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
• MySQL会给唯一约束的列上默认创建一个唯一索引。

1.在CREATE TABLE时候添加约束

CREATE TABLE test02(
									#列级约束
id INT UNIQUE, 		#员工的ID是唯一的，总不能多个人有同一个ID吧
last_name VARCHAR(15),
eamil VARCHAR(25) UNIQUE,
salary DECIMAL(10,2),

#在这里写就是表级约束
CONSTRAINT uni_test2_email UNIQUE(eamil)
)
DESC test02 看一下字段的一些结构啥的

能够看到我们的两个字段是unique约束

我们可以查看一下约束

#OK的，插入一个数据
INSERT into test02(id, last_name, email, salary)
VALUES (1, 'Tom', 'tom@126.com', 4500)

#错误，新的数据不能再设置ID为1
INSERT into test02(id, last_name, email, salary)
VALUES (1, 'Tom1', 'tom@126.com1', 45010)

#向声明为unique的字段上面添加null值？可以的 ，并且可以多次添加null值
insert into test02
VALUES(5,'wqd', null , 465)

2.在ALTER TABLE的时候添加约束

#用ALTER TABLE的方式添加约束,约束的别名：uni_test02_sal
ALTER table test02
ADD CONSTRAINT uni_test02_sal UNIQUE(salary)


#使用modify 来添加约束
ALTER TABLE test02
MODIFY last_name VARCHAR(15) UNIQUE 

3.复合的唯一性约束

CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`passwd` VARCHAR(25),

#表级约束
CONSTRAINT uni_user_name_pwd UNIQUE(`name`, `passwd`)
)

#OK，插入一条数据
INSERT INTO USER
VALUES (1, 'Tom', 'abc')

#插入的数据的密码是和上面相同的，但是名字不同，也是OK的
INSERT INTO USER
VALUES (1, 'Tom1', 'abc')

4.删除唯一性约束

• 添加唯一性约束的列上也会自动创建唯一索引。
• 删除唯一约束只能通过删除唯一索引的方式删除。
• 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
• 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

#使用下面的命令查看表的约束
SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'test02'

#删除掉last_name 的约束 
ALTER TABLE test02
DROP INDEX last_name 

#删除掉salary的约束

ALTER TABLE test02
DROP INDEX uni_test02_sal

PRIMARY KEY约束

• 用来唯一标识表中的一行记录，就是表中的记录需要唯一的进行区分开

• primary key

• 其实主键约束 就是相当于唯一约束 + 非空约束的组合

• 一个表中最多只能拥有一个主键约束

• MySQL的主键名总是：PRIOMARY，重命名也没没用

1 添加主键约束

#在CREATE TABLE的时候添加约束,一个表中最多只能有一个主键约束
CREATE TABLE test03(
id INT PRIMARY KEY,		#列级别约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
#如果加在这里就是表级约束
#CONSTRAINT pk_test03_id PRIMARY KEY(id)
)

#查看约束
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'test03'
#即使是使用表级约束进行了约束的命名，他的名字也是不会更改的
#一直都是primary



#插入一条数据、正确
insert into test03(id, last_name, salary, email)
VALUES (1, 'Tom', 4500, 'tom@126.com')

#插入一条ID数据为null的数据，错误，因为主键就是（非空 + 唯一性）
insert into test03(id, last_name, salary, email)
VALUES(null, 'Tom', 4500, 'tom@126.com')

复合性主键约束

CREATE TABLE user1(
id INT, 
NAME VARCHAR (15),
PASSWORD VARCHAR(25),

PRIMARY KEY(NAME, PASSWORD)		#复合型的主键约束
)
#插入一条数据
INSERT INTO user1
VALUES(1, 'Tom', 'abc')
#OK的，虽然密码一样，但是姓名是不同的
INSERT INTO user1
VALUES(1, 'Tom1', 'abc')

#错误，1048 - Column 'NAME' cannot be null, Time: 0.000000s
#多列复合的主键约束、一个null都不允许有
INSERT INTO user1
VALUES (1, null, 'abc')

2.在ALTER TABLE 的时候添加约束

CREATE TABLE test6(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10, 2),
email VARCHAR(25)
)
#查看表的结构
desc test6

#使用ALTER进行约束的添加
ALTER table test6
ADD PRIMARY KEY(id)

3.删除主键约束

ALTER TABLE test6
DROP PRIMARY key

自增列：AUTO_INCREMENT

• 作用：某个字段的值自增

• 关键字：auto_increment

• 特点 & 要求

  （1）一个表最多只能有一个自增长列

  （2）当需要产生唯一标识符或顺序值时，可设置自增长

  （3）自增长列约束的列必须是键列（主键列，唯一键列）

  （4）自增约束的列的数据类型必须是整数类型

  （5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

自增列是需要搭配主键约束的时候进行使用的，并且既然是自增列嘛

应该是像INT这种的能够自增的数据类型才能够进行使用的

像VARCHAR这种数据类型是不能够使用auto_increment的

1.应用场景：

比如说我们设置了一个int为primary key，那我们添加数据的时候如果不明确

指定这个字段的话，它就会自动给我们设置一个自增的值

CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT, 
last_name VARCHAR(15)
)
#连续按了4次插入数据
INSERT INTO test7(last_name)
VALUES ('Tom')

SELECT * FROM test7


1	Tom
2	Tom
3	Tom
4	Tom



#如果我们向主键（auto_increment）的字段上面添加0 或者是 null的时候
#其实会自动的在字段上递增
#添加的字段值是0
INSERT INTO test7(id, last_name)
VALUES(0, 'Tom')
#添加字段值是null
INSERT INTO test7(id, last_name)
VALUES(null, 'Tom')
#添加字段值是显示的一个数值
INSERT into test7(id, last_name)
VALUES(456, 'Tom')


1	Tom
2	Tom
3	Tom
4	Tom
5	Tom
6	Tom
456	Tom

---

2.添加

假设有一个表声明了主键但是没有声明auto_increment

ALTER TABLE test8 MODIFY id INT AUTO_INCREMENT

3.删除

ALTER TABLE test8 MODIFY id INT

CHECK 约束

检查某个字段的值是否是符合符号xx要求， 一般是值的范围

CHECK

CREATE TABLE test10(
id INT, 
last_name VARCHAR(15),
salary DECIMAL(10,2) CHECK (salary > 2000)#限制工资不能低于2500
)
#OK的
INSERT INTO test10
VALUES(1, 'Tom', 2500)

#3819 - Check constraint 'test10_chk_1' is violated., Time: 0.001000s
INSERT INTO  test10
VALUES(2, 'Tom1', 1500)

DEFAULT 约束

添加约束DEFAULT 数值

那么对应的字段如果没有显示的进行添加的话，自动就会填上我们设置的默认值