13个mysql数据库的实用SQL小技巧-Mysql Tutorial-php.cn

13个mysql数据库的实用SQL小技巧

WBOY

Release： 2016-06-07 15:36:06

Original

1415 people have browsed it

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

使用CASE来重新定义数值类型

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

Copy after login

查找重复的email记录字段

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

Copy after login

取出随机顺序得到记录

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

Copy after login

使用update替换指定字段里的字符

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

Copy after login

重设置指定table里的自动增加数值

<p></p><p></p><ol><li>
<span>ALTER TABLE your_table AUTO_INCREMENT </span><span>=</span><span>2</span>
</li></ol>

Copy after login

下一次你插入数据，那么id自动增加为2

为select语句添加一个自动增加的字段列

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

Copy after login

使用concat来连接字段

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

Copy after login

使用date方法来取出日期类型的指定部分

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

Copy after login

上面语句，第一个select取出了年份，第二取出了月份和年份组合

针对unique key(唯一键)类型字段的重复插入不报错的insert语句

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

Copy after login

这个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>

Copy after login

你需要首先添加全文搜索索引到指定的列(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>

Copy after login

这个语句能够让你查询字段dt_when一个月以前的所有记录

设置正确的字符集

<p></p><p></p><ol><li>
<span>SET NAMES </span><span>'utf8'</span><span>;</span>
</li></ol>

Copy after login

请在连接开始后执行这个语句

从一个表插入另外一个表

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

Copy after login

这个语句能够快速的帮助大家快速的添加一个表中的特定内容到另外一个表中。

希望这里我们介绍的这些SQL能够帮助大家更好更快的使用Mysql，如果大家也有很多自己保存的SQL，请大家给我们留言！