SQL之函数

一：函数

        函数可以理解为一种工具，用于完成定义明确的任务，如平方根、大小字母转换等。

        有些函数需要输入参数，有些函数不需要输入参数，但是函数必须具有返回值。

        函数分为确定性函数和非确定性函数，确定性函数只需要输入相同的参数，则返回值总是相同的，如abs函数，非确定性函数调用相同的参数，会出现返回值不同的情况，如SQL Server的getdate()函数

       对于非确定函数，不同的关系数据库都使用了限制功能使用，如SQL Server 不允许在创建索引的计算列和创建索引的视图中使用非确定性函数，在Oracle中，基于函数的索引不能使用非确定性函数，在DB2中，不允许在连接条件表达式中使用非确定性函数；在SQL Server中，系统函数可以分为聚合函数、数值函数、字符串函数、数据类型转换函数、日期时间函数、文本图像函数、系统统计函数等。

2：统计标准偏差stdev()函数

       该函数返回指定表达式中所有值的统计标准偏差

       显示不同仓库的工资标准差：   

1. use db_sqlserver2  
2. select 仓库号, STDEV(工资) as 工资标准差 from 职工 group by 仓库号

3：总体标准偏差stdevp()

    返回指定表达式中所有值的总体标准偏差

4：统计方差var()

     返回指定表达式中所有值的统计方差var(expression)，只可用于数字列，空值将被忽略。

5：总体变异方差varp()

      返回指定表达式中所有值的总体变异方差

综合运用：

[html]  view plain copy

1. use db_sqlserver2  
2. select MAX(工资) as 最大工资, STDEV(工资) as 工资标准差,   
3.        STDEVP(工资) as 工资总体标准差, VAR(工资) as 工资统计方差,   
4.        VARP(工资) as 工资总体变异方差 from 职工  

效果图：

 

分类聚合的应用：显示不同城市职工的工资标准差、工资总体标准差、工资统计方差和工资总体变异方差信息

[sql]  view plain copy

1. create view view_mysun   
2. as  
3. select 城市, 工资 from 仓库, 职工 where 仓库.仓库号=职工.仓库号  

[sql]  view plain copy

1. select 城市, STDEV(工资) as 工资标准差, STDEVP(工资) as 工资总体标准差,  
2.        VAR(工资) as 工资统计方差, VARP(工资) as 工资总体变异方差   
3.         from view_mysun group by 城市  

6：算术函数

函数类型	函数	功能
三角函数	sin(浮点表达式)	以弧度表示角的正弦
	cos(浮点表达式)	以弧度表示角的余弦
	cot(浮点表达式)	以弧度表示角的余切
	tan(浮点表达式)	以弧度表示角的正切
反三角函数	asin(浮点表达式)	返回正弦是浮点表达式值的弦度角
	acos(浮点表达式)	返回余弦是浮点表达式值的弦度角
	atan(浮点表达式)	返回正切是浮点表达式值的弦度角
弧度转换函数	degrees(数字表达式)	把角度转换为弧度
	radians(数字表达式)	把弧度转换为角度
幂函数	exp(浮点表达式)	返回表达式的指数值
	log(浮点表达式)	返回表达式的自然对数值
	log10(浮点表达式)	返回表达式以10为底的对数值
	sqrt(浮点表达式)	返回表达式的平方根
	ceiling(数字表达式)	返回大于等于表达式的最小整数
	floor(数字表达式)	返回小于等于表达式的最大整数
	round(数字表达式, 整型表达式)	返回以整型表达式为精度的四舍五入值
符号函数	abs(数字表达式)	返回表达式的绝对值
	sign(数字表达式)	测试参数的正负号
随机函数	rand()	随机产生0到1之间的浮点数
pi()函数	pi()	返回值为圆周率

[sql]  view plain copy

1. select 姓名,工资, SQRT(工资) as 工资的平方根, LOG(工资) as 工资的自然对数值 from 职工  

取近似值的应用

[sql]  view plain copy

1. declare @x real  
2. set @x=5.687  
3. print ceiling(@x)  
4. print floor(@x)  
5. print round(@x, 1)  
6. print round(@x, 2)  

输出如下：
6
5
5.7
5.69

随机函数的使用

[sql]  view plain copy

1. print rand()  
2. print rand()  

7：字符串函数

   a:ascii()函数

      返回字符表达式最左端字符的ASCII码值，如果输入不是0-255之间的ASCII码值，则返回值是一个null值

   b:char()函数

      将ASCII码转换为字符

   c: lower()函数

    把字符串全部转换为小写

   d: upper()函数
    把字符串全部转换为大写

   e:str函数

     将数字数据转换为字符数据

     格式str(float_expression[, length [, decimal]])

       其中float_expression是带小数点的近似数字(float)数据类型的表达式。不要在str()函数中将函数或者子查询用做float_expression, length是总长度，

包括小数点、符号、数字或者空格。默认值为10， decimal是小数点右边的位数。  

1. declare @x char  
2. declare @y varchar(10)  
3. set @x='b'  
4. set @y='hello,How are you!'  
5. print 'b的ASCII码是:' + cast(ascii(@x) as varchar(10))  
6. print '变量y的值为:' + @y  
7. print '把变量y转换为小写字母:' + lower(@y)  
8. print '把变量y转换为大写字母:' + upper(@y)  
9. print 'str()函数的应用'  
10. print str(124.654, 6, 1)  
11. print str(124.654, 3, 1)  

循环输出26个小写英文字母和其ASCII码

[sql]  view plain copy

1. declare @i int  
2. set @i = 0  
3. while @i < 26  
4.  begin  
5.     print char(ascii('a') + @i) + '的ASCII码是:' + cast(ascii('a') + @i as varchar(50))  
6.     set @i = @i + 1  
7.  end  

8：字符长度函数以及去空格函数

    a:len()函数

      测量字符表达式的长度

    b:ltrim()函数

     将字符表达式头部的空格去掉

    c:rtrim()函数

     将字符表达式尾部的空格去掉

   

9：取子串函数

     a:left(character_expression, integer_expression)

         返回从字符串左边开始指定个数的字符，如果 integer_expression为负，则返回空字符串

     b:right(character_expression, integer_expression)

         返回从字符串右边开始指定个数的 integer_expression字符，如果 integer_expression是负数，则返回一个错误

     c:substring(expression, start, length)

         其中expression是字符串、二进制字符串、text、image、列或者包含列的表达式。不要使用包含聚合函数的表达式，start是一个整数，指定

     子串的开始位置，length是一个整数，指定子串的长度。

        在字符数中必须指定使用ntext, char或varchar数据类型的偏移量(start 和 length)， 在字节数中必须指定使用text, image, binary 或varbinary数据类型的偏移量。

[sql]  view plain copy

1. print left('hello,how are you', 5)  
2. print right('hello,how are you', 12)  
3. print substring('hello,how are you', 7, 11)  

输出：

[html]  view plain copy

1. hello  
2. ,how are you  
3. how are you  

10：字符串操作函数

      a:replicate(character_expression, integer_expression)

         以指定的次数重复字符表达式

      b:reverse(character_expression)

         反转字符表达式

      c:replace(‘string_expression1’, 'string_expression2', 'string_expression3')

        string_expression1：待搜索的字符表达式

        string_expression2：待查找的字符串表达式

        string_expression3：替换用的字符串表达式

      d:space(integer_expression)

      返回由重复的空格组成的字符串

      e:stuff(character_expression, start, length, character_expression)

       删除指定长度的字符并在指定的起始点插入另一组字符

[sql]  view plain copy

1. print replicate('hello!', 3)  
2. print replicate('hello!', -5)  
3. print reverse('hello!')  
4. print replace('SmallFish Online', 'SmallFish', 'WhiteGood')  
5. print stuff('hello', 2, 3, 'stufffff')  

输出效果：

[html]  view plain copy

1. hello!hello!hello!  
2.    
3. !olleh  
4. WhiteGood Online  
5. hstufffffo  

利用字符串操作函数绘制菱形美元图案

[sql]  view plain copy

1. declare @count int  
2. set @count=1  
3. while @count<=5  
4.  begin  
5.     print space(5-@count) + replicate('*', @count*2 -1)  
6.     set @count = @count + 1  
7.  end  
8.   
9. set @count = 5  
10. while @count >= 0  
11.   begin  
12.      print space(5-@count) + replicate('*', @count*2 -1)  
13.      set @count = @count -1  
14.   end  

输出效果图：

11：数据类型转换函数

     a:cast(expression as data_type)

     b:converse(data_type[length], expression[, style])

        使用此函数时，应注意一下几个问题：

         *data_type必须是数据库系统自定义的数据，用户自定义的数据类型不能在此使用

         *length用于指定数据的长度，默认值是10

         *把char或者varchar类型转换为int类型时，结果必须是带正负号的数值

         *将text类型转换为char或者varchar类型时，最多有8000个字符

         *将image类型转换为binary或者varbinary类型时，最多有8000个字符

         *将整型转换成money或者smallmoney类型时，需按定义的国家或地区的货币单位处理。

12：日期时间函数

       a:getdate()函数

         返回系统当前的日期和时间

       b:year函数

          year(date_expression)返回date_expression中的年份

       c:month函数

          month(date_expression)返回date_expression中的月份

       d:day函数

         day(date_expression)返回date_expression中的日期值

       e:datename函数

          datename(datepart, date_expression)返回日期的指定部分，以字符串的形式返回

       f:datepart函数

         

      g:dateadd函数

         dateadd(datepart, number, date), 返回指定日期date加上指定的额外日期间隔number产生的新日期

      h:datediff函数

        datediff(datepart, startpart, endpart),返回startdate和enddate的差距值，其结果值是一个带有正负号的整数值

显示当前日期

 

[sql]  view plain copy

1. print getdate()  

Mar 22 2012  4:33PM

[sql]  view plain copy

1. print '当前日期是:' + cast(year(getdate()) as varchar(10)) + '年'  
2.                      + cast(month(getdate()) as varchar(10)) + '月'  
3.                      + cast(day(getdate()) as varchar(10)) + '日'  

当前日期是:2012年3月22日

[sql]  view plain copy

1. print '现在的时间是:' + datename(hour, getdate()) + ':' + datename(minute, getdate())   
2.                       + ':' + datename(second, getdate())      

现在的时间是:16:37:32

[sql]  view plain copy

1. select DATEDIFF(HOUR, '2008-5-6 05:36', '2008-5-7 03:24') as 小时差,   
2.        DATEDIFF(MINUTE, '2008-5-6 05:36', '2008-5-7 03:24') as 分钟差  

显示星期

   

[sql]  view plain copy

1. select DATENAME(DW, getdate()) as 今天星期几  

显示本周第一天的日期

 

[sql]  view plain copy

1. select DATEADD(wk, datediff(wk, 0, GETDATE()), 0) as 本周第一天的日期  

显示7年之内的订购单信息

[sql]  view plain copy

1. select * from 订购单 where 订购日期>DATEADD(year, -7, getdate())  

[sql]  view plain copy

1. select * from 订购单 where 订购日期>DATEDIFF(day, 7*360, getdate())  

13：自定义函数

    格式

    create function 函数名(@parameter 变量类型 [, @parameter 变量类型])

    returns 变量类型

    as 

    begin 

        命令行或者程序块

    end 

14：标量值函数的创建与调用

     

[sql]  view plain copy

1. create function db_fun1(@x int)  
2. returns int  
3. as  
4. begin  
5.   declare @y int  
6.   set @y = @x * @x  
7.   return @y  
8. end  

[sql]  view plain copy

1. print dbo.db_fun1(7)  

[sql]  view plain copy

1. declare @x int  
2. set @x = 1  
3. while @x <= 7  
4.   begin  
5.      select CAST(@x as varchar(10)) + '*' + CAST(@x as varchar(10)) as 运算式,  
6.      dbo.db_fun1(@x) as 乘方结果  
7.      set @x = @x + 1   
8.   end  

[sql]  view plain copy

1. select 职工.*, dbo.db_fun1(工资) as 工资的平方 from 职工  

15：表值函数的创建和调用

    

[sql]  view plain copy

1. create function dbo.db_fun2()  
2. returns table  
3. as  
4. return select * from 仓库  

[sql]  view plain copy

1. select * from dbo.db_fun2()  

创建带有输入参数的表值函数

  

[sql]  view plain copy

1. create function dbo.db_fun3(@t varchar(50))  
2. returns table  
3. as  
4. return (select * from 职工 where 仓库号= @t)  

[sql]  view plain copy

1. select * from dbo.db_fun3('wh1')  --显示wh1仓库中的职工信息  

16：查看函数的属性和功能

   

[sql]  view plain copy

1. select name, crdate from sysobjects where type='if' or type='fn'  

标量值函数的类型标识是fn， 而表值函数的类型标识是if

查看函数的属性信息：

   

[sql]  view plain copy

1. execute sp_help db_fun1  

查看函数所使用数据对象的信息：

[sql]  view plain copy

1. execute sp_depends db_fun2  

查看函数的功能代码：

 

[sql]  view plain copy

1. execute sp_helptext db_fun1  

修改函数：

  alter function .....

重命名函数：

   sp_rename 原函数名 新函数名

删除函数：

  drop function 函数名[, ...]