SQL如何使用集合、分组、排序

 

1、集合函数

   集合函数可以对查询到的数据进行汇总。
   
   集合函数的语法：aggregate_function( [all|distinct] expression)
   
   expression表达式一般是指列。对列运行集合函数时，AS将忽略列中的空值，以使该集合函数有效。
但count(*)和count_big(*)除外，这两个函数包括空值。可选的distinct，只能用于sum,avg,count,count_big.
   
   集合函数包括：
   
1.1 sum([all|distinct] expression)
   
   计算表达式(列)中值的总和，如果加上distinct，则自动排除重复值，再求和，NULL值将会忽略
     
1.2 avg([all|distinct] expression)
   
   计算表达式(列)中所有值的平均值，加上distinct,则排除重复值，再求平均，忽略NULL值列
   
1.3 count([all|distinct] expression)
   
   计算表达式(列)的数量，但不将含有NULL值的列计算在内。加上distinct,则排除重复值，再求列的数量
   
1.4 count(*)
   
   计算所在表的所有行数，包括空值的行（这是可以包括空值的一例）
   
1.5 count_big([all|distinct] expression)
   
   得出表达式中非空值的数量，并以bigint类型返回值的数量，而count则以int类型返回数量
   
1.6 count_big(*)
   
   计算所在表的所有行数，包括空值的行，不过是以bigint类型返回行数
   
1.7 min(expression)
   
   返回表达式中的最小值,如表达式值是字符类型，通过比较其ascii值来比较大小.
   
1.8 max(expression)  
   
   返回表达式中的最大值,如表达式值是字符类型，通过比较其ascii值来比较大小.
   
1.9 ascii (美国信息互换标准码)

    单字节编码，即一字符=1字节。 
 常见ascii码的大小规则 0~9 < A-Z < a-z
    1)数字比字母小 ，如 '1' < 'a'
 2)大写字母小于小写字母，如 'A' < 'a'
 3)数字0比数字9小，如 '0'<'9'
 4)字母a[A]比g[G]小，并按A-Z顺序递增,如 'a' < 'd' , 'A' < 'G'
 5)同一个大写字母比小写字母小32，如 a - A = 32

2、将查询结果分组：group by 子句

   group by 可以将查询的输出分成若下组。可以按一个或多个列名进行分组。与集合函数一起使用时，
group by 检索每个组的计算结果，并返回多行记录

   group by 虽然可以不与集合函数一起用，但这样会限制了group by功能，且输出结果会造成混乱。
  

select * from book group by publisher //不带集合函数的group by 

   使用集合的group by 子句的汇总值，称为矢量集合，结果有多行记录。
   
  

select publiser,count(*) from book group by publisher 

   // 根据计算各个出版社的书的总数
   
   不能对text、unitext、image类型的列执行group by.
   
1) 使用group by对组进行嵌套

   在group by 子句后列出多个列，即对组进行嵌套（按多列进行分组）
   
  

select publisher,type,count(*) from book group by publisher,type 

   //将结果先按publisher分组，再按type分组
   
2) 空值NULL 与 group by

   如果分组的列包含一个NULL值，则NULL自成一组；如果包含多个NULL值，则这些NULL值自成一组。
   
3) where 与 group by

   where 可与group by 一起使用，adaptive server会先消除不满足where条件的行，再进行分组。
   
   即where子句是作用于分组之前的数据，记录要先满足where条件，再分组。
   
   where子句不能包含集合函数，如 where avg(count) > 100，这样是错误的。
   
3、选择数据组：having子句

   having子句用于限制group by 的结果。
   
   having 与 group by 一起使用时，是先分组，而后再将having限制应用到分组结果中。
   
1) where 与 group by 和 having 与 group by 的区别
   
   where 是 应用在group by分组之前的，having 应用在 group by分组之后。
   
   having 经常与集合函数一起使用，但where不能包含集合函数。
   
2) where、group by、having同时使用时

   先应用where得出基本结果集，再根据列来进行分组，集合函数计算每一组的值，再利用having过滤分组结果集
   

     select publisher , count(*) from book where price < 100 group by publisher having count(*) > 5 

   
   //先查找book价格小于100的记录，再按照出版社对书本的数目进行统计，但只显示出版5本书以上的出版社

3) 不带group by 的 having

   having子句不带group by时，将检索结果当成一个组，再应用having过滤。(当成where来使用?)
   
4、对查询结果进行排序：order by子句

   可以根椐一个或多个列对查询结果进行排序，可以是升序(asc)或降序(desc).默认为升序asc
   
1) 根据多列进行排序

  

order by id, type desc, name 

   
   //先对结果集按id进行升序排列，
     再对id列中重复值，按type进行降序排列，
  再对type列的重复值按name进行升序排序
  
2) 对含有NULL值的列进行排序

   adaptive server将首先对NULL进行排序，导到NULL排在最前面(按默认asc排列)
   
3) 根据列位置号进行排序

   列位置号，即列出现在select子句的顺序。
   
   如select id ,name ,type 其中id的列位置号为1，name为2，依此类推
   
  

select id,name,type from book order by 2 asc,1 desc,3 asc 

   //使用列位置号进行排序的例子
   
   不能对text,unitext,image列使用order by(原因是否是因这些数据类型太大了？)
   
4) union 与 order by 一起使用

     select name,id+1 from book1 union select name,id+2 from book2 order by 2 

   
   //对两个表拼接起来的结果集，按照列顺序号2进行升序排列。这个例子也表明列顺序号的一个用例
   

     select name,id+1 from book1 union select name,id+2 from book2 order by id+1 

   
   //按照第一个表的id+1对两表合集进行排序，不能order by id+2.(因为联合表的列名使用第一个表的列名)
     如想按照id+2进行排序，可以互换两表的位置。

5) 对group by 的结果集使用order by

   可以使用order by子句对group by的结果进行排序，只需将order by置于group by后.

     select type,avg(price) from book group by type order by avg(price) // order by子句可以使用集合 

6) 与select distinct 一起使用的order by 和 group by

   如果order by 或 group by 子句没有包含select 子句的列，则order by 或 group by与select distinct查询
一起时可以返回重复值。(distinct失效?)

select distinct age from usr order by lname [result] age --- NULL 21 27 21  

 //order by没有包括select子句的age列，则查询返回了重复值
 
4、汇总数据组：compute子句

   compute子句是Transact-SQL扩展。
   
   
   
5、组合查询：union运算符

   union运算符将两个或多个查询的结果组合成一个结果集。(是否T-SQL扩展)
   
1) 使用union 需要遵守的规则

   组合的表的列数必须相同，
   列的顺序必须一致，
   对应的列类型必须相同或可以相互转换，否则需要显式类型转换
   不能对text,image列使用union
   
2) 结果集中的重复行

   默认情况下，union从结果集中删除重复行。使用all选项，以包括重复行。
   
   x union all y // x,y的结果集，因使用all选项，所以保留了结果集中的重复行。
   
   x union all (y union z) //删除y,z结果集中的重复行，保留x与y,z合集中的重复行(从左到右顺序)
   
3) 结果集的列名

   默认情况下，选择左边的表的列名作为结果集的列名。可据此选择结果集的列名
   
   x union y union z //默认使用x表的列名作为结果集的列名
   
   如果要自定义结果集的列名，则在第一个查询中进行重定义。
   
4) 将union结果集保存到表

      select id ,name into result_table from book1 union ... union ... 

   // 只能在第一个select子句中使用into，将结果集保存到表。
      select into子句会创建表result_table，并将结果集复制到result_table。
   使用select into，需将'select into/bulkcopy/pllsort',设置为true
   
5) 使用insert into将结果集插入到已存在的表

   insert into book_result select * from book1 union select * from book2