Oracle where限制条件

Oracle where限制条件

注意：

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

比较运算：

- > < >= <= = != 		常规比较符
- BETWEEN...AND...		在两个值之间 (包含边界)
	SELECT last_name, salary FROM   employees	WHERE  salary BETWEEN 2500 AND 3500;
- IN(set)				等于值列表中的一个 
	SELECT employee_id, last_name, salary, manager_id FROM   employees WHERE  manager_id IN (100, 101, 201);
- LIKE					模糊查询
	% 代表零个或多个字符(任意个字符)。
	_ 代表一个字符
	SELECT	first_name FROM 	employees WHERE	first_name LIKE 'S%';
	可以使用 ESCAPE 标识符 选择‘%’和 ‘_’ 符号   
	回避特殊符号的：使用转义符。例如：将[%]转为[\%]、[_]转为[\_]，然后再加上[ESCAPE ‘\’] 即可
	SELECT job_id FROM   jobs WHERE  job_id LIKE ‘IT\_%‘ escape ‘\‘;
- IS NULL				空值
SELECT last_name, manager_id FROM   employees WHERE  manager_id IS NULL;

逻辑运算：

- AND		逻辑与
	SELECT employee_id, last_name, job_id, salary FROM   employees WHERE  salary >=10000 AND    job_id LIKE '%MAN%';
- OR		逻辑或
	SELECT employee_id, last_name, job_id, salary FROM   employees WHERE  salary >= 10000 OR     job_id LIKE '%MAN%';
- NOT		逻辑非
	SELECT last_name, job_id FROM   employees WHERE  job_id  NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

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 last_name, department_id, salary FROM   employees ORDER BY department_id, salary DESC;