MySQL -老杜练习题（34道）（一）

建表+数据初始化：

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT(
	DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
);
CREATE TABLE EMP(
	EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
);
CREATE TABLE SALGRADE( 
	GRADE INT,
	LOSAL INT,
	HISAL INT 
);

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902,  '1980-12-17', 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20', 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22', 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839,  '1981-04-02', 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28', 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01', 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09', 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17', 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08', 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23', 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698,  '1981-12-03', 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566,  '1981-12-03', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782,  '1982-01-23', 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 5, 3001, 9999); 
commit;

1、取得每个部门最高薪水的人员名称

mysql> select ename,deptno,max(sal) maxsal from emp group by deptno;
+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| CLARK |     10 | 5000.00 |
| SMITH |     20 | 3000.00 |
| ALLEN |     30 | 2850.00 |
+-------+--------+---------+

这是错误的。看上去好像没问题，实际上可能有好几个人是最高薪水，我只查到了其中一个。要进行表连接查询。

select 
	e.ename,t.* 
from 
	emp e 
join 
	(select deptno,max(sal) maxsal from emp group by deptno) t 
on 
	e.deptno = t.deptno and e.sal = t.maxsal;
+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+

2、哪些人的薪水在部门的平均薪水之上

select 
	t.*,e.ename,e.sal 
from 
	emp e 
join 
	(select avg(sal) avgsal,deptno from emp group by deptno) t 
on 
	e.deptno = t.deptno 
where 
	e.sal > t.avgsal;

答案：

select 
	t.*, e.ename, e.sal
from
	emp e
join
	(select deptno,avg(sal) as avgsal from emp group by deptno) t
on
	e.deptno = t.deptno and e.sal > t.avgsal;

3、取得部门中（所有人的）平均的薪水等级

我的解法：

查询结果直接进行分组。

select 
	e.deptno,avg(s.grade)
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal
group by
	e.deptno;

4、不准用分组函数（Max ），取得最高薪水
解法一：降序排序的第一个值就是最大薪水。

select sal from emp order by sal desc limit 1;

select sal from emp order by sal desc limit 1;

解法二：自连接

初步结果：13个结果，king的值没有小于表中任何一个值。

select 
	sal 
from 
	emp 
where 
	sal 
not in 
	(select a.sal from emp a join emp b on a.sal < b.sal);

5、取得平均薪水最高的部门的部门编号

1步.找出每个部门的平均薪水。

2步 降序选第一个。

select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;

方法2：max

SELECT max(t. avgsal) from (select avg(sal) as avgsal  from emp group by deptno)  t ;

6、取得平均薪水最高的部门的部门名称
我的解法：

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;

7、求平均薪水的等级最低的部门的部门名称

-- 7、求平均薪水的等级最低的部门的部门名称
-- 按照部门名称分组，找出每个部门的平均薪水。
SELECT 
	e.DEPTNO , avg(e.sal) as avgsal
from 
	emp e 
GROUP BY
	e.DEPTNO
ORDER BY 
	avgsal DESC;

 -- 2. 找出每个部门的平均薪水的等级
 -- t表与s表连接。
 SELECT 
	t.*, s.grade
FROM	
	(SELECT d.dname, e.DEPTNO , avg(e.sal) as avgsal from  emp e join dept d on e.deptno =d.DEPTNO GROUP BY e.DEPTNO ) t
join 
	salgrade s 
on 
	t.avgsal BETWEEN s.losal and s.hisal;

-- 3.平均薪水对应的等级一定是最低的。找出最低等级
SELECT grade 
from salgrade s
where (select avg(sal) as avgsal  from emp group by deptno ORDER BY avgsal asc limit 1) BETWEEN s.losal and s.hisal;

-- 4. 查找grade=3的表。拼接2，3语句。

 SELECT 
	t.*, s.grade
FROM	
	(SELECT d.dname, e.DEPTNO , avg(e.sal) as avgsal from  emp e join dept d on e.deptno =d.DEPTNO GROUP BY e.DEPTNO ) t
join 
	salgrade s 
on 
	t.avgsal BETWEEN s.losal and s.hisal
where 
	s.grade =(SELECT grade from salgrade s where (select avg(sal) as avgsal  from emp group by deptno ORDER BY avgsal asc limit 1) BETWEEN s.losal and s.hisal) ;

 

8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名

第一步：找出普通员工中，最高薪水。
select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);
第二步：找出比普通员工薪水高的领导。

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));

-- 领导
SELECT distinct mgr from emp;
-- 普通员工最高薪资（必须去重!!!!）
SELECT max(sal) from emp where empno not in (SELECT distinct mgr from emp WHERE mgr is not null);
-- 比普通员工最高薪水还要高的一定是领导！！！！
SELECT 
	ename as leader ,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、取得薪水最高的前五名员工
select sal from emp order by sal desc limit 5;

10、取得薪水最高的第六到第十名员工
select sal from emp order by sal desc limit 5,5;

11、取得最后入职的 5 名员工。日期也可以降序，升序。
select hiredate from emp order by hiredate desc limit 5;

12、取得每个薪水等级有多少员工
select count(e.deptno),s.grade from salgrade s join emp e on e.sal between s.losal and s.hisal group by s.grade;

-- 第一步，找出每个员工的薪水等级
SELECT e.ename , e.sal ,s.grade 
from emp e 
join salgrade s 
on e.sal BETWEEN s.losal and s.hisal; 

-- 第二部，按照grade分组统计数量
SELECT  s.grade ,count(*)
from emp e 
join salgrade s 
on e.sal BETWEEN s.losal and s.hisal
group by s.grade;

13、面试题：（没有讲）
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）

C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）

SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

问题：

1，找出没选过“黎明”老师的所有学生姓名。

2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。

3，即学过 1 号课程又学过 2 号课所有学生的姓名。

14、列出所有员工及领导的姓名
注意：自连接，谁引导谁
select a.ename '员工',b.ename '领导' from emp a left join emp b on a.mgr = b.empno;

15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
我的解法：子查询，查出雇佣日期早于上级领导的，再连接dept表

emp a

- 15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
-- 我的解法：子查询，查出雇佣日期早于上级领导的，再连接dept表

emp a employee
emp b leader

a.hiredate<b.hiredate and a.mgr=b.empno;

SELECT 
	a.ename, a.HIREDATE ,b.ename, b.HIREDATE,d.dname
from
	emp a
join 
	emp b 
on 
	a.hiredate<b.hiredate and a .mgr=b.empno
join dept d
	on a.deptno=d.DEPTNO
	;

16、 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
select e.*,d.dname from emp e right join dept d on e.deptno=d.deptno;

SELECT 
	e.*, d.dname
FROM 
	emp e
right join 
	dept d
on 
	e.deptno=d.deptno;

17、列出至少有 5 个员工的所有部门。
我的解法：

SELECT  deptno
FROM emp
group by DEPTNO 
having count(*)>=5;

18、列出薪金比"SMITH" 多的所有员工信息。

select ename, sal  from emp where sal>(select sal from emp where ename='SMITH');

19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

-- 部门的人数
SELECT deptno,count(*) from emp group by deptno;
select 
	e.ename,e.job, d.dname
from 
	emp e 
join 
	dept d 
ON
	d.DEPTNO=e.DEPTNO
where 
	e. job ='CLERK';
-- 合并，不行


-- 每个部门的人数
-- select deptno

select 
	e.ename,e.job, d.dname，d.deptno
from 
	emp e 
join 
	dept d 
ON
	d.DEPTNO=e.DEPTNO
where 
	e. job ='CLERK';

-- 最终结果
select 
	t1.*, t2.deptcount
FROM
	(select 
		e.ename,e.job, d.dname,d.deptno
	from 
		emp e 
	join 
		dept d 
	ON
		d.DEPTNO=e.DEPTNO
	where 
		e. job ='CLERK'
	) t1
join 
	(SELECT deptno,count(*) as deptcount from emp group by deptno) t2
on 
	t1.deptno = t2.deptno;

其他答案。

select 
	t1.ename,t1.dname,t2.deptno,t2.countDeptno 
from 
	(select e.ename,d.dname,e.deptno from emp e join dept d on e.deptno = d.deptno where e.job = 'clerk') t1 
	join 
	(select deptno,count(deptno) countDeptno from emp group by deptno) t2 
on 
	t1.deptno = t2.deptno;

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数。（某工作的所有员工薪资大于1500）

我的解法：

select t.countJob,t.job from (select count(job) countJob,job,min(sal) minsal from emp group by job) t where minsal > 1500 ;

答案：

select distinct job,count(job) from emp group by job having min(sal) > 1500;

21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.

select ename from emp where deptno =(select deptno from dept where dname='SALES');

22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.

select
	e.ename 'employee',d.dname,l.ename 'leader',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" 从事相同工作的所有员工及部门名称
我的解法：

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 中员工的薪金的其他员工的姓名和薪金。
我的解法：

SELECT ename ,sal  
from emp  
WHERE sal in(SELECT sal from emp where deptno=30) and deptno !=null;

结果为空。

25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称

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);

26、列出在每个部门工作的员工数量, 平均工资和平均服务期限

// 怎么计算年差？？？

TimeStampDiff(间隔类型，前一个日期，后一个日期）；

间隔类型：

SECOND 秒

MINUTE 分钟

HOUR 小时

DAY 天

WEEK 周

MONTH 月

QUARTER 季度

YEAR 年

SELECT TIMESTAMPDIFF(YEAR,HIREDATE,now()) from emp;

select 
	ifnull(avg(e.sal),0) avgsal,ifnull(avg(timestampdiff(year,e.hiredate,now())),0) avghiredate,count(e.deptno) countdept,d.deptno 
from 
	emp e 
right join 
	dept d 
on 
	e.deptno = d.deptno 
group by 
	e.deptno;

我的答案：

select 
	d.deptno, d.dname, count(e.ename) ecount, ifnull(avg(e.sal),0) as avgsal,avg(TimeStampDiff(YEAR,HIREDATE,now())) as avgyear
from 
	emp e 
right join 
	dept d
on
	e.deptno=d.deptno
group by 
	d.deptno ,d.dname;
	
	
SELECT TIMESTAMPDIFF(YEAR,HIREDATE,now()) from emp;

27、 列出所有员工的姓名、部门名称和工资。

select 
 e.ename, e.sal ,d.dname
from 
	emp e
join 
	dept d
on 
	e.deptno = d.deptno;

28、列出所有部门的详细信息和人数
我的解法：为什么外连接，deptno=40的人数为null？

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;