SQL Data Type BOOLEAN CHAR VARCHAR NUMERIC or
SQL基本数据类型 • Data Type • BOOLEAN • CHAR • VARCHAR • NUMERIC or DECIMAL 数值型 NUMERIC(7, 2) • INTEGER Integer • SMALLINT 小整型 • FLOAT or REAL 浮点数. • DATE 日类型 • TIME 时间类型 • TIMESTAMP 时间戳 • CHARACTER LARGE OBJECT SQL 3 文本大对象 • BINARY LARGE OBJECT Description 布尔类型 TRUE/FALSE 固定长度的字符串，如 CHAR(10) 变长字符串，如 VARCHAR(50) SQL 3 二进制大对象
表模式定义语句例子 • CREATE TABLE Emp ( eno CHAR(5), ename VARCHAR(30) NOT NULL, birthday DATE, title CHAR(2) CHECK (title IN (NULL, 'EE', 'SA', 'PR', 'ME')), salary DECIMAL(9, 2), superno CHAR(5), dno CHAR(5) PRIMARY KEY (eno) FOREIGN KEY (dno) REFERENCES Dept(dno) ON DELETE SET NULL ON UPDATE CASCADE FOREIGN KEY (superno) REFERENCESEmp(eno)
引用完整性约束所指的一个方面 Student Name student. No sex birthdate Mike 2008043101 male 1990/12/14 Tom 2008043214 female 1992/02/21 Mary 2008043332 female 1988/07/09 在Student表中的某一行记录： 1）其主关键字student. No被修改； 2）被删除； 3）被插入； Enroll student. No course. No semester grade 2008043332 430803 2010/01 90 2008043101 430317 2009/01 56 2008043101 430317 2008/02 56 2008043332 430007 2004/02 77 2008043332 430601 2000/01 87 2008043101 430317 2002/02 56 在Enroll表中对应 的记录怎么办？
引用完整性约束 Student Name student. No 在Student表中的某一行记录： sex birthdate Mike 2008043101 male 1990/12/14 Tom 2008043214 female 1992/02/21 2）删除某一行记录； Mary 2008043332 female 1988/07/09 3）插入一行新记录； 1）修改的其主关键字student. No； Enroll student. No course. No semester grade 2008043332 430803 2010/01 90 2008043101 430317 2009/01 56 2008043101 430317 2008/02 56 2008043332 430007 2004/02 77 2008043332 430601 2000/01 87 2008043101 430317 2002/02 56 1) CASCADE 2) SET NULL 3) NO ACTION
引用完整性约束所指的另一个方面 Student Name student. No sex birthdate Mike 2008043101 male 1990/12/14 Tom 2008043214 female 1992/02/21 Mary 2008043332 female 1988/07/09 在Enroll表中: l 添加一行记录； l 某行记录的外建字段 student. No的值被修改 Enroll student. No course. No semester grade 2008043332 430803 2010/01 90 2008043101 430317 2009/01 56 2008043101 430317 2008/02 56 2008043332 430007 2004/02 77 2008043332 430601 2000/01 87 2008043101 430317 2002/02 56 l enroll表中行记录的外建字 段student. No的值在 Student中要存在；
表模式创建例子 CREATE TABLE Enroll ( sno CHAR(12) NOT NULL, cno CHAR(8) NOT NULL, semester CHAR(7), tno CHAR(7), class. No CHAR（2）； grade SMALLINT PRIMARY KEY (sno, cno, semester) 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 FOREIGN KEY (tno) REFERENCES teacher(to) ON DELETE SET NULL ON UPDATE CASCADE );
课堂练习 Course Name course. No credit hours textbook syllabus dtuy_tno database A 240608 4 64 a introduction 2004213 24 operating system 430803 4 64 b all 2001007 24 java 430317 3 48 c section null 24 写出Course表的模式定义语句
使用触发器实现比较后两类业务规则约束（ 相对复杂） 触发器表： Table Name Table Type Operation Type Field Name Trigger Type Trigger Name Trigger Code enroll table INSERT null BEFORE enroll_i nsert . . teacher table UPDATE salary AFTER salary_ update . . staff view INSERT null INSTEAD OF transfer _insert staff view DELETE null INSTEAD OF transfer _delete
使用触发器实现每学期选课不超过25学分 CREATE TRIGGER trigger_insert. Enroll BEFORE INSERT ON enroll REFERENCING NEW ROW AS new FOR EACH ROW BEGIN credit integer SELECT SUM(credit) INTO credit FROM course WHERE course. Id IN (SELECT course. Id FROM enroll WHERE student. Id = @new. student. Id AND semester =@new. semester) OR course. Id = @new. course. Id; WHEN ( credit > 25 ) raise_application_error(20000, '这个学期已经选修学分' + @credit); END;
实现每个房间的预订不重叠冲突 CREATE TRIGGER trigger_insert. Booking BEFORE INSERT ON booking REFERENCING NEW ROW AS new FOR EACH ROW BEGIN WHEN( EXIST SELECT hotel. No, room. No FROM booking WHERE hotel. No = newrow. hotel. No AND room. No =@new. room. No AND date. To >=@ new. date. From AND date. From <= @new. date. To) raise_application_error(20000, '这个预订与已有预订有重叠'); END;
实现对视图执行更新操作 CREATE VIEW staff (deptname, e_no, name, job, birthday) AS SELECT dname, e_no, emp. name, title, bdate FROM emp, dept WHERE emp. d_no = dept. dno AND Dept. Name = ‘Accounting';
实现对视图执行更新操作 CREATE TRIGGER trigger_insert. Emp INSTEAD OF INSERT ON staff REFERENCING NEW ROW AS new FOR EACH ROW WHEN (EXISTS (SELECT dno FROM dept WHERE dname = @new. deptname)) BEGIN String deptno SELECT dno INTO deptno FROM dept WHERE dname = @new. deptname INSERT INTO Emp VALUES (@new. e_no, @new. name, @new. job, @new. birthday, 0, @deptno); END；
使用触发器来执行审计 CREATE TRIGGER cheating. Employee AFTER UPDATE OF salary ON Emp REFERENCING OLD ROW AS old NEW ROW AS new FOR EACH ROW WHEN (@new. salary > @old. salary*1. 1) INSERT INTO audit. Emp VALUES (@new. eno, date(now()), @new. salary, @old. salary); 事件 条件 动作
删除或者修改中的一个对象 • DROP TABLE emp; • ALTER TABLE emp ……. . ; • DROP VIEW staff; • ALTER VIEW staff ……. . ; • DROP PROCEDURE add_enroll; • ALTER PROCEDURE add_enroll; • DROP TRIGGER staff_insert; • ALTER TRIGGER staff_insert;
- Slides: 25