mysql 常用函数以及常见查询语句
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql 常用函数以及常见查询语句,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4284字,纯文字阅读大概需要7分钟。
内容图文
![mysql 常用函数以及常见查询语句](/upload/InfoBanner/zyjiaocheng/1213/649ca32479a74a30a2835b9c31ce1932.jpg)
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、日期转化为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转化为日期
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 < ‘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;
本文出自 “勇敢向前,坚决向左” 博客,请务必保留此出处http://quenlang.blog.51cto.com/4813803/1734272
原文:http://quenlang.blog.51cto.com/4813803/1734272
内容总结
以上是互联网集市为您收集整理的mysql 常用函数以及常见查询语句全部内容,希望文章能够帮你解决mysql 常用函数以及常见查询语句所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。