数据库的完整性
- 数据的正确性
数据是符合现实世界语义，反映了当前实际状况的 - 数据的相容性
同一对象在不同关系表中的数据是符合逻辑的
数据的“完整性”和“安全性”是两个不同概念 - 数据的完整性
防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据
防范对象：不合语义的、不正确的数据 - 数据的安全性
保护数据库 防止恶意的破坏和非法的存取
防范对象：非法用户和非法操作
[例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),
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
ON UPDATE CASCADE,
FOREIGN KEY (Cno) REFERENCES Course(Cno)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
[例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),
…
);
[例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(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)，
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.%')
);
[例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元。
应发工资是工资列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 999999),
ALTER TABLE Student
DROP CONSTRAINT C3;
ALTER TABLE Student
ADD CONSTRAINT C3 CHECK(Sage < 40);
断言
- SQL中，可以使用 CREATE ASSERTION语句，通过声明性断言来指定更具一般性的约束。
- 可以定义比较复杂的完整性约束。
- 断言创建以后，任何对断言中所涉及的关系的操作都会触发DBMS对断言的检查，任何使断言不为真值的操作都会被拒绝执行
创建断言的语句格式
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)
);
删除断言的语句格式为
DROP ASSERTION <断言名>;
触发器（Trigger）
- 任何用户对表的增、删、改操作均由服务器自动激活相应的触发器
- 触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力
定义触发器
语法格式：
CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>
BEFORE | AFTER是触发的时机：AFTER表示在触发事件的操作执行之后激活触发器；
BEFORE表示在触发事件的操作执行之前激活触发器。
触发事件可以是INSERT、DELETE或UPDATE，也可以是这几个事件的组合；还可以UPDATE OF<触发列，…>，即进一步指明修改哪些列时激活触发器。
ROW | STATEMENT 行|段
触发器类型：
- 行级触发器（FOR EACH ROW）
- 语句级触发器（FOR EACH STATEMENT）
例如,在例5.11的TEACHER表上创建一个AFTER UPDATE触发器，触发事件是UPDATE语句：UPDATE TEACHER
SET Deptno=5; 假设表TEACHER有1000行： 如果是语句级触发器，那么执行完该语句后，触发动作只发生1次；
如果是行级触发器，触发动作将执行1000次。
[例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)
[例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;
写到这，逐渐意识到这周作业有些多
激活触发器
触发器的执行，是由触发事件激活的，并由数据库服务器自动执行！ 一个数据表上可能定义了多个触发器，遵循如下的执行顺序:
- 执行该表上的BEFORE触发器;
- 激活触发器的SQL语句;
- 执行该表上的AFTER触发器。
删除触发器
DROP TRIGGER <触发器名> ON <表名>;
存储过程：由过程化SQL语句，经编译和优化后存储在数据库服务器中，可以被反复调用，运行速度较快。
- 创建存储过程
CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>；
[例8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。
CREATE OR REPLACE PROCEDURE TRANSFER(inAccount INT,outAccount INT,amount FLOAT)
AS DECLARE /*定义变量*/
totalDepositOut Float;
totalDepositIn Float;
inAccountnum INT;
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;
ENDIF;
UPDATE Account SET total=total-amount WHERE accountnum=outAccount;
UPDATE Account SET total=total + amount
WHERE accountnum=inAccount;
COMMIT;
END;
开始头晕了…脑子乱起来了
- 执行存储过程
CALL/PERFORM PROCEDURE 过程名([参数1,参数2,...])；
[例8.9] 从账户01003815868转10000元到01003813828账户中。
CALL PROCEDURE TRANSFER(01003813828,01003815868,10000);
- 修改存储过程
ALTER PROCEDURE 过程名1 RENAME TO 过程名2;
- 删除存储过程
DROP PROCEDURE 过程名()；
函数
- . 函数的定义语句格式
CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型> AS <过程化SQL块>;
- 函数的执行语句格式
CALL/SELECT 函数名 ([参数1,参数2,…]);
- 修改函数
重命名
ALTER FUNCTION 过程名1 RENAME TO 过程名2;
重新编译
ALTER FUNCTION 过程名 COMPILE;
今天补作业补得要裂开了，看来下次得赶早了
扫一扫
3203
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
