29 数字函数
Abs() ：绝对值
Ceiling():舍入到最大整数 3.33à4 -3.6à-3 (天花板)
Floor():舍入到最小整数 2.89à2 -3.6à-4 (地板)
Round():四舍五入 select round(-3.61,0)à -4
Len():字符串长度 select Len(FName) from T_Employee
Lower(),Upper() 大小写转换
LTrim(),RTrim() 去除左右空格
Substring(string,startindex,length) 截取字符串
select substring(FName,2,2) from T_Employee
GetDate()获取当前时间
DateAdd(datepart,number,date) 计算增加后的日期 datepart:yyyy年份 mm月份，dy 当年度的第几天 ，dd 日 ，hh小时
Select DateAdd(day,3,getDate()) --当前日期上加3天
DateDiff(datedepart,startDate,endDate) –计算两时间的差额
Select Datediff(hh,getDate(),DateAdd(day,-3,getDate())) --计算出当前时间与（当前时间-3天）之间的小时数
Select FName,FInDate,DateDiff(year,FInDate,getDate())from T_Employee --员工入职年数
Select datediff(year,FIndate,getDate()),count(*) from T_Employee group by datediff(year,FIndate,getDate()) --每年入职人数
DatePart(datepart,date) 取时间的某一部分
select datePart(year,getDate()) --取出当前时间年份
Select datePart(year,FIndate),count(*) from T_Employee group by datePart(year,FIndate) --每年入职人数
30类型转换函数
Cast( express as data_type): select cast(‘2’as int)
Convert(data_type , express): select convert(int,’22’)
31流控函数
Isnull() select isnull(FName,N’佚名’)from T_Employee --如果name有为null，则显示佚名
Case 表达式 when 值 then 返回值
when 值 then 返回值
else 返回值 end
Select FName,(case Flevel when 1 then ‘VIP’ when 2 then N‘普通客户’ when 3 then N’特殊客户’else ‘无’end ) as 客户类型 from T_Customer --case用法1
Select FName,(case when FSalary<2000 then N‘低收入’when FSalary>=2000 and FSalary<=3000 then N‘中等收入’else N’高收入’end) as 收入情况 from T_Employee --case用法2
Select (case when a>b then a else b end),(case when c>d then c else d end ) from T --最大值
Select FNumber,(case when FAmount>0 then FAmount else 0 end)as 收入,（case when FAmount<0 then abs(FAmount) else 0 end）as 支出 from T_Employee
Select Name,Sum( case Scores when N‘胜’then 1 else 0 end )as 胜，Sum(case scores when N’负’ then 1 else 0 end)as 负 from T_Scores group bu Name --统计个球员的胜负情况
32 练习：
create table T_CallRecord(id int not null,CallerNumber nvarchar(10),TelNum nvarchar(20),StartDateTime datetime,EndDateTime datetime,primary key(id))
insert into T_CallRecord values('001','02030004304','2008-09-20','2008-10-21')
insert into T_CallRecord values('001','02030004304','2008-07-20','2008-09-21')
insert into T_CallRecord values('002','2030004304','2008-09-20','2008-09-21')
insert into T_CallRecord values('001','02030004304','2008-01-20','2008-05-21')
insert into T_CallRecord values('003','2030004304','2008-06-20','2008-07-21')
insert into T_CallRecord values('001','02030004304','2008-09-20','2008-09-21')
insert into T_CallRecord values('004','02030004304','2008-08-20','2008-09-21')
insert into T_CallRecord values('001','2030004304','2008-09-20','2008-10-21')
insert into T_CallRecord values('005','02030004304','2008-04-20','2008-05-21')
truncate table T_CallRecord
select top 5 * from T_CallRecord order by DATEDIFF(SECOND,StartDateTime,EndDateTime) desc --通话时间最长的top5条记录
select SUM(DateDiff(SECOND,StartDateTime,EndDateTime)) as 长途电话时间
from T_CallRecord where TelNum like '0%' --长途电话总时长
select top 3 CallerNumber
from T_CallRecord
where DATEDIFF(MM,GETDATE(),StartDateTime)=0
group by CallerNumber
order by sum(DATEDIFF(SECOND,StartDateTime,EndDateTime)) desc
--本月通话时间最长的前名人员编号
select top 3 CallerNumber,COUNT(*) as 次数
from T_CallRecord where DATEDIFF(MM,GETDATE(),StartDateTime)=0 group by CallerNumber order by count(*) desc
--本月拨打电话次数最多的前三个呼叫员的编号
select CallerNumber,TelNum,DATEDIFF(SECOND,StartDateTime,EndDateTime)as 总时长 from T_CallRecord union all
select N'汇总',sum((case when TelNum like '0%' then DATEDIFF(second,StartDateTime,EndDateTime) else 0 end ))as 长途电话总时长 ,
sum((case when TelNum not like '0%' then DATEDIFF(second,StartDateTime,EndDateTime)else 0 end ))as 市内电话总时长
from T_CallRecord --汇总
33 索引（目录）：在要建索引的字段上声明下要为这个字段添加索引，提高查询效率，对经常要查询的字段加上索引。
优缺点：查询效率高，更新操作速度慢，占用空间，所以只在经常检索的字段添加索引
34 join用法：
Select o.billId,c.name,c.age from T_customer as c join T_order as o on c.id=o.cusId -–客户表和订单表的关联
Select o.billId,c.name,c.age from T_customer as c join T_order as o on c.id=o.cusId where c.age>15 --年龄大于15
Select o.billId,c.name,c.age from T_customer as c join T_order as o on c.id=o.cusId where c.age>（select avg(age) from T_customer）--年龄大于平均年龄
35 子查询
Select * from (select * from T_customer) as table1
单值子查询：select 1 as a1 ,(select Count(*)from T_customer) as a2
Select * from T_Reader where FYearofJoin in(2001,2003)—入会时间在01，03年的读者信息
Select * from T_Reader where FYearOfJoin in(select Fyearpublished from T_Book ) –入会时间在书出版的时间的读者信息
select * from (
select ROW_NUMBER()over(order by callerNumber desc)as newRow, CallerNumber,TelNum
from T_CallRecord) as newtable where newtable.newRow>3 and newtable.newRow<5 –row_number用法，子查询用法