MySQL Common functions
1, the database to take yesterday's date
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, the database to take tomorrow's date
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. Transformation between IPV4 and shaping
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. Date conversion to Unixtime
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 conversion to date
Mysql> Select From_unixtime (1452568943); +---------------------------+| From_unixtime (1452568943) |+---------------------------+| 2016-01-12 11:22:23 |+---------------------------+1 row in Set (0.00 sec)
MySQL Common Query statements
1. csv file Export Import
# # Export Data to Csvselect * from myID to outfile '/tmp/test.sql '; # # import CSV into a table load data local infile '/tmp/test.sql ' ignore into Table nl_u_mobile_uri_test fields terminated by ' \ t ' lines terminated by ' \ n ';
2, the use of profile analysis of SQL execution process
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 [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;
This article from "Brave forward, resolutely left" blog, please be sure to keep this source http://quenlang.blog.51cto.com/4813803/1734272
MySQL common functions and common query statements