orace 課堂筆記3

cissdy

--迪卡爾集（交叉聯(lián)接）
SELECT EMPNO,ENAME,JOB,DNAME FROM EMP,DEPT
--等值聯(lián)接
SELECT EMPNO,ENAME,JOB,DNAME FROM EMP,DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO
--
SELECT EMPNO,ENAME,JOB,DNAME FROM EMP,DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO
AND JOB='CLERK'
/
--帶有條件的等值聯(lián)接
SELECT EMPNO,ENAME,JOB,DEPT.DEPTNO DEPTNO,DNAME FROM EMP,DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO
AND JOB='CLERK'
/
-- 使用表別名的等值聯(lián)接
SELECT EMPNO,ENAME,JOB,d.DEPTNO DEPTNO,DNAME
FROM EMP e,DEPT d
WHERE d.DEPTNO=e.DEPTNO
AND JOB='CLERK'
/多表聯(lián)接
SELECT * FROM customer,ord,item
where customer.custid=ord.custid AND ord.ordid=item.ordid;
--非等值聯(lián)接
SELECT EMPNO,ENAME,JOB,SAL,GRADE,LOSAL,HISAL FROM
EMP,SALGRADE
WHERE SAL>LOSAL AND SAL<=HISAL
/
--右外聯(lián)接
SELECT EMPNO,ENAME,JOB,DNAME FROM EMP,DEPT
WHERE DEPT.deptno=emp.deptno(+)
/
--
SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,M.EMPNO,M.ENAME
FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO
/
--使用JOIN謂詞的交叉聯(lián)接
SELECT * FROM EMP CROSS JOIN DEPT;
--
SELECT * FROM EMP JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO
;
--使用JOIN謂詞的等值聯(lián)接
SELECT * FROM EMP JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO
WHERE JOB='CLERK';
--使用JOIN謂詞的等值聯(lián)接
SELECT * FROM EMP JOIN DEPT
USING(DEPTNO)
--自然聯(lián)接
SELECT * FROM EMP NATURL JOIN DEPT
--
SELECT * FROM EMP NATURL JOIN DEPT
USING(DEPTNO)
--左外聯(lián)接
SELECT * FROM
EMP LEFT OUTER JOIN DEPT
USING(DEPTNO)
--右外聯(lián)接
SELECT * FROM
EMP RIGHT OUTER JOIN DEPT
USING(DEPTNO)
--全外聯(lián)接
SELECT * FROM
EMP FULL OUTER JOIN DEPT
USING(DEPTNO)
--并集
SELECT EMPNO,ENAME
FROM EMP
UNION ALL
SELECT DEPTNO,DNAME
FROM DEPT;
--去掉重復值的并集
SELECT EMPNO,ENAME
FROM EMP
UNION ALL
SELECT EMPNO,ENAME
FROM EMP WHERE JOB='CLERK';
--交集
SELECT EMPNO,ENAME
FROM EMP
INTERSECT
SELECT EMPNO,ENAME
FROM EMP WHERE JOB='CLERK'
/
--差集
SELECT EMPNO,ENAME
FROM EMP
MINUS
SELECT EMPNO,ENAME
FROM EMP WHERE JOB='CLERK'
/
--子查詢
SELECT DNAME FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE SAL>2000)
/