MySQL操作语句练习【经典20题】

emp 表视图

dept 表视图

题目

1.请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。

2.请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。

3.请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。

4.请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。

5.请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。

6.在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。

7.列出没有对应部门表信息的所有雇员的姓名、工作以及部门号。

8.查找工资在1000～3000之间的雇员所在部门的所有人员信息

9.雇员中谁的工资最高。

10.查询所有雇员的姓名、SAL与COMM之和。

11.查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字

12.查询各部门中81年1月1日以后来的员工数

13.查询所有在CHICAGO工作的经理MANAGER和销售员SALESMAN的姓名、工资

14.查询列出来公司就职时间超过24年的员工名单

15.查询于81年来公司所有员工的总收入（SAL和COMM）

16.查询显示每个雇员加入公司的准确时间，按××××年××月××日 时分秒显示。

17.查询公司中按年份月份统计各地的录用职工数量

18.查询列出各部门的部门名和部门经理名字

19.查询部门平均工资最高的部门名称和最低的部门名称

20.查询与雇员号为7521员工的最接近的在其后进入公司的员工姓名及其所在部门名

参考答案

1.请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。

-- 1.请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。
select ename,sal 
from emp 
where job in ('CLERK','MANAGER');

2.请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。

-- 2.请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。
select ename,deptno,sal,job 
from emp 
where deptno between 10 and 30;
-- between 小 and 大
-- between and 

3.请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。

-- 3.请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。
-- 模糊查询
select ename,sal,job 
from emp 
where ename like 'J%';

4.请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。

-- 4.请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。
-- 排序语法：order by 字段 [asc/desc]  放在最后，升序默认 asc 降序 desc
select ename, job, sal 
from emp
where sal < 2000
order by sal desc ;

5.请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。

-- 5.请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。
-- 两种方式
  -- 1.标准写法
select ename,sal,e.deptno,dname,loc 
from emp e inner join dept d
on job = 'CLERK' and e.`deptno` = d.`deptno`;
-- 注意：该题为多表联查，注意去重，要不然查出来的是两表的笛卡尔积
  -- 2.简写
select ename,sal,e.deptno,dname,loc 
from emp e,dept d
where job = 'CLERK' and e.`deptno` = d.`deptno`;

6.在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。

-- 6.在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。
-- 考察嵌套查询
select ename,job,sal 
from emp
where sal > (select max(sal) from emp where ename = 'JONES')

7.列出没有对应部门表信息的所有雇员的姓名、工作以及部门号。

-- 7.列出没有对应部门表信息的所有雇员的姓名、工作以及部门号。
select ename,job,deptno 
from emp 
where deptno not in (select deptno from dept);

8.查找工资在1000～3000之间的雇员所在部门的所有人员信息

-- 8.查找工资在1000～3000之间的雇员所在部门的所有人员信息
select * from emp where deptno in
(select deptno 
from emp 
where sal between 1000 and 3000 
group by deptno);

9.雇员中谁的工资最高。

-- 9.雇员中谁的工资最高。
-- 嵌套查询，先找出来最高工资，再找谁的工资
select ename 
from emp 
where sal = (select max(sal) from emp);

10.查询所有雇员的姓名、SAL与COMM之和。

-- 10.查询所有雇员的姓名、SAL与COMM之和。
 -- 考察运算符 + - * / %
select ename , sal+comm as 总工资
from emp;

11.查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字

-- 11.查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字
select ename, sal, deptno 
from emp 
where hiredate < "1981-07-01";

12.查询各部门中81年1月1日以后来的员工数

-- 12.查询各部门中81年1月1日以后来的员工数
-- 聚合函数count（*）
select count(*) 
from emp 
where hiredate > "1981-01-01";

13.查询所有在CHICAGO工作的经理MANAGER和销售员SALESMAN的姓名、工资

-- 13.查询所有在CHICAGO工作的经理MANAGER和销售员SALESMAN的姓名、工资
-- 多表查询
select e.`ename`,e.`sal` 
from emp e, dept d 
where e.`deptno` = d.`deptno` 
and e.`job`in ("MANAGER","SALESMAN") 
and d.`loc` = "CHICAGO";

14.查询列出来公司就职时间超过24年的员工名单

-- 14.查询列出来公司就职时间超过24年的员工名单
-- 考察日期函数
select (now() - "1980-01-01")
select datediff(current_date(),"1980-01-01")/365
select now(); -- now() 当前日期和时间
-- 日期函数
select sysdate(); -- sysdate()当前系统日期和时间
select current_date(); -- 获得当前日期
select current_time();-- 获得时分秒
select current_timestamp();-- 获得当前时间戳

15.查询于81年来公司所有员工的总收入（SAL和COMM）

-- 日期函数year()和聚合函数sum()
select sum(sal+comm) 总工资 
from emp 
where year(hiredate) = "1981";

16.查询显示每个雇员加入公司的准确时间，按××××年××月××日 时分秒显示。

-- 16.查询显示每个雇员加入公司的准确时间，按××××年××月××日 时分秒显示。
-- 考察日期格式化函数 date_format(date,"format")
-- 常用的格式：
	-- %Y-%m-%d  2000-01-02
	-- %Y年%m月%d日  2000年02月02日
	-- %y年%c月%e日 00年2月2日
select ename ,date_format(hiredate,"%Y年%m月%d日") 入职时间 from emp;

17.查询公司中按年份月份统计各地的录用职工数量

-- 17.查询公司中按年份月份统计各地的录用职工数量
-- 多表查询，分组查询，聚合函数
select year(e.hiredate)年份,month(e.`hiredate`)月份,d.`loc`,count(*) 
from emp e,dept d 
where e.`deptno` = d.`deptno`
 group by year(e.hiredate),month(e.`hiredate`),d.`loc`; 

18.查询列出各部门的部门名和部门经理名字

-- 18.查询列出各部门的部门名和部门经理名字
select e.`ename` ,d.`dname` from emp e , dept d 
where e.`deptno` = e.`deptno`
and e.`job` = "MANAGER";

19.查询部门平均工资最高的部门名称和最低的部门名称

-- 联合查询，排序，聚合，逻辑较复杂
select deptno,dname from dept where deptno = (
select deptno from emp group by deptno order by avg(sal) desc limit 1
)
union all
select deptno,dname from dept where deptno = (
select deptno from emp group by deptno order by avg(sal) asc limit 1
)

20.查询与雇员号为7521员工的最接近的在其后进入公司的员工姓名及其所在部门名

-- 多次嵌套查询
select e.ename,d.deptno,d.dname 
from
( select ename, deptno, hiredate 
from emp 
where hiredate >( select hiredate from emp where empno = 7521 ) order by hiredate limit 1 ) e,
	dept d 
where
	e.deptno = d.deptno