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子句进行排序
