SQL高级使用
SELECT高级查询
错误处理
事务处理
数据的锁定
游标
1.SELECT高级查询
前面已经介绍了SELECT语句的用法，本节主要介绍数据汇总、联接查询、子查询和关键词UNION的使用。
(1)数据汇总
为决策支持系统生成聚合事务的汇总报表是一项复杂并且相当消耗资源的工作。SQL Server 2000提供两个灵活且强大的组件，用于生成SQL Server 2000 Analysis Services。这些组件是程序员在执行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.聚合函数
数据库的一个最大的特点是将各种分散的数据按照一定规律、条件进行分类组合，最后得出统计结果。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
执行结果：2
USE bookdb
GO
SELECT MAX(price) FROM book
GO
执行结果：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：对其执行分组的表达式，也称为分组列。在选择列表内定义的列的别名不能用于指定分组列；text、ntext和image类型的列不能用于分组列。
    CUBE：指定在结果集内不仅包含由GROUP BY提供的正常行，还包含汇总行。创建所有可能的分组，然后对这些组进行合计。
    ROLLUP：指定在结果集内不仅包含由GROUP BY提供的正常行，还包含汇总行。从最低到最高进行分组，然后对这些组进行合计。
注意：使用CUBE或ROLLUP时，不支持区分合计，如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)

执行语句：
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后面所列第一个字段作汇总运算。
如执行语句：
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：对其执行计算的列名，且在选择列表中出现。
如果使用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子句的查询结果类似于控制中断报表，即汇总值由指定的组(或称中断)控制的报表。可以为各组生成汇总值，也可以对同一组计算多个聚合函数。如在例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的区别
    GROUP BY生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。选择列表只能包含分组依据列和聚合函数。
    COMPUTE生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表达式。另一类结果集包含组的子聚合，或SELECT语句的总聚合。选择列表可包含除分组依据列或聚合函数之外的其他表达式。聚合函数在COMPUTE子句中指定，而不是在选择列表中。
e.联接查询
通过联接，可以根据各个表之间的逻辑关系从两个或多个表中检索数据。联接表示系统应如何使用一个表中的数据来选择另一个表中的行。
联接条件通过以下方法定义两个表在查询中的关联方式：
    指定每个表中要用于联接的列。典型的联接条件在一个表中指定外键，在另一个表中指定与其关联的健。
    指定比较各列的值时要使用的逻辑运算符(=、<>等)。
可在FROM或WHERE子句中指定联接。联接条件与WHERE和HAVING搜索条件组合，用于控制FROM子句引用的基表中所选定的行。
简单的子句联接语法如下：
FROM first_table join_tye second_table [ON(join_condition)]
其中join_tye指定所执行的联接类型，有内联接、外联接或交叉联接。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，执行：