[Linux]Mysql 的几个常用命令

<h3 class="title pre fs1" style="overflow-x: hidden; overflow-y: hidden; text-align: left; word-wrap: break-word; word-break: break-all; font-size: 14px; margin-top: 30px; margin-right: 0px; margin-bottom: 20px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; line-height: 24px; font-family: Arial, Helvetica, simsun, u5b8bu4f53; "><span class="tcnt" style="font-size: 20px; font-family: 微软雅黑, 黑体, Arial, Helvetica, sans-serif; ">mysql 值被包含于字段 更精确于like 查询</span>&nbsp; ：</h3><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; line-height: 25px; text-align: left; ">select name from temp where instr(str,1)&gt;0</span><br /></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; line-height: 25px; text-align: left; "><span style="line-height: 25px; color: rgb(51, 153, 102); font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; text-align: left; "><strong><span style="color:#ff0000;">select&nbsp; name from temp where find_in_set(1,str);</span></strong></span><br /></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; line-height: 25px; text-align: left; "><span style="line-height: 25px; color: rgb(51, 153, 102); font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; text-align: left; "><strong><span style="color:#ff0000;"><br /></span></strong></span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; line-height: 25px; text-align: left; "><span style="line-height: 25px; font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; text-align: left; "></span></span><h2 class="title content-title" style="margin-top: 0px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; font-size: 20px; color: rgb(58, 58, 58); font-family: Tahoma, Arial, Helvetica, STHeiti; "><span style="color: rgb(51, 51, 51); font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; ">MySQL批量替换指定字段字符串语句</span><br /></h2><div style="color: rgb(255, 0, 0); font-weight: bold; "><span style="color: rgb(51, 51, 51); font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; "><span style="color: rgb(51, 51, 51); font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); ">UPDATE&nbsp;数据表名 SET</span><br style="color: rgb(51, 51, 51); font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); " /><span style="color: rgb(51, 51, 51); font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); ">字段名&nbsp;= replace(字段名, '要替换的字符串', '替换为')</span><br style="color: rgb(51, 51, 51); font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); " /><span style="color: rgb(51, 51, 51); font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); ">WHERE 设定条件;</span><br /></span></div><div style="color: rgb(255, 0, 0); font-weight: bold; "><span style="color: rgb(51, 51, 51); font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; "><span style="color: rgb(51, 51, 51); font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); "><br /></span></span></div><div style="font-weight: bold; "><span style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; "><span style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); "><span style="color:#ff0000;"><span style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); ">UPDATE wp_posts SET</span><br style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); " /><span style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); ">post_content = replace(post_content, '搜索引擎优化', '搜索引擎营销')</span><br style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); " /><span style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); ">WHERE ID &lt; 200;</span></span><br /></span></span></div><div style="color: rgb(255, 0, 0); font-weight: bold; "><span style="color: rgb(51, 51, 51); font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; "><span style="color: rgb(51, 51, 51); font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 20px; background-color: rgb(248, 247, 241); "><br /></span></span></div><h2 class="title content-title" style="margin-top: 0px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; font-size: 20px; color: rgb(58, 58, 58); font-weight: normal; font-family: Tahoma, Arial, Helvetica, STHeiti; ">mysql 关键词检索</h2><strong>假如我现在有一组关键词：good ;&nbsp; look&nbsp;</strong><br style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; " /><br style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; " /><strong>现在我检索一个关键词为 go ，那么按照上面的写法，毫无疑问，good 也会被检索出来，这并不是我期望的，</strong><br style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; " /><br style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; " /><strong>我想是不是应该用什么办法把关键词拆分为数组，然后再用 in_array 来查询呢？ 但是mysql并没用数组类型，也没用办法使用诸如php 的 explode 和 split 方法，所以困扰了一阵子，老大解决的办法是：把关键词两边连上 两个分号，然后再把查询的值两边链接上分号，再用LIKE</strong><br style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; " /><br style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; " /><strong>就像下面这样：</strong><br style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; " /><br style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; " /><strong>&quot;SELECT * FROM article WHERE CONCAT( ';'&nbsp; ,&nbsp; keyword&nbsp; ,&nbsp; ';')&nbsp; LIKE '%;$keyword;%' &quot;;</strong><br style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; " /><br style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; " /><strong>这种方法很巧妙，因为这么做了以后就变成：</strong><br style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; " /><br style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; " /><strong>;good;look;</strong><br /></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; line-height: 25px; text-align: left; "><span style="line-height: 25px; color: rgb(51, 153, 102); font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; text-align: left; "><strong><span style="color:#ff0000;"><span style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; ">--------------------------------------------------------------------------</span></span></strong></span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; line-height: 25px; text-align: left; "><span style="line-height: 25px; color: rgb(51, 153, 102); font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; text-align: left; "><span style="font-weight: bold;"><span style="color:#ff0000;"><span style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; "><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">mysql&nbsp;&nbsp;怎么查询字段内容包含 abc 内容。</span><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">例如 ： 456789abcdefkkllseriwerj&nbsp;&nbsp;</span><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">1.查询出abc 并显示.</span><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">2. 删除abc，</span><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">3.在abc前加一个回车符号&lt;br/&gt;&nbsp;&nbsp;请教了。感谢 ！！</span><br /></span></span></span></span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; line-height: 25px; text-align: left; "><span style="line-height: 25px; color: rgb(51, 153, 102); font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; text-align: left; "><span style="font-weight: bold;"><span style="color:#ff0000;"><span style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; "><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; "><br /></span></span></span></span></span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; line-height: 25px; text-align: left; "><span style="line-height: 25px; color: rgb(51, 153, 102); font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; text-align: left; "><span style="font-weight: bold;"><span style="color:#ff0000;"><span style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; "><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; "><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">1.查询出abc 并显示.</span><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">SQL code</span><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">&nbsp; &nbsp; select * from table1 where 字段 like '%abc%';</span><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">2. 删除abc，</span><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">SQL code</span><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">&nbsp; &nbsp; update table1 set 字段=update(字段,'abc','') where 字段 like '%abc%';</span><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">3.在abc前加一个回车符号&lt;br/&gt; 请教了。感谢 ！！</span><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">SQL code</span><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><br style="word-wrap: break-word; color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; " /><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; ">&nbsp; &nbsp; update table1 set 字段=update(字段,'abc','\nabc') where 字段 like '%abc%';</span><br /></span></span></span></span></span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; line-height: 25px; text-align: left; "><span style="line-height: 25px; color: rgb(51, 153, 102); font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; text-align: left; "><span style="font-weight: bold;"><span style="color:#ff0000;"><span style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; "><span style="color: rgb(68, 68, 68); font-family: Tahoma, 'Microsoft Yahei', Simsun; font-size: 14px; line-height: 21px; "><br /></span></span></span></span></span></span></div><div><span style="font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; line-height: 25px; text-align: left; "><span style="line-height: 25px; color: rgb(51, 153, 102); font-family: Arial, Helvetica, simsun, u5b8bu4f53; font-size: 14px; text-align: left; "><strong><span style="color:#ff0000;"><span style="color: rgb(69, 69, 69); font-family: Tahoma, Arial, Helvetica, STHeiti; font-size: 14px; line-height: 25px; "><br /></span></span></strong></span></span></div>