張亮的博客園

29 數(shù)字函數(shù)

      Abs() ：絕對(duì)值

Ceiling():舍入到最大整數(shù)  3.33à4  -3.6à-3    (天花板)

Floor():舍入到最小整數(shù)    2.89à2   -3.6à-4    (地板)

Round():四舍五入    select round(-3.61,0)à -4

Len():字符串長(zhǎng)度    select Len(FName) from T_Employee

Lower(),Upper() 大小寫轉(zhuǎn)換

LTrim(),RTrim() 去除左右空格

Substring(string,startindex,length)           截取字符串
              select substring(FName,2,2) from T_Employee

GetDate()獲取當(dāng)前時(shí)間
DateAdd(datepart,number,date) 計(jì)算增加后的日期  datepart:yyyy年份 mm月份，dy 當(dāng)年度的第幾天 ，dd 日   ，hh小時(shí)

      Select DateAdd(day,3,getDate())  --當(dāng)前日期上加3天

DateDiff(datedepart,startDate,endDate) –計(jì)算兩時(shí)間的差額

Select Datediff(hh,getDate(),DateAdd(day,-3,getDate()))  --計(jì)算出當(dāng)前時(shí)間與（當(dāng)前時(shí)間-3天）之間的小時(shí)數(shù)

Select FName,FInDate,DateDiff(year,FInDate,getDate())from T_Employee  --員工入職年數(shù)

 Select datediff(year,FIndate,getDate()),count(*) from T_Employee group by datediff(year,FIndate,getDate())  --每年入職人數(shù)

DatePart(datepart,date)  取時(shí)間的某一部分

  select datePart(year,getDate())  --取出當(dāng)前時(shí)間年份

Select datePart(year,FIndate),count(*) from T_Employee group by  datePart(year,FIndate)       --每年入職人數(shù)

30類型轉(zhuǎn)換函數(shù)

Cast( express as data_type):  select cast(‘2’as int)

Convert(data_type , express):   select  convert(int,’22’)   

31流控函數(shù)

   Isnull()  select isnull(FName,N’佚名’)from T_Employee  --如果name有為null，則顯示佚名

Case  表達(dá)式 when 值 then 返回值
when 值 then 返回值
else  返回值  end 

Select FName,(case Flevel when 1 then ‘VIP’ when 2 then  N‘普通客戶’ when 3 then N’特殊客戶’else ‘無(wú)’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’負(fù)’ then 1 else 0 end)as 負(fù) from T_Scores group bu Name      --統(tǒng)計(jì)個(gè)球員的勝負(fù)情況

32 練習(xí)：
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 --通話時(shí)間最長(zhǎng)的top5條記錄

select SUM(DateDiff(SECOND,StartDateTime,EndDateTime)) as 長(zhǎng)途電話時(shí)間

from T_CallRecord where TelNum like '0%' --長(zhǎng)途電話總時(shí)長(zhǎng)

select top 3 CallerNumber

from T_CallRecord

where DATEDIFF(MM,GETDATE(),StartDateTime)=0

group by CallerNumber

order by sum(DATEDIFF(SECOND,StartDateTime,EndDateTime)) desc

 --本月通話時(shí)間最長(zhǎng)的前名人員編號(hào)

select top 3 CallerNumber,COUNT(*) as 次數(shù)

from T_CallRecord where DATEDIFF(MM,GETDATE(),StartDateTime)=0 group by CallerNumber order by count(*) desc 

 --本月?lián)艽螂娫挻螖?shù)最多的前三個(gè)呼叫員的編號(hào)

select CallerNumber,TelNum,DATEDIFF(SECOND,StartDateTime,EndDateTime)as 總時(shí)長(zhǎng) from T_CallRecord union all

select N'匯總',sum((case  when TelNum like '0%' then DATEDIFF(second,StartDateTime,EndDateTime)   else 0 end  ))as 長(zhǎng)途電話總時(shí)長(zhǎng) ,

sum((case when TelNum not like '0%' then DATEDIFF(second,StartDateTime,EndDateTime)else 0 end ))as 市內(nèi)電話總時(shí)長(zhǎng) 

from T_CallRecord --匯總

33 索引（目錄）：在要建索引的字段上聲明下要為這個(gè)字段添加索引，提高查詢效率，對(duì)經(jīng)常要查詢的字段加上索引。

優(yōu)缺點(diǎn)：查詢效率高，更新操作速度慢，占用空間，所以只在經(jīng)常檢索的字段添加索引

34 join用法：

Select o.billId,c.name,c.age  from T_customer  as c join T_order as o on c.id=o.cusId -–客戶表和訂單表的關(guān)聯(lián)

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)—入會(huì)時(shí)間在01，03年的讀者信息

Select * from T_Reader where FYearOfJoin in(select Fyearpublished from T_Book   ) –入會(huì)時(shí)間在書出版的時(shí)間的讀者信息

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