第13章_约束

#第13章_约束
/*
1.1为什么需要约束？ 为了保证数据的完整性！

1.2什么叫约束？ 对表中字段的限制！

1.3约束的分类：
角度1：约束的字段的个数
单列约束 vs 多列约束

角度2：约束的作用范围
列级约束：将此约束声明在对应字段的后面
表级约束：表中所有字段都声明完之后，在所有字段后面声明的约束

角度3：约束的作用（或功能）
① not null(非空约束)
② unique(唯一性约束)
③ primary key(主键约束)
④ foreign key(外键约束)
⑤ check(检查约束) 
⑥ default(默认值约束)

1.4如何添加约束？
》CREAT TABLE 时添加约束
》ALTER TABLE 时增加约束、删除约束

*/

#2. 如何查看表中的约束？
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'employees';

#3.NOT NULL 非空约束
#3.1 在CREATE TABLE时添加约束
CREATE DATABASE dbtest13;
USE dbtest13;

CREATE TABLE test1(
id INT NOT NULL,
`name` VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)
);

DESC test1;

INSERT INTO test1(id,`name`,email,salary)
VALUES(1,'Tom','tom@126.com',3400);

SELECT * FROM test1;

INSERT INTO test1(id,`name`,email,salary)
VALUES(1,'Jerry','tom@126.com',3400);

UPDATE test1
SET last_name = NULL
WHERE id = 1;

#3.2 ALTER TABLE 时增加约束、删除约束
SELECT * FROM test1;

ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;


#4 unique(唯一性约束)

#4.1 create时添加unique
CREATE TABLE test2(
id INT UNIQUE,#列级约束
last_name VARCHAR(15),
email VARCHAR(25) UNIQUE,
salary DECIMAL(10,2),

#表级约束
CONSTRAINT uk_test2_email UNIQUE(email)
);
DESC test2;

SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test2';

#在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Jerry','tom@126.com',3400);

INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Jerry','tom@126.com',3400);
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'Jerry','tom2@126.com',3400);
#可以向声明为unique的字段上添加null值，而且可以多次添加null值
INSERT INTO test2(id,last_name,email,salary)
VALUES(3,'Jerry',NULL,3400);
INSERT INTO test2(id,last_name,email,salary)
VALUES(4,'Jerry','tom@126.com',3400);
SELECT * FROM test2;

#4.2 alter时添加unique

DESC test2;

#方式1

UPDATE test2
SET salary = 5000
WHERE id = 2;

ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE (salary);

UPDATE test2
SET last_name = 'Tom'
WHERE id = 2;
#方式2
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

#4.3复合的唯一性约束
CREATE TABLE USER(
id INT ,
`name` VARCHAR(15),
`password` VARCHAR(25),
#表级约束
CONSTRAINT uk_user_name_password UNIQUE(`name`,`password`)
);

INSERT INTO USER
VALUES (1,'Tom','abc');

INSERT INTO USER
VALUES (1,'Tom1','abc');
SELECT * FROM USER;

#复合唯一约束的案例：
#学生表
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');#成功

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


#删除唯一性索引（约束）
DESC test2;
ALTER TABLE test2
DROP INDEX last_name;

ALTER TABLE test2
DROP INDEX uk_test2_sal;


#5.主键约束 primary key  
#一个表中只能有一个主键约束

#5.1CRRATE TABLE时添加约束

#主键约束：唯一且非空。
USE dbtest13;
CREATE TABLE test3(
id INT PRIMARY KEY, # 列级约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);

CREATE TABLE test5(
id INT ,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
CONSTRAINT pk_test5_id PRIMARY KEY(id)#表级约束
);


SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test5';
#MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。

#ALTER TABLE时添加主键约束：
USE dbtest13;
CREATE TABLE test6(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);

DESC test6;
ALTER TABLE test6
ADD PRIMARY KEY(id);

#4.3删除主键约束(在实际开发中不会删除主键约束)
ALTER TABLE test6
DROP PRIMARY KEY;

#6.自增长列：AUTO_INCREMENT
CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15)
);
INSERT INTO test7(last_name)
VALUES('Tom');

#MySQL 8.0新特性—自增变量的持久化


#7FOREIGN KEY 外键约束
 
#7.1在CREAT TABLE 时添加

#先创建父表
CREATE TABLE dept1(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);
#在创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
deparment_id  INT,
emp_name VARCHAR(15),
CONSTRAINT fk_emp1_dept_id FOREIGN KEY(deparment_id) REFERENCES dept1(dept_id)
);
DESC dept1;
DESC emp1;

#演示添加数据：


#
/*
 约束等级
Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子
表的外键列不能为not null
No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict方式 ：同no action， 都是立即检查外键约束
Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置
成一个默认的值，但Innodb不能识别
如果没有指定等级，就相当于Restrict方式。
*/

#7.5
#删除外键约束：
#一个表中可以有多个外键约束
/*
阿里开发规范
【 强制 】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学
生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于 单
机低并发 ，不适合 分布式 、 高并发集群 ；级联更新是强阻塞，存在数据库 更新风暴 的风险；外键影响
数据库的 插入速度 。
*/

#8.CHECK约束(5.7版本不支持CHECK约束，8.0则支持)
CREATE TABLE test10(
id INT ,
last_name VARCHAR(15),
salary DECIMAL (10,2) CHECK (salary > 2000)
);

#9.DEFALUT约束
CREATE TABLE test11(
id INT ,
last_name VARCHAR(15),
salary DECIMAL (10,2) DEFAULT 2000
);