oracle相似的连接,Oracle 连接查询

1.什么是连接查询？(B)

很多时候我们需要查询的数据并不是来源于同一张表，而是来源于多张表，而这种一个查询需要对多张表进行操作，就称为连接查询。

2.如何进行表的连接查询？

连接查询有两种方式：SQL 1992(1992年发布的标准) 和SQL 1999(1999年发布的标准)

3.SQL1992：(A)

3.1 语法：

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;

注：1.在 WHERE 子句中写入连接条件

2.在 WHERE 子句中写入连接条件当多个表中有重名列时，必须在列的名字前加上表名作为前缀(可使用表别名)

3.2 连接类型：

1.笛卡尔积 (表*表)

例： select * from emp,dept;

emp表中的每一条数据会分别与dept表的每一条数据连接

2.等值连接 (列=列)

例：查询员工的部门名称

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

3.非等值连接 (列!=列)

--查询员工的薪水等级

select e.ename,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal

4.自连接 (表自己连接自己)

--查询员工上司的姓名：以"xxx的上司是xxx"方式显示

select e1.ename||'的上司是'|| e2.ename from emp e1,emp e2 where e1.mgr = e2.empno

5.外连接 (在满足一张表的内容都显示的基础上，连接另外一张表，如果连接匹配则正常显示，连接不匹配，另外一张表补null)

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

“(+)” 在等号右边，我们称这次查询为“左外连接查询”

“(+)” 在等号左边，我们成这次查询为“右外连接查询”

“(+)” 在等号哪一边，哪张表就补null

3.3SQL 1992 缺点：

1.表的连接条件和表的过滤条件都放在WHERE子句中，当条件过多时容易混淆

2.外连接的“左外连接查询”、“右外连接查询”不太容易辩别

4.SQL1999(A)

4.1 语法：

SELECT table1.column, table2.column FROM table1 连接类型 JOIN table2 ON 连接条件

在SQL1999 中弥补了SQL1992的缺点，使用 JOIN关键字 连接 ON关键字进行连接条件判断，WHERE进行过滤。并且外连接的“左外连接查询”、“右外连接查询”分别使用LEFT OUTER JOIN、RIGHT OUTER JOIN

4.2 连接类型：

1.CROSS JOIN 交叉连接(笛卡尔积)

select * from emp cross join dept

2.NATURAL JOIN 自然连接,找到两张表中具有相同名称列，进行等值连接

select * from emp natural join dept

3.INNER JOIN 内连接 (inner关键字可以省略)

--等值连接

select e.ename,d.dname from emp e INNER JOIN dept d on e.deptno = d.deptno where e.sal > 2000

--非等值连接

select e.ename,s.grade from emp e INNER JOIN salgrade s on e.sal between s.losal and s.hisal

--自连接

select e1.ename || '的上司是' || e2.ename from emp e1 INNER JOIN emp e2 on e1.mgr = e2.empno

--外连接(outer关键字可以省略)

4.LEFT OUTER JOIN

select * from emp e LEFT OUTER JOIN dept d on e.deptno = d.deptno

5.RIGHT OUTER JOIN

select * from emp e RIGHT OUTER JOIN dept d on e.deptno = d.deptno

6.FULL OUTER JOIN

select * from emp e FULL OUTER JOIN dept d on e.deptno = d.deptno

5.如何进行多表连接？(A)

例：

--SQL 1992

selecte.ename, e.sal, d.dname, s.gradefromemp e, dept d, salgrade swhere e.deptno =d.deptnoand e.sal between s.losal ands.hisal--SQL 1999

selecte.ename, e.sal, d.dname, s.gradefromemp ejoindept don e.deptno =d.deptnojoinsalgrade son e.sal between s.losal and s.hisal

6.子查询(A)：

6.1 什么是子查询？

在SQL中允许多层嵌套。而子查询，即嵌套在其他查询中的查询。

理解子查询的关键在于把子查询当作一张表来看待。外层的语句可以把内嵌的子查询返回的结果当成一张表使用。

–子查询要用括号括起来

–将子查询放在比较运算符的右边(增强可读性)

6.2 子查询的种类(A)

1.在where中使用的子查询

--查询部门名称为RESEARCH、SALES

select *

fromempwhere deptno in(select deptno from dept where dname in ('RESEARCH', 'SALES'))--查询有哪些人的薪水是在整个雇员的平均薪水之上的

select ename,empno, sal, sal+nvl(comm,0)fromempwhere sal+nvl(comm,0)>(select avg(sal+nvl(comm,0)) fromemp);--查在雇员中有哪些人是经理人

selectempno, enamefromempwhere empno in(select distinct mgr fromemp

);--找出部门编号为20的所有员工中收入最高的职员

select * fromempwhere sal >= all(selectsalfromempwhere deptno = 20)anddeptno= 20

2.在from后面使用子查询

--我们要求每个部门平均薪水的等级

selectt1.deptno, t1.savg, s.gradefrom (select deptno, avg(sal) savg from emp group bydeptno) t1joinsalgrade son t1.savg between s.losal ands.hisal--求每个部门薪水的平均等级

select t1.deptno, avg(t1.grade)from (selecte.deptno, s.gradefromemp e, salgrade swhere e.sal between s.losal ands.hisal) t1group by t1.deptno