MySQL數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用第5章-編輯數(shù)據(jù)

第5章編輯數(shù)據(jù)本章學(xué)習(xí)目標(biāo)：掌握插入單行記錄和多行記錄的方法掌握更新記錄的方法，包括根據(jù)查詢(xún)子句更新記錄的方法掌握刪除記錄的方法，包括根據(jù)查詢(xún)子句刪除記錄的方法以及清空表的方法

工作場(chǎng)景導(dǎo)入學(xué)校教務(wù)處工作人員在工作中經(jīng)常會(huì)遇到更新教學(xué)管理數(shù)據(jù)庫(kù)中數(shù)據(jù)的情況。例如，有以下更新需求：新生入學(xué)時(shí)，需要大批量插入學(xué)生的選課信息；學(xué)生的選課信息錄入出錯(cuò)時(shí)需要進(jìn)行更改；修改各專(zhuān)業(yè)培養(yǎng)方案時(shí)，會(huì)添加一批新課程、修改一批課程的信息，或者刪除一批課程。引導(dǎo)問(wèn)題：如何插入單個(gè)記錄或多條記錄？如何更新記錄？如何刪除記錄？5.1插入數(shù)據(jù)對(duì)教學(xué)管理數(shù)據(jù)庫(kù)中的表錄入數(shù)據(jù)、修改和刪除數(shù)據(jù)庫(kù)可以通過(guò)SSMS管理器進(jìn)行操作。5.1.1插入單行數(shù)據(jù)使用INSERT語(yǔ)句可向指定表中插入數(shù)據(jù)。INSERT語(yǔ)法的基本結(jié)構(gòu)如下：INSERT[INTO]<table_name>(column_name1,column_name2…,column_namen)VALUES(values1,values2,…,valuesn)其中，INTO關(guān)鍵字是可選的，可以忽略，teble_name指的是表的名稱(chēng)，column_name1,column_name2…,column_namen指的是表中定義的列名稱(chēng)，這些列必須在表中已經(jīng)定義，VALUES子句中的值values1,values2,…,valuesn指的是要插入的記錄在各列中的取值。INSERT語(yǔ)句中的列名必須與VALUES子句中的值一一對(duì)應(yīng)，且數(shù)據(jù)類(lèi)型一致。1.最簡(jiǎn)單的INSERT語(yǔ)句【例5-1】向教學(xué)管理數(shù)據(jù)庫(kù)中的課程表中增加一門(mén)新課程，課程名稱(chēng)為“SQLServer2014”，其先修課為離散數(shù)學(xué)（課程號(hào)為1008），學(xué)分為3分。代碼如下：INSERTINTO課程(課程號(hào)，課程名，先修課程號(hào)，學(xué)分)VALUES('1007','SQLServer2014','1002',3)2.省略清單的INSERT語(yǔ)句【例5-2】使用省略清單的INSERT語(yǔ)句向教學(xué)管理數(shù)據(jù)庫(kù)中的課程表中增加一條新記錄，課程名稱(chēng)為“算法設(shè)計(jì)與分析”，課程號(hào)為“1017”，先修課為C語(yǔ)言（課程號(hào)為1003），學(xué)分為4分。代碼如下：INSERTINTO課程VALUES('1006','C語(yǔ)言','1005',3)3.使用DEFAULTVALUES子句上例中向課程表插入新課信息時(shí)，如果表中先修課程號(hào)與學(xué)分這兩列允許插入空值或默認(rèn)值，則插入記錄的代碼也可以寫(xiě)成如下形式：INSERTINTO課程VALUES('1017','C語(yǔ)言',DEFAULT,NULL)如果表中所有列都允許為空，或者定義有默認(rèn)值或者其它可自動(dòng)獲取的數(shù)據(jù)類(lèi)型，則可以使用INSERT...DEFAULTVALUES語(yǔ)句向表中插入一行僅用默認(rèn)值的記錄，此行記錄唯一的參數(shù)是表名，列名和數(shù)據(jù)值都沒(méi)有。假設(shè)課程表中所有列都允許為空或者有默認(rèn)值，則可以使用如下語(yǔ)句向課程表中插入一條僅含有默認(rèn)值的記錄。INSERTINTO課程DEFAULTVALUES5.1.2插入多行數(shù)據(jù)利用INSERT…VALUES語(yǔ)句也可以向數(shù)據(jù)庫(kù)的表格中插入多行記錄，其語(yǔ)法結(jié)構(gòu)如下：INSERT[INTO]<table_name>(column_name1,column_name2…,column_namen)VALUES(val11,val12,…,val1n),(val21,val22,…,val2n),…,(valn1,valn2,…,valnn)其中，需要在VALUES后面輸入各條記錄的值。上述方法雖然能夠達(dá)到插入多行數(shù)據(jù)的目的，但是需要錄入大量的數(shù)據(jù)值，效率過(guò)低。下面介紹兩種較為高效的多行數(shù)據(jù)插入方法。5.1.2插入多行數(shù)據(jù)利用SELECT插入查詢(xún)結(jié)果集在T-SQL語(yǔ)言中，最常用且最簡(jiǎn)單的插入多行數(shù)據(jù)的方法是利用INSERT…SELECT語(yǔ)句。它使用SELECT語(yǔ)句查詢(xún)出的結(jié)果代替VALUES子句，將結(jié)果集作為多行記錄插入到表中。INSERT…SELECT語(yǔ)句借助SELECT語(yǔ)句的靈活性，可以從任何地方抽取任意多行數(shù)據(jù)，并對(duì)數(shù)據(jù)進(jìn)行轉(zhuǎn)，從而作為返回結(jié)果集插入數(shù)據(jù)庫(kù)表中。其語(yǔ)法結(jié)構(gòu)如下：INSERT[INTO]table_name[(column_list)]SELECTcolumn_listFROMtable_nameWHEREsearch_conditions其中，search_conditions指的是查詢(xún)條件，INSERT表和SELECT表的結(jié)果集的列數(shù)、列序、數(shù)據(jù)類(lèi)型必須一致?！纠?-3】創(chuàng)建一個(gè)學(xué)分表，然后把每位學(xué)生選修的課程所獲得的學(xué)分輸入到學(xué)分表中。T-SQL代碼如下：--創(chuàng)建學(xué)分表CREATETABLE學(xué)分表(學(xué)號(hào)char(10)notnull,

姓名varchar(10)notnull,

選修課程門(mén)數(shù)

tinyint,

學(xué)分

tinyint)--插入數(shù)據(jù)INSERT學(xué)分表SELECT學(xué)生.學(xué)號(hào),姓名,COUNT(選課.課程號(hào)),SUM(學(xué)分)FROM學(xué)生,選課,課程WHERE學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào)AND選課.課程號(hào)=課程.課程號(hào)GROUPBY學(xué)生.學(xué)號(hào),姓名5.1.2插入多行數(shù)據(jù)5.1.2插入多行數(shù)據(jù)2.大批量插入數(shù)據(jù)BULKINSERT語(yǔ)句是批量加載數(shù)據(jù)的一種方式，它按照用戶(hù)指定的格式把大量數(shù)據(jù)插入到數(shù)據(jù)庫(kù)的表中。BULKINSERT語(yǔ)句經(jīng)常與FIELDTERMINATOR子句和ROWTERMINATOR子句一起使用，前者用于指定字段之間的分隔符，后者用于指定行之間的分隔符?！纠?-4】使用大批量插入數(shù)據(jù)記錄的方法將txt文件中的數(shù)據(jù)插入到指定的表中。T-SQL代碼如下：--創(chuàng)建表

bulk_tableCREATETABLEbulk_table(c1char(4),c2int)--插入數(shù)據(jù)BULKINSERTbulk_tableFROM'F:\temp\bulk_test.txt'WITH(FIELDTERMINATOR=';',ROWTERMINATOR='\n')GO5.2修改數(shù)據(jù)在數(shù)據(jù)輸入過(guò)程中，可能會(huì)出現(xiàn)輸入錯(cuò)誤，或是因?yàn)闀r(shí)間變化而需要更新數(shù)據(jù)，這都需要修改數(shù)據(jù)。修改表中的數(shù)據(jù)可以使用查詢(xún)分析器中的網(wǎng)格界面進(jìn)行修改。本節(jié)主要介紹使用T-SQL的UPDATE語(yǔ)句實(shí)現(xiàn)數(shù)據(jù)的修改，UPDATE語(yǔ)句的語(yǔ)法格式如下：UPDATEtable_nameSETcolumn_1=expression_1,column_2=expression_2,…,column_n=expression_n[WHEREsearch_conditions]SET子句后面既可以跟具體的值，也可以是一個(gè)表達(dá)式。UPDATE語(yǔ)句只更新WHERE子句篩選的行，如果不帶WHERE子句，則表中的所有行都將被更新。WHERE子句的條件也可以是一個(gè)子查詢(xún)。5.2.1修改單行數(shù)據(jù)

【例5-5】將SQLServer2014課程的學(xué)分改為4分。T-SQL代碼如下：UPDATE課程SET學(xué)分=6WHERE課程名='數(shù)據(jù)結(jié)構(gòu)'執(zhí)行結(jié)果如圖5-4所示。如果上例中沒(méi)有WHERE子句，則表示把所有課程的學(xué)分都更新為6分。UPDATE語(yǔ)句還可以同時(shí)修改一個(gè)表中的多個(gè)值。例如：UPDATE課程SET課程名='SQLServer2014數(shù)據(jù)庫(kù)設(shè)計(jì)',學(xué)分=2WHERE課程名='SQLServer2014'5.2.2修改多行數(shù)據(jù)【例5-6】將選課表中所有選修“線(xiàn)性代數(shù)”課的學(xué)生的成績(jī)減3分。T-SQL代碼如下：UPDATE選課SET成績(jī)=成績(jī)-3WHERE課程號(hào)IN(SELECT課程號(hào)FROM課程WHERE課程名='線(xiàn)性代數(shù)')5.3刪除數(shù)據(jù)隨著系統(tǒng)的運(yùn)行，表中可能會(huì)產(chǎn)生一些無(wú)用的數(shù)據(jù)，這些數(shù)據(jù)不僅占用空間，而且還影響查詢(xún)的速度，所以應(yīng)該及時(shí)地刪除。刪除數(shù)據(jù)可以使用DELETE語(yǔ)句和TRUNCATETABLE語(yǔ)句。5.3.1使用DELETE語(yǔ)句刪除數(shù)據(jù)從表中刪除數(shù)據(jù)，最常用的是DELETE語(yǔ)句。DELETE語(yǔ)句的語(yǔ)法格式如下：DELETEFROMtable_name[WHEREsearch_conditions]如果省略了WHEREsearch_conditions子句，就表示刪除數(shù)據(jù)表中的全部數(shù)據(jù)；如果加上了WHEREsearch_conditions子句就可以根據(jù)篩選條件刪除表中的指定數(shù)據(jù)。【例5-7】刪除學(xué)生表中的所有記錄。T-SQL代碼如下：DELETEFROM學(xué)生本例中沒(méi)有使用WHERE語(yǔ)句，將刪除選課表中的所有記錄，只剩下表的定義。用戶(hù)可以通過(guò)“資源管理器”查看?！纠?-8】刪除課程表中沒(méi)有學(xué)分的記錄。T-SQL代碼如下：DELETE課程WHERE學(xué)分ISNULL【例5-9】刪除選課表中姓名為“李華”、選修課程為“1003”的選課信息。T-SQL代碼如下：DELETE選課WHERE選課.課程號(hào)='1003'AND學(xué)號(hào)=(SELECT學(xué)號(hào)FROM學(xué)生WHERE姓名='李華')用戶(hù)在操作數(shù)據(jù)庫(kù)時(shí)，要謹(jǐn)慎使用DELETE語(yǔ)句，因?yàn)閳?zhí)行該語(yǔ)句后，數(shù)據(jù)會(huì)從數(shù)據(jù)庫(kù)中永久的被刪除。5.4.1使用DELETE語(yǔ)句刪除數(shù)據(jù)5.3.2使用TRUNCATETABLE語(yǔ)句清空表使用TRUNCATETABLE語(yǔ)句刪除所有記錄的語(yǔ)法格式如下：TRUNCATETABLEtable_name其中，TRUNCATETABLE為關(guān)鍵字，table_name為要?jiǎng)h除記錄的表名。使用TRUNCATETABLE語(yǔ)句比DELETE語(yǔ)句要快，因?yàn)樗侵痦?yè)刪除表中的內(nèi)容，而DELETE則是逐行刪除內(nèi)容。TRUNCATETABLE是不記錄日志的操作，它將釋放表的數(shù)據(jù)和索引所占據(jù)的所有空間以及所有為全部索引分配的頁(yè)，刪除的數(shù)據(jù)是不可恢復(fù)的。而DELETE語(yǔ)句則不同，它在刪除每一行記錄時(shí)都要把刪除操作記錄在日志中。刪除操作記錄在日志中，可以通過(guò)事務(wù)回滾來(lái)恢復(fù)刪除的數(shù)據(jù)。用TRUNCATETABLE和DELETE語(yǔ)句都可以刪除所有的記錄，但是表結(jié)構(gòu)還在，而DROPTABLE不但刪除表中的數(shù)據(jù)，而且還刪除表的結(jié)構(gòu)并釋放空間?！纠?-10】使用TRUNCATETABLE語(yǔ)句清空課程表。T-SQL代碼如下：TRUNCATETABLE課程5.5多數(shù)據(jù)編輯EditTableData特性使可以將插入、更新和刪除語(yǔ)句組合在一起。它僅僅是將所有修改生成到一個(gè)動(dòng)態(tài)的腳本文件中。當(dāng)你運(yùn)行該腳本文件時(shí)，它會(huì)按照你在模型中給各修改安排的相同順序來(lái)執(zhí)行所有修改。當(dāng)修改超出了一個(gè)事務(wù)的范圍時(shí)，會(huì)存在在執(zhí)行所有修改之前暴露部分修改的風(fēng)險(xiǎn)。例如，假設(shè)在你