SQLServer之常用函数总结详解

select LEN(name) from test1  --计算name的长度

select lower('STUDENT !')
select upper('student !')

declare @str varchar(100) = ' a a a '
select ltrim(@str)
select rtrim(@str)

select substring('HelloWorld!',6,6)  --可截取任意长度
select left('HelloWorld!' ,5)  --从左开始截取
select right('HelloWorld!' ,6) --从右开始截取

select replace('HelloWorld!','o','e')    --string,要被替换的字符串，替换的字符串

select reverse('abc')    --cba

charindex(srt1 ,srt2）--srt1 在srt2中的开始位置

select charindex('H','elloHWorld') 结果为：5 --只能查第一次出现的位置，匹配不到返回0

select replicate('abc',4) 结果为：abcabcabcabc

select avg(id) from test1     avg(列名)

select min(id) from test1
select max(id) from test1

select sum(id) from test1

select count(id) from test1

统计学生的总成绩并排序
select stu_id as 学生编号 ,name as 学生姓名 , SUM(语文+英语+数学+代数) as 总分from tb_stuAchievement 
ORDER BY 总分 DESC
GROUP BY stu_id ,name 

select getdate() 

select GETUTCDATE()

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

select datediff(YYYY,'2011-11-11','2012-12-12')    --输出1 年份相减之后的确是1
select datediff(day,'2011-11-11','2012-12-12')     --输出 397 两个日期相差的天数

1、获取系统时间 SysDateTime()
2、获取当前日期和时间 SysDateTimeOffset
3、获取系统UTC时间 SysUTCDateTime
4、Current_TimeStamp当前数据库系统时间戳
5、判断是否为日期数据isDate
		select isdate('2012-12-12')    -- 输出1
　　　　 select isdate('xxxx-12-12')　　-- 输出0

SELECT MID(City,1,3) as SmallCity FROM Persons

SELECT ROUND(column_name,decimals) FROM table_name

SELECT NOW() FROM table_name

select FORMAT(stu_intime,'yyyy-MM-dd hh:mm:ss') as intime from stu

select stu_name,stu_id into stu1 from stu   --将stu表中的stu_id和stu_name插入新表stu1(会创建一个新表)

  declare @n int=2;
  declare @m int = 5;
  select top (@m) * from stu
  where id not in (select top (@n) id from stu)

  
  declare @n int=2;
  declare @m int = 5;
  select top (@m-@n+1) * from stu
  where id not in (select top (@n-1) id from stu)

  select * from stu where id between '1' and '3' 

 alter table stu add stu_sj varchar(200)   --添加一列名为stu_sj

 alter table stu drop column stu_sj  --删除列

  select distinct stu_name from stu