ORACLE过滤和排序数据.ppt

过滤和排序数据 目标 通过本章学习，您将可以: 在查询中过滤行。 在查询中对行进行排序。 在查询中过滤行 过滤 使用WHERE 子句，将不满足条件的行过滤掉。 WHERE 子句紧随 FROM 子句。 WHERE 子句 字符和日期 字符和日期要包含在单引号中。 字符大小写敏感，日期格式敏感。 默认的日期格式是 DD-MON-RR。 比较运算 比较运算 其它比较运算 BETWEEN 使用 BETWEEN 运算来显示在一个区间内的值。 IN 使用 IN运算显示列表中的值。 LIKE 使用 LIKE 运算选择类似的值 选择条件可以包含字符或数字: % 代表一个或多个字符。 _ 代表一个字符。 LIKE ‘%’和‘-’可以同时使用。 可以使用 ESCAPE 标识符 选择‘%’和 ‘_’ 符号。 NULL 使用 NULL 判断空值。 逻辑运算 AND OR NOT 优先级 优先级 优先级 ORDER BY 字句 使用 ORDER BY 子句排序 ASC: 升序(默认方式) DESC: 降序 ORDER BY 子句在SELECT语句的结尾。 降序排序 按别名排序 多个列排序 按照ORDER BY 列表的顺序排序。 可以使用不在SELECT 列表中的列排序。 总结 Character Strings and Dates Character strings and dates in the WHERE clause must be enclosed in single quotation marks (). Number constants, however, should not be enclosed in single quotation marks. All character searches are case sensitive. In the following example, no rows are returned because the EMPLOYEES table stores all the last names in mixed case: SELECT last_name, job_id, department_id FROM employees WHERE last_name = WHALEN; Oracle databases store dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default date display is DD-MON-RR. Note: Changing the default date format is covered in a subsequent lesson. Instructor Note Some students may ask how to override the case sensitivity. Later in the course, we cover the use of single-row functions such as UPPER and LOWER to override the case sensitivity. Comparison Conditions Comparison conditions are used in conditions that compare one expression to another value or expression. They are used in the WHERE clause in the following format: Syntax ... WHERE expr operator value For Example ... WHERE hire_date=01-JAN-95 ... WHERE salary=6000 ... WHERE last_name=Smith An alias cannot be used in the WHERE clause. Note: The symbol != and ^= can also represent the not equal to condition. Using the Comparison Conditions In the example, the SELECT statement retrieves the last name and salary