多表查询(等值连接)
SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc
FROM emp,dept
WHERE empno.deptno=dept.deptno AND loc=’NEW YORK’;
表起别名
SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc
FROM emp e , dept d
WHERE e.deptno=d.deptno;
非等值连接的数据检索
SELECT e.ename,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
外部连接
加号相当于万能行,这个行全部由空值组成。它可以和另一边表中所有不满足连接条件的行连接
SELECT e.ename,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno(+) =d.deptno
ORDER BY e.deptno;
自身连接
SELECT worker.ename||'leader is'||manager.ename
FROM emp worker,emp manager
WHERE worker.mgr=manager.empno;
交叉连接 (没用)
SELECT emp.empno,emp.ename,dept.loc
FROM emp
CROSS JOIN dept;
自然连接
对两表之间相同名字和数据类型的列进行的等值连接
SELECT empno,ename,sal,deptno,loc
FROM emp
NATURAL JOIN dept;
USING子句
//和自然连接差不多,不过这个是指定列,范围更细
//NATURAL JOIN子句和USING子句相互排斥,不能同时使用
SELECT empno,ename,sal,deptno,loc
FROM emp JOIN dept USING(deptno)
WHERE deptno=20;
ON子句
//指定任意连接条件,或指定任意要连接的列
查询员工的姓名、编号、地址、主管姓名
SELECT e.empno,e.ename,d.loc,m.ename
FROM emp e
JOIN dept d
ON (e.deptno=d.deptno)
JOIN emp m
ON e.mgr=m.empno;
左外连接
//左边为基本表,右边为空的还是会显示出来,作用和(+)差不多
SELECT e.ename,e.deptno,d.loc
FROM emp e
LEFT OUTER JOIN dept d
ON (e.deptno=d.deptno);
右外连接
SELECT e.ename,e.deptno,d.loc
FROM emp e
RIGHT OUTER JOIN dept d
ON (e.deptno=d.deptno);
全外连接
SELECT e.ename,e.deptno,d.loc
FROM emp e
FULL OUTER JOIN dept d
ON (e.deptno=d.deptno);
练习
- SELECTename,dept.deptno,dept.dname FROMemp, dept;
- SELECTename,dept.deptno,dept.dname FROMemp, dept;
- SELECTename,dept.loc,emp.comm FROM emp,dept WHERE dept.loc!='CHICAGO' AND emp.comm IS NOT NULL;
- SELECT* FROM emp,dept;
- SELECTename,dname,hiredate
FROM emp
NATURAL JOIN dept
WHERE hiredate>'01-5月-80';
- SELECTename,dname,manager.ename
FROM emp worker,emp manager,dept
WHERE dept.deptno=worker.deptno
AND worker.mgr=manager.empno
AND worker.ename='SMITH';
- SELECTename,dname,sal,grade
FROM emp,dept,salgrade
WHERE emp.deptno=dept.deptno
AND emp.sal BETWEEN salgrade.losal AND salgrade.hisal
AND grade>4;
- SELECTename,m.ename
FROM emp w,emp m
WHERE w.mgr=m.empno
AND (m.ename='KING' OR m.ename='FORD');
- SELECTename worker_name,w.hiredate,m.ename manager_name,m.hiredate
FROM emp w,emp m
WHERE w.mgr=m.empno
AND w.hiredate<m.hiredate;