十一創(chuàng)建視圖

1、執(zhí)行視圖 課程:視圖簡介什么是視圖?視圖的優(yōu)點什么是視圖?職工視圖 姓 名Davolio Fuller LeverlingNancy Andrew Janet職工職工ID姓 名標題123DavolioFullerLeverlingNancyAndrewJanetUSE NorthwindGOCREATE VIEW dbo.EmployeeViewAS SELECT LastName, FirstnameFROM EmployeesUsers View視圖的優(yōu)點為用戶集中數(shù)據(jù)降低數(shù)據(jù)庫的復雜性簡化用戶權限管理提高性能組織數(shù)據(jù)導出到其它應用程序中課程:定義和使用視圖創(chuàng)建視圖的方法示例:連接表視圖修

2、改和刪除視圖的方法視圖信息的定位限制通過視圖修改數(shù)據(jù)創(chuàng)建視圖的方法創(chuàng)建視圖限制視圖定義不能包含 ORDER BY 語句不能包含 INTO 關鍵字CREATE VIEW dbo.OrderSubtotalsView (OrderID, Subtotal)ASSELECT OD.OrderID, SUM(CONVERT(money,(OD.UnitPrice*Quantity*(1-Discount)/100)*100)FROM Order Details ODGROUP BY OD.OrderIDGOUSE NorthwindGOCREATE VIEW dbo.ShipStatusViewASS

3、ELECT OrderID,ShippedDate,ContactNameFROM Customers c INNER JOIN Orders o ON c.CustomerID = O.CustomerIDWHERE RequiredDate ShippedDate示例:多表連接視圖定單客戶ShipStatusViewOrderID1026410271102801996-08-211996-08-291996-09-11ShippedDate1996-08-231996-08-301996-09-12ContactNameLaurence LebihanGeorg PippsHorst Kl

4、ossBONAPPICCOQUICKLaurence LebihanGeorg PippsHorst KlossCustomerIDContactNameBon appPiccolo und mehrQUICK-StopCompanyName1066310827104271045110515BONAP BONAP PICCO QUICKQUICK 1997-10-031998-02-061997-03-031997-03-121997-05-23定單號客戶號裝船日期1997-09-241998-01-261997-02-241997-03-051997-05-07必需數(shù)據(jù)修改和刪除視圖的方法修改視圖保留已分配的權限使新的 SELECT 語句和選項 來取代存在的定義刪除視圖USE NorthwindGOALTER VIEW dbo.EmployeeViewAS SELECT LastName, FirstName, ExtensionFROM EmployeesDROP VIEW dbo.ShipStatusView通過視圖限制修改數(shù)據(jù)視圖不包含另外的數(shù)據(jù)數(shù)據(jù)更改應為存在的基本表約束一定不包含聚集函數(shù)或