SQL语句相关概念及练习之基础篇(1)(4)

------------------------------ 
-----SQL其他内置函数------ 
------------------------------ 
 
--1.数学函数 
--ABS():求绝对值 
--CEILING():舍入到最大整数 
--FLOOR():舍入到最小整数 
--ROUND():四舍五入 
 
selectABS(-3) 
selectCEILING(3.33) 
selectCEILING(-3.61) 
selectFLOOR(2.98) 
selectFLOOR(-3.61) 
selectROUND(-3.61,1)--第二个参数是精度，小数点后的位数 
selectROUND(-3.61,0) 
selectROUND(3.1415926,3) 
 
--2.字符串函数 
--LEN():计算字符串长度 
--LOWER(),UPPER():转大小写 
--LTRIM():去掉字符串左侧的空格 
--RTRIM():去掉字符串右侧的空格 
--SUBSTRING(string,start_positoin,length): 
--索引从1开始 
selectSUBSTRING('abc111',2,3)--结果是bc1 
selectFName,SUBSTRING(FName,2,2)fromT_Employee 
selectLEN('abc')--结果是3 
selectFName,LEN(FName)fromT_Employee 
 
--没有可以同时既去掉左边空格、又去掉右边空格的TRIM()内置函数，所以先左后右的进行TRim，当然，你也可以先右后左 
selectLTRIM('abc'),RTRIM('abc'),LEN(LTRIM(RTRIM('abc'))) 
 
--3.日期函数 
--GETDATE():获取当前日期时间 
--DATEADD(datepart,numbre,date):计算增加以后的日期， 
--参数date为待计算的日期；参数number为增量；参数datepart为计量单位,时间间隔单位； 
--DATEDIFF(datepart,startdate,enddate):计算2个日期之间的差额 
--DATEPART(datepart,date):返回一个日期的特定部分,比如年月日,时分秒等. 
 
/* 
 
值缩写SqlServer）(Access和ASP)说明 
YearYyyyyy年1753~9999 
QuarterQqq季1~4 
MonthMmm月1~12 
DayofyearDyy一年的日数,一年中的第几日1-366 
DayDdd日，1-31 
WeekdayDww一周的日数，一周中的第几日1-7 
WeekWkww周，一年中的第几周0~51 
HourHhh时0~23 
MinuteMin分钟0~59 
SecondSss秒0~59 
MillisecondMs-毫秒0~999 
*/ 
 
selectDATEADD(DAY,3,getdate()) 
selectDATEADD(MONTH,-3,getdate()) 
selectDATEADD(HOUR,8,getdate()) 
selectDATEDIFF(YEAR,'1989-05-01',GETDATE()) 
selectDATEDIFF(HH,GETDATE(),DATEADD(DAY,-3,GETDATE())) 
 
--查询员工的工龄,年为单位 
selectFName,FInDate,DATEDIFF(year,FInDate,getdate())as工龄fromT_Employee 
 
--取出每一年入职员工的个数V1 
selectDATEDIFF(year,FInDate,getdate()),COUNT(*) 
fromT_Employee 
groupbyDATEDIFF(year,FInDate,getdate()) 
 
--取出每一年入职员工的个数V2 
selectDATEPART(YEAR,FInDate),COUNT(*) 
fromT_Employee 
groupbyDATEPART(YEAR,FInDate) 
selectDATEPART(YEAR,GETDATE()) 
selectDATEPART(MONTH,GETDATE()) 
selectDATEPART(DAY,GETDATE()) 
selectDATEPART(HH,GETDATE()) 
selectDATEPART(MINUTE,GETDATE()) 
selectDATEPART(SECOND,GETDATE()) 
 
--4.类型转换函数 
--CAST(expressionasdata_type) 
--CONVERT(data_type,expression) 
selectCAST('123'asint),CAST('2010-09-08'asdatetime), 
CONVERT(datetime,'2010-09-08'),CONVERT(varchar(20),123) 
 
--5.空值处理函数isNull 
--ISNULL(expression,value) 
selectISNULL(FName,'佚名')as姓名fromT_Employee 
 
--6.CASE函数用法： 
--1.单值判断：相当于switch.case 
--CASEexpression 
--WHENvalue1thenreturnvalue1 
--WHENvalue2thenreturnvalue2 
--WHENvalue3thenreturnvalue3 
--ELSEdefault_return_value 
--END 
 
--判断客户类型 
selectFName, 
( 
caseFLevel 
when1then'普通客户' 
when2then'会员' 
when3then'VIP' 
else'未知客户类型' 
End 
)as客户类型 
fromT_Customer 
 
--收入水平查询 
selectFName, 
( 
case 
whenFSalary<2000then'低收入' 
whenFSalary>=2000andFSalary<=5000then'中等收入' 
else'高收入' 
end 
)as收入水平 
fromT_Employee 
 
--这里有一道关于CASE用法的面试题 
--表T中有ABC三列，用SQL语句实现:当A列大于B列时选择A列，否则选择B列； 
--当B列大于C列时选择B列，否则选择C列。 
select 
( 
case 
whena>bthenaelseb 
end 
), 
( 
case 
whenb>cthenbelsec 
end 
) 
fromT 
 
--------------------------------------- 
 
selectFNumber, 
( 
case 
whenFAmount>0thenFAmount 
else0 
end 
)as收入, 
( 
case 
whenFAmount<0thenABS(FAmount) 
else0 
end 
)as支出 
fromT 
----------------------------------------- 
 
--球队比赛那个题 
--有一张表T_Scroes，记录比赛成绩： 
--DateNameScroe 
--2008-8-8拜仁胜 
--2008-8-9奇才胜 
--2008-8-8湖人胜 
--2008-8-10拜仁负 
--2008-8-8拜仁负 
--2008-8-12奇才胜 
--要求输出下面格式： 
--Name胜负 
--拜仁12 
--湖人10 
--奇才20 
--注意：在中文字符串前加N，比如N'胜' 
createtableT_Scores( 
[Date]datetimenullcollate 
[Name]nvarchar(50) 
) 
CREATETABLE[T_Scores]([Date][datetime]NULL, 
[Name][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL, 
[Score][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL 
); 
INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF200000000ASDateTime),N'拜仁',N'胜'); 
INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF300000000ASDateTime),N'奇才',N'胜'); 
INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF300000000ASDateTime),N'湖人',N'胜'); 
INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF400000000ASDateTime),N'拜仁',N'负'); 
INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF200000000ASDateTime),N'拜仁',N'负'); 
INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF600000000ASDateTime),N'奇才',N'胜'); 
select*fromT_Scores 
 
--列出第一个表格 
--统计每支队伍的胜负情况 
selectName, 
( 
caseScore 
whenN'胜'then1 
else0 
end 
)as胜, 
( 
caseScore 
whenN'负'then1 
else0 
end 
)as负 
fromT_Scores 
selectName, 
sum 
( 
caseScore 
whenN'胜'then1 
else0 
end 
)as胜, 
sum 
( 
caseScore 
whenN'负'then1 
else0 
end 
)as负 
fromT_Scores 
groupbyName 
--根据每个队的胜负判断出胜负的场数