《数据库原理及应用》课后实验4_数据完整性

数据库MySQL

发布日期:2020-05-18 15:07:12

阅读量:137

所属分类: 数据库设计

前言：这个系列的的文章主要是关于 **《数据库原理及应用》(MySQL版)-清华大学出版社** 的课后实验报告的总结，相当于作为**数据库系统**课程的期末复习吧！

## 实验4_数据完整性

> 实验需要掌握知识点

- 使用 CREATE TABLE 定义约束的方法
- 使用 ALTER TABLE 增加或删除约束的方法
- 了解约束的各种类型
- CREATE TRIGGER 创建触发器的方法
- 引发触发器的方法
- 使用 DROP TRIGGER 删除触发器的方法

> 实验中要求创建的 3 张表分别如下图所示

![表1](http://image.linkaiblog.top/数据库原理_2020051815591059SS.png)

----
![](http://image.linkaiblog.top/数据库原理_2020051815501150SS.png)

-----
![](http://image.linkaiblog.top/数据库原理_2020051815141214SS.png)

> 具体 SQL 代码

```
-- 1. 创建 stu 表
create table stu(
	s_number char(4) not null unique primary key,
	s_name char(8),
	s_gender char(2),
	s_birthday	date
);

-- 2. 		注意：在 MySQL 下 CHECK 约束可以设置但是不起作用
create table sc (
	s_number char(4) not null,
	c_number char(4) not null,
	grade decimal(5,2) check (grade >= 0 and grade <= 100),
-- 	外键定义写在后面
	primary key (s_number, c_number)
	constraint fk_sno foreign key (s_number) references stu(s_number),
);

-- 3. 创建 course 表
create table course (
	c_number char(4) not null,
	c_name char(20),
	c_score int,
	constraint uq_cname unique(c_name)
);

-- 4. 在course表的课号列建立主键约束
alter table course add primary key(c_number);

-- 5.在sc表的课号列建立外键约束fk_cno，参照course表的课号列的取值，要求实现级联更新。
ALTER TABLE sc
	ADD CONSTRAINT fk_cno FOREIGN KEY (c_number) REFERENCES course (c_number) ON DELETE CASCADE ON UPDATE CASCADE;

-- 6. 在stu表的姓名列建立唯一约束名uq_sname。
alter table stu add constraint uq_sname unique(s_name);

-- 7. 在course表的学分列建立检查约束ck_xf，检查条件为学分>0。  （在 MySQL 中检查约束不会生效）
alter table course add constraint ck_xf check(grade > 0);

-- 8. 删除sc表的外键约束fk_cno，fk_sno。
alter table sc drop foreign key fk_sno;
alter table sc drop foreign key fk_cno;

-- 9. 删除stu表的主键约束。
alter table stu drop primary key;

-- 10.删除course表的唯一约束uq_cname。	（注意：删除唯一性约束使用的是 drop index 有一点不一样的地方）
alter table course drop index uq_cname;

-- 11.创建测试表test，包含一个字段date_time，字段类型varchar(50)；
-- 创建触发器test_trig，实现在stu表中每插入一条学生记录后，则自动在test表中追加一条插入成功时的日期时间。SYSDATE()函数用来获取当前的日期和时间。
-- 为stu表插入一条记录引发触发器，查看test表的内容。
create table test(
	date_time varchar(50)
);

CREATE TRIGGER test_trig
	AFTER INSERT
	ON stu
	FOR EACH ROW
INSERT INTO test
VALUES (SYSDATE());

insert into stu values('666', 'JackLin', 'M', '2020-04-09');

-- 12.在course表上创建触发器del_trig，当course表上删除一门课程时，级联删除sc表该课程的记录。
-- 删除course表的一条记录，查看sc表相应记录是否被自动删除。

CREATE TRIGGER del_trig
	AFTER DELETE
	ON course
	FOR EACH ROW
DELETE FROM sc
WHERE course.c_number = sc.c_number;

delete from course where c_number = '111';

SELECT * FROM sc ;
```

JackLin的博客

《数据库原理及应用》课后实验4_数据完整性