mysql34道作业题---动力节点

最新推荐文章于 2024-05-19 17:03:37 发布
天是蓝的嘛
最新推荐文章于 2024-05-19 17:03:37 发布
阅读量76
点赞数
分类专栏： Java 文章标签：数据库 mysql sql Powered by 金山文档
本文链接：https://blog.csdn.net/qq_52983712/article/details/129151340
版权
Java 专栏收录该内容
8 篇文章 0 订阅
订阅专栏
作业题：
mysql> select *from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.01 sec)

mysql> select *from 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.01 sec)

mysql> select *from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.01 sec)

1、取得每个部门最高薪水的人员名称
mysql> select e.ename,t.*
    -> from emp e
    -> join (select deptno,max(sal) as maxsal from emp group by deptno) t
    -> on e.deptno=t.deptno and t.maxsal=e.sal;
+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)
2、哪些人的薪水在部门的平均薪水之上
 select e.ename,e.sal,t.*
 from emp e
 join (select deptno,avg(sal) as avgsal from emp group by deptno) t
 on e.sal>t.avgsal and e.deptno=t.deptno;
+-------+---------+--------+-------------+
| ename | sal     | deptno | avgsal      |
+-------+---------+--------+-------------+
| ALLEN | 1600.00 |     30 | 1566.666667 |
| JONES | 2975.00 |     20 | 2175.000000 |
| BLAKE | 2850.00 |     30 | 1566.666667 |
| SCOTT | 3000.00 |     20 | 2175.000000 |
| KING  | 5000.00 |     10 | 2916.666667 |
| FORD  | 3000.00 |     20 | 2175.000000 |
+-------+---------+--------+-------------+
6 rows in set (0.00 sec)
3、取得部门中（所有人的）平均的薪水等级
 每一个部门的平均薪水
select t.deptno,avg(t.sal) as avgsal
from (select s.grade,e.deptno,e.ename,e.sal from emp e,salgrade s where e.sal between s.losal and s.hisal) t
group by t.deptno;
 +--------+-------------+
 | deptno | avgsal      |
 +--------+-------------+
 |     10 | 2916.666667 |
 |     20 | 2175.000000 |
 |     30 | 1566.666667 |
 +--------+-------------+
 3 rows in set (0.00 sec)
 每一个部门的平均薪水等级
 select t.deptno,avg(t.grade) as avggrade
 from (select s.grade,e.deptno,e.ename,e.sal from emp e,salgrade s where e.sal between s.losal and s.hisal) t
 group by t.deptno;
+--------+----------+
| deptno | avggrade |
+--------+----------+
|     10 |   3.6667 |
|     20 |   2.8000 |
|     30 |   2.5000 |
+--------+----------+
3 rows in set (0.00 sec)

sql99语法：
select e.deptno,avg(t.grade) from emp e join salgrade t on e.sal between t.losal and t.hisal group by e.deptno;
    +--------+--------------+
    | deptno | avg(t.grade) |
    +--------+--------------+
    |     10 |       3.6667 |
    |     20 |       2.8000 |
    |     30 |       2.5000 |
    +--------+--------------+
    3 rows in set (0.00 sec)

4、不准用组函数（Max ），取得最高薪水
mysql> select sal as maxsal from emp order by sal desc limit 1;
  +---------+
  | maxsal  |
  +---------+
  | 5000.00 |
  +---------+
  1 row in set (0.00 sec)
5、取得平均薪水最高的部门的部门编号
mysql> select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)
6、取得平均薪水最高的部门的部门名称
mysql> select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by e.deptno limit 1;
+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
1 row in set (0.00 sec)

mysql> select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname limit 1;
+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
1 row in set (0.00 sec)
7、求平均薪水的等级最低的部门的部门名称
mysql> select s.grade,t.*
    -> from salgrade s
    -> join (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname order by avgsal asc limit 1) t
    -> on t.avgsal between s.losal and s.hisal;
+-------+-------+-------------+
| grade | dname | avgsal      |
+-------+-------+-------------+
|     3 | SALES | 1566.666667 |
+-------+-------+-------------+
1 row in set (0.00 sec)
8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
mysql> select ename from emp where sal>(select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+
6 rows in set (0.00 sec)
9、取得薪水最高的前五名员工
取得薪水最高的前五名普通员工
mysql> select ename,sal from emp where empno not in (select distinct mgr from emp where mgr is not null) order by sal desc limit 5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
+--------+---------+
5 rows in set (0.00 sec)

mysql> select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 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 |
| WARD   | 1250.00 |
+--------+---------+
5 rows in set (0.00 sec)

11、取得最后入职的 5 名员工
mysql> select ename, hiredate from emp order by hiredate desc limit 5;
+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES  | 1981-12-03 |
| FORD   | 1981-12-03 |
+--------+------------+
5 rows in set (0.00 sec)
12、取得每个薪水等级有多少员工
mysql> select s.grade,count(e.ename) from emp e join salgrade s on e.sal between s.losal and hisal group by s.grade;
+-------+----------------+
| grade | count(e.ename) |
+-------+----------------+
|     1 |              3 |
|     2 |              3 |
|     3 |              2 |
|     4 |              5 |
|     5 |              1 |
+-------+----------------+
5 rows in set (0.01 sec)
13、面试题：****
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
mysql> select *from s
    -> ;
+-----+-----------+
| sno | sname     |
+-----+-----------+
| 1   | 朱厚照    |
| 2   | 朱棣      |
| 3   | 张居正    |
+-----+-----------+
3 rows in set (0.00 sec)

mysql> select *from c;
+-----+--------------------+-----------+
| cno | cname              | cteacher  |
+-----+--------------------+-----------+
| ed  | 自然辨证原理       | 杨继盛    |
| fr  | 通信原理           | 黎明      |
| wd  | 数字信号处理       | 高拱      |
+-----+--------------------+-----------+
3 rows in set (0.00 sec)

mysql> select * from sc;
+-----+-----+---------+
| sno | cno | scgrade |
+-----+-----+---------+
| 1   | ed  | 59      |
| 1   | fr  | 35      |
| 2   | fr  | 95      |
| 2   | wd  | 89      |
| 3   | ed  | 89      |
| 3   | wd  | 54      |
+-----+-----+---------+
6 rows in set (0.00 sec)
下面题目均按照上面三个表进行
1，找出没选过“黎明”老师的所有学生姓名。
mysql> select s.sname
    -> from s
    -> join (select s.sno,count(s.cteacher) 'count' from (select sc.sno,c.cteacher from sc left join c on sc.cno=c.cno and c.cteacher = '黎明') s group by s.sno) t
    -> on s.sno=t.sno and t.count=0;
+-----------+
| sname     |
+-----------+
| 张居正    |
+-----------+
1 row in set (0.00 sec)

2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
select s.sname,count(s.sname) as count from sc join s on sc.sno=s.sno and sc.scgrade<60 group by s.sname;

select
g.sname,g.avggrade
from (select s.sname,avg(sc.scgrade) as avggrade from s join sc on s.sno=sc.sno group by s.sname) g
join (select s.sname as name,count(s.sname) as count from sc join s on sc.sno=s.sno and sc.scgrade<60 group by s.sname) d
on g.sname=d.name and d.count>=2;

+-----------+----------+
| sname     | avggrade |
+-----------+----------+
| 朱厚照    |       47 |
+-----------+----------+
1 row in set (0.00 sec)

3，即学过 1 号课程又学过 2 号课所有学生的姓名。
mysql> select s.sname
    -> from s
    -> join (select sc.sno,count(sc.sno) as count from sc where sc.cno='ed'or sc.cno='fr' group by sc.sno) t
    -> on s.sno=t.sno and t.count=2;
+-----------+
| sname     |
+-----------+
| 朱厚照    |
+-----------+
1 row in set (0.00 sec)

14、列出所有员工及领导的姓名
mysql> select e.ename,d.ename from emp e join emp d on e.MGR=d.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
13 rows in set (0.00 sec)
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
mysql> select e.empno '编号',e.ename '员工',e.deptno '部门' from emp e join emp d on e.MGR=d.empno and e.hiredate<d.hiredate;
+--------+--------+--------+
| 编号   | 员工   | 部门   |
+--------+--------+--------+
|   7369 | SMITH  |     20 |
|   7499 | ALLEN  |     30 |
|   7521 | WARD   |     30 |
|   7566 | JONES  |     20 |
|   7698 | BLAKE  |     30 |
|   7782 | CLARK  |     10 |
+--------+--------+--------+
6 rows in set (0.00 sec)
16、 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
mysql> select d.dname,e.* from emp e right join dept d on e.deptno=d.deptno order by d.dname;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
| RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.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   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| 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      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.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 |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
15 rows in set (0.00 sec)

17、列出至少有 5 个员工的所有部门
按照部门编号分组，计数，筛选出 >= 5
mysql> select t.dname from (select d.dname,count(e.ename) as count from emp e join dept d on e.deptno=d.deptno group by d.dname) t where t.count>=5;
+----------+
| dname    |
+----------+
| RESEARCH |
| SALES    |
+----------+
2 rows in set (0.01 sec)
答案：
select
    deptno
from
    emp
group by
    deptno
having
    count(*) >= 5;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
+--------+
18、列出薪金比"SMITH" 多的所有员工信息
mysql> select e.* from emp e where e.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.00 sec)

19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
mysql> select t.ename,t.job,t.dname,m.count
    -> from (select e.ename,e.job,d.dname,d.deptno from emp e join dept d on e.job='clerk'and e.deptno=d.deptno) t
    -> join (select deptno,count(ename) as count from emp group by deptno) m
    -> on m.deptno=t.deptno;
+--------+-------+------------+-------+
| ename  | job   | dname      | count |
+--------+-------+------------+-------+
| SMITH  | CLERK | RESEARCH   |     5 |
| ADAMS  | CLERK | RESEARCH   |     5 |
| JAMES  | CLERK | SALES      |     6 |
| MILLER | CLERK | ACCOUNTING |     3 |
+--------+-------+------------+-------+
4 rows in set (0.00 sec)

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
mysql> select t.*,d.count from (select job,min(sal) as min from emp group by job) t join (select job,count(ename) as count from emp group by job) d on d.job=t.job and t.min>1500;
+-----------+---------+-------+
| job       | min     | count |
+-----------+---------+-------+
| ANALYST   | 3000.00 |     2 |
| MANAGER   | 2450.00 |     3 |
| PRESIDENT | 5000.00 |     1 |
+-----------+---------+-------+
3 rows in set (0.00 sec)
mysql> select job,count(ename) from emp group by job having min(sal)>1500;
+-----------+--------------+
| job       | count(ename) |
+-----------+--------------+
| ANALYST   |            2 |
| MANAGER   |            3 |
| PRESIDENT |            1 |
+-----------+--------------+
3 rows in set (0.00 sec)

21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
mysql> select ename from emp where deptno=(select deptno from dept where dname='sales');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.00 sec)
22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
select h.*,k.name as mgr
from (select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal>(select avg(sal) from emp) and e.sal between s.losal and hisal) h
left join (select e.ename,t.ename as name from emp e join emp t on e.mgr=t.empno) k
on h.ename=k.ename;
+-------+--------+-------+-------+
| ename | deptno | grade | mgr   |
+-------+--------+-------+-------+
| JONES |     20 |     4 | KING  |
| BLAKE |     30 |     4 | KING  |
| CLARK |     10 |     4 | KING  |
| SCOTT |     20 |     4 | JONES |
| FORD  |     20 |     4 | JONES |
| KING  |     10 |     5 | NULL  |
+-------+--------+-------+-------+
6 rows in set (0.00 sec)
23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称
mysql> select e.ename,d.dname from emp e join dept d on e.job=(select job from emp where ename='scott') and e.deptno=d.deptno and e.ename!='scott';
+-------+----------+
| ename | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.(in 的使用)
mysql> select e.ename,e.sal from emp e join (select distinct sal from emp where deptno=30) t on e.sal=t.sal and e.deptno!=30;
Empty set (0.00 sec)

mysql> select ename,sal from emp where deptno!=30 and sal in(select distinct sal from emp where deptno=30);
Empty set (0.00 sec)
25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
sql> select e.ename,e.sal ,d.dname from emp e join dept d on sal>(select max(sal) from emp where deptno=30) where e.deptno=d.deptno;
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| KING  | 5000.00 | ACCOUNTING |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+
4 rows in set (0.00 sec)
26、列出在每个部门工作的员工数量, 平均工资和平均服务期限（不会）
mysql> select d.deptno,count(e.ename),ifnull(avg(sal),0) avgsal, ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) avgyear from emp e right join dept d on e.deptno=d.deptno group by d.deptno;
+--------+----------------+-------------+---------+
| deptno | count(e.ename) | avgsal      | avgyear |
+--------+----------------+-------------+---------+
|     10 |              3 | 2916.666667 | 41.0000 |
|     20 |              5 | 2175.000000 | 38.8000 |
|     30 |              6 | 1566.666667 | 41.1667 |
|     40 |              0 |    0.000000 |  0.0000 |
+--------+----------------+-------------+---------+
4 rows in set (0.00 sec)

在mysql当中怎么计算两个日期的“年差”，差了多少年？
    TimeStampDiff(间隔类型, 前一个日期, 后一个日期)

    timestampdiff(YEAR, hiredate, now())

    间隔类型：
        SECOND   秒，
        MINUTE   分钟，
        HOUR   小时，
        DAY   天，
        WEEK   星期
        MONTH   月，
        QUARTER   季度，
        YEAR   年
        
27、 列出所有员工的姓名、部门名称和工资。
mysql> select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno;
+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| SMITH  | RESEARCH   |  800.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| JONES  | RESEARCH   | 2975.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| CLARK  | ACCOUNTING | 2450.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| KING   | ACCOUNTING | 5000.00 |
| TURNER | SALES      | 1500.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| JAMES  | SALES      |  950.00 |
| FORD   | RESEARCH   | 3000.00 |
| MILLER | ACCOUNTING | 1300.00 |
+--------+------------+---------+
14 rows in set (0.00 sec)

28、列出所有部门的详细信息和人数
mysql> select d.*,count(e.ename) as count from emp e right join dept d on e.deptno=d.deptno group by d.deptno;
+--------+------------+----------+-------+
| DEPTNO | DNAME      | LOC      | count |
+--------+------------+----------+-------+
|     10 | ACCOUNTING | NEW YORK |     3 |
|     20 | RESEARCH   | DALLAS   |     5 |
|     30 | SALES      | CHICAGO  |     6 |
|     40 | OPERATIONS | BOSTON   |     0 |
+--------+------------+----------+-------+
4 rows in set (0.01 sec)
29、列出各种工作的最低工资及从事此工作的雇员姓名
mysql> select t.*,e.ename
    -> from (select job,min(sal)as minsal from emp group by job) t
    -> join emp e
    -> on t.minsal=e.sal;
+-----------+---------+--------+
| job       | minsal  | ename  |
+-----------+---------+--------+
| CLERK     |  800.00 | SMITH  |
| SALESMAN  | 1250.00 | WARD   |
| SALESMAN  | 1250.00 | MARTIN |
| MANAGER   | 2450.00 | CLARK  |
| ANALYST   | 3000.00 | SCOTT  |
| PRESIDENT | 5000.00 | KING   |
| ANALYST   | 3000.00 | FORD   |
+-----------+---------+--------+
7 rows in set (0.00 sec)

30、列出各个部门的 MANAGER( 领导) 的最低薪金
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,12*(sal+ifnull(comm,0)) as yearsal from emp order by yearsal;
+--------+----------+
| 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 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| KING   | 60000.00 |
+--------+----------+
14 rows in set (0.00 sec)
32、求出员工领导的薪水超过3000的员工名称与领导
mysql> select e.ename,f.ename from emp e join emp f on e.mgr=f.empno and f.sal>3000;
+-------+-------+
| ename | ename |
+-------+-------+
| JONES | KING  |
| BLAKE | KING  |
| CLARK | KING  |
+-------+-------+
3 rows in set (0.00 sec)
33、求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数
mysql> select d.*,ifnull(sum(e.sal),0) '工资合计',count(e.ename) '人数'  from emp e  right join dept d on e.deptno=d.deptno where d.dname like '%s%' group by d.deptno;
+--------+------------+---------+--------------+--------+
| DEPTNO | DNAME      | LOC     | 工资合计     | 人数   |
+--------+------------+---------+--------------+--------+
|     20 | RESEARCH   | DALLAS  |     10875.00 |      5 |
|     30 | SALES      | CHICAGO |      9400.00 |      6 |
|     40 | OPERATIONS | BOSTON  |         0.00 |      0 |
+--------+------------+---------+--------------+--------+
3 rows in set (0.00 sec)
34、给任职日期超过 30 年的员工加薪 10%.
mysql> update emp set sal=sal*1.1 where timestampdiff(year,hiredate,now())>30;
Query OK, 14 rows affected (0.02 sec)
Rows matched: 14  Changed: 14  Warnings: 0

mysql> select*from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  880.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1760.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1375.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 3272.50 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1375.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 3135.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2695.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3300.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5500.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1650.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1210.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 | 1045.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3300.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1430.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)