限制和排列数据结果集
#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.