零點起飛學SQL Sever之SQL Server 28數據表和索引

1、第5章 SQL Server 2008數據表和索引在SQL Server 2008中，表是用來存儲數據的對象。通常將同一類或者是相關數據存放在一個數據表中。例如，所有的學生信息存放在一個表中，而成績信息都存放到另一個表中。用戶可以通過多種方式訪問數據庫中的數據。掌握SQL Server 2008數據表的相關知識是非常重要的。本章主要包括數據表的基礎知識、數據表定義的管理、表中數據的管理以及索引的管理等知識。15.1 數據表簡介為了更好地理解和掌握SQL Server 2008中數據表的設計與使用，本節(jié)首先簡要介紹數據表及其相關的基礎知識。25.1.1 什么是數據表數據表是SQL Server

2、2008的數據庫對象，它存儲著數據庫的所有數據。在數據表中，數據以行和列的形式存儲在規(guī)范化的二維表格中。SQL Server 2008數據表主要由行和列構成。教務管理系統(tǒng)數據庫（Practice_JWGL）中的學生信息表的部分截圖。35.1.2 數據完整性定義表除了要定義每一列的名稱、數據類型和長度之外，還可以為列定義一些其他屬性。這些屬性也很重要，它們可以保證數據表的數據完整性和參照完整性。在SQL Server 2008中用于保證以上兩種完整性的相關技術有如下3種。1約束2規(guī)則（Rules）3默認值45.1.3 SQL Server 2008特殊表SQL Server 2008除了提供用戶

3、定義的標準數據表外，還提供了一些特殊用途的表：分區(qū)表、臨時表和系統(tǒng)表。1分區(qū)表2臨時表3系統(tǒng)表55.1.4 規(guī)劃數據表在創(chuàng)建數據表之前，用戶首先要做好表的設計工作，然后再創(chuàng)建表，最后輸入數據。在創(chuàng)建表及其對象之前，最好先確定表的下列特征：表要包含的數據的類型。表中的列數，每一列中數據的類型和長度（如果必要）。哪些列允許空值。是否需要使用以及何處使用約束、規(guī)則和默認值。所需索引的類型，哪里需要索引，哪些列是主鍵，哪些是外鍵。1列的數據類型的選擇2自動生成列的編號或標識符3需要數據計算的列4加強數據完整性65.1.5 系統(tǒng)數據類型系統(tǒng)數據類型是SQL Server預先定義好的，可以直接使用。在實際

4、使用中，SQL Server會自動限制每個系統(tǒng)數據類型的值的范圍。當插入數據庫中的值超過了數據類型允許的范圍，SQL Server系統(tǒng)就會報錯。SQL Server 2008提供了7類，共26種系統(tǒng)數據類型。1精確數字2近似數字3時間和日期4字符串5Unicode字符串6二進制字符串7其他數據類型8特殊數據類型75.1.6 用戶自定義數據類型SQL Server 2008允許用戶自定義數據類型，用戶自定義數據類型是建立在SQL Server 2008系統(tǒng)數據類型基礎上的。當用戶定義一種數據類型時，需要指定該類型的名稱、建立在其上的系統(tǒng)數據類型以及是否允許為空等。SQL Server 2008為

5、用戶提供了兩種方法來創(chuàng)建自定義數據類型，即使用SQL Server Management Studio圖形界面或者通過系統(tǒng)存儲過程sp_addtype。85.1.6 用戶自定義數據類型1使用SQL Server Management Studio創(chuàng)建用戶定義數據類型例如，在SQL Server Management Studio中，定義一個名稱為User_ID的數據類型。2利用系統(tǒng)存儲過程創(chuàng)建用戶自定義數據類型SQL Server 2008系統(tǒng)存儲過程sp_addtype為用戶提供了用Transact-SQL語句創(chuàng)建自定義數據類型的途徑，其語法形式如下：sp_addtype typename=

6、 type,phystype= system_data_type, nulltype= null_type, owner= owner_name【實例5-1】：自定義一個地址（address）數據類型，最多可以包含20個字符。95.1.6 用戶自定義數據類型3使用SQL Server Management Studio刪除用戶定義數據類型在SQL Server Management Studio圖形界面中，右擊“用戶定義的數據類型”節(jié)點下的某個用戶定義數據類型在彈出的快捷菜單中選擇“刪除”命令，打開“刪除對象”對話框，即可刪除該數據類型。4利用系統(tǒng)存儲過程刪除用戶自定義數據類型使用系統(tǒng)存儲過程

7、sp_droptype刪除自定義數據類型的語法如下：sp_droptype typename= type【實例5-2】：刪除自定義的地址（address）數據類型。105.2 數據表定義在設計完數據表后，接下來的任務就是創(chuàng)建數據表。用戶可以使用SQL Server Management Studio圖形化工具創(chuàng)建表，也可以使用Transact-SQL語句方便快速地創(chuàng)建數 據表。115.2.1 使用SSMS設計數據表使用SQL Server Management Studio圖形工具，可以完成SQL Server 2008數據表的建立、修改、查看和刪除等絕大多數的工作，這是數據庫管理員最常用的操

8、作方式。1創(chuàng)建數據表使用SQL Server Management Studio中的表設計器，用戶可以方便地創(chuàng)建數據表。2修改數據表數據庫管理員可以根據需要，修改已經建立的數據表。對數據表的修改主要包括修改列屬性、添加和刪除列以及修改約束等選項。修改數據表同樣可以在“表設計器”窗口中進行3重命名數據表在SQL Server Management Studio管理工具中，用戶可以對創(chuàng)建后的數據表進行重命名。4查看數據表屬性在SQL Server Management Studio管理工具中，用戶可以查看表的物理信息，以及常規(guī)設置等屬性信息。5刪除數據表用戶可以刪除不再需要或者設計錯誤的數據表。刪

9、除表后，該表的結構定義、數據、全文索引、約束和索引都將從數據庫中刪除，原來存儲該表及其索引的空間可以用來存儲其他數據表。使用SQL Server Management Studio刪除數據表。6編寫表腳本在SQL Server Management Studio管理工具中，用戶可以依據圖形化界面所設計后的數據表的屬性，來自動生成對應的Transact-SQL腳本語句。125.2.2 使用SSMS維護數據表數據庫的系統(tǒng)管理員，日常工作中最基本的一項工作就是對數據表的維護。它包括修改數據表的結構、改變表的排列順序以及數據的備份等工作。SQL Server Management Studio提供了方

10、便的圖形化界面幫助用戶完成這些工作。下面列舉一些常見操作，說明其具體的操作步驟。1在數據表中插入新列2改變數據表中列的排序規(guī)則3自動編號列和標識符列4可計算的列5刪除數據列135.2.3 使用Transact-SQL設計與維護數據表除了使用SQL Server Management Studio工具之外，用戶還可以使用Transact-SQL語句來設計與維護數據表。下面簡要介紹如何使用Transact-SQL語句來創(chuàng)建和管理數據表。1使用CREATE TABLE語句創(chuàng)建表Transact-SQL語言提供了創(chuàng)建數據表的語句CREATE TABLE，其語法格式如下：CREATE TABLE dat

11、abase_name.table_name( column_name data_type NULL|NOT NULL DEFAULT constant_expression PRIMARY KEY | UNIQUE, .n,-表示可以定義許多列)FOREIGN KEY(column_name1 ,.n )REFERENCES referenced_table_name(ref_column_name ,.n )【實例5-3】：在Practice_JWGL數據庫中創(chuàng)建一個“學生信息”表，并確定學號為主鍵。145.2.3 使用Transact-SQL設計與維護數據表2使用ALTER TABLE語句

12、修改表Transact-SQL語言提供了修改數據表定義的語句ALTER TABLE，其語法格式如下：ALTER TABLE database_name.table_name( ALTER COLUMN column_newdesp-修改已經存在列的屬性 |ADD new_column_desp-新增加一列 |DROP COLUMN column_name-刪除列)【實例5-4】：在Practice_JWGL數據庫中創(chuàng)建一個學生信息表Student，然后增加一個出生日期字段（出生日期，Datatime，Null），修改姓名的長度為20，最后刪除籍貫字段。3利用系統(tǒng)存儲過程sp_rename重命名

13、表和表中的數據列系統(tǒng)存儲過程sp_rename可以對數據表和數據表中的數據列進行重命名操作，其語法格式如下：sp_rename old_name,new_name【實例5-5】：將前面創(chuàng)建的Student數據表，重命名為Students?！緦嵗?-6】：將前面創(chuàng)建的Student數據表中的“班級編號”數據列，重命名為“班號”。155.2.3 使用Transact-SQL設計與維護數據表4使用DROP TABLE語句刪除表Transact-SQL語句中刪除表的語句是DROP TABLE，其語法格式如下：DROP TABLE database_name.table_name ,.n【實例5-7】：

14、將前面創(chuàng)建的Student數據表，從Practice_JWGL數據中刪除。5利用系統(tǒng)存儲過程SP_help查看表的信息系統(tǒng)存儲過程SP_help可以提供指定數據庫對象的信息，也可以提供系統(tǒng)或者用戶定義的數據庫類型的信息，其語法形式如下：sp_help objname=name【實例5-8】：分別查看Practice_JWGL數據庫中所有對象信息和學生信息表信息。165.3 管理數據表中數據數據庫管理的主要內容是數據，用戶或管理員會經常操作、查詢表和視圖中的數據。SQL Server Management Studio工具為用戶提供了簡便的圖形化方式來實現這一功能，用戶也可以在查詢編輯器中使用T

15、ransact-SQL中的數據操縱語言（DML）來實現。175.3.1 查看數據使用SQL Server Management Studio工具可以查看數據表中的所有記錄信息，也可以只查看自己需要的數據。1查看數據表中的所有記錄用戶可以使用SQL Server Management Studio工具，很方便地瀏覽數據表中的所有數據。2查詢數據表中的記錄SQL Server Management Studio工具還提供了“視圖”工具，用戶可以查看指定條件的數據表記錄。185.3.2 更新數據對數據表中數據的維護操作，是SQL Server 2008用戶要掌握的基本操作。一般的維護過程包括查找表，

16、根據查詢條件查看記錄，對表數據記錄進行添加、修改和刪除等。1添加新的記錄使用SQL Server Management Studio工具的圖形界面，可以非常方便地向數據表增加記錄。2修改現有記錄修改現有的數據表中的記錄，也是數據庫管理員的一項基本技能。在實際應用中，用戶一般是通過客戶端應用程序對表中的數據進行修改。3刪除記錄刪除現有表中的數據記錄和修改表中的數據行相同。在實際應用中，用戶一般是通過客戶端應用程序對表中的數據進行刪除操作。195.4 索引索引是數據庫中的重要數據對象，通過建立索引可以提高數據查詢速度或者其他操作的效率。SQL Server 2008數據庫提供了多種索引類型。205

17、.4.1 什么是索引索引可以創(chuàng)建在任意表和視圖的列字段上。索引中包含鍵值，這些鍵值存儲在一種數據結構（B-樹）中，通過鍵值可以快速地找到與鍵值相關的數據記錄。SQL Server提供了兩種形式的索引：聚集索引（Clustered）和非聚集索引（Nonclustered）。聚集索引根據鍵的值對行進行排序，所以每個表只能有一個聚集索引。非聚集索引不根據鍵值排序，索引數據結構與數據行是分開的。SQL Server 2008提供的索引類型具體包括以下幾種：聚集索引非聚集索引唯一索引包含列的索引全文（full-text）索引XML索引。215.4.1 什么是索引索引的建立有利也有弊，建立索引可以提高查詢速度，但過多地建立索引會占據很多的磁盤空間。所以在建立索引時，數據庫管理員必須權衡利弊，考慮讓索引帶來的有利效果大于帶來的弊病。下列情況適合建立索引。經常被查詢搜索的列，如經常在WHERE子句中出現的列。在ORDER BY子句中使用的列。外鍵或主鍵列。值唯一的列。下列情況不適合建立索引。在查詢中很少被引用的列。包含太多重復值的列。數據類型為bit、text、image等的列不能建立索引。225.4.2 維護索引用戶可以使用SQL Server Management Studio工具或者Transact-SQL語句創(chuàng)建和管理索引。下面介紹如何使用SQL Server Managemen