第7章-操縱數(shù)據(jù)課件

第7章操縱數(shù)據(jù)SQLServer2005基礎(chǔ)教程清華大學(xué)出版社教學(xué)目標(biāo)教學(xué)過程7/22/20231教學(xué)目標(biāo)理解操縱數(shù)據(jù)需要解決的問題掌握使用INSERT語句插入數(shù)據(jù)掌握使用UPDATE語句更新數(shù)據(jù)掌握使用DELETE語句刪除數(shù)據(jù)掌握使用SELECT語句檢索數(shù)據(jù)理解分組、子查詢、連接、集合運(yùn)算、CTE等檢索特點(diǎn)理解數(shù)據(jù)加密的方式和特點(diǎn)7/22/20232教學(xué)過程7.1概述7.2插入數(shù)據(jù)7.3更新數(shù)據(jù)7.4刪除數(shù)據(jù)7.5檢索操作概述7.6使用SELECT子句檢索數(shù)據(jù)7.7排序7.8使用WHERE子句選擇數(shù)據(jù)7.9聚合技術(shù)7.10分組技術(shù)7.11連接技術(shù)7.12子查詢技術(shù)7.13集合運(yùn)算技術(shù)7.14公用表表達(dá)式7.15PIVOT和UNPIVOT7.16加密表中數(shù)據(jù)7/22/202337.1概述表創(chuàng)建之后，表只是一個空表。如何向表中添加數(shù)據(jù)呢？如果表中已有數(shù)據(jù)了，但是數(shù)據(jù)不合適或不正確，那么如何更新這些數(shù)據(jù)呢？如果表中的數(shù)據(jù)不再需要了，那么如何刪除這些過時的數(shù)據(jù)呢？如何按照用戶需要，將表中的數(shù)據(jù)檢索出來呢？這些問題都是數(shù)據(jù)操縱問題。用戶可以使用INSERT、UPDATE、DELETE、SELECT等語句來解決這些數(shù)據(jù)操縱問題。7/22/202347.2插入數(shù)據(jù)表創(chuàng)建之后往往只是一個空表，因此向表中插入數(shù)據(jù)是在表結(jié)構(gòu)創(chuàng)建之后，首先需要執(zhí)行的操作。向表中插入數(shù)據(jù)，應(yīng)該使用INSERT語句。該語句包括了兩個子句，即INSERT子句和VALUES子句。INSERT子句指定要插入數(shù)據(jù)的表名或視圖名稱，它可以包含表或視圖中列的列表。VALUES子句指定將要插入的數(shù)據(jù)。一般地，使用INSERT語句一次只能插入一行數(shù)據(jù)。INSERT語句的基本語法形式如下所示：INSERTINTOtable_or_view_name(column_list)VALUES(expression)7/22/202357.3更新數(shù)據(jù)可以使用UPDATE語句更新表中已經(jīng)存在的數(shù)據(jù)。UPDATE語句既可以一次更新一行數(shù)據(jù)，也可以一次更新許多行，甚至可以一次更新表中的全部數(shù)據(jù)行。在UPDATE語句中，使用WHERE子句指定要更新的數(shù)據(jù)行滿足的基本條件，使用SET子句給出新的數(shù)據(jù)。新數(shù)據(jù)既可以是常量，也可以是指定的表達(dá)式。UPDATE語句的基本語法形式如下：UPDATEtable_or_view_nameSETcolumn_name=expression,…WHEREsearch_condition7/22/202367.4刪除數(shù)據(jù)當(dāng)表中的數(shù)據(jù)不再需要時，可以刪除。一般情況下，使用DELETE語句刪除數(shù)據(jù)。DELETE語句可以從一個表中刪除一行或多行數(shù)據(jù)。刪除數(shù)據(jù)的DELEE語句的基本語法形式如下：DELETEFROMtable_or_nameWHEREsearch_condition7/22/202377.5檢索操作概述如果我們希望檢索表中數(shù)據(jù)，可以使用SELECT語句。在SELECT語句中，有三個基本的組成部分：SELECT子句、FROM子句和WHERE子句。SELECT子句用于指定將要檢索的列名稱，F(xiàn)ROM子句指定將要檢索的對象，WHERE子句則用于指定數(shù)據(jù)應(yīng)該滿足的條件。7/22/202387.6使用SELECT子句檢索數(shù)據(jù)在SELECT語句中，可以在SELECT子句中選擇指定的數(shù)據(jù)列、使用文字串、改變列標(biāo)題、執(zhí)行數(shù)據(jù)運(yùn)算、使用ALL關(guān)鍵字、使用DISTINCT關(guān)鍵字等。7/22/20239選擇指定的數(shù)據(jù)列選擇指定的數(shù)據(jù)列是指可以在SELECT子句中指定將要檢索的列名稱。選擇指定的列名稱要注意幾點(diǎn)，第一，這些列名稱應(yīng)該與表中定義的列名稱一致，否則就可能出錯或者得到意想不到的結(jié)果；第二，列名稱之間的順序既可以與表中定義的列順序相同，也可以不相同；第三，SELECT語句的檢索結(jié)果只是影響數(shù)據(jù)的顯示，對表中數(shù)據(jù)的存儲沒有任何的影響。7/22/202310使用文字串通常，直接閱讀SELECT語句的檢索結(jié)果，是一件頭疼的事情，因?yàn)轱@示出來的數(shù)據(jù)，只是一些不連貫的、閱讀性不強(qiáng)的信息。為了提高SELECT語句檢索結(jié)果的可讀性，可以通過在SELECT關(guān)鍵字后面增加文字串。通常情況下，使用單引號將文字串引起來。7/22/202311改變列標(biāo)題在默認(rèn)情況下，在數(shù)據(jù)檢索結(jié)果中所顯示出來的列標(biāo)題就是在定義表時使用的列名稱。但是，在檢索過程中可以根據(jù)用戶的需要改變顯示的列標(biāo)題。實(shí)際上，改變列標(biāo)題也就是為指定的列定義一個別名。改變列標(biāo)題有兩種方法，一種方法是使用等號(=)，另一種方法是使用AS關(guān)鍵字。7/22/202312數(shù)據(jù)運(yùn)算數(shù)據(jù)運(yùn)算就是指對檢索的數(shù)據(jù)進(jìn)行各種運(yùn)算。也就是說，可以在SELECT關(guān)鍵字后面列出的列項(xiàng)中使用各種運(yùn)算符和函數(shù)。這些運(yùn)算符和函數(shù)包括算術(shù)運(yùn)算符、數(shù)學(xué)函數(shù)、字符串函數(shù)、日期和時間函數(shù)、系統(tǒng)函數(shù)等。7/22/202313使用ALL和DISTINCT關(guān)鍵字在SELECT語句中，可以在SELECT子句中通過使用ALL或DISTINCT關(guān)鍵字控制查詢結(jié)果集的顯示樣式。ALL關(guān)鍵字表示檢索所有的數(shù)據(jù)，包括重復(fù)的數(shù)據(jù)行。DISTINCT關(guān)鍵字表示僅僅顯示那些不重復(fù)的數(shù)據(jù)行，重復(fù)的數(shù)據(jù)行只是顯示一次。由于ALL關(guān)鍵字是默認(rèn)值，所以當(dāng)沒有顯式使用ALL或DISTINCT關(guān)鍵字時，隱含著使用ALL關(guān)鍵字。7/22/2023147.7排序在使用SELECT語句時，排序是一種常見的操作。排序是指按照指定的列或其他表達(dá)式對結(jié)果集進(jìn)行排列順序的方式。SELECT語句中的ORDERBY子句負(fù)責(zé)完成排序操作。在排序時，既可以按照升序排列，也可以按照降序排列。關(guān)鍵字ASC表示升序，DESC表示降序，默認(rèn)情況下是升序。7/22/2023157.8使用WHERE子句選擇數(shù)據(jù)在SELECT語句中，WHERE子句指定將要搜索的數(shù)據(jù)行的條件。也就是說，只有滿足WHERE子句條件的數(shù)據(jù)行才會出現(xiàn)在結(jié)果集中。這些搜索條件可以分為簡單搜索條件模糊搜索條件復(fù)合搜索條件7/22/202316簡單搜索條件在WHERE子句中，簡單搜索條件是指使用比較運(yùn)算符、范圍、列表、合并以及取反等運(yùn)算方式形成的搜索條件。7/22/202317模糊搜索條件在檢索字符數(shù)據(jù)時，通常提供的檢索條件是不十分準(zhǔn)確的，例如這種搜索條件僅僅是包含、類似某種樣式的字符。在WHERE子句中，可以使用LIKE關(guān)鍵字實(shí)現(xiàn)這種靈活的模糊搜索條件。LIKE關(guān)鍵字用于檢索與特定字符串匹配的字符數(shù)據(jù)。LIKE關(guān)鍵字后面可以跟一個列值的一部分而不是一個完整的列值，從而形成LIKE子句。LIKE子句的語法形式如下：match_expression[NOT]LIKEpattern[ESCAPEescape_character]7/22/202318復(fù)合搜索條件在WHERE子句中可以使用邏輯運(yùn)算符把若干個搜索條件合并起來，組成復(fù)雜的復(fù)合搜索條件。這些邏輯運(yùn)算符包括AND，OR和NOT。7/22/2023197.9聚合技術(shù)聚合技術(shù)是指對一組數(shù)據(jù)進(jìn)行聚合運(yùn)算得到聚合值的過程。在聚合運(yùn)算中主要是使用聚合函數(shù)。在MicrosoftSQLServer2005系統(tǒng)中，一般情況下，可以在三個地方使用聚合函數(shù)，即SELECT子句、COMPUTE子句和HAVING子句。本節(jié)主要講述如何在SELECT子句和COMPUTE子句中使用聚合函數(shù)，有關(guān)HAVING子句使用聚合函數(shù)的內(nèi)容在下一節(jié)介紹7/22/202320SELECT子句中的聚合在SELECT子句中可以使用聚合函數(shù)進(jìn)行運(yùn)算，運(yùn)算結(jié)果作為新列出現(xiàn)在結(jié)果集中。在聚合運(yùn)算的表達(dá)式中，可以包括列名、常量以及由算術(shù)運(yùn)算符連接起來的函數(shù)。例如，在如圖7-34所示的示例中，在SELECT子句中使用聚合函數(shù)計(jì)算了Production.Product表中的數(shù)據(jù)量以及有關(guān)標(biāo)準(zhǔn)成本的最大值、最小值、平均值、標(biāo)準(zhǔn)偏差、方差等。注意COUNT函數(shù)的特點(diǎn)。7/22/202321COMPUTE子句中的聚合需要指出的是，當(dāng)在SELECT子句中出現(xiàn)聚合函數(shù)時，結(jié)果集中的數(shù)據(jù)全是聚合值，沒有明細(xì)值。這是使用SELECT子句計(jì)算聚合值的缺點(diǎn)。能否解決這種問題呢？能，解決問題的方法就是使用COMPUTE子句。7/22/2023227.10分組技術(shù)聚合函數(shù)只能產(chǎn)生一個單一的匯總數(shù)據(jù)，使用GROUPBY子句，則可以生成分組的匯總數(shù)據(jù)。GROUPBY子句把數(shù)據(jù)組織起來分成組。一般情況下，可以根據(jù)表中的某一列進(jìn)行分組，通過使用聚合函數(shù)，對每一個組可以產(chǎn)生聚合值。如果希望過濾某些分組，可以使用HAVING子句排。分組技術(shù)是指使用GROUPBY子句完成分組操作的技術(shù)。如果在GROUPBY子句中沒有使用CUBE或ROLLUP關(guān)鍵字，那么表示這種分組技術(shù)是普通分組技術(shù)。7/22/202323普通分組技術(shù)GROUPBY子句、HAVING子句和聚合函數(shù)一起完成對每一個組生成一行和一個匯總值。在使用GROUPBY子句和HAVING子句的過程中，要求考慮一些條件7/22/202324ROLLUP和CUBE關(guān)鍵字在GROUPBY子句中，可以使用ROLLUP或CUBE關(guān)鍵字獲得附加的分組數(shù)據(jù)，這些附加的分組數(shù)據(jù)是通過各組之間的組合得到的。使用ROLLUP關(guān)鍵字可以得到各組的單項(xiàng)組合，但是CUBE關(guān)鍵字可以得到各組之間的任意組合。在結(jié)果集中，通過組組合起來的組名稱是NULL，可以使用GROUPING函數(shù)來判斷該組是否是經(jīng)過組合得到的。實(shí)際上，使用CUBE關(guān)鍵字可以生成多維數(shù)據(jù)。下面，通過一個示例講述這兩個關(guān)鍵字的特點(diǎn)。7/22/2023257.11連接技術(shù)在設(shè)計(jì)表時，為了提高表的設(shè)計(jì)質(zhì)量，經(jīng)常把相關(guān)數(shù)據(jù)分散在不同的表中。但是，在使用數(shù)據(jù)時，需要把這些數(shù)據(jù)集中在一個查詢語句中。連接技術(shù)可以滿足這種客觀需求。在MicrosoftSQLServer2005系統(tǒng)中，這種連接操作又可以細(xì)分為交叉連接、內(nèi)連接、外連接等。下面分別研究這些連接技術(shù)。7/22/202326交叉連接交叉連接也被稱為笛卡爾乘積，返回兩個表的乘積。在檢索結(jié)果集中，包含了所連接的兩個表中所有行的全部組合。例如，如果對A表和B表執(zhí)行交叉連接，A表中有5行數(shù)據(jù)，B表中有12行數(shù)據(jù)，那么結(jié)果集中可以有60行數(shù)據(jù)。7/22/202327內(nèi)連接內(nèi)連接把兩個表中的數(shù)據(jù)連接生成一個第3個表，在這個第3個表中，僅包含那些滿足連接條件的數(shù)據(jù)行。在內(nèi)連接中，使用INNERJOIN連接運(yùn)算符，并且使用ON關(guān)鍵字指定連接條件。內(nèi)連接是一種常用的連接方式，如果在JOIN關(guān)鍵字前面沒有明確指定連接類型，那么默認(rèn)的連接類型是內(nèi)連接。7/22/202328外連接在外連接中，不僅包括那些滿足條件的數(shù)據(jù)，而且某些表不滿足條件的數(shù)據(jù)也會顯示在結(jié)果集中。也就是說，外連接只限制其中一個表的數(shù)據(jù)行，而不限制另外一個表中的數(shù)據(jù)。在MicrosoftSQLServer2005系統(tǒng)中，可以使用的3種外連接關(guān)鍵字，即LEFTOUTERJOIN、RIGHTOUTERJOIN和FULLOUTERJOIN。7/22/2023297.12子查詢技術(shù)SELECT語句可以嵌套在其他許多語句中，這些語句包括SELECT、INSERT、UPDATE或DELETE等，這些嵌套的SELECT語句被稱為子查詢。當(dāng)一個查詢依賴于另外一個查詢結(jié)果時，那么可以使用子查詢。在某些查詢中，查詢語句比較復(fù)雜不容易理解，因此為了把這些復(fù)雜的查詢語句分解成多個比較簡單的查詢語句形式時，可以使用子查詢方式。使用子查詢方式完成查詢操作的技術(shù)是子查詢技術(shù)。7/22/2023307.13集合運(yùn)算技術(shù)查詢語句的結(jié)果集往往是一個包含了多行數(shù)據(jù)集合。集合之間可以進(jìn)行并、差、交等運(yùn)算。在MicrosoftSQLServer2005系統(tǒng)中，兩個查詢語句之間也可以進(jìn)行集合運(yùn)算。其中，UNION運(yùn)算符表示并集運(yùn)算，EXCEPT運(yùn)算符從左查詢中返回右查詢中沒有找到的重復(fù)值，INTERSECT運(yùn)算符則返回左右兩個查詢語句都包含的所有非重復(fù)值。需要注意的是，在集合運(yùn)算時，所有查詢語句中的列的數(shù)量和順序必須相同，且數(shù)據(jù)類型必須兼容。7/22/2023317.14公用表表達(dá)式在MicrosoftSQLServer2005系統(tǒng)中，可以使用公用表表達(dá)式(commontableexpression，CTE)。CTE是定義在SELECT、INSERT、UPDAT