SQL高級使用
SELECT高級查詢
錯(cuò)誤處理
事務(wù)處理
數(shù)據(jù)的鎖定
游標(biāo)
1.SELECT高級查詢
前面已經(jīng)介紹了SELECT語句的用法，本節(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ù)倉庫或數(shù)據(jù)集合中的匯總聚合中。
    MS SQL Server Analysis Services。這組組件將數(shù)據(jù)倉庫中的數(shù)據(jù)組織到含有預(yù)先計(jì)算好的匯總信息的多維數(shù)據(jù)集中，以對復(fù)雜的分析查詢提供快速響應(yīng)。Analysis Services還提供一套向?qū)�，用于定義分析處理過程中所用的多維結(jié)構(gòu)，并提供用于管理分析結(jié)構(gòu)的Microsoft管理控制臺(tái)管理單元。
但是對于生成簡單匯總報(bào)表的應(yīng)用程序，可使用下列T-SQL元素：
    CUBE或ROLLUP運(yùn)算符。這兩者均是SELECT語句的GROUP BY子句的一部分。
    COMPUTE或COMPUTE BY運(yùn)算符。這兩者也與GROUP BY相關(guān)聯(lián)。
下面介紹使用生成匯總報(bào)表的語句。
a.聚合函數(shù)
數(shù)據(jù)庫的一個(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ù)對一組值計(jì)算并返回單一的值。除COUNT外，聚合函數(shù)忽略空值。聚合函數(shù)僅在下列項(xiàng)中允許作為表達(dá)式使用：
    SELECT語句的選擇列表(子查詢或外部查詢)
    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子句用來為結(jié)果集中的每一行產(chǎn)生聚合值，即對結(jié)果集進(jìn)行分組。如果聚合函數(shù)沒有使用此子句，則只為SELECT語句報(bào)告一個(gè)聚合值。指定GROUP BY時(shí)，選擇列表中任一非聚合表達(dá)式內(nèi)的所有列都應(yīng)包含在GROUP BY列表中，或者GROUP BY表達(dá)式必須與選擇列表表達(dá)式完全匹配。
GROUP BY語法格式：
[GROUP BY [ALL]group_by_expression[,...n]
[WITH {CUBE | ROLLUP} ]
]
參數(shù)說明：
    ALL：表示對所有列和結(jié)果集(包括不滿足WHERE子句的列)進(jìn)行分組。但是ALL關(guān)鍵字不支持遠(yuǎn)程表查詢。對組中不滿足搜索條件的匯總列將返回空值。
    group_by_expression：對其執(zhí)行分組的表達(dá)式，也稱為分組列。在選擇列表內(nèi)定義的列的別名不能用于指定分組列；text、ntext和image類型的列不能用于分組列。
    CUBE：指定在結(jié)果集內(nèi)不僅包含由GROUP BY提供的正常行，還包含匯總行。創(chuàng)建所有可能的分組，然后對這些組進(jìn)行合計(jì)。
    ROLLUP：指定在結(jié)果集內(nèi)不僅包含由GROUP BY提供的正常行，還包含匯總行。從最低到最高進(jìn)行分組，然后對這些組進(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í)行語句：
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表示)分別訂購的總量，還會(huì)統(tǒng)計(jì)字段中各種品種的訂購總量。
ROLLUP參數(shù)則會(huì)依據(jù)GROUP BY后面所列第一個(gè)字段作匯總運(yùn)算。
如執(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子句指定或合計(jì)的搜索條件，HAVING通常與GROUP BY子句一起使用。如果不使用GROUP BY子句，HAVING的作用與WHERE子句一樣。但是聚合函數(shù)可以在HAVING子句中使用，而不能在WHERE子句中使用。
語法格式為：[HAVING <search_condition>]
其中<search_condition>指定組或聚合應(yīng)滿足的條件。當(dāng)HAVING與GROUP BY ALL一起使用時(shí)，HAVING子句替代ALL。
注意：在HAVING子句中不能使用text、image和ntext數(shù)據(jù)類型。另外，在SELECT語句中使用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是為了保持向后兼容。如果不考慮兼容的問題，則應(yīng)使用Analysis Services和用于Analysis Services的OLE DB或多維的ActiveX數(shù)據(jù)對象或ROLLUP運(yùn)算符。
語法格式如下：
[COMPUTE aggregate_func(column_name),[...n] [BY column_name]...]
其中：
    aggregate_func：為上面的聚合函數(shù)之一，如果沒有，則等同于COUNT(*)函數(shù)。
    column_name：對其執(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)表�？梢詾楦鹘M生成匯總值，也可以對同一組計(jì)算多個(gè)聚合函數(shù)。如在例2中的計(jì)算子句中加入,avg(qty),sum(no)等。
例：顯示含有年初至今銷售額的行，然后按type以遞減順序計(jì)算書籍的平均價(jià)格和預(yù)付款總額。將返回四個(gè)數(shù)據(jù)列，包括截?cái)嗟臅�名。所有的�?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語句的總聚合。選擇列表可包含除分組依據(jù)列或聚合函數(shù)之外的其他表達(dá)式。聚合函數(shù)在COMPUTE子句中指定，而不是在選擇列表中。
e.聯(lián)接查詢
通過聯(lián)接，可以根據(jù)各個(gè)表之間的邏輯關(guān)系從兩個(gè)或多個(gè)表中檢索數(shù)據(jù)。聯(lián)接表示系統(tǒng)應(yīng)如何使用一個(gè)表中的數(shù)據(jù)來選擇另一個(gè)表中的行。
聯(lián)接條件通過以下方法定義兩個(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子句引用的基表中所選定的行。
簡單的子句聯(lián)接語法如下：
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定義要為每對聯(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)鍵詞，上面的查詢也可以使用下面語句完成：
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搜索條件。將檢索通過左向外聯(lián)接引用的左表的所有行，以及通過右向外聯(lián)接引用的右表的所有行。完整外部聯(lián)接中兩個(gè)表的所有行都將返回。
系統(tǒng)對在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í)行：