限制查询
一、逻辑比较操作符
= > < >= <=
不等于:三个都表示不等于的意思(经常用的是!=)
✓!= <> ^=
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，且第四个字母为n的员工id和工资
select id,salary,last_name
from s_emp
where last_name like '___n_%';
3.查看员工名字中包含一个_的员工id和工资
select id,salary,last_name from s_emp where last_name like '%\_%' escape\;
4. 查询名字以n结尾的员工信息
select * from s_emp where last_name like '%n';
5. 查询名字中包含a的员工信息
select * from s_emp where last_name like '%a%';
6.查询第二个字母为a的员工信息
select * from s_emp where last_name like '_a%';
7.查询到数第三个字母为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优先级比or优先级要高
➢ 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%';
