数据查询技术
4.1.2 SQL查询技术
最常用的关系数据库系统通过称为SQL的语言对数据库进行查询和更新。SQL的含义是“结构化查询语言（Structured Query Language）”。SQL中最简单的查询就是从某个关系中查找满足某种条件的一些元组。这种查询类似于关系代数中的选择。这种简单的查询，同几乎所有的SQL查询一样，使用了具有SQL特性的三个关键字：SELECT、FROM以及WHERE。
1．SELECT检索数据
在许多方面，查询都是SQL语言的中心内容，而用于表示SQL查询的SELECT语句，是SQL语句中功能最强大也是最复杂的。
SELECT语句的作用是让数据库服务器根据客户的要求搜索出所需要的信息，并按规定的格式进行整理，再返回给客户端，SELECT语句的完整结构如下：
SELECT statement ::=
< query_expression >
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[ ,...n ] ]
[ COMPUTE
{ { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ]
[ BY expression [ ,...n ] ]
]
[ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT }
[ , XMLDATA ]
[ , ELEMENTS ]
[ , BINARY base64 ]
} ]
[ OPTION ( < query_hint > [ ,...n ]) ]
< query expression > ::=
{ < query specification > | ( a< query expression > ) }
[ UNION [ ALL ] < query specification | ( < query expression > ) [...n ] ]
< query specification > ::=
SELECT [ ALL | DISTINCT ]
[ { TOP integer | TOP integer PERCENT } [ WITH TIES ] ]
< select_list >
[ INTO new_table ]
[ FROM { < table_source > } [ ,...n ] ]
[ WHERE < search_condition > ]
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
[ WITH { CUBE | ROLLUP } ]
]
[HAVING <search_condition>]
上述语法较复杂，这里只须掌握SELECT语句的一部分，就可以完成对数据库的查询操作，SELECT语句中的主要子句归纳如下：
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ASC| DESC ]]
参数：
select_list：指定由查询返回的列。它是一个逗号分隔的表达式列表。每个表达式同时定义格式（数据类型和大小）和结果集列的数据来源。每个选择列表表达式通常是对从中获取数据的源表或视图的列的引用，但也可能是其它表达式，例如常量或T-SQL函数。在选择列表中使用 * 表达式指定返回源表中的所有列。
INTO new_table_name：创建新表并将查询行从查询插入新表中。new_table_name 指定新表的名称。
FROM table_list：指定从其中检索行的表。这些来源可能包括：基表、视图和链接表。FROM子句还可包含联接说明，该说明定义了 SQL Server用来在表之间进行导航的特定路径。FROM子句还用在DELETE和UPDATE 语句中以定义要修改的表。
WHERE search_conditions：WHERE子句指定用于限制返回的行的搜索条件。WHERE 子句还用在 DELETE 和 UPDATE 语句中以定义目标表中要修改的行。
GROUP BY group_by_list：GROUP BY子句根据 group_by_list 列中的值将结果集分成组。例如，student 表在 “性别” 中有两个值。GROUP BY ShipVia 子句将结果集分成两组，每组对应于ShipVia 的一个值。
HAVING search_conditions：HAVING子句是指定组或聚合的搜索条件。逻辑上讲，HAVING 子句从中间结果集对行进行筛选，这些中间结果集是用 SELECT 语句中的 FROM、WHERE 或 GROUP BY 子句创建的。HAVING 子句通常与 GROUP BY 子句一起使用，尽管HAVING 子句前面不必有 GROUP BY 子句。
ORDER BY order_list [ ASC | DESC ]：ORDER BY 子句定义结果集中的行排列的顺序。order_list 指定组成排序列表的结果集的列。ASC 和 DESC 关键字用于指定行是按升序还是按降序排序。ORDER BY 之所以重要，是因为关系理论规定除非已经指定 ORDER BY，否则不能假设结果集中的行带有任何序列。如果结果集行的顺序对于SELECT 语句来说很重要，那么在该语句中就必须使用ORDER BY子句。
例如显示course表的所有记录。在查询分析器中输入SQL语句如下：
use student
select *
from course
SELECT子句指定要查询的列。这些列通常被一个选择列表指定，选择列表是中间用逗号分开的选择项列表。选择项可以是字段名、常量、SQL表达式。下面是SELECT子句的语法：
SELECT [ ALL | DISTINCT ]
[ TOP n [ PERCENT ] [ WITH TIES ] ]
< select_list >
< select_list > ::=
{ *
| { table_name | view_name | table_alias }.*
| { column_name | expression | IDENTITYCOL | ROWGUIDCOL }
[ [ AS ] column_alias ]
| column_alias = expression
}
[ ,...n ]
参数：
ALL：指定在结果集中可以显示重复行。ALL是默认设置。
DISTINCT：去掉重复记录。
TOP n [PERCENT]：指定只从查询结果集中输出前n行。n是介于0和4294967295之间的整数。如果还指定了PERCENT，则只从结果集中输出前百分之n行。当指定时带PERCENT时，n必须是介于0~00之间的整数。如果查询包含ORDER BY子句，将输出由ORDER BY子句排序的前n行（或前百分之n行）。如果查询没有ORDER BY子句，行的顺序任意。
WITH TIES：指定从基本结果集中返回附加的行，这些行包含与出现在TOP n (PERCENT)行最后的ORDER BY列中的值相同的值。如果指定了ORDER BY子句，则只能指定TOP ...WITH TIES。
select_list ：为结果集选择的列。选择列表是以逗号分隔的一系列表达式。
* 指定在FROM子句内返回表和视图内的所有列。列按FROM子句所指定的表或视图，按他们在表或视图中的顺序返回，table_name | view_name | table_alias.*将*的作用域限制为指定的表或视图。
column_name：是要返回的列名。限定column_name以避免二义性引用，当FROM子句中的两个表内有包含重复名的列时会出现这种情况。
expression：是列名、常量、函数以及由运算符连接的列名、常量和函数的任意组合，或者是子查询。
IDENTITYCOL：返回标识列。
ROWGUIDCOL：返回行全局惟一标识列。如果在FROM子句中有多个表具有ROWGUIDCOL属性，则必须用特定的表名（如T1.ROWGUIDCOL）限定 ROWGUIDCOL。
column_alias：是查询结果集内替换列名的可选名。别名还可用于为表达式结果指定名称。
2．FROM子句
对于每个SELECT子句，FROM子句是强制性的。FROM子句主要用来指定检索数据的来源，数据的来源可以是若干个表或视图，数据表之间或视图名之间用逗号分隔。下面是FROM子句的语法：
[ FROM { < table_source > } [ ,...n ] ]
< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
| view_name [ [ AS ] table_alias ] [ WITH ( < view_hint > [ ,...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
| user_defined_function [ [ AS ] table_alias ]
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| < joined_table >
< joined_table > ::=
< table_source > < join_type > < table_source > ON < search_condition >
| < table_source > CROSS JOIN < table_source >
| [ ( ] < joined_table > [ ) ]
< join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]
[ < join_hint > ]
JOIN
参数说明：
table_source ：指定要在 Transact-SQL语句中使用的表或视图（带或不带别名均可）。可在语句中使用多达 256 个表。可将table 变量指定为表源。如果表或视图存在于同一台运行Microsoft® SQL Server™的计算机的其它数据库中，应按格式 database.owner.object_name 使用完全合法的名称。如果表或视图存在于本地服务器之外的一台链接的服务器上，应按以下格式使用由四部分组成的名称：linked_server.catalog.schema.object。如果由四部分构造的表（或视图）名称使用OPENDATASOURCE 函数作为名称中的服务器部分，则该名称也可用于指定表源。
table_name：表名。FROM 关键字之后的表和视图的顺序并不影响返回的结果集。当 FROM子句中出现重复名称时将报告错误。
[AS] table_alias：table_name、view_name 或 rowset_function 的别名，为方便起见而使用，或用于区分自联接或子查询中的表或视图。别名通常是一个缩短了的表名，用于在联接中引用表中的特定列。如果联接中的多个表中有相同名称的列存在，SQL Server 要求必须使用表名或别名来限定列名。（如果定义了别名则不能使用表名）。
WITH ( < table_hint > )：指定表扫描、查询优化器要使用的一个或多个索引或查询优化器要对此表、此语句使用的锁定方法。
view_name：是视图名称。视图是一个"虚拟表"，通常创建为一个或多个表中列的子集。
WITH ( < view_hint > )：指定索引视图扫描。默认情况下，视图在查询优化器处理查询之前展开。视图提示只能用在 SELECT 语句中，而不能用于 UPDATE、DELETE 或 INSERT 语句。
rowset_function：指定一个行集函数，该函数返回可替代表引用的对象。
user_defined_function：指定用户定义的函数，该函数返回一个表。如果用户定义的函数是一个内置的用户定义函数，则前面必须加两个冒号，如：FROM fn_listextendedproperty：derived_table是从数据库中检索行的子查询。derived_table 用作对外部查询的输入。
column_alias：替换结果集内列名的可选别名。在选择列表中放入每个列的一个别名，并将整个列别名列表用圆括号括起来。
joined_table：由两个或更多表的积组成的结果集。对于多个 CROSS 联接，请使用圆括号来更改联接的自然顺序。
join_type：指定联接操作的类型。
INNER：指定返回每对匹配的行。废弃两个表中不匹配的行。如果未指定联接类型，则这是默认设置。
FULL [OUTER]：指定在结果集中包含左表或右表中不满足联接条件的行，并将对应于另一个表的输出列设为NULL。这是对通常由 INNER JOIN 返回的所有行的补充。说明按此处指定的方法指定外联接或在 WHERE 子句中使用旧式非标准的 *= 和 =* 运算符都是可行的。不能在同一语句中同时使用这两种方法。
LEFT [OUTER]：指定在结果集中包含左表中所有不满足联接条件的行，且在由内联接返回所有的行之外，将另外一个表的输出列设为NULL。
RIGHT [OUTER]：指定在结果集中包含右表中所有不满足联接条件的行，且在由内联接返回的所有行之外，将与另外一个表对应的输出列设为NULL。
join_hint：指定SQL Server查询优化器为在查询的FROM子句中指定的每个联接使用一个联接提示或执行算法。
JOIN：指明所指定的联接操作应在给定的表或视图之间执行。
ON <search_condition>：指定联接所基于的条件。尽管经常使用列和比较运算符，但此条件可指定任何谓词。
（1）从表和视图中选择
例如：查询course表的信息，在查询分析器中输入SQL语句如下：
use student
select * from course
例如：查询用户建的视图newview的信息，在查询分析器中输入SQL语句如下：
select * from newview
其中，course是表的名称；newview是视图的名称。
（2）列以表的名称作为前缀
由于FROM子句指定的数据源可以是多个表，列名可能会有重复的，所以对不同的子句中的列以表的名称作为前缀也使它变得更为直观。这样就可以帮助消除当两个或更多个表有相同的列名时所引起的混乱。
例如：查询显示student表和grade表中的几个列，在查询分析器中输入SQL语句如下：
use student
select students.学号,
studnet.姓名,grade.学号,grade.课程成绩
from students,grade
列以表的名称作为前缀主要用于多个表连接。为了直观，即使有一个表，其中的列也可以用表的名称作为前缀。
例如：查询course表中所有列的信息，在查询分析器中输入SQL语句如下：
use student
select course.* from course
（3）在一个FROM子句中使用子查询
在FROM子句中使用一个子查询的结果作为查询的源表，同时也可以给源表起一个别名。
例如：在students数据库中，查询course表作为子查询并起别名为a，然后再查询别名是a的表中某些列。在查询分析器中输入SQL语句如下：
use student
select a.课程代号,a.课程内容 from (select * from course ) a
3．WHERE子句
WHERE子句是用来选取需要检索的记录。因为一个表通常会有数千条记录，在查询结果中，用户仅需其中的一部分记录，这时需要使用WHERE子句指定一系列的查询条件。下面是WHERE子句最简单的语法：
SELECT<字段列表>
FROM<表名>
WHERE<条件表达式>
例如：在students表中，查询年龄>20的学生，在查询分析器中输入SQL语句如下：
use student
select *
from students
where 年龄>20
为了实现许多不同种类的查询，WHERE子句提供了丰富的搜索条件，下面总结了5个基本的搜索条件。
① 比较运算符（如=、<>、<和>）。
② 范围说明（BETWEEN 和 NOT BETWEEN）。
③ 可选值列表（IN和NOT IN）。
④ 模式匹配（LIKE和NOT LIKE）。
⑤ 上述条件的逻辑组合（AND、OR、NOT）。
（1）比较查询条件
比较查询条件由比较运算符连接表达式组成，系统将根据该查询条件的真假来决定某一条记录是否满足该查询条件，只有满足该查询条件的记录才会出现在最终的结果集中。SQL Server比较运算符如表4.1所示 。
表4.1 比较运算符及其说明
|
运算符 |
说明 |
|
= |
等于 |
|
> |
大于 |
|
< |
小于 |
|
>= |
大于等于 |
|
<= |
小于等于 |
|
!> |
不大于 |
|
!< |
不小于 |
|
<>或!= |
不等于 |
例如：在grage表中，查询“课程成绩”大于90分的，在查询分析器中输入SQL语句如下：
use student
select *
from grade
where 课程成绩>90
例如：在grade表中，查询“课程成绩”小于等于90分的，SQL语句如下：
use student
select * from grade
where 课程成绩<=90
例如：在students表中，查询“年龄”在20~22之间（包括20和22）的所有学生。
use student
select * from students
where 年龄>=20 and 年龄<=22
例如：在students表中，查询“年龄”不大于20~22之间的所有学生。SQL语句如下：
use student
select * from students where 年龄<20 or 年龄>22
例如：在students表中，查询“年龄”不小于20的所有学生。SQL语句如下：
use student
select * from students where 年龄 !<20
换一种写法。查询年龄不小于20的所有学生。SQL语句如下：
use student
select * from students where 年龄>=20
例如：在students表中，查询年龄不等于20的所有学生。SQL语句如下：
use student
select * from students where 年龄!=20
注意：搜索满足条件的记录行，要比消除所有不满足条件的记录行快得多，所以，将否定的WHERE条件改写为肯定的条件将会提高性能，这是一个必须记住的准则。
（2）范围查询条件
需要返回某一个数据值是否位于两个给定的值之间，读者可以使用范围条件进行检索。通常使用BETWEEN…AND和NOT…BETWEEN…AND来指定范围条件。
使用 BETWEEN AND查询条件时，指定的第一个值必须小于第二个值。因为BETWEEN…AND实质是查询条件“大于等于第一个值，并且小于等于第二个值”的简写形式。即BETWEEN…AND要包括两端的值，等价于比较运算符（>=...<=）。
例如：在students表中，查询年龄在20~22之间的所有学生。
在查询分析器中输入的SQL语句如下：
use student
select *
from students
where 年龄 between 20 and 22
而NOT BETWEEN AND语句返回某个数据值在两个指定值的范围以外的，但并不包括两个指定的值。
例如：在students表中，查询年龄不在20~22之间的所有学生。
在查询分析中输入的SQL语句如下：
use student
select *
from students
where 年龄 not between 20 and 22
（3）列表查询条件
当测试一个数据值是否匹配一组目标值中的一个时，通常使用IN关键字来指定列表搜索条件。IN关键字的格式是IN（目标值1，目标值2，目标值3，…），目标值的项目之间必须使用逗号分隔,并且括在括号中。
例如：在course表中，查询“课程编号”为k01,k03和k04的课程信息。
在查询分析器中输入SQL语句如下：
use student
select *
from course
where 课程代号 in ('MR01','MR03', 'MR04')
IN运算符可以与NOT配合使用排除特定的行。测试一个数据值是否不匹配任何目标值。
例如：在course表中，课程代号不是k01、k03和k04的。
在查询分器中输入的SQL语句如下：
use student
select *
from course
where 课程代号 not in ('MR01','MR03', 'MR04')
（4）模式查询条件
模式查询条件是用来返回符合某种匹配格式的所有记录，通常使用LIKE或NOT LIKE关键字来指定模式查询条件。LIKE查询条件需要使用通配符在字符串内查找指定的模式，所以读者需要了解通配符及其含义。通配符的含义如表4.2所示。
表4.2 LIKE关键字中的通配符及其含义
|
通配符 |
说明 |
|
% |
由零个或更多字符组成的任意字符串 |
|
_ |
任意单个字符 |
|
[ ] |
用于指定范围，例如[A~F]，表示A到F范围内的任何单个字符 |
|
[^ ] |
表示指定范围之外的，例如[ ^ A~F]范围以外的任何单个字符 |
（1）“%”通配符
“%”通配符能匹配0个或更多个字符的任意长度的字符串。
在SQL Server语句中，可以在查询条件的任意位置放置一个“%”符号来代表任意长度的字符串。在设置查询条件时，也可以放置两个“%”，当最好不要连续出现两个“%”符号。
（2）“_”通配符
“_”号表示任意单个字符，该符号只能匹配一个字符，利用“_”号可以作为通配符组成匹配模式进行查询。
“_”符号可以放在查询条件的任意位置，且只能代表一个字符。
（3）“[ ]”通配符
在模式查询中可以使用“[ ]”符号来查询一定范围内的数据。“[ ]”符号用于表示一定范围内的任意单个字符，它包括两端数据。
例如：在students表中，查询电话号码以'3451'结尾并且开头数字位于1~5之间的学生信息。
（4）“[^ ]”通配符
在模式查询中可以使用“[^ ]”符号来查询不在指定范围内的数据。“[^ ]”符号用于表示不在某范围内的任意单个字符，它包括两端数据。
例如：在students表中，查询电话号码以'3451'结尾，但不以2开头的学生信息。
在查询分析器中输入的SQL语句如下：
use student
select *
from students
where 联系方式 like '[^2]3451'
NOT LIKE的含义与LIKE关键字正好相反，查询结果将返回不符合匹配模式查询。
例如：查询不姓“李”的学生信息。SQL语句如下：
use student
select * from students where 姓名 not like '李%'
例如：查询除了名字是两个字的姓“李”的其他同学信息。SQL语句如下：
use student
select * from students where 姓名 not like '李_'
例如：查询除了电话号码以'3451'结尾并且开头数字位于1~5之间的其他的学生信息。SQL语句如下：
use student
select * from students where 联系方式 not like '[1-5]3451'
例如：查询电话号码不符合如下条件的学生信息，这些条件是电话号码是以'3451'结尾，但不以2开头的。SQL语句如下：
use student
select * from students where 联系方式 not like '[^2]3451'
（5）复合搜索条件（AND、OR 和NOT）
如果读者想把前面讲过的几个单一条件组合成一个复合条件，这就需要使用逻辑运算符AND、OR 和NOT，才能完成复合条件查询。使用逻辑运算符时，遵循的指导原则
① 使用AND返回满足所有条件的行。
② 使用OR返回满足任一条件的行。
③ 用NOT返回不满足表达式的行。
就像数据运算符乘和除一样，它们之间是具有优先级顺序的：NOT优先级最高，AND次之，OR的优先级最低。
例如：用OR进行查询。查询学号是“B001”或者是"B002"的学生信息。SQL语句如下：
use student
select * from students where 学号='B001' or 学号='B002'
例如：用AND进行查询。查询性别是女并且年龄大于21岁的学生信息。SQL语句如下：
use student
select * from students where 性别='女' and 年龄>21
下面用AND和OR结合进行查询。
例如：在students表中，要查询年龄大于20的女生或者年龄大于22的男生的信息。
在查询分析器中输入SQL语句如下：
use student
select *
from students
where 年龄 > 20 and 性别='女' or 年龄>22 and 性别='男'
使用逻辑关键字AND、OR、NOT和括号把搜索条件分组，可以构建非常复杂的搜索条件。
例如：在student表中，查询年龄大于20的女生或者年龄大于22的男生，并且电话号码都是以‘1~3’打头、以‘3451’结尾的学生信息。
在查询分析器中输入的SQL语句如下：
use student
select *
from students
where (年龄>20 and 性别='女' or 年龄>22 and 性别='男') and 联系方式 like '[1-3]3451'
4．其他子句
① ORDER BY子句
对于表格比较小，不用ORDER BY子句，查询结果会按照在表格中的顺序排列的。但对于表格比较大的，则必须使用ORDER BY子句，方便查看查询结果。
ORDER BY子句由关键字ORDER BY 后跟一个用逗号分开的排序列表组成。
语法：
[ ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n ] ]
参数：
order_by_expression：指定要排序的列。可以将排序列指定为列名或列的别名（可由表名或视图名限定）和表达式，或者指定为代表选择列表内的名称、别名或表达式的位置的负整数。可指定多个排序列。ORDER BY 子句中的排序列序列定义排序结果集的结构。
ORDER BY：子句可包括未出现在此选择列表中的项目。然而，如果指定SELECT DISTINCT，或者如果 SELECT 语句包含 UNION 运算符，则排序列必定出现在选择列表中。此外，当 SELECT 语句包含 UNION 运算符时，列名或列的别名必须是在第一选择列表内指定的列名或列的别名。
ASC：指定按递增顺序，从低到高对指定列中的值进行排序。默认就是递增顺序。
DESC：指定按递减顺序，从高到低对指定列中的值进行排序。
例如：在grade表中，按照学生的“课程成绩”升序显示。
SQL语句如下所示：
use student
select * from grade
order by 课程成绩
查询结果若以降序排序，必须在列名后指定关键字的DESC。默认是升序排序。
例如在students表中，按照学生的“年龄”降序显示。SQL语句如下：
use student
select * from students
order by 年龄 desc
ORDER BY子句会根据查询结果中的一个列或多个列对查询结果进行排序。第一个排序项是主要的排序依据，其次那些是次要的排序依据。
例如：在grade表中，按照学生的“课程成绩”升序排列，然后再按照“学期”降序排序。SQL语句如下：
use student
select * from grade order by 课程成绩,学期 desc
在ORDER BY 列表中不允许使用子查询、聚合表达式或常量表达式。但是，用户可以在选择列表为聚合表达式指定的一个名称，然后在ORDER BY 子句中引用这个指定的名称。
例如：在grade表中，按照学生的平均成绩排序。SQL语句如下：
use student
select 课程代号,avg(课程成绩) as 平均成绩 from grade
group by 课程代号 order by 平均成绩
说明：在ORDER BY 子句中不能使用数据类型是ntext、text和image列。
② GROUP BY子句
GROUP BY子句可以将表的行划分为不同的组。分别总结每个组，这样就可以控制想要看见的详细信息的级别。
语法：
[ GROUP BY [ ALL ] group_by_expression[ ,...n ]
[ WITH { CUBE | ROLLUP } ] ]
参数：
ALL：包含所有组和结果集，甚至包含那些任何行都不满足 WHERE 子句指定的搜索条件的组和结果集。如果指定了 ALL，将对组中不满足搜索条件的汇总列返回空值。不能用 CUBE 或 ROLLUP 运算符指定 ALL。如果访问远程表的查询中有 WHERE 子句，则不支持GROUP BY ALL操作。
group_by_expression：是对其执行分组的表达式。group_by_expression也称为分组列。group_by_expression 可以是列或引用列的非聚合表达式。在选择列表内定义的列的别名不能用于指定分组列。对于不包含 CUBE 或 ROLLUP 的 GROUP BY 子句，group_by_expression 的项数受查询所涉及的 GROUP BY 列的大小、聚合列和聚合值的限制。该限制从 8,060 字节的限制开始，对保存中间查询结果所需的中间级工作表有 8,060 字节的限制。如果指定了 CUBE 或 ROLLUP，则最多只能有10个分组表达式。
CUBE：指定在结果集内不仅包含由 GROUP BY 提供的正常行，还包含汇总行。在结果集内返回每个可能的组和子组组合的 GROUP BY 汇总行。GROUP BY 汇总行在结果中显示为 NULL ，但可用来表示所有值。使用 GROUPING 函数确定结果集内的空值是否是 GROUP BY 汇总值。结果集内的汇总行数取决于GROUP BY 子句内包含的列数。GROUP BY 子句中的每个操作数（列）绑定在分组 NULL 下，并且分组适用于所有其它操作数（列）。由于 CUBE 返回每个可能的组和子组组合，因此不论指定分组列时所使用的是什么顺序，行数都相同。
ROLLUP：指定在结果集内不仅包含由 GROUP BY 提供的正常行，还包含汇总行。按层次结构顺序，从组内的最低级别到最高级别汇总组。组的层次结构取决于指定分组列时所使用的顺序。更改分组列的顺序会影响在结果集内生成的行数。
注意：
◎ 在SELECT子句的字段列表中，除了聚集函数外，其他所出现的字段一定要GROUP BY子句中有定义才行。例如“GROUP BY A，B”，那么“SELECT SUM（A），C”就有问题，因为C不在GROUP BY中，但是SUM（A）还是可以的。
◎ SELECT子句的字段列表中不一定要有聚集函数，但至少要用到GROUP BY子句列表中的一个项目。例如“GROUP BY A，B，C”，则“SELECT A”是可以的。
◎ 在SQL Server中text、ntext和image数据类型的字段不能作为GROUP BY子句的分组依据。
◎ GROUP BY子句不能使用字段别名。
GROUP BY子句可以基于指定某一列的值将数据集合划分为多个分组，同一组内所有记录在分组属性上具有相同值。
例如：把students表按照“性别”这个单列进行分组。
SQL语句如下所示：
Use student
select 性别
from students
group by 性别
但仍然要强调SELECT子句必须与GROUP BY后的子句或者是分组函数列相一致。
例如：由于下列查询中“姓名”列即不包含在GROUP BY子句中，也不包含在分组函数中，所以是错误的。错误的SQL语句如下：
select 姓名,性别
from student group by 性别
例如：在grade表中，按学期分组查询。正确的SQL语句如下：
select 学期
from grade group by 学期
GROUP BY子句可以基于指定多列的值将数据集合划分为多个分组。
例如：在students表中，按照“性别”和“年龄”列进行分组。
SQL语句如下所示：
Use student
select 性别,年龄
from student
group by 性别,年龄
在students表中，首先按照性别分组，然后再按照年龄分组，再举一个例子。
例如：在grade表中，按照“学号”和“课程代号”列进行分组。SQL语句如下：
use student
select 学号,课程代号
from grade group by 学号,课程代号
按多列进行分组时有NULL组的是如何处理的？当表按多列进行分组时有NULL组，这时NULL被作为一个特定值处理，就像其他任何值一样。也就是说，如果在某个分组列中存在两个NULL，则可以好像它们有相同的值那样处理它们并将它们放在相同的组中。
例如：在grade表中，按“学期”和“课程代号”列进行分组。
SQL语句如下所示：
Use student
select 学期,课程代号
from grade
group by 学期,课程代号
GROUP BY子句是经常与聚集函数一起使用。如果SELECT子句中包含聚集函数，则计算每组的汇总值，当用户指定GROUP BY时，选择列表中任一非聚集表达式内的所有列都应包含在GROUP BY列表中，或者GROUP BY表达式必须与选择列表表达式完全匹配
例如：在students表中，分别求男女生的平均年龄。
SQL语句如下所示：
Use student
select 性别,avg(年龄) as 平均年龄
from students
group by 性别
例如：在students表中，分别求有多少个男生和女生。SQL语句如下：
use student
select 性别,count(性别) as 人数
from students group by 性别
HAVING子句对GROUP BY子句选择出来的结果进行再次筛选，最后输出符合HAVING子句中条件的记录。HAVING子句的语法与WHERE子句的语法相类似，惟一不同的是HAVING子句中可以包含聚合函数。
语法：
[HAVING <search_condition>]
参数：
search_condition：指定组或聚合应满足的搜索条件。当 HAVING 与 GROUP BY ALL 一起使用时，HAVING 子句替代 ALL。
例如：在students表中，按性别分组求平均年龄，并且查询其平均年龄大于21的学生信息。
SQL语句如下所示：
Use student
select avg(年龄), 性别
from students
group by 性别
having avg(年龄)>21
例如：在grade表中，按学期分组求平均成绩，并且查询平均成绩大于93的课程信息。
SQL语句如下所示：
Use student
select 学期,avg(课程成绩) as 平均成绩
from grade
group by 学期
having avg(课程成绩)>93
说明：HAVING查询条件是在进行分组操作之后才应用的；在 HAVING 子句中不能使用 text、image 和 ntext 数据类型。
统计结果并不能保证结果集内记录按一定顺序排列，如果使用ORDER BY子句，就可以使结果集中的结果按一定的顺序（升序、降序）排序。
例如：在student表中，按“性别”和“年龄”列分组，并按“年龄”列降序排序。
SQL语句如下所示：
Use student
select 性别,年龄
from students
group by 性别,年龄
order by 年龄 desc
例如：在grade表中，按“学号”分组，并按课程的平均成绩升序排序。SQL语句如下：
use student
select 学号,avg(课程成绩) as 平均成绩
from grade group by 学号 order by 平均成绩
③ Compute 和Compute by子句主要用来汇总数据。
语法：
[ COMPUTE
{ { AVG | COUNT | MAX | MIN | STDEV | STDEVP
| VAR | VARP | SUM }
( expression ) } [ ,...n ]
[ BY expression [ ,...n ] ]
参数说明：
AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM
指定要执行的聚合。下面是COMPUTE 子句使用的行聚合函数：
AVG：数字表达式中所有值的平均值
COUNT：选定的行数
MAX：表达式中的最高值
MIN：表达式中的最低值
STDEV：表达式中所有值的统计标准偏差
STDEVP：表达式中所有值的填充统计标准偏差
SUM：数字表达式中所有值的和
VAR：表达式中所有值的统计方差
VARP：表达式中所有值的填充统计方差
Expression：表达式，如对其执行计算的列名。expression 必须出现在选择列表中，并且必须将其指定为与选择列表中的某个表达式完全一样。在 expression 内不能使用在选择列表中指定的列的别名。
BY expression：在结果集内生成控制中断和分类汇总。expression 是 order_by_expression 在相关 ORDER BY 子句中的精确复本。一般情况下，这是列名或列的别名。可指定多个表达式。在 BY 后列出多个表达式可将一个组分成子组并在每个分组级别上应用聚合函数。
例如：在studnet表中，求“年龄”字段的平均值。
SQL语句如下所示：
Use student
select *
from student
compute avg(年龄)
说明：在 COMPUTE 或 COMPUTE BY 子句中，不能指定ntext、text和image数据类型。
下面是COMPUTE和COMPUTE BY两个子句的区别。
◎ 没有BY时，查询结果将包含两个结果集。第一个结果集将是包含选择列表中所有字段的明细记录。第二个结果集只有一条记录，这条记录只包含COMPUTE子句中所指定的汇总函数的合计。
◎ 有BY时，查询结果将根据BY后的字段名称进行分组，并且为每个符合SELECT语句查询条件的组返回两个结果集。第一个结果集是明细记录集，包含结果集中将包含选择列表中所有的字段信息。第二个结果集是只包含一条记录，这条记录的内容只有该组的COMPUTE子句中所指定的汇总函数的小计。
例如：在students表中，分别求男生和女生的平均年龄。
SQL语句如下所示：
Use student
select *
from students
order by 性别
compute avg(年龄) by 性别
注意：如果使用COMPUTE BY，则必须也使用ORDER BY子句。表达式必须与在 ORDER BY 后列出的子句相同或是其子集，并且必须按相同的序列。
④ OPTION子句
指定应在整个查询中使用所指定的查询提示。每个查询提示只能指定一次，但允许指定多个查询提示。用该语句只可能指定一个OPTION子句。查询提示影响语句中的所有运算符。如果主查询中涉及 UNION，则只有涉及 UNION 运算符的最后一个查询可以有 OPTION 子句。如果一个或多个查询提示导致查询优化器不生成有效计划，则产生8622号错误。
语法：
[ OPTION ( < query_hint > [ ,...n ) ]
< query_hint > ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| {LOOP | MERGE | HASH } JOIN
| FAST number_rows
| FORCE ORDER
| MAXDOP number
| ROBUST PLAN
| KEEP PLAN
| KEEPFIXED PLAN
| EXPAND VIEWS
}
参数说明：
{ HASH | ORDER }GROUP：指定在 GROUP BY、DISTINCT 或 COMPUTE 查询子句中所描述的聚合应使用哈希操作或排列。
{ MERGE | HASH | CONCAT } UNION：指定由合并、哈希或串联 UNION 集合执行所有 UNION 运算。如果指定了不止一个 UNION 提示，查询优化器就会从这些指定的提示中选择开销最少的策略。
{ LOOP | MERGE | HASH } JOIN：指定在整个查询中所有的联接操作由循环联接、合并联接或哈希联接来完成。如果指定了多个联接提示，则优化器从允许的联接策略中选择最便宜的联接策略。如果在同一个查询中，还为一对特定的表指定了联接提示，则虽然仍须遵守查询提示，但该联接提示将优先联接这两个表。因此，为这对表指定的联接提示可能只限制选择查询提示中允许的联接方法。
FAST number_rows：指定对查询进行优化，以便快速检索第一个 number_rows（非负整数）。在第一个 number_rows 返回后，查询继续进行并生成完整的结果集。
FORCE ORDER：指定在查询优化过程中保持由查询语法表示的联接顺序。
MAXDOP number：只对指定了 sp_configure 的 max degree of parallelism 配置选项的查询替代该选项。当使用 MAXDOP查询提示时，所有和 max degree of parallelism 配置选项一起使用的语义规则均适用。
ROBUST PLAN：强制查询优化器以性能为代价，使用对最大可能的行大小有效的计划。处理查询时，中间级表和运算符可能需要存储和处理比输入行宽的行。在有些情况下，行可能很宽，以致某个运算符无法处理行。如果发生这种情况，SQL Server 将在查询执行过程中生成错误。通过使用 ROBUST PLAN，可以指示查询优化器不考虑可能会遇到该问题的查询计划。
KEEP PLAN：强制查询优化器对查询放宽估计的重新编译阈值。估计的重新编译阈值是一个点，基于该点当对表的索引列更改（更新、删除或插入）达到估计的数字时自动重新编译查询。指定 KEEP PLAN 将确保当表有多个更新时不会频繁地对查询进行重新编译。
KEEPFIXED PLAN：强制查询优化器不因统计中的更改或索引列（更新、删除或插入）而重新编译查询。指定 KEEPFIXED PLAN 将确保仅当更改基础表的架构或在那些表上执行 sp_recompile 时才重新编译查询。
EXPAND VIEWS：指定展开索引视图，而且查询优化器不将任何索引视图看作是查询中任何部分的替代。（当视图名称由查询文本中的视图定义替换时，视图将展开。）实际上，该查询提示不允许在查询计划中直接使用索引视图和直接在索引视图上使用索引。只有在查询的 SELECT 部分中直接引用视图，而且指定 WITH (NOEXPAND)或 WITH（NOEXPAND、INDEX( index_val [ ,...n ] )），才会展开索引视图。
⑤ DISTINCT关键字
DISTINCT关键字主要用来从SELECT语句的结果集中去掉重复的记录。如果用户没有指定DISTINCT关键字，那么系统将返回所有符合条件的记录组成结果集，其中包括重复的记录。
DISTINCT关键字可以找出一个列中的所有值，并使每个值只显示一次。
例如：显示grade表中学号的不同值。
SQL语句如下所示：
Use student
select distinct 学号
from grade
说明：用SELECT DISTINCT关键字处理null值与其它数据一样。多个null值只显示一个。
对多个列使用DISTINCT关键字时，查询结果只显示每个有效组合的一个例子。即结果表中没有完全相同的两个行。
例如：显示grade表中学号和课程代号的不同值。
SQL语句如下所示：
Use student
select distinct 学号,课程代号
from grade
一个表由于没有主键可能在操作过程中有许多重复行，这样导致查询数据时不必要的麻烦，在SQL SERVER中还不允许修改重复行数据。下面使用SELECT DISTINCT创建一个没有重复行的新表，再删除旧表的方式来删除重复行。
例如：删除有重复行的grade表。首先创建一个没有重复值的表。SQL语句如下：
use student
select distinct *
into ls
from grade
然后再删除旧表。SQL语句如下：
drop table grade
最后把临时表的名称改成旧表的名称grade。SQL语句如下：
EXEC sp_rename 'ls', 'grade'
注意：
在SELECT列表中只能使用一次DISTINCT关键字，不要将查询字段放在DISTINCT关键字前面，或在其后添加逗号。如下面的语句将提示出错信息。
Select state,distinct city from authors
正确的语句应为：
select distinct state,city from authors
如果省略了DISTINCT关键字，查询结果中不会消除重复纪录。也可以指定ALL关键字来明确指示要保留重复纪录，但是这是不必要的，因为这是默认的行为。
DISTINCT关键字并不是指某一行，而是指不重复SELECT输出的所有列。这一点十分重要，其作用是防止相同的行出现在一个查询结果的输出中，而不是防止行中某一字段重复。
DISTINCT是SUM、AVG和COUNT函数的可选关键字。如果使用DISTINCT关键字，那么在计算机总和、平均值或计数之前，先消除重复的值。
⑥ AS关键字
AS关键字可以为查询结果的列指定别名。下面讲解在什么情况下使用AS关键字。
列名如果是英文的，查询结果不易查看，可以为其起个中文别名。
例如：pubs数据库中的pub_info图书信息表列名都是英文的，为“pub_id”图书编号列起个中文名。
SQL语句如下所示：
Use pubs
select pub_id as 图书编号, logo as 图标
from pub_info
说明：pubs数据库是SQL SERVER自带的数据库。
如果同时对多个表进行查询，结果表中出现相同的列名，容易引起混淆或者不能引用这些列只能为这些列起个别名。
例如：同时查询grade和students表。把两个表相同的列“学号”分别起了别名。
SQL语句如下所示：
Use student
select students.学号 as 学生编号 ,姓名,年龄,grade.学号 as 考生编号 ,课程成绩
from students,grade
当SELECT子句的选择列为表达式时，在查询结果中无法显示，只能为该表达式起个别名。
有的选择列的表达式是使用了聚集函数。
例如：求bookinfo表中“销售数量”列的平均值。
SQL语句如下所示：
Use student
select avg(销售数量) as 平均销售
from bookinfo
注意：字段别名可以使用在ORDER BY子句，但是不能用在WHERE、GROUP BY或HAVING子句中。
⑦ TOP关键字
在查询数据时，经常需要查询前若干条数据或最后若干条数据，这个时候就需要使用TOP关键字进行数据查询。
语法：
SELECT TOP n [PERCENT]
FROM table
WHERE
ORDER BY…
参数：
PERCENT：返回行的百分之n,而不是n行。
n：如果SELECT语句中没有ORDER BY子句，TOP n返回满足WHERE子句的前n条记录。如果子句中满足条件的记录少于n，那么仅返回这些记录。
例如：显示student表的前3条记录。
在查询分析器中输入SQL语句如下：
use student
select top 3 * from bookinfo
注意：
如果包含ORDER BY子句，TOP n返回满足查询的前n行，但不删除重复组，这样有可能输入大于n条的纪录。如果使用ORDER BY，TOP n返回前n条纪录，但是如果第n条后有与排序字段相同值的纪录，也将输出这些纪录。例如：如果有另外两条纪录有相同的值，得到的将不是n条纪录而是n+2条纪录。因为在使用ORDER BY时TOP并不删除重复的组。
按升序排列一般在ORDER BY子句后添加ASC谓词，也可省略，默认按升序排列。
TOP关键字还可以按百分比返回记录的行数。
例如：显示bookinfo表中的前6%的记录。
在查询分析器中输入SQL语句如下：
use student
select top 6 percent *
from bookinfo
TOP关键字可以显示结果记录的后几行，是用ORDER BY关键字降序排列实现的。
例如：显示bookinfo表中销售数量最多的书籍信息。
在查询分析器中输入SQL语句如下：
use student
select top 1 *
from bookinfo
order by 销售数量 desc
4.1.3 解决查询过程中的问题
1．有空格的字符型数据
当字符型列中有空格时，给查询该数据时带来了麻烦。有时不去掉空格，就查找不到该数据。
例如“ 明日 ”和“明日”就不是相等的两个字符串。SQL去空格用LTRIM（）和RTRIM（）函数。
（1）LTRIM函数
LTRIM函数用于删除字符或表达式左侧的空格。
语法：
LTRIM ( character_expression )
参数：
character_expression：是字符或二进制数据表达式。character_expression可以是常量、变量或列。character_expression必须是可以隐性转换为varchar的数据类型。否则，使用CAST显式转换 character_expression。
返回值：
数据类型为varchar的数据。
例如：使用LTRIM函数删除字符变量“ ing is a song very much“中的起始空格。结果如图4.15所示。
图4.15 删除起始空格之后得到的字符串
在查询分析器中输入SQL语句如下：
DECLARE @string varchar(50)
SET @string =' ing is a song very much'
SELECT 'lov' + LTRIM(@string)
（2）RTRIM（）函数
RTRIM函数用于截断所有尾随空格后返回一个字符串。
语法：
RTRIM ( character_expression )
参数：
character_expression：由字符数据组成的表达式。character_expression可以是常量、变量，也可以是字符或二进制数据的列。character_expression必须为可隐性转换为varchar的数据类型。否则请使用CAST函数显式转换character_expression。
返回值：
数据类型为varchar数据。
例如：显示如何使用RTRIM删除字符变量“LOVING “中的尾随空格。结果如图4.16所示。
图4.16 删除尾随空格之后得到的字符串
在查询分析器中输入SQL语句如下：
DECLARE @string varchar(60)
SET @string = 'LOVING '
SELECT RTRIM(@string)+'is a song very much'
例如：查询student1表的信息，同时把“姓名”列的空格去掉。下面是查询前和查询后的结果，如图4.17所示。
图4.17 查询去掉空格的students表的信息
在查询分析器中输入SQL语句如下：
use student
SELECT 姓名,LTRIM(姓名) AS 去除左面空格,
RTRIM(姓名) AS 去除右面空格,
LTRIM(RTRIM(姓名)) AS 去除左右空格
FROM student1
2．查询时间日期型的数据
（1）查询指定日期的数据
在SQL Server中，日期型常量和字符型常量一样，使用时都用一对引号。例如'1990-01-01'、''2007-01-01'。
例如：在student1表中，查询出生日期是“1984-03-10”的学生。下面是查询前和查询后的结果，如图4.18所示。
图4.18 查询出生日期是“1984-03-10”
在查询分析器中输入SQL语句如下：
use student
select *
from student1
where 出生日期='1984-03-10'
注意：在使用日期函数时，其日期值应在1753-9999年之间，这是SQL Server系统所能识别的日期范围，否则会出现错误。
（2）按月查询数据
当使用日期型数据进行查询时，经常按月份查询，用MONTH（）函数可以从日期型数据中提取月份数据。该函数的语法：
MONTH（date）
MONTH（）函数能够将日期时间表达式date中的月份返回，返回的月份是以数值１～12来表示，1代表一月、2代表二月…其余依此类推。
注意：日期时间表达式date必须是datetime或smalldatetime数据类型，值得注意的是，如果将date设置为0，SQL Server会将0视为1900年1月1日。
例如：在student1表中，查询3月份出生的学生信息。下面是查询前和查询后的结果，如图4.19所示。
图4.19 查询3月份出生的学生
在查询分析器中输入SQL语句如下：
use student
select *
from student1
where month(出生日期)='3'
（3）查询指定时间段的数据
要实现对指定日期时间段数据的查询，须在SQL语句中使用BETWEEN...AND。
例如：在student1表中，查询出生日期在“1984-01-10”和“1986-01-01”之间的学生信息。下面是查询前和查询后的结果，如图4.20所示。
图4.20 按时间段查询学生信息
在查询分析器中输入如下SQL语句。
use student
select *
from student1
where 出生日期 between '1984-01-01' and '1986-01-01'
posted on 2008-05-16 14:59 Harlan--- 阅读(1391) 评论(0) 编辑 收藏 举报
