項(xiàng)目6 在教學(xué)評測系統(tǒng)數(shù)據(jù)庫表中操作數(shù)據(jù)

1、項(xiàng)目6 在教學(xué)評測系統(tǒng)數(shù)據(jù)庫中操作數(shù)據(jù)6.1 用戶需求與分析 教學(xué)測評系統(tǒng)中，經(jīng)常需要向系統(tǒng)中新增學(xué)生，這就需要用到Insert語句來完成。同時(shí)，也經(jīng)常需要對數(shù)據(jù)進(jìn)行匯總，例如，統(tǒng)計(jì)某一個(gè)教師一學(xué)期其所上班級學(xué)生對其評分的總分、平均分等，這就需要用到對單表、多表查詢的知識。6.2相關(guān)知識-插入數(shù)據(jù) 1.插入單個(gè)元組。 插入單個(gè)元組的INSERT語句的格式為： INSERT INTO (，) VALUES(，) 其功能是將新元組插入指定表中。其中新記錄屬性列1的值為常量1，屬性列2的值為常量2，。如果某些屬性列在INTO子句中沒有出現(xiàn)，則新記錄在這些列上將取空值。 應(yīng)當(dāng)了解關(guān)于默認(rèn)值和空值優(yōu)先的

2、常規(guī)INSERT行為。如果遺漏了列表和數(shù)值表中的一列，那么當(dāng)默認(rèn)值存在時(shí)，該列會使用默認(rèn)值。如果默認(rèn)值不存在，SQL Server會嘗試補(bǔ)上一個(gè)空值。如果列聲明了NOT NULL，嘗試空值會導(dǎo)致錯(cuò)誤。在數(shù)值表中，如果明確指定了NULL，即使默認(rèn)值存在，列仍會設(shè)置為NULL（假設(shè)它允許NULL）。當(dāng)在一個(gè)允許NULL且沒有聲明默認(rèn)值的列中使用DEFAULT占位符時(shí)，NULL會被插入到該列中。如果在一個(gè)聲明NOT NULL且沒有默認(rèn)值的列中指定NULL或DEFAULT，或者完全省略了該值，將導(dǎo)致錯(cuò)誤。6.2相關(guān)知識插入數(shù)據(jù) 2.插入子查詢結(jié)果子查詢不僅可以嵌套在SELECT語句中，用以構(gòu)造父查詢的

3、條件，也可以嵌套在INSERT語句中，用以生成要插入的數(shù)據(jù)。插入子查詢結(jié)果的INSERT語句的格式為INSERT INTO (，) 子查詢其功能是以批量插入。通過SELECT語句生成結(jié)果集，由INSERTSELECT語句把這些行添加到表中。通過INSERTSELECT語句把其他數(shù)據(jù)源的行添加到現(xiàn)有的表中。使用INSERTSELECT語句可比使用單行的INSERT語句效率要高得多6.2相關(guān)知識修改數(shù)據(jù) 1.更新指定元組更新數(shù)據(jù)的UPDATE語句的格式為：UPDATE table_name SET column_name = expression | DEFAULT | NULL WHERE 6.

4、2相關(guān)知識修改數(shù)據(jù) 2.更新所有元組可以不使用where子句指定元組，這時(shí)更新所有的元組6.2相關(guān)知識刪除數(shù)據(jù)用戶經(jīng)常需要刪除數(shù)據(jù)。如學(xué)生畢業(yè)了，需要將學(xué)生信息，學(xué)生課程信息等數(shù)據(jù)從數(shù)據(jù)庫中刪除。這就需要用到Delete語句。DELETE語句可以通過使用事務(wù)從表或視圖中刪除一行或多行。通過篩選目標(biāo)表或者使用子查詢指定SQL Server要刪除的行。Delete語句的語法結(jié)構(gòu)如下：DELETE FROM table_name WHERE 其中，table_name為要刪除數(shù)據(jù)的表名稱，search_condition為條件。6.2相關(guān)知識- SELECT語句建立數(shù)據(jù)庫的目的是為了查詢數(shù)據(jù)，因此，

5、可以說數(shù)據(jù)庫查詢是數(shù)據(jù)庫的核心操作。在數(shù)據(jù)庫中，數(shù)據(jù)查詢是通過SELECT語句來完成的。SELECT語句的功能是從數(shù)據(jù)庫中按用戶要求檢索數(shù)據(jù)，并將查詢結(jié)果以表格的形式返回，其一般格式如下：SELECT ALL|DISTINCT ，F(xiàn)ROM ，WHERE GROUP BYHAVINGORDER BYASC|DESC;6.2相關(guān)知識單表查詢 選擇表中的全部列或部分列，這類運(yùn)算又稱為投影。其變化方式主要表現(xiàn)在SELECT子句的上。語法格式如下：SELECT := *| table_name | view_name .*| column_name | expression ,.n 參數(shù)說明select

6、_list：是所要查詢的表的列的集合，多個(gè)列之間用逗號分開。*：通配符，返回所有對象的所有列。table_name | view_name.*：限制通配符*的作用范圍。凡是帶*的項(xiàng)，均返回其中所有的列。column_name：指定返回的列名。Expression：表達(dá)式可以為列名、常量、函數(shù)或它們的組合。6.2相關(guān)知識單表查詢 顯示全部列將表中的所有屬性列都選出來，可以有兩種方法。一種方法就是在SELECT關(guān)鍵字后面列出所有列名。如果列的顯示順序與其在基表中的順序相同，也可以簡單的將指定為*。6.2相關(guān)知識單表查詢 顯示經(jīng)過計(jì)算的列SELECT子句的不僅可以是表中的屬性列，也可以是有關(guān)系表達(dá)式

7、，即可以將查詢出來的屬性列經(jīng)過一定的計(jì)算后列出結(jié)果。SELECT StudentNO ,StudentName ,SubString(StudentNO,1,4) FROM TTS.dbo.TTS_Student6.2相關(guān)知識單表查詢 顯示字面值字面值包括字母、數(shù)字或在結(jié)果集中作為特殊值的符號。在選擇列表中使用字面值，使結(jié)果集更具可讀性。SELECT StudentNO As 學(xué)號 ,StudentName 姓名 ,SubString(StudentNO,1,4) As 年級 FROM TTS.dbo.TTS_Student6.2相關(guān)知識單表查詢 使用比較運(yùn)算符過濾數(shù)據(jù)使用比較運(yùn)算符可以讓表中

8、值與指定值或表達(dá)式作比較，也可以使用比較運(yùn)算符來做條件檢查。比較運(yùn)算符用來比較兼容數(shù)據(jù)類型的列或變量。操作符操作符說明說明操作符操作符說明說明=等于=大于或等于大于或等于 大于=小于或等于小于或等于 小于小于 不等于不等于6.2相關(guān)知識單表查詢 使用邏輯運(yùn)算符過濾數(shù)據(jù)使用邏輯運(yùn)算符AND、OR和NOT來連接一系列表達(dá)式并且簡化處查詢處理。查詢結(jié)果將根據(jù)表達(dá)式的分組情況和選擇條件的順序的不同而有所不同。使用邏輯運(yùn)算符要遵循以下規(guī)則：1.使用AND運(yùn)算符選擇滿足所有選擇條件的行。2.使用OR運(yùn)算符選擇滿足任意選擇條件的行。3.使用NOT運(yùn)算符否定NOT后面的表達(dá)式。4.當(dāng)有兩個(gè)或更多的表達(dá)式作為選

9、擇條件時(shí)，可以使用括號。使用括號可以： （1）將表達(dá)式分組 （2）改變求值的次序 （3）增加表達(dá)式的可讀性5.當(dāng)在一條語句中使用多個(gè)邏輯運(yùn)算符時(shí)，需要考慮以下的事項(xiàng)： （1）SQL Server 2000將首先求NOT運(yùn)算符的值，然后是AND運(yùn)算符，最后求OR運(yùn)算符的值。 （2）當(dāng)一個(gè)表達(dá)式中的所有運(yùn)算符具有相同的優(yōu)先級時(shí)，則按照從左到右的順序依次求值。6.2相關(guān)知識單表查詢 使用字符串比較符過濾數(shù)據(jù) 使用通配符結(jié)合LIKE查詢條件，通過進(jìn)行字符串比較來查詢符合條件的行。使用它Like關(guān)鍵字可以實(shí)現(xiàn)模糊插敘，例如，要查詢姓名為“王”開頭的學(xué)生或姓名中含有“王”的學(xué)生信息。使用Like關(guān)鍵字遵循

10、以下規(guī)則：1.模式字符串中的所有字符都有意義，包括開頭與結(jié)尾的空格。2.LIKE只適用數(shù)據(jù)類型為char、nachar、varchar、nvarchar、binary、varbinary、smalldatetime或datetime的數(shù)據(jù)，以及特定條件下數(shù)據(jù)類型為text、next和image的數(shù)據(jù)。6.2相關(guān)知識單表查詢 根據(jù)值的范圍過濾在WHERE子句中，使用BETWEENAND和NOT BERWEENAND選擇條件選擇屬性值在（或不在）指定范圍內(nèi)的行。其中BETWEEN后是范圍的下限（即低值），AND后面是上限（即高值）。例如查詢學(xué)號介于“20050001”和“20051000”之間的學(xué)

11、生信息。使用BETWEENAND或NOT BETWEENAND應(yīng)該遵循以下規(guī)則：1.在結(jié)果集中，SQL Server包括邊界值。2.為了簡化語法，使用BETWEEN選擇條件而不使用由AND運(yùn)算符連接的兩個(gè)比較運(yùn)算符所組成的表達(dá)式（=x AND x AND y）。3.使用NOT BETWEENAND選擇條件來選擇指定范圍外的行。注意，使用NOT條件會降低數(shù)據(jù)選擇的速度。6.2相關(guān)知識單表查詢 根據(jù)值的列表過濾在WHERE子句中使用IN選擇條件選擇與指定值列表相匹配的行。使用IN選擇條件遵循以下規(guī)則：1.不管是使用IN選擇條件還是使用由OR運(yùn)算符連接的一系列比較表達(dá)式，SQL Server都用相同

12、的方式處理，返回完全相同的結(jié)果集。2.在選擇條件中不要包含空值。3.使用NOT IN選擇條件查詢不在指定值列表中的行。注意使用NOT會降低數(shù)據(jù)選擇的速度。6.2相關(guān)知識單表查詢 涉及未知值的查詢?nèi)绻跀?shù)據(jù)輸入過程中沒有給某個(gè)列輸入值并且沒有給該列定義默認(rèn)值，那么該列就存在一個(gè)空值。空值不等同于數(shù)值0或空字符。使用IS NULL查詢條件來查詢那些指定列中遺漏信息的行。使用它NULL應(yīng)遵循以下規(guī)則：1.空值與任何值比較都會失敗，因?yàn)榭罩挡坏扔谌魏沃怠?.在CREATE TABLE語句中定義列是否允許有空值。3.使用IS NOT NULL查詢條件來查詢在指定列上具有已知值的行。6.2相關(guān)知識單表查詢

13、 消除重復(fù)行兩個(gè)本來并不完全相同的行，投影到指定的某些列上后，可能變成完全相同的行了。如果希望一個(gè)列表沒有重復(fù)值，則可以使用DISTINCT子句來消除結(jié)果集中的重復(fù)行。語法格式為：SELECT DISTINCT 使用Distinct關(guān)鍵字遵循宜家規(guī)則：1.在結(jié)果集中，除非已經(jīng)指定了DISTINCT子句，否則將返回所有符合SELECT語句中指定的查詢條件的行。2.選擇列表中所有值的組合決定其惟一性。3.查詢包含任何惟一值組合的行，并且將這些行返回到結(jié)果集中。4.DISTINCT子句以隨機(jī)的次序排列結(jié)果集，除非使用ORDER BY子句。5.如果指定DISTINCT子句，ORDER BY指定的列必須

14、出現(xiàn)在SELECT語句的選擇列表中。6.對于DISTINCT關(guān)鍵字來說，各空值將被認(rèn)為是相互重復(fù)的內(nèi)容。當(dāng)SELECT語句中包括DISTINCT時(shí)，不論遇到多少個(gè)空值，在結(jié)果中只返回一個(gè)NULL。DISTINCT關(guān)鍵字有一個(gè)與其對應(yīng)的關(guān)鍵字ALL，無論選擇列表中的值的組合惟一與否，它讓SQL Server返回所有的行。由于ALL關(guān)鍵字是SELECT語句的默認(rèn)行為，所以在一般情況下不使用ALL，但可以使用它來使查詢語法更容易理解。6.2相關(guān)知識單表查詢 對查詢結(jié)果排序如果沒有指定查詢結(jié)果的顯示順序，SQL Server 2008將按其最方便的順序（通常是行在表中的先后順序）輸出查詢結(jié)果。用戶也可

15、以用ORDER BY子句指定按照一個(gè)或多個(gè)屬性列的升序或降序重新排列查詢結(jié)果。語法格式如下： ORDER BY order_by_expression ASC | DESC ,.n 。6.2相關(guān)知識使用聚合函數(shù) COUNT()函數(shù)COUNT()函數(shù)用于統(tǒng)計(jì)查詢結(jié)果集中記錄的個(gè)數(shù)。使用COUNT（）函數(shù)應(yīng)該遵循以下規(guī)則：1.DISTINCT：指定COUNT返回惟一非空值的數(shù)量。2.expression：一個(gè)表達(dá)式，其類型是除uniqueidentifier、text、image或ntext之外的任何類型。不允許使用聚合函數(shù)和子查詢。3.*：指定應(yīng)該計(jì)算所有行以返回表中行的總數(shù)。COUNT(*)不

16、需要任何參數(shù)，而且不能與DISTINCT一起使用。COUNT(*)不需要expression參數(shù)，因?yàn)楦鶕?jù)定義，該函數(shù)不使用有關(guān)任何特定列的信息。COUNT(*)返回指定表中行的數(shù)量而不消除副本。它對每行分別進(jìn)行計(jì)數(shù)，包括含有空值的行。4.COUNT(*) 返回組中項(xiàng)目的數(shù)量，這些項(xiàng)目包括 NULL 值和副本。COUNT(ALL expression) 對組中的每一行都計(jì)算 expression 并返回非空值的數(shù)量。COUNT(DISTINCT expression) 對組中的每一行都計(jì)算 expression 并返回惟一非空值的數(shù)量。6.2相關(guān)知識使用聚合函數(shù) MAX()函數(shù)MAX函數(shù)用于返

17、回指定列中的最大值。使用MAX（）函數(shù)遵循以下規(guī)則：1.DISTINCT：指定每個(gè)惟一值都被考慮。DISTINCT對于MAX無意義，使用它僅僅是為了符合SQL-92兼容性。2.expression：常量、列名、函數(shù)以及算術(shù)運(yùn)算符、按位運(yùn)算符和字符串運(yùn)算符的任意組合。MAX可用于數(shù)字列、字符列和datetime列，但不能用于bit列。不允許使用聚合函數(shù)和子查詢。6.2相關(guān)知識使用聚合函數(shù) MIN()函數(shù)MIN（）函數(shù)用于返回指定列中的最小值。6.2相關(guān)知識使用聚合函數(shù) SUM()函數(shù)SUM函數(shù)返回表達(dá)式中所有值的和，或只返回DISTINCT值。SUM只能用于數(shù)字列?？罩祵⒈缓雎浴Ｊ褂肧UM函數(shù)，

18、有兩種形式，SUM（expression）或，SUM（DISTINCT expression），參數(shù)說明如下：DISTINCT：指定SUM返回惟一值的和。expression：是常量、列或函數(shù)，或者是算術(shù)、按位與字符串等運(yùn)算符的任意組合。expression是精確數(shù)字或近似數(shù)字?jǐn)?shù)據(jù)類型分類（bit數(shù)據(jù)類型除外）的表達(dá)式。不允許使用聚合函數(shù)和子查詢。6.2相關(guān)知識使用聚合函數(shù) AVG()函數(shù)AVG（）函數(shù)返回組中值的平均值，該組中的NULL值在計(jì)算過程中將被忽略。使用AVG函數(shù)遵循以下規(guī)則：1.DISTINCT：指定AVG操作只使用每個(gè)值的惟一實(shí)例，而不管該值出現(xiàn)了多少次。2.expressio

19、n：精確數(shù)字或近似數(shù)字?jǐn)?shù)據(jù)類型類別的表達(dá)式（bit數(shù)據(jù)類型除外）。不允許使用聚合函數(shù)和子查詢。6.2相關(guān)知識分組查詢 分組查詢在某些列或表達(dá)式中使用GROUP BY子句，把表分成組，并對組進(jìn)行匯總。使用GROUP BY遵循以下規(guī)則：1.SQL Server為每一個(gè)定義的組生成一個(gè)列值。2.SQL Server只是為每一個(gè)指定的組返回單一的行，并不返回詳細(xì)信息。3.所有在GROUP BY子句中指定的列必須包含在選擇列表中。4.如果包含WHERE子句，則SQL Server只對滿足WHERE子句條件的行進(jìn)行分組。5.GROUP BY列的大小、聚合的列和包含在查詢中的聚合的值限制了列項(xiàng)的數(shù)目。這個(gè)限

20、制來源與中間工作表每行最多只能有8060字節(jié)的限制，中間工作表用來存放中間查詢結(jié)果。6.因?yàn)榭罩凳亲鳛橐粋€(gè)組處理的，所以不要在包含多個(gè)空值的列中使用GROUP BY子句。7.使用ALL關(guān)鍵字與GROUP BY子句顯示在聚合列中含有空值的所有行，而忽略這些記錄是否滿足WHERE子句。6.2相關(guān)知識分組查詢 篩選分組結(jié)果對列或表達(dá)式使用HAVING子句為結(jié)果集中的組設(shè)置條件。HAVING子句為GROUP BY子句設(shè)置條件的方式與WHERE子句為SELECT語句設(shè)置條件的方式大致相同。使用HAVING子句應(yīng)遵循以下規(guī)則：1.HAVING子句只有與GROUP BY子句連用才能對分組進(jìn)行約束。只使用HA

21、VING子句而不使用GROUP BY子句是沒有意義的。2.可以引用任何出現(xiàn)在選擇列表中的列。3.不要與HAVING子句一起使用ALL關(guān)鍵字，因?yàn)镠AVING子句會忽略ALL關(guān)鍵字而返回只滿足自己條件的分組。6.2相關(guān)知識連接查詢 等值與非等值連接查詢但用戶的一個(gè)查詢請求涉及到數(shù)據(jù)庫的多個(gè)表時(shí)，必須按照一定的條件把這些表連接在一起，以便能夠共同提供用戶需要的信息。用來連接兩個(gè)表的條件成為連接條件或連接謂詞，其一般格式為：. . 其中比較運(yùn)算符主要有：=、=、=、!=。此外，連接謂詞還可以使用下面形式：. BETWEEN . AND .但連接運(yùn)算符為時(shí)，成為等值連接。使用其他運(yùn)算符成為非等值連接。

22、連接謂詞中的列名稱為連接字段。連接條件中的各連接字段類型必須時(shí)可比的，但不必是相同的。例如，可以都是字符型，或都是日期型；也可以一個(gè)是整型，另一個(gè)是實(shí)型，整型和實(shí)型都是數(shù)值型，因此是可比的。但若一個(gè)是字符型，另一個(gè)是日期型就不允許了，因?yàn)樗鼈兪遣豢杀鹊念愋汀?.2相關(guān)知識連接查詢 內(nèi)連接查詢內(nèi)連接通過比較兩個(gè)表共同擁有的列的值，把兩個(gè)表連接起來。SQL Server 2008將只返回滿足條件的行。使用內(nèi)連接可以得到兩個(gè)單獨(dú)的表中的信息，并把這些信息組合到一個(gè)結(jié)果集中。使用內(nèi)連接查詢遵循以下規(guī)則：1.內(nèi)連接是SQL Server 2008默認(rèn)的連接方式?？梢园袸NNER JOIN子句簡寫成JOI

23、N。2.在SELECT語句選擇列表中包含合法列名，以指定要在結(jié)果集中顯示的列名。3.使用WHERE子句以限制結(jié)果集要返回的行。4.在連接的條件中不要使用空值，因?yàn)榭罩岛推渌刀疾幌嗟取?.2相關(guān)知識連接查詢 外連接查詢?nèi)粢獎?chuàng)建一個(gè)查詢，以返回一個(gè)或多個(gè)表中的所有行（無論在另一個(gè)表中是否含有相匹配行），則需要使用外連接。外連接包括左外連接和右外連接。左外連接或右外連接可以將兩個(gè)表中返回符合連接條件的行組合在一起，同時(shí)也將左邊或右邊的表中不符合連接條件的行組合在一起。在結(jié)果集中，不滿足連接條件的行將顯示空值。使用外連接遵循以下規(guī)則：1.左外連接可以顯示表達(dá)式左邊的那個(gè)表中的所有行。如果反置FROM子句中兩個(gè)表的順序，則生成的結(jié)果集同使用右外連接的結(jié)果集相同。2.右外連接可以顯示表達(dá)式右邊的那個(gè)表中的所有行。如果反置FROM子句中兩個(gè)表的順序，則生成的結(jié)果集同使用左外連接的結(jié)果集相同。3.可以把LEFT OUTER JOIN（左外連接）或RIGHT OUTER JOIN（右外連接）簡寫成LEFT JOIN或RIGHT JOIN。6.2相關(guān)知識連接查詢 交叉連接查詢交叉連接將從被連接的表中返回所有可能的行的組合。使用交叉連接時(shí)不要求連接的表一定擁有相同的列。盡管在一個(gè)規(guī)范化的數(shù)據(jù)庫中很少使用交叉連接，但可以用它為數(shù)據(jù)庫生成測試數(shù)據(jù)，或?yàn)楹藢Ρ砑皹I(yè)務(wù)模板生成所有可能組合的清單。6