数据库上机实验-创建视图和触发器

目录

学习目标：

学习内容：

实施步骤与技术要点（附上代码和截图）

#(1)创建一个计算机系的学生视图view_1，要求列出学生表所有字段。

#(2)创建一个讲师职称的教师视图view_2，要求列出教师表所有字段，并设置SQL SECURITY为INVOKER，DEFINER为root用户。

#(3)创建一个王平老师所讲授课程的视图view_3，要求列出教师姓名、系别和课程号。

#(4)创建视图view_4，要求列出计算机系学生所选修课程的学号、课程号、课时。

#(5)修改视图view_2，要求列出教师表的教师号、姓名、职称、工资、岗位津贴和系别。

#(6)向视图view_1添加一条记录：学号用你们各自真实的学号的后三位,姓名、性别、出生日期、年龄和性别根据自己的情况填写，插入之后再查看视图view_1中的数据记录和学生表S中的记录。

#(7)将视图view_4中的课时修改为原来的二分之一，再查看视图view_4中的数据记录和课程表C中的记录。

#(8)将视图view_2中计算机系的教师信息删除，再查看视图view_2中的数据记录和教师表T中的记录。

#(9)创建一个信息表，该信息包含三个字段，一个是主键id字段，一个是事件字段，一个是时间字段。创建一个插入触发器，当向学生表S中插入一条学生记录时，学生记录自定，向信息表msg中插入一条信息” 学生表插入了一条记录”。（附上触发器代码，以及学生表S和信息表msg的内容截图）。

#(10)创建一个更新触发器，当修改学生表S中某一个学生记录时，向信息表msg中插入一条信息”学生表更新了一条记录”。（附上触发器代码，以及学生表S和信息表msg的内容截图）。

#(11)创建一个删除触发器，当在学生表S中根据学号删除某一个学生时，学生选课表SC中的选课记录也全部被删除。（附上触发器代码，以及学生表S和学生选课表SC的删除前后的效果截图）。

实验总结

---

学习目标：

1、能实现视图的创建、修改和删除；

2、能实现触发器的创建。

---

学习内容：

提示：以下是本篇文章正文内容，下面案例可供参考

实施步骤与技术要点（附上代码和截图）

表的创建请参照 数据库上机实验-创建数据库和数据表_创建teach数据库_<自由舰>的博客-CSDN博客

#(1)创建一个计算机系的学生视图view_1，要求列出学生表所有字段。

CREATE VIEW view_1 AS

SELECT * FROM s WHERE Dept = '计算机';

#(2)创建一个讲师职称的教师视图view_2，要求列出教师表所有字段，并设置SQL SECURITY为INVOKER，DEFINER为root用户。

CREATE DEFINER = root SQL SECURITY INVOKER VIEW view_2 AS

SELECT * FROM t WHERE Prof = '讲师';

#(3)创建一个王平老师所讲授课程的视图view_3，要求列出教师姓名、系别和课程号。

CREATE VIEW view_3 AS

SELECT TN,Dept,tc.CNo FROM t,tc WHERE t.TNo = tc.TNo and TN = '王平';

#(4)创建视图view_4，要求列出计算机系学生所选修课程的学号、课程号、课时。

CREATE VIEW view_4 AS

SELECT s.SNo,c.CNo,CT FROM s,c,sc WHERE s.SNo = sc.SNo AND c.CNo = sc.CNo AND s.Dept = '计算机';s

#(5)修改视图view_2，要求列出教师表的教师号、姓名、职称、工资、岗位津贴和系别。

ALTER VIEW view_2 AS

SELECT TNo,TN,Prof,Sal,Comm,Dept FROM t;s

#(6)向视图view_1添加一条记录：学号用你们各自真实的学号的后三位,姓名、性别、出生日期、年龄和性别根据自己的情况填写，插入之后再查看视图view_1中的数据记录和学生表S中的记录。

INSERT INTO view_1(SNo, SN, Sex, Age,Birthdary, Dept) VALUES ('148','自由舰','男',TIMESTAMPDIFF(YEAR, '2000-01-01', CURDATE()),YEAR('2000-01-01'),NULL);

SELECT * FROM s;

SELECT * FROM view_1;

学生表中的记录：

视图一的记录：

#(7)将视图view_4中的课时修改为原来的二分之一，再查看视图view_4中的数据记录和课程表C中的记录。

UPDATE view_4 SET CT = CT / 2;

SELECT * FROM view_4;

SELECT * FROM c;

view_4中的记录如下：

C表中的记录如下：

#(8)将视图view_2中计算机系的教师信息删除，再查看视图view_2中的数据记录和教师表T中的记录。

ALTER TABLE tc DROP FOREIGN KEY tc_ibfk_1;
DELETE FROM view_2 WHERE Dept = '计算机' ;
SELECT * FROM view_2;
SELECT * FROM T;

view_2修改后记录如下：

教师表的记录如下：

#(9)创建一个信息表，该信息包含三个字段，一个是主键id字段，一个是事件字段，一个是时间字段。创建一个插入触发器，当向学生表S中插入一条学生记录时，学生记录自定，向信息表msg中插入一条信息” 学生表插入了一条记录”。（附上触发器代码，以及学生表S和信息表msg的内容截图）。

CREATE TABLE msg (

id INT PRIMARY KEY AUTO_INCREMENT,

event VARCHAR(50),

time TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TRIGGER insert_msg_trigger

AFTER INSERT ON s

FOR EACH ROW

INSERT INTO msg(event) VALUES ('学生表插入了一条信息');

#测试：插入一条学生记录

INSERT INTO s VALUES ('666','测试','男',10,2000,null);

学生表s记录如下：

Msg表记录如下：

#(10)创建一个更新触发器，当修改学生表S中某一个学生记录时，向信息表msg中插入一条信息”学生表更新了一条记录”。（附上触发器代码，以及学生表S和信息表msg的内容截图）。

CREATE TRIGGER update_msg_trigger

AFTER UPDATE on s

FOR EACH ROW

INSERT INTO msg(event) VALUES('学生表更新了一条记录');

#测试

UPDATE s SET SN = '新测试' WHERE SNo = '666';

#(11)创建一个删除触发器，当在学生表S中根据学号删除某一个学生时，学生选课表SC中的选课记录也全部被删除。（附上触发器代码，以及学生表S和学生选课表SC的删除前后的效果截图）。

CREATE TRIGGER delete_S AFTER DELETE ON s

FOR EACH ROW

BEGIN

DELETE FROM sc WHERE sc.SNo = old.SNo;

END

DELETE FROM s WHERE Sno = '666';

实验总结

在今天的学习中，我们主要了解了MySQL数据库中的触发器和事务，这对于提高数据库的数据完整性和安全性非常重要。

以下是一些心得体会：

触发器是一种自动执行的程序，可以在特定的数据库操作（例如插入、更新、删除等）发生时自动触发执行，从而实现特定的业务逻辑或数据处理。

MySQL支持两种类型的触发器：BEFORE触发器和AFTER触发器。BEFORE触发器在执行数据库操作之前触发，可以用于数据验证和修正；AFTER触发器在执行数据库操作之后触发，可以用于记录操作日志和更新相关数据等。

事务是一组逻辑操作，要么全部执行成功，要么全部执行失败，具有原子性、一致性、隔离性和持久性四个特性。通过事务，可以确保数据库操作的完整性和一致性，避免脏读、不可重复读和幻读等问题。

在使用触发器和事务时，需要注意以下几点：

触发器和事务的设计应该符合实际业务需求，不能过度或不足。

在使用事务时，应该对事务的范围、隔离级别和锁定策略进行合理的设置，以达到最优性能和最佳的数据完整性。

在使用触发器时，需要考虑触发器的执行效率和数据安全性，避免触发器的死循环和数据不一致等问题。

在进行数据库操作时，要注意数据备份和恢复，以避免误操作或数据丢失等问题。

总之，触发器和事务是MySQL数据库中非常重要的功能，可以提高数据库的数据完整性和安全性，应该在实际应用中充分加以利用和发挥。