云计算服务
服务器租用
DDOS 防护
虚拟主机
域名服务
基础设施
关于我们
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 |
+-------+--------+-----------+------+------------+---------+---------+--------+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、取得每个部门最高薪水的人员名称
mysql>
select
a.ename,b.*
from
emp a
join
(
select
deptno,max(sal) maxsal
from
emp
group by
deptno
) b
on
a.deptno = b.deptno and a.sal = b.maxsal;+-------+--------+---------+
| ename | deptno | maxsal |
+-------+--------+---------+
| KING | 10 | 5000.00 |
| SCOTT | 20 | 3000.00 |
| FORD | 20 | 3000.00 |
| BLAKE | 30 | 2850.00 |
+-------+--------+---------+2、哪些人的薪水在部门的平均薪水之上
mysql>
select
a.ename,a.sal
from
emp a
join
(
select
deptno,avg(sal) avgsal
from
emp
group by
deptno
) b
on
a.sal > b.avgsal and a.deptno = b.deptno; +-------+---------+
| ename | sal |
+-------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+3、取得部门中（所有人的）平均的薪水等级
mysql>
select
t.deptno,avg(grade)
from
(
select
e.deptno,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
) t
group by
t.deptno;+--------+------------+
| deptno | avg(grade) |
+--------+------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+------------+4、不准用组函数（Max ），取得最高薪水 可重做
方法一：mysql>
select
sal
from
emp
where
sal not in
(
select
distinct a.sal
from
emp a
join
emp b
on a.sal < b.sal
);mysql>
select
sal
from
emp
order by
sal desc
limit 1;+---------+
| sal |
+---------+
| 5000.00 |
+---------+5、取得平均薪水最高的部门的部门编号
方法一： mysql>
select
deptno,avg(sal) avgsal
from
emp
group by
deptno
order by
avgsal;方法二：
mysql>
select
deptno,avg(sal) avgsal
from
emp
group by
deptno
having
avgsal =
(
select
max(t.avgsal)
from
(
select
avg(sal) avgsal
from emp
group by deptno
) t
);+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+6、取得平均薪水最高的部门的部门名称 可重做
mysql>
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 desc
limit
1;+------------+-------------+
| dname | avgsal |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+7、求平均薪水的等级最低的部门的部门名称
注：平均薪水最低就是等级最低
mysql>
select
t.dname
from
(
select
d.dname,avg(e.sal) avgsal
from
emp e
join
dept d
on
e.deptno = d.deptno
group by
d.dname) t
join
salgrade s on t.avgsal
between
s.losal and s.hisal
order by
avgsal limit 1;+-------+
| dname |
+-------+
| SALES |
+-------+8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
mysql>
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
)
);+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+9、取得薪水最高的前五名员工
mysql>
select
ename,sal
from
emp
order by
sal desc
limit 5;+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+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 |
+--------+---------+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 |
| FORD | 1981-12-03 |
| JAMES | 1981-12-03 |
+--------+------------+12、取得每个薪水等级有多少员工
mysql>
select
s.grade grade,count(grade)
from
emp e
join
salgrade s
on
e.sal
between
s.losal and s.hisal
group by
grade;+-------+--------------+
| grade | count(grade) |
+-------+--------------+
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
+-------+--------------+13、面试题：
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。
14、列出所有员工及领导的姓名
mysql>
select
a.ename,ifnull(b.ename,'没有上级')
from
emp a
left join
emp b
on
a.mgr = b.empno;+--------+----------------------------+
| ename | ifnull(b.ename,'没有上级') |
+--------+----------------------------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | 没有上级 |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+----------------------------+15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
mysql>
select
a.empno,a.ename,d.dname
from
emp a
join
emp b
on
a.mgr = b.empno
join
dept d
on
a.deptno = d.deptno
where
a.hiredate < b.hiredate;+-------+-------+------------+
| empno | ename | dname |
+-------+-------+------------+
| 7782 | CLARK | ACCOUNTING |
| 7369 | SMITH | RESEARCH |
| 7566 | JONES | RESEARCH |
| 7499 | ALLEN | SALES |
| 7521 | WARD | SALES |
| 7698 | BLAKE | SALES |
+-------+-------+------------+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 |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+17、列出至少有 5 个员工的所有部门
mysql>
select
d.dname,count(e.ename) count
from
emp e join dept d
on
e.deptno = d.deptno
group by
d.dname
having
count >= 5;+----------+-------+
| dname | count |
+----------+-------+
| RESEARCH | 5 |
| SALES | 6 |
+----------+-------+18、列出薪金比"SMITH" 多的所有员工信息
mysql>
select
*
from
emp
where
sal > (
select
sal
from
emp
where
ename = 'smith'
);19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数\
mysql>
select
t.ename,t2.*
from
(
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where
e.job = 'clerk'
) t
join
(
select
d.dname,count(e.ename) deptcount
from
emp e
join
dept d
on
e.deptno = d.deptno
group by
d.dname
) t2
on
t.dname = t2.dname;+--------+------------+-----------+
| ename | dname | deptcount |
+--------+------------+-----------+
| MILLER | ACCOUNTING | 3 |
| SMITH | RESEARCH | 5 |
| ADAMS | RESEARCH | 5 |
| JAMES | SALES | 6 |
+--------+------------+-----------+20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
简单方法 mysql>
select
job,count(*)
from
emp
group by
job
having
min(sal) > 1500;mysql>
select
t.job,t2.cc
from
(
select
job
from
emp
group by
job
having
min(sal) > 1500
) t
join
(
select
job,count(ename) cc
from
emp
group by
job
) t2
on t.job = t2.job;+-----------+----------+
| job | count(*) |
+-----------+----------+
| ANALYST | 2 |
| MANAGER | 3 |
| PRESIDENT | 1 |
+-----------+----------+21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
方法一：
mysql>
select
e.ename
from
emp e
join
dept d
on
e.deptno = d.deptno
where
d.dname = 'sales';方法二：
select
ename
from
emp
where
deptno =
(
select
deptno
from
dept
where
dname = 'sales'
);+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
left原因是KING有一个NULL，不加左位会少一条KING的信息
mysql>
select
e.ename,d.dname,b.ename,s.grade
from
emp e
join
emp b
on
e.mgr = b.empno
join
salgrade s
on
e.sal between s.losal and s.hisal
join
dept d
on
e.deptno = d.deptno
where
e.sal > (
select avg(sal)
from emp
);+-------+------------+-------+-------+
| 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 |
+-------+------------+-------+-------+23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称
mysql>
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
where job =
(
select
job
from
emp
where
ename = 'scott')
and
e.ename <> 'scott';+-------+----------+
| ename | dname |
+-------+----------+
| FORD | RESEARCH |
+-------+----------+24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
mysql>
select
ename,sal
from
emp
where
sal in
(
select
sal
from
emp
where
deptno = '30'
)
and
deptno <> 30;+-------+----------+
| ename | dname |
+-------+----------+
| FORD | RESEARCH |
+-------+----------+25、列出薪金高于在部门 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)26、列出在每个部门工作的员工数量, 平均工资和平均服务期限
timestampdiff(YEAR, hiredate, now())
mysql>
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 |
+--------+--------+-------------+----------------+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 |
+--------+------------+---------+28、列出所有部门的详细信息和人数
mysql>
select
d.deptno,d.dname,d.loc,count(e.ename)
from
dept d
join
emp e
on
d.deptno = e.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 |
+--------+------------+----------+----------------+29、列出各种工作的最低工资及从事此工作的雇员姓名
mysql>
select
a.job,e.*
from
(
select
job,min(sal) minsal
from
emp
group by
job
) a
join
emp e
on
a.job = e.job
and
a.minsal = e.sal;+-----------+-------+--------+-----------+------+------------+---------+---------+--------+
| job | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-----------+-------+--------+-----------+------+------------+---------+---------+--------+
| CLERK | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| SALESMAN | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| SALESMAN | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| MANAGER | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| ANALYST | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| PRESIDENT | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| ANALYST | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-----------+-------+--------+-----------+------+------------+---------+---------+--------+30、列出各个部门的 MANAGER( 领导) 的最低薪金
简单写法mysql> select
-> deptno, min(sal)
-> from
-> emp
-> where
-> job = 'MANAGER'
-> group by
-> deptno;mysql>
select
deptno,min(sal)
from
emp
where
empno in
(
select
distinct mgr
from
emp
where
mgr is not null
)
group
by deptno;+--------+----------+
| deptno | min(sal) |
+--------+----------+
| 10 | 2450.00 |
| 20 | 2975.00 |
| 30 | 2850.00 |
+--------+----------+31、列出所有员工的 年工资, 按 年薪从低到高排序
mysql>
select
ename,(sal+ifnull(comm,0))*12 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 |
| FORD | 36000.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
+--------+----------+32、求出员工领导的薪水超过3000的员工名称与领导
要b表的sal>3000,原因是因为b的sal才是领导的工资
mysql>
select
a.ename ename,b.ename mname
from
emp a
join
emp b
on
a.mgr = b.empno
where
b.sal > 3000;+-------+-------+
| ename | mname |
+-------+-------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+-------+33、求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数 可重做
where 在 group by前写,并且可以直接再count一下
mysql>
select
d.dname,sum(e.sal),count(e.ename)
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
d.dname like '%S%'
group by
d.dname;+------------+------------+----------------+
| dname | sum(e.sal) | count(e.ename) |
+------------+------------+----------------+
| OPERATIONS | NULL | 0 |
| RESEARCH | 10875.00 | 5 |
| SALES | 9400.00 | 6 |
+------------+------------+----------------+34、给任职日期超过 30 年的员工加薪 10%.
timestampdiff(YEAR, hiredate, now())
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 |
+--------+-----------+
.png)
.png)
