第二章 Oracle过滤和排序数据

一、在查询中过滤行(where)

select employee_id,last_name,salary
  from employees
  where employee_id  > 200

操作符	含义
=	等于（不是==）
>	大于
>=	大于、等于
<	小于
<=	小于、等于
<>	不等于（也可以是！=）

赋值使用：=符号

between…and…包含边界值

select last_name,hire_date,salary
	from employees
	where salary>=4000 and salary<=7000;

select last_name,hire_date,salary
	from employees
	where salary between 4000 and 7000;

in取括号中的几个值（离散值）

select last_name,hire_date,salary,department_id
	from employees
	where department_id =90 or department_id =80 or department_id=70

select last_name,hire_date,salary,department_id
	from employees
	where department_id in (70,80,90);

like模糊查询

select last_name,hire_date,salary,department_id
	from employees
	where last_name like '%a%';
--like
--员工中名字含有字符a的员工

select last_name,hire_date,salary,department_id
	from employees
	where last_name like '%a';
--员工中名字末位是a的员工

select last_name,hire_date,salary,department_id
	from employees
	where last_name like '%\_%' escape '\';
--员工中名字含有_的员工

null 空

select last_name,hire_date,salary,department_id,commission_pct
	from employees
	where commission_pct is null;

1.逻辑运算

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

select last_name,hire_date,salary,department_id,commission_pct
	from employees
	where department_id = 80 and salary <=8000;

2.优先级

优先级
1	算法运算符
2	连接符
3	比较符
4	IS (NOT) NULL,LIKE,(NOT) IN
5	(NOT) BETWEEN
6	NOT
7	AND
8	OR

可以使用括号改变优先级顺序

二、在查询中对行进行排序

select last_name,hire_date,salary,department_id,commission_pct
from employees
	where department_id = 80 
	order by salary desc
--desc降序 asc升序，默认为desc

select last_name,hire_date,salary,department_id,commission_pct
	from employees
	where department_id = 80 
	order by salary asc，last_name asc;
--根据工资降序排序，工资相同时按照工资排序

总结

1.使用where子句过滤数据

• 使用比较运算
• 使用between and，in，like，null运算
• 使用逻辑运算符AND,OR和NOT

2.使用ORDER BY子句进行排序