Java38: 數據庫二(Oracle)

group by 分組

--每個工作的人數
select count(job) from emp group by job;
--有多少工作
select count(coutn(job)) from emp group by job;
--每個工作的最高工資
select max(sal) from emp group by job;
--每個工作的最低工資
select min(sal) from emp group by job;
--每個工作的平均工資
select avg(sal) from emp group by job;
--所有工作中最大的平均工資
select max(avg(sal)) from emp group by job;

使用having  根據條件過濾組

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 函數

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

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

 如果 value1=if1 返回then1

    否則 如果 value1=if2 返回 then2

        默認的返回值是else

select decode(sal,1600,'一千六',5000,'五千','其他') from emp;

--按照job的內容分類統計其中部門ID是30的人數
select job,count(decode(DEPTNO,30,1,null)) deptno from emp group by(job);
--按照部門分類 統計每個部門中工作是CLERK的人數
select deptno,count(decode(job,'CLERK',1,null)) counts from emp group by(deptno);
--按照部門分類 計算每個每部中工作是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);

表的關聯

    外鍵 在一個表中 存著對應另外一張表的主鍵

 連接表

    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·內連接   這個結果和笛卡爾積 用條件后的結果一樣

inner join 和 join 一樣（省略 inner） 默認就是 內連接

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

兩個表的一個外鍵的名字 和 另一張表的主鍵的名字一樣 

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·左外連(左邊表的數據必須是全都有的，沒有的地方補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);

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

         3·全外連（全都有）

emp full outer join dept

select * from emp full outer join dept using(deptno);
--查詢所有部門的名稱以及部門的人數 沒有部門的人 化為 未分配
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)) 人數 
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() 任意一個

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 的子查詢中不能出現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<=10

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

select * 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;

內聯視圖(Inner View)

就是把select后的結果當作一張表

select ename 名字,job 職位,emp_num 手下人數 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 部門編號,emp_num 手下人數 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 對多個列分組

而這兩個列都是對部門的唯一標識，不會影響分組