HNU_数据库_实验4（2021级）-触发器实验

实验四 触发器实验

0.LAB4-SQL语句汇总

CREATE DATABASE ST;
CREATE TABLE student(   /*学生信息表*/
/*    Sno CHAR(9) PRIMARY KEY,*/
	Sno CHAR(9),
    Sname CHAR(20) UNIQUE,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20),
	PRIMARY KEY(Sno)
);
ALTER TABLE Student ADD CONSTRAINT age_check1 CHECK(Sage<50);
CREATE TABLE Course(    /*课程信息表*/
/*    Cno CHAR(9) PRIMARY KEY,*/
	Cno CHAR(9),
    Cname CHAR(20) NOT NULL,
    Cpno CHAR(9) NULL,
    Ccredit SMALLINT DEFAULT 4
   # FOREIGN KEY(Cpno) REFERENCES Course(Cno)
);  
ALTER TABLE Course ADD PRIMARY KEY(Cno);
/*Cpno参照于Cno*/
ALTER TABLE Course ADD CONSTRAINT FK_Course_cpno
	FOREIGN KEY(Cpno) REFERENCES Course(Cno);

CREATE TABLE SC(        /*选课信息表*/
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno)
#    FOREIGN KEY(Sno) REFERENCES Student(Sno),
#   FOREIGN KEY(Cno) REFERENCES Course(Cno)
);  
ALTER TABLE SC
	ADD CONSTRAINT SC_s1 FOREIGN KEY (Sno) REFERENCES Student(Sno),
	ADD CONSTRAINT SC_c2 FOREIGN KEY (Cno) REFERENCES Course(Cno) ON UPDATE CASCADE;

ALTER TABLE SC DROP CONSTRAINT SC_c2;

# 查询学生的姓名，性别和年龄
SELECT Sname,Ssex,Sage FROM student;

# 查询信息科学与工程学院所有男生的所有信息
SELECT * FROM student WHERE Sdept='cs' AND Ssex='男';

# 查询每个学生的课程门数
SELECT Sno,count(Cno) FROM sc group by Sno; 
SELECT cno,COUNT(*) FROM sc group by Sno; # 报错
SELECT S.Sno,Count(Cno) FROM STUDENT S,SC WHERE SC.Sno=S.Sno GROUP BY S.Sno;

# 查询课程数大于2的学生学号,姓名,选课数量
SELECT S.Sno,Sname,count(Cno) FROM student S,sc WHERE SC.Sno=S.Sno GROUP BY S.Sno HAVING count(Cno)>2;

# 查询与学号为200215121的学生修读相同课程的学生学号
SELECT DISTINCT sc2.Sno FROM SC sc1,SC sc2 WHERE sc1.Sno='200215121' AND sc1.Cno=sc2.Cno;

# 查询选修了课程号为1的学生的学生信息
SELECT * FROM student,sc WHERE sc.Cno='1' AND student.Sno=sc.Sno;
SELECT student.*,Cno,Grade FROM student,sc WHERE sc.Cno='1' AND student.Sno=sc.Sno;

# 查询"李勇"修读的所有课程的课程编号，课程名，课程学分
SELECT course.Cno,course.Cname,course.Ccredit FROM student,sc,course 
	WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND sc.Cno=course.Cno;
    
# 查询选择了学分为4的课程的学生
SELECT Sno,Sname FROM student WHERE Sno IN(
	SELECT Sno FROM sc WHERE Cno IN(
		SELECT Cno FROM course WHERE Ccredit=4
    )
);

# 查询每个学生大于等于他自己选修课程平均成绩的课程号
SELECT Sno,Cno FROM sc WHERE Grade>=(
	SELECT AVG(Grade) FROM sc sc1 WHERE sc.Sno=sc1.Snocustomer
);

# 查询非计算机科学系比计算机科学系中任意一个学生年龄小的学生姓名和年龄
SELECT Sname,Sage FROM student WHERE Sdept!='CS' AND Sage < ANY(
	SELECT Sage FROM student s1 WHERE Sdept='CS'
);
# 查询非计算机科学系比计算机科学系中所有一个学生年龄小的学生姓名和年龄
SELECT Sname,Sage FROM student WHERE Sdept!='CS' AND Sage < ALL(
	SELECT Sage FROM student s1 WHERE Sdept='CS'
);

# 查询没有修读数学课程的IS专业的学生：
SELECT Sno,Sname FROM student WHERE  Sdept='IS' and
	NOT EXISTS(
	SELECT sc.Sno FROM sc,course WHERE sc.Cno=course.Cno AND course.Cname='数学' AND Student.Sno=SC.Sno
);

# 查询修读了所有"刘晨"修读的课程的学生 
/*查找一个学生，不存在刘晨修读而他没有修读的课程*/
SELECT Sno,Sname FROM student s WHERE
	NOT EXISTS(
		SELECT * FROM sc,student s1 WHERE sc.Sno=s1.Sno AND s1.Sname='刘晨' AND
        NOT EXISTS(
			SELECT * FROM sc sc1 WHERE sc1.Cno=sc.Cno AND sc1.Sno=s.Sno
		)
);

# 查询修读课程平均成绩超过80的计科专业的学生
SELECT S.*,t.avg_grade FROM student s,(SELECT Sno,AVG(GRADE) avg_grade FROM sc GROUP BY sc.Sno) t 
	WHERE s.Sno = t.Sno AND s.Sdept='CS' AND t.avg_grade>80;
    
# 查询"刘晨"和"李勇"都修读过的课程的信息。
SELECT course.* FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno
INTERSECT
SELECT course.* FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno;
#使用嵌套查询替代INTERSECT语句进行以上查询如下：
SELECT course.* FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno
	AND course.CNO IN (SELECT course.Cno FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno);
    
# 查询"刘晨"和"李勇"修读过的课程的信息
SELECT course.* FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno
UNION
SELECT course.* FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno;

# 查询"李勇"修读过而"刘晨"没有修读过的课程信息。MYSQL不支持EXCEPT语句，SQL语句使用EXCEPT进行查询的方式如下：
SELECT course.* FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno
EXCEPT
SELECT course.* FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno;
#使用嵌套查询替代EXCEPT语句进行查询如下：
SELECT course.* FROM sc,student,course WHERE student.Sname='李勇' AND student.Sno=sc.Sno AND course.Cno=sc.Cno
	AND course.Cno NOT IN(SELECT course.Cno FROM sc,student,course WHERE student.Sname='刘晨' AND student.Sno=sc.Sno AND course.Cno=sc.Cno);
    
# 不指明属性列，按照顺序插入单个元组
INSERT INTO student VALUES('202103008','段富钊','男',21,'CS');
SELECT * FROM student;
# 指明属性列，没有赋值的属性列将自动赋空值
INSERT INTO sc(Sno,Cno) Values('202103008',5);
SELECT * FROM sc;

# 对每一个课程，求课程的平均成绩，并把结果存入数据库。
CREATE TABLE C_avg_grade(
	Cno Char(9),
    avg_grade SMallINT
);
INSERT INTO C_avg_grade SELECT Cno,AVG(GRADE) FROM SC GROUP BY Cno;
SELECT * FROM C_avg_grade;

# 将学生"段富钊"的年龄改为81岁
SET sql_safe_updates = false;
UPDATE Student SET Sage=81 WHERE Sname='段富钊';
SELECT * FROM student;
#将所有学生的年龄+1
UPDATE student SET Sage=Sage+1;
SELECT * FROM student;

# 将计算机科学系学生的成绩清0。
UPDATE sc SET Grade=0 WHERE Sno IN(
	SELECT Sno FROM student WHERE Sdept='CS'
);
SELECT * from sc;

# 删除学号为"202103008"的学生的选课记录
DELETE FROM sc where sno='202103008';
select * from sc;

# 删除所有选课记录
DELETE from sc;
select * from sc;

# 删除计算机科学系学生的选课记录
DELETE FROM sc WHERE sno in(
	select sno from student where sdept='cs'
);
select * from sc;

# 省略视图列名，建立计科专业学生的视图，该视图由单个表导出，且保留了主码
create view cs_student as 
	select sno,sname,sage,sdept from student where sdept='cs';
select *  from cs_student;

# 建立学号和平均成绩的视图，这种视图带有聚集函数和GROUP BY子句查询
create view s_g(sno,avg_grade) as
	select sno,AVG(grade) from sc group by sno;
select * from s_g;

# 使用WITH CHECK OPTION，建立计科专业学生的视图。
create view cs_student1 as 
	select sno,sname,sage,sdept from student where sdept='cs'
    with check option;
select *  from cs_student1;

/*不满足Sdept="CS"，CHECK OPTION FAILED，不能增加*/
INSERT INTO cs_student1 values('202103001','张飞',28,'EE');
INSERT INTO cs_student1 values('202103001','张飞',28,'cs');
select * from student;
delete from cs_student1 where sname='张飞';
select * from student;
/*不满足CHECK OPTION的修改*/
UPDATE cs_student1 set sdept='ee' where sname='段富钊';
UPDATE cs_student1 set sage=19 where sname='段富钊';
select * from student;

# 建立机械专业学生的视图。并对其进行更新
create view me_student as
	select sno,sname,sage from student where sdept='me';
select * from me_student;
insert into me_student values('201802056','陈真',24);
select * from student;
update me_student set sage=80 where sname='陈真';
delete from me_student where sname='陈真';

/*不可以对该视图进行更新*/
INSERT INTO S_G VALUES("201714021",80);


/*创建选课明细视图V_SC*/
create view v_sc as
	select s.sno,s.sname,c.cname,sc.grade from student s,course c,sc where s.sno=sc.sno and c.cno=sc.cno;
select * from v_sc;
/*创建学生平均成绩视图V_avgGrade*/
create view v_avgGrade(sno,sname,avg_grade) as
	select sno,max(sname),avg(grade) from v_sc group by sno;
select * from v_avgGrade;
DROP view v_sc restrict;
DROP view v_sc cascade;

# 在学生表建立学生姓名的唯一索引
CREATE UNIQUE INDEX student_name ON student(Sname);
show index from student;

# 在选课表的学号和课号两个字段上创建一个复合索引
CREATE UNIQUE INDEX idx_sno_cno ON SC(Sno,Cno);
SHOW INDEX FROM SC;

# 在选课表上创建一个平均成绩索引
CREATE UNIQUE INDEX idx_avg_grade ON SC((Cno*Grade));
show index from sc;

# 在课程表的课号字段上创建一个聚簇索引
CREATE UNIQUE CLUSTER INDEX idx_cluster ON Course(Cno);
show index from course;
CLUSTER idx_cluster ON Course;

# 在课程表的名称字段上创建一个Hash索引
CREATE UNIQUE INDEX idx_hash USING HASH ON Course(Cname);
show index from course;

# 修改课程表的名称字段的索引名
ALTER TABLE Course RENAME INDEX idx_hash TO idx_hash_alter;
SHOW INDEX FROM Course;

# 分析某个SQL查询语句执行时是否使用了索引
EXPLAIN SELECT * FROM Course where Cno='1';

use st;
# 创建一个TestIndex，计算SQL查询执行的时间
/*开启了bin-log, 需要制定函数类型*/
SET GLOBAL log_bin_trust_function_creators = 1;
/*delimiter // 的意思是把 // 当作结束符，mysql在编译的时候就不会因为语句中有多个符号而报错。*/
delimiter //
CREATE 
FUNCTION TestIndex(name CHAR(55)) RETURNS INTEGER
BEGIN
	DECLARE begintime,endtime TIMESTAMP(3);
	DECLARE durationtime,result INTEGER;
	SELECT current_timestamp(3) INTO begintime;
	SELECT Cno FROM Course WHERE Cno=name INTO result ;
	SELECT current_timestamp(3) INTO endtime;
	select TIMESTAMPDIFF(MICROSECOND,begintime,endtime) INTO durationtime;
	RETURN durationtime;
END//
delimiter ;
/*查看当课程表Course数据规模比较小，并且无索引时的执行时间*/
SELECT TestIndex('1');

INSERT INTO Course SELECT Cno + 100*(SELECT COUNT(*) FROM Course),Cname,Cpno,Ccredit FROM Course;
select * from course;
select * from Course where Cname="数据库";

# 复合索引和多个简单索引的不同
#创建一个组合索引和一个单个索引
CREATE INDEX combined_idx ON Student(Sdept,Sname);
CREATE INDEX single_idx ON Student(Sdept);
#查询
EXPLAIN SELECT * FROM student WHERE Sdept='CS' AND Sname = '段富钊';
EXPLAIN SELECT * FROM student WHERE  Sdept = 'CS';
#再建一个单独索引
CREATE INDEX single_idx2 ON Student(Sname);
EXPLAIN SELECT * FROM student WHERE  Sname = '段富钊';


# lab4 触发器实验
# 在student表上定义一个UPDATE触发器，修改学生年龄后，就自动在年龄变换表Sage_log中增加一条相应记录
# 创建学生年龄变化表Sage_log
CREATE TABLE Sage_log(
	Sno char(9) REFERENCES Student(Sno),
    Sage int,
    Username char(45),
    Date timestamp
);
# 触发事件
DELIMITER $
CREATE TRIGGER UPDATE_Sage
AFTER UPDATE ON Student
FOR EACH ROW
BEGIN
	IF(new.Sage<>old.Sage) THEN INSERT INTO Sage_log
		VALUES(new.Sno,new.Sage,CURRENT_USER,CURRENT_TIMESTAMP);
    END IF;
END$
DELIMITER ;
# 测试
SET sql_safe_updates = false;
UPDATE Student SET Sage=Sage+1;
SELECT * FROM st.sage_log;

# 在student表上定义一个INSERT触发器，插入新学生后，就自动在学生插入表Student_insert_log中增加一条相应记录。
# 创建学生插入表Student_insert_log
CREATE TABLE Student_insert_log(
	Sno char(9),
    Sname CHAR(20),
    Ssex CHAR(2),
    Sage INT,
    Sdept CHAR(20),
    Username char(45),
    Date timestamp
);
# 触发事件
DELIMITER $
CREATE TRIGGER INSERT_STUDENT_Sage
AFTER INSERT ON Student
FOR EACH ROW
BEGIN
	INSERT INTO Student_insert_log
		VALUES(new.Sno,new.Sname,new.Ssex,new.Sage,new.Sdept,CURRENT_USER,CURRENT_TIMESTAMP);
END$
DELIMITER ;
# DROP TRIGGER INSERT_STUDENT_Sage;
# 测试
INSERT INTO student VALUES ('202103007', '张麻子', '男', 35, 'GM');
SELECT * FROM Student_insert_log;

# 在student表上定义一个DELETE触发器，在student表中删除一个学生后，就自动在学生插入表Student_delete_log中增加一条相应记录。
# 创建表Student_delete_log:
CREATE TABLE Student_delete_log(
	Sno char(9),
    Sname CHAR(20),
    Ssex CHAR(2),
    Sage INT,
    Sdept CHAR(20),
    Username char(45),
    Date timestamp
);
# 触发事件
DELIMITER $
CREATE TRIGGER DELETE_STUDENT_Sage
AFTER DELETE ON Student
FOR EACH ROW
BEGIN
	INSERT INTO Student_delete_log
		VALUES(old.Sno,old.Sname,old.Ssex,old.Sage,old.Sdept,CURRENT_USER,CURRENT_TIMESTAMP);
END$
DELIMITER ;
# 测试
DELETE FROM student WHERE Sno='202103007';
SELECT * FROM Student_delete_log;
# SHOW TRIGGERS;

# 在student表上定义一个INSERT、UPDATE触发器，插入或更新学生前，检查新记录的学生的年龄是否大小50，若大于50自动改为18，就自动在学生插入表insert_or_update_log中增加一条相应记录。
# 触发事件
DELIMITER $
CREATE TRIGGER before_insert
BEFORE INSERT ON Student
FOR EACH ROW
BEGIN
	IF(new.Sage<16) OR (new.Sage>25)
		THEN SIGNAL SQLSTATE 'HYOOO' SET message_text = '输入的年龄要在16~25之间';
    END IF;
END $

CREATE TRIGGER before_update
BEFORE UPDATE ON Student
FOR EACH ROW
BEGIN
	IF(new.Sage<16) OR (new.Sage>25)
		THEN SIGNAL SQLSTATE 'HYOOO' SET message_text = '输入的年龄要在16~25之间';
    END IF;
END$
DELIMITER ;
# 测试
INSERT INTO student VALUES ('202103007', '张麻子', '男', 35, 'GM');
INSERT INTO student VALUES ('202103007', '张麻子', '男', 25, 'GM');
UPDATE Student SET Sage=35 WHERE Sno='202103007';

# 在student表上定义一个DELETE触发器，删除学生记录前，检查该记录的学生的年龄是否在【16,25】之间，若在，则拒绝修改。 
# 触发事件
DELIMITER $
CREATE TRIGGER before_delete
BEFORE DELETE ON Student
FOR EACH ROW
BEGIN
	IF(old.Sage=23)
		THEN SIGNAL SQLSTATE 'HYOOO' SET message_text = '该年龄为23，不允许删除';
    END IF;
END $
DELIMITER ;
# 测试
DELETE FROM student WHERE Sno='202103007';
DELETE FROM student WHERE Sno='202103008';

# 删除触发器
show TRIGGERS;
DROP TRIGGER DELETE_STUDENT_Sage;
show TRIGGERS;

1.实验目的

        掌握数据库触发器的设计和使用方法。

2.实验内容和要求

        定义BEFORE触发器和AFTER触发器，能够理解不同类型触发器的作用和执行原理，验证触发器的有效性。

3.实验重点和难点

实验重点：触发器的定义。

实验难点：利用触发器实现较为复杂的用户自定义完整性。

4.实验过程

(1)触发器Trigger

触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程

• 由服务器自动激活

• 可以进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力

• 语法格式：

  CREATE TRIGGER <触发器名>  
         {BEFORE | AFTER} <触发事件> ON <表名>
          FOR EACH  {ROW | STATEMENT}
        ［WHEN <触发条件>］
          <触发动作体>

(2)AFTER触发器

①UPDATE触发器

在student表上定义一个UPDATE触发器，修改学生年龄后，就自动在年龄变换表Sage_log中增加一条相应记录。

a.创建表Sage_log：

数据类型依据为student表：

b.触发事件

问题1：DELIMITER $

默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

但这里，不希望MySQL这么做。因为BEGIN和END之间的语句中包含有分号。 我们不希望mysql读到这里的分号就执行。

所以在这里，把delimiter换成$，要求mysql遇到$才执行。相应的，我们在END后加一个字符$，并且在最后把delimiter换回分号

问题2：AS

mysql中语句语法是as begin，但这里会报错，正确格式是begin。

c.测试：

将student表中所有学生的年龄加一：

查看Sage_log：

②INSERT触发器

在student表上定义一个INSERT触发器，插入新学生后，就自动在学生插入表Student_insert_log中增加一条相应记录。

a.创建表Student_insert_log：

b.触发事件

c.测试：

向student表中插入一条记录，然后查看student_insert_log:

③DELETE触发器

在student表上定义一个DELETE触发器，在student表中删除一个学生后，就自动在学生插入表Student_delete_log中增加一条相应记录。

a.创建表Student_delete_log：

b.触发事件

c.测试：

从student表中删除一条记录，然后查看student_delete_log:

(3)BEFORE触发器

①INSERT、UPDATE触发器

在student表上定义一个INSERT、UPDATE触发器，插入或更新学生前，检查新记录的学生的年龄是否在【16,25】之间，若不在，则则拒绝修改。

a.触发事件

直接用OR会报错：

所以，分成两个触发器来写：

注：通过SIGNAL语句抛出异常，拒绝执行。自定义异常处理来进行检测调试,设置异常处理的提示信息。

signal语法格式如下：

signal sqlstate | condition_name;
set condition_information_item_name_1 = value_1,
    condition_information_item_name_1 = value_2, etc;
​
1.signal关键字是由declare condition语句声明的sqlstate值或条件名称。
2.如果需要向调用者提供提示信息，可使用set子句。
condition_information_item_name可以是messge_text，mysql_errorno，cursor_name等。

 

b.测试：

向student表中插入一条记录，然后查看报错信息:

向student表中更新一条记录，然后查看报错信息:

②DELETE触发器

在student表上定义一个DELETE触发器，删除学生记录前，检查该记录的学生的年龄，若是23，则拒绝删除。

a.触发事件

b.测试：

测试前：

测试命令：

测试结果：

(4)删除触发器

删除前：show TRIGGERS;

删除命令：DROP TRIGGER DELETE_STUDENT_Sage;

删除后：show TRIGGERS;

5.实验总结

触发器类似于约束，但能实现更复杂的检查和操作，尤其是可以在动作体中对其他表或数据库对象进行修改。

使用BEFORE触发器在MYSQL中可以通过SIGNAL语句抛出异常，拒绝执行。

6.思考题

(1) 设计一个AFTER触发器，当Lineitem表中的quantity变化时，自动计算Lineitem中的extendedprice值，同时也要修改PartSupp中的availqty值。

DELIMITER $
CREATE TRIGGER after_update_lineitem
AFTER UPDATE lineitem
FOR EACH ROW
BEGIN
    DECLARE rp REAL;
    SELECT retailprice INTO rp
    FROM Part
    WHERE new.partkey = Part.partkey;
    /*重新计算extendedprice*/
    UPDATE lineitem
    SET extendedprice =  new.quantity*rp
    WHERE partkey = new.partkey AND suppkey=new.suppkey;
    /*更新PartSupp中的availqty*/
    UPDATE PartSupp
    SET availqty = availqty+old.quantity-new.quantity
    WHERE partkey = new.partkey AND suppkey=new.suppkey;
END$
DELIMITER ;

​