☆日期函数
日期符号对应关系
| 年 | 月 | 日 | 时 | 分 | 秒 |
| Y | m | d | H | m | s |
获取当前日期 now()
> select now(); +---------------------+ | now() | +---------------------+ | 2020-06-02 22:21:20 | +---------------------+ 1 row in set (0.03 sec)
获取当前时间戳 current_timestamp
> select current_timestamp, current_timestamp();
+---------------------+---------------------+
| current_timestamp | current_timestamp() |
+---------------------+---------------------+
| 2020-06-02 22:23:27 | 2020-06-02 22:23:27 |
+---------------------+---------------------+
格式转换: 时间->字符串 date_format
> select date_format(‘2020-10-18 22:23:01‘, ‘%Y-%m-%d %H%i%s‘); +-------------------------------------------------------+ | date_format(‘2020-10-18 22:23:01‘, ‘%Y-%m-%d %H%i%s‘) | +-------------------------------------------------------+ | 2020-10-18 222301 | +-------------------------------------------------------+
格式转换: 字符串->时间 str_to_date
> select str_to_date(‘08/09/2008‘, ‘%m/%d/%Y‘); +---------------------------------------+ | str_to_date(‘08/09/2008‘, ‘%m/%d/%Y‘) | +---------------------------------------+ | 2008-08-09 | +---------------------------------------+ 1 row in set (0.03 sec)
星期几DAYOFWEEK
周日1，周一2...周六7
> select now(), dayofweek(now());
+---------------------+------------------+
| now() | dayofweek(now()) |
+---------------------+------------------+
| 2020-06-03 21:48:03 | 4 |
获取年月日时分秒周
> select now(), dayofweek(now()), year(now()), month(now()), day(now()), hour(now()), minute(now()), second(now()),week(now());
+---------------------+------------------+-------------+--------------+------------+-------------+---------------+---------------+-------------+
| now() | dayofweek(now()) | year(now()) | month(now()) | day(now()) | hour(now()) | minute(now()) | second(now()) | week(now()) |
+---------------------+------------------+-------------+--------------+------------+-------------+---------------+---------------+-------------+
| 2020-06-03 21:50:13 | 4 | 2020 | 6 | 3 | 21 | 50 | 13 | 22 |
☆字符串处理
字符串拼接 concat
将多个字符串连接成一个字符串
> select concat(‘2020‘, ‘/‘, ‘12‘, ‘/‘, ‘20‘); +--------------------------------------+ | concat(‘2020‘, ‘/‘, ‘12‘, ‘/‘, ‘20‘) | +--------------------------------------+ | 2020/12/20 | +--------------------------------------+
以第一个字符为拼接符：concat_ws
> select concat_ws(‘/‘, ‘2020‘, ‘12‘, ‘20‘); +------------------------------------+ | concat_ws(‘/‘, ‘2020‘, ‘12‘, ‘20‘) | +------------------------------------+ | 2020/12/20 | +------------------------------------+
字符串截取：left, right, substring
> select left(‘123456‘, 3), right(‘123456‘, 3), left(‘123‘, 100); +-------------------+--------------------+------------------+ | left(‘123456‘, 3) | right(‘123456‘, 3) | left(‘123‘, 100) | +-------------------+--------------------+------------------+ | 123 | 456 | 123 | +-------------------+--------------------+------------------+
substring
用法：substring(字符串, 位置, 长度)
> select substring(‘123456‘, 3); # 从第3个开始截取 +------------------------+ | substring(‘123456‘, 3) | +------------------------+ | 3456 | +------------------------+ 1 row in set (0.03 sec) > select substring(‘123456‘, 3, 2); #从第三个开始，只截2个 +---------------------------+ | substring(‘123456‘, 3, 2) | +---------------------------+ | 34 | +---------------------------+ 1 row in set (0.03 sec) > select substring(‘123456‘, -3); #从倒数第3个开始 +-------------------------+ | substring(‘123456‘, -3) | +-------------------------+ | 456 | +-------------------------+ 1 row in set (0.03 sec) > select substring(‘123456‘, -3, 2); # 从倒数第3个开始，只截2个 +----------------------------+ | substring(‘123456‘, -3, 2) | +----------------------------+ | 45 | +----------------------------+ 1 row in set (0.03 sec) > select substring(‘123456‘, -3, -2); # 第三个参数是长度，为正有意义 +-----------------------------+ | substring(‘123456‘, -3, -2) | +-----------------------------+ | | +-----------------------------+
按关键字截取 substring_index
用法substring_index(str, delim, count)，即：substring_index(被截取字符串，关键字，关键字出现的次数)
> select substring_index(‘123aaa3bb3dd‘, ‘3‘, 1); +-----------------------------------------+ | substring_index(‘123aaa3bb3dd‘, ‘3‘, 1) | +-----------------------------------------+ | 12 | +-----------------------------------------+ > select substring_index(‘123aaa3bb3dd‘, ‘3‘, 2); +-----------------------------------------+ | substring_index(‘123aaa3bb3dd‘, ‘3‘, 2) | +-----------------------------------------+ | 123aaa | +-----------------------------------------+
最左边字符的ascii码
>select ASCII(2), ASCII(‘2‘), ASCII(‘22‘); +----------+------------+-------------+ | ASCII(2) | ASCII(‘2‘) | ASCII(‘22‘) | +----------+------------+-------------+ | 50 | 50 | 50 |
字符串长度 LENGHT
>select length(‘abc‘), length(333333); +---------------+----------------+ | length(‘abc‘) | length(333333) | +---------------+----------------+ | 3 | 6 |
一字符串在另一个字符串中的位置 locate(substr, str), instr(str, substr)
返回substr在str出现的第一个位置，如果在则>0；否则为0
> select locate(‘abc‘, ‘mmabcmm‘), locate(‘abc‘, ‘aaaaaa‘); +--------------------------+-------------------------+ | locate(‘abc‘, ‘mmabcmm‘) | locate(‘abc‘, ‘aaaaaa‘) | +--------------------------+-------------------------+ | 3 | 0 | +--------------------------+-------------------------+ > select instr(‘abc‘, ‘a‘), locate(‘abc‘, ‘aa‘); +-------------------+---------------------+ | instr(‘abc‘, ‘a‘) | locate(‘abc‘, ‘aa‘) | +-------------------+---------------------+ | 1 | 0 | +-------------------+---------------------+
替换REPLACE
> select replace(‘www.baidu.com.ww‘, ‘ww‘, ‘&&‘); +-----------------------------------------+ | replace(‘www.baidu.com.ww‘, ‘ww‘, ‘&&‘) | +-----------------------------------------+ | &&w.baidu.com.&& |
指定位置字符替换
insert(con, pos, len, anotherstr), 把字符con, 从第pos(从1开始计数)的len个字符由anotherstr替换
> select insert(‘123456‘, 3, 3, ‘aaaaa‘); +---------------------------------+ | insert(‘123456‘, 3, 3, ‘aaaaa‘) | +---------------------------------+ | 12aaaaa6 |
重复REPEAT
> select repeat(‘abc‘, 3); +------------------+ | repeat(‘abc‘, 3) | +------------------+ | abcabcabc |
翻转REVERSE
> select reverse(‘abc‘); +----------------+ | reverse(‘abc‘) | +----------------+ | cba |
☆数学函数
绝对值ABS, 取余MOD
> select abs(-3.2), mod(3, 4);
+-----------+-----------+
| abs(-3.2) | mod(3, 4) |
+-----------+-----------+
| 3.2 | 3 |
+-----------+-----------+
四舍五入 ROUND
select round(1.49), round(1.50);
+-------------+-------------+
| round(1.49) | round(1.50) |
+-------------+-------------+
| 1 | 2 |
+-------------+-------------+
不大于x的最大整数FLOOR，不小于x的最小整数CEILING
> select FLOOR(-1.23), FLOOR(1.23), CEILING(-1.23), CEILING(1.23);
+--------------+-------------+----------------+---------------+
| FLOOR(-1.23) | FLOOR(1.23) | CEILING(-1.23) | CEILING(1.23) |
+--------------+-------------+----------------+---------------+
| -2 | 1 | -1 | 2 |
☆控制流程函数
条件控制 case when then
> select case when 1 < 0 then ‘a‘ when 2 > 1 then ‘b‘ else ‘c‘ end; +-----------------------------------------------------------+ | case when 1 < 0 then ‘a‘ when 2 > 1 then ‘b‘ else ‘c‘ end | +-----------------------------------------------------------+ | b |
☆加密函数
password 一般对用户密码加密
> select password(‘a‘); +-------------------------------------------+ | password(‘a‘) | +-------------------------------------------+ | *667F407DE7C6AD07358FA38DAED7828A72014B4E |
md5一般对普通数据加密
> select md5(‘abc‘); +----------------------------------+ | md5(‘abc‘) | +----------------------------------+ | 900150983cd24fb0d6963f7d28e17f72 |
加密ENCODE, 解密DECODE
encode(str, pwd_str), decode(str, pwd_str), 使用pwd_str对str进行加密、解密
select encode(‘abc‘, ‘123‘), decode(encode(‘abc‘, ‘123‘), ‘123‘); +----------------------+-------------------------------------+ | encode(‘abc‘, ‘123‘) | decode(encode(‘abc‘, ‘123‘), ‘123‘) | +----------------------+-------------------------------------+ | ??? | abc |