| 以文本方式查看主題 - 曙海教育集團論壇 (http://www.022-oo.cn/bbs/index.asp) -- SQL Server數據庫 (http://www.022-oo.cn/bbs/list.asp?boardid=67) ---- SQL高級使用數據庫 (http://www.022-oo.cn/bbs/dispbbs.asp?boardid=67&id=2520) |
| -- 作者：wangxinxin -- 發(fā)布時間：2010-12-13 13:08:33 -- SQL高級使用數據庫 SQL高級使用 SELECT高級查詢 錯誤處理 事務處理 數據的鎖定 游標 1.SELECT高級查詢 前面已經介紹了SELECT語句的用法，本節(jié)主要介紹數據匯總、聯接查詢、子查詢和關鍵詞UNION的使用。 (1)數據匯總 為決策支持系統生成聚合事務的匯總報表是一項復雜并且相當消耗資源的工作。SQL Server 2000提供兩個靈活且強大的組件，用于生成SQL Server 2000 Analysis Services。這些組件是程序員在執(zhí)行SQL Server數據的多維分析時應當使用的主要工具。這兩個組件為： 數據轉換服務(DTS)。DTS支持提取事務數據并將這些數據轉換到數據倉庫或數據集合中的匯總聚合中。 MS SQL Server Analysis Services。這組組件將數據倉庫中的數據組織到含有預先計算好的匯總信息的多維數據集中，以對復雜的分析查詢提供快速響應。Analysis Services還提供一套向導，用于定義分析處理過程中所用的多維結構，并提供用于管理分析結構的Microsoft管理控制臺管理單元。 但是對于生成簡單匯總報表的應用程序，可使用下列T-SQL元素： CUBE或ROLLUP運算符。這兩者均是SELECT語句的GROUP BY子句的一部分。 COMPUTE或COMPUTE BY運算符。這兩者也與GROUP BY相關聯。 下面介紹使用生成匯總報表的語句。 a.聚合函數 數據庫的一個最大的特點是將各種分散的數據按照一定規(guī)律、條件進行分類組合，最后得出統計結果。SQL Server提供了聚合函數，用于完成一定的統計功能。常用的函數如下表： AVG：求平均值 COUNT：返回組中項目的數量，返回值為int類型 COUNT_GIG：返回組中項目的數量，返回值為bigint類型 MAX：求最大值 MIN：求最小值 SUM：求和 STDEV：計算統計標準偏差 VAR：統計方差 VARP: 填充統計方差 聚合函數對一組值計算并返回單一的值。除COUNT外，聚合函數忽略空值。聚合函數僅在下列項中允許作為表達式使用： SELECT語句的選擇列表(子查詢或外部查詢) COMPUTE或COMPUTE BY子句 HAVING子句 如： USE bookdb GO SELECT COUNT(address) FROM authors GO 執(zhí)行結果：2 USE bookdb GO SELECT MAX(price) FROM book GO 執(zhí)行結果：45.0 b.GROUP BY子句 GROUP BY子句用來為結果集中的每一行產生聚合值，即對結果集進行分組。如果聚合函數沒有使用此子句，則只為SELECT語句報告一個聚合值。指定GROUP BY時，選擇列表中任一非聚合表達式內的所有列都應包含在GROUP BY列表中，或者GROUP BY表達式必須與選擇列表表達式完全匹配。 GROUP BY語法格式： [GROUP BY [ALL]group_by_expression[,...n] [WITH {CUBE | ROLLUP} ] ] 參數說明： ALL：表示對所有列和結果集(包括不滿足WHERE子句的列)進行分組。但是ALL關鍵字不支持遠程表查詢。對組中不滿足搜索條件的匯總列將返回空值。 group_by_expression：對其執(zhí)行分組的表達式，也稱為分組列。在選擇列表內定義的列的別名不能用于指定分組列；text、ntext和image類型的列不能用于分組列。 CUBE：指定在結果集內不僅包含由GROUP BY提供的正常行，還包含匯總行。創(chuàng)建所有可能的分組，然后對這些組進行合計。 ROLLUP：指定在結果集內不僅包含由GROUP BY提供的正常行，還包含匯總行。從最低到最高進行分組，然后對這些組進行合計。 注意：使用CUBE或ROLLUP時，不支持區(qū)分合計，如AVG(DISTINCT column_name)、COUNT(DISTINCT column_name)和SUM(DISTINCT column_name)。如果使用這類聚合，SQL Server將返回錯誤信息并取消查詢。 例：表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í)行語句： 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可以計算NAME字段中及其所有客戶(以NULL表示)分別訂購的總量，還會統計字段中各種品種的訂購總量。 ROLLUP參數則會依據GROUP BY后面所列第一個字段作匯總運算。 如執(zhí)行語句： 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子句指定或合計的搜索條件，HAVING通常與GROUP BY子句一起使用。如果不使用GROUP BY子句，HAVING的作用與WHERE子句一樣。但是聚合函數可以在HAVING子句中使用，而不能在WHERE子句中使用。 語法格式為：[HAVING <search_condition>] 其中<search_condition>指定組或聚合應滿足的條件。當HAVING與GROUP BY ALL一起使用時，HAVING子句替代ALL。 注意：在HAVING子句中不能使用text、image和ntext數據類型。另外，在SELECT語句中使用HAVING子句不影響CUBE運算符分組結果集和返回匯總局聚合行的方式。 如： 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是為了保持向后兼容。如果不考慮兼容的問題，則應使用Analysis Services和用于Analysis Services的OLE DB或多維的ActiveX數據對象或ROLLUP運算符。 語法格式如下： [COMPUTE aggregate_func(column_name),[...n] [BY column_name]...] 其中： aggregate_func：為上面的聚合函數之一，如果沒有，則等同于COUNT(*)函數。 column_name：對其執(zhí)行計算的列名，且在選擇列表中出現。 如果使用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 由上面的結果可以看到，COMPUTE所生成的匯總值在查詢結果中顯示為分離的結果集。包括COMPUTE子句的查詢結果類似于控制中斷報表，即匯總值由指定的組(或稱中斷)控制的報表�？梢詾楦鹘M生成匯總值，也可以對同一組計算多個聚合函數。如在例2中的計算子句中加入,avg(qty),sum(no)等。 例：顯示含有年初至今銷售額的行，然后按type以遞減順序計算書籍的平均價格和預付款總額。將返回四個數據列，包括截斷的書名。所有的計算列都將出現在選擇列表內。 程序清單如下： 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生成單個結果集。每個組都有一個只包含分組依據列和顯示該組子聚合的聚合函數的行。選擇列表只能包含分組依據列和聚合函數。 COMPUTE生成多個結果集。一類結果集包含每個組的明細行，其中包含選擇列表中的表達式。另一類結果集包含組的子聚合，或SELECT語句的總聚合。選擇列表可包含除分組依據列或聚合函數之外的其他表達式。聚合函數在COMPUTE子句中指定，而不是在選擇列表中。 e.聯接查詢 通過聯接，可以根據各個表之間的邏輯關系從兩個或多個表中檢索數據。聯接表示系統應如何使用一個表中的數據來選擇另一個表中的行。 聯接條件通過以下方法定義兩個表在查詢中的關聯方式： 指定每個表中要用于聯接的列。典型的聯接條件在一個表中指定外鍵，在另一個表中指定與其關聯的健。 指定比較各列的值時要使用的邏輯運算符(=、<>等)。 可在FROM或WHERE子句中指定聯接。聯接條件與WHERE和HAVING搜索條件組合，用于控制FROM子句引用的基表中所選定的行。 簡單的子句聯接語法如下： FROM first_table join_tye second_table [ON(join_condition)] 其中join_tye指定所執(zhí)行的聯接類型，有內聯接、外聯接或交叉聯接。join_condition定義要為每對聯接折行選取的謂詞。 例：與外健表相聯接。 設表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 設表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) 內聯接 內聯接是用比較運算符比較要聯接列的值的聯接。內聯接使用INNER JOIN關鍵詞，上面的查詢也可以使用下面語句完成： select a.no,a.name,a.addr,b.name from student a inner join teacher b on(a.t_no=b.no) 一般地，要做內聯接的兩個或多個表之間若存在著主鍵盤和外鍵的關系時，將這些鍵的關系列出即可得到表的聯接結果。 外聯接 僅當至少有一個同屬于兩個表的行符合聯接條件時，內聯接才返回行。因此內聯接消除與另一表中的任何不匹配的行。而外聯接會返回FROM子句中提到的至少一個表或視圖的所有行，只要這些行符合任何WHERE或HAVING搜索條件。將檢索通過左向外聯接引用的左表的所有行，以及通過右向外聯接引用的右表的所有行。完整外部聯接中兩個表的所有行都將返回。 系統對在FROM子句中指定的外聯接使用以下關鍵字： LEFT OUTER JOIN或LEFT JOIN(左向外聯接) RIGHT OUTER JOIN或RIGHT JOIN(右向外聯接) FULL OUTER JOIN或FULL JOIN(完整外部聯接) 左向外聯接：包括第一個表(左表，在JOIN子句的左邊)中的所有行，不包括右表中不匹配的行。 如由上面的表student和teacher，執(zhí)行： |