SQL学习笔记：字符串函数，数学函数，时间函数

字符串函数:

lower(列名):转为小写

upper(列名):转为大写

charindex('要查的字符','列名'):返回字符所在的位置

len(列名):查询指定列名的内容长度

select len(name) from singer

replace(列名,旧内容,新内容):替换内容

select sing_name,replace(sing_name,1,'') from singer

subString(列名,开始位置,长度):截取内容

select sing_name,SUBSTRING(sing_name,2,2) from singer

reverse(列名):内容翻转

select reverse(name)from singer

数学函数

ceiling():向上取整

floor():向下取整

round():四舍五入

power():幂值

abs():绝对值

sqrt():平方根

时间函数:

year():取年

month():取月

day():取日

dateadd(MM,1,列名):增加时间

lower(列名):转为小写 upper(列名):转为大写

select sing_imgpath as '原内容', lower(sing_imgpath) as '转换小写后', upper(sing_imgpath) as '转换为大写后'from singer 

--去除空格函数select sing_name,LTRIM(sing_name) as '去除左边空格' from singerselect sing_name,len(sing_name) as '长度' from singer

--查询用户名长度为4的用户select sing_name,len(sing_name) as '长度',

REVERSE(sing_name) as '翻转后' from singer where len(sing_name)=4 

--返回指定字符的开始位置select sing_name,CHARINDEX('周',sing_name) from singer

--截取字符串select sing_name,replace(sing_name,1,'') from singer 

--字符串截取select sing_name,SUBSTRING(sing_name,2,2) from singer

--删除并插入select sing_name,stuff(sing_name,1,5,'你好') from singer 

--数学函数select sing_cunkuan,CEILING(sing_cunkuan) as '向上取整' from singer

select sing_cunkuan,floor(sing_cunkuan) as '向下取整' from singer 

select sing_cunkuan,round(sing_cunkuan,1) as '四舍五入' from singer 

--幂值select sing_ID,POWER(sing_ID,3) from singer 

--绝对值select sing_cunkuan,ABS(sing_cunkuan) from singer

--平方根select sing_ID,sqrt(sing_ID) from singer select * from singer

--取年，取月，取日select sing_birthday,YEAR(sing_birthday) '取年',MONTH(sing_birthday) '取月',day(sing_birthday),dateadd(YY,1,sing_birthday) as '增加一个月'  from singer  

 select *,'中国' as '国家' from singer where singname like '[a-i]'