数据库第五章例题

最新推荐文章于 2023-04-22 10:26:57 发布

cristove

最新推荐文章于 2023-04-22 10:26:57 发布

阅读量4.4k

点赞数 2

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

本文链接：https://blog.csdn.net/cristove/article/details/116017170

版权

数据库实验课专栏收录该内容

22 篇文章

订阅专栏

本文详细介绍了数据库的完整性约束，包括实体完整性、参照完整性和用户定义完整性，并通过多个实例展示了如何定义和修改这些约束。同时，文章还探讨了触发器的使用，如在数据修改时自动记录变化及维护数据的完整性。此外，提到了存储过程和函数在数据库操作中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实体完整性

实体完整性在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));

这个跟5.1一样，因为要把两列都定义为码，只能在表级定义主码。
在这里插入图片描述

参照完整性

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

在表级定义的Sno，Cno为主码
Sno，Cno也是在表级定义的参照完整性。
在这里插入图片描述

例5.4 显示说明参照完整性的违约处理事例。

CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno) REFERENCES Student (Sno)
ON DELETE CASCADE
ON UPDATE CASCADE
FOREIGN KEY(Cno) REFERENCES Course (Cno)
ON DELETE NO ACTION
ON UPDATE CASCADE);

主要是针对被参照表的操作
当删除（或更新）Student表中的元组时，级联删除（或更新）SC表中的相应元组。
删除Course表中的元组，如果与SC表不一致，则拒绝删除。
更新Course表的Cno，SC表也跟着更新。
被参照表更新，参照表拒绝/更改/设为空值。

用户定义完整性

列值非空 NOT NULL
列值唯一 UNIQUE
检查列值是否满足一个条件 CHECK短语

例5.5 定义表时，说明一些属性不允许空值。

CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT NOT NULL,
PRIMARY KEY (Sno,Cno),
……);

这里设置的Sno，Cno，Grade都不能为空值，就是在定义的时候，在数据类型后面加上 NOT NULL。

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

CREATE TABLE DEPT
(Deptno NUMERIC(2),
Dname CHAR(9) UNIQUE NOT NULL,
Location CHAR(10),
PRIMARY KEY(Deptno));

Dname 列值唯一且不能为空值，在数据类型后面加上 UNIQUE 和NOT NULL一样，之间用空格隔开。

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

CHECK(条件表达式)

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

CHECK()
这个括号里面如果填列值区间，就直接填列值的条件表达式，要是在几个选项之间选择，就用列值IN（，，，）。

例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.%'));

在这里插入图片描述
往表中插入或修改元组的时候，如果不满足元组的约束条件，就会被拒绝执行。

完整性约束命名子句

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

完整性约束条件就是NOT NULL、UNIQUE、PRIMARY、FOREIGN KEY、CHECK短语等。
这个是嵌入在CREATE里面的。

例5.10 建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄不能小于30，性别只能是“男”或“女”。

CREATE TABLE Student
(
	Sno NUMERIC(6)
		CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),
	Sname CHAR(20)
		CONSTRAONT C2 NOT NULL,
	Sage NUMERIC(3)
		CONSTRAINT C3 CHECK (Sage<30),
	Ssex CHAR(2)
		CONSTRAINT C4 CHECK(Ssex IN ('男','女'))，
		CONSTRAINT StudentKey PRIMARY(Sno)
);

五个列级约束，行级约束跟定义主码行级方法类似

例5.11 建立教师表TEACKER，要求每个教师的应发工资不低于3000元。应发工资是工资列Sal与扣除项Deduct之和。

CREATE TABLE TEACHER
(
Eno NUMERIC(10) PRIMARY KEY,
Ename CHRA(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 去掉例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
CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),
ALTER TABLE Student
	DROP CONSTRAINT C3;
ALTER TABLE Student
CONSTRAINT C3 CHECK(Sage<40),

断言

CREATE ASSERTION <断言名><CHECK 子句>
CHECK 子句中的约束条件与WHERE子句的条件表达式相似。

例5.18 限制数据库课程最多60学生选修。

CREATE ASSERTION ASSE_SC_DB_NUM
	CHECK (60>=(SELECT COUNT(*)
	FROM Course,SC
	WHERE SC.Cno=Course.Cno AND Course.Cname='数据库')
);

例5.19 限制每一门课程最多60名学生选修。

CREATE ASSERTION ASSE_SC_CNUM1
	CHECK (60>=ALL(SELECT COUNT(*)
	FROM SC
	GROUP BY Cno)
);

删除断言

DROP ASSERTION <断言名>

触发器

定义触发器

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

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

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

Tsql

CREATE TRIGGER trigger_name[触发器名称] 
	ON table_name[表名] 
		 instead of/for before [insert、update、delete]
		AS
			BEGIN
				....触发器执行体....
			END
		GO
注释：[]中的为中文解释；
DML触发器分为两种：
	前置触发：instead of
	后置触发：for before
		  	 insert ：后置插入触发 
		  	 update ：后置修改触发
		  	 delete ：后置删除触发
		     写法：for [insert、update、delete];before可省略不写

CREATE TRIGGER  SC_T	
ON SC
AFTER UPDATE 
as
begin
	declare @Oldgrade int ,@Newgrade int ,@Sno char(9), @Cno char(4)
	select @Oldgrade=Grade from deleted
	select @Newgrade=Grade from inserted
	select @Sno=Sno from inserted
	select @Cno=Cno from inserted
	if(@Newgrade>=1.1*@Oldgrade)
	begin
	insert 
	into SC_U(Sno,Cno,Oldgrade,Newgrade)  
	VALUES(@Sno,@Cno,@Oldgrade,@Newgrade)
	print'添加了一条数据'
	end
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(*) 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                           
             	IF (new.Job='教授') AND (new.Sal < 4000) 
             	 THEN  new.Sal :=4000;                
           	 END IF;
        END;

删除触发器

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

TSQL中不用加ON，和表名。
在这里插入图片描述

存储过程

（1）创建存储过程

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

例8.9 利用存储过程实现下面的应用：从账户1转指定数额的款项到账户2中。假设账户关系表为Account(Accountnum,Total).

create table Account(Accountnum char(3),total float);
insert into Account values('01',100),('02',100);

reate or replace procedure Transfer(inAccount int ,outAccount int, amount float)
as
	declare 
	totalDepositOut float;
	totalDepositIn float;
	inAccountnum int;
begin
	select total into totalDespositOut from Account
	where Accountnum = outAccount;
	if totalDespositOut is NULL then
	rollback;
	return ;
	end if;
	if totalDespositOut<amount then
	rollback;
	return ;
	end if;


	select Accountnum into inAccountnum from Account where Accountnum=inAccount;
	if inAccountnum is null then
	rollback;
	return ;
	end if;

	update Account set total =total -amount
	where Accountnum=outAccout;
	update Account set total =total + amount
	where Accountnum = inAccount;
	commit;
end;

执行存储过程

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

在这里插入图片描述

例8.10 从账户01003815868转10000元到01003813828账户中。

CALL  PROCEDURE TRANSFER(01003815868,01003813828,10000);

修改存储过程

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

删除存储过程

DROP  PROCEDURE 过程名()；

TSQL

DROP PROCEDURE 过程名；

函数

函数和存储过程类似，都是持久性存储模块。不同之处是函数必须指定返回的类型。
函数的定义语句格式

CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型>  
AS <过程化SQL块>;

函数的执行语句格式

CALL/SELECT 函数名 ([参数1,参数2,…]);

修改函数

ALTER FUNCTION 过程名1 RENAME TO 过程名2;--重命名一个函数
ALTER FUNCTION 过程名 COMPILE;--重新编译一个函数