Java38:數(shù)據(jù)庫二(Oracle)-創(chuàng)新互聯(lián)

group by 分組

為企業(yè)提供網(wǎng)站制作、成都網(wǎng)站制作、網(wǎng)站優(yōu)化、營銷型網(wǎng)站建設(shè)、競價(jià)托管、品牌運(yùn)營等營銷獲客服務(wù)。成都創(chuàng)新互聯(lián)擁有網(wǎng)絡(luò)營銷運(yùn)營團(tuán)隊(duì)，以豐富的互聯(lián)網(wǎng)營銷經(jīng)驗(yàn)助力企業(yè)精準(zhǔn)獲客，真正落地解決中小企業(yè)營銷獲客難題，做到“讓獲客更簡單”。自創(chuàng)立至今，成功用技術(shù)實(shí)力解決了企業(yè)“網(wǎng)站建設(shè)、網(wǎng)絡(luò)品牌塑造、網(wǎng)絡(luò)營銷”三大難題，同時(shí)降低了營銷成本，提高了有效客戶轉(zhuǎn)化率，獲得了眾多企業(yè)客戶的高度認(rèn)可！--每個(gè)工作的人數(shù) select count(job) from emp group by job; --有多少工作 select count(coutn(job)) from emp group by job; --每個(gè)工作的高工資 select max(sal) from emp group by job; --每個(gè)工作的最低工資 select min(sal) from emp group by job; --每個(gè)工作的平均工資 select avg(sal) from emp group by job; --所有工作中大的平均工資 select max(avg(sal)) from emp group by job;

使用having 根據(jù)條件過濾組

select job,count(job) from emp group by job having count(job)>=3;

where 用在group by 之前

select job,count(job) from emp where sal>1000 group by job having count(job)>=3;

decode 函數(shù)

decode(value1 if1 then1 if2 then2 ... else)

decode(條件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

如果 value1=if1 返回then1

否則如果 value1=if2 返回 then2

默認(rèn)的返回值是else

select decode(sal,1600,'一千六',5000,'五千','其他') from emp; --按照job的內(nèi)容分類統(tǒng)計(jì)其中部門ID是30的人數(shù) select job,count(decode(DEPTNO,30,1,null)) deptno from emp group by(job); --按照部門分類統(tǒng)計(jì)每個(gè)部門中工作是CLERK的人數(shù) select deptno,count(decode(job,'CLERK',1,null)) counts from emp group by(deptno); --按照部門分類計(jì)算每個(gè)每部中工作是CLERK的平均工資 select deptno,avg(decode(job,'CLERK',sal,null)) CLERK_AVG_SAL from emp group by(deptno);insert into emp values(8000,'lmdtx','CLERK',8080,to_date('1980/09/09','yyyy/mm/dd'),30000,null,null); insert into emp values(8880,'dsy','CLERK',8980,to_date('1982/02/02','yyyy/mm/dd'),3000,null,null);

表的關(guān)聯(lián)

外鍵在一個(gè)表中存著對(duì)應(yīng)另外一張表的主鍵

連接表

1·笛卡爾積 emp 表中 deptno 為空的查詢不到

--emp表中的所有的行* dept中的所有的行 select * from emp,dept; --需要寫過濾條件過濾掉不需要的 select * from emp,dept where emp.deptno=dept.deptno; select * from emp e.dept d where e.deptno=d.deptno;

2·內(nèi)連接這個(gè)結(jié)果和笛卡爾積用條件后的結(jié)果一樣

inner join 和 join 一樣（省略 inner）默認(rèn)就是內(nèi)連接

join...on... from emp e inner join dept d on e.deptno=d.deptno

兩個(gè)表的一個(gè)外鍵的名字和另一張表的主鍵的名字一樣

from emp inner join dept using(deptno)select * from emp e inner join dept d on e.deptno=d.deptno; select * from emp inner join dept using(deptno);

3·外連接

1·左外連(左邊表的數(shù)據(jù)必須是全都有的，沒有的地方補(bǔ)null)

from emp left outer join dept using(deptno)select * from emp left outer join dept on emp.deptno=dept.deptno; select ename,nvl(dname,'還沒有部門') from emp2 left outer join dept2 using(deptno);

2·右外連

select * from emp right outer join dept using(deptno); --查詢所有部門的名稱和部門人數(shù)按人數(shù)的降序排 select dname,count(empno) num from emp2 right join dept2 Using(deptno) group by dname order by num desc;

3·全外連（全都有）

emp full outer join deptselect * from emp full outer join dept using(deptno); --查詢所有部門的名稱以及部門的人數(shù) 沒有部門的人化為未分配 select nvl(dname,'未分配'),count(empno) from emp2 full outer join dept2 using(deptno) group by dname order by dname; select nvl(dname,'未分配部門') 部門名字,decode(count(empno),0,'未分配人手',count(empno)) 人數(shù) from emp2 full outer join dept2 using(deptno) group by dname order by dname;

4·自連接

select e1.ename,e2.ename from emp2 e1 inner join emp2 e2 on e1.mgr=e2.empno;select e1.ename, e2.ename from emp2 e1 left join emp2 e2 on e1.mgr = e2.empno; select nvl(e1.ename,'沒有手下') , e2.ename MGR from emp2 e1 right join emp2 e2 on e1.mgr=e2.empno; select e2.ename,decode(count(e1.ename),0,'沒有手下',count(e1.ename))from emp2 e1 right join emp2 e2 on e1.mgr=e2.empno group by e2.ename

子查詢

用在where 子句中，單行單列 select e1.ename from emp2 e1 inner join emp2 e2 on e1.mgr=e2.empno where e2.ename='KING'; select distinct dname from emp2 e1 join dept2 d2 ON e1.deptno= d2.deptno where e1.ename like '%S%'; select dname from dept2 where deptno in(select deptno from emp2 where ename like '%S%');

ANY() 任意一個(gè)

ALL() 所有

select ename,job,sal from emp2 where sal>all(select DISTINCT sal from emp2 where job='MANAGER') and job<>'MANAGER'; select ename job,sal from emp2 where sal>any(select distinct sal from emp2 where job='CLERK') and job<>'CLERK';

exists 用于判斷后面的子查詢是否有行有true 沒有false

select ename from emp2 e1 where EXISTS(select 1 from emp2 e2 where e2.mgr= e1.empno); select ename from emp2 e1 where not exists(select 1 from emp2 e2 where e2.mgr=e1.empno);

3 in (1,2,3) true

3 in (1,2,null) false

5 not in (1,2,3) true

5 not in (1,2,null) false

5 in (1,2,null) false

5 not in (null) false

5in (null) false

not in 的子查詢中不能出現(xiàn)null值

where 中的多列查詢

select e.ename,e.sal, d.deptno,d.dname from emp2 e inner join dept2 d on e.deptno= d.deptno where (e.deptno,e.sal) in (select deptno,min(sal) from emp2 where deptno is not null group by deptno); select * from emp2 where (deptno,sal)in (select deptno,min(sal) from emp2 where deptno is not null group by deptno); select deptno,min(sal) from emp2 where deptno is not null group by deptno;

用在having

from emp where deptno is not null group by deptno having avg(sal)<(select max(avg(sal)) from emp group by deptno) select max(avg(sal)) from emp2 group by deptno;select deptno,round(avg(sal)) from emp2 where deptno is not null group by deptno HAVING avg(sal)<(select max(avg(sal)) from emp2 group by deptno); select dname,deptno from emp2 join dept2 using(deptno) where deptno is not null group by deptno,dname having avg(sal)=(select max(avg(sal)) from emp2 group by deptno);

分頁

偽列

select empno,ename,rownum from emp2 order by ename; select * from emp2 where rownum<=10;select * from (select empno,ename, rownum n from emp2 where rownum<=10) where n>5 and n<=10select * from (select empno,ename, rownum n from emp2 ) where n>5 and n<=10select * from (select empno,ename, rownum n from emp2 where rownum<=10) where n>5 select * from (select empno,ename,sal, rownum n from (select * from emp2 order by sal) where rownum <=10) where n>5;

內(nèi)聯(lián)視圖(Inner View)

就是把select后的結(jié)果當(dāng)作一張表

select ename 名字,job 職位,emp_num 手下人數(shù) from emp2 join(select deptno d,count(empno) emp_num from emp2 inner join dept2 using(deptno) group by deptno) on deptno=d where job='MANAGER';select ename 名字，job 職位,dname 部門,deptno 部門編號(hào),emp_num 手下人數(shù) from emp2 inner join(select deptno d,count(empno) emp_num,dname from emp2 inner join dept2 using(deptno) group by deptno, dname) on deptno=d where job='MANAGER'

group by 對(duì)多個(gè)列分組

而這兩個(gè)列都是對(duì)部門的唯一標(biāo)識(shí)，不會(huì)影響分組

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

當(dāng)前題目：Java38:數(shù)據(jù)庫二(Oracle)-創(chuàng)新互聯(lián)
本文地址：http://bm7419.com/article14/iipge.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、網(wǎng)站營銷、移動(dòng)網(wǎng)站建設(shè)、動(dòng)態(tài)網(wǎng)站、網(wǎng)站維護(hù)、App設(shè)計(jì)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容