1.字符串函数mysql> use test1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select concat('aaa','bbb','ccc'),concat('aaa',null);+---------------------------+--------------------+| concat('aaa','bbb','ccc') | concat('aaa',null) |+---------------------------+--------------------+| aaabbbccc | NULL |+---------------------------+--------------------+1 row in set (0.00 sec)mysql> select insert('beijing200&you',12,3,'me');+------------------------------------+| insert('beijing200&you',12,3,'me') |+------------------------------------+| beijing200&me |+------------------------------------+1 row in set (0.00 sec)mysql> select lower('BEIJING2008'),UPPER('beijing2008');+----------------------+----------------------+| lower('BEIJING2008') | UPPER('beijing2008') |+----------------------+----------------------+| beijing2008 | BEIJING2008 |+----------------------+----------------------+1 row in set (0.00 sec)mysql> select left('beijing2008',7),left('beijing',null),right('beijing2008',4);+-----------------------+----------------------+------------------------+| left('beijing2008',7) | left('beijing',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(' lbeijing'),rtrim('beijing! ');+---------------------+------------------------------+| ltrim(' lbeijing') | rtrim('beijing! ') |+---------------------+------------------------------+| lbeijing | beijing! |+---------------------+------------------------------+1 row in set (0.00 sec)mysql> select repeat('mysql ',3);+-----------------------+| repeat('mysql ',3) |+-----------------------+| mysql mysql mysql |+-----------------------+1 row in set (0.01 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(' $ beijing2008 $ ');+-------------------------------------------+| trim(' $ beijing2008 $ ') |+-------------------------------------------+| $ beijing2008 $ |+-------------------------------------------+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)2.数值处理函数mysql> select abs(-0.8),abs(0.8);+-----------+----------+| abs(-0.8) | abs(0.8) |+-----------+----------+| 0.8 | 0.8 |+-----------+----------+1 row in set (0.00 sec)mysql> select ceil(-9.21),ceil(95.21355);+-------------+----------------+| ceil(-9.21) | ceil(95.21355) |+-------------+----------------+| -9 | 96 |+-------------+----------------+1 row in set (0.00 sec)mysql> select floor(-0.8),floor(0.32805);+-------------+----------------+| floor(-0.8) | floor(0.32805) |+-------------+----------------+| -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.00 sec)mysql> select rand(),rand(),rand();+--------------------+--------------------+--------------------+| rand() | rand() | rand() |+--------------------+--------------------+--------------------+| 0.7249892304884169 | 0.4090333165685025 | 0.8701989221109068 |+--------------------+--------------------+--------------------+1 row in set (0.00 sec)mysql> select ceil(100*rand()),ceil(100*rand());+------------------+------------------+| ceil(100*rand()) | ceil(100*rand()) |+------------------+------------------+| 13 | 1 |+------------------+------------------+1 row in set (0.00 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.00 sec)mysql> select round(1.1),round(1.1,2),round(1.235251,2);+------------+--------------+-------------------+| round(1.1) | round(1.1,2) | round(1.235251,2) |+------------+--------------+-------------------+| 1 | 1.10 | 1.24 |+------------+--------------+-------------------+1 row in set (0.00 sec)mysql> select round(1.1),round(1.1,2),round(1.2635251,2);+------------+--------------+--------------------+| round(1.1) | round(1.1,2) | round(1.2635251,2) |+------------+--------------+--------------------+| 1 | 1.10 | 1.26 |+------------+--------------+--------------------+1 row in set (0.00 sec)mysql> select round(1.2356234234,2),truncate(1.2384235235,2);+-----------------------+--------------------------+| round(1.2356234234,2) | truncate(1.2384235235,2) |+-----------------------+--------------------------+| 1.24 | 1.23 |+-----------------------+--------------------------+1 row in set (0.01 sec)3.日期函数mysql> select curdate();+------------+| curdate() |+------------+| 2015-10-02 |+------------+1 row in set (0.01 sec)mysql> select curtime();+-----------+| curtime() |+-----------+| 09:40:21 |+-----------+1 row in set (0.00 sec)mysql> select now();+---------------------+| now() |+---------------------+| 2015-10-02 09:41:40 |+---------------------+1 row in set (0.00 sec)mysql> select unix_timestamp(now());+-----------------------+| unix_timestamp(now()) |+-----------------------+| 1443804114 |+-----------------------+1 row in set (0.00 sec)mysql> select from_unixtime(1443804114);+---------------------------+| from_unixtime(1443804114) |+---------------------------+| 2015-10-02 09:41:54 |+---------------------------+1 row in set (0.00 sec)mysql> select week(now()),year(now());+-------------+-------------+| week(now()) | year(now()) |+-------------+-------------+| 39 | 2015 |+-------------+-------------+1 row in set (0.00 sec)mysql> select hour(curtime()),minute(curtime());+-----------------+-------------------+| hour(curtime()) | minute(curtime()) |+-----------------+-------------------+| 9 | 42 |+-----------------+-------------------+1 row in set (0.00 sec)mysql> select now();+---------------------+| now() |+---------------------+| 2015-10-02 09:42:53 |+---------------------+1 row in set (0.00 sec)mysql> select monthname(now());+------------------+| monthname(now()) |+------------------+| October |+------------------+1 row in set (0.01 sec)mysql> select date_format(now(),'%M,%D,%Y');+-------------------------------+| date_format(now(),'%M,%D,%Y') |+-------------------------------+| October,2nd,2015 |+-------------------------------+1 row in set (0.00 sec)mysql> select now() current;+---------------------+| current |+---------------------+| 2015-10-02 09:44:33 |+---------------------+1 row in set (0.00 sec)mysql> select date_add(now(),INTERVAL '1_2' year_month);+-------------------------------------------+| date_add(now(),INTERVAL '1_2' year_month) |+-------------------------------------------+| 2016-12-02 09:44:52 |+-------------------------------------------+1 row in set (0.00 sec)mysql> select date_add(now(),INTERVAL 31 day) ;+---------------------------------+| date_add(now(),INTERVAL 31 day) |+---------------------------------+| 2015-11-02 09:45:44 |+---------------------------------+1 row in set (0.00 sec)mysql> select date_add(now(),INTERVAL 31 day) as after31days;+---------------------+| after31days |+---------------------+| 2015-11-02 09:45:55 |+---------------------+1 row in set (0.01 sec)mysql> select date_add(now(),interval '1_2' year_month) as after_oneyear_twomonth;+------------------------+| after_oneyear_twomonth |+------------------------+| 2016-12-02 09:46:27 |+------------------------+1 row in set (0.01 sec)mysql> select datediff('2008-08-08',now());+------------------------------+| datediff('2008-08-08',now()) |+------------------------------+| -2611 |+------------------------------+1 row in set (0.00 sec)4.查询的逻辑处理mysql> use test1;Database changedmysql> create table salary( -> userid int, -> salart decimal(9,2));Query OK, 0 rows affected (0.02 sec)mysql> insert into salary values -> (1,1000), -> (2,2000), -> (3,3000), -> (4,4000), -> (5,5000), -> (6,6000), -> (7,7000), -> (1,null);Query OK, 8 rows affected (0.02 sec)Records: 8 Duplicates: 0 Warnings: 0mysql> select * from salary;+--------+---------+| userid | salart |+--------+---------+| 1 | 1000.00 || 2 | 2000.00 || 3 | 3000.00 || 4 | 4000.00 || 5 | 5000.00 || 6 | 6000.00 || 7 | 7000.00 || 1 | NULL |+--------+---------+8 rows in set (0.00 sec)mysql> select if(salary>2000,'high','low') from salary;+------------------------------+| if(salary>2000,'high','low') |+------------------------------+| low || low || high || high || high || high || high || low |+------------------------------+8 rows in set (0.00 sec)mysql> select ifnull(salary,0) from salary;+------------------+| ifnull(salary,0) |+------------------+| 1000.00 || 2000.00 || 3000.00 || 4000.00 || 5000.00 || 6000.00 || 7000.00 || 0.00 |+------------------+8 rows in set (0.00 sec)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 || high || high |+---------------------------------------------------+8 rows in set (0.00 sec)mysql> select case salary when 1000 then 'low' -> when 2000 then 'mid' -> else 'high' end as level from salary;+-------+| level |+-------+| low || mid || high || high || high || high || high || high |+-------+8 rows in set (0.01 sec)5.mysql系统相关内容查询mysql> select database();+------------+| database() |+------------+| test1 |+------------+1 row in set (0.00 sec)mysql> select version();+------------+| version() |+------------+| 5.5.44-log |+------------+1 row in set (0.00 sec)mysql> select user();+----------------+| user() |+----------------+| [email protected] |+----------------+1 row in set (0.00 sec)6.IP地址相关查询mysql> select inet_aton('192.168.1.1');+--------------------------+| inet_aton('192.168.1.1') |+--------------------------+| 3232235777 |+--------------------------+1 row in set (0.00 sec)mysql> select inet_ntoa(323235777);+----------------------+| inet_ntoa(323235777) |+----------------------+| 19.68.47.193 |+----------------------+1 row in set (0.00 sec)mysql> select inet_ntoa(3232235777);+-----------------------+| inet_ntoa(3232235777) |+-----------------------+| 192.168.1.1 |+-----------------------+1 row in set (0.00 sec)mysql> select * from t;+------+| col |+------+| a,b || a,d || a,b || a,c || a |+------+5 rows in set (0.00 sec)mysql> alter table t rename t_oldtable;Query OK, 0 rows affected (0.02 sec)mysql> create table t( ip varchar( 20));Query OK, 0 rows affected (0.02 sec)mysql> insert into t(ip) 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.01 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> select * from t;+--------------+| ipaddress |+--------------+| 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 t where ip>='192.168.1.3' and ip<='192.168.1.20';Empty set (0.00 sec)mysql> select * from t where ip>='192.168.1.3' and ip<='192.168.1.20';Empty set (0.00 sec)mysql> select * from t 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.01 sec)7.密码相关函数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)版权声明：本博客原创文章欢迎转载，请转载的朋友最好注明出处，谢谢大家。