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 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;