{"id":284,"date":"2014-06-30T14:21:47","date_gmt":"2014-06-30T14:21:47","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=284"},"modified":"2014-06-30T14:21:47","modified_gmt":"2014-06-30T14:21:47","slug":"mysql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%9a%84%e5%9f%ba%e6%9c%ac%e6%93%8d%e4%bd%9c-%e5%ad%a6%e4%b9%a0","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/284.html","title":{"rendered":"MySQL\u6570\u636e\u5e93\u7684\u57fa\u672c\u64cd\u4f5c-\u5b66\u4e60"},"content":{"rendered":"<p>\u5728\u547d\u4ee4\u884c\u4e0b\u4ee5root\u7528\u6237\u8fdbMySQL\u6570\u636e\u5e93\u7684\u547d\u4ee4\u5982\u4e0b\uff1a<\/p>\n<p><code>mysql -h127.0.0.1 -P3306 -uroot -p<\/code><\/p>\n<p>\u7136\u540e\u8f93\u5165\u5bc6\u7801\u518d\u6572\u5165\u56de\u8f66\u952e\u8fdb\u5165MySQL\u63a7\u5236\u53f0\u3002<span style=\"color: #ff0000;\"><strong>\u6bcf\u4e00\u6761MySQL\u6570\u636e\u5e93\u67e5\u8be2\u8bed\u53e5\u5747\u8981\u4ee5\u82f1\u6587\u5206\u53f7\u7ed3\u5c3e\u56de\u8f66\u67e5\u8be2\uff08\u6216\\G\uff09\u3002<\/strong><\/span><\/p>\n<p>\u4ee5\u4e0b\u57fa\u7840\u7684MySQL\u547d\u4ee4\u4e2d\uff0c\u8bbe\u8ba1\u4e86\u4e00\u4e2a\u540d\u4e3abash\u7684\u6570\u636e\u5e93\uff0c\u4e00\u4e2acmd_info\u7684\u8868\uff0c\u8868\u4e2d\u5305\u62ec\u4e00\u4e9b\u5217\u540d\u3002\u5176\u4e2d\u4e2d\u62ec\u53f7[]\u5185\u7684\u8868\u793a\u53ef\u9009\u53c2\u6570\u3002<\/p>\n<p>\u5217\u51famysql\u7684\u6240\u6709\u6570\u636e\u5e93\uff1a<br \/>\n<code>show databases;<\/code><\/p>\n<p>\u4f7f\u7528bash\u6570\u636e\u5e93\uff1a<br \/>\n<code>use bash;<\/code><\/p>\n<p>\u5217\u51fabash\u6570\u636e\u5e93\u7684\u6240\u6709\u6570\u636e\u8868\uff1a<br \/>\n<code>show tables [from bash];<\/code><\/p>\n<p>\u5217\u51fabash\u6570\u636e\u5e93\u7684\u6240\u6709\u6570\u636e\u8868\uff08\u63d0\u4f9b\u6bd4\u8f83\u8be6\u7ec6\u7684\u4fe1\u606f\uff09\uff1a<br \/>\n<code>show table status [from bash];<\/code><\/p>\n<p>\u5217\u51fa\u6570\u636e\u8868cmd_info\u6240\u6709\u7684\u5217\uff08\u63d0\u4f9b\u6bd4\u8f83\u8be6\u7ec6\u7684\u4fe1\u606f\uff0c\u7b49\u4ef7\u4e8e\uff1adesc cmd_info\uff09\uff1a<br \/>\n<code>show full columns from cmd_info [from bash];<\/code><br \/>\n\u6216<br \/>\n<code>show full fields from cmd_info [from bash];<\/code><\/p>\n<p>\u67e5\u8be2\u6570\u636e\u8868cmd_info\u7684\u603b\u8bb0\u5f55\u6761\u6570\uff1a<br \/>\n<code>select count(*) from cmd_info;<\/code><\/p>\n<p>\u67e5\u8be2\u6570\u636e\u8868cmd_info\u7684\u6240\u6709\u6570\u636e\uff1a<br \/>\n<code>select * from cmd_info;<\/code><\/p>\n<p>\u67e5\u8be2\u6570\u636e\u8868cmd_info\u7684\u524d10\u6761\u6570\u636e\uff1a<br \/>\n<code>select * from cmd_info limit 10;<\/code><\/p>\n<p>\u5217\u51fa\u6570\u636e\u8868cmd_info\u7684\u7d22\u5f15\u4fe1\u606f\uff1a<br \/>\n<code>show index from cmd_info [from bash];<\/code><\/p>\n<p>\u5217\u51famysql\u7684\u5f53\u524d\u72b6\u6001\uff1a<br \/>\n<code>status;<\/code><br \/>\n<code>show status;<\/code><\/p>\n<p>\u5217\u51famysql\u7cfb\u7edf\u53d8\u91cf\uff1a<br \/>\n<code>show variables;<\/code><\/p>\n<p>\u663e\u793a\u6b63\u5728\u8fd0\u884c\u547d\u4ee4\u7684mysql\u7528\u6237\u6e05\u5355\uff1a<br \/>\n<code>show process list;<\/code><\/p>\n<p>\u9000\u51famysql\u63a7\u5236\u53f0\uff1a<br \/>\n<code>quit<\/code><\/p>\n<p>\u83b7\u53d6\u5f53\u524d\u7528\u6237\u7684\u6743\u9650\u4fe1\u606f\uff1a<br \/>\n<code>show grants [for root@localhost];<\/code><\/p>\n<hr \/>\n<p>\u8bb0\u5f55\u4e00\u4e9bselect\u7684\u6280\u5de7\uff0c\u6bd4\u5982\u5982\u4f55\u7528 in\u3001limit\u3001concat\u3001distinct \u7b49MySQL\u5173\u952e\u5b57\uff1a<\/p>\n<p>1\u3001select\u8bed\u53e5\u53ef\u4ee5\u7528\u56de\u8f66\u5206\u9694<\/p>\n<pre class=\"lang:default decode:true\">$sql=\"select * from article where id=1\"\n\u548c $sql=\"select * from article\nwhere id=1\"\uff0c\u90fd\u53ef\u4ee5\u5f97\u5230\u6b63\u786e\u7684\u7ed3\u679c\uff0c\u4f46\u6709\u65f6\u5206\u5f00\u5199\u6216\u8bb8\u80fd\u66f4\u660e\u4e86\u4e00\u70b9\uff0c\u7279\u522b\u662f\u5f53sql\u8bed\u53e5\u6bd4\u8f83\u957f\u65f6\u3002<\/pre>\n<p>2\u3001\u6279\u91cf\u67e5\u8be2\u6570\u636e<\/p>\n<pre class=\"lang:default decode:true\">\u53ef\u4ee5\u7528in\u6765\u5b9e\u73b0\n$sql=\"select * from article where id in (1,3,5)\"<\/pre>\n<p>3\u3001\u4f7f\u7528concat\u8fde\u63a5\u67e5\u8be2\u7684\u7ed3\u679c<\/p>\n<pre class=\"lang:default decode:true\">$sql=\"select concat(id,\"-\",con) as res from article where id=1\"\n\u8fd4\u56de\"1-article content\"<\/pre>\n<p>4\u3001\u4f7f\u7528locate<\/p>\n<pre class=\"lang:default decode:true\">\u7528\u6cd5\uff1aselect locate(\"hello\",\"hello baby\"); \u5b58\u5728\u8fd4\u56de1\uff0c\u4e0d\u5b58\u5728\u8fd4\u56de0<\/pre>\n<p>5\u3001\u4f7f\u7528group by<\/p>\n<pre class=\"lang:default decode:true\">\u4ee5\u524d\u4e00\u76f4\u6ca1\u600e\u4e48\u641e\u660egroup by \u548c order by\uff0c\u5176\u5b9e\u4e5f\u6ee1\u7b80\u5355\u7684\uff0cgroup by \u662f\u628a\u76f8\u540c\u7684\u7ed3\u679c\u7f16\u4e3a\u4e00\u7ec4\uff0c\u6bd4\u5982\uff1a$sql=\"select city, count(*) from customer group by city\";\n\u8fd9\u53e5\u8bdd\u7684\u610f\u601d\u5c31\u662f\u4ececustomer\u8868\u91cc\u5217\u51fa\u6240\u6709\u4e0d\u91cd\u590d\u7684\u57ce\u5e02\uff0c\u53ca\u5176\u6570\u91cf\uff08\u6709\u70b9\u7c7b\u4f3cdistinct\uff09\ngroup by \u7ecf\u5e38\u4e0e AVG(),MIN(),MAX(),SUM(),COUNT() \u4e00\u8d77\u4f7f\u7528<\/pre>\n<p>6\u3001\u4f7f\u7528having<\/p>\n<pre class=\"lang:default decode:true\">having \u5141\u8bb8\u6709\u6761\u4ef6\u5730\u805a\u5408\u6570\u636e\u4e3a\u7ec4\n$sql=\"select city,count(*),min(birth_day) from customer group by city having count(*)&gt;10\";\n\u8fd9\u53e5\u8bdd\u662f\u5148\u6309city\u5f52\u7ec4\uff0c\u7136\u540e\u627e\u51facity\u5730\u6570\u91cf\u5927\u4e8e10\u7684\u57ce\u5e02\nbtw\uff1a\u4f7f\u7528group by + having \u901f\u5ea6\u6709\u70b9\u6162\n\u540c\u65f6having\u5b50\u53e5\u5305\u542b\u7684\u8868\u8fbe\u5f0f\u5fc5\u987b\u5728\u4e4b\u524d\u51fa\u73b0\u8fc7<\/pre>\n<p>7\u3001\u7ec4\u5408\u5b50\u53e5<\/p>\n<pre class=\"lang:default decode:true\">where\u3001group by\u3001having\u3001order by\uff08\u5982\u679c\u8fd9\u56db\u4e2a\u90fd\u8981\u4f7f\u7528\u7684\u8bdd\uff0c\u4e00\u822c\u6309\u8fd9\u4e2a\u987a\u5e8f\u6392\u5217\uff09<\/pre>\n<p>8\u3001\u4f7f\u7528distinct<\/p>\n<pre class=\"lang:default decode:true\">distinct\u662f\u53bb\u6389\u91cd\u590d\u503c\u7528\u7684\n$sql=\"select distinct(city) from customer order by id desc\";\n\u8fd9\u53e5\u8bdd\u7684\u610f\u601d\u5c31\u662f\u4ececustomer\u8868\u4e2d\u67e5\u8be2\u6240\u6709\u7684\u4e0d\u91cd\u590d\u7684city<\/pre>\n<p>9\u3001\u4f7f\u7528limit<\/p>\n<pre class=\"lang:default decode:true\">\u5982\u679c\u8981\u663e\u793a\u67d0\u6761\u8bb0\u5f55\u4e4b\u540e\u7684\u6240\u6709\u8bb0\u5f55\n$sql=\"select * from article limit 100,-1\";<\/pre>\n<p>10\u3001\u591a\u8868\u67e5\u8be2<\/p>\n<pre class=\"lang:default decode:true\">$sql=\"select user_name from user u, member m\nwhere u.id=m.id and\nm.reg_date&gt;=2006-12-28\norder by u.id desc;\"\n\u6ce8\u610f\uff1a\u5982\u679cuser\u548cmember\u4e24\u4e2a\u8868\u540c\u65f6\u6709user_name\u5b57\u6bb5\uff0c\u4f1a\u51fa\u73b0mysql\u9519\u8bef\uff08\u56e0\u4e3amysql\u4e0d\u77e5\u9053\u4f60\u5230\u5e95\u8981\u67e5\u8be2\u54ea\u4e2a\u8868\u91cc\u7684user_name\uff09\uff0c\u5fc5\u987b\u6307\u660e\u662f\u54ea\u4e2a\u8868\u7684\u3002<\/pre>\n<hr \/>\n<h5>MySQL\u7684limit\u67e5\u8be2\u4f18\u5316<\/h5>\n<p>\u901a\u8fc7\u5177\u4f53\u7684\u6848\u4f8b\u6765\u8bf4\u660e\uff0c\u8fd9\u91cc\u662f\u5bf9\u540c\u4e00\u5f20\u8868\u5728\u4e0d\u540c\u7684\u5730\u65b9\u53d610\u6761\u6570\u636e\uff1a<\/p>\n<p>\uff081\uff09offset\u6bd4\u8f83\u5c0f\u7684\u65f6\u5019<\/p>\n<pre class=\"lang:default decode:true\">select * from user limit 10,10;\n\u8fd9\u6761SQL\u8bed\u53e5\u591a\u6b21\u8fd0\u884c\uff0c\u65f6\u95f4\u4fdd\u6301\u57280.0004-0.0005\u4e4b\u95f4\u3002\n\nselect * From user Where uid &gt;=( select uid From user Order By uid limit 10,1 ) limit 10;\n\u8fd9\u6761SQL\u8bed\u53e5\u591a\u6b21\u8fd0\u884c\uff0c\u65f6\u95f4\u4fdd\u6301\u57280.0005-0.0006\u4e4b\u95f4\uff0c\u4e3b\u8981\u662f0.0006\u3002<\/pre>\n<p>\u7ed3\u8bba\uff1a\u504f\u79fboffset\u8f83\u5c0f\u7684\u65f6\u5019\uff0c\u76f4\u63a5\u4f7f\u7528limit\u8f83\u4f18\u3002\u8fd9\u4e2a\u663e\u7136\u662f\u5b50\u67e5\u8be2\u7684\u539f\u56e0\u3002<\/p>\n<p>\uff082\uff09offset\u5927\u7684\u65f6\u5019<\/p>\n<pre class=\"lang:default decode:true\">select * from user limit 10000,10;\n\u8fd9\u6761SQL\u8bed\u53e5\u591a\u6b21\u8fd0\u884c\uff0c\u65f6\u95f4\u4fdd\u6301\u57280.0187\u5de6\u53f3\n\nselect * From user Where uid &gt;=( select uid From user Order By uid limit 10000,1 ) limit 10;\n\u8fd9\u6761SQL\u8bed\u53e5\u591a\u6b21\u8fd0\u884c\uff0c\u65f6\u95f4\u4fdd\u6301\u57280.0061\u5de6\u53f3\uff0c\u53ea\u6709\u524d\u8005\u76841\/3\u3002\u53ef\u4ee5\u9884\u8ba1offset\u8d8a\u5927\uff0c\u540e\u8005\u8d8a\u4f18\u3002<\/pre>\n<p>\u603b\u7ed3\uff1a\u4f7f\u7528limit\u8bed\u53e5\u65f6\uff0c\u5f53\u6570\u636e\u91cf\u504f\u79fb\u91cf\u8f83\u5c0f\u7684\u65f6\u5019\u53ef\u4ee5\u76f4\u63a5\u4f7f\u7528limit\uff0c\u5f53\u6570\u636e\u91cf\u504f\u79fb\u91cf\u8f83\u5927\u7684\u65f6\u5019\uff0c\u6211\u4eec\u53ef\u4ee5\u9002\u5f53\u7684\u4f7f\u7528\u5b50\u67e5\u8be2\u6765\u505a\u76f8\u5173\u7684\u6027\u80fd\u4f18\u5316\u3002<\/p>\n<p>====<\/p>\n<p>\u4e00\u4e9bMySQL\u8bed\u53e5\uff1a<\/p>\n<pre class=\"lang:default decode:true\">mysql&gt; show variables like '%skip_networking%';\n\nmysql&gt; show variables like 'collation_%';\n\nmysql&gt; show variables like 'char_%';\n\nuse db_name;\nstatus; #\u67e5\u770b\u67d0\u4e2a\u6570\u636e\u5e93\u7684\u7f16\u7801\n\nshow create database db_name; #\u67e5\u770b\u67d0\u4e2a\u6570\u636e\u5e93\u7684\u7f16\u7801\nshow [global] status;\n\nshow variables;\n\nshow engines;\n\nshow grants; #\u67e5\u770b\u6743\u9650\u5206\u914d\u60c5\u51b5\n\nshow columns from db_name.tb_name; &lt;=&gt; desc db_name.tb_name\nshow create table db_name.tb_name\\G #\u6ce8\u610f\u6700\u540e\u7528\u7684\u662f\"\\G\"\u800c\u4e0d\u662f\";\"<\/pre>\n<p>\u5728MySQL\u4e2d\u9700\u8981\u5220\u9664\u8868\u4e2d\u7279\u5b9a\u7684\u8bb0\u5f55\uff0c\u53ef\u7528 WHERE \u5b50\u53e5\u6765\u9009\u62e9\u6240\u8981\u5220\u9664\u7684\u8bb0\u5f55\u3002\u8fd9\u7c7b\u4f3c\u4e8e SELECT \u8bed\u53e5\u4e2d\u7684 WHERE \u5b50\u53e5\u3002<\/p>\n<p><code>mysql&gt; delete from pet where name=\"Whistler\";<\/code><\/p>\n<p>\u53ef\u4ee5\u7528\u4e0b\u9762\u7684\u8bed\u53e5\u6e05\u7a7a\u6574\u4e2a\u8868\uff1a<\/p>\n<p><code>mysql&gt; delete from pet;<\/code><\/p>\n<p>truncate\u547d\u4ee4\u7684\u8bed\u6cd5\u7ed3\u6784\u4e3a\uff1a<\/p>\n<p><code>TRUNCATE [TABLE] tbl_name;<\/code><\/p>\n<p>\u8fd9\u91cc\u7b80\u5355\u7684\u7ed9\u51fa\u4e2a\u793a\u4f8b\uff0c\u6211\u60f3\u5220\u9664 friends \u8868\u4e2d\u6240\u6709\u7684\u8bb0\u5f55\uff0c\u53ef\u4ee5\u4f7f\u7528\u5982\u4e0b\u8bed\u53e5\uff1a<\/p>\n<p><code>truncate table friends;<\/code><\/p>\n<p>truncate \u548c delete\u7684\u6548\u7387\u95ee\u9898\uff1a<\/p>\n<p>\u5982\u679c\u60f3\u8981\u5220\u9664\u8868\u7684\u6240\u6709\u6570\u636e\uff0ctruncate\u8bed\u53e5\u8981\u6bd4 delete \u8bed\u53e5\u5feb\u3002\u56e0\u4e3a truncate \u5220\u9664\u4e86\u8868\uff0c\u7136\u540e\u6839\u636e\u8868\u7ed3\u6784\u91cd\u65b0\u5efa\u7acb\u5b83\uff0c\u800c delete \u5220\u9664\u7684\u662f\u8bb0\u5f55\uff0c\u5e76\u6ca1\u6709\u5c1d\u8bd5\u53bb\u4fee\u6539\u8868\u3002\u8fd9\u4e5f\u662f\u4e3a\u4ec0\u4e48\u5f53\u5411\u4e00\u4e2a\u4f7f\u7528 delete \u6e05\u7a7a\u7684\u8868\u63d2\u5165\u6570\u636e\u65f6\uff0cMySQL \u4f1a\u8bb0\u4f4f\u524d\u9762\u4ea7\u751f\u7684AUTOINCREMENT\u5e8f\u5217\uff0c\u5e76\u4e14\u7ee7\u7eed\u5229\u7528\u5b83\u5bf9AUTOINCREMENT\u5b57\u6bb5\u7f16\u53f7\u3002\u800ctruncate\u5220\u9664\u8868\u540e\uff0c\u8868\u662f\u4ece1\u5f00\u59cb\u4e3aautoincrement\u5b57\u6bb5\u7f16\u53f7\u3002\u4e0d\u8fc7truncate\u547d\u4ee4\u5feb\u662f\u5feb\uff0c\u5374\u4e0d\u50cfdelete\u547d\u4ee4\u90a3\u6837\u5bf9\u4e8b\u52a1\u5904\u7406\u662f\u5b89\u5168\u7684\u3002<\/p>\n<p>====<\/p>\n<pre class=\"lang:default decode:true\">show variables like \"plugin%\"; #\u67e5\u770b\u63d2\u4ef6\u5e93\u8def\u5f84\n\nselect @@basedir;\n\/*\nsystem_user() \u7cfb\u7edf\u7528\u6237\u540d\nuser() \u7528\u6237\u540d\ncurrent_user \u5f53\u524d\u7528\u6237\u540d\nsession_user()\u8fde\u63a5\u6570\u636e\u5e93\u7684\u7528\u6237\u540d\ndatabase() \u6570\u636e\u5e93\u540d\nversion() MYSQL\u6570\u636e\u5e93\u7248\u672c\nload_file() MYSQL\u8bfb\u53d6\u672c\u5730\u6587\u4ef6\u7684\u51fd\u6570\n@@datadir \u8bfb\u53d6\u6570\u636e\u5e93\u8def\u5f84\n@@basedir MYSQL \u5b89\u88c5\u8def\u5f84\n@@version_compile_os \u64cd\u4f5c\u7cfb\u7edf\n*\/\n\nselect Host,User,Password from mysql.user;\n\nselect * from mysql.func;<\/pre>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"http:\/\/onebash.com\/16.html\" target=\"_blank\">mysql\u6570\u636e\u5e93\u7684\u57fa\u672c\u64cd\u4f5c\u547d\u4ee4<\/a><\/li>\n<\/ul>\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5728\u547d\u4ee4\u884c\u4e0b\u4ee5root\u7528\u6237\u8fdbMySQL\u6570\u636e\u5e93\u7684\u547d\u4ee4\u5982\u4e0b\uff1a mysql -h127.0.0.1 -P3306 -u [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,6,12],"tags":[16],"class_list":["post-284","post","type-post","status-publish","format-standard","hentry","category-database","category-other","category-tools","tag-mysql"],"views":5541,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/284","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=284"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/284\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}