第4章-創(chuàng)建和管理SQL-Server-2005數(shù)據(jù)表

第4章

創(chuàng)建和管理SQLServer2005數(shù)據(jù)表基本內(nèi)容：表的基本概念；數(shù)據(jù)表的創(chuàng)建、修改和刪除；查看和編輯表中的數(shù)據(jù)；重點(diǎn)：數(shù)據(jù)表的創(chuàng)建、修改和刪除;編輯表中數(shù)據(jù)；難點(diǎn)：使用SQL語句創(chuàng)建和修改數(shù)據(jù)表；要求：理解表的特點(diǎn)和類型，熟練掌握創(chuàng)建和修改表的相關(guān)技術(shù)。本章目錄4.1表的概念4.2數(shù)據(jù)表的創(chuàng)建4.3數(shù)據(jù)表的修改4.4添加和修改表數(shù)據(jù)本章小結(jié)4.1表的概念表是關(guān)系模型中中表示實(shí)體的方式，是數(shù)據(jù)庫(kù)存儲(chǔ)數(shù)據(jù)的主要對(duì)象。SQLServer數(shù)據(jù)庫(kù)的表由行和列組成，行有時(shí)也稱為記錄，列有時(shí)也稱為字段或域，如圖4.1所示。行列訂單號(hào)客戶代號(hào)產(chǎn)品號(hào)單價(jià)數(shù)量訂單日期10248VINET1114.00202006-07-0510248VINET429.80152006-07-0510249TOM2218.60102006-07-0610250JACK1114.00302006-07-0810250JACK4134.50252006-07-08圖4.1表的結(jié)構(gòu)在表中，行的順序可以是任意的，一般按照數(shù)據(jù)插入的先后順序存儲(chǔ)。在使用過程中，可以使用排序語句或按照索引對(duì)表中的行進(jìn)行排序。列的順序也可以是任意的，對(duì)于每一個(gè)表，最多可以允許用戶定義1024列。在同一個(gè)表中，列名必須是唯一的，即不能有名稱相同的兩個(gè)或兩個(gè)以上的列同時(shí)存在于一個(gè)表中，并且在定義時(shí)為每一個(gè)列指定一種數(shù)據(jù)類型。但是，在同一個(gè)數(shù)據(jù)庫(kù)的不同表中，可以使用相同的列名。表的類型在SQLServer2005系統(tǒng)中把表分成了4種類型，即普通表、臨時(shí)表、已分區(qū)表和系統(tǒng)表。普通表就是通常提到的數(shù)據(jù)庫(kù)中存儲(chǔ)數(shù)據(jù)的數(shù)據(jù)表，是最重要、最基本的表其他幾種類型的表都是有特殊用途的表，往往是在特殊應(yīng)用環(huán)境下，為了提高系統(tǒng)的使用效率而派生出來的。臨時(shí)表，顧名思義是臨時(shí)創(chuàng)建的、不能永久生存的表。臨時(shí)表被創(chuàng)建之后，可以一直存儲(chǔ)到SQLServer實(shí)例斷開連接為止。臨時(shí)表又可以分為本地臨時(shí)表和全局臨時(shí)表，本地臨時(shí)表只對(duì)創(chuàng)建者是可見的，全局臨時(shí)表在創(chuàng)建之后對(duì)所有的用戶和連接都是可見的。已分區(qū)表是將數(shù)據(jù)水平劃分成多個(gè)單元的表，這些單元可以分散到數(shù)據(jù)庫(kù)中多個(gè)文件組里面，實(shí)現(xiàn)對(duì)單元中數(shù)據(jù)的并行訪問。如果表中的數(shù)據(jù)量非常龐大，并且這些數(shù)據(jù)經(jīng)常被以不同的使用方式來訪問，那么建立已分區(qū)表是一個(gè)有效的選擇。系統(tǒng)表儲(chǔ)存了有關(guān)SQLServer服務(wù)器的配置、數(shù)據(jù)庫(kù)配置、用戶和表對(duì)象的描述等系統(tǒng)信息。一般來說，只能由DBA來使用系統(tǒng)表。4.2數(shù)據(jù)表的創(chuàng)建

4.2.1在圖形界面下創(chuàng)建數(shù)據(jù)表

4.2.2用SQL命令創(chuàng)建數(shù)據(jù)表

4.2.1在圖形界面下創(chuàng)建數(shù)據(jù)表表的有關(guān)操作都是在MicrosoftSQLServer2005的MicrosoftSQLServerManagementStudio中完成的。在MicrosoftSQLServer2005系統(tǒng)中，可以使用ManagementStudio完成創(chuàng)建表、修改表結(jié)構(gòu)、查看表屬性信息等操作。本節(jié)將以“學(xué)生選課”數(shù)據(jù)庫(kù)中Student表的創(chuàng)建為例，介紹如何在圖形界面下完成數(shù)據(jù)表的創(chuàng)建。在MicrosoftSQLServerManagementStudio中，創(chuàng)建一個(gè)名為“學(xué)生選課”的數(shù)據(jù)庫(kù)。右鍵單擊該數(shù)據(jù)庫(kù)中的“表”節(jié)點(diǎn)，在彈出菜單中選擇“新建表”，打開表設(shè)計(jì)器，如圖4.2所示。圖4.2使用表設(shè)計(jì)器創(chuàng)建表在圖4.2的結(jié)構(gòu)中修改表屬性，將表名“Table_1”改為“Student”，并輸入表中各列的名稱、數(shù)據(jù)類型、是否為空等信息。選中任意一列，在窗體的下半部分將顯示該列的主要屬性信息。如圖4.3所示。圖4.3建立Student表結(jié)構(gòu)SQLServer的主要列屬性1．允許空（NULL或NOTNULL）該屬性定義在輸入數(shù)據(jù)時(shí)指定列是否可以為空。在SQLServer中，列的默認(rèn)屬性為“允許空”，例如，Student表中的Ssex列和Sage列。當(dāng)某列不允許為空時(shí)，只需要在該列的“允許空”選項(xiàng)中取消復(fù)選框就行了，如Sno列。2．標(biāo)識(shí)規(guī)范（IDENTITY）很多表中使用編號(hào)列來標(biāo)識(shí)表中的記錄。在插入數(shù)據(jù)時(shí)，用戶并不需要指定編號(hào)列的值，只要他們互不相同就行了。在這種情況下，可以將編號(hào)列設(shè)置為標(biāo)識(shí)列，設(shè)置成功后，每插入一條記錄，系統(tǒng)都會(huì)根據(jù)增量值自動(dòng)為該列生成新數(shù)據(jù)。定義標(biāo)識(shí)列需要指定兩個(gè)值：種子值和增量值。這樣，表中第一行記錄的IDENTITY列的值就是種子值，其他行的IDENTITY列的值是在前一行值的基礎(chǔ)上增加一個(gè)增量值得到的。通常將標(biāo)識(shí)列的類型定義為int或bigint。例如，在上面創(chuàng)建學(xué)生表Student時(shí)，如果將學(xué)號(hào)列“Sno”定義為int型，那么就可以通過更改窗口下方的列屬性將其設(shè)置為標(biāo)識(shí)列，然后由自動(dòng)生成該列的值。如圖4.4所示。SQLServer的主要列屬性由于IDENTITY屬性列的增長(zhǎng)是單方向的，所以一般情況下不能手工為設(shè)置了IDENTITY屬性的列添加數(shù)據(jù)。而且，如果刪除了這些列中的部分?jǐn)?shù)據(jù)，還會(huì)造成標(biāo)識(shí)符序列空缺——已刪除的標(biāo)識(shí)符值是不能重用的，系統(tǒng)不會(huì)自動(dòng)補(bǔ)充這部分?jǐn)?shù)據(jù)值。解決辦法：使用SETIDENTITY_INSERT語句將標(biāo)識(shí)列設(shè)置為可以插入數(shù)據(jù)的狀態(tài)。該語句的基本格式為：

SETIDENTITY_INSERT<表名>{ON|OFF}

其中，ON表示可以插入數(shù)據(jù)，OFF表示拒絕插入數(shù)據(jù)。

SQLServer的主要列屬性3．主鍵（PRIMARYKEY）主鍵是表中的一列或者一組列，它的值可以惟一標(biāo)識(shí)表中的每一行記錄。例如，每一個(gè)學(xué)生入學(xué)后都有一個(gè)學(xué)號(hào)，而且該學(xué)號(hào)和其他任何一個(gè)學(xué)生都是不同的，或者說，確定了一個(gè)學(xué)號(hào)就確定了一個(gè)學(xué)生。因此，在設(shè)計(jì)表時(shí)，可以把學(xué)號(hào)定義為主鍵。4.2.2用SQL命令創(chuàng)建數(shù)據(jù)表基本語法如下：

CREATETABLE表名

(

列名1數(shù)據(jù)類型和長(zhǎng)度1列屬性1,

列名2數(shù)據(jù)類型和長(zhǎng)度2列屬性2, …………

列名n數(shù)據(jù)類型和長(zhǎng)度n列屬性n )在CREATETABLE語句中需要指出的元素與在表設(shè)計(jì)器中相同，包括表名、列名、列的數(shù)據(jù)類型以及列屬性等。在SQLServerManagementStudio中，單擊工具欄上的“新建查詢”，在查詢窗口中輸入下面的腳本命令，可以創(chuàng)建4.2.1節(jié)中定義的Student表。

USE學(xué)生選課

GO CREATETABLEStudent (

Sno

int PRIMARYKEYIDENTITY(20050301,1),

Sname varchar(20) NOTNULL,

Ssex char(2), Sage smallint,

Sdept varchar(20) )其中，USE語句表示選擇數(shù)據(jù)庫(kù)；PRIMARYKEY屬性定義Sno字段為主鍵；IDENTITY(20050301,1)屬性定義Sno列的第一行記錄值為20050301，以后每一行的Sno列值在此基礎(chǔ)上依次遞增，增量為1；NOTNULL表示不允許Sname列為空。在創(chuàng)建表的過程中，除了在列中直接指定數(shù)據(jù)類型和屬性之外，還可以對(duì)某些列進(jìn)行計(jì)算。例如，使用計(jì)算列創(chuàng)建學(xué)生成績(jī)表Sgrade可用下面語句實(shí)現(xiàn)：

CREATETABLESgrade (

Sno

int, Grade1 int, Grade2 int, Grade3 int, TotalASGrade1+Grade2+grade3 )注意:一般情況下，計(jì)算列的數(shù)據(jù)并不進(jìn)行物理存儲(chǔ)，它僅僅是一個(gè)虛擬列，只能用于顯示。如果希望將該列的數(shù)據(jù)物理化存儲(chǔ)，可以使用PERSISTED關(guān)鍵字。

4.3數(shù)據(jù)表的修改4.3.1查看表屬性4.3.2修改表結(jié)構(gòu)4.3.3刪除數(shù)據(jù)表4.3.1查看表屬性在SQLServerManagementStudio中，選中要查看的數(shù)據(jù)表，單擊鼠標(biāo)右鍵，選擇“屬性”，將打開“表屬性”對(duì)話框，如圖4.7所示。圖4.7通過“表屬性”對(duì)話框查看表屬性還可以通過sp_help存儲(chǔ)過程來查看表結(jié)構(gòu)信息。如圖4.8所示。圖4.8使用存儲(chǔ)過程查看表結(jié)構(gòu)信息4.3.2修改表結(jié)構(gòu)1、在圖形方式下修改表結(jié)構(gòu)在SQLServerManagementStudio中，選中要查看的數(shù)據(jù)表，單擊右鍵，在彈出菜單中選擇“修改”，打開表設(shè)計(jì)器即可修改表結(jié)構(gòu)。2、使用ALTERTABLE語句修改表結(jié)構(gòu)添加列修改列屬性刪除列修改列名和表名添加列基本語法：

ALTERTABLE表名ADD列名數(shù)據(jù)類型和長(zhǎng)度列屬性【例4.1】在表Student中新增一列“入學(xué)年月”，列名為Sentrance，數(shù)據(jù)類型為datetime，允許空值。執(zhí)行以下語句：ALTERTABLEStudentADDSentrance

datetime注意：當(dāng)向表中新增一列時(shí)，最好為該列定義一個(gè)默認(rèn)約束，使該列有一個(gè)默認(rèn)值。這一點(diǎn)可以使用關(guān)鍵字DEFAULT來實(shí)現(xiàn)；如果增加的新列沒有設(shè)置默認(rèn)值，并且表中已經(jīng)有了其他數(shù)據(jù)，那么必須指定該列允許空值，否則，系統(tǒng)將產(chǎn)生錯(cuò)誤信息。修改列屬性基本語法：ALTERTABLE表名ALTERCOLUMN列名新數(shù)據(jù)類型和長(zhǎng)度新列屬性例如，【例4.1】中創(chuàng)建的Sentrance列是datetime類型，并且不允許為空。現(xiàn)在要將該列改為smalldatetime類型，并且允許為空。

SQL語句如下：ALTERTABLEStudentALTERCOLUMNSentrance

smalldatetimeNULL

刪除列基本語法：

ALTERTABLE表名DROPCOLUMN列名例如，要?jiǎng)h除Student表中的Sentrance列，可以執(zhí)行下面的SQL語句。

ALTERTABLEStudentDROPCOLUMNSentrance修改列名和表名可以使用sp_rename存儲(chǔ)過程對(duì)表和表中的列進(jìn)行重命名，重命名的基本語法為：sp_rename

原對(duì)象名,新對(duì)象名例如，如果想將Student表改名為StudentInfo，可以執(zhí)行以下SQL語句：

sp_renameStudent,StudentInfo4.3.3刪除數(shù)據(jù)表刪除表就是將表中的數(shù)據(jù)和表的結(jié)構(gòu)從數(shù)據(jù)庫(kù)中永久性的移除。也就是說，表一旦被刪除，就無法恢復(fù)，除非還原數(shù)據(jù)庫(kù)。因此，執(zhí)行此操作時(shí)應(yīng)該慎重。在SQLServerManagementStudio中，選中要查看的數(shù)據(jù)表，單擊右鍵，在彈出菜單中選擇“刪除”，將彈出“刪除對(duì)象”對(duì)話框，單擊“確定”按鈕，選中的表就從數(shù)據(jù)庫(kù)中被刪除了。也可以使用DROPTABLE語句來完成數(shù)據(jù)表的刪除。DROPTABLE語句的語法：

DROPTABLE表名【例4.2】刪除“學(xué)生選課”數(shù)據(jù)庫(kù)中的exam表。USE學(xué)生選課GODROPTABLEexam在使用DROPTABLE語句刪除數(shù)據(jù)表時(shí)，需要注意以下幾點(diǎn)：DROPTABLE語句不能刪除系統(tǒng)表；DROPTABLE語句不能刪除正被其他表中的外鍵約束參考的表。當(dāng)需要?jiǎng)h除這種有外鍵約束參考的表時(shí)，必須先刪除外鍵約束，然后才能刪除該表；當(dāng)刪除表時(shí)，屬于該表的約束和觸發(fā)器也會(huì)自動(dòng)被刪除。如果重新創(chuàng)建該表，必須重新創(chuàng)建相應(yīng)的規(guī)則、約束和觸發(fā)器等；DROPTABLE語句可以一次性刪除多個(gè)表，表之間用逗號(hào)分開。4.4添加和修改表數(shù)據(jù)4.4.1手工添加表數(shù)據(jù)4.4.2查看表記錄4.4.3用INSERT語句插入數(shù)據(jù)4.4.4用UPDATE語句更新數(shù)據(jù)4.4.5用DELETE語句刪除數(shù)據(jù)表創(chuàng)建以后，往往只是一個(gè)沒有數(shù)據(jù)的空表。因此，向表中出入數(shù)據(jù)可能是創(chuàng)建表之后首先要執(zhí)行的操作。無論表中是否有數(shù)據(jù)，都可以根據(jù)需要向表中添加數(shù)據(jù)。當(dāng)表中的數(shù)據(jù)不合適或者出現(xiàn)了錯(cuò)誤時(shí)，可以更新表中的數(shù)據(jù)。如果表中的數(shù)據(jù)不再需要了，則可以刪除這些數(shù)據(jù)。4.4.1手工添加表數(shù)據(jù)在SQLServerManagementStudio中，選中要查看的數(shù)據(jù)表，單擊右鍵，在彈出菜單中選擇“打開表”，可以打開查詢表數(shù)據(jù)的窗口，該窗口顯示了表中已經(jīng)存儲(chǔ)的數(shù)據(jù)，數(shù)據(jù)列表的最后有一個(gè)空行。如圖4.11所示。插入數(shù)據(jù)時(shí)，將光標(biāo)定位在空白行某個(gè)字段的編輯框中，就可以輸入新數(shù)據(jù)。編輯完成后，選中其他行，即可完成數(shù)據(jù)的插入。圖4.11編輯表數(shù)據(jù)注意：在編輯表中數(shù)據(jù)的過程中，有些讀者可能會(huì)遇到這樣一個(gè)問題，就是無法輸入中文。這種情況出現(xiàn)在表的第一列為標(biāo)識(shí)列、并且打開表時(shí)表中已經(jīng)存在數(shù)據(jù)，此時(shí)，光標(biāo)定位在第一條記錄的標(biāo)識(shí)列中，再將光標(biāo)定位到其他的編輯框，將無法輸入中文。解決方法是在定義表結(jié)構(gòu)時(shí)，不要將標(biāo)識(shí)列定義為表的第一列，或者使用SQL命令進(jìn)行數(shù)據(jù)的插入。4.4.2查看表記錄在SQLServerManagementStudio中，選中要查看的數(shù)據(jù)表，單擊右鍵，在彈出菜單中選擇“打開表”，可以打開查詢表數(shù)據(jù)的窗口，就可以查看該表中現(xiàn)已存儲(chǔ)的數(shù)據(jù)。另外，用戶還還可在查詢窗口中使用SELECT命令查看一個(gè)或多個(gè)表中的數(shù)據(jù)。4.4.3用INSERT語句插入數(shù)據(jù)INSERT語句的基本語法：INSERTINTO表名(列名1,列名2,……,列名n)VALUES(值1,值2,……,值n)INSERT子句指定要插入的數(shù)據(jù)表名，并且可以同時(shí)指定表的列名稱。VALUES子句指定要插入的數(shù)據(jù)。例：

USE學(xué)生選課GOINSERTINTOStudent(Sname,Ssex,Sage,Sdep)VALUES('曾玉林','男',20,'CS')使用INSERT插入數(shù)據(jù)時(shí)注意幾點(diǎn)：要插入的數(shù)據(jù)表必須已經(jīng)存在。要插入數(shù)據(jù)的表結(jié)構(gòu)必須和SELECT語句的結(jié)果集兼容，也就是說，二者的列的數(shù)量和順序必須相同、列的數(shù)據(jù)類型必須兼容等。當(dāng)完全按照表中列的存儲(chǔ)順序來安排VALUES子句中的值時(shí)，可以在INSERTINTO子句中省略列名。向表中插入數(shù)據(jù)時(shí)，數(shù)字?jǐn)?shù)據(jù)可以直接插入，但是字符數(shù)據(jù)和日期數(shù)據(jù)要用英文單引號(hào)引起來，不然就會(huì)提示系統(tǒng)錯(cuò)誤。一般情況下，使用INSERT語句一次只能插入一行數(shù)據(jù)。但是如果在INSERT語句中包含了SELECT語句，就可以一次插入多行數(shù)據(jù)了。使用SELECT語句插入數(shù)據(jù)的基本語法形式為：

INSERTINTO表名（列名1,列名2,……,列名n）

SELECT語句可以使用UPDATE語句更新表中已經(jīng)存在的數(shù)據(jù)，該語句既可以一次更新一行數(shù)據(jù)，也可以一次更新多行數(shù)據(jù)，甚至可以一次更新表中的全部數(shù)據(jù)行。UPDATE語句基本語法：UPDATE表名SET列名1＝值1,列名2＝值2,……,列名n＝值nWHERE更新條件表達(dá)式當(dāng)執(zhí)行UPDATE語句時(shí)，如果使用了WHERE子句，則指定表中所有滿足WHERE子句條件的行都將被更新，如果沒有指定WHERE子句，則表中所有的行都將被更新。4.4.4用UPDATE語句更新數(shù)據(jù)【例4.3】將學(xué)生表Student中“劉塵”所屬的學(xué)院由CS改為SS。USE學(xué)生選課GOUPDATEStudentSETSdep='SS'WHERESname='劉塵'執(zhí)行結(jié)果如圖4.15所示，有一行記錄被更新。圖4.15更新數(shù)據(jù)表數(shù)據(jù)更新數(shù)據(jù)時(shí)，每個(gè)列既可以被直接賦值，如例4.3，也可以通過計(jì)算得到新值?！纠?.4】將所有學(xué)生的年齡增加1歲。USE學(xué)生選課GOUPDATEStudentSETSage=Sage+14.4.5用DELETE語句刪除數(shù)據(jù)當(dāng)表中的數(shù)據(jù)不再需要的時(shí)候，可以將其刪除。一般情況下，可以使用DELETE語句刪除表中的數(shù)據(jù)。該語句可以從一個(gè)表中刪除一行或多行數(shù)據(jù)。使用DELETE語句刪除數(shù)據(jù)的基本語法形式如下：DELETEFROM表名WHERE條件表達(dá)式在DELETE語句中，如果使用了WHERE子句，表示從指定的表中刪除滿足