MySQL的函数查询语句_mysql 常用函数以及常见查询语句

MySQL 常用函数

1、数据库中取昨天的日期mysql> select date_sub(current_date(),interval 1 day);

+-----------------------------------------+

| date_sub(current_date(),interval 1 day) |

+-----------------------------------------+

| 2016-01-11                              |

+-----------------------------------------+

1 row in set (0.00 sec)

2、数据库中取明天的日期mysql> select date_add(current_date(),interval 1 day) ;

+-----------------------------------------+

| date_add(current_date(),interval 1 day) |

+-----------------------------------------+

| 2016-01-13                              |

+-----------------------------------------+

1 row in set (0.00 sec)

3、IPV4和整形之间相互转化mysql> select inet_aton(‘192.168.2.18‘);

+---------------------------+

| inet_aton(‘192.168.2.18‘) |

+---------------------------+

|                3232236050 |

+---------------------------+

1 row in set (0.00 sec)

mysql> select inet_ntoa(3232236050);

+-----------------------+

| inet_ntoa(3232236050) |

+-----------------------+

| 192.168.2.18          |

+-----------------------+

1 row in set (0.00 sec)

4、日期转化为UNIXTIMEmysql> select unix_timestamp(‘2016-01-12 11:22:23‘);

+---------------------------------------+

| unix_timestamp(‘2016-01-12 11:22:23‘) |

+---------------------------------------+

|                            1452568943 |

+---------------------------------------+

1 row in set (0.00 sec)

5、UNIXTIME转化为日期mysql> select from_unixtime(1452568943);

+---------------------------+

| from_unixtime(1452568943) |

+---------------------------+

| 2016-01-12 11:22:23       |

+---------------------------+

1 row in set (0.00 sec)

MySQL 常用查询语句

1、csv文件导出导入## 将数据导出成csv

select * from myid into outfile ‘/tmp/test.sql‘;

## 将csv导入到表中

load data local infile ‘/tmp/test.sql‘ ignore into table NL_U_MOBILE_URI_TEST fields terminated by ‘\t‘ lines terminated by ‘\n‘;

2、使用profile分析sql的执行过程mysql> set profiling=1;

mysql> select  sum(error_count) as error_count, mobile_app_version_id as mobile_app_version_id from NL_MOB_APP_ERROR_TRACE where  timestamp >= ‘2015-05-27 09:00:00‘ AND timestamp 

mysql> show profiles;

mysql> select state,sum(duration) as Total_R, round(100*sum(duration)/(select sum(duration) from information_schema.profiling where [email protected]_id),2) as Pct_R, count(*) as Calls, sum(duration)/count(*) as "R/Call" from information_schema.profiling where [email protected]_id group by state order by Total_R desc;

+--------------------------------+------------+-------+-------+----------------+

| state                          | Total_R    | Pct_R | Calls | R/Call         |

+--------------------------------+------------+-------+-------+----------------+

| Copying to tmp table           | 149.779769 | 99.41 |     1 | 149.7797690000 |

| statistics                     |   0.893111 |  0.59 |     1 |   0.8931110000 |

| System lock                    |   0.000117 |  0.00 |     2 |   0.0000585000 |

| checking query cache for query |   0.000090 |  0.00 |     1 |   0.0000900000 |

| Opening tables                 |   0.000062 |  0.00 |     1 |   0.0000620000 |

| removing tmp table             |   0.000051 |  0.00 |     1 |   0.0000510000 |

| freeing items                  |   0.000049 |  0.00 |     1 |   0.0000490000 |

| init                           |   0.000048 |  0.00 |     1 |   0.0000480000 |

| Creating tmp table             |   0.000044 |  0.00 |     1 |   0.0000440000 |

| optimizing                     |   0.000039 |  0.00 |     1 |   0.0000390000 |

| preparing                      |   0.000039 |  0.00 |     1 |   0.0000390000 |

| closing tables                 |   0.000036 |  0.00 |     1 |   0.0000360000 |

| Sorting result                 |   0.000022 |  0.00 |     1 |   0.0000220000 |

| starting                       |   0.000020 |  0.00 |     1 |   0.0000200000 |

| Sending data                   |   0.000016 |  0.00 |     1 |   0.0000160000 |

| Opening table                  |   0.000013 |  0.00 |     1 |   0.0000130000 |

| logging slow query             |   0.000011 |  0.00 |     2 |   0.0000055000 |

| checking permissions           |   0.000008 |  0.00 |     1 |   0.0000080000 |

| end                            |   0.000006 |  0.00 |     2 |   0.0000030000 |

| Waiting on query cache mutex   |   0.000006 |  0.00 |     2 |   0.0000030000 |

| query end                      |   0.000004 |  0.00 |     1 |   0.0000040000 |

| cleaning up                    |   0.000002 |  0.00 |     1 |   0.0000020000 |

| executing                      |   0.000002 |  0.00 |     1 |   0.0000020000 |

| Waiting for query cache lock   |   0.000002 |  0.00 |     2 |   0.0000010000 |

+--------------------------------+------------+-------+-------+----------------+

mysql> set profiling=0;

原文：http://quenlang.blog.51cto.com/4813803/1734272