Oracle多表连接

多表关联查询

什么是连接？

连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。

连接时需要的条件？

如果有n个表进行连接时，需要n-1个条件。

连接时产生笛卡尔积？

• 笛卡尔积是：

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

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

–连接条件被省略

–连接条件是无效的

• 为了避免笛卡尔积的产生，通常需要在WHERE子句中包含一个有效的连接条件

笛卡尔积写法：例如：SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc

FROM emp, dept;

 

1. 等值连接和不等值连接

1）等值连接：指的是关联条件相等的连接查询 （多表关联查询一定要有1到2个条件）

例如：查询出部门在芝加哥的员工姓名，部门编号，部门名称

select emp.ename,emp.deptno,dept.dname from emp ,dept where emp.deptno=dept.deptno and dept.loc='CHICAGO' ;

例如：查询出部门在芝加哥和纽约的员工姓名，部门编号，部门名称

select emp.ename, emp.deptno, dept.dname,dept.loc from emp, dept

where emp.deptno = dept.deptno and dept.loc in('CHICAGO','NEW YORK') ;

2）不等值连接：表关联关系不相等或使用不等值方式连接

例如：查询出员工的姓名，薪资，薪资级别 (员工表和薪资等级表没有直接的关联，没有主外键关系) 可以使用多种方法

select ename, sal, grad from emp, salgrade where sal between losal and hisal;

3）别名：

多表关联查询时，如果有列名重复，必须使用表名或者别名进行指定

例如：查询出员工的姓名，员工的编号，部门编号，部门名称，部门地址；

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

where e.deptno=d.deptno;

 

2.DDL

DDL 数据定义语句，用于数据库对象管理

DDL：数据库对象管理，包括：create创建数据库，alter更新数据库，drop删除数据库

DQL:数据查询命令，查询数据库

DML：数据更新命令，insert插入数据，update修改数据，delect删除数据命令

 

1）创建数据库

创建表customer:

create table customer(

custid number,

name nvarchar2(50) not null,

constraint pk_customer primary key (custid)

);

创建goods表：

create table goods(

goodsid number ,

goods_name nvarchar2(200),

goods_price number(7,2),

constraint pk_item primary key(goodsid)

);

创建ord表：（设置外键）

create table ord(

ordid number,

custid number,

goodsid number,

constraint pk_ord primary key(ordid),

constraint fk_ord_customer foreign key(custid) references customer(custid),

constraint fk_ord_goods foreign key(goodsid) references goods(goodsid)

);

 

2） 添加数据：（使用DML更新数据）

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.01);

insert into goods(goodsid,goods_name,goods_price) values(2,'禄钻',20.01);

insert into goods(goodsid,goods_name,goods_price) values(3,'康帅博',5);

insert into goods(goodsid,goods_name,goods_price) values(4,'二手电脑',1000.01);

insert into goods(goodsid,goods_name,goods_price) values(5,'三手电脑',500.01);

 

insert into ord(ordid,custid,goodsid) values(1,10000,1);

insert into ord(ordid,custid,goodsid) values(2,10002,1);

insert into ord(ordid,custid,goodsid) values(3,10002,3);

insert into ord(ordid,custid,goodsid) values(4,10000,3);

insert into ord(ordid,custid,goodsid) values(5,10003,3);

 

3)删除数据库

drop table customer;

drop table goods;

drop table ord;

 

3. 连接

连接分为内连接，外连接（左内连接，右内连接，全连接），自连接

 

例题：

1.查询出 马化腾购买的所有 商品信息

select g.goodsid, goods_name, goods_price from goods g,customer c,ord o

where g.goodsid = o.goodsid and o.custid = c.custid and c.name='马化腾';

2. 查询出 所有买过康帅博的所有客户信息

select c.custid, name

from customer c, goods g, ord o

where g.goodsid = o.goodsid

and o.custid = c.custid

and g.goods_name = '康帅博';

3. 查询出 所有买过康帅博的所有客户信息以及商品信息

select c.custid, name,g.goodsid,g.goods_name,g.goods_price

from customer c, goods g, ord o

where g.goodsid = o.goodsid

and o.custid = c.custid

and g.goods_name = '康帅博';

 

1） 内连接

只查询有完整关联关系的数据

例如：查询出 所有买过 康帅博 的客户信息以及商品信息，及其他客户信息

select c.custid, name,g.goodsid,g.goods_name,g.goods_price

from customer c, goods g, ord o

where g.goodsid = o.goodsid

and o.custid = c.custid ;

2）外连接（左连接，右连接，全连接）

外连接以连接的一方作为参考：查询出所有的数据，在根据关联关系查询出其他的数据，如果参考方有数据没有关联，则使用空行进行占位

注意：outer 外连是有方向的，方向必须是一致的，要么使用左连接，要么使用右连接，不能一会左连接，一会右连接

例如：查询出所有的客户信息，如果客户购买过商品，把其购买过的商品信息也查询出来

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; --左连接 从用户信息到商品信息

select c.custid, c.name, g.goodsid, g.goods_name, g.goods_price

from goods g

right outer join ord o on o.goodsid = g.goodsid

right outer join customer c on c.custid = o.custid; --右连接 从商品信息到用户信息

 

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

select g.goodsid, g.goods_name, g.goods_price, c.custid, c.name

from goods g

left outer join ord o

on g.goodsid = o.goodsid

left outer join customer c on o.custid = c.custid; --商品查到客户

--查那个就放在左边

select g.goodsid, g.goods_name, g.goods_price,c.custid, c.name

from customer c

right outer join ord o

on c.custid = o.custid

right outer join goods g on o.goodsid = g.goodsid; ----客户查到商品

 

3）内连接;

--如果省略了outer和left和right默认是内连接，内连接只查询有关联关系的数据

--内连接使用inner join 表示：即使用join查询时，如果不指定outer或inner 默认是inner join

例如：

select c.custid, c.name, g.goodsid, g.goods_name, g.goods_price

from goods g

join ord o on o.goodsid = g.goodsid

join customer c on c.custid = o.custid;

select c.custid, c.name, g.goodsid, g.goods_name, g.goods_price

from goods g

inner join ord o on o.goodsid = g.goodsid

inner join customer c on c.custid = o.custid;

 

4）全连接

查询出所有有关联关系的数据，并把关联关系的两端没有关联的数据也查询出来，对应的数据使用空行进行占位

select g.goodsid, g.goods_name, g.goods_price,c.custid, c.name

from customer c

full outer join ord o on c.custid = o.custid

full outer join goods g on o.goodsid = g.goodsid; ----客户查到商品

 

5）oracle 外连接的写法

--查询出所有的客户信息，如果客户购买过商品，把其购买过的商品信息也查询出来

--oracle 可以使用（+）表示外连接，（+）在关联关系的右侧表示左连接

select c.custid,c.name,g.goodsid,g.goods_name,goods_price

from customer c, goods g, ord o

where c.custid = o.custid(+) and o.goodsid = g.goodsid(+);

 

--查询出所有的商品信息，如果商品有销售记录，把购买过商品的客户信息也查询出来

--oracle 可以使用（+）表示外连接，（+）在关联关系的左侧表示右连接

select c.custid,c.name,g.goodsid,g.goods_name,goods_price

from customer c, goods g, ord o

where c.custid(+) = o.custid and o.goodsid(+) = g.goodsid;

6）自连接

--查询每个员工的姓名和直接上级姓名？

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

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

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

 

7）交叉连接

--连接查询需要避免出现笛卡尔积 ;交叉连接会产生笛卡尔积，需要避免使用

select e.*, d.* from emp e cross join dept d;

8）自然连接

--可以自动根据相同的列名进行关联数据，输出数据时相同的数据只显示一列

select * from emp natural join dept d;

--使用using 进行关联

select * from emp join dept using(deptno);

 

 

补充：

左连接 left outer join 或者 left join

select * from testA left outer join testB on testA.A = testB.B;

理解：A表左连接B表，将A表的所有数据全部显示 ，也将B表匹配的数据显示。

oracle 的另外一种写法 (+) 写在从表上匹配表上

select * from textA ,textB where textA.A = textB.A(+);

 

右连接 right outer join 或者 right join

select * from textA right outer join textB on textA.A = textB.A;

理解：A表右连接B表 （B表左连接A表），显示B表的所有信息，也显示A表匹配信息，

oracle 的另外一种写法 (+) 写在从表上匹配表上

select * from textA ,textB where textA.A(+) = textB.A;