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

最新推荐文章于 2023-02-09 12:28:38 发布

开朗的大蒜

最新推荐文章于 2023-02-09 12:28:38 发布

阅读量672

点赞数 3

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

本文链接：https://blog.csdn.net/qq_31747473/article/details/105327678

版权

数据库专栏收录该内容

22 篇文章 8 订阅

订阅专栏

本文参考内容地址：
https://blog.csdn.net/qq_38975453/article/details/104729681

实体完整性：

关系模型的实体完整性再create table 中用 primary key定义。
对单属性构成的码有两种说明方法：
定义为列级约束条件
定义为表级约束条件
对多个属性构成的码，只能定义为表级约束条件；

[例5.1] 将Student表中的Sno属性定义为码

--列级定义主码
create tablr 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        /*级联删除SC表中相应的元组*/
             ON UPDATE CASCADE,      /*级联更新SC表中相应的元组*/
         FOREIGN KEY (Cno) REFERENCES Course(Cno)	                    
             ON DELETE NO ACTION 	
          /*当删除Course 表中的元组造成了与SC表不一致时拒绝删除*/
             ON UPDATE CASCADE   
      	  /*当更新Course表中的Cno时，级联更新SC表中相应的元组*/
        );

当修改Student表的Sno列时，SC表也会自动同步更新；

用户定义完整性

属性上的约束条件的定义：
列值非空（not null）
列值唯一（unique）
检查列值是否满足一个条件表达式（check语句）。

（1）不允许空值：
[例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不允许取空值，则在列级不允许取空值的定义可不写 */
		...
		
	);

（2）列值唯一

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

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

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

我们来进行测试（插入数据）：
在这里插入图片描述
若插入的性别不为‘男’或‘女’ ，插入失败：

[例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.%')/*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/
	);

完整性约束命名：

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) /*应发工资是工资列Sal与扣除项Deduct之和。*/
	);

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

触发器：

定义触发器：

create trigger <触发器名>  
{before | after} <触发事件> on <表名>
referencing new|old row as<变量>
for each  {row | statement}
[when<触发条件>]<触发动作体>

删除触发器：

drop trigger <触发器名> on <表名>;

[例5.21] 当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：SC_U（Sno,Cno,Oldgrade,Newgrade）
其中Oldgrade是修改前的分数，Newgrade是修改后的分数。

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

在其中，如果触发条件是update并且由for each row子句，那么可以引用的变量有oldrow 和 newrow，分别表示修改前后的元组。

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

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

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

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;

在此定义的是before触发器，再插入和更新教师记录前就可以按照触发器的规则调整教授的工资；

过程化sql

存储过程和函数：

创建存储过程：

create or replace procedure过程名([参数1,参数2,...]) as <过程化SQL块>；

执行存储过程：

call / perform procedure过程名([参数1,参数2,...])；

修改存储过程：

alter procedure过程名1  rename to 过程名2;

删除存储过程：

drop procedure 过程名()；

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

以上两道题目的语句在SQL server中无法运行，如果在SQL server运行需要用到以下语句：

--建立新表Account ,写入两个用户
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


--执行存储过程：
SELECT * FROM Account
EXEC	Proc_TRANSFER
		@inAccount = 101,	--转入账户
		@outAccount = 102,	--转出账户
		@amount = 50		--转出金额

SELECT * FROM Account

在这里插入图片描述

还需要多加练习。

开朗的大蒜

关注

3
点赞
踩
4

收藏

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

实体完整性：关系模型的实体完整性再create table 中用 primary key定义。对单属性构成的码有两种说明方法：定义为列级约束条件定义为表级约束条件对多个属性构成的码，只能定义为表级约束条件；[例5.1] 将Student表中的Sno属性定义为码--列级定义主码create tablr Student ( Sno char(9) primary...
复制链接

扫一扫