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

《《数据库管理与编程技术》第7章函数.ppt》由会员分享，可在线阅读，更多相关《《数据库管理与编程技术》第7章函数.ppt（74页珍藏版）》请在三一办公上搜索。

1、数据库管理与编程技术,第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：返

2、回表达式的最大值。5.MIN：返回表达式的最小值。,聚合函数（续）,6.SUM：返回表达式中所有值的和。SUM 只能用于数字列。空值将被忽略。7.STDEV：返回给定表达式中所有值的统计标准偏差。8.VAR：返回给定表达式中所有值的统计方差。,示例,例1.返回 titles 表中所有版税（royalty）费用的标准偏差。USE pubsSELECT STDEV(royalty)FROM titles,示例,例2.返回 titles 表中所有版税（royalty）值的方差。USE pubsSELECT VAR(royalty)FROM titles,7.1.2 日期和时间函数,这些函数对日期和时

3、间输入值执行操作，并返回一个字符串、数字值或日期和时间值。,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

4、 mi,n dayofyear dy,y second ss,s Day dd,d millisecond ms,示例,查询 pubs 数据库中当前发布日期加上21天后的日期。SELECT DATEADD(day,21,pubdate)AS timeframeFROM titles,3DATEDIFF,返回跨两个指定日期的日期和时间边界数。语法：DATEDIFF(datepart,startdate,enddate)返回类型：integer 注释：结果是 enddate 减去startdate。如果 startdate 比 enddate 晚，返回负值。,示例,确定在 pubs 数据库中图书发

5、布日期和当前日期间的天数。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 及 DA

6、TEPART 的输出。SELECT DATEPART(year,GETDATE()AS Current year,例2也可以用DATEPART函数返回的结果作为分组依据条件。例：对Northwind数据库中的Employees表统计每年招聘的雇员总数。,USE NorthwindSELECT DATEPART(yy,HireDate)AS Year,COUNT(*)AS NumberOfHires FROM Employees GROUP BY DATEPART(yy,HireDate),6DAY,返回代表指定日期的天的日期部分的整数语法：DAY(date)返回类型：int此函数等价于 DAT

7、EPART(dd,date)。示例：返回从日期 03/12/1998 中的日。SELECT DAY(03/12/1998)AS Day Number,7MONTH,返回代表指定日期月份的整数。语法：MONTH(date)返回类型：intMONTH 等价于 DATEPART(mm,date)。示例：从日期 03/12/1998 中返回月份数。SELECT Month Number=MONTH(03/12/1998),8YEAR,返回表示指定日期中的年份的整数。语法：YEAR(date)返回类型：int此函数等价于 DATEPART(yy,date)。示例：从日期 03/12/1998 中返回年份

8、数。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

9、示例：返回半径为 1 英寸、高为 5 英寸的圆柱容积。DECLARE h float,r floatSET h=5SET r=1SELECT PI()*SQUARE(r)*h AS Cyl Vol,数学函数（续）,5SQRT返回给定表达式的平方根。语法：SQRT(float_expression)返回类型：float,示例,返回 1.00 到 10.00 之间的数字的平方根。DECLARE myvalue floatSET myvalue=1.00WHILE myvalue 10.00 BEGIN SELECT SQRT(myvalue)SELECT myvalue=myvalue+1 END

10、,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,返回字符串中指定表达式的起始

11、位置。语法：CHARINDEX(expression1,expression2,start_location)expression1：一个表达式，其中包含要寻找的字符的次序。expression2：一个表达式，通常是一个用于搜索指定序列的列。start_location：在 expression2 中搜索 expression1 时的起始字符位置。如果没有给定 start_location，或者是一个负数或零，则将从 expression2 的起始位置开始搜索。返回类型：int，如果在 expression2 内没有找到 expression1，则 CHARINDEX 返回 0。,示例,SEL

12、ECT CHARINDEX(wul,rrertwulrt)SELECT CHARINDEX(wul,rrertwulrt,8),示例,例3：显示了当 expression2 内找不到 expression1 时的结果集。SELECT CHARINDEX(wondrous,notes)FROM titlesWHERE title_id=TC3218,4LEFT,返回从字符串左边开始指定个数的字符。语法：LEFT(character_expression,integer_expression)返回类型：varchar,示例,A.返回每个书名最左边的 5 个字符。SELECT LEFT(title,

13、5)FROM titlesORDER 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,示

14、例,Select len(4235565632)10Select len(潍坊学院)4Select len(潍坊1234学院)8,7SUBSTRING,返回字符或表达式的一部分。语法：SUBSTRING(expression,start,length)返回类型：字符数据,示例,该查询在一列中返回 authors 表中的姓氏，在另一列中返回 authors 表中的名字首字母。SELECT au_lname,SUBSTRING(au_fname,1,1)FROM authorsORDER BY au_lname,8LOWER,将大写字符数据转换为小写字符数据后返回字符表达式。语法：LOWER(ch

15、aracter_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_

16、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 aft

17、er the period in this sentence.SELECT Here is the string without the leading spaces:+CHAR(13)+RTRIM(string_to_trim),示例,例2使用 UPPER 函数和 RTRIM 函数来返回整理过的大写的作者姓名。USE pubsSELECT UPPER(RTRIM(au_lname)+,+au_fname AS NameFROM authorsORDER BY au_lname,7.1.5 类型转换函数,CAST 和 CONVERT将某种数据类型的表达式显式转换为另一种数据类型。CAST 和

18、CONVERT 提供相似的功能。语法CAST(expression AS data_type)CONVERT(data_type(length),expression),示例,A.检索书名（这些图书的销售额的第一位数字为 3），并将这些图书的 ytd_sales 转换为 char(6)。-Use CASTSELECT SUBSTRING(title,1,30)AS Title,ytd_salesFROM titlesWHERE CAST(ytd_sales AS char(6)LIKE 3%-Use CONVERTSELECT SUBSTRING(title,1,30)AS Title,ytd

19、_salesFROM titlesWHERE CONVERT(char(6),ytd_sales)LIKE 3%,示例,使用 CAST 数据类型转换函数来连接非字符、非二进制表达式。SELECT The price is+CAST(price AS varchar(12)FROM titlesWHERE price 10.00,例3对pubs数据库的titles表，计算每类（type）图书的平均价格（price），将平均价格转换为小数点前保留3位，小数点后保留2位的定点小数。,USE pubs SELECT type,CAST(AVG(price)AS numeric(5,2)as avg_p

20、rice 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 拥

21、有者名.函数名(参数名 AS 标量数据类型=default,.n)RETURNS 返回值类型 AS BEGIN 函数体 RETURN 标量表达式END,例1创建计算立方体的体积的标量函数，此函数有三个输入参数，分别为立方体的长、宽和高，类型均为整型，函数的返回值的类型也为整型。,CREATE FUNCTION dbo.CubicVolume(CubeLength int,CubeWidth int,CubeHeight int)RETURNS int ASBEGIN RETURN(CubeLength*CubeWidth*CubeHeight)END,例2对pubs数据库的authors表，创

22、建统计指定州（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 intAS BEGIN DECLARE x int select x=cou

23、nt(*)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 authorsWHERE state=CA,调用例3定义的函数，查询“

24、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表，创建查询指定州的作者的名字和地址的内嵌表值函数。,CREAT

25、E 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 TABLEAS RETURN(select au_fname+au_lname as 作者姓名,title as 书名,price as 价格 from authors a join title

26、author 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 TABLEAS RETURN(select fname,lname,job_lvl,hire_date from employee where job_lvl between min_level and m

27、ax_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 创建和调用多语句表值函数,多语句表值函数的功能是视图和存储过程的组合可以利用多语句表值函数返回一个

28、表，表中的内容可由复杂的逻辑和多条SQL语句构建（类似于存储过程）。可以在SELECT语句的FROM子句中使用多语句表值函数（同视图）。,创建多语句表值函数,定义多语句表值函数的语法：CREATE FUNCTION 拥有者名.函数名(参数名 AS 标量数据类型=default,.n)RETURNS 返回变量 TABLE AS BEGIN 函数体 RETURNEND：=(列定义|表约束,.n),例7对pubs数据库，定义查询指定类型的图书的书名、出版日期和新旧类型的多语句表值函数，其中新旧类型的值为：如果此书的出版月数超过100个月，则为旧书；如果出版月数小于等于100个月，则为新书。,CREA

29、TE 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 t

30、ype=type RETURN END,例8对pubs数据库的titles表，创建查询指定类型的图书的书名、出版日期、类型和图书数量的函数。,CREATE FUNCTION f_Details(type varchar(20)returns f_Details table(title varchar(30),pubdate smalldatetime,type varchar(20),shl int)AS BEGIN insert into f_Details select left(title,30),pubdate,type,dbo.f_CountType(type)from titles

31、 where type=type RETURN END,调用多语句表值函数,对多语句表值函数的使用是放在SELECT语句的FROM子句部分。例：调用例7定义的函数，查询“business”类型的图书的信息。select*from dbo.f_Type(business)利用例8定义的函数，查询“business”类型的图书的书名、出版日期、类型和出版数量。select*from dbo.f_Details(business),7.2.5 更改和删除函数,1更改用户自定义函数更改函数的定义使用ALTER FUNCTION语句来实现，语法格式为：ALTER FUNCTION 函数名,例9修改例4定

32、义的函数为查询指定州的作者的名字、电话和地址的内嵌表值函数。,ALTER FUNCTION f_State(state char(20)RETURNS tableAS RETURN(select au_fname,phone,address from authors where state=state),删除用户自定义函数,删除函数使用DROP FUNCTION语句实现。语法为：DROP FUNCTION 拥有者名.函数名,.n 例10删除例1定义的函数。DROP FUNCTION dbo.CubicVolume,7.3 小结,函数分为两类，一类是系统提供的函数，称为内置系统函数；另一类是用户自己定义的函数。提供系统函数的目的是为方便用户进行数据库数据的操作。用户自定义函数是一个可共享的代码段，它支持输入参数，并能返回执行的结果。SQL Server 2000支持三种类型的用户自定义函数。标量函数类似于普通编程语言中的函数，它返回的是单个的数据值；内嵌表值函数的使用与视图类似，其功能如同带参数的视图；多语句表值函数的函数体类似于存储过程，其使用方法类似于视图，它可以返回根据用户输入参数不同而内容不同的表。,