优化myshool 第六章上机练习

最新推荐文章于 2018-12-26 09:30:22 发布

柠檬不萌耶

最新推荐文章于 2018-12-26 09:30:22 发布

阅读量1.1k

点赞数 1

本文链接：https://blog.csdn.net/qq_36074150/article/details/56844172

版权

这篇博客主要介绍了myshool系统的上机练习，聚焦于毕业学生的离校手续办理流程及借阅事务的管理，详细阐述了这两个关键操作的实现和优化细节。

摘要由CSDN通过智能技术生成

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

BEGIN TRANSACTION
DECLARE @errorSum INT
SET @errorSum=0
/*--插入数据--*/
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(1,1,GETDATE(),90)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(1,1,GETDATE(),70)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(2,1,GETDATE(),67)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(2,1,GETDATE(),55)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(3,1,GETDATE(),102)--分数违反约束
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(3,4,GETDATE(),90)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(4,4,GETDATE(),56)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(4,4,GETDATE(),88)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(5,4,GETDATE(),40)
SET @errorSum=@errorSum+@@error
INSERT 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   
  END
GO

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

BEGIN TRANSACTION
DECLARE @errorSum INT
SET @errorSum=0
/*--查询Result表中所有Y2学员的考试成绩，保存到新表HistoreResult*/
SELECT Result.* INTO HistoreResult 
FROM Result INNER JOIN Student ON Result.StudentNo=Student.StudentNo
INNER 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.StudentNo
INNER JOIN Grade ON Grade.GradeId=Student.GradeId 
WHERE GradeName='Y2'
SET @errorSum=@errorSum+@@error

/*--将Student表中所有Y2的学员记录，保存到新表HistoryStudent*/
SELECT Student.* INTO HistoryStudent
FROM 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   
  END
GO

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

CREATE VIEW vw_student_result_info
AS
  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,Total
GO
SELECT * FROM vw_student_result_info

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

USE MySchool
GO

IF 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 = 70
GO


--查询输出学生姓名、课程名称、考试日期和考试成绩 
SELECT StudentName '学生姓名',SubjectName '课程名称',ExamDate '考试日期', StudentResult '考试成绩' FROM Subject,Student,Result
WITH (INDEX = IX_Result_StudentResult)
WHERE StudentResult BETWEEN 80 AND 90 AND Student.StudentNo = Result.StudentNo AND Subject.SubjectId = Result.SubjectId

--课后简答题

--借阅事物

/*
*事务操作借书
*/
BEGIN TRANSACTION
DECLARE @errorSum int
DECLARE @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=0
INSERT INTO Borrow(RID,BID)VALUES (@RID,@BID)
SET @errorSum=@errorSum+@@error
UPDATE Book SET BCount=BCount-1 WHERE BName=' 深入。Net平台和c#编程'
SET @errorSum=@errorSum+@@error
UPDATE Reader SET LendNum=LendNum+1 WHERE RName='张无忌'
SET @errorSum=@errorSum+@@error
IF @errorSum<>0
		ROLLBACK TRANSACTION
	ELSE 
	COMMIT TRANSACTION
GO

--罚款事物

/*
*事务操作借书
*/
BEGIN TRANSACTION


DECLARE @errorSum int
DECLARE @RID varchar(50)
DECLARE @BID varchar(50)
SELECT @RID=RID FROM Reader WHERE RName='刘冰冰'
SELECT @BID=BID FROM Book WHERE BName='西游记'
SET @errorSum=0
INSERT INTO Penalty(RID,BID,PType,Amount) VALUES(@RID,@BID,'1',5.6)
SET @errorSum=@errorSum+@@error
UPDATE Borrow SET ReturnDate=getdate() WHERE BID=@BID
SET @errorSum=@errorSum+@@error
UPDATE Reader SET LendNum=LendNum-1 WHERE RName='刘冰冰'
SET @errorSum=@errorSum+@@error
UPDATE Book SET BCount=BCount+1 WHERE BID=@BID
SET @errorSum=@errorSum+@@error
IF @errorSum<>0
		ROLLBACK TRANSACTION
	ELSE 
	COMMIT TRANSACTION
GO

--视图

USE Library
GO

--创建索引
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 = 30
GO

--创建管理员视图
IF EXISTS (SELECT  * FROM sysobjects  WHERE  NAME='view_borrow')
	Drop VIEW view_borrow
go
CREATE VIEW view_borrow 
	AS
		SELECT   图书名称=Book.BNAME,到期时间=Borrow.WillDate,读者姓名=Book.BNAME 
		from Borrow,book 
		with (index = IX_BOOK_BookName)
		WHERE  Book.BID=Borrow.BID 
		--order by Borrow.WillDate
GO

SELECT * FROM view_borrow


--创建读者视图
IF EXISTS(SELECT  *FROM sysobjects  WHERE  NAME='view_book')
	DROP VIEW view_book
	go
CREATE VIEW view_book
	As 
		SELECT  图书名称=BNAME,图书总量=BCount,可借阅量=BCount-(
		  SELECT COUNT(*) FROM Borrow WHERE returnDate is null AND Borrow.bid = Book.bid
		) FROM Book 
		with (index = IX_BOOK_BookName)
GO

SELECT * FROM view_book