# 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