JAVA培训之多表查询练习1

多表查询练习一
通过对多表查询的合并结果集、连接查询以及子查询的学习，下面我们来练习多表查询，如下所示：
(1)查询至少有一个员工的部门，并显示部门编号、部门名称、部门位置、部门人数：

• 分析：

• 查询结果集包含的列：deptno，dname，loc，部门人数；
• 查询的表：dept，emp；
• 查询条件：dept.deptno=emp.deptno，部门中至少有一个员工，即部门人数大于或等于1；

• 第一步：获得每个部门的信息，对应sql语句是：select * from dept；
• 第二步：获取每个部门的人数，利用分组查询，对应的sql语句是：select deptno,count(*) from emp group by deptno；
• 第三步：使用内连接查询以上两步的结果集，获取每个部门的编号，名称，位置及部门人数。

• 实现：

通过分析可知，对应的sql语句是：

• SELECT

FROM
dept d,(SELECT deptno,COUNT(*) FROM emp GROUP BY deptno) e

以上语句中，查询的列不能写成e.count(*)，最好在子查询中给count(*)起一个别名，如下所示：

• SELECT

d.*,e.cnt
FROM
dept d,(SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) e

将以上sql语句执行，如图1-1所示：

图1-1 多表查询练习1
图1-1中，查询语句中虽然没有指定部门人数至少为一人，但是仍然是将部门人数大于0人的部门查询出来，原因是，内连接只会查询满足条件的记录，dept表中的40部门在emp表中不存在员工，因此不满足d.deptno=e.deptno这个条件，所以该部门就被过滤掉。
(2)查询所有员工的姓名及其直接上级的姓名：

• 分析：

• 查询结果集包含的列：员工姓名，上级姓名；
• 查询的表：上级也属于员工，因此要查询的表是emp表，并且是两个emp表，emp e1，emp e2；
• 查询条件：e1.mgr=e2.empno；

emp表中，有一个员工是没有上级的，如果这里使用内连接，那么该员工的记录就会被过滤掉，因此这里使用左外连接。

• 实现：

通过分析可知，对应的sql语句是：

• SELECT

e1.ename,e2.ename
FROM
emp e1 LEFT OUTER JOIN emp e2
ON

将以上sql语句执行，如图1-2所示：

图1-2 多表查询练习2
使用左外连接查询，左表是e1，因此e1表中的所有员工姓名都被查询出来，e2表中不满足条件的位置用null代替。