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
优化MySchool 第四章课后5 视图
最新推荐文章于 2018-10-31 11:09:46 发布