第5章 MySQL 函数（下）

一 、MySQL 日期和时间函数

1、 日期和时间函数介绍

日期和时间函数	功能介绍
WEEK(date,mode)	该函数返回日期参数 date 对应的星期数。WEEK( )的双参数形式允许指定该星期是否起始于周日或周一， 以及返回值的范围是否为从 0 到 53 或从 1 到53。若 mode 参数被省略，则使用 default_week_format 系统自变量的值
DAYOFYEAR(date)	返回日期参数 date 对应的当前年份的第几天，范围为 1 ～ 365
year(date)	返回日期参数 date 对应的年份
month(date)	返回日期参数 date 对应的月份
day(date)	返回日期参数 date 对应的当前月份的天数值
DATE_ADD(date,INTERVAL   expr type) 和      ADDDATE(date,  INTERVAL expr type)	加法计算日期函数，这两个函数作用相同，都是返回一个以参数 date 为起始日期加上时间间隔之后的日期值。expr 参数是希望添加的时间间隔，type 表示时间间隔的类型，type 和 expr 取值见表 5.6

日期和时间函数	功能介绍
DATE_SUB(date,INTERVAL   expr type) 和   SUBDATE(date,INTERVAL   expr type)	减法计算日期函数，这两个函数作用相同，都是返回一个以参数 date 为起始日期减去时间间隔之后的日期值。expr 参数是希望添加的时间间隔，type 表示时间间隔的类型，type 和 expr 取值见表 5.6
ADDTIME(time,expr)	加法计算时间值函数，返回将 expr 值加上原始时间time 之后的值
SUBTIME(time,expr)	减法计算时间值函数，返回将原始时间 time 减去expr 之后的值
DATEDIFF(date1,date2)	计算两个日期之间天数间隔的函数，返回参数 date1 减去 date2 之后的值。如果 date1 在 date2 之前，返回的值为负值
DATE_FORMAT(date,  format)	日期和时间格式化函数。返回根据参数 format 指定的格式显示的 date 值。format 包含的格式见表 5.7，即 DATE_FORMAT 和 TIME_FORMAT 函数中format 格式表
TIME_FORMAT(time,  format)	时间格式化函数。返回根据参数 format 指定的格式显示的 time 值

type 值	预期的 expr 格式
MICROSECOND	微秒数
SECOND	秒数
MINUTE	分钟数
HOUR	小时数
DAY	天数
WEEK	周数
MONTH	月数
QUARTER	季度数
YEAR	年数

type 值	预期的 expr 格式
SECOND_MICROSECOND	‘秒数 . 微秒数’
MINUTE_MICROSECOND	‘分钟数 . 微秒数’
MINUTE_SECOND	‘分钟数 . 秒数’
HOUR_MICROSECOND	‘小时数 . 微秒数’
HOUR_SECOND	‘小时数 : 分钟数 : 秒数’
HOUR_MINUTE	‘小时数 : 分钟数’
DAY_MICROSECOND	‘天数 . 微秒数’

type 值	预期的 expr 格式
DAY_SECOND	‘天数 小时数 : 分钟数 : 秒数’
DAY_MINUTE	‘天数 小时数 : 分钟数’
DAY_HOUR	‘天数 小时数’
YEAR_MONTH	‘年数 - 月数’

格式说明符	描述说明
%a	一星期中每天名称的缩写（Sun...Sat）
%b	月份的缩写（Jan...Dec）
%c	月份的数字表现形式（0...12）
%D	带有英语后缀的一个月中的每一天的名称（0th、1st、2nd、3rd）
%d	用数字形式表现的每月中的每一天（00...31）
%e	用数字形式表现的每月中的每一天（0...31）
%f	毫秒（000000...999999）
%H	24 时制显示的小时（00...23）
%h	12 时制显示的小时（01...12）

格式说明符	描述说明
%I	12 时制显示的小时（01...12）
%i	以数字形式表现的分钟数（00...59）
%j	一年中的每一天（001...366）
%k	24 时制小时的另一种表现格式（0...23）
%l	12 时制小时的另一种表现格式（1...12）
%M	用完整英文名称表示的月份（January...December）
%m	用数字表现的月份（00...12）
%p	上午（AM）或下午（PM）
%r	12 时制的时间值（hh:mm:ss，后跟 AM 或 PM）

格式说明符	描述说明
%S	秒（00...59）
%s	秒（00...59）
%T	24 时制的小时（hh:mm:ss）
%U	星期（00...53），其中星期天是每星期的开始日
%u	星期（00...53），其中星期一是每星期的开始日
%V	星期（01...53），其中星期天是每星期的开始日，和 %X 一起使用
%v	星期（01...53），其中星期一是每星期的开始日，和 %x 一起使用
%W	一星期中各日名称（Sunday...Saturday）
%w	一星期中各日名称（0 代表星期日，6 代表星期六，以此类推）

格式说明符	描述说明
%X	某星期所处年份。其中，星期天是每星期的开始日，采用 4 位数字形式表现，和 %V 一起使用
%x	某星期所处年份。其中，星期一是每星期的开始日，采用 4 位数字形式表现，和 %V 一起使用
%Y	4 位数字表示的年份
%y	2 位数字表示的年份

2、使用 curdate() 和 current_date() 显示当前系统日期

select curdate(),current_date()

3、使用 curtime() 和 current_time() 显示当前系统时间

select curtime(),current_time()

4、使用 current_timestamp()、localtime()、now() 和 sysdate() 函数显示当前系统的日期和时间

select current_timestamp(),localtime(),now(),sysdate()

5、使用 dayname()、dayofweek() 和 weekday() 函数返回指定日期所对应的星期信息

select  dayname(now()),dayofweek(now()),weekday(now())

6、使用 date_format(date,format) 函数显示当前系统时间，格式为“4 位年 - 月 - 日 小时 : 分钟 : 秒”，其中小时为 24 小时制

select date_format(now(),'%Y-%c-%d %H:%i:%s')

7、使用 date_add(date,INTERNAL expr type) 和 adddate(date,INTERNAL expr type) 函数对当前系统时间执行加法操作

select date_format(now(),'%Y-%c-%d %H:%i:%s') 当前时间 , DATE_ADD(now(),INTERVAL 20 SECOND) 增加 20秒 ,ADDDATE(now(),INTERVAL 20 MINUTE) 增加 20 分钟 , ADDDATE(now(),INTERVAL '1 1:1:1' DAY_SECOND) 增加 1 天 1 小时 1 分钟 1 秒

 8、使用 datediff(date1,date2) 返回当前日期与 2017-5-25 日之间的间隔天数

select datediff(now(),'2017-5-25')

二 、 MySQL 系统信息函数和加密函数

1、系统信息函数介绍

系统信息函数	功能介绍
VERSION()	返回当前 MySQL 版本号的字符串。执行“select version( )”，返回作者使用的 MySQL 版本 5.6.24
CONNECTION_ID()	返回当前 MySQL 服务器当前用户的连接次数。每个连接都有各自唯一的 ID，登录次数不同，返回的数值也就不同
PROCESSLIST()	"show processlist" 输出结果显示哪些线程在运行，不仅可以查看当前所有连接数，还可以查看当前的连接状态，帮助识别出有问题的查询语句等。如果是 root 账号，能看到所有用户的当前连接；如果是其他普通帐号，则只能看到自己占用的连接。show processlist 只列出前100 条，如果想全部列出可使用 "show full processlist" 命令
DATABASE()， SCHEMA()	返回使用 utf8 字符集的默认（当前）数据库名
USER(),CURRENT_   USER(),SYSTEM_ USER()，   SESSION_USER()	这几个函数返回当前被 MySQL 服务器验证的用户和主机名组合。这个值符合确定当前登录用户存取权限的 MySQL 账户。一般情况下，这几个函数的返回值是相同的
CHARSET(str)	返回字符串 str 自变量的字符集
LAST_INSERT_ID()	获取最后一个自动生成的 ID 值的函数。自动返回最后一个 INSERT或 UPDATE 为 AUTO_INCREMENT 列设置的第一个发生的值

2、MySQL 中加密函数用来对数据进行加密和解密的处理，以保证数据表中某些重要数据不被别人窃取，这些函数能保证数据库的安全

加密函数	功能介绍
PASSWORD(str)	加密函数。该函数对原明文 str 进行加密，并返回加密之后的字符串。该加密函数不可逆
MD5(str)	加密函数。该函数对原明文 str 计算出一个 MD5 128 比特校验和，该值以 32 位十六进制数字的二进制字符串形式返回。该加密函数可逆
ENCODE(str,pswd_str)	加密函数。该函数使用 pswd_str 作为密钥对 str 进行加密，该加密函数可以使用 DECODE() 函数进行解密
DECODE(crypt_str,  pswd_str)	解密函数。使用 pswd_str 作为密钥，对经过 ENCODE 加密之后的密文 crypt_str 进行解密

三 、总结

1、ceil(x) 和 floor(x) 函数的功能是：

ceil(x) 返回不小于 x 的最小整数值，floor(x) 返回不大于 x 的最大整数值。

 2、locate(s1,str)、substr(str,pos,len) 和 strcmp(s1,s2) 函数的功能：

(1)locate(s1,str)：匹配字符串开始位置的函数。返回子字符串 s1 在字符串 str 中第一次出现的位置。若 str中没有包括 s1，则返回 0。

(2)substr(str,pos,len)：截取原始字符串 str 中从 pos 开始的 len 长度字符串。len 是可选项，如果该项缺省则获取从 pos 开始的剩余字符串内容。

(3)strcmp(s1,s2)：比较字符串大小的函数。若 s1 和 s2 相等，则返回 0；若 s1 小于 s2，则返回 -1；若 s1 大于 s2，则返回 1。

3、dayofweek(date)、date_add(date,INTERVAL expr type) 和 datediff(date1, date2) 函数的功能：

(1)dayofweek(date)：返回日期参数 date 对应的一周的索引位置。返回值的范围是 1 ～ 7。其中，1 表示周日，2 表示周一，…，7 表示周六。

(2)date_add(date,INTERVAL expr type)：加法计算日期函数，这两个函数作用相同，都是返回一个以参数 date 为起始日期加上时间间隔之后的日期值。expr 参数是希望添加的时间间隔，type 表示时间间隔的类型。

(3)datediff(date1,date2)：计算两个日期之间天数间隔的函数，返回参数 date1 减去 date2 之后的值。如果date1 在 date2 之前，返回的值为负值。