数据库原理及应用 第8章 函数 8.1 系统内置函数 8.2 自定义函数.

Presentation on theme: "数据库原理及应用 第8章 函数 8.1 系统内置函数 8.2 自定义函数."— Presentation transcript:

1 数据库原理及应用 第8章 函数 8.1 系统内置函数 8.2 自定义函数

2 第8章 函数 8.1 系统内置函数 为了让用户更方便地对数据库进行操作，SQL Server 在T-SQL 中提供了许多内置函数。函数其实就是一段程序代码，用户可以通过调用内置函数并为其提供所需的参数来执行一些特殊的运算或完成复杂的操作。T-SQL 提供的函数有系统函数、字符串函数、日期和时间函数、数学函数、转换函数等。 8.1.1 系统函数 系统函数用于获取有关计算机系统、用户、数据库和数据库对象的信息。可以使用户在不直接访问系统表的情况下，获取SQL Server 系统表中的信息。 系统函数可以让用户在得到信息后使用条件语句，根据返回的信息进行不同的操作。与其他函数一样，可以在SELECT 语句的SELECT 和WHERE 子句以及表达式中使用系统函数。

3 第8章 函数 【例 8.1】 返回学生信息表中姓名字段的长度和数据长度。
第8章 函数 【例 8.1】 返回学生信息表中姓名字段的长度和数据长度。 SELECT COL_LENGTH('StudInfo','StudName') AS Name_Col_Length, DATALENGTH(StudName) AS Name_Data_Length FROM StudInfo

4 8.1.2 字符串函数 第8章 函数 1. 字符转换函数 （1）ASCII
第8章 函数 8.1.2 字符串函数 字符串函数对二进制数据、字符串和表达式执行不同的运算。此类函数作用于CHAR、VARCHAR、BINARY 和VARBINARY 数据类型以及可以隐式转换为CHAR 或VARCHAR的数据类型。可以在SELECT 语句的SELECT 和WHERE 子句以及表达式中使用字符串函数。 1. 字符转换函数 （1）ASCII 功能：返回字符表达式最左端字符的ASCII 码值。 语法：ASCII <character_expression> 返回类型：INT 示例：SELECT ascii(123),ascii('A') 在ASCII 函数中，纯数字的字符串可不用括起来，但含其他字符的字符串必须用括起来使用，否则会出错。

5 第8章 函数 （2）CHAR （3）LOWER 功能：用于将 ASCII 码转换为字符。
第8章 函数 （2）CHAR 功能：用于将 ASCII 码转换为字符。 语法：CHAR <integer_expression> 返回类型：CHAR 示例：SELECT char(65),Char(123) 如果没有输入0 ～ 255 之间的ASCII 码值CHAR()函数会返回一个NULL 值 （3）LOWER 功能：把字符串全部转换为小写。 语法：LOWER <character _expression> 返回类型：VARCHAR 示例：SELECT lower('Abc'),lower('A 李C')

6 第8章 函数 （4）UPPER （5）STR 功能：把字符串全部转换为大写。
第8章 函数 （4）UPPER 功能：把字符串全部转换为大写。 语法：LOWER <character _expression> 返回类型：VARCHAR 示例：SELECT upper('Abc'),upper('a 李c') （5）STR 功能：把数值型数据转换为字符型数据。 语法：STR <float _expression>[ length[ <decimal>]] 返回类型：CHAR 示例：SELECT str(123.5),str(123456,5),str( ,8,2) 自变量length 和decimal 必须是非负值，length 指定返回的字符串的长度。decimal 指定返回的小数位数，如果没有指定长度缺省的length 值为10，decimal 缺省值为0。小数位数大于decimal 值时STR 函数将其下一位四舍五入。指定长度应大于或等于数字的符号位数+小数点前的位数+小数点位数+小数点后的位数。如果<float_expression>小数点前的位数超过了指定的长度，则返回指定长度的*。

7 第8章 函数 2. 去空格函数 （1）LTRIM （2）RTRIM 【例 8.2】 去除字符串头部和尾部空格。
第8章 函数 2. 去空格函数 （1）LTRIM 功能：把字符串头部的空格去掉。 语法：LTRIM <character _expression> 返回类型：VARCHAR 示例：SELECT Ltrim(' A'),rtrim(' 李 ') （2）RTRIM 功能：把字符串尾部的空格去掉。 语法：RTRIM <character _expression> 示例：SELECT Ltrim('A '),rtrim(' 李 ') 在许多情况下,往往需要得到头部和尾部都没有空格字符的字符串这时可将上两个函数嵌套使用。 【例 8.2】 去除字符串头部和尾部空格。 SELECT Rtrim(ltrim(' 李 '))

8 第8章 函数 3. 取子串函数 （1）LEFT （2）RIGHT （3）SUBSTRING 功能：返回从字符串左边开始指定个数的字符。
第8章 函数 3. 取子串函数 （1）LEFT 功能：返回从字符串左边开始指定个数的字符。 语法：LEFT(character_expression,integer_expression) 返回类型：VARCHAR 示例：SELECT LEFT('ABC',2),LEFT('A 西林C',2) （2）RIGHT 功能：返回从字符串右边开始指定个数的字符。 语法：RIGHT(character_expression,integer_expression) 示例：SELECT RIGHT('ABC',2),RIGHT('A 西林C',2) （3）SUBSTRING 功能：返回字符串、binary、text 表达式的一部分。 语法：SUBSTRING (expression,start,length) 返回类型：VARCHAR，NVARCHAR，VARBINARY 示例：SELECT SubString('ABC',2,1), SubString('ABC',2,4), SubString('A 西F',1,2)

9 第8章 函数 4. 字符串比较函数 （1）CHARINDEX （2）REPLACE 功能：返回字符串中某个指定的子串出现的起始位置。
第8章 函数 4. 字符串比较函数 （1）CHARINDEX 功能：返回字符串中某个指定的子串出现的起始位置。 语法：CHARINDEX(substring_expression,expression[,start_location]) 返回类型：INT 示例：SELECT Charindex('B','ABC'),CharIndex('AD','ABCD'), CharIndex('B','ABCBDE',3) 其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式，start_location 表示要查询的开始位置，省略该参数默认为1。如果没有发现子串则返回0 值。此函数不能用于TEXT 和IMAGE 数据类型。 （2）REPLACE 功能：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。 语法：REPLACE('string_expression1','string_expression2','string_expression3') 返回类型：与表达式类型一致 示例：SELECT Replace('ABC','B','123'),Replace('ABCD','BD','ERR')

10 第8章 函数 8.1.3 日期函数 日期函数用来显示关于日期和时间的信息，日期函数的数据类型为datetime 和smalldatetime 值，可以对这些值执行算术运算，最后将返回一个字符串、数字值或日期和时间值。可以在SELECT 语句的SELECT 和WHERE 子句以及表达式中使用日期函数。 1. DAY 功能：返回date_expression 中的日期值。 语法：DAY <date_expression> 返回类型：INT 示例：SELECT Day(' '),Day('05/12/2008') 2. MONTH 功能：返回date_expression 中的月份值。 语法：MONTH <date_expression> 示例：SELECT Month(' '),Month('05/12/2008')

11 第8章 函数 3. YEAR 4. GETDATE 功能：返回date_expression 中的年份值。
第8章 函数 3. YEAR 功能：返回date_expression 中的年份值。 语法：YEAR <date_expression> 返回类型：INT 示例：SELECT YEAR(' '),YEAR('05/12/2008') 在使用日期函数时，其日期值应在1753 年到9999 年之间，这是 SQL Server 系统所能识别的日期范围，否则会出现错误。 4. GETDATE 功能：按datetime 数据类型格式返回当前系统日期和时间。 语法：GETDATE() 返回类型：datetime 示例：SELECT getdate()

12 第8章 函数 5. DATEPART 功能：返回代表指定日期的指定日期部分的整数。 语法：DATEPART(datepart,date)
第8章 函数 5. DATEPART 功能：返回代表指定日期的指定日期部分的整数。 语法：DATEPART(datepart,date) 返回类型：INT 示例：SELECT Datepart(year,getdate()),Datepart(Month,' ') Datepart 是返回的日期部分可选参数:

13 第8章 函数 6. DATEADD 7. DATEDIFF 功能：在向指定日期加上一段时间的基础上，返回新的datetime 值。
第8章 函数 6. DATEADD 功能：在向指定日期加上一段时间的基础上，返回新的datetime 值。 语法：DATEADD（Datepart，number，date） 返回类型：Datetime 示例：SELECT DATEADD（day，20，' '），DATEADD（Month，10，' '）， 7. DATEDIFF 功能：返回跨两个指定日期的日期和时间边界数。 语法：DATEDIFF(datepart,startdate,enddate） 返回类型：Int 示例：SELECT DATEDIFF(Day,' ',' ')

14 8.1.4 数学函数 第8章 函数 1. POWER 2. ROUND 3. SQRT 功能：返回给定表达式乘指定次方的值。
第8章 函数 8.1.4 数学函数 1. POWER 功能：返回给定表达式乘指定次方的值。 语法：POWER(numeric_expression,y) 返回类型：与numeric_expression 相同 示例：SELECT POWER(2,3),POWER(2,0) 2. ROUND 功能：返回数字表达式并四舍五入为指定的长度或精度。 语法：ROUND(numeric_expression,length[,function]) 返回类型：与numeric_expression 相同。 示例：SELECT ROUND(24.567,2),ROUND(24.25,0) 3. SQRT 功能：返回给定表达式的平方根。 语法：SQRT(float_expression) 返回类型：float 示例：SELECT SQRT(4) ROUND(748.58, -1) ROUND(748.58, -2) ROUND(748.58, -3)

15 8.1.5 CASE 函数 第8章 函数 1. 简单 CASE 函数 【例 8.3】 查询学生信息，将性别以英文显示。
第8章 函数 8.1.5 CASE 函数 1. 简单 CASE 函数 【例 8.3】 查询学生信息，将性别以英文显示。 SELECT StudNo,StudName, 学生性别=Case StudSex When '男' then 'Male‘ When '女' then 'Female' Else '性别不详' end FROM StudInfo

16 第8章 函数 2. CASE 搜索函数 【例 8.4】 统计各学生平均分，并按等级显示。
第8章 函数 2. CASE 搜索函数 【例 8.4】 统计各学生平均分，并按等级显示。 SELECT StudNo,AVG(StudScore) AvgScore, Case When AVG(StudScore)>=90 then '优秀' When AVG(StudScore)>=80 AND AVG(StudScore)<90 then '良好' When AVG(StudScore)>=70 then '中等' When AVG(StudScore)>=60 then '及格' Else '不及格' End AS ScoreLevel FROM StudScoreInfo GROUP BY StudNo

17 第8章 函数 8.1.6 系统内置函数应用 系统内置函数不仅可以在T-SQL 程序设计中使用，也可以在标准SQL 语句使用，下面举例说明系统内置函数在SQL 语句中的应用。 【例 8.5】 查询姓“李”的学生信息。 SELECT * FROM StudInfo WHERE Left(StudName,1)='李' 【例 8.6】 查询姓名中包含“文”的学生信息。 WHERE Charindex('文',StudName)>0 【例 8.7】 查询姓名中第二个字为“文”的学生信息。 WHERE substring(StudName,2,1)='文'

18 第8章 函数 【例 8.8】 将姓名中的“云”字改为“芸”。 【例 8.9】 统计学生信息表中同姓学生人数为3 人以上的信息。
第8章 函数 【例 8.8】 将姓名中的“云”字改为“芸”。 UPDATE StudInfo Set StudName=Replace(StudName,’云’,’芸’) 【例 8.9】 统计学生信息表中同姓学生人数为3 人以上的信息。 SELECT Left(StudName,1),COUNT(*) FROM StudInfo GROUP BY Left(StudName,1) HAVING COUNT(*)>=3 【例 8.11】 使用NEWID()函数实现将学生信息随机排序，每次运行排序结果不同。 SELECT * FROM StudInfo ORDER BY newid()

19 第8章 函数 【例 8.10】 统计学号为20050319001 的各分数段课程门数。
第8章 函数 【例 8.10】 统计学号为 的各分数段课程门数。 SELECT Case When StudScore>=90 then '优秀' When StudScore>=80 then '良好' When StudScore>=70 then '中等' When StudScore>=60 then '及格' Else '不及格' End AS ScoreLevel, COUNT(*) CourseCount FROM StudScoreInfo WHERE StudNo=' ' GROUP BY Case When StudScore>=90 then '优秀' End

20 第8章 函数 8.2 自定义函数 8.2.1 自定义函数简介 除了使用系统提供的函数外，用户还可以根据需要自定义函数。用户自定义函数不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过EXECUTE 命令来执行。用户自定义函数中存储了一个Transact-SQL 例程，可以返回一定的值。 在 SQL Server 中根据函数返回值形式的不同将用户自定义函数分为以下类型： ① 标量函数（Scalar function）：返回单一的数据值。 ② 返回数据集（RowSet）的用户定义函数：返回一个table 类型的数据集，依定义语法不同分为以下两类： 行内数据集函数（或称为“内嵌数据表值函数”） 多语句数据集函数（或称为 “多语句式数据表值函数”）

21 第8章 函数 8.2.2 创建自定义函数 创建标量函数语法如下：
第8章 函数 8.2.2 创建自定义函数 标量函数（Scalar function）：这类函数会返回单一的数据值，而数据值的类型可以是除了text、ntext、image、cursor 及rowversion(timestamp)之外的所有类型。 创建标量函数语法如下： CREATE FUNCTION [ owner_name.] function_name ( [ [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type [ WITH < function_option> [ [,] ...n] ] [ AS ] BEGIN function_body RETURN scalar_expression END <function_option>::={ENCRYPTION | SCHEMABINDING} 用来结束函数的执行，并将scalar-expression 表达式的值返回。在函数中可以出现多个RETURN 语句，但函数的最后一个语句必须是RETURN 语句。 函数的参数，可有0 个或多个（最多可有1024 个参数），而参数的名称前要加上“＠”。参数行必须用小括号括起来，即使没有参数，小括号也不可省略。可以用＝来为参数指定默认值。例如：CREATE FUNCTION int=500)。 指定ENCRYPTION 时表示函数的内容加密，函数建立之后即无法查看其程序内容。若指定SCHEMABINDING（结构绑定）选项，则可限制在函数中所使用到的各数据库对象。 声明返回值的类型 设置函数的选项

22 第8章 函数 【例 8.12】 创建一个计算阶乘的函数。 CREATE FUNCTION Get_JC(@N Int)
第8章 函数 【例 8.12】 创建一个计算阶乘的函数。 CREATE FUNCTION Int) Returns Bigint as Begin Bigint While set set End

23 第8章 函数 8.2.3 调用自定义函数 8.2.4 查看自定义函数 【例 8.13】 调用阶乘函数。
第8章 函数 8.2.3 调用自定义函数 调用自定义函数（用户定义的函数）和调用内置函数方式基本相同。当调用标量值函数时，必须加上“所有者”，通常是dbo（但不是绝对，可以在SQL Server Management Studio中的“可编程性→函数→标量值函数”中查看所有者）。 【例 8.13】 调用阶乘函数。 SELECT dbo.Get_JC(5) 8.2.4 查看自定义函数 在 SQL Server Management Studio 中查看前一节创建的自定义函数，展开数据库“StudScore_DB”，点击“可编程性→函数→标量值函数”，可查看用户自定义的标量值函数。 右键选中需要查看的函数，选择“编写函数脚本为→CREATE 到→新查询编辑窗口”就可以查看该函数的创建脚本。 如果要对函数进行修改，直接右键选择“修改”。修改脚本后需运行，完成对函数修改。

24 8.2.5 删除自定义函数 8.2.6 自定义函数示例 第8章 函数 语法：DROP FUNCTION function_name
第8章 函数 8.2.5 删除自定义函数 语法：DROP FUNCTION function_name 示例：DROP FUNCTION Get_JC 8.2.6 自定义函数示例