SQL 数据库函数有哪些?What are the SQL database functions?

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

了解可在 SQL 数据库中使用的内置函数类别。Learn about the categories of built-in functions you can use with SQL databases. 可以使用内置函数,或创建自己的用户定义函数。You can use the built-in functions or create your own user-defined functions.

聚合函数Aggregate functions

聚合函数对一组值执行计算,并返回单个值。Aggregate functions perform a calculation on a set of values and return a single value. 在 select 列表或 SELECT 语句的 HAVING 子句中允许使用它们。They are allowed in the select list or the HAVING clause of a SELECT statement. 可以将聚合与 GROUP BY 子句结合使用,来计算行类别的聚合。You can use an aggregation in combination with the GROUP BY clause to calculate the aggregation on categories of rows. 使用 OVER 子句来计算特定范围内的值的聚合。Use the OVER clause to calculate the aggregation on a specific range of value. OVER 子句不能跟在 GROUPING 或 GROUPING_ID 聚合后。The OVER clause cannot follow the GROUPING or GROUPING_ID aggregations.

所有聚合函数都是确定性的,这意味着对相同的输入值进行运算时,它们始终返回相同的值。All aggregate functions are deterministic, which means they always return the same value when they run on the same input values. 有关详细信息,请参阅 确定性函数和不确定性函数。|For more information, see Deterministic and Nondeterministic Functions.|

分析函数Analytic functions

解析函数基于一组行计算聚合值。Analytic functions compute an aggregate value based on a group of rows. 不过,与聚合函数不同,分析函数可能针对每个组返回多行。However, unlike aggregate functions, analytic functions can return multiple rows for each group. 可以使用分析函数来计算移动平均线、运行总计、百分比或一个组内的前 N 个结果。You can use analytic functions to compute moving averages, running totals, percentages, or top-N results within a group.

排名函数Ranking functions

排名函数为分区中的每一行返回一个排名值。Ranking functions return a ranking value for each row in a partition. 根据所用函数的不同,某些行可能与其他行接收到相同的值。Depending on the function that is used, some rows might receive the same value as other rows. 排名函数具有不确定性。Ranking functions are nondeterministic.

行集函数Rowset functions

行集函数 返回可在 SQL 语句中像表引用一样使用的对象。Rowset functions Return an object that can be used like table references in an SQL statement.

标量函数Scalar functions

对单一值进行运算,然后返回单一值。Operate on a single value and then return a single value. 只要表达式有效,即可使用标量函数。Scalar functions can be used wherever an expression is valid.

标量函数类别Categories of scalar functions

函数类别Function category 描述Description
配置函数Configuration Functions 返回当前配置信息。Return information about the current configuration.
转换函数Conversion Functions 支持数据类型强制转换和转换。Support data type casting and converting.
游标函数Cursor Functions 返回游标信息。Return information about cursors.
日期和时间数据类型及函数Date and Time Data Types and Functions 对日期和时间输入值执行运算,然后返回字符串、数字或日期和时间值。Perform operations on a date and time input values and return string, numeric, or date and time values.
JSON 函数JSON Functions 验证、查询或更改 JSON 数据。Validate, query, or change JSON data.
逻辑函数Logical Functions 执行逻辑运算。Perform logical operations.
数学函数Mathematical Functions 基于作为函数的参数提供的输入值执行运算,然后返回数字值。Perform calculations based on input values provided as parameters to the functions, and return numeric values.
元数据函数Metadata Functions 返回有关数据库和数据库对象的信息。Return information about the database and database objects.
安全函数Security Functions 返回有关用户和角色的信息。Return information about users and roles.
字符串函数String Functions 对字符串(char 或 varchar)输入值执行运算,然后返回一个字符串或数字值 。Perform operations on a string (char or varchar) input value and return a string or numeric value.
系统函数System Functions 执行运算后返回 SQL ServerSQL Server 实例中有关值、对象和设置的信息。Perform operations and return information about values, objects, and settings in an instance of SQL ServerSQL Server.
系统统计函数System Statistical Functions 返回系统的统计信息。Return statistical information about the system.
文本和图像函数Text and Image Functions 对文本或图像输入值或列执行运算,然后返回有关值的信息。Perform operations on text or image input values or columns, and return information about the value.

函数确定性Function Determinism

SQL ServerSQL Server 内置函数可以是确定的或是不确定的。built-in functions are either deterministic or nondeterministic. 如果任何时候用一组特定的输入值调用内置函数,返回的结果总是相同的,则这些内置函数为确定的。Functions are deterministic when they always return the same result any time they are called by using a specific set of input values. 如果每次调用内置函数时,即使用的是同一组特定输入值,也总返回不同结果,则这些内置函数为不确定的。Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values. 有关详细信息,请参阅 确定性函数和不确定性函数For more information, see Deterministic and Nondeterministic Functions

函数排序规则Function Collation

使用字符串输入并返回字符串输出的函数,对输出使用输入字符串的排序规则。Functions that take a character string input and return a character string output use the collation of the input string for the output.

使用非字符输入并返回字符串的函数对输出使用当前数据库的默认排序规则。Functions that take non-character inputs and return a character string use the default collation of the current database for the output.

使用多个字符串输入并返回字符串的函数,使用排序规则的优先顺序规则设置输出字符串的排序规则。Functions that take multiple character string inputs and return a character string use the rules of collation precedence to set the collation of the output string. 有关详细信息,请参阅排序规则优先顺序 (Transact-SQL)For more information, see Collation Precedence (Transact-SQL).

另请参阅See Also

CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)
确定性函数和不确定性函数 Deterministic and Nondeterministic Functions
使用存储过程 (MDX)Using Stored Procedures (MDX)