A股上市公司传智教育(股票代码 003032)旗下技术交流社区北京昌平校区

 找回密码
 加入黑马

QQ登录

只需一步,快速开始

© theape 中级黑马   /  2016-8-28 10:11  /  1226 人查看  /  0 人回复  /   0 人收藏 转载请遵从CC协议 禁止商业使用本文


[SQL] 纯文本查看 复制代码
--1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
--多表连查,多行多列
SELECT d.deptno, d.dname, d.loc, e.c AS count 
FROM dept AS d, (SELECT deptno, count(*) AS c FROM emp GROUP BY deptno) AS e 
WHERE d.deptno=e.deptno;

--3. 列出所有员工的姓名及其直接上级的姓名。
--多表查询,多行多列
SELECT e.ename, m.ename AS mname FROM emp AS e, (SELECT empno, ename FROM emp) AS m WHERE e.mgr=m.empno;
--以上有bug,没有显示老板,老板的mgr是NULL加方法即可IFNULL()但是依然没有这个项
--连接查询,左连接没有强调INNER还是OUTER
SELECT e.ename, IFNULL(m.ename, 'BOSS') AS lead
FROM emp e LEFT JOIN emp m
ON e.mgr=m.empno;

/*4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

员工的编号emp.empno、姓名emp.ename、部门名称dept.dname
表:员工emp,上级emp, dept
利用子查询只能得到一半结果,下面就无法进行了,如下
SELECT e.empno, e.ename FROM emp AS e WHERE hiredate < (SELECT hiredate FROM emp AS m WHERE e.mgr=m.empno);
子查询不可以多层嵌套,建议用连接查询,可以多连接,可以得到新表之后再通过日期筛选
单嵌套,多连接,先多表查再筛选
*/
SELECT e.empno,e.ename, m.ename, d.dname 
FROM emp e 
LEFT JOIN emp m ON e.mgr=m.empno 
LEFT JOIN dept d ON d.deptno=m.deptno
WHERE e.hiredate<m.hiredate;

/*5. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
字段:部分名称(可重复)dept.dname,员工信息(可以为NULL)emp.*
dept为主表,emp为副表,两种写法如下
*/

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

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

/*7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
这个主要是对分组条件的理解
WHERE	在分组之前,连接表之后,或者子查询嵌套时筛选
HAVING	在分组之后筛选
ON		是连接表的时候筛选

*/
SELECT job, COUNT(*)
FROM emp e
GROUP BY job
HAVING MIN(sal) > 15000;

/*8. 列出在销售部工作的员工的姓名,假定不知道销售部的部门编号。
两个方法:连接查询 和 子查询(单行单列)
*/
SELECT e.ename FROM emp e 
JOIN dept d ON e.deptno=d.deptno 
WHERE d.dname='销售部';

SELECT ename FROM emp WHERE deptno=
(SELECT * FROM dept WHERE dname='销售部');


/*9. 列出薪金高于公司平均薪金的所有员工信息,所在部门名称,上级领导,工资等级。
第一段是我写的,第二段是标准答案。原来薪金不包括奖金啊。
还有NATRUAL的作用不太清楚(在这里是自然左外连接)
*/
SELECT e.*,d.dname,IFNULL(m.ename,'BOSS') lead,g.grade  FROM emp e 
LEFT JOIN dept d ON d.deptno=e.deptno 
LEFT JOIN emp m ON m.empno=e.mgr
LEFT JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal
WHERE e.sal+IFNULL(e.comm,0) > (SELECT AVG(sal+IFNULL(comm,0)) FROM emp);

SELECT e.*, d.dname, m.ename, s.grade
FROM emp e 
  NATURAL LEFT JOIN dept d
  LEFT JOIN emp m ON m.empno=e.mgr
  LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal > (SELECT AVG(sal) FROM emp);

/*10.列出与庞统从事相同工作的所有员工及部门名称。
第一段是我写的,第二段是标准答案。
连接查询我用的是自然内连接,答案用的是内连接的mysql方言。
*/
SELECT e.ename, d.dname FROM emp e
NATURAL JOIN dept d
WHERE job=(SELECT job FROM emp WHERE ename='庞统');

SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');

/*11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
第一段是我写的,第二段是标准答案。
连接查询:我的是左连接(好像不用分内外还是左右),答案是msql方言内连接而且两个WHERE条件用AND合在一起了
sal条件不用:我的是sal>他们的最大工资,答案是用ALL集合表示大于他们每一位的工资。
IN ALL ANY在子查询多汗单列中的用法与区别
field IN(SELECT field ...)匹配集合中的某一元素
field > ALL(SELECT field ...) field大于集合中任意一个元素(相当于大于集合中最大的元素,如果是<则小于最小的)
field > ANY(SELECT field ...) field大于任何一个都可以(相当于大于最小的就满足条件)
field = ANY(SELECT field ...) 与 field IN(SELECT field ...) 等价
注:
IN可以自定义集合比如:IN(a,b,c,d)而另外俩个不可以。
IN可以应用与子查询是单行多列的查询,比如:(field1,field2, ...) IN (SELECT field1,field2, ...)

*/
SELECT e.ename, e.sal, d.dname FROM emp e 
LEFT JOIN dept d ON e.deptno=d.deptno
WHERE sal>(SELECT MAX(sal) FROM emp,dept WHERE emp.deptno=dept.deptno AND emp.deptno=30);

SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND sal > ALL(SELECT sal FROM emp WHERE deptno=30)

/*13.查出年份、利润、年度增长比

*/

/*2. 列出薪金比关羽高的所有员工。
子查询(单行单列)求出关羽的sal:SELECT sal FROM emp WHERE ename='关羽';
SELECT * FROM emp WHERE sal>(关羽的sal);
*/
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='关羽');

/*6. 列出所有文员的姓名及其部门名称,部门的人数。
第一段是我写的,第二段是标准答案。
*. 部门人数作为子表,可以放到FROM后,也可以放到JOIN后(证明我的方法的正确性)
*. 两个JOIN副表都是加入到主表emp中,所以ON条件都是和emp作比较
*. c表的COUNT(*)的别名问题,count与关键字“COUNT”重复,建议别名不要用关键字
*. 左表emp不存在不满足右表dept的记录,所以用内连接即可,可以用msql方言ON条件放到WHERE里
我的代码修改如下:
SELECT e.ename, d.dname, c.num FROM emp e, dept d
LEFT JOIN (SELECT deptno,COUNT(*) num FROM emp GROUP BY deptno) c ON c.deptno=d.deptno
WHERE e.job='文员' AND e.deptno=d.deptno;
*. 左表dept不存在不满足右表(子表部门人数表)的记录,所以用内连接即可,可以用mysql方言ON条件放到WHERE里
再次修改我的代码:
SELECT e.ename, d.dname, c.num 
FROM emp e, dept d, (SELECT deptno,COUNT(*) num FROM emp GROUP BY deptno) c 
WHERE e.job='文员' AND d.deptno=e.deptno AND c.deptno=e.deptno;
两个AND后的条件e,d,c没有顺序要求,什么顺序都可以,比如:
e.deptno=d.deptno AND e.deptno=c.deptno
d.deptno=c.deptno AND d..deptno=e.deptno
建议安装容易理解的逻辑顺序去写,例如:(d表c表都加入e表)
d.deptno=e.deptno AND c.deptno=e.deptno

*/

 SELECT e.ename, d.dname, c.count FROM emp e 
 LEFT JOIN dept d ON d.deptno=e.deptno
 LEFT JOIN (SELECT deptno,COUNT(*) count FROM emp GROUP BY deptno) c ON c.deptno=e.deptno
 WHERE e.job='文员';
 
SELECT e.ename, d.dname, z.cnt
FROM emp e, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z, dept d
WHERE e.deptno=d.deptno AND z.deptno=d.deptno;
/*12.列出每个部门的员工数量、平均工资。
前两个是我写的,最后一个是答案。
我写的,表明上看是分组和连接一块儿做,实际上是先连接后分组。而且WHERE条件要写在GROUP BY分组的前面
*/
SELECT dept.dname,COUNT(*) dnum,AVG(sal) dsal FROM emp 
JOIN dept ON dept.deptno=emp.deptno
GROUP BY dept.deptno;

SELECT dept.dname,COUNT(*) dnum,AVG(sal) dsal FROM emp,dept 
WHERE dept.deptno=emp.deptno
GROUP BY dept.deptno;

SELECT d.dname, e.cnt, e.avgsal
FROM (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d
WHERE e.deptno=d.deptno;




多表查询练习.zip

3.02 KB, 下载次数: 64

0 个回复

您需要登录后才可以回帖 登录 | 加入黑马