Oracle-DQL 4- 多表查询

多表查询：

1.笛卡尔积
SELECT * FROM dept;
--查询员工的信息和其所在部门的信息
SELECT ename,job,dname,loc
FROM emp,dept;

--集合A中的所有元素和集合B中的所有元素组成的有序对的集合称为笛卡尔积，也叫做笛卡尔结果
--给两张表建立连接，再进行多表查询可以避免笛卡尔积
--在where字句中定义连接条件，就是两张表之间数据的对应关系
--表1.列1 = 表2.列2
--查询n张表，连接条件至少有n-1个

 

2.多表查询的语法规范
--多表查询时，必须使用表名对列名进行限定：表名.列名
--避免两张表中有同名的列，查询时出错；提高查询效率
SELECT emp.ename,emp.job,emp.deptno,dept.dname,dept.loc
FROM emp,dept;

--多表查询时，给表起别名，使用别名对列名进行限定
--先写from
SELECT e.ename,e.job,e.deptno,d.dname,d.loc
FROM emp e,dept d;

--如果查询的列中有相同名字，起不同的别名加以区分
SELECT e.ename,e.job,e.deptno eno,d.deptno dno,d.dname,d.loc
FROM emp e,dept d;

 

3.等值连接
--当两张表之间存在主键和外键的引用关系时，使用等值连接
SELECT e.ename,e.job,e.deptno eno,d.deptno dno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno;

--多表查询先生成笛卡尔结果，然后在其中找到满足连接条件的数据

--查询工资高于2000的员工信息和其所在部门的信息
SELECT e.*,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.sal > 2000;

 

4.不等连接
SELECT * FROM salgrade;
--查询所有员工的信息和其所在的工资等级
SELECT e.*,s.*
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

 

5.自连接
--当某张表中的数据内部有联系，外键引用了本张表的主键
--需要使用自连接将这样的关系体现出来
--将一张表看作两张表，取不同的别名，再进行查询和连接条件的建立
--查询员工的名字和他上级的名字
SELECT w.ename worker,w.mgr,m.empno,m.ename manager
FROM emp w,emp m
WHERE w.mgr = m.empno;

 

6.外连接
--当多表查询时，可能会有一些数据不满足连接条件
--如果需要显示满足连接条件的数据，使用外连接
--如果要显示某张表的所有数据，将(+)放在连接条件的另一侧
--查询员工信息和其所在部门的信息，没有员工的部门也显示
SELECT e.*,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno;

--查询员工的名字和他上级的名字，没有上级的员工也显示
SELECT w.ename worker,m.ename manager
FROM emp w,emp m
WHERE w.mgr = m.empno(+);

 

【练习】

1.查询EMP、DEPT表，输出的列包含员工姓名、工资、部门编号、部门名称、部门地址.
SELECT e.ename,e.sal,e.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno;

2.查询工资等级为3/4/5级的员工姓名，工资，工资等级
SELECT e.ename,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
AND s.grade >= 3;

3.显示职位是CLERK的员工姓名，工资，工资等级，部门名称
SELECT e.ename,e.sal,s.grade,d.dname
FROM emp e,salgrade s,dept d
WHERE e.deptno = d.deptno
AND e.sal BETWEEN s.losal AND s.hisal
AND e.job = 'CLERK';

4.查询emp表，显示员工姓名及其经理的姓名，没有经理的员工也需要显示
SELECT w.ename worker,m.ename manager
FROM emp w,emp m
WHERE w.mgr = m.empno(+);

5.列出EMP表中部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT d.dname,e.*
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno;