sql-过滤和排序数据.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 列表中的列排序。 总结 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 SA_REP has been specified in uppercase to ensure that it matches the job ID column in the EMPLOYEES table. Character strings are case sensitive. 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 valu