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