黑马程序员+SQL基础(下)

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用法，子查询用法