数据库第五章 check /constraint/trigger/procedure/function

最新推荐文章于 2023-11-01 20:44:22 发布

睡觉对我很重要

最新推荐文章于 2023-11-01 20:44:22 发布

阅读量910

点赞数 4

分类专栏：数据库文章标签： sql

本文链接：https://blog.csdn.net/weixin_44724691/article/details/105280025

版权

数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

实体完整性概述
实体完整性是指关系模型中主码不能为空。在关系模型数据库中，使用primary key 定义主码。
【例5.1】将Student表中Sno设置为主码

create table Student
(
Sno  char(9) primary key,--列级
Sname char(20) not null， --设置不为空
Sage smallint 
--primary key(Sno) 表级条件
)

【例5.2】

use[Stu1]
create table SC
(
Sno char(9) not null,
Cno char(4) not null ,
Grade smallint ,
primary key (Sno,Cno)
--需要使用表级约束
)

定义参照完整性
1、使用foreign key声明外键，references声明参照那些主码

create table SC(
Sno char(9) not null,
Cno char(4) not null,
primary key(Sno),
foreign key (Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)
)

2、参照完整性检查以及违约处理
在这里插入图片描述

--标准SQL
create table SC(
Sno char(9) not null,
Cno char(4) not null,
primary key(Sno),
foreign key (Sno) references Student(Sno),
on delete cascade,
on update cascade ,
foreign key(Cno) references Course(Cno)
on delete cascade,
on update cascade ,
---T-sql 下面介绍
)

用户自定义的完整性
【例5.5】


create table SC
(
Sno char(9) not null,
Cno char(9) not null,
Grade smallint not null,
primary key (Sno ,Cno)
--用户定义为不能为空
)

【例5.6】


create table dept(
deptno smallint,
Dname char(9) unique not null ,
Location char(9) ,
primary key (deptno)

)

【例5.7】
Sex 只能是男或者是女

create table Student
(
Sno  char(9) primary key,--列级
Sname char(20) not null， --设置不为空
Sage smallint 
Ssex char (2) check (Ssex in('男','女'))
--性别只能是男或者女,如果不是将报错
--primary key(Sno) 表级条件
)

【例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】
用户定义的完整性中不只是在属性中，也能体现在元组中


create table Student
(
Sno  char(9) primary key,--列级
Sname char(20) not null, --设置不为空
Sage smallint ,
Ssex char (2) check (Ssex in('男','女')),
--性别只能是男或者女,如果不是将报错
--primary key(Sno) 表级条件
check (Sname not like'Ms.%' )
)

引入知识点：
完整性约束子句
对于创建的约束会保存在数据字典中

constraint <完整性约束名><完整性约束条件>
条件包括not null ,unique primary key , check 语句

【例5.10】


create table Student(
Sno char(9) 
constraint constraint_sno check (Sno between 2000 and 2050),
Sname char(9)
constraint constraint_name not null,
Sage  smallint
constraint constraint_sage check (Sage<30),
Ssex char(2)
constraint constraint_sex check(Ssex in ('男','女')),
constraint Studentkey primary key (Sno)
)

重述【例5.4】
SQLserver 直接运行代码会报错,使用以下代码

--先删掉SC
use [Stu1]
create table SC 
(
Sno char(9),
Cno char(4),
Grade smallint check(Grade>=0 and Grade <=100),
primary key (Sno,Cno),
)
alter table SC add constraint from_stu_sno  foreign key(Sno) references Student (Sno )on update cascade 
--插入数据


insert into Student values('2016','jisoo',25)
insert into Student values('2017','jisoo2',26)
insert into Student values('2018','rose',27)
insert into SC values('2016','1',90)
insert into SC values('2017','2',95)
insert into SC values('2018','1',100)
select *from Student 
select *from SC

在这里插入图片描述
将演示级联更新

update Student set Sno='2020' where Sname='刘1'
select *from SC

在这里插入图片描述
可以看到2016 这位同学学号改为了2020 ，SC中表的信息也被修改了。
对于delete也是同样的不再赘述。
【例5.11】

use [Stu1]
create table teacher(
Eno char (9),
Ename char(9),
Sal smallint,
Deduct  smallint ,
Deptno smallint,
constraint teacher_key primary  key(Eno), 
constraint c1 check (Sal +Deduct>3000)
--约束
)

【例5.12】修改完整性

alter table Student  drop constraint constraint_name 
--去掉完整性

【例5.13】修改约束条件

alter table Student drop constraint constraint_name
alter table Student add constraint constraint_name check(Sage<40)

重点
触发器

当事件发生时，自动的调用（包括对关系的增删改），会对规则的条件检测，规则的动作是可以很复杂的，会涉及其他的关系甚至是不同的数据库对象。

create trigger <名称>--名称
{before |after }<触发事件> on <表名>
--指明是在之前还在之后
referencing new |old row as <变量>
--指出引用的变量
for each{rows |statement}
--定义类型，指明触发器的频率
[when <触发条件><触发动作>]
---当触发器的条件为真时候相应触发器

说明：(这个是标准SQL)
（1）触发器只能是定义在基本表上的，不能在视图上
（2）触发器类型是指根据动作发生的间隔尺寸分为行级触发器，语句级触发器。

（3）
在这里插入图片描述

【例5.21】
问题

---标准SQL
create table S_U
(
Sno char(9) primary key ,
Cno char(9) ,
oldgrade smallint ,
newgrade smallint ,
)

create trigger SC_T
alter 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)

应改为

create table S_U
(
Sno char(9) primary key ,
Cno char(9) ,
oldgrade smallint ,
newgrade smallint ,
)

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
  insert into SC_U(Sno,Cno,oldgrade,newgrade)
  values(@Sno, @Cno,@old,@new)
end
update SC
set Grade=62
where Sno='2017' and Cno ='刘'
select *from SC
select *from SC_U

在这里插入图片描述
因此这里是有问题的，修改的是2017 刘同学但是SC_U表中的信息是刘2同学2019的信息，这很让我费解。
【例5.22】
将每次对表Student的插入操作增加的学生的个数记录到别的表中


create table StudentInsertLog(
numbers int 
)

create table StudentInsertUser(
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 StudentInsertUser(Username,Dateandtime)
  values(@username,@datetime)  

  insert into Student
  values('2023','刘5',27)


  select *from Student

  select *from StudentInsertLog
  select *from StudentInsertUser

在这里插入图片描述
可以看到结果是正确的。
【例5.23】
定义一个before级别的行级触发器定义一个完整性的规则

--创建表
  create table Teacher(
  Tno char(9),
  Tname char(9),
  Job char(9),
  Sal smallint ,
  )
--添加数据

  insert into teacher
  values('1','刘1','讲师',3000)
  insert into teacher
  values('2','刘2','教授',3000)
  insert into teacher
  values('3','刘3','教授',5000)

--标准SQL 语言 会报错
create trigger Insert_or_update 
  before insert or update on Teacher
  references new row  as newtuple
  for each row

  begin 
    if(newtuple.Job='教授') and (newtuple.Sal<4000)
	 then newtuple.Sal =4000
	end if 
  end

在这里插入图片描述
可以看到是正确的，触发器真的是很方便，删除的话使用drop即可。


drop  trigger Insert_or_update

总结：
触发器是真的好用，一旦对表进行增删改就可以使用特定的操作，不过感觉还是不熟。

【例8.8】


 create procedure pro_tran 
 @incount int ,@outcount int ,@amount float 
 as 
 begin transaction trans
  declare 
  @totaldepostionout float,
  @totalDepositionin float,
  @inaccounttion int ;
  select @totaldepostionout =total from account where accountnum=@outcount;
  if @totaldepostionout is null 
  begin 
   print'转出账户不再';
   rollback  transaction trans;
   return ;
   end;
   select @inaccounttion =accountnum from account  where accountnum =@inaccounttion;

   if @inaccounttion is null
   begin 
   print'转入账户不存在'
   rollback transaction trans 
   return 
   end;

 begin 
 update account set total=total -@amount where accountnum =@outcount;
 update account set total =total +@amount where accountnum =@incount;
 print '请取走银行卡'
 commit transaction trans
 return 
 end

看到这个代码我还是很开心的，和之前的代码不同，这个代码更像是一个程序。
在这里插入图片描述
可以看到消息中：

--分别运行代码结果不在演示
exec pro_tran 
@incount=100,
@outcount=102,
@amont=40

exec pro_tran
@incount =103,
@outcount=102,
@amont=10

【8.9】


 insert into account values(104,4000)
 insert into account values (105,4500)

 exec pro_tran 
 @incount=104,
 @outcount=105,
 @amount=1000

在这里插入图片描述
总结：存储过程更像是一个程序，一个函数，另外触发器真的好用。

睡觉对我很重要

关注

4
点赞
踩
7

收藏

觉得还不错? 一键收藏
4
评论
数据库第五章 check /constraint/trigger/procedure/function

实体完整性概述1.实体完整性是指关系模型中主码不能为空。在关系模型数据库中，使用primary key 定义主码。【例5.1】将Student表中Sno设置为主码create table Student(Sno char(9) primary key,--列级Sname char(20) not null --设置不为空Sage smallint --primary key(Sn...
复制链接

扫一扫