oracle实验18-19：表的连接查询
多表连接
要从多张表中得到信息，就需要以一定的条件将多张表连接起来。
笛卡儿连接
多表连接主要分类：
- 等价连接
- 不等连接
- 外键连接
- 自连接
实验18：表的连接查询
笛卡儿（Cartesian）连接
笛卡儿积是把表中所有的记录作乘积操作，结果是第一张表的所有行和第二张表的所有行进行矩阵相乘，得到m*n行的结果。
而通常结果中可用的值有限。笛卡尔积出现的原因多种多样，通常是由于连接条件缺失造成的。
SQL> select ename , dname from emp,dept;
ENAME DNAME
---------- --------------
SMITH ACCOUNTING
ALLEN ACCOUNTING
WARD ACCOUNTING
JONES ACCOUNTING
MARTIN ACCOUNTING
BLAKE ACCOUNTING
CLARK ACCOUNTING
KING ACCOUNTING
TURNER ACCOUNTING
JAMES ACCOUNTING
FORD ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
ALLEN RESEARCH
WARD RESEARCH
JONES RESEARCH
MARTIN RESEARCH
BLAKE RESEARCH
CLARK RESEARCH
KING RESEARCH
TURNER RESEARCH
JAMES RESEARCH
FORD RESEARCH
MILLER RESEARCH
SMITH SALES
ALLEN SALES
WARD SALES
JONES SALES
MARTIN SALES
BLAKE SALES
CLARK SALES
KING SALES
TURNER SALES
JAMES SALES
FORD SALES
MILLER SALES
SMITH OPERATIONS
ALLEN OPERATIONS
WARD OPERATIONS
JONES OPERATIONS
MARTIN OPERATIONS
BLAKE OPERATIONS
CLARK OPERATIONS
KING OPERATIONS
TURNER OPERATIONS
JAMES OPERATIONS
FORD OPERATIONS
MILLER OPERATIONS
已选择48行。
结果是每个员工在每个部门上了一次班，4*12=48行。
要避免笛卡尔连接要给你正确的连接条件。
等价连接
等价连接又称简单连接或内连接。就是当两个表的公共字段相等的时候把两个表连接在一起。公共字段是两个表中有相同含义的列。
等价连接的语法结构
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1=table2.column2;
-在WHERE 子句中写连接条件
-当多个表中有重名列时，必须在列的名字前加上表名作为前缀
SQL> select ename,dname from emp,dept where emp.deptno=dept.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
KING ACCOUNTING
TURNER SALES
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
已选择12行。
等价连接中的记录筛选
-多表连接中，记录筛选语句同样写在WHERE语句中，用逻辑AND和连接判断语句写在一起。
SQL> select ename,job,dname from emp,dept where emp.deptno=dept.deptno and job like '%MAN%';
ENAME JOB DNAME
---------- --------- --------------
ALLEN SALESMAN SALES
WARD SALESMAN SALES
JONES MANAGER RESEARCH
MARTIN SALESMAN SALES
BLAKE MANAGER SALES
CLARK MANAGER ACCOUNTING
TURNER SALESMAN SALES
已选择7行。
表的别名
–便于书写，将同名的表区分
–表别名长度不超过30个字符;
–表别名定义在FROM子句中；
–如果已经定义了表别名，那么只能使用表别名而不能使用原表名；
–表别名的有效范围只是当前语句。
–定义方式为表别名紧跟别名，用空格间隔。
SQL语句的书写顺序是：
–SELECT FROM WHERE ORDER BY
而实际的执行顺序是：
–FROM WHERE SELECT ORDER BY
SQL> select ename,dname from emp e ,dept d where e.deptno=d.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
ENAME DNAME
---------- --------------
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
已选择14行。
列的别名
为了区分相同列的名称，这是别名的本质。
SQL> select ename,dname,e.deptno,d.deptno from emp e ,dept d where e.deptno=d.deptno;
ENAME DNAME DEPTNO DEPTNO
---------- -------------- ---------- ----------
SMITH RESEARCH 20 20
ALLEN SALES 30 30
WARD SALES 30 30
JONES RESEARCH 20 20
MARTIN SALES 30 30
BLAKE SALES 30 30
CLARK ACCOUNTING 10 10
SCOTT RESEARCH 20 20
KING ACCOUNTING 10 10
TURNER SALES 30 30
ADAMS RESEARCH 20 20
ENAME DNAME DEPTNO DEPTNO
---------- -------------- ---------- ----------
JAMES SALES 30 30
FORD RESEARCH 20 20
MILLER ACCOUNTING 10 10
已选择14行。
两个列名都是deptno，无法区分。
通过列的别名区分
SQL> select ename,dname,e.deptno "员工表的部门编号",d.deptno "部门表的部门编号"
from emp e ,dept d
where e.deptno=d.deptno;
ENAME DNAME 员工表的部门编号 部门表的部门编号
---------- -------------- ---------------- ----------------
SMITH RESEARCH 20 20
ALLEN SALES 30 30
WARD SALES 30 30
JONES RESEARCH 20 20
MARTIN SALES 30 30
BLAKE SALES 30 30
CLARK ACCOUNTING 10 10
SCOTT RESEARCH 20 20
KING ACCOUNTING 10 10
TURNER SALES 30 30
ADAMS RESEARCH 20 20
JAMES SALES 30 30
FORD RESEARCH 20 20
MILLER ACCOUNTING 10 10
已选择14行。
不等连接
除了等号之外，在表连接语句中还可以使用其它的运算符。这种使用除等号之外运算符的连接语句被称为不等价连接。
使用不等价连接查询可以查询两个表中具有非等值关系的数据。操作符可以是比较运算符，也可以是between...and或者是in、like。
SQL> select ename,sal,grade from emp,salgrade
where sal between losal and hisal;
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
JAMES 950 1
ADAMS 1100 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
SCOTT 3000 4
FORD 3002 5
KING 5000 5
已选择14行。
两个以上表连接
SQL> select a.ename,a.sal,b.dname,c.grade
from emp a,dept b,salgrade c
where a.deptno=b.deptno and a.sal between c.losal and c.hisal;
ENAME SAL DNAME GRADE
---------- ---------- -------------- ----------
SMITH 800 RESEARCH 1
JAMES 950 SALES 1
ADAMS 1100 RESEARCH 1
WARD 1250 SALES 2
MARTIN 1250 SALES 2
MILLER 1300 ACCOUNTING 2
TURNER 1500 SALES 3
ALLEN 1600 SALES 3
CLARK 2450 ACCOUNTING 4
BLAKE 2850 SALES 4
JONES 2975 RESEARCH 4
SCOTT 3000 RESEARCH 4
FORD 3002 RESEARCH 5
KING 5000 ACCOUNTING 5
已选择14行。
外连接
为了查找到所有记录，包括没有匹配的记录，需要用外连接语句来实现。
+号不能同时放在左右两边，只能出现在一边。
右外连接，如果左边的列所在表是缺乏表
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+)=table2.column;
左外连接，如果右边的列所在表是缺乏表
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column=table2.column(+);
SQL> select ename,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno；
ENAME DNAME DEPTNO
---------- -------------- ----------
SMITH RESEARCH 20
ALLEN SALES 30
WARD SALES 30
JONES RESEARCH 20
MARTIN SALES 30
BLAKE SALES 30
CLARK ACCOUNTING 10
KING ACCOUNTING 10
TURNER SALES 30
JAMES SALES 30
FORD RESEARCH 20
MILLER ACCOUNTING 10
已选择12行。
这条语句不会显示40号的部门，因为40号没有员工。
SQL> select ename,dname,dept.deptno from emp,dept where emp.deptno(+)=dept.deptno；
ENAME DNAME DEPTNO
---------- -------------- ----------
KING ACCOUNTING 10
CLARK ACCOUNTING 10
MILLER ACCOUNTING 10
FORD RESEARCH 20
SMITH RESEARCH 20
JONES RESEARCH 20
JAMES SALES 30
TURNER SALES 30
MARTIN SALES 30
WARD SALES 30
ALLEN SALES 30
BLAKE SALES 30
OPERATIONS 40
已选择13行。
将没有员工的部门用NULL来匹配。
自连接
表的一列和同一个表的另一列作为连接的条件。
SQL> select w.ename "下级",m.ename "上级"
from emp w,emp m
where w.mgr=m.empno(+);
下级 上级
---------- ----------
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
KING
TURNER BLAKE
JAMES BLAKE
FORD JONES
下级 上级
---------- ----------
MILLER CLARK
已选择12行。
"下级"和 "上级"为列的别名，区分相同的列。
w和m为表的别名，区分相同的表。
(+)为了将没有上级的人也显示。
实验19：sql99规则的表的连接操作
sql99：美国国家标准协会（ANSI）的SQL：1999标准的连接语法
语法：
SELECT table1.column, table2.column
FROMtable1[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)];
CROSS JOIN：交叉连接，生成笛卡尔积
SQL> select ename,dname from emp cross join dept;
ENAME DNAME
---------- --------------
SMITH ACCOUNTING
ALLEN ACCOUNTING
WARD ACCOUNTING
JONES ACCOUNTING
MARTIN ACCOUNTING
BLAKE ACCOUNTING
CLARK ACCOUNTING
KING ACCOUNTING
TURNER ACCOUNTING
JAMES ACCOUNTING
FORD ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
ALLEN RESEARCH
WARD RESEARCH
JONES RESEARCH
MARTIN RESEARCH
BLAKE RESEARCH
CLARK RESEARCH
KING RESEARCH
TURNER RESEARCH
JAMES RESEARCH
FORD RESEARCH
MILLER RESEARCH
SMITH SALES
ALLEN SALES
WARD SALES
JONES SALES
MARTIN SALES
BLAKE SALES
CLARK SALES
KING SALES
TURNER SALES
JAMES SALES
FORD SALES
MILLER SALES
SMITH OPERATIONS
ALLEN OPERATIONS
WARD OPERATIONS
JONES OPERATIONS
MARTIN OPERATIONS
BLAKE OPERATIONS
CLARK OPERATIONS
KING OPERATIONS
TURNER OPERATIONS
JAMES OPERATIONS
FORD OPERATIONS
MILLER OPERATIONS
已选择48行。
NATURAL JOIN：自然连接
- 所有同名的列都作为等价条件。
- 同名的列的数据类型必须匹配。
- 列的名称前不能加表的前缀。
SQL> select ename,deptno,dname from emp natural join dept;
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
MARTIN 30 SALES
BLAKE 30 SALES
CLARK 10 ACCOUNTING
KING 10 ACCOUNTING
TURNER 30 SALES
JAMES 30 SALES
FORD 20 RESEARCH
MILLER 10 ACCOUNTING
已选择12行。
USING (column_name)：指定列的连接
- 当有多列同名，但想用其中某一列作为连接条件时使用。
- USING子句和NATURAL JOIN不能在一条语句中同时书写。
SQL> select ename , deptno,dname from emp join dept using(deptno);
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
MARTIN 30 SALES
BLAKE 30 SALES
CLARK 10 ACCOUNTING
KING 10 ACCOUNTING
TURNER 30 SALES
JAMES 30 SALES
FORD 20 RESEARCH
MILLER 10 ACCOUNTING
已选择12行。
JOIN table2 ON (table1.column_name= table2.column_name)：等价连接语句
SQL> SELECT e.ename,e.job,e.deptno,d.dname
FROM emp e JOIN dept d ON (e.deptno=d.deptno);
ENAME JOB DEPTNO DNAME
---------- --------- ---------- --------------
SMITH CLERK 20 RESEARCH
ALLEN SALESMAN 30 SALES
WARD SALESMAN 30 SALES
JONES MANAGER 20 RESEARCH
MARTIN SALESMAN 30 SALES
BLAKE MANAGER 30 SALES
CLARK MANAGER 10 ACCOUNTING
KING PRESIDENT 10 ACCOUNTING
TURNER SALESMAN 30 SALES
JAMES CLERK 30 SALES
FORD ANALYST 20 RESEARCH
MILLER CLERK 10 ACCOUNTING
已选择12行。
sql99的外键连接
[LEFT|RIGHT|FULL OUTER JOIN：左外连接|右外连接|全外连接
在LEFT OUTER JOIN中，会返回所有左边表中的行，即使在右边的表中没有可对应的列值。
SQL> select ename,dname,dept.deptno
from dept left outer join emp
on(dept.deptno=emp.deptno);
ENAME DNAME DEPTNO
---------- -------------- ----------
KING ACCOUNTING 10
CLARK ACCOUNTING 10
MILLER ACCOUNTING 10
FORD RESEARCH 20
SMITH RESEARCH 20
JONES RESEARCH 20
JAMES SALES 30
TURNER SALES 30
MARTIN SALES 30
WARD SALES 30
ALLEN SALES 30
BLAKE SALES 30
OPERATIONS 40
已选择13行。
返回目录 http://www.cnblogs.com/downpour/p/3155689.html
