(數(shù)據(jù)庫應用技術)第5章

1、第5章 數(shù)據(jù)庫對象的操作5.1 數(shù)據(jù)類型 5.2 表操作 5.3 視圖操作 5.4 索引操作 5.5 存儲過程 5.6 觸發(fā)器 5.7 圖 表 上一章返回目錄5.1 數(shù)據(jù)類型 5.1.1 系統(tǒng)數(shù)據(jù)類型5.1.2 自定義數(shù)據(jù)類型 5.1.1 系統(tǒng)數(shù)據(jù)類型 1. 整型數(shù)據(jù)類型2. 浮點數(shù)據(jù)類型 3. 字符數(shù)據(jù)類型 4. 日期和時間數(shù)據(jù)類型 5. 文本和圖形數(shù)據(jù)類型 6. 貨幣數(shù)據(jù)類型 7. 位數(shù)據(jù)類型 8. 二進制數(shù)據(jù)類型 9. 特殊數(shù)據(jù)類型 10. 新增數(shù)據(jù)類型1. 整型數(shù)據(jù)類型整型數(shù)據(jù)類型是最常用的數(shù)據(jù)類型之一，它主要用來存儲數(shù)值，可以直接進行數(shù)據(jù)運算，而不必使用函數(shù)轉(zhuǎn)換。 int（integ

2、er）：int（或integer）數(shù)據(jù)類型可以存儲從-231（-2，147，483，648）到231-1（2，147，483，647）范圍之間的所有正負整數(shù)。 Smallint：可以存儲從-215（-32，768）到215-1范圍之間的所有正負整數(shù) 。Tinyint：可以存儲從0到255范圍之間的所有正整數(shù)。 2. 浮點數(shù)據(jù)類型 浮點數(shù)據(jù)類型用于存儲十進制小數(shù)。浮點數(shù)值的數(shù)據(jù)在SQL Server中采用只入不舍的方式進行存儲 。Real：可以存儲正的或者負的十進制數(shù)值，最大可以有7位精確位數(shù)。 Float：可以精確到第15位小數(shù)，其范圍從-1.79E-308到1.79E+308。 Decima

3、l和numeric：Decimal數(shù)據(jù)類型和numeric數(shù)據(jù)類型完全相同，它們可以提供小數(shù)所需要的實際存儲空間，但也有一定的限制，可以用2到17個字節(jié)來存儲從-1038-1到1038-1之間的數(shù)值。 3. 字符數(shù)據(jù)類型 字符數(shù)據(jù)類型可以用來存儲各種字母、數(shù)字符號和特殊符號。 Char：其定義形式為char（n），每個字符和符號占用一個字節(jié)的存儲空間。 Varchar：其定義形式為varchar（n）。用char數(shù)據(jù)類型可以存儲長達255個字符的可變長度字符串 。Nchar：其定義形式為nchar（n）。 Nvarchar：其定義形式為nvarchar（n）。 4. 日期和時間數(shù)據(jù)類型 Dat

4、etime：用于存儲日期和時間的結(jié)合體 。它可以存儲從公元1753年1月1日零時起到公元9999年12月31日23時59分59秒之間的所有日期和時間 。Smalldatetime：與datetime數(shù)據(jù)類型類似，但其日期時間范圍較小，它存儲從1900年1月1日到2079年6月6日內(nèi)的日期。 5. 文本和圖形數(shù)據(jù)類型 Text：用于存儲大量文本數(shù)據(jù)，其容量理論上為1到231-1（2，147，483，647）個字節(jié)，但實際應用時要根據(jù)硬盤的存儲空間而定。 Ntext：與text數(shù)據(jù)類型類似，存儲在其中的數(shù)據(jù)通常是直接能輸出到顯示設備上的字符，顯示設備可以是顯示器、窗口或者打印機。Image：用于存

5、儲照片、目錄圖片或者圖畫，其理論容量為231-1（2，147，483，647）個字節(jié)。6. 貨幣數(shù)據(jù)類型 Money：用于存儲貨幣值，存儲在money數(shù)據(jù)類型中的數(shù)值以一個正數(shù)部分和一個小數(shù)部分存儲在兩個4字節(jié)的整型值中，存儲范圍為-922337213685477.5808到922337213685477.5808，精度為貨幣單位的萬分之一。Smallmoney：與money數(shù)據(jù)類型類似，但其存儲的貨幣值范圍比money數(shù)據(jù)類型小，其存儲范圍為-214748.3468到214748.3467。7. 位數(shù)據(jù)類型 Bit：稱為位數(shù)據(jù)類型，其數(shù)據(jù)有兩種取值：0和1，長度為1字節(jié)。 8. 二進制數(shù)據(jù)類

6、型Binary：其定義形式為binary(n)，數(shù)據(jù)的存儲長度是固定的，即n+4字節(jié)，當輸入的二進制數(shù)據(jù)長度小于n時，余下部分填充0。 Varbinary：其定義形式為varbinary(n)，數(shù)據(jù)的存儲長度是變化的，它為實際所輸入數(shù)據(jù)的長度加上4字節(jié)。其它含義同binary。9. 特殊數(shù)據(jù)類型 Timestamp：亦稱時間戳數(shù)據(jù)類型，它提供數(shù)據(jù)庫范圍內(nèi)的唯一值，反應數(shù)據(jù)庫中數(shù)據(jù)修改的相對順序，相當于一個單調(diào)上升的計數(shù)器。 Uniqueidentifier：用于存儲一個16字節(jié)長的二進制數(shù)據(jù)類型，它是SQL Server根據(jù)計算機網(wǎng)絡適配器地址和CPU時鐘產(chǎn)生的唯一號碼而生成的全局唯一標識符代

7、碼（Globally Unique Identifier，簡寫為GUID）。10. 新增數(shù)據(jù)類型 Bigint：用于存儲從-263（-9，223，372，036，854，775，807）到263-1（9，223，372，036，854，775，807）之間的所有正負整數(shù)。 sql_variant：用于存儲除文本、圖形數(shù)據(jù)和timestamp類型數(shù)據(jù)外的其它任何合法的SQL Server數(shù)據(jù)。table：用于存儲對表或者視圖處理后的結(jié)果集。這種新的數(shù)據(jù)類型使得變量可以存儲一個表，從而使函數(shù)或過程返回查詢結(jié)果更加方便、快捷。5.1.2 自定義數(shù)據(jù)類型 1. 使用企業(yè)管理器（Enterprise M

8、anager）創(chuàng)建用戶自定義數(shù)據(jù)類型。2. 利用系統(tǒng)存儲過程創(chuàng)建用戶自定義數(shù)據(jù)類型 。系統(tǒng)存儲過程sp_addtype為用戶提供了T_SQL語句創(chuàng)建自定義數(shù)據(jù)類型的途徑，其語法形式如下：sp_addtype typename= type,phystype= system_data_type, nulltype= null_type, owner= owner_name例子5-1：自定義一個地址數(shù)據(jù)類型。 exec sp_addtype address, varchar(80), not null其運行結(jié)果如下：（1 row(s) affected）type added.例子5-2：刪除自定義的

9、生日數(shù)據(jù)類型。 exec sp_droptype birthday其運行結(jié)果如下：（1 row(s) affected）（0 row(s) affected）Type has been dropped.5.2 表操作 5.2.1 創(chuàng)建表5.2.2 增加、刪除和修改字段 5.2.3 創(chuàng)建、刪除和修改約束 5.2.4 查看表格 5.2.1 創(chuàng)建表在SQL Server 2000中，每個數(shù)據(jù)庫中最多可以創(chuàng)建200萬個表，用戶創(chuàng)建數(shù)據(jù)庫表時，最多可以定義1024列，也就是可以定義1024個字段。 SQL Server 2000提供了兩種方法創(chuàng)建數(shù)據(jù)庫表，第一種方法是利用企業(yè)管理器（Enterprise

10、 Manager）創(chuàng)建表；另一種方法是利用Transact-SQL語句中的create命令創(chuàng)建表。1. 利用Enterprise Manager創(chuàng)建表 在Enterprise Manager中，展開指定的服務器和數(shù)據(jù)庫，打開想要創(chuàng)建新表的數(shù)據(jù)庫，用右鍵單擊表對象，從彈出的快捷菜單中選擇新建表選項，或者在工具欄中選擇圖標，就會出現(xiàn)新建表對話框，在該對話框中，可以定義列的以下屬性：列名稱、數(shù)據(jù)類型、長度、精度、小數(shù)位數(shù)、是否允許為空、缺省值、標識列、標識列的初始值、標識列的增量值和是否有行的標識。 然后根據(jù)提示進行設置。2. 利用create命令創(chuàng)建表 其語法形式如下：CREATE TABLE d

11、atabase_name. owner .| owner. table_name( | column_name AS computed_column_expression| ，n) ON filegroup | DEFAULT 創(chuàng)建表的各參數(shù)的說明如下： database_name：用于指定在其中創(chuàng)建表的數(shù)據(jù)庫名稱。 owner：用于指定新建表的所有者的用戶名 。table_name：用于指定新建的表的名稱。 column_name：用于指定新建表的列的名稱。 computed_column_expression：用于指定計算列的列值的表達式。 ON filegroup | DEFAULT：用

12、于指定存儲表的文件組名。 TEXTIMAGE_ON：用于指定 text、ntext 和 image 列的數(shù)據(jù)存儲的文件組。 data_type：用于指定列的數(shù)據(jù)類型 。DEFAULT：用于指定列的缺省值。 constant_expression：用于指定列的缺省值的常量表達式 。IDENTITY：用于指定列為標識列。 Seed：用于指定標識列的初始值。Increment：用于指定標識列的增量值。 NOT FOR REPLICATION：用于指定列的IDENTITY屬性在把從其它表中復制的數(shù)據(jù)插入到表中時不發(fā)生作用，即不足的生成列值，使得復制的數(shù)據(jù)行保持原來的列值。ROWGUIDCOL：用于指定

13、列為全球唯一鑒別行號列 。COLLATE：用于指定表使用的校驗方式。 column_constraint和table_constraint：用于指定列約束和表約束。 例子5-3：創(chuàng)建了一個雇員信息表 其SQL語句的程序清單如下： CREATE TABLE employee ( number int not null, name varchar(20) NOT NULL, sex char(2) NULL, birthday datetime null, hire_date datetime NOT NULL DEFAULT (getdate() professional_title varch

14、ar(10) null, salary money null, memo ntext null)5.2.2 增加、刪除和修改字段 利用企業(yè)管理器增加、刪除和修改字段 。在企業(yè)管理器中，打開指定的服務器中要修改表的數(shù)據(jù)庫，用右鍵單擊要進行修改的表，從彈出的快捷菜單中選擇設計表選項，則會出現(xiàn)設計表對話框，在該對話框中，可以利用圖形化工具完成增加、刪除和修改字段的操作。 利用Transact-SQL語言中的alter table命令增加、刪除和修改字段。利用Transact-SQL語言中的alter table命令增加、刪除和修改字段的各參數(shù)的說明如下： table：用于指定要修改的表的名稱。 AL

15、TER COLUMN：用于指定要變更或者修改數(shù)據(jù)類型的列。column_name：用于指定要更改、添加或刪除的列的名稱。 new_data_type：用于指定新的數(shù)據(jù)類型的名稱。 precision：用于指定新的數(shù)據(jù)類型的精度。 scale：用于指定新的數(shù)據(jù)類型的小數(shù)位數(shù)。 NULL | NOT NULL：用于指定該列是否可以接受空值。 ADD | DROP ROWGUIDCOL ：用于指定在某列上添加或刪除 ROWGUIDCOL 屬性。 ADD：用于指定要添加一個或多個列定義、計算列定義或者表約束。computed_column_expression：用于指定一個計算列的計算表達式。 WIT

16、H CHECK | WITH NOCHECK：用于指定已經(jīng)存在于表中的數(shù)據(jù)是否需要使用新添加的或者剛啟用的 FOREIGN KEY 或 CHECK 約束進行驗證。 DROP CONSTRAINT constraint_name | COLUMN column_name ：用于指定從表中刪除的約束或者列的名稱。 CHECK | NOCHECK CONSTRAINT：用于指定啟用或禁用FOREIGN KEY或者CHECK約束。ALL ：用于指定使用 NOCHECK 選項禁用所有的約束，或者使用 CHECK 選項啟用所有約束。ENABLE | DISABLE TRIGGER：用于指定啟用或禁用觸發(fā)器

17、。當一個觸發(fā)器被禁用時，它對表的定義依然存在；然而，當在表上執(zhí)行 INSERT、UPDATE 或 DELETE 語句時，觸發(fā)器中的操作將不執(zhí)行，除非重新啟用該觸發(fā)器。ALL ：用于指定啟用或禁用表中所有的觸發(fā)器。trigger_name ：指定要啟用或禁用的觸發(fā)器名稱。例子5-4：創(chuàng)建了一個雇員信息表 其SQL語句的程序清單如下：create table employees(id char(8) primary keyname char(20) not null,department char(20) null,memo char(30) nullage int null,)alter tab

18、le employees add salary int null drop column agealter column memo varchar(200) null5.2.3 創(chuàng)建、刪除和修改約束 在SQL Server 2000中有5種約束：主鍵約束（primary key constraint）唯一性約束（unique constraint）檢查約束（check constraint）缺省約束（default constraint）外部鍵約束（foreign key constraint）1. 主鍵約束 主鍵的添加、刪除和修改操作方法有兩種：（一）企業(yè)管理器操作法，在企業(yè)管理器中，用右鍵

19、單擊要操作的數(shù)據(jù)庫表，從彈出的快捷菜單中選擇設計表選項 ，然后根據(jù)提示操作。（二）Transact-SQL語句操作法。 其語法形式如下：CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED （column_name,n）主鍵約束各參數(shù)的說明如下 constraint_name：用于指定約束的名稱，約束的名稱在數(shù)據(jù)庫中應該是唯一的。如果不指定，則系統(tǒng)會自動生成一個約束名。LUSTERED|NONCLUSTERED：用于指定索引的類型，即聚簇索引或者非聚簇索引，CLUSTERED為默認值。column_name：用于指定主鍵的列

20、名。主鍵最多由16個列組成。例子5-5 在執(zhí)行創(chuàng)建產(chǎn)品信息表的操作時，指定產(chǎn)品編號為主鍵值，并且創(chuàng)建一個聚簇索引。create table products(id char(10) not null,name char(20) not nullprice money default 0.01quantity smallint nullconstraint pk_id primary key clustered (id)2. 唯一性約束唯一性約束用于指定一個或者多個列的組合的值具有唯一性，以防止在列中輸入重復的值。當使用唯一性約束時，需要考慮以下幾個因素： 使用唯一性約束的字段允許為空值。一個表

21、中可以允許有多個唯一性約束。可以把唯一性約束定義在多個字段上。唯一性約束用于強制在指定字段上創(chuàng)建一個唯一性索引。 缺省情況下，創(chuàng)建的索引類型為非聚簇索引。創(chuàng)建和修改唯一性約束的操作方法創(chuàng)建和修改唯一性約束的操作方法有兩種：（一）企業(yè)管理器操作法，通過企業(yè)管理器可以完成創(chuàng)建和修改唯一性約束的操作。 （二）Transact-SQL語句操作法。CONSTRAINT constraint_name UNIQUE CLUSTERED|NONCLUSTERED（column_name,n）3. 檢查約束 一個列級檢查約束只能與限制的字段有關；一個表級檢查約束只能與限制的表中字段有關。一個表中可以定義多個檢

22、查約束。每個CREATE TABLE語句中每個字段只能定義一個檢查約束。在多個字段上定義檢查約束，則必須將檢查約束定義為表級約束。當執(zhí)行INSERT語句或者UPDATE語句時，檢查約束將驗證數(shù)據(jù)。檢查約束中不能包含子查詢。創(chuàng)建檢查約束常用的操作方法有兩種：1.企業(yè)管理器操作法。使用企業(yè)管理器創(chuàng)建檢查約束，與創(chuàng)建唯一性約束類似 。2.Transact-SQL語句操作法。檢查約束的Transact-SQL語句操作法，其語法形式如下：CONSTRAINT constraint_name CHECK NOT FOR REPLICATION (logical_expression)例子5-7： 創(chuàng)建了一

23、個學生信息表，其中輸入性別字段值時，只能接受“F”或者“M”，并且為phonenum字段創(chuàng)建檢查約束，限制只能輸入類似01080798654之類的數(shù)據(jù)，而不能隨意輸入其他數(shù)據(jù)。create table student(Id char(8)name char(8)sex char(2)phonenum intconstraint chk_sex check（sex in （F,M）Constraint chk_phonenum check（phonenum like (010)0-90-90-90-9 0-90-90-90-9）4. 缺省約束使用缺省約束時，應該注意以下幾點：每個字段只能定義一個

24、缺省約束。如果定義的缺省值長于其對應字段的允許長度，那么輸入到表中的缺省值將被截斷。不能加入到帶有IDENTITY屬性或者數(shù)據(jù)類型為timestamp的字段上。如果字段定義為用戶定義的數(shù)據(jù)類型，而且有一個缺省綁定到這個數(shù)據(jù)類型上，則不允許該字段有缺省約束。5. 外部鍵約束外部鍵約束用于強制參照完整性，提供單個字段或者多個字段的參照完整性。當使用外部鍵約束時，應該考慮以下幾個因素：外部鍵約束提供了字段參照完整性。外部鍵從句中的字段數(shù)目和每個字段指定的數(shù)據(jù)類型必須和REFERENCES從句中的字段相匹配。外部鍵約束不能自動創(chuàng)建索引，需要用戶手動創(chuàng)建。 用戶想要修改外部鍵約束的數(shù)據(jù)，必須有對外部鍵約

25、束所參考表的SELECT權限或者REFERENCES權限。參考同一表中的字段時，必須只使用REFERENCES子句，不能使用外部鍵子句。一個表中最多可以有31個外部鍵約束。在臨時表中，不能使用外部鍵約束。主鍵和外部鍵的數(shù)據(jù)類型必須嚴格匹配。5.2.4 查看表格 查看表格的定義2. 查看表格中的數(shù)據(jù) 3. 查看表格與其它數(shù)據(jù)庫對象的依賴關系 4. 利用系統(tǒng)存儲過程Sp_help查看表的信息 例子5-10： 顯示當前數(shù)據(jù)庫中所有對象的信息；顯示表word的信息。在查詢分析器（Query Analyzer）中它們對應的語句和運行結(jié)果如圖5-24(a)和圖5-24(b)所示。圖5-24(a) 當前數(shù)據(jù)

26、庫對象顯示窗口 圖5-24(b) 表word顯示窗口5.2.5 刪除表1. 利用企業(yè)管理器刪除表在企業(yè)管理器中，展開指定的數(shù)據(jù)庫和表格項，用右鍵單擊要刪除的表，從快捷菜單中選擇“除去表”選項，則會出現(xiàn)除去對象對話框。單擊“全部刪除”按鈕，即可刪除表。 2. 利用DROP TABLE語句刪除表DROP TABLE語句可以刪除一個表和表中的數(shù)據(jù)及其與表有關的所有索引、觸發(fā)器、約束、許可對象。DROP TABLE語句的語法形式如下：DROP TABLE table_name5.3.1 創(chuàng)建視圖5.3.2 修改和重命名視圖5.3.3 查看視圖信息、刪除視圖5.3.4 通過視圖修改記錄5.3 視圖操作視

27、圖是從一個或者多個表或視圖中導出的表，其結(jié)構(gòu)和數(shù)據(jù)是建立在對表的查詢基礎上的。和真實的表一樣，視圖也包括幾個被定義的數(shù)據(jù)列和多個數(shù)據(jù)行，但從本質(zhì)上講，這些數(shù)據(jù)列和數(shù)據(jù)行來源于其所引用的表。因此，視圖不是真實存在的基礎表而是一個虛擬表，視圖所對應的數(shù)據(jù)并不實際地以視圖結(jié)構(gòu)存儲在數(shù)據(jù)庫中，而是存儲在視圖所引用的表中。 視圖的概念：使用視圖的優(yōu)點和作用 可以使視圖集中數(shù)據(jù)、簡化和定制不同用戶對數(shù)據(jù)庫的不同數(shù)據(jù)要求。使用視圖可以屏蔽數(shù)據(jù)的復雜性，用戶不必了解數(shù)據(jù)庫的結(jié)構(gòu)，就可以方便地使用和管理數(shù)據(jù)，簡化數(shù)據(jù)權限管理和重新組織數(shù)據(jù)以便輸出到其他應用程序中。視圖可以使用戶只關心他感興趣的某些特定數(shù)據(jù)和他們

28、所負責的特定任務，而那些不需要的或者無用的數(shù)據(jù)則不在視圖中顯示。視圖大大地簡化了用戶對數(shù)據(jù)的操作。 視圖可以讓不同的用戶以不同的方式看到不同或者相同的數(shù)據(jù)集。在某些情況下，由于表中數(shù)據(jù)量太大，因此在表的設計時常將表進行水平或者垂直分割，但表的結(jié)構(gòu)的變化對應用程序產(chǎn)生不良的影響。 視圖提供了一個簡單而有效的安全機制。 5.3.1 創(chuàng)建視圖創(chuàng)建視圖時應該注意以下情況： 只能在當前數(shù)據(jù)庫中創(chuàng)建視圖。如果視圖引用的基表或者視圖被刪除，則該視圖不能再被使用，直到創(chuàng)建新的基表或者視圖。如果視圖中某一列是函數(shù)、數(shù)學表達式、常量或者來自多個表的列名相同，則必須為列定義名稱。不能在視圖上創(chuàng)建索引，不能在規(guī)則、缺

29、省、觸發(fā)器的定義中引用視圖。當通過視圖查詢數(shù)據(jù)時，SQL Server要檢查以確保語句中涉及的所有數(shù)據(jù)庫對象存在，而且數(shù)據(jù)修改語句不能違反數(shù)據(jù)完整性規(guī)則。視圖的名稱必須遵循標識符的規(guī)則，且對每個用戶必須是唯一的。此外，該名稱不得與該用戶擁有的任何表的名稱相同。1. 利用企業(yè)管理器創(chuàng)建視圖圖5-26 ，圖5-27(a) ，5-27(b) ，圖5-28 ，圖5-29 ，圖5-30 ，是利用企業(yè)管理器創(chuàng)建視圖的具體操作步驟，用戶可根據(jù)圖中提示創(chuàng)建視圖。圖5-26 選擇新建視圖選項對話框圖5-27 (a) 新建視圖對話框 圖5-27(b) 添加表菜單 圖5-28 添加表對話框圖5-29 選擇視圖字段對

30、話框圖5-30 視圖屬性對話框 2. 利用Transact-SQL語句中的CREATE VIEW命令創(chuàng)建視圖使用Transact-SQL語句中的CREATE VIEW創(chuàng)建視圖的語法形式如下：CREATE VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 使用Transact-SQL語句中的CREATE VIEW創(chuàng)建視圖的參數(shù)說明如下：view_name用于指定視圖的名稱，column用于指定視

31、圖中的字段名稱。WITH ENCRYPTION表示 SQL Server 加密包含 CREATE VIEW 語句文本在內(nèi)的系統(tǒng)表列。select_statement用于創(chuàng)建視圖的SELECT語句，利用SELECT命令可以從表中或者視圖中選擇列構(gòu)成新視圖的列。 WITH CHECK OPTION用于強制視圖上執(zhí)行的所有數(shù)據(jù)修改語句都必須符合由 select_statement 設置的準則。SCHEMABINDING表示在select_statement語句中如果包含表、視圖或者引用用戶自定義函數(shù)，則表名、視圖名或者函數(shù)名前必須有所有者前綴。VIEW_ METADATA表示如果某一查詢中引用該視圖

32、且要求返回瀏覽模式的元數(shù)據(jù)時，那么SQL Server將向DBLIB和OLE DB APIS返回視圖的元數(shù)據(jù)信息。例子5-12： 選擇表student和teacher中的部分字段和記錄創(chuàng)建視圖，并且限制表student中的記錄只能是名稱為“張三”的記錄集合，視圖定義為view_part，其程序清單如下：create view view_partas Select ,student.age,student.sex,,age,salaryfromstudent,teacherwhere =張三3. 利用向?qū)?chuàng)建視圖 圖5-31

33、 -圖5-38 是利用向?qū)?chuàng)建視圖 的具體步驟。圖5-31 選擇工具菜單中的向?qū)?圖5-32 歡迎使用創(chuàng)建視圖向?qū)υ捒驁D5-33 選擇數(shù)據(jù)庫對話框圖5-34 選擇對象對話框圖5-35 選擇字段對話框圖5-36 定義限制對話框圖5-37 命名視圖對話框圖5-38 完成創(chuàng)建視圖向?qū)υ捒?.3.2 修改和重命名視圖 1. 修改視圖（1）利用企業(yè)管理器 修改視圖。（2）使用ALTER VIEW語句修改視圖 。ALTER VIEW view_name (column,.n) WITHENCRYPTION ASselect_statement WITH CHECK OPTION 2. 重命名視圖

34、（1）利用企業(yè)管理器重命名視圖 。（2）可以使用系統(tǒng)存儲過程sp_rename修改視圖的名稱，該過程的語法形式如下： sp_rename old_name,new_name例子5-14：把視圖v_all重命名為v_part，其程序清單如下：sp_rename v_all,v_part5.3.3 查看視圖信息、刪除視圖 1. 查看視圖信息每當創(chuàng)建了一個新的視圖后，則在系統(tǒng)說明的系統(tǒng)表中就定義了該視圖的存儲，因此，可以使用系統(tǒng)存儲過程sp_help顯示視圖特征，使用sp_helptext顯示視圖在系統(tǒng)表中的定義，使用sp_depends顯示該視圖所依賴的對象。它們的語法形式分別如下：（1）sp_h

35、elp 數(shù)據(jù)庫對象名稱（2）sp_helptext 視圖（觸發(fā)器、存儲過程）（3） sp_depends 數(shù)據(jù)庫對象名稱使用SQL Server 查詢分析器和企業(yè)管理器來顯示視圖使用SQL Server 查詢分析器（Query Analyzer）可以方便地顯示視圖信息，如圖5-42所示，該對話框顯示了使用sp_helptext存儲過程顯示視圖v_employee的特征。另外，也可以使用企業(yè)管理器來顯示視圖的定義，如圖5-43所示。 利用企業(yè)管理器也可以查看視圖的輸出數(shù)據(jù)，如圖5-44所示。 圖5-42 顯示視圖信息對話框 圖5-43 視圖屬性對話框圖5-44 視圖輸出數(shù)據(jù)窗口2. 刪除視圖 （

36、1）使用企業(yè)管理器刪除視圖的操作方法為如圖5-45和圖5-46所示。 （2）使用Transact-SQL語句DROP VIEW刪除視圖的語法形式如下：DROP VIEW view_name ,n可以使用該命令同時刪除多個視圖，只需在要刪除的視圖名稱之間用逗號隔開即可。例子5-15：同時刪除視圖v_student和v_teacher，其程序清單如下：drop view v_student,v_teacher圖5-45 除去對象對話框圖5-46 顯示相關性對話框5.3.4 通過視圖修改記錄 使用視圖修改數(shù)據(jù)時，需要注意以下幾點： 修改視圖中的數(shù)據(jù)時，不能同時修改兩個或者多個基表，可以對基于兩個或多

37、個基表或者視圖的視圖進行修改，但是每次修改都只能影響一個基表。不能修改那些通過計算得到的字段。如果在創(chuàng)建視圖時指定了WITH CHECK OPTION選項，那么所有使用視圖修改數(shù)據(jù)庫信息時，必須保證修改后的數(shù)據(jù)滿足視圖定義的范圍。執(zhí)行UPDATE、DELETE命令時，所刪除與更新的數(shù)據(jù)必須包含在視圖的結(jié)果集中。如果視圖引用多個表時，無法用DELETE命令刪除數(shù)據(jù)。1. 插入數(shù)據(jù)記錄 例子5-16：首先創(chuàng)建了一個新的視圖v_employees，該視圖基于表employees創(chuàng)建。create view v_employees(number, name, age, sex, salary)asse

38、lect number, name, age, sex, salary from employeeswhere name=張三然后，通過執(zhí)行以下語句使用該視圖向表employees中添加一條新的數(shù)據(jù)記錄。Insert into v_employeesValues(001,李力,22,m,2000)2. 更新數(shù)據(jù)記錄使用視圖可以更新數(shù)據(jù)記錄，但應該注意的是，更新的只是數(shù)據(jù)庫中的基表。例子5-19：創(chuàng)建了一個基于表employees的視圖v_employees，然后通過該視圖修改表employees中的記錄。其程序清單如下：create view v_employeesas select * fr

39、om employeesupdate v_employeesset name=張然where name=張三3. 刪除數(shù)據(jù)記錄 使用視圖刪除記錄，可以刪除任何基表中的記錄，直接利用DELETE語句刪除記錄即可。但應該注意，必須指定在視圖中定義過的字段來刪除記錄。例子5-20：利用視圖v_employees刪除表employees中姓名為張然的記錄。其程序清單為：delete from v_employeeswhere name=張然5.4 索引操作5.4.1 創(chuàng)建索引 5.4.2 查看、修改和刪除索引一、什么叫索引 數(shù)據(jù)庫中的索引與書籍中的索引類似，在一本書中，利用索引可以快速查找所需信息，無

40、須閱讀整本書。在數(shù)據(jù)庫中，索引使數(shù)據(jù)庫程序無須對整個表進行掃描，就可以在其中找到所需數(shù)據(jù)。書中的索引是一個詞語列表，其中注明了包含各個詞的頁碼。而數(shù)據(jù)庫中的索引是某個表中一列或者若干列值的集合和相應的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單。 二、索引的作用通過創(chuàng)建唯一索引，可以保證數(shù)據(jù)記錄的唯一性?？梢源蟠蠹涌鞌?shù)據(jù)檢索速度?？梢约铀俦砼c表之間的連接，這一點在實現(xiàn)數(shù)據(jù)的參照完整性方面有特別的意義。在使用ORDER BY和GROUP BY子句中進行檢索數(shù)據(jù)時，可以顯著減少查詢中分組和排序的時間。使用索引可以在檢索數(shù)據(jù)的過程中使用優(yōu)化隱藏器，提高系統(tǒng)性能。 聚集索引與非聚集索引聚集索引對表的物

41、理數(shù)據(jù)頁中的數(shù)據(jù)按列進行排序，然后再重新存儲到磁盤上，即聚集索引與數(shù)據(jù)是混為一體的，它的葉節(jié)點中存儲的是實際的數(shù)據(jù)。 非聚集索引具有完全獨立于數(shù)據(jù)行的結(jié)構(gòu)，使用非聚集索引不用將物理數(shù)據(jù)頁中的數(shù)據(jù)按列排序。非聚集索引的葉節(jié)點存儲了組成非聚集索引的關鍵字值和行定位器。 5.4.1 創(chuàng)建索引 SQL Server 2000創(chuàng)建索引的方法（1）利用企業(yè)管理器中的索引向?qū)?chuàng)建索引。（2）利用企業(yè)管理器直接創(chuàng)建索引。（3）利用Transact-SQL語句中的CREATE INDEX命令創(chuàng)建索引。（4）利用企業(yè)管理器中的索引優(yōu)化向?qū)?chuàng)建索引。 1. 利用索引向?qū)?chuàng)建索引 如圖5-47 -圖5-52 所示。圖

42、5-47 歡迎使用創(chuàng)建索引向?qū)υ捒驁D5-48 選擇數(shù)據(jù)庫和對象對話框圖5-49 顯示當前索引信息對話框圖5-50 選擇創(chuàng)建索引的列對話框圖5-51 指定索引選項對話框圖5-52 指定索引名稱對話框2. 利用企業(yè)管理器直接創(chuàng)建索引其具體步驟如下：（）在企業(yè)管理器中，展開指定的服務器和數(shù)據(jù)庫，選擇要創(chuàng)建索引的表，用右鍵單擊該表，從彈出的快捷菜單中選擇所有任務項的管理索引選項（如圖5-53所示），就會出現(xiàn)管理索引對話框，如圖5-54所示。在該對話框中，可以選擇要處理的數(shù)據(jù)庫和表 。（）單擊“新建”按鈕，則出現(xiàn)新建索引對話框，如圖5-55所示。 （）選擇完成后單擊“確定”按鈕，即可生成新的索引；單擊

43、“取消”按鈕，則取消新建索引的操作。圖5-53 選擇管理索引選項對話框圖5-54 管理索引對話框圖5-55 新建索引對話框3. 利用Transact-SQL語句中的CREATE INDEX命令創(chuàng)建索引其語法形式如下：CREATE UNIQUE CLUSTERED| NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC ,.n ) withPAD_INDEX,FILLFACTOR=fillfactor,IGNORE_DUP_KEY,DROP_EXISTING ,STATISTICS_NORECOMPUTE,SORT_I

44、N_TEMPDB ON filegroup CREATE INDEX命令創(chuàng)建索引各參數(shù)說明如下： UNIQUE：用于指定為表或視圖創(chuàng)建唯一索引，即不允許存在索引值相同的兩行。 CLUSTERED：用于指定創(chuàng)建的索引為聚集索引。 NONCLUSTERED：用于指定創(chuàng)建的索引為非聚集索引。index_name：用于指定所創(chuàng)建的索引的名稱。 table：用于指定創(chuàng)建索引的表的名稱。 view：用于指定創(chuàng)建索引的視圖的名稱。 ASC|DESC：用于指定具體某個索引列的升序或降序排序方向。 Column：用于指定被索引的列。PAD_INDEX：用于指定索引中間級中每個頁（節(jié)點）上保持開放的空間。FILL

45、FACTOR = fillfactor：用于指定在創(chuàng)建索引時，每個索引頁的數(shù)據(jù)占索引頁大小的百分比，fillfactor的值為1到100。IGNORE_DUP_KEY：用于控制當往包含于一個唯一聚集索引中的列中插入重復數(shù)據(jù)時SQL Server所作的反應。 DROP_EXISTING：用于指定應刪除并重新創(chuàng)建已命名的先前存在的聚集索引或者非聚集索引。 STATISTICS_NORECOMPUTE：用于指定過期的索引統(tǒng)計不會自動重新計算。 SORT_IN_TEMPDB：用于指定創(chuàng)建索引時的中間排序結(jié)果將存儲在 tempdb 數(shù)據(jù)庫中。 ON filegroup：用于指定存放索引的文件組。 例子5

46、-21：例子5-21：為表employees創(chuàng)建了一個唯一聚集索引，其程序清單如下：CREATE UNIQUE CLUSTERED INDEX number_ind ON employees (number)with pad_index, fillfactor=20, ignore_dup_key, drop_existing, statistics_norecompute例子5-22：為表employees創(chuàng)建了一個復合索引，其程序清單如下：create index employees_cpl_indon employees(name,age)with pad_index, fillfact

47、or=504. 利用索引優(yōu)化向?qū)?chuàng)建索引索引優(yōu)化向?qū)Э梢酝瓿梢韵聨追矫娴墓ぷ鳎?（1） 根據(jù)給定的工作負荷，通過使用查詢優(yōu)化器分析該工作負荷中的查詢，為數(shù)據(jù)庫推薦最佳索引組合。（2）分析所建議的更改將會產(chǎn)生的影響，包括索引的使用，查詢在表之間的分布，以及查詢在工作負荷中的性能。（3）推薦為執(zhí)行一個小型的問題查詢集而對數(shù)據(jù)庫進行優(yōu)化的方法。（4）通過設定高級選項如磁盤空間約束、最大查詢語句數(shù)和每個索引最多對應字段數(shù)等，允許定制推薦方式。利用索引優(yōu)化向?qū)?chuàng)建和優(yōu)化索引的具體步驟如下： 如圖5-56 -圖5-65 所示。圖5-56 歡迎使用索引優(yōu)化向?qū)υ捒驁D5-57 選擇服務器和數(shù)據(jù)庫對話框圖5-

48、58 連接服務器對話框圖5-59 選擇服務器對話框圖5-61 修改缺省優(yōu)化參數(shù)對話框圖5-62 選擇要優(yōu)化的表對話框圖5-63 索引建議對話框圖5-64 索引分析對話框圖5-65 完成索引優(yōu)化向?qū)υ捒?.4.2 查看、修改和刪除索引 1. 利用企業(yè)管理器查看、修改和刪除索引要查看和修改索引的詳細信息，可以在企業(yè)管理器中，展開指定的服務器和數(shù)據(jù)庫項，用右鍵單擊要查看的表，從彈出的快捷菜單中選擇所有任務子菜單中的“管理索引”選項，則會出現(xiàn)管理索引對話框。選擇要查看或者修改的索引，單擊“編輯”按鈕，就會出現(xiàn)修改索引對話框。在該對話框中，可以修改索引的大部分設置，還可以直接修改其SQL腳本，只需按下

49、“編輯SQL”按鈕，即可出現(xiàn)編輯SQL腳本對話框，其中可以編輯、測試和運行索引的SQL腳本。要刪除索引，可以在企業(yè)管理器中，從管理索引對話框中或者表的屬性對話框中，選擇要刪除的索引，單擊“刪除”按鈕，即可刪除索引。2. 用系統(tǒng)存儲過程查看和更改索引名稱系統(tǒng)存儲過程sp_helpindex可以返回表的所有索引信息，其語法形式如下：sp_helpindex objname=name其中objname=name參數(shù)用于指定當前數(shù)據(jù)庫中的表的名稱。另外，系統(tǒng)存儲過程sp_rename可以用來更改索引的名稱，其語法形式如下：sp_renameobjname=object_name,newname=new

50、_name , objtype = object_type 例子5-23：更改employees表中的索引employees_name_index名稱為employees_name_ind，其程序清單如下：Exec sp_rename employees.employees_name_index, employees_name_ind, index3. 使用Transact-SQL語句中的DROP INDEX命令刪除索引當不再需要某個索引時，可以將其刪除，DROP INDEX命令可以刪除一個或者多個當前數(shù)據(jù)庫中的索引，其語法形式如下：DROP INDEX table.index | view.

51、index ,.n 其中，table | view用于指定索引列所在的表或索引視圖；index用于指定要刪除的索引名稱。注意，DROP INDEX命令不能刪除由CREATE TABLE或者ALTER TABLE命令創(chuàng)建的主鍵或者唯一性約束索引，也不能刪除系統(tǒng)表中的索引。5.5 存儲過程5.5.1 創(chuàng)建存儲過程5.5.2 執(zhí)行存儲過程5.5.3 查看和修改存儲過程5.5.4 重命名和刪除存儲過程 存儲過程的概念SQL Server提供了一種方法，它可以將一些固定的操作集中起來由SQL Server數(shù)據(jù)庫服務器來完成，以實現(xiàn)某個任務，這種方法就是存儲過程。在SQL Server中存儲過程分為兩類：

52、即系統(tǒng)提供的存儲過程和用戶自定義的存儲過程。 5.5.1 創(chuàng)建存儲過程 在SQL Server中，可以使用三種方法創(chuàng)建存儲過程 ：使用創(chuàng)建存儲過程向?qū)?chuàng)建存儲過程。利用SQL Server 企業(yè)管理器創(chuàng)建存儲過程。使用Transact-SQL語句中的CREATE PROCEDURE命令創(chuàng)建存儲過程。 創(chuàng)建存儲過程時，需要確定存儲過程的三個組成部分：所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其它存儲過程的語句。返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。1. 使用創(chuàng)建存儲過程向?qū)?chuàng)建存儲過程 在企業(yè)管理器中，選擇工具菜單中的向?qū)нx項，選擇“創(chuàng)建存儲過程向?qū)?/p>

53、”（如圖3-1所示），則出現(xiàn)歡迎使用創(chuàng)建存儲過程向?qū)υ捒?，如圖5-71所示。根據(jù)圖5-71-圖5-76提示可完成創(chuàng)建存儲過程。圖3-1 新建SQL Server組圖5-71 歡迎使用創(chuàng)建存儲過程向?qū)υ捒驁D5-72 選擇數(shù)據(jù)庫對話框 圖5-73 選擇數(shù)據(jù)庫對象對話框圖5-74 完成創(chuàng)建存儲過程向?qū)υ捒驁D5-75 編輯存儲過程屬性對話框圖5-76 編輯存儲過程SQL對話框2. 使用SQL Server 企業(yè)管理器創(chuàng)建存儲過程 （）在SQL Server企業(yè)管理器中，選擇指定的服務器和數(shù)據(jù)庫，用右鍵單擊要創(chuàng)建存儲過程的數(shù)據(jù)庫，在彈出的快捷菜單中選擇“新建”選項，再選擇下一級菜單中的“存儲過程”

54、選項，如圖5-77所示；或者用右鍵單擊存儲過程圖標，從彈出的快捷菜單中選擇“新建存儲過程”選項，如圖5-78所示。均會出現(xiàn)創(chuàng)建存儲過程對話框，如圖5-79所示。（）在文本框中可以輸入創(chuàng)建存儲過程的T_SQL語句，單擊“檢查語法”，則可以檢查語法是否正確；單擊“確定”按鈕，即可保存該存儲過程。如果要設置權限，單擊“權限”按鈕，如圖5-80所示。圖5-77 選擇新建存儲過程對話框（1）圖5-78 選擇新建存儲過程對話框（2）圖5-79 新建存儲過程對話框圖5-80 設置權限對話框3. 使用Transact-SQL語句中的CREATE PROCEDURE命令創(chuàng)建存儲過程創(chuàng)建存儲過程前，應該考慮下列幾

55、個事項： 不能將 CREATE PROCEDURE 語句與其它 SQL 語句組合到單個批處理中。創(chuàng)建存儲過程的權限默認屬于數(shù)據(jù)庫所有者，該所有者可將此權限授予其他用戶。存儲過程是數(shù)據(jù)庫對象，其名稱必須遵守標識符規(guī)則。只能在當前數(shù)據(jù)庫中創(chuàng)建存儲過程。一個存儲過程的最大尺寸為128M。使用CREATE PROCEDURE創(chuàng)建存儲過程的語法形式如下： CREATEPROCEDUREprocedure_name;number parameterdata_type VARYING=defaultOUTPUT,.n WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION

56、 FORREPLICATION AS sql_statement .n 用CREATE PROCEDURE創(chuàng)建存儲過程的語法參數(shù)的意義如下： procedure_name：用于指定要創(chuàng)建的存儲過程的名稱。 number：該參數(shù)是可選的整數(shù)，它用來對同名的存儲過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。 parameter：過程中的參數(shù)。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數(shù)。 data_type：用于指定參數(shù)的數(shù)據(jù)類型。 VARYING：用于指定作為輸出OUTPUT參數(shù)支持的結(jié)果集。 Default：用于指定參數(shù)的默認值。 OU

57、TPUT：表明該參數(shù)是一個返回參數(shù)。 RECOMPILE：表明 SQL Server 不會保存該存儲過程的執(zhí)行計劃 。ENCRYPTION ：表示 SQL Server 加密了 syscomments 表，該表的text字段是包含 CREATE PROCEDURE 語句的存儲過程文本。 FOR REPLICATION：用于指定不能在訂閱服務器上執(zhí)行為復制創(chuàng)建的存儲過程。 AS：用于指定該存儲過程要執(zhí)行的操作。sql_statement：是存儲過程中要包含的任意數(shù)目和類型的 Transact-SQL 語句。5.5.2 執(zhí)行存儲過程 直接執(zhí)行存儲過程可以使用EXECUTE命令來執(zhí)行，其語法形式如下

58、：EXECUTE return_status=procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT,.n WITH RECOMPILE 例子5-27： 使用 EXECUTE 命令傳遞單個參數(shù)，它執(zhí)行 showind 存儲過程，以 titles 為參數(shù)值。showind 存儲過程需要參數(shù) (tabname)，它是一個表的名稱。其程序清單如下：EXEC showind titles當然，在執(zhí)行過程中變量可以顯式命名：EXEC showind tabname = titles如果這是 isql

59、腳本或批處理中第一個語句，則 EXEC 語句可以省略：showind titles或者showind tabname = titles5.5.3 查看和修改存儲過程 查看存儲過程 存儲過程被創(chuàng)建之后，它的名字就存儲在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中?？梢允褂檬褂闷髽I(yè)管理器或系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程。（1）使用企業(yè)管理器查看用戶創(chuàng)建的存儲過程 在企業(yè)管理器中，打開指定的服務器和數(shù)據(jù)庫項，選擇要創(chuàng)建存儲過程的數(shù)據(jù)庫，單擊存儲過程文件夾，此時在右邊的頁框中顯示該數(shù)據(jù)庫的所有存儲過程。用右鍵單擊要查看的存儲過程，從彈出的快捷菜單中選擇屬性選項，此

60、時便可以看到存儲過程的源代碼。 （2）使用系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程 可供使用的系統(tǒng)存儲過程及其語法形式如下：sp_help：用于顯示存儲過程的參數(shù)及其數(shù)據(jù)類型sp_help objname= name參數(shù)name為要查看的存儲過程的名稱。 sp_helptext：用于顯示存儲過程的源代碼 sp_helptext objname= name參數(shù)name為要查看的存儲過程的名稱。sp_depends：用于顯示和存儲過程相關的數(shù)據(jù)庫對象sp_depends objname=object參數(shù)object為要查看依賴關系的存儲過程的名稱。sp_stored_procedures：用于返回當前