select LEN(name) from test1  --計(jì)算name的長(zhǎng)度

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

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

select substring('HelloWorld!',6,6)  --可截取任意長(zhǎng)度
select left('HelloWorld!' ,5)  --從左開(kāi)始截取
select right('HelloWorld!' ,6) --從右開(kāi)始截取

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

select reverse('abc')    --cba

charindex(srt1 ,srt2）--srt1 在srt2中的開(kāi)始位置

select charindex('H','elloHWorld') 結(jié)果為：5 --只能查第一次出現(xiàn)的位置，匹配不到返回0

select replicate('abc',4) 結(jié)果為：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

統(tǒng)計(jì)學(xué)生的總成績(jī)并排序
select stu_id as 學(xué)生編號(hào) ,name as 學(xué)生姓名 , SUM(語(yǔ)文+英語(yǔ)+數(shù)學(xué)+代數(shù)) 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 兩個(gè)日期相差的天數(shù)

1、獲取系統(tǒng)時(shí)間 SysDateTime()
2、獲取當(dāng)前日期和時(shí)間 SysDateTimeOffset
3、獲取系統(tǒng)UTC時(shí)間 SysUTCDateTime
4、Current_TimeStamp當(dāng)前數(shù)據(jù)庫(kù)系統(tǒng)時(shí)間戳
5、判斷是否為日期數(shù)據(jù)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(會(huì)創(chuàng)建一個(gè)新表)

/*查詢 從n開(kāi)始的m條數(shù)據(jù) */
  declare @n int=2;
  declare @m int = 5;
  select top (@m) * from stu
  where id not in (select top (@n) id from stu)

  /*查詢n到m之間的數(shù)據(jù)*/
  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)

/* 查詢id 1 到3 的數(shù)據(jù) */
  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  --刪除列

/* 返回名字，重復(fù)的不返回 */
  select distinct stu_name from stu