第五章 TP3_2_带条件查询.pptVIP

对数据进行限定和排序 使用WHERE子句 字符串和日期 字符串和日期值两边要加单引号 字符串值大小写敏感，日期值格式敏感 缺省的日期格式为DD-MON-YY (NLS_DATE_FORMAT) 比较操作符 使用比较操作符 其它比较操作符 使用BETWEEN操作符 使用BETWEEN操作符显示值在某个范围内的行. 使用IN操作符 使用 IN操作符来检查值在一个列表中. 使用LIKE操作符 使用 LIKE操作符来进行字符串的通配符查询. 查询条件可以包括文法字符或者数字. % 代表0个或多个字符 _ 代表一个字符 使用LIKE操作符 你可以结合两种通配符一起使用. 也可以使用ESCAPE来搜索%或_. 使用IS NULL操作符 用IS NULL操作符检查null值 逻辑操作符 使用AND操作符 使用OR操作符 使用NOT操作符 ORDER BY子句 使用ORDER BY子句来对行排序 ASC: 升顺, 缺省值 DESC: 降顺 ORDER BY子句写在SELECT语句的最后面. 用降顺排序 使用列别名排序(还可以用序号) 多列排序 ORDER BY子句列表的顺序就是结果的顺序. 可以使用不在SELECT子句列表中的列进行排序. 总结 The IS NULL Operator The IS NULL operator tests for values that are null. A null value means the value is unavailable, unassigned, unknown, or inapplicable. Therefore, you cannot test with (=) because a null value cannot be equal or unequal to any value. The example above retrieves the name and manager of all employees who do not have a manager. For example, to display name, job title, and commission for all employees who are not entitled to get a commission, use the following SQL statement: SQL SELECT ename, job, comm 2 FROM emp 3 WHERE comm IS NULL; ENAME JOB COMM -------- ----------- ------ KING PRESIDENT BLAKE MANAGER CLARK MANAGER ... Logical Operators A logical operator combines the result of two component conditions to produce a single result based on them or to invert the result of a single condition. Three logical operators are available in SQL: AND OR NOT All the examples so far have specified only one condition in the WHERE clause. You can use several conditions in one WHERE clause using the AND and OR operators. The AND Operator In the example, both conditions must be true for any record to be selected. Therefore, an employee who has a job title of CLERK and earns more than $1100 will be selected. All character searches are case sensitive. No rows are returned if CLERK is not in uppercase. Character strings must be enclosed in quotation marks. AND Truth Table The followi