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

標籤：

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;

 

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