第4章-創建和管理SQL-Server-2005數據表

第4章

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

4.2.1在圖形界面下創建數據表

4.2.2用SQL命令創建數據表

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

SETIDENTITY_INSERT<表名>{ON|OFF}

其中，ON表示可以插入數據，OFF表示拒絕插入數據。

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

CREATETABLE表名

(

列名1數據類型和長度1列屬性1,

列名2數據類型和長度2列屬性2, …………

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

USE學生選課

GO CREATETABLEStudent (

Sno

int PRIMARYKEYIDENTITY(20050301,1),

Sname varchar(20) NOTNULL,

Ssex char(2), Sage smallint,

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

CREATETABLESgrade (

Sno

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

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

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

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

SQL語句如下：ALTERTABLEStudentALTERCOLUMNSentrance

smalldatetimeNULL

刪除列基本語法：

ALTERTABLE表名DROPCOLUMN列名例如，要刪除Student表中的Sentrance列，可以執行下面的SQL語句。

ALTERTABLEStudentDROPCOLUMNSentrance修改列名和表名可以使用sp_rename存儲過程對表和表中的列進行重命名，重命名的基本語法為：sp_rename

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

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

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

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

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

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