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>

Mysql常用语句

标签：mysql