第7章 索引與視圖.ppt

1、第7章 索引與視圖,1,本章內(nèi)容,7.1 索引概述 7.2 索引的操作 7.3 視圖概述 7.4 視圖的操作 7.5 視圖的應(yīng)用,2,7.1 索引概述,索引的基本概念 索引是對數(shù)據(jù)庫表中一個或多個字段的值進行排序而創(chuàng)建的一種分散存儲結(jié)構(gòu)。,3,7.1 索引概述,索引的基本概念 索引是對數(shù)據(jù)庫表中一個或多個字段的值進行排序而創(chuàng)建的一種分散存儲結(jié)構(gòu)。 建立索引的目的有以下幾點： (1)加速數(shù)據(jù)檢索 (2)加速連接、ORDER BY和GROUP BY等操作 (3)查詢優(yōu)化器依賴于索引起作用 (4)強制實行的惟一性,4,7.1 索引概述,索引的分類 根據(jù)數(shù)據(jù)庫的功能，在SQL Server 2005中

2、可創(chuàng)建3種類型的索引，即惟一性索引、主鍵索引和聚集索引。 1惟一性索引 在表中建立惟一性索引時，組成該索引的字段或字段組合在表中具有惟一值，也就是說，對于表中的任何兩行記錄來說，索引鍵的值都是各不相同。 2主鍵索引 表中通常有一個字段或一些字段組的合，其值用來惟一標識表中的每一行記錄，該字段或字段組合稱為表的主鍵。,5,7.1 索引概述,3聚集索引 在聚集索引中，表中各記錄的物理順序與鍵值的邏輯(索引)順序相同。只有在表中建立了一個聚集索引后，數(shù)據(jù)才會按照索引鍵值指定的順序存儲到表中。由于一個表中的數(shù)據(jù)只能按照一種順序來存儲，所以在一個表中只能建立一個聚集索引。,6,7.2 索引的操作,創(chuàng)建索

3、引 1使用SQL Server管理平臺創(chuàng)建索引,7,演示操作,7.2 索引的操作,2使用Transact-SQL語句創(chuàng)建索引 語法格式： CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX index_name ON table_name(column_nameASC|DESC,n) WITH PAD_INDEX , FILLFACTOR=fillfactor , IGNORE_DUP_KEY , DROP_EXISTING , STATISTICS_NORECOMPUTE ON filegroup,8,7.2 索引的操作,例7-1 按employee表的emp

4、loyee_name列建立非聚集索引。 CREATE NONCLUSTERED INDEX name_idx ON employee(employee_name),9,7.2 索引的操作,查看索引 1用SQL Server管理平臺查看修改索引 2使用系統(tǒng)存儲過程查看索引 語法格式： sp_helpindex objname=name 其中objname=name子句為指定當(dāng)前數(shù)據(jù)庫中的表的名稱。 例7-2 在查詢分析器中查看表employee的索引。 USE Sales GO EXEC sp_helpindex employee GO,10,演示操作,7.2 索引的操作,編輯索引 1使用SQL

5、 Server管理平臺編輯索引 2使用系統(tǒng)存儲過程更改索引 語法格式： sp_rename OldName,NewName,object_type 例7-3 更改employee表中索引employee_index_1名稱為employee_index_id。 USE Sales GO EXEC sp_rename employee.employee_index_1,employee_index_id,index,11,演示操作,7.2 索引的操作,刪除索引 索引會減慢INSERT、UPDATE和DELETE語句的執(zhí)行速度。如果發(fā)現(xiàn)索引阻礙整體性能或不再需要索引，則可將其刪除。 1使用SQL

6、Server管理平臺刪除索引,12,演示操作,7.2 索引的操作,2使用Transact-SQL語句刪除索引 語法格式： DROP INDEX table_name.index_name ,.n 各選項的含義： (1)table_name：索引列所在的表。 (2)index_name：要刪除的索引名稱。索引名必須符合標識符的規(guī)則。,13,7.2 索引的操作,例7-4 刪除employee表內(nèi)名為employee_index_name的索引。 USE Sales IF EXISTS (SELECT name FROM sysindexes WHERE name = employee_index_

7、name) DROP INDEX employee.employee_index_name GO,14,7.3 視圖概述,視圖的概念 視圖是一個虛擬表，并不表示任何物理數(shù)據(jù)，而只是用來查看數(shù)據(jù)的窗口而已。 對視圖的操作與對表的操作一樣，可以對其進行查詢、修改和刪除，但對數(shù)據(jù)的操作要滿足一定的條件。 對視圖所引用的基礎(chǔ)表來說，視圖的作用類似于篩選。,15,7.3 視圖概述,視圖的作用 視圖通常用來集中、簡化和自定義每個用戶對數(shù)據(jù)庫的不同認識。視圖可用作安全機制，方法是允許用戶通過視圖訪問數(shù)據(jù)，而不授予用戶直接訪問視圖基礎(chǔ)表的權(quán)限。 (1)簡化操作 (2)定制數(shù)據(jù) (3)導(dǎo)出數(shù)據(jù) (4)安全性,1

8、6,7.3 視圖概述,視圖的限制 在創(chuàng)建或使用視圖時，應(yīng)遵守以下規(guī)定： (1)在一個批事務(wù)中，CREATE VIEW語句不能與其他SQL語句結(jié)合使用。 (2)不能在視圖上建立觸發(fā)器和索引。 (3)一個視圖最多只能有250列。 (4)不能基于臨時表建立視圖，由SELECT INTO語句可建立臨時表，在CREATE VIEW語句中不能使用INTO關(guān)鍵字。 (5)在CREATE VIEW語句中不能使用聯(lián)合操作符UNION。 (6)在插入或修改數(shù)據(jù)時，每個INSERT語句和UPDATE語句不能影響一個以上的視圖基表。 (7)對視圖中的計算列不允許使用INSERT語句插入數(shù)據(jù)。 (8)UPDATE語句不

9、能夠修改視圖的計算列數(shù)據(jù)，也不允許它修改包含集合的函數(shù)和內(nèi)置函數(shù)的視圖列。 (9)在視圖的Text和Image數(shù)據(jù)類型的列上不允許使用READTEXT和WRITETEXT語句。,17,7.4 視圖的操作,創(chuàng)建視圖 1.使用SQL Server管理平臺創(chuàng)建視圖 2. 使用Transact-SQL語句建立視圖 語法結(jié)構(gòu)： CREATE VIEW database_name.owner_name.view_name(column,) WITH ENCRYPTION|SCHEMABINDING|VIEW_METADATA AS select_statement WITH CHECK OPTION,18

10、,7.4 視圖的操作,例7-6 在Sales數(shù)據(jù)庫中創(chuàng)建sell_view視圖，該視圖選擇3個基表(employee,goods,sell_order)中的數(shù)據(jù)來顯示員工銷售貨物情況的虛擬表。 CREATE VIEW sell_view AS SELECT employee.employee_name, employee.employee_id, sell_order.order_num, sell_order.discount, goods.goods_name, goods.unit_price , sell_order.order_date FROM employee INNER JOI

11、N sell_order ON employee.employee_id = sell_order.employee_id INNER JOIN goods ON sell_order.goods_id = goods.goods_id,19,7.4 視圖的操作,修改視圖 1使用SQL Server管理平臺修改視圖 2使用Transact-SQL語句修改視圖 可以使用ALTER VIEW語句來修改視圖，其語法格式如下： ALTER VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION :

12、= ENCRYPTION | SCHEMABINDING | VIEW_METADATA ,20,7.4 視圖的操作,刪除視圖 1. 使用SQL Server管理平臺刪除視圖 2使用Transact-SQL語句刪除視圖 可以使用DROP VIEW語句來刪除視圖，其語法格式如下： DROP VIEW view_name ,.n 其中View_name是要刪除的視圖名稱，可以刪除多個視圖。 例7-7 刪除view1視圖。 USE Sales IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = v

13、iew1) DROP VIEW view1 GO,21,7.4 視圖的操作,查看和修改視圖屬性 1用SQL Server管理平臺查看視圖屬性 2使用系統(tǒng)存儲過程sp_helptext查看視圖 語法格式： sp_helptext objname = name 3使用系統(tǒng)存儲過程重命名視圖 語法格式： sp_rename objname = object_name , newname = new_name , objtype = object_type ,22,7.5 視圖的應(yīng)用,通過視圖檢索表數(shù)據(jù) 在建立視圖后，可以用任一種查詢方式檢索視圖數(shù)據(jù)，對視圖可使用連接、GROUP BY子句、子查詢等以

14、及它們的任意組合。 例7-8 查詢例7-6所創(chuàng)建的視圖sell_view中的姓名為錢達理的員工所銷售的商品名稱。 SELECT goods_name FROM sell_view WHERE employee_name=錢達理 ORDER BY order_date,23,7.5 視圖的應(yīng)用,通過視圖添加表數(shù)據(jù) 語法格式： INSERT INTO 視圖名 VALUES(列值1，列值2，列值3，列值n) 例7-9 在goods表中建立一個視圖，利用視圖插入一行數(shù)據(jù)。 CREATE VIEW goods_view AS SELECT goods_id，goods_name，classificati

15、on_id，supplier_id，unit_price，stock_quantity FROM goods WHERE (classification_id = P001) INSERT INTO goods_view VALUES (G0008,SONY DVD,P002, S001，400,10),24,7.5 視圖的應(yīng)用,通過視圖修改表數(shù)據(jù) 語法格式： UPDATE 視圖名 SET 列1=列值1 列2=列值2 . 列n=列值n WHERE 邏輯表達式,25,7.5 視圖的應(yīng)用,通過視圖刪除表數(shù)據(jù) 盡管視圖不一定包含基礎(chǔ)表的所有列，但可以通過視圖刪除基礎(chǔ)表的數(shù)據(jù)行。 語法格式： DELETE FROM 視圖名 WHERE 邏輯表達式 WHERE 邏輯表達式,26,7.5 視圖的應(yīng)用,通過視圖修改表數(shù)據(jù) 語法格式： UPDATE 視圖名 SET 列1=列值1 列2=列值2 . 列n=列值n WHERE 邏輯表達式,27,本章小結(jié),(1)索引是對數(shù)據(jù)庫表中一個或多個字段的值進行排序而創(chuàng)建的一種分散存儲結(jié)構(gòu)。建立索引的主要目的是加速數(shù)據(jù)檢索和連接、優(yōu)化查詢、強制實行惟一性等操作。主要有3種類型的索引，即惟一性索引、主鍵索