第五章事务、视图、索引、备份和恢复习题

一、批量插入学生考试成绩

训练要点：使用事务向表中插入多条记录                                                                                          需求说明：批量插入参加Logic Java课程考试的5名学生的成绩考试成绩为当天日期，如果输入的成绩大于100分，则取消操作

begin;
insert into result(studentNo,subjectNo,examDate,studentResult)
values(1000,1,NOW(),99);
insert into result(studentNo,subjectNo,examDate,studentResult)
values(1002,1,NOW(),65);
insert into result(studentNo,subjectNo,examDate,studentResult)
values(1007,1,NOW(),85);
COMMIT;
begin;
insert into result(studentNo,subjectNo,examDate,studentResult)
values(1009,1,NOW(),101);
rollback;
insert into result(studentNo,subjectNo,examDate,studentResult)
values(1010,1,NOW(),120);	
rollback;

二、为毕业学生办理离校手续

需求说明：将毕业学生的基本信息和考试成绩分别保存到历史表中

begin;
create table historyRestlt(
select r.* from result as r,grade as g,student as s
where s.studentNo=r.studentNo and g.gradeID=s.gradeID and g.gradName='S2';
);
delete from result where studentNo in(
select* from student where gradeID in (select gradeID from grade where gradName='S2')
);
create table historyStudent(
select * from student where gradeID in(select gradeID from grade where gradName='S2')
);
delete from student where gradeId in(select gradeID from grade where gradName='S2')
commit;

三、查看学生各科目考试成绩平均分

训练要点：使用视图获取表中的数据                                                                                                  需求说明：统计每个学生所参考课程的平均成绩

create VIEW pjcj
AS
select studentName,sub.subjectName,avg(studentResult)
from student as s
inner join result as r on s.studentNo=r.studentNo
inner join `subject` as sub on sub.subjectNo=r.subjectNo
group by studentName,subjectName;
select * from pjcj;

四、创建学生表和创建表索引并查看索引

需求说明：为提高以下查询的速度，请为学生表和成绩表添加合适的索引，并使用show index语句查看所创建的索引

select * from student;
select * from result;

create index index_student_studentNo
on student(studentNo);
show index from student;

create index index_result_studentNo
on result(studentNo);
show index from student;