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

第5章

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

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

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

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

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

語(yǔ)法格式：

ALTERTABLEtable

{[ALTERCOLUMNcolumn_name

]|[ADDcolumn_name

data_base]|[DROPCOLUMNcolumn_name

]|}

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

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

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

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

student.sc

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

ALTERTABLE

學(xué)生altercolumn

住址varchar(50）知識(shí)回顧建立表結(jié)構(gòu)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ù)類型，也可增加一些相應(yīng)約束條件）AlTERTABLE<表名>ADD<新列名稱><新數(shù)據(jù)類型>注意：約束條件不能有noy

null,primarykey等。

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

student.couse

add

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

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

student.couse

drop

columnid_xuanke

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

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

DROPTABLEtable_name

參數(shù)說(shuō)明：

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

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

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

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

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

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

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

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

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

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

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

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

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

int,we_grade

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

deletefromstuwhere系別isnull例3刪除stu表中系別計(jì)算機(jī)、自動(dòng)化的所有記錄

deletefromstuwhere系別in(‘計(jì)算機(jī)’,’自動(dòng)化’)或者：deletefromstuwhere系別=‘計(jì)算機(jī)’or系別=‘自動(dòng)化’

知識(shí)回顧1、對(duì)數(shù)據(jù)庫(kù)結(jié)構(gòu)的修改？2、對(duì)數(shù)據(jù)表結(jié)構(gòu)的修改？3、對(duì)數(shù)據(jù)表數(shù)據(jù)的修改？增加：insertinto….values(…..)select…from…where…

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

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

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

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

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

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

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

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

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

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

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

PRIMARYKEY約束主鍵

FOREIGNKEY約束外鍵

UNIQUE約束唯一

CHECK約束約束變量取值范圍

notnull非空約束

DEFAULT（默認(rèn)值）約束

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

例1，為student表中的姓名列設(shè)notnull約束，國(guó)家列設(shè)置缺省值'中國(guó)',

CREATETABLEstudent(namechar(20)NOTNULL,countrychar(30)DEFAULT'中國(guó)')CREATETABLEstudent1(namechar(20)NOTNULL,countrychar(30)constraintwee1DEFAULT'中國(guó)’)注意：不能寫成CREATETABLEstudent1(namechar(20)NOTNULL,countrychar(30)，constraintwee1DEFAULT'中國(guó)’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))/*以上為建立一個(gè)沒(méi)有任何約束的新數(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約束時(shí)格式和其他的有所不同*/go檢查表中都有哪些約束execsp_helpconst