(44) 将日期和时间格式化的函数
mysql> select date_format(now(),'%y %m %d'); +-------------------------------+ | date_format(now(),'%y %m %d') | +-------------------------------+ | 23 04 14 | +-------------------------------+ 1 row in set (0.00 sec) mysql> select date_format(now(),get_format(date,'usa')); +-------------------------------------------+ | date_format(now(),get_format(date,'usa')) | +-------------------------------------------+ | 04.14.2023 | +-------------------------------------------+ 1 row in set (0.00 sec)
四，逻辑函数
(45) IF() #判断添加是否正确，正确返回第一个值，错误返回第二个值
mysql> select if(1>2,'true','false'); +------------------------+ | if(1>2,'true','false') | +------------------------+ | false | +------------------------+ 1 row in set (0.00 sec) mysql> select if(1<2,'true','false'); +------------------------+ | if(1<2,'true','false') | +------------------------+ | true | +------------------------+ 1 row in set (0.00 sec)
(46) IFNULL() #判断字符串是否为空
mysql> select ifnull(null,'true'); +---------------------+ | ifnull(null,'true') | +---------------------+ | true | +---------------------+ 1 row in set (0.00 sec)
(47) CASE
mysql> select case 1 when 1 then 'one' end; +------------------------------+ | case 1 when 1 then 'one' end | +------------------------------+ | one | +------------------------------+ 1 row in set (0.00 sec) mysql> select case 1 when 1 then 'one' when 2 then 'two' end; +------------------------------------------------+ | case 1 when 1 then 'one' when 2 then 'two' end | +------------------------------------------------+ | one | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> select case 1 when 1 then 'one' when 2 then 'two' else 'no' end; +----------------------------------------------------------+ | case 1 when 1 then 'one' when 2 then 'two' else 'no' end | +----------------------------------------------------------+ | one | +----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select case 3 when 1 then 'one' when 2 then 'two' else 'no' end; +----------------------------------------------------------+ | case 3 when 1 then 'one' when 2 then 'two' else 'no' end | +----------------------------------------------------------+ | no | +----------------------------------------------------------+ 1 row in set (0.00 sec)
五，系统函数
(48) 获取 MySQL 版本号的函数：
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.36 | +-----------+ 1 row in set (0.00 sec)
(49) 查看当前用户的连接数的ID函数:
mysql> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 3 | +-----------------+ 1 row in set (0.00 sec)
(50) 查看当前使用的数据库的函数:
mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec)
(51) 查看当前登录的用户名的函数:
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> show processlist; #查看系统当前所有的登录用户的详细信息 +----+------+-----------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+------------------+ | 3 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+------+-----------+------+---------+------+----------+------------------+ 1 row in set (0.00 sec)
(52) 查看指定字符串的字符集的函数:
mysql> select charset('a'); +--------------+ | charset('a') | +--------------+ | utf8 | +--------------+ 1 row in set (0.01 sec)
(53) 查看指定字符串的排列方式的函数:
mysql> select collation('a'); +-----------------+ | collation('a') | +-----------------+ | utf8_general_ci | +-----------------+ 1 row in set (0.01 sec)
(54) 获取最后一个自动生成的 ID 值得函数
mysql> select last_insert_id(); #查看当前库中最后一个插入库中的最后一行的ID +------------------+ | last_insert_id() | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec)
六，加密/解密函数
(55) 加密函数
mysql> select password('123'); #使用password加密方法进行加密 +-------------------------------------------+ | password('123') | +-------------------------------------------+ | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select md5('123'); #使用md5进行加密 +----------------------------------+ | md5('123') | +----------------------------------+ | 202cb962ac59075b964b07152d234b70 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select encode('123','test'); #使用test进行加密 +----------------------+ | encode('123','test') | +----------------------+ | ~▒5 | +----------------------+ 1 row in set, 1 warning (0.00 sec)
(56) 解密函数 #将123使用test来进行加密 然后通过test进行解密
mysql> select decode(encode('123','test'),'test'); +-------------------------------------+ | decode(encode('123','test'),'test') | +-------------------------------------+ | 123 | +-------------------------------------+ 1 row in set, 2 warnings (0.00 sec)
七，其他函数
(57) 格式化函数：
mysql> select format(123.123,4); +-------------------+ | format(123.123,4) | +-------------------+ | 123.1230 | +-------------------+ 1 row in set (0.00 sec)
(58) 不同进制的数字进行转换的函数: #将二进制转为16进制
mysql> select conv('101010111101000',2,16); +------------------------------+ | conv('101010111101000',2,16) | +------------------------------+ | 55E8 | +------------------------------+ 1 row in set (0.00 sec)
(59) IP 地址与数字互相转换的函数：
mysql> select inet_aton('127.0.0.1'); +------------------------+ | inet_aton('127.0.0.1') | +------------------------+ | 2130706433 | +------------------------+ 1 row in set (0.00 sec) mysql> select inet_ntoa('2130706433'); +-------------------------+ | inet_ntoa('2130706433') | +-------------------------+ | 127.0.0.1 | +-------------------------+ 1 row in set (0.00 sec)
(60) 重复执行指定操作的函数:
mysql> select benchmark(1000,password('123')); +---------------------------------+ | benchmark(1000,password('123')) | +---------------------------------+ | 0 | +---------------------------------+ 1 row in set, 1 warning (0.00 sec)
(61) 改变字符集的函数:
mysql> select convert('a' using 'gb18030'); +------------------------------+ | convert('a' using 'gb18030') | +------------------------------+ | a | +------------------------------+ 1 row in set (0.00 sec)
(62) 改变数据类型的函数：
mysql> select convert(123,char(2)); +----------------------+ | convert(123,char(2)) | +----------------------+ | 12 | +----------------------+ 1 row in set, 1 warning (0.00 sec)
