【数据库作业13】SQL练习8 - CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION

最新推荐文章于 2023-09-23 11:34:57 发布

Freedomhy

最新推荐文章于 2023-09-23 11:34:57 发布

阅读量1.6k

点赞数 4

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/Freedomhy/article/details/105333979

版权

数据库完整性包括：

实体完整性，参照完整性，用户定义的完整性

1、实体完整性

主码的定义
[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)
  );

该主码为多个属性的组合，则这些属性必须不为空且唯一，否则将被拒绝插入或修改。

2、参照完整性

指多表之间关系的设计，主要使用外键约束。
[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）拒绝（NO ACTION）执行
不允许该操作执行。该策略一般设置为默认策略
（2）级联（CASCADE）操作
当删除或修改被参照表（Student）的一个元组造成了与参照表（SC）的不一致，则删除或修改参照表中的所有造成不一致的元组
（3）设置为空值（SET-NULL）
当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。

[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 on action
   --删除course表中的元组造成与SC表不一致时拒绝删除
   on update cascade
   --更新course表中cno时，级联更新SC表中相应的元组
  );

3、用户定义的完整性

用户定义完整性约束是指根据应用环境的要求和实际的需要，对某一具体应用所涉及的数据提出约束性条件。
例如，约定学生成绩的数据必须小于或等于100。
其主要通过在属性上和在元组上的约束条件来实现。
列值非空（NOT NULL）；列值唯一（UNIQUE）；检查列值是否满足一个条件表达式（CHECK）；
属性上的约束条件检查和违约处理:
插入元组或修改属性的值时，关系数据库管理系统检查属性上的约束条件是否被满足,如果不满足则操作被拒绝执行。
2.元组上的约束条件：
用CHECK短语定义元组上的约束条件，即元组级的限制。
元组上的约束条件检查和违约处理：
插入元组或修改属性的值时，关系数据库管理系统检查元组上的约束条件是否被满足，如果不满足则操作被拒绝执行。

属性上的约束条件

不允许取空值（NOT NULL）
[5.5]在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

create table sc
(
 sno char(9) not null,
 cno char(4) not null,
 grade smallint not null,
 primary key (sno,cno)
);

列值唯一（UNIQUE）
[5.6]建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

create table dept
(
 deptno numeric(2),
 dname char(9) unique not null,
 location char(10),
 primary key(deptno)
);

用CHECK短语指定列值应该满足的条件
[5.7]Student表的Ssex只允许取“男”或“女”。

create table student
(
 sno char(9) primary key,
 sname char(8) not null,
 ssex char(2) check(ssex in('男','女')),
 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),
 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 char(20),
  primary key(sno),
  check(ssex='女' or sname not like'Ms.%')
  );

元组上的约束条件检查和违约处理：
插入元组或修改属性的值时，关系数据库管理系统检查元组上的约束条件是否被满足，如果不满足则操作被拒绝执行。

4、完整性约束命名子句

CONSTRAINT <完整性约束条件名><完整性约束条件>

<完整性约束条件>包括not null、unique、primary key、foreign key、check短语等
[5.10]建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。
在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束。

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)
);

查询结果：
在这里插入图片描述
这里没有找到C2，是因为NULL和NOT NULL 不被放在约束栏里。
[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)
  );

在这里插入图片描述
[5.12]使用ALTER TABLE语句修改表中的完整性限制

alter table student drop constraint c4;

去掉例5.10 Student表中对性别的限制。
[5.13]修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40。

alter table student drop constraint c1;
alter table student add constraint c1 check (sno between 90000 and 99999);
alter table student drop constraint c3;
alter table student add constraint c3 check(sage<40):

5、触发器（Trigger）

任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力。

定义触发器格式：

   CREATE TRIGGER <触发器名>  
       {BEFORE | AFTER} <触发事件> ON <表名>
       REFERENCING NEW|OLD ROW AS<变量>
       FOR EACH  {ROW | STATEMENT}
       [WHEN <触发条件>]<触发动作体>

当特定的系统事件发生时，对规则的条件进行检查。如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，通常是一段SQL存储过程。
[例5.21]当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：SC_U（Sno,Cno,Oldgrade,Newgrade）其中Oldgrade是修改前的分数，Newgrade是修改后的分数。
先创建sc_u表：

create table sc_u
  (  sno char(8),
  cno char(8),
  oldgrade smallint,
  newgrade smallint
  );

create trigger sc_t
after update of grade on sc
referencing oldrow as oldtuple,
   newrow 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)
  --报错“after”附近有语法错误。

在T-SQL中，应写成：

create trigger sc_t
on sc
after
update
as
 declare @sno char (15)
 declare @cno char(4)
 declare @oldgrade smallint
 declare @newgrade smallint
  if(update(grade))
 select @oldgrade=grade from deleted
 select @newgrade = grade from inserted
 select @sno=sno from sc
 select @cno= cno from sc
  insert
 into sc_u(sno,cno,oldgrade,newgrade)
 values(@sno,@cno,@oldgrade,@newgrade);

[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(*) from delta
  --消息 102，级别 15，状态 1，过程 student_count，行 2 [批起始行 0]
--“after”附近有语法错误。

--新建表，存储学生人数及存储用户名和操作时间
create table StudentInsertLog
(
 numbers int
)
create table studentInsertLogUser
(
 UserName nchar(10)
 DateAndTime datetime
);
--创建触发器
create trigger Student_count
on student
after
insert 
as
 insert 
  into studentInsertLog(numbers)
  select count(*)
  from student
  
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 ('201215135','王五','男',18,'CS');
  select * from Student
  select * from StudentInsertLog
  select * from StudentInsertLogUser

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

relate trigger Insert_Or_Update_Sal 
before
insert OR or on Teacher  
                         /*触发事件是插入或更新操作*/
for each row        /*行级触发器*/
begin                             /*定义触发动作体，是PL/SQL过程块*/
 if (new.Job='教授') AND (new.Sal < 4000) 
 then  new.Sal :=4000;                
 end if;
end;

--创建Teacher表
 CREATE TABLE Teacher
 (
  Name CHAR(10),
  Salary SMALLINT,
  Job CHAR(10)
 )
 --加入数据
 INSERT
 INTO Teacher(name,Salary,Job)
 VALUES('清风',3500,'教授');
 INSERT
 INTO Teacher(name,Salary,Job)
 VALUES('朗月',4500,'教授');
 INSERT
 INTO Teacher(name,Salary,Job)
 VALUES('枝枝',3000,'副教授');
 --创建触发器
  CREATE TRIGGER Insert_Or_Update_Sal
 ON Teacher
  FOR INSERT , UPDATE
 AS
  declare @Salary SMALLINT
  declare @Job CHAR(10)
  IF(UPDATE(Salary))
  SELECT @Salary=Salary FROM INSERTED
  SELECT @Job=Job FROM Teacher
  IF (@Job='教授' AND @Salary < 4000)
  UPDATE Teacher
  SET Salary=4000
  WHERE Job='教授' AND Salary<4000

测试：

 INSERT 
 INTO Teacher
 VALUES('张明',3200,'教授');
 SELECT *
 FROM Teacher

在这里插入图片描述
[8.8]利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2
首先在一个表中创建两个账户：

CREATE TABLE Account
(
accountnum INT,
total FLOAT 
)

INSERT INTO Account VALUES(11111,530)
INSERT INTO Account VALUES(22222,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 = 11111, --转入账户
  @outAccount = 22222, --转出账户
  @amount = 50  --转出金额
  select* fromAccount

在这里插入图片描述

--余额不足情况
  EXEC Proc_TRANSFER
  @inAccount = 11111, --转入账户
  @outAccount = 22222, --转出账户
  @amount = 51  --转出金额
  --账户存款不足

在这里插入图片描述
[8.9]从账户01003815868转10000元到01003813828账户中。
在8.8的基础上进行修改即可：

 drop table if EXISTS Account;
 create table Account
 (
 accountnum CHAR(15), -- 账户编号
 total FLOAT  -- 账户余额
 );
  insert into Account values(01003815868,20000)
 insert into Account values(01003813828,10000)
 exec Proc_TRANSFER
   @inAccount = 01003813828, --转入账户
   @outAccount = 01003815868, --转出账户
   @amount = 10000  --转出金额
   select * from Account

在这里插入图片描述
在这里首先感谢魏老师给了一个“重新做人”的机会，在这里还是要“狡辩”一下（hhh），我一般都是周二周三上午的时候专门做数据库实验的作业，这周三写完了12之后就以为13、14是下周才写的，而且在周日当天中午老师提醒的时候写13、14的同学也不多，所以就没有太上心，希望老师能够理解一下，打分的时候手下留情啊！

本章小结：触发器这一部分在语法以及代码量上相对于前面增加了，整体来说需要用到的逻辑相对于前面简单机械的增删改查也复杂了，后面需要勤加练习。

Freedomhy

关注

4
点赞
踩
15

收藏

觉得还不错? 一键收藏
0
评论
【数据库作业13】SQL练习8 - CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION

数据库完整性包括：实体完整性，参照完整性，用户定义的完整性1、实体完整性主码的定义[5.1] 将Student表中的Sno属性定义为码分别可用在列级定义主码create table student (sno char(9) primary key, sname char(20) not null, ssex char(2), sage smallint, ...
复制链接

扫一扫