SQL Server常用函数

-- SQL 操作结果集
-- 1. 并集
--union [all] 加all代表最终的结果集中将包含所有的行，不能去除重复行
--对两个或多个结果集进行连接,形成并集;子结果集所有的记录组合形成新的结果集
--限定条件：
--①子结果集要具有相同的结构
--②子结果集的列数相同
--③子结果集对应数据类型必须可兼容
--④每个子结果集不能包含order by和compute子句
--select A,B,C FROM D union [all] select A,B,C FROM E

-- 2. 差集
--except 
--对两个或多个结果集进行连接,形成差集;返回左边结果集中已有记录,而右边结果集中没有的记录
--限定条件:
--①子结果集要具有相同的结构
--②子结果集的列数相同
--③子结果集对应数据类型必须可兼容
--④每个子结果集不能包含order by和compute子句
--select A FROM B EXCEPT SELECT A FROM D

-- 3. 交集
--intersect   --可通过内连接实现(inner join),同时匹配两张表
--对两个或多个结果集进行连接，形成交集；返回左边结果集和右边结果集中都有的记录
--限定条件：
--①子结果集要具有相同的结构
--②子结果集的列数相同
--③子结果集对应数据类型必须可兼容
--④每个子结果集不能包含order by和compute子句
--select A from B intersect select A from D

-- 4. 结果集排序
--select A from B intersect select A from D order by A

-- SQL Server常用函数

-- 1.时间日期函数
--1.1 DATEADD(datepart,number,date) 在向指定日期加上一段时间的基础上，返回新的datetime值；number只取整数部分
select DATEADD(YY,1,'2017-4-17') as '年份加1'                 --YY/YYYY
select DATEADD(QQ,1,'2017-4-17') as '月份加1季度'             --QQ/Q
select DATEADD(MM,1,'2017-4-17') as '月份加1'                 --MM/M
select DATEADD(DY,1,'2017-4-30') as '天数加1'                 --DY/Y
select DATEADD(DD,1,'2017-4-30') as '天数加1'                 --DD/D
select DATEADD(WK,1,'2017-4-17') as '天数加一周(7天)'         --WK/WW
select DATEADD(HH,1,'2017-4-17 13:30:45') as '加1h'           --HH
select DATEADD(MI,1,'2017-4-17 13:30:45') as '加1 分钟'       -- MI/N
select DATEADD(SS,1,'2017-4-17 13:30:45') as '加1s'           --SS/S
select DATEADD(MS,1,'2017-4-17 13:30:45.058') as 'ms'         --MS 结果为：2017-04-17 13:30:45.057  --1s=1000ms

--1.2 DATEDIFF(datepart,startdate,enddate) 返回跨两个指定日期的日期和时间边界数；datepart参数同1.1
select DATEDIFF(QQ,'2017-4-17','2017-8-30') as '两日期相差季度'

--1.3 DATENAME(datepart,date) 返回代表指定日期的指定日期部分的字符串;datepart参数在1.1基础上加DW
select DATENAME(WK,'2017-4-17') as '今年第几周'
select DATENAME(DW,'2017-4-17') as '周几'                     --DW
select DATENAME(DY,'2017-4-17') as '今年第几天'
select DATENAME(DD,'2017-4-17') as '本月第几天'
select DATENAME(MS,'2017-4-17 13:30:45.058') as '当前s内多少毫秒'

--1.4 DATEPART(datepart,date) 返回代表指定日期的指定日期部分的整数；datepart参数同1.3
select DATEPART(DD,'2017-4-17') as '天'
select DATEPART(MM,'2017-4-17') as '月份'
select DATEPART(YY,'2017-4-17') as '年份'
select DATEPART(MS,GETDATE()) 

--1.5 GETDATE() 返回当前系统日期和时间
select GETDATE()

--1.6 GETUTCDATE() 返回表示当前UTC时间(世界时间坐标或格林尼治标准时间)的 datetime 值,即北京时间-8
select GETUTCDATE() 

--1.7 YEAR() 返回表示指定日期中的年份的整数;此函数等价于DATEPART(yy, date)
select YEAR('2017-4-17')
select DATEPART(YY,'2017-4-17')

--1.8 MONTH(date) 返回代表指定日期月份的整数;等价于DATEPART(mm,date)
select MONTH('2017-4-17')
select DATEPART(MM,'2017-4-17')

--1.9 DAY(date) 返回代表指定日期的天的日期部分的整数；此函数等价于 DATEPART(dd,date)
select DAY('2017-4-17')
select DATEPART(DD,'2017-4-17')

-- 2.数学运算函数
--2.1 ABS(精确数字或近似数字数据类型类别的表达式(bit除外))   求绝对值
select ABS(2.45)
select ABS(0.000)
select ABS(-50.098)

--2.2 EXP() 返回所给的 float 表达式的指数值;exp(5)即e的5次方
select EXP(-1)
select EXP(0)
select EXP(5)

--2.3 FLOOR() 返回小于或等于所给数字表达式的最大整数,即取整
select FLOOR(8.9)
select FLOOR(10.10)

--2.4 LOG() 返回给定 float 表达式的自然对数(以e为底的对数),参数需大于0
select LOG(1)
select LOG(0.5)
select LOG(10)

--2.5 LOG10() 返回给定float表达式的以10为底的对数,参数需大于0
select LOG10(1)
select LOG10(0.3)
select LOG10(10)

--2.6 PI() 
select PI()

--2.7 POWER(numeric_expression,y) 返回给定表达式乘指定次方的值
select POWER(3,3)

--2.8 RAND() 返回0到1之间的随机float值
select RAND()
--使用同一个种子值重复调用 RAND() 会返回相同的结果
--对于一个连接，如果使用指定的种子值调用 RAND()，则 RAND() 的所有后续调用将基于使用该指定种子值的 RAND() 调用生成结果。例如，以下查询将始终返回相同的数字序列
SELECT RAND(100), RAND(), RAND()
select RAND(),RAND(),RAND(100)

--2.9 ROUND() 返回数字表达式并四舍五入为指定的长度或精度;
--第二个参数是负数且大于小数点前的数字个数，ROUND 将返回0;
select ROUND(748.58, -5) --0.00
select ROUND(748.58, -4) --0.00
select ROUND(748.58, -3) --算术溢出错误
select ROUND(748.58, -2) --700.00
select ROUND(748.58, -1) --750.00
select ROUND(748.58, 0)  --749.00
select ROUND(748.58, 1)  --748.60

--第三个参数为0或空时四舍五入,其它值时截断
select ROUND(3.19878,3)   --3.19900
select ROUND(3.19878,3,0) --3.19900
select ROUND(3.19878,3,1) --3.19800
select ROUND(3.19878,3,2) --3.19800

--2.10 SIGN() 返回给定表达式的符号
select SIGN(-5) -- -1
select SIGN(0)  --  0 
select SIGN(5)  --  1

--2.11 SQUARE() 求平方
select SQUARE(5)

--2.12 SQRT() 求平方根
select SQRT(4)

--2.13 ACOS() 反余弦,求弧度表示的角度值;参数为float或real类型的表达式,其取值范围从-1到1;对超过此范围的参数值，函数将返回NULL并且报告域错误
select ACOS(0)
select ACOS(2) --报错

--2.14 ASIN() 反正弦,求弧度表示的角度值;是 float 类型的表达式，其取值范围从 -1 到 1。对超过此范围的参数值，函数将返回 NULL 并且报告域错误
select ASIN(0)
select ASIN(2)

--2.15 部分角度相关函数未写

-- 3.字符串函数
--3.1 ASCII() 返回字符表达式最左端字符的 ASCII 代码值
select ASCII('aa')
select ASCII('a')

--3.2 CHAR() 将 int ASCII 代码转换为字符的字符串函数;介于0和255间的整数。如果整数表达式不在此范围内,将返回 NULL 值
select CHAR(-1)  --NULL
select CHAR(97)  --a

--3.3 CHARINDEX(expression1,expression2[,start_location]) 返回字符串中指定表达式的起始位置
--expression1:一个表达式,其中包含要寻找的字符的次序,expression1 是一个短字符数据类型分类的表达式;
--expression2:-一个表达式,通常是一个用于搜索指定序列的列,expression2 属于字符串数据类型分类;
--start_location:-在 expression2 中搜索expression1 时的起始字符位置,如果没有给定 start_location,而是一个负数或零,则将从 expression2 的起始位置开始搜索

--3.4 DIFFERENCE(character_expression,character_expression) 以整数返回两个字符表达式的 SOUNDEX 值之差
--返回的整数是 SOUNDEX 值中相同字符的个数。返回的值从 0 到 4 不等，4 表示 SOUNDEX 值相同
--比较两个相差很小的字符串的 SOUNDEX 值，DIFFERENCE 返回的值是 4;比较两个相差很大的字符串的 SOUNDEX(见3.17) 值，DIFFERENCE 返回的值是 0
select DIFFERENCE('12345','123')
select DIFFERENCE('965544566','etresdsswwwws')

--3.5 LEFT(character_expression,integer_expression) 返回从字符串左边开始指定个数的字符;第二个参数为自然数
select LEFT('wertrrrr',3)  --wer
select LEFT('wertrrrr',0)  --空字符串
select LEFT('wertrrrr',-1) --第二个参数无效

--3.6 LEN() 返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格
select LEN('123 45 678 9 ')  --12
select LEN(' 123 45 678 9 ') --13
select LEN('123456789')      --9

--3.7 LOWER() 将大写字符数据转换为小写字符数据后返回字符表达式；3.23 小写=>大写
select LOWER('WEeR')

--3.8 LTRIM() 删除起始空格后返回字符表达式
select LTRIM(' 3e e e ') --[3e e e]

--3.9 NCHAR() 根据 Unicode 标准所进行的定义，用给定整数代码返回 Unicode 字符;参数介于 0 与 65535 之间的所有正整数。如果指定了超出此范围的值，将返回 NULL
select NCHAR(-1) --NULL
select NCHAR(54)

--3.10 PATINDEX(’%pattern%’, expression) 返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零
select PATINDEX('%wer%','w5errrr')

--3.11 REPLACE(’string_expression1’,’string_expression2’,’string_expression3’) 用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式
select REPLACE('0012344','0','9')

--3.12 QUOTENAME 返回带有分隔符的 Unicode 字符串,第二个参数默认为括号,可为单引号,双引号,左括号或右括号
SELECT QUOTENAME('ab]c[def')  --[ab]]c[def];]]表示转义字符
SELECT QUOTENAME('abc[]def')  --[abc[]]def];]]表示转义字符
SELECT QUOTENAME('abc,def','''') 
SELECT QUOTENAME('abc,def','[') 

--3.13 REPLICATE(character_expression,integer_expression) 以指定的次数重复字符表达式
select REPLICATE('we',3)  --wewewe
select REPLICATE('we',0)  --空字符串
select REPLICATE('we',-1) --NULL

--3.14 REVERSE(character_expression) 返回字符表达式的反转
select REVERSE('asdfg')

--3.15 RIGHT(character_expression,integer_expression) 返回字符串从右边指定个数的字符
select RIGHT('abcdefg',2) --fg

--3.16 RTRIM 截断所有尾随空格后返回一个字符串
select RTRIM(' as w e ')-- as w e

--3.17 SOUNDEX() 返回由4个字符组成的代码以评估两个字符串的相似性
select SOUNDEX('1122'),SOUNDEX('drft') --0000 D120
select DIFFERENCE('1122','drft')       --2

--3.18 SPACE() 返回由重复的空格组成的字符串
select '12'+ SPACE(2) + '12' --12  12;中间有两个空格

--3.19 STR (float_expression[,length[,decimal]])由数字数据转换来的字符数据；
--第一个参数为数字;第二个参数为总长度，包括小数点、符号、数字或空格，默认为0;第三个参数为小数点右边的位数
select STR(123.4568,5,2) --123.5
select STR(123.4568)     --[       123]

--3.20 STUFF(character_expression,start,length,character_expression)
--删除指定长度的字符并在指定的起始点插入另一组字符
select STUFF('12345',6,1,'000')   --NULL
select STUFF('12345',5,1,'000')   --1234000
select STUFF('12345',2,3,'000')   --10005
select STUFF('12345',-1,3,'000')  --NULL
select STUFF('12345',0,3,'000')   --NULL
select STUFF('12345',1,3,'000')   --NULL
--start最小为1,最大为字符串长度,其它输出NULL
select STUFF('12345',1,7,'000')  --000
select STUFF('12345',1,6,'000')  --000
select STUFF('12345',1,5,'000')  --000
select STUFF('12345',1,0,'000')  --00012345
select STUFF('12345',1,-1,'000') --NULL
--length最小为0,负数输出NULL

--3.21 SUBSTRING(expression,start,length) 截取字符串
select SUBSTRING('12345',-1,2) --空字符串
select SUBSTRING('12345',0,2)  --1
select SUBSTRING('12345',1,2)  --12
select SUBSTRING('12345',2,2)  --23
select SUBSTRING('12345',3,2)  --34
select SUBSTRING('12345',5,2)  --5
select SUBSTRING('12345',6,2)  --空字符串

--3.22 UNICODE() 返回输入表达式的第一个字符的整数集
select UNICODE('ass') --97

--3.23 UPPER() 返回将小写字符数据转换为大写的字符表达式;3.7大写=>小写
select UPPER('aWa')

-- 4.系统函数
--4.1 CASE
select 
CASE sex                 --sex为表中某一列列名
when '1' then '男'       --'1','2'为sex列中可能的值
when '2' then '女'       --'男','女','中性'为查询显示
else '中性' end as show
from(select '1' as sex )as table_sex

--4.2 CURRENT_TIMESTAMP 返回当前的日期和时间，等价于GETDATE()
SELECT CURRENT_TIMESTAMP
SELECT GETDATE()

--4.3 DATALENGTH  返回任何表达式所占的字节数
select DATALENGTH('000')
select DATALENGTH('ashdddd')

--4.4 IDENTITY (data_type[,seed,increment]) AS column_name  自增;只用在带有 INTO table 子句的 SELECT 语句中，以将标识列插入到新表中

--4.5 ISDATE() 确定输入表达式是否为有效的日期
select ISDATE('2015-8-9')  --有效返回1
select ISDATE('2017-2-30') --无效返回0

--4.6 ISNULL(check_expression,replacement_value)  使用给定的值替换NULL 
select ISNULL(null,'000')  --000
select ISNULL('','000')    --空字符串

--4.7 ISNUMERIC 确定表达式是否是一个有效的数字类型
select ISNUMERIC(000)   --是返回1,不是返回0

--4.8 NULLIF(A,B)  如果两个指定表达式相等,则返回空值;若不等,则返回第一个表达式的值
select NULLIF(200,200)
select NULLIF(200,100)

select NULLIF(100,200)