MySQL common functions and common query statements

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.