数据库开发（三）T-SQL分组、函数

Order by

Order by语句一般要放到所有语句的后面，就是先让其他语句进行筛选，全部筛选完成后，最后排序一下。

表中的数据是集合，集合是没有顺序的。而Order by返回的数据是有顺序的，所以Order by以后返回的数据集合叫做“游标”。另外，order by后的数据不能作为另外一个查询的结果来使用。

select
    总和=(select ... from ... order by ...) --有序列表，不是集合，不能作为另外一个查询的结果使用

Group by

从学生表中查询出每个班的班级ID和班级人数。

select
    tsClassId as 班级Id,
    班级人数=count(*)
from TblStudent
group by tsClassId

统计出所有学生中，男同学与女同学的人数分别是多少。

select
    性别=tsgender,
    人数=count(*)
from tblStudent
group by tsgender

从学生表中查询出每个班的班级Id和班级中男同学的人数。

select
    班级Id=tsClassId,
    男同学人数=count(*)
from tblStudent
where tsgender='男'
group by tsClassId
--执行顺序，from，where，group by，select，先筛选，再分组

当使用了分组语句（group by）或者是聚合函数的时候，在select的查询列表中不能再包含其他的列名，除非该列同时也出现在了group by字句中，或者该列也包含在了某个聚合函数中。

select
    sum(tsage), --包含在sum聚合函数中
    性别=tsgender, --group by的列
    人数=count(*) --包含在count聚合函数中
from TblStudent
group by tsgender

Having

对分组以后的数据进行筛选，使用having。

where和having的区别

having与where都是对数据进行筛选。where是对分组前的每一行数据进行筛选，而having是对分组后的每一组数据进行筛选。

select
    tsClassId as 班级Id，
    班级人数=count(*)
from TblStudent
group by tsClassId
having count(*)>10 --不能使用“班级人数”列名，因为having在select之前执行
order by 班级人数 asc --order by在select之后执行，所以可以使用“班级人数”列名

注意：where后面不能使用聚合函数，但可以跟任何列。having后面只能跟分组的列，或者使用聚合函数。

SQL语句的执行顺序

以下步骤显示SELECT语句的处理顺序。

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE或WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

类型转换函数

CAST

基本语法：cast(表达式 as 数据类型)

--字符串转为整数
select 100.0+cast('1000' as int)

CONVERT

基本语法：convert(数据类型, 表达式)

--字符串转为整数
select 100.0+convert(int, '1000')
--整数转为字符串
select '编号：'+convert(char(1),1)
--日期转换为字符串
select convert(varchar(10),getdate(),120) --2019-05-05

Union联合

联合：将多个结果集合并成一个结果集。union（去除重复，相当于默认应用了distinct）、union all。

使用union all联合结果集。

select tsname,tsgender,tsage from TblStudent
union all
select fname,fgender,fage from MyStudent

使用union联合结果集。

select tsname,tsgender,tsage from TblStudent
union
select fname,fgender,fage from MyStudent

union和union all的区别

使用union和union all都能进行联合。使用union联合会去除重复，重新排列数据，而union all不会去除重复，也不会重新排列。
大多数情况下，联合的时候不需要去除重复，同时要保持数据的顺序，所以一般建议使用union all。

不同写法，查询成绩表中的最高分、最低分、平均分。

--聚合函数
select
    max(tmath) as 最高分
    min(tmath) as 最低分
    avg(tmath) as 平均分
from TblScore

--子查询
select
    最高分=(select max(tmath) from TblScore,
    最低分=(select min(tmath) from TblScore,
    平均分=(select avg(tmath) from TblScore
         
--联合
select 名称='最高分',分数=max(tmath) from TblScore
union all
select 名称='最低分',分数=min(tmath) from TblScore
union all
select 名称='平均分',分数=avg(tmath) from TblScore

向表中插入多条数据

使用union all插入多条数据。

--插入3条记录
insert into TblStudent
select 'name1','男','address1','phone1',18,'birthday1','code1',3
union all
select 'name2','男','address2','phone2',19,'birthday2','code1',2
union all
select 'name3','男','address3','phone3',20,'birthday3','code1',1

在使用union进行插入数据的时候，也要注意union会去除重复的。

--只成功插入一条数据，因为union去除了重复数据
insert into TblStudent
select 'name1','男','address1','phone1',18,'birthday1','code1',3
union
select 'name1','男','address1','phone1',18,'birthday1','code1',3
union
select 'name1','男','address1','phone1',18,'birthday1','code1',3
union
select 'name1','男','address1','phone1',18,'birthday1','code1',3
union
select 'name1','男','address1','phone1',18,'birthday1','code1',3

Select into

备份表的结构和数据

基本语法：select 列 into 备份表名 from 原表名

备份表是在执行select into语句时创建的，包含表结构和数据（自动编号列），但不包含原来表的约束。

select * into TblStudent20190506Backup from TblStudent

注意：select into语句不能重复执行，因为每次执行都会创建一个新的表。

备份表结构

只拷贝表结构，不拷贝数据。

select top 0 * into TblStudent20190506Backup from TblStudent
select * into TblStudent20190506Backup from TblStudent where 1<>1 --不建议使用

向表中数据插入另一个表

基本语法：insert into 表A select … from 表B

insert into TblStudent20190506Backup
select tsname,tsgender,tsaddress,tsphone,tsage,tsbirthday,tscardid,tsclassid --自动编号列不需要插入
from TblStudent
where tsgender='女'

字符串函数

常用的字符串函数

len：计算字符的个数

print len('你好123') --5

datalength：返回所占用的字节的个数（不是字符串函数）

print datalength('你好123') --7，中文占两个字节
print datalength(N'你好123') --10，使用unicode保存，每个字符占2个字节

upper和lower：转换大小写

print upper('Hello, How are you?') --转换大写
print lower('Hello, How are you?') --转换小写

ltrim和rtrim：去掉两端空格

print '==='+'   hello   '+'===' /*===   hello   ===*/
print '==='+ltrim('   hello   ')+'===' /*===hello   ===*/
print '==='+rtrim('   hello   ')+'===' /*===   hello===*/
print '==='+rtrim(ltrim('   hello   '))+'===' /*===hello===*/

left和right：字符串截取

left()，从左边开始截取，截取5个字符。

print left('中华人民共和国',5) --中华人民共

right()，从右边开始截取，截取5个字符。

print right('中华人民共和国',5) --人民共和国

substring：字符串截取

第一个参数是从第几位开始截取（从1开始），第二个参数是截取多少个字符。

print substring('中华人民共和国',1,5) --中华人民共
print substring('中华人民共和国',0,3) --中华
print substring('中华人民共和国',-2,4) --中

日期和时间函数

getdate：取得当前日期和时间

print getdate() --05 12 2019 12:02AM

sysdatetime：获取当前日期和时间（精确）

print sysdatetime() --2019-05-12 00:03:41.9537566

dateadd：增加时间函数

语法：DATEADD(增加的部分, 增加的值, 要增加的时间)

--当前时间增加200天
select dateadd(day, 200, getdate()) --2019-11-28 00:08:23.847
--当前时间增加200个月
select dateadd(month, 200, getdate()) --2036-01-12 00:09:40.403
--当前时间增加200年
select dateadd(year, 200, getdate()) --2219-05-12 00:10:17.040
--当前时间增加200分钟
select dateadd(minute, 200, getdate()) --2019-05-12 03:30:56.297
--当前时间增加200秒
select dateadd(second, 200, getdate()) --2019-05-12 00:14:53.553
--当前时间增加200小时
select dateadd(hour, 200, getdate()) --2019-05-20 08:12:13.777

datediff：计算两个日期的差

语法：DATEDIFF(相差的部分, 第一个日期, 第二个日期)

--计算两个日期相差多少年
select datediff(year, '1991-05-31', getdate()) --28
--计算两个日期相差多少个月
select datediff(month, '1991-05-31', getdate()) --336
--计算两个日期相差多少天
select datediff(day, '1991-05-31', getdate()) --10208

datepart：获取日期的某部分的值（数字表示形式）

print datepart(year, getdate()) --2019
print datepart(month, getdate()) --5
print datepart(day, getdate()) --12
print datepart(hour, getdate()) --22
print datepart(minute, getdate()) --46
print datepart(second, getdate()) --33
print datepart(dayofyear, getdate()) --132，一年中的第几天

datename：返回日期的某部分（字符串表示形式）

print datename(year, getdate()) --2019

year：获取日期的年部分

print year(getdate()) --2019

month：获取日期的月部分

print month(getdate()) --5

day：获取日期的日部分

print day(getdate()) --12

Order by
Group by
Having
1. where和having的区别
2. SQL语句的执行顺序
类型转换函数
1. CAST
2. CONVERT
Union联合
1. union和union all的区别
2. 向表中插入多条数据
Select into
字符串函数
1. 常用的字符串函数
日期和时间函数