oracle表查詢

1、使用邏輯操作符號問題：查詢工資高于500或者是崗位為MANAGER的雇員，同時還要滿足他們 的姓名首字母為大寫的J?SELECT*FROMempWHERE(sal>500orjob='MANAGER')a nden ameLIKE'J%I.J使用orderby 字句默認asc問題：如何按照工資的從低到高的順序顯示雇員的信息？SELECT*FROMempORDERbysal;問題：按照部門號升序而雇員的工資降序排列SELECT*FROMempORDERbydept no,salDESC;使用列的別名排序問題：按年薪排序selecte name,(sal+nvl(co

2、mm,0)*12" 年薪"fromemporderby"年薪"asc;別名需要使用“”號圈中，英文不需要“”號分頁查詢等學(xué)了子查詢再說吧。Clear清屏命令oracle表復(fù)雜查詢、說明在實際應(yīng)用中經(jīng)常需要執(zhí)行復(fù)雜的數(shù)據(jù)統(tǒng)計，經(jīng)常需要顯示多張表的數(shù)據(jù)，現(xiàn)在我們給大家介紹較為復(fù)雜的select語句問題：如何顯示所有員工中最高工資和最低工資？SELECTMAX(sal),mi n( sal)FROMempe;最高工資那個人是誰？錯誤寫法：selecte name,salfromempwheresal=max(sal);正確寫法：selecte name,sal

3、fromempwheresal=(selectmax(sal)fromemp);注意：selectename,max(sal)fromemp;這語句執(zhí)行的時候會報錯，說ORA-OO937 :非單組分組函數(shù)。因為 max是分組函數(shù)，而ename不是分組函 數(shù).但是selectmin(sal),max(sal)fromemp;這句是可以執(zhí)行的。因為 min和max都是分組函數(shù)，就是說：如果列里面有一個分組函數(shù)，其它的都必須是分組函數(shù)， 否則就出錯。這是語法規(guī)定的問題：如何顯示所有員工的平均工資和工資總和？問題：如何計算總共有多少員工問題：如何擴展要求：查詢最高工資員工的名字，工作崗位SELECTe

4、 name,job,salFROMempewheresal=(SELECTMAX(sal)FROMemp); 顯示工資高于平均工資的員工信息SELECT*FROMempewheresal>(SELECTAVG(sal)FROMemp);groupby 和 having 子句groupby用于對查詢的結(jié)果分組統(tǒng)計，having子句用于限制分組顯示結(jié)果。問題：如何顯示每個部門的平均工資和最高工資?SELECTAVG(sal),MAX(sal),dept noFROMempGROUPbydept no;(注意：這里暗藏了一點，如果你要分組查詢的話，分組的字段 dept no 定要 出現(xiàn)在查詢的

5、列表里面，否則會報錯。因為分組的字段都不出現(xiàn)的話，就沒辦法 分組了)問題：顯示每個部門的每種崗位的平均工資和最低工資？SELECTmi n(sal),AVG(sal),dept nojobFROMempGROUPbydept nojob;問題：顯示平均工資低于2000的部門號和它的平均工資？SELECTAVG(sal),MAX(sal),dept noFROMempGROUPbydept nohavi ngAVG (sal)<2000;對數(shù)據(jù)分組的總結(jié)1分組函數(shù)只能出現(xiàn)在選擇列表、having、orderby子句中(不能出現(xiàn)在where 中)2如果在select語句中同時包含有g(shù)roup

6、by,having,orderby那么它們的順序是 groupby,having,orderby3在選擇列中如果有列、表達式和分組函數(shù)，那么這些列和表達式必須有一個出 現(xiàn)在groupby子句中，否則就會出錯。如SELECTdept no,AVG(sal),MAX(sal)FROMempGROUPbydept noHAVINGAVG(sal)<2000;這里dept no 就一定要出現(xiàn)在 groupby 中多表查詢說明 多表查詢是指基于兩個和兩個以上的表或是視圖的查詢。 在實際應(yīng)用中， 查詢單 個表可能不能滿足你的需求，（如顯示 sales 部門位置和其員工的姓名），這種 情況下需要使用到

7、（ dept 表和 emp 表） 問題：顯示雇員名，雇員工資及所在部門的名字【笛卡爾集】？ 規(guī)定：多表查詢的條件是至少不能少于表的個數(shù) -1 才能排除笛卡爾集 （如果有 N 張表聯(lián)合查詢，必須得有 N-1 個條件，才能避免笛卡爾集合） SELECTe.ename,e.sal,d.dnameFROMempe,deptdWHEREe.deptno=d.dep tno;問題：顯示部門號為 10 的部門名、員工名和工資？ SELECTd.dname,e.ename,e.salFROMempe,deptdWHEREe.deptno=d.dep tnoande.deptno=10;問題：顯示各個員工的姓名

8、，工資及工資的級別？先看 salgrade 的表結(jié)構(gòu)和記錄SQL>select*fromsalgrade; GRADELOSALHISAL17001200212011400314012000420013000 530019999SELECTe.e name,e.sal,s.gradeFROMempe,salgradesWHEREe.salBETWEEN s.losalANDs.hisal;擴展要求：問題：顯示雇員名，雇員工資及所在部門的名字，并按部門排序？SELECTe.e name,e.sal,d.d nameFROMempe,deptdWHEREe.dept no=d.dep tno

9、 ORDERbye.dept no;（注意：如果用groupby，一定要把e.deptno 放到查詢列里面）'-自連接自連接是指在同一張表的連接查詢問題：顯示某個員工的上級領(lǐng)導(dǎo)的姓名？比如顯示員工FORD'的上級SELECTworker.e name,boss.e nameFROMempworker,empbossWHEREworker.mgr=boss.emp no ANDworker.e name='FORD'子查詢4十么是子查詢子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢。單行子查詢單行子查詢是指只返回一行數(shù)據(jù)的子查詢語句請思考：顯示與

10、SMITH同部門的所有員工？思路：1查詢出SMITH的部門號 selectdept nofromempWHEREe name='SMITH'2顯示SELECT*FROMempWHEREdept no=(selectdept no fromempWHEREe name='SMITH');數(shù)據(jù)庫在執(zhí)行sql是從左到右掃描的，如果有括號的話，括號里面的先被優(yōu)先執(zhí) 行。'多行子查詢多行子查詢指返回多行數(shù)據(jù)的子查詢請思考：如何查詢和部門10的工作相同的雇員的名字、崗位、工資、部門號SELECTDISTINCTjobFROMempWHEREdept no=10;SE

11、LECT*FROMempWHEREjoblN(SELECTDISTINCTjobFROMempWHEREdeptno=10);(注意：不能用job=.，因為等號=是一對一的)、在多行子查詢中使用all操作符問題：如何顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門 號？SELECTe name,sal,dept noFROMempWHEREsal>all(SELECTsalFROMempWHEREdept no=30);擴展要求：大家想想還有沒有別的查詢方法。SELECTe name,sal,dept noFROMempWHEREsal>(SELECTMAX(sal)F

12、ROMe mpWHEREdept no=30);執(zhí)行效率上，函數(shù)高得多在多行子查詢中使用any操作符 問題：如何顯示工資比部門30的任意一個員工的工資高的員工姓名、工資和部 門號？SELECTe name,sal,dept noFROMempWHEREsal>ANY(SELECTsalFROMem pWHEREdept no=30);擴展要求：大家想想還有沒有別的查詢方法。SELECTe name,sal,dept noFROMempWHEREsal>(SELECTmi n( sal)FROMempWHEREdept no=30);多列子查詢單行子查詢是指子查詢只返回單列、單行數(shù)據(jù)

13、，多行子查詢是指返回單列多行數(shù) 據(jù)，都是針對單列而言的，而多列子查詢是指查詢返回多個列數(shù)據(jù)的子查詢語句。 請思考如何查詢與SMITH的部門和崗位完全相同的所有雇員。SELECTdept nojobFROMempWHEREe name='SMITH'SELECT*FROMempWHERE(dept no,job)=(SELECTdept nojobFROMempWHEREe name='SMITH');在from子句中使用子查詢請思考：如何顯示高于自己部門平均工資的員工的信息思路：1查出各個部門的平均工資和部門號SELECTdept no,AVG(sal)mysa

14、lFROMempGROUPbydept no;2.把上面的查詢結(jié)果看做是一張子表SELECTe.e name,e.dept no,e.sal,ds.mysalFROMempe,(SELECTdept no,AVG( sal)mysalFROMempGROUPbydept no)dsWHEREe.dept no=ds.dept noAN De.sal>ds.mysal;如何衡量一個程序員的水平？網(wǎng)絡(luò)處理能力，數(shù)據(jù)庫，程序代碼的優(yōu)化程序的效率要很高小總結(jié)：在這里需要說明的當在from子句中使用子查詢時，該子查詢會被作為一個視圖 來對待，因此叫做內(nèi)嵌視圖，當在from子句中使用子查詢時，必須給

15、子查詢指 定別名。注意：別名不能用as，如：SELECTe.e name,e.dept no,e.sal,ds.mysalFROMempe,(SELECTdept no,AVG( sal)mysalFROMempGROUPbydept no)asdsWHEREe.dept no=ds.dept noA NDe.sal>ds.mysal;在ds前不能加as，否則會報錯(給表取別名的時候，不能加as；但是給列取別名，是可以加as的)分頁查詢按雇員的id號升序取出oracle的分頁一共有三種方式1. 根據(jù)rowid來分select*fromt_xiaoxiwhererowidi n( sele

16、ctridfrom(selectrow numrn ,ridfrom( selectrowidrid,cidfromt_xiaoxiorderbyciddesc)whererow num <10000)wh erern>9980)orderbyciddesc;執(zhí)行時間 0.03 秒2. 按分析函數(shù)來分select*from(selectt.*,row_number()over(orderbyciddesc)rkfromt_xiaoxit) whererk<10000andrk>9980;執(zhí)行時間 1.01 秒3.按 rownum 來分select*from(select

17、t.*,rownumrnfrom(select*fromt_xiaoxiorderbyciddesc)t whererownum<10000)wherern>9980;執(zhí)行時間 0.1 秒其中 t_xiaoxi 為表名稱， cid 為表的關(guān)鍵字段，取按 cid 降序排序后的第9981-9999 條記錄， t_xiaoxi 表有 70000 多條記錄。個人感覺 1 的效率最好， 3 次之， 2 最差。/ 測試通過的分頁查詢 okokok select*from(selecta1.*,rownumrnfrom(selectename,jobfromemp)a1wher erownum&

18、lt;=10)wherern>=5;下面最主要介紹第三種：按 rownum 來分1.rownum 分頁SELECT*FROMemp;2.顯示 rownumoracle 分配的 SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)e;rn 相當于 Oracle 分配的行的 ID 號3. 挑選出 6 10 條記錄先查出1-10條記錄SELECTe.*,ROWNUMr nF ROM(SELECT*FROMemp)eWHEREROWNUM<=10;如果后面加上rownum>=6 是不行的，4然后查出6-10條記錄SELECT*FROM(SELECTe.*,RO

19、WNUMr nF ROM(SELECT*FROMemp)eWHEREROWNUM<=1O)WHEREr n>=6;5.幾個查詢變化a. 指定查詢列，只需要修改最里層的子查詢只查詢雇員的編號和工資SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECTe name,salFROMemp)eWHEREROWNUM<=1O)WHERErn>=6;b. 排序查詢，只需要修改最里層的子查詢工資排序后查詢6-10條數(shù)據(jù)SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECTe name,salFROMempORDERbysal)e

20、WHEREROWNUM<=1O)WHERErn>=6;用查詢結(jié)果創(chuàng)建新表這個命令是一種快捷的建表方式CREATETABLEmytable(id, name,sal,job,dept no)asSELECTemp no,e name,s al,job,dept no FROMemp;創(chuàng)建好之后，descmytable ;和select*frommytable;看看結(jié)果如何？ 合并查詢'合并查詢 有時在實際應(yīng)用中，為了合并多個 select語句的結(jié)果，可以使用集合操作符號union，union all ， in tersect ，minus多用于數(shù)據(jù)量比較大的數(shù)據(jù)局庫，運行速度快。1) .u nio n該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時，會自動去掉結(jié)果集中 重復(fù)行。SELECTe name,sal,jo