亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

外連接

核心：通過外連接，把對于連接條件不成立的記錄，仍然包含在最后的結果中

左外連接：當連接條件不成立的時候，等號左邊的表仍然被包含

右外連接：當連接條件不成立的時候，等號右邊的表仍然被包含

注意：左右外連接的叫法和寫法是相反的。如：若是右外連接，則在等號的左邊加上(+)符合，左外連接則在等號的右邊加上(+)符號。

select d.deptno,d.dname,count(e.empno) from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname;

--#案例三：按部門統計員工人數(已知員工入職年份）

select count(*) from emp;

select HIREDATE from emp;

select count(*) Total,

? ? ? ?sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",

? ? ? ?sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",

? ? ? ?sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",

? ? ? ?sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"

from emp;--函數方式

-------------------------

select?

(select count(*) from emp) Total,

(select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",

(select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",

(select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",

(select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987"

from dual;--子查詢方式

----------

--創建表

desc pn_ci;

desc PM_STU;

create table pn_ci

(CI_ID varchar2(20) NOT NULL,

?STU_IDS? varchar2(100));--選課表

-------------------

create table pm_stu

(STU_ID varchar2(20) NOT NULL,

?STU_NAME? varchar2(20));--學生表

------------------

INSERT into pn_ci values('1','1,2,3,4');

INSERT into pn_ci values('2','14');

select * from pn_ci;

---------------------

INSERT into pm_stu values('1','張三');

INSERT into pm_stu values('2','李四');

INSERT into pm_stu values('3','王五');

INSERT into pm_stu values('4','趙六');

select * from pm_stu;

--#案例三：按部門統計員工人數(已知員工入職年份）

select count(*) from emp;

select HIREDATE from emp;

select count(*) Total,

? ? ? ?sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",

? ? ? ?sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",

? ? ? ?sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",

? ? ? ?sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"

from emp;--函數方式

-------------------------

select?

(select count(*) from emp) Total,

(select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",

(select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",

(select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",

(select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987"

from dual;--子查詢方式

----------

--創建表

desc pn_ci;

desc PM_STU;

create table pn_ci

(CI_ID varchar2(20) NOT NULL,

?STU_IDS? varchar2(100));--選課表

-------------------

create table pm_stu

(STU_ID varchar2(20) NOT NULL,

?STU_NAME? varchar2(20));--學生表

------------------

INSERT into pn_ci values('1','1,2,3,4');

INSERT into pn_ci values('2','14');

select * from pn_ci;

---------------------

INSERT into pm_stu values('1','張三');

INSERT into pm_stu values('2','李四');

INSERT into pm_stu values('3','王五');

INSERT into pm_stu values('4','趙六');

select * from pm_stu;

設置報表樣式

第五章 ：解決實際問題

案例一：分頁查詢顯示員工信息：顯示員工號，姓名，月薪

--每頁顯示四條記錄

--顯示第二頁的員工

--按照月薪降序排列

select empno,ename,sal from emp order by sal desc;

--rownum 行號（永遠從1開始），只能使用<,<=,不能使用>,>=

select r,empno,ename,sal

from (select rownum r,empno,ename,sal?

? ?from (select rownum,empno,ename,sal from emp order by sal desc) e1

?where rownum<=8) e2

?where r>=5;--r是e2表的第一列，變成不是偽列的值

案例2：找到員工表中薪水大于本部門平均薪水的員工

select * from emp ;

--部門平均薪水

select deptno,avg(sal) from emp? group by deptno;

----------------------------------------------------------

explain plan for

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal

from emp e

where sal>(select avg(sal) from emp where deptno=e.deptno);

select * from table (dbms_xplan.display);

----------------------------------------------------------

explain plan for--解釋計劃

select *?

from emp e ,(select deptno,avg(sal) avgsal from emp? group by deptno) d

where e.deptno=d.deptno and e.sal>d.avgsal;--多表查詢，嵌套子查詢

--查看執行計劃

select * from table (dbms_xplan.display);--占用CPU更少

簡單的報表實現，統計部門工資，并作出總和

break on? deptno? skip 2? ?將相同的部門號不顯示，并且跳過兩行預留空白

group? by? 語句增強? 例子

group by 語句增強

使用列轉行listagg函數

第四章：子查詢

1.子查詢案例

示例：查詢工資比員工scott高的員工信息

select * from emp?

where sal>(select sal from emp where ename='SCOTT');

2.子查詢注意的10個問題

• 子查詢語法中的小括號

• 子查詢的書寫風格

• 可以使用子查詢的位置：where，select，having，from

select empno,ename,sal,(select job from emp where empno=7839) 第四列

from emp ;

select deptno,avg(sal) from emp group by deptno

having avg(sal)>(select max(sal) from emp where deptno=30);--having與where區別，having可以跟group by，where不可以。

• 不可以使用子查詢的位置：group by

--按照部門查詢部門平均工資

select deptno,avg(sal) from emp group by deptno;--正確

select deptno,avg(sal) from?

emp group by (select deptno from emp);--錯誤

• 強調：from后面的子查詢

--from后面的子查詢，看做新的表

--示例1：查詢員工信息：員工號，姓名，月薪

select empno,ename,sal from emp;

--示例1：查詢員工信息：員工號，姓名，月薪,年薪

select empno,ename,sal,(select sal*12 from emp) from emp;

select empno,ename,sal 月薪,sal*12 年薪 from emp;

select * from (select empno,ename,sal 月薪,sal*12 年薪 from emp);

• 主查詢和子查詢可以不是同一張表

--主查詢和子查詢可以不是同一張表

--示例：查詢部門名稱是SALES的員工信息

select * from dept;

select * from emp?

? where deptno=

? (select deptno from DEPT where dname='SALES');

select e.* from emp e,dept d

? where e.deptno=d.deptno?

? and d.dname='SALES';

? --結果一致，理論上，下面的好與上面的，實際中，子查詢好于多表查詢，因為笛卡爾集很大

• 一般不在子查詢中，使用排序；但在Top-N分析問題中，必須對子查詢排序

--一般不在子查詢中進行排序

--Top-N分析中要排序

--示例：找到員工表中工資最高的前三名

--rownum 行號 偽列? (查詢語句中必須寫出它）

--行號需要注意的兩個問題：行號永遠按照默認順序生成，行號只能使用<.<=;不能使用>,>=

select rownum,empno,ename,sal from

(select * from emp order by sal desc)

where rownum<=3;--正確排序

select? rownum,empno,ename,sal from emp??

where rownum<=3

order by sal desc;

--只是前三條記錄，沒有按照薪水排序

• 一般先執行子查詢，再執行主查詢，但相關子查詢例外

--示例：找到員工表中薪水大于本部門平均薪水的員工

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal?

from emp e?

where sal>(select avg(sal) from emp where deptno=e.deptno);

• 單行子查詢只能使用單行操作符（查詢語句返回一條記錄）；

  多行子查詢只能使用多行操作符（查詢語句返回多條記錄）

--子查詢只返回一條記錄叫單行子查詢，返回兩條及以上記錄叫多行操作符

--單行子查詢只能使用單行操作符，多行子查詢只能使用多行操作符

--單行操作符：=,>,>=,<,<=,<>;多行操作符：in，any，all

--查詢員工信息，要求：職位與7566員工一樣，薪水大于7782員工的薪水

select * from emp?

where job=(select job from emp where empno=7566) --單行子查詢，

and sal>(select sal from emp where empno=7782);

--單行子查詢只能使用單行操作符，一個查詢語句中可以有多個單行子查詢

--單行子查詢

--查詢工資最低的員工信息

select * from emp?

where sal in (select min(sal) from emp);?

select * from emp?

where sal = (select min(sal) from emp); --單行子查詢

--查詢最低工資,大于10號部門最低工資,的部門號和部門的最低工資

select deptno,min(sal) from emp group by deptno;--所有部門的最低工資

(select min(sal) from emp where deptno=20) ;--10號部門最低工資

select * from (select deptno,min(sal) b? from emp group by deptno)?

where b>(select min(sal) from emp where deptno=20);--不使用having

select deptno,min(sal) from emp group by deptno

having min(sal)>(select min(sal) from emp where deptno=20) ;

--分組函數后面不能跟where，可以跟having代替where

--非法使用單行子查詢

select empno,ename from emp

where sal = (select min(sal) from emp group by deptno);--子查詢中是多行的集合，非法

--多行操作符in

--查詢部門名稱是SALES和ACCOUNTING的員工

select emp.* from emp,dept where emp.DEPTNO=dept.DEPTNO --多表查詢

and dept.dname IN('SALES','ACCOUNTING');

select emp.* from emp,dept where emp.DEPTNO=dept.DEPTNO --多表查詢

and (dept.dname? ='SALES' or dept.dname ='ACCOUNTING');--優先級先and，后or

select * from emp --子查詢

where deptno in?

(select deptno from dept where dname ='SALES' or dname ='ACCOUNTING');

--多行操作符ANY

--查詢工資比30號部門任意一個員工高的員工信息

select * from emp where sal>(select min(sal) from emp where deptno=30);

select * from emp where sal >any(select sal from emp where deptno=30);

--多行操作符ALL

select * from emp where sal >ALL(select sal from emp where deptno=30);

• 注意：子查詢中是null值問題

--單行子查詢中的null值問題，判斷一個值等不等與null值不能用=或<>，因為永遠是不等的，

--判斷一個值等不等與null值，只能用IN 或is not

select * from emp?

? where job=

? (select job from emp where ename='TOM');--子查詢不返回任何行

--多行子查詢中的空值

--查詢不是老板的員工

select * from emp where empno? in (select MGR from emp);

select * from emp where empno not in (select MGR from emp);--空值

--只要子查詢中返回值包含null值，不要使用not in，因為not in 等同與<> ALL（不等于所有值）

select * from emp?

where empno not in?

? (select MGR from emp where mgr is not null);--加上不為空的限制

第三章：多表連接

?1. 多表查詢

?多表查詢：從多個表中獲取數據

?查詢員工信息與部門信息，員工表與部門表

?2. 笛卡爾集

?多表查詢的基礎

表1*表2的笛卡爾集：列數=兩張表相加，行數兩張表相乘

連接條件N-1個，N代表多少張表連接

?3. 等值連接

? 等值連接:連接條件中是等號

? 示例：查詢員工信息，要求顯示：員工號，姓名，月薪，部門名稱

? 試驗：select empno,ename,sal,dname from emp?

? ? ? ? ? ? ?left join DEPT on emp.deptno=dept.deptno;

? ?標準：select e.empno,e.ename,e.sal,d.dname

? ? ? ? ? ? ? ? from emp e，dept d where e.deptno=d.deptno;

? ? ? ? ? ? ?（習慣上重命名表）

?4. 不等值連接

? ? ?不等值連接：連接條件不是等號

? ? ?示例：查詢員工信息，要求顯示：員工號，姓名，月薪，薪水的級別

?select * from emp;

select * from salgrade;

select e.empno,e.ename,e.sal,s.grade?

from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal;--（具體問題，具體分析）注意大于等于下線，和小于上線

select e.empno,e.ename,e.sal,s.grade?

from emp e,salgrade s where e.sal between s.losal and s.hisal;--（between and包含兩邊邊界值）

?5. 外連接

select * from emp;

select * from dept;

select d.deptno,d.dname,count(e.empno)

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

group by d.deptno,d.dname;

select d.deptno,d.dname,count(e.empno)

from emp e left join dept d? on e.deptno=d.deptno

group by d.deptno,d.dname;

?7. 自連接

查詢員工姓名和對應老板姓名

select * from emp ;

select e1.ename，e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;--普通表連接自動顧略空值（14*14=196條記錄）--如果一億條數據表，則是平方關系

select e1.ename，e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno;

?8. 層次查詢（解決自連接問題）

自連接存在的問題：不適合操作大表

解決辦法：層次查詢（單表查詢）

原理：

條件：上層員工號是下層員工的老板號

? ? ? ? ? 上一層的員工號=老板號

select level,empno,ename,sal,mgr from emp?

connect by prior empno=mgr

start with empno =7893;

select level,empno,ename,sal,mgr from emp

connect by prior empno=mgr

start with empno is null

order by 1;（根節點）

select d.deptno?部門號，d.dname部門名稱，count(e.empno)人數

from emp e,dept d ?where e.deptno=d.deptno ?group by d.deptno,d.dname;

select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;