MySQL面试题(一)

数据库创建

-- 创建员工信息表
CREATE TABLE `emp` (
  `empno` varchar(10) NOT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(10) DEFAULT NULL,
  `mgr` varchar(10) DEFAULT NULL,
  `sal` varchar(10) DEFAULT NULL,
  `deptno` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 创建部门信息表
CREATE TABLE `dept` (
  `deptno` varchar(10) NOT NULL,
  `dname` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- 转存表中的数据 `dept`
--
INSERT INTO `dept` (`deptno`, `dname`) VALUES
('1', '事业部'),
('2', '销售部'),
('3', '技术部');

--
-- 转存表中的数据 `emp`
--

INSERT INTO `emp` (`empno`, `ename`, `job`, `mgr`, `sal`, `deptno`) VALUES
('01', 'jacky', 'clerk', 'tom', '1000', '1'),
('02', 'tom', 'clerk', 'tom', '2000', '1'),
('03', 'jenny', 'sales', 'pretty', '1000', '2'),
('04', 'pretty', 'sales', '', '800', '2'),
('05', 'buddy', 'jishu', 'canndy', '1000', '3'),
('06', 'canddy', 'jishu', '', '1000', '3'),
('07', 'biddy', 'clerk', 'tom', '2000', '1');

实际练习

– 1.查询各部门中薪资最低和最高的数据，包括的子段有部门编号、部门名词、员工名称、最高薪资、最第薪资.

select b.deptno as '部门编号',dname as '部门名词',max(sal) as '最高薪资',
min(sal) as '最低薪资' from emp a inner join dept b on a.deptno=b.deptno 
group by a.deptno;
+--------------+--------------+--------------+--------------+
| 部门编号     | 部门名词     | 最高薪资     | 最低薪资     |
+--------------+--------------+--------------+--------------+
| 1            | 销售部       | 2000         | 1000         |
| 2            | 事业部       | 800          | 600          |
| 3            | 技术部       | 1500         | 1000         |
+--------------+--------------+--------------+--------------+
3 rows in set (0.01 sec)

– 2.查询出各部门中job为CLERK的最高薪资和最低薪资.

select deptno as '部门编号', max(sal) as '最高薪资', min(sal) as '最低薪资' 
from emp where job='CLERK' group by deptno;
+--------------+--------------+--------------+
| 部门编号     | 最高薪资     | 最低薪资     |
+--------------+--------------+--------------+
| 1            | 2000         | 1000         |
+--------------+--------------+--------------+
1 row in set (0.00 sec)

– 3.查询出emp表中最低薪资小于2000的部门且job为clerk的部门编号、最低薪资和最高薪资.

select b.deptno as '部门号',max(sal) as '最高工资',min(sal) as '最低工资' 
from emp as b where job='derk' and (select min(sal) from emp as a where 
a.deptno=b.deptno)<2000 group by b.deptno;
Empty set (0.03 sec)

– 4.查询出emp表中薪资小于2000的且job为clerk的部门编号、最低薪资和最高薪资.

select deptno as '部门编号', max(sal) as '最高薪资', min(sal) as '最低薪资' 
from emp where job='CLERK' and emp.sal< 2000 group by deptno;
+--------------+--------------+--------------+
| 部门编号     | 最高薪资     | 最低薪资     |
+--------------+--------------+--------------+
| 1            | 1000         | 1000         |
+--------------+--------------+--------------+
1 row in set (0.02 sec)

– 5.查询出emp表中员工编号、员工名称、员工薪资并按照薪资由低到高、编号有高到低的顺序进行排序.

select deptno as '部门编号',ename as '员工名词',sal as '薪资' 
from emp order by deptno desc,sal asc;
+--------------+--------------+--------+
| 部门编号     | 员工名词     | 薪资   |
+--------------+--------------+--------+
| 3            | buddy        | 1000   |
| 3            | canndy       | 1500   |
| 2            | jenny        | 600    |
| 2            | pretty       | 800    |
| 1            | jacky        | 1000   |
| 1            | tom          | 2000   |
| 1            | biddy        | 2000   |
+--------------+--------------+--------+
7 rows in set (0.00 sec)

– 6.查询出emp中名字为buddy的所在部门编号以及该部门所在的员工，只查询部门编号与员工名词.

select a.deptno as '部门编号',a.ename as '员工名称' from emp as a where 
deptno=(select b.deptno as deptno from emp as b where b.ename = 'buddy');
* 需要注意的是在子查询中给表取别名的时候不能和前面的主查询的别名一致
+--------------+--------------+
| 部门编号     | 员工名称     |
+--------------+--------------+
| 3            | buddy        |
| 3            | canndy       |
+--------------+--------------+
2 rows in set (0.03 sec)

– 7.查询emp中job为clerk的员工信息.

select a.deptno as '部门编号',a.dname as '部门名词', b.ename as '员工姓名', 
b.job as '员工内容' , b.sal as '员工薪资' from dept as a inner join emp b 
on b.job = 'CLERK' and a.deptno=b.deptno;
+--------------+--------------+--------------+--------------+--------------+
| 部门编号     | 部门名词     | 员工姓名     | 员工内容     | 员工薪资     |
+--------------+--------------+--------------+--------------+--------------+
| 1            | 销售部       | jacky        | clerk        | 1000         |
| 1            | 销售部       | tom          | clerk        | 2000         |
| 1            | 销售部       | biddy        | clerk        | 2000         |
+--------------+--------------+--------------+--------------+--------------+
3 rows in set (0.02 sec)

– 8.查询emp中员工有管理者的员工姓名、管理者的信息

insert into emp values ('01','jacky','clerk','tom','1000','1');
+--------------+-----------+
| 员工名称     | 管理员    |
+--------------+-----------+
| tom          | jacky     |
| tom          | tom       |
| pretty       | jenny     |
|              | pretty    |
| canndy       | buddy     |
|              | canddy    |
| tom          | biddy     |
+--------------+-----------+
7 rows in set (0.00 sec)

– 9.查询出emp表中，部门名称、部门编号并且工作内容为clerk的员工名称和员工岗位.

select b.dname as '部门名称',b.deptno as '部门编号',a.ename as '员工名称',
a.job as '工作岗位' from emp a  inner join dept b  on a.deptno = b.deptno
and  a.job='clerk';
+--------------+--------------+--------------+--------------+
| 部门名称     | 部门编号     | 员工名称     | 工作岗位     |
+--------------+--------------+--------------+--------------+
| 事业部       | 1            | jacky        | clerk        |
| 事业部       | 1            | tom          | clerk        |
| 事业部       | 1            | biddy        | clerk        |
+--------------+--------------+--------------+--------------+
3 rows in set (0.00 sec)

– 10.查询本部门中，高于平均薪资的员工的员工编号、员工名称、部门名称、员工工资

select a.deptno as '部门编号',b.dname as '部门名称',a.ename as '员工姓名',
a.sal as '员工工资' from emp a inner join dept b on a.deptno = b.deptno 
where a.sal>(select avg(sal) as sal from emp)  order by a.deptno;
+--------------+--------------+--------------+--------------+
| 部门编号     | 部门名称     | 员工姓名     | 员工工资     |
+--------------+--------------+--------------+--------------+
| 1            | 事业部       | tom          | 2000         |
| 1            | 事业部       | biddy        | 2000         |
+--------------+--------------+--------------+--------------+
2 rows in set (0.01 sec)

– 11.对于emp中工资高于本部门平均水平，人数多余1人的，列出部门号，人数，平均工资，按部门号排序.

select count(a.empno) as '员工数量',a.deptno as '部门编号',avg(sal) as '平均工资' 
from emp as a where(select count(c.empno) from emp as c where c.deptno=a.deptno 
and c.sal>(select avg(sal) from emp as b where c.deptno=b.deptno))>1 group by 
a.deptno order by a.deptno;  
+--------------+--------------+--------------------+
| 员工数量     | 部门编号     | 平均工资           |
+--------------+--------------+--------------------+
|            3 | 1            | 1666.6666666666667 |
+--------------+--------------+--------------------+
1 row in set (0.00 sec)

– 12.查询工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序

select b.deptno as '部门号',c.dname as '部门名称', b.ename as '姓名', 
b.sal as '工资' from emp as b inner join dept as c on c.deptno = b.deptno  
where b.sal>(select avg(a.sal) from emp as a where a.deptno = b.deptno) order by b.deptno;
+-----------+--------------+--------+--------+
| 部门号    | 部门名称     | 姓名   | 工资   |
+-----------+--------------+--------+--------+
| 1         | 事业部       | tom    | 2000   |
| 1         | 事业部       | biddy  | 2000   |
| 2         | 销售部       | jenny  | 1000   |
+-----------+--------------+--------+--------+
3 rows in set (0.00 sec)

– 13.查询出各个部门中工资高于本部门平均工资的员工数和部门号，按照部门号进行排序.

select a.deptno as '部门号',count(a.sal) as '员工数' 
from emp as a where a.sal>(select avg(b.sal) from emp as b 
where a.deptno=b.deptno) group by a.deptno order by a.deptno;
+-----------+-----------+
| 部门号    | 员工数    |
+-----------+-----------+
| 1         |         2 |
| 2         |         1 |
+-----------+-----------+
2 rows in set (0.00 sec)

–14. 查询出工资低于自己工资至少5人员工，罗列出部门号、姓名和工资以及工资少于自己的人数.

select a.deptno as '部门号',a.ename as '姓名',a.sal as '工资',
(select count(b.ename) from emp as b where b.sal<a.sal) as '人数' 
from emp as a where (select count(b.ename) from emp as b where b.sal<a.sal)>=5;
+-----------+--------+--------+--------+
| 部门号    | 姓名   | 工资   | 人数   |
+-----------+--------+--------+--------+
| 2         | pretty | 800    |      6 |
+-----------+--------+--------+--------+
1 row in set (0.00 sec)