第二节----过滤和排序数据

|-如下以employees表为例
①过滤行

select employee_id,last_name from employees where employee_id > 200;
select employee_id,last_name,salary from employees where salary > 5000;
select employee_id,last_name,salary from employees where department_id = 90;

②字符和日期

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

select employee_id,last_name,salary from employees where last_name = 'Higgins';
select last_name,hire_date from employees where hire_date = '7-6月-1994';

③比较运算

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

④其他比较运算

select last_name,hire_date from employees where salary between 4000 and 7000;
select last_name,department_id,salary from employees where department_id in(70,80,90);
--包含字符a
select last_name,department_id,salary from employees where last_name like '%a%';
--末尾是字符a
select last_name,department_id,salary from employees where last_name like '%a';
--第二位是字符a的员工
select last_name,department_id,salary from employees where last_name like '_a%';
--名字中含有_的员工
select last_name,department_id,salary from employees where last_name like '%\_%' escape '\';
--空值
select last_name,department_id,salary from employees where commission_pct is null
select last_name,department_id,salary from employees where commission_pct is not null

⑤逻辑运算

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

⑥ORDER BY 子句排序

ASC（ascend）: 升序
DESC（descend）: 降序
ORDER BY 子句在SELECT语句的结尾

select last_name,department_id,salary,commission_pct from employees where department_id = 80 order by salary desc;
select last_name,department_id,salary,commission_pct from employees where department_id = 80 order by salary asc 
--如果工资一样，按照名字排
select last_name,department_id,salary from employees order by salary asc,last_name asc