5.2条件限制和排序

Oracle

1、条件限制和排序

条件限制的关键字：WHERE

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;

2、比较操作符

SELECT last_name, salary
FROM employees
WHERE salary <= 6000;

SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;

SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);

3、使用LIKE做模糊匹配

可使用**%** 或者**_** 作为通配符：
**%** 代表 0个或者多个 字符.
**_** 代表一个单个字符.

SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';

4、要搜索统配符本身-使用ESCAPE 标识转义字符

select * from t_char where a like '%\%%' escape '\';

5、使用NULL条件查询

SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;

6、多条件组合的逻辑操作符

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >=2000
AND job_id LIKE '%MAN%';

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%';

SELECT last_name, job_id
FROM employees
WHERE job_id
NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

7、使用ORDER BY 子句进行排序

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;

ASC：升序

DESC：倒序

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC;

按照字段别名排序

SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;

按照多个字段排序

SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

8、操作实例

1、员工信息按先后入职日期排序

select * from emp order by hiredate

2、员工信息按薪水从大到小排序

select * from emp order by sal desc

3、员工信息按部门号和薪水排序

select * from emp order by deptno,sal

按照顺序排序deptno,sal

4、员工信息按部门和薪水排列，降序

select * from emp order by deptno desc,sal desc

【结论】：asc|desc 作用在前面那列

5、员工信息按奖金倒序

select * from emp order by comm desc

NULL 会影响排序

select * from emp order by comm desc nulls last

6、员工信息按第2列排序

select * from emp order by 2

【 结论】：按照返回结果集第几列来排序，而不是表的第几列

7、员工信息按别名排序

select enmno ,sal "月薪" from emp order by "月薪";

可以根据别名进行排序