# mysql 语句整理一 # 本例返回 "Persons" 表中的行数： ``` <pre class="calibre13">``` <span class="token5">SELECT</span> <span class="token5">COUNT</span><span class="token1">(</span><span class="token">*</span><span class="token1">)</span> <span class="token5">FROM</span> Persons ``` ``` **返回大于 20 岁的人数：** ``` <pre class="calibre13">``` <span class="token5">SELECT</span> <span class="token5">COUNT</span><span class="token1">(</span><span class="token">*</span><span class="token1">)</span> <span class="token5">FROM</span> Persons <span class="token5">WHERE</span> Age<span class="token">></span><span class="token5">20</span> ``` ``` # case when then end的用法 相当于if判断 ``` <pre class="calibre13">``` select id<span class="token1">,</span>title<span class="token1">,</span>status<span class="token1">,</span><span class="token2">case</span> status when <span class="token5">0</span> then <span class="token4">'待修复'</span> when <span class="token5">1</span> then <span class="token4">'修复中'</span> when <span class="token5">2</span> then <span class="token4">'已完成'</span> when <span class="token5">3</span> then <span class="token4">'已取消'</span> end <span class="token2">as</span> status2<span class="token1">,</span><span class="token5">DATE_FORMAT</span><span class="token1">(</span>updateTime<span class="token1">,</span><span class="token4">'%Y-%m-%d %H:%i:%s'</span><span class="token1">)</span> <span class="token2">as</span> updateTime2<span class="token1">,</span><span class="token5">DATE_FORMAT</span><span class="token1">(</span>createTime<span class="token1">,</span><span class="token4">'%Y-%m-%d %H:%i:%s'</span><span class="token1">)</span> <span class="token2">as</span> createTime2<span class="token1">,</span>submitter <span class="token2">from</span> bug_list where isDelete<span class="token">=</span><span class="token5">0</span> `<span class="token1">;</span> ``` ``` **limit用法** ``` <pre class="calibre13">``` <span class="token5">1.</span> mysql<span class="token">></span> <span class="token5">SELECT</span> \<span class="token">*</span> <span class="token5">FROM</span> table <span class="token5">LIMIT</span> <span class="token5">5</span><span class="token1">,</span><span class="token5">10</span><span class="token1">;</span> <span class="token6">// 检索记录行 6-15 </span> <span class="token5">3.</span> <span class="token6">//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： </span> <span class="token5">4.</span> mysql<span class="token">></span> <span class="token5">SELECT</span> \<span class="token">*</span> <span class="token5">FROM</span> table <span class="token5">LIMIT</span> <span class="token5">95</span><span class="token1">,</span><span class="token">-</span><span class="token5">1</span><span class="token1">;</span> <span class="token6">// 检索记录行 96-last. </span> <span class="token5">6.</span> <span class="token6">//如果只给定一个参数，它表示返回最大的记录行数目： </span> <span class="token5">7.</span> mysql<span class="token">></span> <span class="token5">SELECT</span> \<span class="token">*</span> <span class="token5">FROM</span> table <span class="token5">LIMIT</span> <span class="token5">5</span><span class="token1">;</span> <span class="token6">//检索前 5 个记录行 </span> <span class="token5">9.</span> <span class="token6">//换句话说，LIMIT n 等价于 LIMIT 0,n。</span> ``` ``` **使用limit实现分页** pageno: 1 代表页码 pagesize: 20 每页显示数量20 当第一页的时候 显示1-20数量 第二页的时候显示21-40数量： ``` <pre class="calibre13">``` 思路如下 不是具体的sql语句： <span class="token3">limit</span><span class="token1">(</span>pageno<span class="token">-</span><span class="token5">1</span><span class="token1">)</span><span class="token">*</span>pagesize<span class="token1">,</span>pagesize ``` ``` **data\_format的基本用法** ``` <pre class="calibre13">``` select <span class="token">*</span><span class="token1">,</span><span class="token5">DATE_FORMAT</span><span class="token1">(</span>create_time<span class="token1">,</span><span class="token4">'%Y-%m-%d %H:%i:%S'</span><span class="token1">)</span> <span class="token2">as</span> create_time2 <span class="token2">from</span> sys_role where is_delete<span class="token">=</span><span class="token5">0</span> ``` ```  \#3 case whern then  \#3 between and  # substrs substring