select查询之过滤和排序数据

2.过滤和排序数据

2.1 使用where子句比较运算

select * from employees where department_id = 90;

• 比较运算
  操作符：=、>、<、<=、>=、<>
  例如：select last_name,salary from employees where salary <= 3000;

2.2 between…and…

select last_name,salary from employees where salary between 3000 and 3500;

2.3 in(set)表示在某些数值内

select employee_id, last_name, salary, manager_id from employees where manager_id in (102, 101);

2.4 like选择类似的值

选择条件可以包含字符或数字
% 代表零个或多个字符
_ 代表一个字符
select first_name from employees where first_name like ‘%t’;

2.5 is (not) NULL判断空值

select last_name, manager_id from employees where manager_id is null;

2.6 AND 要求并的关系为真

select employee_id, last_name, job_id, salary from employees where salary >=10000 AND job_id like ‘%MAN%’;

2.7 OR 要求或关系为真

2.8 NOT

select last_name, job_id from employees where job_id NOT IN(‘IT_PROG’, ‘ST_CLERK’, ‘SA_REP’);

2.9 ORDER BY子句排序

• ASC(ascend)：升序
• DESC(descend)：降序
• ORDER BY 子句在select语句的结尾
  select last_name,job_id, department_id,hire_date from employees ORDER BY hire_date;
  
• 按别名排序
  select id, boyName idol, userCP users from boys order by users;
  
• 多个列排序
  按照 order by 列表的顺序排序，可以使用步骤select列表中的列排序。
  select last_name, department_id, salary from employees order by department_id, salary DESC;