oracle 学习 复杂查询、多表连接查询、课后练习题答案

oracle 学习笔记

---

–多表连接查询
Oracle自有写法：等值连接、非等值连接、外部连接、自身连接

---

–笛卡尔积 15*4=60 条
SELECT emp.*,dept.deptno,dept.dname,dept.loc FROM emp,dept ORDER BY empno;

–等值连接
SELECT emp.*,dept.deptno,dept.dname,dept.loc
FROM emp,dept
WHERE emp.deptno=dept.deptno
ORDER BY empno;

–使用别名
SELECT e.*,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
ORDER BY empno;

练习1
• 1.写一个查询，显示所有员工姓名，部门编号，
部门名称。
SELECT e.ename,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno;

• 2.写一个查询，显示所有工作在CHICAGO并且奖
金不为空的员工姓名，工作地点，奖金
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;

• 3.写一个查询，显示所有姓名中含有A字符的员
工姓名，工作地点。
SELECT e.ename,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno and e.ename like ‘%A%’;

---

–非等值连接的数据检索

---

SELECT e.ename,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

练习2
• 1.查询每个员工的编号，姓名，工资，工资等级，所在工
作城市，按照工资等级进行升序排序。
SELECT e.empno,e.ename,e.sal,s.grade,d.loc
FROM emp e,dept d,salgrade s
WHERE e.deptno=d.deptno and e.sal BETWEEN s.losal AND s.hisal
ORDER BY s.grade;

---

–外部连接
外部连接就好象是为符号(+)所在边的表增加
一个“万能”的行，这个行全部由空值组成。
它可以和另一边的表中所有不满足连接条件的
“” 行进行连接。由于这个 万能 行的各列全部
是空值，因此在连接结果中，来自“万能”行
属性值全部为空值。
–查询所有雇员姓名，部门编号，部门名称， 包括没有员工
–的部门也要显示出来

---

SELECT e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
ORDER BY e.deptno;

---

–自身连接

---

–思考：查询每个员工的姓名和直接上级姓名？
SELECT e1.ename,e2.ename
FROM emp e1,emp e2
WHERE e1.mgr=e2.empno;

练习3
• 1.查询所有工作在NEW YORK和CHICAGO的员工姓
名，员工编号，以及他们的经理姓名，经理编号。
SELECT emp.*,d.dname,d.loc,manager.ename,manager.empno
FROM emp emp,dept d,emp manager
WHERE emp.deptno=d.deptno
AND emp.mgr=manager.empno
AND (d.loc=’NEW YORK’ OR d.loc=’CHICAGO’);
select * from emp order by mgr;
• 2.第上一题的基础上，添加没有经理的员工King
，并按照员工编号排序。
SELECT emp.*,d.dname,d.loc,manager.ename,manager.empno
FROM emp emp,dept d,emp manager
WHERE emp.deptno=d.deptno
AND manager.empno(+)=emp.mgr
AND (d.loc=’NEW YORK’ OR d.loc=’CHICAGO’);

• 3.查询所有员工编号，姓名，部门名称，包括没
有部门的员工也要显示出来。
SELECT e.empno,e.ename,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno(+);
insert into emp(empno,ename) values(7799,’张三’);

---

–SQL99标准

---

–交叉连接(cross join)：会产生笛卡尔积
select * from emp cross join dept;
select * from emp,dept;–同上

---

自然连接
• 自然连接是对两个表之间相同名字和数据类型的
列进行的等值连接；
• 如果两个表之间相同名称的列的数据类型不同，
则会产生错误；
• 使用NATURAL JOIN子句来完成
SELECT empno,ename,sal,deptno,loc
FROM emp
NATURAL JOIN dept;

–using子句:指定连接条件
–注意： 1.列名不允许表名或表别名修饰
– 2.NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用。
select * from emp join dept using(deptno);

---

–on子句
select * from emp join dept on(emp.deptno = dept.deptno);
select * from emp,dept where emp.deptno = dept.deptno;

---

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

SELECT e.*,d.dname,d.loc
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;

---

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

SELECT e.*,d.deptno,d.dname,d.loc
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

---

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

SELECT e.*,d.deptno,d.dname,d.loc
FROM emp e FULL OUTER JOIN dept d
ON e.deptno=d.deptno;

练习4
• 使用SQL-99写法，完成如下练习
1.创建一个员工表和部门表的交叉连接。
SELECT e.*,d.dname,d.loc
FROM emp e
CROSS JOIN dept d;
2.使用自然连接，显示入职日期在80年5月1日之后的员工
姓名，部门名称，入职日期
SELECT empno,ename,hiredate,deptno,dname,loc
FROM emp e
NATURAL JOIN dept d
WHERE e.hiredate>’01-5月-80’;

3.使用USING子句，显示工作在CHICAGO的员工姓名，部门
名称，工作地点
SELECT e.ename,d.dname,d.loc
FROM emp e JOIN dept d USING (deptno)
WHERE d.loc=’CHICAGO’;
4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名
称，工作地点，薪资等级
SELECT e.ename,d.dname,d.loc,s.grade
FROM emp e JOIN dept d ON e.deptno=d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE d.loc=’CHICAGO’;
5.使用左连接，查询每个员工的姓名，经理姓名，没有经
理的King也要显示出来。
SELECT e.,man.
FROM emp e LEFT OUTER JOIN emp man ON e.mgr=man.empno;
6.使用右连接，查询每个员工的姓名，经理姓名，没有经
理的King也要显示出来
SELECT e.,man.
FROM emp man RIGHT OUTER JOIN emp e ON e.mgr=man.empno;

课后作业
• 1.显示员工SMITH的姓名，部门名称，直接上级名称
SELECT e.ename,d.dname,man.ename
FROM emp e,dept d,emp man
WHERE e.deptno=d.deptno
AND e.mgr=man.empno
AND e.ename=’SMITH’;
2.显示员工姓名，部门名称，工资，工资级别，要求工资
级别大于4级。
SELECT w.ename,d.dname,w.sal,s.grade
FROM emp w ,dept d,salgrade s
WHERE w.deptno=d.deptno
AND w.sal BETWEEN s.losal AND s.hisal
AND s.grade>4;
• 3.显示员工KING和FORD管理的员工姓名及其经理姓名。
SELECT w.ename emp,m.ename man
FROM emp w LEFT OUTER JOIN emp m ON w.mgr=m.empno
WHERE w.ename=’KING’ OR m.ename=’FORD’;

• 4.显示员工姓名，参加工作时间，经理名，参加工作时间，
要求参加时间比经理早。

SELECT w.ename emp,w.hiredate,m.ename man,m.hiredate
FROM emp w LEFT OUTER JOIN emp m ON w.mgr=m.empno
WHERE w.hiredate