oracle数据库基本知识之version学习与使用

多表连接

–了解多表连接的目的，分清连接的类型;

–掌握oracle及sql99标准各种类型连接的写法，并能写出相对复杂的sql;

–多表连接和限制条件、排序条件、单行函数的关联使用;

话不多说，直接进入正题。(这里直接将oracle的scott账户作为实例)

1.笛卡尔积

笛卡尔积是：

–第一个表中的所有行和第二个表中的所有行都发生

连接。

笛卡尔积在下列情况产生：

–连接条件被省略

–连接条件是无效的

为了避免笛卡尔积的产生，通常需要在where

子句中包含一个有效的连接条件。

例：

select emp.empno, emp.ename, emp.deptno,dept.deptno, dept.loc from emp, dept;

结果会由emp(14行)与dept(4行)变为总的14 x 4 = 56(行)。

2.等值连接

运用表与表之间的关系进行相关的查询、检索等操作。

可运用and运算符增加其它查询条件

课后练习1:

(1).写一个查询，显示所有员工姓名，部门编号，部门名称。

select ename,deptno,dname from emp,dept and emp.deptno = dept.deptno

(2).写一个查询，显示所有工作在chicago并且奖金不为空的员工姓名，工作地点，奖金。

select e.ename,loc,e.comm from emp,dept where emp.comm<>0 and dept.deptno=emp.deptno

(3).写一个查询，显示所有姓名中含有a字符的员工姓名，工作地点。

select e.ename,loc from emp join dept on dept.deptno = emp.deptno where emp.ename like '%a%'

3.多于两个表的连接

多个表连接和两个表连接一样，在构造sql语句时，需要多考虑一个表之间的关联条件。

例：

--查询每个顾客都订购了哪些商品

select c.name,o.itemid from customer c, order o,item i where c.custid = o.custid and o.ordid = i.ordid;

4.非等值连接

通过一定的范围来确定某个属性的值，例如oracle中scott账户里的salgrade表。

tips：

1.分析要查询的列都来自于哪些表，构成from子句;

2.分析这些表之间的关联关系，如果表之间没有直接关联关系，而是通过另一个中间表关联，则也要在from子句中补充中间关联表;

3接下来在where子句中补充表之间的关联关系，通常n个表，至少要有n-1个关联关系;

4.分析是否还有其它限制条件，补充到where子句的表关联关系之后，作为限制条件;

5.根据用户想要显示的信息，补充select子句。

6.分析是否有排序要求，如果排序要求中还涉及到其它表，则也要进行第2步补充排序字段所在的表，并且添加表之间的关联关系;

课后练习2：

(1).查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。

select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.loc from emp,dept,salgrade

where emp.deptno = dept.deptno and emp.sal between salgrade.losal and salgrade.hisal order by salgrade.grade

5.外部连接

在多表连接时，可以使用外部连接来查看哪些行,按照连接条件没有被匹配上。

外部连接就好象是为符号(+)所在边的表增加一个“万能”的行，这个行全部由空值组成。它可以和另一边的表中所有不满足连接条件的行进行连接。由于这个“万能”行的各列全部是空值，因此在连接结果中，来自“万能”行属性值全部为空值。

符号：(+)

语法示例1：

select table1.column, table2.column

from table1, table2

where table1.column(+) = table2.column;

语法示例2：

select table1.column, table2.column

from table1, table2

where table1.column = table2.column(+);

以scott账户为实例：

--查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来

select e.ename, d.deptno, d.dname from emp e, dept d

where e.deptno(+) = d.deptno order by e.deptno;

tips:(+)符号所在的一方是没有完整的查询行数据。

6.自身连接

自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样。

思考：查询每个员工的姓名和直接上级姓名

select worker.ename||' leader is '||manager.ename from emp worker, emp manager

where worker.mgr = manager.empno;

课后练习3：

(1).查询所有工作在new york和chicago的员工姓名，员工编号，以及他们的经理姓名，经理编号。

select e.ename,e.empno,m.ename,m.empno from emp e, emp m,dept

where e.mgr = m.empno and dept.deptno = e.deptno and dept.loc in ('newyork','chicago')

(2).第上一题的基础上，添加没有经理的员工king，并按照员工编号排序。

select e.ename,e.empno,m.ename,m.empno from emp e, emp m,dept

where e.mgr = m.empno(+) and dept.deptno = e.deptno and dept.loc in ('newyork','chicago') order by e.empno

(3).查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来。

select e.ename,e.empno,d.deptno from emp e,dept d

where e.deptno(+) = d.deptno

7.交叉连接

交叉连接会产生连个表的交叉乘积，和两个表之间的笛卡尔积是一样的(使用cross join 完成)。

实例：

select emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc

from emp cross join dept;

8.自然连接

自然连接是对两个表之间相同名字和数据类型的列进行的等值连接;如果两个表之间相同名称的列的数据类型不同，则会产生错误(使用natural join完成)。

实例：

select empno,ename,sal,deptno,loc

from emp natural join dept;

9.using子句

自然连接是使用所有名称和数据类型相匹配的列作为连接条件，而using子句可以指定用某个或某几个相同名字和数据类型的列作为连接条件。

实例：

select e.ename,e.ename,e.sal,deptno,d.loc

from emp e join dept d using (deptno) where deptno = 20 ;

10.on子句

自然连接条件基本上是具有相同列名的表之间的等值连接;

如果要指定任意连接条件,或指定要连接的列，则可以使用on子句;

用on将连接条件和其它检索条件分隔开，其它检索条件写在where子句;

on子句可以提高代码的可读性。

实例：

select e.empno, e.ename, e.deptno, d.deptno, d.loc

from emp e join dept d on (e.deptno = d.deptno);

11.左外连接

左外连接以from子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来。

实例：

select e.ename,e.deptno,d.loc

from emp e left outer join dept d on (e.deptno = d.deptno);

12.右外连接

右外连接以from子句中的右边表为基表，该表所有行数据按照连接条件无论是否与左边表能匹配上，都会被显示出来。

实例：

select e.ename,e.deptno,d.loc

from emp e right outer join dept d on (e.deptno = d.deptno);

13.全外连接

全外连接返回两个表等值连接结果，以及两个表中所有等值连接失败的记录。

实例：

select e.ename,e.deptno,d.loc

from emp e full outer join dept d on (e.deptno = d.deptno);

课后练习4：

(1).创建一个员工表和部门表的交叉连接。

select emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc

from emp cross join dept;

(2).使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期。

select ename,deptno,hiredate from emp natural join dept

where e.hiredate > '1-5月-80'

(3).使用using子句，显示工作在chicago的员工姓名，部门名称，工作地点。

select ename,dname,loc from emp join dept using (deptno)

where loc = 'chicago'

(4).使用on子句，显示工作在chicago的员工姓名，部门名称，工作地点，薪资等级。

select ename,dname,loc from emp

join dept on emp.deptno = dept.deptno

where loc = 'chicago'

(5).使用左连接，查询每个员工的姓名，经理姓名，没有经理的king也要显示出来。

select e.ename,m.ename from emp e

left outer join emp m on e.mgr = m.empno

(6).使用右连接，查询每个员工的姓名，经理姓名，没有经理的king也要显示出来。

select e.ename,m.ename from emp e

right outer join emp m on e.mgr = m.empno

课后作业：

(1).显示员工smith的姓名，部门名称，直接上级名称

select e.ename,dname,m.ename from emp e,emp m, dept d

where e.mgr = m.empno and e.deptno = d.deptno and e.ename = 'smith'

(2).显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。

select e.ename,d.dname,e.sal,s.grade from emp e,salgrade s,dept d

where e.deptno = d.deptno and e.sal between 3001 and 9999 and s.grade > 4

(3).显示员工king和ford管理的员工姓名及其经理姓名。

select e.ename,m.ename from emp e,emp m

where e.mgr = m.empno and e.ename in ('king','ford')

(4).显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

select e.ename,e.hiredate,m.ename,m.hiredate from emp e,emp m

where e.mgr = m.empno and e.hiredate < m.hiredate