六、MySQL---综合练习题（单表、多表、分组函数以及等级查询）

最新推荐文章于 2024-07-07 13:53:42 发布
最新推荐文章于 2024-07-07 13:53:42 发布
阅读量1.4k 收藏 20
点赞数 32
分类专栏： Mysql 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/M372109150/article/details/135718757
版权
本文介绍了如何使用MySQL进行一系列数据库操作，包括单表查询如部门筛选、员工信息查找，多表连接查询，分组函数如计数、平均工资计算，以及等级查询。涉及创建和使用的表如dept、emp和salgrade，以及多个SQL查询实例。
摘要由CSDN通过智能技术生成

六、MySQL---综合练习题（单表、多表、分组函数以及等级查询）

1.素材简介

数据库：dept_emp
1.dept表：deptno’部门编号’、dname’部门名称’、loc’地理位置’；
2.emp表：empno ‘员工编号’、ename ‘员工名字’、job ‘职位’、mgr ‘上司’、hiredate ‘入职时间’、sal ‘基本工资’,comm ‘补贴’,deptno ‘所属部门编号’；
3.salgrade：grade ‘工资等级’、losal ‘最低限额’、hisal ‘最高限额’；
问题：

单表查询
1.选择部门30中的所有员工
2.列出所有办事员的姓名，编号和部门编号
3.找出佣金高于薪金的员工
4.找出没有佣金的员工
5.找出佣金高于薪金的60%的员工
6.找出部门10中所有人事部长和部门20中所有办事员的详细资料
7.找出收取佣金的员工
8.找出不收取佣金或收取的佣金低于100的员工
9.找出姓张的员工的信息
10.显示员工的姓名和受雇日期,新的员工排在最前面
11.按工作的降序排序,若工作相同则按薪金排序
12.查出不姓刘的员工的信息
13.取出姓李的员工
多表查询
1. 查询出每一位雇员的编号、姓名、职位、部门名称、位置
2.要求查询出每一位雇员的姓名、职位、领导的姓名
3.查询出每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称及位置。
4.列出在部门"销售部"工作的员工的姓名，假定不知道销售部的部门编号。 
5.列出与"李兴"从事相同工作的所有员工。
6.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 
7.列出受雇日期早于其直接上级的所有员工。
分组函数查询
1.按照部门编号分组，求出每个部的人数，平均工资
2.按照职位分组，求出每个职位的最高和最低工资
3.计算出每个各职位的平均工资
4.查询出每个部门的名称、部门的人数、平均工资
left outer join on
5.要求统计出每个部门的详细信息，并且要求这些部门的平均工资高于2000；
6.要求查询出工资比华仔还要高的全部雇员信息
7.要求查询出高于公司平均工资的全部雇员信息
8.查询出每个部门的编号、名称、位置、部门人数、平均工资
9.列出至少有一个员工的所有部门。 
等级查询
1.查询每个工资等级各有多少员工
2.查询部门中（所有人）的平均工资等级

2.创建dept表

CREATE TABLE dept (
deptno INT(2) NOT NULL COMMENT '部门编号',
dname VARCHAR (15) COMMENT '部门名称',
loc VARCHAR (20) COMMENT '地理位置' );
-- 添加主键
ALTER TABLE dept ADD PRIMARY KEY (deptno);
-- 添加数据
INSERT INTO dept (deptno,dname,loc)VALUES (10,'财务部','高新四路');
INSERT INTO dept (deptno,dname,loc)VALUES (20,'人事部','科技二路');
INSERT INTO dept (deptno,dname,loc)VALUES (30,'销售部','长安区');
INSERT INTO dept (deptno,dname,loc)VALUES (40,'运输部','雁塔区');

3.创建emp表

CREATE TABLE emp(
	empno INT(4) NOT NULL COMMENT '员工编号',
	ename VARCHAR(10) COMMENT '员工名字',
	job VARCHAR(10) COMMENT '职位',
	mgr INT(4) COMMENT '上司',
	hiredate DATE COMMENT '入职时间',
	sal INT(7) COMMENT '基本工资',
	comm INT(7) COMMENT '补贴',
	deptno INT(2) COMMENT '所属部门编号'
);

-- 添加主键
ALTER TABLE emp ADD PRIMARY KEY (empno);

-- 添加外键约束
ALTER TABLE emp ADD CONSTRAINT f_ed_key
FOREIGN KEY (deptno) 
REFERENCES dept(deptno);
-- 添加数据
INSERT INTO `emp` VALUES('7369','张倩','办事员','7902','2002-12-17','820',NULL,'20');
INSERT INTO `emp` VALUES('7499','刘博','售货员','7698','1992-02-20','1900','300','30');
INSERT INTO `emp` VALUES('7521','李兴','售货员','7698','1995-07-22','1250','500','30');
INSERT INTO `emp` VALUES('7566','李雷','人事部长','7839','1991-04-02','975',NULL,'20');
INSERT INTO `emp` VALUES('7654','刘浩','售货员','7698','1991-09-28','1250','1400','30');
INSERT INTO `emp` VALUES('7698','刘涛','销售部长','7839','1997-05-01','2850',NULL,'30');
INSERT INTO `emp` VALUES('7782','华仔','人事部长','7839','1995-06-09','2450',NULL,'10');
INSERT INTO `emp` VALUES('7788','张飞','人事专员','7566','1998-04-19','3000',NULL,'20');
INSERT INTO `emp` VALUES('7839','马晓云','董事长',NULL,'1991-11-17','5000',NULL,'10');
INSERT INTO `emp` VALUES('7844','马琪','售货员','7698','1996-09-08','1500','0','30');
INSERT INTO `emp` VALUES('7876','李涵','办事员','7788','1997-05-23','1100',NULL,'20');
INSERT INTO `emp` VALUES('7900','李小涵','销售员','7698','1993-2-13','950',NULL,'30');
INSERT INTO `emp` VALUES('7902','张三','人事组长','7566','1992-10-08','3000',NULL,'20');
INSERT INTO `emp` VALUES('7934','张三丰','人事长','7782','1997-06-23','1300',NULL,'10');

4.创建salgrade表

CREATE TABLE salgrade(
grade INT (10) COMMENT '工资等级',
losal INT (10) COMMENT '最低限额',
hisal INT (10) COMMENT '最高限额');
-- 添加数据
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);

测试：
在这里插入图片描述
在这里插入图片描述
至此，所有数据库与表就创建完成了，就可以就行查询了！

解答：

一、单表查询

1.选择部门30中的所有员工

mysql>  select * from emp where deptno=30;

在这里插入图片描述
2.列出所有办事员的姓名，编号和部门编号

mysql> select ename,empno,deptno from emp where job='办事员';

在这里插入图片描述

3.找出佣金高于薪金的员工

mysql> select * from emp where comm > sal;

在这里插入图片描述

4.找出没有佣金的员工

mysql> select * from emp where comm is null or comm=0;

在这里插入图片描述

5.找出佣金高于薪金的60%的员工

mysql> select * from emp where comm is null or comm=0;

在这里插入图片描述

6.找出部门10中所有人事部长和部门20中所有办事员的详细资料

mysql> select * from emp where comm is null or comm=0;

在这里插入图片描述
7.找出收取佣金的员工

mysql> select * from emp where comm >0;

在这里插入图片描述

8.找出不收取佣金或收取的佣金低于100的员工

mysql> select * from emp where comm is NULL or comm<100;

在这里插入图片描述

9.找出姓张的员工的信息

mysql> select * from emp where ename like '张%';

在这里插入图片描述

10.显示员工的姓名和受雇日期,新的员工排在最前面

mysql> select ename,hiredate from emp order by hiredate desc;

在这里插入图片描述

11.按工作的降序排序,若工作相同则按薪金排序

mysql> select * from emp order by job,sal;

在这里插入图片描述

12.查出不姓刘的员工的信息

mysql>  select * from emp where ename not like '刘%';

在这里插入图片描述

13.取出姓李的员工

mysql> select ename from emp where ename like '李%';

在这里插入图片描述

二、多表查询

1.查询出每一位雇员的编号、姓名、职位、部门名称、位置

mysql>select e.empno,e.ename,e.job,d.dname,d.loc from dept d,emp e where d.deptno=e.deptno;

在这里插入图片描述
2.要求查询出每一位雇员的姓名、职位、领导的姓名

mysql> select e.ename,e.job,m.ename
    -> from emp e,emp m
    -> where e.mgr=m.empno;

在这里插入图片描述

3.查询出每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称及位置。

mysql> select e.empno,e.ename,e.sal,e.job,e.mgr,d.dname,d.loc
    -> from emp e,emp m,dept d
    -> where e.mgr=m.empno and e.deptno=d.deptno;

在这里插入图片描述

4.列出在部门"销售部"工作的员工的姓名，假定不知道销售部的部门编号。

mysql> select e.empno,e.ename,e.sal,e.job,e.mgr,d.dname,d.loc
    -> from emp e,emp m,dept d
    -> where e.mgr=m.empno and e.deptno=d.deptno;

在这里插入图片描述

5.列出与"李兴"从事相同工作的所有员工。

mysql> select e.* from emp e where e.job=(select el.job from emp el where el.ename='李兴');

在这里插入图片描述

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

mysql> select e.ename,e.sal from emp e where e.sal > all
    -> (select el.sal from emp el where deptno =30);

在这里插入图片描述

7.列出受雇日期早于其直接上级的所有员工。

mysql> select e.* from emp e where ((select el.hiredate from emp el where e.mgr=el.empno)-e.hiredate)>0;

在这里插入图片描述

三、分组函数查询

1.按照部门编号分组，求出每个部的人数，平均工资

mysql> select deptno,count(empno),avg(sal) from emp group by deptno;

在这里插入图片描述

2.按照职位分组，求出每个职位的最高和最低工资

mysql> select job,max(sal) as 最高工资,min(sal) as 最低工资 from emp group by job;

在这里插入图片描述

3.计算出每个各职位的平均工资

mysql> select job,avg(sal) from emp group by job;

在这里插入图片描述

4.查询出每个部门的名称、部门的人数、平均工资

mysql> select d.dname,count(e.empno),avg(e.sal) from emp e,dept d where e.deptno=d.deptno group by dname;

在这里插入图片描述
left outer join on
5.要求统计出每个部门的详细信息，并且要求这
些部门的平均工资高于2000；

mysql> select d.dname,avg(e.sal) a
    -> from dept d left outer join emp e
    -> on d.deptno =e.deptno
    -> group by d.dname
    -> having a > 2000;

在这里插入图片描述

6.要求查询出工资比华仔还要高的全部雇员信息

mysql> select * from emp where sal > (select sal from emp where ename='华仔');

在这里插入图片描述

7.要求查询出高于公司平均工资的全部雇员信息

mysql> select * from emp where sal > (select avg(sal) from emp);

在这里插入图片描述

8.查询出每个部门的编号、名称、位置、部门人数、平均工资

mysql> select d.dname,count(e.deptno) c ,avg(e.sal)
    -> from dept d left outer join emp e
    -> on d.deptno =e.deptno
    -> group by d.dname order by c;

在这里插入图片描述

9.列出至少有一个员工的所有部门。

mysql> select d.deptno,d.dname from dept d where(
    -> select count(e.empno) from emp e
    -> where e.deptno=d.deptno)>1;

在这里插入图片描述

四、等级查询

1.查询每个工资等级各有多少员工

mysql> select s.grade,count(*)
    -> from emp e join salgrade s
    -> on e.sal between s.losal and hisal
    -> group by s.grade;

在这里插入图片描述

2.查询部门中（所有人）的平均工资等级

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

在这里插入图片描述

Fly`
关注
  • 32
    点赞
  • 踩
  • 20
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
《第五部分-练习题1：MySQL单表查询》
大大大钢琴
07-15 6891
目录 （新建语句）在Mysql中执行创建student数据库和添加数据 1、查询1832班的成绩信息 2、查询1833班，语文成绩大于80小于90的成绩信息 3、查询学生表中5到10行的数据 4、显示1832班英语成绩为98，数学成绩为77的姓名与学号， 5、查询出1832班成绩并且按语文成绩排序(降序) 6、查询1833班与1832班，语文成绩与数学成绩都小于80的姓名。 7、查询出没有参加语文考试的学生姓名和班级名称。 8、求出班上语文成绩不及格的学生姓名 9、求出每个班的...
题目3：MySQL基本练习【单、多表】
秦淏的博客
06-28 601
文章目录题目1题目2：根据下述条件正确【完美】创建表题目3:根据以下信息，插入数据题目4：写出如下SQL题目5：多表基础练习完成下面的sql 题目1 有如下表： S（sno,sname）学生表，sno为学号，sname为学生姓名； C（cno，cname）课程表，cno为课程号，cname为课程名； SC（sno,cno,scgrade）选课成绩表，scgrade为成绩。 使用SQL语句查询下面条件给出的信息： 有两门以上（含两门）不及格课程的学生姓名及其平均成绩。 既学过“语文”又学过“英语”的所有学生
MySQL 综合分析题_MySQL练习题：综合面试题
weixin_42395171的博客
01-27 219
student学生表sid 学生编号，字符串，主键sname 学生姓名，字符串sage 年龄，数值ssex 性别，字符串teacher教师表tid 教师编号，字符串，主键tname 教师姓名，字符串course课程表cid 课程编号，字符串，主键cname 课程名称，字符串tid 教师编号，字符串，外键sc成绩表sid 学生编号，字符串，外键cid 课程编号，字符串，外键score 成绩，数值1、...
MySQL单表查询练习
12-07
MySQL单表查询练习题
MySQL数据库查询练习
最新发布
2201_75758583的博客
07-07 319
12、查询grade表中math大于90 或者 chinese大于90的记录。2、查询grade表中的id,firstname,lastname字段。10、查询grade表中firstname以A开头后面有4个字符的记录。8、查询grade表中firstname以A开头以e结尾的记录。7、查询grade表中firstname以A开头的记录。13、查询grade表中id不是1、3、5、7的记录。5、查询grade表中id值为2,4,6的学生记录。17、求出表中英语成绩的80的记录的条数。
MySQL单表查询练习题
wjs_6667的博客
07-08 1659
INSERT INTO `emp` VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);INSERT INTO `emp` VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);INSERT INTO `emp` VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
MySQL案例—分组查询
señoritaw的博客
04-25 391
#分组查询 语法： select 分组函数,列(要求出现在group by的后面) from 表名 【where 筛选条件】 group by 分组的列表 【order by 子句】; 特点：1、分组查询中的筛选条件分为两类 位置 关键字 分组前的筛选 group by的前面 where 分组后的筛选 group by的后面 having 注意：分组函数做为筛选条件一定是放在having子句中能用分组前筛选的，
MySQL练习题（单表多表查询）
阿瑾的博客
01-09 410
mysql> select e.depno “部门号”,d.depname “部门名称”,avg(s.inCome-s.outCome) “平均工资” from Employee e,salay s,department d where e.num=s.num and e.depno=d.depno group by d.depno having avg(s.inCome-s.outCome)>2500;求各部门的雇员数（要求显示，部门号、部门名称和部门雇员数）；查找比所有财务部雇员收入都高的雇员的姓名；
【MySQL】练习---单表及多表查询
trichloromethane的博客
01-16 252
一、准备数据 1、创建数据库 mysql> create database if not exists school character set utf8; Query OK, 1 row affected, 1 warning (0.00 sec) 2、进入数据库创建表 #进入数据库 mysql> use school Database changed mysql> select database(); +------------+ | database() | +-.
Mysql-单表查询 教学.pdf
06-17
### MySQL单表查询知识点详解 #### 一、简单查询 **简单查询**是MySQL中最基本的查询类型之一，主要用于从数据库表中检索数据。 ##### 4.1.1 SELECT语句 - **基本语法**: `SELECT [DISTINCT] * | {字段名1, 字段...
mysql-batch:小批量运行大型MySQL UPDATE和DELETE查询，以防止表行级锁定
05-27
mysql批处理 在MySQL中更新或删除大量行将创建锁定，这些锁定将使并行运行的其他查询瘫痪。 该工具将小批量运行UPDATE和DELETE查询，以防止表级和行级锁定（使用InnoDB）。 如果必须更新或删除大量行，则也可以限制...
Mysql练习题基础操作 单表查询 多表查询 数据更新
04-11
在进行MySQL的学习与实践时，基础操作、单表查询、多表查询以及数据更新是四个核心知识点。 1. **基础操作**： - **创建数据库**：使用`CREATE DATABASE`语句创建数据库，如`CREATE DATABASE fuxi;` - **删除...
45-MySQL单表2000万数据查询慢解决方案1
08-08
MySQL单表2000万数据查询慢解决方案1 本文主要讨论了如何解决MySQL单表2000万数据查询慢的问题，通过将表分区和使用时间触发器来实现数据的优化。 分区设计 在解决方案中，我们使用的是按照8周将单表分为8个区，...
mysql-5.7.43-winx64
07-31
9. **分区增强**：对分区表进行了优化，支持更多的分区策略，如线性哈希分区，以及在分区表上使用更多的索引类型。 10. **二进制日志改进**：MySQL 5.7的二进制日志格式更加灵活，可以支持Row、Statement和Mixed...
MySQL单表查询练习：
weixin_73871736的博客
02-13 164
MySQL练习题
MySQL单表多表查询练习
HealerCCX的博客
01-09 964
MySQL单表多表查询
MySQL查询练习-单表查询
Miziguo的博客
07-08 559
MySQL查询-单表查询练习，本文提到了order by，group by，MySQL聚合函数，运算符等进行练习
MySQL基础练习，涉及建库、见表、约束、修改表结构等，超详细
weixin_51612062的博客
11-14 3081
MySQL基础练习(建库、建表、修改表结构，约束)，1. 创建数据库company,在库中创建两个表offices和employees表；2. 创建数据库Market，在Market中创建数据表customers；3. 创建数据库MySchool。超详细！
mysql单表查询练习题
qq_53012201的博客
07-08 839
找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。19.查询男生和女生分别有多少人，并将人数降序输出，并查询出大于9的性别人数信息。查询姓名第二个字母不是’A’且薪水大于1000元的员工信息，按薪水降序排列。找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序。将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Fly`

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值