1、1第第6 6章章 創(chuàng)建和管理創(chuàng)建和管理SQL Server SQL Server 20082008數(shù)據(jù)表數(shù)據(jù)表主要內(nèi)容主要內(nèi)容l6.1 表的概念l6.2 數(shù)據(jù)表的創(chuàng)建l6.3 數(shù)據(jù)表的修改l6.4 標識符列的使用l小 結(jié)6.1 6.1 表的概念表的概念l表是關系模型中表示實體和聯(lián)系的方式，是數(shù)據(jù)庫存儲數(shù)據(jù)的主要對象。lSQL Server數(shù)據(jù)庫的表由行和列組成，行有時也稱為記錄，列有時也稱為字段或域，如圖6.1所示。 訂單號訂單號客戶代號客戶代號產(chǎn)品號產(chǎn)品號單價單價數(shù)量數(shù)量訂單日期訂單日期10248VINET1116.0020 2010-07-0510248VINET429.8015 2010

2、-07-0510249TOM2218.6010 2010-07-0610250JACK1116.0030 2010-07-0810250JACK4136.5025 2010-07-08l在表中，行的順序可以是任意的，一般按照數(shù)據(jù)插入的先后順序存儲。在使用過程中，可以使用排序語句或按照索引對表中的行進行排序。l列的順序也可以是任意的，對于每一個表，最多可以允許用戶定義1024列。在同一個表中，列名必須是唯一的，即不能有名稱相同的兩個或兩個以上的列同時存在于一個表中，并且在定義時為每一個列指定一種數(shù)據(jù)類型。但是，在同一個數(shù)據(jù)庫的不同表中，可以使用相同的列名。 6.2 6.2 數(shù)據(jù)表的創(chuàng)建數(shù)據(jù)表的創(chuàng)

3、建l6.2.1 在圖形界面下創(chuàng)建數(shù)據(jù)表l6.2.2 用SQL命令創(chuàng)建數(shù)據(jù)表6.2.1 6.2.1 在圖形界面下創(chuàng)建數(shù)據(jù)表在圖形界面下創(chuàng)建數(shù)據(jù)表l本節(jié)以Student表的創(chuàng)建為例，說明在Management Studio中創(chuàng)建數(shù)據(jù)表的基本方法。Student表的結(jié)構如表6.1所示。 表6.1 Student表結(jié)構列名列名數(shù)據(jù)類型數(shù)據(jù)類型長度長度能否為空能否為空字段說明字段說明studentIDchar10否否學生學生ID號，號，主鍵主鍵studentNamevarchar10否否學生姓名學生姓名sexchar2可以可以學生性別，取值學生性別，取值男男或或女女birthdaydatetime可以可

4、以出生日期出生日期specialityvarchar30可以可以所屬院系，默認值所屬院系，默認值軟件學軟件學院院credithourtinyint1否否總學分總學分ru_datechar4可以可以入學年月入學年月passwordvarchar20可以可以密碼密碼remarkvarchar200可以可以備注備注l打開SQL Server Management Studio，連接到CJGL數(shù)據(jù)庫上。單擊數(shù)據(jù)庫節(jié)點上的加號，可以看到數(shù)據(jù)庫內(nèi)各類對象的文件夾節(jié)點。右鍵單擊該數(shù)據(jù)庫中的“表”節(jié)點，在彈出菜單中選擇“新建表”，打開表設計器。l在表設計器中：l創(chuàng)建列l(wèi)設置主鍵l創(chuàng)建約束 1 1）創(chuàng)建列）創(chuàng)建

5、列l(wèi)在表設計器的“列定義”窗口中輸入Student表中每一列的列名、數(shù)據(jù)類型、是否為空等信息（具體信息參考表6.1）。選中一列，在列屬性窗口中可以查看該列詳細的屬性定義，如數(shù)據(jù)類型、默認值、是否是標識列等。如圖6.2所示。2 2）定義主鍵）定義主鍵l主鍵是表中的一列或者一組列，它的值可以惟一標識表中的每一行記錄。l例如，每一個學生入學后都有一個學號，而且該學號和其他任何一個學生都是不同的，或者說，確定了一個學號就確定了一個學生。因此，在設計表時，可以把學號定義為主鍵。l以Student表為例，將studentID列定義為主鍵列，有3種可視化方法。 l（1）點擊選中列studentID，單擊右鍵

6、，選擇“設置主鍵”項，如圖6.3所示。此時studentID列的左側(cè)出現(xiàn)了一個圖標，表明主鍵設置成功。l（2）按照（1）中步驟，選中studentID列后，選擇“表設計器”菜單，在打開的下拉菜單中選擇“設置主鍵”，即可將studentID列設置為主鍵。l（3）按照（1）中步驟，選中studentID列后，在工具欄上單擊圖標，同樣可以將studentID列設置為主鍵。l注意：l當一列被設置為主鍵后，該列的“允許空”選項將自動取消，因為主鍵列必須輸入數(shù)據(jù)。l當要取消某列的主鍵屬性時，步驟和設置主鍵的過程完全一樣，只是選項由“設置主鍵”變成了“移除主鍵”。3 3）創(chuàng)建約束）創(chuàng)建約束l（1）創(chuàng)建CHE

7、CK約束l性別列sex的取值只能為“男”或“女”，需要通過CHECK約束來實現(xiàn)該功能。l（2）創(chuàng)建DEFAULT約束l所屬院系speciality列默認取值軟件學院，需要通過DEFAULT約束來實現(xiàn)該功能。（1 1）創(chuàng)建）創(chuàng)建CHECKCHECK約束約束l可以通過以下方法創(chuàng)建約束。l選中sex列，單擊右鍵，在彈出菜單中選擇“Check約束”，如同6.5所示，或者單擊工具欄上的（管理Check約束）按鈕，打開Check約束對話框。l單擊“添加”按鈕，新增一個約束，在表達式中輸入：sex=男 or sex=女，在名稱中輸入：CK_Sex，如圖6.6所示。點擊“關閉”按鈕，然后保存student表，

8、剛才在sex列上創(chuàng)建的約束就保存到了數(shù)據(jù)庫中。 管理管理Check約束約束（2 2）創(chuàng)建）創(chuàng)建DEFAULTDEFAULT約束約束l在表設計器里選中speciality列，在下面的“列屬性”窗口中可以看到該列的詳細屬性。選中“默認值或綁定”項，在文本框內(nèi)輸入字符串“軟件學院”，如圖所示。單擊保存按鈕，保存表結(jié)構，默認值創(chuàng)建成功。 6.2.2 6.2.2 用用SQLSQL命令創(chuàng)建數(shù)據(jù)表命令創(chuàng)建數(shù)據(jù)表l在使用T-SQL語句時，使用CREATE TABLE創(chuàng)建表。語句中需要指出的元素與在表設計器中相同，包括表名、列名、列的數(shù)據(jù)類型、列屬性及相關約束等。l在SQL Server Management

9、Studio中，單擊工具欄上的“新建查詢”按鈕，在查詢窗口中輸入下面的腳本命令，可以創(chuàng)建Student表。 創(chuàng)建創(chuàng)建studentstudent表表lUSE CJGLlGOlCREATE TABLE student(lstudentID char(10) PRIMARY KEY,lstudentName varchar(10) NOT NULL,lsex char(2) CHECK(sex=男or sex=女),lbirthday datetime ,lspeciality varchar(30) DEFAULT(軟件學院),lcredithourtinyintNOT NULL,lru_dat

10、e char(4) ,lpassword varchar(20) ,lremark varchar(200) ,l) ON PRIMARYl其中：lUSE語句表示選擇CJGL數(shù)據(jù)庫；lPRIMARY KEY屬性定義studentID字段為主鍵；lNOT NULL表示不允許studentName列為空；lCHECK(sex=男 or sex=女)約束限制sex列只能輸入“男”或者“女”兩個值；lDEFAULT(軟件學院)約束表示speciality列默認取值“軟件學院”；lON PRIMARY表示該表創(chuàng)建在PRIMARY文件組中，“PRIMARY”是個關鍵字，要想在命令里把命令字符串作為普通字符

11、串（文件組名）使用，需要加上或者雙引號。如果省略ON PRIMARY則表示將表創(chuàng)建在默認文件組中。 建表時單獨添加約束建表時單獨添加約束該表也可以用下面的代碼實現(xiàn)：lCREATE TABLE student(lstudentID char(10) ,lstudentName varchar(10) NOT NULL,lsex char(2),lbirthday datetime ,lspeciality varchar(30) DEFAULT(軟件學院),lcredithourtinyintNOT NULL,lru_date char(4) ,lpassword varchar(20) ,lr

12、emark varchar(200) ,lCONSTRAINT PK_student PRIMARY KEY (studentID),lCONSTRAINT CK_sex CHECK(sex=男 or sex=女),l);創(chuàng)建表中的計算列創(chuàng)建表中的計算列l(wèi)在創(chuàng)建表的過程中，除了在列中直接指定數(shù)據(jù)類型和屬性之外，還可以對某些列進行計算?；蛘哒f，某些列的值可以不用輸入，而是通過計算得到。l例如，下面創(chuàng)建學生成績表Sgrade的示例中，就使用了一個計算列：lCREATE TABLE Sgradel(lStudentID int,l Grade1 int,l Grade2 int,lGrade3 in

13、t,lTotal AS Grade1+Grade2+grade3l) l需要注意的一點是，一般情況下，計算列的數(shù)據(jù)并不進行物理存儲，它僅僅是一個虛擬列，只能用于顯示。如果希望將該列的數(shù)據(jù)物理化存儲，可以使用PERSISTED關鍵字。 CREATE TABLE Sgrade(Sno int,Grade1 int,Grade2 int,Grade3 int,Total AS Grade1+Grade2+grade3PERSISTED)6.3 6.3 數(shù)據(jù)表的修改數(shù)據(jù)表的修改l6.3.1 查看表屬性l6.3.2 修改表結(jié)構l6.3.3 刪除數(shù)據(jù)表6.3.1 6.3.1 查看表屬性查看表屬性l在SQL

14、 Server Management Studio中，選中要查看的數(shù)據(jù)表，單擊鼠標右鍵，選擇“屬性”，將打開“表屬性”對話框，如圖所示。 l另外，還可以通過sp_help存儲過程來查看表結(jié)構信息，如圖所示。 6.3.2 6.3.2 修改表結(jié)構修改表結(jié)構l在SQL Server Management Studio中，選中要查看的數(shù)據(jù)表，單擊右鍵，在彈出菜單中選擇“設計”，可以打開表設計器，在圖形界面下修改表結(jié)構。 l右鍵單擊表名，在彈出菜單中選擇“重命名”，可以更改表名。l另外一種很常用的方法是使用SQL命令ALTER TABLE語句修改表結(jié)構，包括添加列、修改列、刪除列、添加與刪除約束等。 使

15、用使用SQLSQL命令修改表結(jié)構命令修改表結(jié)構l向表中添加列 l修改列屬性 l刪除列 l添加約束 l刪除約束 l修改列名和表名 1 1向表中添加列向表中添加列l(wèi)使用ALTER TABLE語句向表中添加列的基本語法為：lALTER TABLE table_namelADD l l l | l ,.n l其中，、的含義與CREATE TABLE中的含義完全一樣。l【例6.1】在表Student中新增加一列登錄名，列名為LoginName，數(shù)據(jù)類型為varchar（20），允許空值。執(zhí)行以下語句：l這里需要注意的是：l（1）當向表中新增一列時，最好為該列定義一個默認約束，使該列有一個默認值。這一點可

16、以使用關鍵字DEFAULT來實現(xiàn)；l（2）如果增加的新列沒有設置默認值，并且表中已經(jīng)填寫了數(shù)據(jù)，那么必須指定該列允許空值，否則，系統(tǒng)將產(chǎn)生錯誤信息。l【例6.2】在表Student中新增加一列登錄名，列名為LoginName0，數(shù)據(jù)類型為varchar（20），并且不能為空。l此時student表中存在7行數(shù)據(jù)，所以當執(zhí)行下面的SQL語句時，系統(tǒng)報錯，如圖6.10所示。l糾正的方法就是給新增的列設置默認約束，將上述SQL命令改為：l該命令執(zhí)行后，會在Student中添加LoginName0列，并將該列的值賦為“l(fā)oginname”。 2 2修改列屬性修改列屬性 l使用ALTER TABLE語句

17、修改列屬性的基本語法為：ALTER TABLE table_name ALTER TABLE table_name ALTER COLUMN column_name type_name ( precision , ALTER COLUMN column_name type_name ( precision , scale | max ) NULL | NOT NULL scale | max ) NULL | NOT NULL l使用此命令可以修改列的數(shù)據(jù)類型、列是否允許為空。 l【例6.3】修改表Student中的列LoginName0，將其數(shù)據(jù)類型改為char(10)，并且允許為空。3 3

18、刪除列刪除列l(wèi)使用ALTER TABLE語句刪除列的基本語法為：ALTER TABLE table_nameDROP COLUMN column_namel【例6.4】刪除Student表中的LoginName列。l注意：如果要刪除的列上存在約束，如主鍵、默認值等，則需要先刪除約束，然后才能刪除列。l【例6.5】刪除Student表中的speciality列。l結(jié)果如圖所示，DROP COLUMN失敗。4 4添加約束添加約束 l使用ALTER TABLE語句添加約束的基本語法為： ALTER TABLE table_nameADD l其中，的含義與CREATE TABLE中的完全一樣。l 【例

19、6.6】為Grade表中的成績列grade添加一個約束，限制該列的值只能為0100。SQL語句為：5 5刪除約束刪除約束l使用ALTER TABLE語句刪除約束的基本語法為： ALTER TABLE table_nameDROP CONSTRAINT constraint_namel【例6.7】刪除Student表中speciality列上的約束，然后刪除speciality列。執(zhí)行下面的代碼：l注意：l刪除約束時需要指出要刪除的約束的名稱，該名稱可以通過sp_help存儲過程來查看，如：sp_help student，該語句可以顯示student表上的約束及約束具體信息。 6 6修改列名和表

20、名修改列名和表名l可以使用sp_rename存儲過程對表和表中的列進行重命名，重命名的基本語法為：sp_rename objname = object_name , newname = new_name , objtype = object_type l其中：l objname = object_name：表示用戶對象的當前名 稱 ； 如 果 要 重 命 名 的 對 象 是 表 中 的 列 ， 則 object_name 的格式必須是 table.column。l newname = new_name：指定對象的新名稱；l objtype = object_type：要重命名的對象的類型。如果

21、重命名表名，則省略該參數(shù)；如果重命名列名，則該參數(shù)的值為“COLUMN”。l【6.8】將Student表改名為StudentInfo。命令為：sp_rename Student, StudentInfol【6.9】將Student表中的studentID改名為StuID。命令為：sp_rename Student.studentID, StuID, COLUMN執(zhí)行結(jié)果為：注意: 更改對象名的任一部分都可能會破壞腳本和存儲過程。6.3.3 6.3.3 刪除數(shù)據(jù)表刪除數(shù)據(jù)表l刪除表就是將表中的數(shù)據(jù)和表的結(jié)構從數(shù)據(jù)庫中永久性的移除。也就是說，表一旦被刪除，就無法恢復，除非還原數(shù)據(jù)庫。因此，執(zhí)行此操

22、作時應該慎重。l在SQL Server Management Studio中，選中要查看的數(shù)據(jù)表，單擊右鍵，在彈出菜單中選擇“刪除”，將彈出“刪除對象”對話框，單擊“確定”按鈕，選中的表就從數(shù)據(jù)庫中被刪除了。l注意：和刪除列的操作類似，如果要刪除的表此時正被其他表引用（如外鍵約束），則表不能刪除。需要先刪除約束，再刪除該表。 l也可以使用DROP TABLE語句來完成數(shù)據(jù)表的刪除。該語句的語法非常簡單，如下所示：DROP TABLE table_namel【例6.10】刪除CJGL數(shù)據(jù)庫中的Grade表。語句如下：l在使用DROP TABLE語句刪除數(shù)據(jù)表時，需要注意以下幾點：l（1）不能刪除

23、系統(tǒng)表；l（2）不能刪除正被其他表中的外鍵約束參考的表。當需要刪除這種有外鍵約束參考的表時，必須先刪除外鍵約束，然后才能刪除該表；l（3）當刪除表時，屬于該表的約束和觸發(fā)器也會自動被刪除。如果重新創(chuàng)建該表，必須重新創(chuàng)建相應的規(guī)則、約束和觸發(fā)器等；l（4）DROP TABLE語句可以一次性刪除多個表，表之間用逗號分開。 6.4 6.4 標識符列的使用標識符列的使用l可以使用標識符列在表中創(chuàng)建自動遞增標識號。通過使用 IDENTITY 屬性可以實現(xiàn)標識符列。l對于每個表，均可創(chuàng)建一個包含系統(tǒng)生成的序號值的標識符列，該序號值以唯一方式標識表中的每一行。l例如，student表中的學號值具有這樣的特征

24、：可以唯一標識每一行，同時數(shù)據(jù)以等差數(shù)列的形式出現(xiàn)。此時就可以將學號列創(chuàng)建為標識符列。l定義標識列需要指定兩個值：種子值和增量值。這樣，表中第一行記錄的IDENTITY列的值就是種子值，其他行的IDENTITY列的值是在前一行值的基礎上增加一個增量值得到的。 l需要注意的是，標識列的類型必須為整數(shù)型，如tinyint、smallint、int、bigint、decimal、numeric等（一般不使用tinyint和smallint，因為它們的適用范圍較小，當編號范圍超過相應的適用范圍時，會造成算術溢出錯誤）。字符（串）類型的數(shù)據(jù)無法設置標識列。 6.4.1 6.4.1 圖形界面下創(chuàng)建標識符列

25、圖形界面下創(chuàng)建標識符列l(wèi)假設要創(chuàng)建一個學生基本信息表StuInfo，結(jié)構如表6.2所示。 l首先，參照6.2.1中創(chuàng)建數(shù)據(jù)表的步驟，打開表設計器，在表設計器中輸入并設置StuInfo表的各列列名、數(shù)據(jù)類型、能否為空及相應的約束。然后，再來設置stuID列為標識符列。 表6.2 StuInfo表結(jié)構列名數(shù)據(jù)類型 長度能否為空 字段說明stuIDint4否學號，主鍵，系統(tǒng)自動生成，初值為2010130101stuNamevarchar10否學生姓名sexchar2可以學生性別，取值男或女l首先，參照6.2.1中創(chuàng)建數(shù)據(jù)表的步驟，打開表設計器，在表設計器中輸入并設置StuInfo表的各列列名、數(shù)據(jù)類

26、型、能否為空及相應的約束。然后，再來設置stuID列為標識符列。l在表設計器中選中stuID列（或者將光標定位在該列），在“列屬性”窗口中打開“標識規(guī)范”，按照圖所示將“（是標識）”的值改為“是”，然后設置標識增量值、標識種子值。單擊“保存”按鈕，保存表結(jié)構。 標識選項l注意：l由于IDENTITY屬性列的增長是單方向的，所以一般情況下不能手工為設置了IDENTITY屬性的列添加數(shù)據(jù)。l而且，如果刪除了這些列中的部分數(shù)據(jù)，還會造成標識符序列空缺已刪除的標識符值是不能重用的，系統(tǒng)不會自動補充這部分數(shù)據(jù)值。l如 果 必 須 手 工 輸 入 某 些 標 識 列 的 數(shù) 據(jù) ， 需 要 將 IDENT

27、ITY_INSERT標志設置為ON，然后使用INSERT命令輸入數(shù)據(jù)。 6.4.2 6.4.2 使用使用SQLSQL命令創(chuàng)建標識符列命令創(chuàng)建標識符列 l使用命令創(chuàng)建標識符列的基本語法為：column_name NULL | NOT NULL IDENTITY ( seed ,increment ) l其中：lseed表示標識種子值；lincrement表示標識增量值。l【例6.11】使用命令創(chuàng)建一個包含標識符列的表，標識種子值和增量值采用默認值。命令如下：l【例6.11】使用命令創(chuàng)建表6.2所示的表。l命令如下：本章小結(jié)本章小結(jié) l表是數(shù)據(jù)庫中最核心、最重要的一個內(nèi)容，它負責存儲數(shù)據(jù)庫中的數(shù)據(jù)

28、。本章介紹了有關表的基本概念和SQL Server 2008中常見的數(shù)據(jù)表類型，然后在此基礎上重點介紹了數(shù)據(jù)表的管理技術，詳細講解了如何在圖形方式下和SQL命令方式下進行表的創(chuàng)建、修改、刪除操作。l創(chuàng)建、更改、刪除表的SQL命令分別為：CREATE TABLE、ALTER TABLE、DROP TABLE。各命令的語法形式都在文中進行了詳細的介紹。l本章涉及到的SQL命令，需要讀者在學習過程中多上機進行練習，做到熟練使用并掌握。 本章小結(jié)本章小結(jié) l需要掌握的操作有：創(chuàng)建數(shù)據(jù)表Create table創(chuàng)建check約束創(chuàng)建default約束設置主鍵 primary key設置是否允許為空 NULL or not null單獨定義約束條件CON