优化mysh 事务视图索引

最新推荐文章于 2018-09-11 10:06:29 发布

权世界是我家的

最新推荐文章于 2018-09-11 10:06:29 发布

阅读量406

点赞数

本文链接：https://blog.csdn.net/qq_36074088/article/details/56844360

版权

---------------------上机练习一

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='西游记'  
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  
---------------------------------------简答二
/*  
*事务操作借书  
*/  
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  

-------------------------------------简答三
--创建索引  
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