《数据库原理与应用》作业（1-1）
四、触发器
1、触发器完整性控制验证
（1）创建下面的触发器，用于控制添加course信息时，保证cpno是已经存在的课程号。
delimiter //
create trigger trig2
before insert
on course
for each row
begin
If new.cpno is not null then
select count(*) into @icount from course where cno=new.cpno;
If @icount=0 then
signal sqlstate '45000' set message_text ='你输入的先修课号不存在！';
end if;
end if;
End//
delimiter ;（2）执行下面两条语句，查看结果，并思考不成功语句原因。
insert into course values('090901','数据库设计与开发','999888');
insert into course values('090902','智能金融概论',null);原因 ：第一句执行失败，999888不在已有的课程号里面，第二句执行成功，没有设置先修课号，只是增加了一个课程信息。
2、触发器级联操作验证
（1）创建一个学生选课统计表（选课数量，总学分）
create table stu_credit
as
select s.sno,sname,count(*) as 'coursecount',sum(credit) as 'sumcredit'
from student s,student_course sc,course_class cc
where s.sno=sc.sno and sc.ccno=cc.ccno
group by s.sno,sname;（2）创建触发器，当学生选课后自动跟新stu_credit表内容，为所选课程数量加1，为所选课程总学分增加新选的课程学分
delimiter //
create trigger trig01_student_course
after insert
on student_course
for each row
begin
declare credit1 int;
select credit into credit1 from course_class where ccno=new.ccno;
update stu_credit
set coursecount=coursecount+1,sumcredit=sumcredit+credit1
where sno=new.sno;
end//
delimiter ;（3）分别执行下面三条语句进行测试
--先查看表中数据学生信息
select * from stu_credit where sno='081220101';--添加数据，触发器执行
insert into student_course(sno,ccno) values('081220101','2009010101');--再次查看表中信息
select * from stu_credit where sno='081220101';3、设计触发器
（1）创建一个触发器，当在student_course中添加一个学生选课的时候，如果已选学分超过30分或者选课人数超过65人，则拒绝学生本次选修。
delimiter //
create trigger trig02_student_course
before insert
on student_course
for each row
begin
SELECT sumcredit INTO @credits FROM stu_credit where sno=new.sno;
IF @credits >30 THEN
SIGNAL SQLSTATE '45000' SET message_text = '选课失败！';
SELECT count(*) INTO @counts FROM student_course;
ELSEIF @counts >65 THEN
SIGNAL SQLSTATE '45000' SET message_text = '选课失败！';
END IF;
end;//
delimiter ;五、安全性管理
以管理员身份，按照下面步骤进行用户创建、对象授权、权限测试及权限回收。分别使用两个用户登录MySQL，其中一个使用管理员root登录，进行用户的创建及授权。另外一个进行用户权限测试。
1、创建用户，并授权
root登录，并使用stumanage数据库。
（1）创建用户u1。
use mysql;
create user u1@localhost identified by '123';（2）为u1用户授权course表的select权限。
GRANT SELECT ON stumanage.course TO u1@localhost;（3）为u1用户授权student表中部分列的select权限。
GRANT SELECT(sno,sname,ssex,mno) on stumanage.student TO u1@localhost;（4）创建视图v_ic。
create view v_ic(课号,课名,选修课) As select * from stumanage.course;（5）为u1用户授权v_ic视图的insert权限。
GRANT insert ON v_ic TO u1@localhost;2、用户权限测试
使用新建用户u1建立新的会话。
（1）尝试查询course表中数据，记录结果并分析原因。
use stumanage;
SELECT * from course;结果：执行成功，调出stumanage数据库的course表，因为u1用户有查询stumanage数据库的course表的权限。
（2）尝试在course表中添加数据，记录结果并分析原因。
insert into course values('999991','智能金融概论',null);结果：执行失败，因为u1用户没有插入stumanage数据库的course表的权限。
（3）尝试查询student表中数据，记录结果并分析原因。
select * from student;结果：执行失败，因为u1用户没有查询stumanage数据库的student表的权限。
（4）尝试查询student表中部分数据，记录结果并分析原因。
select sno,sname,ssex from student;结果：执行成功，调出stumanage数据库的student表的部分信息，因为u1用户有查询stumanage数据库的student表的部分权限。
（5）尝试通过视图v_ic间接在course表中添加数据，记录结果并分析原因。
use mysql;
insert into v_ic values('999991','智能金融概论',null);结果：执行成功，因为u1用户有插入mysql数据库的v_ic视图的权限，视图成功插入数据，数据表也随之更新数据。
3、用户权限回收
（1）回收用户u1在course表的select权限。
revoke select on stumanage.course from u1@localhost;（2）回收用户u1在course表的select权限。
revoke select(ssex) on stumanage.student from u1@localhost;（3）请思考，如何只将男生的数据授权给u1查询？
/*建立只有男生的表*/
use stumanage;
CREATE TABLE nanstudent1 AS SELECT * FROM student WHERE ssex='男';
GRANT SELECT on nanstudent1 TO u1@localhost;
/*查询*/
select * FROM nanstudent1;六、数据备份与恢复
本部分实验使用stuManage数据库，以管理员root登录建联系数据备份与恢复操作。
1、mysqldump备份数据库
（1）备份test数据库和stumanage数据库
C:>mysqldump -u root -p --databases test stumannge >d:/test_stumange.sql
（2）备份整个stumanage数据库
C:>mysqldump -u root -p --databases stumanage>d:/all_stumanage.sql
或者使用
mysqldump -u root -p stumanage>d:/all_stumanage.sql
（3）备份stumange数据库的student表和teacher表。
C:>mysqldump -u root -p stumanage student teacher >d:/stutea_stumanange.sql
或者使用
mysqldump -u root -p --databases stumanage --tables student teacher >d:/stutea_stumanange.sql
（4）备份stumanage数据库中student_course表中有成绩及格的信息，只备份数据。
C:>mysqldump -u root -p stumanage student_course --where=”mark>=60” -t --no-create-info>d:/sc60_stumanage.sql
其中-t --no-create-info表示只导出数据，不包含相关数据表的drop table和create table语句。
2、mysql恢复数据库
（1）删除student_course中mark>=60的数据，然后恢复
/*1111*/
select count(*) from student_course where mark>=60;
/*2222*/
delete from student_course where mark>=60;
mysql select count(*) from student_course where mark>=60;
/*3333*/
C:\>mysql -u root -p stumanage<d:/sc60_stumanage.sql
select count(*) from student_course where mark>=60;注意： 如果student_course上有触发器，数据恢复也会引起触发器执行。
（2）删除stumanage数据库，然后恢复数据库
drop database stumanage; /*删除数据库*/
C:\>mysql -u root -p <d:/all_stumanage.sql3、source恢复数据
delete from student_course where mark>=60;
mysql select count(*) from student_course where mark>=60;
use stumanage;
source <d:/sc60_stumanage.sql
mysql select count(*) from student_course where mark>=60;七、实验思考题
1、可更新视图必须满足哪些条件?
1、select语句在选择列表中没有聚合函数，也不包含TOP,GROUP BY,UNION(除非视图是分区视图)或DISTINCT子句。聚合函数可以用在FROM子句的子查询中，只要不修改函数返回的值。
2、select语句的选择列表中没有派生列。派生列是由任何非简单列表达式(使用函数、加法或减法运算符等)所构成的结果集列。
3、select语句中的FROM子句至少引用一个表。select语句不能只包含非表格格式的表达式(即不是从表派生出的表达式)。
4、INSERT,UPDATE和DELETE语句在引用可更新视图之前，也必须如上述条件指定的那样满足某些限制条件。
只有当视图可更新，并且所编写的UPDATE或INSERT语句只修改视图的FROM子句引用的一个基表中的数据时，UPDATE和INSERT语句才能引用视图。
只有当视图在其FROM子句中只引用一个表时，DELETE语句才能引用可更新的视图。
2、应该如何决定在哪些字段上设计索引？
（1）经常用于查询且更新较少的表才适合创建索引。
（2）数据量越大的表越适合创建索引。
（3）每次查询的数据比例越小，越适合创建索引。
（4）在取值重复率较大的字段上不要建立B树索引。
（5）要为经常需要排序、分组和连接查询的字段建立索引。
3、如何使用存储过程来保证数据安全性？
把一系列 SQL 语句预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。
不仅执行效率非常高，而且客户端不需要把所有的 SQL 语句通过网络发给服务器，减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性。
4、维护复杂数据完整性方面，触发器的优点是什么？
触发器不依赖于任何客户端应用程序，也不依赖于访问数据库的语言。
1）标准化。在整个应用上，触发器保证了数据的完整性和一致性，一旦在表上建立了触发器，它就存储在数据库中；这种方法消除了各个客户应用程序的冗余编码，便于规则发生变化时对编码进行修改；
2）高效率。触发器初始执行后，作为编译的代码执行。它的运行速度快，与在客户工作站上执行这些代码相比，在服务器上执行这些代码减少了网络通讯量和网络冲突；触发器把数据完整性代码放在服务器平台上比放在客户工作站上更有效；
3）安全性。触发器运行要有表主人的授权，但是，触发器能够被在表中插入、删除、修改记录的任何一个用户触发。任何一个应用程序或交互式子用户都无法避开触发器。