数据库作业10：SQL Server的部分操作（第五章例题 数据库完整性 存储过程）
数据库作业10：SQL Server的部分操作（第五章例题 数据库完整性 存储过程）
操作目录
5.1 实体完整性
一. 定义实体完整性
二. 实体完整性检查和违约处理
5.2 参照完整性
一. 定义参照完整性
二. 参照完整性检查和违约处理
5.3 用户定义的完整性
一. 属性上的约束条件
1.属性上约束条件的定义
2.属性上约束条件的检查和违约处理
二.元组上的约束条件
1.元组上约束条件的定义
2.元组上约束条件的检查和违约处理
5.4 完整性约束命名子句
1.完整性约束命名子句
2.修改表中的完整性限制
5.6 断言
1.创建断言的语句格式
2.删除断言的语句格式
5.7 触发器
一.定义触发器
二.激活触发器
三.删除触发器
8.3 存储过程函数
一.存储过程
二.函数
5.1 实体完整性
一. 定义实体完整性
关系模型的实体完整性
CREATE TABLE中用PRIMARY KEY定义
【例5.1】将Student表中的Sno属性定义为码。
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, ---在列级定义主码
Sname CHAR(20) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
1
2
3
4
5
6
7
CREATE TABLE Student
(Sno CHAR(9),
Sname CHAR(20) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20),
PRIMARY KEY (Sno) ---在表级定义主码
);
1
2
3
4
5
6
7
8
9
【例5.2】将SC表中的Sno，Cno属性组定义为码。
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY (Sno,Cno) ---只能在表级定义主码
);
1
2
3
4
5
6
7
二. 实体完整性检查和违约处理
插入或更新操作时，DBMS按照实体完整性规则自动进行检查。
检查 主码值是否唯一 ，如果不唯一则拒绝插入或修改
检查 主码的各个属性是否为空 ，只要有一个为空就拒绝插入或修改
5.2 参照完整性
关系模型的参照完整性定义
在 CREATE TABLE 中用 FOREIGN KEY 短语定义哪些列为外码,
用 REFERENCES 短语指明这些外码参照哪些表的主码
一. 定义参照完整性
例如，关系SC中（Sno，Cno）是主码。Sno，Cno分别参照Student表
的主码和Course表的主码
【例5.3】定义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
二. 参照完整性检查和违约处理
可能破坏参照完整性的情况及违约处理：
【例5.4】显式说明参照完整性的违约处理示例。
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 ---级联删除SC表中相应的元组
ON UPDATE CASCADE, ---级联更新SC表中相应的元组
FOREIGN KEY (Cno) REFERENCES Course(Cno)
ON DELETE NO ACTION ---当删除course 表中的元组造成了与SC表不一致时拒绝删除
ON UPDATE CASCADE ---当更新course表中的cno时，级联更新SC表中相应的元组
);
1
2
3
4
5
6
7
8
9
10
11
12
5.3 用户定义的完整性
用户定义的完整性是：
针对某一具体应用的数据必须满足的语义要求 。
一. 属性上的约束条件
1.属性上约束条件的定义
CREATE TABLE时定义属性上的约束条件：
1) 列值非空（NOT NULL）
2) 列值唯一（UNIQUE）
3) 检查列值是否满足一个条件表达式（CHECK）
1） 不允许取空值
【例5.5】 在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。
CREATE TABLE SC
(Sno CHAR(9) NOT NULL, ---Sno属性不允许取空值
Cno CHAR(4) NOT NULL, ---Cno属性不允许取空值
Grade SMALLINT NOT NULL, ---Grade属性不允许取空值
PRIMARY KEY (Sno, Cno),
…
---在表级定义实体完整性，隐含了Sno，Cno不允许取空值，在列级不允许取空值的定义可以不写
);
1
2
3
4
5
6
7
8
2） 列值为一
【例5.6】 建立部门表DEPT ，要求部门名称Dname 列取值唯一，部门编号Deptno列为主码。
CREATE TABLE DEPT
(Deptno NUMERIC(2),
Dname CHAR(9) UNIQUE NOT NULL,
---要求Dname列值唯一, 并且不能取空值
Location CHAR(10),
PRIMARY KEY (Deptno)
);
1
2
3
4
5
6
7
3） 用 CHECK 短语指定列值应该满足的条件
【例5.7】Student表的Ssex只允许取“男”或“女”。
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(8) NOT NULL,
Ssex CHAR(2) CHECK (Ssex IN ('男','女')),
---性别属性Ssex只允许取'男'或'女'
Sage SMALLINT,
Sdept CHAR(20)
);
1
2
3
4
5
6
7
8
【例5.8】SC表的Grade的值应该在0和100之间。
CREATE TABLE SC
(Sno CHAR(9) ,
Cno CHAR(4),
Grade SMALLINT CHECK (Grade>=0 AND Grade <=100), /*Grade取值范围是0到100*/
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
1
2
3
4
5
6
7
8
2.属性上约束条件的检查和违约处理
属性上的约束条件检查和违约处理:
插入元组或修改属性的值时，关系数据库管理系统检查属性上的约束条件是否被满足,如果不满足则操作被拒绝执行。
二.元组上的约束条件
1.元组上约束条件的定义
在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制
【例5.9】当学生的性别是男时，其名字不能以Ms.打头。
CREATE TABLE Student
( Sno CHAR(9),
Sname CHAR(8) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20),
PRIMARY KEY (Sno),
CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')
/*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/
);
1
2
3
4
5
6
7
8
9
10
性别是女性的元组都能通过该项检查，因为Ssex=‘女’成立;
当性别是男性时，要通过检查则名字一定不能以Ms.打头
2.元组上约束条件的检查和违约处理
元组上的约束条件检查和违约处理:
插入元组或修改属性的值时，关系数据库管理系统检查元组上的约束条件是否被满足,如果不满足则操作被拒绝执行 。
5.4 完整性约束命名子句
1.完整性约束命名子句
完整性约束命名子句：
CONSTRAINT <完整性约束条件名><完整性约束条件>
1
<完整性约束条件> 包括 NOT NULL 、UNIQUE、PRIMARY KEY短语、FOREIGN KEY 短语、CHECK 短语等
【例5.10】✳ 建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。
CREATE TABLE Student
( Sno NUMERIC(6)
CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
Sname CHAR(20)
CONSTRAINT C2 NOT NULL,
Sage NUMERIC(3)
CONSTRAINT C3 CHECK (Sage < 30),
Ssex CHAR(2)
CONSTRAINT C4 CHECK (Ssex IN ( '男','女')),
CONSTRAINT StudentKey PRIMARY KEY(Sno)
);
1
2
3
4
5
6
7
8
9
10
11
操作结果：
【例5.11】建立教师表TEACHER，要求每个教师的应发工资不低于3000元。应发工资是工资列Sal与扣除项Deduct之和。
CREATE TABLE TEACHER
(Eno NUMERIC(4) PRIMARY KEY,
Ename CHAR(10),
Job CHAR(8),
Sal NUMERIC(7,2),
Deduct NUMERIC(7,2),
Deptno NUMERIC(2),
CONSTRAINT TEACHERFKey FOREIGN KEY (Deptno)
REFERENCES DEPT(Deptno),
CONSTRAINT C1 CHECK (Sal + Deduct >= 3000)
)
1
2
3
4
5
6
7
8
9
10
11
2.修改表中的完整性限制
可以使用 ALTER TABLE 语句修改表中的完整性限制。
【例5.12】
ALTER TABLE Student
DROP CONSTRAINT C4;
1
2
【例5.13】
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
5.6 断言
在SQL中，可以使用 CREATE ASSERTION 语句，通过声明性断言来指定更具一般性的约束。
断言创建以后，任何对断言中所涉及的关系的操作都会触发关系数据库管理系统对断言的检查，任何使 断言不为真值的操作都会被拒绝执行 。
注意！ ：T-SQL 中没有 ASSERTION关键字，可以使用触发器实现相似的功能。（RULE 语句不建议使用）
1.创建断言的语句格式
语句格式：
CREATE ASSERTION<断言名><CHECK 子句>
1
每个断言都被赋予一个名字，<CHECK 子句>中的约束条件与WHERE子句的条件表达式类似。
【例5.18】限制数据库课程最多60名学生选修.
CREATE ASSERTION ASSE_SC_CNUM1
CHECK(60>=ALL(SELECT count(*)
FROM Course,SC
WHERE SC.Cno=Course.Cno and Course.Cname='数据库')
);
1
2
3
4
5
【例5.19】限制每一门课程最多60名学生选修。
CREATE ASSERTION ASSE_SC_CNUM1
CHECK(60>=ALL(SELECT count(*)
FROM SC
GROUP BY cno)
);
1
2
3
4
5
2.删除断言的语句格式
语句格式：
DROP ASSERTION <断言名>;
1
5.7 触发器
一.定义触发器
定义触发器语句：
CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>
1
2
3
4
5
1）当特定的系统事件发生时，对规则的条件进行检查。
如果条件成立则执行规则中的动作，否则不执行该动作。
规则中的动作体可以很复杂，通常是一段SQL存储过程。
2）触发事件可以是 INSERT、DELETE 或 UPDATE ，也可以是这几个事件的组合，还可以 UPDATE OF<触发列，…>，即进一步指明修改哪些列时激活触发器。
3）AFTER/BEFORE 是触发的时机。
AFTER 表示在触发事件的操作执行之后激活触发器。
BEFORE 表示在触发事件的操作执行之前激活触发器。
4）触发器类型 ：
行级触发器（FOR EACH ROW）
语句级触发器（FOR EACH STATEMENT）
如果是语句级触发器，那么执行完该语句后，触发动作只发生1次。
如果是行级触发器，触发动作将执行1000次。
注意！： 不同的RDBMS产品触发器语法各不相同。
【例5.21】当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到另一个表（ SC_U（Sno,Cno,Oldgrade,Newgrade））中，其中Oldgrade是修改前的分数，Newgrade是修改后的分数。
标准SQL 语句：
CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC
REFERENCING
OLD row AS OldTuple,
NEW row AS 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)
1
2
3
4
5
6
7
8
9
T-SQL 语句：
①：建立SC-U表
DROP TABLE IF EXISTS SC_U;
create table SC_U
(
Sno char(9) ,
Cno char(9) ,
oldgrade smallint ,
newgrade smallint ,
)
1
2
3
4
5
6
7
8
9
②：建立触发器
create trigger SC_T
on SC
for update
as declare @old smallint
declare @new smallint
declare @Sno char(9)
declare @Cno char(4)
if(update(Grade))
begin
select @old =Grade from deleted
select @new =Grade from inserted
select @Sno =Sno from deleted。
select @Cno =Cno from deleted
IF(@NEW>=1.1*@OLD)
insert into SC_U(Sno,Cno,oldgrade,newgrade)
values(@Sno, @Cno,@old,@new)
end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
【例5.22】 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。
标准SQL 语句：
CREATE TRIGGER Student_Count
AFTER INSERT ON Student
REFERENCING
NEW TABLE AS DELTA
FOR EACH STATEMENT
INSERT INTO StudentInsertLog (Numbers)
SELECT COUNT(*) FROM DELTA
1
2
3
4
5
6
7
T-SQL 语句：
create table StudentInsertLog(
Numbers int
);
1
2
3
create trigger Student_Count
on Student
after insert
as insert into StudentInsertLog(Numbers)
select count(*) from Student;
1
2
3
4
5
【例5.23】定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。
标准SQL语句：
CREATE TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher
/*触发事件是插入或更新操作*/
FOR EACH ROW /*行级触发器*/
BEGIN /*定义触发动作体，是PL/SQL过程块*/
IF (new.Job='教授') AND (new.Sal < 4000)
THEN new.Sal :=4000;
END IF;
END;
1
2
3
4
5
6
7
8
9
二.激活触发器
触发器的执行，是由触发事件激活的，并 由数据库服务器自动执行 ！
一个数据表上可能定义了多个触发器，遵循如下的执行顺序:
（1） 执行该表上的BEFORE触发器;
（2） 激活触发器的SQL语句;
（3） 执行该表上的AFTER触发器。
三.删除触发器
删除触发器的SQL语句：
DROP TRIGGER <触发器名> ON <表名>;
1
8.3 存储过程函数
一.存储过程
存储过程 ：由过程化SQL语句，经编译和优化后存储在数据库服务器中，可以被反复调用，运行速度较快。
优点：
（1）运行效率高。
（2）降低了客户机和服务器之间的通信量。
（3）方便实施企业规则。
（ 1）创建存储过程
CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>；
1
【例8.9】 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。
标准SQL :
CREATE OR REPLACE PROCEDURE TRANSFER(inAccount INT,outAccount INT,amount FLOAT)
/*定义存储过程TRANSFER，参数为转入账户、转出账户、转账额度*/
AS DECLARE /*定义变量*/
totalDepositOut Float;
totalDepositIn Float;
inAccountnum INT;
BEGIN /*检查转出账户的余额 */
SELECT Total INTO totalDepositOut FROM Accout
WHERE accountnum=outAccount;
IF totalDepositOut IS NULL THEN
/*如果转出账户不存在或账户中没有存款*/
ROLLBACK; /*回滚事务*/
RETURN
END IF;
IF totalDepositOut< amount THEN /*如果账户存款不足*/
ROLLBACK; /*回滚事务*/
RETURN
END IF
SELECT Accountnum INTO inAccountnum FROM Account
WHERE accountnum=inAccount;
IF inAccount IS NULL THEN /*如果转入账户不存在*/
ROLLBACK; /*回滚事务*/
RETURN;
ENDIF;
UPDATE Account SET total=total-amount WHERE accountnum=outAccount; /* 修改转出账户余额，减去转出额 */
UPDATE Account SET total=total + amount
WHERE accountnum=inAccount; /* 修改转入账户余额，增加转入额 */
COMMIT; /* 提交转账事务 */
END;
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
T-SQL语句：
CREATE PROCEDURE Proc_TRANSFER (
--定义存储过程为 transfer
@inAccount INT,
@outAccount INT,
@amount FLOAT)
--形参
AS
BEGIN TRANSACTION TRANS
DECLARE
@totalDepositOut FLOAT,--定义变量
@totalDepositin FLOAT,
@inAccountum INT;
SELECT @totalDepositOut=total
FROM Account
WHERE accountnum = @outAccount;
IF @totalDepositOut is null --1.如果转出账户不存在或账户中没有存款
BEGIN
PRINT '转出账户不存在或者账户余额为0'
ROLLBACK TRANSACTION TRANS; --回滚事务
RETURN
END ;
IF @totalDepositOut < @amount --2.账户余额不足
BEGIN
PRINT '账户余额不足'
ROLLBACK TRANSACTION trans;
RETURN;
END
SELECT @inAccount =accountnum
FROM Account
WHERE accountnum = @inAccount;
IF @inAccount is null
BEGIN
PRINT '转入账户不存在'
ROLLBACK TRANSACTION trans;
RETURN;
END
BEGIN
UPDATE Account SET total =total-@amount
WHERE accoutnum =@outAccount;
UPDATE account SET total = total+@amount
WHERE accountnum =@inAccount;
PRINT '转账成功'
COMMIT TRANSACTION TRANS;
RETURN ;
END;
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
（2）执行存储过程
CALL/PERFORM PROCEDURE 过程名([参数1,参数2,...])；
1
使用 CALL 或者 PERFORM 等方式激活存储过程的执行。
数据库服务器 支持在过程体中调用其他存储过程。
【例8.10】从账户01003815868转10000元到01003813828账户中。
标准SQL语句：
CALL PROCEDURE
TRANSFER (01003815868,01003813828,10000);
1
2
T-SQL语句：
EXEC Proc_TRANSFER
@inAccount = 01003815868, --转入账户
@outAccount = 01003813828, --转出账户
@amount = 10000 --转出金额
SELECT * FROM Account;
1
2
3
4
5
6
（3）修改存储过程
ALTER PROCEDURE 过程名1 RENAME TO 过程名2;
1
（4）删除存储过程
DROP PROCEDURE 过程名()；
1
二.函数
函数 和 存储过程 的异同：
同 ：都是持久性存储模块
异 ：函数必须指定返回的类型
1）函数的定义语句格式
CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型> AS <过程化SQL块>;
1
2）函数的执行语句格式
CALL/SELECT 函数名 ([参数1,参数2,…]);
1
3）修改函数
重命名
ALTER FUNCTION 过程名1 RENAME TO 过程名2;
1
重新编译
ALTER FUNCTION 过程名 COMPILE;
1
总结：T-SQL 和标准SQL真的有太多不一样了，一定要好好注意区分。
————————————————
版权声明：本文为CSDN博主「偏偏意气用事_」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_46009153/article/details/115860341