过滤和排序
1.过滤
•使用WHERE 子句,将不满足条件的行过滤掉。
•WHERE 子句紧随 FROM 子句。
例如:返回在 90号部门工作的所有员工的信息
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;
1.1 过滤条件中存在字符和日期
•字符和日期要包含在单引号中。
•字符大小写敏感,日期格式敏感。
•默认的日期格式是 DD-MON月-RR。
SELECT last_name, job_id, department_id
FROM employees
WHERE last_name = 'Whalen';
SELECT last_name, hire_date, department_id
FROM employees
WHERE hire_date = '7-6月-1994'
1.2 在过滤条件中的比较运算
SELECT last_name, salary
FROM employees
WHERE salary <= 3000;
其他的比较运算:
BETWEEN
使用 BETWEEN 运算来显示在一个区间内的值
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
IN
使用 IN运算显示列表中的值
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
LIKE
•使用 LIKE 运算选择类似的值
•选择条件可以包含字符或数字:
–% 代表零个或多个字符(任意个字符)。
–_ 代表一个字符。
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';
•回避特殊符号的:使用转义符。例如:将[%]转为[\%]、[_]转为[\_],然后再加上[ESCAPE ‘\’] 即可。
SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT\_%‘ escape ‘\‘;
NULL
使用 IS (NOT) NULL 判断空值。
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
1.3 逻辑运算
AND OR NOT
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');
2.排序
ORDER BY子句
•使用 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, 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;
按department_id排序,在按salary的降序排序;
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;