oracle多表连接

多表连接总结:

笛卡尔积(交叉连接)、等值连接、非等值连接、左外连接、右外连接、自身连接、自然连接、全外连接

 

1.笛卡尔积

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

      SELECT * 
         FROM EMP,DEPT;

 

2.等值连接

  增加两个表相等的条件

--查询员工编号、员工姓名、部门编号、部门名称、部门地点。
SELECT EMP.EMPNO,EMP.ENAME,EMP.DEPTNO,DEPT.DNAME,DEPT.LOC  
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;
--或者用别名SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME,D.LOC  
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

 

    使用AND运算符增加其它查询条件

--查询工作地点在NEW YORK的员工编号、姓名、部门编号、工作地点。
SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.LOC
  FROM EMP E,DEPT D
 WHERE E.DEPTNO=D.DEPTNO AND D.LOC='NEW YORK';
--4.1.4.2  写一个查询,显示所有工作在CHICAGO并且奖金不为空的员工姓名,工作地点,奖金
SELECT EMP.ENAME,DEPT.LOC,EMP.COMM
  FROM EMP,DEPT
 WHERE EMP.DEPTNO=DEPT.DEPTNO AND DEPT.LOC='CHICAGO' AND EMP.COMM IS NOT NULL;

 

3.非等值连接

     分析这些表之间的关系

--查询每个员工的姓名,工资,工资等级
SELECT E.ENAME 姓名,E.SAL 工资,S.GRADE 等级
  FROM EMP E,SALGRADE S
 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
--查询每个员工的编号,姓名,工资,工资等级,所在工作城市,按照工资等级进行升序排序.
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 S.LOSAL AND S.HISAL
 ORDER BY S.GRADE;

 

4.外部连接( 左外连接 和 右外连接 )

外部连接的符号是(+)

符号(+)所在边的表增加空行

(+)操作符只能出现在where子句中,并且不能与outer join语法同时使用。

(+)操作符只能用于实现左外连接和右外连接,而不能用于实现完全外连接。

--查询员工表和部门表的所有列,包括没有员工的部门也要显示出来。
SELECT *
  FROM EMP E, DEPT D
 --WHERE E.DEPTNO(+)=D.DEPTNO--右外连接,右边的表数据全显示
 WHERE D.DEPTNO=E.DEPTNO(+)--左外连接,左边表数据全显示

 

5. 自身连接 也叫 自连接(内部链接)

    一个表 通过某种条件和本身进行连接的一种方式,

--查询每个员的姓名和直接上级姓名?
SELECT YG.ENAME 员工名,JL.ENAME 经理名
  FROM EMP YG,EMP JL
 WHERE YG.MGR=JL.EMPNO(+)

--查询所有工作在NEW YORK和CHICAGO的员工姓名,员工编号,以及他们的经理姓名,经理编号。
SELECT YG.ENAME 员工姓名,YG.EMPNO 员工编号,JL.ENAME 经理姓名,JL.EMPNO 经理编号
  FROM EMP YG,EMP JL,DEPT D
 WHERE YG.DEPTNO=D.DEPTNO AND D.LOC IN('NEW YORK','CHICAGO')
  AND YG.MGR=JL.EMPNO
--在上一题的基础上,添加没有经理的员工KING,并按照员工编号排序。
SELECT YG.ENAME 员工姓名,YG.EMPNO 员工编号,JL.ENAME 经理姓名,JL.EMPNO 经理编号
  FROM EMP YG,EMP JL,DEPT D
 WHERE YG.DEPTNO=D.DEPTNO AND D.LOC IN('NEW YORK','CHICAGO')
  AND YG.MGR=JL.EMPNO(+)
  ORDER BY YG.EMPNO
--查询所有员工编号,姓名,部门名称,包括没有员工的部门也要显示出来。
SELECT E.EMPNO 员工编号,E.ENAME 员工姓名,D.DNAME 部门名称
  FROM EMP E,DEPT D
 WHERE E.DEPTNO(+)=D.DEPTNO

 

6.SQL标准语法

    交叉连接(笛卡尔乘积)

   多个表交叉相乘

笛卡尔乘积SQL99标准的写法:
--ORACLE
SELECT *
  FROM EMP,DEPT;
--SQL99
SELECT *
  FROM EMP CROSS JOIN DEPT;--cross join 交叉连接  
--【记】cross“可绕死”→交叉的路口可绕死我了【记】join- 表示“结合,连接”

7.自然连接

   两个表之间进行等值连接(条件名字相同,数据类型相同)

   使用所有名称和数据类型相匹配的列作为连接条件

使用SQL99标准 查询员工姓名,工资,部门名称,工作地点。
--ORACLE
SELECT *
  FROM EMP E,DEPT D
 WHERE E.DEPTNO=D.DEPTNO
--SQL99
SELECT * 
  FROM EMP NATURAL JOIN DEPT;--natural join    natural自然   join连接
--自动去匹配两张表中相同的列名(相同的数据类型)

 

8.USING子句

   using子句可以指定用某个或某些相同名字和数据类型的列作为连接条件

使用SQL99标准的USING子句 查询员工姓名,工资,部门名称,工作地点。
--ORACLE
SELECT *
  FROM EMP E,DEPT D
 WHERE E.DEPTNO=D.DEPTNO
--SQL99
SELECT *
  FROM EMP E JOIN DEPT D
 USING(DEPTNO)
--USING子句相当于oracle中where的表的连接条件
--using子句中不能加别名
--natural join和using子句不能同时使用

注意:natural join 和 using 子句相互排斥,不可以同时使用

 

9. ON子句

   指定任意连接条件,或指定要连接的列,

   用on将连接条件和其他检索条件分隔开,其他检索条件写在WHERE子句

   on 后面写等值表达式

使用SQL99标准的ON子句 查询员工姓名,工资,部门名称,工作地点。
--oracle
SELECT *
  FROM EMP E,DEPT D
 WHERE E.DEPTNO=D.DEPTNO
--SQL99
SELECT *
  FROM EMP E JOIN DEPT D
    ON E.DEPTNO=D.DEPTNO --表和表的连接条件
查询工作地点在CHICAGO的员工姓名,工资,部门名称,工作地点。
--SQL99
SELECT *
  FROM EMP E JOIN DEPT D
    ON E.DEPTNO=D.DEPTNO
 WHERE D.LOC='CHICAGO'
使用SQL99标准的ON子句 查询员工的编号、姓名、工作地点、员工经理的姓名
SELECT *
  FROM EMP YG
  JOIN EMP JL
    ON YG.MGR=JL.EMPNO
  JOIN DEPT D
    ON YG.DEPTNO=D.DEPTNO

10.左外连接

    左外连接以from子句中的左边表为基表,这个表所有的行无论是否和右边表能匹配上,左边表都会显示出来

查询员工姓名,工资,部门名称,工作地点
--ORACLE
SELECT *
  FROM EMP E,DEPT D
 WHERE D.DEPTNO=E.DEPTNO(+)
--SQL99
SELECT *
  FROM DEPT D LEFT OUTER JOIN EMP E   --left左  outer外  join连接
    ON D.DEPTNO=E.DEPTNO;

 

11. 右外连接

    右外连接以from子句中的右边表为基表,右边表的内容全部显示

查询员工姓名,工资,部门名称,工作地点
--ORACLE
SELECT *
  FROM EMP E,DEPT D
 WHERE E.DEPTNO(+)=D.DEPTNO
--SQL99
SELECT *
  FROM EMP E RIGHT OUTER JOIN DEPT D   --right右
    ON E.DEPTNO=D.DEPTNO

12. 全外连接

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

查询员工姓名,工资,部门名称,工作地点
--ORACLE
SELECT *
  FROM EMP E,DEPT D
 WHERE D.DEPTNO=E.DEPTNO(+)
 UNION
SELECT *
  FROM EMP E,DEPT D
 WHERE E.DEPTNO(+)=D.DEPTNO
--SQL99
SELECT *
  FROM EMP E FULL OUTER JOIN DEPT D  --full全  outer外  join连接
    ON E.DEPTNO=D.DEPTNO

 

练习难题

4.3.1  显示员工SMITH的姓名,部门名称,直接上级名称
SELECT E.ENAME,D.LOC,JL.ENAME
  FROM EMP E,DEPT D,EMP JL
 WHERE E.DEPTNO=D.DEPTNO
   AND E.MGR=JL.EMPNO
   AND E.ENAME='SMITH'
4.3.2  显示员工姓名,部门名称,工资,工资级别,要求工资级别大于4级
SELECT E.ENAME,D.LOC,E.SAL,S.GRADE
  FROM EMP E,DEPT D,SALGRADE S
 WHERE E.DEPTNO=D.DEPTNO
   AND E.SAL BETWEEN S.LOSAL AND S.HISAL
   AND S.GRADE>4
--4.3.3  显示员工KING和FORD管理的员工姓名及KING和FORD的经理姓名
SELECT XS.ENAME 下属姓名,ZJ.ENAME 自己姓名,JL.ENAME 经理姓名
  FROM EMP ZJ,EMP JL,EMP XS
 WHERE ZJ.MGR=JL.EMPNO(+)
   AND XS.MGR=ZJ.EMPNO
   AND ZJ.ENAME IN('KING','FORD')
4.3.4  显示员工姓名,参加工作时间,经理名,参加工作时间,要求参加工作时间比经理早。
SELECT YG.ENAME 员工姓名,YG.HIREDATE 工作时间,JL.ENAME 经理姓名,JL.HIREDATE 工作时间
  FROM EMP YG,EMP JL 
 WHERE YG.MGR=JL.EMPNO
   AND JL.HIREDATE<YG.HIREDATE

 

 

 

关于oracle多表连接分组查询

10-20

目前遇到的问题是,我用的是两张表,一个是MANUFACTURE厂家表,一个是STB_DEVICE硬件设备表,STB_DEVICE表中引用了厂家表的厂家ID作为外键,现在想要通过厂家作为分组条件查询出各个厂家在硬件设备中的分布情况。用图表显示分布情况的。rnString sql="select MANUFACTURE,count(MANUFACTURE) from STB_DEVICE group by MANUFACTURE";rn这个语句是正确的,分布图的显示比例也是正确的,可是由于坐标用的是厂家ID,在分布图中只看到显示的ID不直观,用厂家名字比较好,但是STB_DEVICE表中没有MANUFACTURENAME字段,所要将sql语句改变成两表连接,内联,左外联都可以,可是不是语句有问题,就是查出来的比例不对。rn查询没报错,但是分布图的比例不对的语句是:String sql="select MANUFACTURE_NAME,STB_DEVICE.MANUFACTURE_ID,(SELECT COUNT(MANUFACTURE_ID) FROM STB_DEVICE") FROM STB_DEVICE LEFT JOIN MANUFACTURE ON (STB_DEVICE.MANUFACTURE-_ID=MANUFACTURE.MANUFACTURE_ID) GROUP BY STB_DEVICE.MANUFACTURE_ID,MANUFACTURE_NAME ";rnrn这种写法,关键是后面的group by ,[b]该句的分组条件如何让它知道是根据STB_DEVICE表中的厂家id来比例分布的[/b],最主要的是rnSTB_DEVICE表的分布,MANUFACTURE表只是用了他的MANUFACTURE_NAME字段,让他在STB_DEVICE的分布图上用名称显示而不是ID显示就可以了。rnrn可是总是出现问题。说得有点乱,不懂的,我再重新说一下 论坛

没有更多推荐了,返回首页