第二天 -- 《2014-07-07 sql server》3——检索（排序、分组、类型转换、union联合）

一、下午  《02、空值处理》-- C#的null与数据库null不是一回事。

1、数据库中Null值表示“不知道”。如：

　　select null + 1;  --返回null，因为DBMS不知道null是个啥，所以加1之后也不知道是个啥。

不能用比较运算符（=   <  >  <=  >=  <> ）来比较。如：

　　select * from score where english = null;

　　select * from score where english <> null;

以上两句都不能返回任何结果。

 

2、SQL中可以使用is null 、is not null 来进行空值判断。如：

　　select * from score where english is null;

　　select * from score where english is not  null;

 

3、函数 ISNULL（测试表达式 ，替换值）可以用来根据测试值是不是NULL决定是否需要返回替换值。如：

--ISNULL如果发现对应的值是null值，则以指定的字符串文本进行替换
select StudentNo,StudentName,ISNULL(Email,'没有填写') from Student

 

二、下午  《03、数据排序》--  order by对结果集排序

1、排序不会修改结果集的数据，只是对结果集数据做重排

2、order by子句可包含未显示在select选择列表中的项（也就是可以按不显示在结果集中的列排序）。但是以下3种情况排序列必须显示在select选择列表中。

（1）检索已指定了select distinct 

（2）检索包含group by分组子句。

（3）select语句包含union运算符。

 

3、 ntext、text、image、xml列不能用于order by子句。因为它们不能用于比较。

 

三、下午  《04、什么是分组统计》--  《05、多字段分组》-- group by 对where筛选后的数据源分组

1、select检索的字段列表中，如果出现聚合函数和列并存。必须把字段放在group by或聚合函数中。如

select  sex ,  Count(*)  from student;         --报错：选择列表中的列'student.sex' 无效，因为该列没有包含在聚合函数或group by 子句中。

上句于情于理都是错误的，select sex 检索所有记录，得到所有记录（可能是多条）；而count(*)对记录条数计数，得到一个数值结果（一行）。两者根本无法放到一起。如下图

              

所以必须把sex字段放在group by或聚合函数中。见下文2和3节

 

2、把字段放在group by分组中。

如果使用group by 按sex分组后，select  sex 得到的是各个分组的sex（有几组就有几行），而count(*)是各个分组分别进行计数的（有几组就有几行）。两者就可以放到一起。如下图：

	sex
1	男	10
2	女	14

 

3、把字段放在聚合函数中。（几个聚合函数都只产生一行，所以可以放到一起）

--统计学生的平均年龄和总人数
select AVG(age), COUNT(*) from Student

总之：

与聚合函数一起出现在select后面进行查询的列，只有两种可能性：被聚合  或  被分组

--查询每一个班级男女生的人数
select ClassId,  sex,  COUNT(*) from  Student   group by  ClassId,  sex   order by  ClassId

 

4、用Having子句对分组进行筛选（有的组留下，有的组剔除）

（1）where子句中不能出现聚合函数作为条件（因为聚合函数是针对多行进行统计的，而where是一行一行筛选的，即where子句针对单行）

（2）where在select之前执行，所以select中定义的别名，where中不能使用。

（3）having:是对分组统计得到的结果集做筛选的。

 

四、下午  《06、查询语句的执行顺序》--《07、分组练习》

1、SQL检索中各子句的执行顺序（单表查询，葫芦娃7兄弟）

--5    7                                 1            2                       3                    4                 6
select top 1 ClassId,sex,COUNT(*) as cnt from Student where Email is not null group by ClassId,sex having COUNT(*)>3 order by  cnt

 

2、分组的总结

（1）什么时候需要分组--

　　看到要求里出现：每一个 、不同 、 各自 、 分别  等等字眼

（2）从哪张表中查询

　　要看最终要的数据是由哪些表的列（或列值计算后）组成的。

（3）先写好select   from  表  where  的大体框架，然后一点一点完善填入。

 

五、下午  《08、类型转换》-- CAST（）和 CONVERT（）函数

1、cast(源值  as  目标类型)  如：

print '我的分数是：'+cast(100 as char(3))

cast(‘100’  as  int)

cast(列  as  int)

 

2、convert(目标类型，源数据，[格式])     --格式是对日期值而言

print   '我的分数是：'  +   convert(char(3) ,   100)

--输出今天
print '今天是：'  +   getdate()  --输出失败，因为后者是个日期，加号会执行加法运算，前者是个无法转换成数值的字符串。
print '今天是：'   +   convert(  char(20),  getdate(),   112)         --112代表日期的转换格式，就是YYYYMMDD

 

 

 

六、下午  《09、一次性插入》-- union   、 insert select  、  select  into

1、联合union:是用来联合多个结果集的（数据源可以来自多个表或视图等）

--（1）.要求联合的多个结果集有相同数量的字段
--（2）.要求联合的多个结果集对应的列的类型需要一致，或数据内容能够隐式转换。

--（3）.当select语句包含union运算符时，列名或列的别名必须是在第一选择列表内指定的列名或别名。

如下：

select 100,   COUNT(*) from Student where Sex='男'
union
select '200', COUNT(*) from Student where Sex='女'  --OK的，‘200’字符串可以自动转换为整数，假如是‘abc123’那么执行时会报错，因为‘abc123’无法转换成整数。

2、union all

--union默认是去除重复值的,效率低， 是因为需要为你做是否重复的判断
--union all就是不去除重复。

 

3、使用union一次插入多条记录

--union还是可以去除重复记录，只有全部都使用union all才不考虑重复值
insert   into   Admin
select   'fasdf'  ,'fasdf' union all      --不去除重复
select   'fasdf'  ,'fasdf' union all　  --不去除重复
select   'adsfasdf'  ,'fsdfasdf'

 

4、插入结果集  insert  into..select...from...

可以将select查询的结果集insert into到指定的表中。表需要先存在。

insert into newtable select LoginPwd,StudentName,Sex,ClassId,Phone,Address,BornDate,Email,isDel from Student

 

 5、把数据复制到新建表  select...into... from...

可以将from数据源表中的select指定的列的数据into到新表中，新表是系统自行生成的，不能预先人为创建，也就不能先存在.新表中列的属性只保留标识列，其余都消失。

select * into newtable  from Student