Javaweb笔记(九)：多表查询小试牛刀(1)
2019-07-12 本文已影响0人
睿丶清
上一文章对单表的查询进行了相关的练习。这一篇则尝试进行多表的查询，这个尝试中涉及到两张表，分别是emp表，dept 表，（emp表是员工信息表，dept是部门信息表）
员工信息表
071201.png
部门信息表
071202.png
针对上边两张表进行多表查询信息的操作：
1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
首先进行分析我们需要展示的列，和需要涉及到的表：
列：部门编号，部门名称，部门位置 -> 表dept 可以查询；部门人数->表emp分组查询
表：dept ，emp
select deptno , count(*) cnt from emp group by deptno;//对emp进行分组，查询每组的人数
mysql> select deptno , count(*) cnt from emp group by deptno
-> ;
+--------+-----+
| deptno | cnt |
+--------+-----+
| 20 | 5 |
| 30 | 6 |
| 10 | 3 |
| 50 | 1 |
+--------+-----+
4 rows in set (0.00 sec)
select * from dept ;d //查询dept表的所有信息
mysql> select * from dept
-> ;
+--------+-----------+--------+
| deptno | dname | loc |
+--------+-----------+--------+
| 10 | 教研部 | 北京 |
| 20 | 学工部 | 上海 |
| 30 | 销售部 | 广州 |
| 40 | 财务部 | 武汉 |
+--------+-----------+--------+
4 rows in set (0.00 sec)
两张表进行查询结果
select d.* ,z.cnt from dept d , (select deptno , count(*) cnt from emp group by deptno) z ；
mysql> select d.* ,z.cnt from dept d , (select deptno , count(*) cnt from emp group by deptno) z;
+--------+-----------+--------+-----+
| deptno | dname | loc | cnt |
+--------+-----------+--------+-----+
| 10 | 教研部 | 北京 | 5 |
| 20 | 学工部 | 上海 | 5 |
| 30 | 销售部 | 广州 | 5 |
| 40 | 财务部 | 武汉 | 5 |
| 10 | 教研部 | 北京 | 6 |
| 20 | 学工部 | 上海 | 6 |
| 30 | 销售部 | 广州 | 6 |
| 40 | 财务部 | 武汉 | 6 |
| 10 | 教研部 | 北京 | 3 |
| 20 | 学工部 | 上海 | 3 |
| 30 | 销售部 | 广州 | 3 |
| 40 | 财务部 | 武汉 | 3 |
| 10 | 教研部 | 北京 | 1 |
| 20 | 学工部 | 上海 | 1 |
| 30 | 销售部 | 广州 | 1 |
| 40 | 财务部 | 武汉 | 1 |
+--------+-----------+--------+-----+
16 rows in set (0.00 sec)
从上述查询结果看到，会有相关数据的重复，和一些无用错误的数据，这样在两张表又一个相同的关联的字段deptno，通过这个字段来去除无用的数据(笛卡尔积)
select d.* ,z.cnt from dept d , (select deptno , count(*) cnt from emp group by deptno) z where d.deptno=z.deptno;
mysql> select d.* ,z.cnt from dept d , (select deptno , count(*) cnt from emp group by deptno) z where d.deptno=z.deptno;
+--------+-----------+--------+-----+
| deptno | dname | loc | cnt |
+--------+-----------+--------+-----+
| 10 | 教研部 | 北京 | 3 |
| 20 | 学工部 | 上海 | 5 |
| 30 | 销售部 | 广州 | 6 |
+--------+-----------+--------+-----+
3 rows in set (0.00 sec)
针对上述的编写使用的是mysql语言自有的编写方式，为了是sql语句在不同的数据库中同样可以使用我们需要在上述语言进行标准化的修改，即","用"inner join"来替代，"where"用"on"来替代，替代之后的sql语句为：
select d.* ,z.cnt from dept d inner join (select deptno , count(*) cnt from emp group by deptno) z on d.deptno=z.deptno;
mysql> select d.* ,z.cnt from dept d inner join (select deptno , count(*) cnt from emp group by deptno) z on d.deptno=z.deptno;
+--------+-----------+--------+-----+
| deptno | dname | loc | cnt |
+--------+-----------+--------+-----+
| 10 | 教研部 | 北京 | 3 |
| 20 | 学工部 | 上海 | 5 |
| 30 | 销售部 | 广州 | 6 |
+--------+-----------+--------+-----+
3 rows in set (0.00 sec)
总结：我们这里用到了多表查询中的内链接查询，涉及到的知识点有：内链接查询概念原理，inner join，on，笛卡尔积，如何去除笛卡尔积；
2. 列出所有员工的姓名及其直接上级的姓名。
分析：
列：姓名，上级姓名
表：emp
查询员工姓名和员工领导的编号：
select ename ,mgr from emp;
mysql> select ename ,mgr from emp;
+-----------+------+
| ename | mgr |
+-----------+------+
| 甘宁 | 1013 |
| 黛绮丝 | 1006 |
| 殷天正 | 1006 |
| 刘备 | 1009 |
| 谢逊 | 1006 |
| 关羽 | 1009 |
| 张飞 | 1009 |
| 诸葛亮 | 1004 |
| 曾阿牛 | NULL |
| 韦一笑 | 1006 |
| 周泰 | 1008 |
| 陈普 | 1006 |
| 庞统 | 1004 |
| 黄盖 | 1007 |
| 张三 | 1001 |
+-----------+------+
15 rows in set (0.00 sec)
查询编号和对应员工姓名
select ename , empno from emp;
mysql> select ename , empno from emp;
+-----------+-------+
| ename | empno |
+-----------+-------+
| 甘宁 | 1001 |
| 黛绮丝 | 1002 |
| 殷天正 | 1003 |
| 刘备 | 1004 |
| 谢逊 | 1005 |
| 关羽 | 1006 |
| 张飞 | 1007 |
| 诸葛亮 | 1008 |
| 曾阿牛 | 1009 |
| 韦一笑 | 1010 |
| 周泰 | 1011 |
| 陈普 | 1012 |
| 庞统 | 1013 |
| 黄盖 | 1014 |
| 张三 | 1015 |
+-----------+-------+
15 rows in set (0.00 sec)
内联查询信息，在内联查询中会出现笛卡尔积，去除笛卡尔积
select e1.,e2. from (select ename ,mgr from emp) e1 inner join (select ename , empno from emp) e2 on e1.mgr=e2.empno;
mysql> select e1.*,e2.* from (select ename ,mgr from emp) e1 inner join (select ename , empno from emp) e2 on e1.mgr=e2.empno;
+-----------+------+-----------+-------+
| ename | mgr | ename | empno |
+-----------+------+-----------+-------+
| 甘宁 | 1013 | 庞统 | 1013 |
| 黛绮丝 | 1006 | 关羽 | 1006 |
| 殷天正 | 1006 | 关羽 | 1006 |
| 刘备 | 1009 | 曾阿牛 | 1009 |
| 谢逊 | 1006 | 关羽 | 1006 |
| 关羽 | 1009 | 曾阿牛 | 1009 |
| 张飞 | 1009 | 曾阿牛 | 1009 |
| 诸葛亮 | 1004 | 刘备 | 1004 |
| 韦一笑 | 1006 | 关羽 | 1006 |
| 周泰 | 1008 | 诸葛亮 | 1008 |
| 陈普 | 1006 | 关羽 | 1006 |
| 庞统 | 1004 | 刘备 | 1004 |
| 黄盖 | 1007 | 张飞 | 1007 |
| 张三 | 1001 | 甘宁 | 1001 |
+-----------+------+-----------+-------+
14 rows in set (0.00 sec)
根据题目具体优化查询语句
select e1.ename '员工', e2.ename '领导' from (select ename ,mgr from emp) e1 inner join (select ename , empno from emp) e2 on e1.mgr=e2.empno;
mysql> select e1.ename '员工', e2.ename '领导' from (select ename ,mgr from emp) e1 inner join (select ename , empno from emp) e2 on e1.mgr=e2.empno;
+-----------+-----------+
| 员工 | 领导 |
+-----------+-----------+
| 甘宁 | 庞统 |
| 黛绮丝 | 关羽 |
| 殷天正 | 关羽 |
| 刘备 | 曾阿牛 |
| 谢逊 | 关羽 |
| 关羽 | 曾阿牛 |
| 张飞 | 曾阿牛 |
| 诸葛亮 | 刘备 |
| 韦一笑 | 关羽 |
| 周泰 | 诸葛亮 |
| 陈普 | 关羽 |
| 庞统 | 刘备 |
| 黄盖 | 张飞 |
| 张三 | 甘宁 |
+-----------+-----------+
14 rows in set (0.00 sec)
但是在查询中我们会发现'曾阿牛'出现在表中，则这里用到的则是外连接
select e1.ename '员工', ifnull(e2.ename ,'BOSS') '领导' from (select ename ,mgr from emp) e1 left outer join (select ename , empno from emp) e2 on e1.mgr=e2.empno;
mysql> select e1.ename '员工', ifnull(e2.ename ,'BOSS') '领导' from (select ename ,mgr from emp) e1 left outer join (select ename , empno from emp) e2 on e1.mgr=e2.empno;
+-----------+-----------+
| 员工 | 领导 |
+-----------+-----------+
| 甘宁 | 庞统 |
| 黛绮丝 | 关羽 |
| 殷天正 | 关羽 |
| 刘备 | 曾阿牛 |
| 谢逊 | 关羽 |
| 关羽 | 曾阿牛 |
| 张飞 | 曾阿牛 |
| 诸葛亮 | 刘备 |
| 曾阿牛 | BOSS |
| 韦一笑 | 关羽 |
| 周泰 | 诸葛亮 |
| 陈普 | 关羽 |
| 庞统 | 刘备 |
| 黄盖 | 张飞 |
| 张三 | 甘宁 |
+-----------+-----------+
15 rows in set (0.01 sec)
总结：这道题目涉及到的是单张表的多次查询，还有就是对于寻找去除笛卡尔积的方法,外联查询的知识（左外链，右外链，全外链（mysql无法实现）），通过'曾阿牛'信息的空缺可以看出内链接，外链接查询的区别；
4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
列：员工编号empno，姓名ename deptno>emp 部门名称dname,deptno->dept
表：emp e1 ,emp e2,dept d
查询员工编号，姓名，领导编号，部门编号，入职日期（员工）
mysql> select empno,ename,mgr ,deptno ,hiredate from emp;
+-------+-----------+------+--------+------------+
| empno | ename | mgr | deptno | hiredate |
+-------+-----------+------+--------+------------+
| 1001 | 甘宁 | 1013 | 20 | 2000-12-17 |
| 1002 | 黛绮丝 | 1006 | 30 | 2001-02-20 |
| 1003 | 殷天正 | 1006 | 30 | 2001-02-22 |
| 1004 | 刘备 | 1009 | 20 | 2001-04-21 |
| 1005 | 谢逊 | 1006 | 30 | 2001-09-28 |
| 1006 | 关羽 | 1009 | 30 | 2001-05-01 |
| 1007 | 张飞 | 1009 | 10 | 2001-09-01 |
| 1008 | 诸葛亮 | 1004 | 20 | 2007-04-19 |
| 1009 | 曾阿牛 | NULL | 10 | 2001-11-17 |
| 1010 | 韦一笑 | 1006 | 30 | 2001-09-08 |
| 1011 | 周泰 | 1008 | 20 | 2007-05-23 |
| 1012 | 陈普 | 1006 | 30 | 2001-12-03 |
| 1013 | 庞统 | 1004 | 20 | 2001-12-03 |
| 1014 | 黄盖 | 1007 | 10 | 2002-01-23 |
| 1015 | 张三 | 1001 | 50 | 2013-05-01 |
+-------+-----------+------+--------+------------+
15 rows in set (0.00 sec)
查询员工编号，姓名，入职日期(领导)
mysql> select empno,ename,hiredate from emp;
+-------+-----------+------------+
| empno | ename | hiredate |
+-------+-----------+------------+
| 1001 | 甘宁 | 2000-12-17 |
| 1002 | 黛绮丝 | 2001-02-20 |
| 1003 | 殷天正 | 2001-02-22 |
| 1004 | 刘备 | 2001-04-21 |
| 1005 | 谢逊 | 2001-09-28 |
| 1006 | 关羽 | 2001-05-01 |
| 1007 | 张飞 | 2001-09-01 |
| 1008 | 诸葛亮 | 2007-04-19 |
| 1009 | 曾阿牛 | 2001-11-17 |
| 1010 | 韦一笑 | 2001-09-08 |
| 1011 | 周泰 | 2007-05-23 |
| 1012 | 陈普 | 2001-12-03 |
| 1013 | 庞统 | 2001-12-03 |
| 1014 | 黄盖 | 2002-01-23 |
| 1015 | 张三 | 2013-05-01 |
+-------+-----------+------------+
15 rows in set (0.00 sec)
查询员工和领导信息集合表(去笛卡尔积)
mysql> select e1.empno,e1.ename,e1.hiredate,e1.mgr, e2.ename,e2.hiredate,e2.empno from emp e1 inner join emp e2 on e1.mgr=e2.empno;
+-------+-----------+------------+------+-----------+------------+-------+
| empno | ename | hiredate | mgr | ename | hiredate | empno |
+-------+-----------+------------+------+-----------+------------+-------+
| 1001 | 甘宁 | 2000-12-17 | 1013 | 庞统 | 2001-12-03 | 1013 |
| 1002 | 黛绮丝 | 2001-02-20 | 1006 | 关羽 | 2001-05-01 | 1006 |
| 1003 | 殷天正 | 2001-02-22 | 1006 | 关羽 | 2001-05-01 | 1006 |
| 1004 | 刘备 | 2001-04-21 | 1009 | 曾阿牛 | 2001-11-17 | 1009 |
| 1005 | 谢逊 | 2001-09-28 | 1006 | 关羽 | 2001-05-01 | 1006 |
| 1006 | 关羽 | 2001-05-01 | 1009 | 曾阿牛 | 2001-11-17 | 1009 |
| 1007 | 张飞 | 2001-09-01 | 1009 | 曾阿牛 | 2001-11-17 | 1009 |
| 1008 | 诸葛亮 | 2007-04-19 | 1004 | 刘备 | 2001-04-21 | 1004 |
| 1010 | 韦一笑 | 2001-09-08 | 1006 | 关羽 | 2001-05-01 | 1006 |
| 1011 | 周泰 | 2007-05-23 | 1008 | 诸葛亮 | 2007-04-19 | 1008 |
| 1012 | 陈普 | 2001-12-03 | 1006 | 关羽 | 2001-05-01 | 1006 |
| 1013 | 庞统 | 2001-12-03 | 1004 | 刘备 | 2001-04-21 | 1004 |
| 1014 | 黄盖 | 2002-01-23 | 1007 | 张飞 | 2001-09-01 | 1007 |
| 1015 | 张三 | 2013-05-01 | 1001 | 甘宁 | 2000-12-17 | 1001 |
+-------+-----------+------------+------+-----------+------------+-------+
14 rows in set (0.01 sec)
筛选受雇日期早于直接上级的信息
mysql> select e1.empno,e1.ename,e1.hiredate,e1.mgr, e2.ename,e2.hiredate,e2.empno from emp e1 inner join emp e2 on e1.mgr=e2.empno and e1.hiredate<e2.hiredate;
+-------+-----------+------------+------+-----------+------------+-------+
| empno | ename | hiredate | mgr | ename | hiredate | empno |
+-------+-----------+------------+------+-----------+------------+-------+
| 1001 | 甘宁 | 2000-12-17 | 1013 | 庞统 | 2001-12-03 | 1013 |
| 1002 | 黛绮丝 | 2001-02-20 | 1006 | 关羽 | 2001-05-01 | 1006 |
| 1003 | 殷天正 | 2001-02-22 | 1006 | 关羽 | 2001-05-01 | 1006 |
| 1004 | 刘备 | 2001-04-21 | 1009 | 曾阿牛 | 2001-11-17 | 1009 |
| 1006 | 关羽 | 2001-05-01 | 1009 | 曾阿牛 | 2001-11-17 | 1009 |
| 1007 | 张飞 | 2001-09-01 | 1009 | 曾阿牛 | 2001-11-17 | 1009 |
+-------+-----------+------------+------+-----------+------------+-------+
6 rows in set (0.00 sec)
整合部门信息
mysql> select e1.empno,e1.ename,e1.hiredate,e1.mgr, e2.ename,e2.hiredate,e2.empno ,d.dname from emp e1 inner join emp e2 inner join dept d on e1.mgr=e2.empno and e1.hiredate<e2.hiredate and e1.deptno=d.deptno;
+-------+-----------+------------+------+-----------+------------+-------+-----------+
| empno | ename | hiredate | mgr | ename | hiredate | empno | dname |
+-------+-----------+------------+------+-----------+------------+-------+-----------+
| 1001 | 甘宁 | 2000-12-17 | 1013 | 庞统 | 2001-12-03 | 1013 | 学工部 |
| 1002 | 黛绮丝 | 2001-02-20 | 1006 | 关羽 | 2001-05-01 | 1006 | 销售部 |
| 1003 | 殷天正 | 2001-02-22 | 1006 | 关羽 | 2001-05-01 | 1006 | 销售部 |
| 1004 | 刘备 | 2001-04-21 | 1009 | 曾阿牛 | 2001-11-17 | 1009 | 学工部 |
| 1006 | 关羽 | 2001-05-01 | 1009 | 曾阿牛 | 2001-11-17 | 1009 | 销售部 |
| 1007 | 张飞 | 2001-09-01 | 1009 | 曾阿牛 | 2001-11-17 | 1009 | 教研部 |
+-------+-----------+------------+------+-----------+------------+-------+-----------+
6 rows in set (0.00 sec)
优化查询字段
mysql> select e1.empno,e1.ename,e1.deptno, d.dname from emp e1 inner join emp e2 inner join dept d on e1.mgr=e2.empno and e1.hiredate<e2.hiredate and e1.deptno=d.deptno;
+-------+-----------+--------+-----------+
| empno | ename | deptno | dname |
+-------+-----------+--------+-----------+
| 1001 | 甘宁 | 20 | 学工部 |
| 1002 | 黛绮丝 | 30 | 销售部 |
| 1003 | 殷天正 | 30 | 销售部 |
| 1004 | 刘备 | 20 | 学工部 |
| 1006 | 关羽 | 30 | 销售部 |
| 1007 | 张飞 | 10 | 教研部 |
+-------+-----------+--------+-----------+
6 rows in set (0.00 sec)
