Mysql常用語句
select *from db_user where user_type in(5,8);
select *from db_user where user_type between(5,8);
select *from db_user where user_name like '%s%';<span style="font-family: Arial, Helvetica, sans-serif;">其中"%"可以匹配一個或多個字符</span>
select *from db_user where user_name like '_3_';<span style="font-family: Arial, Helvetica, sans-serif;">“_”匹配一個字符</span>
select *from db_user where user_name=a or password=3;//or關鍵字，匹配一個就行
select *from db_user order by id desc;
<span style="font-family: Arial, Helvetica, sans-serif;"></span><pre name="code" class="sql">select *from db_user order by id asc;//升序
select id,user_name,password group by user_type;//每組只顯示一條記錄
select *from db_user order by id asc limit 3;//限制只顯示三條記錄
select *from db_user order by id asc limit 1,2.從編號一開始，查詢兩條記錄
<p>select count(*) from db_user;//顯示所有行的數目</p><p>select *from user_name ,user_type from tp_user,db_user here tp_user.id = db_user.id;//內連接查詢，連接兩個表</p><p>select *from tp_user where user_name in(select user_name from db_user);//查詢tp_user中與db_user的user_name相同的記錄</p><p>select *from tp_user where money >=(select money from db_user where id=1);//比較運算符的查詢方式</p><p>select *from tp_user where user_name like '%php%';//查詢包含php字段的記錄</p><p>select Max(score) from tp_user;//查詢socre最高的記錄 </p><p>select *from tp_user where money<ANY(select money from db_user);//查詢money小於所有db_user的記錄，其中括號裡的部分稱為內查詢，外面的稱外查詢</p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select *from tp_user where money>ALL(select money from db_user);//money大於所有的db_user</span>
</p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select user_name from tp_user UNION select user_name from db_user;//合並重復的user_name</span></p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select user_name from tp_user UNION ALL select user_name from db_user;//列出所有的結果，不合並</span>
</span></p><p>//正則表達式</p><p>select books from tp_book where books REGEXP '^php' //^ 匹配以特定開頭或字符串開頭的記錄</p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select books from tp_book where books REGEXP 'php$';//$匹配以特定結尾的記錄</span>
</p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select books from tp_book where books REGEXP '.p';//.匹配字符集和中任意一個字符,包括換行和回車</span>
</span></p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select books from tp_book where books REGEXP '[pca]';//[pca]匹配包含pca的記錄</span>
</span></span></p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select books from tp_book where books REGEXP '[^c-z]';//[^]匹配除字符集合以外的任何一個字符</span>
</p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select books from tp_book where books REGEXP 'php|java|c#';//|相當於or匹配任意一個字符串</span>
</span></p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select books from tp_book where books REGEXP 'J*A';//匹配A之前出現過J字符的記錄，*可以表示0個</span>
</span></span></p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select books from tp_book where books REGEXP 'J+A';//匹配A之前至少出現過一次J的記錄，+至少表示1個</span>
</span></span></span></p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select books from tp_book where books REGEXP 'a{3}';//匹配a出現至少三次的記錄</span>
</span></span></span></span></p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select books from tp_book where books REGEXP 'a{2,4};//匹配a字符至少出現2次至多出現4次的記錄</span>
</p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">
</span></span></span></span></span></p><p><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);"><span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">
</span></span></p>
</pre><pre>
