限制查詢
一、邏輯比較操作符
=? >?<? >=? <=
不等于:三個(gè)都表示不等于的意思(經(jīng)常用的是!=)
?!=?? <>??^=
1.查看員工工資小于1000的員工id和名字
select id,first_name||'*'||last_name? all_name
from s_emp
where salary<1000;
2. 查詢名字Ngao的員工信息
select*
from s_emp
where last_name = 'Ngao'
3.查詢不是41號部門的員工信息
select * from s_emp where dept_id != 41;
二、比較操作符
◆ BETWEEN ...AND
查看員工工資在700到 1500之間的員工id，和名字
select id,first_name||'*'||last_name all_name,salary
from s_emp
where salary between 700 and 1500;
◆IN(list)
1.查看員工號1,3,5,7,9員工的工資
select id,salary from s_emp where id in (1,3,5,7,9);
2.查看名字為Ngao或者Smith的員工信息
select * from s_emp where last_name in ('Ngao','Smith');
◆LIKE??
1.查看員工名字以C字母開頭的員工的id，工資
select id,salary
from s_emp
where last_name like 'C%';
2.查看員工名字長度不小于5，且第四個(gè)字母為n的員工id和工資
select id,salary,last_name
from s_emp
where last_name like '___n_%';
3.查看員工名字中包含一個(gè)_的員工id和工資
select id,salary,last_name from s_emp where last_name like '%\_%' escape\;
4. 查詢名字以n結(jié)尾的員工信息
select * from s_emp where last_name like '%n';
5. 查詢名字中包含a的員工信息
select * from s_emp where last_name like '%a%';
6.查詢第二個(gè)字母為a的員工信息
select * from s_emp where last_name like '_a%';
7.查詢到數(shù)第三個(gè)字母為o的員工信息
select * from s_emp where last_name like '%o__';
◆IS? NULL
查看員工提成(commission_pct)為空的員工的id和名字
select id,last_name,commission_pct
from s_emp
where commission_pct is null;
三、邏輯操作符
? and：且邏輯
? or： 或邏輯
?注意：and優(yōu)先級比or優(yōu)先級要高
? not：非邏輯
1.查看員工部門id為41且職位名稱為Stock Clerk(存庫管理員)的員工id和名字（字符串單引號包裹起來）
select id,last_name,dept_id,title
from s_emp
where dept_id = 41 and title = 'Stock
Clerk';
2.查看(員工部門為41) 或者 (44號部門 且工資大于1000的)員工信息
select * from s_emp where dept_id
= 41 or (dept_id
= 44 and salary
> 1000);
3.查看（員工部門為41或44）且(工資大于1000的)員工信息
select * from s_emp where (dept_id
= 41 or dept_id =44) and salary
> 1000;
4.查看員工提成不為空的員工信息
select * from s_emp where commission_pctis not null;
5.查看員工名字不是以C字母開頭的員工信息。???
select * from s_emp where last_name not like 'C%';
