数据库之MySQL函数（二）
一、日期和时间函数
1、获取当前日期的函数 都一样，用法不同
CURDATE() ：用于获取系统当前日期 CURRENT_DATE() 用于系统获取当前日期
mysql> select CURDATE(),CURRENT_DATE(); 
2、获取当前时间的函数
CURTIME() ：用于获取系统当前时间
mysql> select CURTIME(); 
3、获取当前日期和时间的函数
CURRENT_TIMESTAMP() ：用于获取系统当前日期和时间 LOCALTIME() ：用于获取系统当前日期和时间 NOW() ：用于获取系统当前日期和时间 SYSDATE()： 用于获取系统当前日期和时间
mysql> select CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
4、获取时间戳的函数
UNIX_TIMESTAMP() 用于获取 UNIX 格式的时间戳
mysql> select UNIX_TIMESTAMP(); 
时间戳是一份能够表示一份数据在一个特定时间点已经存在的完整的可验证的数据。 5、转换时间戳的函数
FROM_UNIXTIME() 用于将 UNIX 格式的时间戳转换为普通格式的时 间
mysql> select FROM_UNIXTIME(1587693176); # 添加之前查出的时间戳
6、获取 UTC 日期的函数
UTC_DATE() ：用于获取当前 UTC (世界标准时间) 日期值
mysql> select UTC_DATE();
7、获取 UTC 时间的函数
UTC_TIME() 用于获取当前 UTC (世界标准时间) 时间值
mysql> select UTC_TIME(); 
8、获取月份的函数
MONTH(date) ：用于返回 date 对应的月份 MONTHNAME(date)： 用于返回 date 对应月份的英文全名
mysql> select MONTH('2020-4-24'),MONTHNAME('2020-4-24');
9、获取星期的函数
DAYNAME(date) ：用于返回 date 对应的工作日的英文名称 DAYOFWEEK(date) ：用于返回 date 对应的一周中的索引，1 表示周日，2 表示周一，...... ，7 表示周六 WEEKDAY(date)： 用于返回日期对应的工作日索引，0 表示周一，1 表示周二，...... ，6 表示周日 WEEK(date)： 用于计算 date 是一年中的第几周，一年有 53 周 WEEKOFYEAR(date) ：用于计算日期 date 是一年中的第几周，一年有 53 周
mysql> select DAYNAME('2020-4-24'),DAYOFWEEK('2020-4-24'),WEEKDAY('2020-4-24'),WEEK('2020-4-24'),WEEKOFYEAR('2020-4-24');
10、获取天数的函数
DAYOFYEAR(date) :用于返回 date 是一年中的 第几天，一年有 365 天 DAYOFMONTH(date): 用于计算 date 是一个月中的第几天
mysql> select DAYOFYEAR('2020-4-24'),DAYOFMONTH('2020-4-24');
11、获取年份的函数
YEAR(date) ：返回 date 对应的年份
mysql> select YEAR('20-04-24'),YEAR('98-04-24');
12、获取季度的函数
QUARTER(date) ：返回 date 对应的一年中的季度值
mysql> select QUARTER('20-04-24');
13、获取分钟的函数
MINUTE(time) 返回 time 对应的分钟值
mysql> select MINUTE('20-04-24 10:18:00');
14、获取秒钟的函数
SECOND(time) 返回 time 对应的秒数
mysql> select SECOND('10:18:22');
15、获取日期的指定值的函数
EXTRACT(type FROM date) 用于获取指定的日期值
mysql> select EXTRACT(YEAR FROM '2020-04-24') AS col1, # 当type为YEAR 时，只返回年值
-> EXTRACT(YEAR_MONTH FROM '2020-04-24 10:18:22') AS col2, # 当type为 YEAR_MONTH时，返回年与月
-> EXTRACT(DAY_MINUTE FROM '2020-04-24 10:18:22') AS col3; # 当type为 DAY_MINUTE时，返回日、小时、分钟 
16、时间和秒钟转换的函数
TIME_TO_SEC(time) ：用于将 time 转换 为秒钟，公式为 " 小时3600 + 分钟60 + 秒 " SEC_TO_TIME(time)： 用于将秒值转换为时间格式
mysql> select TIME_TO_SEC('23:23:00'),SEC_TO_TIME('84180'); 
17、计算日期和时间的函数
DATE_ADD() ：用于对日期进行加运算，格式为 DATE_ADD(date, INTERVAL expr type) DATE_SUB()： 用于对日期进行减运算，格式为 DATE_SUB(date, INTERVAL expr type) SUBDATE()： 用于对日期进行减运算，格式为 SUBDATE(date, INTERVAL expr type) ADDTIME() ：用于对日期进行加运算，格式为 ADDTIME(date, expr) SUBTIME() ：用于对日期进行减运算，格式为 SUBTIME(date, expr) DATE_DIFF() ：用于计算两个日期之间的间隔天数
mysql> select DATE_ADD('2020-12-31 23:59:59', INTERVAL 1 SECOND); # 对指定的日期 增加1秒 
mysql> select DATE_ADD('2020-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND); # 对指定的日期增加1分1秒
mysql> select DATE_SUB('2020-01-01', INTERVAL 31 DAY); # 给指定的日期减去31天 
mysql> select SUBDATE('2020-01-01', INTERVAL 31 DAY); 
mysql> select ADDTIME('2020-12-31 23:59:59', '1:1:1'); # 给指定的日期增加1时1分1 秒 
mysql> select SUBTIME('2020-12-31 23:59:59', '1:1:1'); # 给指定的日期减去1时1分1 秒
mysql> select DATEDIFF('2020-06-01', '2020-04-24');
18、将日期和时间格式化的函数
DATE_FORMAT(date, format) 用于格式化日期，即根据 format 指定的格式 显示 date 值 TIME_FORMAT(time, format) 用于格式化时间，即根据 format 指定的格式显示 time 值 GET_FORMAT() ，我们指定值类型和格式化类型，然后会显示成格式字符串
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); 
mysql> select TIME_FORMAT('16:00:00', '%H %k %I'); 
mysql> select DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE,'USA')); 
%d该月日期，数字形式（00..31） %e该月日期，数字形式（0..31） %f微秒（000000...999999） %H以2位数表 示24小时（00..23） %h,%I 以2位数表示12小时（01..12） %i分钟，数字形式（00-59） %j一年中的天数（001366） %k以24小时（0-23） %l以12小时（0..12） %M月份名称（january..December） %m月份数字形式 （00..12） %p上午（AM）或下午（PM） %r时间，12小时制（小时hh：分钟mm：秒钟ss后面加AM或PM） %S,%s以2位数形式表示秒（00..59） %T时间，24小时制（小时hh：分钟mm：秒数ss） %U周（00..53），其 中周日为每周的第一天 %u周（00..53），其中周一为每周的第一天 %V周（01..53），其中周日为每周的第一 天，和%X一起使用 %v周（01..53），其中周一为每周的第一天，和%x一起使用 %W工作日名称（周日..周六） %w一周中的每日（0=周日..6=周六） %X该周的年份，其中周日为每周的第一天；数字形式4位数，和%V同时 使用 %x该周的年份，其中周一为每周的第一天；数字形式4位数，和%v同时使用 %Y4位数形式表示年份 %y2 位数形式表示年份 %% “%”文字字符
二、条件判断函数
IF() IF(expr, v1, v2) 如果表达式 expr 为 TRUE ，则返回值为 v1 ，否则返回 v2
mysql> select IF(1>2,2,3);
可以看到一并不大于二所以返回v2，也就是3
IFNULL() IFNULL(v1, v2) ，如果 v1 不为 NULL ，则返回值为 v1 ；如果 v1 为 NULL ，则返回值为 v2
mysql> select IFNULL(1,2),IFNULL(NULL,10);
CASE 语法：CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END 含义：如果 expr 等于某个 vn ，则返回对应位置 THEN 后面的结果，如果与所有值都不相等，则返回 ELSE 后面的 rn
mysql> select CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; 
三、系统信息函数
1、获取 MySQL 版本号的函数
VERSION() 用于获取 MySQL 版本号
mysql> select VERSION(); 
2、查看当前用户的连接数的ID函数
CONNECTION_ID() 用于查看当前用户的连接数
mysql> select CONNECTION_ID(); 
mysql> show PROCESSLIST; # 查看当前用户的连接信息
上图标签含义如下：
- Id ：用户登录 MySQL 时，系统分配的连接 id
- User ：当前连接的用户
- Host ：显示这个语句是从哪个 IP 的哪个端口上发出的，可以用来追踪出现问题语句的用户
- db ：显示这个进程目前连接的是哪个数据库
- Command ：显示当前连接执行的命令，一般取值为休眠(Sleep)、查询(Query)、连接(Connect)
- Time ：显示这个状态持续的时间，单位是秒
- State ：显示使用当前连接的 SQL 语句的状态
- Info ：显示这个 SQL 语句
3、查看当前使用的数据库的函数
DATABASE() ：用于查看当前使用的数据库 SCHEMA()： 用于查看当前使用的数据库
mysql> select database(),SCHEMA(); 
4、查看当前登录的用户名的函数
USER() :返回当前登录的用户 及主机名 CURRENT_USER(): 用于返回当前登录的用户及主机名 SYSTEM_USER() :用于返回当前登录的用户及主机名
mysql> select USER(),CURRENT_USER(),SYSTEM_USER(); 
5、查看指定字符串的字符集的函数
CHARSET(str) 用于查看字符串 str 的字符集
mysql> select CHARSET('abc'); 
6、查看指定字符串的排列方式的函数
COLLATION(str)： 用于查看字符串 str 的字符排列方式
mysql> select COLLATION('abc'); 
7、获取后一个自动生成的 ID 值得函数
LAST_INSERT_ID() ：用于获取后一个自动生成的 ID 值
#先创建一个表，其id字段带有 AUTO_INCREMENT约束
mysql> create table tab1(
-> id int auto_increment primary key,
-> name varchar(30)
-> );
mysql> insert into tab1 values (NULL,'zhangsan'); #插入一条数据，这时id没有指定， 则自动生成，id为1
mysql> insert into tab1 values (NULL,'lisi'); # 插入一条数据，这时id没有指定， 则自动生成，id为2
mysql> select * from tab1; # 查看表的信息
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
+----+----------+
mysql> select LAST_INSERT_ID();
mysql> insert into tab1 values (NULL,'aaa'),(NULL,'bbb'),(NULL,'ccc');
mysql> select * from tab1; # 如果我们一次性插入多条数据，虽然 id到了5
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | aaa |
| 4 | bbb |
| 5 | ccc |
+----+----------+
mysql> select LAST_INSERT_ID(); # 但我们用LAST_INSERT_ID()查看时 却为3 ，这是因为LAST_INSERT_ID()只返回插入的 第一行数据时产生值
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+四、加/解密函数 1、加密函数
PASSWORD(str) ：从明文密码 str 计算并返 回加密后的密码字符串，当参数为 NULL 时，返回 NULL MD5(str) 为字符串 str 算出一个 MD5 128 比特校验和 ENCODE(str, pswd_str) 使用 pswd_str 作为密码，加密 str
mysql> select PASSWORD('newpwd');
mysql> select MD5('newpwd');
mysql> select ENCODE('secret','123.com');
2、解密函数
DECODE(crypt_str, pswd_str) 使用 pswd_str 作为密码，解密加密字 符串 crypt_str
mysql> select DECODE(ENCODE('secret','cry'),'cry');
五、其他函数 1、格式化函数
FORMAT(x, n) ：将数字 x 格式化，并以四舍五入的方式保留小数点后 n 位，结果 以字符串的形式返回
mysql> select FORMAT(1.23456, 4), FORMAT(1.2, 4), FORMAT(1.234, 0); 
2、不同进制的数字进行转换的函数
CONV() ：用于不同进制数之间的转换
mysql> select CONV('a',16,2), # 将16进制的a转换为2进制
-> CONV(15,10,2), # 将10进制的15转换为2进制
-> CONV(15,10,8), # 将10进制的15转换为8进制
-> CONV(15,10,16); # 将10进制的15转换为16进制 
3、 IP 地址与数字互相转换的函数
INET_ATON(expr) ：用于将网络地址转 换为一个代表该地址数值的整数
mysql> select INET_ATON('192.168.1.1');
4、加锁函数和解锁函数
GET_LOCK(str, timeout) ：使用字符串 str 来得到一个锁，持续时间 timeout 秒
- 若成功得到锁，则返回 1
- 若操作超时，则返回 0
- 若发生错误，则返回 NULL
mysql> select GET_LOCK('lock1',10); # 返回结果为1，说明成功得到了一个名称为'lock1'的锁，持续时间为 10秒 
RELEASE_LOCAK(str) ：用于解开被 GET_LOCK() 获取的，用字符串 str 所命名的锁
- 若锁被解开，则返回 1
- 若该线程尚未创建锁，则返回 0
- 若命名的锁不存在，则返回 NULL
- 若该锁从未被 GET_LOCK() 的调用获取，或锁已经被提前解开，则该锁不存在
mysql> select RELEASE_LOCK('lock1'); # 返回值为1说明解锁成功 
IS_FREE_LOCK(str)： 检查名为 str 的锁是否可以使用
- 若锁可以使用，则返回 1
- 若锁正在被使用，则返回 0
- 若出现错误，则返回 NULL
mysql> select IS_FREE_LOCK('lock1');
IS_USED_LOCK(str) ：用于检查名为 str 的锁是否正在被使用，若被封锁，则返回使用该锁的客户端的连接标识 符，否则返回 NULL
mysql> select IS_USED_LOCK('lock1');
5、重复执行指定操作的函数
BENCHMARK(count, expr)： 用于重复 count 次执行表 达式 expr
- 可以用于计算 MySQL 处理表达式的速度
- 可以在 MySQL 客户端内部报告语句执行的时间
mysql> select PASSWORD('newpwd'); # # 执行1次加密操作花费了0.00秒 
mysql> select BENCHMARK( 500000, PASSWORD('newpwd') );
+-----------------------------------------+
| BENCHMARK( 500000, PASSWORD('newpwd') ) |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
1 row in set, 1 warning (0.06 sec) # 执行500000次加密操作花费了0.06秒 6、改变字符集的函数
CONVERT(... USING ...) 用于改变字符串的默认字符集
mysql> select CHARSET('abc'); # 默认是utf8
+----------------+
| CHARSET('abc') |
+----------------+
| utf8 |
+----------------+
mysql> select CHARSET(CONVERT('abc' USING latin1)); # 转换成latin1字符集
+--------------------------------------+
| CHARSET(CONVERT('abc' USING latin1)) |
+--------------------------------------+
| latin1 |
+--------------------------------------+7、改变数据类型的函数
CAST(x, AS type) ：用于将一个数据类型的值转换 为另一个数据类型的值 CONVERT(x, type) ：用于将一个数据类型的值转换为另一个数据类型的值
mysql> select CAST(100 AS CHAR(2)); # 将整数类型100转换为带有两个显示宽度的字符串类 型，结果为'10' 
mysql> select CONVERT(100,CHAR(2)); # 将整数类型的100转换为带有两个显示宽度的字符 串类型，结果为'10' 