【實驗】【MySQL】MySQL常用函式“自助式”示例演示全過程
結合MySQL自帶的幫助文件列一下MySQL資料庫中常用的一些函式。
事實證明：MySQL的聯機幫助資料非常實用，希望哪一天可愛的Oracle可以像MySQL學習一下，她可以讓您基本不用檢視其他的資料就將函式的基本使用方法和示例盡收眼底。
廢話少說，直入主題
一、常用字串函式
1.CONCAT(str1,str2,...)
mysql> ? concat;
mysql> SELECT CONCAT('My', 'S', 'QL');
+-------------------------+
| CONCAT('My', 'S', 'QL') |
+-------------------------+
| MySQL |
+-------------------------+
2.INSERT(str,pos,len,newstr)
mysql> ? insert function;
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
+-----------------------------------+
| INSERT('Quadratic', 3, 4, 'What') |
+-----------------------------------+
| QuWhattic |
+-----------------------------------+
3.LOWER(str)
mysql> ? lower
mysql> SELECT LOWER('QUADRATICALLY');
+------------------------+
| LOWER('QUADRATICALLY') |
+------------------------+
| quadratically |
+------------------------+
4.UPPER(str)
mysql> ? upper
mysql> SELECT UPPER('Hej');
+--------------+
| UPPER('Hej') |
+--------------+
| HEJ |
+--------------+
5.LEFT(str,len)
mysql> ? left
mysql> SELECT LEFT('foobarbar', 5);
+----------------------+
| LEFT('foobarbar', 5) |
+----------------------+
| fooba |
+----------------------+
6.RIGHT(str,len)
mysql> ? right
mysql> SELECT RIGHT('foobarbar', 4);
+-----------------------+
| RIGHT('foobarbar', 4) |
+-----------------------+
| rbar |
+-----------------------+
7.LPAD(str,len,padstr)
mysql> ? lpad
mysql> SELECT LPAD('hi',4,'??');
+-------------------+
| LPAD('hi',4,'??') |
+-------------------+
| ??hi |
+-------------------+
8.RPAD(str,len,padstr)
mysql> ? rpad
mysql> SELECT RPAD('hi',5,'?');
+------------------+
| RPAD('hi',5,'?') |
+------------------+
| hi??? |
+------------------+
9.LTRIM(str)
mysql> ? ltrim
mysql> SELECT LTRIM(' barbar');
+-------------------+
| LTRIM(' barbar') |
+-------------------+
| barbar |
+-------------------+
10.RTRIM(str)
mysql> ? rtrim
mysql> SELECT RTRIM('barbar ');
+--------------------+
| RTRIM('barbar ') |
+--------------------+
| barbar |
+--------------------+
11.TRIM(str)
mysql> ? trim
mysql> SELECT TRIM(' bar ');
+------------------+
| TRIM(' bar ') |
+------------------+
| bar |
+------------------+
12.REPEAT(str,count)
mysql> ? repeat function;
mysql> SELECT REPEAT('MySQL', 3);
+--------------------+
| REPEAT('MySQL', 3) |
+--------------------+
| MySQLMySQLMySQL |
+--------------------+
13.REPLACE(str,from_str,to_str)
mysql> ? replace
mysql> SELECT REPLACE('', 'w', 'Ww');
+-------------------------------------+
| REPLACE('', 'w', 'Ww') |
+-------------------------------------+
| WwW |
+-------------------------------------+
14.STRCMP(expr1,expr2)
mysql> ? strcmp
mysql> SELECT STRCMP('text', 'text2'),STRCMP('text2', 'text'),STRCMP('text', 'text');
+-----------------------+-----------------------+----------------------+
|STRCMP('text', 'text2')|STRCMP('text2', 'text')|STRCMP('text', 'text')|
+-----------------------+-----------------------+----------------------+
| -1 | 1| 0|
+-----------------------+-----------------------+----------------------+
15.SUBSTRING
mysql> ? substring
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len),
SUBSTRING(str FROM pos FOR len)
mysql> SELECT SUBSTRING('Secooler',3,4), SUBSTRING('Secooler',6);
+---------------------------+-------------------------+
| SUBSTRING('Secooler',3,4) | SUBSTRING('Secooler',6) |
+---------------------------+-------------------------+
| cool | ler |
+---------------------------+-------------------------+
二、數值函式
1.ABS(X) 取絕對值函式
mysql> ? abs
mysql> SELECT ABS(-32);
+----------+
| ABS(-32) |
+----------+
| 32 |
+----------+
2.CEILING(X), CEIL(X) 取天棚函式
mysql> ? ceil
mysql> SELECT CEILING(1.23), CEIL(-1.23);
+---------------+-------------+
| CEILING(1.23) | CEIL(-1.23) |
+---------------+-------------+
| 2 | -1 |
+---------------+-------------+
3.FLOOR(X) 取地板函式
mysql> ? floor
mysql> SELECT FLOOR(1.23),FLOOR(-1.23);
+-------------+--------------+
| FLOOR(1.23) | FLOOR(-1.23) |
+-------------+--------------+
| 1 | -2 |
+-------------+--------------+
4.MOD(N,M), N % M, N MOD M 取模函式
mysql> ? mod
mysql> SELECT MOD(234, 10), 253 % 7, MOD(29,9), 29 MOD 9;
+--------------+---------+-----------+----------+
| MOD(234, 10) | 253 % 7 | MOD(29,9) | 29 MOD 9 |
+--------------+---------+-----------+----------+
| 4 | 1 | 2 | 2 |
+--------------+---------+-----------+----------+
5.RAND(), RAND(N) 取0-1之間的隨機數函式
mysql> ? rand
mysql> SELECT RAND(), RAND();
+------------------+-----------------+
| RAND() | RAND() |
+------------------+-----------------+
| 0.77874226009356 | 0.5317868818825 |
+------------------+-----------------+
6.TRUNCATE(X,D) 返回數字X被截斷為D位小數的結果
mysql> ? truncate
mysql> SELECT TRUNCATE(1.223,1), TRUNCATE(1.999,1), TRUNCATE(-1.999,2);
+-------------------+-------------------+--------------------+
| TRUNCATE(1.223,1) | TRUNCATE(1.999,1) | TRUNCATE(-1.999,2) |
+-------------------+-------------------+--------------------+
| 1.2 | 1.9 | -1.99 |
+-------------------+-------------------+--------------------+
三、日期和時間函式
1.CURDATE() 當前日期函式
mysql> ? curdate
mysql> SELECT CURDATE(),CURDATE() + 0;
+------------+---------------+
| CURDATE() | CURDATE() + 0 |
+------------+---------------+
| 2009-07-03 | 20090703 |
+------------+---------------+
2.CURTIME() 當前時間函式
mysql> ? curtime
mysql> SELECT CURTIME(), CURTIME() + 0;
+-----------+---------------+
| CURTIME() | CURTIME() + 0 |
+-----------+---------------+
| 12:07:08 | 120708 |
+-----------+---------------+
3.NOW() 當前日期和時間函式
mysql> ? now
mysql> SELECT NOW(), NOW() + 0;
+---------------------+----------------+
| NOW() | NOW() + 0 |
+---------------------+----------------+
| 2009-07-03 12:07:54 | 20090703120754 |
+---------------------+----------------+
4.UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date) 日期date的時間戳
mysql> ? unix_timestamp
mysql> SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP('1981-02-15 23:23:00');
+------------------+---------------------------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP('1981-02-15 23:23:00') |
+------------------+---------------------------------------+
| 1246594366 | 351098580 |
+------------------+---------------------------------------+
5.FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format) 返回時間戳的日期值（UNIX_TIMESTAMP的反函式）
mysql> ? from_unixtime
mysql> SELECT FROM_UNIXTIME(1246594135),FROM_UNIXTIME(351098580);
+---------------------------+--------------------------+
| FROM_UNIXTIME(1246594135) | FROM_UNIXTIME(351098580) |
+---------------------------+--------------------------+
| 2009-07-03 12:08:55 | 1981-02-15 23:23:00 |
+---------------------------+--------------------------+
6.WEEK(date[,mode]) 返回所給日期是一年中的第幾周
mysql> ? week
mysql> SELECT WEEK('1981-02-15');
+--------------------+
| WEEK('1981-02-15') |
+--------------------+
| 7 |
+--------------------+
7.YEAR(date)
mysql> ? year
mysql> SELECT YEAR('81-02-15');
+------------------+
| YEAR('81-02-15') |
+------------------+
| 1981 |
+------------------+
8.HOUR(time) 返回時間的小時資訊
mysql> ? hour
mysql> SELECT HOUR('10:05:03');
+------------------+
| HOUR('10:05:03') |
+------------------+
| 10 |
+------------------+
9.MINUTE(time) 返回時間的分鐘資訊
mysql> SELECT MINUTE('98-02-03 10:05:03');
+-----------------------------+
| MINUTE('98-02-03 10:05:03') |
+-----------------------------+
| 5 |
+-----------------------------+
10.MONTHNAME(date) 返回時間的完整月份名字
mysql> SELECT MONTHNAME('1981-02-15');
+-------------------------+
| MONTHNAME('1981-02-15') |
+-------------------------+
| February |
+-------------------------+
11.DATE_FORMAT(date,format) 根據format格式date顯示形式
mysql> ? date_format
mysql> SELECT DATE_FORMAT('1981-02-15 23:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('1981-02-15 23:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday February 1981 |
+------------------------------------------------+
12.DATE_ADD(date,INTERVAL expr type) 返回與所給日期date相差INTERVAL的時間段
mysql> select now() current, date_add(now(), INTERVAL 31 day) after31days;
+---------------------+---------------------+
| current | after31days |
+---------------------+---------------------+
| 2009-07-03 12:34:15 | 2009-08-03 12:34:15 |
+---------------------+---------------------+
13.DATEDIFF(expr,expr2) 計算兩個日期之間相差的天數
mysql> SELECT DATEDIFF(now(),'1981-02-15 23:23:00');
+---------------------------------------+
| DATEDIFF(now(),'1981-02-15 23:23:00') |
+---------------------------------------+
| 10365 |
+---------------------------------------+
四、MySQL控制流程函式
首先建立演示表salary
mysql> use test;
mysql> create table salary (userid int, salary decimal(9,2));
mysql> insert into salary values (1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
mysql> select * from salary;
+--------+---------+
| userid | salary |
+--------+---------+
| 1 | 1000.00 |
| 2 | 2000.00 |
| 3 | 3000.00 |
| 4 | 4000.00 |
| 5 | 5000.00 |
| 1 | NULL |
+--------+---------+
1.IF(expr1,expr2,expr3)函式：如果expr1為真則返回expr2，否則返回expr3
mysql> ? IF FUNCTION
mysql> select IF( salary > 2000, 'High', 'Low') from salary;
+------------------------------------+
| if ( salary > 2000, 'High', 'Low') |
+------------------------------------+
| Low |
| Low |
| High |
| High |
| High |
| Low |
+------------------------------------+
2.IFNULL(expr1,expr2)函式：若expr1為NULL則返回expr2內容
mysql> ? IFNULL
mysql> select ifnull(salary,0) from salary;
+------------------+
| ifnull(salary,0) |
+------------------+
| 1000.00 |
| 2000.00 |
| 3000.00 |
| 4000.00 |
| 5000.00 |
| 0.00 |
+------------------+
3.NULLIF(expr1,expr2)函式：若expr1 = expr2則返回NULL，否則返回expr1
mysql> ? NULLIF
mysql> select nullif(salary,2000) from salary;
+---------------------+
| nullif(salary,2000) |
+---------------------+
| 1000.00 |
| NULL |
| 3000.00 |
| 4000.00 |
| 5000.00 |
| NULL |
+---------------------+
4.CASE函式
語法如下：
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
或
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
mysql> ? CASE FUNCTION
mysql> select case when salary <= 2000 then 'Low' else 'High' end from salary;
+-----------------------------------------------------+
| case when salary <= 2000 then 'Low' else 'High' end |
+-----------------------------------------------------+
| Low |
| Low |
| High |
| High |
| High |
| High |
+-----------------------------------------------------+
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 |
+-----------------------------------------------------------------------+
五、其他常用函式
1.DATABASE()函式：用於查詢當前使用資料庫的名字（類似Oracle的show user;）
mysql> ? database
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
2.VERSION()函式：使用者查詢所使用資料庫的版本
mysql> ? version
mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.0.22-log |
+------------+
3.USER()函式：查詢當前登陸使用者名稱
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
4.INET_ATON(expr)函式：查詢IP地址的網路位元組序表示，反函式是INET_NTOA
mysql> SELECT INET_ATON('144.194.192.183');
+------------------------------+
| INET_ATON('144.194.192.183') |
+------------------------------+
| 2428682423 |
+------------------------------+
5.INET_NTOA(expr)函式：查詢網路位元組序代表的IP地址，是INET_ATON的反函式
mysql> ? inet_ntoa
mysql> SELECT INET_NTOA(2428682423);
+-----------------------+
| INET_NTOA(2428682423) |
+-----------------------+
| 144.194.192.183 |
+-----------------------+
六、小結
有事沒事多請教一下幫助is a good idea. 也許這就是開源的好處，她會盡可能的考慮到您查詢和參考的便利性。MySQL的函式還是很豐富的，以上實驗用到的函式都是非常常用的。
這個小文兒，可以“一看了之”，只要你能想到MySQL提供的這些功能函式，check一下幫助系統，就什麼都得到啦。
如果這些函式仍然沒有滿足您的求知慾望，敬請參考官方文件《第12章：函式和運算子》，地址為：http://dev.mysql.com/doc/refman/5.1/zh/functions.html
好運！
-- The End --
事實證明：MySQL的聯機幫助資料非常實用，希望哪一天可愛的Oracle可以像MySQL學習一下，她可以讓您基本不用檢視其他的資料就將函式的基本使用方法和示例盡收眼底。
廢話少說，直入主題
一、常用字串函式
1.CONCAT(str1,str2,...)
mysql> ? concat;
mysql> SELECT CONCAT('My', 'S', 'QL');
+-------------------------+
| CONCAT('My', 'S', 'QL') |
+-------------------------+
| MySQL |
+-------------------------+
2.INSERT(str,pos,len,newstr)
mysql> ? insert function;
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
+-----------------------------------+
| INSERT('Quadratic', 3, 4, 'What') |
+-----------------------------------+
| QuWhattic |
+-----------------------------------+
3.LOWER(str)
mysql> ? lower
mysql> SELECT LOWER('QUADRATICALLY');
+------------------------+
| LOWER('QUADRATICALLY') |
+------------------------+
| quadratically |
+------------------------+
4.UPPER(str)
mysql> ? upper
mysql> SELECT UPPER('Hej');
+--------------+
| UPPER('Hej') |
+--------------+
| HEJ |
+--------------+
5.LEFT(str,len)
mysql> ? left
mysql> SELECT LEFT('foobarbar', 5);
+----------------------+
| LEFT('foobarbar', 5) |
+----------------------+
| fooba |
+----------------------+
6.RIGHT(str,len)
mysql> ? right
mysql> SELECT RIGHT('foobarbar', 4);
+-----------------------+
| RIGHT('foobarbar', 4) |
+-----------------------+
| rbar |
+-----------------------+
7.LPAD(str,len,padstr)
mysql> ? lpad
mysql> SELECT LPAD('hi',4,'??');
+-------------------+
| LPAD('hi',4,'??') |
+-------------------+
| ??hi |
+-------------------+
8.RPAD(str,len,padstr)
mysql> ? rpad
mysql> SELECT RPAD('hi',5,'?');
+------------------+
| RPAD('hi',5,'?') |
+------------------+
| hi??? |
+------------------+
9.LTRIM(str)
mysql> ? ltrim
mysql> SELECT LTRIM(' barbar');
+-------------------+
| LTRIM(' barbar') |
+-------------------+
| barbar |
+-------------------+
10.RTRIM(str)
mysql> ? rtrim
mysql> SELECT RTRIM('barbar ');
+--------------------+
| RTRIM('barbar ') |
+--------------------+
| barbar |
+--------------------+
11.TRIM(str)
mysql> ? trim
mysql> SELECT TRIM(' bar ');
+------------------+
| TRIM(' bar ') |
+------------------+
| bar |
+------------------+
12.REPEAT(str,count)
mysql> ? repeat function;
mysql> SELECT REPEAT('MySQL', 3);
+--------------------+
| REPEAT('MySQL', 3) |
+--------------------+
| MySQLMySQLMySQL |
+--------------------+
13.REPLACE(str,from_str,to_str)
mysql> ? replace
mysql> SELECT REPLACE('', 'w', 'Ww');
+-------------------------------------+
| REPLACE('', 'w', 'Ww') |
+-------------------------------------+
| WwW |
+-------------------------------------+
14.STRCMP(expr1,expr2)
mysql> ? strcmp
mysql> SELECT STRCMP('text', 'text2'),STRCMP('text2', 'text'),STRCMP('text', 'text');
+-----------------------+-----------------------+----------------------+
|STRCMP('text', 'text2')|STRCMP('text2', 'text')|STRCMP('text', 'text')|
+-----------------------+-----------------------+----------------------+
| -1 | 1| 0|
+-----------------------+-----------------------+----------------------+
15.SUBSTRING
mysql> ? substring
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len),
SUBSTRING(str FROM pos FOR len)
mysql> SELECT SUBSTRING('Secooler',3,4), SUBSTRING('Secooler',6);
+---------------------------+-------------------------+
| SUBSTRING('Secooler',3,4) | SUBSTRING('Secooler',6) |
+---------------------------+-------------------------+
| cool | ler |
+---------------------------+-------------------------+
二、數值函式
1.ABS(X) 取絕對值函式
mysql> ? abs
mysql> SELECT ABS(-32);
+----------+
| ABS(-32) |
+----------+
| 32 |
+----------+
2.CEILING(X), CEIL(X) 取天棚函式
mysql> ? ceil
mysql> SELECT CEILING(1.23), CEIL(-1.23);
+---------------+-------------+
| CEILING(1.23) | CEIL(-1.23) |
+---------------+-------------+
| 2 | -1 |
+---------------+-------------+
3.FLOOR(X) 取地板函式
mysql> ? floor
mysql> SELECT FLOOR(1.23),FLOOR(-1.23);
+-------------+--------------+
| FLOOR(1.23) | FLOOR(-1.23) |
+-------------+--------------+
| 1 | -2 |
+-------------+--------------+
4.MOD(N,M), N % M, N MOD M 取模函式
mysql> ? mod
mysql> SELECT MOD(234, 10), 253 % 7, MOD(29,9), 29 MOD 9;
+--------------+---------+-----------+----------+
| MOD(234, 10) | 253 % 7 | MOD(29,9) | 29 MOD 9 |
+--------------+---------+-----------+----------+
| 4 | 1 | 2 | 2 |
+--------------+---------+-----------+----------+
5.RAND(), RAND(N) 取0-1之間的隨機數函式
mysql> ? rand
mysql> SELECT RAND(), RAND();
+------------------+-----------------+
| RAND() | RAND() |
+------------------+-----------------+
| 0.77874226009356 | 0.5317868818825 |
+------------------+-----------------+
6.TRUNCATE(X,D) 返回數字X被截斷為D位小數的結果
mysql> ? truncate
mysql> SELECT TRUNCATE(1.223,1), TRUNCATE(1.999,1), TRUNCATE(-1.999,2);
+-------------------+-------------------+--------------------+
| TRUNCATE(1.223,1) | TRUNCATE(1.999,1) | TRUNCATE(-1.999,2) |
+-------------------+-------------------+--------------------+
| 1.2 | 1.9 | -1.99 |
+-------------------+-------------------+--------------------+
三、日期和時間函式
1.CURDATE() 當前日期函式
mysql> ? curdate
mysql> SELECT CURDATE(),CURDATE() + 0;
+------------+---------------+
| CURDATE() | CURDATE() + 0 |
+------------+---------------+
| 2009-07-03 | 20090703 |
+------------+---------------+
2.CURTIME() 當前時間函式
mysql> ? curtime
mysql> SELECT CURTIME(), CURTIME() + 0;
+-----------+---------------+
| CURTIME() | CURTIME() + 0 |
+-----------+---------------+
| 12:07:08 | 120708 |
+-----------+---------------+
3.NOW() 當前日期和時間函式
mysql> ? now
mysql> SELECT NOW(), NOW() + 0;
+---------------------+----------------+
| NOW() | NOW() + 0 |
+---------------------+----------------+
| 2009-07-03 12:07:54 | 20090703120754 |
+---------------------+----------------+
4.UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date) 日期date的時間戳
mysql> ? unix_timestamp
mysql> SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP('1981-02-15 23:23:00');
+------------------+---------------------------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP('1981-02-15 23:23:00') |
+------------------+---------------------------------------+
| 1246594366 | 351098580 |
+------------------+---------------------------------------+
5.FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format) 返回時間戳的日期值（UNIX_TIMESTAMP的反函式）
mysql> ? from_unixtime
mysql> SELECT FROM_UNIXTIME(1246594135),FROM_UNIXTIME(351098580);
+---------------------------+--------------------------+
| FROM_UNIXTIME(1246594135) | FROM_UNIXTIME(351098580) |
+---------------------------+--------------------------+
| 2009-07-03 12:08:55 | 1981-02-15 23:23:00 |
+---------------------------+--------------------------+
6.WEEK(date[,mode]) 返回所給日期是一年中的第幾周
mysql> ? week
mysql> SELECT WEEK('1981-02-15');
+--------------------+
| WEEK('1981-02-15') |
+--------------------+
| 7 |
+--------------------+
7.YEAR(date)
mysql> ? year
mysql> SELECT YEAR('81-02-15');
+------------------+
| YEAR('81-02-15') |
+------------------+
| 1981 |
+------------------+
8.HOUR(time) 返回時間的小時資訊
mysql> ? hour
mysql> SELECT HOUR('10:05:03');
+------------------+
| HOUR('10:05:03') |
+------------------+
| 10 |
+------------------+
9.MINUTE(time) 返回時間的分鐘資訊
mysql> SELECT MINUTE('98-02-03 10:05:03');
+-----------------------------+
| MINUTE('98-02-03 10:05:03') |
+-----------------------------+
| 5 |
+-----------------------------+
10.MONTHNAME(date) 返回時間的完整月份名字
mysql> SELECT MONTHNAME('1981-02-15');
+-------------------------+
| MONTHNAME('1981-02-15') |
+-------------------------+
| February |
+-------------------------+
11.DATE_FORMAT(date,format) 根據format格式date顯示形式
mysql> ? date_format
mysql> SELECT DATE_FORMAT('1981-02-15 23:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('1981-02-15 23:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday February 1981 |
+------------------------------------------------+
12.DATE_ADD(date,INTERVAL expr type) 返回與所給日期date相差INTERVAL的時間段
mysql> select now() current, date_add(now(), INTERVAL 31 day) after31days;
+---------------------+---------------------+
| current | after31days |
+---------------------+---------------------+
| 2009-07-03 12:34:15 | 2009-08-03 12:34:15 |
+---------------------+---------------------+
13.DATEDIFF(expr,expr2) 計算兩個日期之間相差的天數
mysql> SELECT DATEDIFF(now(),'1981-02-15 23:23:00');
+---------------------------------------+
| DATEDIFF(now(),'1981-02-15 23:23:00') |
+---------------------------------------+
| 10365 |
+---------------------------------------+
四、MySQL控制流程函式
首先建立演示表salary
mysql> use test;
mysql> create table salary (userid int, salary decimal(9,2));
mysql> insert into salary values (1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
mysql> select * from salary;
+--------+---------+
| userid | salary |
+--------+---------+
| 1 | 1000.00 |
| 2 | 2000.00 |
| 3 | 3000.00 |
| 4 | 4000.00 |
| 5 | 5000.00 |
| 1 | NULL |
+--------+---------+
1.IF(expr1,expr2,expr3)函式：如果expr1為真則返回expr2，否則返回expr3
mysql> ? IF FUNCTION
mysql> select IF( salary > 2000, 'High', 'Low') from salary;
+------------------------------------+
| if ( salary > 2000, 'High', 'Low') |
+------------------------------------+
| Low |
| Low |
| High |
| High |
| High |
| Low |
+------------------------------------+
2.IFNULL(expr1,expr2)函式：若expr1為NULL則返回expr2內容
mysql> ? IFNULL
mysql> select ifnull(salary,0) from salary;
+------------------+
| ifnull(salary,0) |
+------------------+
| 1000.00 |
| 2000.00 |
| 3000.00 |
| 4000.00 |
| 5000.00 |
| 0.00 |
+------------------+
3.NULLIF(expr1,expr2)函式：若expr1 = expr2則返回NULL，否則返回expr1
mysql> ? NULLIF
mysql> select nullif(salary,2000) from salary;
+---------------------+
| nullif(salary,2000) |
+---------------------+
| 1000.00 |
| NULL |
| 3000.00 |
| 4000.00 |
| 5000.00 |
| NULL |
+---------------------+
4.CASE函式
語法如下：
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
或
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
mysql> ? CASE FUNCTION
mysql> select case when salary <= 2000 then 'Low' else 'High' end from salary;
+-----------------------------------------------------+
| case when salary <= 2000 then 'Low' else 'High' end |
+-----------------------------------------------------+
| Low |
| Low |
| High |
| High |
| High |
| High |
+-----------------------------------------------------+
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 |
+-----------------------------------------------------------------------+
五、其他常用函式
1.DATABASE()函式：用於查詢當前使用資料庫的名字（類似Oracle的show user;）
mysql> ? database
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
2.VERSION()函式：使用者查詢所使用資料庫的版本
mysql> ? version
mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.0.22-log |
+------------+
3.USER()函式：查詢當前登陸使用者名稱
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
4.INET_ATON(expr)函式：查詢IP地址的網路位元組序表示，反函式是INET_NTOA
mysql> SELECT INET_ATON('144.194.192.183');
+------------------------------+
| INET_ATON('144.194.192.183') |
+------------------------------+
| 2428682423 |
+------------------------------+
5.INET_NTOA(expr)函式：查詢網路位元組序代表的IP地址，是INET_ATON的反函式
mysql> ? inet_ntoa
mysql> SELECT INET_NTOA(2428682423);
+-----------------------+
| INET_NTOA(2428682423) |
+-----------------------+
| 144.194.192.183 |
+-----------------------+
六、小結
有事沒事多請教一下幫助is a good idea. 也許這就是開源的好處，她會盡可能的考慮到您查詢和參考的便利性。MySQL的函式還是很豐富的，以上實驗用到的函式都是非常常用的。
這個小文兒，可以“一看了之”，只要你能想到MySQL提供的這些功能函式，check一下幫助系統，就什麼都得到啦。
如果這些函式仍然沒有滿足您的求知慾望，敬請參考官方文件《第12章：函式和運算子》，地址為：http://dev.mysql.com/doc/refman/5.1/zh/functions.html
好運！
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1144932/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Mysql中常用函式的使用示例MySql函式
- mysql儲存過程及日期函式實踐MySql儲存過程函式
- MySQL 儲存過程和函式MySql儲存過程函式
- MySQL儲存過程 (即函式)MySql儲存過程函式
- MySQL儲存過程和函式MySql儲存過程函式
- mySql 儲存過程與函式MySql儲存過程函式
- MySQL 常用函式MySql函式
- MySQL 常用函式。MySql函式
- MySQL常用函式MySql函式
- 【實驗】【MySQL】MySQL的DDL語言演示MySql
- 【實驗】【MySQL】MySQL的DML語言演示MySql
- mysql儲存過程基本函式（轉）MySql儲存過程函式
- Mysql 常用函式（1）- 常用函式彙總MySql函式
- Mysql 常用函式（15）- upper 函式MySql函式
- 轉MySQL--mysql常用函式打全MySql函式
- MySQL自定義函式與儲存過程MySql函式儲存過程
- MySQL 5.5 建立儲存過程和函式MySql儲存過程函式
- MySQL4：儲存過程和函式MySql儲存過程函式
- MySQL常用函式彙總MySql函式
- mysql常用函式詳解MySql函式
- MySQL 5.5常用資訊函式MySql函式
- centos 安裝MySQL全過程CentOSMySql
- mysql儲存過程procedure、函式function的用法MySql儲存過程函式Function
- Mysql 的儲存過程和儲存函式MySql儲存過程儲存函式
- MYSQL學習與實驗（八）——儲存過程實驗MySql儲存過程
- 【MySQL】MySQL（三）儲存過程和函式、觸發器、事務MySql儲存過程函式觸發器
- mysql 常用的九類函式MySql函式
- (4)mysql 中的常用函式MySql函式
- mysql常用函式彙總(分享)MySql函式
- mysql常用函式--個人筆記MySql函式筆記
- 《MySQL 入門教程》第 16 篇 MySQL 常用函式之日期函式MySql函式
- mysql日期函式小結及個人實驗MySql函式
- Mysql 常用函式（20）- ceiling 函式MySql函式
- 《MySQL 入門教程》第 14 篇 MySQL 常用函式之數學函式MySql函式
- redhat 5.4下安裝MYSQL全過程RedhatMySql
- mysql之常用函式(核心總結)MySql函式
- MySQL使用之五_自定義函式和自定義過程MySql函式
- mysql和orcale的儲存過程和儲存函式MySql儲存過程儲存函式