oracle查询第三个字母是A的员工,oracle_day2 限制查询

限制查询

一、逻辑比较操作符

=  > =  <=

不等于:三个都表示不等于的意思(经常用的是!=)

✓!=   <>  ^=

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%';