SQL学习笔记(三)

发表于 更新于 分类于 Waline： 阅读次数：

SQL学习笔记(三)——数据库的完整性

关系数据库管理系统使得完整性控制成为其核心支持的功能，从而能够为所有用户和应用提供一致的数据库完整性。

实体完整性

定义实体完整性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 将Student表中Sno属性定义为码（主键）
CREATE TABLE Student
	(Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20) NOT NULL,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20));
    
-- 将SC表中Sno、Cno属性组定义为码
CREATE TABLE Student
	(Sno CHAR(9) NOT NULL,
    CnO CHAR(4) NOT NULL,
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno));

实体完整性检查和违约处理

  1. 检查主码是否唯一，如果不唯一则拒绝插入或修改
  2. 检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。

为避免全表搜索，一般会在主码上自动建立一个索引。

参照完整性

定义参照完整性

外键

1
2
3
4
5
6
7
8
-- 定义SC的参照完整性
CREATE TABLE SC
	(Sno CHAR(9) NOT NULL,
    Cno CHAR(4) NOT NULL,
    Grade SMALLINT,
    PRIMARY KEY (Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES Student(Sno),
    FOREIGN KEY(Cno) REFERENCES Course(Cno));

参照完整性检查和违约处理

1
2
3
4
5
6
7
8
9
10
11
12
-- 显示说明参照完整性的违约处理示例
CREATE TABLE SC
	(Sno CHAR(9) NOT NULL,
    Cno CHAR(4) NOT NULL,
    Grade SMALLINT,
    PRIMARY KEY (Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES Student(Sno)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
    FOREIGN KEY(Cno) REFERENCES Course(Cno))
     ON DELETE NO ACTION
     ON UPDATE CASCADE;
操作 说明
CASCADE 级联，对应删除或修改
NO ACTION 拒绝操作

用户定义的完整性

属性上的约束条件

  • NOT NULL
  • UNIQUE 列值唯一
  • CHECK指定应满足的条件
1
2
3
4
5
6
7
-- Student表Ssex只允许‘男’或‘女’
CREATE TABLE Student
	(Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20) NOT NULL,
    Ssex CHAR(2) CHECK(Ssex IN('男','女')),
    Sage SMALLINT,
    Sdept CHAR(20));

元组上的约束条件

1
2
3
4
5
6
7
8
-- 当性别为男时，其姓名不能用Ms大头
CREATE TABLE Student
	(Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20) NOT NULL,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
    CHECK(Ssex='女' OR Sname NOT LIKE 'MS.%'));

完整性约束命名子句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 建立学生登记表Student，要求学号在90000-99999之间，姓名不能取空，年龄小于30，性别只能是‘男’或‘女’
CREATE TABLE Student
	(Sno NUMBER(6)
    	CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
    Sname CHAR(20)
    	CONSTRAINT C2 NOT NULL,
    Sage NUMBER(3)
    	CONSTRAINT C3 CHECK(Sage<30),
    Ssex CHAR(2)
    	CONSTRAINT C4 CHECK (Ssex IN('男','女')),
    	CONSTRAINT Studentkey PRIMARY KEY(Sno));
    	
-- 去掉上例中对性别的限制
ALTER TABLE Student
	DROP CONSTRAINT C4;
	
-- 修改Student中的约束条件，要求学号改为900000-999999之间，年龄由小于30改为小于40
ALTER TABLE Student
	DROP CONSTRAINT C1;
ALTER TABLE Student
	ADD CONSTRAINT C1 CHECK(Sno BETWEEN 900000 AND 999999);
ALTER TABLE Student
	DROP CONSTRAINT C3;
ALTER TABLE Student
	ADD CONSTRAINT C3 CHECK(Sage<40);

域中的完整性限制

域是一组具有相同数据类型值的集合

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 建立一个性别域，并声明性别域的取值范围
CREATE DOMAIN GenderDomain CHAR(2)
	CHECK(VALUES IN ('男','女'));
	
-- 简历一个性别域，并对其中的限制命名
CREATE DOMAIN GenderDomain CHAR(2)
	CONSTRAINT GD CHECK(VALUES IN ('男','女'));
	
-- 删除于GenderDomain中的限制条件GD
ALTER DOMAIN GenderDomain
	DROP CONSTRAINT GD;
	
-- 在域上添加性别限制条件
ALTER DOMAIN GenderDomain
	ADD CONSTRAINT GD CHECK(VALUES IN('0','1'));

断言

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 限制数据库课程最多60名学生选修
CREATE ASSERTION ASSE_SC_DB_NUM
	CHECK (60>=(SELECT COUNT(*)
               FROM Course,SC
               WHERE SC.Cno=Course.Cno AND Course.Cname='数据库'));
               
-- 限制每门课程最多60名学生选修
CREATE ASSERTION ASSE_SC_CNUM1
	CHECK (60>=(SELECT COUNT(*)
               FROM SC
               GROUP BY Cno));
               
-- 删除断言
DROP ASSERTION ASSE_SC_DB_NUM;

断言导致系统在检测和维护上开销很大，应该谨慎使用。

触发器

各家差异较大

定义触发器

1
2
3
4
5
CREATE TRIGGER <触发器名>
{BEFORE|AFTER} <触发器> ON <表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH{ROW|STATEMENT}
[WHEN<触发条件>]<触发动作体>
  • 触发器只能定义在基本表上，不能定义在视图上。
  • FOR EACH ROW:行级触发器 FOR EACH STATEMENT:语句级触发器
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 当表SC的Grade属性进行修改是，若分数增加了10%，则将此次操作记录到另一个表SC_U(Sno，Cno,Oldgrade,Newgrade)中，其中Oldgrade是修改前的分数，Newgrade为修改后的分数
CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC
REFERENCING
	OLDROW AS OldTuple.
	NEWROW AD NewTuple
FOR EACH ROW
WHEN(Newtuple.Grade>=1.1*OldTuple.Grade)
	INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)
	VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade);
	
-- 将每次对表Student的插入操作所增加的学生个数记录到表Student-InsertLog中
CREATE TRIGGER Student_Count
AFTER INSERT ON Student
REFERENCING
	NEW TABLE AS DELTA
	FOR EACH STATEMENT
		INSERT INTO StudentInsertLog(Numbers)
		SELECT COUNT(*) FROM DELTA;
		
-- 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”
CREATE TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher
REFERENCING NEW row AD newTuple
FOR EACH ROW
BEGIN
	IF (newtuple.Job='教授')AND(newtuple.Sal<4000)
	THEN newTuple.Sal:=4000;
	END IF;
END;

-- 删除触发器
DROP TRIGGER <触发器> ON <表名>