mysql 常用函数以及常见查询语句

321ed

MySQL 常用函数
1、数据库中取昨天的日期

1 2 3 4 5 6 7

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、数据库中取明天的日期

1 2 3 4 5 6 7

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和整形之间相互转化

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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、日期转化为UNIXTIME

1 2 3 4 5 6 7

mysql> 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转化为日期

1 2 3 4 5 6 7

mysql> select from_unixtime(1452568943); +---------------------------+ | from_unixtime(1452568943) | +---------------------------+ | 2016-01-12 11:22:23       | +---------------------------+ 1 row in set (0.00 sec)

MySQL 常用查询语句
1、csv文件导出导入

1 2 3 4

## 将数据导出成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的执行过程

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

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 < '2015-06-03 09:00:00' and error_code in (  904  )  and request_url_id = -859289307 and mobile_app_id = 6589  group by mobile_app_version_id order by error_count desc; mysql> show profiles; mysql> select state,sum(duration) as Total_R, round(100*sum(duration)/(select sum(duration) from information_schema.profiling where query_id=@query_id),2) as Pct_R, count(*) as Calls, sum(duration)/count(*) as "R/Call" from information_schema.profiling where query_id=@query_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;