《ORACLE过滤和排序数据》由会员分享，可在线阅读，更多相关《ORACLE过滤和排序数据（26页珍藏版）》请在金锄头文库上搜索。
1、过滤和排序数据,目标,通过本章学习，您将可以: 在查询中过滤行。 在查询中对行进行排序。,在查询中过滤行,“retrieve all employees in department 90”,EMPLOYEES,过滤,使用WHERE 子句，将不满足条件的行过滤掉。WHERE 子句紧随 FROM 子句。,SELECT *|DISTINCT column|expression alias,. FROM table WHERE condition(s);,WHERE 子句,SELECT employee_id, last_name, job_id, department_id FROM employe
2、es WHERE department_id = 90 ;,字符和日期,字符和日期要包含在单引号中。 字符大小写敏感，日期格式敏感。 默认的日期格式是 DD-MON-RR。,SELECT last_name, job_id, department_id FROM employees WHERE last_name = Whalen;,比较运算,Operator = = ,Meaning Equal to Greater than Greater than or equal to Less than Less than or equal to Not equal to,SELECT last_n
3、ame, salary FROM employees WHERE salary =10000 AND job_id LIKE %MAN%;,OR,OR 要求或关系为真。,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);,NOT,优先级,Override rules
4、 of precedence by using parentheses.,Order Evaluated Operator1 Arithmetic operators 2 Concatenation operator3 Comparison conditions4 IS NOT NULL, LIKE, NOT IN5 NOT BETWEEN6 NOT logical condition7 AND logical condition8 OR logical condition,SELECT last_name, job_id, salary FROM employees WHERE job_id
5、 = SA_REP OR job_id = AD_PRES AND salary 15000;,优先级,AND优先级大于OR,SELECT last_name, job_id, salary FROM employees WHERE (job_id = SA_REP OR job_id = AD_PRES) AND salary 15000;,优先级,使用括号控制执行顺序。,SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ;,ORDER BY 字句,使用 ORDER BY
6、子句排序 ASC: 升序(默认方式) DESC: 降序 ORDER BY 子句在SELECT语句的结尾。,降序排序,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;,按照ORDER BY 列表的顺序排序。可以使用不在SELECT 列表中的列排序。,SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;,多个列排序,总结,SELECT *|DISTINCT column|expression alias,. FROM table WHERE condition(s) ORDER BY column, expr, alias ASC|DESC;,通过本课，您应该可以完成: 使用WHERE 子句过滤数据 使用比较运算 使用 BETWEEN, IN, LIKE和 NULL运算 使用逻辑运算符 AND, OR和NOT 使用 ORDER BY 子句进行排序。,
