MySQL序列-面试题

于 2024-07-04 09:41:37 发布
阅读量367 收藏 8
点赞数 3
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_63086381/article/details/140170831
版权

第一题

1.jpg

# 第一步：找小于等于80分的学员姓名
select distinct name from t_student where fenshu <= 80

# 第二步：not in
select distinct name from t_student where name not in(select distinct name from t_student where fenshu <= 80)

第二题

2.jpg
image.png
其中，两个表的关联字段为申请单号。
1）查询身份证号为440401430103082的申请日期。
2）查询同一个身份证号码有两条以上记录的身份证号码及记录个数。
3）将身份证号码为440401430103082的记录在两个表中的申请状态均改为07。
4）删除g_cardapplydetail表中所有姓李的记录。
模拟数据：考试做这种题目最重要的是要冷静下来，只有静下来SQL才能写好。要模拟数据。看到数据SQL就好写了。
image.png
1）查询身份证号为440401430103082的申请日期。
bigint转date，可以使用from_unixtime函数。

select a.g_applydate from g_cardapply a join g_cardapplydetail b on a.g_applyno = b.g_applyno where b.g_idcard = '440401430103082'

2）查询同一个身份证号码有两条以上记录的身份证号码及记录个数。

select count(g_idcard),g_idcard from g_cardapplydetail group by g_idcard having count(g_idcard) >= 2

3）将身份证号码为440401430103082的记录在两个表中的申请状态均改为07。

UPDATE 
	g_cardapply
JOIN 
	g_cardapplydetail 
ON 
	g_cardapply.g_applyno = g_cardapplydetail.g_applyno 
AND
	g_cardapplydetail.g_idcard = '440401430103082'
SET g_cardapply.g_state = '07',
g_cardapplydetail.g_state = '07'

4）删除g_cardapplydetail表中所有姓李的记录。

delete t1,t2 from g_cardapply t1 join g_cardapplydetail t2 on t1.g_applyno=t2.g_applyno where t2.g_name like '李%';

第三题

面试题3.jpg
面试题5.jpg
表名：stuscore
1）统计如下：课程不及格[059]的多少个，良[6080]多少个，优[81-100]多少个。
2）计算科科及格的人的平均成绩。

第四题

QQ图片20151126234632.jpg
1）请用一条SQL语句查询出不同部门中担任“钳工”的职工平均工资。
2）请用一条SQL语句查询出不同部门中担任“钳工”的职工平均工资高于2000的部门。

第五题

image.png
Employee是雇员信息表：
雇员姓名（主键）：person-name
街道：street
城市：city
Company是公司信息表：
公司名称（主键）：company-name
城市：city
Works是雇员工作信息表：
雇员姓名（主键）：person-name
公司名称：company-name
年薪：salary
Manages是雇员工作关系表：
雇员姓名（主键）：person-name
经理姓名：manager-name
模拟数据：
员工表：employee
image.png
公司表：company
image.png
雇员工作信息表：Works
image.png
雇员工作关系表：Manages
image.png

请给出下面每一个查询的SQL语句：

  1. 找出所有居住地与工作的公司在同一城市的员工的姓名。
  2. 找出比Small Bank Corporation的所有员工收入都高的所有员工的姓名。
  3. 找出平均年薪在10000美元以上的公司及其平均年薪。

第六题

IMG_1621.JPGIMG_1616.JPG
客户表Client
image.png
订单表Order
image.png
客户订单表ClientOrder
image.png
图书表Book
image.png

  1. 请写出一条SQL语句，查询出每个客户的所有订单并按照地址排序，要求输出格式为：address client_name phone order_id
  2. 请写出一条SQL语句，查询出每个客户订购的图书总价。要求输出格式为：client_name total_price
  3. 如果要求每个订单可以包含多种图书，应该如何修改Order表的主键？为了保证每个订单只被一个客户拥有，应该在ClientOrder表上增加怎样的约束？

第七题

image.png
image.png
模拟数据：
学生表：student
image.png
课程表：course
image.png
成绩表：sc
image.png
教师表：teacher
image.png

  1. 查询1号课比2号课成绩高的所有学生学号。
  2. 查询平均成绩大于60分的学号和平均成绩。
  3. 查询所有学生学号、姓名、选课数、总成绩。
  4. 查询姓“李”的老师的个数。
  5. 查询没学过“叶平”老师课的学号、姓名。

第八题

image.png
学生表：student
image.png
课程表：class
image.png
选课表：chosen_class
image.png

  1. 没有选修课程编号为C1的学生姓名
  2. 列出每门课程名称和平均成绩，并按照成绩排序
  3. 选了2门课以上的学生姓名。

第九题

image.png
image.png
要转换成：
image.png

MySQL行转列

MySQL行转列又叫做数据透视。什么叫做行转列？将原本横向排列的数据透视成纵向排列的数据，进而进行计算、分析、展示等操作。

假设有一个学生选课成绩表，包含学生姓名（stu_name）、课程名称（course_name）和分数（score）三个字段。在原始数据中，每个学生在不同的课程中都有自己的得分情况，数据样例如下：

stu_namecourse_namescore
张三数学80
张三英语85
张三历史90
李四数学75
李四英语92
李四历史85
王五数学88
王五英语90
王五历史95

可以使用行转列操作，将每个学生在不同课程中的分数拆分成多条记录，每条记录包含一个课程以及对应的分数。转换后的数据样例如下：

stu_name数学英语历史
张三808590
李四759285
王五889095

从上表中可以看出，在行转列之后，每一行记录都表示了一个学生在不同课程中的分数。这样更便于对不同科目的分数进行比较、计算平均值等分析操作。

使用case when+group by完成

drop table if exists t_student;
create table t_student(
  stu_name varchar(10),
  course_name varchar(10),
  score int
);
insert into t_student(stu_name, course_name, score) values('张三', '数学', 80);
insert into t_student(stu_name, course_name, score) values('张三', '英语', 85);
insert into t_student(stu_name, course_name, score) values('张三', '历史', 90);
insert into t_student(stu_name, course_name, score) values('李四', '数学', 75);
insert into t_student(stu_name, course_name, score) values('李四', '英语', 92);
insert into t_student(stu_name, course_name, score) values('李四', '历史', 85);
insert into t_student(stu_name, course_name, score) values('王五', '数学', 88);
insert into t_student(stu_name, course_name, score) values('王五', '英语', 90);
insert into t_student(stu_name, course_name, score) values('王五', '历史', 95);
commit;
select * from t_student;

image.png
行转列后的效果是：
image.png
sql如下：

select
	stu_name,
	max(case course_name when '数学' then score else 0 end) as '数学',
	max(case course_name when '英语' then score else 0 end) as '英语', 
	max(case course_name when '历史' then score else 0 end) as '历史' 
from 
	t_student
group by 
	stu_name;

通过以上内容的学习，我们这个面试题就迎刃而解了：

select
	year,
	max(case season when '一季度' then count else 0 end) as '一季度',
	max(case season when '二季度' then count else 0 end) as '二季度',
	max(case season when '三季度' then count else 0 end) as '三季度',
	max(case season when '四季度' then count else 0 end) as '四季度'
from 
	t_temp 
group by 
	year;

第十题

image.png

select 
	x.a 开始数字, y.a 结束数字
from 
	(select m.a,row_number() over(order by m.a) as rownum from (select a, lag(a) over(order by a asc) as pre_a from t) m where m.a - m.pre_a != 1 or m.pre_a is null) x 
join 
	(select n.a,row_number() over(order by n.a) as rownum from (select a, lead(a) over(order by a asc) as next_a from t) n where n.next_a - n.a != 1 or n.next_a is null) y 
on 
	x.rownum = y.rownum;

解答上面这个题目需要具备以下知识点：

  • lag函数
  • lead函数
  • row_number函数

lag函数：获取当前行的上一行数据

select empno,ename,sal,(lag(sal) over(order by sal asc)) as pre_sal from emp;

image.png
注意：over函数用来指定“在…范围内”，通常和lag函数联用。

lead函数：获取当前行的下一行数据

select empno,ename,sal,(lead(sal) over(order by sal asc)) as next_sal from emp;

image.png
注意：over函数用来指定“在…范围内”，通常和lead函数联用。

row_number函数：可以为查询结果集生成行号：

select empno,ename,sal,row_number() over(order by sal) as rownum from emp;

image.png

利用row_number函数，将两个不相关的列拼接在一起显示：
image.png
image.png

select 
	x.a, y.b 
from 
	(select a,row_number() over(order by a) as rownum from t1) x 
join 
	(select b,row_number() over(order by b) as rownum from t2) y 
on 
	x.rownum = y.rownum;

image.png

CTE语法（公用表表达式）：Common Table Expression。创建临时表的一种语法：

-- 查询每个部门平均工资的工资等级
-- 第一种写法
select 
	t.deptno,t.avgsal,s.grade 
from 
	(select deptno,avg(sal) as avgsal from emp group by deptno) t 
join 
	salgrade s 
on 
	t.avgsal between s.losal and s.hisal;

-- 第二种写法：使用CTE语法
with cte_exp as(select deptno,avg(sal) as avgsal from emp group by deptno)
select 
	cte_exp.deptno,cte_exp.avgsal,s.grade
from
	cte_exp
join
	salgrade s
on
	cte_exp.avgsal between s.losal and s.hisal;

partition by：将数据分区，和group by区别是：group by是分组，然后和分组函数一起用。partition by分区不需要和分组函数一起使用

select deptno, empno,ename,sal,(lag(sal) over(partition by deptno order by sal asc)) as pre_sal from emp;

image.png

MySQL 8.0及以上版本中支持如下常用的窗口函数：

  1. ROW_NUMBER()：排名函数，返回当前结果集中每个行的行号；
  2. RANK()：排名函数，计算分组结果中的排名，相同的行排名相同且没有空缺，下一个行排名跳过空缺；
  3. DENSE_RANK()：排名函数，计算分组结果中的排名，相同的行排名相同，排名连续，没有空缺；
  4. NTILE()：将分组结果等分为指定的组数，计算每组的大小；
  5. LAG()：返回分组内前一行的值；
  6. LEAD()：返回分组内后一行的值；
  7. FIRST_VALUE()：返回分组内第一个值；
  8. LAST_VALUE()：返回分组内最后一个值；
  9. AVG()、SUM()、COUNT()、MIN()、MAX()：聚合函数，可以配合OVER()进行窗口操作。

需要注意的是，MySQL的窗口函数和其他DBMS中的窗口函数相比较，可能略有不同，需要根据MySQL的文档进行使用。

Mysql相关面试题 - 2024最新总结（持续更新...）
伟伟_爱撒娇的博客
03-12 1250
Mysql、mysql、面试相关题目、存储引擎、事务、字段类型、索引、锁、日志
SQL练习题（共82题）
weixin_43176270的博客
08-05 3079
id 表示用户 id；非禁止用户即 Banned 为 No 的用户，禁止用户即 Banned 为 Yes 的用户。[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JX7NHMq3-1691249682537)(imgs/image-20211128192755637.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-p7aupgxU-1691249682537)(imgs/image-20211128192845380.png)]
综合练习4：企业综合SQL笔试1
08-03
1、查询“201”课程比“202”课程成绩高的所有学生的学号 2、查询平均成绩大于“60”分的同学的学号和平均成绩 3、查询“所有”同学的学号、姓名、选课数、总
MySQL——SQL练习题目（部门员工表）
会写Bug的攻城狮
11-03 7291
1、建表 -- 部门表 create table dept( deptno int primary key auto_increment, -- 部门编号 dname varchar(14) , -- 部门名字 loc varchar(13) -- 地址 ) ; -- 员工表 create table emp( empno int primary key auto_increment,-- 员工编号 ename varchar(10), -- 员工姓名 - job
SQL训练
stay hungry ! stay foolish!
02-11 1485
题目描述 一个员工employees表简况如下: 建表语句如下: CREATETABLE`employees`( `emp_no`int(11)NOTNULL, `birth_date`dateNOTNULL, `first_name`varchar(14)NOTNULL, `last_name`varchar(16)NOTNULL, `gender`char(1)NOTNULL, `hire_date`dateNOTNULL, PRIMARY...
[每日一练]销售员查询
最新发布
2302_77975940的博客
06-13 1147
表:sales_id 是该表的主键列(具有唯一值的列)。该表的每一行都显示了销售人员的姓名和 ID ，以及他们的工资、佣金率和雇佣日期。表:Companycom_id 是该表的主键列(具有唯一值的列)。该表的每一行都表示公司的名称和 ID ，以及公司所在的城市。表:Ordersorder_id 是该表的主键列(具有唯一值的列)。com_id 是 Company 表中 com_id 的外键（reference 列）。
2024年java面试题-数据库MySQL面试题第一部分
04-22
根据给定文件的信息，我们可以总结出一系列关于MySQL的知识点，特别是针对面试中可能出现的问题及其解答。下面是详细解析： ### 1. ID 自增主键的行为 - **问题**：一张表中有 ID 自增主键，当插入了 17 条记录之后...
2023-MySQL面试必备25题及答案
02-28
MySQL是世界上最受欢迎的...掌握这些MySQL面试题的解答，可以帮助你在面试中展示扎实的数据库理论基础和实践经验，提升你的竞争力。在实际工作中，了解如何优化查询、管理存储引擎、处理事务和安全等高级主题同样重要。
记录一些MySQL、Redis面试题以及一些常见的算法题 获取最新MySQL面试题
11-11
本资料集主要涵盖了MySQL和Redis的面试题目以及一些常见的算法问题，旨在帮助准备面试或提升技能的IT从业者。 对于MySQL，你需要了解的基本概念包括但不限于： 1. **数据类型**：了解MySQL中的各种数据类型，如INT...
mysql面试题100分
11-15
MySQL 面试题知识点总结 MySQL 是一种关系型数据库管理系统，广泛应用于各种 web 应用程序中。本文总结了 MySQL 面试题中的关键知识点，涵盖了选择题、问答题和综合题三个部分。 选择题 1. MySQL 是一种关系型...
身份证号码区域查询数据库 (mysql 格式)
10-25
全国各地的身份证号码区域数据, 更新到2017年. 直接的mysql语句,直接可以使用.
以企业面试讲解sql语句
qy20115549的博客
10-17 805
如上图所示，关于sql语句相关写法请去看我写的另外一篇博客 http://blog.csdn.net/qy20115549/article/details/52819503：首先，我们要分析题，分析其逻辑结构。根据我的分析，我认为其中存在4个表格。一张是关于医院信息的表格。一张是关于医院门诊部的表格。一张是门诊部对应医生的表格。最后，一张便是病人信息的表格。 第（1）问为建表语句：CREATE
sql语句练习2
qq_53012201的博客
01-05 810
sql语句练习2
MySql的Sql语句的练习（试试你能写出来几道呢）
m0_56154713的博客
07-26 1698
MySql的练习题
sql 50道 练习题
running_131的博客
03-25 8788
sql 练习题目，学生教师分数表
1-6练习题-SQL进阶教程
Tracy785的博客
08-23 273
练习题1-6-1 ：简化多行数据的比较 在“用列表展示与上一年的比较结果”部分，我们比较了企业每年的营业额与上一年相比是否增加。其实，第一条SQL的性能还有改善的余地。在三个when子句里，同样的子查询执行了三次，有点多余，所以请把它们整合在一个when子句里 练习题1-6-2 ：使用overlaps查询重叠的时间区间 很多人都不知道，SQL-92提供了overlaps谓词（Oracle和Post...
SQL脚本优化《企业所用》
weixin_42325823的博客
05-07 147
SQL笔试题
沉觞的博客
09-25 954
SQL笔试题
MySQL窗口函数详解与面试题
"这篇文档是关于MySQL窗口函数的面试题，涵盖了窗口函数的基本语法和一些专用函数，如ROW_NUMBER()和RANK()的讲解，并提供了一个实际问题的示例来展示如何应用这些知识。" 在MySQL中，窗口函数是一种强大的工具，它...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

ChnpngWng

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

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

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

打赏作者

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

抵扣说明：

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

余额充值