Oracle过滤和排序数据和子查询
(1)ORDER BY子句
(ASC:升序,DESC:降序)
ORDER BY 子句在SELECT语句的结尾。
(2)NOT
逻辑否
• SELECT last_name, job_id
• FROM employees
• WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
(3)OR
OR 要求或关系为真。
• SELECT employee_id, last_name, job_id, salary
• FROM employees
• WHERE salary >= 10000
• OR job_id LIKE '%MAN%';
(4)逻辑运算
(5)AND
AND 要求并的关系为真。
• SELECT employee_id, last_name, job_id, salary
• FROM employees
• WHERE salary >=10000
• AND job_id LIKE '%MAN%';
(6)LIKE
使用 LIKE 运算选择类似的值
选择条件可以包含字符或数字
• % 代表零个或多个字符(任意个字符)。
• _ 代表一个字符。
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';
(7)BETWEEN
用来运算显示区间的值
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500
(8)IN
使用 IN运算显示列表中的值。
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
(9)子查询
子查询 (内查询) 在主查询之前一次执行完成。
子查询的结果被主查询(外查询)使用 。
SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name = 'Abel');
注意事项:子查询要包含在括号内。将子查询放在比较条件的右侧。单行操作符对应子查询,多行操作符对应多行子查询
(10)ANY 操作符
ANY运算符必须与单行比较运算符结合使用,并且返回行只要匹配子查询的任何一个结果即可
select job_id,employee_id,last_name,salary
from employees
where salary < any
(select salary
from employees
where job_id = 'IT_PROG') AND job_id != 'IT_PROG';
(11)ALL 操作符
和子查询返回的所有值比较
select job_id,last_name,employee_id,salary
from employees
where salary < all
(select salary
from employees
where job_id = 'IT_PROG') and job_id != 'IT_PROG'