创建与管理表课件

創建與管理表

4.1創建表

表的創建是使用表的前提。表定義為列的集合，數據在表中是按行和列的組織形式排列的，每一行代表惟一的一條記錄，每一列則代表記錄中的一個域。在SQLServer2000中，每個資料庫中最多可以創建200萬個表，用戶創建資料庫表時，最多可以定義1024列，也就是可以定義1024個字段。

在SQLServer2000中，可以使用企業管理器和使用CREATETABLE語句兩種方法創建表。

4.1.1使用企業管理器創建表

【例4-1】使用企業管理器創建“系部表”。

①在控制臺樹中展開XSCJ資料庫。

②右擊【表】，在彈出的菜單中單擊【新建表】命令，出現如圖4-1所示的對話框。圖4-1【新建表】對話框在該對話框中，需要對表的內容進行定義，其具體內容為：l輸入列名。列名在表中必須惟一，列的命名必須遵守SQLServer資料庫對象的命名規則。l選擇正確的數據類型、數據長度和精度，有關數據類型的相關知識可以參見2.1.3Transact-SQL語法元素一節的內容。l規定該列的數據可否為空。l設置表的主鍵和索引。創建主鍵的方法非常簡單，首先選中要建立主鍵的列，單擊工具欄的【設置主鍵】按鈕即可。建立為主鍵的列旁會出現一個小鑰匙形的標誌。

③本例在【列名】中輸入“系部編號”，【數據類型】框中選擇“char”，在【長度】中輸入長度“2”，不允許為空。④繼續設置列，在【列名】中輸入“系部名稱”，【數據類型】框中選擇“char”，在【長度】中輸入長度“16”，不允許為空。

⑤設置完成後，如圖4-2所示。圖4-2使用企業管理器創建“系部表”⑥單擊企業管理器工具欄上的【保存】按鈕，出現如圖4-3所示的對話框，輸入表名“系部表”，最後單擊【確定】按鈕完成表的創建。圖4-3為創建的表命名【例4-2】使用企業管理器創建“學生基本資訊表”。

①在控制臺樹中展開XSCJ資料庫。

②右擊【表】，在彈出的菜單中單擊【新建表】命令。③本例在【列名】中輸入“學號”，【數據類型】框中選擇“char”，在【長度】中輸入長度“8”，不允許為空。

④繼續設置列，在【列名】中輸入“姓名”，【數據類型】框中選擇“char”，在【長度】中輸入長度“12”，不允許為空。

⑤繼續設置列，在【列名】中輸入“性別”，【數據類型】框中選擇“char”，在【長度】中輸入長度“2”，不允許為空。⑥繼續設置列，在【列名】中輸入“族別”，【數據類型】框中選擇“char”，在【長度】中輸入長度“8”，不允許為空。

⑦繼續設置列，在【列名】中輸入“出生日期”，【數據類型】框中選擇“datetime”，在【長度】中輸入長度“8”，不允許為空。

⑧繼續設置列，在【列名】中輸入“政治面貌”，【數據類型】框中選擇“char”，在【長度】中輸入長度“8”，不允許為空。⑨繼續設置其他列，完成後的表結構如圖4-4所示。

⑩單擊企業管理器工具欄上的【保存】按鈕，輸入表名“學生基本資訊表”，最後單擊【確定】按鈕完成表的創建。圖4-4使用企業管理器創建“學生基本資訊表”4.1.2使用CREATETABLE語句創建表

其語法形式如下：

CREATETABLE

[資料庫名.[擁有者名].]表名

(

列名列的屬性

[，…n])

）

其中：列的屬性包括列的數據類型、列的長度、列上的約束等。【例4-3】使用SQL語句創建“班級表”、“課程資訊表”、“成績表”。

在查詢分析器中運行如下命令：USEXSCJ

GO

CREATETABLE班級表

(

班級編號char(8)NOTNULL,

系部編號char(2)NOTNULL,

班級名稱char(16)NOTNULL

)

GO

CREATETABLE課程資訊表(

課程編號char(4)NOTNULL,

課程名稱char(30)NOTNULL,

學分decimal(5)NOTNULL,

學時decimal(5)NOTNULL,

考核類型char(6)NOTNULL,

任課教師char(12)NOTNULL,

系部編號char(2)NOTNULL,

上課時間char(40)NOTNULL

)GO

CREATETABLE成績表

(

學號char(8)NOTNULL,

課程編號char(4)NOTNULL,

成績decimal(9)NOTNULL

)

GO4.2管理表

4.2.1修改表結構

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

使用企業管理器可以修改表的定義，包括添加、刪除列，更改列的名稱、類型和長度。也可以添加、刪除、修改約束，啟用或禁用約束和觸發器等。具體操作時只需右擊要修改的表，在彈出的菜單中選擇【設計表】命令即可。【例4-4】使用企業管理器修改XSCJ資料庫中的“課程資訊表”，將“課程編號”列設置為主鍵。

①在控制臺樹中展開XSCJ資料庫，單擊【表】。

②在詳細列表中右擊【課程資訊表】，在彈出的菜單中單擊【設計表】命令。

③選中要建立主鍵的列“課程編號”，單擊企業管理器工具欄的【設置主鍵】按鈕即可。建立為主鍵的列旁即出現一個小鑰匙形的標誌。

④單擊企業管理器工具欄上的【保存】按鈕完成表的修改。2．使用ALTERTABLE語句修改表結構

（1）修改表設置主鍵

基本語法如下：

ALTERTABLE表名

ADDCONSTRAINT約束名稱

PRIMARYKEYCLUSTERED

(

列名[,…]

)其中：

ADDCONSTRAINT表示增加約束。

PRIMARYKEY表示主鍵。

CLUSTERED表示聚集索引，一般主鍵為聚集索引。

【例4-5】使用SQL語句把“課程資訊表”的“課程編號”列設置為主鍵“PX_課程資訊表”，再將其刪除。

在查詢分析器中運行如下命令即可：

USEXSCJ

GO

ALTERTABLE課程資訊表

ADDCONSTRAINTPX_課程資訊表

PRIMARYKEYCLUSTERED

(課程編號)

GO（2）添加列

向表中增加一列時，應使新增加的列有默認值或允許為空值，SQLServer將向表中已存在的行填充新增列的默認值或空值，如果既沒有提供默認值也不允許為空值，那麼新增列的操作將出錯，因為SQLServer不知道該怎麼處理那些已經存在的行。

向表中添加列的語句格式如下：

ALTERTABLE表名

ADD列名列的描述【例4-6】向“學生基本資訊表”中添加“Email：”列。

在查詢分析器中運行如下命令：

USEXSCJ

GO

ALTERTABLE學生基本資訊表

ADDEmail：varchar(20)NULL

GO（3）刪除列

刪除一列的語句格式為：

ALTERTABLE表名

DROP列名【例4-7】將“學生基本資訊表”中添加的“Email：”列刪除。

在查詢分析器中運行如下命令：

USEXSCJ

GO

ALTERTABLE學生基本資訊表

DROPCOLUMNEmail：

GO（4）修改列定義

表中的每一列都有其定義，包括列名、數據類型、數據長度以及是否允許為空值等，這些值都可以在表創建好以後修改。

修改列定義的語句格式為：

ALTERTABLE表名

ALTERCOLUMN列名列的描述【例4-8】將“學生基本資訊表”中的“姓名”列改為最大長度為20的varchar型數據，且不允許空值。

在查詢分析器中運行如下命令：

USEXSCJ

GO

ALTERTABLE學生基本資訊表

ALTERCOLUMN姓名varchar(20)NOTNULL

GO

默認狀態下，列是被設置為允許空值的，將一個原來允許空值的列改為不允許空值，必須在以下兩個條件滿足時才能成功：

①列中沒有存放是空值的記錄

②在列上沒有創建索引

4.2.2表的更名

1．使用企業管理器重新命名表

【例4-9】使用企業管理器將XSCJ資料庫中的“成績表”更名為“學生成績表”。

①在控制臺樹中展開XSCJ資料庫，單擊【表】。

②在詳細列表中右擊“成績表”，在彈出的菜單中選擇【重命名】命令。

③輸入“學生成績表”。

④單擊【是】按鈕確認。

⑤單擊【確定】按鈕完成重命名。

2．使用sp_rename存儲過程重新命名表

【例4-10】使用SQL命令將XSCJ資料庫中的“學生成績表”重新更名為“成績表”。

在查詢分析器中運行如下命令：

USEXSCJ

GO

EXECsp_rename‘學生成績表’,’成績表’

GO

4.2.3表的刪除

1.使用企業管理器刪除表

【例4-11】使用企業管理器刪除XSCJ資料庫中的“課程資訊表”。

①在控制臺樹中展開XSCJ資料庫，單擊【表】。

②在詳細列表中右擊“課程資訊表”，在彈出的菜單中選擇【刪除】命令。

③單擊【全部刪除】按鈕，完成刪除。

2.利用DROPTABLE語句刪除表

DROPTABLE語句可以刪除一個表和表中的數據及其與表有關的所有索引、觸發器、約束、許可對象。DROPTABLE語句的語法形式如下：

DROPTABLE表名

【例4-12】使用SQL語句刪除XSCJ資料庫中的“學生基本資訊表”。

在查詢分析器中運行如下命令：

USEXSCJ

GO

DROPTABLE學生基本資訊表

GO

注：為保證本書的連貫性，刪除後應該原樣恢復。4.3表的數據操作

4.3.1向表中插入數據

1．使用INSERT語句

基本語法為：

INSERT[INTO]表名

[(列名)]

VALUES(運算式)

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

當將數據添加到一行的所有列時，使用VALUES關鍵字來給出要添加的數據。INSERT語句中無需給出表中的列名，只要VALUES中給出的數據與用CREATETABLE定義表時給定的列名順序相同即可。【例4-13】向“班級表”中輸入一行數據。

在查詢分析器中運行如下命令：

USEXSCJ

GO

INSERTINTO班級表

VALUES(‘20051004’,’01’,’05高職網路’)

GO返回的結果為：

（所影響的行數為1行）

需要注意的是：

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

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

③字元型和日期型值插入時要用單引號擴起來。（2）添加數據到一行中的部分列

要將數據添加到一行中的部分列時，則需要同時給出要使用的列名以及要賦給這些列的數據。【例4-14】向“學生基本資訊表”中輸入一行數據。

在查詢分析器中運行如下命令：

USEXSCJ

GO

INSERTINTO學生基本資訊表(學號，姓名，家庭住址)

VALUES(‘000107’,’巴爾夏提’,’新疆庫爾勒市’)

GO

返回的結果為：

（所影響的行數為1行）

對於這種添加部分列的操作，在添加數據前應確認未在VALUES列表中出現的列允許不允許為NULL；只有允許為NULL的列，才可以不出現在VALUES列表中。2．使用企業管理器添加數據

【例4-15】使用企業管理器向“班級表”中輸入一行數據。

①在控制臺樹中展開XSCJ資料庫，單擊【表】。

②在詳細列表中右擊“班級表”，在彈出的菜單中選擇【打開表】命令中的【返回所有行】命令。

③在出現的窗口中分別輸入“20051005”、“01”、“05高職軟體”。

④單擊【關閉】按鈕即可保存確認。

4.3.2使用UPDATE語句更新表中數據

語法格式為：

UPDATE表名

SET列名=運算式

[WHERE條件]

【例4-16】將劉瑞恒的出生日期改為。

在查詢分析器中運行如下命令：

USEXSCJ

GO

UPDATE學生基本資訊表

SET出生日期='1985/10/31'

WHERE姓名=’劉瑞恒’

GO

返回的結果為：

（所影響的行數為1行）4.3.3使用DELETE語句刪除表中數據

使用T-SQL中的DELETE語句可以刪除數據表中的一個或多個記錄。

DELETE語句最簡單的形式如下：

DELETE[FROM]表名

[WHERE條件運算式]

其中，表名是要刪除數據的表的名字。如果DELETE語句中沒有WHERE子句限制，表中的所有記錄都將被刪除。【例4-16】刪除“學生基本資訊表”中姓名為楊文利的學生記錄。

在查詢分析器中運行如下命令：

USEXSCJ

GO

DELETEFROM學生基本資訊表

WHERE姓名=’楊文利’

GO

返回的結果為：

（所影響的行數為1行）本章小結

本章介紹了如何創建、修改和刪除表，以及如何使用SQL語句修改、添加、刪除表結構等。同學們應該重點掌握如何使用企業管理器和SQL語句創建、刪除、修改表及表中的數據。練習與上機

一．選擇題

1．使用下列哪種語句可以創建數據表（）。

A、CREATEDATABASEB、CREATETABLE

C、ALTERDATABASED、ALTERTABLE

2．使用下列哪種語句可以修改數據表（）。

A、CREATEDATABASEB、CREATETABLE

C、ALTERDATABASED、ALTERTABLE

3．使用下列哪種語句可以刪除數據表（）。

A、DROPDATABASEB、CREATETABLE

C、ALTERDATABASED、DROPTABLE

4．使用下列哪種語句可以向表中插入數據（）。

A、INSERTB、UPDATE

C、DELETED、CREATE

5．使用下列哪種語句可以更新表中數據（）。

A、INSERTB、UPDATE

C、DELETED、CREATE

6．使用下列哪種語句可以刪除表中數據（）。

A、INSERTB、UPDATE

C、DELETED、CREATE二．填空題

1．在SQLServer2000中，可以使用_____________和____________語句兩種方法創建表。

2．在SQLServer2000中，可以使用_____________和____________語句兩種方法重新命名表。