SQL 第六章

最新推荐文章于 2023-11-08 19:27:00 发布

「已注销」

最新推荐文章于 2023-11-08 19:27:00 发布

阅读量306

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qq_36074134/article/details/56844905

本章节展示了SQL中的事务处理，包括插入借阅记录、更新读者和书籍信息，并在发生错误时回滚事务。此外，还创建了两个视图：view_borrow显示借阅信息，view_book展示书籍的可借阅状态。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--第六章简答3  
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