opc學(xué)習(xí)筆記(編輯修改稿)
2025-08-31 08:57 本頁面
【文章內(nèi)容簡介】 。fmt39。)To_char(數(shù)字，’fmt’)To_number(字符串[,’fmt]’)To_date(字符串[,’fmt]’)查詢82年入職的員工的日期select ename,sal from emp where hiredate like 39。%8239。查詢員工年薪并顯示貨幣符號和千位符，保留2位小數(shù)select ename,to_char(sal*12,39。L999,39。) from emp。加密手機(jī)號碼:18611116666 輸出 186****6666select replace(39。1861111666639。,substr(39。1861111666639。,4,4),39。****39。) from dual。NVL(列名，表達(dá)式|值)查詢非銷售的員工的總收入select ename,sal+m ,job from emp where job not like 39。SALE%39。 order by job。select ename,sal+nvl(m,0) ,job from emp where job not like 39。SALE%39。 order by job。NVL2(表達(dá)式0，表達(dá)式1，表達(dá)式2)查詢員工的總收入select ename,nvl2(m,sal+m,m) ,job from emp order by job。NULLIF (表達(dá)式1，表達(dá)式2)select nullif(1,2) from dual。select nullif(1,1) from dual。六 分組count 計(jì)數(shù) selectt count(nvl(m0))sum 合計(jì)avg 平均值 max 最大min 最小注意nvl值，空值會(huì)被忽略掉group by 組select job,count[avg](X)from empgroup by job。一定要有g(shù)roup by來定義where 語句不能用于分組函數(shù)，因此where語句一定在group by語句之前計(jì)算emp的總行數(shù)select count(*) *是全部，列名不算空值from emp。計(jì)算有多少員工有經(jīng)理管理select count(mgr) from emp。計(jì)算員工的平均工資和工資總和select avg(sal),sum(sal) from emp。計(jì)算員工中的最低工資和最高工資select min(sal),max(sal) from emp。查詢雇傭的第一個(gè)員工和最后一個(gè)員工select min(hiredate),max(hiredate) from emp。按照職位分，查詢每類員工的平均工資select job,avg(sal) from emp group by job。select job,avg(sal) avgsal from emp group by job order by avgsal。select avg(sal) from empgroup by job。查詢平均工資大于1500的職位select job,avg(sal) from emp having avg(sal) 1500group by job。select job,avg(sal) from emp group by job having avg(sal) 1500 order by 2。having 和group by 語句位置可以互換查詢按照職位分，最高最低平均工資select min(avg(sal)),max(avg(sal)) from empwhere job not like 39。PRESI%39。 group by job。計(jì)算員工的平均獎(jiǎng)金select avg(m) from emp。select avg(m),sum(m),job,count(m) from empgroup by job。select avg(nvl(m,0)) from emp七 表連接表連接中where語句是必須有的，否則會(huì)產(chǎn)生笛卡爾乘積（n*m）自然連接using onnature join工資為1500以上的員工所屬部門和具體地點(diǎn)select ,from emp,deptwhere = and sal =1500order by loc。查詢每一個(gè)分析員的管理者信息select ,,from emp w,emp mwhere = and like 39。ANA%39。查詢分析員和文員的管理者信息以及其工作地點(diǎn)select ,from emp w,emp m,dept dwhere = and = and in (39。CLERK39。,39。ANA
研究報(bào)告相關(guān)推薦
鄂ICP備17016276號-1