mysql 常用函数

字符串函数

mysql> select concat('a','b','c'),concat('a',null);
+---------------------+------------------+
| concat('a','b','c') | concat('a',null) |
+---------------------+------------------+
| abc                 | NULL             |
+---------------------+------------------+
1 row in set (0.00 sec)

mysql> select insert('beijing2008you',12,3,'me');
+------------------------------------+
| insert('beijing2008you',12,3,'me') |
+------------------------------------+
| beijing2008me                      |
+------------------------------------+
1 row in set (0.01 sec)

mysql> select lower('BEIJING'),upper('beijing');
+------------------+------------------+
| lower('BEIJING') | upper('beijing') |
+------------------+------------------+
| beijing          | BEIJING          |
+------------------+------------------+
1 row in set (0.00 sec)

mysql> select left('beijing2008',7),left('beijing2008',null),right('beijing2008',4);
+-----------------------+--------------------------+------------------------+
| left('beijing2008',7) | left('beijing2008',null) | right('beijing2008',4) |
+-----------------------+--------------------------+------------------------+
| beijing               | NULL                     | 2008                   |
+-----------------------+--------------------------+------------------------+
1 row in set (0.00 sec)

mysql> select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');
+---------------------------+---------------------------+
| lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |
+---------------------------+---------------------------+
| beijingbeijingbe2008      | beijing2008200820082      |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

mysql> select ltrim(' |beijing'),rtrim('beijing|  ');
+--------------------+---------------------+
| ltrim(' |beijing') | rtrim('beijing|  ') |
+--------------------+---------------------+
| |beijing           | beijing|            |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select repeat('mysql ',3);
+--------------------+
| repeat('mysql ',3) |
+--------------------+
| mysql mysql mysql  |
+--------------------+
1 row in set (0.00 sec)

mysql> select replace('beijing_2010','_2010','2008');
+----------------------------------------+
| replace('beijing_2010','_2010','2008') |
+----------------------------------------+
| beijing2008                            |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
|              -1 |               0 |               1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> select trim(' $ beijing $ ');
+-----------------------+
| trim(' $ beijing $ ') |
+-----------------------+
| $ beijing $           |
+-----------------------+
1 row in set (0.00 sec)

mysql> select substring('beijing2008',8,4),substring('beijing2008',1,7);
+------------------------------+------------------------------+
| substring('beijing2008',8,4) | substring('beijing2008',1,7) |
+------------------------------+------------------------------+
| 2008                         | beijing                      |
+------------------------------+------------------------------+
1 row in set (0.00 sec)

数值函数

mysql> select abs(-0.8),abs(0.8);
+-----------+----------+
| abs(-0.8) | abs(0.8) |
+-----------+----------+
|       0.8 |      0.8 |
+-----------+----------+
1 row in set (0.01 sec)

mysql> select ceil(-0.8),ceil(0.8);
+------------+-----------+
| ceil(-0.8) | ceil(0.8) |
+------------+-----------+
|          0 |         1 |
+------------+-----------+
1 row in set (0.02 sec)

mysql> select floor(-0.8),floor(0.8);
+-------------+------------+
| floor(-0.8) | floor(0.8) |
+-------------+------------+
|          -1 |          0 |
+-------------+------------+
1 row in set (0.00 sec)

mysql> select mod(15,10),mod(1,11),mod(null,10);
+------------+-----------+--------------+
| mod(15,10) | mod(1,11) | mod(null,10) |
+------------+-----------+--------------+
|          5 |         1 |         NULL |
+------------+-----------+--------------+
1 row in set (0.01 sec)

mysql> select rand(),rand();
+---------------------+---------------------+
| rand()              | rand()              |
+---------------------+---------------------+
| 0.14735915059909147 | 0.09535266654133114 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select ceil(100*rand()),ceil(100*rand());
+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+
|                4 |               89 |
+------------------+------------------+
1 row in set (0.01 sec)

mysql> select round(1.1),round(1.1,2),round(1,2);
+------------+--------------+------------+
| round(1.1) | round(1.1,2) | round(1,2) |
+------------+--------------+------------+
|          1 |         1.10 |          1 |
+------------+--------------+------------+
1 row in set (0.01 sec)

mysql> select round(1.235,2),truncate(1.235,2);
+----------------+-------------------+
| round(1.235,2) | truncate(1.235,2) |
+----------------+-------------------+
|           1.24 |              1.23 |
+----------------+-------------------+
1 row in set (0.00 sec)

日期和时间函数

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2016-09-21 |
+------------+
1 row in set (0.00 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 10:22:39  |
+-----------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2016-09-21 10:22:48 |
+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1474424612 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1474424612);
+---------------------------+
| from_unixtime(1474424612) |
+---------------------------+
| 2016-09-21 10:23:32       |
+---------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1);
+---------------------+
| from_unixtime(1)    |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> select week(now()),year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
|          38 |        2016 |
+-------------+-------------+
1 row in set (0.00 sec)

mysql> select hour(now()),minute(now());
+-------------+---------------+
| hour(now()) | minute(now()) |
+-------------+---------------+
|          10 |            27 |
+-------------+---------------+
1 row in set (0.00 sec)

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| September        |
+------------------+
1 row in set (0.02 sec)

mysql日期和时间格式

mysql> select date_format(now(),'%M,%D,%Y');
+-------------------------------+
| date_format(now(),'%M,%D,%Y') |
+-------------------------------+
| September,21st,2016           |
+-------------------------------+
1 row in set (0.01 sec)

mysql日期间隔类型

mysql> select now() current, date_add(now(),interval 31 day) after31days;
+---------------------+---------------------+
| current             | after31days         |
+---------------------+---------------------+
| 2016-09-21 11:08:33 | 2016-10-22 11:08:33 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select now() current, date_add(now(),interval -31 day) before31days,date_add(now(),interval '-1_-2' year_month) before_oneyear_twomonth;
+---------------------+---------------------+-------------------------+
| current             | before31days        | before_oneyear_twomonth |
+---------------------+---------------------+-------------------------+
| 2016-09-21 11:10:34 | 2016-08-21 11:10:34 | 2015-07-21 11:10:34     |
+---------------------+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql> select datediff('2015-07-06',now());
+------------------------------+
| datediff('2015-07-06',now()) |
+------------------------------+
|                         -443 |
+------------------------------+
1 row in set (0.00 sec)

流程函数

测试数据

mysql> create table salary(userid int,salary decimal(9,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into salary values (1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
Query OK, 6 rows affected (0.01 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 |
+--------+---------+
6 rows in set (0.00 sec)

mysql> select userid, if(salary>2000,'high','low') from salary;
+--------+------------------------------+
| userid | if(salary>2000,'high','low') |
+--------+------------------------------+
|      1 | low                          |
|      2 | low                          |
|      3 | high                         |
|      4 | high                         |
|      5 | high                         |
|      1 | low                          |
+--------+------------------------------+
6 rows in set (0.00 sec)

mysql> select userid,ifnull(salary,0) from salary;
+--------+------------------+
| userid | ifnull(salary,0) |
+--------+------------------+
|      1 |          1000.00 |
|      2 |          2000.00 |
|      3 |          3000.00 |
|      4 |          4000.00 |
|      5 |          5000.00 |
|      1 |             0.00 |
+--------+------------------+
6 rows in set (0.00 sec)

mysql> select userid, case when salary<=2000 then 'low' else 'high' end from salary;
+--------+---------------------------------------------------+
| userid | case when salary<=2000 then 'low' else 'high' end |
+--------+---------------------------------------------------+
|      1 | low                                               |
|      2 | low                                               |
|      3 | high                                              |
|      4 | high                                              |
|      5 | high                                              |
|      1 | high                                              |
+--------+---------------------------------------------------+
6 rows in set (0.00 sec)

mysql> select userid,case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
+--------+-----------------------------------------------------------------------+
| userid | case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end |
+--------+-----------------------------------------------------------------------+
|      1 | low                                                                   |
|      2 | mid                                                                   |
|      3 | high                                                                  |
|      4 | high                                                                  |
|      5 | high                                                                  |
|      1 | high                                                                  |
+--------+-----------------------------------------------------------------------+
6 rows in set (0.00 sec)

其他函数

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.31-log |
+------------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select inet_aton('192.168.0.7');
+--------------------------+
| inet_aton('192.168.0.7') |
+--------------------------+
|               3232235527 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(3232235527);
+-----------------------+
| inet_ntoa(3232235527) |
+-----------------------+
| 192.168.0.7           |
+-----------------------+
1 row in set (0.00 sec)

使用inet_aton查看192.168.1.3和192.168.1.20之间有多少个ip地址

mysql> create table ipaddr (ip varchar(15));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ipaddr values ('192.168.1.1'),('192.168.1.3'),('192.168.1.6'),('192.168.1.10'),('192.168.1.20'),('192.168.1.30');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from ipaddr;
+--------------+
| ip           |
+--------------+
| 192.168.1.1  |
| 192.168.1.3  |
| 192.168.1.6  |
| 192.168.1.10 |
| 192.168.1.20 |
| 192.168.1.30 |
+--------------+
6 rows in set (0.00 sec)


mysql> select * from ipaddr where inet_aton(ip)>=inet_aton('192.168.1.3') and inet_aton(ip)<=inet_aton('192.168.1.20');
+--------------+
| ip           |
+--------------+
| 192.168.1.3  |
| 192.168.1.6  |
| 192.168.1.10 |
| 192.168.1.20 |
+--------------+
4 rows in set (0.00 sec)

password用户系统用户，md5用于应用

mysql> select password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select md5('123456');
+----------------------------------+
| md5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)