字符串函数
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)
