第5章數據表管理

第5章

SQLServer數據表的管理

本章內容5.1數據表的創建和管理5.2表數據的管理5.3數據完整性管理

5.1數據表的創建和管理

5.1.1使用企業管理創建數據表 5.1.2使用Transact-SQL語言創建數據表 步驟：明確在針對哪個數據庫操作。對該數據庫應如何規劃，設定幾個數據表每個數據表各存儲什么信息？表中需要設置哪些字段？字段的名字及數據類型？哪些字段確定為主鍵？哪些字段不可以為空？是否使用約束或索引？在何處用？5.1數據表的創建和管理使用查詢分析器在數據庫Student_db中創建Student學生信息表，要求包含S_NO（學號）、NAME（姓名）、AGE（年齡）、SEX（性別）信息，其中學號不能為空。（1）啟動SQLServer服務器，運行查詢分析器，并連接到數據庫。（2）在查詢窗口中輸入創建表的語句。創建數據表Student的語句如下：（3）單擊工具欄上的“分析查詢”按鈕，若結果窗口無錯誤信息，再單擊“執行查詢”按鈕，將返回“命令已成功完成”的信息。

5.1數據表的創建和管理

5.1.3使用企業管理器對數據表進行管理5.1.4使用Transact-SQL語言對數據表進行管理5.1數據表的創建和管理5.1.3使用企業管理器對數據表進行管理使用企業管理器查看數據表的相關設置使用企業管理器更改數據表名稱使用企業管理器刪除數據表5.1數據表的創建和管理5.1.4使用T-SQL語言對數據表進行重命名Execsp_rename

old_table_name,new_table_name如將圖書庫中的“讀者”表更名為“學生”表Execsp_name‘讀者’,’學生’5.1數據表的創建和管理5.1.5使用T-SQL語言對數據表字段進行管理

語法格式：

ALTERTABLEtable

{[ALTERCOLUMNcolumn_name

]|[ADDcolumn_name

data_base]|[DROPCOLUMNcolumn_name

]|}

參數說明： （1）table：要更改的數據表的名稱是要更改的表的名稱。如果表不在當前數據庫中或者不屬于當前用戶所擁有，可以顯式指定數據庫和所有者。 （2）ALTER/ADD/DROPCOLUMN：指定要更改的列。 （3）column_name：要更改、添加的列的名稱。修改表字段的數據類型

AlTERTABLE<表名>ALTERCOLUMN<列名稱><新數據類型>注意：數據類型為text、image、ntext

的列，或者屬于primarykey的列不能修改。新的數據類型應與原數據類型兼容

舉例：1、修改student庫中sc表里的grade屬性列的數據類型，由原來的int改為smallintusestudentALTERTABLEscaltercolumngradeSMALLINT或者：ALTERTABLE

student.sc

altercolumngradeSMALLINT2、在”學生”表中，修改“住址”一列，數據類型為varchar(50）

ALTERTABLE

學生altercolumn

住址varchar(50）知識回顧建立表結構Createtable<表名>(字段名數據類型約束,…….,)修改表名字Execsp_rename

old_table_name,new_table_name修改字段數據類型AlTERTABLE<表名>ALTERCOLUMN<列名稱><新數據類型>更改字段名Execsp_rename‘表名.[<原列名>]’,’<新列名>’,’column’

舉例：3、修改讀者表中字段，把“備注”字段名更名為“附言”Use圖書execsp_rename‘讀者.[備注]’,‘附言','column'

增加表字段（一定要包含字段名和數據類型，也可增加一些相應約束條件）AlTERTABLE<表名>ADD<新列名稱><新數據類型>注意：約束條件不能有noy

null,primarykey等。

舉例：2、在student庫中couse表里,新增一列id_xuanke，數據類型為5個字符，而且取值唯一ALTERTABLE

student.couse

add

id_xuankechar(5)unique刪除表字段AlTERTABLE<表名>dropcolumn<列名稱>

舉例：3、在student庫中couse表里,刪除列id_xuankeALTERTABLE

student.couse

drop

columnid_xuanke

練習:在讀者表中，增加一個年齡字段，然后刪除地址字段，并修改備注字段的數據類型為varchar(200).Altertable圖書.讀者add年齡intAltertable圖書.讀者dropcolumn地址

Altertable圖書.讀者altercolumn備注varchar(200)5.1數據表的創建和管理5.1.6刪除數據表可使用DROPTABLE語句 語法格式：

DROPTABLEtable_name

參數說明：

table：要刪除的數據表的名稱。例：刪除當前數據庫中的Student_1學生信息表。刪除該表的語句如下：DropTABLEStudent_1注意：（1）DROPTABLE不能用于刪除由FOREIGNKEY約束引用的表。必須先刪除引用的FOREIGNKEY約束或引用的表。（2）在系統表上不能使用DROPTABLE語句。

(3)數據、表上的索引都刪除。表上的視圖往往仍然保留，但無法引用實例例：在同一語句中指定多個表對它們進行刪除。假設有一stu數據庫，此數據庫中存在book表和temp2表，同時刪除這兩張表的語句如下：DROPTABLEbook,temp2例：刪除指定數據庫中的表。假設stu

數據庫內有temp1表，可以在任何數據庫內執行如下語句，完成刪除temp1表的操作。刪除該表的語句如下：DROPTABLEstu.dbo.temp1默認情況下，將DROPTABLE權限授予表所有者，該權限不可轉讓。然而，dbo可以通過在DROPTABLE內指定所有者除去任何對象

5.2表數據的管理

5.2.1使用企業管理管理表數據使用表設計器瀏覽、添加、修改、刪除數據

5.2表數據的管理

5.2.2使用Transact-SQL語言創建數據表 表數據的添加表數據的修改表數據的刪除5.2表數據的管理5.2.2使用Transact-SQL語言管理表數據表數據的添加，可以使用INSERTINTO語句，語法格式：INSERT[INTO]table_or_view[(column_list)]

{values({default|null|expression})|select…from…}作用:可以一次追加一行數據，也可以從另外的表和查詢中追加數據參數說明：[INTO]：一個可選的關鍵字，可以將它用在INSERT和目標表之間。table_or_view：要輸入數據的表名或視圖名。[(column_list)]：要在其中插入數據的一列或多列的列表。必須用圓括號將column_list

括起來，并且用逗號進行分隔。如果沒有指定column_list，表或者視圖中的所有列都將接收數據

有兩種方法指定數據值：用VALUES子句為一行指定數據值：用SELECT子查詢為一行或多行指定數據值。datavalues：作為一行或者多行要插入數值的列表。所提供的數據值必須與列的列表匹配。數據值的數目必須與列數相同，每個數據值的數據類型、精度和小數位數也必須與相應的列匹配5.2表數據的管理例5-2-1：假設數據庫Student_db中已經創建好了課程信息數據表C_Info，試將新課程記錄（9720044，網絡技術與應用，選修，3）添加到課程信息數據表C_Info中。添加新課程記錄的語句如下：INSERTINTOC_InfoVALUES('9720044','網絡技術與應用','選修',3,null)

5-2-1：如何使用values將一行新的記錄追加到表中的所有字段5.2表數據的管理例5-2-2：在C_Info表中添加一條新記錄，課程類別和課程說明暫缺。根據題意知，要求添加3個字段的數據，使用如下語句：INSERTINTOC_Info(C_NO,C_Name,C_Credit)VALUES('9720045','Web開發技術',2)注意：使用INSERT語句一次只能為表插入一行數據。增加多行使用多條insert如果INSERT語句違反約束或規則，或者它有與列的數據類型不兼容的值，那么該語句就會失敗，并且SQLServer將顯示錯誤信息。此時必須列出列名（因為提供的值的個數與表中列的個數不一致）。未列出的列中將自動填入缺省值，如果沒有設置缺省值則填入NULL

C_Info中的課程類別和課程說明列必須允許為NULL，因為系統實際插入的數據為：(‘9720045’,‘Web開發技術’,default,2,default)。5-2-2：如何使用values將一行新的記錄追加到表中的部分字段練習2：給“讀者”表中增加一條記錄，內容為：張華，借書證號為220100usetsglINSERTINTO讀者(姓名,借書證號)VALUES('王芳','210900’)練習1：給“讀者”表中增加一條記錄，內容為：王芳，借書證號為210900，女，19歲，專業，系別不詳。usetsglINSERTINTO讀者VALUES('210900','王芳','女',19,null,default)或者：usetsglINSERTINTO讀者(借書證號,姓名,性別,年齡)VALUES('210900','王芳','女',19)5.2表數據的管理例5-2-3：假設數據庫Student_db中已經創建好了課程信息數據表C_Info，試將新課程記錄（9720044，網絡技術與應用，選修，3）添加到課程信息數據表C_Info中。添加新課程記錄的語句如下：INSERTINTOC_Infoselect'9720044','網絡技術與應用','選修',3,null

5-2-3：如何使用select將一行或多行記錄追加到表中的所有字段5.2表數據的管理例5-2-4：在C_Info表中添加一條新記錄，課程類別和課程說明暫缺。根據題意知，要求添加3個字段的數據，使用如下語句：INSERTINTOC_Info(C_NO,C_Name,C_Credit)select'9720045','Web開發技術',25-2-4：如何使用select將一行新的記錄追加到表中的部分字段5.2表數據的管理例5-2-5：在student數據庫中新建一個結構與sc表相同的表we,并且將sc表中的記錄都追加到新表中新建表語句：createtablewe(we_idnvarchar(10),we_coid

int,we_grade

smallint,primarykey(we_id,we_coid))追加記錄語句：insertintoweselect*fromsc如果只追加主鍵字段insertintowe（we_id,we_coid)select學號,課號fromsc5-2-5：如何使用select將另外表中的一行或者多行記錄追加到本表中的部分或者全部字段5.2表數據的管理例5-2-6：把pubs數據庫中authors表中的author_id,au_fname追加到student數據庫中的stu表下的學號、姓名字段，使用如下語句：insertstudent.dbo.stu(學號,姓名)selectau_id,au_fnamefrompubs.dbo.authors5-2-6：如何使用select將另外數據庫中某表的一行或者多行記錄追加到本表中的部分或者全部字段注意：學號、姓名字段的數據類型應與追加記錄的字段author_id,au_fname數據類型一致或兼容學號、姓名字段的數據類型的長度應小于或者等于追加記錄的字段author_id,au_fname數據類型的長度。Stu表中其他為追加的字段必須允許為NULL5.2表數據的管理5.2.7使用Transact-SQL語言管理表數據表數據的刪除，可以使用DELECT語句，基本語法格式：DELECT[FROM]table_namewhere…….作用:根據where設定條件刪除一行或者多行數據例1：刪除新建的we表中的所有記錄Deletefromwe注意：刪除的只是表的記錄而不是表結構，表本身還存在。刪除表droptablewe例2刪除stu表中系別為空的所有記錄

deletefromstuwhere系別isnull例3刪除stu表中系別計算機、自動化的所有記錄

deletefromstuwhere系別in(‘計算機’,’自動化’)或者：deletefromstuwhere系別=‘計算機’or系別=‘自動化’

知識回顧1、對數據庫結構的修改？2、對數據表結構的修改？3、對數據表數據的修改？增加：insertinto….values(…..)select…from…where…

刪除：deletefrom….where…..修改？5.2表數據的管理5.2.8使用Transact-SQL語言管理表數據表數據的修改，可以使用update語句，基本語法格式：Updatetable_nameset{column_name=….}where…….作用:根據where設定條件修改一個字段或者多個字段的數據例1、在pub數據庫中將publishers表中的所有出版社都將總部遷移到喬治亞(Georgia)州的亞特蘭大(Atlanta)使用以下UPDATE語句：UsepubsgoUPDATEpublishersSETcity='Atlanta',state='Georgia'例2、在student數據庫中將stu

表中的所有備注字段數據為空的記錄都修改為“新生”使用語句：UsestudentgoUPDATEstuSET備注=‘新生‘where備注isnull例3、在tsgl數據庫中將圖書表中的所有單價上漲一倍使用語句：UsetsglgoUPDATE圖書SET單價=單價*1.1例4將student數據庫中的scourse表中課程號為‘2001’的學時增加2UsestudentgoUPDATEscourseSET學時=學時+2where課號=2001小結（續）

5.3數據完整性管理

數據庫是一種共享資源。因此，在數據庫的使用過程中保證數據的安全、可靠、正確、可用就成為非常重要的問題。數據庫的完整性保護可以保證數據的正確性和一致性。5.3.1數據完整性概述定義：數據的完整性是指數據庫中數據的正確性、有效性和一致性。正確性：是指數據的合法性，例如數值型數據只能包含數字不能包含字母；有效性：是指數據是否處在所定義域的有效范圍之內；一致性：是指同一事實的兩個數據應該一致，不一致即是不相容的。

5.3數據完整性管理

滿足完整性要求的數據應具有以下特點：數據類型準確無誤數據的值滿足范圍設置同一表格之間數據不存在沖突多個表格數據之間不存在沖突例如：在一個數據庫中有兩個表，人事表和財務表。人事表記錄了本單位的員工信息，財務表記錄了本單位員工的借款情況。如果某個員工有借款，那么他不能從人事表中刪除，這就是一種數據完整性約束。設計數據庫時考慮數據的完整性是設計的一個重要內容

5.3數據完整性管理

5.3.2數據完整性的類型實體完整性：規定表的每一行在表中是唯一的實體如：Unique,primarykey約束域完整性：數據庫表中的列必須滿足某種特定的數據類型或約束如：default,notnull約束引用（參照）完整性：指兩個表的主關鍵字和外關鍵字的數據應對應一致如：外鍵約束用戶定義完整性：針對某個特定關系數據庫的約束條件

存儲在數據庫中的所有數據值均應保持正確的狀態。如果數據庫中存儲有不正確的數據值，則該數據庫稱為已喪失數據完整性。

5.3數據完整性管理

5.3.3實現數據庫中的數據完整性

PRIMARYKEY約束主鍵

FOREIGNKEY約束外鍵

UNIQUE約束唯一

CHECK約束約束變量取值范圍

notnull非空約束

DEFAULT（默認值）約束

使用企業管理器創建數據完整性1、主鍵約束2、唯一性約束3、檢查性約束（check）舉例：在stu表中設定系別只能為自動化或者計算機，輸入：系in(‘自動化’，‘計算機’）使用企業管理器創建數據完整性4、外鍵約束外鍵的設置主要是通過將一個表中主鍵所含的列包含到另一個表中，這個列就是另一個表的外鍵。企業管理器創建外鍵即建立表間關系使用企業管理器創建數據完整性5、缺省約束6、notnull約束使用T-SQL創建數據完整性CONSTRAINT【約束名】約束類型約束對象

例1，為student表中的姓名列設notnull約束，國家列設置缺省值'中國',

CREATETABLEstudent(namechar(20)NOTNULL,countrychar(30)DEFAULT'中國')CREATETABLEstudent1(namechar(20)NOTNULL,countrychar(30)constraintwee1DEFAULT'中國’)注意：不能寫成CREATETABLEstudent1(namechar(20)NOTNULL,countrychar(30)，constraintwee1DEFAULT'中國’forcountry)例2建立主鍵、非空、檢查、缺省約束createtablestudent2(stu_nochar(6)primarykey,

stu_namechar(10)notnull,

stu_sexchar(2)check(stu_sex='男'orstu_sex='女'),/*約束此列的值只能輸入“男”或“女”值*/

stu_nationchar(10)default'漢族',)或者：createtablestudent32(stu_nochar(6)constraint約束1primarykey,

stu_namechar(10)notnull,

stu_sexchar(2)constraint約束2check(stu_sex='男'orstu_sex='女'),

stu_nationchar(10)constraint約束3default'漢族')或者：createtablestudent32(stu_nochar(6),

stu_namechar(10)notnull,

stu_sexchar(2),

stu_nationchar(10)constraint約束3default'漢',constraint約束1primarykey(stu_no),constraint約束2check(stu_sexin('男','女')))但是若建立好數據表之后，想要再往列增加約束或修改約束，則格式根據約束的不同各有不同：createtableabc

(s_nochar(10),s_namechar(10),s_sexchar(2),

s_nationchar(16))/*以上為建立一個沒有任何約束的新數據表*/altertableabc

altercolumns_nochar(10)notnullgo/*以上為給表的s_no列加上notnull約束*/altertableabcaddprimarykey(s_no)go/*以上為給表的s_no列加上primarykey約束*/

altertableabcaddcheck(s_sex=''男''ors_sex=''女'')/*給x_sex列加check約束*/goaltertableabc

adddefault'漢族'fors_nation

/*加default約束時格式和其他的有所不同*/go檢查表中都有哪些約束execsp_helpconst