AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

MySQL经典练习题+解题思路（一）

2022-10-15 919
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL DuckDB 分析主实例，集群系列 4核8GB
RDS AI 助手，专业版
RDS MySQL DuckDB 分析主实例，基础系列 4核8GB
简介： 本文总结了在学习完MySQL之后 个人练习相关练习题的解题思路和答案

MySQL练习题（一）

导入练习数据

CREATE DATABASE `mysqlpractice`
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、取得每个部门最高薪水的人员名称

（1）先按照部门编号分组，取得每个部门的最高薪水。

select 
    e.ename,e.deptno,max(e.sal) '最高薪水'
from
    emp e
group by 
    e.deptno;

在这里插入图片描述

（2）再将查询结果当做一张临时表，与emp表进行表连接（因为有的最高薪水是相同的人，需要将他们都显示出来）

select
    e.ename,e.deptno,e.sal
from
    (select ename,deptno,max(sal) as maxsal from emp group by deptno) t
join
    emp e
on 
    t.deptno = e.deptno and e.sal = t.maxsal
order by  
    e.deptno;

在这里插入图片描述

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

（1）先找出每个部门的平均薪水

select 
    deptno,avg(sal)
from
    emp
group by
    deptno;

在这里插入图片描述

（2）找出每个部门比各自部门平均薪水高的人

    条件：比平均薪水高，部门号相同

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

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

（1）先找出每个人的薪资等级

select 
    e.ename,s.grade,e.deptno
from
    emp e
join
    salgrade s
on 
    e.sal between s.losal and s.hisal;

在这里插入图片描述

（2）再按部门分组，求得每个部门的平均薪资水平

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

在这里插入图片描述

4、不准用组函数（Max ），取得最高薪水

  • 方式一：使用limit分页

（1）将每个人的薪水进行降序排列，然后使用limit分页取第一个人的薪水

select 
    e.ename,e.sal
from
    emp e
order by
    e.sal desc
limit 0,1;

（2）将结果当做一个临时表，与emp进行内连接 条件为emp表中的薪水 = 临时表中的薪水

select 
    e.ename,e.sal
from
    emp e
join 
    (select 
        e.ename as ename,e.sal as sal
    from
        emp e
    order by
        e.sal desc
    limit 0,1
    ) t
on
    e.sal = t.sal;

在这里插入图片描述

  • 方式二：使用表的自连接

（1）将emp表自连接，找出 emp a表中所有比 emp b表中薪资小的薪水生成一个结果

select 
    distinct a.sal
from
    emp a
join 
    emp b
on 
    a.sal < b.sal;

在这里插入图片描述

（2）再将emp中薪水不在这个结果中薪水找出来就是最高薪资

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

在这里插入图片描述

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

（1）取得每个部门的平均薪水

select 
    deptno,avg(sal)
from
    emp
group by
    deptno;

在这里插入图片描述

（2）将结果当做一张临时表进行取最高

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

在这里插入图片描述

6、取得平均薪水最高的部门的部门名称

（1）按部门分组求得每个组的平均薪水

select deptno ,avg(sal) from emp group by deptno;

image-20220410143312102.png

（2）将结果与dept表进行内连接

select 
    d.dname,t.deptno,max(t.avgsal) 
from 
    (select deptno ,avg(sal) avgsal from emp group by deptno) t
join 
    dept d
on 
    t.deptno = d.deptno;

在这里插入图片描述

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

（1）按部门分组求得每个组的平均薪水

select deptno ,avg(sal) from emp group by deptno;

在这里插入图片描述

（2）将结果与salgrade表进行内连接 求得每个部门的部门等级,并取最低等级的部门

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

在这里插入图片描述

（3）将结果与部门表进行内连接求得平均薪资水平最低的部门名称

select
    d.dname,t2.deptno,t2.avgsal,t2.mingrade
from (
    select 
        t.deptno deptno,min(s.grade) mingrade,t.avgsal avgsal
    from 
        (select deptno ,avg(sal) avgsal from emp group by deptno) t
        join 
            salgrade s
        on 
            t.avgsal between s.losal and s.hisal
) t2
join
    dept d
on
    t2.deptno = d.deptno;

在这里插入图片描述

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

（1）找出所有是领导身份的员工代码

select distinct mgr from emp where mgr is not null;

在这里插入图片描述

（2）不在上面结果的员工都是普通员工，取工资最高的

select 
    max(sal) 
from 
    emp 
where empno not in(select distinct mgr from emp where mgr is not null);

在这里插入图片描述

（3）找出比这个最高工资大的员工的姓名和编号

select 
    e.ename,e.empno
from 
    emp e
where(
    e.sal > (select 
                max(sal) 
            from 
                emp 
            where 
                 empno not in(select distinct mgr from emp where mgr is not null))
);

在这里插入图片描述

文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
关键词：
云数据库 RDS MySQL 版练习题
云数据库 RDS MySQL 版经典练习题解题思路
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
BoBooY
目录
相关文章
小蜗牛耶
|
SQL 关系型数据库 MySQL
MySQL子查询篇（精选20道子查询练习题）-2
MySQL子查询篇（精选20道子查询练习题）
小蜗牛耶
188 0
小蜗牛耶
|
SQL 关系型数据库 MySQL
MySQL子查询篇（精选20道子查询练习题）-1
MySQL子查询篇（精选20道子查询练习题）
小蜗牛耶
219 0
大刀五十米
|
SQL 关系型数据库 MySQL
MySQL数据库练习题（单表查询，多表关联查询）（一）
MySQL数据库练习题（单表查询，多表关联查询）
大刀五十米
789 1
大刀五十米
|
关系型数据库 MySQL 数据库
MySQL数据库练习题（单表查询，多表关联查询）（二）
MySQL数据库练习题（单表查询，多表关联查询）（二）
大刀五十米
340 0
啊猪是的读来过倒
|
关系型数据库 MySQL 数据库
MySQL SELECT查询实战：练习题精选，提升你的数据库查询技能
MySQL SELECT查询实战：练习题精选，提升你的数据库查询技能
啊猪是的读来过倒
353 0
攒了一袋星辰
|
存储 关系型数据库 MySQL
mysql数据库练习题(涵盖几乎所有知识点)
mysql数据库练习题(涵盖几乎所有知识点)
攒了一袋星辰
1274 0
小蜗牛耶
|
SQL 关系型数据库 MySQL
MySQL子查询篇（精选20道子查询练习题）-2
MySQL子查询篇（精选20道子查询练习题）
小蜗牛耶
229 0
小蜗牛耶
|
SQL 关系型数据库 MySQL
MySQL子查询篇（精选20道子查询练习题）-1
MySQL子查询篇（精选20道子查询练习题）
小蜗牛耶
586 0
Rayce.
|
关系型数据库 MySQL
MySQL练习题
MySQL练习题
Rayce.
152 0
大刀五十米
|
关系型数据库 MySQL
MySQL练习题（单表多表查询）
MySQL练习题（单表多表查询）
大刀五十米
435 0

热门文章

最新文章

  • 1
    阿里云DTS踩坑经验分享系列｜如何使用DTS进行MySQL->ClickHouse同步
  • 2
    MySQL表连接（内连接、交叉连接、外连接、联合查询）
  • 3
    MySQL 小心使用 replace into
  • 4
    连接远程mysql数据库失败常见原因及解决办法
  • 5
    对比MySQL，一文看透HBase的能力及使用场景
  • 6
    MYSQL-mybatisplus的主键自增问题与@Tableld@TableField@TableLogic的学习
  • 7
    谨慎使用MYSQL表分区
  • 8
    mysql replace into语句学习
  • 9
    MySQL 深潜 - MDL 锁的实现与获取机制
  • 10
    Mysql Binlog的介绍
  • 1
    【紧急救援】MySQL CPU 100%！一套组合拳教你快速定位并解决！
    661
  • 2
    MySQL中日期和时间函数的使用指南
    343
  • 3
    MySQL 与 Redis 如何保证双写一致性？
    625
  • 4
    【赵渝强老师】MySQL的事务隔离级别
    359
  • 5
    【Docker】（3）学习Docker中 镜像与容器数据卷、映射关系！手把手带你安装 MySql主从同步 和 Redis三主三从集群！并且进行主从切换与扩容操作，还有分析 哈希分区 等知识点！
    693
  • 6
    MySQL源码编译安装
    1437
  • 7
    MySQL二进制包安装
    737
  • 8
    MySQL包安装 -- SUSE系列（离线RPM包安装MySQL）
    627
  • 9
    MySQL包安装 -- SUSE系列（SUSE资源库安装MySQL）
    596
  • 10
    MySQL包安装 -- Debian系列（离线DEB包安装MySQL）
    2142

    • 相关课程

    更多
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云MySQL云数据库产品体系介绍
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL

    • 相关实验场景

    更多
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    云安全中心：病毒查杀