5.6MySQL

-- 开启事务 begin begin; update score set result100 where scoreID1; update score set result100 where scoreID2; -- 测试·是内存的状态还没有提交持久化 select * from score where scoreId in (1,2); -- 提交·向数据库持久化写入数据 commit; -- 回滚·取消内存里面的sql操作 rollback; -- 开启事务 START TRANSACTION; -- 1. 删除该班级学生的所有成绩score 表 DELETE FROM score WHERE studentNo IN ( SELECT studentNo FROM student WHERE classNo 202404 ); -- 2. 删除该班级的所有学生student 表 DELETE FROM student WHERE classNo 202404; -- 3. 删除班级本身class 表 DELETE FROM class WHERE classNo 202404; -- -- 执行到这里可以检查数据是否正确 -- 如果没问题提交事务有问题执行回滚 -- -- 提交事务一旦提交无法撤销 COMMIT; -- 如果发现错误执行回滚数据全部恢复 ROLLBACK; -- 创建视图 v_student_score CREATE VIEW v_student_score AS SELECT s.studentNo AS 学生编号, s.studentName AS 学生姓名, c.className AS 班级名称, co.courseName AS 课程名称, sc.result AS 考试成绩 FROM student s JOIN class c ON s.classNo c.classNo JOIN score sc ON s.studentNo sc.studentNo JOIN course co ON sc.courseNo co.courseNo; -- 调用试图 SELECT * FROM v_student_score;