[Mysql 查询语句]——查询指定记录

#比较

等于
select * from orders where order_num = 2006;
大于
select * from orders where order_num > 2006;
小于
select * from orders where cust_id < 10003;
小于或等于
select * from orders where cust_id <= 10003;
大于或等于
select * from orders where cust_id >= 10003;
不等于
select * from orders where cust_id != 10003;    
排除掉
select * from orders where cust_id <> 10003;

 

#指定范围查询 BETWEEN IN

select cust_id from orders where cust_id between     10004 and 10005;
select cust_id from orders where cust_id not between 10004 and 10005;

 

#指定集合查询 IN

select cust_id  from orders  where cust_id in    (10003,10004);
select cust_id  from orders  where cust_id not in(10003,10004);

集合元素可以是字符串类型
select *        from student where name    in    ('taeyeon','jessica');  

 

#匹配字符查询 LIKE

like中可以使用通配符(%)和(_)
%表示匹配0个或多个字符
_表示匹配1个字符
select * from employee where name like     "Jelly";
select * from employee where name not like "Jelly";
select * from employee where name like     "J%y";
select * from employee where name like     "K_y";
select * from employee where homeaddr like "北京%";

#查询空值

select * from vendors where vend_state is     null;
select * from vendors where vend_state is not null;

 

#带AND|OR的多条件查询

select * from employee WHERE age=26  AND sex like '男';
select * from employee WHERE age=26  OR  sex like '男';
select * from employee WHERE id<1005 AND age<26 AND sex='男';
select * from employee WHERE id IN (1001,1005) AND age BETWEEN 20 and 26;