第五章例题

最新推荐文章于 2024-07-18 18:43:19 发布

fanlrr

最新推荐文章于 2024-07-18 18:43:19 发布

阅读量1.9k

点赞数 4

文章标签：数据库

本文链接：https://blog.csdn.net/fanlrr/article/details/115839170

版权

数据库的完整性

实体完整性
参照完整性
用户定义的完整性
- 元组上的约束条件
完整性约束命名子句
断言
触发器
- 定义触发器
删除触发器
存储过程和函数
- 存储过程
- 函数

实体完整性

例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.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),
--表级完整性约束条件，Sno是外码，Student是被参照表
FOREIGN KEY(Cno)REFERENCES Course(Cno)
--表级完整性约束条件，Cno是外码，Course是被参照表
);

在这里插入图片描述
例5.4 显示说明参照完整性的违约处理示例
标准SQL语句下：

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表中的Sno时，级联更新SC表中相应的元组
FOREIGN KEY(Cno)REFERENCES Course(Cno)
ON DELETE NO ACTION --当删除Course表中的元组造成与SC表不一致时，拒绝删除
ON UPDATE CASCADE   --当更新Course表中的Cno时，级联更新SC表中相应的元组

用户定义的完整性

例5.6 在定义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));

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

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

在这里插入图片描述
例5.7 Student表的Ssex只允许取“男”或“女”

CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) 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)NOT NULL,
Cno CHAR(4)NOT NULL,
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(20) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20),
PRIMARY KEY(Sno),
CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%'))
;

完整性约束命名子句

完整性约束命名子句：

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

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

CREATE TABLE Student2
(Sno NUMERIC(6) CONSTRAINT SSNO CHECK(Sno BETWEEN 90000 AND 99999),
Sname CHAR(20) CONSTRAINT SSNAME NOT NULL,
Sage NUMERIC(3) CONSTRAINT SSAGE CHECK(Sage<30),
Ssex CHAR(2) CONSTRAINT SSSEX CHECK(Ssex IN('男','女')),
CONSTRAINT STUDENTKEY PRIMARY KEY (Sno));

在这里插入图片描述
例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 SSUM CHECK(Sal+Deduct>=3000));

在这里插入图片描述
2.修改表中的完整性限制
例5.12 去掉例5.10Student2表中对性别的限制

ALTER TABLE Student2
DROP CONSTRAINT SSSEX;

在这里插入图片描述
例5.13 修改表Student2中的约束条件，要求学号改为在900000~999999之间，年龄有小于30改为小于40
需要先删除原来的约束条件，再增加新的约束条件

ALTER TABLE Student2
DROP CONSTRAINT SSNO;
ALTER TABLE Student2
ADD CONSTRAINT SSNO CHECK(Sno BETWEEN 900000 AND 999999);
ALTER TABLE Student2
DROP CONSTRAINT SSAGE;
ALTER TABLE Student2
ADD CONSTRAINT SSAGE CHECK (Sage<40);

在这里插入图片描述

断言

断言语句格式：

CREATE ASSERTION<断言名><CHECK子句>

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

例5.20 限制每个学期每一门课程最多60名学生选修

alter TABLE SC ADD TERM DATE; --修改SC表增加TERM属性，类型是DATE
CREATE ASSERTION ASSE_SC_CNUM2
CHECK(60>=ALL(SELECT COUNT(*)
FROM SC GROUP BY Cno,TERM));

删除断言语句格式：

DROP ASSERTION <断言名>

注意：T-SQL 中没有 ASSERTION 功能。

类似的有RULE，但使用方法不同：

上下文中不允许使用子查询，只允许使用标量表达式。
总结：又熟悉了一遍建表，以及更加系统的建表流程，over，乏了乏了。。。

触发器

定义触发器

触发器又叫做事件-条件-动作规则。当特定的系统事件（如对一个表的增。删、改操作，事务的结束等）发生时，对规则中的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。
触发器的一般格式为：

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

出发事件可以是：INSERT、DELETE、UPDATE
例5.21 当对表SC的Grade属性进行修改时，若分数增加了10%，则将此次操作记录到另一张表SC_U（Sno，Cno，Oldgrade，Newgrade）上，其中Oldgrade是修改前的分数，Newgrade是修改后的分数
标准SQL语句：

IF(OBJECT_ID('SC_T') is not null)        -- 判断名为 Student_Count 的触发器是否存在
DROP TRIGGER SC_T        -- 删除触发器
GO
CREATE TRIGGER SC_T           --SC.T触发器名字
AFTER UPDATE OF Grade ON SC   --UPDATE OF Grade ON SC 触发事件
                              --AFTER是触发时机
REFERENCING OLDROW AS OLDTUPLE,NEWROW AS NEWTUPLE --REFERENCING指出引用变量，但是T-SQL并不支持
FOR EACH ROW    --行级触发器，每执行一次Grade的更新，下面规则就执行一次
WHEN (NEWTUPLE.Grade>=1.1*OLDTUPLE.Grade)  --触发条件
INSERT INTO SC_U(Sno,Cno,OLDGRADE,NEWGARADE)   --INSERT操作
VALUES(OLDTUPLE.Sno,OLDTUPLE.Cno,OLDTUPLE.Grade,NEWTUPLE.Grade)

T-SQL 语句(参考）：
~~先执行创建表格~~：

CREATE TABLE SC_U
(Sno CHAR(9),
Cno CHAR(4),
OLDGRADE SMALLINT,
NEWGRADE SMALLINT);

~~再添加触发器（因为触发器这傲娇的小东西非要再第一个进行执行）：~~我又试了一次，好像不用创建表格，但我不信

CREATE TRIGGER SC_T 
ON SC 
AFTER UPDATE
AS 
BEGIN SET NOCOUNT ON;
INSERT INTO SC_U(Sno,Cno,OLDGRADE,NEWGRADE)
SELECT Sno,Cno,Grade,GETDATE() FROM INSERTED
END
;

在这里插入图片描述

不得不说，标准SQL和T-SQL 的差别不是一般的大啊！！！
注：inserted 里的是准备增加的元组，deleted是准备删除的元组，一切的开始就是referencing SQLserver不支持，没得洗，告诉我，谁的锅
然后我发现好像用不了，是因为getdate返回date类型无法向smallint进行转换，而且我找不到相应的内置函数，所以我不得不重新编写，然后。。。

IF(OBJECT_ID('SC_T') IS NOT NULL)        -- 判断名为 Student_Count 的触发器是否存在
DROP TRIGGER SC_T        -- 删除触发器
GO
CREATE TRIGGER SC_T
ON SC
AFTER UPDATE
AS
BEGIN
DECLARE @Sno CHAR(9),@Cno CHAR(4),@OLDGRADE SMALLINT,@NEWGRADE SMALLINT
SELECT @Sno=Sno FROM INSERTED
SELECT @Cno=Cno FROM INSERTED
SELECT @OLDGRADE=GRADE FROM DELETED
SELECT @NEWGRADE=GRADE FROM INSERTED
IF(@NEWGRADE>=1.1*@OLDGRADE)
BEGIN
INSERT INTO SC_U
VALUES(@Sno,@Cno,@OLDGRADE,@NEWGRADE)
END;
END;

检验：
在这里插入图片描述
成功了

例5.22 将每次对表Student的插入操作所增加的学生个数记录到表Student-InsertLog中
标准SQL

CREATE TRIGGER Student_CNT
AFTER INSERT ON Student
REFERENCING 
NEW TABLE AS DELTA
FOR EACH STATEMENT
INSERT INTO Student StudentINSERTLOG(NUMBERS)
SELECT CNT(*)FROM DELTA

标准SQL常规操作罢了。。。
在这里插入图片描述

T-SQL：
欸，得先建个表：

CREATE TABLE StudentInsertLog
(
Numbers INT
)

IF(OBJECT_ID('Student_Count') IS NOT NULL)        -- 判断名为 Student_Count 的触发器是否存在
DROP TRIGGER Student_Count        -- 删除触发器
GO

CREATE TRIGGER Student_CNT
ON Student  	         
AFTER
INSERT
AS 
    INSERT INTO StudentInsertLog(Numbers)
	  SELECT COUNT(*) FROM Student

在这里插入图片描述
检测：

INSERT
INTO  Student
VALUES ('201215136','王五1','男',18,'CS');

SELECT * FROM Student
SELECT * FROM StudentInsertLog

在这里插入图片描述
然后加一个时间序列：
对于这个@***定义一个变量来进行构造看起来比较简单的构造触发器，然后我返回第一例题在设计一回

CREATE TABLE StudentInsertLogUser
(
  UserName nchar(10),
  DateAndTime datetime
)

IF(OBJECT_ID('Student_Time') is not null)        -- 判断名为 Student_Count 的触发器是否存在
DROP TRIGGER Student_Time        -- 删除触发器
GO

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) --2018-04-11 16:33:10

	INSERT INTO StudentInsertLogUser(UserName,DateAndTime)
	VALUES (@UserName,@DateTime)

在这里插入图片描述

例5.23 定义一个BEFORE行级触发器，为教师Teacher定义完整性规则“教师的工资不得低于4000元，如果低于4000元，自动改为4000元
标准SQL：

CREATE TRIGGER INSERT_OR_UPDATE_SAL  --对教师表插入或更新时激活触发器
BEFORE INSERT OR UPDATE ON TEACHER    --before 触发事件
REFERENCING NEW ROW AS NEWTUPLE      
FOR EACH ROW             --行触发器
BEGIN                         --定义触发动作体，这是一个PL/SQL过程块
IF(NEWTUPLE.Job='教授')AND (NEWTUPLE.SAL<4000)
THEN NEWTUPLE.SAL:=4000;   --因为是行触发器，可在过程体中
END IF;
END;                       --触发动作结束

在这里插入图片描述

T-SQL：

CREATE TRIGGER INSERT_OR_UPDATE_SAL
ON TEACHER                       
AFTER INSERT,UPDATE     
AS
UPDATE TEACHER SET Sal=4000 WHERE Job='教授' AND SAL<4000;

在这里插入图片描述
额。。before好像用不了，但是这次编的语句没错。。。

删除触发器

语句

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

《论从课本只提供0.3知识我自己创造0.7的T-SQL触发器语言之路》
在这里插入图片描述

存储过程和函数

存储过程

1.创建存储过程

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

例8.9 利用存储过程实现下面的应用：从账户1转指定数额的款项到账户2中，假设账户关系表为ACCOUNT(ACCOUNTNUM,TOTAL)
建立一个表用来检验和使用：

CREATE TABLE T_RE
(ANUM CHAR(3), --账户编号
TOTAL FLOAT);  --账户余额
INSERT INTO T_RE VALUES(101,200),(102,300);
SELECT * FROM T_RE;

在这里插入图片描述

CREATE OR REPLACE PROCEDURE TRANSFER
(INACCOUNT INT,OUTACCOUNT INT,AMOUNT FLOAT)
AS DECLARE
TOTALDEPOSITOUT FLOAT;
TOTALDEPOSITIN FLOAT;
INACCOUNTNUM INT;
BEGIN
SELECT TOTAL INTO TOTALDEPOSITOUT
FROM ACCOUNT 
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=IN ACCOUNT；
IF INACCOUNT IS NULL THEN
ROLLBACK;
RETURN;
END IF;
UPDATE ACCOUNT SET TOTAL=TATAL-AMOUNT WHERE ACCOUNTNUM=OUTACCOUNT;
UPDATE ACCOUNT SET TOTAL=TATAL+AMOUNT WHERE ACCOUNTNUM=INACCOUNT;
COMMIT;
END;

在这里插入图片描述

然后写T-SQL

IF(EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='P_T'))
DROP PROCEDURE P_T
GO
CREATE PROCEDURE P_T
@INID CHAR(3),@OUTID CHAR(3),@AMOUNT FLOAT
AS
BEGIN
TRANSACTION TRANS
DECLARE @TOTALOUT FLOAT,@TOTALIN FLOAT,@OUTIDNUM CHAR(3);   --查询转出账户余额
SELECT @TOTALOUT=TOTAL FROM T_RE WHERE ANUM=@OUTID;
IF(@TOTALOUT IS NULL) BEGIN PRINT'账户不存在或者无余额'
ROLLBACK TRANSACTION TRANS;RETURN ; END; --回滚事务
IF(@TOTALOUT<@AMOUNT)BEGIN PRINT'账户余额不足' 
ROLLBACK TRANSACTION TRANS;RETURN;END;
SELECT @TOTALIN=ANUM FROM T_RE WHERE ANUM=@INID;
IF(@TOTALIN IS NULL) BEGIN PRINT'转入账户不存在'
ROLLBACK TRANSACTION TRANS;RETURN;END;
BEGIN
UPDATE T_RE SET TOTAL=TOTAL-@AMOUNT WHERE ANUM=@OUTID;
UPDATE T_RE SET TOTAL=TOTAL+@AMOUNT WHERE ANUM=@INID;
PRINT'请取走银行卡'
COMMIT TRANSACTION TRANS;
RETURN;
END;

检验：

SELECT * FROM T_RE;
EXEC	P_T
		@INID = 101,	--转入账户
		@OUTID = 102,	--转出账户
		@amount = 50		--转出金额

SELECT * FROM T_RE;

在这里插入图片描述

2.执行存储过程

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

例8.10 从账户01转100元到02账户中

CALL PROCEDURE TRANSFER (01,02,100);

3.修改存储过程
使用ALTER PROCEDURE 重命名一个存储过程

ALTER PROCEDURE 过程1 RENAME TO 过程2；

可以使用ALTER PROCEDURE重新编译一个存储过程

ALTER PROCEDURE 过程名 COMPILE

4.删除存储过程

DROP PROCEDURE 过程名();

函数

函数的定义语句格式

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

函数的执行语句格式

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

修改函数
可以使用ALTER FUNCTION重命名一个自定义函数；

ALTER FUNCTION 过程名1 RENAME TO 过程名2；

可以使用ALTER FUNCTION重新编译一个函数

ALTER FUNCTION 函数名 COMPILE;

总结：来来回回改了七八遍，整完了，感触就是累，无数的资料查不完，还老出错，摸索中前进，还老碰壁，也就是这样，这部分应该问题不大了（当且仅限于实际操作，书上理论的语句看的脑阔疼，没有T-SQL好看）。。。

fanlrr

关注

4
点赞
踩
19

收藏

觉得还不错? 一键收藏
2
评论
第五章例题

数据库的完整性实体完整性参照完整性用户定义的完整性元组上的约束条件完整性约束命名子句实体完整性例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
复制链接

扫一扫