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

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

2022-10-15 802
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 本文总结了在学习完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 版经典练习题解题思路
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
BoBooY
目录
相关文章
小蜗牛耶
|
5月前
|
SQL 关系型数据库 MySQL
MySQL子查询篇（精选20道子查询练习题）-2
MySQL子查询篇（精选20道子查询练习题）
小蜗牛耶
61 0
小蜗牛耶
|
5月前
|
SQL 关系型数据库 MySQL
MySQL子查询篇（精选20道子查询练习题）-1
MySQL子查询篇（精选20道子查询练习题）
小蜗牛耶
73 0
大刀五十米
|
SQL 关系型数据库 MySQL
MySQL数据库练习题（单表查询，多表关联查询）（一）
MySQL数据库练习题（单表查询，多表关联查询）
大刀五十米
491 0
啊猪是的读来过倒
|
4月前
|
关系型数据库 MySQL 数据库
MySQL SELECT查询实战：练习题精选，提升你的数据库查询技能
MySQL SELECT查询实战：练习题精选，提升你的数据库查询技能
啊猪是的读来过倒
64 0
大刀五十米
|
关系型数据库 MySQL 数据库
MySQL数据库练习题（单表查询，多表关联查询）（二）
MySQL数据库练习题（单表查询，多表关联查询）（二）
大刀五十米
196 0
攒了一袋星辰
|
4月前
|
存储 关系型数据库 MySQL
mysql数据库练习题(涵盖几乎所有知识点)
mysql数据库练习题(涵盖几乎所有知识点)
攒了一袋星辰
34 0
小蜗牛耶
|
11月前
|
SQL 关系型数据库 MySQL
MySQL子查询篇（精选20道子查询练习题）-2
MySQL子查询篇（精选20道子查询练习题）
小蜗牛耶
79 0
小蜗牛耶
|
11月前
|
SQL 关系型数据库 MySQL
MySQL子查询篇（精选20道子查询练习题）-1
MySQL子查询篇（精选20道子查询练习题）
小蜗牛耶
210 0
Rayce.
|
关系型数据库 MySQL
MySQL练习题
MySQL练习题
Rayce.
74 0
大刀五十米
|
关系型数据库 MySQL
MySQL练习题（单表多表查询）
MySQL练习题（单表多表查询）
大刀五十米
194 0

热门文章

最新文章

  • 1
    RDS for SQL server 空间问题排查汇总
  • 2
    MySQL + Keepalived 双主热备搭建
  • 3
    RHEL5下Mysql-5.0.87+apache-2.2.14+php-5.2.11的安装笔记
  • 4
    MySQL 创建数据表
  • 5
    mysql建库
  • 6
    关于MYSQL的行列转换
  • 7
    (解释文)My SQL中主键为0和主键自排约束的关系
  • 8
    MySQL常规日志排错
  • 9
    centos安装nginx+mysql+php+fastcgi+memcache最简单方法
  • 10
    mysql驱动包引发的问题
  • 1
    连接和管理RDS
    102
  • 2
    购买和初始化阿里云RDS
    57
  • 3
    rds教程
    78
  • 4
    TiDB与MySQL、PostgreSQL等数据库的比较分析
    885
  • 5
    OBCP实践 - 迁移 MySQL 数据到 OceanBase 集群
    102
  • 6
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    133
  • 7
    TIDB和MySQL的区别
    744
  • 8
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    308
  • 9
    [MySQL]事务原理之redo log,undo log
    171
  • 10
    [MySQL]SQL优化之索引的使用规则
    70

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    无影云桌面