MySQL5.6中的常用函式詳細用法介紹

 常用的函式有：
1. 字串函式；主要用於處理字串。
2. 數值函式；主要用於處理數字。
3. 日期和時間函式；主要用於處理日期和事件。
4. 系統資訊函式；獲取系統資訊。


1. 使用字串函式：
1.1 合併字串函式concat() 和 concat_ws()：
concat(s1, s2,...sn)
//該函式會將傳入的引數連線起來返回合併的字串型別的資料。如果其中一個引數為null，則返回值為null.

例子:
mysql> select concat('my','s','ql');
+-----------------------+
| concat('my','s','ql') |
+-----------------------+
| mysql                 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select concat('my','s','ql',null);
+----------------------------+
| concat('my','s','ql',null) |:


+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)

mysql> select concat(curdate(), 12.2);
+-------------------------+
| concat(curdate(), 12.2) |
+-------------------------+
| 2017-03-1712.2          |
+-------------------------+
1 row in set (0.00 sec)


concat_ws()的定義：
concat_ws(sep,s1,s2,...sn)
//該函式與concat()相比，多了一個表示分隔符的seq引數，不僅將傳入的其他引數連線起來，而且還會透過分隔符將各個字串分割開來。
//分隔符可以是一個字串，也可以是其他引數。如果分割符為null，則返回結果為null。函式會忽略任何分割符後的引數null.

例子:
mysql> select concat_ws('-','010','87658907');
+---------------------------------+
| concat_ws('-','010','87658907') |
+---------------------------------+
| 010-87658907                    |
+---------------------------------+
1 row in set (0.02 sec)


mysql> select concat_ws(null,'010','87658907');
+----------------------------------+
| concat_ws(null,'010','87658907') |
+----------------------------------+
| NULL                             |
+----------------------------------+
1 row in set (0.00 sec)


mysql> select concat_ws('-','010',null,'87658907');
+--------------------------------------+
| concat_ws('-','010',null,'87658907') |
+--------------------------------------+
| 010-87658907                         |
+--------------------------------------+
1 row in set (0.00 sec)


1.2 比較字串大小函式strcmp()：
strcmp()定義為：
strcmp(str1,str2);
//如果引數str1大於str2，返回1；如果str1小於str2,則返回-1；如果str1等於str2，則返回0；

例子:
mysql> select strcmp('abc','abd'),strcmp('abc','abc'),strcmp('abc','abb');
+---------------------+---------------------+---------------------+
| strcmp('abc','abd') | strcmp('abc','abc') | strcmp('abc','abb') |
+---------------------+---------------------+---------------------+
|                  -1 |                   0 |                   1 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)


1.3 獲取字串長度函式length()和字元數函式char_length()：
length()的定義如下：
length(str)
char_length(str)的定義如下：
char_length(str)

例子:
mysql> select length('mysql'),length('中國'),char_length('mysql'),char_length('中國');
+-----------------+------------------+----------------------+-----------------------+
| length('mysql') | length('漢字')   | char_length('mysql') | char_length('中國')   |
+-----------------+------------------+----------------------+-----------------------+
|               5 |                6 |                    5 |                     2 |
+-----------------+------------------+----------------------+-----------------------+
1 row in set (0.00 sec)

//字串‘MySQL'共有5個字元，但是佔6個位元組空間。這是因為每個字串都是以\0結束。兩個函式都是獲取字串的字元數而不是所佔空間大小。utf8(一箇中文三個位元組),所以兩個漢字佔6個位元組。


1.4 字母的大小寫轉換upper()和lower()：
字母大寫轉換函式：upper(s); ucase(s);
字母小寫轉換函式：lower(s); lcase(s);

例子:
mysql> select upper('mysql'),ucase('mYsql'),lower('MYSQL'),lcase('MYsql');
+----------------+----------------+----------------+----------------+
| upper('mysql') | ucase('mYsql') | lower('MYSQL') | lcase('MYsql') |
+----------------+----------------+----------------+----------------+
| MYSQL          | MYSQL          | mysql          | mysql          |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)


1.5 查詢字串：
mysql中提供了豐富的函式去查詢字串的位置。分別有find_in_set()函式、field()函式、locate()函式、position()函式和instr()函式。同時還提供了查詢指定位置的字串的函式elt()。

1.5.1 返回字串位置的find_in_set()函式：
函式定義為：
find_in_set(str1,str2) 
//會返回在字串str2中與str1相匹配的字串的位置，引數str2字串中將包含若干個用逗號隔開的字串。

例子：
mysql> select find_in_set('mysql','oracle,db2,mysql');
+-----------------------------------------+
| find_in_set('mysql','oracle,db2,mysql') |
+-----------------------------------------+
|                                       3 |
+-----------------------------------------+
1 row in set (0.00 sec)


1.5.2 返回指定字串位置的field()函式：
函式定義為：
filed(str,str1,str2...)
//返回第一個與字串str匹配的字串的位置。

例子：
mysql>  select field('mysql','oracle','db2','redis','mysql');
+-----------------------------------------------+
| field('mysql','oracle','db2','redis','mysql') |
+-----------------------------------------------+
|                                             4 |
+-----------------------------------------------+
1 row in set (0.00 sec)


1.5.3 返回子字串相匹配的開始位置：
mysql中有三個函式可以獲取子字串相匹配的開始位置，分別是locate()、position()、instr()函式。
locate(str1,str) //返回引數str中字串str1的開始位置
position(str1 in str) 和 instr(str,str1)

例子:
mysql> select locate('sql','mysql'),position('sql' in 'mysql'),instr('mysql','sql');
+-----------------------+----------------------------+----------------------+
| locate('sql','mysql') | position('sql' in 'mysql') | instr('mysql','sql') |
+-----------------------+----------------------------+----------------------+
|                     3 |                          3 |                    3 |
+-----------------------+----------------------------+----------------------+
1 row in set (0.00 sec)


1.5.4 返回指定位置的字串的elt()函式：
函式語法為：
elt(n,str1,str2...);

例子:
mysql> select elt(2,'db2','mysql','oracle');
+-------------------------------+
| elt(2,'db2','mysql','oracle') |
+-------------------------------+
| mysql                         |
+-------------------------------+
1 row in set (0.00 sec)


1.5.5 選擇字串的make_set()函式：
函式定義為：
make_set(num,str1,str2...strn)

例子:
mysql> select bin(5),make_set(5,'mysql','db2','oracle','redus');
+--------+--------------------------------------------+
| bin(5) | make_set(5,'mysql','db2','oracle','redus') |
+--------+--------------------------------------------+
| 101    | mysql,oracle                               |
+--------+--------------------------------------------+
1 row in set (0.00 sec)

//make_set()首先會將數值num轉換成二進位制數，然後按照二進位制從引數str1,str2,...,strn中選取相應的字串。再透過二進位制從右到左的順序讀取該值，如果值為1選擇該字串，否則將不選擇該字串。


1.6 從現有字串中擷取子字串：

擷取子字串的函式有：left(),right(),substring(),mid();

1.6.1 從左邊或右邊擷取子字串：
函式定義為：
left(str,num)
//返回字串str中包含前num個字母（從左邊數）的字串。
right(str,num)
//返回字串str中包含後num個字母（從右邊數）的字串。

例子：
mysql> select left('mysql',2),right('mysql',3);
+-----------------+------------------+
| left('mysql',2) | right('mysql',3) |
+-----------------+------------------+
| my              | sql              |
+-----------------+------------------+
1 row in set (0.01 sec)


1.6.2 擷取指定位置和長度的字串：
可以透過substring()和mid()函式擷取指定位置和長度的字串。
函式語法為：
substring(str,num,len) //返回字串str中的第num個位置開始長度為len的子字串。
mid(str,num,len)

例子：
mysql> select substring('duansf',1,4),mid('duansf',5,6);
+-------------------------+-------------------+
| substring('duansf',1,4) | mid('duansf',5,6) |
+-------------------------+-------------------+
| duan                    | sf                |
+-------------------------+-------------------+
1 row in set (0.00 sec)


1.7 去除字串的首尾空格：
去除字串首尾空格的函式有：ltrim()、rtrim()、trim()

1.7.1 去除字串開始處的空格：
函式定義如下：
ltrim(str) //返回去掉開始處空格的字串

例子:
mysql> select length(concat('-',' mysql ','-')),length(concat('-',ltrim(' mysql '),'-'));
+-----------------------------------+------------------------------------------+
| length(concat('-',' mysql ','-')) | length(concat('-',ltrim(' mysql '),'-')) |
+-----------------------------------+------------------------------------------+
|                                 9 |                                        8 |
+-----------------------------------+------------------------------------------+
1 row in set (0.00 sec)


1.7.2 去除字串結束處的空格：
rtrim(str) //返回去掉結束處空格的字串。

例子：
mysql>  select length(concat('-',' mysql ','-')) ,length(concat('-',rtrim(' mysql '),'-'));
+-----------------------------------+------------------------------------------+
| length(concat('-',' mysql ','-')) | length(concat('-',rtrim(' mysql '),'-')) |
+-----------------------------------+------------------------------------------+
|                                 9 |                                        8 |
+-----------------------------------+------------------------------------------+
1 row in set (0.00 sec)


1.7.3 去除字串首尾空格：
trim(str) //返回去掉首尾空格的字串

例子：
mysql> select concat(' mysql ') origi,length(concat(' mysql ')) orilen, concat(trim(' mysql ')) after, length(concat(trim(' mysql '))) afterlen;
+---------+--------+-------+----------+
| origi   | orilen | after | afterlen |
+---------+--------+-------+----------+
|  mysql  |      7 | mysql |        5 |
+---------+--------+-------+----------+
1 row in set (0.00 sec)


1.8 替換字串：

實現替換字串的功能，分別為insert()和replace()

1.8.1 使用insert()函式：

函式定義為：
insert(str,pos,len,newstr)
//insert()函式會將字串str中的pos位置開始長度為len的字串用字串newstr來替換。
//如果引數pos的值超過字串長度，則返回值為原始字串str。
//如果len的長度大於原來str中所剩字串的長度，則從位置pos開始進行全部替換。若任何一個引數為null,則返回值為null.

例子:
mysql> select insert('這是mysql資料庫系統',3,5,'oracle') bieming;
+-----------------------------+
| bieming                     |
+-----------------------------+
| 這是oracle資料庫系統        |
+-----------------------------+
1 row in set (0.00 sec)


1.8.1 使用replace()函式：
函式的定義為：
replace(str,substr,newstr) //將字串str中的子字串substr用字串newstr來替換。

例子:
mysql> select replace('這是db2資料庫','db2','mysql') bieming;
+----------------------+
| bieming              |
+----------------------+
| 這是mysql資料庫      |
+----------------------+
1 row in set (0.00 sec)


2. 使用數值函式：


2.1 獲取隨機數：
透過rand()和rand(x)函式來獲取隨機數。這兩個函式都會返回0-1之間的隨機數，其中rand()函式返回的數是完全隨機的，而rand(x)函式返回的隨機數值是完全相同的。

例子:
mysql> select rand(),rand(),rand(2),rand(2);
+--------------------+--------------------+--------------------+--------------------+
| rand()             | rand()             | rand(2)            | rand(2)            |
+--------------------+--------------------+--------------------+--------------------+
| 0.6218820266629402 | 0.4098255535679176 | 0.6555866465490187 | 0.6555866465490187 |
+--------------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)


2.2 獲取整數的函式：

在具體應用中，如果想要獲取整數，可以透過ceil()和floor()函式來實現。
ceil()函式的定義為：
ceil(x) //函式返回大於或等於數值x的最小整數。
floor() //函式返回小於或等於數值x的最大整數。

例子:
mysql> select ceil(4.3),ceil(-2.4),floor(4.3),floor(-2.4);
+-----------+------------+------------+-------------+
| ceil(4.3) | ceil(-2.4) | floor(4.3) | floor(-2.4) |
+-----------+------------+------------+-------------+
|         5 |         -2 |          4 |          -3 |
+-----------+------------+------------+-------------+
1 row in set (0.00 sec)


2.3 擷取數值函式：

可以透過truncate()對數值的小數位進行擷取：
函式定義為：
truncate(x,y) //返回數值x,保留小數點後y位

例子:
mysql> select truncate(903.342434,2),truncate(903.342,-1);
+------------------------+----------------------+
| truncate(903.342434,2) | truncate(903.342,-1) |
+------------------------+----------------------+
|                 903.34 |                  900 |
+------------------------+----------------------+
1 row in set (0.00 sec)


2.4 四捨五入函式：

對數值進行四捨五入可以透過round()函式實現:
round(x) 
//函式返回值x經過四捨五入操作後的數值。

round(x,y)
//返回數值x保留到小數點後y位的值。在具體擷取資料時需要進行四捨五入的操作。

例子:
mysql> select round(902.53567),round(-902.53567),round(902.53567,2),round(902.53567,-1);
+------------------+-------------------+--------------------+---------------------+
| round(902.53567) | round(-902.53567) | round(902.53567,2) | round(902.53567,-1) |
+------------------+-------------------+--------------------+---------------------+
|              903 |              -903 |             902.54 |                 900 |
+------------------+-------------------+--------------------+---------------------+
1 row in set (0.02 sec)


3. 使用日期和時間函式：

3.1 獲取當前日期和時間的函式：

3.1.1 獲取當前日期和時間（日期 + 時間）：
MySQL中可以透過四個函式獲取當前日期和時間，分別是now(),current_timestamp(),localtime(),sysdate()，這四個函式不僅可以獲取當前日期和時間，而且顯示的格式也一樣。推薦使用now()

例子:
mysql> select now(),current_timestamp(),localtime(),sysdate();
+---------------------+---------------------+---------------------+---------------------+
| now()               | current_timestamp() | localtime()         | sysdate()           |
+---------------------+---------------------+---------------------+---------------------+
| 2017-03-17 15:52:24 | 2017-03-17 15:52:24 | 2017-03-17 15:52:24 | 2017-03-17 15:52:24 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)


3.1.2 獲取當前日期：

獲取當前日期的函式curdate()和current_date()函式。

例子:
mysql>  select curdate(),current_date();
+------------+----------------+
| curdate()  | current_date() |
+------------+----------------+
| 2017-03-17 | 2017-03-17     |
+------------+----------------+
1 row in set (0.00 sec)




3.1.3 獲取當前時間：
獲取當前時間的函式，curtime()或者current_time();推薦使用curtime();

例子:
mysql> select curtime(),current_time();
+-----------+----------------+
| curtime() | current_time() |
+-----------+----------------+
| 15:53:32  | 15:53:32       |
+-----------+----------------+
1 row in set (0.00 sec)


3.2 獲取日期和時間各部分值：

在MySQL中，可以透過各種函式來獲取當前日期和時間的各部分值，其中year()函式返回日期中的年份，quarter()函式返回日期屬於第幾個季度，month()函式返回日期屬於第幾個月，week()函式返回日期屬於第幾個星期，dayofmonth()函式返回日期屬於當前月的第幾天,hour()函式返回時間的小時，minute()函式返回時間的分鐘，second()函式返回時間的秒。

例子：
mysql> select now(),year(now()),quarter(now()),month(now()),week(now()),dayofmonth(now()),hour(now()),minute(now()),second(now());
+---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+
| now()               | year(now()) | quarter(now()) | month(now()) | week(now()) | dayofmonth(now()) | hour(now()) | minute(now()) | second(now()) |
+---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+
| 2017-03-17 15:54:07 |        2017 |              1 |            3 |          11 |                17 |          15 |            54 |             7 |
+---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+
1 row in set (0.00 sec)


3.2.1 關於月的函式：

例子：
mysql> select now(),month(now()),monthname(now());
+---------------------+--------------+------------------+
| now()               | month(now()) | monthname(now()) |
+---------------------+--------------+------------------+
| 2017-03-17 15:54:43 |            3 | March            |
+---------------------+--------------+------------------+
1 row in set (0.03 sec)


3.2.2 關於星期的函式：

例子：
mysql>  select now(),week(now()),weekofyear(now()),dayname(now()),dayofweek(now()),weekday(now());
+---------------------+-------------+-------------------+----------------+------------------+----------------+
| now()               | week(now()) | weekofyear(now()) | dayname(now()) | dayofweek(now()) | weekday(now()) |
+---------------------+-------------+-------------------+----------------+------------------+----------------+
| 2017-03-17 15:55:32 |          11 |                11 | Friday         |                6 |              4 |
+---------------------+-------------+-------------------+----------------+------------------+----------------+
1 row in set (0.00 sec)


3.2.3 關於天的函式：

例子：
mysql> select now(),dayofyear(now()),dayofmonth(now());
+---------------------+------------------+-------------------+
| now()               | dayofyear(now()) | dayofmonth(now()) |
+---------------------+------------------+-------------------+
| 2017-03-17 15:56:12 |               76 |                17 |
+---------------------+------------------+-------------------+
1 row in set (0.00 sec)


3.2.4 獲取指定值的extract()：
函式定義為：
extract(type from date)
//上述函式會從日期和時間引數date中獲取指定型別引數type的值。type的取值可以是：year，month，day，hour，minute和second

例子：
mysql> select now(),extract(year from now()) year,extract(month from now()) month,extract(day from now()) day,extract(hour from now()) hour,extract(minute from now()) minute,extract(second from now()) second;
+---------------------+------+-------+------+------+--------+--------+
| now()               | year | month | day  | hour | minute | second |
+---------------------+------+-------+------+------+--------+--------+
| 2017-03-17 15:57:26 | 2017 |     3 |   17 |   15 |     57 |     26 |
+---------------------+------+-------+------+------+--------+--------+
1 row in set (0.00 sec)


3.3 計算日期和時間的函式：

3.3.1 與預設日期和時間操作：
兩個函式來實現與預設日期和時間的操作，分別為to_days()和from_days()
to_days(date):該函式計算日期引數date與預設日期和時間（0000年1月1日）之間的想個天數。
from_days(number)：該函式計算從預設日期和時間（0000年1月1日）開始經歷number天后的日期和時間。

例子：
mysql> select now(),to_days(now()),from_days(to_days(now()));
+---------------------+----------------+---------------------------+
| now()               | to_days(now()) | from_days(to_days(now())) |
+---------------------+----------------+---------------------------+
| 2017-03-17 15:58:44 |         736770 | 2017-03-17                |
+---------------------+----------------+---------------------------+
1 row in set (0.00 sec)


3.3.2 與指定日期和時間操作：
adddate(date,n)函式：該函式計算日期引數date加上n天后的日期。
subdate(date,n)函式：該函式計算日期引數date減去n天后的日期。
adddate(d,interval expr type)：返回日期引數d加上一段時間後的日期，表示式引數expr決定了時間的長度，引數type決定了所操作的物件。
subdate(d,interval expr type)：返回日期引數d減去一段時間後的日期，表示式expr決定了時間的長度。引數type決定了所操作的物件。
addtime(time,n):計算時間引數time加上n秒後的時間。
subtime(time,n):計算時間引數time減去n秒後的時間。

例子一：
mysql> select curdate(),adddate(curdate(),5),subdate(curdate(),5);
+------------+----------------------+----------------------+
| curdate()  | adddate(curdate(),5) | subdate(curdate(),5) |
+------------+----------------------+----------------------+
| 2017-03-17 | 2017-03-22           | 2017-03-12           |
+------------+----------------------+----------------------+
1 row in set (0.00 sec)

例子二：
mysql> select curdate(),adddate(curdate(),interval '2,3' year_month),subdate(curdate(),interval '2,3' year_month);
+------------+----------------------------------------------+----------------------------------------------+
| curdate()  | adddate(curdate(),interval '2,3' year_month) | subdate(curdate(),interval '2,3' year_month) |
+------------+----------------------------------------------+----------------------------------------------+
| 2017-03-17 | 2019-06-17                                   | 2014-12-17                                   |
+------------+----------------------------------------------+----------------------------------------------+
1 row in set (0.00 sec)

例子三：
mysql> select curtime(),addtime(curtime(),5),subtime(curtime(),5);
+-----------+----------------------+----------------------+
| curtime() | addtime(curtime(),5) | subtime(curtime(),5) |
+-----------+----------------------+----------------------+
| 16:03:49  | 16:03:54             | 16:03:44             |
+-----------+----------------------+----------------------+
1 row in set (0.03 sec)


4. 使用系統資訊函式：
select version(),database(),user();

例子：
mysql> select version(),database(),user();
+------------+------------+----------------+
| version()  | database() | user()         |
+------------+------------+----------------+
| 5.6.27-log | mysql      | mdba@localhost |
+------------+------------+----------------+
1 row in set (0.00 sec)