--取数值表达式的绝对值
select abs(-41) 41
select abs(41) 41
select abs(-41.12) 41.12
select abs(41.12) 41.12
--弧度表达式 括号内是（1 -1）
select acos(0.5) 1.0471975511966
select asin(0.5) 0.523598775598299
select atan(0.5) 0.463647609000806
select atn2(0.5 ,1) 0.463647609000806
--返回给定数的最小 最大正整数
select ceiling(44.2) 45
select floor(44.2) 44
--返回给定角的三角余弦 给定弧度算出数值
select cos(0) 1
select exp(2) --e^() 7.38905609893065
select power(3,2) 9
--求近似值
select round(44.546,2) 44.550
select sign(-45) -1
select sign(45) 1
select sign(0) 0
--随机数 0-1之间的数
select rand()
--求平方根
select sqrt(4) 2
select square(4+5) 81
--字符串函数
select ascii(0) --unicode的值 48
select char(48) 0
--返回字符串中指定的表达式的开始位置
select charindex('abcd','a') 0
select difference('abc','acd') 3
select left('abc',2) -- ab
select len('abc') 3
select lower('ABC') abc
select ltrim(' abc') abc
select nchar(48) 0
select replace('angel','g','h') anhel
select replicate('abc',2) abcabc
select reverse('abcd') dcba
select right('abcd',1) d
select rtrim('abc ') abc
select space(20) --返回有多个空格组成的字符串
select stuff('abc',2,1,'cc') accc
select upper('abcd') ABCD
--日期和时间函数
select current_timestamp --2013-04-14 18:34:04.657 返回当前的日期
select dateadd( mm,1,'2013-04-14') 2013-05-14 00:00:00.000
select datediff(mm,'2013-04-14','2014-04-14') 12个月
select datename( dw,'2013-04-14') 星期日
select datename( week,'2013-04-14') 16
select datepart(mm,'2013-04-14') 四月
select datepart(day,'2013-04-14') 14
select month('2013-04-14') 4
select year('2013-04-14') 2013
select getdate() 2013-04-14 18:41:47.673 当前系统
select getutcdate() 2013-04-14 10:42:47.090 表式当时utc的时间
select isdate('2013-04-14') --判断括号内的是不日期类型的不是返回0 是返回1
select sysdatetime() 2013-04-14 18:46:00.3441457
select cast(1 as decimal(4,3)) --格式为四位有效数字, 三位小数 , 结果是1.000
select * from YuJinginfo where DateDiff(dd,time,getdate())=0 --查询当天
select * from YuJinginfo where DateDiff(hh,time,getDate())<=24 --查询24小时内
SELECT * FROM YuJinginfo where DATEDIFF(day,time,GETDATE())=1 --查询昨天
SELECT * FROM YuJinginfo where datediff(week,time,getdate())=0 --查询本周
SELECT * FROM YuJinginfo where datediff(week,time,getdate())=1 --查询上周
SELECT * FROM YuJinginfo where DATEDIFF(month,time,GETDATE())=0 --查询本月
SELECT * FROM YuJinginfo where DATEDIFF(month,time,GETDATE())=1 --查询上月
select sad_id,title from SupplyAndDemand where sad_id in (select MAX(sad_id) as sad_id from SupplyAndDemand group by transactionType )order by transactionType desc --一张表中获取不同种类的最新数据
--判断一个数是否大于一张表中四列值中其中一列的 然后根据需要输出信息
select case when CAST(20 as int)>= CAST(F_Threshold1 as int) then '一级预警'
when CAST(20 as int)>=CAST(F_Threshold2 as int) then '二级预警'
when CAST(20 as int)>= CAST(F_Threshold3 as int) then '三级预警'
when CAST(20 as int)>= CAST(F_Threshold4 as int) then '四级预警'
end as yjstate from T_WeatherStationThreshold where F_CODE='5'