【SQL学习进阶】从入门到高级应用【企业真题】

在这里插入图片描述

文章目录

  • 第一题
  • 第二题
  • 第三题
  • 第四题
  • 第五题
  • 第六题
  • 第七题
  • 第八题
  • 第九题
    • MySQL行转列
      • 使用case when+group by完成
  • 第十题

🌈你好呀！我是 山顶风景独好
💕欢迎来到我的博客，很高兴能够在这里和您见面！
💕希望您在这里可以感受到一份轻松愉快的氛围！
💕这里不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。
🚀 欢迎一起踏上探险之旅，挖掘无限可能，共同成长！

🏠大家订阅本专栏！本专栏旨在为SQL初学者提供一条逐步迈向高级应用的学习之路，带您从零开始，一步一步练习，逐步掌握SQL的精髓，实现技能的提升与飞跃！😊

第一题

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的文档进行使用。

✨ 这就是今天要分享给大家的全部内容了，我们下期再见！😊

🏠 我在CSDN等你哦！我的主页😍

在这里插入图片描述

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/670459.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【RuoYi】实现文件的上传与下载

【RuoYi】实现文件的上传与下载

一、前言 首先&#xff0c;最近在做一个管理系统&#xff0c;里面刚好需要用到echarts图和富文本编辑器&#xff0c;然后我自己去看了官网觉得有点不好懂&#xff0c;于是去B站看来很多视频&#xff0c;然后看到了up主【程序员青戈】的视频&#xff0c;看了他讲的echarts图和富…
阅读更多...
社交媒体数据恢复：飞信

社交媒体数据恢复：飞信

请注意&#xff0c;本教程只适用于飞信&#xff0c;并且不涉及推荐任何数据恢复软件。 一、备份飞信聊天记录 在开始恢复飞信聊天记录之前&#xff0c;我们建议您先备份现有的聊天记录。这样&#xff0c;即使恢复过程中出现问题&#xff0c;您也可以通过备份文件找回重要的聊…
阅读更多...
搭建基于Django的博客系统增加广告轮播图（三）

搭建基于Django的博客系统增加广告轮播图（三）

上一篇&#xff1a;ChatGPT搭建博客Django的web网页添加用户系统&#xff08;二&#xff09; 下一篇&#xff1a;搭建基于Django的博客系统数据库迁移从Sqlite3到MySQL&#xff08;四&#xff09; 功能概述 增加轮播图显示广告信息。 需求详细描述 1. 增加轮播图显示广告信…
阅读更多...
python解决flask启动的同时启动定时任务

python解决flask启动的同时启动定时任务

业务场景描述&#xff1a;在常规的开发中&#xff0c;我们开发接口服务&#xff0c;一般会将数据放在数据库、文件等第三方文件&#xff0c;启动服务后&#xff0c;服务到后台数据库中加载数据&#xff0c;这样做的好处当然是开发会更加便利以及数据的可复用性较高&#xff0c;…
阅读更多...
一键实现文件夹批量高效重命名：轻松运用随机一个字母命名，让文件管理焕然一新！

一键实现文件夹批量高效重命名：轻松运用随机一个字母命名，让文件管理焕然一新！

在数字化时代&#xff0c;文件夹管理是我们日常生活和工作中不可或缺的一部分。然而&#xff0c;随着文件数量的不断增加&#xff0c;文件夹命名的繁琐和重复成为了一个让人头疼的问题。你是否曾因为手动一个个重命名文件夹而感到枯燥乏味&#xff1f;你是否曾渴望有一种方法能…
阅读更多...
arm cortex-m架构 SVC指令详解以及其在freertos的应用

arm cortex-m架构 SVC指令详解以及其在freertos的应用

1. 前置知识 本文基于arm cortex-m架构描述&#xff0c; 关于arm cortex-m的一些基础知识可以参考我另外几篇文章&#xff1a; arm cortex-m 架构简述arm异常处理分析c语言函数调用规范-基于arm 分析 2 SVC指令 2.1 SVC指令位域表示 bit15 - bit12&#xff1a;条件码&#…
阅读更多...
深入分析 Android BroadcastReceiver (一)

深入分析 Android BroadcastReceiver (一)

文章目录 深入分析 Android BroadcastReceiver (一)1. Android BroadcastReceiver 设计说明1.1 BroadcastReceiver 的主要用途 2. BroadcastReceiver 的工作机制2.1 注册 BroadcastReceiver2.1.1 静态注册2.1.2 动态注册 3. BroadcastReceiver 的生命周期4. 实现和使用 Broadca…
阅读更多...
Android下HWC以及drm_hwcomposer普法(上)

Android下HWC以及drm_hwcomposer普法(上)

Android下HWC以及drm_hwcomposer普法(上) 引言 按摩得全套&#xff0c;错了&#xff0c;做事情得全套&#xff0c;普法分析也是如此。drm_hwcomposer如果对Android图形栈有一定研究的童鞋们应该知道它是Android提供的一个的图形后端合成处理HAL模块的实现。但是在分析这个之前…
阅读更多...
yolov8使用：数据格式转换（目标检测、图像分类）多目标跟踪

yolov8使用：数据格式转换（目标检测、图像分类）多目标跟踪

安装 yolov8地址&#xff1a;https://github.com/ultralytics/ultralytics git clone https://github.com/ultralytics/ultralytics.git安装环境&#xff1a; pip install ultralytics -i https://pypi.tuna.tsinghua.edu.cn/simple目标检测 标注格式转换 若使用 labelimg…
阅读更多...
sql注入-布尔盲注

sql注入-布尔盲注

布尔盲注&#xff08;Boolean Blind SQL Injection&#xff09;是一种SQL注入攻击技术&#xff0c;用于在无法直接获得查询结果的情况下推断数据库信息&#xff1b;它通过发送不同的SQL查询来观察应用程序的响应&#xff0c;进而判断查询的真假&#xff0c;并逐步推断出有用的信…
阅读更多...
微服务学习Day9

微服务学习Day9

文章目录 分布式事务seata引入理论基础CAP定理BASE理论 初识Seata动手实践XA模式AT模式TCC模式SAGA模式 高可用 分布式事务seata 引入 理论基础 CAP定理 BASE理论 初识Seata 动手实践 XA模式 AT模式 TCC模式 Service Slf4j public class AccountTCCServiceImpl implements A…
阅读更多...
C语言 | Leetcode C语言题解之第126题单词接龙II

C语言 | Leetcode C语言题解之第126题单词接龙II

题目&#xff1a; 题解&#xff1a; char** list; int** back; int* backSize;// DFS uses backtrack information to construct results void dfs(char*** res, int* rSize, int** rCSizes, int* ans, int last, int retlevel) {int i ans[last];if (i 0) {res[*rSize] (c…
阅读更多...
DALL·E 2详解：人工智能如何将您的想象力变为现实！

DALL·E 2详解：人工智能如何将您的想象力变为现实！

引言 DALLE 2是一个基于人工智能的图像生成模型&#xff0c;它通过理解自然语言描述来生成匹配这些描述的图像。这一模型的核心在于其创新的两阶段工作流程&#xff0c;首先是将文本描述转换为图像表示&#xff0c;然后是基于这个表示生成具体的图像。 下面详细介绍DALL-E2的功…
阅读更多...
Vivado Design Suite一级物件

Vivado Design Suite一级物件

Vivado Design Suite一级物件 按设计过程导航内容 Xilinx文档围绕一组标准设计流程进行组织&#xff0c;以帮助您 查找当前开发任务的相关内容。本文件涵盖 以下设计过程&#xff1a; •硬件、IP和平台开发&#xff1a;为硬件创建PL IP块 平台&#xff0c;创建PL内核&#xff0…
阅读更多...
HTML的标签（标题、段落、文本、图片、列表）

HTML的标签（标题、段落、文本、图片、列表）

HTML的标签1 标题标签&#xff1a;段落标签&#xff1a;文本标签&#xff1a;图片标签:列表标签&#xff1a;有序列表&#xff1a;无序列表&#xff1a;定义列表&#xff1a;列表案例&#xff1a; 标题标签&#xff1a; 标签&#xff1a;h1~h6 注意&#xff1a;如果使用无效标…
阅读更多...
C语言怎样写数据⽂件，使之可以在不同字⼤⼩、 字节顺序或浮点格式的机器上读⼊？

C语言怎样写数据⽂件，使之可以在不同字⼤⼩、 字节顺序或浮点格式的机器上读⼊？

一、问题 怎样写数据⽂件&#xff0c;使之可以在不同字⼤⼩、字节顺序或浮点格式的机器上读⼊&#xff0c;也就是说怎样写⼀个可移植性好的数据⽂件&#xff1f; 二、解答 最好的移植⽅法是使⽤⽂本⽂件&#xff0c;它的每⼀字节放⼀个 ASCII 代码&#xff0c;代表⼀个字符。 …
阅读更多...
从JS角度直观理解递归的本质

从JS角度直观理解递归的本质

让我们写一个函数 pow(x, n)&#xff0c;它可以计算 x 的 n 次方。换句话说就是&#xff0c;x 乘以自身 n 次。 有两种实现方式。 迭代思路&#xff1a;使用 for 循环&#xff1a; function pow(x, n) {let result 1;// 在循环中&#xff0c;用 x 乘以 result n 次for (let i…
阅读更多...
短时间内如何顺利通过 Java 面试？

短时间内如何顺利通过 Java 面试？

今天我们来探讨一个重要的话题&#xff1a;短时间内如何顺利通过 Java 面试&#xff1f; 在此之前&#xff0c;我正在精心编写一套完全面向小白的 Java 自学教程&#xff0c;我相信这套教程会非常适合正在努力提升的你。教程里面涵盖了丰富全面的编程教学内容、详细生动的视频…
阅读更多...
2.8Flowmap的实现

2.8Flowmap的实现

一、Flowmap 是什么 半条命2中水的流动 求生之路2中的水的流动 这种方式原理简单&#xff0c;容易实现&#xff0c;运算量少&#xff0c;如今也还在使用 1.flowmap的实质 Flow map(流向图) &#xff0c;一张记录了2D向量信息的纹理&#xff0c;Flow map上的颜色(通常为RG通道…
阅读更多...
Python知识点14---被规定的资源

Python知识点14---被规定的资源

提前说一点&#xff1a;如果你是专注于Python开发&#xff0c;那么本系列知识点只是带你入个门再详细的开发点就要去看其他资料了&#xff0c;而如果你和作者一样只是操作其他技术的Python API那就足够了。 在Python中被规定的东西不止有常识中的那些关键字、构造器等编程语言…
阅读更多...
最新文章

Copyright @ 2022~2023