【Oracle 官方技术支持】Les02_过滤和排序数据.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 列表中的列排序。 总结 Schedule: Timing Topic 45 minutes Lecture 30 minutes Practice 75 minutes Total Lesson Aim While retrieving data from the database, you may need to restrict the rows of data that are displayed or specify the order in which the rows are displayed. This lesson explains the SQL statements that you use to perform these actions. Limiting Rows Using a Selection In the example on the slide, assume that you want to display all the employees in department 90. The rows with a value of 90 in the DEPARTMENT_ID column are the only ones returned. This method of restriction is the basis of the WHERE clause in SQL. Limiting the Rows Selected You can restrict the rows returned from the query by using the WHERE clause. A WHERE clause contains a condition that must be met, and it directly follows the FROM clause. If the condition is true, the row meeting the condition is returned. In the syntax: WHERE restricts the query to rows that meet a condition condition is composed of column names, expressions, constants, and a comparison operator The WHERE clause can compare values in columns, literal values, arithmetic expressions, or functions. It consists of three elements: Column name Comparison condition Column name, constant, or list of values Using the WHERE Clause In the example, the SELECT statement retrieves the name, job ID, and department number of all employees whose job ID is SA_REP. Note that the job title