oracle表查詢

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

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

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

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

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

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

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

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

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

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

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

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

13、，多行子查詢是指返回單列多行數(shù) 據(jù)，都是針對(duì)單列而言的，而多列子查詢是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語(yǔ)句。 請(qǐng)思考如何查詢與SMITH的部門和崗位完全相同的所有雇員。SELECTdept nojobFROMempWHEREe name='SMITH'SELECT*FROMempWHERE(dept no,job)=(SELECTdept nojobFROMempWHEREe name='SMITH');在from子句中使用子查詢請(qǐng)思考：如何顯示高于自己部門平均工資的員工的信息思路：1查出各個(gè)部門的平均工資和部門號(hào)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;如何衡量一個(gè)程序員的水平？網(wǎng)絡(luò)處理能力，數(shù)據(jù)庫(kù)，程序代碼的優(yōu)化程序的效率要很高小總結(jié)：在這里需要說(shuō)明的當(dāng)在from子句中使用子查詢時(shí)，該子查詢會(huì)被作為一個(gè)視圖 來(lái)對(duì)待，因此叫做內(nèi)嵌視圖，當(dāng)在from子句中使用子查詢時(shí)，必須給

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，否則會(huì)報(bào)錯(cuò)(給表取別名的時(shí)候，不能加as；但是給列取別名，是可以加as的)分頁(yè)查詢按雇員的id號(hào)升序取出oracle的分頁(yè)一共有三種方式1. 根據(jù)rowid來(lái)分select*fromt_xiaoxiwhererowidi n( sele

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

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

18、lt;=10)wherern>=5;下面最主要介紹第三種：按 rownum 來(lái)分1.rownum 分頁(yè)SELECT*FROMemp;2.顯示 rownumoracle 分配的 SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)e;rn 相當(dāng)于 Oracle 分配的行的 ID 號(hào)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.幾個(gè)查詢變化a. 指定查詢列，只需要修改最里層的子查詢只查詢雇員的編號(hào)和工資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)建新表這個(gè)命令是一種快捷的建表方式CREATETABLEmytable(id, name,sal,job,dept no)asSELECTemp no,e name,s al,job,dept no FROMemp;創(chuàng)建好之后，descmytable ;和select*frommytable;看看結(jié)果如何？ 合并查詢'合并查詢 有時(shí)在實(shí)際應(yīng)用中，為了合并多個(gè) select語(yǔ)句的結(jié)果，可以使用集合操作符號(hào)union，union all ， in tersect ，minus多用于數(shù)據(jù)量比較大的數(shù)據(jù)局庫(kù)，運(yùn)行速度快。1) .u nio n該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中 重復(fù)行。SELECTe name,sal,jo