常用函数
字符串函数：
- 常用的字符串函数
- CONCAT(s1, s2, ...):依次连接字符串。
mysql> SELECT CONCAT('aa', 'bb'), CONCAT('abc', 'de', 'fgh');
+--------------------+----------------------------+
| CONCAT('aa', 'bb') | CONCAT('abc', 'de', 'fgh') |
+--------------------+----------------------------+
| aabb | abcdefgh |
+--------------------+----------------------------+- INSERT(str, x, y, instr):将str从x位置开始的y个字符串替换为instr，索引从1开始。
mysql> SELECT INSERT('ilike2014', 2, 4, 'enjoy');
+------------------------------------+
| INSERT('ilike2014', 2, 4, 'enjoy') |
+------------------------------------+
| ienjoy2014 |
+------------------------------------+- LPAD(str, n, pad):str长度不足n，则左填充pad。
mysql> SELECT LPAD('XXX', 12, '01');
+-----------------------+
| LPAD('XXX', 12, '01') |
+-----------------------+
| 010101010XXX |
+-----------------------+- SUBSTRING(str, x, y): 求子串str[x..y]，索引从1开始。
mysql> SELECT SUBSTRING('abcdefg', 1, 3);
+----------------------------+
| SUBSTRING('abcdefg', 1, 3) |
+----------------------------+
| abc |
+----------------------------+数值函数：
- MySQL常用的数值函数：
范例：
mysql> SELECT ABS(0.8), ABS(-0.8), CEIL(-0.8), CEIL(0.8), FLOOR(-0.8), FLOOR(0.8), MOD(15,10), MOD(1,11), MOD(NULL,10);
+----------+-----------+------------+-----------+-------------+------------+------------+-----------+--------------+
| ABS(0.8) | ABS(-0.8) | CEIL(-0.8) | CEIL(0.8) | FLOOR(-0.8) | FLOOR(0.8) | MOD(15,10) | MOD(1,11) | MOD(NULL,10) |
+----------+-----------+------------+-----------+-------------+------------+------------+-----------+--------------+
| 0.8 | 0.8 | 0 | 1 | -1 | 0 | 5 | 1 | NULL |
+----------+-----------+------------+-----------+-------------+------------+------------+-----------+--------------+
mysql> SELECT ROUND(1.235, 2), TRUNCATE(1.235, 2);
+-----------------+--------------------+
| ROUND(1.235, 2) | TRUNCATE(1.235, 2) |
+-----------------+--------------------+
| 1.24 | 1.23 |
+-----------------+--------------------+日期和时间函数：
- 常用的日期时间函数：
范例：
-- 列举当前时间
mysql> SELECT CURDATE(), CURTIME(), NOW();
+------------+-----------+---------------------+
| CURDATE() | CURTIME() | NOW() |
+------------+-----------+---------------------+
| 2014-06-12 | 11:54:22 | 2014-06-12 11:54:22 |
+------------+-----------+---------------------+
-- 返回Unix时间戳
mysql> SELECT UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
| 1402545326 |
+-----------------------+
-- 返回Unix时间戳对应的日期
mysql> SELECT UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
| 1402545326 |
+-----------------------+
-- 现在是哪周，哪年
mysql> SELECT WEEK(now()), YEAR(now());
+-------------+-------------+
| WEEK(now()) | YEAR(now()) |
+-------------+-------------+
| 23 | 2014 |
+-------------+-------------+- MySQL中的日期时间格式化：
如：
mysql> SELECT DATE_FORMAT(now(), '%Y-%m-%d %H:%m:%s');
+-----------------------------------------+
| DATE_FORMAT(now(), '%Y-%m-%d %H:%m:%s') |
+-----------------------------------------+
| 2014-06-12 12:06:13 |
+-----------------------------------------+- 有关时间计算
MySQL中的日期时间间隔类型有：
范例：
mysql> SELECT now() current, DATE_ADD(now(), INTERVAL 31 DAY) after31days, DATE_ADD(now(), INTERVAL -7 DAY) before7days, DATE_ADD(now(), INTERVAL '1_2' YEAR_MONTH) after_1year_2month;
+---------------------+---------------------+---------------------+---------------------+
| current | after31days | before7days | after_1year_2month |
+---------------------+---------------------+---------------------+---------------------+
| 2014-06-12 22:48:49 | 2014-07-13 22:48:49 | 2014-06-05 22:48:49 | 2015-08-12 22:48:49 |
+---------------------+---------------------+---------------------+---------------------+流程函数：
- MySQL中的流程函数：
范例：
-- 初始化数据
mysql> CREATE TABLE salary (userid int, salary decimal(9, 2));
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO salary VALUES(1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (1, NULL);
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM salary;
+--------+---------+
| userid | salary |
+--------+---------+
| 1 | 1000.00 |
| 2 | 2000.00 |
| 3 | 3000.00 |
| 4 | 4000.00 |
| 5 | 5000.00 |
| 1 | NULL |
+--------+---------+
-- 根据薪水高低判断薪水
mysql> SELECT IF(salary>2000, 'high', 'low') FROM salary;
+--------------------------------+
| IF(salary>2000, 'high', 'low') |
+--------------------------------+
| low |
| low |
| high |
| high |
| high |
| low |
+--------------------------------+
-- IFNULL使用
mysql> SELECT IFNULL(salary, 0) FROM salary;
+-------------------+
| IFNULL(salary, 0) |
+-------------------+
| 1000.00 |
| 2000.00 |
| 3000.00 |
| 4000.00 |
| 5000.00 |
| 0.00 |
+-------------------+
-- CASE WHEN使用
mysql> SELECT CASE salary WHEN 1000 THEN 'low' WHEN 2000 THEN 'mid' ELSE 'high' end FROM salary;
+-----------------------------------------------------------------------+
| CASE salary WHEN 1000 THEN 'low' WHEN 2000 THEN 'mid' ELSE 'high' end |
+-----------------------------------------------------------------------+
| low |
| mid |
| high |
| high |
| high |
| high |
+-----------------------------------------------------------------------+其他常用函数：
- MySQL中其他常用函数：
范例：
mysql> SELECT DATABASE(), VERSION(), USER();
+------------+-----------+----------------+
| DATABASE() | VERSION() | USER() |
+------------+-----------+----------------+
| test | 5.6.14 | root@localhost |
+------------+-----------+----------------+
mysql> SELECT DATABASE(), VERSION(), USER();
+------------+-----------+----------------+
| DATABASE() | VERSION() | USER() |
+------------+-----------+----------------+
| test | 5.6.14 | root@localhost |
+------------+-----------+----------------+
mysql> SELECT INET_ATON('192.168.141.129');
+------------------------------+
| INET_ATON('192.168.141.129') |
+------------------------------+
| 3232271745 |
+------------------------------+
-- 41位加密密码
mysql> SELECT PASSWORD('111111');
+-------------------------------------------+
| PASSWORD('111111') |
+-------------------------------------------+
| *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+-------------------------------------------+
-- MD5值计算
mysql> SELECT MD5('111111');
+----------------------------------+
| MD5('111111') |
+----------------------------------+
| 96e79218965eb72c92a549dd5a330112 |
+----------------------------------+http://dev.mysql.com/doc/refman/5.7/en/functions.html
不吝指正。
