备用sql小案例

--字符串函数
select len('213')

select len(sName),sName from student

select lower('adfCE')
select upper('asdf23')

select '    abc   '
select ltrim('  abc  ')
select rtrim('  abc  ') + '123'
select ltrim(rtrim('   abc   ')) + '123'

--
select left('张三',1)
--ip1551616414

select right('ip1551616414',len('ip1551616414')-2) + 1
--起始位置从1开始
select substring('ip1551616414',1,2)

--查询班级内所有学生的姓
select distinct left(sName,1) from student

 

--日期函数
select getdate()

select dateadd(day,3,getdate())

select dateadd(month,-1,getdate())

select dateadd(year,-1,getdate())

select dateadd(quarter,1,getdate());
select dateadd(week,1,getdate());

select year(getdate())
select month(getdate())
select day(getdate())

--当前月的销售记录
select * from xsjl
where month(date) = month(getdate()) and year(date) = year(getdate())

--datediff
select datediff(day,getdate(),'2012-12-21')
select datediff(second,getdate(),'2012-12-21')

--求当月销售记录
select * from xsjl
where datediff(month,date,getdate())=0

--统计不同生日年份的学生个数
select count(*),year(sBirthday)
from student
group by year(sBirthday)

--求每个年龄有多少个学生
select DateDiff(year,sBirthday,getdate())
,count(*) from student Group by DateDiff(year,sBirthday,getdate())

--datename datepart
select datename(year,getdate()) --字符串
select datepart(year,getdate()) --整数

--一年中的第几天
select datename(dayofyear,getdate())
--一年中的第几周
select datename(week,getdate())

select datename(weekday,getdate())
select datepart(weekday,getdate())

 

--类型转换
--问题
select '平均分数' + 123
select avg(english) from score
select * from student

select '平均分数' + cast(23.56 as varchar(5))
select cast(right(sNo,3) as int)+1 from student

--不4舍5入
select cast (89.6 as int)
--4舍5入
select cast (round(89.6,0) as int)

select cast(89.239345 as decimal(10,2))
select cast(avg(english) as decimal(10,2)) from score

--convert
select convert(int,89.63934)
select convert(decimal(10,2),89.64789)
select convert(decimal(10,2),avg(english)) from score

select convert(varchar(30),getdate(),20)
select convert(varchar(20),getdate(),21)
select convert(varchar(30),getdate(),23)

select convert(varchar(10),getdate(),108)

select convert(varchar(10),getdate(),20)
select sId, sName, sAge, sNo, convert(varchar(10),sBirthday,20), sClassId, sSex, convert(varchar(10),sIntime,108), sPhone
from student

--isnull
select * from score

select studentId,isnull(english,0)
from score

select avg(english) from score
select sum(english)/count(sId) from score

 

select cast(avg(isnull(english,0)) as decimal(10,2)) from score

--输出所有数据中通话时间最长的5条记录。orderby datediff
select top 5 * from [CallRecords]
order by datediff(ss,[StartDateTime],[EndDateTime])desc from CallRecords
--输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum
select sum(datediff(ss,StartDateTime,EndDatetime)) as 总时长 from CallRecords
where TelNum like '0%'
--输出本月通话总时长最多的前三个呼叫员的编号。
select top 3 CallerNumber,sum(datediff(ss,startdatetime,enddatetime)) from callrecords
where datediff(month,startdatetime,'2012-7-5') = 0
group by callernumber
order by sum(datediff(ss,startdatetime,enddatetime)) desc
--
--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)
select top 3 CallerNumber,count(*) from callrecords
where datediff(month,startdatetime,'2012-7-5') = 0
group by callernumber
order by count(*) desc