第4章數(shù)據(jù)庫(kù)數(shù)據(jù)操作

數(shù)據(jù)庫(kù)系統(tǒng)開(kāi)發(fā)與應(yīng)用主講：凃云杰電子工業(yè)出版社鄭阿奇主編第

四章數(shù)據(jù)庫(kù)數(shù)據(jù)操作教學(xué)目標(biāo)：了解：索引的概念理解：創(chuàng)建索引的必要性掌握：數(shù)據(jù)記錄的增刪改操作、索引的創(chuàng)建。重點(diǎn)難點(diǎn)：教學(xué)重點(diǎn)：數(shù)據(jù)記錄的增刪改操作、創(chuàng)建索引的方法。教學(xué)難點(diǎn)：用命令方式創(chuàng)建索引的方法、數(shù)據(jù)記錄的增刪改操作1．插入數(shù)據(jù)記錄在“對(duì)象資源管理器”中展開(kāi)“數(shù)據(jù)庫(kù)jxgl”，用鼠標(biāo)右鍵單擊要進(jìn)行操作的表teacher，在彈出的快捷菜單上選擇“編輯前200行”菜單項(xiàng)，打開(kāi)“表數(shù)據(jù)”窗口。在此窗口中，表中的記錄將按行顯示，每個(gè)記錄占一行。

4.1使用SSMS操作表中的數(shù)據(jù)2．修改數(shù)據(jù)記錄如果信息有變動(dòng)，定位到所在的一行的相應(yīng)位置進(jìn)行修改，修改之后將光標(biāo)移到下一行保存修改的內(nèi)容。3．刪除數(shù)據(jù)記錄在表

的“表數(shù)據(jù)”窗口中定位需被刪除的記錄行，單擊該行最前面的黑色箭頭處選擇全行，用鼠標(biāo)右鍵單擊，選擇“刪除(D)”菜單項(xiàng)。選擇“刪除(D)”后，將出現(xiàn)一個(gè)確認(rèn)對(duì)話框，單擊“是”按鈕將刪除所選擇的記錄行。1．使用INSERT語(yǔ)句輸入數(shù)據(jù)

使用T-SQL的INSERTINTO語(yǔ)句，可以向表中輸入數(shù)據(jù)?；菊Z(yǔ)法如下：INSERT[INTO]<table_name|view_name>[columnlist]VALUES(valueslist|selectstatement)4.2使用T-SQL操作表中的數(shù)據(jù)INSERT語(yǔ)句輸入數(shù)據(jù)時(shí)應(yīng)注意以下問(wèn)題：⑴當(dāng)向表中所有列輸入數(shù)據(jù)時(shí)，columnlist子句可以缺省。若缺省該子句，數(shù)據(jù)清單中數(shù)據(jù)的順序一定與表中列的順序一致。⑵當(dāng)要輸入數(shù)據(jù)的表中包含有標(biāo)識(shí)列、允許為空的列、設(shè)置默認(rèn)值的列時(shí)，可以不輸入該列的值。⑶當(dāng)向字符型、日期型列輸入數(shù)據(jù)時(shí)，要以單引號(hào)引起來(lái)。（1）向表中輸入一行數(shù)據(jù)【例4-1】向表major中插入一條合法記錄。USEjxglGOINSERTINTOmajorVALUES('m05','電子商務(wù)')練習(xí)：

向teacher表中輸入一條元組，元組相應(yīng)字段值如下：('611','張力軍','女','1975-7-7','副教授','教研室主任',null,,'海拉爾學(xué)府路號(hào)','黨員','zhanglijun@163.com',,'計(jì)算機(jī)科學(xué)與技術(shù)學(xué)院')（2）輸入一行中的部分列【例4-2】向表teacher插入一條合法記錄。代碼如下：USEJXGLGOINSERTINTOteacher(tno,tname,tsex,professionaltitle)

--列名清單只列出需要輸入數(shù)據(jù)的列VALUES('t09','董衛(wèi)剛','男','講師')

--輸入清單中列出的部分列的數(shù)據(jù)GO（3）一次向表中輸入多行記錄【例4-3】向major表輸入2條合法記錄。USEJXGLGOINSERTINTOmajorVALUES('m06','遠(yuǎn)程技術(shù)'),('m07','傳感技術(shù)')【例4-4】建立一張新表js(bhchar(4),xmchar(8))，將teacher表的所有記錄的教師號(hào)和姓名插入到j(luò)s中。

CREATETABLEjs(bhCHAR(4),xmCHAR(8))GOINSERTINTOjsSELECTtno,tnameFROMteacher對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫(kù)。第一步：建表CREATETABLEDept_age(SdeptCHAR(15), /*系名*/Avg_agenumeric(5,2)) /*學(xué)生平均年齡*/練習(xí)第二步：插入數(shù)據(jù)

INSERTINTODept_age(Sdept，Avg_age)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；2．使用DELETE語(yǔ)句刪除表中的數(shù)據(jù)行表中沒(méi)有意義的數(shù)據(jù)，使用DELETE語(yǔ)句以行為單位刪除?；菊Z(yǔ)法如下：DELETE[FROM]table_name|view_nameWHEREsearch_condition【例4-5】刪除表major中majorno為‘m05’的行。代碼如下：DELETEFROMmajorWHEREmajorno='m05'GO練習(xí)：刪除teacher表中張力軍老師的記錄。3．使用UPDATE語(yǔ)句更新表中的數(shù)據(jù)使用UPDATE語(yǔ)句可以更新表的一行、一行中的某些列，也可以更新表中所有行或所有行的某些列?；菊Z(yǔ)法如下：UPDATEtable_nameSETcolumn_name=express|NULL|DEFAULT[,…n][WHERElogical_express]其中：SET子句：指定要被更新的列及其新的值。WHERE子句：指定修改條件，只有滿足條件的那些行的列值才被修改為SET子句指定的數(shù)據(jù)。express：新數(shù)據(jù)可以是常量或表達(dá)式。（1）更新表中所有行的某一列?！纠?-6】將表student_teacher_course中所有同學(xué)的成績(jī)提高5分。USEJXGLGOUPDATEstudent_teacher_courseSETgrade=grade+5GO（2）更新表中某些行的某一列。【例4-7】將表student_teacher_course中學(xué)號(hào)為‘2010010001’的同學(xué)所有課程的成績(jī)提高5分。代碼如下：USEJXGLGOUPDATEstudent_teacher_courseSETgrade=grade+5WHEREsno='2010010001'GO（3）更新表中多個(gè)行的多個(gè)列。【例4-8】將表major_course中專業(yè)基礎(chǔ)課的學(xué)分提高0.5分，考試形式改為閉卷。代碼如下：USEJXGLGOUPDATEmajor_courseSETcredit=credit+0.5,examinationform='閉卷'WHEREtype='專業(yè)基礎(chǔ)'GOTRUNCATETABLE語(yǔ)句使用TRUNCATETABLE語(yǔ)句將刪除指定表中的所有數(shù)據(jù)，因此也稱為清除表數(shù)據(jù)語(yǔ)句。語(yǔ)法格式如下：TRUNCATETABLE<表名>說(shuō)明：使用TRUNCATETABLE語(yǔ)句刪除了指定表中的所有行，但表的結(jié)構(gòu)及其列、約束、索引等保持不變，而新行標(biāo)識(shí)所用的計(jì)數(shù)值重置為該列的初始值。如果想保留標(biāo)識(shí)計(jì)數(shù)值，則要使用DELETE語(yǔ)句。TRUNCATETABLE在功能上與不帶WHERE子句的DELETE語(yǔ)句相同，二者均刪除表中的全部行。但TRUNCATETABLE比DELETE速度快，且使用的系統(tǒng)和事務(wù)日志資源少。設(shè)有三個(gè)關(guān)系：C（CNO，CNAME，PCNO）SC（SNO，CNO，SCORE）S（SNO，SNAME，AGE，SEX）其中：C為課程表關(guān)系，對(duì)應(yīng)的屬性分別是課號(hào)、課程名和預(yù)選課號(hào)；SC為學(xué)生選課表關(guān)系，對(duì)應(yīng)的屬性分別是學(xué)號(hào)、課號(hào)和成績(jī)；S表示學(xué)生，它的各屬性依次為學(xué)號(hào)、姓名、年齡和性別。用SQL語(yǔ)言寫出：（1）向S表中插入一條新元組（‘2011010001’，‘張力’，19，‘男’）。（2）將學(xué)號(hào)為‘2011010001’的學(xué)生年齡改為20。（3）刪除學(xué)號(hào)為‘2011010001’的學(xué)生記錄。（4）建立一反映各學(xué)生總成績(jī)的視圖S_ZCJ，視圖包括學(xué)號(hào)、姓名、總成績(jī)?nèi)小?.索引的概念

索引是對(duì)表或視圖中的一列或多個(gè)列的值進(jìn)行排序的一種物理結(jié)構(gòu)，它由該表或視圖中的一列或若干列值，以及這些列值的記錄在數(shù)據(jù)表中存儲(chǔ)位置的物理地址所組成。

（1）聚集索引（CLUSTEREDINDEX，也稱聚類索引、簇集索引）。聚集索引是表數(shù)據(jù)的物理排序。

（2）非聚集索引（NONCLUSTEREDINDEX，也稱非聚類索引、非簇集索引）。非聚集索引具有與表數(shù)據(jù)完全分離的存儲(chǔ)結(jié)構(gòu)。

4.3索引索引的優(yōu)缺點(diǎn)(1)索引的優(yōu)點(diǎn)唯一性索引可保證表中每一行數(shù)據(jù)的唯一性加快數(shù)據(jù)查詢速度，尤其是數(shù)據(jù)量很大時(shí)。（最主要原因）加速表和表之間連接速度，特別是在實(shí)現(xiàn)數(shù)據(jù)的參照完整性方面特別有意義。在使用分組和排序子句進(jìn)行數(shù)據(jù)查詢時(shí)，可以顯著減少分組和排序時(shí)間。通過(guò)使用索引，可以在查詢過(guò)程中使用優(yōu)化隱藏器，提高系統(tǒng)性能。(2)索引的缺點(diǎn)索引需要占用數(shù)據(jù)表以外的物理存儲(chǔ)空間創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，且這個(gè)時(shí)間隨著數(shù)據(jù)量的增加而增加。當(dāng)表中的數(shù)據(jù)增加、刪除和修改時(shí)，索引也要?jiǎng)討B(tài)地更改或重建，降低數(shù)據(jù)的更新速度。索引的應(yīng)用原則主鍵列一般需要建立索引，因?yàn)橥ǔ２樵兒痛嫒”矶际峭ㄟ^(guò)主鍵進(jìn)行的。外鍵列一般需要建立索引，以便保證進(jìn)行表連接操作時(shí)的快速執(zhí)行。經(jīng)常查詢的字段最好建立索引。索引中列的組合取決于哪些字段經(jīng)常一起作為聯(lián)合查詢條件。數(shù)據(jù)量小的表、很少或從來(lái)不在查詢中作為條件引用的列，只有很少取值的列，定義為text、image和bit數(shù)據(jù)類型的列不要建立索引。當(dāng)更新或插入操作的性能要求比查詢操作的性能要求更重要時(shí)，最好不建立索引。2．使用T-SQL語(yǔ)句創(chuàng)建索引可以使用T-SQL的CREATEINDEX語(yǔ)句創(chuàng)建索引。CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}(column[ASC|DESC][,...n])[WITH<index_option>[,...n]][ONfilegroup][;]其中：UNIQUE：指明創(chuàng)建的是唯一索引。CLUSTERED|NONCLUSTERED：指明創(chuàng)建的索引為聚集索引還是非聚集索引。系統(tǒng)默認(rèn)創(chuàng)建的索引為非聚集索引。ASC|DESC：指定特定的索引列的排序方式，默認(rèn)為升序（ASC）。Column：指定被索引的列。如果使用兩個(gè)或兩個(gè)以上的列組成一個(gè)索引，則稱為復(fù)合索引?！纠?-9】在表student上創(chuàng)建非聚集索引idstu，該索引基于“班級(jí)”和“出生日期”兩個(gè)字段，并按照“班級(jí)”升序、“出生日期”降序創(chuàng)建。CreatenonclusteredindexclassbirOnstudent(classno,birthdaydesc)【例4-10】為teacher創(chuàng)建唯一索引，該索引基于身份證號(hào)identitycard字段CreateuniqueindexteaidOnteacher(identitycarddesc)go3．使用系統(tǒng)存儲(chǔ)過(guò)程查看索引信息通過(guò)執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程sp_helpindex或sp_help，可以查看數(shù)據(jù)表的索引信息?；菊Z(yǔ)法如下：

[EXEC]{sp_helpindex|sp_help}table_name【例4-11】查看表student中的索引信息。代碼如下：sp_helpindexstudentGOsp_helpindex'student'GO4．使用系統(tǒng)存儲(chǔ)過(guò)程更改索引名稱使用系統(tǒng)存儲(chǔ)過(guò)程sp_rename可以更改索引的名稱，基本語(yǔ)法如下：

[EXEC]sp_rename'old_table_name','new_table_name'【例4-12】將索引classbir的名稱修改為idx_class。代碼如下：

sp_rename'student.classbir','student.idx_class'GO5．使用T-SQL語(yǔ)句刪除索引當(dāng)不再需要某個(gè)索引時(shí)，要及時(shí)把索引從數(shù)據(jù)庫(kù)中刪除掉。對(duì)因?yàn)樵O(shè)置PRIMARYKEY約束或UNIQUE約束時(shí)由系統(tǒng)創(chuàng)建的索引，可以通過(guò)刪除約束的方法刪除索引；對(duì)于用戶手工創(chuàng)建的其他索引，可以使用DROPINDEX語(yǔ)句刪除，基本語(yǔ)法如下：

DROPINDEXtable_name.index_name[,…n]【例4-13】將表teacher中的索引teaid刪除。代碼如下：DROPINDEXteacher.teaidGO本次課講解了數(shù)據(jù)的增刪改的操作與索引的相關(guān)操作?？偨Y(jié)作業(yè)：字段名數(shù)據(jù)類型字段說(shuō)明其他AU_IDint作者ID號(hào)主鍵AU_NAMEvarchar(80)作者姓名

AU_CITYvarchar(20)作者所在城市

TELvarchar(20)作者電話號(hào)碼

假設(shè)有三張表：TITLES表、AUTHORS表和PUBLISHERS