过滤和排序数据

字符和日期

• 字符和日期要包含在单引号中
• 字符大小写敏感，日期格式敏感
• 默认日期格式是DD-MON月-RR
  如下：

select last_name,job_id,department_id
from employees where last_name = 'Higgins'

select last_name,hir_date
from employees
where hire_date = '7-6月-1994'

单行函数转化：

select last_name,hir_date
from employees
where 
to_char('hire_date,'yyyy-mm-dd') = '1994-06-07'

比较运算

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

赋值使用 := 符号

如：

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

其它比较运算

操作符	含义
between......and......	在两个之间（包含边界）
in(set)	等于列表中的一个
like	模糊查询
is null	空值

逻辑运算

操作符	含义
and	逻辑并
or	逻辑或
not	逻辑否

select last_name,hir_date,salary
from employees
where salary between 4000 and 7000

in用法：

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

select last_name,department_id,salary
from employee
where department_id in (70,80,90)

注意：范围取值用between....and
有限个值用in

模糊查询like

//员工名字中含有字符a的员工有哪些
select last_name,department_id,salary
from employee
where last_name like '%a%'

//员工名字中第二位含有字符a的员工有哪些
select last_name,department_id,salary
from employee
where last_name like '_a%'
//下划线表示一个字符
//员工名字中含有_（下划线）的员工有哪些
select last_name,department_id,salary
from employee
where last_name like '%\_%' escape '\'
//转义escape

is null用法：

select last_name,department_id,salary,
commission_pct
from employee
where commission_pct is null

//commission_pct不是空的
select last_name,department_id,salary,commission_pct
from employee
where commission_pct is not null

order by子句

• 使用order by 子句排序
• asc(asscend):升序
• desc(descend):降序
• order by 子句在select语句结尾

select last_name,department_id,salary
from employees
order by salary desc,laser_name asc

别名排序

select last_name,department_id,salary,
12*salary annual_sal
from employees
order by annual_sal