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

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

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

姓名varchar(10)notnull,

選修課程門數(shù)

tinyint,

學(xué)分

tinyint)--插入數(shù)據(jù)INSERT學(xué)分表SELECT學(xué)生.學(xué)號,姓名,COUNT(選課.課程號),SUM(學(xué)分)FROM學(xué)生,選課,課程WHERE學(xué)生.學(xué)號=選課.學(xué)號AND選課.課程號=課程.課程號GROUPBY學(xué)生.學(xué)號,姓名5.1.2插入多行數(shù)據(jù)5.1.2插入多行數(shù)據(jù)2.大批量插入數(shù)據(jù)BULKINSERT語句是批量加載數(shù)據(jù)的一種方式，它按照用戶指定的格式把大量數(shù)據(jù)插入到數(shù)據(jù)庫的表中。BULKINSERT語句經(jīng)常與FIELDTERMINATOR子句和ROWTERMINATOR子句一起使用，前者用于指定字段之間的分隔符，后者用于指定行之間的分隔符。【例5-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ù)輸入過程中，可能會出現(xiàn)輸入錯誤，或是因為時間變化而需要更新數(shù)據(jù)，這都需要修改數(shù)據(jù)。修改表中的數(shù)據(jù)可以使用查詢分析器中的網(wǎng)格界面進行修改。本節(jié)主要介紹使用T-SQL的UPDATE語句實現(xiàn)數(shù)據(jù)的修改，UPDATE語句的語法格式如下：UPDATEtable_nameSETcolumn_1=expression_1,column_2=expression_2,…,column_n=expression_n[WHEREsearch_conditions]SET子句后面既可以跟具體的值，也可以是一個表達式。UPDATE語句只更新WHERE子句篩選的行，如果不帶WHERE子句，則表中的所有行都將被更新。WHERE子句的條件也可以是一個子查詢。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所示。如果上例中沒有WHERE子句，則表示把所有課程的學(xué)分都更新為6分。UPDATE語句還可以同時修改一個表中的多個值。例如：UPDATE課程SET課程名='SQLServer2014數(shù)據(jù)庫設(shè)計',學(xué)分=2WHERE課程名='SQLServer2014'5.2.2修改多行數(shù)據(jù)【例5-6】將選課表中所有選修“線性代數(shù)”課的學(xué)生的成績減3分。T-SQL代碼如下：UPDATE選課SET成績=成績-3WHERE課程號IN(SELECT課程號FROM課程WHERE課程名='線性代數(shù)')5.3刪除數(shù)據(jù)隨著系統(tǒng)的運行，表中可能會產(chǎn)生一些無用的數(shù)據(jù)，這些數(shù)據(jù)不僅占用空間，而且還影響查詢的速度，所以應(yīng)該及時地刪除。刪除數(shù)據(jù)可以使用DELETE語句和TRUNCATETABLE語句。5.3.1使用DELETE語句刪除數(shù)據(jù)從表中刪除數(shù)據(jù)，最常用的是DELETE語句。DELETE語句的語法格式如下：DELETEFROMtable_name[WHEREsearch_conditions]如果省略了WHEREsearch_conditions子句，就表示刪除數(shù)據(jù)表中的全部數(shù)據(jù)；如果加上了WHEREsearch_conditions子句就可以根據(jù)篩選條件刪除表中的指定數(shù)據(jù)。【例5-7】刪除學(xué)生表中的所有記錄。T-SQL代碼如下：DELETEFROM學(xué)生本例中沒有使用WHERE語句，將刪除選課表中的所有記錄，只剩下表的定義。用戶可以通過“資源管理器”查看?！纠?-8】刪除課程表中沒有學(xué)分的記錄。T-SQL代碼如下：DELETE課程WHERE學(xué)分ISNULL【例5-9】刪除選課表中姓名為“李華”、選修課程為“1003”的選課信息。T-SQL代碼如下：DELETE選課WHERE選課.課程號='1003'AND學(xué)號=(SELECT學(xué)號FROM學(xué)生WHERE姓名='李華')用戶在操作數(shù)據(jù)庫時，要謹慎使用DELETE語句，因為執(zhí)行該語句后，數(shù)據(jù)會從數(shù)據(jù)庫中永久的被刪除。5.4.1使用DELETE語句刪除數(shù)據(jù)5.3.2使用TRUNCATETABLE語句清空表使用TRUNCATETABLE語句刪除所有記錄的語法格式如下：TRUNCATETABLEtable_name其中，TRUNCATETABLE為關(guān)鍵字，table_name為要刪除記錄的表名。使用TRUNCATETABLE語句比DELETE語句要快，因為它是逐頁刪除表中的內(nèi)容，而DELETE則是逐行刪除內(nèi)容。TRUNCATETABLE是不記錄日志的操作，它將釋放表的數(shù)據(jù)和索引所占據(jù)的所有空間以及所有為全部索引分配的頁，刪除的數(shù)據(jù)是不可恢復(fù)的。而DELETE語句則不同，它在刪除每一行記錄時都要把刪除操作記錄在日志中。刪除操作記錄在日志中，可以通過事務(wù)回滾來恢復(fù)刪除的數(shù)據(jù)。用TRUNCATETABLE和DELETE語句都可以刪除所有的記錄，但是表結(jié)構(gòu)還在，而DROPTABLE不但刪除表中的數(shù)據(jù)，而且還刪除表的結(jié)構(gòu)并釋放空間。【例5-10】使用TRUNCATETABLE語句清空課程表。T-SQL代碼如下：TRUNCATETABLE課程5.5多數(shù)據(jù)編輯EditTableData特性使可以將插入、更新和刪除語句組合在一起。它僅僅是將所有修改生成到一個動態(tài)的腳本文件中。當你運行該腳本文件時，它會按照你在模型中給各修改安排的相同順序來執(zhí)行所有修改。當修改超出了一個事務(wù)的范圍時，會存在在執(zhí)行所有修改之前暴露部分修改的風險。例如，假設(shè)在你