参考菜鸟教程
=
select * from `users` where `id`=1;
<>|!=(不等于)
select * from `users` where `id`!=1;
BETWEEN (两值之间)
select * from `users` where `id` BETWEEN 2 and 4;
not BETWEEN (不在两值之间)
select * from `users` where `id` not between 2 and 4;
in (在集合中)
select * from `users` where `id` in (1,3,5);
not in (不在集合中)
select * from `users` where `id` not in (1,3,5);
<=> (比较两个值是否严格相等)
LIKE (模糊查询)
select * from `users` where `username` like 'admin%';
select * from `users` where `username` like '%666';
_占一位
%占0位或一位或多位
模糊查询应该注意一下几点:
1,使用模糊查询会导致索引失效,如果不以%和_开头查询的话,索引还是有效的
2,当查询的的条件和查询的结果都是索引中的字段的时候,这个索引我们可以称之为覆盖索引,这个时候,使用like模糊查询索引是有效的(注意:使用覆盖索引,对于字段的长度是由要求限制的,一般超过长度,索引也会失效)
3,可以使用全文索引替代like模糊查询
4,使用一些额外的全文搜索引擎来解决。Lucene,solr,elasticsearch等等
REGEXP 或 RLIKE 正则式匹配
sql语句执行顺序
- FROM, including JOINs
- WHERE
- GROUP BY
- HAVING
- WINDOW functions
- SELECT
- DISTINCT
- UNION
- ORDER BY
- LIMIT and OFFSET