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

12任務1插入、修改、刪除表數據五、任務實施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任務1插入、修改、刪除表數據五、任務實施2.省略INSERT子句列表要求：使用省略INSERT子句列表方法插入兩條數據。從表數據的插入的語法格式中可以看出，INSERTINTO子句后可以不帶列名，但若不帶指定列的列表，輸入值的順序必須與表或者視圖中的列順序一致，數據類型、數據精度和小數位數必須與列的列表中對應列一致。程序代碼如下，結果如圖4.1.4所示：InsertintoproductValues('A0104','A01','樂風數據線',24,56,'略')Insertintoproduct(ProCode,CatCode,ProName,Price,StockNum,Description)Values('A0308','A03','樂風手機音響',98,200,'略')14任務1插入、修改、刪除表數據五、任務實施3.使用INSERT…SELECT語句要求：先建立Product備用表格Producr1，然后使用INSERT…SELECT語句的方法插入數。據INSERT...SELECT語句利用SELECT子句的結果集與INSERT語句結合使用，可以將結果集數據插入到制定的表中，該方法可以將一條或多條數據插入表中，也可用于將一個或多個其他表或者視圖的值添加到表中。（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任務1插入、修改、刪除表數據五、任務實施3.使用INSERT…SELECT語句要求：先建立Product備用表格Producr1，然后使用INSERT…SELECT語句的方法插入數。據INSERT...SELECT語句利用SELECT子句的結果集與INSERT語句結合使用，可以將結果集數據插入到制定的表中，該方法可以將一條或多條數據插入表中，也可用于將一個或多個其他表或者視圖的值添加到表中。（2）在建立備用表格的基礎上利用INSERT…SELECT語句插入數據Insertintoproduct1(ProCode,CatCode,ProName,Price,StockNum,Description)SelectProCode,CatCode,ProName,Price,StockNum,DescriptionFromproductwhereprice<10016任務1插入、修改、刪除表數據五、任務實施4.使用SELECT…INTO語句要求：使用SELECT…INTO語句完成新建“飛毛腿商品”表格的同時插入數據。使用SELECT…INTO語句可以將SELECT查詢的結果集數據插入到指定的新表中。程序代碼如下，結果如下圖4.1.7所示。SelectProCode,CatCode,ProName,StockNum,DescriptionInto飛毛腿商品

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

TruncatetableProduct120任務1插入、修改、刪除表數據六、課堂互動1.向數據表中添加數據的方法有哪幾種？2.數據更新操作能不能同時修改一條數據的多個值？21任務2簡單查詢一、任務背景數據查詢能為我們做些什么？在數據庫管理工作中，數據查詢是最基本最常態化的工作。22任務2簡單查詢二、任務需求1.查詢Product表中的所有記錄；2.查詢Product表中CatCode，去掉重復值；3.查詢Product表格中的ProCode、CatCode和ProName列，返回前5條記錄；4.使用列的別名，查詢Product表格中ProCode（別名為產品編號）、CatCode（別名為類別編號）和ProName（別名為產品名稱）；5.查詢Product表格中庫存各種商品及價值總金額；6.查詢Product表格中Price大于100的產品ProName和ProCode；7.查詢Account中Birthday在1990年以后（不包含1990年）的記錄；8.查詢Product表格中所有“飛毛腿”品牌產品的記錄；9.查詢Account表格中nick姓“王”或“李”的人員的記錄；10.查詢Account表格中Birthday在1989年的記錄的AccCode、Nick、Email、Address；11.查詢Product表格CatCode為A03、A06、B02的產品記錄；12.查詢Product表格中Description為空的記錄；13.將Product表格中的CatCode為A02的產品數據按照Price由高到低的順序輸出。23任務2簡單查詢三、任務分析本任務的主要作用是進行數據庫查詢，數據庫查詢速度的提高是數據庫技術發展的重要標志之一，在數據庫發展過程中，數據查詢曾經是一個非常困難的事情，直到使用了Transact-SQL語言之后，數據庫的查詢才變得相對簡單。在數據庫應用中，數據查詢是通過SELECT語句來完成的，SELECT語法提供了強大的操作能力，對于數據庫開發人員來說，隨時可以通過查詢來完成。。24任務2簡單查詢四、知識要點SELECT語句結構SELECT語句能夠從數據庫中查詢出符合用戶需求的數據，并將結果以表格的形式返回，是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任務2簡單查詢四、知識要點命令說明：（1）ALL|DISTINCT：DISTINCT關鍵字用于禁止在查詢結果數據集中顯示重復的行。ALL關鍵字允許在查詢結果數據集中顯示查詢到的全部行。默認值為ALL關鍵字。（2）TOPn[PERCENT]：TOPn用于在查詢結果數據集中顯示查詢到的前n行數據（n為自然數）；TOPnPERCENT用于在查詢結果數據集中顯示查詢到的前百分之n行的數據。（3）select_list：查詢所涉及到的列清單。（4）INTOnew_table：將查詢結果集保存到新表中。（5）FROMtable_source：查詢所涉及到的源表，即從中查詢數據的表。（6）WHEREsearch_condition：查詢條件。（7）GROUPBYgroup_by_expression：查詢的分組匯總表達式。（8）HAVINGsearch_condition：分組匯總結果的篩選條件。（9）ORDERBYorder_expression[ASC|DESC]：查詢結果集的排序準則。ASC表示查詢結果升序排列，DESC表示降序排列。（10）[COMPUTE{{AVG|COUNT|MAX|MIN|SUM}(列名1)}[,…n][BY列名1[,…n]]：用于對列進行聚合函數計算并生成匯總值，匯總的結果以附加行的形式出現。26任務2簡單查詢四、知識要點WHERE子句WHERE子句是對表中的行進行選擇查詢，即通過在SELECT語句中使用WHERE子句可以從數據表中過濾出符合WHERE子句指定選擇條件的記錄，從而實現行的查詢。WHERE子句必須緊跟在FROM子句之后，其基本格式為：WHERE<search_condition>27任務2簡單查詢四、知識要點說明：search_condition為查詢條件，查詢條件是一個邏輯表達式，其中可以包含的運算符如下表4-2-1所示。28任務2簡單查詢五、任務實施1.使用通配符“*”，返回所有列值要求：查詢Product表中的所有記錄。Select*FromProduct29任務2簡單查詢五、任務實施2.使用DISRINCT關鍵字消除重復記錄要求：查詢Product表中CatCode，去掉重復值。程序如和執行如圖4.2.2所示。SelectdistinctcatcodeFromProduct30任務2簡單查詢五、任務實施3.使用TOPn指定返回查詢結果的前n行記錄要求：查詢Product表格中的ProCode、CatCode和ProName列，返回前5條記錄。程序及執行如圖4.2.3所示。Selecttop5ProCode,CatCode,ProNameFromProduct另外，top關鍵字還可以有另外的一種用法，返回前百分之n行，用法為topnpercent，比如要返回結果的前50%，程序如下：Selecttop50percentProCode,CatCode,ProNameFromProduct31任務2簡單查詢五、任務實施4.使用列別名改變查詢結果中的列名要求：使用列的別名，查詢Product表格中ProCode（別名為產品編號）、CatCode（別名為類別編號）和ProName（別名為產品名稱）。SelectProCodeas產品編號,類別編號=CatCode,ProName

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

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