Oracle-第5章 多表连接

第5章 多表连接知识点

5.1 多表连接概述
5.2 Oracle自有连接方法
  5.2.1 等值连接
  5.2.2 不等值连接
  5.2.3 外部连接
  5.2.4 自身连接
5.3 标准连接语法
  5.3.1 外连接
  5.3.2 交叉连接
  5.3.3 自然连接,using,on子句
5.4 练习
5.5 创建表格

5.1 多表连接概述

  连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。
语法为：
  Select table1.column,table2.column
    From table1,table2
    Where table1.column1=table2.column2
  其中，在where子句中书写连接条件，若果多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。
n个表相连时，至少需要n-1个连接条件。
  笛卡尔积：是第一个表中的所有行和第二个表中的所有行都发生连接，其在连接条件被省略或无效的情况下产生。为了避免笛卡尔积的产生，通常需要在where子句中包含一个有效的条件。例：笛卡尔积=emp*dept。
  注意：连接查询需要避免笛卡尔积，交叉连接会产生笛卡尔积，需要避免使用。
  在多表关联查询时，如果有列名重复，必须使用表前缀或表的别名进行制定。通过使用表前缀可以提高性能，通过使用列的别名可以区分来自不同表但是名字相同的列。
例1：select emp.ename,emp.deptno,dept.loc from emp,dept where emp.deptno=dept.deptno;
例:2：select e.ename,e.deptno,d.loc from emp e,dept d where e.deptno=d.deptno
多表连接的写法：
  1.分析要查询的列来自哪些表，构成from子句
  2.分析这些表之间的关联关系，如果表之间没有直接关联关系，而是通过另一个中间表关联，则也要在FROM子句中补充中间关联表;
  3.接下来在WHERE子句中补充表之间的关联关系，通常N个表，至少要有N-1个关联关系;
  4.分析是否还有其它限制条件，补充到WHERE子句的表关联关系之后，作为限制条件；
  5.根据用户想要显示的信息，补充SELECT子句；
  6.分析是否有排序要求，如果排序要求中还涉及到其它表，则也要进行第2步补充排序字段所在的表，并且添加表之间的关联关系。
例：查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。
Ans：

select e.empno,e.ename,e.sal,s.grade,d.loc 
       from emp e,salgrade s,dept d
       where e.deptno=d.deptno and e.sal between losal and hisal
       order by s.grade;

5.2 Oracle自有连接方法

5.2.1 等值连接
  ① 两个表之间的连接
例1：查询员工姓名，部门编号和工作地点。
Ans：select emp.ename,emp.deptno,dept.loc from emp,dept where emp.deptno=dept.deptno;
在连接查询时，可使用and运算符增加其他查询条件。
例2：查询工作地点在NEW YORK 的姓名，部门编号，工作地点。
Ans：select emp.ename,emp.deptno,dept.loc from emp,dept where emp.deptno=dept.deptno and dept.loc='NEW YORK';
  ② 多个表之间的连接
  多个表之间的连接和两个表连接一样，在构造SQL语句时，需要多考虑表和表之间的关联条件。
例：查询出马化腾购买的所有商品信息。
Ans:

 select c.name,g.goodsid,g.goods_name, g.goods_price
  from goods g, ord o, customer c
 where g.goodsid = o.goodsid and o.custid = c.custid and c.name = '马化腾';

5.2.2 不等值连接
   不等值连接：是指表关联关系不相等或使用不等值方式关联。
例：查询员工的姓名，薪资，薪资级别。
M1：select e.ename,e.sal,s.grade from emp e ,salgrade s where e.sal between s.losal and s.hisal;
M2：select e.ename,e.sal,s.grade from emp e ,salgrade s where e.sal>=s.losal and e.sal<=s.hisal;
5.2.3 外部连接
  在多表连接时，可以使用外部连接来查看哪些行，按照连接条件有没有被匹配上。外部连接的符号是 (+)，(+)在关联关系的右边则表示左连接；(+)在关联关系的左边，表示右连接。
  外部连接就好像是为了符号(+)所在边的表增加一个“万能”的行，这个行全部由空值组成。它可以和另外一边的表中所不满足连接条件的行进行连接。
例1：查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来。
Ans:

select e.ename,d.deptno,d.dname from emp e,dept d
       where e.deptno(+)=d.deptno;

5.2.4 自身连接
   自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样。自连接,对连接的列，如果有同行数据就把同行数据连接。
例：查询每个员工的姓名和直接上级姓名。
Ans:

 select e.ename 员工,e.mgr,e.empno,m.ename 经理 from emp e,emp m
       where e.mgr=m.empno(+);

5.3 标准连接语法

5.3.1 外连接
  外连接是以连接的一方作为参考，查询出所有的数据，再根据关联关系出查询出其他关联的数据，如果参考方有数据没有关联，则使用空行进行占位。外连接包括左连接，右连接和全连接。
  注意：outer外连接是有方向的，方向必须是一致的，要么使用左连接，要么使用右连接，不能左右连接同时使用。
   a).左外连接：以from子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边能匹配上，都会被显示出来。
  例：

select e.ename,e.comm,e.deptno,d.loc from emp e
       left outer join dept d
       on e.deptno=d.deptno;

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

select e.ename,e.comm,e.deptno,d.loc from emp e
       right outer join dept d
       on e.deptno=d.deptno;

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

select e.ename,e.comm,e.deptno,d.loc from emp e
       full outer join dept d
       on e.deptno=d.deptno;

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

select emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc
       from emp
       cross join dept;

5.3.3 自然连接,using,on子句
  自然连接是对两个表之间相同名字和数字类型的列进行的等值连接。即自动根据相同的列名进行关联数据。输出数据时，相同数据只显示一列。使用natural join句来完成。
例：select * from emp e natural join dept d;
  using子句可以指定用某个或某几个相同名字和数据类型的列作为连接条件。
例：select * from emp e join dept using(deptno);
  使用using子句创建连接时，应注意以下几点：
   如果有若干个列名称相同但数据类型不同，自然连接子句可以用using子句来替换，以指定产生等值连接的列。
   如果有多于一个列都匹配的情况，使用using子句只能指定其中的一列。
   using子句中的用到的列不能使用表名和别名作为前缀。
   natural join子句和using子句是相互排斥的，不能同时使用。自然连接条件基本上是具有相同列名的表之间的等值连接；
   如果要指定任意连接条件,或指定要连接的列，则可以使用on子句；
   用on将连接条件和其它检索条件分隔开，其它检索条件写在where子句；
   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;

5.4 练习

1.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金。
Ans：select e.ename,d.loc,e.comm from emp e,dept d where e.deptno=d.deptno and d.loc='CHICAGO' and e.comm is not null;
2.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。
Ans: select emp.ename,dept.deptno from emp,dept where emp.deptno=dept.deptno and emp.ename like '%A%';
3.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。
Ans：

select e.ename 员工,e.empno 员工编号,m.ename 经理,d.loc 工作地点,m.empno 经理编号 from emp e,emp m,dept d
       where e.mgr=m.empno(+) and d.loc in ('NEW YORK','CHICAGO');

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

select e.empno,e.ename,d.dname from emp e,dept d
       where e.deptno=d.deptno(+);

5.查询出所有购买过康帅博的客户信息以及商品信息。
Ans：

select c.custid,c.name, g.goods_name,g.goods_price from customer c,goods g,ord o
       where g.goods_name='康帅博' and g.goodsid=o.goodsid and 
       o.custid=c.custid;

6.查询所有的客户信息，如果客户购买过商品，把其购买过的商品信息也查询出来。
M1-左连接查询：

select c.custid,c.name,g.goodsid,g.goods_name,g.goods_price
        from customer c
        left outer join ord o on c.custid=o.custid
        left outer join goods g on o.goodsid=g.goodsid;

M2-右连接查询：

select c.custid,c.name,g.goodsid,g.goods_name,g.goods_price
        from goods g
        right outer join ord o on g.goodsid=o.goodsid
        right outer join customer c on o.custid=c.custid;

外连接查询时outer可以省略，即左连接，右连接都是外连接。若果省略了outer，left，right默认是内连接，内连接只查询有关联关系的数据。
M3-外连接查询：

select c.custid,c.name,g.goodsid,g.goods_name,g.goods_price
        from goods g
        right join ord o on g.goodsid=o.goodsid
        right join customer c on o.custid=c.custid;

7.查询出所有的商品信息，如果商品有销售记录，把购买商品的客户信息也查询出来。
M1：

select g.goodsid,g.goods_name,g.goods_price,c.name,c.custid
       from goods g
       left outer join ord o on g.goodsid=o.goodsid
       left outer join customer c on o.custid=c.custid;

M2：

select g.goodsid,g.goods_name,g.goods_price,c.name,c.custid
       from customer c
       right outer join ord o on c.custid=o.custid
       right outer join goods g on g.goodsid=o.goodsid;

5.5 创建表格

使用create table 创建表，创建表格的方法有三种。
a).创建表格方法1，先创建表格，后添加约束。
创建表格步骤filenewtable再分别设置general,columns,keys等设置约束条件。

create table customer
(
  cutid number,
  name  nvarchar2(20) not null
)

b).创建表格方法2，在创建表格，定义列同时添加约束。

create table customer2(
custid number primary key,
name nvarchar2(30) not null);

c).创建表格方法3，在创建表格后，添加约束。

create table customer3(
       custid number,
       name nvarchar2(50) not null,
       constraint pk_customer3 primary key(custid)
       );

  表约束的分类：表级约束和行级约束
  表约束的分类2：主键约束，唯一约束，外键约束，非空约束，检查约束
  修改表的方法：

alter table customer add constraint pk_customer primary key (SUSTID)

  例：创建客户表customer，商品表goods和订单表ord。
  Ans：

create table customer
(
  custid number,
  name  nvarchar2(20) not null
);
create table goods(
       goodsid number,
       goods_name nvarchar2(200),
       goods_price number(7,2),--7个有效数字保留两位小数
       constraint pk_item primary key(goodsid)
);
drop table ord;
create table ord(
       ordid number,
       custid number,
       goodsid number,
       constraint pk_ord primary key(ordid,custid),
       constraint fk_ord_customer foreign key (custid ) references customer(custid),
       constraint fk_ord_goods foreign key(goodsid) references goods(goodsid)
);

  创建表格后对新建的表格更新数据。
  例：往客户表customer，商品表goods和订单表ord插入5行记录。

insert into customer(custid,name) values(10000,'马化腾');
insert into customer(custid,name) values(10001,'马云');
insert into customer(custid,name) values(10002,'刘强东');
insert into customer(custid,name) values(10003,'霍金');
insert into customer(custid,name) values(10004,'巴菲特');

insert into goods(goodsid,goods_name,goods_price) values(1,'QQ会员',10.5);
insert into goods(goodsid,goods_name,goods_price) values(2,'绿钻',13.2);
insert into goods(goodsid,goods_name,goods_price) values(3,'康帅博',7.5);
insert into goods(goodsid,goods_name,goods_price) values(4,'二手电脑',22.5);
insert into goods(goodsid,goods_name,goods_price) values(5,'蓝钻',8.5);

insert into ord(custid,ordid,goodsid) values(10000,1,1);
insert into ord(custid,ordid,goodsid) values(10002,2,1);
insert into ord(custid,ordid,goodsid) values(10003,3,3);
insert into ord(custid,ordid,goodsid) values(10002,4,2);
insert into ord(custid,ordid,goodsid) values(10002,5,2);

commit;
--rollback;