SQL函数大全汇总

野性酷女 2022-12-16 09:06 97阅读 0赞

# SQL中包含以下七种类型的函数： #

## 一、聚合函数 ##

1.  **聚合函数:返回汇总值 **（它对其应用的每个行集返回一个值）
    
    1.  **`AVG(表达式)`** 返回表达式中所有的`平均值`。仅用于数字列并自动忽略NULL值。
    2.  **`COUNT(表达式)`** 返回表达式中`非NULL值的数量`。可用于数字和字符列。
    3.  **`COUNT(*)` **返回表中的`行数`(**包括有NULL值的列**)。
    4.  **`MAX(表达式)`** 返回表达式中的`最大值`,忽略NULL值。可用于数字、字符和日期时间列。
    5.  **`MIN(表达式)`** 返回表达式中的`最小值`,忽略NULL值。可用于数字、字符和日期时间列。
    6.  **`SUM(表达式)`** 返回表达式中所有的`总和`,忽略NULL值。仅用于数字列。

## 二、转换函数 ##

1.  **转型函数:将一种数据类型转换为另外一种**（有CONVERT和CAST两种）
    
    1.  **CONVERT(data\_type\[(length)\], expression \[, style\])**
        
        1.  例: Select convert(varchar(10) ,stuno) as stuno,stuname from student
    2.  **CAST( expression AS data\_type )**
        
        1.  例: Select cast(stuno as varchar(10)) as stuno,stuname from student

### **Cast和Convert的区别** ###

**Cast 和Convert都是用来将一种数据类型的表达式转换为另一种数据类型的表达式。CAST 和 CONVERT 提供相似的功能，只是语法不同。在时间转化中一般用到convert,因为它比cast多加了一个style,可以转化成不同时间的格式。**

## 三、日期函数 ##

1.  **日期函数:处理日期和时间**（由于不能直接执行算术函数,所以日期函数就十分有用）
    
    1.  **`GETDATE()`** 当前的系统日期。
        
        1.  例：select GETDATE() --结果：2020-10-19 15:34:27.343
    2.  **`DATEADD(日期部分,number,date)` **返回带有指定数字(number)的日期(date),该数字添加到指定的日期部分(datepart)
        
        1.  例：elect DATEADD(dd, 5, getdate()) --增加5天时间
    3.  **`DATEDIFF(日期部分,date1,date2)`** 返回两个日期中指定的`日期部分之间的差值`
        
        1.  例：select DATEDIFF(mm, '2010-1-1', '2010-3-1 00:00:00') --结果：2
    4.  **`DATENAME(日期部分,date)`** 返回日期中日期部分的`字符串形式`
        
        1.  例：select DATENAME(dw,GETDATE()) --结果：星期二
    5.  **`DATEPART(日期部分,date)` **返回日期中指定的日期部分的`整数形式`
        
        1.  `例：`select DATEPART(dw,GETDATE()) --结果(返回今天是一周中的第几天)：3
    6.  **`YEAR(date)`** 返回指定日期的`年份`数值
        
        1.  例：select YEAR(GETDATE()) --结果：2020
    7.  **`MONTH(date)`**返回指定日期的`月份`数值
    8.  **`DAY(date)`**返回指定日期的`天数`值

***注***：

*  DATENAME 和 DATEPART 的区别，返回的值类型不同，一个是VARCHAR一个是INT，另外就是星期会用本地语言来表示
 *  当显示日期列的内容时如果只显示年月日部分,可以使用CONVERT转换函数对日期列进行转换
    
    CONVERT(VARCHAR(10),日期字段名,120) --120 为日期格式YYYY-MM-DD
    
    SELECT CONVERT(VARCHAR(10),盘点日期,120) AS 盘点日期 FROM 原材料盘点日期明细表

**SQL中日期的表示方法及有效范围,如下:**

<table> 
 <tbody> 
  <tr> 
   <td>日期部分</td> 
   <td>缩写</td> 
   <td>值</td> 
   <td>日期部分</td> 
   <td>缩写</td> 
   <td>值</td> 
  </tr> 
  <tr> 
   <td>年</td> 
   <td>yy</td> 
   <td>1753-9999</td> 
   <td>周</td> 
   <td>wk</td> 
   <td>1-53</td> 
  </tr> 
  <tr> 
   <td>季度</td> 
   <td>qq</td> 
   <td>1-4</td> 
   <td>小时</td> 
   <td>hh</td> 
   <td>0-23</td> 
  </tr> 
  <tr> 
   <td>月</td> 
   <td>mm</td> 
   <td>1-12</td> 
   <td>分钟</td> 
   <td>mi</td> 
   <td>0-59</td> 
  </tr> 
  <tr> 
   <td>一年中的天</td> 
   <td>dy</td> 
   <td>1-366</td> 
   <td>秒</td> 
   <td>ss</td> 
   <td>0-59</td> 
  </tr> 
  <tr> 
   <td>一月中的天</td> 
   <td>dd</td> 
   <td>1-31</td> 
   <td>毫秒</td> 
   <td>ms</td> 
   <td>0-999</td> 
  </tr> 
  <tr> 
   <td>一周中的天</td> 
   <td>dw</td> 
   <td>1-7</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

## 四、数学函数 ##

1.  **数学函数:执行算术运算**（对数字值执行代数运算）
    
    1.  **`ABS(num_expr)` **返回数值表达式的绝对值。
    2.  **`ACOS(float_expr)` **返回角(以弧度表示),它的余弦值近似于指定的浮点表达式。
    3.  **`ASIN(float_expr)` **返回角(以弧度表示),它的正弦值近似于指定的浮点表达式。
    4.  **`ATAN(float_expr)`** 返回角(以弧度表示),它的正切值近似于指定的浮点表达式。
    5.  **`ATN2(float_expr1, float_expr2)`** 返回角(以弧度表示),它的正切值在两个近似的浮点表达式之间。
    6.  **`CEILING(num_expr)` **返回大于或等于数值表达式的最小整数。
    7.  **`COS(float_expr)` **返回以浮点表达式表示的近似于指定角度(以弧度表示)的余弦三角函数的值。
    8.  **`COT(float_expr)` **返回以浮点表达式表示的近似于指定角度(以弧度表示)的余切三角函数的值。
    9.  **`DEGREES(num_expr)`**返回数值表达式表示的弧度值对应的度值。
    10. **`EXP(float_expr)` **根据指定的近似浮点表达式,返回指数值。
    11. **`FLOOR(num_expr)` **返回小于或等于数值表达式的最大整数
    12. **`LOG(float_expr)` **根据指定的近似浮点表达式,返回自然对数值。
    13. **`LOG10(float_expr)` **根据指定的近似浮点表达式,返回以为底的对数。
    14. **`PI()`** 返回常量值.141592653589793
    15. **`POWER(num_expr,y)` **返回幂为y的数值表达式的值。
    16. **`RADIANS(num_expr)` **返回数值表达式表示的度值对应的弧度值。
    17. **`RAND([seed])`** 随机返回的到之间的近似浮点值,可以对seed指定为整数表达式(可选)。
    18. **`ROUND(num_expr,length)` **对数值表达式截取指定的整数长度,返回四舍五入后的值。
    19. **`SIGN(num_expr)` **对正数执行+1操作,对负数和零执行-1操作。
    20. **`SIN(float_expr)`** 返回以浮点表达式表示的近似于指定角度(以弧度表示)的正弦三角函数的值。
    21. **`SQUARE(float_expr)` **返回浮点表达式的平均值。
    22. **`SQRT(float_expr)`** 返回指定的近似浮点表达式的平方根。
    23. **`TAN(float_expr)` **返回以浮点表达式表示的近似于指定角度(以弧度表示)的正切三角函数的值。

## 五、字符串函数 ##

1.  **字符串函数:对字符串、二进制数据或表达式执行操作**（可用于binary 和varbinary数据类型列,但主要用于char和varchar数据类型）
    
    1.  **`Expr1+expr2` **返回两个表达式的组合形式的字符串。
    2.  **`ASCII(char_expr)` **返回表达式最左边字符的ASCⅡ代码值。
    3.  **`CHAR(int_expr)` **返回到之间的整数表达式的ASCⅡ字符值。如果输入的值不在有效范围内,则返回NULL。
    4.  **`CHARINDEX('pattern',char_expr)`** 返回字符表达式中指定模式的起始位置。
    5.  **`DIFFERENCE(char_expr1,char_expr2)` **根据比较两个字符表达式的相似度,返回到之间的值。表示匹配度最佳。
    6.  **`LEN(char_expr)`** 返回字符表达式的长度。
    7.  **`LOWER(char_expr)` **将字符表达式全部转换为小写。
    8.  **`LTRIM(char_expr)`** 返回删除掉前面空格的字符表达式。
    9.  **`PATINDEX('%pattern%',expr)`** 返回表达式中模式第一次出现的起始位置。返回表示不存在模式形式。
    10. **`REPLICATE(char_expr,int_expr)` **返回重复指定次数的字符表达式产生的字符串。
    11. **`REVERSE(char_expr)` **反转字符表达式。
    12. **`RIGHT(char_expr,int_expr)` **返回从字符表达式最右端起根据指定的字符个数得到的字符。
    13. **`RTRIM(char_expr)` **返回删除掉其后空格的字符表达式。
    14. **`SOUNDEX(char_expr)` **评估两个字符串的相似度后得到的位代码。
    15. **`SPACE(int_expr)` **返回包含指定空格数的字符串。
    16. **`STR(float_expr[,length[,decimal]])` **返回浮点表达式的字符串表示法。
    17. **`STUFF(char_expr1,start,length,char_expr2)` **使用字符表达式替换字符表达式的一部分字符,从指定的位置开始替换指定的长度。
    18. **`SUBSTRING(char_expr,start,length)` **返回从字符表达式的指定位置开始,截取指定长度得到的字符集。
    19. **`UPPER(char_expr)` **将字符表达式全部转换为大写。

## 六、系统函数 ##

1.  **系统函数:从数据库返回在SQLSERVER中的值、对象或设置的特殊信息**（用于返回元数据或配置设置）
    
    1.  **`COALESCE(expr1,expr2, xprN)` **返回第一个非NULL表达式。
    2.  **`COL_LENGTH('table_name','column_name')` **返回列的长度。
    3.  **`COL_NAME(table_id,column_id)` **返回指定的表中的列名。
    4.  **`DATALENGTH('expr')`** 返回任何数据类型的实际长度。
    5.  **`DB_ID([‘database_name'])`** 返回数据库的标识号。
    6.  **`DB_NAME([database_id])` **返回数据库的名称。
    7.  **`GETANSINULL([‘database_name'])`** 返回数据库的默认空性(Nullability)。
    8.  **`HOST_ID()`** 返回工作站的标识号。
    9.  **`HOST_NAME()` **返回工作站的名称。
    10. **`IDENT_INCR('table_or_view')` **有新的记录添加入到表中时计数加。
    11. **`IDENT_SEED('table_or_view')` **返回标识列的起始编号。
    12. **`INDEX_COL('table_name',index_id,key_id)`** 返回索引的列名。
    13. **`ISNULL(expr,value)` **使用指定的值替换的NULL表达式。
    14. **`NULLIF(expr1,expr2)`** Expr1与Expr2相等时,返回Null。
    15. **`OBJECT_ID('obj_name')` **返回数据库对象标识号。
    16. **`OBJECT_NAME('object_id')` **返回数据库对象名。
    17. **`STATS_DATE(table_id,index_id)` **返回上次更新指定索引的统计的日期。
    18. **`SUSER_SID([‘login_name'])`** 返回用户的登录标识号。
    19. **`SUSER_ID([‘login_name'])`** 返回用户的登录标识号。这个函数类似于SUSER\_SID()函数,并且保留了向后的兼容性。
    20. **`SUSER_SNAME([server_user_id])` **返回用户的登录标识号。
    21. **`SUSER_NAME([server_user_id])` **返回用户的登录标识号。这个函数类似于SUSER\_SNAME()函数,并且保留了向后的兼容性。
    22. **`USER_ID('user_name')` **返回用户的数据库标识号。
    23. **`USER_NAME(['user_id'])` **返回用户的数据库名称。

## 七、文本和图像函数 ##

1.  **文本和图像函数:对文本和图像数据执行操作**（通常返回有关文本和图像数据所需的信息。文本和图像数据是以二进制格式的形式进行存储的）
    
    1.  **`TEXTPTR(col_name)` **返回varbinary格式的文本指针值。对文本指针进行检查以确保它指向第一个文本页。
    2.  **`TEXTVALID('table_name.col_name',text_ptr)`**检查给定的文本指针是否有效。返回表示有效,返回表示指针无效。