SQL学习之函数

原创 2012年03月22日 17:32:33

1:函数

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

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

   函数分为确定性函数和非确定性函数,确定性函数只需要输入相同的参数,则返回值总是相同的,如abs函数,非确定性函数调用相同的参数,

  会出现返回值不同的情况,如SQL Server的getdate()函数

 对于非确定函数,不同的关系数据库都使用了限制功能使用,如SQL Server 不允许在创建索引的计算列和创建索引的视图中使用非确定性函数,

在Oracle中,基于函数的索引不能使用非确定性函数,在DB2中,不允许在连接条件表达式中使用非确定性函数

  在SQL Server中,系统函数可以分为聚合函数、数值函数、字符串函数、数据类型转换函数、日期时间函数、文本图像函数、系统统计函数等。


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

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

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

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

3:总体标准偏差stdevp()

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

    

4:统计方差var()

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


5:总体变异方差varp()

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

 

综合运用:

    

use db_sqlserver2
select MAX(工资) as 最大工资, STDEV(工资) as 工资标准差, 
       STDEVP(工资) as 工资总体标准差, VAR(工资) as 工资统计方差, 
       VARP(工资) as 工资总体变异方差 from 职工
效果图:

 


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

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

select 城市, STDEV(工资) as 工资标准差, STDEVP(工资) as 工资总体标准差,
       VAR(工资) as 工资统计方差, VARP(工资) as 工资总体变异方差 
        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()

返回值为圆周率



print abs(-2)
print abs(5)
print sign(10)
print sign(-45)
print sign(0)

2
5
1
-1
0


print log(15)
print log10(15)
print sqrt(8)
print exp(3)

2.70805
1.17609
2.82843
20.0855


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


取近似值的应用

 

declare @x real
set @x=5.687
print ceiling(@x)
print floor(@x)
print round(@x, 1)
print round(@x, 2)
输出如下:
6
5
5.7
5.69


随机函数的使用

print rand()
print rand()

0.983736
0.0224439


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是小数点右边的位数。

     

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

b的ASCII码是:98
变量y的值为:hello,How 
把变量y转换为小写字母:hello,how 
把变量y转换为大写字母:HELLO,HOW 
str()函数的应用
 124.7
125


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

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

a的ASCII码是:97
b的ASCII码是:98
c的ASCII码是:99
d的ASCII码是:100
e的ASCII码是:101
f的ASCII码是:102
g的ASCII码是:103
h的ASCII码是:104
i的ASCII码是:105
j的ASCII码是:106
k的ASCII码是:107
l的ASCII码是:108
m的ASCII码是:109
n的ASCII码是:110
o的ASCII码是:111
p的ASCII码是:112
q的ASCII码是:113
r的ASCII码是:114
s的ASCII码是:115
t的ASCII码是:116
u的ASCII码是:117
v的ASCII码是:118
w的ASCII码是:119
x的ASCII码是:120
y的ASCII码是:121
z的ASCII码是:122

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

    a:len()函数

      测量字符表达式的长度

    b:ltrim()函数

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

    c:rtrim()函数

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

   

declare @str varchar(30)
set @str = ' hello, how are you?'
print @str
print ltrim(@str)
print rtrim(@str)
print len(@str)
print len(ltrim(@str))
print len(rtrim(@str))
输出:
 hello, how are you?
hello, how are you?
 hello, how are you?
20
19
20

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数据类型的偏移量。

print left('hello,how are you', 5)
print right('hello,how are you', 12)
print substring('hello,how are you', 7, 11)
输出:
hello
,how are you
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)

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


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

输出效果:

hello!hello!hello!
 
!olleh
WhiteGood Online
hstufffffo

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

declare @count int
set @count=1
while @count<=5
 begin
    print space(5-@count) + replicate('*', @count*2 -1)
    set @count = @count + 1
 end

set @count = 5
while @count >= 0
  begin
     print space(5-@count) + replicate('*', @count*2 -1)
     set @count = @count -1
  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的差距值,其结果值是一个带有正负号的整数值


显示当前日期

 

print getdate()
Mar 22 2012  4:33PM


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

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


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

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


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



显示星期

   

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


显示本周第一天的日期

 

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


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

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


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




13:自定义函数

    格式

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

    returns 变量类型

    as 

    begin 

        命令行或者程序块

    end 


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

     

create function db_fun1(@x int)
returns int
as
begin
  declare @y int
  set @y = @x * @x
  return @y
end

print dbo.db_fun1(7)

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



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



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

    

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



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

  

create function dbo.db_fun3(@t varchar(50))
returns table
as
return (select * from 职工 where 仓库号= @t)
select * from dbo.db_fun3('wh1')  --显示wh1仓库中的职工信息



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

   

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



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


查看函数的属性信息:

   

execute sp_help db_fun1



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

execute sp_depends db_fun2



查看函数的功能代码:

 

execute sp_helptext db_fun1



修改函数:

  alter function .....


重命名函数:

   sp_rename 原函数名 新函数名


删除函数:

  drop function 函数名[, ...]




    





    













   


相关文章推荐

SQL语句:基础语句和高级函数学习总结

基本查询语句:(基础、重要)          1、distinct  select distinct 列名 from 表名   去重,查询不同的数据时。          2、where   s...

SQL函数知识学习

  • 2012年11月27日 16:48
  • 441KB
  • 下载

sql server函数学习总结

  • 2010年01月21日 13:07
  • 2KB
  • 下载

PL/SQL学习(函数)

函数 一.函数 --命名的PL/SQL块 --能够返回值 --存储在数据库中 --能够作为表达式的一部分被调用 删除函数: drop function functio...

sql plsql 函数学习 Oracle 快速入门

  • 2012年12月25日 13:00
  • 51KB
  • 下载

Oracle学习笔记之二SQL更新数据和一些常用函数

数据库中的数据变更,主要有以下三种:插入数据(insert)、修改数据(update)、删除数据(delete)。这几种操作都是开发人员常用的操作。本章将针对这几种操作进行简要讲解。本章的主要内容包括...

黑马程序员-- .Net学习日记——SQL函数

---------------------- Windows Phone 7手机开发、Net培训、期待与您交流! ---------------------- 一、字符转换函数 1、ASCI...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL学习之函数
举报原因:
原因补充:

(最多只允许输入30个字)