| 以文本方式查看主題 - 曙海教育集團(tuán)論壇 (http://www.adgdgn.cn/bbs/index.asp) -- SQL Server數(shù)據(jù)庫(kù) (http://www.adgdgn.cn/bbs/list.asp?boardid=67) ---- SQL高級(jí)使用數(shù)據(jù)庫(kù) (http://www.adgdgn.cn/bbs/dispbbs.asp?boardid=67&id=2520) |
| -- 作者：wangxinxin -- 發(fā)布時(shí)間：2010-12-13 13:08:33 -- SQL高級(jí)使用數(shù)據(jù)庫(kù) SQL高級(jí)使用 SELECT高級(jí)查詢 錯(cuò)誤處理 事務(wù)處理 數(shù)據(jù)的鎖定 游標(biāo) 1.SELECT高級(jí)查詢 前面已經(jīng)介紹了SELECT語(yǔ)句的用法，本節(jié)主要介紹數(shù)據(jù)匯總、聯(lián)接查詢、子查詢和關(guān)鍵詞UNION的使用。 (1)數(shù)據(jù)匯總 為決策支持系統(tǒng)生成聚合事務(wù)的匯總報(bào)表是一項(xiàng)復(fù)雜并且相當(dāng)消耗資源的工作。SQL Server 2000提供兩個(gè)靈活且強(qiáng)大的組件，用于生成SQL Server 2000 Analysis Services。這些組件是程序員在執(zhí)行SQL Server數(shù)據(jù)的多維分析時(shí)應(yīng)當(dāng)使用的主要工具。這兩個(gè)組件為： 數(shù)據(jù)轉(zhuǎn)換服務(wù)(DTS)。DTS支持提取事務(wù)數(shù)據(jù)并將這些數(shù)據(jù)轉(zhuǎn)換到數(shù)據(jù)倉(cāng)庫(kù)或數(shù)據(jù)集合中的匯總聚合中。 MS SQL Server Analysis Services。這組組件將數(shù)據(jù)倉(cāng)庫(kù)中的數(shù)據(jù)組織到含有預(yù)先計(jì)算好的匯總信息的多維數(shù)據(jù)集中，以對(duì)復(fù)雜的分析查詢提供快速響應(yīng)。Analysis Services還提供一套向?qū)В糜诙x分析處理過(guò)程中所用的多維結(jié)構(gòu)，并提供用于管理分析結(jié)構(gòu)的Microsoft管理控制臺(tái)管理單元。 但是對(duì)于生成簡(jiǎn)單匯總報(bào)表的應(yīng)用程序，可使用下列T-SQL元素： CUBE或ROLLUP運(yùn)算符。這兩者均是SELECT語(yǔ)句的GROUP BY子句的一部分。 COMPUTE或COMPUTE BY運(yùn)算符。這兩者也與GROUP BY相關(guān)聯(lián)。 下面介紹使用生成匯總報(bào)表的語(yǔ)句。 a.聚合函數(shù) 數(shù)據(jù)庫(kù)的一個(gè)最大的特點(diǎn)是將各種分散的數(shù)據(jù)按照一定規(guī)律、條件進(jìn)行分類組合，最后得出統(tǒng)計(jì)結(jié)果。SQL Server提供了聚合函數(shù)，用于完成一定的統(tǒng)計(jì)功能。常用的函數(shù)如下表： AVG：求平均值 COUNT：返回組中項(xiàng)目的數(shù)量，返回值為int類型 COUNT_GIG：返回組中項(xiàng)目的數(shù)量，返回值為bigint類型 MAX：求最大值 MIN：求最小值 SUM：求和 STDEV：計(jì)算統(tǒng)計(jì)標(biāo)準(zhǔn)偏差 VAR：統(tǒng)計(jì)方差 VARP: 填充統(tǒng)計(jì)方差 聚合函數(shù)對(duì)一組值計(jì)算并返回單一的值。除COUNT外，聚合函數(shù)忽略空值。聚合函數(shù)僅在下列項(xiàng)中允許作為表達(dá)式使用： SELECT語(yǔ)句的選擇列表(子查詢或外部查詢) COMPUTE或COMPUTE BY子句 HAVING子句 如： USE bookdb GO SELECT COUNT(address) FROM authors GO 執(zhí)行結(jié)果：2 USE bookdb GO SELECT MAX(price) FROM book GO 執(zhí)行結(jié)果：45.0 b.GROUP BY子句 GROUP BY子句用來(lái)為結(jié)果集中的每一行產(chǎn)生聚合值，即對(duì)結(jié)果集進(jìn)行分組。如果聚合函數(shù)沒(méi)有使用此子句，則只為SELECT語(yǔ)句報(bào)告一個(gè)聚合值。指定GROUP BY時(shí)，選擇列表中任一非聚合表達(dá)式內(nèi)的所有列都應(yīng)包含在GROUP BY列表中，或者GROUP BY表達(dá)式必須與選擇列表表達(dá)式完全匹配。 GROUP BY語(yǔ)法格式： [GROUP BY [ALL]group_by_expression[,...n] [WITH {CUBE | ROLLUP} ] ] 參數(shù)說(shuō)明： ALL：表示對(duì)所有列和結(jié)果集(包括不滿足WHERE子句的列)進(jìn)行分組。但是ALL關(guān)鍵字不支持遠(yuǎn)程表查詢。對(duì)組中不滿足搜索條件的匯總列將返回空值。 group_by_expression：對(duì)其執(zhí)行分組的表達(dá)式，也稱為分組列。在選擇列表內(nèi)定義的列的別名不能用于指定分組列；text、ntext和image類型的列不能用于分組列。 CUBE：指定在結(jié)果集內(nèi)不僅包含由GROUP BY提供的正常行，還包含匯總行。創(chuàng)建所有可能的分組，然后對(duì)這些組進(jìn)行合計(jì)。 ROLLUP：指定在結(jié)果集內(nèi)不僅包含由GROUP BY提供的正常行，還包含匯總行。從最低到最高進(jìn)行分組，然后對(duì)這些組進(jìn)行合計(jì)。 注意：使用CUBE或ROLLUP時(shí)，不支持區(qū)分合計(jì)，如AVG(DISTINCT column_name)、COUNT(DISTINCT column_name)和SUM(DISTINCT column_name)。如果使用這類聚合，SQL Server將返回錯(cuò)誤信息并取消查詢。 例：表a2如下： No name coop qty --------------------- 1 c1 mac1 1.0 2 c2 mac2 2.0 3 c1 mac2 3.0 4 c3 mac1 4.0 5 c2 mac1 5.0 6 c1 mac2 6.0 (6 row(s) affected) 執(zhí)行語(yǔ)句： select name,coop,sum(qut) as sumqut from a2 group by name,coop with cube name coop sumqut ------------------------- c1 mac1 1.0 c1 mac2 9.0 c1 NULL 10.0 //前面二行的匯總 c2 mac1 5.0 c2 mac2 2.0 c2 NULL 7.0 //前面二行的匯總 c3 mac1 4.0 c3 NULL 4.0 //前面一行的匯總 NULL NULL 21.0 //前面各行的匯總 NULL mac1 10.0 //前面所有mac1的匯總 NULL mac2 11.0 //前面所有mac2的匯總 (11 row(s) affected) 使用CUBE可以計(jì)算NAME字段中及其所有客戶(以NULL表示)分別訂購(gòu)的總量，還會(huì)統(tǒng)計(jì)字段中各種品種的訂購(gòu)總量。 ROLLUP參數(shù)則會(huì)依據(jù)GROUP BY后面所列第一個(gè)字段作匯總運(yùn)算。 如執(zhí)行語(yǔ)句： select name,coop,sum(qut) as sumqut from a2 group by name,coop with rollup name coop sumqut -------------------- c1 mac1 1.0 c1 mac2 9.0 c1 NULL 10.0 //前面二行的匯總 c2 mac1 5.0 c2 mac2 2.0 c2 NULL 7.0 //前面二行的匯總 c3 mac1 4.0 c3 NULL 4.0 //前面一行的匯總 NULL NULL 21.0 //前面各行的匯總 (9 row(s) affected) c.HAVING子句 HAVING子句指定或合計(jì)的搜索條件，HAVING通常與GROUP BY子句一起使用。如果不使用GROUP BY子句，HAVING的作用與WHERE子句一樣。但是聚合函數(shù)可以在HAVING子句中使用，而不能在WHERE子句中使用。 語(yǔ)法格式為：[HAVING <search_condition>] 其中<search_condition>指定組或聚合應(yīng)滿足的條件。當(dāng)HAVING與GROUP BY ALL一起使用時(shí)，HAVING子句替代ALL。 注意：在HAVING子句中不能使用text、image和ntext數(shù)據(jù)類型。另外，在SELECT語(yǔ)句中使用HAVING子句不影響CUBE運(yùn)算符分組結(jié)果集和返回匯總局聚合行的方式。 如： select bame,coop,sum(qty) from a2 group by name,coop with cube having sum(qty)>3 d.COMPUTE和COMPUTE BY子句 SQL Server2000提供COMPUTE和COMPUTE BY是為了保持向后兼容。如果不考慮兼容的問(wèn)題，則應(yīng)使用Analysis Services和用于Analysis Services的OLE DB或多維的ActiveX數(shù)據(jù)對(duì)象或ROLLUP運(yùn)算符。 語(yǔ)法格式如下： [COMPUTE aggregate_func(column_name),[...n] [BY column_name]...] 其中： aggregate_func：為上面的聚合函數(shù)之一，如果沒(méi)有，則等同于COUNT(*)函數(shù)。 column_name：對(duì)其執(zhí)行計(jì)算的列名，且在選擇列表中出現(xiàn)。 如果使用COMPUTE BY，則必須使用ORDER BY子句。 例1：select * from a2 compute sum(qty) No name coop qty --------------------------------------------------- 1 c1 mac1 1.0 2 c2 mac2 2.0 3 c1 mac2 3.0 4 c3 mac1 4.0 5 c2 mac1 5.0 6 c1 mac2 6.0 sum ===================================================== 21.0 例2：select * from a2 order by name compute sum(qty) by name 由上面的結(jié)果可以看到，COMPUTE所生成的匯總值在查詢結(jié)果中顯示為分離的結(jié)果集。包括COMPUTE子句的查詢結(jié)果類似于控制中斷報(bào)表，即匯總值由指定的組(或稱中斷)控制的報(bào)表。可以為各組生成匯總值，也可以對(duì)同一組計(jì)算多個(gè)聚合函數(shù)。如在例2中的計(jì)算子句中加入,avg(qty),sum(no)等。 例：顯示含有年初至今銷售額的行，然后按type以遞減順序計(jì)算書(shū)籍的平均價(jià)格和預(yù)付款總額。將返回四個(gè)數(shù)據(jù)列，包括截?cái)嗟臅?shū)名。所有的計(jì)算列都將出現(xiàn)在選擇列表內(nèi)。 程序清單如下： USE pubs SELECT CAST(title AS char(20)) AS title,type,price,advance FROM titles WHERE ytd_sales IS NOT NULL ORDER BY type DESC COMPUTE AVG(price),SUM(advance) BY type COMPUTE SUM(price),SUM(advance) COMPUTE與GROUP BY的區(qū)別 GROUP BY生成單個(gè)結(jié)果集。每個(gè)組都有一個(gè)只包含分組依據(jù)列和顯示該組子聚合的聚合函數(shù)的行。選擇列表只能包含分組依據(jù)列和聚合函數(shù)。 COMPUTE生成多個(gè)結(jié)果集。一類結(jié)果集包含每個(gè)組的明細(xì)行，其中包含選擇列表中的表達(dá)式。另一類結(jié)果集包含組的子聚合，或SELECT語(yǔ)句的總聚合。選擇列表可包含除分組依據(jù)列或聚合函數(shù)之外的其他表達(dá)式。聚合函數(shù)在COMPUTE子句中指定，而不是在選擇列表中。 e.聯(lián)接查詢 通過(guò)聯(lián)接，可以根據(jù)各個(gè)表之間的邏輯關(guān)系從兩個(gè)或多個(gè)表中檢索數(shù)據(jù)。聯(lián)接表示系統(tǒng)應(yīng)如何使用一個(gè)表中的數(shù)據(jù)來(lái)選擇另一個(gè)表中的行。 聯(lián)接條件通過(guò)以下方法定義兩個(gè)表在查詢中的關(guān)聯(lián)方式： 指定每個(gè)表中要用于聯(lián)接的列。典型的聯(lián)接條件在一個(gè)表中指定外鍵，在另一個(gè)表中指定與其關(guān)聯(lián)的健。 指定比較各列的值時(shí)要使用的邏輯運(yùn)算符(=、<>等)。 可在FROM或WHERE子句中指定聯(lián)接。聯(lián)接條件與WHERE和HAVING搜索條件組合，用于控制FROM子句引用的基表中所選定的行。 簡(jiǎn)單的子句聯(lián)接語(yǔ)法如下： FROM first_table join_tye second_table [ON(join_condition)] 其中join_tye指定所執(zhí)行的聯(lián)接類型，有內(nèi)聯(lián)接、外聯(lián)接或交叉聯(lián)接。join_condition定義要為每對(duì)聯(lián)接折行選取的謂詞。 例：與外健表相聯(lián)接。 設(shè)表student為： no name addr t_no ----------------------- 001 s1 sss 501 002 s2 ttt 502 003 s3 uuu 501 004 s4 vvv 503 005 s5 www 602 006 s6 xxx 601 設(shè)表teacher為： no name sala ----------------------- 501 t1 100 502 t2 200 503 t3 300 504 t4 400 505 t5 500 506 t6 600 select a.no,a.name,a.addr,b.name from student a join teacher b on(a.t_no=b.no) no name addr name ----------------------- 001 s1 sss t1 002 s2 ttt t2 003 s3 uuu t1 004 s4 vvv t3 (4 row(s) affected) 內(nèi)聯(lián)接 內(nèi)聯(lián)接是用比較運(yùn)算符比較要聯(lián)接列的值的聯(lián)接。內(nèi)聯(lián)接使用INNER JOIN關(guān)鍵詞，上面的查詢也可以使用下面語(yǔ)句完成： select a.no,a.name,a.addr,b.name from student a inner join teacher b on(a.t_no=b.no) 一般地，要做內(nèi)聯(lián)接的兩個(gè)或多個(gè)表之間若存在著主鍵盤和外鍵的關(guān)系時(shí)，將這些鍵的關(guān)系列出即可得到表的聯(lián)接結(jié)果。 外聯(lián)接 僅當(dāng)至少有一個(gè)同屬于兩個(gè)表的行符合聯(lián)接條件時(shí)，內(nèi)聯(lián)接才返回行。因此內(nèi)聯(lián)接消除與另一表中的任何不匹配的行。而外聯(lián)接會(huì)返回FROM子句中提到的至少一個(gè)表或視圖的所有行，只要這些行符合任何WHERE或HAVING搜索條件。將檢索通過(guò)左向外聯(lián)接引用的左表的所有行，以及通過(guò)右向外聯(lián)接引用的右表的所有行。完整外部聯(lián)接中兩個(gè)表的所有行都將返回。 系統(tǒng)對(duì)在FROM子句中指定的外聯(lián)接使用以下關(guān)鍵字： LEFT OUTER JOIN或LEFT JOIN(左向外聯(lián)接) RIGHT OUTER JOIN或RIGHT JOIN(右向外聯(lián)接) FULL OUTER JOIN或FULL JOIN(完整外部聯(lián)接) 左向外聯(lián)接：包括第一個(gè)表(左表，在JOIN子句的左邊)中的所有行，不包括右表中不匹配的行。 如由上面的表student和teacher，執(zhí)行： |