Oracle（三） 过滤和排序

使用 WHERE 过滤数据

-- 等于
select employee_id,last_name,department_id
from employees
where department_id = 90; --过滤数据

-- 大于等于
select employee_id,last_name,salary
from employees
where salary >= 5000;

-- 日期比较
selectemployee_id,last_name,hire_date
from employees
--where hire_date = '7-6月-1994';
--where to_char(hire_date,'yyyy-mm-dd') = '1994-06-07';
where to_date('1994-06-07','yyyy-mm-dd') = hire_date;

-- 不相等
select employee_id,last_name
from employees
where last_name <> 'Higgins';

-- between ... and（在两个值之间 (包含边界)）
select employee_id,last_name,salary
from employees
--where salary >= 6000 and salary <= 8000;
where salary between 6000 and 8000;--包含边界

-- in (,,,) 等于值列表中的一个
select employee_id,salary
from employees
--where salary = 6000 or salary = 7000 or salary = 8000;
where salary in (6000,7000,8000);

select employee_id,last_name
from employees
where last_name in ('King','Kochhar','Austin');

-- not in (6000,7000,8000)不在这三个数中的薪水
select employee_id,salary,last_name
from employees
--where salary <> 6000 and salary <> 7000 and salary <> 8000;
where salary not in (6000,7000,8000);

-- like模糊查询
--% 代表零个或多个字符(任意个字符)
select employee_id,last_name
from employees
--where last_name like '%har';
--查询last_name中含字符a
where last_name like '%a%';
--查询last_name中含字符a并且包含字符e
where last_name like '%a%e%' or last_name like '%e%a%';

-- '_' 下划线代表一个字符
select employee_id,last_name
from employees
--last_name中第3个字符是a
where last_name like '__a%';

update employees
set last_name = 'Wh_alen'
where employee_id = 200;

--字符是区分大小写的
select *
from employees
where last_name = 'wh_alen';

select employee_id,last_name
from employees
--查询学生的姓名的第3个字符是_且第4个字符是a
/*特殊符号使用***转义符***
将[%]转为[\%]、[_]转为[\_]，然后再加上[ESCAPE ‘\’] 即可 */
where last_name like '__\_a%' escape '\';

--is null  vs  is not null
select employee_id,commission_pct
from employees
where commission_pct is not null;

使用 ORDER BY 子句进行排序

• 排序 order by asc/desc
  asc(ascend)：升序
  desc(descend)：降序
  order by 子句要在select语句的结尾

--降序排序
select employee_id,last_name,salary
from employees
where salary >= 4000
order by last_name desc;

--错误写法（order by 子句要在select语句的结尾）
select employee_id,last_name,salary
from employees
order by last_name desc
where salary >= 4000;

--先按salary降序，相同的再按employee_id 升序
select employee_id,last_name,salary
from employees
where salary >= 4000
order by salary desc,employee_id asc;

--按照列的别名排序
select employee_id,12*salary "annual_sal"
from employees
order by "annual_sal";