SQL中的函数

目录

前言

一、系统内置函数

1、数学函数

2、日期和时间函数

3、聚合函数

4、字符串函数

二、自定义函数

1、标量函数的创建与调用

2、内嵌表值函数的创建与调用

3、多语句表值函数的创建与调用

---

前言

        函数是由一个或多个 T-SQL 语句组成的子程序，可用于封装代码以便重复使用。

一、系统内置函数

        T-SQL提供的内置函数，按照返回值是否具有确定性可分为确定性函数和非确定性函数两大类。

• 确定性函数：每次使用特定的输入值调用该函数时，返回的结果都相同。
• 非确定性函数：每次使用特定的输入值调用时，返回的结果都不同。

        函数 DATEADD为确定性函数，因为对于任何给定参数，返回的结果总是相同。函数 GETDATE为非确定性函数，因为每次执行这个函数，返回的结果都不同。

1、数学函数

常用数学函数

函数名称	函数功能	函数名称	函数功能
ABS	求绝对值	POWER	求 x的 y 次方
COS	余弦函数	RAND	求随机数
COT	余切函数	ROUND	四舍五入
EXP	计算 e的 x次幂	SIN	正弦函数
FLOOR	求仅次于最小值的值	SQUARE	开方
LOG	求自然对数	SQRT	求平方根
PI	常量，圆周率	TAN	正切函数

2、日期和时间函数

• GETDATE()：返回系统当前的日期和时间。
• DATEADD(datepart, integer_expression, date_expression)：返回指定日期 date_expression（日期表达式）加上指定的额外日期间隔 integer_expression（整形表达式）产生的新日期。
• DATEDIFF(datepart, date_expression1, date_expression2)：返回两个指定日期在 datepart 方面的不同之处，即 date_expression2 超过 date_expression1 的差值，结果值是一个带有正负号的整数值。
• DATENAME(datepart, date_expression)：以字符串的形式返回日期的指定部分，此部分由 datepart 来指定。
• DATEPART(datepart, date_expression)：以整数值的形式返回日期表达式的指定部分。此部分由 datepart 来指定。
• day(date_expression)：返回日期表达式中的日。
• month(date_expression)：返回日期表达式中的月。
• year(date_expression)：返回日期表达式中的年。

        DATEPART() 函数 和 DATENAME 函数极其相似，只不过前者返回的是时间的名称，后者返回的是具体的时间数值。

3、聚合函数

常用聚合函数

函数名	描述
sum(列名)	返回一个数字列的总和。
avg(列名)	对一个数据列计算平均值。
min(列名)	返回一个数字、字符串或日期列的最小值。
max(列名)	返回一个数字、字符串或日期列的最大值。
count(列名)	返回一个列的数据项数，条数。
count(*)	返回找到的函数

4、字符串函数

• ASCII(char_expression)：返回表达式最左边一个字符的 ASCII码。
• CHAR(integer_expression)：返回整数所代表的 ASCII码值所对应的字符。
• LOWER(char_expression)：将大写字符转为小写字符。
• UPPER(char_expression)：将小写字符转为大写字符。
• LTRIM(char_expression)：删除字符串开始部分的空格。
• RTRIM(char_expression)：删除字符串结尾部分的空格。
• RIGHT(char_expression, integer_expression)：返回 char_expression 字符串中 integer_expression 个字符以后的部分字符串，integer_expression为负数时，返回 NULL。
• SPACE(integer_expression)：返回由 integer_expression 个空格组成的字符串，integer_expression为负数时，返回 NULL。
• STR(float_expression [ , length [ , decimal ] ])：将一个数值类型数据转为字符串，length 为字符串长度，decimal 为小数点的位数。
• STUFF(char_expression1, start, length, char_expression2)：从 char_expression1 字符串的 start个字符位置删除 length个字符，然后把 char_expression2 字符串插入到 char_expression1 的 start 处。
• SUBSTRING(expression, start, length)：从 expression 的第 start 个字符处返回 length 个字符。
• REVERSE(char_expression)：返回 char_expression 的逆序。
• CHARINDEX( ' pattern ', char_expression )：返回指定 pattern字符串在表达式中的起始位置。

二、自定义函数

        在 SQL SERVER 中，根据函数返回值类型，将不同自定义函数分为 标量函数、内嵌表值函数 和多语句表值函数。

• 标量函数：返回一个确定类型的变量值，其返回值类型除了 TEXT、NTEXT、IMAGE、CURSOR、TIMESTAMP 和 TABLE 类型外的其它数据类型。函数体语句定义在 BEGIN ··· END 语句内。
• 内嵌表值函数：返回值为一个表。内嵌表值函数没有由 BEGIN ··· END 语句括起来的函数体。返回的表由一个位于 RETURN 子句中的 SELECT 命令段从数据库中筛选出来。内嵌表值函数功能相当于一个参数化的试图。
• 多语句表值函数：可以看作是 内嵌表值函数和内嵌表值函数的结合体。它返回的是一个表，但它和标量函数一样有一个用  BEGIN ··· END 括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。因此，它可以进行多次查询，对数据进行多次筛选与合并，弥补了内嵌表值函数的不足。

1、标量函数的创建与调用

--  创建标量函数的语法格式

CREATE  FUNCTIOIN  [ owner_name. ]   function_name    /*  函数名部分  */

        ( [ { @parameter_name   [ AS ]   parameter_data_type

                [ = DEFAULT ] }   [ , ... n ]  ] )                                 /*  形参定义部分  */

RETURNS   return_data_type                                               /*  返回参数的类型  */

[ AS ]

BEGIN

        function_body                                                                 /*  函数体部分  */

                RETURN  expression                                              /*  返回语句  */

END

 

--  owner_name：指定自定义函数的所有者。

--  function_name：指定自定义函数的名称。

--  @parameter_name：定义一个或多个参数，一个函数最多可以定义1024个参数，参数的作用范围是整个函数。

--  parameter_data_type 和 return_data_type 指定参数的数据类型和 返回值类外的其它数据类型。

--  function_body：是函数体部分，决定了函数的返回值。

--  expression：指定自定义函数返回的标量值表达式。

--  当函数参数由默认值时，调用该函数时必须指定默认 DEFAULT 关键字才能获取默认值。

--  示例：求 sc表中某门课程的平均成绩。

create   function   average  (@cn  char(20))   returns   float

as

begin

        declare   @aver   float

        select  @aver = (select  avg(score)   from  sc  where   sno = @cn)

        return   @aver

end

--  调用标量函数的语法格式

owner_name.function_name  (parameter_expression   1 ··· parameter_expression   n)

 

--  含义为： 所有者. 函数名(实参1, 实参2, ... , 实参n)。

当调用自定义的标量函数时，必须提供至少由两部分组成的名称（所有者. 函数名）。可以在 SELECT 语句中调用，实参可为已赋值的局部变量或表达式；也可以使用 EXECUTE 语句调用，方法与调用存储过程相同。

--  示例：求 C001号课的平均成绩。

declare   @course   char(6)

set   @course = 'C001'

select   dbo.average(@course)   as   'C001号课的平均成绩'

2、内嵌表值函数的创建与调用

--  创建内嵌表值函数的语法格式

CREATE  FUNCTIOIN  [ owner_name. ]   function_name    /*  函数名部分  */

        ( [ { @parameter_name   [ AS ]   parameter_data_type

                [ = DEFAULT ] }   [ , ... n ]  ] )                                 /*  形参定义部分  */

RETURNS   table                                                                  /*  返回参数的类型  */

[ AS ]

RETURN   [ ( SELECT   statement ) ]                         /*  通过 SELECT 语句返回内嵌表  */

 

--  table：指定返回值为一个表。

--  SELECT   statement：指单个 SELECT语句，确定返回的表的数据。

--  示例：查看某个专业所有学生的学号、姓名、所选课程的课程号和成绩。

create   function   st_func(@major   nvarchar(20))   returns   table

as   return

        (select   student.sno, student.sname, sc.cno, sc.score  from  student, sc

                where  specialty = @major   and   student.sno  = sc.sno)

        因为内嵌套表值函数的返回值为 table 类型，所以在调用这类函数时，只能通过 SELECT语句。

--  示例：查看计算机专业所有学生的学号、姓名、所选的课程号和成绩。

select  *  from  st_func('计算机')

3、多语句表值函数的创建与调用

        内嵌表值函数 和多语句表值函数的返回值都是表，其不同之处在于，内嵌表值函数没有函数体，返回的表是单个 SELECT语句的结果集；而多语句表值函数在 BEGIN ··· END 块中定义的函数主体包含 T-SQL 语句，这些语句可生成行，并将行插入至表中，最后返回表。

--  多语句表值函数的创建语法格式

CREATE  FUNCTIOIN  [ owner_name. ]   function_name    /*  函数名部分  */

        ( [ { @parameter_name   [ AS ]   parameter_data_type

                [ = DEFAULT ] }   [ , ... n ]  ] )                                 /*  定义函数参数部分  */

RETURNS   @return_variable  table  <table_definition>      /*  定义作为返回值的表  */

[ AS ]

BEGIN

        function_body                                                                 /*  函数体部分  */

                RETURN      

END

 

--  @return_variable：是一个 TABLE 类型的变量，用于存储和累积返回的表中的数据行。其余参数与标量函数相同。

--  示例：创建多语句表值函数，通过学号作为实参调用该函数，可显示该学生的姓名，以及各门功课的成绩和学分。

create   function   st_score  (@no  char(20))   returns   @score   table

(

s_no  char(20),

s_name   nvarchar(20),

c_name   nvarchar(20),

c_score   tinyint,

c_credit   tinyint

)

as   

begin

        insert   into   @score

        select  s.sno, s.sname, c.cname, sc.score, c.credit

        from   student  s,  course   c,  sc   sc  

        where   s.sno = sc.sno  and  sc.cno = c.cno  and  s.sno = @co

        return

end

        多语句表值函数的调用方法与内嵌表值函数的调用方法相同，只能通过 SELECT 语句调用。

--  示例：查询201602001号学生的姓名以及各门功课的成绩和学分。

select  *  from  st_score('201602001')

若有不足之处，欢迎大佬斧正。