SQLServer 之 常用函数及查看

一、查看

(1)应用程序名称              SELECT APP_NAME()

(2)获取登录者名字           SELECT SUSER_NAME()

(3)获取字段定义的长度     SELECT COL_LENGTH('table', 'column')()

(4)获取架构名                 SELECT OBJECT_SCHEMA_NAME(object_id [,database_id])

(5)获取字段名称             SELECT COL_NAME(OBJECT_ID('object'), column_id)  //column_id从1开始

(6)查看数据库                EXEC sp_helpdb

(7)获取表的数据结构及相关信息    EXEC sp_help tableName

(8)查看表信息                 EXEC sp_tables tableName

(9)查看字段信息              EXEC sp_columns tableName

(10)查看索引                  EXEC sp_helpIndex tableName

(12)查看约束                  EXEC sp_helpConstraint tableName

(13)查看存储过程/触发器            EXEC sp_helptext 'SP_Name'/'Trigger_Name'

(14)查看触发器的属性      EXEC sp_helptrigger 'Trigger_Name'

 

二、常用函数

1、数学相关函数

(1)ABS：ABS函数返回数值表达式的绝对值

语法：ABS（numeric_expression）可以是整型、短整型、微短整型、小数、浮点数、货币、实型或小货币型数。

(2)ACOS：ACOS函数是一个数学函数，它返回以弧度表示的角度，即：ARCCOS。

语法：ACOS（float_expression）变量是浮点数数据类型，表示角度的余弦。

(3)ASIN：反正弦函数。SIN：正弦值。 COS：余弦值。

语法：SIN（ float_expression）float_expression是浮点型；

语法：COS（ float_expression）float_expression是浮点型。

(4)TAN：正切值。 COT:余切值。ATAN：反正切函数。

(5)SIGN：如果表达式为正，SIGN函数返回1，如果表达为负，SIGN返回-1，如果表达式为0，则函数返回0。

(6)CEILING：返回不小于给定的数值表达式的最小整数。

语法：CEILING（numeric_expression）小数、浮点、整数、实数、货币等类型。

例如：SELECT CEILING（1.45）返回值为2

SELECT CEILING （-1.45）返回值为-1

(7)FLOOR：返回不大于给定的数值表达式的最大整数。

语法：FLOOR（numeric_expression）小数、浮点、整数、实数、货币等类型。

例如：SELECT FLOOR（1.45）返回值为1

SELECT FLOOR （-1.45）返回值为-2

(8)ASCII：ASCII函数返回整数，表示字符表达式最左边的字符的ASCII代码值。

语法：ASCII（character_expression）

例：ASCII（‘AB’）返回65。

(9)EXP：返回以E为底的N次幂。

语法：EXP（float_expression）

例如：EXP（0）返回值为1。

(10)LOG：LOG函数返回浮点表达式的自然对数。

语法：LOG（float_expression）

(11)LOG10：LOG函数返回浮点表达式的以10为底的对数值。

语法：LOG10（float_expression）

(12)ROUND：ROUND返回给出的数学表达式的，并四舍五入至指定的精度。

语法：Round（numeric_expression,length[,funcion]）

变量：numeric_expression为数值型表达式

length是四舍五入的精度，小数点后边的数，当为负值时，数字表达式在小数点左边进行四舍五入。

Function值为0时表达式四舍五入，如果不为0，将数字表达式舍位。

(13)SQUARE：求平方

语法：Square（float_expression）

(14)SQRT：求平方根

语法：SQRT（float_expression）

/***　　常用聚合函数　　***/

(15)AVG：AVG是个集合函数，它计算数值列的平均值，忽略空值，如果使用了DISTINCT则对重复的值只取一次进行平均。

语法；AVG（[All|Distinct] expression）ALL是缺省值。

例如：SELECT Distinct（AGE）FROM TABLE1 如果任意行有NULL值，则此行被忽略。

(16)COUNT：是一个集合函数，它返回的整数代表表达式中值的个数通常为一个列，Distinct和Count一起使用时，返回单一值的个数。

语法：COUNT（{[All|Distinct] Expression |*}）

例如：SELECT  COUNT（Column_Name）FROM  Table，

返回COLUMN1中没有空值的行数，如果是使用*将返回表中所有行的数目。

使用Distinct则返回唯一值的个数。

(17)SUM：SUM是一个集合函数，返回表格中选定的数值型列中的值的总和。

语法：SUM（[All| Distinct]expression）

(18)MAX：MAX返回表达式中的最大值，

语法：MAX（expression）

(19)MIN：MIN返回表达式中的最小值，

语法：MIN（expression）

 

2、日期相关函数

2.1 GETDATE：GETDATE是一个函数，它返回系统日期和时间。

　　语法：GETDATE()　　返回值示例：2016-08-24 13:32:06.497

2.2 DATEADD：DateAdd返回根据日期时间添加的日期，

　　语法：DateAdd（datepart,number,date）

　　变量：DatePart是用于计算的日期单位（year，month，day）；Number是指添加至日期变量中的日期单位数；Date：格式化为日期的字符串，或日期时间型的列名、变量或参数。

　　例如：Select  DateAdd（DAY，7，GetDate（））

2.3 DATEDIFF：返回两个指定日期的日期单位差别。

　　语法：DateDiff（datepart,date,date）

　　例如：DATEDIFF（day,datevar,getdate()）

　　返回值为datevar和当前日期相差的天数。

2.4 DATEPART：返回的整数表示日期中的日期单位。

　　语法：Datepart（datepart,date）

　　例如：DATEPART（month,getdate()）

　　返回值为当前日期的月份数。

2.5 YEAR：显示所提供日期的年，MONTH：显示所提供日期的月，DAY：显示所提供日期的天，

　　语法：Year（Date）, Month（Date）,  Day（Date）

 

3、类型转换函数

3.1 CAST：CAST语句用来进行数据类型的转换。

　　语法：CAST（expression  AS  datatype）

　　例如：CAST（MYDATE AS VARCHAR）

　　把MYDATE 转换成可变字符类型。

3.2 CONVERT：用来进行数据类型的转换。

　　语法：Convert（data_type,expression）

　　例如：SELECT ‘my birthday is today’+convert(varchar(12),getdate( ))

　　把日期转换成字符串类型，用加号与前边相连。

 

4、字符相关函数

4.1 CHAR：字符串函数，用于将ASCII码整数转换为字符。

　　语法：CHAR（integer_expression）变量是0~255间的正整数。

　　例如：CHAR（65）返回‘A’

4.2 CharIndex：字符串函数，从指定位置开始查找，返回想要的查找字符在目标字符串中的起始位置。

　　语法：CharIndex（'Pattern'，Expression，[StartLocation]）；

　　例如：CharIndex（'A'，'BCDEA'，2）返回值为5，其2为从第几个字符进行搜索(不写默认为从第一个位置)。

4.3 STR：可以将数值型转换为字符型，允许控制小数的格式。

　　语法：STR（float_expression[,length[,decimal]]）

　　变量：length：想要的字符串的总长度。缺省为10。

　　decimal：是小数点右边的位数。四舍五入。如果不写长度则为取整，小数后四舍五入。

4.4 ISNULL：ISNULL可有非空类型代替NULL,如果expression为NULL，则返回value；否则返回expression。

　　语法：IsNull（expression,value） 例如：Select IsNull(NULL,'ABC')

4.5 LEFT/RIGHT：LEFT/RIGHT函数返回字符表达式从左边/右边开始的给定的字节数。如果该整数是一个负数， 就返回NULL。

　　语法：Left/Right（character_expression,integer_expression）

　　变量：character_expression可以是变量、常量、或表中的列。Integer_expression为要返回的字符数。

4.6 LEN：LEN函数提供了字符串表达式中的字符的长度。

　　语法：LEN（string_expression） 例如：LEN（'ABCE'）值为4

4.7 LOWER：LOWER函数将大写字符转换为小写字符。

　　语法：Lower（character_expression）  变量是字符表达式。

4.8 UPPER：UPPER函数将小写字符转换为大写字符。

　　语法：Upper（character_expression）  变量是字符表达式。

4.9 REVERSE：REVERSE返回字符串表达式的倒序。

　　语法：Reverse（character_expression）  例如：REVERSE（'PAM'）结果为'MAP'

4.10 LTRIM：LTRIM删除字符表的前导空格。

　　语法：LTRIM（character_expression）  变量是字符表达式。

4.11 RTRIM：RTRIM删除字符表的后继空格。

　　语法：RTRIM（character_expression）  变量是字符表达式。

4.12 NULLIF：如果一对表达式相等，NULLIF函数就返回NULL，如果它们不相等，NULLIF函数就返回第一个表达式的值。

　　语法：NullIf（expression1,expression2） 变量：expression可以数值型，也可以是字符串。

　　例如：SELECT NULLIF（134，135）返回值为134。

4.13 PI：PI()函数返回pi的值3.1415926

4.14 REPLACE：REPLACE用串表达式3取代串表达式1中的表达式2。

　　语法：REPLACE(string_expression, string_pattern, string_replacement)

　　例如：SELECT REPLACE（'mydog'，'dog'，'car'），用car取代了mydog中的dog，结果为mycar。

4.15 STUFF：函数可以将表达式的一部分用所提供的另一个字符串替代，替代部分用起始位置和长度来定义。

　　语法：STUFF（character_expression,start,length,character_expression）

　　length：是决定要替换的字符数。 

　　例如：STUFF('MY DOG FIDO', 8, 4, 'SALLY')结果为 MY DOG SALLY

4.16 SUBSTRING：返回字符型的一部分。

　　语法：SubString（expression,start,length）

　　例如：SUBSTRING('MICROSOFT', 6, 4)， 返回SOFT。

4.17 REPLICATE：REPLICATE将字符串表达式复制用户定义的次数。

　　语法：REPLICATE(string_expression, integer-expression)

　　例如：REPLICATE('2',5)

5、函数积累

5.1 ROW_NUMBER() OVER()函数　　

5.1.1 ROW_NUMBER() OVER(ORDER BY colName DESC)

　　将数据记录按照 colName字段排序后，为每一行记录赋予行号。

5.1.2 ROW_NUMBER() OVER (PARTITION BY colName1 ORDER BY colName2)

　　首先，根据 colName1 分组；然后，在分组内部根据 colName2 排序；而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)。