Restricting And Sorting Data

                                                         限制和排列数据结果集
  #Objective
   -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
  #Limiting the Rows Selected
   -Restrict the rows returned by using the where clause.限制行的返回用where字句
   -The WHERE clause follows the FROM clause.where字句位于FROM字句的后面
  #例:
SQL> select *
  2  from scott.emp
  3  where ename like 'A%';

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-2月 -81       1600        300         30
      7876 ADAMS      CLERK           7788 23-5月 -87       1100                    20

   #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
     日期的默认格式是DD-MON-RR
   #Comparison Conditions
    我们经常引入where条件的比较表达式
    --------------|----------------------------
    Operator   |    Meaning
    --------------|----------------------------
          =           |   Equal to
    --------------|----------------------------
          >          |   Greater than of equal to
    --------------|----------------------------
          >=        |   Greater than or less than
    --------------|----------------------------
          <          |   Less than
    --------------|----------------------------
          <=        |   Less than or equal to
    --------------|----------------------------
          <>        |   Not equal to
    --------------|----------------------------
例:Using Case Sensitive
SQL> select * from emp
  2  where ename like 's%';

未选定行

SQL> c /s/S/
  2* where ename like 'S%'
SQL> run
  1  select * from emp
  2* where ename like 'S%'

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80        800                     20
      7788 SCOTT      ANALYST         7566 19-4月 -87       3000                    20

SQL>
例:Using Comparison Conditions
SQL> select * from emp
  2  where empno>=7902;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-12月-81       3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82       1300                    10

   #Other  Comparison Conditions 
     --------------|----------------------------
       Operator |    Meaning
     --------------|----------------------------
     between...and| between two value(inclusive),
    --------------|----------------------------
     in(set)      | Match any of a list of values
    --------------|----------------------------
     like           | Match a character pattern
    --------------|----------------------------
     is null       | is a null value
    --------------|----------------------------
    注意:between...and的取值范围包括两端的端点,如between 10 and 50 意思是说
         10<=x=<50.
         in(set)是用于取结果中的没有规律的值(离散值).
         like用于字符匹配上面曾用到过.
         is null用于匹配值是否为null.
例:Using Between...and Condition
SQL> select * from emp
  2  where empno between 7566 and 7788;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-4月 -81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81       2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81       2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87       3000                    20

例:Using In(set) Condition
SQL> select * from emp
  2  where sal in(800,5000,1100);

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80        800                    20
      7839 KING       PRESIDENT            17-11月-81       5000                    10
      7876 ADAMS      CLERK           7788 23-5月 -87       1100                    20

例:Using Like Condition
   -Use the like condition to perform wildcard searches of valid
    search string values.
   -Search conditions can contain  either  literal characters of
    number:
       ·% denotes zero or many characters.
       ·_ denotes one character.
SQL> select * from emp
  2  where ename like 'WAR_';

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7521 WARD       SALESMAN             22-2月 -81       1250        500         30

例:Using Is Null Condition
SQL> select * from emp
  2  where ename is null;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7900            CLERK           7698 03-12月-81        950                    30

    #Logical Condition
    --------------|-------------------------------------------------
   Operator   |    Meaning
    --------------|-------------------------------------------------
         and       | Return TRUE if Both component condition are true
    --------------|-------------------------------------------------
         or          | Return TRUE if either component codition is true
    --------------|-------------------------------------------------
         not        | Return TRUE if the following codition is false
    --------------|-------------------------------------------------     

例:Using The AND Operator
SQL> select * from emp
  2  where job='CLERK' and empno<7788;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80        800                    20

例:Using The OR Operator
SQL> select * from emp
  2  where mgr is null or empno<7500;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81       1600        300         30
      7521 WARD       SALESMAN             22-2月 -81       1250        500         30
      7839 KING       PRESIDENT            17-11月-81       5000                    10

例:Using The  NOT Operator
SQL> select * from emp
  2  where not empno>7500;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81       1600        300         30

   #Rules of Precedence
    -------------- |-------------------------------------------------
  Order Evaluated |   Operator
    --------------|-------------------------------------------------
         1           |   Arithmetic Operator
    --------------|-------------------------------------------------
         2           |   Concatenation Operator
    --------------|-------------------------------------------------
         3           |   Comparion conditions
    --------------|-------------------------------------------------
         4           |   IS[NOT] NULl,LIKE,[NOT] IN
    --------------|-------------------------------------------------
         5           |   [NOT] BETWEEN 
    --------------|-------------------------------------------------
         6           |   NOT logical condition
    --------------|-------------------------------------------------
         7           |   AND logical condition
    --------------|-------------------------------------------------
         8           |   OR logical condition
    --------------|-------------------------------------------------
   #ORDER BY Clause
    -Sort rows with the ORDER BY clause
       ·ASC:ascending order,default
       ·DESC:dascending order
    -The ORDER BY clause comes last in the SELECT
     statement.
例:Using Ascending Order
SQL> select * from emp
  2  where job like 'C%'
  3  order by empno;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80        800                    20
      7876 ADAMS      CLERK           7788 23-5月 -87       1100                    20
      7900            CLERK           7698 03-12月-81        950                    30
      7934 MILLER     CLERK           7782 23-1月 -82       1300                    10
例:Using Dascending Order
SQL>  select * from emp
  2   where job like 'C%'
  3  order by empno desc;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-1月 -82       1300                    10
      7900            CLERK           7698 03-12月-81        950                    30
      7876 ADAMS      CLERK           7788 23-5月 -87       1100                    20
      7369 SMITH      CLERK           7902 17-12月-80        800                    20
例:Sorting By Column alias
注意:在Oracle环境里别名(alias)可以用于排序,但是不能用它代替column进行
     任何的运算.
SQL> select empno id,ename,job from emp
  2  where job like 'C%'
  3  order by id;

        ID ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7876 ADAMS      CLERK
      7900            CLERK
      7934 MILLER     CLERK
例:Sorting by Multiple Columns
   ·The order of ORDER BY list is the order of sort.
   ·You can sort by a column that is not in the SELECT list.
SQL> select * from scott.emp
  2  where job like 'S%'
  3  order by sal,empno desc;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28-9月 -81       1250       1400         30
      7521 WARD       SALESMAN             22-2月 -81       1250        500         30
      7844 TURNER     SALESMAN        7698 08-9月 -81       1500          0         30
      7499 ALLEN      SALESMAN        7698 20-2月 -81       1600        300         30
 在这个例子我们可以知道结果集先按照SAL排序在第一.二行SAL是相等的又
 按照empno进行降序排列
 

                             Summary
in this lesson ,you should have learned how to:
  ·Use the WHERE clause to restrict rows of output
     -Use the comparion conditions
     -Use the BETWEEN,IN,LIKE,and NUll conditions
     -Apply the logical AND,OR,and NOT operators
  ·Use the ORDER BY clause to sort rows of outout.