第13章函数和游标

7．MONTH
• • • • • 作用：返回代表指定日期月份的整数。 语法：MONTH ( date ) 返回类型：int MONTH 等价于 DATEPART(mm, date)。 例13.3.查询2008年3月份出版的全部图书， 列出书名和出版日期。 SELECT 书名, 出版日期 FROM 图书表 WHERE DATEPART(year, 出版日期) = 2008 AND MONTH(出版日期) = 3
示例：作为列的默认值
• 例2. 创建employees表，并用从GETDATE()函 数得到的系统当前时间作为职工受雇日期的 默认值。
CREATE TABLE employees( eid char(11) NOT NULL PRIMARY KEY, ename varchar(20) NOT NULL, hire_date smalldatetime DEFAULT GETDATE() )
示例
• 例17．统计每年出版的图书的平均单价。
SELECT YEAR(出版日期) AS 出版年份, AVG(单价) AS 平均单价 FROM 图书表 GROUP BY YEAR(出版日期)
• 如果希望平均单价保留到小数点后2位：
SELECT YEAR(出版日期)AS 出版年份, CAST(AVG(单价) AS numeric(4,2)) AS 平均单价 FROM 图书表 GROUP BY YEAR(出版日期)
6．DAY
• 作用：返回代表指定日期的天的日期部分 的整数 • 语法：DAY ( date ) • 返回类型：int • 此函数等价于 DATEPART(dd, date)。
示例
• 例8．查询2008年每个月10日之前（包括 10日）出版的图书，列出书名和出版日 期。
SELECT 书名, 出版日期 FROM 图书表 WHERE DATEPART(year, 出版日期) = 2008 AND DAY(出版日期) <= 10
5．LTRIM
• 作用：返回删除了字符串左边的起始 空格后的字符串。 • 语法：LTRIM ( 字符串 ) • 返回类型：varchar • 例16．去掉学生姓名前的起始空格。
UPDATE Student SET Sname = LTRIM(Sname)
6．RTRIM
• 作用：返回截断字符串后边的所有尾随空格 后的字符串。 • 语法：RTRIM ( 字符串 ) • 返回类型：varchar • 例16．查询名字的最后一个字是“海”或者 是“平”的学生姓名、性别和所在系。
高等院校计算机教材系列
数据库原理与应用（第2版）
第13章 函数和游标
• 13.1 系统提供的内置函数 • 13.2 用户自定义函数（UDF） • 13.3 游标
13.1 系统提供的内置函数
• 13.1.1 日期时间函数 • 13.1.2 字符串函数 • 13.1.3 类型转换函数
13.1.1 日期和时间函数
示例
列1.查询今天加上100天后的新的日期。 SELECT DATEADD(day,100,getdate())
列2.查询每本书的书名、出版日期以及出版日 期加上21天后的新日期。 SELECT 书名, 出版日期, DATEADD(day, 21, 出版日期) AS 新日期 FROM 图书表
3．DATEDIFF
• 对日期和时间输入值执行操作，并返回 一个字符串、数字值或日期和时间值。
1. GETDATE()
• 作用：按 datetime 值的SQL Server标准 内部格式返回当前的系统日期和时间。 • 返回类型：datetime。 • 说明：该函数可用在SELECT语句的选择列 表或WHERE子句中。 • 例1. 查询系统当前的日期和时间。 SELECT GETDATE()
示例
• 例1．创建统计指定学生（学号）的选课门数的标量 函数。 CREATE FUNCTION dbo.f_Count(@sno char(7)) RETURNS int AS BEGIN DECLARE @x int SELECT @x = count(*) FROM SC WHERE SNO = @sno RETURN @x END
• expression：可以是字符串常量，也可以是列名 • start：整数，指定字符串的开始位置。 • length：正整数，指定要返回的expression的字 符数或字节数。
• 返回类型：字符数据
示例
• 例15．查询名字的第二个字是“小”或 者是“大”的学生姓名、性别和所在系
SELECT Sname,Ssex,Sdept FROM Student WHERE SUBSTRING(Sname,2,1) IN ('小','大')
3.1.2 字符串函数——1．LEFT
• 作用：返回从字符串左边开始指定个数的字 符。 • 语法：LEFT(char_exp, int_exp) • 说明： • char_exp：可以是常量、变量或列名。 • int_exp：正整数值，若此值为负数，则返回 一个错误。 • 返回类型：varchar 或 nvarchar
示例
• 例14．查询姓王且名字是三个字的学生 的详细信息。
SELECT * FROM Student WHERE LEFT(Sname,1) = '王' AND LEN(Sname) = 3
4．SUBSTRING
• 作用：返回字符或表达式的一部分。 • 语法： SUBSTRING (expression, start, length)
13.2.2 标量函数
• 标量用户自定义函数接受0个或多个参数并返回一个值。 CREATE FUNCTION [ 架构名.] 函数名 ( [ { @参数名 [ AS ] 参数数据类型 [ = 默认值 ] } [ ,...n ] ] ) RETURNS 返回值数据类型 [ AS ] BEGIN 函数体 RETURN 标量表达式 END
13.2 用户自定义函数（UDF）
• • • • 13.2.1 13.2.2 13.2.3 13.2.4 基本概念 标量函数 内联表值函数 多语句表值函数
• 用户自定义函数可以扩展数据操作的功能， 它在概念上类似于一般的程序设计语言中定 义的函数。 • 用户自定义函数具有如下优点：
• 模块化程序设计 • 执行速度更快 • 减少网络流量
• 作用：返回跨两个指定日期的日期和时间边 界数。 • 语法：
DATEDIFF (datepart, startdate, enddate )
• 返回类型：integer • 说明：startdate 是从 enddate 减去。如果 startdate比enddate晚，则返回负值。
示例
例3．计算2010年1月1日到2010年6月15日之间有 多少天。 SELECT DATEDIFF(DAY,'2010/1/1', '2010/6/15') 例4．查询每本图书从出版日期到当前日期共有多 少个月，列出书名、出版日期和出版的月数，只 列出出版月数超过12个月的结果。
示例
• 例11．查询全体学生的不同姓氏。 SELECT DISTINCT LEFT(Sname,1) FROM Student
2．RIGHT
• 作用：返回字符串中从右边开始指定个数的 字符。 • 语法：RIGHT (char_exp, int_exp) • 说明：其中参数含义同LEFT函数。 • 返回类型：varchar 或 nvarchar • 例12．查询学生姓名、学号的后5位和所在系 SELECT Sname,RIGHT(Sno,5),Sdept FROM Student
5．DATEPART
• 作用：返回代表指定日期的指定日期部分的 整数。 • 语法：DATEPART ( datepart , date ) • 返回类型：int • 例7．查询2008年出版的全部图书，列出书 名和出版日期。 SELECT 书名, 出版日期 FROM 图书表 WHERE DATEPART(year, 出版日期) = 2008
SELECT 书名,出版日期, DATEDIFF(month, 出版日期, getdate()) AS 出版月数 FROM 图书表 WHERE DATEDIFF(month, 出版日期, getdate()) > 12
示例
• 例5．查询截止到当前日期出版年数超过 1年的图书的详细信息。
SELECT * FROM 图书表 WHERE DATEDIFF(year, 出版日期, getdate()) > 1
CAST(expression AS AS data_type [(length)]) CONVERT(data_type [ (length ) ], expression [, style ])
类型转换函数格式说明
• length：字符数据类型的可选参数。对于CON VERT函数，如果未指定length，则默认为30 个字符。 • style：用于将datetime或smalldatetime数 据转换为字符数据的日期格式的样式；或用 于将float、real数据转换为字符数据的字符 串格式的样式。如果style为NULL，则返回的 结果也为NULL
3．LEN
• 作用：返回给定字符串表达式的字符个数， 不包含尾随空格。 • 语法：LEN (char_exp) • 说明： char_exp是要计算的字符串常量， 也可以是字符类型的变量或列名。 • 返回类型：int • 例13．查询学生姓名和姓名的汉字个数。 SELECT Sname,LEN(Sname) AS 汉字数 FROM Student
SELECT Sname,Ssex,Sdept FROM Student WHERE RIGHT(RTRIM(Sname),1) IN ('海','平')