Oracle學習筆記五 SQL命令（三）：Group by、排序、連接查詢、子查詢、分頁，oracle學習筆記

Oracle學習筆記五 SQL命令（三）：Group by、排序、連接查詢、子查詢、分頁，oracle學習筆記

GROUP BY和HAVING子句

GROUP BY子句

用於將信息劃分為更小的組
每一組行返回針對該組的單個結果

--統計每個部門的人數：
Select count(*) from emp group by deptno; --根據部門分組，並統計
 Select deptno, count(*) form emp group by deptno;
        
select deptno, avg(sal) from emp group by deptno;  --每個部門的平均工資

HAVING子句

用於指定 GROUP BY 子句檢索行的條件

Select deptno, avg(sal) from emp group by deptno having avg(sal) >2000; --找出平均工資大於2000的部門;

數據排序

Order by 列名1 asc | desc, 列2 asc | desc; --默認為asc升序
Select * from emp order by sal;  --根據工資升序排序
Select * from emp order by sal desc;  --工資降序
Select * from emp order by sal desc, hiredate asc;  --按工資降序排，如果工資一樣，則按日期升序排;

 查詢語句的執行順序

Where ,group by , having

先根據where條件將符合要求的數據篩選出來，

再根據group by來進行分組

最後將分組之後的數據用having進行約束，得到select的結果

多表查詢

Select e.ename, t.tid from emp e, test t;  --笛卡爾集，交叉查詢

連接查詢

內連接: inner join返回多個表之間共同的數據

Select empno,ename,dname from emp,dept where emp.deptno = dept.deptno;

Select empno,ename,dname form emp inner join dept on emp.deptno = dept.deptno; 

外連接:

左連接: left join  數據的結果以左表為主，右表如果在左表中不存在就顯示是否為空！

select s.*, c.cid, c.grade from student s left join cs c on s.sid = c.sid

右連接: right join

全連接: full join

具體參看：IndexMan 文章 oracle連接查詢詳解

子查詢

在一個查詢中可以包含另一個查詢，它可以出現在任何一個地方，外部查詢得到結果，內部查詢返回條件

--得到工資最高的人的信息
select * from emp where sal = (select max(sal) from emp); 

--查詢所有和SMITH在同一個部門的人
select ename from emp where deptno = (select deptno from emp where ename = 'SMITH');

--列出與smith在同一個部門的員工信息
Select * from emp where deptno = (select deptno form emp were ename = 'SMITH');

--與MARTIN是同一個領導的員工
Select * form emp where mrg = (select mgr from emp where ename = 'MARTIN');

--列出所有在New York辦公的員工
--連接查詢方式：
select * from emp inner join dept on emp.deptno = dept.deptno where LOC = 'NEW YORK';
--子查詢方式：
select * from emp where deptno = (select deptno from dept where LOC = 'NEW YORK');

--列出所有在New York和DALLAS辦公的員工
 Select * from emp where deptno in (select deptno from dept where loc = 'NEW YORK' or loc = 'DALLAS');
 Select * from emp where deptno = any (select deptno from dept where loc = 'NEW YORK' or loc = 'DALLAS');

 數據的分頁

--查詢前5條
    Select rownum, emp.* from emp where rownum <=5;
--查詢第6條以後的數據
    select * from emp where empno not in(select empno from emp where rownum <=5);
--查詢第6-10條數據
        ○ Select * from (select * from emp where empno not in(select empno from emp where rownum <=5)) where rownum <=5;
        ○ select * from emp where empno not in(select empno from emp where rownum <=5) and rownum <= 5;
--總結
    ○ Select * from (select * from emp where empno not in(select empno from emp where rownum <= size * (page - 1))) where rownum <= size;
    ○ select * from emp where empno not in(select empno from emp where rownum <= size * (page - 1)) and rownum <= size;
    
--排序後再分頁查詢
    --將排序之後的數據編號（效率排第2）
    select * from (select rownum rn, d.* from (select * from emp order by sal desc) d) where rn > 5 and rn < 11;
    
--分頁函數:row_number函數（效率排第1）
    select row_number() over(order by sal desc) rn, emp.* from emp;
    --分頁
    select * from (select row_number() over(order by sal desc) rn, emp.* from emp) where rn > 5 and rn < 11;