SQL语法整理[6]——函数

 

 

SQL语法整理[6]——函数

系统函数

(1)配置函数

@@VERSION

/*使用@@VERSION查看当前SQL Server版本*/

SELECT   @@VERSION  AS  ‘SQL Server版本’

 

 

 @@SERVERNAME

/*查看SQL Server的当前实例名称*/

SELECT  @@SERVERNAME  AS   ‘服务名称’

 

(2)日期与时间函数

GETDATE()

/*查看当前系统时间*/

SELECT  GETDATE()  AS  ‘当前系统时间’

 

YEAR(date)/ MONTH/DAY

/*查看给定时间的年份*/

SELECT  YEAR(‘03/12/2005’)  AS   ‘年’

 

DATEADD(datepart,number,date)

/*查看20天后的系统时间*/

SELECT  DATEADD(day, 20, GETDATE())

AS   '二十天后的时间'

 

DATEDIFF(datepart,startdate,enddate)

/*查看系统当前与北京奥运会相差的天数*/

SELECT  DATEDIFF(day, GETDATE(), ‘08/8/2008')

AS ‘今天与北京奥运会还差多少天'

 

DATENAME(datepart,date)

/*查看当前系统时间的月份值*/

SELECT DATENAME(month,getdate()) as '本月'

 

(3)数学函数

COUNT()

返回的是INT数据类型的数组项数，主要用于统计当前表中有多少数据量，其中空值的行数不记录在内。

/*统计所有学生信息*/

SELECT   COUNT(*)  as ‘学生总数’  FROM     student

 

AVG()/ABS()

ROUND(numeric_expression,length[,function])

/*计算学生的平均成绩(保留两位小数)*/

SELECT   ROUND(AVG(result),2)  as  ‘主科平均分’

 

(4)字符串函数

SUBSTRING(expression,start,length)

LOWER(character_expression)

LTRIM(character_expression)---去除字符串两端的空格

d.ASCII(character_expression)

e.REVERSE(character_expression)

f.REPLACE(string_expression1,string_expression2,string_expression3)

SELECT    REPLACE(‘ABCDECCC’,’C’,’X’)     AS   ‘使用REPLACE函数运行结果’

GO

 

g.LEFT(charater_expression,integer_expression)

SELECT     LEFT(‘ADBADSS’,5)   AS  ‘返回前5位字母’

GO

 

(5)其他常用函数

a.ISDATE(expression1)

用于判断输入的表达式是否为有效日期，返回的数值为1或者0。

 

b.ISNULL(expression1,expression2)

此函数判断expression1的值是否为NULL，如果expression1为空，则返回expression2；如果不为空，则返回expression1的原值。在使用该函数时，expression1与expression2的类型必须是相同的。

 

c.ISNUMERIC(expression)---判断是否是数字

SELECT    ISNUMERIC(23432.2345), ISNUMERIC(23),

ISNUMERIC('ADG')

GO

 

d.PRINT(expression)

PRINT('HELLO WORLD')

 

e.CAST(expression AS datatype)

将expression转换为另一种数据类型

SELECT   name   AS   ‘学生姓名’,

CAST(sysdate  AS   char(20))  AS   ‘数据备份时间’

FROM    student_bak

 

f.CONVERT(data_type[(length)],expression[,style])

把表达式的值从一种数据类型转换为另一种。但比CAST函数更加细致。

SELECT  CONVERT(CHAR,GETDATE(),101)   AS    ‘转换成字符串的时间’

GO

 

用户自定义函数

(1)标量函数

(2)内嵌表值函数

(3)多语句表值函数

标量函数是一个简单的数值,例如INT、CHAR、DECIMAL(十进制)等。标量函数的函数体要以BEGIN开始，并且用END来标识结束。

内嵌与多语句表值函数，返回的都是一个TABLE的数据类型，即一个表。其中，内嵌表值函数返回的是一个用SELECT语句的查询值，而多语句则返回的是用户自定的TABLE变量，是包含在BEGIN与END之间的函数体。

 

(1)标量函数

CREATE FUNCTION [ schema_name. ] function_name                            

 ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type     

[ = default ] }                                                           

[ ,...n ]                                                                 

]                                                                         

 )                                                                        

RETURNS return_data_type                                                  

 [ WITH <function_option> [ ,...n ] ]                                     

 [ AS ]                                                                   

BEGIN                                                                     

 function_body                                                            

 RETURN scalar_expression                                                 

END                                                                       

[ ; ]        

 

例如：创建函数f(x)=x*x+2X+1

/*创建一个名称为f()的函数*/

CREATE  FUNCTION      f(@x   INT) RETURNS   INT

AS

BEGIN

       RETURN @X*@X+2*@X+1

END

GO

 

/*将@x=3带入函数*/

SELECT   dbo.f(3)    as   ‘参数为3的函数计算结果’

GO

 

（使用用户自定义函数时，都必须加上前面的所有者，前面涉及到dbo就是一个架构，当前创建的用户自定义函数属于这个架构的。）

 

 

 

(2)内嵌表值函数

CREATE FUNCTION [ schema_name. ] function_name                            

( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type     

[ = default ] }                                                           

[ ,...n ]                                                                 

]                                             

)                                             

RETURNS TABLE                                 

[ WITH <function_option> [ ,...n ] ]          

[ AS ]                                        

RETURN [ ( ] select_stmt [ ) ]                

[ ; ]       

 

例如：创建一个函数，在输入学生编号以后，输出学生、老师的姓名。

/*在school数据库中创建带学生编号参数的函数*/

CREATE   FUNCTION  select_student(@id   INT)

RETURNS  TABLE

AS

RETURN

SELECT   s.name  AS   ‘学生姓名’,

t.name    AS   ‘老师姓名’

FROM  student   s   INNER  JOIN  teachers    t

ON    s.teacher_id=t.id

WHERE    s.id=@id

GO

 

/*在函数中输入学生编号*/

SELECT   *    FROM    dbo.select_student(2006005)

GO

 

 (3)多语句表值函数

CREATE FUNCTION [ schema_name. ] function_name                            

( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type     

[ = default ] }                                                           

[ ,...n ]                                                                 

]                                                                         

)                                                                         

RETURNS @return_variable TABLE < table_type_definition >                  

 [ WITH <function_option> [ ,...n ] ]                                     

[ AS ]                                                                    

BEGIN                                                                     

function_body                                                             

RETURN                                                                    

END                                                                       

[ ; ]   

 

@return_variable就是要返回的表，这里用到了一个之前没有用过的数据类型TABLE ，

< table_type_definition >表示对表限制

例如：创建一个函数，输入学生的编号，输出学生、老师及家长姓名。

/*创建一个带学生编号参数的多语句表值函数*/

CREATE     FUNCTION   select_info(@id  INT)

RETURNS  @information  TABLE

(

   学生姓名    CHAR(20),

   老师姓名    CHAR(20),

   父亲姓名    CHAR(20),

   母亲姓名    CHAR(20)

)

AS

   BEGIN

         INSERT    INTO    @information

         SELECT    s.name, t.name,p.father,p.mother

         FROM   student  s  INNER  JOIN   teachers   t

         ON   t.id=s.teacher_id   INNER   JOIN   parents  p

         ON   s.parents_id=p.id   WHERE    s.id=@id

         RETURN

   END;

GO

 

SELECT * FROM  dbo.select_info(2006005)

GO

 

修改/重新命名/删除函数

修改：

ALTER FUNCTION function_name

<NEW function content>

 

重命名：

SP_RENAME

 

删除：

DROP FUNCTION {[owner.]function_name}[,…n]

 

——大二的期末考试整理 peace_power@126.com