Les02约束和排序数据.ppt

Restricting and Sorting Data Objectives After completing this lesson, you should be able to do the following: Limit the rows retrieved by a query Sort the rows retrieved by a query 课程目标 从数据库取回数据的时候，你可能需要限制所显示的数据行，或者将显示的结果行排序。本课介绍用来完成这些工作的SQL语句。 Limiting Rows Using a Selection Limiting the Rows Selected Restrict the rows returned by using the WHERE clause. The WHERE clause follows the FROM clause. 限制选择的行 你能够用WHERE子句限制从查询返回的行。一个WHERE子句包含一个必须满足的条件，WHERE子句紧跟着FROM子句。如果条件是true，返回满足条件的行。 在语法中： WHERE 限制查询满足条件的行 condition 由列名、表达式、常数和比较操作组成 WHERE子句能够比较列值、文字值、算术表达式或者函数，WHERE子句由三个元素组成： 列名 比较条件 列名、常量或值列表 Using the WHERE Clause Character Strings and Dates Character strings and date values are enclosed in single quotation marks. Character values are case sensitive, and date values are format sensitive. The default date format is DD-MON-RR. 字符串和日期 在WHERE子句中字符串和日期必须包含在单引号 (‘ ’) 中。但是，数字常数不应该包含在单引号中。 所有的字符搜索是大小写敏感的。在下面的例子中，没有行返回，因为 EMPLOYEES表存储的所有名字都是大小写混合的： SELECT last_name, job_id, department_id FROM employees WHERE last_name = WHALEN; Oracle数据库以内部数字格式存储日期，表示为：世纪、年、月、日、小时、分和秒。默认的日期显示是DD-MON-RR。 Comparison Conditions Using Comparison Conditions Other Comparison Conditions Using the BETWEEN Condition Use the BETWEEN condition to display rows based on a range of values. Using the IN Condition Use the IN membership condition to test for values in a list. Using the LIKE Condition Use the LIKE condition to perform wildcard searches of valid search string values. Search conditions can contain either literal characters or numbers: % denotes zero or many characters. _ denotes one character. Using the LIKE Condition You can combine pattern-matching characters. You can use the ESCAPE identifier to search for the actual % and _ symbols. Using the NULL Conditions Test for nulls with the IS NULL operator. Logical Conditions Using the AND Operator Using the OR Operator Using the NOT Operator Rules of Precedence 优先规则 1 算术运算 2 连字操作 3 比较操作 4 IS[NOT]NULL, LIK