首页 > 数据库 > 数据库其它

2021-04-25

admin 数据库其它 2021-04-26 15:59:17 sql  
后台-系统设置-扩展变量-手机广告位-内容正文底部

第五章例题

实体完整性

关系模型的实体完整性在 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)); --只能在表级定义主码

实体完整性检查和违约处理
(1) 检查主码值是否唯一,如果不唯一则拒绝插入或者修改。
(2) 检查主码的各个属性是否为空,只要有一个为空就拒绝插入或者修改。

参照完整性
关系模型的参照完整性在CREATE TABLE中用FOREIGN KEY 短语定义哪些列为外码,用 REFERENCES短语指明这些外码参照哪些表的主码。
例如,关系SC 中一个元组表示一个学生选修的某门课程的成绩,(Sno,Cno)是主码。Sno、 Cno分别参照引用Student表的主码和 Course表的主码。

例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 Studen(Sno)
on delete cascade --级联删除SC3表中相应的元组
on update cascade --级联更新SC3表中相应的元组
foreign key (Cno) references Course(Cno)
on delete on action --当删除course 表中的元组造成了与SC表不一致时拒绝删除
on update cascade --当更新course表中的cno时,级联更新SC表中相应的元组
);
可以对DELETE 和 UPDATE 采用不同的策略。例如,例5.4中当删除被参照表Course表中的元组,造成与参照表(SC表)不一致时,拒绝删除被参照表的元组;对更新操作则采取级联更新的策略。
从上面的讨论可以看到关系数据库管理系统在实现参照完整性时,除了要提供定义主码、外码 的机制外,还需要提供不同的策略供用户选择。

用户定义的完整性

1.属性上约束条件的定义
在CREATE TABLE中定义属性的同时,可以根据应用要求定义属性上的约束,即属性值约束,包括:
列值非空、列值唯一、检查列值是否满足一个条件表达式。
(1) 不允许取空值
例 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),
…
);

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

create table Dept
(Deptno numeric(2)
 Dname char(9) unique not null,
 --Dname 列值唯一 并且不能取空
 location char(10),
 primary lkey(Deptno)
 );

(3)用CHECK短语指定列值因该满足的条件
例5.7 Student表的Ssex只允许取“男”或“女”。

create 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),
--Grade 取值范围是0-100
primary key (Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key(Cno)references Course(Cno)
);

2 .属性上约束条件的检查和违约处理
当往表中擦汗如元组或修改属性的值时,关系数据库管理系统将检查属性上的约束条件是否被满足,如果不满足就拒绝执行操作。

元组上的约束条件

例5.9 当学生的性别是男时,其名字不能以Ms.打头。
当往表中插入元组或修改属性的值时,关系数据库管理系统将检查属性上的约束条件是否被满足,如果不满足则操作被拒绝执行。
与属性上约束条件的定义类似,在 CREATE TABLE语句中可以用CHECK短语定义元组上的约束条件,即元组级的限制。同属性值限制相比,元组级的限制可以设置不同属性之间的取值的相互约束条件。

create table Student4
( 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 C3 check (Sage<30),
Ssex char(2)
constraint C4 check(Ssex in('男','女')),
constraint StudentKey primary e=key(Sno)
);

在Student表上建立了5个约束条件,包括主码约束(命名为StudentKey)以及C1、C2、C3、C4四个列级约束。

例5.11 建立教师表 TEACHER,要求每个教师的应发工资不低于3 000元。应发工资是工资列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 去掉例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 99999);
alter table Student
     drop constraint C3;
alter table Student
     add constraint C3 check (Sage<40);

断言
创建断言的语句格式
CREATE ASSERTION<断言名><CHECK子句>
每个断言都被赋予一个名字,<CHECK子句>中的约束条件与WHERE子句的条件表达式类似。
//(T-SQL中没有这个功能)
例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)
);

触发器
触发器是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义,触发器将保存在数据库服务器中。任何对表的增删改操作均有服务器自动激活相应的触发器,在关系数据库管理系统核心层进行集中的完整性控制。

定义触发器

触发器又叫做 事件-条件-动作规则。
SQL是由CREATE TRIGGER命令创建触发器,其一般格式为
CREATE TRIGGER <触发器名 >
{
BEFOR|AFTER}<触发事件>ON <表名>
REFERENCING NEW|OLD ROW AS <变量>
FOR EACH {ROW|STATEMENT}
[WHEN<触发条件> ]<触发动作体>

例 5.21 当对表SC的grade属性进行修改时,若分数增加了10%,则将此次操作记录到另一个表SC_U(Sno,Cno,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)

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

for each statement
	insert into StudentInsertLog(Numbers)
	select count(*) 
	from delta

在本例中出现的for each statement,表示触发事件INSERT语句执行完成后才执行一次触发器中的动作,这种触发器叫做语句级触发器。而5.21中的触发器时行级触发器。默认的触发器时语句级触发器。

激活触发器
触发器的执行是由触发事件激活,并由数据库服务器自动执行的。一个数据表上可能有多个触发器,在同一个表上的触发器激活时遵循以下执行顺序:
(1)执行该表上的BEFORE触发器
(2)激活触发器SQL语句
(3)执行该表上的AFTER触发器
删除触发器
SQL语句为
DROP TRIGGER <触发器名>ON<表名>;
触发器必须是一个已创建的触发器,并且只能由具有相应权限的用户删除。

存储过程
存储过程是由过程化SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,因此称它为存储过程,使用时只要调用即可。
1.存储过程的有点
(1)由于存储过程不像解释执行的SQL语句那样在提出操作请求时才进行语法分析和优化工作,因而效率高。
(2)存储过程降低了客户机和服务器之间的通信量。
(3)方便实施企业规则。
2.存储过程的用户接口
(1)创建存储过程
CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,……])
AS <过程化SQl块>;
(2)执行存储过程
CALL/PERFORM PROCEDURE 过程名([参数名1,参数名2……]);
使用CALL或者PERFORM等方式激活存储过程的执行。在过程化SQL中,数据库服务器支持在过程体中调用其他存储过程。
(3)修改储存过程
可以使用ALTER PROCEDURE 重命名一个存储过程。
ALTER PROCEDURE 过程名1 RENAME to 过程名2;
可以使用ALTER PROCEDURE 重新编译一个存储过程。
ALTER PROCEDURE 过程名 COMPILE;
(4)删除存储过程
DROP PROCEDURE 过程名();

文章来源:https://blog.csdn.net/qq_45993036/article/details/116127442

后台-系统设置-扩展变量-手机广告位-内容正文底部
版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
本文地址:https://jcdi.cn/shujuku/c92bcea32623ceb951181e28c8519727.html

留言与评论(共有 0 条评论)
   
验证码:
后台-系统设置-扩展变量-手机广告位-评论底部广告位

教程弟

https://www.jcdi.cn/

统计代码 | 京ICP1234567-2号

Powered By 教程弟 教程弟

使用手机软件扫描微信二维码