[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;
收藏
淘帖
踩
