#比較
等於
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;