ORACLE 连接查询

--多表查詢
DROP TABLE DEPT;
CREATE TABLE DEPT (
       DID VARCHAR2(10) PRIMARY KEY,
       DEPTNO VARCHAR2(30) NOT NULL,
       DNAME VARCHAR2(30) NOT NULL,
       D_PART VARCHAR2(30),
       MIN_SAR VARCHAR2(30),
       MAX_SAR VARCHAR2(30)
       );
INSERT INTO DEPT VALUES(1,'000001','DEPT1','A','2000','5000');
INSERT INTO DEPT VALUES(2,'000002','DEPT2','A','2000','5000');
INSERT INTO DEPT VALUES(3,'000003','DEPT3','A','4000','6000');
INSERT INTO DEPT VALUES(4,'000004','DEPT4','B','4000','7000');
INSERT INTO DEPT VALUES(5,'000005','DEPT5','B','5000','8000');
INSERT INTO DEPT VALUES(6,'000006','DEPT6','B','2000','9000');
COMMIT;

DROP TABLE DEPT_USER;  
CREATE TABLE DEPT_USER(
       U_ID VARCHAR2(10) PRIMARY KEY,
       D_DID VARCHAR2(10),
       D_UNAME VARCHAR2(30) NOT NULL,
       D_SAR VARCHAR2(30)
       );
INSERT INTO DEPT_USER VALUES(1,1,'JONES','5000');
INSERT INTO DEPT_USER VALUES(2,1,'IRIS','5000');
INSERT INTO DEPT_USER VALUES(3,3,'SIMIDA','6000');
INSERT INTO DEPT_USER VALUES(4,5,'TOM','7000');
INSERT INTO DEPT_USER VALUES(5,5,'SUSAN','8000');
INSERT INTO DEPT_USER VALUES(6,5,'JANE','9000');
INSERT INTO DEPT_USER VALUES(7,NULL,'TIM','9000');
COMMIT;       
 
DROP TABLE EMP1;       
CREATE TABLE EMP1(
       U_ID VARCHAR2(10) PRIMARY KEY,
       DID VARCHAR2(10),
       DEPTNO VARCHAR2(30),
       D_UNAME VARCHAR2(30) NOT NULL,
       SAR VARCHAR2(30),
       SAl VARCHAR2(30),
       SEX VARCHAR2(30)
       );   
INSERT INTO EMP1 VALUES(1,1,'000001','JONES','500','4500','B');
INSERT INTO EMP1 VALUES(2,2,'000002','IRIS','650','6500','B');
INSERT INTO EMP1 VALUES(3,2,'000003','SIMIDA','660','6600','B');
INSERT INTO EMP1 VALUES(4,3,'000003','TOM','780','7800','G');
INSERT INTO EMP1 VALUES(5,3,'000003','SUSAN','850','850','G');
INSERT INTO EMP1 VALUES(6,3,'000003','JANE','800','800','G');
INSERT INTO EMP1 VALUES(7,NULL,'','TIM','9000','9000','G');
INSERT INTO EMP1 VALUES(8,9,'000009','TIM','6000','6000','G');
COMMIT; 
DELETE FROM   EMP1        

/**
JOINS
left join、right join、full join、inner join、join........on
natural join 、cross join[笛卡爾乘積] (不需要on)
**/
SELECT * FROM DEPT DE,DEPT_USER  DU WHERE DE.DID=DU.D_DID;--EQUIJION(2表对称)
SELECT * FROM DEPT DE,DEPT_USER  DU WHERE DU.D_SAR BETWEEN DE.MIN_SAR AND DE.MAX_SAR;--NON-EQUIJION(1为表BE的一个值，2为表BE的另一个值)????
SELECT * FROM DEPT DE,DEPT_USER  DU WHERE DE.DID(+)=DU.D_DID;--（+对应的表自动填空值）outer joins[OUTER JOINS]
SELECT DU.D_UNAME|| ' WORK IN ' ||DE.DNAME FROM DEPT DE,DEPT_USER  DU WHERE DE.DID=DU.D_DID;--（self joins）[SELF JOINS]

--部分連接
select * from emp1 e,dept d where e.did=d.did;
select * from emp1 e join dept d on e.did=d.did;
select * from emp1 e inner join dept d on e.did=d.did;
--全連接
select * from emp1 e full join dept d on e.did=d.did;
--左連接
select * from emp1 e left join dept d on e.did=d.did;
--右連接
select * from emp1 e right join dept d on e.did=d.did;
--笛卡尓乘幾（交叉連接）
select * from emp1 e cross join dept d; 
--字段名相同的自動匹配成e.xx=d.xx and ...（自然連接）
select * from emp1 e natural join  dept d;

/**
(not)in
(not)exisit
null

**/
--in和not in 
select d.* from dept d where d.did in(select e.did from emp1 e);
select * from dept where did not in(select did from emp1);

--exists與not exisist（子句返回ture或者false）
select * from dept d where exists(select did,sex from emp1 e where e.did=d.did );
select * from dept d where not exists(select e.did,e.sex from emp1 e where d.did>2 );

--有空值和無空值的區別(主要是子句存在null有影響，主句存在null無影響)
select * from emp1 where did in(1,null);--獲取不到did為null的值
select * from emp1 e where e.did in(select d.did from dept d) or e.did is null;--解決：獲取到did為null的值
select * from dept d where d.did not in(1,null)--返回結果集為NULL
select * from dept d where d.did not in(select e.did from emp1 e where e.did is not null)or d.did is null;--解決

--空值处理
select * from dept d where coalesce(d.d_part,'0') not in 
(select a.d_part from dept a where a.d_part is not null);
--select a.d_part from dept a where a.d_part is not null

--exists和not exists語句必須存在select從句
select * from dept d where d.did exists(1);--錯誤寫法
select * from dept d where exists(select e.deptno from emp e where e.deptno=d.deptno);--空值獲取不到
select * from dept d where not exists(select e.deptno from emp e where e.deptno=d.deptno);--表B有和表A都有空，正常不返回數據，實際返回，null=null返回false

--案例：查詢emp表中sar>6500的dept表中的信息
select * from dept d WHERE d.deptno in(select e.deptno from emp1 e where e.sal>4500);--不包括空值
select * from dept d where exists(select 1 from emp1 e where e.deptno=d.deptno and e.sal>4500 );--不包括空值
select * from dept d where e.deptno=d.deptno and exists(select 1 from emp1 e where e.sal>4500 );--錯誤案例，e.did無效的定義
select * from dept d where exists(select 1 from emp1 e where e.sar>4500 ) and e.did=d.did;--錯誤案例，e.did無效的定義
select * from dept d,emp1 e where e.deptno=d.deptno and e.sal>4500 and exists(select e.deptno from emp1 e);-- 1、exists(select e.did from emp1 e)沒有存在意義2、這種查詢可以將空值查出來

--minus與intersect的使用
select did from emp1 minus select did from dept;--表A非交集部分
select did from emp1 intersect select did from dept;--表A與表B的交集部分

/**
union（all）(查询出来的列数，顺序，字段必须一致，且类型兼容)
直接使用union時會自動去除重發值
**/
SELECT SID FROM A UNION ALL SELECT SID FROM MYDB;
SELECT SID FROM A UNION SELECT SID,SNAME FROM MYDB;--錯誤，字段不一致
SELECT SID FROM A UNION SELECT SNAME FROM MYDB;--錯誤，類型不一致
--eg:
select e.did,e.sex from emp1 e union select d.did,d.sex from dept d;--去除重複值
select e.did,e.sex from emp1 e union all select d.did,d.sex from dept d;--不去除重複值
select d.did,d.dname,e.name from dept d where d.did=(select e.did from emp1 e)--錯誤示範：無效的e.name
     

--any和all、
select * from emp1 e where e.sar> all(3000)

SELECT * FROM DEPT WHERE DID> ANY(SELECT D_DID FROM DEPT_USER);--输出的DID中的值大于D_DID的最小值  ( DID>值1 or  DID>值2 or  DID>值n)
SELECT * FROM DEPT WHERE MAX_SAR> some(SELECT D_SAR FROM DEPT_USER WHERE D_DID=5);--输出的DID中的值大于D_DID的最小值

SELECT * FROM DEPT WHERE DID> ALL(SELECT DISTINCT  D_DID FROM DEPT_USER WHERE D_DiD IS NOT NULL);--输出的DID中的值大于D_DID的最大值  ( DID>值1 and  DID>值2 and  DID>值n),不能含空，空最大

--------查詢--------

/**
查詢：去除重复值(放在select后)
**/
SELECT DISTINCT D_DID FROM  DEPT_USER;--一列
SELECT DISTINCT U_ID, D_DID FROM  DEPT_USER;--多列
SELECT COUNT(DISTINCT D_DID) FROM  DEPT_USER;

--group by
/**
1、group by (a,b,c)根據所有字段abc分組
2、group by rollup(a,b,c)以a為主，根據a,ab,abc分組，外加一個匯總行
3、group by cube(a,b,c)根據a,ab,ac,abc,b,bc,c分組，外加一個匯總行
**/
select job,deptno,sal,avg(sal),sum(sal) from emp group by job,deptno,sal;
select job,deptno,sal,avg(sal),sum(sal) from emp group by rollup(job,deptno,sal);
select job,deptno,sal,avg(sal),sum(sal) from emp group by cube(job,deptno,sal);