习题篇(二)
- 首要:三表的全部数据查询
- 18、列出薪金比"SMITH" 多的所有员工信息
- 19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
- 20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
- 21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
- 22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
- 23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称
- 24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
- 25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
- 26、列出在每个部门工作的员工数量, 平均工资和平均服务期限
- 27、 列出所有员工的姓名、部门名称和工资
- 28、列出所有部门的详细信息和人数
- 29、列出各种工作的最低工资及从事此工作的雇员姓名
- 30、列出各个部门的 MANAGER( 领导) 的最低薪金
- 31、列出所有员工的 年工资, 按 年薪从低到高排序
- 32、求出员工领导的薪水超过3000的员工名称与领导
- 33、求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数
- 34、给任职日期超过 30 年的员工加薪 10%.
首要:三表的全部数据查询
emp
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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
dept
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
salgrade
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
18、列出薪金比"SMITH" 多的所有员工信息
select
*
from
emp
where
sal > (select sal from emp where ename = 'smith');
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
找出是办事员的员工姓名
select ename,job from emp where job = 'clerk';
+--------+-------+
| ename | job |
+--------+-------+
| SMITH | CLERK |
| ADAMS | CLERK |
| JAMES | CLERK |
| MILLER | CLERK |
+--------+-------+
找出是办事员的员工的部门名称
select
e.ename,e.job,d.dname,d.deptno
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = 'clerk';
+--------+-------+------------+--------+
| ename | job | dname | deptno |
+--------+-------+------------+--------+
| MILLER | CLERK | ACCOUNTING | 10 |
| SMITH | CLERK | RESEARCH | 20 |
| ADAMS | CLERK | RESEARCH | 20 |
| JAMES | CLERK | SALES | 30 |
+--------+-------+------------+--------+
求每个部门的人数
select deptno,count(*) as deptcount from emp group by deptno;
+--------+-----------+
| deptno | deptcount |
+--------+-----------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+--------+-----------+
办事员的员工的部门名称的表与每个部门的人数的表连接 在找出名字等,连接条件为编号相等
select
t1.*,t2.deptcount
from
(select
e.ename,e.job,d.dname,d.deptno
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = 'CLERK') t1
join
(select deptno, count(*) as deptcount from emp group by deptno) t2
on
t1.deptno = t2.deptno;
+--------+-------+------------+--------+-----------+
| ename | job | dname | deptno | deptcount |
+--------+-------+------------+--------+-----------+
| MILLER | CLERK | ACCOUNTING | 10 | 3 |
| SMITH | CLERK | RESEARCH | 20 | 5 |
| ADAMS | CLERK | RESEARCH | 20 | 5 |
| JAMES | CLERK | SALES | 30 | 6 |
+--------+-------+------------+--------+-----------+
20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
按照工作岗位分组求最小值。
select
job,count(*)
from
emp
group by
job
having
min(sal) > 1500;
+-----------+----------+
| job | count(*) |
+-----------+----------+
| ANALYST | 2 |
| MANAGER | 3 |
| PRESIDENT | 1 |
+-----------+----------+
21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
select
ename
from
emp
where
deptno = (select deptno from dept where dname = 'sales');
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
求出平均薪资
表连接查询出部门上级领导和工资等级
select
e.ename as '员工',b.ename as '上级领导',d.dname,s.grade as salgrade
from
emp e
join
dept d
on
e.deptno = d.deptno
join
emp b
on
e.mgr = b.empno
join
salgrade s
on
e.sal between s.losal and s.hisal
where
e.sal > (select avg(sal) from emp);
+-------+----------+------------+----------+
| 员工 | 上级领导 | dname | salgrade |
+-------+----------+------------+----------+
| JONES | KING | RESEARCH | 4 |
| BLAKE | KING | SALES | 4 |
| CLARK | KING | ACCOUNTING | 4 |
| SCOTT | JONES | RESEARCH | 4 |
| FORD | JONES | RESEARCH | 4 |
+-------+----------+------------+----------+
注意!!!!这里少了一个king 原因是表的自连接时 king的上级领导为空,所以没有显示,因此自连接的时候要使用外连接 left join…
select
e.ename as '员工',b.ename as '上级领导',d.dname,s.grade as salgrade
from
emp e
join
dept d
on
e.deptno = d.deptno
left join
emp b
on
e.mgr = b.empno
join
salgrade s
on
e.sal between s.losal and s.hisal
where
e.sal > (select avg(sal) from emp);
+-------+----------+------------+----------+
| 员工 | 上级领导 | dname | salgrade |
+-------+----------+------------+----------+
| JONES | KING | RESEARCH | 4 |
| BLAKE | KING | SALES | 4 |
| CLARK | KING | ACCOUNTING | 4 |
| SCOTT | JONES | RESEARCH | 4 |
| KING | NULL | ACCOUNTING | 5 |
| FORD | JONES | RESEARCH | 4 |
+-------+----------+------------+----------+
23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称
select job from emp where ename = 'SCOTT';
+---------+
| job |
+---------+
| ANALYST |
+---------+
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';
+-------+---------+----------+
| ename | job | dname |
+-------+---------+----------+
| FORD | ANALYST | RESEARCH |
+-------+---------+----------+
24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
部门 30 中员工的薪金
select distinct e.sal from emp e where e.deptno = 30;
+---------+
| sal |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
| 950.00 |
+---------+
select
e.ename,e.sal
from
emp e
where
e.sal in (select distinct e.sal from emp e where e.deptno = 30) and e.deptno != 30;
Empty set (0.00 sec)
注意这里不能用等于,因为查询的结果是多个,等于是一对一,所以要用in
25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
找出部门30中的最高工资
select max(sal) maxsal from emp where deptno = 30;
+---------+
| maxsal |
+---------+
| 2850.00 |
+---------+
select
e.ename,e.sal,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.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 |
+-------+---------+------------+
26、列出在每个部门工作的员工数量, 平均工资和平均服务期限
没有员工的部门,部门人数是0
select
d.deptno, count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime
from
emp e
right join
dept d
on
e.deptno = d.deptno
group by
d.deptno;
+--------+--------+-------------+----------------+
| deptno | ecount | avgsal | avgservicetime |
+--------+--------+-------------+----------------+
| 10 | 3 | 2916.666667 | 38.0000 |
| 20 | 5 | 2175.000000 | 35.8000 |
| 30 | 6 | 1566.666667 | 38.3333 |
| 40 | 0 | 0.000000 | 0.0000 |
+--------+--------+-------------+----------------+
在mysql当中怎么计算两个日期的“年差”,差了多少年?
TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
timestampdiff(YEAR, hiredate, now())
间隔类型:
SECOND 秒,
MINUTE 分钟,
HOUR 小时,
DAY 天,
WEEK 星期
MONTH 月,
QUARTER 季度,
YEAR 年
27、 列出所有员工的姓名、部门名称和工资
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 |
+--------+------------+---------+
28、列出所有部门的详细信息和人数
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;
+--------+------------+----------+----------------+
| deptno | dname | loc | count(e.ename) |
+--------+------------+----------+----------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
+--------+------------+----------+----------------+
29、列出各种工作的最低工资及从事此工作的雇员姓名
找出各种工作中的最低工资
select
job,min(sal) as minsal
from
emp
group by
job;
+-----------+---------+
| job | minsal |
+-----------+---------+
| ANALYST | 3000.00 |
| CLERK | 800.00 |
| MANAGER | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1250.00 |
+-----------+---------+
emp e和以上t连接
select
e.ename,t.*
from
emp e
join
(select
job,min(sal) as minsal
from
emp
group by
job) t
on
e.job = t.job and e.sal = t.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 |
+--------+-----------+---------+
30、列出各个部门的 MANAGER( 领导) 的最低薪金
select
deptno,min(sal) as minsal
from
emp
where
job = 'manager'
group by
deptno;
+--------+---------+
| deptno | minsal |
+--------+---------+
| 10 | 2450.00 |
| 20 | 2975.00 |
| 30 | 2850.00 |
+--------+---------+
31、列出所有员工的 年工资, 按 年薪从低到高排序
select
ename,(sal + ifnull(comm,0)) * 12 as 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 |
+--------+----------+
如果comm为空则当成0
32、求出员工领导的薪水超过3000的员工名称与领导
select
a.ename as '员工',b.ename as '领导',b.sal
from
emp a
left join
emp b
on
a.mgr = b.empno
where
b.sal > 3000;
+-------+------+---------+
| 员工 | 领导 | sal |
+-------+------+---------+
| JONES | KING | 5000.00 |
| BLAKE | KING | 5000.00 |
| CLARK | KING | 5000.00 |
+-------+------+---------+
33、求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数
select
d.deptno,d.dname,count(e.ename),ifnull(sum(e.sal),0) as sumsal
from
emp e
join
dept d
on
e.deptno = d.deptno
where
d.dname like '%s%'
group by
d.deptno,d.dname;
+--------+----------+----------------+----------+
| deptno | dname | count(e.ename) | sumsal |
+--------+----------+----------------+----------+
| 20 | RESEARCH | 5 | 10875.00 |
| 30 | SALES | 6 | 9400.00 |
+--------+----------+----------------+----------+
注意 !!!!又漏了一个数据,因为没有加上右外连接
select
d.deptno,d.dname,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;
+--------+------------+----------------+----------+
| deptno | dname | count(e.ename) | sumsal |
+--------+------------+----------------+----------+
| 20 | RESEARCH | 5 | 10875.00 |
| 30 | SALES | 6 | 9400.00 |
| 40 | OPERATIONS | 0 | 0.00 |
+--------+------------+----------------+----------+
34、给任职日期超过 30 年的员工加薪 10%.
update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;