数据库的限定和排序数据ppt课件

1、限定和排序数据 -Itpub技术培训目的n排序是昂贵的操作n限制某一查询所取记录n排序查询结果运用选择限定记录 EMPNO ENAME JOB . DEPTNO 7839 KINGPRESIDENT 10 7698 BLAKEMANAGER 30 7782 CLARKMANAGER 10 7566 JONESMANAGER 20 . EMPNO ENAME JOB . DEPTNO 7839 KINGPRESIDENT 10 7782 CLARKMANAGER 10 7934 MILLER CLERK 10SELECT * FROM emp WHERE deptno= 10;排序是昂贵的操作n

2、从开场就思索优化nn排序往往破费大量的时间及资源n磁盘排序比内存排序慢10000倍，在磁盘Cache存在的情况下根本上也会慢100倍限定所选择的记录n运用where子句限定前往的记录n WHERE子句在 FROM 子句后SELECTDISTINCT *, column alias, .FROM tableWHEREcondition(s);运用WHERE子句SQL SELECT ename, job, deptno 2 FROM emp 3 WHERE job=CLERK;ENAME JOB DEPTNO- - -JAMES CLERK 30SMITH CLERK 20ADAMS CLERK

3、20MILLER CLERK 10字符串和日期n字符串和日期要用单引号扩起来n字符串是大小写敏感的,日期值是格式敏感的n缺省的日期格式是 DD-MON-RRSQL SELECTename, job, deptno 2 FROM emp 3 WHEREename = JAMES;留意:数字通常不要运用引号引起来，以免产生数据库歧义及潜在转换！日期格式nSQL select * from v$nls_parameters;nPARAMETER VALUEn- -nNLS_LANGUAGE SIMPLIFIED CHINESEnNLS_TERRITORY CHINAnNLS_CURRENCY RMB

4、nNLS_ISO_CURRENCY CHINAnNLS_NUMERIC_CHARACTERS .,nNLS_CALENDAR GREGORIANnNLS_DATE_FORMAT DD-MON-RR日期格式n内部以数字方式存储n默许显示格式为DD-MON-RRn内部依然保管日期和时间的一切组成部分nOracle效力器不存在2000年问题RR日期格式 日期格式nSQL select name,value$ from props$ where name like %DATE%;nNAME VALUE$n- -nNLS_DATE_FORMAT DD-MON-RRnNLS_DATE_LANGUAGE A

5、MERICANnExecution Plann-n 0 SELECT STATEMENT Optimizer=CHOOSEn 1 0 TABLE ACCESS (FULL) OF PROPS$比较运算符运算符运算符= = =含义含义等于等于大于大于 大于等于大于等于 小于小于 小于等于小于等于不等于不等于运用比较运算符SQL SELECT ename, sal, comm 2 FROM emp 3 WHERE sal SELECTename, sal 2 FROM emp 3 WHEREsal BETWEEN 1000 AND 1500;LowerlimitHigherlimitn运用BETW

6、EEN运算符显示某一 值域范围的记录运用IN运算符n运用IN运算符获得匹配列表值的记录 SQL SELECTempno, ename, sal, mgr 2 FROM emp 3 WHEREmgr IN (7902, 7566, 7788); EMPNO ENAME SAL MGR- - - - 7902 FORD 3000 7566 7369 SMITH 800 7902 7788 SCOTT 3000 7566 7876 ADAMS 1100 7788听说nIn 最多允许1000个值列表n这是一个朋友的测试结果运用LIKE运算符SQL SELECTename 2 FROM emp 3 WH

7、EREename LIKE S%;运用LIKE运算符n运用组合方式匹配字符n运用ESCAPE 标识符来查找带特殊符号的字符号SQL SELECTename 2 FROMemp 3 WHEREename LIKE _A%;ENAME- JAMES WARDESCAPE用法nSQL select * from test;nNAMEn-ntest%tenSQL select name from test where name like %t% escape ;nNAMEn-ntest%tenSQLnSQL select name from test where name like %t% escap

8、e ;n未选定行怎样插入特殊字符nSQL insert into test values(aa&a);n输入 a 的值: cn原值 1: insert into test values(aa&a)n新值 1: insert into test values(aac)n已创建 1 行。nSQL select * from test;nNAMEn-ntest%tenaac-nSQL show escapenescape OFFnSQL set escape onnSQL show escapenescape (hex 5c)nSQL INSERT INTO test VALUES

9、(&1);n已创建已创建 1 行。行。nSQL select * from test;nNAMEn-ntest%tenaacn&1运用IS NULL运算符n查询包含空值的记录SQL SELECT ename, mgr 2 FROM emp 3 WHERE mgr IS NULL;ENAME MGR- -KING逻辑运算符运算符运算符ANDORNOT含义含义假设组合的条件都是假设组合的条件都是TRUE,前往前往TRUE假设组合的条件假设组合的条件 之一是之一是TRUE,前往前往TRUE假设下面的条件是假设下面的条件是FALSE,前往前往TRUE运用AND运算符SQL SELECT

10、 empno, ename, job, sal 2 FROM emp 3 WHERE sal=1100 4 AND job=CLERK; EMPNO ENAME JOB SAL- - - - 7876 ADAMS CLERK 1100 7934 MILLER CLERK 1300运用OR运算符SQL SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal=1100 4 OR job=CLERK; EMPNO ENAME JOB SAL- - - - 7839 KING PRESIDENT 5000 7698 BLAKE MANAGER 2850

11、 7782 CLARK MANAGER 2450 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250.14 rows selected.运用NOT运算符SQL SELECT ename, job 2 FROM emp 3 WHERE job NOT IN (CLERK,MANAGER,ANALYST);ENAME JOB- -KING PRESIDENTMARTIN SALESMANALLEN SALESMANTURNER SALESMANWARD SALESMAN优先级规那么n括号将跨越一切优先级规那么次序次序 运算符运算符 1一切的比较运算一

12、切的比较运算2NOT3AND4OR优先级规那么ENAME JOB SAL- - -KING PRESIDENT 5000MARTIN SALESMAN 1250ALLEN SALESMAN 1600TURNER SALESMAN 1500WARD SALESMAN 1250SQL SELECT ename, job, sal 2 FROM emp 3 WHERE job=SALESMAN 4 OR job=PRESIDENT 5 AND sal1500;优先级规那么ENAME JOB SAL- - -KING PRESIDENT 5000ALLEN SALESMAN 1600SQL SELEC

13、T ename, job, sal 2 FROM emp 3 WHERE (job=SALESMAN 4 OR job=PRESIDENT) 5 AND sal1500;留意很多人在简单的问题上犯下艰苦的错误，所以在他的SQL中，运用()来明确他的业务逻辑！ORDER BY 子句n运用ORDER BY 子句将记录排序nASC: 升序,缺省nDESC: 降序n ORDER BY 子局在SELECT指令的最后SQL SELECT ename, job, deptno, hiredate 2 FROM emp 3 ORDER BY hiredate;ENAME JOB DEPTNO HIREDATE

14、- - - -SMITH CLERK 20 17-DEC-80ALLEN SALESMAN 30 20-FEB-81.14 rows selected.降序排序SQL SELECT ename, job, deptno, hiredate 2 FROM emp 3 ORDER BY hiredate DESC;ENAME JOB DEPTNO HIREDATE- - - -ADAMS CLERK 20 12-JAN-83SCOTT ANALYST 20 09-DEC-82MILLER CLERK 10 23-JAN-82JAMES CLERK 30 03-DEC-81FORD ANALYST

15、20 03-DEC-81KING PRESIDENT 10 17-NOV-81MARTIN SALESMAN 30 28-SEP-81.14 rows selected.运用列的别名排序SQL SELECT empno, ename, sal*12 annsal 2 FROM emp 3 ORDER BY annsal; EMPNO ENAME ANNSAL- - - 7369 SMITH 9600 7900 JAMES 11400 7876 ADAMS 13200 7654 MARTIN 15000 7521 WARD 15000 7934 MILLER 15600 7844 TURNER 18000.14 rows selected.多个列排序n经过ORDER BY 列表的顺序来排序SQL SELECTename,