数据库完整性
数据库的完整性是指
- 数据的正确性：数据是符合现实世界语义，反映了当前实际状况的。
- 数据的相容性：同一对象在不同的关系表中的数据是符合逻辑的。
实体完整性
关系模型的实体完整性在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)
);
或者
create table Student
(
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中的参照完整性
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) /*在表级定义参照完整性*/
);
【例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 -- 当删除student表中的元组时，级联删除SC表中对应的元组
on update cascade -- 当更新student表中的元组时，级联更新SC表中对应的元组
foreign key (Cno) references Course(Cno)
on delete no action -- 当删除course表中的元组造成与SC表不一致时，拒绝删除
on update cascade -- 当更新course表中的cno时，级联更新SC表中对应的元组
);
用户定义完整性
属性上的约束条件的定义：
1.列值非空（not null）
2.列值唯一（unique）
3.检查列值是否满足一个条件表达式（check语句）。
【例5.5】在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。
create table SC
(
Sno char(9) not null, /*Sno属性不能取空值*/
Cno char(4) not null,
Grade smallint not null,
primary key(Sno, Cno), /* 在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义可不写 */
);
【例5.6】建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码
create table DEPT
(
Deptno numeric(2),
Dname char(9) unique not null, -- 要求Dname列值唯一, 并且不能取空值
Location char(10),
primary key (Deptno)
);
【例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)
);
【例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)
);
【例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.%') --利用模糊查询来check
);
完整性约束子句
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( '男','女')),
constraint StudentKey primary key(Sno)
);
【例5.11】建立教师表TEACHER，要求每个教师的应发工资不低于3000元。
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 TEACHERFKry foreign key (Deptno) references DEPT(Deptno),
constraint C1 check(Sal + Deduct >= 3000)
);
【例5.12】去掉例5.10 Student表中对性别的限制。
alter table Student
drop constraint C4;
【例5.13】修改表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);
断言
在SQL中可以用create assertion语句来指定更具一般性的约束。可以涉及多个表和聚集操作的比较复杂的完整性约束。但在T-SQL中没有这个功能。但不知道考试会不会考到，还是把例题练一练。看了大佬的博客发现断言也可以用触发器来间接的实现，跟着就把例5.18用触发器写了一遍。
创建断言的语句格式：
CREATE ASSERTION<断言名><CHECK子句>
【例5.18】限制数据库课程最多60名学生选修
create assertion ASSE_SC_DB_NUM
check (60>=(select count(*)
from Course,SC
where Course.Cno=SC.Cno and Course.Cname='数据库'));
T-SQL触发器写法：
if(object_id('trg_sc_insert')is not NULL)
drop trigger trg_sc_insert;
go --这里必须要写，不然会有错误提示
create trigger trg_sc_insert
on SC for insert
as
declare @sumstudent int,
@Sno varchar(15),
@Cno varchar(10),
@Grade smallint;
select @sumstudent=count(*) from Course,SC where SC.Cno=Course.Cno and Course.Cname='数据库'
select @Sno=Sno,@Cno=Cno,@Grade=Grade from inserted;
if(@sumstudent>60)
begin
delete SC where Sno=@Sno and Cno=@Cno and Grade=@Grade;
end
【例5.19】限制每一门课程最多60名学生选修
create assertion ASSE_SC_DB_NUM
check (60>=all(select count(*)
from Course,SC
group by Cno));
删除断言的语句格式：
drop assertion asse_name;
触发器（trigger）
- 任何用户对表的增、删、改操作均由服务器自动激活相应的触发器。
- 触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力 。
定义触发器
create trigger 语法格式：
--标准SQL
create trigger <触发器名>
{before | after} <触发事件> on <表名>
referencing new|old row as<变量>
for each {row | statement}
[when<触发条件>]<触发动作体>
--T-SQL（来自SQLserver帮助文档）
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
其中 <触发事件> 可以是insert、delete、update和这几个事件的组合。还可以是update of <触发列，…> 即进一步指明修改哪些列时激活触发器。
after | before 是触发的时机，是触发事件执行后还是执行前激活。
还有行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT）
【例5.21】当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：SC_U（Sno,Cno,Oldgrade,Newgrade），其中Oldgrade是修改前的分数，Newgrade是修改后的分数。
首先建表：
create table SC_U(Sno char(9),
Cno char(4),
Old smallint,
New smallint);
建立触发器：
-- 标准SQL
create trigger SC_T
after update of Grade on SC
referencing
old raw as oldTuple,
new raw as newTuple
for each raw
when(NewTuple.Grade >= 1.1 * OldTuple.Grade)
insert into SC_U(Sno,Cno,OldGrade,NewGrade)
values(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade);
-- T-SQL（参考之前的大佬的博客）
create trigger SC_T
on SC for update
as
declare @Sno varchar(15),
@Cno varchar(10),
@new_grade smallint,
@old_grade smallint;
select @Sno=Sno,@Cno=Cno,@new_Grade=Grade from inserted;
select @old_Grade=Grade from deleted;
if(@new_grade>@old_grade*1.1)
begin
insert into SC_U(Sno,Cno,Old,New)
values(@Sno,@Cno,@old_Grade,@new_Grade);
end
测试：
update SC
set Grade=100
where Sno='201215121' and Cno='1'; --最一开始是0分
select * from SC_U;
【例5.22】将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。
首先建表StudentInsertLog：
create table StudentInsertLog(
New_add_stu_num int
)
--标准SQL
create trigger Student_Count
after update on Student
referencing
new table as DELTA
for each statement
insert into StudentInsertLog (Numbers)
select count(*) from DELTA
T-SQL做法：参考
-- T-SQL
create trigger Student_Count
on Student for insert
as
declare @newNum smallint
select @newNum=count(*) from inserted
insert into StudentInsertLog
values(@newNum);
测试：
insert into Student
values(1,'啊啊','男',20,'CS'),
(2,'啊啊啊','男',20,'CS');
select * from StudentInsertLog;
【例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;
--T-SQL（参考之前的大佬的博客）
create trigger insert_or_update_salary
on Teacher for insert,update
as
declare @Eno numeric(4,0),
@Ename varchar(10),
@Job varchar(8),
@Salary numeric(7,2),
@Deduct numeric(7,2),
@Deptno numeric(2,0);
select @Eno=Eno,@Ename=Ename,@Job=Job,@Salary=Salary,@Deduct=Deduct,@Deptno=Deptno
from inserted;
if(@Salary+@Deduct<4000)
begin
update Teacher set Salary=4000 where Eno=@Eno and Ename=@Ename;
end
删除触发器
drop trigger <触发器名> on <表名>;
--T-SQL的不同就是不用on表名了
存储过程
存储过程：由过程化SQL语句，经编译和优化后存储在数据库服务器中，可以被反复调用，运行速度较快。
创建存储过程
语法格式：
create or replace procedure过程名([参数1,参数2,...]) as <过程化SQL块>；
【例8.8】利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。
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;
end if;
update Account set total = total - amount
where accountnum = outAccount; /* 修改转出账户余额，减去转出额 */
update Account set total = total + amount
where accountnum = inAccount; /* 修改转入账户余额，增加转入额 */
commit; /* 提交转账事务 */
end;
【例8.9】从账户01003815868转10000元到01003813828账户中
call procedure transfer(01003813828,01003815868,10000);
【例8.8】和【例8.9】的T-SQL写法：
先建表Account：
create table Account
(
account_id int,
total int
);
建立存储过程：
create procedure transfer_account
@in_id int,
@out_id int,
@salary int
as
declare @in_salary int,
@out_salary int;
select @in_salary=total from Account where account_id=@in_id;
select @out_salary=total from Account where account_id=@out_id;
if(@in_salary is null)
begin
print '收款账户不存在';
return;
end
if(@out_salary is null)
begin
print '汇款账户不存在';
return;
end
if(@out_salary<@salary)
begin
print '钱不够';
return;
end
update Account set total=total+@salary where Account_id=@in_id;
update Account set total=total-@salary where Account_id=@out_id;
--另外改存储过程名可以用
--exec sp_rename 'transfer_account','transfer_salary';
--删除存储过程可以用
--drop procedure transfer_salary;
测试（例8.9）：
exec transfer_account 01003813828,01003815868,10000
select * from Account;
1096
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
