SQL Server函数（聚合，数学，字符串，日期时间，元数据函数）

一，聚合函数
聚合函数对一组值进行计算并返回单一的值，通常聚合函数会与SELECT语句的GROUP BY子句一同使用，在与GROUP BY子句使用时，聚合函数会为每一个组产生一个单一值，而不会为整个表产生一个单一值。
常用的聚合函数及说明：
（1）SUM 返回表达式中所有值的和
（2）AVG 计算平均值
（3）MIN 返回表达式的最小值
（4）MAX 返回表达式的最大值
（5）COUNT 返回组中项目的数量
（6）DISTINCT 返回一个集合，并从指定集合中删除重复的元组
（7） HAVING函数，查询数据表中的重复记录，可以借助HAVING子句实现，该子句用来指定组或聚合的搜索条件。HAVING子句只能与SELECT语句一起使用，而且，它通常在GROUP BY子句中使用。

SELECT SUM(AGE) FROM tb_table
SELECT AVG(AGE) FROM tb_table

SELECT COUNT(*) FROM tb_table
SELECT COUNT(DISTINCT(NAME)) FROM tb_table

SELECT MAX(AGE) FROM tb_table
SELECT MIN(AGE) FROM tb_table

--HAVING 需要跟分组语句一起使用
SELECT AGE,COUNT(AGE) AS ACOUNT FROM tb_table GROUP BY AGE HAVING COUNT(AGE)>1

二，数学函数

数学函数可以对数据类型为整型（integer）、实型（real）、浮点型（float）、货币型（money）和smallmoney的列进行操作。它的返回值是6位小数，如果使用出错，则返回NULL值并显示提示信息，通常该函数可以用在SQL语句的表达式中。
常用的数学函数及说明：
（1）ABS 返回指定数字表达式的绝对值
（2）COS 返回指定的表达式中指定弧度的三角余弦值
（3）COT 返回指定的表达式中指定弧度的三角余切值
（4）PI 返回值为圆周率
（5）POWER 将指定的表达式乘指定次方
（6）RAND 返回0～1之间的随机float数
（7）ROUND 将数字表达式四舍五入为指定的长度或精度
（8）SIGN 返回指定表达式的零(0)、正号(+1)或负号(-1)
（9）SIN 返回指定的表达式中指定弧度的三角正弦值
（10）SQUARE 返回指定表达式的平方
（11）SQRT 返回指定表达式的平方根
（12）TAN 返回指定的表达式中指定弧度的三角正切值

SELECT ABS(-2.5) AS 绝对值,
 ABS(0) AS 绝对值,
 ABS(2.5) AS 绝对值


 SELECT PI() AS 圆周率

SELECT POWER(2,4) AS '2的4次幂'
SELECT POWER(2,4) AS '2的4次幂',POWER(3,3)

--随机浮点数
SELECT RAND(100),RAND(),RAND()
--四舍五入
SELECT ROUND(123.995,3),ROUND(123.9895,3)

SELECT SQUARE(4) AS '4的平方'

SELECT SQRT(16) AS 平方根

DECLARE @ANGLE FLOAT
SET @ANGLE=10
SELECT convert(varchar,COS(@ANGLE)) AS COS三角余弦

DECLARE @ANGLE1 FLOAT
SET @ANGLE1=10
SELECT convert(varchar,COT(@ANGLE1)) AS COS三角余切

DECLARE @ANGLE2 FLOAT
SET @ANGLE2=10
SELECT convert(varchar,SIN(@ANGLE2)) AS COS三角正弦

SELECT TAN(PI()/2) AS COS三角正切

三，字符串函数
字符串函数作用于char、varchar、binary和varbinary数据类型以及可以隐式转换为char或varchar的数据类型。通常字符串函数可以用在SQL语句的表达式中。
常用的字符串函数及说明：
1.ASCII函数
ASCII函数返回字符表达式中最左侧的字符的 ASCII 代码值。
语法如下：
ASCII ( character_expression )
参数说明：
character_expression：char 或 varchar 类型的表达式。
返回类型：int类型。
说明：
ASCII码共有127个，其中Microsoft Windows不支持1～7、11～12和14～31之间的字符。值8、9、10和13分别转换为退格、制表、换行和回车字符。它们并没有特定的图形显示，但会依不同的应用程序而对文本显示有不同的影响。
ASCII码值对照表如表：

2.CHARINDEX函数
CHARINDEX函数返回字符串中指定表达式的起始位置（如果找到）。搜索的起始位置为 start_location。
语法如下：
CHARINDEX ( expression1 ,expression2 [ , start_location ] )
参数说明：
expression1：包含要查找的序列的字符表达式。expression1 最大长度限制为 8000 个字符。
expression2：要搜索的字符表达式。
start_location：在 expression2 中搜索 expression1 时的起始字符位置。如果没有给定 start_location，而是一个负数或零，则将从 expression2 的起始位置开始搜索。
返回类型：如果 expression2 的数据类型为 varchar(max)、nvarchar(max) 或 varbinary(max)，则为 bigint，否则为 int。
3.LEFT函数
LEFT函数返回字符串中从左边开始指定个数的字符。
语法如下：
LEFT ( character_expression , integer_expression )
参数说明：
character_expression：字符或二进制数据表达式。character_expression 可以是常量、变量或列。character_expression 可以是任何能够隐式转换为 varchar 的数据类型。否则，请使用 CAST 函数对 character_expression 进行显式转换。
integer_expression：正整数。如果 integer_expression 为负，则返回空字符串。
返回类型：
当 character_expression 为非 Unicode 字符数据类型时，返回 varchar。
当 character_expression 为 Unicode 字符数据类型时，返回 nvarchar。

4.RIGHT函数
RIGHT函数返回字符表达式中从起始位置（从右端开始）到指定字符位置（从右端开始计数）的部分。
语法如下：
RIGHT(character_expression,integer_expression)
参数说明：
character_expression：是从中提取字符的字符表达式。
integer_expression：是起始位置，用正整数表示。如果 integer_expression 是负数，则返回一个错误。

5.LEN函数
LEN函数返回字符表达式中的字符数。如果字符串中包含前导空格和尾随空格，则函数会将它们包含在计数内。LEN 对相同的单字节和双字节字符串返回相同的值。
语法如下：
LEN(string_expression)
参数说明：
string_expression：要计算的字符串表达式。

6.REPLACE函数
REPLACE函数将表达式中的一个字符串替换为另一个字符串或空字符串后，返回一个字符表达式。语法如下：
REPLACE(character_expression,searchstring,replacementstring)
参数说明：
character_expression：是函数要搜索的有效字符表达式。
searchstring：是函数尝试定位的有效字符表达式。
replacementstring：是用作替换表达式的有效字符表达式。

7.REVERSE函数
REVERSE函数按相反顺序返回字符表达式。
语法如下：
REVERSE(character_expression)
参数说明：
character_expression：是要反转的字符表达式。

8.STR函数
STR函数返回由数字数据转换来的字符数据。语法如下：
STR ( float_expression [ , length [ , decimal ] ] )
参数说明：
float_expression：带小数点的近似数字 (float) 数据类型的表达式。
length：总长度。它包括小数点、符号、数字以及空格。默认值为 10。
decimal：小数点后的位数。decimal 必须小于或等于 16。如果 decimal 大于 16，则会截断结果，使其保持为小数点后具有十六位。

9.SUBSTRING函数
SUBSTRING函数为字符表达式、二进制表达式、文本表达式或图像表达式的一部分。
语法如下：
SUBSTRING ( value_expression ,start_expression , length_expression )
参数说明：
value_expression：是 character、binary、text、ntext 或 image 表达式。
start_expression：指定返回字符的起始位置的整数或 bigint 表达式。如果 start_expression 小于 0，会生成错误并终止语句。如果 start_expression 大于值表达式中的字符数，将返回一个零长度的表达式。
length_expression：是正整数或指定要返回的 value_expression 的字符数的 bigint 表达式。如果 length_expression 是负数，会生成错误并终止语句。如果 start_expression 与 length_expression 的总和大于 value_expression 中的字符数，则返回整个值表达式。
返回类型：如果 expression 是受支持的字符数据类型，则返回字符数据。如果 expression 是支持的 binary 数据类型中的一种数据类型，则返回二进制数据。
返回的字符串类型与指定表达式的类型相同

--ASCII函数返回字符表达式中最左侧的字符的 ASCII 代码值。
DECLARE @POSION INT,@STRING CHAR(2)
SET @POSION=1
SET @STRING='NXT'
WHILE @POSION<=DATALENGTH(@STRING)
  BEGIN
   SELECT ASCII(SUBSTRING(@STRING,@POSION,1))	
  END

SELECT ASCII('XTR') 

--使用CHARINDEX函数返回指定字符串的起始位置
SELECT CHARINDEX('23',AGE) AS 起始位置 FROM tb_table 

SELECT LEFT('明日科技有限公司',5)
SELECT NAME,ID FROM tb_table 
SELECT LEFT(NAME,1) AS '姓氏',count(left(NAME,1)) AS 'shu' FROM tb_table
group by LEFT(NAME,1)

SELECT right('明日科技有限公司',5)

SELECT LEN('明日科技有限公司') AS LEN函数字符个数
select len('SDDFGZXC')

SELECT REPLACE('MingRERE','RERE','NN') AS '替换结果'
--SELECT REPLACE((SELECT NAME FROM tb_table) ,'珍妮','小妮子')

SELECT REVERSE('我的名字是') AS 翻转结果

SELECT  STR(123.45,5,1),STR(123.45,8,8)

SELECT SUBSTRING('123456',2,5) AS 截取

四，日期时间函数
日期和时间函数主要用来操作datetime、smalldatetime类型的数据，日期和时间函数执行算术运行与其他函数一样，也可以在SQL语句的SELECT、WHERE子句以及表达式中使用。
常用的日期时间函数及说明：
1，GETDATE函数返回系统的当前日期。GETDATE 函数不使用参数。
注意：
GETDATE 函数的返回结果的长度为 29 个字符。
语法如下：
GETDATE()
2，DAY函数返回一个整数，表示日期的“日”部分。
语法如下：
DAY(date)
参数说明：
date：以日期格式返回有效的日期或字符串的表达式。
3，MONTH函数返回一个表示日期中的“月份”日期部分的整数。
语法如下：
MONTH(date)
参数说明：
date：是任意日期格式的日期。
4，YEAR函数用于返回指定日期的年份。
语法如下：
YEAR (date)
参数说明：
date表示返回类型为datetime或smalldatetime的日期表达式。
有关YEAR函数使用的几点说明如下：
该函数等价于DATEPART(yy,date)。
SQL Server数据库将0解释为1900年1月1日。
在使用日期函数时，其日期只应在1753～9999年之间，这是SQL Server系统所能识别的日期范围，否则会出现错误。
5，DATEDIFF函数用于返回日期和时间的边界数。
语法如下：
DATEDIFF (datepart,startdate,enddate)
参数说明：
datepart规定了应在日期的哪一部分计算差额的参数。
startdate表示计算的开始日期，startdate是返回datetime值、smalldatetime值或日期格式字符串的表达式。
enddate表示计算的终止日期。enddate是返回datetime值、smalldatetime值或日期格式字符串的表达式。
SQL Server识别的日期部分和缩写。

有关DATEDIFF函数使用的几点说明如下：
startdate是从enddate中减去。如果startdate比enddate晚，则返回负值。
当结果超出整数值范围，DATEDIFF产生错误。对于毫秒，最大数是24天20小时31分钟零23.647秒。对于秒，最大数是68年。
计算跨分钟、秒和毫秒这些边界的方法，使得DATEDIFF 给出的结果在全部数据类型中是一致的。结果是带正负号的整数值，其等于跨第一个和第二个日期间的datepart边界数。例如，在1月4日（星期日）和1月11日（星期日）之间的星期数是1。

6，DATEADD函数将表示日期或时间间隔的数值与日期中指定的日期部分相加后，返回一个新的 DT_DBTIMESTAMP 值。number 参数的值必须为整数，而 date 参数的取值必须为有效日期。
语法如下：
DATEADD(datepart, number, date)
参数说明：
datepart：指定要与数值相加的日期部分的参数。
number：用于与 datepart 相加的值。该值必须是分析表达式时已知的整数值。
date：返回有效日期或日期格式的字符串的表达式。
注意：
如果指定一个不是整数的值，则将废弃此值的小数部分。

SELECT GETDATE() AS '当前时间'

SELECT DAY('2013-2-20') AS 'day'
SELECT DAY(GETDATE()) AS '日'
SELECT MONTH(GETDATE()) AS '月'
SELECT YEAR(GETDATE()) AS '年'

SELECT DATEDIFF(YEAR,'2013-1-1','2103-5-5') AS 时间差
SELECT DATEDIFF(DAY,'2013-1-1','2103-5-5') AS 时间差

SELECT GETDATE() AS '当前时间'
SELECT DATEADD(MONTH,1,GETDATE()) AS '下一个月',
     DATEADD(DAY,10,GETDATE()) AS '后十天',
	 DATEADD(YEAR,1,GETDATE()) AS '下一年'

五，转换函数
当遇到类型转换的问题时，可以使用SQL Server所提供的CAST和CONVERT函数。这两种函数不但可以将指定的数据类型转换为另一种数据类型，还可用来获得各种特殊的数据格式。CAST和CONVERT函数都可用于选择列表、WHERE子句和允许使用表达式的任何地方。
在SQL Server中数据类型转换分为两种，分别如下：
（1）隐性转换：SQL Server自动处理某些数据类型的转换。例如，如果比较char和datetime表达式、smallint和int表达式、或不同长度的char表达式，SQL Server可将它们自动转换，这种转换称为隐性转换，对这些转换不必使用CAST函数。
（2）显式转换：显式转换是指CAST和CONVERT函数，CAST和CONVERT函数将数值从一种数据类型（局部变量、列或其他表达式）转换到另一种数据类型。
说明：
隐性转换对用户是不可见的，SQL Server自动将数据从一种数据类型转换成另一种数据类型。例如，如果一个smallint 变量和一个int变量相比较，这个smallint变量在比较前即被隐性转换成int变量。
有关转换函数使用的几点说明如下：
CAST函数基于SQL-92标准并且优先于CONVERT。
当从一个SQL Server对象的数据类型向另一个数据类型转换时，一些隐性和显式数据类型转换是不支持的。例如，nchar数值根本就不能被转换成image数值。nchar只能显式地转换成binary，隐性地转换到binary是不支持的。nchar可以显式地或者隐性地转换成nvarchar。
当处理sql_variant数据类型时，SQL Server支持将具有其他数据类型的对象隐性转换成sql_variant类型。然而，SQL Server并不支持从sql_variant数据类型隐性地转换到其他数据类型的对象。

1，CAST函数
CAST函数用于将某种数据类型的表达式显示转换为另一种数据类型。
语法如下：
CAST (expression AS data_type[(length)])
参数说明：
expression：表示任何有效的SQL Server表达式
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type：表示目标系统所提供的数据类型，包括xml、bigint和sql_variant，不能使用别名数据类型。
length：指定目标数据类型长度的可选整数。默认值为30。.
使用CAST函数进行数据类型转换时，在下列情况下能够被接受。
两个表达式的数据类型完全相同。
两个表达式可隐性转换。
必须显式转换数据类型。
如果试图进行不可能的转换（例如，将含有字母的 char 表达式转换为 int 类型），SQL Server 显示一条错误信息。
如果转换时没有指定数据类型的长度，则SQL Server自动提供长度为30

2，CONVERT函数
CONVERT函数与CAST函数的功能相似。该函数不是一个ANSI标准SQL函数，它可以按照指定的格式将数据转换为另一种数据类型。
语法如下：
CONVERT (data_type[ (length) ],expression [, style])
参数说明：
data_type表示目标系统所提供的数据类型，包括bigint和sql_variant。不能使用用户定义的数据类型。
length为nchar、nvarchar、char、varchar、binary和varbinary数据类型的可选参数。参数expression表示任何有效的SQL Server表达式。
style为日期样式，指定当将datetime数据转换为某种字符数据时或将某种字符数据转换为datetime数据时会使用style中的样式。style日期样式。

六，元数据函数
元数据函数描述了数据的结构和意义，它主要用于返回数据库中的相应信息，其中包括：
返回数据库中数据表或视图的个数和名称。
返回数据表中数据字段的名称、数据类型、长度等描述信息。
返回数据表中定义的约束、索引、主键或外键等信息。
常用的元数据函数及说明：
（1）COL_LENGTH：返回列的定义长度（以字节为单位）
（2）COL_NAME：返回数据库列的名称，该列具有相应的表标识号和列标识号
（3）DB_NAME：返回数据库名
（4）OBJECT_ID：返回数据库对象标识号

SELECT GETDATE(),
       CAST(GETDATE() AS DATETIME),
       CONVERT(DATETIME,GETDATE(),126)

SELECT COL_LENGTH('tb_table','NAME') AS VARCHAR类型长度,
       COL_LENGTH('tb_table','ID')  AS INT类型长度,
	   COL_LENGTH('tb_table','AGE')  AS INT类型长度,
	   COL_LENGTH('tb_table','OTHER')  AS CHAR类型长度,
	   COL_LENGTH('tb_table','SEX')  AS NCHAR类型长度

SELECT COL_NAME(OBJECT_ID('tb_table'),1) AS 首列名称

SELECT DB_NAME() AS 数据库名称