《数据库管理与编程技术》第7章函数.ppt

数据库管理与编程技术,第7章 函数,第7章 函数,7.1 系统提供的内置函数 7.2 用户自定义函数,7.1 系统提供的内置函数,7.1.1 聚合函数 7.1.2 日期和时间函数 7.1.3 数学函数 7.1.4 字符串函数 7.1.5 类型转换函数,7.1.1 聚合函数,1. avg：返回组中值的平均值。空值将被忽略。 2. count：返回组中项目的数量 3. count_big：返回组中项目的数量。count_big 的使用与 count 函数相似。它们之间的唯一差别是它们的返回值：count_big 总是返回 bigint 数据类型值，而 count 则总是返回 int 数据类型值。 4. max：返回表达式的最大值。 5. min：返回表达式的最小值。,聚合函数（续）,6. sum：返回表达式中所有值的和。sum 只能用于数字列。空值将被忽略。 7. stdev：返回给定表达式中所有值的统计标准偏差。 8. var：返回给定表达式中所有值的统计方差。,示例,例1.返回 titles 表中所有版税（ royalty）费用的标准偏差。 use pubs select stdev(royalty) from titles,示例,例2. 返回 titles 表中所有版税（royalty） 值的方差。 use pubs select var(royalty) from titles,7.1.2 日期和时间函数,这些函数对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。,1. getdate ( ),返回类型：datetime 示例 a. 用 get date 返回当前日期和时间 select getdate() b. 在 create table 语句中使用 getdate ( )作为列的默认值,2dateadd,在向指定日期加上一段时间的基础上，返回新的 datetime 值。 语法：dateadd ( datepart , number, date ) datepart的形式 日期部分 缩写 日期部分 缩写 year yy, yyyy week wk, ww quarter qq, q hour hh month mm, m minute mi, n dayofyear dy, y second ss, s day dd, d millisecond ms,示例,查询 pubs 数据库中当前发布日期加上21天后的日期。 select dateadd(day, 21, pubdate) as timeframe from titles,3datediff,返回跨两个指定日期的日期和时间边界数。 语法：datediff ( datepart , startdate , enddate ) 返回类型：integer 注释：结果是 enddate 减去startdate 。如果 startdate 比 enddate 晚，返回负值。,示例,确定在 pubs 数据库中图书发布日期和当前日期间的天数。 select datediff(day, pubdate, getdate() as no_of_days from titles,4datename,返回代表指定日期的指定日期部分的字符串描述。 语法：datename ( datepart , date ) 返回类型：nvarchar,示例,从 getdate 返回的日期中提取月份名。 select datename(month, getdate() as month name,5datepart,返回代表指定日期的指定日期部分的整数。 语法：datepart ( datepart , date ) 返回类型：int,示例,显示 getdate 及 datepart 的输出。 select datepart (year, getdate() as current year,例2也可以用datepart函数返回的结果作为分组依据条件。例：对northwind数据库中的employees表统计每年招聘的雇员总数。,use northwind select datepart(yy, hiredate) as year, count(*) as numberofhires from employees group by datepart(yy, hiredate),6day,返回代表指定日期的天的日期部分的整数 语法：day ( date ) 返回类型：int 此函数等价于 datepart(dd, date)。 示例：返回从日期 03/12/1998 中的日。 select day(03/12/1998) as day number,7month,返回代表指定日期月份的整数。 语法：month ( date ) 返回类型：int month 等价于 datepart(mm, date)。 示例：从日期 03/12/1998 中返回月份数。 select “month number“ = month(03/12/1998),8year,返回表示指定日期中的年份的整数。 语法：year ( date ) 返回类型：int 此函数等价于 datepart(yy, date)。 示例：从日期 03/12/1998 中返回年份数。 select “year number“ = year(03/12/1998),7.1.3 数学函数,1abs 返回给定数字表达式的绝对值。 语法：abs ( numeric_expression ) 2pi 返回 pi 的常量值。 语法：pi ( ) 返回类型：float 示例：返回 pi 值。 select pi(),数学函数（续）,3power 返回给定表达式乘指定次方的值。 语法：power ( numeric_expression , y ) 返回类型：与 numeric_expression 相同。,数学函数（续）,4square 返回给定表达式的平方。 语法：square ( float_expression ) 返回类型：float 示例：返回半径为 1 英寸、高为 5 英寸的圆柱容积。 declare h float, r float set h = 5 set r = 1 select pi()* square(r)* h as cyl vol,数学函数（续）,5sqrt 返回给定表达式的平方根。 语法：sqrt ( float_expression ) 返回类型：float,示例,返回 1.00 到 10.00 之间的数字的平方根。 declare myvalue float set myvalue = 1.00 while myvalue 10.00 begin select sqrt(myvalue) select myvalue = myvalue + 1 end,7.1.4 字符串函数,1ascii 返回字符表达式最左端字符的 ascii 代码值。 语法：ascii（字符串表达式） 返回类型：int 例：返回字符串ascii中第一个字母的ascii编码值。 select ascii (ascii) 结果为：65,7.1.4 字符串函数,2char 将 int ascii 代码转换为字符的字符串函数。 语法：char ( integer_expression ) 参数：integer_expression ：介于 0 和 255 之间的整数。如果整数表达式不在此范围内，将返回 null 值。 返回类型：char(1) 长度为1的字符,3charindex,返回字符串中指定表达式的起始位置。 语法：charindex ( expression1 , expression2 , start_location ) expression1：一个表达式，其中包含要寻找的字符的次序。 expression2：一个表达式，通常是一个用于搜索指定序列的列。 start_location：在 expression2 中搜索 expression1 时的起始字符位置。如果没有给定 start_location，或者是一个负数或零，则将从 expression2 的起始位置开始搜索。 返回类型：int，如果在 expression2 内没有找到 expression1，则 charindex 返回 0。,示例,select charindex(wul, rrertwulrt ) select charindex(wul, rrertwulrt ,8),示例,例3：显示了当 expression2 内找不到 expression1 时的结果集。 select charindex(wondrous, notes) from titles where title_id=tc3218,4left,返回从字符串左边开始指定个数的字符。 语法：left ( character_expression , integer_expression ) 返回类型：varchar,示例,a. 返回每个书名最左边的 5 个字符。 select left(title, 5) from titles order by title_id b. 使用 left 函数返回字符串 abcdefg 最左边的 2 个字符。 select left(abcdefg,2),5right,返回字符串中从右边开始指定个数的字符。 语法：right ( character_expression , integer_expression ) 返回类型：varchar,示例,返回每个作者名字中最右边的五个字符。 select right(4235565632,5),6len,返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格。 语法：len ( string_expression ) 返回类型：int,示例,select len(4235565632) 10 select len(潍坊学院) 4 select len(潍坊1234学院) 8,7substring,返回字符或表达式的一部分。 语法：substring ( expression , start , length ) 返回类型：字符数据,示例,该查询在一列中返回 authors 表中的姓氏，在另一列中返回 authors 表中的名字首字母。 select au_lname, substring(au_fname, 1, 1) from authors order by au_lname,8lower,将大写字符数据转换为小写字符数据后返回字符表达式。 语法：lower ( character_expression ) 返回类型：varchar 例：select lower(abcdefg) 结果为：abcdefg,9upper,返回将小写字符数据转换为大写的字符表达式。 语法：upper ( character_expression ) 返回类型：varchar 例：select lower(abcdefg) 结果为：abcdefg,10ltrim,删除起始空格后返回字符表达式。 语法：ltrim ( character_expression ) 返回类型：varchar,示例,删除字符变量中的起始空格。 declare string_to_trim varchar(60) set string_to_trim = five spaces are at the beginning of this string. select here is the string without the leading spaces: + ltrim(string_to_trim),11rtrim,截断所有尾随空格后返回一个字符串。 语法：rtrim ( character_expression ) 返回类型：varchar,示例,例1：使用 rtrim 删除字符变量中的尾随空格。 declare string_to_trim varchar(60) set string_to_trim = four spaces are after the period in this sentence. select here is the string without the leading spaces: + char(13) + rtrim(string_to_trim),示例,例2使用 upper 函数和 rtrim 函数来返回整理过的大写的作者姓名。 use pubs select upper(rtrim(au_lname) + , + au_fname as name from authors order by au_lname,7.1.5 类型转换函数,cast 和 convert 将某种数据类型的表达式显式转换为另一种数据类型。cast 和 convert 提供相似的功能。 语法 cast ( expression as data_type ) convert (data_type(length), expression),示例,a. 检索书名（这些图书的销售额的第一位数字为 3），并将这些图书的 ytd_sales 转换为 char(6)。 - use cast select substring(title, 1, 30) as title, ytd_sales from titles where cast(ytd_sales as char(6) like 3% - use convert select substring(title, 1, 30) as title, ytd_sales from titles where convert(char(6), ytd_sales) like 3%,示例,使用 cast 数据类型转换函数来连接非字符、非二进制表达式。 select the price is + cast(price as varchar(12) from titles where price 10.00,例3对pubs数据库的titles表，计算每类（type）图书的平均价格（price），将平均价格转换为小数点前保留3位，小数点后保留2位的定点小数。,use pubs select type, cast(avg(price) as numeric(5,2) as avg_price from titles group by type,7.2 用户自定义函数,7.2.1 基本概念 7.2.2 创建和调用标量函数 7.2.3 创建和调用内嵌表值函数 7.2.4 创建和调用多语句表值函数 7.2.5 更改和删除函数,7.2.1 基本概念,可以扩展数据操作的功能， 在概念上类似于一般的程序设计语言中定义的函数。 微软从sql server 2000版本开始支持用户自定义函数 sql server 2000支持三种用户自定义函数： 标量函数 内嵌表值函数 多语句表值函数,7.2.2 创建和调用标量函数,标量函数是返回单个数据值的函数。 定义标量函数的语法： create function 拥有者名. 函数名 ( 参数名 as 标量数据类型 = default ,.n ) returns 返回值类型 as begin 函数体 return 标量表达式 end,例1创建计算立方体的体积的标量函数，此函数有三个输入参数，分别为立方体的长、宽和高，类型均为整型，函数的返回值的类型也为整型。,create function dbo.cubicvolume (cubelength int, cubewidth int, cubeheight int) returns int as begin return ( cubelength * cubewidth * cubeheight ) end,例2对pubs数据库的authors表，创建统计指定州（state）的作者人数的标量函数。,create function dbo.f_count( state varchar(20) ) returns int as begin declare x int select x=count(*) from authors where state = state return x end,例3对pubs数据库的titles表，创建查询指定类型的图书的数量的标量函数。,create function dbo.f_counttype( type varchar(20) ) returns int as begin declare x int select x=count(*) from titles where type = type return x end,调用标量函数,当调用标量函数时，必须提供至少由两部分组成的名称：函数拥有者名和函数名。 可在任何允许出现表达式的sql语句中调用标量函数，只要类型一致。 调用例1定义的函数示例： select dbo.cubicvolume(4,6,8),调用例2定义的函数，查询“ca”州的作者的姓名和作者人数。,select state as 州, au_fname + + au_lname as 作者姓名, dbo.f_count(ca) as 作者人数 from authors where state = ca,调用例3定义的函数，查询“business”类型的图书的书名和数量。,select title as 书名, dbo.f_counttype (business) as 数量 from titles where type = business,7.2.3 创建和调用内嵌表值函数,内嵌表值函数的返回值是一个表，该表的内容是一个查询语句的结果。 定义内嵌表值函数的语法为： create function 拥有者名. 函数名 ( 参数名 as 标量数据类型 = default ,.n ) returns table as return ( select语句 ) ,例4对pubs数据库的authors表，创建查询指定州的作者的名字和地址的内嵌表值函数。,create function f_state(state char(20) returns table as return ( select au_fname, address from authors where state = state),例5对pubs数据库，创建查询编写价格高于指定价格的图书的作者的姓名，书名和图书价格的内嵌表值函数。,create function f_price(jg int) returns table as return(select au_fname + + au_lname as 作者姓名,title as 书名, price as 价格 from authors a join titleauthor b on a.au_id = b.au_id join titles c on b.title_id = c.title_id where price jg),例6对pubs数据库的jobs表，创建查询从事指定级别范围的工作的雇员的姓名、工作级别和受雇日期的内嵌表值函数,create function f_job(min_level int, max_level int) returns table as return (select fname,lname,job_lvl,hire_date from employee where job_lvl between min_level and max_level),调用内嵌表值函数,对内嵌表值函数的使用与视图非常类似，需要放置在查询语句的from子句部分，它的作用很象是带参数的视图。 调用例4定义的内嵌表值函数，查询“ut”州的作者信息。 select * from dbo.f_state (ut),调用示例,利用例5定义的内嵌表值函数，查询价格高于20的图书的信息。 select * from dbo.f_price (20) 利于例6定义的函数，查询从事工作级别在30到50范围内的雇员的信息。 select * from f_job(30, 50),7.2.4 创建和调用多语句表值函数,多语句表值函数的功能是视图和存储过程的组合 可以利用多语句表值函数返回一个表，表中的内容可由复杂的逻辑和多条sql语句构建（类似于存储过程）。 可以在select语句的from子句中使用多语句表值函数（同视图）。,创建多语句表值函数,定义多语句表值函数的语法： create function 拥有者名. 函数名 ( 参数名 as 标量数据类型 = default ,.n ) returns 返回变量 table as begin 函数体 return end ：= ( 列定义 | 表约束 ,.n ),例7对pubs数据库，定义查询指定类型的图书的书名、出版日期和新旧类型的多语句表值函数，其中新旧类型的值为：如果此书的出版月数超过100个月，则为旧书；如果出版月数小于等于100个月，则为新书。,create function f_type(type varchar(20) returns f_type table( title varchar(30), pubdate smalldatetime, new_old varchar(10) as begin insert into f_type select left(title,30),pubdate, case when datediff(month,pubdate,2006/6/1) 100 then 旧书 when datediff(month,pubdate,2006/6/1) = 100 then 新书 end from titles where type = type return end,例8对pubs数据库的titles表，创建查询指定类型的图书的书名、出版日期、类型和图书数量的函数。,create function f_details (type varchar(20) r