SqlServer常用函数汇总

一 、字符串函数

1.charindex() – 字符索引
2.len() – 查看长度
3.upper() – 字符串大写转换
4.lower() -字符串小写转换
5.Ltrim() – 清除左边空格
6.Rtrim() – 清除右边空格
7.Left() – 从右第一位开始截取字符串
8.Right() – 从左第一位开始截取字符串
9.stuff() – 从某个位置删除替换
10.replace() – 查找并替换

--charindex(查询内容，数据源)，不指定开始位置，默认从1开始
select charindex('sjdy','www.sjdy.com')            --返回 5
---charindex(查询内容，数据源，开始位置)
select charindex('sjdy','www.sjdy.com',3)        --返回 5

-- len(数据源)    求字符串长度 

select len('www.baidu.com')                        --返回 13
-- left(数据源，左边开始位置)     从左开始截取指定长度的字符串
select left('www.baidu.com',3)                    --返回 www

--right(数据源，右边开始位置)    从右开始截取指定长度的字符串
select right('www.baidu.com',3)                    --返回 com

--replace(数据源，查找内容，替换内容)  将查找内容更改为替换内容
select replace('我爱我的家乡','家乡','祖国')        --返回 '我爱我的祖国'

--stuff(数据源，开始位置，要删除字符串的长度，新字符)   
--数据源，开始位置，删除几个字符，插入新的数据
select stuff('ABCDEF',2,3,'我和我的祖国')        --返回 'A我和我的祖国EF'
select stuff('ABCDEF',2,3,'')                    --返回 'AEF'

--upper(数据源)    将字符串转换为大写
select upper('sql server课程')                   --返回 'SQL SERVER课程'

--lower(数据源)    将字符串转换为大写
select lower('SQL SERVER课程')                   --返回 'sql server课程'

--Ltrim(数据源)    清除字符左边的空格
select Ltrim('       SQL')                        --返回 'SQL'

--Rtrim(数据源)    清除字符右边的空格
select Rtrim('SQL       ')                        --返回 'SQL'

二、日期函数

--日期部分参数及缩写
1.yy,yyyy    --年                    (year)
2.qq,q        --季度                   (quarter)
3.mm,m        --月                    (month)
4.dy,y        --一年中的某一天            (dayofyear)
5.dd,d        --日                    (day)
6.wk,ww        --周                    (week)
7.dw,w        --星期                    (weekday)
8.hh        --小时                    (hour)
9.mi,n        --分钟                    (minute)
10.ss,s        --秒                    (second)
11.ms        --毫秒                    (millisecond)

--getdate()        获取当前系统日期
select getDate()                           --返回 当前系统时间(2020-07-10 7:36:15.986)

--dateadd()        添加指定日期后的日期
select dateAdd(mm,4,'4/10/2020')             --返回 修改后的日期(8/10/2020)

--dateDiff()     获取时差
select dateDiff(dy,'4/10/2020','4/19/2020')     --返回 时差(9)

--datename()    获取指定日期部分的字符串形式
select datename(dw,'01/01/2020')            --返回 字符串(星期三)

--datePart()    获取指定日期部分的整数形式
select datePart(day,'01-01-2020')            --返回 数字(3)

业务常用

1、一个月第一天的
SELECT   DATEADD(mm,   DATEDIFF(mm,0,getdate()),   0) 

2、本周的星期一
SELECT   DATEADD(wk,   DATEDIFF(wk,0,getdate()),   0)  

3、一年的第一天
SELECT   DATEADD(yy,   DATEDIFF(yy,0,getdate()),   0)

4、季度的第一天
SELECT   DATEADD(qq,   DATEDIFF(qq,0,getdate()),   0)

5、当天的半夜
SELECT   DATEADD(dd,   DATEDIFF(dd,0,getdate()),   0)

6、上个月的最后一天
SELECT   dateadd(ms,-3,DATEADD(mm,  DATEDIFF(mm,0,getdate()),   0))

7、去年的最后一天
SELECT   dateadd(ms,-3,DATEADD(yy,   DATEDIFF(yy,0,getdate()),   0))

8、本月的最后一天
SELECT   dateadd(ms,-3,DATEADD(mm,   DATEDIFF(m,0,getdate())+1,   0))

9、本年的最后一天
SELECT   dateadd(ms,-3,DATEADD(yy,   DATEDIFF(yy,0,getdate())+1,   0))

10、查询本周注册人数
select   count(*)   from   [user]  
where   datediff(week,create_day-1,getdate())=0 

11、上周注册人数
select   count(*)   from   [user]  
where   datediff(week,create_day-1,getdate())=1

12、本月注册人数
select   count(*)   from   [user]  
where   datediff(month,create_day,getdate())=0 

三、数学函数

--常用数学函数
1.rand()        --返回0到1之间的随机float值
2.abs()            --获取绝对值
3.ceiling()        --向上取整，
4.floor()        --向下取整
5.power()        --取数值表达式的幂
6.round()        --取数值表达式四舍五入为指定精度
7.sign()        --对于整数返回 1，对于负数返回-1，对于0则返回0
8.sqrt()        --取浮点数表达式的平方根

--获取随机数
select rand()            --返回0~1之间的浮点数(0.522138223779467)        
--获取绝对值
select abs(-123)        --返回 123
--向上取整
select ceiling(12.5)    --返回 13
--向下取整
select floor(12.5)        --返回 12
--取数值表达式的幂
select power(4,2)        --返回 16
--四舍五入
select round(43.1453,2) --返回 43.15
--返回符号或0
select sign(23)            --返回 1
--返回平方根
select sqrt(9)            --返回 3

四、系统函数

--常用系统函数
1.convert()            --用来转变数据类型
2.current_user        --返回你登录的用户名
3.datalength()        --返回指定表达式的字节数
4.host_name()        --返回当前用户所登录的计算机名称
5.system_user        --返回你当前所登录的用户名
6.user_name()        --从给定的用户名ID返回用户名    
--转变数据类型
select convert(int,'1234')    返回整数 123
--返回你登录的用户名
select current_user            返回 dbo
--返回表达式的字节数
select datalength('孙')        返回 2
--返回当前登录的计算机名称
select host_name()            返回 DESKTOP-3NS4GCJ
--返回当前登录的用户名
select system_user            返回 DESKTOP-3NS4GCJ\12930
--返回指定用户名Id的用户名
select user_name(1)          返回 dbo

五、聚合函数

--常用聚合函数
1.sum()            --和
2.avg()            --平均分
3.max()            --最大值
4.min()            --最小值
5.count()        --计数

--求和
select sum(列名)        --返回 该列值的总和        该列必须是整数类型

--求平均分
select avg(列名)        --返回 该列值的平均数   该列必须是整数类型

--求最大值
select max(列名)        --返回 该列值中最大值   该列必须是整数类型

--求最小值
select min(列名)        --返回 该列值中最小值   该列必须是整数类型

--统计
select count(*)            --返回 统计个数 *代表每条记录

六、全局变量

1、@@CONNECTIONS --服务器上次启动以来创建的连接数

select @@CONNECTIONS  --返回88366669

2、@@CPU_BUSY  --自 SQL Server 启动至今，系统持续运行的毫秒数。

select @@CPU_BUSY --返回18579349

3、@@CURSOR_ROWS  --最近打开的游标中的行数

select @@CURSOR_ROWS --返回0

4、@@DATEFIRST  --SET DATEFIRST 参数的当前值，该参数用于设置一个星期的第一天为哪一天。

select @@DATEFIRST --返回7

5、@@ERROR  --最后一个 T-SQL 错误的错误号

select @@ERROR --返回0

6、@@FETCH_STATUS --如果最后一次提取的状态为成功状态，则为 0。如果出错，则为 -1

select @@FETCH_STATUS  --返回0

7、@@IDENTITY  --最后一次插入的标识值

select @@IDENTITY  --返回Null

8、@@LANGUAGE  --当前使用的语言的名称

select @@LANGUAGE  --返回us_english

9、@@MAX_CONNECTIONS --可以创建的同时连接的最大数

select @@MAX_CONNECTIONS  --返回32767

10、@@ROWCOUNT  --受上一个 SQL 语句影响的行数

select @@ROWCOUNT  --返回1

11、@@SERVERNAME  --本地服务器的名称

select @@SERVERNAME --返回sd305362023

12、@@SERVICENAME  --该计算机上的 SQL 服务的名称

select @@SERVICENAME --返回MSSQLSERVER

13、@@TIMETICKS  --当前计算机上每指令周期的微秒数

select @@TIMETICKS --返回31250

14、@@TRANCOUNT--当前连接打开的事务数

select @@TRANCOUNT --返回0

15、@@VERSION  --SQL Server 的版本信息

select @@VERSION --返回Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64)   Jan 12 2022 22:30:08   Copyright (C) 2019 Microsoft Corporation  Web Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor) 

七、自带存储过程

1、sp_databases --列出服务器上的所有数据库

EXEC sp_databases --其他写法差不多就不多做介绍

2、sp_server_info --列出服务器信息，如字符集，版本和排列顺序

3、sp_stored_procedures--列出当前环境中的所有存储过程

4、sp_tables --列出当前环境中所有可以查询的对象

5、sp_start_job --立即启动自动化任务

6、sp_stop_job --停止正在执行的自动化任务

7、sp_password --添加或修改登录帐户的密码

8、sp_configure --显示(不带选项)或更改(带选项)当前服务器的全局配置设置

9、sp_help --返回表的列名，数据类型，约束类型等

10、sp_helptext --显示规则，默认值，未加密的存储过程，用户定义的函数，触发器或视图的实际文本

11、sp_helpfile --查看当前数据库信息

12、sp_dboption --显示或更改数据库选项

13、sp_detach_db --分离数据库

14、sp_attach_db --附加数据库

15、sp_addumpdevice --添加设备

16、sp_dropdevice --删除设备

17、sp_pkeys --查看主键

18、sp_fkeys --查看外键

19、sp_helpdb --查看指定数据库相关文件信息

20、sp_addtype --自建数据类型

21、sp_droptype --删除自建数据类型

22、sp_rename --重新命名数据库

23、sp_executesql --执行SQL语句

24、sp_addlogin --添加登陆

25、sp_droplogin --删除登录

26、sp_grantdbaccess --把用户映射到登录，即添加一个数据库安全帐户并授予塔访问权限

27、sp_revokedbaccess--撤销用户的数据访问权,即从数据库中删除一个安全帐户

28、sp_addrole --添加角色

29、sp_addrolemember --向角色中添加成员，使其成为数据库角色的成员

30、sp_addsrvrolemember--修改登录使其成为固定服务器角色的成员

31、sp_grantlogin --允许使用组帐户或系统用户使用Windows身份验证连接到SQL

32、sp_defaultdb --修改一个登录的默认数据库

33、sp_helpindex --用于查看表的索引

34、sp_cursoropen --定义与游标和游标选项相关的SQL语句，然后生成游标

35、sp_cursorfetch --从游标中提取一行或多行

36、sp_cursorclose --关闭并释放游标

37、sp_cursoroption --设置各种游标选项

38、sp_cursor --用于请求定位更新

39、sp_cursorprepare --把与游标有关的T-SQL语句或批处理编译成执行计划，但并不创建游标

40、sp_cursorexecute --从由sp_cursorprepare创建的执行计划中创建并填充游标

41、sp_cursorunprepare --废弃由sp_cursorprepare生成的执行计划

42、sp_settriggerorder --指定第一个或最后一个激发的、与表关联的 AFTER 触发器。在第一个和最后一个触发器之间激发的 AFTER 触发器将按未定义的顺序执行