http://www.gbtags.com/gb/share/2351.htm MYSQL作为最成功的开源关系型数据库之一，拥有大批的粉丝（本人也是），在这篇文章中，我们精心收集了10个最实用的mysql查询技巧，希望能够带给大家惊喜，如果大家也有非常不错的SQL，请留言与我们分享！ SQL，MYSQ
http://www.gbtags.com/gb/share/2351.htm
MYSQL作为最成功的开源关系型数据库之一，拥有大批的粉丝（本人也是），在这篇文章中，我们精心收集了10个最实用的mysql查询技巧，希望能够带给大家惊喜，如果大家也有非常不错的SQL，请留言与我们分享！
SQL ，MYSQL
<p></p><p></p><ol> <li> <span>SELECT id</span><span>,</span><span>title</span><span>,</span> </li> <li> <span>(</span><span>CASE date WHEN </span><span>'0000-00-00'</span><span> THEN </span><span>''</span><span> ELSE date </span><span>END</span><span>)</span><span> AS date</span> </li> <li><span> FROM your_table</span></li> <li> <span>SELECT id</span><span>,</span><span>title</span><span>,</span> </li> <li> <span>(</span><span>CASE status WHEN </span><span>0</span><span> THEN </span><span>'open'</span><span> WHEN </span><span>1</span><span> THEN </span><span>'close'</span><span> ELSE </span><span>'standby'</span><span>END</span><span>)</span><span> AS status</span> </li> <li><span> FROM your_table</span></li> </ol><p></p><p></p><p></p>
<p></p><p></p><ol> <li> <span>SELECT email</span><span>,</span><span> COUNT</span><span>(</span><span>email</span><span>)</span><span> AS q</span> </li> <li> <span> FROM emails_table GROUP BY email HAVING q </span><span>></span><span>1</span> </li> <li><span> ORDER BY q DESC</span></li> </ol><p></p><p></p><p></p>
<p></p><p></p><ol><li> <span>SELECT </span><span>*</span><span> FROM your_table ORDER BY RAND</span><span>()</span> </li></ol><p></p><p></p><p></p>
<p></p><p></p><ol> <li><span>UPDATE your_table</span></li> <li> <span> SET name</span><span>=</span><span>REPLACE</span><span>(</span><span>name</span><span>,</span><span>'gbin1.com'</span><span>,</span><span>'gbtags.com'</span><span>)</span> </li> <li> <span> WHERE name LIKE </span><span>'%John%'</span><span>;</span> </li> </ol><p></p><p></p><p></p>
<p></p><p></p><ol><li> <span>ALTER TABLE your_table AUTO_INCREMENT </span><span>=</span><span>2</span> </li></ol>
下一次你插入数据，那么id自动增加为2
<p></p><p></p><ol> <li> <span>set</span><span>@N</span><span>=</span><span>0</span><span>;</span> </li> <li> <span>SELECT </span><span>@N</span><span>:=</span><span>@N</span><span>+</span><span>1</span><span> AS number</span><span>,</span><span> name</span><span>,</span><span> surname FROM gbtags_users</span><span>;</span> </li> </ol><p></p><p></p><p></p>
<p></p><p></p><ol><li> <span>SELECT CONCAT</span><span>(</span><span>name</span><span>,</span><span>' '</span><span>,</span><span>surname</span><span>)</span><span> AS complete_name FROM users</span> </li></ol>
<p></p><p></p><ol> <li> <span>SELECT id</span><span>,</span><span>title</span><span>,</span><span> YEAR</span><span>(</span><span>date_field</span><span>)</span><span> FROM your_table</span> </li> <li> <span>SELECT id</span><span>,</span><span>title</span><span>,</span> </li> <li> <span> CONCAT</span><span>(</span><span>MONTH</span><span>(</span><span>date_field</span><span>),</span><span>'/'</span><span>,</span><span>YEAR</span><span>(</span><span>date_field</span><span>))</span><span>as</span><span> new_date</span> </li> <li><span> FROM your_table</span></li> </ol>
上面语句，第一个select取出了年份，第二取出了月份和年份组合
<p></p><p></p><ol><li> <span>INSERT IGNORE INTO tags </span><span>(</span><span>tag</span><span>)</span><span> VALUES </span><span>(</span><span>'good'</span><span>);</span> </li></ol>
这个insert语句可以执行多次，不会报错，重复的插入会被忽略
<p></p><p></p><ol><li> <span>select</span><span>*</span><span>from</span><span> articles </span><span>where</span><span> MATCH</span><span>(</span><span>content_column</span><span>)</span><span> AGAINST </span><span>(</span><span>'music'</span><span>)</span> </li></ol>
你需要首先添加全文搜索索引到指定的列(content_column)。注意如果你表里已经有数据的话，不会创建索引，所以你需要使用一个空的表来执行这个语句
<p></p><p></p><ol> <li> <span>SELECT user</span><span>,</span><span> count</span><span>(*)</span><span> AS logins</span> </li> <li><span> FROM stat_log</span></li> <li> <span> WHERE action</span><span>=</span><span>'LOGIN'</span><span> AND dt_when </span><span>>=</span><span> DATE_ADD</span><span>(</span><span>CURDATE</span><span>(),</span><span> INTERVAL </span><span>-</span><span>1</span><span> MONTH</span><span>)</span> </li> <li><span> GROUP BY user</span></li> </ol><p></p><p></p><p></p>
这个语句能够让你查询字段dt_when一个月以前的所有记录
<p></p><p></p><ol><li> <span>SET NAMES </span><span>'utf8'</span><span>;</span> </li></ol>
请在连接开始后执行这个语句
<p></p><p></p><ol> <li> <span>INSERT INTO yourtable </span><span>(</span><span>field1</span><span>,</span><span>field2</span><span>,</span><span>field3</span><span>)</span> </li> <li> <span> SELECT newfield1</span><span>,</span><span>newfield2</span><span>,</span><span>'fixed value'</span> </li> <li><span> FROM yourtable2</span></li> </ol><p></p><p></p><p></p>
这个语句能够快速的帮助大家快速的添加一个表中的特定内容到另外一个表中。
希望这里我们介绍的这些SQL能够帮助大家更好更快的使用Mysql，如果大家也有很多自己保存的SQL，请大家给我们留言！
