第5章數(shù)據(jù)表管理

第5章

SQLServer數(shù)據(jù)表的管理

本章內容5.1數(shù)據(jù)表的創(chuàng)建和管理5.2表數(shù)據(jù)的管理5.3數(shù)據(jù)完整性管理

5.1數(shù)據(jù)表的創(chuàng)建和管理

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

5.1數(shù)據(jù)表的創(chuàng)建和管理

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

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

語法格式：

ALTERTABLEtable

{[ALTERCOLUMNcolumn_name

]|[ADDcolumn_name

data_base]|[DROPCOLUMNcolumn_name

]|}

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

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

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

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

student.sc

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

ALTERTABLE

學生altercolumn

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

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

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

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

null,primarykey等。

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

student.couse

add

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

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

student.couse

drop

columnid_xuanke

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

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

DROPTABLEtable_name

參數(shù)說明：

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

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

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

5.2表數(shù)據(jù)的管理

5.2.1使用企業(yè)管理管理表數(shù)據(jù)使用表設計器瀏覽、添加、修改、刪除數(shù)據(jù)

5.2表數(shù)據(jù)的管理

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

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

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

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

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

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

5-2-3：如何使用select將一行或多行記錄追加到表中的所有字段5.2表數(shù)據(jù)的管理例5-2-4：在C_Info表中添加一條新記錄，課程類別和課程說明暫缺。根據(jù)題意知，要求添加3個字段的數(shù)據(jù)，使用如下語句：INSERTINTOC_Info(C_NO,C_Name,C_Credit)select'9720045','Web開發(fā)技術',25-2-4：如何使用select將一行新的記錄追加到表中的部分字段5.2表數(shù)據(jù)的管理例5-2-5：在student數(shù)據(jù)庫中新建一個結構與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表數(shù)據(jù)的管理例5-2-6：把pubs數(shù)據(jù)庫中authors表中的author_id,au_fname追加到student數(shù)據(jù)庫中的stu表下的學號、姓名字段，使用如下語句：insertstudent.dbo.stu(學號,姓名)selectau_id,au_fnamefrompubs.dbo.authors5-2-6：如何使用select將另外數(shù)據(jù)庫中某表的一行或者多行記錄追加到本表中的部分或者全部字段注意：學號、姓名字段的數(shù)據(jù)類型應與追加記錄的字段author_id,au_fname數(shù)據(jù)類型一致或兼容學號、姓名字段的數(shù)據(jù)類型的長度應小于或者等于追加記錄的字段author_id,au_fname數(shù)據(jù)類型的長度。Stu表中其他為追加的字段必須允許為NULL5.2表數(shù)據(jù)的管理5.2.7使用Transact-SQL語言管理表數(shù)據(jù)表數(shù)據(jù)的刪除，可以使用DELECT語句，基本語法格式：DELECT[FROM]table_namewhere…….作用:根據(jù)where設定條件刪除一行或者多行數(shù)據(jù)例1：刪除新建的we表中的所有記錄Deletefromwe注意：刪除的只是表的記錄而不是表結構，表本身還存在。刪除表droptablewe例2刪除stu表中系別為空的所有記錄

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

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

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

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

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

5.3數(shù)據(jù)完整性管理

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

5.3數(shù)據(jù)完整性管理

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

5.3數(shù)據(jù)完整性管理

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

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

5.3數(shù)據(jù)完整性管理

5.3.3實現(xiàn)數(shù)據(jù)庫中的數(shù)據(jù)完整性

PRIMARYKEY約束主鍵

FOREIGNKEY約束外鍵

UNIQUE約束唯一

CHECK約束約束變量取值范圍

notnull非空約束

DEFAULT（默認值）約束

使用企業(yè)管理器創(chuàng)建數(shù)據(jù)完整性1、主鍵約束2、唯一性約束3、檢查性約束（check）舉例：在stu表中設定系別只能為自動化或者計算機，輸入：系in(‘自動化’，‘計算機’）使用企業(yè)管理器創(chuàng)建數(shù)據(jù)完整性4、外鍵約束外鍵的設置主要是通過將一個表中主鍵所含的列包含到另一個表中，這個列就是另一個表的外鍵。企業(yè)管理器創(chuàng)建外鍵即建立表間關系使用企業(yè)管理器創(chuàng)建數(shù)據(jù)完整性5、缺省約束6、notnull約束使用T-SQL創(chuàng)建數(shù)據(jù)完整性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('男','女')))但是若建立好數(shù)據(jù)表之后，想要再往列增加約束或修改約束，則格式根據(jù)約束的不同各有不同：createtableabc

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

s_nationchar(16))/*以上為建立一個沒有任何約束的新數(shù)據(jù)表*/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