MySQL作业题
三个表情况
mysql> select * from emp; // 从emp表查询所有数据。* 代表所有
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
1、取得每个部门最高薪水的人员名称
没想到第一个就写了老半天，害！
自己写的
先找每个部分最高薪水，形成一个表，并起别名为n(不起别名报错)
将员工表和表n连接
根据薪水相等，找出这四个人
看视频后：
我怎么没有表连接的on语句啊…答案应该是侥幸对了
mysql> select e.ename,d.* from emp e
-> join (select deptno,max(sal) maxsal from emp group by deptno) d
-> on e.deptno = d.deptno and e.sal = d.maxsal;
+-------+--------+---------+
| ename | deptno | maxsal |
+-------+--------+---------+
| BLAKE | 30 | 2850.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 5000.00 |
| FORD | 20 | 3000.00 |
+-------+--------+---------+
4 rows in set (0.01 sec)
2.哪些人的薪水在部门的平均薪水之上
根据上一个题的思路，先找部门平均薪水，形成表n
mysql> select avg(sal),deptno
-> from emp
-> group by deptno;
+-------------+--------+
| avg(sal) | deptno |
+-------------+--------+
| 2916.666667 | 10 |
| 2175.000000 | 20 |
| 1566.666667 | 30 |
+-------------+--------+
3 rows in set (0.02 sec)
然后在e表中，找到部门相同，但是薪水大于平均薪水的人，很快昂
mysql> select e.ename,e.sal
-> from emp e
-> join (select avg(sal) sal,deptno from emp group by deptno) n
-> on e.sal > n.sal and e.deptno = n.deptno;
+-------+---------+
| ename | sal |
+-------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.03 sec)
3、取得部门中（所有人的）平均的薪水等级
第一步，取得所有人的薪水等级
mysql> select e.ename,e.deptno,sg.grade
-> from emp e
-> join salgrade sg
-> on e.sal between sg.losal and sg.hisal;
+--------+--------+-------+
| ename | deptno | grade |
+--------+--------+-------+
| SMITH | 20 | 1 |
| ALLEN | 30 | 3 |
| WARD | 30 | 2 |
| JONES | 20 | 4 |
| MARTIN | 30 | 2 |
| BLAKE | 30 | 4 |
| CLARK | 10 | 4 |
| SCOTT | 20 | 4 |
| KING | 10 | 5 |
| TURNER | 30 | 3 |
| ADAMS | 20 | 1 |
| JAMES | 30 | 1 |
| FORD | 20 | 4 |
| MILLER | 10 | 2 |
+--------+--------+-------+
14 rows in set (0.00 sec)
第二步，将这个表中的等级按部门平均
mysql> select deptno, avg(grade)
-> from (select e.ename, e.deptno, sg.grade
-> from emp e
-> join salgrade sg
-> on e.sal between sg.losal and sg.hisal) as n
-> group by
-> deptno;
+--------+------------+
| deptno | avg(grade) |
+--------+------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+------------+
3 rows in set (0.00 sec)
看视频后：
在进行表连接后没有必要把它看成临时表，而是直接在后面进行分组就可以了
mysql> select e.deptno, avg(s.grade)
-> from emp e
-> join salgrade s
-> on e.sal between s.losal and s.hisal
-> group by deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
3 rows in set (0.00 sec)
4、不准用组函数（Max），取得最高薪水（给出两种解决方案）
方法一：
第一反应，排序以后形成一个表，然后取第一行
或者升序，统计数量，然后找最后一个
mysql> select sal from emp order by sal desc
-> limit 0,1;
+---------+
| sal |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)
方法二：
想不出来，看答案
神之操作a.sal < b.sal，怎么取的呢，就是对于b中的一个值，将a中所有比它小的都得到，
这样最大值就不会显示在这个表中
mysql> select a.sal
-> from emp a
-> join emp b
-> on a.sal < b.sal;
（下面显示部分）
+---------+
| sal |
+---------+
| 800.00 | //b=800时，没有值符合条件；b= 1600，共有6个值符合条件
| 1250.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 1300.00 |
| 800.00 | //此时，b = 1250，共有3个值符合条件
| 1100.00 |
| 950.00 |
| 800.00 | //b = 2975....
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
......
+---------+
89 rows in set (0.00 sec)
然后用dinsinct去重
mysql> select distinct a.sal
-> from emp a
-> join emp b
-> on a.sal < b.sal;
+---------+
| sal |
+---------+
| 800.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 1300.00 |
| 1600.00 |
| 2850.00 |
| 2450.00 |
| 2975.00 |
| 3000.00 |
+---------+
11 rows in set (0.00 sec)
最后找到不等于上表中的值
mysql> select sal from emp
-> where sal not in (
-> select distinct a.sal
-> from emp a
-> join emp b
-> on a.sal < b.sal);
+---------+
| sal |
+---------+
| 5000.00 |
+---------+
1 row in set (0.01 sec)
看了这个答案，发现上面自己写的几道题应该是写的麻烦了，后面写注意
5.取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
第一步，每个部门的平均薪水
mysql> select deptno, avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
第二步，取得最高薪水部分对应编号
用max或者排序
mysql> select deptno, max(avgsal)
-> from (select deptno, avg(sal) avgsal from emp group by deptno) new;
+--------+-------------+
| deptno | max(avgsal) |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)
注意：这样写对吗，看似输出的部门编号正确
在用select deptno, avg(sal) from emp group by deptno;生成一个表以后，在表中查询最大值，和部门编号并没有任何关系
因此输出的10只是因为在10在第一行而顺序输出的编号，实则并不正确
如果将最大值改成最小值以后，输出明显错误
mysql> select deptno, min(avgsal)
-> from (select deptno, avg(sal) avgsal from emp group by deptno) new;
+--------+-------------+
| deptno | min(avgsal) |
+--------+-------------+
| 10 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)
所以这里应该是取这个最大值，然后去表中匹配对应部门编号
mysql> select max(avgsal)
-> from (select deptno, avg(sal) avgsal from emp group by deptno) new;
然后再与根据部门分组的平均值相匹配
mysql> select deptno, avg(sal) as avgsal
-> from emp
-> group by
-> deptno
-> having avgsal = (select max(new.avgsal) from (select deptno, avg(sal) avgsal from emp group by deptno) new);
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)
或者（排序）
mysql> select deptno,avg(sal) avgsal
-> from emp
-> group by deptno
-> order by avgsal desc
-> limit 0,1;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)
6.取得平均薪水最高的部门的部门名称
接上一题，上一题找的是部分编号，这题找名称，需要连接另一个表，然后查询
mysql> select d.dname
-> from dept d
-> join (select deptno, avg(sal) avgsal from emp group by deptno order by avgsal desc limit 0,1) as t
-> on d.deptno = t.deptno;
+------------+
| dname |
+------------+
| ACCOUNTING |
+------------+
1 row in set (0.00 sec)
感觉我怎么呆呆的啊，看着一样，就呆呆把上面的结果套了一下，唉
可以先将两个表连接，然后，再根据部门编号分组，求平均值，然后排序取第一个
mysql> select d.dname, avg(e.sal) as avgsal
-> from emp e
-> join dept d
-> on d.deptno = e.deptno
-> group by e.deptno
-> order by avgsal desc
-> limit 0,1;
+------------+-------------+
| dname | avgsal |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
1 row in set (0.01 sec)
求平均薪水的等级最低的部门的部门名称
先求平均薪水
mysql> select deptno, avg(sal)
-> from emp
-> group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
再求平均薪水的等级最低的部门
mysql> select n.deptno, n.avgsal,s.grade
-> from salgrade s
-> join (select deptno, avg(sal) avgsal from emp group by deptno) as n
-> on n.avgsal between losal and hisal
-> order by grade asc
-> limit 0,1
-> ;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 30 | 1566.666667 | 3 |
+--------+-------------+-------+
1 row in set (0.00 sec)
再连接部门表得到部门名称
mysql> select d.dname
-> from dept d
-> join (
-> select n.deptno, n.avgsal,s.grade
-> from salgrade s
-> join (select deptno, avg(sal) avgsal from emp group by deptno) as n
-> on n.avgsal between losal and hisal
-> order by grade asc
-> limit 0,1) as t
-> on d.deptno = t.deptno;
+-------+
| dname |
+-------+
| SALES |
+-------+
1 row in set (0.00 sec)
感觉还是繁琐了，后面看视频在学习咋改进吧，先自己都做完
看视频后，发现不能用limit取等级最低，因为等级最低的可能有多个部门，所以要先求出最低的等级
因为最低薪水的等级肯定是最低的，根据这个取最低的等级
最低平均薪水
mysql> select avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1
-> ;
+-------------+
| avgsal |
+-------------+
| 1566.666667 |
+-------------+
1 row in set (0.00 sec)
最低平均薪水的等级
mysql> select grade from salgrade where (select avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal;
+-------+
| grade |
+-------+
| 3 |
+-------+
1 row in set (0.00 sec)
平均薪水等级的部门名称
mysql> select t.*, s.grade
-> from (select d.dname, avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) as t
-> join salgrade s
-> on t.avgsal between s.losal and s.hisal;
+------------+-------------+-------+
| dname | avgsal | grade |
+------------+-------------+-------+
| SALES | 1566.666667 | 3 |
| ACCOUNTING | 2916.666667 | 4 |
| RESEARCH | 2175.000000 | 4 |
+------------+-------------+-------+
3 rows in set (0.00 sec)
最低平均薪水等级的部门名称（条件：上表中的等级 等于 最低平局薪水等级）
mysql> select t.*, s.grade
-> from (select d.dname, avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) as t
-> join salgrade s
-> on t.avgsal between s.losal and s.hisal
-> where s.grade =
-> (select grade from salgrade where (select avg(sal) avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);
+-------+-------------+-------+
| dname | avgsal | grade |
+-------+-------------+-------+
| SALES | 1566.666667 | 3 |
+-------+-------------+-------+
1 row in set (0.00 sec)
8、取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的领导人姓名
这个题的是什么意思呢，就是不当领导的员工（普通员工）的最高薪水，然后求比这个最高薪水还高的领导人的姓名
所以肯定是自连接
先求两个表，一个是普通员工的薪水表，另一个是领导的薪水表
首先试了一下，这样报错
mysql> select empeno,ename,sal
-> from emp
-> where empno not in mgr;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mgr' at line 3
然后就规规矩矩先找出所有领导的编号
mysql> select distinct mgr
-> from emp
-> where mgr is not null;
±-----+
| mgr |
±-----+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
±-----+
6 rows in set (0.00 sec)
然后找出所有普通员工的编号和薪水，并排序
注意：not in在使用的时候，后面小括号中记得排除NULL。
mysql> select ename,sal,empno
-> from emp
-> where empno not in(
-> select distinct mgr
-> from emp
-> where mgr is not null)
-> order by sal desc
-> ;
+--------+---------+-------+
| ename | sal | empno |
+--------+---------+-------+
| ALLEN | 1600.00 | 7499 |
| TURNER | 1500.00 | 7844 |
| MILLER | 1300.00 | 7934 |
| WARD | 1250.00 | 7521 |
| MARTIN | 1250.00 | 7654 |
| ADAMS | 1100.00 | 7876 |
| JAMES | 950.00 | 7900 |
| SMITH | 800.00 | 7369 |
+--------+---------+-------+
8 rows in set (0.00 sec)
这时已经找到了普通员工的最高薪水，然后就找比这个薪水还高的领导姓名
找所有领导
mysql> select ename, sal, empno
-> from emp
-> where empno in(
-> select distinct mgr
-> from emp
-> where mgr is not null);
+-------+---------+-------+
| ename | sal | empno |
+-------+---------+-------+
| JONES | 2975.00 | 7566 |
| BLAKE | 2850.00 | 7698 |
| CLARK | 2450.00 | 7782 |
| SCOTT | 3000.00 | 7788 |
| KING | 5000.00 | 7839 |
| FORD | 3000.00 | 7902 |
+-------+---------+-------+
6 rows in set (0.00 sec)
找薪水大于最多薪水的领导姓名
mysql> select l.ename,l.sal
-> from (select ename, sal from emp where empno in(select distinct mgr from emp where mgr is not null)) as l
-> where l.sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)
这个题老师给的答案应该是错的，虽然答案是一样的
找的是领导人姓名，而不是在所有员工里找大于1600的
看视频后：
老师没错，我错了，比普通员工的最高薪水还高的一定是领导！！！！！
所以不用筛选领导，直接在所有员工里找就行了
select ename,sal from emp where sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
9、取得薪水最高的前五名员工
突然简单，不太适应
mysql> select ename, sal
-> from emp
-> order by sal desc
-> limit 0,5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)
10、取得薪水最高的第六到第十名员工
mysql> select ename, sal
-> from emp
-> order by sal desc
-> limit 5,5;
+--------+---------+
| ename | sal |
+--------+---------+
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
+--------+---------+
5 rows in set (0.00 sec)
11、取得最后入职的 5 名员工
这个题给的知识点，可以按日期排序，并且从早到晚是升序，从晚到早是降序
mysql> select ename, hiredate
-> from emp
-> order by hiredate desc
-> limit 0,5;
+--------+------------+
| ename | hiredate |
+--------+------------+
| ADAMS | 1987-05-23 |
| SCOTT | 1987-04-19 |
| MILLER | 1982-01-23 |
| FORD | 1981-12-03 |
| JAMES | 1981-12-03 |
+--------+------------+
5 rows in set (0.00 sec)
12、取得每个薪水等级有多少员工
先算薪水等级，然后计数
mysql> select e.ename, s.grade
-> from emp e
-> join salgrade s
-> on e.sal between losal and hisal;
+--------+-------+
| ename | grade |
+--------+-------+
| SMITH | 1 |
| ALLEN | 3 |
| WARD | 2 |
| JONES | 4 |
| MARTIN | 2 |
| BLAKE | 4 |
| CLARK | 4 |
| SCOTT | 4 |
| KING | 5 |
| TURNER | 3 |
| ADAMS | 1 |
| JAMES | 1 |
| FORD | 4 |
| MILLER | 2 |
+--------+-------+
14 rows in set (0.00 sec)
计数
mysql> select grade, count(*)
-> from (
-> select e.ename, s.grade
-> from emp e
-> join salgrade s
-> on e.sal between losal and hisal) as n
-> group by n.grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
+-------+----------+
5 rows in set (0.00 sec)
看视频后：
这里同样没必要把第一步的查询结果变成一个张表，直接分组查就行了
mysql> select s.grade,count(*)
-> from emp e
-> join salgrade s
-> on e.sal between losal and hisal
-> group by s.grade;
13、面试题
问题 1.找出没选过“黎明”老师的所有学生姓名。
先找出黎明老师的编号，然后再选课表中，查看选了黎明老师的学生的编号，然后在学生表中查看没有选黎明老师的学生姓名
先找出黎明老师的编号
mysql> select cno from c where cteacher = 'liming';
+------+
| cno |
+------+
| 5 |
+------+
1 row in set (0.08 sec)
然后再选课表中，查看选了黎明老师的学生的编号，
mysql> select sno from sc
-> where cno = (select cno from c where cteacher = 'liming');
+------+
| sno |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.03 sec)
在学生表中查看没有选黎明老师的学生姓名
mysql> select sname from s
-> where sno not in (
-> select sno from sc
-> where cno = (select cno from c where cteacher = 'liming'));
+-----------+
| sname |
+-----------+
| xuesheng3 |
| xuesheng4 |
+-----------+
2 rows in set (0.03 sec)
问题 2.列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。
先找出不及格的所有人和课程
mysql> select sno,cno,scgrade
-> from sc where scgrade < 60;
+------+------+---------+
| sno | cno | scgrade |
+------+------+---------+
| 1 | 1 | 40 |
| 1 | 2 | 30 |
| 1 | 3 | 20 |
| 2 | 5 | 40 |
+------+------+---------+
4 rows in set (0.01 sec)
然后统计每个人不及格的课程数目，选出2门以上的学生编号
mysql> select sno,count(sno) from (select sno,cno,scgrade from sc where scgrade < 60) as n
-> group by sno having count(sno) >= 2;
+------+------------+
| sno | count(sno) |
+------+------------+
| 1 | 3 |
+------+------------+
1 row in set (0.12 sec)
找选课表中计算该学生的平均成绩
mysql> select sno,avg(scgrade) from sc where sno = (
-> select sno from (select sno,cno,scgrade from sc where scgrade < 60) as n
-> group by sno having count(sno) >= 2)
-> group by sno;
+------+--------------+
| sno | avg(scgrade) |
+------+--------------+
| 1 | 46 |
+------+--------------+
1 row in set (0.07 sec)
在学生表中找学生名字，将两个表连接，根据学生编号
mysql> select a.sname, b.avggrade
-> from s as a
-> join (
-> select sno,avg(scgrade) avggrade from sc where sno = (
-> select sno from (select sno,cno,scgrade from sc where scgrade < 60) as n
-> group by sno having count(sno) >= 2)
-> group by sno) as b
-> on a.sno = b.sno;
+-----------+----------+
| sname | avggrade |
+-----------+----------+
| xuesheng1 | 46 |
+-----------+----------+
1 row in set (0.01 sec)
问题 3:既学过 1 号课程又学过 2 号课所有学生的姓名。
学过1号课程的学生，学过2号课程的学生
mysql> select sno,cno from sc where cno = '1';
+------+------+
| sno | cno |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
+------+------+
3 rows in set (0.00 sec)
mysql> select sno,cno from sc where cno = '2';
+------+------+
| sno | cno |
+------+------+
| 1 | 2 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)
同时学过两门课程的学生
mysql> select a.sno from (select sno,cno from sc where cno = '1') as a
-> where a.sno in (select b.sno from
-> (select sno,cno from sc where cno = '2') as b);
+------+
| sno |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
在学生表中找出姓名
mysql> select s.sname from s
-> where sno in (
-> select a.sno from (select sno,cno from sc where cno = '1') as a
-> where a.sno in (select b.sno from
-> (select sno,cno from sc where cno = '2') as b));
+-----------+
| sname |
+-----------+
| xuesheng1 |
| xuesheng2 |
+-----------+
2 rows in set (0.00 sec)
14、列出所有员工及领导的姓名
相当于两张表，一张所有员工的表，一张关系表，然后通过关系表查对应的领导，注意因为boss没有领导，所以使用外连接，为NULL时处理
mysql> select a.ename, ifnull(b.ename, 'I\'m boss') from emp a
-> left join emp b
-> on a.mgr = b.empno;
+--------+------------------------------+
| ename | ifnull(b.ename, 'I\'m boss') |
+--------+------------------------------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | I'm boss |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+------------------------------+
14 rows in set (0.00 sec)
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
上一题找到了领导，这里判断它的受雇日期是否早于其领导
mysql> select a.empno, a.ename, b.ename,a.deptno
-> from emp a
-> join emp b
-> on a.mgr = b.empno
-> where a.hiredate < b.hiredate;
+-------+-------+-------+--------+
| empno | ename | ename | deptno |
+-------+-------+-------+--------+
| 7369 | SMITH | FORD | 20 |
| 7499 | ALLEN | BLAKE | 30 |
| 7521 | WARD | BLAKE | 30 |
| 7566 | JONES | KING | 20 |
| 7698 | BLAKE | KING | 30 |
| 7782 | CLARK | KING | 10 |
+-------+-------+-------+--------+
6 rows in set (0.02 sec)
然后连接部门表，查找部门名称
mysql> select t.empno, t.ename, d.dname
-> from (
-> select a.empno, a.ename, a.deptno
-> from emp a
-> join emp b
-> on a.mgr = b.empno
-> where a.hiredate < b.hiredate) as t
-> join dept d
-> on t.deptno = d.deptno;
+-------+-------+------------+
| empno | ename | dname |
+-------+-------+------------+
| 7782 | CLARK | ACCOUNTING |
| 7369 | SMITH | RESEARCH |
| 7566 | JONES | RESEARCH |
| 7499 | ALLEN | SALES |
| 7521 | WARD | SALES |
| 7698 | BLAKE | SALES |
+-------+-------+------------+
看视频后：
还是同样的问题，不需要嵌套，又繁琐了，直接在后面再连接就行
mysql> select a.ename, b.ename,a.deptno,d.dname
-> from emp a
-> join emp b
-> on a.mgr = b.empno
-> join dept d
-> a.deptno = d.deptno
-> where a.hiredate < b.hiredate;
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
左外链接
mysql> select d.dname,e.* from dept d left join emp e on d.deptno = e.deptno;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| SALES | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| SALES | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| SALES | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
15 rows in set (0.00 sec)
17、列出至少有 5 个员工的所有部门
接上一题，分组统计完事
mysql> select d.dname,count(dname) from dept d left join emp e on d.deptno = e.deptno
-> group by dname
-> having count(dname) >= 5;
+----------+--------------+
| dname | count(dname) |
+----------+--------------+
| RESEARCH | 5 |
| SALES | 6 |
+----------+--------------+
2 rows in set (0.00 sec)
18、列出薪金比"SMITH"多的所有员工信息
mysql> select * from emp where sal > (select sal from emp where ename = 'smith');
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.02 sec)
19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数
先找出办事员的姓名及部门编号
mysql> select ename,job, deptno from emp where job = 'clerk';
+--------+-------+--------+
| ename | job | deptno |
+--------+-------+--------+
| SMITH | CLERK | 20 |
| ADAMS | CLERK | 20 |
| JAMES | CLERK | 30 |
| MILLER | CLERK | 10 |
+--------+-------+--------+
4 rows in set (0.00 sec)
找出每个部门所对应的编号及人数
mysql> select deptno,count(deptno) from emp group by deptno;
+--------+---------------+
| deptno | count(deptno) |
+--------+---------------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+--------+---------------+
3 rows in set (0.00 sec)
再找出对应名称
mysql> select e.deptno,count(e.deptno), d.dname from emp e join dept d on e.deptno = d.deptno group by deptno;
+--------+-----------------+------------+
| deptno | count(e.deptno) | dname |
+--------+-----------------+------------+
| 10 | 3 | ACCOUNTING |
| 20 | 5 | RESEARCH |
| 30 | 6 | SALES |
+--------+-----------------+------------+
3 rows in set (0.00 sec)
然后两个表连接
mysql> select a.ename, b.dname, b.cc
-> from(
-> select ename,job, deptno from emp where job = 'clerk') as a
-> join(
-> select e.deptno,count(e.deptno) as cc, d.dname from emp e join dept d on e.deptno = d.deptno group by deptno) as b
-> on a.deptno = b.deptno;
+--------+------------+----+
| ename | dname | cc |
+--------+------------+----+
| SMITH | RESEARCH | 5 |
| ADAMS | RESEARCH | 5 |
| JAMES | SALES | 6 |
| MILLER | ACCOUNTING | 3 |
+--------+------------+----+
4 rows in set (0.03 sec)
20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数.
先找出每个工作的最低薪金
mysql> select job, min(sal) from emp group by job;
+-----------+----------+
| job | min(sal) |
+-----------+----------+
| ANALYST | 3000.00 |
| CLERK | 800.00 |
| MANAGER | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1250.00 |
+-----------+----------+
5 rows in set (0.03 sec)
选出最低薪金大于1500的工作
mysql> select job, min(sal) from emp group by job having min(sal) > 1500;
+-----------+----------+
| job | min(sal) |
+-----------+----------+
| ANALYST | 3000.00 |
| MANAGER | 2450.00 |
| PRESIDENT | 5000.00 |
+-----------+----------+
3 rows in set (0.00 sec)
从事此工作的人数
mysql> select job,count(job) from emp where job in (
-> select job from emp group by job having min(sal) > 1500)
-> group by job;
+-----------+------------+
| job | count(job) |
+-----------+------------+
| ANALYST | 2 |
| MANAGER | 3 |
| PRESIDENT | 1 |
+-----------+------------+
3 rows in set (0.00 sec)
又写的麻烦了，在选工作的同时直接计数
mysql> select job, count(*) from emp group by job having min(sal) > 1500;
21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号
先把两个表连起来，然后再查询就完事了
mysql> select e.*, d.dname from emp e join dept d on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | dname |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | ACCOUNTING |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | ACCOUNTING |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | ACCOUNTING |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | RESEARCH |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | RESEARCH |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | RESEARCH |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | RESEARCH |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | RESEARCH |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | SALES |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | SALES |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | SALES |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | SALES |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | SALES |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | SALES |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
14 rows in set (0.00 sec)
mysql> select n.ename from (select e.*, d.dname from emp e join dept d on e.deptno = d.deptno) as n where dname = 'sales';
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
6 rows in set (0.00 sec)
看视频后：
先根据部门名称找部门编号，然后部门编号相同找员工
select ename from emp where deptno = (select deptno from dept where dname = ‘SALES’);
22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级
先找薪金高于平均薪金的所有员工
mysql> select ename,deptno, mgr,sal from emp where sal > (select avg(sal) from emp);
+-------+--------+------+---------+
| ename | deptno | mgr | sal |
+-------+--------+------+---------+
| JONES | 20 | 7839 | 2975.00 |
| BLAKE | 30 | 7839 | 2850.00 |
| CLARK | 10 | 7839 | 2450.00 |
| SCOTT | 20 | 7566 | 3000.00 |
| KING | 10 | NULL | 5000.00 |
| FORD | 20 | 7566 | 3000.00 |
+-------+--------+------+---------+
6 rows in set (0.00 sec)
然后连接三个表分别查询
mysql> select e.ename, d.dname,l.ename, s.grade from (
-> select ename,deptno, mgr,sal from emp where sal > (select avg(sal) from emp)) as e
-> join dept d
-> on e.deptno = d.deptno
-> left join emp l
-> on e.mgr = l.empno
-> join salgrade s
-> on e.sal between losal and hisal;
+-------+------------+-------+-------+
| ename | dname | ename | grade |
+-------+------------+-------+-------+
| JONES | RESEARCH | KING | 4 |
| BLAKE | SALES | KING | 4 |
| CLARK | ACCOUNTING | KING | 4 |
| SCOTT | RESEARCH | JONES | 4 |
| KING | ACCOUNTING | NULL | 5 |
| FORD | RESEARCH | JONES | 4 |
+-------+------------+-------+-------+
6 rows in set (0.00 sec)
看视频后：
又繁琐了
select
e.ename '员工',d.dname,l.ename '领导',s.grade
from
emp e
join
dept d
on
e.deptno = d.deptno
left join
emp l
on
e.mgr = l.empno
join
salgrade s
on
e.sal between s.losal and s.hisal
where
e.sal > (select avg(sal) from emp);
23、列出与"SCOTT"从事相同工作的所有员工及部门名称
找出scott从事的工作
mysql> select job from emp where ename = 'scott';
+---------+
| job |
+---------+
| ANALYST |
+---------+
1 row in set (0.00 sec)
找到和他从事相同工作的 员工名称和部门编号
mysql> select ename,deptno from emp where job = (select job from emp where ename = 'scott') and ename != 'scott';
+-------+--------+
| ename | deptno |
+-------+--------+
| FORD | 20 |
+-------+--------+
1 row in set (0.00 sec)
连接表查询部门名称
mysql> select e.ename, d.dname from (
-> select ename,deptno from emp where job = (select job from emp where ename = 'scott') and ename != 'scott') as e
-> join dept d
-> on e.deptno = d.deptno;
+-------+----------+
| ename | dname |
+-------+----------+
| FORD | RESEARCH |
+-------+----------+
看视频后：
我发现我之前做的都是一步步来，这样我就容易将每一步看成一个张表，然后再去连接另一张表进行下一步，有些地方其实不用这样麻烦
例如这里，我在找到和他从事相同工作的 员工名称和部门编号后直接连接部门表把部门名称找出来就行了
select
e.ename,e.job,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = (select job from emp where ename = 'SCOTT')
and
e.ename <> 'SCOTT';
24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金
这句话读着咋这么别扭
先找部门30中员工的薪金及名字
mysql> select ename, sal from emp where deptno = 30;
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| TURNER | 1500.00 |
| JAMES | 950.00 |
+--------+---------+
6 rows in set (0.00 sec)
找出薪资在这个表中，但名字不在这个表中的员工的姓名和薪金，
又死板了，我一直这样写
mysql> select e.ename,e.sal from emp e
-> join (
-> select ename, sal from emp where deptno = 30) as b
-> on e.sal in b.sal and e.ename not in b.ename;
但是这样不行，最下面一一行直报错
名字不在上面表中的员工，就是部门不是30的，直接筛选就完事了…
mysql> select ename,sal from emp
-> where sal in(
-> select sal from emp where deptno = 30)
-> and deptno != 30;
Empty set (0.00 sec)
25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金.部门名
同样先找部门30中员工的薪金的最大值
mysql> select max(sal) from emp where deptno = 30;
+----------+
| max(sal) |
+----------+
| 2850.00 |
+----------+
1 row in set (0.00 sec)
然后找大于这个薪金的员工姓名和部门编号
mysql> select ename,sal,deptno from emp
-> where sal > (select max(sal) from emp where deptno = 30) and deptno != 30;
+-------+---------+--------+
| ename | sal | deptno |
+-------+---------+--------+
| JONES | 2975.00 | 20 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| FORD | 3000.00 | 20 |
+-------+---------+--------+
4 rows in set (0.00 sec)
连接表，将部门编号变成名称
mysql> select e.ename, e.sal ,d.dname from (
-> select ename,sal,deptno from emp
-> where sal > (select max(sal) from emp where deptno = 30) and deptno != 30) e
-> join dept d
-> on e.deptno = d.deptno;
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| KING | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+
4 rows in set (0.00 sec)
看视频后：
我又去看成一个表了，直接连接就行了，还有不需要加部门编号不等于30
mysql> select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno
-> where sal > (select max(sal) from emp where deptno = 30);
mysql> select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno where sal > (select max(sal) from emp where deptno = 30);
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| KING | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+
4 rows in set (0.00 sec)
26、列出在每个部门工作的员工数量,平均工资和平均服务期限
不太明白这个平均服务期限是什么鬼
先写一下其他两项
mysql> select d.dname, count(e.ename),avg(e.sal) from emp e
-> join dept d
-> on e.deptno = d.deptno
-> group by e.deptno;
+------------+----------------+-------------+
| dname | count(e.ename) | avg(e.sal) |
+------------+----------------+-------------+
| ACCOUNTING | 3 | 2916.666667 |
| RESEARCH | 5 | 2175.000000 |
| SALES | 6 | 1566.666667 |
+------------+----------------+-------------+
3 rows in set (0.00 sec)
因为有一个没有员工的部门，所以这里不对，需要外连接，并对空赋值
mysql> select d.dname, count(e.ename),ifnull(avg(e.sal),0) from emp e
-> right join dept d
-> on e.deptno = d.deptno
-> group by e.deptno;
+------------+----------------+----------------------+
| dname | count(e.ename) | ifnull(avg(e.sal),0) |
+------------+----------------+----------------------+
| OPERATIONS | 0 | 0.000000 |
| ACCOUNTING | 3 | 2916.666667 |
| RESEARCH | 5 | 2175.000000 |
| SALES | 6 | 1566.666667 |
+------------+----------------+----------------------+
4 rows in set (0.04 sec)
服务期限，我猜是现在的时间减去入职的时间
在MySQL中计算两个日期的差值
在mysql当中怎么计算两个日期的“年差”，差了多少年？
TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
timestampdiff(YEAR, hiredate, now())
间隔类型：
SECOND 秒，
MINUTE 分钟，
HOUR 小时，
DAY 天，
WEEK 星期
MONTH 月，
QUARTER 季度，
YEAR 年
mysql> select d.dname, count(e.ename) ecount,ifnull(avg(e.sal),0) avgsal,
-> ifnull(Timestampdiff(YEAR,hiredate, now()),0) avgservetime from emp e
-> right join dept d
-> on e.deptno = d.deptno
-> group by e.deptno;
+------------+--------+-------------+--------------+
| dname | ecount | avgsal | avgservetime |
+------------+--------+-------------+--------------+
| OPERATIONS | 0 | 0.000000 | 0 |
| ACCOUNTING | 3 | 2916.666667 | 39 |
| RESEARCH | 5 | 2175.000000 | 40 |
| SALES | 6 | 1566.666667 | 40 |
+------------+--------+-------------+--------------+
4 rows in set (0.02 sec)
27、列出所有员工的姓名、部门名称和工资
怎么还感觉越来越简单了
mysql> select e.ename, d.dname, e.sal
-> from emp e
-> join dept d
-> on e.deptno = d.deptno;
+--------+------------+---------+
| ename | dname | sal |
+--------+------------+---------+
| CLARK | ACCOUNTING | 2450.00 |
| KING | ACCOUNTING | 5000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| SMITH | RESEARCH | 800.00 |
| JONES | RESEARCH | 2975.00 |
| SCOTT | RESEARCH | 3000.00 |
| ADAMS | RESEARCH | 1100.00 |
| FORD | RESEARCH | 3000.00 |
| ALLEN | SALES | 1600.00 |
| WARD | SALES | 1250.00 |
| MARTIN | SALES | 1250.00 |
| BLAKE | SALES | 2850.00 |
| TURNER | SALES | 1500.00 |
| JAMES | SALES | 950.00 |
+--------+------------+---------+
14 rows in set (0.00 sec)
28、列出所有部门的详细信息和人数
mysql> select d.*,ifnull(e.ecount,0) from (
-> select deptno,count(ename) ecount from emp group by deptno) as e
-> right join dept d
-> on e.deptno = d.deptno;
+--------+------------+----------+--------------------+
| DEPTNO | DNAME | LOC | ifnull(e.ecount,0) |
+--------+------------+----------+--------------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
+--------+------------+----------+--------------------+
4 rows in set (0.00 sec)
又复杂了
select
d.deptno,d.dname,d.loc,count(e.ename)
from
emp e
right join
dept d
on
e.deptno = d.deptno
group by
d.deptno,d.dname,d.loc;
29、列出各种工作的最低工资及从事此工作的雇员姓名
首先，各种工作的最低工资和名称
mysql> select job,min(sal) from emp group by job;
+-----------+----------+
| job | min(sal) |
+-----------+----------+
| ANALYST | 3000.00 |
| CLERK | 800.00 |
| MANAGER | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1250.00 |
+-----------+----------+
5 rows in set (0.00 sec)
根据所给出的答案判断，题目应该是从事此工作的并且工资是最低的雇员姓名
mysql> select e.ename,j.job,j.minsal from emp e
-> join (
-> select job,min(sal) minsal from emp group by job) as j
-> on e.job = j.job and e.sal = j.minsal;
+--------+-----------+---------+
| ename | job | minsal |
+--------+-----------+---------+
| SMITH | CLERK | 800.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| CLARK | MANAGER | 2450.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| FORD | ANALYST | 3000.00 |
+--------+-----------+---------+
7 rows in set (0.00 sec)
30、列出各个部门的 MANAGER(领导)的最低薪金
这里特别注意，先判断是领导，再分组，可以得到答案；但是先分组，再判断会显示没有job这一列
因此可以得到结论，分组以后，表中所剩的字段就只剩下分组所用字段和分组函数了，然后才会去执行having
mysql> select deptno, min(sal) from emp group by deptno having job = 'manager';
ERROR 1054 (42S22): Unknown column 'job' in 'having clause'
mysql> select deptno, min(sal) from emp where job= 'manager' group by deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
| 10 | 2450.00 |
| 20 | 2975.00 |
| 30 | 2850.00 |
+--------+----------+
3 rows in set (0.00 sec)
31、列出所有员工的年工资,按年薪从低到高排序
mysql> select ename,(sal * 12 + ifnull(comm, 0) * 12) yearsal from emp order by yearsal asc;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| JAMES | 11400.00 |
| ADAMS | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD | 21000.00 |
| ALLEN | 22800.00 |
| CLARK | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| JONES | 35700.00 |
| FORD | 36000.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
+--------+----------+
14 rows in set (0.00 sec)
32、求出员工领导的薪水超过 3000 的员工名称与领导名称
mysql> select e.ename, l.ename,l.sal
-> from emp e
-> join emp l
-> on e.mgr = l.empno
-> where l.sal > 3000;
+-------+-------+---------+
| ename | ename | sal |
+-------+-------+---------+
| JONES | KING | 5000.00 |
| BLAKE | KING | 5000.00 |
| CLARK | KING | 5000.00 |
+-------+-------+---------+
3 rows in set (0.00 sec)
33、求出部门名称中,带’S’字符的部门员工的工资合计、部门人数
模糊查询用一下，求出带S字符的部门名称
mysql> select deptno,dname from dept where dname like '%s%';
+--------+------------+
| deptno | dname |
+--------+------------+
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
3 rows in set (0.00 sec)
然后连接表查工资合计和部门人数
mysql> select d.deptno, d.dname, sum(e.sal), count(e.ename)
-> from emp e
-> right join (
-> select deptno,dname from dept where dname like '%s%') as d
-> on e.deptno = d.deptno
-> group by deptno;
+--------+------------+------------+----------------+
| deptno | dname | sum(e.sal) | count(e.ename) |
+--------+------------+------------+----------------+
| 20 | RESEARCH | 10875.00 | 5 |
| 30 | SALES | 9400.00 | 6 |
| 40 | OPERATIONS | NULL | 0 |
+--------+------------+------------+----------------+
3 rows in set (0.00 sec)
看视频后：
也不用嵌套，
select
d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
d.dname like '%S%'
group by
d.deptno,d.dname,d.loc;
34、给任职日期超过 30 年的员工加薪 10%
全超过30年了哈哈
mysql> select ename, sal * 1.1 from emp where timestampdiff(year, hiredate, now()) > 30;
+--------+-----------+
| ename | sal * 1.1 |
+--------+-----------+
| SMITH | 880.00 |
| ALLEN | 1760.00 |
| WARD | 1375.00 |
| JONES | 3272.50 |
| MARTIN | 1375.00 |
| BLAKE | 3135.00 |
| CLARK | 2695.00 |
| SCOTT | 3300.00 |
| KING | 5500.00 |
| TURNER | 1650.00 |
| ADAMS | 1210.00 |
| JAMES | 1045.00 |
| FORD | 3300.00 |
| MILLER | 1430.00 |
+--------+-----------+
14 rows in set (0.03 sec)
总结
花了两三天把这些题做完了，刚开始做的时候有点懵，后面知道套路以后就越做越快了，当然也和后面的题变简单有一定关系，当然也发现了一些小问题，感觉数据库还是比较简单的，继续加油了
看了视频，发现自己很多题都是一步一步来的，这样虽然很条理，不容易出错，但是有很多过程中我都是把上一步结果变成了一张表，然后嵌套处理，其实都没必要，所以这里注意一下
扫一扫
918
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
