《轻轻松松认识函数》PPT课件.ppt

第5章轻轻松松认识函数 函数对于任何程序设计语言来说都是非常关键的部分 它给用户提供了强大的功能 帮助用户使用简捷的代码实现复杂的功能 函数在Transact SQL中代表的是能够完成某个功能并返回值的一个模块或者代码块 Transact SQL支持两类函数 系统内置函数和用户定义函数 Transact SQL的内置函数主要包括聚合函数 游标函数 数学函数 字符串函数 系统函数 日期和时间函数 元数据函数以及文本和图像函数等多种本章主要介绍如下几种常用的函数 数学函数字符串函数日期和时间函数数据类型转换函数聚合函数系统函数文本和图像函数 5 1数学函数 Transact SQL提供了数学运算的一组函数 能够对数字表达式进行计算 并将结果返回给用户 这些函数是进行数据运算 统计和分析的基础 5 2字符串函数 字符串函数可以对字符串执行查找 转换等操作 作用于CHAR VARCHAR BINARY 和VARBINARY数据类型 字符串函数可以在SELECT语句的SELECT和WHERE子句以及表达式中使用字符串函数 5 2 1ASCII函数 ASCII函数返回字符串表达式最左端字符的ASCII码值 语法格式 ASCII character expression character expression 字符或字符串类型的表达式 实例 查询 h 的ASCII码值 SELECTASCII happy 返回值为104 类型为int 5 2 2CHAR函数 与ASCII函数的功能恰好相反 CHAR函数把ASCII码值换算成对应的字符 语法格式 CHAR integer expression integer expression 0 255之间的整数 否则返回NULL 实例 查询104对应的字符 SELECTCHAR 104 返回值为 h 类型为char 5 2 3CHARINDEX函数 CHARINDEX函数用于返回字符串中指定表达式的开始位置 语法格式 CHARINDEX expression1 expression2 start location expression1 字符串表达式 指定的搜索串 expression2 字符串表达式 包含要查找的字符序列 start location 搜索的起始位置 是可选项 如不指定或者指定为0或者负值 则搜索从初始位置开始 否则从指定位置开始搜索 5 2 4LEFT函数 LEFT函数返回从字符串左边开始指定个数的字符串 语法格式 LEFT character expression integer expression character expression integer expression 实例 返回字符串前6个字符以及前20个字符 SELECTLEFT Ihavamanyfriends 6 SELECTLEFT Ihavamanyfriends 20 第一条语句返回值为 Ihava 第二条语句返回值为 Ihavamanyfriends 即如果指定长度超过字符串的长度 并不会出错 而是返回整个字符串 5 2 5RIGHT函数 RIGHT函数与LEFT函数功能相反 它返回从字符串右边开始指定个数的字符串 语法格式 RIGHT character expression integer expression character expression integer expression 实例 返回给定字符串后7个字符以及该这七个字符的第一个字符 SELECTRIGHT Ihavamanyfriends 7 SELECTLEFT RIGHT Ihavamanyfriends 7 1 第一条语句返回值为 friends 第二条语句返回值为 f 即先使用RIGHT Ihavamanyfriends 7 求出最后7个字符组成的字符串 然后再使用LEFT函数求出该字符串的首字符 5 2 6LEN函数 LEN函数用于返回指定字符串的总长度 即包含的字符的个数 语法格式 LEN string expression string expressionn 指定的需计算长度的字符串 实例 计算给定字符串的长度 SELECTLEN friends SELECTLEN 朋友 SELECTLEN 2046 这三条语句的返回值依次为7 2 4 需要注意的是一个汉字也是作为一个字符处理的 同时纯数字组成的串可以不加 但如果是数字和字符混合出现时必须要加上 5 2 7LTRIM函数 LTRIM函数用于去除指定字符串左边的空格 语法格式 LTRIM character expression character expression 指定字符串或二进制表达式 实例 去除指定字符串前的空格 SELECTLTRIM Therearetwospaceintheleft SELECTLTRIM Therearetwospaceanda Tab intheleft 第一条语句返回值为 Therearetwospaceintheleft 第二条语句返回值为 Therearetwospaceanda Tab intheleft 第二条语句看似空格没有去除掉 实际上在字符串前的输入的 Tab 键造成的 并非空格 5 2 8RTRIM函数 RTRIM函数用于去除指定字符串右边的空格 语法格式 RTRIM character expression character expression 指定字符串或二进制表达式 实例 去除指定字符串前后的空格 SELECTRTRIM LTRIM Therearethreespaceintheleftandthreespaceintherignt 该语句的返回值为 Therearethreespaceintheleftandthreespaceintherignt 即先使用LTRIM函数去除字符串左端的空格 然后对得出的字符处去除右端的空格 5 2 9LOWER函数 LOWER函数用于把指定的字符串中的大写字符装换为相应的小写字符 语法格式 LOWER character expression character expression 指定需要进行转换的字符串 实例 将字符串中的大写字符全部转换成小写字符 SELECTLOWER Abecameaafterthisfunction 该语句的返回值为 abecameaafterthisfunction 即经过转换大写字符和小写字符没有任何区别 便于程序的统一处理 5 2 10UPPER函数 UPPER函数用于把指定的字符串中的小写字符装换为相应的大写字符 语法格式 UPPER character expression character expression 指定需要进行转换的字符串 实例 将字符串中的小写字符全部转换成大写字符 SELECTUPPER aTOA 该语句的返回值为 ATOA 5 2 11PATINDEX函数 PATINDEX函数用于返回指定表达式中某模式的初始位置 如果在字符串中未能匹配该模式 则返回0 语法格式 PATINDEX pattern expression pattern expression 实例 返回字符串in在字符串Therearemanytreeinmygarden中的初始位置 SELECTPATINDEX in Therearemanytreeinmygarden 该语句的返回值为21 5 2 12REPALCE函数 PEPLACE函数用于在一个字符串中用指定的字符串替换另一个字符串 语法格式 REPLACE string expresddion1 string expresddion2 string expresddion3 string expresddion1 要搜索的原字符串 可以是字符或二进制数据类型 string expresddion2 原字符串中将要被替换的字符串 string expresddion3 指定的进行替换的字符串 5 2 13REVERSE函数 REVERSE函数用于返回指定表达式的逆向形式 语法格式 REVERSE character expression character expression 指定的用于需要进行转换的表达式 可以是字符串或二进制数据实例 将字符串 Afineday 进行整体反转 SELECTREVERSE Afineday 该语句返回值为 yadenifA 即原字符串的最后一个字符作为新字符串的第一个字符 原字符串的第一个字符作为新字符串的最后一个字符 5 2 14STR函数 STR函数用于将数字数据转换为字符数据 语法格式 STR float expression length decimal float expression 带小数点的近似数字 length 包括小数点 符号 数字或空格的总长度值 默认为10 decimal 小数点右边的位数 5 2 15SUBSTRING函数 SUBSTRING函数用于获取指定字符串或二进制数据的一部分 语法格式 SUBSTRING expression start length expression 字符串表达式 start 指定子串的开始位置 length 指定子串的长度 5 3日期和时间函数 日期和时间函数主要用于对时间和日期有关信息的输入 处理及输出操作 基本使用方式与其他函数相同 5 3 1DATEADD函数 DATEADD函数用于返回指定日期加上一个时间段后的新日期时间值 语法格式 DATEADD datepart number date datepart 返回新值的日期的组成部分 可识别的日期部分为year month day hour minute second millsecond等 number 与datepart相加的指定值 如果该值为非整数值 舍弃该值的小数部分 date 表达式 用于返回datetime或日期格式的字符串 5 3 2DATENAME函数 DATENAME函数用于返回指定日期指定部分的字符串 语法格式 DATENAME datepart date datepart 返回新值的日期的组成部分 date 表达式 用于返回datetime或日期格式的字符串 实例 查询给定日期的星期值 SELECTDATENAME weekday 2010 10 10 该语句的返回值为星期日 即指定时间2010 10 10为星期日 5 3 3DATEPART函数 DATENPART函数用于返回指定日期指定部分的整数值 语法格式 DATEPART datepart date datepart 返回新值的日期的组成部分 date 表达式 用于返回datetime或日期格式的字符串 实例 查询给定日期的星期值 SELECTDATEPART weekday 2010 10 10 该语句的返回值为1 此处既可以看出该函数与DATENAME函数的区别 5 3 4DAY函数 DAY函数用于返回指定日期的天数 从功能实现上进行比较 该函数等价于DATEPART dd date 函数的实现 语法格式 DAY day day 日期表达式 实例 查询给定日期天数 SELECTDAY 2010 10 10 该语句的返回值为10 5 3 5MONTH函数 MONTH函数用于返回指定日期的月份 从功能实现上进行比较 该函数等价于DATEPART mm date 函数的实现 语法格式 MONTH day day 日期表达式 实例 查询给定日期月份 SELECTMONTH 2010 10 10 该语句的返回值为10 5 3 6YEAR函数 YEAR函数用于返回指定日期的年份 从功能实现上进行比较 该函数等价于DATEPART yy date 函数的实现 语法格式 YEAR day day 日期表达式 实例 查询给定日期的年份 SELECTYEAR 2010 10 10 该语句的返回值为2010 5 3 7GETDATE函数 GETDATE函数用于返回系统的日期和时间 语法格式 GETDATE 参数 无 实例 查询系统当前时间及当前天数 SELECTGETDATE SELECTDAY GETDATE 第一条语句返回值为2010 05 3022 21 45 043 即当前的系统日期和时间 第二条语句在取得系统日期和时间的前提下 使用DAY函数取得当前的天数值 5 4数据类型转换函数 同时处理不同数据类型的值时 SQLServer一般会自动进行隐式的类型转换 这种转换对于相近数据类型的值是有效的 比如说int型和float型的数据 而对于其他的类型 如float型数据与字符型数据 隐式转换是行不通的 必须使用显示转换 T SQL提供两个显示转换函数 分别是CAST函数与CONVERT函数 下面将对这两个类型转换函数详细介绍 5 4 1CAST函数 CAST函数用于将某种类型的数据转换为另一种类型的数据 语法格式 CAST expressionASdata type expression 指定的需进行转换的表达式AS 参数分隔符data type 目标数据类型 5 4 2CONVERT函数 CONVERT函数用于按照指定的格式将数据装换成另一种类型的数据 语法格式 CONVERT data type length expression style data type 目标系统所提供的数据类型 bigint和sql variant length 数据长度expression 指定的需进行转换的表达式style 日期格式实例 输出当前系统的日期 SELECTCONVERT varchar 12 getdate 该语句的返回值为05312010 5 5聚合函数 聚合函数能够对一组值进行计算 并返回一个单一值 聚合函数经常与SELECT语句的GROUPBY字句联合使用 5 5 1AVG函数 AVG函数用于计算表达式的平均值 语法格式 AVG ALL DISTINCT expression ALL 对表达式的所有值进行计算 为默认设置 DISTINCT 去除重复值后计算平均值 expression 精确数字或近似数字数据类型的表达式 不允许使用聚合函数与子查询 实例 计算班级中学生的平均年龄 SELECTAVG stuAge fromstudentInfoSELECTAVG DISTINCTstuAge fromstudentInfo第一条语句返回值为22 第二条语句返回值为23 返回值不同的原因在于第二条语句使用了 DISTINCT 去除了数据中的重复值 5 5 2COUNT函数 COUNT函数用于计算总数 语法格式 COUNT ALL DISTINCT expression ALL 对表达式的所有值进行计算 为默认设置 DISTINCT 去除重复值后计算总数 expression 精确数字或近似数字数据类型的表达式 不允许使用聚合函数与子查询 5 5 3MAX函数 MAX函数用于计算组数据中的最大值 语法格式 MAX ALL DISTINCT expression ALL 对表达式中的所有值进行计算 为默认设置 DISTINCT 去除重复值后计算最大值 对于MAX函数来说 使用此设置五任何意义 expression 可以是常量 列名 函数等类型 不允许使用聚合函数与子查询 实例 求出班级中学生的最大年龄是多少 SELECTMAX stuAge fromstudentInfo该语句返回值为24 5 5 4MIN函数 MIN函数用于计算组数据中的最小值 语法格式 MAX ALL DISTINCT expression ALL 对表达式中的所有值进行计算 为默认设置 DISTINCT 去除重复值后计算最小值 expression 可以是常量 列名 函数等类型 不允许使用聚合函数与子查询 实例 求出班级中学生的最小年龄是多少 SELECTMIN stuAge fromstudentInfo该语句返回值为20 5 5 5SUM函数 SUM函数用于计算表达式中所有数据的和 语法格式 SUM ALL DISTINCT expression ALL 对表达式中的所有值进行计算 为默认设置 DISTINCT 去除重复值后计算数据和 expression 可以是常量 列名 函数等类型 不允许使用聚合函数与子查询 5 6系统函数 系统函数可以显示T SQL执行运算后返回的SQLServer服务器和数据库中有关值 对象和设置的特殊信息 5 6 1SERVERPROPERTY函数 SERVERPROPERTY函数用于查询有关服务器实例的属性信息 语法格式 SERVERPROPERTY argument 其中 argument指定要查询的信息标志 实例 查询服务器实例名称 SELECTCONVERT char 15 SERVERPROPERTY servername 该语句返回值为当前服务器所使用的实例名称 5 6 2DATABASEPROPERTY函数 DATABASEPROPERTY函数用于查询指定数据库和属性名的命名数据库属性值 语法格式 DATABASEPROPERTY argument1 argument2 argument 指定数据库 argument2 要查询的属性名称实例 查询master数据库的 IsTruncLog 属性值设置情况 SELECTDATABASEPROPERTY master IsTruncLog 该语句返回值为1 表示该选项设置成了true 5 6 3TYPEOFPROPERTY函数 TYPEOFPROPERTY函数用于查询有关数据类型的信息 语法格式 TYPEOFPROPERTY date type property name date type 指定数据库 property name 要查询的属性名称实例 查询int与char类型的PRECISION属性 SELECTTYPEPROPERTY int PRECISION SELECTTYPEPROPERTY char PRECISION 两条语句的返回值分别为10 8000 5 6 4OBJECT ID函数 OBJECT ID函数用于查询数据库对象的ID号 语法格