第4章表、視圖及索引的基本操作4.1SQLServer中的數據類型4.2表的基本操作4.3視圖的基本操作4.4索引的基本操作4.5本章小結

4.1SQLServer中的數據類型

4.1.1數據類型

在建立SQLServer的表格時，要求用戶先對數據庫列進行數據類型的確定。定義表列的數據類型后，數據列的數據類型將作為表的永久屬性加以保存，普通用戶是無法對其進行修改的。因此，建立自己的表格前，先全面理解SQLServer數據類型并精心選擇表格列的數據類型，是保證創建的數據表格滿足設計需求和表格性能良好的前提。

1．系統數據類型

1)整型數據類型

整型數據類型用來存儲整數。在SQLServer系統中支持3種類型的整型數據類型。

Int或Integer：整數型，長度為4個字節，可以存儲從-2147683647～2147683647之間的所有正負整數。

Smallint：短整數類型，長度為2個字節，存儲范圍較

Int或Integer小，可以存儲從-32768～32767之間的所有正負整數。

2)浮點數據類型

浮點數據類型可以用來存儲含小數的十進制數。浮點類型的數據在SQLServer中采用只入不舍的方式進行存儲。

Real：長度為4個字節。可以存儲-3.40E+38～3.40E+38之間的十進制數值，最大可以有7位精確位數。

Float：可以精確到第15位小數，其范圍從-1.79E-308～1.79E?+?308。

Decimal和Numeric：Decimal數據類型和Numeric數據類型完全相同，它們可以提供小數所需要的實際存儲空間，但也有一定的限度，可以用2～17個字節來存儲從-1038～1038-1之間的數值。

3)字符數據類型

字符數據類型可以用來存儲各種由字母、數字和符號組成的字符串。在SQL中輸入字符數據時，必須將數據引在單引號中，否則SQL不能接受該字符數據。

Char：其定義形式為char(n)，每個字符和符號占用一個字節的存儲空間。其中n表示該字符數據的字節長度，注意該長度只能取1～255之間的數。長度超過規定范圍，則系統只取規定范圍內的字符串；長度不足規定范圍時，則字符串后面的位置將被空格填充。

Varchar：其定義形式為varchar(n)。用Varchar數據類型可以存儲長達255個字符的可變長度字符串。

Nchar：其定義形式為nchar(n)，用于支持存儲固定長度的國際上的非英語語種字符串。

Nvarchar：其定義形式為nvarchar(n)，用于支持存儲可變長度的國際上的非英語語種字符串。

4)日期和時間數據類型

日期和時間數據類型用于存儲日期和時間數據。它有下面兩種形式，區別在于存儲長度以及所代表的時間范圍和存儲精確度的不同。

Datetinme：用于存儲日期和時間的結合體。它可以存儲從公元1753年1月1日零時起到公元9999年12月31日23時59分59秒之間的所有日期和時間。

Smalldatetime：與Datetime數據類型類似，但其日期時間范圍較小，它存儲從1900年1月1日到2079年6月6日內的日期。

5)文本和圖形數據類型

Text：文本數據類型是用來存儲可變長度的文本數據。Text存儲大量文本數據時，其容量理論上為231-1(2147483647)個字節，但實際應用時要根據硬盤的存儲空間而定。用戶在向Text類型的數據項中寫入數據時，必須將寫入的數據引在單引號下，如‘studentnameis:’。

Ntext：與Text數據類型類似，存儲在其中的數據通常是直接能輸入到顯示設備上的字符，顯示設備可以是顯示器、窗口或打印機。

Image：用于存儲照片、目錄圖片或者圖畫，其理論容量為231-1個字節。

6)貨幣數據類型

Money：用于存儲貨幣值，存儲在Money數據類型中的數值以一個正數部分和一個小數部分分別存儲在兩個4字節的整型值中，存儲范圍為-922337213685477.5808～922337213685477.5808，精度為貨幣單位的萬分之一。

Smallmoney：與Money數據類型類似，但其存儲的貨幣值范圍比Money數據類型小，其存儲范圍為-214748.3468～214748.3467。

7)位數據類型

Bit：稱為位數據類型，其數據有兩種取值：0和1，長度為1字節，用來做為邏輯變量使用。

8)二進制數據類型

Binary：其定義形式為Binary(n)，數據的存儲長度是固定的，即n+4字節，當輸入的二進制數據長度小于n時，余下部分填充0。

Varbinary：其定義形式為Varbinary(n)，數據的存儲長度是變化的，它為實際所輸入數據的長度加上4個字節。其他含義同Binary。

9)特殊數據類型

Timestamp：稱為時間戳數據類型，它提供數據庫范圍內的唯一值，反應數據庫中數據修改的相對順序，相當于一個單調上升的計數器。

Uniqueidentifier：用于存儲一個16字節長的二進制數據類型，它是SQLServer根據計算機網絡適配器地址和CPU時鐘產生的唯一號碼而生成的全局唯一標志符號代碼(GloballyUniqueIdentifier,GUID)。

10)新增數據類型

Bigint：用于存儲-263-1(-9223372036854775807)～263-1(9223372036854775807)之間的所有正負整數。

sql_variant：用于存儲除文本、圖形數據(text、ntext、image)和timestamp類型數據外的其他任何合法的SQLServer數據。此數據類型大大方便了SQLServer的開發工作。

Table：用于存儲對表或視圖處理后的結果集。這一新類型使得變量可以存儲一個表，從而使函數或過程返回查詢結果更加方便、快捷。

2．用戶定義數據類型

1)利用系統存儲過程定義和刪除用戶自定義數據類型

語法：

(1)定義用戶自定義數據類型：

sp-addtype[@typename]type，

[@phystype]system-data-type

[，[@nulltype=]null-type]

[，[@owner=]owner-name]

(2)刪除用戶自定義數據類型：

sp-droptype[@typename=]type

【例1】利用sp-addtype定義一個新的數據類型phone。

Execsp-addtypephone，‘char(10)’

所定義的數據類型名稱是phone，基于char(10)。

【例2】利用sp-addtype定義一個新的數據類型birthday。

Execsp-addtypebirthday，DATETIME，‘NULL’

所定義的數據類型名稱是birthday，基于DATETIME。

2)利用企業管理器定義和刪除用戶自定義數據類型

以上面的用戶自定義數據類型phone為例，說明如何通過企業管理器來定義和刪除。圖4-1創建用戶自定義數據類型圖4-2創建用戶自定義數據類型phone

3)使用用戶自定義數據類型

用戶自定義數據類型創建好后，可以像系統數據類型一樣使用。下面的創建表student-info的createtable定義語句中就使用了上面所定義的數據類型(注意黑體字)。4.1.2空置的含義

空值(NULL)不等于0，代表空白或零長度的字符串，意味著沒有輸入，表明未知或未定義。使用時應注意避免用空值直接參與運算，盡量不要允許用空值(用默認值解決沒有輸入的問題)。

4.2表的基本操作

4.2.1創建表

在SQLServer中，表存儲在數據庫中。數據庫建立后，接下來就該建立存儲數據的表，并對表進行修改和刪除。

1．使用企業管理器創建表

使用企業管理器建立一個表的過程非常簡單。下面以在學生成績管理數據庫中建立學生表為例，說明建立表的具體操作步驟。圖4-3學生表的各個字段圖4-4保存學生表

圖4-5課程表的結構

圖4-6成績表的結構

2．使用Transact-SQL創建表

createtabletable_name

(column_namedate_type[null|notnull][,…n])

【例3】use學生成績管理

createtablexs

(學號char(10)notnullprimarykey,

姓名char(8)notnull,

專業名char(16)null,

性別bitnotnull,

出生日期smalldatetimenotnull,

總學分tinyintnull,

備注textnull)4.2.2修改表結構

1．使用企業管理器修改表結構

表結構的修改和查看的操作步驟是相同的，下面給學生表中加入“民族”字段。圖4-7插入“民族”字段

2．使用Transact-SQL修改表結構

1)修改表結構——添加列

向表中增加一列時，應使新增加的列有默認值或允許為空值，SQLServer將向表中已存在的行填充新增列的默認值或空值，如果既沒有提供默認值也不允許為空值，那么新增列的操作將出錯。向表中添加列的語句格式如下：

altertable表名add列名列的描述

【例4】為xs表增加“獎學金”列。

altertablexsadd獎學金smallmoneynull

2)修改表結構——刪除列

如果某一列不再需要，可將其刪除，但有下列情況不可刪除：

(1)該表正在復制；

(2)用在索引中的列；

(3)用在check、foreignkey、unique或primarykey約束中的列；

(4)與default定義關聯或綁定到某一默認對象；

(5)綁定到規則的列；

(6)已注冊為全文本支持；

(7)用做表的全文鍵。刪除一列的語句格式為：

altertable表名dropcolumn列名

【例5】altertablexsdropcolumn總學分。

3)修改表結構—修改列定義

表中的每一列都有一組屬性，如列名、數據類型、數據長度以及是否允許為空值等，列的所有屬性構成列的定義，這些屬性都可以在表創建好以后修改。修改列定義的語句格式為：

altertable表名altercolumn列名列的描述

【例6】修改表中已有列的屬性，將“姓名”這一列的長度由原來的8位改為18位。

altertablexsaltercolumn姓名char(18)4.2.3建立表間的聯系

下面建立上述3個表的關聯，操作步驟如下：圖4-8選擇要添加的表圖4-9顯示所選擇表圖4-10“創建關系”對話框圖4-11建立的學生成績管理數據庫中各個表之間的關系圖4-12保存關系圖4.2.4插入表中的記錄

1．在企業管理器中向數據表插入數據

(1)展開數據庫，單擊“表”。

(2)在詳細列表中右擊需插入數據的表名稱，在彈出菜單中選擇“打開表”下的“返回所有行”命令。

(3)如果屏幕彈出SQLServer登錄窗口，則輸入登錄帳號和密碼，按“確定”按鈕后，屏幕顯示數據表的內容。

(4)此時彈出查詢設計器的結果窗格，在該窗格中可以向表中添加新記錄，也可以修改和刪除表中已有的記錄。

2．使用insert語句插入數據

語法格式為：insert[into]表名[(字段列表)]values(相應的值列表)

1)添加數據到一行中的所有列

當將數據添加到一行的所有列時，使用values關鍵字來給出要添加的數據。insert語句中無需給出表中的列名，只要values中給出的數據與用createtable定義表時給定的列名順序相同即可。

【例7】insertintoxsvalues(‘0002’,‘張玲’,‘會計學’,0,‘1992-5-6’,200,‘三好學生’)

需要注意的是：

(1)輸入的順序和數據類型必須與表中列的順序和數據類型一致。

(2)可以不給全部列賦值，但沒有賦值的列必須是可以為空的列。

(3)字符型和日期型值插入時要用單引號擴起來。

2)添加數據到一行中的部分列

要將數據添加到一行中的部分列時，則需要同時給出要使用的列名以及要賦給這些列的數據。

【例8】insertintoxs(學號,姓名,性別)values('0005','劉曉莉',1)4.2.5修改表中的記錄

1．在企業管理器中修改表中的記錄

(1)打開“企業管理器”并展開服務器，繼續展開“數據庫”，并展開要修改表的數據庫，例如是“學生表”。在“表”項上單擊鼠標，右邊會出現表對象，右擊要修改的表名，并選“設計表”命令。

(2)這時會出現創建表結構時的窗口，然后對表結構做修改就可以了，最后保存退出。

2．使用update語句修改表中的記錄

語法格式為：update表名set列名=更新后新的數據值[,…n][where條件]

【例9】updatexsset備注=‘優秀黨員’where學號=‘0001’

【例10】updatexsset獎學金=獎學金+1000

【例11】updatexsset專業名='計算機',出生日期='10/20/1988',備注='班長'where學號='0003'4.2.6刪除表中的記錄

1．在企業管理器中刪除表中的記錄

通過企業管理器刪除記錄和插入記錄的操作非常類似。在要刪除的記錄上右擊，在彈出菜單中選擇刪除命令就可以了。這時會出現一個警告信息對話框，詢問用戶是否確定要刪除該行記錄。選擇“是”，則數據會永久刪除，無法恢復。如果用戶同時刪除多條記錄，那么配合shift鍵就可以完成多條記錄的選擇。

2．使用delete語句刪除表中的記錄

語法格式為：deletefrom表名[where條件]

其中，表名是要刪除數據的表的名字。如果delete語句中沒有where子句限制，表中的所有記錄都將被刪除。

【例12】deletefromxswhere性別=0

【例13】deletexs/*清空表中所有記錄*/4.2.7刪除表

有時需要刪除表(如要實現新的設計或釋放數據庫的空間時)。刪除表時，表的結構定義、數據、全文索引、約束和索引都永久地從數據庫中刪除，原來存放表及其索引的存儲空間可用來存放其他表。

表的刪除操作很簡單，但要注意的是，如果與其他表存在關聯時，則不能直接刪除表。要先刪除關聯，然后再刪除表。圖4-13“除去對象”對話框圖4-14錯誤信息

4.3視圖的基本操作

4.3.1視圖的概念

視圖是用于創建動態表的靜態定義，視圖中的數據是根據預定義的選擇條件從一個或多個行集中生成的。用視圖可以定義一個或多個表的行列組合。為了得到所需要的行列組合的視圖，可以使用select語句來指定視圖中包含的行和列。視圖有很多優點，主要表現在以下幾點。

(1)視點集中。

(2)簡化操作。

(3)定制數據。

(4)合并分割數據。

(5)安全性。4.3.2創建視圖

1.使用企業管理器創建視圖

使用企業管理器創建視圖應注意以下4點：

(1)只能在當前的數據庫中創建視圖；

(2)視圖中最多只能引用1024列；

(3)如果視圖引用的表被刪除，則使用該視圖時將返回一條錯誤提示信息；如果創建具有相同結構的新表來代替已經刪除的表，則可以繼續使用視圖，否則必須重新創建視圖；

(4)如果視圖中的某一列是函數、數學表達式常量或與來自多個表的列名相同，則必須為列定義名字。圖4-15視圖設計窗口圖圖4-16“添加表”對話框圖4-17在視圖設計窗口中創建視圖

2.使用Transact-SQL語言創建視圖

除了使用企業管理器創建視圖以外，還可以使用Transact-SQL語句中的CreateView命令創建視圖。創建視圖的語法格式如下：

createview[<數據庫名>.][<所有者>.]視圖名[(列名[,...n])]

[with{encryption|schemabinding|view_metadata}]

as

select查詢語句

[withcheckoption]

【例14】創建一個新視圖“視圖1”，要求基表選擇學生表和成績表，來源字段為學生表中學號、姓名和性別，成績表中成績。要求查詢所有女同學的成績，程序為：

use學生成績管理

go

createview視圖1

as

select學生表.學號,學生表.姓名,學生表.性別,成績表.成績

from學生表,成績表

where學生表.學號=成績表.學號and學生表.性別='女'圖4-18查詢結果【例15】創建一個新視圖“視圖2”，要求基表選擇學生表和成績表，來源字段為學生表中學號、姓名，成績表中成績。要求查詢陳艷的成績，并對視圖的定義進行加密，程序為：

use學生成績管理

go

createview視圖2

withencryption

as

select學生表.學號,學生表.姓名,成績表.成績

from學生表,成績表

where學生表.學號=成績表.學號and學生表.姓名='陳艷'圖4-19查詢結果圖4-20“錯誤提示”對話框

【例16】創建一個新視圖“視圖3”，要求基表選擇成績表，來源字段為成績表中學號和成績，要求計算每位同學的“成績總和”，并對視圖的定義進行加密，程序為：

use學生成績管理

go

createview視圖3

withencryption

as

select成績表.學號,

sum(成績表.成績)

as成績總和

from成績表

groupby成績表.學號圖4-21查詢結果4.3.3修改視圖

1.使用企業管理器修改視圖

使用企業管理器修改視圖的步驟如下：

(1)在企業管理器中右擊要修改的視圖，在彈出的快捷菜單中選擇“設計視圖”命令，打開SQLServer的視圖設計窗口。

(2)在視圖設計窗口中按照在企業管理器中創建視圖的方法，對已經創建好的視圖進行修改。可以添加和刪除數據源，也可以在數據源列表窗格的復選框列表中增加或刪除在視圖中顯示的列，還可以修改列的排序類型和排序順序，修改查詢條件等。

2.使用Transact-SQL語言修改視圖

對于一個已經創建好的視圖，可以使用alterview語句對其屬性進行修改。alterview語句用于修改一個先前創建的視圖(用createview創建)，包括索引視圖，但不影響相關的存儲過程或觸發器，也不更改權限。該語句的語法格式如下：

alterview[<數據庫名>.][<所有者>.]視圖名[(列名[,...n])]

[with{encryption|schemabinding|view_metadata}]

as

select查詢語句

[withcheckoption]

【例17】修改視圖2，在該視圖中增加一個新的限制條件，要求查詢陳艷大于70分的成績，并對視圖2取消加密，程序為：圖4-22查詢結果4.3.4使用視圖查詢數據

使用視圖查詢基表中的數據可以使用企業管理器和Transact-SQL語句兩種方法。

1．使用企業管理器通過視圖查詢數據

使用企業管理器查詢基表中的數據，操作步驟如下。

(1)在企業管理器的視圖對象中右擊視圖(例如視圖1)，在彈出的快捷菜單中選擇“打開視圖”菜單下的“返回所有行”。

(2)可以在彈出的新窗口中查看滿足該視圖限制條件的基表中的數據，如圖4-23所示。圖4-23視圖1中的數據

2．使用Transact-SQL語句

可以在查詢分析器中輸入Transact-SQL語句查詢視圖的基表中的數據。

【例18】查詢視圖1的基表中的數據，程序為：

select*from視圖14.3.5使用視圖管理數據表中的數據

1．插入數據

可以通過視圖向基表中插入數據，但應該注意的是：

插入的數據實際上存放在基表中，而不是存放在視圖中。視圖中的數據若發生變化，是因為相應的基表中的數據發生

變化。

【例19】創建一個視圖4，該視圖的基表為學生表，要求在視圖中顯示所有男同學的信息，程序為：

use學生成績管理

go

createview視圖4

as

select*

from學生表

where性別=‘男’

此時視圖中的數據如圖4-24所示。圖4-24的查詢結果此時如果通過視圖4向學生表中插入數據，在查詢分析器中輸入下列Transact-SQL語句：

use學生成績管理

go

insertinto視圖4values(‘2013006’,‘張鍵’,‘男’,‘1991-10-20’,,‘群眾’)insertinto視圖4

values(‘2013007’,‘張蕭蕭’,‘女’,‘1992-03-20’,,‘黨員’)

輸入并執行下面一段查詢語句，分別查看視圖4及其基表中數據的變化：

select*from視圖4

select*from學生表

代碼執行后，結果窗口如圖4-25所示。圖4-25插入數據后的查詢結果

【例20】創建一個視圖5，該視圖的基表為學生表，要求在視圖中顯示團員中所有男同學的信息，程序為：

use學生成績管理

go

createview視圖5

as

select*

from學生表

where性別=‘男’and備注=‘團員’

withcheckoption

此時視圖5中的數據如圖4-26所示。圖4-26查詢結果此時如果通過視圖5向學生表中插入數據，在查詢分析器中輸入下列Transact-SQL語句：

use學生成績管理

go

insertinto視圖5values(‘2013008’,‘張見軍’,‘男’,‘1989-01-20’,,‘團員’)insertinto視圖5values(‘2013100’,‘張渙渙’,‘女’,‘1992-09-11’,,‘黨員’)

運行之后在查詢分析器的結果顯示窗口中顯示提示信息如圖4-27所示。圖4-27“插入數據失敗”錯誤提示框輸入并執行下面一段查詢語句分別查看視圖5及其基表中數據的變化：

select*from視圖5

select*from學生表

代碼執行后，視圖5和基表學生表中的數據分別如圖4-28和圖4-29所示。圖4-28插入數據后視圖5的變化圖4-29插入數據后基表學生表的變化

2．更新數據

使用update命令通過視圖更新數據時，被更新的列必須位于同一個表中。

【例21】創建一個視圖6，該視圖的基表為課程表，在視圖中顯示課程表中學分為3的課程信息，程序為：

use學生成績管理

go

createview視圖6

as

select*

from課程表

where學分=3

視圖6中的數據如圖4-30所示。圖4-30視圖6中的數據如果要通過視圖6來更新課程表中的數據，則在查詢分析器中輸入下列Transact-SQL語句：

use學生成績管理

go

update視圖6

set學分=5

where課程號?=?‘20002’

此時視圖6和基表課程表中的數據都發生了變化，課

程號為20002的課程學分修改為5。分別如圖4-31和圖4-32

所示。圖4-31更新數據后視圖6的變化圖4-32更新數據后基表課程表的變化

3．刪除數據

【例22】利用視圖5，刪除編號為1007的員工的記錄，程序為：

use學生成績管理

go

deletefrom視圖5

where姓名?=?‘李勇’

執行該段代碼后，視圖5和基表學生表中的數據分別如圖4-33和圖4-34所示。圖4-33刪除數據后視圖5的變化圖4-34刪除數據后基表學生表的變化

4.4索引的基本操作

4.4.1索引的概念

在SQLServer中，索引主要起到輔助查詢和組織數據的功能，通過使用它，可以大大地提高查詢數據的效率。索引類似目錄，使得查詢更快速、更高效，適用于訪問大型數據庫。在本章中，具體介紹索引的概念、索引的類型、使用企業管理器和Transact-SQL語句創建和管理索引的方法等。4.4.2索引的類型

1．聚集索引

聚集索引對表的物理數據頁中的數據按列進行排序，然后再重新存儲到磁盤上，即聚集索引與數據是混為一體的，它的葉節點中存儲的是實際的數據。如果在一個表中建立了聚集索引，那么表中的數據頁會依照該索引的順序來存放。由于一個數據表只能有一種實際的存儲順序，所以在一個數據表中只能建立一個聚集索引。

2．非聚集索引

非聚集索引具有完全獨立于數據行的結構，使用非聚集索引不需要將物理數據頁中的數據按列重新排序。非聚集索引的頁面頂級存儲了組成非聚集索引的關鍵字值和行定位器，行定位器即指針將指向數據頁中的數據行，該行具有與索引值相同的列值，這樣就加快了檢索的速度。

3．唯一索引

無論是聚集索引還是非聚集索引，如果考慮到索引鍵值是否重復，就可以判定是否為唯一索引；如果考慮索引字段的組成情況，又可以判斷是否為復合索引。4.4.3創建索引

在SQLServer中創建索引有下面幾種方法：

(1)利用企業管理器中的索引向導創建索引。

(2)利用企業管理器直接創建索引。

(3)利用Transact-SQL語句中的createindex命令創建索引。

(4)利用企業管理器中的索引優化向導創建索引。

1．利用企業管理器中的索引向導創建索引

具體操作步驟如下：圖4-35選擇向導界面圖4-36索引向導開始界面圖4-37選擇數據庫和表圖4-38當前索引信息圖4-39選擇要創建索引的列圖4-40指定索引選項圖4-41完成創建索引界面圖圖4-42創建索引成功

2．利用企業管理器直接創建索引

在企業管理器中也可以直接對某個數據庫表格建立索引，其方法是：圖4-43企業管理器中直接創建索引圖4-44選擇數據庫和表圖4-45創建新的索引圖4-46索引管理

3．使用createindex命令創建索引

使用createindex語句來創建索引，是最基本、最具有適應性的索引創建方式，可以創建出符合自己需要的索引。在使用這種方式創建索引時，可以使用許多選項，例如指定數據頁的充滿度、進行排序、整理統計信息等，從而優化索引。另外，使用這種方法，還可以指定索引類型、唯一性、包含性和復合性，也就是說，既可以創建聚集索引，也可以創建非聚集索引；既可以在一個列上創建索引，也可以在兩個或兩個以上的列上創建索引。在SQLServer2000系統中，使用createindex語句可以在關系表上創建索引，其基本的語法形式如下：

【例23】在學生成績管理數據庫中，給學生表的姓名創建降序的聚集索引，索引名為xm。

use學生成績管理

go

createclu