#第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
);
第13章_约束
最新推荐文章于 2024-08-29 11:53:52 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)