Mysql第五章事务、视图、索引、备份和恢复上机练习

-- 上机练习1
#批量插入参加今天“Logic Java”课程考试的3名学生成绩。如果输入的成绩大于100分，则取消操作。
BEGIN;
INSERT into result (studentNo,subjectNo,examDate,studentResult)
VALUES (10001,1,now(),40);
INSERT into result (studentNo,subjectNo,examDate,studentResult)
VALUES (10002,1,now(),80);
INSERT into result (studentNo,subjectNo,examDate,studentResult)
VALUES (10003,1,now(),70);
commIT;

#插入错误数据        
INSERT INTO result(studentNo,subjectNo,examDate,studentResult)
            VALUES(10004,1,now(),101);            
INSERT INTO result(studentNo,subjectNo,examDate,studentResult)
            VALUES(10005,1,now(),102);
Rollback;

-- 上机练习2
BEGIN;

INSERT into historyresult(
 select * from result where studentNo in (
select studentNo from student where gradeId=(
  select gradeId from grade where gradeName= 'Y2'
 )
 )
);
delete from result WHERE  studentNo in(
SELECT gradeId FROM student WHERE gradeId=(
 select gradeId from grade where gradeName= 'Y2'
 )
);

INSERT into historyStudent(
 select * from student where gradeId=(
  select gradeId from grade where gradeName='Y2'
 )
);
DELETE FROM student WHERE gradeId = (
 select gradeId from grade where gradeName='Y2'
);
COMMIT;
ROLLBACK;

-- 上机练习3
CREATE view view_result AS
select student.studentName as 学生姓名,
result.studentResult as 成绩,
`subject`.subjectName as 课程名称
from result
inner join student on result.subjectNo = student.studentNo
inner join `subject` on `subject`.subjectNo = result.subjectNo
-- 查询视图
select 学生姓名,sum(成绩) as 总分 from view_result
WHERE  成绩 > 60
GROUP BY 学生姓名
ORDER BY 总分 DESC
-- 删除
DROP view view_result

-- 上机练习4
select * from student
create index index_studentName_gradeid on student(studentName,gradeId);
create UNIQUE index index_idcard on student(identityCard);
create index index_student_result on result(studentResult);

show index from student
show index from result

-- 上机练习5
--使用mysqldump命令将myschool数据库中学生表,成绩表备份文件D:backup\
mysqldump -uroot -p  myschool student,result > D:\backup\myschool_20210818.sql
--使用mysql命令还原
mysql -u root -p myschool < D:\backup\myschool_20210818.sql
--使用source命令还原
USE myschool;
source D:\backup\myschool_20210818.sql

仅供参考