Oracle學習筆記2

1》多表查詢：

 select * from scott.dept;
 select * from scott.salgrade;

  select  * from scott.emp,scott.dept;
dept表的每一條記錄，都會與emp表的每一條記錄進行匹配；

笛卡爾積，在多表查詢的時候，如果不帶任何條件，則會出現笛卡爾積，怎麼避免：
多表查詢的條件至少不能少於表的個數減1；

例題：
1.顯示各個員工的姓名，工資，及其工資的層級；
2.顯示僱員名，工資，及所在部門的名字，並按部門排序。

3.顯示各個員工的姓名，及其領導姓名 (自串連,把emp表看做兩張表，一張是僱員表worker，一張是上級表boss)

 自串連：
 select worker.ename,boss.ename  from scott.emp worker,scott.emp boss where worker.mgr=boss.empno

 左外串連
 select worker.ename,boss.ename  from scott.emp worker,scott.emp boss where worker.mgr=boss.empno(+);
 右外串連
 select worker.ename,boss.ename  from scott.emp worker,scott.emp boss where worker.mgr(+)=boss.empno
2》

在多表查詢時，什麼時候加表名：如果兩個表的列同名，則需要加表名區分，
否則可以不加，建議加表名。
注意： 建議在進行多表查詢時，使用別名

3》
select distinct job from emp where deptno=10;
all操作符的使用：
select * from emp where sal > all(select sal from emp where deptno=30);
any操作符：
select * from emp where sal > any(select sal from emp where deptno=30);

4》多列子查詢：
查詢與SMITH的部門和崗位完全相同的所有僱員：

select * from emp where(deptno,job)=(select deptno,job from
emp where ename='SMITH');

5》在from子句中使用子查詢 (把結果當做暫存資料表) (重要的知識點)

如何顯示高於自己部門平均工資的員工資訊?
思路：
1.查詢出各個部門的平均工資
select avg(sal),deptno from scott.emp group by deptno;
2.把上面查詢的結果當做一個暫存資料表來對待
select t1.deptno,t1.ename,t1.sal,t2.myavg from scott.emp t1,(select avg(sal) myavg,deptno from scott.emp group by deptno) t2 where t1.deptno=t2.deptno and t1.sal>t2.myavg;

每個部門工資最高的人的詳細資料？
思路：
1.select max(sal),deptno from scott.emp group by deptno;
2.select t1.ename,t1.sal,t1.deptno,t2.maxsal from scott.emp t1,(select max(sal) maxsal,deptno from scott.emp group by deptno) t2 where t1.deptno=t2.deptno and t1.sal=t2.maxsal;

顯示各個部門的資訊（編號，名稱）和人員數量?
select count(*) pnum,deptno from scott.emp group by deptno;

select d.deptno,d.dname,p.pnum from scott.dept d,(select count(*) pnum,deptno from scott.emp group by deptno) p where d.deptno=p.deptno;

6》分頁查詢：
mysql：
select * from 表名 where 條件 limit 從第幾條取,取幾條;
sql server:
select top 10 * from 表名 where id not in (select top 10 id from 表名 where 條件)
排除前10條，再取10條，實際上取出11-20條；
oralce：
select t2.* from (select t1.*,rownum rn from (select * from scott.emp) t1 where rownum<=6) t2 where rn>=4;

測試效率：
類比一個40w條資料的表
create table mytest as select empno,ename,sal,comm,deptno from scott.emp;
自我複製
insert into mytest(empno,ename,sal,comm,deptno) select empno,ename,sal,comm,deptno from mytest;
分頁測試：
select t2.* from (select t1.*,rownum rn from (select * from mytest) t1 where rownum<=6) t2 where rn>=4;
查看SCOTT 使用者有哪些表
select table_name from dba_tables where owner='SCOTT';

7》合并查詢
1.union取得兩個結果集的並集，當使用該操作符時，會自動去掉結果集中重複行。
2.union all 與union相似，但不會取消重複行，而且不會排序
3.intersect取得交集
4.minus取得差集，顯示存在第一個集合中的資料，不顯示第二個集合中的資料。

cube函數：
select avg(sal),deptno,job from scott.emp group by cube(deptno,job);

8》內串連和外串連
1.內串連select 列名 from 表1 inner join 表2 on 條件
特點：只有兩張表同時匹配，才被選擇，顯示出來。
2.外串連分三種：左外連，右外連，完全外連
create table stu(id number,name varchar2(33);
insert into stu values(1,'jack');
insert into stu values(2,'tom');
insert into stu values(3,'kity');
insert into stu values(4,'nono');

create table exam(id number,grade number);
insert into exam values(1,58);
insert into exam values(2,78);
insert into exam values(11,87);

1--左外串連：左邊表完全顯示，右邊按條件匹配顯示
select stu.name,stu.id,exam.grade from stu left join exam on stu.id=exam.id;
或者：
select stu.name,stu.id,exam.grade from stu,exam where stu.id=exam.id(+);
2--右外串連：右邊表完全顯示，左邊按條件匹配顯示
select stu.name,stu.id,exam.grade from stu right join exam on stu.id=exam.id;
或者：
select stu.name,stu.id,exam.grade from stu,exam where stu.id(+)=exam.id;
3--完全串連：左右兩邊都完全顯示。

 

 

9》序列：
序列的建立：
create sequence myseq  --myseq表示序列名
start with 1
increment by 1
minvalue 1
maxvalue 30000
cycle      --若不希望迴圈用nocycle
nocache  --cache 10 ：表示一次產生10個號共你使用；使用緩衝產生號，優點提高效率，缺點可能產生跳號。

序列的使用：
create table test1 (id numbe primary key,name varchar2(33));
insert into test1 values(myseq.nextval,'abc'); --nextval是關鍵字

system使用者可以使用scott使用者的序列。從上次使用的地方接著增長

查看當前的序號：
若剛建立的序列，要先使用
select 序列名.nextval from dual; --再使用，每次使用後序列值都會增加，相應的步長
select 序列名.currval from dual;

刪除序列： drop sequence myseq;