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

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

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

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

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

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

向teacher表中輸入一條元組，元組相應(yīng)字段值如下：('611','張力軍','女','1975-7-7','副教授','教研室主任',null,,'海拉爾學(xué)府路號','黨員','zhanglijun@163.com',,'計算機科學(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','遠程技術(shù)'),('m07','傳感技術(shù)')【例4-4】建立一張新表js(bhchar(4),xmchar(8))，將teacher表的所有記錄的教師號和姓名插入到j(luò)s中。

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

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

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

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

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

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

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

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

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

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

AU_CITYvarchar(20)作者所在城市

TELvarchar(20)作者電話號碼

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