Oracle表管理(查詢)05

1、L o g oL o g o Oracle表查詢表查詢查詢條件；輸出該條件下的內(nèi)容vOracle單表查詢 查詢表結(jié)構(gòu) 查詢所有列 查詢指定列 查詢出不重復(fù)的數(shù)據(jù)簡(jiǎn)單查詢 where條件查詢v Oracle多表查詢 等值連接(內(nèi)連接) 左連接(左外連接) 右連接(右外連接) 完全連接(全外連接) v Oracle嵌套查詢Oracle單表查詢v 查詢表結(jié)構(gòu) desc 表名;v 查詢所有列 select * from 表名;v 查詢指定列 select name,age,birthday from 表名;v 查詢某些字段不同記錄(去重) select distinct name,birthday f

2、rom 表名;v Oracle大小寫不區(qū)分 字段名不區(qū)分大小寫 字段中的內(nèi)容區(qū)分大小寫Oracle單表查詢v 字段運(yùn)算查詢 select sal*12 ,age from 表名;v 使用列的別名 select sal *12 as “年工資”, age 年齡，name username from 表名;v 如何處理null值 select sal*12+nvl(jiang,0) as 年工資,age from 表名;v 去重復(fù)查詢行 select distinct name,birthday from 表名;去重復(fù)用as 為sal*12 起別名 年工資如果jiang為空，則用nvl函數(shù)，jia

3、ng為空時(shí)取0不用as 為age 起別名 年齡運(yùn)算符*乘Oracle單表查詢涉及到的運(yùn)算符v 運(yùn)算符分類 比較運(yùn)算符 邏輯運(yùn)算符 算術(shù)運(yùn)算符 連接運(yùn)算符Oracle單表查詢涉及到的運(yùn)算符v 比較運(yùn)算符名稱說(shuō)明=（等于）select * from a where job =IT;select * from a where sal =1100;!=（不等于）select * from a where job !=IT;select * from a where sal !=1100;=（不等于）select * from a where job =IT;select * from a where

4、sal =1100;（不等于）select * from a where job IT;select * from a where sal 1100;(小于)select * from a where sal (大于)select * from a where sal 1100;=(小于等于)select * from a where sal =(大于等于)select * from a where sal =1100;in(列表)select * from a where sal in(2000,3000,4000);（in輸出指定內(nèi)容）select * from a where job i

5、n(IT,MANAGER);not in(不在列表)select * from a where sal not in(2000,3000,4000);select * from a where job not in(IT,MANAGER);Oracle單表查詢涉及到的運(yùn)算符v 比較運(yùn)算符名稱說(shuō)明between(介于之間)select * from a where sal between 200 and 300;not between(不介于之間)select * from a where sal not between 200 and 300;like(模糊匹配)select * from a

6、 where job likem%select * from a where job likem_not like(模糊匹配)select * from a where job not like(M%);select * from a where job not like(M_);is null(是否為空)select * from a where sal is null;select * from a where job is null;is not null(是否不為空)select * from a where sal is not null;select * from a where

7、job is not null;%代表N個(gè)任意字符，可放前或后_代表一個(gè)任意字符Oracle單表查詢涉及到的運(yùn)算符v 邏輯運(yùn)算符名稱說(shuō)明and(與)select * from a where job =IT and sal2000;or(或)select * from a where job ！=IT or sal2000;not（非）select * from a where not name=zhansan;not job=IT等價(jià)于jobITOracle單表查詢涉及到的運(yùn)算符名稱說(shuō)明+(加)select from a ;-(減)select sal-200 from a ;*（乘）sel

8、ect sal*200 from a ;/（除）select sal/200 from a ;Oracle單表查詢涉及到的運(yùn)算符v 連接運(yùn)算符：v 用于將兩個(gè)或多個(gè)字符串合并在一起用于將兩個(gè)或多個(gè)字符串合并在一起;或者將一個(gè)字符串與或者將一個(gè)字符串與一個(gè)數(shù)據(jù)值合并在一起一個(gè)數(shù)據(jù)值合并在一起名稱說(shuō)明|（連接)select id | name | sal from a ;Oracle單表查詢涉及到的運(yùn)算符v 操作符的優(yōu)先級(jí)：Oracle單表查詢v 使用where子句 查詢出工資高于（等于）3000的員工 查詢出部門不是IT部門的員工 查詢出收入不等于3000之間的員工 查詢出生日大于1970年10

9、月2日的員工v 使用模糊查詢like(反之not like) like M% like M_v 在where條件中使用in（反之not in） select * from 表名 where sal in(2000,1000,3000);v 在where條件中使用between(介于之間) select * from 表名 where between 2000 and 3000;v 在where條件中使用is null(反之 is not null) select * from 表名 where sal is null;Oracle單表查詢v 使用邏輯運(yùn)算符組成條件查詢 查詢出工資高于3000或

10、者部門是IT部，并且姓名開(kāi)頭是M，年齡25歲以上的員工v 使用排序查詢order by .ascv 使用排序查詢order by .desv 使用排序查詢order by .des.asc 排序的語(yǔ)法 select * from 表名表名 where 條件條件 order by 字字段段1,字段字段2 desc; select * from 表名表名 where 條件條件 order by 字字段段1,字段字段2 asc; select * from 表名表名 where 條件條件 order by 字字段段1 desc,字段字段2 asc;asc升序desc降序若有where條件order

11、by 放在where后面，多個(gè)排序字段之間通過(guò)逗號(hào)分割Oracle單表查詢v 使用排序查詢 select * from xs where sp_id=2 order by idno desc; select * from xs where sp_id=2 order by idno asc; select * from xs where sp_id=2 order by idno asc ,numb desc;idno按升序，numb相同按降序排列多字段用逗號(hào)分隔Oracle單表查詢v 使用Oracle的函數(shù)進(jìn)行查詢 ceil(n),取大于等于數(shù)值n的最小整數(shù) floor(n),取小于等于數(shù)值

12、n的最小整數(shù) mod(m,n)取m整數(shù)除n后的余數(shù) power(m,n)取m的n次方 round(m,n)四舍五入，保留位 avg(字段名)求平均值,要求字段為數(shù)值型 count(字段名)統(tǒng)計(jì)總數(shù) min(字段名)計(jì)算數(shù)值型字段最小數(shù) max(字段名)計(jì)算數(shù)值型字段最大數(shù) sum( 字段名)計(jì)算數(shù)值型字段總和 lower(字段名)轉(zhuǎn)換為小寫 upper(字段名)轉(zhuǎn)換為大寫 abs(字段名)取絕對(duì)值Oracle單表查詢v分組查詢 在介紹GROUP BY 和 HAVING 子句前，我們必需先講講sql語(yǔ)言中一種特殊的函數(shù):聚合函數(shù) 例如SUM, COUNT, MAX, AVG等。這些函數(shù)和其它函數(shù)

13、的根本區(qū)別就是它們一般作用在多條記錄上。 select SUM(population) from b這里的SUM作用在所有返回記錄的population字段上，結(jié)果就是該查詢只返回一個(gè)結(jié)果，即所有國(guó)家的總?cè)丝跀?shù) 通過(guò)使用GROUP BY 子句，可以讓SUM 和 COUNT 這些函數(shù)對(duì)屬于一組的數(shù)據(jù)起作用。v Oracle單表查詢v分組查詢 having子句可以讓我們篩選成組后的各組數(shù)據(jù)，where子句在聚合前先篩選記錄.也就是說(shuō)作用在group by 子句和having子句前.而 having子句在聚合后對(duì)組記錄進(jìn)行篩選v 分組查詢SELECT region, SUM(population),

14、 SUM(area)FROM bbcGROUP BY region當(dāng)你指定 GROUP BY region 時(shí)， 屬于同一個(gè)region(地區(qū))的一組數(shù)據(jù)將只能返回一行值，也就是說(shuō)，表中所有除region(地區(qū))外的字段，只能通過(guò) SUM, COUNT等聚合函數(shù)運(yùn)算后返回一個(gè)值。SELECT region, SUM(population), SUM(area)FROM bbcGROUP BY regionHAVING SUM(area)1000000 HAVING子句可以讓我們篩選成組后的各組數(shù)據(jù)注意Oracle單表查詢v分組查詢注意事項(xiàng)group by語(yǔ)句可以單獨(dú)使用having語(yǔ)句只能配合

15、group by語(yǔ)句使用oracle的函數(shù)只能出現(xiàn)在選擇列表、having、order by 子句中如果在 select 語(yǔ)句中同時(shí)包含group by ,having,order by那么它們的順序是group by ,having,order by select deptno,avg(sal+nvl(comm,0) from scott.emp where sal+nvl(comm,0)500 group by deptno select aid,sum(scount) from schooltable group by aid having sum(scount)20000 order

16、by sum(scount)asc; Oracle多表查詢v 多表查詢：指從多個(gè)數(shù)據(jù)表查詢數(shù)據(jù) v 無(wú)條件多表查詢無(wú)條件查詢是將各表的記錄以“笛卡爾”集的方式組合起來(lái)select a.uname,a.age,b.uanme,b.age from a,b;v 等值多表查詢按照等值的條件查詢多個(gè)數(shù)據(jù)表中關(guān)聯(lián)的數(shù)據(jù)，要求關(guān)聯(lián)的多個(gè)數(shù)據(jù)表的某些字段具有相同的屬性，即具有相同的數(shù)據(jù)類型、寬度和取值范圍select a.uname,a.age,b.uanme,b.age ,b.sal from a,b where a.age=b.age and b.sal1000;v 非等值多表查詢select a.un

17、ame,a.age,b.uanme,b.age ,b.sal from a,b where a.age！=b.age and b.sal1000;表名用逗號(hào)分隔表名用逗號(hào)分隔兩個(gè)表中有相同屬性O(shè)racle多表查詢v 左連接：left（outer） join select * from aa left join bb on aa.aid=bb.bid; 是以aa表的記錄為基礎(chǔ)的,aa可以看成左表,bb可以看成右表,left join是以左表為準(zhǔn)的. 換句話說(shuō),左表(aa)的記錄將會(huì)全部表示出來(lái),而右表(bb)只會(huì)顯示符合搜索條件的記錄(例子中為: aa.aid = bb.bid). bb表記錄不

18、足的地方均為NULL. 等價(jià)于 select * from aa,bb where aa.aid=bb.bid(+);Oracle多表查詢v 右連接：right （outer）join select * from aa right join bb on aa.aid=bb.bid; 和left join的結(jié)果剛好相反,這次是以右表(B)為基礎(chǔ)的,A表不足的地方用NULL填充 等價(jià)于 select * from aa,bb where aa.aid(+)=bb.bid; Oracle多表查詢v 等值連接：inner join select * from aa innner join bb on

19、aa.aid=bb.bid; 只返回兩個(gè)表中聯(lián)結(jié)字段相等的行 等價(jià)于 select * from aa,bb where aa.aid=bb.bid;Oracle多表查詢v 完全連接：full （outer）join select * from aa full join bb on aa.aid=bb.bid; 只返回兩個(gè)表中的所有行 等價(jià)于 select * from aa left join bb on aa.aid=bb.bid union select * from aa right join bb on aa.aid=bb.bid Oracle嵌套查詢Avg max 不可以在不可以在

20、where后面使用因此用嵌套查詢后面使用因此用嵌套查詢v 嵌套查詢：在select查詢語(yǔ)句里可以嵌入select查詢語(yǔ)句，稱為嵌套查詢，又稱子查詢 v 簡(jiǎn)單嵌套查詢select bumen from a where employid=(select employid from b where name=張三) ;1) select employid from b where name=張三 查詢出姓名是張三的員工編號(hào) 2）select bumen from a where employid=員工編號(hào) 查詢出員工編號(hào)為10的員工所在部門嵌套查詢Oracle嵌套查詢v 帶in的嵌套查詢 selec

21、t employid,name,sal from a where sal in( select sal from b where name=張三); 也可select employid,name,sal from a where sal in(200,300); 等價(jià)于sal=200 or sal=300v 帶any的嵌套查詢 select employid,name,sal from a where sal any( select sal from b where name=張三); 也可select employid,name,sal from a where sal any(200,300); 等價(jià)于sal200 or sal300反之not inOracle嵌套查詢v 帶some的嵌套查詢 select employid,name,sal from a where sal some( select sal from b where name=張三); 也可select employid,name,sal from a where sal some(200,300); 等價(jià)于sal200 or sal300v 帶all的嵌套查詢 select employid,name,sal from a whe