SQL Server——从入门到放弃（10）-- CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION 练习

又是新的一章开始了，今天开始的是第五章的内容——数据库完整性

所谓数据库的完整性指的是

1. 数据的正确性
   * 数据是符合现实世界语义，反映了当前实际状况的
2. 数据的相容性
   * 同一对象在不同关系表中的数据是符合逻辑的

一、实体完整性

关系模型的实体完整性是通过在CREATE TABLE中使用PRIMARY KEY来定义的。具体使用方法有两种，一种是在列级定义主码，另一种是在表级定义主码。

【例5.1】将Student表中的Sno属性定义为码
列级定义主码

create table Student1
(Sno char(9) primary key,
Sname char(20) not null,
Ssex char(2),
Sage smallint,
Sdept char(20)
)

表级定义主码

create table Student2
(Sno char(9),
Sname char(20) not null,
Ssex char(2),
Sage smallint,
Sdept char(20),
primary key(Sno)
)

在实际应用中一般推荐第一种，在列级定义主码。

【例5.2】将SC表中的Sno，Cno属性组定义为码

create table SC
(Sno char(9) not null,
Cno char(4) not null,
Grade smallint,
primary key(Sno,Cno)
)

只能在表级定义主码

二、参照完整性

关系模型的参照完整性定义

• 在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码
• 用REFERENCES短语指明这些外码参照那些表的主码

【例5.3】定义SC中的参照完整性
这里需要新建一个Course表，具体建表过程详见。。。算了，不详了，到现在建表还不会的话，👍

create table SC
(
   Sno char(9) not null,
   Cno char(4) not null,
   Grade smallint,
   primary key(Sno,Cno),
   /*在表级定义实体完整性*/
   foreign key(Sno) references Student1 (Sno),  /*在表级定义参照完整性*/
   foreign key(Cno) references Course (Cno)
   /*在表级定义参照完整性*/
)

可能破坏参照完整性的情况及违约处理

被参照表（例如Student）	参照表（例如SC）	违约处理
可能破坏参照完整性	←插入元组	拒绝
可能破坏参照完整性	←修改外码值	拒绝
删除元组 →	可能破坏参照完整性	拒绝/级联删除 /设置为空值
修改主码值 →	可能破坏参照完整性	拒绝/级联删除 /设置为空值

【例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 Student1 (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表中相应的元组*/
)

三、用户定义的完整性

用户定义的完整性是：针对某一具体应用的数据必须满足的语义要求。

属性上的约束条件
CREATE TABLE时定义属性上的约束条件

• 列值非空（NOT NULL）
• 列值唯一（UNIQUE）
• 检查列值是否满足一个条件表达式（CHECK）

【例5.5】在定义SC表时，说明Sno、Cno、Grade属性不允许取空值

create table SC
(
   Sno char(9) not null,
   Cno char(4) not null,
   Grade smallint,
   primary key(Sno,Cno),
   foreign key(Sno) references Student1 (Sno)
 )

【例5.6】建立部门表DEPT，要求部门名称Dname列值取唯一，部门编号Deptno列为主码

create table Dept
(
   Deptno numeric(2),
   Dname char(9) unique not null,
   /*要求Dname列值唯一，并且不能取空值*/
   Location char(10),
   primary key(Deptno)
)

用CHECK短语指定列值应满足的条件
【例5.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)
)

检验：

insert into Student 
values ('2018110','张三','男',18,'CS')

select * from Student

insert into Student 
values ('2018111','李四','M',18,'CS')

select * from Student

报错原因就是因为Student表有Ssex约束，只能是男或女。

【例5.8】SC表的Grade的值应该在0和100之间

create table SC
(
  Sno char(9),
  Cno char(4),
  Grade smallint check(Grade>=0 and Grade<=100),
  primary key(Sno,Cno),
  foreign key(Sno) references Student(Sno),
  foreign key(Cno) references Course(Cno)
)

元组上的约束条件
在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级限制
【例5.9】当学生性别是男时，其名字不能以Ms. 打头

create table Student
(
   Sno char(9),
   Sname char(8) not null,
   Ssex char(2),
   Sage smallint,
   Sdept char(10),
   primary key(Sno),
   check(Ssex='女' or Sname not like 'Ms.%')
)

四、完整性约束命名子句

完整性约束命名子句
CONSTRAINT <完整性约束条件名> <完整性约束条件>
<完整性约束条件>包括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 ('男','女')),
   Sdept char(10),
   constraint StudentKey primary key(Sno),
)

在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束

【例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 C0 check(Sal+Deduct>=3000)
)

修改表中的完整性限制

• 使用ALTER TABLE语句修改表中的完整性限制

【例5.12】去掉例5.10Student表中对性别的限制

alter table Student
drop constraint C4

【例5.13】修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40
做法思路：先删除原来的约束条件，再增加新的约束条件

alter table Student3
drop constraint C1;
alter table Student3
add constraint C1 check(Sno between 900000 and 999999)

alter table Student3
drop constraint C3;
alter table Student3
add constraint C3 check(Sage<40)

五、触发器

触发器（Trigger） 是用户定义在关系表上的一类由事件驱动的特殊过程。任何用户对表的增、删、改操作均有服务器自动激活 相应的触发器。触发器类似于约束，但是比约束更加灵活，可以实施更为复杂的检查和操作。

定义触发器：
触发器又叫事件—条件—动作 规则，定义的一般语句格式为：

CREATE TRIGGER <触发器名>                 /*每当触发事件发生时，该触发器被激活*/
{ BEFORE | AFTER } <触发事件> ON <表名>   /*指明触发器激活的时间是在执行触发事件前或后*/
REFERENCES NEW|OLD ROW AS <变量>         /*REFERENCES 指出引用的变量*/
FOR EACH { ROW | STATEMENT }            /*定义触发器的类型，指明东座体执行的频率*/
[WHEN <触发条件> ] <触发动作体>          /*仅当触发条件为真时才执行触发动作体*/

【例5.21】当对表SC的Grade属性进行修改时，若分数增加了10%则将此此操作记录到下面的表中：
SC_U(Sno，Cno，Oldgrade，Newgrade)
其中Oldgrade是修改前的分数，Newgrade是修改后的分数
这里也应该先定义SC_U表，并且在之前定义的三张表中添加一些数据

CREATE TABLE SC_U
	(Sno CHAR(9) PRIMARY KEY,  
	 Cno CHAR(9),
	 Oldgrade SMALLINT,
	 Newgrade SMALLINT
	 )

create trigger SC_T
after update of Grade on SC
references
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(OldGrade.Sno,OldeGrade.Cno,OldGrade.Grade,NewGrade.Grade)

执行之后

应该是T-SQL和标准SQL又有不同了。
此时T-SQL写法应为

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 SC
	select @Cno =Cno from SC
	if(@new>=1.1*@old)
	insert into SC_U(Sno,Cno,OldGrade,NewGrade)
	values (@Sno,@Cno,@old,@new)
end

【例5.22】将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中

create trigger Student_Count
after insert on Student
referencing
    new table as delta
for each statement
    insert into StudentInsertLog(Numbers)
	select count(*) form delta

😓😓😓
T-SQL语句

/*新建表StudentInsertLog，存储学生人数*/
CREATE TABLE StudentInsertLog(
	Numbers INT
)

/*新建表StudentInsertLogUser存储用户名和操作时间*/
CREATE TABLE StudentInsertLogUser(
	UserName NCHAR(10),
	DateAndTime datetime
)
/*新建触发器Student_Count,插入新的学生记录的时候，触发器启动，自动在StudentInsertLog里记录学生的人数*/
CREATE TRIGGER Student_Count
ON Student
AFTER 
INSERT
AS
	INSERT INTO StudentInsertLog(Numbers)
	SELECT COUNT(*) FROM Student
/*新建触发器Student_Time，当插入新的学生记录时，触发器启动，自动在StudentInsertLogUser记录用户名和操作时间*/
CREATE TRIGGER Student_Time
ON Student
AFTER
INSERT
AS
	declare @UserName nchar(10)
	declare @DateTime datetime

	select  @UserName=system_user
	select  @DateTime=CONVERT(datetime,GETDATE(),120)

	INSERT INTO StudentInsertLogUser(UserName,DateAndTime)
	VALUES(@UserName,@DateTime)
/*测试触发器效果*/
INSERT 
INTO Student
VALUES('2018113','王五','男',19,'CS')
SELECT * FROM Student
SELECT * FROM StudentInsertLogUser
SELECT * FROM StudentInsertLog

这里直接借用了老师的代码，因为我真的是不想敲了😳
另外使用的时候一定要分步执行。

【例5.23】定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则，教授的工资不低于4000元，如果低于4000元，自动改为4000元

教师表

create trigger Insert_Or_Update_Sal
before insert or update on TEACHER
for each row
begin
   if(new.Job='教授')and(new.Sal<4000)
          then new.Sal : =4000
		  end if
end

接下来发生什么不用我说都懂吧。
T-SQL写法

create trigger Insert_Or_Update_Sal
on TEACHER
for update,insert
as
	if update(Sal)
	begin
	declare @ENO char(9)
	declare @ENAME char(9)
	declare @JOB char(9)
	declare @SAL smallint

	select @SAL = Sal from inserted
	select @ENO =Eno from TEACHER
	select @ENAME =Ename from TEACHER
	select @JOB =Job from TEACHER
	
	if(@SAL<4000 AND @JOB='教授')
	update TEACHER
	set SAL=4000
	where Sal<4000 and Job='教授'
end

测试一下

insert 
into TEACHER
values('05','石昊','教授',2500,null,null)


select * from TEACHER

删除触发器

DROP TRIGGER <触发器名> ON <表名>

六、储存过程

储存过程： 由过程化SQL语句，经编译和优化后储存在数据库服务器中，可以被反复调用，运行速度较快。

优点：

1. 运行效率高
2. 降低了客户机和服务器之间的通信量
3. 方便实施企业规划

创建、执行、修改、删除

创建储存过程

CREATE OR REPLACE PROCEDURE  过程名  ([参数1，参数2，...])
AS <过程化SQL块>

【例8.8】利用储存过程来实现下面的应用：从账户1转指定数额的款项到账户2中
课本上的是标准SQL写法，不用我多说，你们都懂，所以下面我将用T-SQL来写

建立新表并写入有两个用户

drop table if exists Account

create table Account
(
   accountnum char(3),  --账户编号
   total float         --账户余额
)

insert into Account values(101,50)
insert into Account values(102,100)

select * from Account

建立储存过程

if (exists (select * from sys.objects where name ='Proc_TRANSFER'))
    drop procedure Proc_TRANSFER
go
create procedure Proc_TRANSFER
@inAccount int,@outAccount int,@amount float
/*定义储存过程TRANSFER，参数为转入账户、转出账户、转账额度*/
as
begin transaction trans
    declare     /*定义变量*/
	@totalDepositOut float,
	@totalDepositIn float,
	@inAccountnum int;
	/*检查转出账户的余额*/
	select @totalDepositOut = total from Account where accountnum=@outAccount
	/*如果转出账户不存在或账户中没有存款*/
	if @totalDepositOut is null
	   begin
	      print '转出账户不存在或账户中没有存款'
		  rollback transaction trans
		  return
		end
	/*如果账户存款不足*/
	if @totalDepositOut<@amount
	   begin
	      print '账户存款不足'
		  rollback transaction trans
		  return
		end
	/*检查账户的状态*/
	select @inAccountnum=accountnum from Account where accountnum=@inAccount
	/*如果转入账户不存在*/
	if @inAccountnum is null
	   begin
	      print '转入账户不存在'
		  rollback transaction trans
		  return
		end
	/*如果条件都没有异常，开始转帐*/
	begin
	   update Account set total=total-@amount where accountnum=@outAccount
	   update Account set total=total+@amount where accountnum=@inAccount
	   print '转账完成，请取走银行卡'
	   commit transaction trans
	   return
	end

测试：
正常状态

exec Proc_TRANSFER
     @inAccount=101,
	 @outAccount=102,
	 @amount=50

select * from Account

存款不足

exec Proc_TRANSFER
     @inAccount=101,
	 @outAccount=102,
	 @amount=100

转出账户不存在

exec Proc_TRANSFER
     @inAccount=101,
	 @outAccount=10,
	 @amount=10

转入账户不存在

exec Proc_TRANSFER
     @inAccount=100,
	 @outAccount=102,
	 @amount=10

执行存储过程

CALL/PERFORM PROCEDURE 过程名([参数1，参数2，...])

• 使用CALL或者PERFORM等方式激活存储过程的执行
• 数据库服务器支持在过程体中调用其他存储过程

【例8.9】从账户666中转500元到888账户

call procedure
transfer(666,888,500)

不能执行（就知道😅）

insert Account values (666,1000)
insert Account values (888,1000)

select * from Account

exec Proc_TRANSFER
     @inAccount=888,
	 @outAccount=666,
	 @amount=500

select * from Account

修改存储过程

ALTER PROCEDURE 过程名1 RENAME TO 过程名2

删除存储过程

DROP PROCEDURE 过程名（）

呼~终于写完了。
前面几部分之前学过，写起来还好，比较轻松，但是这个触发器，我滴个神呐，也太难了吧，标准SQL与T-SQL完全不一样，无语。代码倒是敲完了，但还有很多不明白的地方，继续研究吧。如果有哪位读者有什么好的方法学触发器的话，还请赐教。