优化myshool 第六章上机练习

--上机练习一（批量插入学生考试成绩）

BEGIN TRANSACTIONDECLARE @errorSum INTSET @errorSum=0/*--插入数据--*/INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(1,1,GETDATE(),90)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(1,1,GETDATE(),70)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(2,1,GETDATE(),67)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(2,1,GETDATE(),55)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(3,1,GETDATE(),102)--分数违反约束SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(3,4,GETDATE(),90)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(4,4,GETDATE(),56)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(4,4,GETDATE(),88)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(5,4,GETDATE(),40)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(5,4,GETDATE(),65)SET @errorSum=@errorSum+@@error/*--根据是否有错误，确定事务是提交还是撤销--*/IF(@errorSum<>0) --如果有错误  BEGIN    PRINT '插入失败，回滚事务'    ROLLBACK TRANSACTION   END  ELSE  BEGIN    PRINT '插入成功，提交事务'    COMMIT TRANSACTION     ENDGO

--上机练习二（为毕业学生办理离校手续）

BEGIN TRANSACTIONDECLARE @errorSum INTSET @errorSum=0/*--查询Result表中所有Y2学员的考试成绩，保存到新表HistoreResult*/SELECT Result.* INTO HistoreResult FROM Result INNER JOIN Student ON Result.StudentNo=Student.StudentNoINNER JOIN Grade ON Grade.GradeId=Student.GradeId WHERE GradeName='Y2'SET @errorSum=@errorSum+@@error/*--删除Result表中所有Y2学员的考试成绩*/DELETE  Result FROM  Result JOIN Student ON Result.StudentNo=Student.StudentNoINNER JOIN Grade ON Grade.GradeId=Student.GradeId WHERE GradeName='Y2'SET @errorSum=@errorSum+@@error/*--将Student表中所有Y2的学员记录，保存到新表HistoryStudent*/SELECT Student.* INTO HistoryStudentFROM Student INNER JOIN Grade ON Grade.GradeId=Student.GradeId WHERE GradeName='Y2'SET @errorSum=@errorSum+@@error/*--删除Studet表中所有Y2学员记录*/DELETE Student FROM Student INNER JOIN Grade ON Grade.GradeId=Student.GradeId WHERE GradeName='Y2'SET @errorSum=@errorSum+@@error/*--根据是否有错误，确定事务是提交还是撤销--*/IF (@errorSum<>0) --如果有错误  BEGIN    PRINT '插入失败，回滚事务'    ROLLBACK TRANSACTION   END  ELSE  BEGIN    PRINT '插入成功，提交事务'    COMMIT TRANSACTION     ENDGO

--上机练习三（查看学生各学期考试成绩）

CREATE VIEW vw_student_result_infoAS  SELECT 姓名=StudentName,学号=Student.StudentNo,                 联系电话=Phone,学期=GradeName,成绩=Total  FROM Student  LEFT OUTER JOIN (      SELECT r.StudentNo,GradeName,SUM(StudentResult) Total       FROM Result r       INNER JOIN (         SELECT StudentNo,SubjectNo,MAX(ExamDate)  ExamDate          FROM Result          GROUP BY StudentNo,SubjectNo) tmp       ON r.ExamDate=tmp.ExamDate      AND r.SubjectNo = tmp.SubjectNo AND r.StudentNo = tmp.StudentNo      INNER JOIN Subject sub ON sub.SubjectNo = r.SubjectNo      INNER JOIN Grade g ON g.GradeId = sub.GradeId      GROUP By r.StudentNo,GradeName ) TmpResult2  ON Student.StudentNo = TmpResult2.StudentNo GROUP BY StudentName,Student.StudentNo,Phone,GradeName,TotalGOSELECT * FROM vw_student_result_info

--上级练习四（创建并使用索引查询学生考试成金）

USE MySchoolGOIF EXISTS (SELECT name FROM sysindexes          WHERE name = 'IX_Result_StudentResult')   DROP INDEX Result.IX_Result_StudentResult  --删除索引GO--考试成绩列上创建可重复索引：填充因子为70％--CREATE INDEX IX_Result_StudentResult   ON Result(StudentResult)       WITH FILLFACTOR = 70GO--查询输出学生姓名、课程名称、考试日期和考试成绩 SELECT StudentName '学生姓名',SubjectName '课程名称',ExamDate '考试日期', StudentResult '考试成绩' FROM Subject,Student,ResultWITH (INDEX = IX_Result_StudentResult)WHERE StudentResult BETWEEN 80 AND 90 AND Student.StudentNo = Result.StudentNo AND Subject.SubjectId = Result.SubjectId

--课后简答题

--借阅事物

/**事务操作借书*/BEGIN TRANSACTIONDECLARE @errorSum intDECLARE @RID varchar(50)DECLARE @BID varchar(50)SELECT @RID=RID FROM Reader WHERE RName='张无忌'SELECT @BID=BID FROM Book WHERE BName='深入。Net平台和c#编程'SET @errorSum=0INSERT INTO Borrow(RID,BID)VALUES (@RID,@BID)SET @errorSum=@errorSum+@@errorUPDATE Book SET BCount=BCount-1 WHERE BName=' 深入。Net平台和c#编程'SET @errorSum=@errorSum+@@errorUPDATE Reader SET LendNum=LendNum+1 WHERE RName='张无忌'SET @errorSum=@errorSum+@@errorIF @errorSum<>0ROLLBACK TRANSACTIONELSE COMMIT TRANSACTIONGO

--罚款事物

/**事务操作借书*/BEGIN TRANSACTIONDECLARE @errorSum intDECLARE @RID varchar(50)DECLARE @BID varchar(50)SELECT @RID=RID FROM Reader WHERE RName='刘冰冰'SELECT @BID=BID FROM Book WHERE BName='西游记'SET @errorSum=0INSERT INTO Penalty(RID,BID,PType,Amount) VALUES(@RID,@BID,'1',5.6)SET @errorSum=@errorSum+@@errorUPDATE Borrow SET ReturnDate=getdate() WHERE BID=@BIDSET @errorSum=@errorSum+@@errorUPDATE Reader SET LendNum=LendNum-1 WHERE RName='刘冰冰'SET @errorSum=@errorSum+@@errorUPDATE Book SET BCount=BCount+1 WHERE BID=@BIDSET @errorSum=@errorSum+@@errorIF @errorSum<>0ROLLBACK TRANSACTIONELSE COMMIT TRANSACTIONGO 

--视图

USE LibraryGO--创建索引IF EXISTS (SELECT * FROM sysindexes           WHERE name = 'IX_BOOK_BookName')   DROP INDEX Book.IX_BOOK_BookName  --删除索引/*--笔试列创建非聚集索引：填充因子为30％--*/CREATE INDEX IX_BOOK_BookName   ON BOOK(BName)   WITH FILLFACTOR = 30GO--创建管理员视图IF EXISTS (SELECT  * FROM sysobjects  WHERE  NAME='view_borrow')Drop VIEW view_borrowgoCREATE VIEW view_borrow ASSELECT   图书名称=Book.BNAME,到期时间=Borrow.WillDate,读者姓名=Book.BNAME from Borrow,book with (index = IX_BOOK_BookName)WHERE  Book.BID=Borrow.BID --order by Borrow.WillDateGOSELECT * FROM view_borrow--创建读者视图IF EXISTS(SELECT  *FROM sysobjects  WHERE  NAME='view_book')DROP VIEW view_bookgoCREATE VIEW view_bookAs SELECT  图书名称=BNAME,图书总量=BCount,可借阅量=BCount-(  SELECT COUNT(*) FROM Borrow WHERE returnDate is null AND Borrow.bid = Book.bid) FROM Book with (index = IX_BOOK_BookName)GOSELECT * FROM view_book