oracle like in 结合查询_mysql查询操作符(and,or,all,in,like,is null,between and)

表信息

1 用 and 操作符查询s_id为101并且f_id为a1的水果记录 (*)

select * from fruits 
where s_id = 101 and f_id = 'a1';

2 -- 用 or 操作符查询苹果或者橙子的相关记录 (*)

select * from fruits
where f_name = 'fruit_orange' or f_name = 'fruit_apple';

3 -- 用 in 操作符查询苹果和橙子的相关记录 (***)

select * from fruits 
where f_name in ('fruit_apple','fruit_orange');

4 -- 用 not in 操作符查询苹果和橙子之外的水果的相关记录 (***)

select * from fruits
where f_name not in ('fruit_apple','fruit_orange');

5 -- 用 between...and 操作符查询f_price在10元到20元之间的水果记录 (*)

select * from fruits
where f_price between 10 and 20;

6 -- 用 like 操作符查询所有f_name含a的水果记录 (***)

select * from fruits
where f_name like '%a%';

7 -- 用 like 操作符查询所有f_id由b开始且字符长度为两位的水果记录 (**)

select * from fruits 
where f_id like 'b%' and length(f_id) = 2;

8 -- 用 is null 操作符查询所有f_name为空的水果记录 (*)

select * from fruits
where f_name is null;

9-- 用 distinct 查询fruits表中所有不重复的s_id (***)

 select distinct s_id from fruits;