限制和排列数据结果集 #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 SensitiveSQL> 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 ConditionSQL> 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) ConditionSQL> 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 ConditionSQL> 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 OperatorSQL> 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 OperatorSQL> 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 OperatorSQL> 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 OrderSQL> 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 OrderSQL> 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进行降序排列
Summaryin 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.
