SQLServer 之 常用函数及查看

时间:2020-12-25 10:36:48

一、查看

(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 排序;而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。