当前位置: 首页 > 网络学院 > 服务端脚本教程 > SQL > SQL 函数
SQL has a lot of built-in functions for counting and calculations.
SQL有很多内置的函数,可以用来计算以及记数
The syntax for built-in SQL functions is:
使用SQL内置函数的语法:
SELECT function(column) FROM table |
There are several basic types and categories of functions in SQL. The basic types of functions are:
在SQL中有几种基础类型和分类。基础的函数类型有:
Aggregate functions operate against a collection of values, but return a single value.
合计函数可以将多个值经过操作后返回出一个值
Note: If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause!!
注意:如果在一SELECT声明中有多个其它的表达式,那么SELECT必须有GROUP BY子句!
Name | Age |
---|---|
Hansen, Ola | 34 |
Svendson, Tove | 45 |
Pettersen, Kari | 19 |
函数 | 描述 |
---|---|
AVG(column) | Returns the average value of a column 返回一列的平均值 |
COUNT(column) | Returns the number of rows (without a NULL value) of a column 返回一列中有多少记录行(不包括NULL值的) |
COUNT(*) | Returns the number of selected rows 返回所选择记录行的数量 |
FIRST(column) | Returns the value of the first record in a specified field 返回指定范围内第一条数据记录的值 |
LAST(column) | Returns the value of the last record in a specified field 返回指定范围内最后一条数据记录的值 |
MAX(column) | Returns the highest value of a column 返回一列中最大的值 |
MIN(column) | Returns the lowest value of a column 返回一列中最小的值 |
STDEV(column) | |
STDEVP(column) | |
SUM(column) | Returns the total sum of a column 返回一列中的总计值 |
VAR(column) | |
VARP(column) |
函数 | 描述 |
---|---|
AVG(column) | Returns the average value of a column 返回一列的平均值 |
BINARY_CHECKSUM | |
CHECKSUM | |
CHECKSUM_AGG | |
COUNT(column) | Returns the number of rows (without a NULL value) of a column 返回一列中有多少记录行(不包括NULL值的) |
COUNT(*) | Returns the number of selected rows 返回所选择记录行的数量 |
COUNT(DISTINCT column) | Returns the number of distinct results 返回不重复结果的数量 |
FIRST(column) | Returns the value of the first record in a specified field (not supported in SQLServer2K) 返回指定范围内第一条数据记录的值(SQLServer2000里不支持) |
LAST(column) | Returns the value of the last record in a specified field (not supported in SQLServer2K) 返回指定范围内最后一条数据记录的值(SQLServer2000里不支持) |
MAX(column) | Returns the highest value of a column 返回一列中最大的值 |
MIN(column) | Returns the lowest value of a column 返回一列中最小的值 |
STDEV(column) | |
STDEVP(column) | |
SUM(column) | Returns the total sum of a column 返回一列中的总计值 |
VAR(column) | |
VARP(column) |
Scalar functions operate against a single value, and return a single value based on the input value.
数量函数可以处理单个值,并基于该值返回。
函数 | 描述 |
---|---|
UCASE(c) | Converts a field to upper case 转换成大写 |
LCASE(c) | Converts a field to lower case 转换成小写 |
MID(c,start[,end]) | Extract characters from a text field 从文字范围内抽取所需要的字符集 |
LEN(c) | Returns the length of a text field 返回字长 |
INSTR(c,char) | Returns the numeric position of a named character within a text field 返回某字符在文字范围内的位置,用数字来表示 |
LEFT(c,number_of_char) | Return the left part of a text field requested 返回从左向右所请求的部分文字 |
RIGHT(c,number_of_char) | Return the right part of a text field requested 返回从右向左所请求的部分文字 |
ROUND(c,decimals) | Rounds a numeric field to the number of decimals specified 将数字完全的十进制数字 |
MOD(x,y) | Returns the remainder of a division operation 返回运算后的余值 |
NOW() | Returns the current system date 返回当前系统日期 |
FORMAT(c,format) | Changes the way a field is displayed 改变显示方式 |
DATEDIFF(d,date1,date2) | Used to perform date calculations 用来执行日期运算 |