SQL Server里面的函数,你知道多少?

详解sqlserver数据库sql优化注意事项25条

概述 今天主要分享下sqlserver数据库在做sql优化时的25条注意事项,仅供参考。 SQL优化事项 1、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: SELECT ID FROM T WHERE NUM IS NULL 可以在NUM上设置默认

AVG()

平均值,忽略null值,字段类型必须为数字型

Select avg(score)

From dbo.score

SUM()

求和,忽略null值,字段类型必须为数字型

Select sum(score)

From dbo.score

avg,sum函数

Min()

最小值,忽略null值,字段类型为数字型、字符型

Select min(score)

From dbo.score

Max()

最大值,忽略null值,字段类型必须为数字型

Select max(score)

From dbo.score

min,max函数

Count()

项数,忽略null值,字段类型为整型

Select count(score)

From dbo.score

count函数

Count_big()表示项数在大于2^23-1这个数的时候使用

数据量非常大的时候,再用count会显示报错,需要用到Count_big()

Len()

指定字符串表达式的字符数,不区分中英文,其中不包含尾随空格

如果要返回表达式的字节数,区分中英文,需要用到datalength()函数

Select len(score)

From dbo.score

Len函数

随机数产生

用于抽奖,需要随机产生数据的情况下使用

在查询分析器中执行:select rand()

可以随机得到一个小数,类型:0.836393773069793

这种随机数在实用过程中使用不多,一般使用的是随机整数。

Select floor(rand()*10) 到9之间的一个随机整数

Select ceiling(rand()*10) 1到10之间的一个随机整数

Floor函数返回小于或等于所给数字表达式的最大整数(向下取整,把小数后面的小数直接抹去)

Ceiling函数返回大于或等于所给数字表达式的最小整数(向上取整)

乘以1000的话就随机产生1到1000之间的一个随机整数

Getdate() Getutcdate()

返回数据库系统当前时间值,返回值类型为datetime

Select getdate()

返回当前国际标准时间值,返回值类型为datetime

Select getutcdate()

返回当前时间值函数

Convert()函数

把日期转换为新数据类型的通用函数

可以用不同格式显示日期/时间数据

Convert(date_type(length),date_to_be_converted,style)

(要转换成的数据类型,要转换的时间值,要转换的时间格式样式)

时间格式样式

covert函数例子

Datediff()函数

返回两个日期之间的天数/月数/小时数等

Datediff(datepart,startdate,enddate)

(计算时间的部分(day、month、year),开始时间,结束时间)

startdate和enddate参数是合法的日期表达式

Dateadd()函数

在日期中添加或减去指定的时间间隔

Dateadd(datepart,number,date)

例子

Datepart()函数

用于返回日期/时间的单独部分,返回类型是一个int型

(获取的部分,时间值)

Datename()同样是用于返回日期的/时间的单独部分,返回类型是一个varchar型

Day() month() year()

数据库服务器当前部分时间

例子

charindex函数

返回字符或字符串在另一个字符串中的起始位置

Charindex(expression1,expression2[,start_location])

Expression1是要到expression2中寻找的字符中,start_location是开始查找的位置

返回一个整数,返回的整数是要找字符串再被找字符串中起始的位置,没有找到字符串,返回一个0值。

Patindex函数

返回字符或者字符串在另一个字符串或者表达式中的位置,patindex函数支持搜索字符串中使用通配符

Patindex(‘%BC%’,’ABCD’)

返回的是2

其中,如果不加通配符,两个参数要一样,比如patindex(‘abcd’,’abcd’)

Charindex,Patindex函数

例子

Patindex函数例子

例一:
找出Northwind.dbo.Categories表中Description字段中是包含单词“Bread”或“bread”的所有记录,那么选择语句就可能是这样:
Select Description from Northwind.dbo.Categories
Where patindex(“%[b,B]read%”,description) > 0
PATINDEX 中可以使用通配符来确定大写和小写的“b”

例二:
找出Northwind.dbo.Categories表中Description字段中是包含单词“Bread”或“bread”,且第二子字母不是“e”的记录。
select Description from Northwind.dbo.Categories
where patindex(“%[b,B]read%”,description) > 0 and patindex(“_[^e]%”,description) = 1
通过在条件语句中增加一个使用^通配符的PATINDEX函数,我们可以过滤掉“Dessert, candies, and sweet breads”这条记录。上面的查询结果只有一条记录。

Charindex函数例子

假设你要显示Northwind数据库Customer表前5行联系人列的Last Name。这是前5行数据
ContactName
——————————
Maria Anders
Ana Trujillo
Antonio Moreno
Thomas Hardy
Christina Berglund
你可以看到,CustomName包含客户的First Name和Last Name,它们之间被一个空格隔开。我用CHARINDX函数确定两个名字中间空格的位置。通过这个方法,我们可以分析ContactName列的空格位置,这样我们可以只显示这个列的last name部分。这是显示Northwind的Customer表前5行last name的记录!
select top 5 substring(ContactName,charindex(” “,ContactName)+1 ,
len(ContactName)) as [Last Name] from Northwind.dbo.customers
下面是这个命令输出的结果。
Last Name
——————————
Anders
Trujillo
Moreno
Hardy
Berglund

通配符

通配符

Stuff函数

用于删除指定长度的字符,并可以在制定的起点处插入另一组字符,返回类型是字符串型

Stuff(要删除的字符串,开始位置,长度,代替字符串)

Stuff函数

Substring函数

用于截取指定长度额字符串

Substring(expression,start,length)

Substring函数

Left、right函数

Left返回字符串从左边开始指定个数的字符

Left(character_expression,integer_expression)

right返回字符串从右边开始指定个数的字符

Right(character_expression,integer_expression)

Left、right函数

Ltrim函数、rtrim函数

Ltrim删除起始空格后返回字符串表达式

Ltrim(character_expression)

rtrim截断所有尾随空格后返回一个字符串

rtrim(character_expression)

Ltrim函数、rtrim函数

Upper函数、lower函数

Upper返回小写字符数据转换为大写的字符表达式

Upper(character_expression)

Lower返回大写字符数据转换为小写的字符表达式

Lower(character_expression

Upper、lower函数

Replace函数

用另一个字符串替换出现的所有指定字符串值

Replace(string_expression,string_pattern,string_replacement)

(原字符串,原字符串中的需要替换的某一个字符,将要替换成的字符串)

Replace函数

Replicate函数、space函数

Replicate是以指定的次数重复字符表达式

Replicate(string_expression,integer_expression)

Space返回指定个数的空格表达式

Space(integer_expression)

Replicate、space函数

reverse函数

用于倒置字符串中的各个字符中的位置

Reverse(string_expression)

reverse函数

cast函数

用于将某种数据类型的表达式显示转换为另一种数据类型

Cast(string_expression as data_type)

cast函数

Case函数

是一种简单的条件判断转换的一个函数

会把满足条件的表达式转换为对应的结果

具有两种格式,简单case函数和case搜索函数

简单case函数

Case sex when ‘1’ then ‘男’

when ‘2’ then ‘女’

else ‘其他’

End

Case搜素函数

Case when sex=’1’ then ‘男’

when sex=’2’ then ‘女’

else ‘其他’

end

case函数

【云技术在线】为您提供以下专业技术服务:

上云服务:上云架构设计,大流量高并发架构设计,混合云架构设计,上云迁移,云上容灾,跨国云访问加速等;

安全服务:等保咨询及实施,渗透测试,漏洞扫描,木马查杀,安全加固,应急响应,DDOS/CC防护,勒索病毒防护,安全托管等;

运维服务:Linux系统运维,数据恢复及备份,负载均衡集群搭建及维护,数据库集群搭建及维护,网站运行环境搭建及维护,系统性能调优,中间件性能调优,数据库性能调优,数据库读写分离,分库分表,性能(压力)测试,7*24小时监控及故障应急响应,故障排查,大流量高并发活动护航等。