1、基本函数
ABS(X)
绝对值
SELECT ABS(-121),ABS(21) FROM DUAL;
+-----------+---------+
| ABS(-121) | ABS(21) |
+-----------+---------+
| 121 | 21 |
+-----------+---------+
CEIL(X) 返回大于或等于最小整数
FLOOR(X) 返回小于或等于最大整数
SELECT CEIL(3.3) FLOOR(3.3) FROM DUAL;
+-----------+------------+
| CEIL(3.3) | FLOOR(3.3) |
+-----------+------------+
| 4 | 3 |
+-----------+------------+
1 row in set (0.00 sec)
LEAST(x,x1,x2) 返回最小值
GREATEST(x,x1,x2) 返回最大值
SELECT LEAST(1,2,3) 'least',GREATEST(1,2,3) 'greatest' FROM DUAL;
+-------+----------+
| least | greatest |
+-------+----------+
| 1 | 3 |
+-------+----------+
1 row in set (0.00 sec)
MOD 取余数
SELECT MOD(10,3) FROM DUAL;
+-----------+
| MOD(10,3) |
+-----------+
| 1 |
+-----------+
TRUNCATE(X,Y) 返回数字截断y小数的结果
SELECT TRUNCATE(10.32,1), TRUNCATE(10.32,0) FROM DUAL;
+-------------------+-------------------+
| TRUNCATE(10.32,1) | TRUNCATE(10.32,0) |
+-------------------+-------------------+
| 10.3 | 10 |
+-------------------+-------------------+
2、字符串函数
2.1 ASCII(s) 返回字符串第一个字符的ASCII码值
SELECT ASCII('0A') FROM DUAL;
+-------------+
| ASCII('0A') |
+-------------+
| 48 |
+-------------+
0的ASCII码值是48
2.2 CHAR_LENGTH(字符个数) LENGTH(字符长度) 中文每个字符长度3
mysql> SELECT CHAR_LENGTH('HELLO'),CHAR_LENGTH('中国'),LENGTH('HELLO'),LENGTH('中国') FROM DUAL;
+----------------------+-----------------------+-----------------+------------------+
| CHAR_LENGTH('HELLO') | CHAR_LENGTH('中国') | LENGTH('HELLO') | LENGTH('中国') |
+----------------------+-----------------------+-----------------+------------------+
| 5 | 2 | 5 | 6 |
+----------------------+-----------------------+-----------------+------------------+
2.3 CONCAT(S1,S2,S3) 相当于JAVA s1+s2+s3 CONCAT_WS(x,s1,s2,s3) 相当于JAVA s1+x+s2+x+s3
SELECT CONCAT('he','llo'),CONCAT_WS(',','Hello','World') FROM DUAL;
+--------------------+--------------------------------+
| CONCAT('he','llo') | CONCAT_WS(',','Hello','World') |
+--------------------+--------------------------------+
| hello | Hello,World |
+--------------------+--------------------------------+
2.4 INSERT(STR,idx,len,replacestr) 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
字符串从1开始
SELECT INSERT('hello---',6,3,' world') FROM DUAL;
+---------------------------------+
| INSERT('hello---',6,3,' world') |
+---------------------------------+
| hello world |
+---------------------------------+
2.5 REPLACE(STR,A,B) 用B字符串替换STR 字符中出现A
SELECT REPLACE('oollo','oo','he') FROM DUAL;
+----------------------------+
| REPLACE('oollo','oo','he') |
+----------------------------+
| hello |
+----------------------------+
2.6 UPPER(大写字母) LOWER(小写字母)
SELECT UPPER('hello'),LOWER('WORLD') FROM DUAL;
+----------------+----------------+
| UPPER('hello') | LOWER('WORLD') |
+----------------+----------------+
| HELLO | world |
+----------------+----------------+
2.7 LEFT(STR,N)左边N个字符，RIGHT(STR,N)右边N个字符
SELECT LEFT('Hello---',5),RIGHT('---World',5) FROM DUAL;
+--------------------+---------------------+
| LEFT('Hello---',5) | RIGHT('---World',5) |
+--------------------+---------------------+
| Hello | World |
+--------------------+---------------------+
2.8 SUBSTR(S,INDEX,LEN) 返回从字符串s的index位置其len个字符
SELECT SUBSTR('Hello---',1,5) FROM DUAL;
+------------------------+
| SUBSTR('Hello---',1,5) |
+------------------------+
| Hello |
+------------------------+
2.9 REVERSE(S) 字符串反转
SELECT REVERSE('olleH') FROM DUAL;
+------------------+
| REVERSE('olleH') |
+------------------+
| Hello |
+------------------+
2.10 NULLIF(v1,v2) 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回 value1
SELECT NULLIF('A','A'),NULLIF('A',NULL),NULLIF(NULL,NULL),NULLIF(NULL,1) FROM DUAL;
+-----------------+------------------+-------------------+----------------+
| NULLIF('A','A') | NULLIF('A',NULL) | NULLIF(NULL,NULL) | NULLIF(NULL,1) |
+-----------------+------------------+-------------------+----------------+
| NULL | A | NULL | NULL |
+-----------------+------------------+-------------------+----------------+
3 日期和时间函数
3.1 CURDATE() 只包含日期
3.2 CURTIME() 只包含时间
3.3 NOW() / SYSDATE() 系统当前日期和时间
SELECT CURDATE(), CURTIME(),NOW(),SYSDATE() FROM DUAL;
+------------+-----------+---------------------+---------------------+
| CURDATE() | CURTIME() | NOW() | SYSDATE() |
+------------+-----------+---------------------+---------------------+
| 2023-10-21 | 17:24:17 | 2023-10-21 17:24:17 | 2023-10-21 17:24:17 |
+------------+-----------+---------------------+---------------------+
3.4 UNIX_TIMESTAMP(X) 以UNIX时间戳的形式返回当前时间 3.5 FROM_UNIXTIME(timestamp)
SELECT UNIX_TIMESTAMP(NOW()) 'now' ,\
-> UNIX_TIMESTAMP(CURDATE()) 'curdate', \
-> UNIX_TIMESTAMP('2023-10-21 17:24:17') 'cur_datetime', \
-> FROM_UNIXTIME(1697880257) FROM DUAL;
+------------+------------+--------------+---------------------------+
| now | curdate | cur_datetime | FROM_UNIXTIME(1697880257) |
+------------+------------+--------------+---------------------------+
| 1697880833 | 1697817600 | 1697880257 | 2023-10-21 17:24:17 |
+------------+------------+--------------+---------------------------+
3.6 DATE_ADD(datetime, INTERVAL expr type),返回与给定日期时间相差INTERVAL时间段的日期时间
SELECT DATE_ADD('2023-10-21 17:24:17',INTERVAL 1 SECOND ) AS add_one_second, \
-> DATE_ADD('2023-10-21 17:24:17',INTERVAL 1 MINUTE ) AS add_one_minute,\
-> DATE_ADD('2023-10-21 17:24:17',INTERVAL 1 HOUR) AS add_one_hour,\
-> DATE_ADD('2023-10-21 17:24:17',INTERVAL '1_1' HOUR_MINUTE) col4 FROM DUAL;
+---------------------+---------------------+---------------------+---------------------+
| add_one_second | add_one_minute | add_one_hour | col4 |
+---------------------+---------------------+---------------------+---------------------+
| 2023-10-21 17:24:18 | 2023-10-21 17:25:17 | 2023-10-21 18:24:17 | 2023-10-21 18:25:17 |
+---------------------+---------------------+---------------------+---------------------+
3.7
增加50秒
SELECT ADDTIME(NOW(), 50);
+---------------------+
| ADDTIME(NOW(), 50) |
+---------------------+
| 2023-10-21 17:52:19 |
+---------------------+
减去1小时1分1秒
SELECT NOW(), SUBTIME(NOW(), '1:1:1');
+---------------------+-------------------------+
| NOW() | SUBTIME(NOW(), '1:1:1') |
+---------------------+-------------------------+
| 2023-10-21 17:52:14 | 2023-10-21 16:51:13 |
+---------------------+-------------------------+
相差一天
SELECT DATEDIFF(CURDATE(),'2023-10-20') FROM DUAL;
+----------------------------------+
| DATEDIFF(CURDATE(),'2023-10-20') |
+----------------------------------+
| 1 |
+----------------------------------+
相差1小时8分12秒
SELECT TIMEDIFF(CURTIME(),'16:51:13') FROM DUAL;
+--------------------------------+
| TIMEDIFF(CURTIME(),'16:51:13') |
+--------------------------------+
| 01:08:12 |
+--------------------------------+
SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D') as col1 ,\
-> DATE_FORMAT(NOW(),'%Y-%m-%d') as col2 ,TIME_FORMAT(CURTIME(),'%h:%i:%S') as col3 FROM DUAL;
+-------------------+------------+----------+
| col1 | col2 | col3 |
+-------------------+------------+----------+
| 2023-October-21st | 2023-10-21 | 06:11:07 |
+-------------------+------------+----------+
