MySQL 函数探索

数学

--绝对值
求2，-3.3和-33的绝对值：
SELECT ABS(2), ABS(-3.3), ABS(-33);

--求余
--用在范围限制方面，如分母为8，求余后的数字不会超过8：
 SELECT MOD(31,8),MOD(234, 10),MOD(45.5,6);
  7		4		3.5
  
--向上取整
 SELECT  CEIL(-3.35),CEILING(3.35);
-3  4

--向下取整：
 SELECT FLOOR(-3.35), FLOOR(3.35);
-4 3

--产生指定范围的随机数：
SELECT
	round( RAND( ) * 10 ),
	round( RAND( ) * 100 ),
	RAND( );
10	98	0.030897653690443984

--四舍五入-整数： 
 SELECT ROUND(-1.14),ROUND(-1.67), ROUND(1.14),ROUND(1.66);
四舍五入-两位小数：
 SELECT ROUND(1.3864, 2), ROUND(1.38, 0), ROUND(232.38, -1);
 1.39	1	230
--截断，非四舍五入
ELECT TRUNCATE(1.31,1), TRUNCATE(1.99,1), TRUNCATE(1.99,0), TRUNCATE(19.99,-1);
1.3	1.9	1	10
 
--返回参数的符号：
 SELECT SIGN(-21),SIGN(0), SIGN(21);
-1	0	1

## 字符串
--字符串长度(或：CHAR_LENGTH)：
 SELECT LENGTH('date'), LENGTH('egg');
4	3

--连接字符串(其中有一个为null则整个拼接为null)：
 SELECT CONCAT('My SQL', '5.6'),CONCAT('My',NULL, 'SQL');
My SQL5.6	null

--用分隔符连接字符串：
 SELECT CONCAT_WS('-', '1st','2nd', '3rd'), CONCAT_WS('*', '1st', NULL, '3rd');
 1st-2nd-3rd	1st*3rd

--大小写
SELECT ucase('BEAUTIful'), lcase('Well');
BEAUTIFUL	well

```sql
--符串左、右、填充
SELECT LEFT('football', 5),RIGHT('football', 4);
footb	ball
SELECT LPAD('hello',4,'??'), LPAD('hello',10,'??');
 hell	?????hello
 SELECT RPAD('hello',4,'?'), RPAD('hello',10,'?');
 hell	hello?????

--删除字符串左边的空格(右侧：RTRIM；两边：TRIM)：
SELECT '(  book  )',LTRIM('  book  ');
--删除字符串两端指定的字符：
 SELECT TRIM('xy' FROM 'xyxboxyokxxyxy') ;
xboxyokx

--重复生成相同的字符串：
SELECT REPEAT('MySQL', 3);
  MySQLMySQLMySQL

--比较字符串大小：
SELECT
	STRCMP( 'txt', 'txt2' ),
	STRCMP( 'txt2', 'txt' ),
	STRCMP( 'txt', 'txt' ); -1	1	0
 
--字符串替代：
 SELECT REPLACE('xxx.mysql.com', 'x', 'w');

--截取
SELECT SUBSTRING('breakfast',5) AS col1, 
SUBSTRING('breakfast',5,3) AS col2,
SUBSTRING('lunch', -3) AS col3,
SUBSTRING('lunch', -5, 3) AS col4;

--查找字符串中指定子字符串的开始位置：
SELECT
	LOCATE( 'ball', 'football' ),
	POSITION( 'ball' IN 'football' ),
	INSTR ( 'football', 'ball' );
5	5	5

--反转字符串：
 SELECT REVERSE('abc');
cba

--返回指定位置字符串：
SELECT
	ELT( 3, '1st', '2nd', '3rd' ),
	ELT( 3, 'net', 'os' );
3rd	null

--返回指定字符串第一次出现的位置：
 SELECT
	FIELD( 'Hi', 'hihi', 'Hey', 'Hi', 'bas' ) AS col1,
	FIELD( 'Hi', 'Hey', 'Lo', 'Hilo', 'foo' ) AS col2;
3	0

--返回子字符串在字符串列表中的位置：
SELECT
	FIND_IN_SET( 'Hi', 'hihi,Hey,Hi,bas' );
3


--将字符串网络点地址转换为数值网络地址：
 SELECT INET_ATON('209.207.224.40');
 3520061480
--将数值网络地址转换为字符串网络点地址：
 SELECT INET_NTOA(3520061480);
209.207.224.40

--数据类型的转换，SQL语句如下：
 SELECT CAST(100 AS CHAR(2)), CONVERT('2010-10-01 12:12:12',TIME);
10	12:12:12

日期&时间

--获取系统当前日期：
 SELECT
	CURDATE( ),
	CURRENT_DATE ( ),
	CURDATE( ) + 0;
 2020-01-22	2020-01-22	20200122

--获取系统当前时间：
SELECT
	CURTIME( ),
	CURRENT_TIME ( ),
	CURTIME( ) + 0;
23:08:34	23:08:34	230834

--获取当前系统日期和时间：
SELECT CURRENT_TIMESTAMP(),
	LOCALTIME ( ),
	NOW( ),
	SYSDATE( );
2020-01-22 23:09:23	
2020-01-22 23:09:23	
2020-01-22 23:09:23	
2020-01-22 23:09:23

--UNIX格式的时间戳：
SELECT
	UNIX_TIMESTAMP( ),
	UNIX_TIMESTAMP( NOW( ) ),
	NOW( );
1579705939	1579705939	2020-01-22 23:12:19

--将UNIX时间戳转换为普通格式时间：
SELECT FROM_UNIXTIME('1364098609');
2013-03-24 12:16:49.000000

--返回当前UTC日期值：
 SELECT UTC_DATE(), UTC_DATE() + 0;
--返回当前UTC时间值：
 SELECT UTC_TIME(), UTC_TIME() + 0;

--返回指定日期中的月份：
 SELECT MONTH('2013-02-13');
--返回指定日期中的月份的名称：
 SELECT MONTHNAME('2013-02-13');

--返回指定日期的工作日名称：
 SELECT DAYNAME('2013-02-13');
--返回日期对应的周索引：
 SELECT DAYOFWEEK('2011-02-13');
--返回日期对应的工作日索引：
 SELECT WEEKDAY('2011-02-13 22:23:00'), WEEKDAY('2011-07-01');

--查询指定日期是一年中的第几周：
 SELECT WEEK('2011-02-20'),WEEK('2011-02-20',0), WEEK('2011-02-20',1);
--查询指定日期是一年中的第几周：
 SELECT WEEK('2011-02-20',3), WEEKOFYEAR('2011-02-20');
--返回指定日期在一年中的位置：
 SELECT DAYOFYEAR('2011-02-20');
--返回指定日期在一个月中的位置：
 SELECT DAYOFMONTH('2011-02-20');

--返回指定日期对应的年份：
  SELECT YEAR('11-02-03'),YEAR('96-02-03');
--返回指定日期对应的季度：
 SELECT QUARTER('11-04-01');

--返回指定时间的分钟值：
 SELECT MINUTE('11-02-03 10:10:03');
--返回指定时间的秒值：
 SELECT SECOND('10:05:03');

--提取日期或者时间值：
 SELECT EXTRACT(YEAR FROM '2011-07-02') AS col1,
     EXTRACT(YEAR_MONTH FROM '2011-07-12 01:02:03') AS col2,
     EXTRACT(DAY_MINUTE FROM '2011-07-12 01:02:03') AS col3;

--将时间值转换为秒值：
 SELECT TIME_TO_SEC('23:23:00');
--将秒值转换为时间格式：
 SELECT SEC_TO_TIME(2345),SEC_TO_TIME(2345)+0,
     TIME_TO_SEC('23:23:00'), SEC_TO_TIME(84180);

--日期加操作：
SELECT
	DATE_ADD( '2010-12-31 23:59:59', INTERVAL 1 SECOND ) AS col1,
	ADDDATE( '2010-12-31 23:59:59', INTERVAL 1 SECOND ) AS col2,
	DATE_ADD( '2010-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND ) AS col3;
2011-01-01 00:00:00	2011-01-01 00:00:00	2011-01-01 00:01:00	
--日期减操作：
 SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY) AS col1,
     SUBDATE('2011-01-02', INTERVAL 31 DAY) AS col2,
     DATE_SUB('2011-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND) AS col3;
     
--时间加操作：
SELECT
	ADDTIME( '2000-12-31 23:59:59', '1:1:1' ),
	ADDTIME( '02:02:02', '02:00:00' );
2001-01-01 01:01:00	04:02:02	
--时间减操作：
SELECT
	SUBTIME( '2000-12-31 23:59:59', '1:1:1' ),
	SUBTIME( '02:02:02', '02:00:00' );
2000-12-31 22:58:58	00:02:02

--计算两个日期之间的间隔天数：
SELECT
	DATEDIFF( '2010-12-31 23:59:59', '2010-12-30' ) AS col1,
	DATEDIFF( '2010-11-30 23:59:59', '2010-12-31' ) AS col2;
1	-31

--计算两个日期之间的差值：
SELECT YEAR
	( CURRENT_DATE ( ) ) - YEAR ( '2010-12-30' );
10
# 1579696487 = 2020-01-22 20:34:47
SELECT date( CURRENT_DATE ( ) ) - date((FROM_UNIXTIME(1579696487)));

--格式化输出日期和时间值：
 SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') AS col1,
     DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j') AS col2;
--格式化输入时间值：
 SELECT TIME_FORMAT('16:00:00', '%H %k %h %I %l');
16 16 04 04 4

--显示不同格式化类型下的格式字符串：
SELECT
	GET_FORMAT( DATE, 'EUR' ),
	GET_FORMAT( DATE, 'USA' );
%d.%m.%Y	%m.%d.%Y

--显示指定的日期值：
 SELECT
	DATE_FORMAT( '2000-10-05 22:23:00', GET_FORMAT( DATE, 'USA' ) );
10.05.2000

条件判断

--IF（如果第一个位置为true，则返回第二个，否则返回第三个）：
SELECT
IF ( 12, 2, 3 ),
IF ( 1 < 2, 'yes ', 'no' ),
IF ( STRCMP( 'test', 'test1' ), 'no', 'yes' );
2	yes 	no

--IFNULL（如果第一个为true则返回第一个，否则返回第二个）：
 SELECT
	IFNULL( 1, 2 ),
	IFNULL( NULL, 10 ),
	IFNULL( 1 / 0, 'wrong' );
1	10	wrong

--CASE value WHEN的分支操作：
SELECT
CASE 2 
		WHEN 1 THEN 'one' 
		WHEN 2 THEN	'two' 
		ELSE 'more' 
		END;
two	

系统

--当前MySQL版本号：
 SELECT VERSION();
 8.0.11
 5.5.62
 
--当前用户的连接ID：
  SELECT CONNECTION_ID();
  9
  
--前用户的连接信息：
 SHOW PROCESSLIST;
4	event_scheduler	localhost		Daemon	815	Waiting on empty queue	
9	root	localhost:49330	yikao	Query	0	starting	SHOW PROCESSLIST
10	root	localhost:49331	yikao	Sleep	14		
11	root	localhost:49528		Sleep	14		

--当前使用的数据库：
 SELECT DATABASE(),SCHEMA();
 
--当前登录用户名称：
SELECT USER(), CURRENT_USER(), SYSTEM_USER();
root@localhost	root@localhost	root@localhost
 
--字符串使用的字符集：
SELECT
	CHARSET( 'abc' ),
	CHARSET( CONVERT ( 'abc' USING latin1 ) ),
	CHARSET( VERSION( ) );
utf8mb4	latin1	utf8
	
--返回字符串排列方式：
 SELECT COLLATION('abc'),COLLATION(CONVERT('abc' USING utf8));
 utf8mb4_0900_ai_ci	utf8_general_ci

加密函数

--使用PASSWORD函数加密密码：
SELECT PASSWORD('newpwd');

--MD5函数加密字符串：
SELECT MD5 ('mypwd');

--ENCODE加密字符串：
SELECT ENCODE('secret','cry'), LENGTH(ENCODE('secret','cry'));

--DECODE函数解密被ENCODE加密的字符串：
SELECT DECODE(ENCODE('secret','cry'),'cry');