SQLServer数据库基础之-常用函数

 
--字符串函数，可以用列名取代
select * from  Tb_Stu_Info
  
select ltrim(Stu_Name)  from  Tb_Stu_Info--把字符串变成列名

select patindex('%is%','this')--与char比较

select replace ('/**********/','*','_')--替换把星号替换成下划线

select reverse ('CHAIN')--倒置

select   'thank'+space(2)+'you'--中间加两个空格

select str(123.454,6,1)--6表示保留六位 1表示保留一位小数、

select substring ('CHINA',3,2)--截取从第三位开始截取截取2个字符
        
select * from  Tb_Stu_Info

select distinct left(ltrim(stu_name),1) from Tb_Stu_Info --distinct去掉重复函数嵌套
 

--时间日期函数
select dateadd(yyyy,-3,'20051120')--第一个参数加什么 ，第二个参数加几 /时间加三年
select  dateadd(mm,3,'20051120')    
select datediff(dd,'20051128','20051120')--

select datediff(yy,(Stu_Birthday),'20160401') from Tb_Stu_Info  --查询到今天的年龄


select datediff(yy,(Stu_Birthday),getdate()) from Tb_Stu_Info

--七月份出生的
select Stu_name  from  Tb_Stu_Info
where month (Stu_Birthday)=7
and Stu_xb='女' 


select datename(qq,'20160401')



----数据转换函数
select getdate()--得到当前日期
select convert(varchar(30),getdate(),120)--时间日期转换成字符类型
select convert(varchar(30),getdate(),111)












----次序函数


select *from  Tb_Stu_Score
where   subject_id=1
order by  score --desc             

--order by 
--依据某个列来排序
--order by 列名  asc(升序、)desc(降序)
--可以针对任何数据			
--row_number函数

select stu_no,score, row_number()over(order by score desc) as rownumber
from  Tb_Stu_Score where subject_id=1
 

-- rank 函数
select stu_no,score,rank()over(order by score desc)as rank
from Tb_Stu_Score where subject_id=1


--denserank 函数
select stu_no,score,dense_rank()over(order by score desc)as denserank
from Tb_Stu_Score where subject_id=1


--汇总函数
select sum(score)as totalScore
from  Tb_Stu_Score
where stu_no='082009030029'



select AVG(score)as  avergeScore
from  Tb_Stu_Score
where stu_no='082009030029'
--
select  count(*)  from  Tb_Stu_Info
where Stu_Address   like '湖南%'
 
select count(*) from Tb_Stu_Info
where    left(Stu_Address,2)='湖南'

--分类汇总
--男女个有多少人
select 
count(*)as 人数 from Tb_Stu_Info
group by Stu_xb

select Stu_Address  from Tb_Stu_Info as  生源地, Stu_xb as 性别,
count(*) as 人数 from Tb_Stu_Info
group by  Stu_Address, Stu_xb 


select  left(Stu_Address,2) as  生源地, Stu_xb as 性别,
count(*) as 人数 from Tb_Stu_Info
--having count>5 
group by  left(Stu_Address ,2), Stu_xb