mySQL教学第6章MySQL内置函数.ppt

第6章 MySQL函数,微软最有价值专家（MVP） IT集成课程视频教学下载地址 ,本章内容,6.1 MySQL函数简介 6.2 数学函数 6.3 字符串函数 6.4 日期和时间函数 6.5 条件判断函数 6.6 系统信息函数 6.7 加密函数 6.8 其他函数 6.9综合案例MySQL函数的使用,6.2 MySQL函数简介,各类函数从功能方面主要分为以下几类： 数学函数 字符串函数 日期和时间函数 条件判断函数 系统信息函数和加密函数 其他函数,6.2数学函数,绝对值函数ABS(x)和返回圆周率的函 数PI() 平方根函数SQRT(x)和求余函数MOD(x,y) 获取整数的函数CEIL(x)、CEILING(x)和 FLOOR(x) 获取随机数的函数RAND()和RAND(x) 四舍五入函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y) 符号函数SIGN(x),绝对值函数ABS(x)和返回圆周率的函 数PI(),绝对值函数：ABS(x) 圆周率函数：PI(),平方根函数SQRT(x)和求余函数MOD(x,y),平方根函数：SQRT(x) 求余函数：MOD(x,y),获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x),获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x),获取随机数的函数RAND()和RAND(x),获取随机数的函数RAND()和RAND(x),四舍五入函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y),四舍五入函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y),符号函数SIGN(x),SIGN(x)返回参数的符号，x的值为负、零或正时返回结果依次为-1、0或1。,6.3字符串函数,计算机字符串字符数的函数和字符串长度的函数 Length() char_length() 合并字符串 Concat(s1,s2) 结果 s1s2 替换字符串的函数 Insert(s1,x,len,s2)使用s2替换s1字符串 大小写字符转换函数 lower() upper() 获取指定长度的字符串函数 Left(s,n) right(s,n),6.3字符串函数,填充字符串的函数 PAD(s1,10,s2) 删除空格的函数 LTRIM() 删除指定字符串两端字符的函数 TRIM(s1 from s) 重复生成字符串的函数 Repeat(s,n) 空格函数和替换函数 Space(n)空格函数 比较字符串大小函数 STRCMP(abc,def) 获取子字符串 SUBSTRING(password,4,2) 匹配子串开始位置的函数 Locate(str1,str)、position(str1 in str),6.4日期和时间函数1,取当前日期和时间 curdate(),CURRENT_DATE() 获取当前日期和时间的函数 CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE() 获取UTC（世界标准时间） UTC_DATE() UTD日期,CURDATE() 当前日期,UTC_TIME() UTC时间,CURTIME() 当前时间 获取月份的函数 MONTH(2012-02-12),MONTHNAME(2012-02-12),6.4 日期和时间函数2,获取星期的函数 DAYNAME(2012-02-23) 获取星期数 获取指定日期是一年的第几周 WEEK(2012-08-04) 获取天数的函数 获取指定日期是一年中的第几天 DAYOFYEAR(2012-08-04),6.4 日期和时间函数3,获取年份、季度、小时、分钟和秒钟的函数 YEAR(11-02-04) 00-69转换成2000-2069，70-99转换成1970-1999 QUARTER(2012-02-21) HOUR(NOW() MINUTE(NOW() SECOND(NOW() 获取日期 指定值的函数 EXTRACT(YEAR from NOW(),EXTRACT(YEAR_MONTH from NOW(),EXTRACT(DAY_MINUTE from NOW(),6.4日期和时间函数4,计算日期和时间的函数 select DATE_ADD(2011-12-31 23:59:59,interval 1 second) as 加1秒, DATE_ADD(2011-12-2 21:05:23,INTERVAL 2 day) as 加2天, DATE_ADD(2011-12-2 21:05:23,interval -2 DAY) as 减2天, DATE_ADD(2011-12-2 21:05:23,interval -2 YEAR) as 2年前, DATE_ADD(2011-12-2 21:05:23,interval 2 MONTH) as 2月后 获得两个时间间隔天数 select sname 姓名,DATEDIFF(NOW(),birthday) 来到这个世界的天数 from TStudent,6.4日期和时间函数5,将日期和时间格式化的函数 DATE_FORMAT(date,format),6.5 条件判断函数,IF(expr,v1,v2)函数 IFNULL(v1,v2)函数 CASE函数,IF(expr,v1,v2)函数,IF(expr, v1, v2)如果表达式expr是TRUE(expr 0 and expr NULL)，则IF()的返回值为v1；否则返回值则为v2。IF()的返回值为数字值或字符串值，具体情况视其所在语境而定。,IFNULL(v1,v2)函数,IFNULL(v1,v2)假如v1不为NULL，则IFNULL()的返回值为v1，否则其返回值为v2。,CASE函数,CASE expr WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE rn END CASE WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE rn END,6.6 系统信息函数,获取MySQL版本号、连接数和数据库名的函数 获取用户名的函数 获取字符串的字符集和排序方式的函数 获取最后一个自动生成的ID值的函数,获取MySQL版本号、连接数和数据库名的函数,VERSION()返回指示MySQL服务器版本的字符串。 CONNECTION_ID()返回当前MySQL服务器当前连接的次数，每个连接都有各自的唯一的ID。 DATABASE()和SCHEMA()函数返回使用utf8字符集的默认(当前)数据库名。,获取用户名的函数,USER() CURRENT_USER CURRENT_USER() SYSTEM_USER() SESSION_USER(),获取字符串的字符集和排序方式的函数,CHARSET(str)返回字符串str自变量的字符集。 COLLATION(str)返回字符串str的字符排列方式。,获取最后一个自动生成的ID值的函数,LAST_INSERT_ID()自动返回最后一个INSERT或UPDATE问询为AUTO_INCREMENT列设置的第一个发生的值。,6.7 加密函数,加密函数PASSWORD(str) 加密函数MD5(str) 加密函数ENCODE(str,pswd_str) 解密函数DECODE(crypt_str,pswd_str),加密函数PASSWORD(str),PASSWORD(str)从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时返回NULL。,加密函数MD5(str),MD5(str)为字符串算出一个MD5 128比特检查和。,加密函数ENCODE(str,pswd_str),ENCODE(str,pswd_str)使用pswd_str作为密码，加密str。使用DECODE()解密结果。,解密函数DECODE(crypt_str,pswd_str),DECODE(crypt_str,pswd_str)使用pswd_str作为密码，解密加密字符串 crypt_str，crypt_str是由ENCODE()返回的字符串。,6.8 其他函数,格式化函数FORMAT(x,n) 不同进制的数字进行转换的函数 IP地址与数字相互转换的函数 加锁函数和解锁函数 重复执行指定操作的函数 改变字符集的函数 改变数据类型的函数,格式化函数FORMAT(x,n),FORMAT(x,n)将数字x的格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0，则返回结果不带有小数部分。,不同进制的数字进行转换的函数,CONV(N, FROM_base, to_base)函数进行不同进制数间转换。,IP地址与数字相互转换的函数,INET_ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。 INET_NTOA(expr)给定一个数字网络地址(4或8比特)，返回作为字符串的该地址的点地址表示。,加锁函数和解锁函数,GET_LOCK(str,timeout) RELEASE_LOCK(str) IS_FREE_LOCK(str) IS_USED_LOCK(str),重复执行指定操作的函数,BENCHMARK(count,expr)函数重复count次执行表达式expr。,改变字符集的函数,CONVERT(. USING .)带有USING的CONVERT()函数被用来在不同的字符集之间转化数据。,数据类型的函数,CAST(x , AS type)和CONVERT(x,