項目4 數(shù)據(jù)插入、刪除、修改和查詢(沖突時的文件備份2013-08-18 16-09-07)

數(shù)據(jù)插入、刪除、修改和查詢項目4技能目標及知識目標√熟悉數(shù)據(jù)增加、刪除、修改的SQL語法；√掌握數(shù)據(jù)增加、刪除、的方法√掌握簡單查詢，條件查詢、連接查詢、嵌套查詢的方法；√掌握聚合函數(shù)的用法；√掌握分組篩選，計算匯總的方法；2項目導(dǎo)引創(chuàng)建數(shù)據(jù)庫和表的目的在于存儲數(shù)據(jù)，系統(tǒng)開發(fā)過程中，更多的時候是對表的數(shù)據(jù)進行管理，包括數(shù)據(jù)的插入、修改（更新）、刪除、查詢。通常這些操作都是通過SQL語句來完成的，所以本項目需要重點掌握應(yīng)用SQL語句來進行數(shù)據(jù)的管理。數(shù)據(jù)查詢是數(shù)據(jù)庫的重要技術(shù)，是數(shù)據(jù)庫的主要應(yīng)用目的；熟練掌握各種查詢?yōu)閿?shù)據(jù)庫應(yīng)用系統(tǒng)的開發(fā)奠定基礎(chǔ)；因此，本項目用三個任務(wù)詳細學(xué)習(xí)各種查詢。3任務(wù)1插入、修改、刪除表數(shù)據(jù)一、任務(wù)背景表數(shù)據(jù)操作。DML、DDL、DCL的概念。4任務(wù)1插入、修改、刪除表數(shù)據(jù)二、任務(wù)需求1.使用INSERT語句向Product表添加兩條記錄；2.使用省略INSERT子句列表方法向Product表添加兩條數(shù)據(jù)；3.建立Product備用表格Producr1，然后使用INSERT…SELECT語句的方法插入數(shù)據(jù)；4.使用SELECT…INTO語句完成新建“飛毛腿商品”表格的同時插入數(shù)據(jù)；5.將Product表格中所有Catcode為“A01”的商品的Price降低5元；6.使用DELETE語句刪除Product表格中價格小于100的商品；7.使用TRUNCATETABLE語句刪除Product1表格中的剩余數(shù)據(jù)。5任務(wù)1插入、修改、刪除表數(shù)據(jù)三、任務(wù)分析此任務(wù)的需求主要是進行表數(shù)據(jù)的管理，主要包括表數(shù)據(jù)的插入、修改和刪除。通常，表和視圖數(shù)據(jù)管理可使用SQLServer2008管理平臺和Transact-SQL語言兩種方法來完成。使用SQLserver2008管理平臺管理表數(shù)據(jù)的方法是：啟動SQLServer2008管理平臺，在“對象資源管理器”中逐級展開其中的樹形目錄，在GoodsDB數(shù)據(jù)庫下右擊要數(shù)據(jù)管理的具體表格（如Product表、Account表），從彈出的快捷菜單中選擇“打開表（O）”命令，而后即可進行表數(shù)據(jù)的添加、更新與刪除。后文中將會具體介紹使用Transact-SQL語言進行數(shù)據(jù)庫管理的操作。6任務(wù)1插入、修改、刪除表數(shù)據(jù)四、知識要點（一）數(shù)據(jù)插入Transact-SQL語言中主要使用INSERT語句向表或視圖中插入新的數(shù)據(jù)行。用于查詢的SELECT語句也可用于向表中插入數(shù)據(jù)。表數(shù)據(jù)的插入的語法格式如下：INSERT[INTO]{table_name|view_name}{(column_name[,...n]){VALUES({DEFAULT|NULL|expression}[,...n])|derived_table}}7任務(wù)1插入、修改、刪除表數(shù)據(jù)四、知識要點（一）數(shù)據(jù)插入命令說明：（1）INSERT[INTO]：指定要向表中插入數(shù)據(jù)，INTO可以省略。（2）{table_name|view_name}：表示要插入數(shù)據(jù)的表或視圖的名稱。（3）(column_name[,...n])：表示要插入數(shù)據(jù)的表或視圖的列名清單。（4）VALUES：該關(guān)鍵字指定要插入數(shù)據(jù)的列表清單。（5）{DEFAULT|NULL|expression}[,...n]：該數(shù)據(jù)列表清單中包括默認值、空值和表達式的數(shù)量、次序和數(shù)據(jù)類型必須與列清單中指定列的定義相匹配。如果在VALUES清單中按表中定義的列的順序提供每列的值，則可以省略列清單。（6）derived_table：這是一個導(dǎo)入表中數(shù)據(jù)的SELECT子句。通常INSERT命令一次只能在表中插入一行數(shù)據(jù)，但可以采用SELECT子句替代VALUES子句，將一張表中的多行數(shù)據(jù)導(dǎo)入到要插入數(shù)據(jù)的表中。8任務(wù)1插入、修改、刪除表數(shù)據(jù)四、知識要點（二）數(shù)據(jù)更新Transact-SQL語言中的UPDATE語句用于更新修改表中的數(shù)據(jù)，該語句可以用于一次修改表中一行或多行數(shù)據(jù)，其語法格式如下：UPDATEtable_name SET{column_name={expression|DEFAULT|NULL}|@variable=expression|@variable=column_name=expression}[,...n][FROM{<table_source>}[,...n]][WHERE<search_condition>]9任務(wù)1插入、修改、刪除表數(shù)據(jù)四、知識要點命令說明：（1）UPDATEtable_name：指定需要更新的表的名稱為table_name所表示的名稱。（2）SET：該子句表示對指定的列或變量名稱賦予新值。（3）column_name={expression|DEFAULT|NULL}：將變量、字符、表達式的值，或默認值，或空值替換column_name所指定列的現(xiàn)有值。不能修改標識列數(shù)據(jù)。（4）@variable=expression：指定將變量、字符、表達式的值賦予一個已經(jīng)聲明的局部變量。（局部變量見項目5“T-SQL語言編程”任務(wù)）（5）@variable=column_name=expression：指定將變量、字符、表達式的值同時賦予一列和一個變量。（6）FROM{<table_source>：表示要依據(jù)一個表（可以是本表或其他表）中的數(shù)據(jù)進行更新操作。（7）WHERE<search_condition>：指定修改數(shù)據(jù)的條件，如果省略此選項，則修改每一行中的該列數(shù)據(jù)。有WHERE子句時，僅修改符合WHERE條件的行。（8）在一個UPDATE中，可以一次修改多列的數(shù)據(jù)，只要在SET后面寫入多個列名及其表達式，每個用逗號隔開。（9）UPDATE不能修改具有IDENTITY屬性的列值。10任務(wù)1插入、修改、刪除表數(shù)據(jù)四、知識要點（三）數(shù)據(jù)刪除Transact-SQL語言中刪除表中數(shù)據(jù)的方法有兩種，在指定的表或視圖中刪除滿足給定條件的數(shù)據(jù)可以使用DELETE語句；如果要清除表中全部數(shù)據(jù)，則還可以使用TRUNCATETABLE語句。DELETE語句的語法如下：DELETE[FROM]{table_name|view_name}[FROM{<table_source>}][WHERE<search_condition>]命令說明：（1）DELETE語句中的語法項目含義與UPDATE語句相同。（2）WHERE子句給出刪除數(shù)據(jù)所必須滿足的條件，省略WHERE子句時將刪除所有數(shù)據(jù)。11任務(wù)1插入、修改、刪除表數(shù)據(jù)四、知識要點（3）刪除數(shù)據(jù)TRUNCATETABLE語句的語法格式如下：TRUNCATETABLE[{database_name.[schema_name].|schema_name.}]table_name[;]其中，table_name表示要刪除數(shù)據(jù)的表的名稱。TRUNCATETABLE語句的語法說明如下：（1）TRUNCATETABLE語句可刪除指定表中的所有數(shù)據(jù)行，表結(jié)構(gòu)及其所有索引繼續(xù)保留，為該表所定義的約束、規(guī)則、默認值和觸發(fā)器仍然有效。（2）與DELETE語句相比，TRUNCATETABLE語句刪除速度更快。因為DELETE語句在每刪除一行時都要把刪除操作記錄到日志中，而TRUNCATETABLE語句則是通過釋放表數(shù)據(jù)頁面的方法來刪除表中數(shù)據(jù)，它只在釋放頁面后做一次事務(wù)日志。（3）使用TRUNCATETABLE語句刪除數(shù)據(jù)后，這些行時不可恢復(fù)的，而DELETE操作則可回滾，能夠恢復(fù)原來數(shù)據(jù)。（4）TRUNCATETABLE語句不能操作日志，它不能激活觸發(fā)器，所有TRUNCATETABLE語句不能刪除一個被其他表通過FOREIGNKEY約束所參照的表。

12任務(wù)1插入、修改、刪除表數(shù)據(jù)五、任務(wù)實施1.基本格式插入實例要求：使用INSERT語句向Product表添加兩條記錄。程序代碼如下：insertintoproduct(ProCode,CatCode,ProName,Price,StockNum,Description)Values('A0103','A01','金士頓16G',200,100,'略')Insertintoproduct(ProCode,CatCode,ProName,Price,StockNum,Description)Values('A0208','A02','品勝充電器',154,100,'略')13任務(wù)1插入、修改、刪除表數(shù)據(jù)五、任務(wù)實施2.省略INSERT子句列表要求：使用省略INSERT子句列表方法插入兩條數(shù)據(jù)。從表數(shù)據(jù)的插入的語法格式中可以看出，INSERTINTO子句后可以不帶列名，但若不帶指定列的列表，輸入值的順序必須與表或者視圖中的列順序一致，數(shù)據(jù)類型、數(shù)據(jù)精度和小數(shù)位數(shù)必須與列的列表中對應(yīng)列一致。程序代碼如下，結(jié)果如圖4.1.4所示：InsertintoproductValues('A0104','A01','樂風(fēng)數(shù)據(jù)線',24,56,'略')Insertintoproduct(ProCode,CatCode,ProName,Price,StockNum,Description)Values('A0308','A03','樂風(fēng)手機音響',98,200,'略')14任務(wù)1插入、修改、刪除表數(shù)據(jù)五、任務(wù)實施3.使用INSERT…SELECT語句要求：先建立Product備用表格Producr1，然后使用INSERT…SELECT語句的方法插入數(shù)。據(jù)INSERT...SELECT語句利用SELECT子句的結(jié)果集與INSERT語句結(jié)合使用，可以將結(jié)果集數(shù)據(jù)插入到制定的表中，該方法可以將一條或多條數(shù)據(jù)插入表中，也可用于將一個或多個其他表或者視圖的值添加到表中。（1）新建Product備用表格Product1CREATETABLEProduct1(ProCodechar(5)NOTNULL,

CatCodechar(3)NOTNULL,

ProNamevarchar(30)NOTNULL, Pricedecimal(10,2)NULL,

StockNum

intNULL, Descriptionvarchar(200)NULL,CONSTRAINTPK_Product1PRIMARYKEY(procode))ON[PRIMARY]15任務(wù)1插入、修改、刪除表數(shù)據(jù)五、任務(wù)實施3.使用INSERT…SELECT語句要求：先建立Product備用表格Producr1，然后使用INSERT…SELECT語句的方法插入數(shù)。據(jù)INSERT...SELECT語句利用SELECT子句的結(jié)果集與INSERT語句結(jié)合使用，可以將結(jié)果集數(shù)據(jù)插入到制定的表中，該方法可以將一條或多條數(shù)據(jù)插入表中，也可用于將一個或多個其他表或者視圖的值添加到表中。（2）在建立備用表格的基礎(chǔ)上利用INSERT…SELECT語句插入數(shù)據(jù)Insertintoproduct1(ProCode,CatCode,ProName,Price,StockNum,Description)SelectProCode,CatCode,ProName,Price,StockNum,DescriptionFromproductwhereprice<10016任務(wù)1插入、修改、刪除表數(shù)據(jù)五、任務(wù)實施4.使用SELECT…INTO語句要求：使用SELECT…INTO語句完成新建“飛毛腿商品”表格的同時插入數(shù)據(jù)。使用SELECT…INTO語句可以將SELECT查詢的結(jié)果集數(shù)據(jù)插入到指定的新表中。程序代碼如下，結(jié)果如下圖4.1.7所示。SelectProCode,CatCode,ProName,StockNum,DescriptionInto飛毛腿商品

FromproductWhereProNamelike’*飛毛腿*’17任務(wù)1插入、修改、刪除表數(shù)據(jù)五、任務(wù)實施5.使用UPDATE…SET語句更新數(shù)據(jù)要求：將Product表格中所有Catcode為“A01”的商品的Price降低5元。程序代碼如下：UpdateproductSetprice=price-5WhereCatcode='A01'18任務(wù)1插入、修改、刪除表數(shù)據(jù)五、任務(wù)實施6.使用DELETE語句刪除數(shù)據(jù)要求：使用DELETE語句刪除Product表格中價格小于100的商品。程序代碼如下：deletefromproductwhereprice<10019任務(wù)1插入、修改、刪除表數(shù)據(jù)五、任務(wù)實施7.使用TRUNCATETABLE語句刪除數(shù)據(jù)要求：使用TRUNCATETABLE語句刪除Product1表格中的剩余數(shù)據(jù)。程序代碼如下：

TruncatetableProduct120任務(wù)1插入、修改、刪除表數(shù)據(jù)六、課堂互動1.向數(shù)據(jù)表中添加數(shù)據(jù)的方法有哪幾種？2.數(shù)據(jù)更新操作能不能同時修改一條數(shù)據(jù)的多個值？21任務(wù)2簡單查詢一、任務(wù)背景數(shù)據(jù)查詢能為我們做些什么？在數(shù)據(jù)庫管理工作中，數(shù)據(jù)查詢是最基本最常態(tài)化的工作。22任務(wù)2簡單查詢二、任務(wù)需求1.查詢Product表中的所有記錄；2.查詢Product表中CatCode，去掉重復(fù)值；3.查詢Product表格中的ProCode、CatCode和ProName列，返回前5條記錄；4.使用列的別名，查詢Product表格中ProCode（別名為產(chǎn)品編號）、CatCode（別名為類別編號）和ProName（別名為產(chǎn)品名稱）；5.查詢Product表格中庫存各種商品及價值總金額；6.查詢Product表格中Price大于100的產(chǎn)品ProName和ProCode；7.查詢Account中Birthday在1990年以后（不包含1990年）的記錄；8.查詢Product表格中所有“飛毛腿”品牌產(chǎn)品的記錄；9.查詢Account表格中nick姓“王”或“李”的人員的記錄；10.查詢Account表格中Birthday在1989年的記錄的AccCode、Nick、Email、Address；11.查詢Product表格CatCode為A03、A06、B02的產(chǎn)品記錄；12.查詢Product表格中Description為空的記錄；13.將Product表格中的CatCode為A02的產(chǎn)品數(shù)據(jù)按照Price由高到低的順序輸出。23任務(wù)2簡單查詢?nèi)⑷蝿?wù)分析本任務(wù)的主要作用是進行數(shù)據(jù)庫查詢，數(shù)據(jù)庫查詢速度的提高是數(shù)據(jù)庫技術(shù)發(fā)展的重要標志之一，在數(shù)據(jù)庫發(fā)展過程中，數(shù)據(jù)查詢曾經(jīng)是一個非常困難的事情，直到使用了Transact-SQL語言之后，數(shù)據(jù)庫的查詢才變得相對簡單。在數(shù)據(jù)庫應(yīng)用中，數(shù)據(jù)查詢是通過SELECT語句來完成的，SELECT語法提供了強大的操作能力，對于數(shù)據(jù)庫開發(fā)人員來說，隨時可以通過查詢來完成。。24任務(wù)2簡單查詢四、知識要點SELECT語句結(jié)構(gòu)SELECT語句能夠從數(shù)據(jù)庫中查詢出符合用戶需求的數(shù)據(jù)，并將結(jié)果以表格的形式返回，是Transact-SQL語言中使用最頻繁的語句之一。它功能能強大，所以也有較多的子句，包含主要子句的基本語法格式如下：SELECT[ALL|DISTINCT][TOPn[PERCENT]]select_list[INTOnew_table]FROMtable_source

[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]][COMPUTE{{AVG|COUNT|MAX|MIN|SUM}(列名1)}[,…n][BY列名1[,…n]]25任務(wù)2簡單查詢四、知識要點命令說明：（1）ALL|DISTINCT：DISTINCT關(guān)鍵字用于禁止在查詢結(jié)果數(shù)據(jù)集中顯示重復(fù)的行。ALL關(guān)鍵字允許在查詢結(jié)果數(shù)據(jù)集中顯示查詢到的全部行。默認值為ALL關(guān)鍵字。（2）TOPn[PERCENT]：TOPn用于在查詢結(jié)果數(shù)據(jù)集中顯示查詢到的前n行數(shù)據(jù)（n為自然數(shù)）；TOPnPERCENT用于在查詢結(jié)果數(shù)據(jù)集中顯示查詢到的前百分之n行的數(shù)據(jù)。（3）select_list：查詢所涉及到的列清單。（4）INTOnew_table：將查詢結(jié)果集保存到新表中。（5）FROMtable_source：查詢所涉及到的源表，即從中查詢數(shù)據(jù)的表。（6）WHEREsearch_condition：查詢條件。（7）GROUPBYgroup_by_expression：查詢的分組匯總表達式。（8）HAVINGsearch_condition：分組匯總結(jié)果的篩選條件。（9）ORDERBYorder_expression[ASC|DESC]：查詢結(jié)果集的排序準則。ASC表示查詢結(jié)果升序排列，DESC表示降序排列。（10）[COMPUTE{{AVG|COUNT|MAX|MIN|SUM}(列名1)}[,…n][BY列名1[,…n]]：用于對列進行聚合函數(shù)計算并生成匯總值，匯總的結(jié)果以附加行的形式出現(xiàn)。26任務(wù)2簡單查詢四、知識要點WHERE子句WHERE子句是對表中的行進行選擇查詢，即通過在SELECT語句中使用WHERE子句可以從數(shù)據(jù)表中過濾出符合WHERE子句指定選擇條件的記錄，從而實現(xiàn)行的查詢。WHERE子句必須緊跟在FROM子句之后，其基本格式為：WHERE<search_condition>27任務(wù)2簡單查詢四、知識要點說明：search_condition為查詢條件，查詢條件是一個邏輯表達式，其中可以包含的運算符如下表4-2-1所示。28任務(wù)2簡單查詢五、任務(wù)實施1.使用通配符“*”，返回所有列值要求：查詢Product表中的所有記錄。Select*FromProduct29任務(wù)2簡單查詢五、任務(wù)實施2.使用DISRINCT關(guān)鍵字消除重復(fù)記錄要求：查詢Product表中CatCode，去掉重復(fù)值。程序如和執(zhí)行如圖4.2.2所示。SelectdistinctcatcodeFromProduct30任務(wù)2簡單查詢五、任務(wù)實施3.使用TOPn指定返回查詢結(jié)果的前n行記錄要求：查詢Product表格中的ProCode、CatCode和ProName列，返回前5條記錄。程序及執(zhí)行如圖4.2.3所示。Selecttop5ProCode,CatCode,ProNameFromProduct另外，top關(guān)鍵字還可以有另外的一種用法，返回前百分之n行，用法為topnpercent，比如要返回結(jié)果的前50%，程序如下：Selecttop50percentProCode,CatCode,ProNameFromProduct31任務(wù)2簡單查詢五、任務(wù)實施4.使用列別名改變查詢結(jié)果中的列名要求：使用列的別名，查詢Product表格中ProCode（別名為產(chǎn)品編號）、CatCode（別名為類別編號）和ProName（別名為產(chǎn)品名稱）。SelectProCodeas產(chǎn)品編號,類別編號=CatCode,ProName

產(chǎn)品名稱FromProduct32任務(wù)2簡單查詢五、任務(wù)實施5.使用列表達式要求：查詢Product表格中庫存各種商品及價值總金額。在SELECT子句中可以使用算術(shù)運算符對數(shù)字型數(shù)據(jù)列進行加（+）、減（-）、乘（×）、除（÷）和取模（%）運算，構(gòu)造列表達式，獲取經(jīng)過計算的查詢結(jié)果。SelectProName

產(chǎn)品名稱,Price*StockNumas總金額FromProduct33任務(wù)2簡單查詢五、任務(wù)實施6.比較表達式作為查詢條件要求：查詢Product表格中Price大于100的產(chǎn)品ProName和ProCode。使用比較表達式作為查詢條件的一般格式是：expression比較運算符expressionselectProName,procodefromProductwherePrice>10034任務(wù)2簡單查詢五、任務(wù)實施7.邏輯表達式作為查詢條件要求：查詢Account中Birthday在1990年以后（不包含1990年）的記錄。使用邏輯表達式作為查詢條件的一般格式是：Expressionandexpression或expressionorexpression或notexpression程序及執(zhí)行結(jié)果如圖4.2.7所示。select*fromAccountwhereBirthday>'1990-12-31'35任務(wù)2簡單查詢五、任務(wù)實施8.模糊查詢要求：查詢Product表格中所有“飛毛腿”品牌產(chǎn)品的記錄。模糊查詢使用到謂詞LIKE，用于指出一個字符串是否與指定的字符串相匹配，返回邏輯值TRUE或FALSE。語法的格式如下：String_expression[NOT]LIKEstring_expressionselect*fromProductwhereProNamelike'%飛毛腿%'36任務(wù)2簡單查詢五、任務(wù)實施8.模糊查詢通配符列表及用法示例如下表4-2-237任務(wù)2簡單查詢五、任務(wù)實施9.模糊查詢——單個字符匹配要求：查詢Account表格中nick姓“王”或“李”的人員的記錄。程序和執(zhí)行結(jié)果如圖4.2.9所示。select*fromAccountwhereNicklike'[王李]_'38任務(wù)2簡單查詢五、任務(wù)實施10.范圍比較要求：查詢Account表格中Birthday在1989年的記錄的AccCode、Nick、Email、Address。用于范圍比較的關(guān)鍵字有兩個：BETWEEN和IN。使用BETWEEN關(guān)鍵字可以方便地限制查詢數(shù)據(jù)的范圍；使用IN關(guān)鍵字可以指定一個值表，值表中列出所有可能的值，當與值表中的任一個值匹配時，即返回TRUE,否則返回FALSE。BETWEEN關(guān)鍵字的語法格式：Expression[NOT] BETWEENexpression1ANDexpression2selectAccCode,Nick,Email,AddressfromAccountwhereBirthdaybetween'1989-1-1'and'1989-12-31'39任務(wù)2簡單查詢五、任務(wù)實施11.范圍比較——In運算符要求：查詢Product表格CatCode為A03、A06、B02的產(chǎn)品記錄。程序和執(zhí)行結(jié)果如圖4.2.11所示。select*fromProductwhereCatCodein('A03','A06','B02')go40任務(wù)2簡單查詢五、任務(wù)實施12.空值比較要求：查詢Product表格中Description為空的記錄。當需要判定一個表達式的值是否為空值時，使用ISNULL關(guān)鍵字，格式為：ExpressionIS[NOT]NULL說明：當不使用NOT時，若表達式expression為空值，返回TURE，否則返回FALSE；當使用NOT時，結(jié)果剛好相反。程序及執(zhí)行結(jié)果如圖4.2.12所示。select*fromProductwhereDescriptionisnull41任務(wù)2簡單查詢五、任務(wù)實施13.ORDERBY子句排序要求：將Product表格中的CatCode為A02的產(chǎn)品數(shù)據(jù)按照Price由高到低的順序輸出。在數(shù)據(jù)庫應(yīng)用中，經(jīng)常要對查詢的結(jié)果進行排序輸出，比如將商品按照價格的由高到低排序輸出。在SELECT語句中