MySql 数据库操作实例

article/2025/10/8 15:20:08

MySql 数据库操作实例

  • 案例描述
  • 创建
  • 插入数据
  • 内外连接~问题
    • 问题1：查询周星星的成绩
    • 问题2：查询所有人的平均成绩以及其他信息
      • 1）查询所有人的平均成绩
      • 2）查询平均成绩最高的前三名
      • 3）查询平均成绩排名第三的学生信息
    • 问题3：查询所有人的成绩以及其他信息
  • 自连接问题
  • 嵌套问题
    • 问题1：查找同班同学
      • 未嵌套解法
        • 1、最简单解法
        • 2、自连接解法
      • 嵌套解法
    • 问题2：查找成绩信息
      • 内连接解法
      • 嵌套解法
  • 合并查询问题
    • 非合并查询解法
    • 合并查询解法

本篇博客主要是将前面的知识，比如 MySql 基本操作、操作1、操作2 等以实例来进行练习。

案例描述

分别创建班级、学生、课程、成绩四表，并随机插入数据，然后根据所提问题进行练习。
其中 班级表 中需包含 班级id 、班级名称 和 班级描述 三种信息；
学生表 中需包含 学生id 、学号、姓名、邮箱、对应的班级id 等信息；
课程表 中应包含 课程id 、课程名称 两种信息；
分数表 中则需包含 id 、分数、学生id 、班级id 等信息。

创建

上述四表的创建代码如下

-- 创建班级表
CREATE TABLE if not exists classes (
id INT PRIMARY KEY auto_increment comment '班级id',
name VARCHAR(20) comment '班级名称',
`desc` VARCHAR(100) comment '班级描述'
);-- 创建学生表
CREATE TABLE if not exists student (
id INT PRIMARY KEY auto_increment comment '学生id',
sn INT UNIQUE comment '学生学号',
name VARCHAR(20) DEFAULT 'unkown' comment '学生名字', -- 默认为unkown
qq_mail VARCHAR(20) comment '学生邮箱',
classes_id int comment '学生班级id',
FOREIGN KEY (classes_id) REFERENCES classes(id) -- 外键约束， class_id 受 classes表 中 id 数据的约束
);-- 创建课程表
CREATE TABLE if not exists course (
id INT PRIMARY KEY auto_increment comment '课程id',
name VARCHAR(20) comment '课程名称'
);-- 创建分数表
CREATE TABLE score (
id INT PRIMARY KEY auto_increment comment '分数id',
score DECIMAL(3, 1) comment '分数',
student_id int comment '学生id',
course_id int comment '班级id',
FOREIGN KEY (student_id) REFERENCES student(id), -- 外键约束，student_id 受 student表 中 id 数据的约束
FOREIGN KEY (course_id) REFERENCES course(id) -- 外键约束， course_id 受 course表 中 id 数据的约束
);

创建结果如下
在这里插入图片描述

插入数据

-- 为班级表添加数据，3班级
insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');-- 为学生表插入数据，8个学生
insert into student(sn, name, qq_mail, classes_id) values
('09982','龙大','longda@qq.com',1),
('00835','龙二',null,1),
('00391','龙三',null,1),
('00031','龙四','longsi@qq.com',1),
('00054','龙五',null,1),
('51234','周星星','xingxing@qq.com',2),
('83223','彪仔',null,2),
('09527','关天下','tianxia@qq.com',2);-- 为课程表插入数据
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');-- 为分数表插入7人数据
insert into score(score, student_id, course_id) values
-- 龙大
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 龙二
(60, 2, 1),(59.5, 2, 5),
-- 龙三
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 龙四
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 龙五
(81, 5, 1),(37, 5, 5),
-- 周星星
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- 彪仔
(80, 7, 2),(92, 7, 6);

结果如下
在这里插入图片描述

内外连接~问题

问题1：查询周星星的成绩

该问题需要 学生表 与 成绩表 进行联查，用到了 inner join + on，还有条件过滤 where。
实现代码如下

select  score.id, score.score, student.name from score 
join student on student.id = score.student_id where student.name = '周星星';

注：from 和 join 后表的名称可以互换位置，不影响最终结果

结果如下
在这里插入图片描述

问题2：查询所有人的平均成绩以及其他信息

1）查询所有人的平均成绩

该问题主要是查询所有人的平均成绩，并输出每个人的学号、姓名信息。
即依然是需要对 成绩表 与 学生表 进行联查，用到了内连接 inner join + on ,还有分组查询 group by。
实现代码如下

select student.sn, student.name, avg(score.score) from score 
join student on student.id = score.student_id group by student.id;

上述代码相当于

select student.sn, student.name, avg(score.score) from score 
join student on student.id = score.student_id group by score.student_id;

结果如下
在这里插入图片描述

2）查询平均成绩最高的前三名

即以平均成绩作降序排序，然后输出前三名即可，代码如下

select student.sn, student.name, avg(score.score) from student  
join score on student.id=score.student_id group by student.id 
order by avg(score.score) desc limit 3;

其中limit是用于分页查询，具体作用可见博客 MySql 表操作指令小全与案例中查询部分的内容。运行结果如下
在这里插入图片描述

3）查询平均成绩排名第三的学生信息

还是以平均成绩作降序排序，然后输出第三名的信息即可，此处就只输出学号、姓名、平均成绩这三种信息。代码如下

select student.sn, student.name, avg(score.score) from student 
join score on score.student_id=student.id group by student.id 
order by avg(score.score) desc limit 2,1;

运行结果如下
在这里插入图片描述

问题3：查询所有人的成绩以及其他信息

此问题相较于问题2，其所查询的成绩为每个人每门课的成绩，即其他信息中还需包括每个人对应的课程名称。
1、该问题的求解应用到了 内连接 inner join + on ，排序查询 order by。
代码如下

select student.sn, student.name, course.name, score.score from student 
join score on score.student_id = student.id
join course on score.course_id = course.id order by student.id;

结果如下
在这里插入图片描述
2、利用外连接中 左连接 left join + on 进行求解
代码为

select student.sn, student.name, course.name, score.score from student 
left join score on score.student_id = student.id
left join course on score.course_id = course.id order by student.id;

结果如下
在这里插入图片描述
因为上述代码是以 student 表 为基准进行处理，因此结果中出现了 关天下 这一学生的信息，但由于他没有上课与成绩信息，所以显示的为 NULL。

3、利用外连接中 右连接 right join + on 进行求解
代码如下

select student.sn, student.name, course.name, score.score from student 
right join score on score.student_id = student.id
right join course on score.course_id = course.id order by student.id;

该程序是以 score 和 course 表为基准，所以他的结果为
在这里插入图片描述
即与内连接的结果一致！

自连接问题

问题描述：显示所有“计算机原理”成绩比“Java”成绩高的学生信息，包含学生名称与两科成绩！

select stu.name, computer.score, java.score from student stu 
join score computer on stu.id = computer.student_id
join score java on stu.id = java.student_id
join course cou1 on cou1.id = computer.course_id
join course cou2 on cou2.id = java.course_id
where cou1.name = '计算机原理' and cou2.name = 'Java'
and computer.score > java.score;

注：其中运用了 ’起别名‘ 的技巧

结果如下
在这里插入图片描述

嵌套问题

问题1：查找同班同学

问题描述：在 student 表中查询与“龙五” 同学的同班同学：

未嵌套解法

1、最简单解法

1） 先查询出 龙五 同学的班级

select classes_id from student where name = '龙五';

2） 通过班级 id 查询出 龙五 同学的同班同学

select name from student where classes_id = 1;

结果如下
在这里插入图片描述

2、自连接解法

程序如下

select stu1.name from student stu1
join student stu2 on stu2.name = '龙五'
where stu2.classes_id = stu1.classes_id;

先找出 stu2 中 龙五 对应的班级号，然后再在 stu1 中找出相同班级号下的学生姓名。

结果为
在这里插入图片描述

嵌套解法

程序如下：

select name from student where classes_id = (select classes_id from student where name = '龙五');
或
select name from student where classes_id in (select classes_id from student where name = '龙五');

程序解释：输出 name 的 sql 的查询条件是另一条输出 classes_id 的 sql 的执行结果。

结果如下：
在这里插入图片描述

问题2：查找成绩信息

问题描述：查询所有比“中文系2019级3班”平均分高的成绩信息。
该问题有点复杂，首先需要从 classes 表中找出对应 id ，然后以此 id 找出 student 表中在此班的 学生id ，然后以这些学生id 去求解 score 表中这些学生的平均成绩，再以这 平均成绩 作为判决条件，求出 score 表中所有大于该平均分的成绩信息。

内连接解法

依据上面解释进行程序编写，如下

select * from score where score > (
select avg(score.score) from score
join student on student.id = score.student_id
join classes on classes.id = student.classes_id
where classes.name = '中文系2019级3班');或select * from score ,(
select avg(score.score) score  -- 起别名
from score
join student on student.id = score.student_id
join classes on classes.id = student.classes_id
where classes.name = '中文系2019级3班'
)tmp
where score.score > tmp.score;

上述程序结合 嵌套 与 内连接 进行实现的！

注意：由于avg是个内聚函数，除了在 分组查询 的时候在条件中能用否则不能在条件中使用，也就是说这个函数不适合用内连接，即下面这个语句就是错的

select * from score sco1 
join score sco2 on sco1.score > avg(sco2.score);

结果如图
在这里插入图片描述

嵌套解法

select * from score where score >  -- 求取大于平均成绩的成绩信息
(select avg(score) from score  -- 求取平均成绩
where student_id in  (select id from student  -- 求取 所求班级id 对应的 学生id
where classes_id in (select id from classes  -- 求取name = '中文系2019级3班'对应的 班级id
where name = '中文系2019级3班')
));

结果如下
在这里插入图片描述

合并查询问题

问题描述：查询id小于3，或者名字为“英文”的课程

非合并查询解法

直接利用 or 进行问题求解。程序如下

select * from course where id < 3 or name = '英文';

结果如下
在这里插入图片描述

合并查询解法

合并查询所用关键字为 union ，具体程序如下

select * from course where id < 3
union 
select * from course where name = '英文';

结果如下
在这里插入图片描述
注意：相比之下，or会忽略索引，而索引可有效提高系统查询效率！

以上为本篇博客主要内容，内容有点多，静下心，最好敲一遍，理解每个命令或每种用法的作用~

侵权删~


http://chatgpt.dhexx.cn/article/Y828ghia.shtml

相关文章

数据库五个经典实例

数据库五个经典实例

创建数据库链接&#xff0c;需要用到connection对象&#xff0c;recordset对象。 对数据库进行操作&#xff0c;需要用到command对象&#xff0c;parameter对象。这两个对象成对出现。 connection对象&#xff1a;创建数据库链接。在对数据库进行操作的前提步骤。 recordset…
阅读更多...
Oracle 数据库实例介绍

Oracle 数据库实例介绍

文章目录 数据库实例介绍实例结构实例配置读写实例与只读实例实例生命周期实例标识Oracle 根目录Oracle 主目录Oracle SID 实例启动与关闭启动实例与数据库管理员登录启动实例加载数据库打开数据库只读模式数据库文件检查 关闭数据库与实例关闭模式关闭数据库正常关闭异常关闭 …
阅读更多...
Python：二叉树遍历

Python：二叉树遍历

二叉树遍历共有四种方法&#xff0c;分别是前序遍历、中序遍历、后序遍历和层次遍历。 前序遍历&#xff1a; 父节点——左孩子——右孩子 中序遍历&#xff1a;左孩子——父节点——右孩子 后序遍历&#xff1a;左孩子——右孩子——父节点 层次遍历&#xff1a;利用队列解…
阅读更多...
【算法】二叉树遍历的几种常见方法

【算法】二叉树遍历的几种常见方法

二叉树遍历的几种常见方法 一. 二叉树分类&#xff1a; 完全二叉树满二叉树扩充二叉树平衡二叉树 二. 二叉树的四种遍历方式&#xff1a; 前序遍历&#xff08;先根&#xff0c;再左&#xff0c;最后右&#xff09;中序遍历&#xff08;先左&#xff0c;再根&#xff0c;最…
阅读更多...
二叉树遍历的非递归算法

二叉树遍历的非递归算法

非递归的算法主要采用的是循环出栈入栈来实现对二叉树的遍历&#xff0c;下面是过程分析 以下列二叉树为例&#xff1a;&#xff08;图片来自懒猫老师《数据结构》课程相关内容&#xff09; 1.前序遍历 前序遍历的顺序为&#xff1a;根结点->左子树->右子树 基本过程&a…
阅读更多...
二叉树的中序遍历算法

二叉树的中序遍历算法

一&#xff0c;简介 二叉树的中序遍历在计算机行业有着重要的作用&#xff0c;其中一个应用就是判断一棵二叉树是否二叉排序树。 下面介绍递归和非递归两种方式实现中序遍历。 二&#xff0c;递归实现 递归实现非常简单&#xff0c;左根右依次进行即可。 void mid_scan2(n…
阅读更多...
JavaScript算法 — 二叉树遍历

JavaScript算法 — 二叉树遍历

目录 1、构造二叉树2、递归遍历3、非递归遍历3.1 先序3.2 中序3.3 后序 1、构造二叉树 树节点&#xff1a; // 二叉树节点的构造函数 function TreeNode(val, left, right) {this.val (valundefined ? 0 : val)this.left (leftundefined ? null : left)this.right (righ…
阅读更多...
二叉树遍历算法之一：前序遍历

二叉树遍历算法之一：前序遍历

递归实现前序遍历 二叉树的前序遍历是指从根节点出发&#xff0c;按照先根节点&#xff0c;再左子树&#xff0c;后右子树的方法遍历二叉树中的所有节点&#xff0c;使得每个节点都被访问一次。 当调用遍历算法的时候前序遍历的具体过程如下&#xff1a; 首先访问根节点&…
阅读更多...
二叉树遍历小结

二叉树遍历小结

前言 二叉树是相当重要的数据结构&#xff0c;目前我还只会玩玩它的遍历&#xff08;年轻不懂事没好好学&#xff0c;不然早就达到人生巅峰了&#xff09;&#xff0c;LeetCode上二叉树的简单题&#xff0c;大部分通过遍历加一点小逻辑即可解决&#xff0c;所以总结一下几种遍…
阅读更多...
二叉树遍历之层次遍历算法入门详解

二叉树遍历之层次遍历算法入门详解

一、引言 二叉树的遍历常见的方法有先序遍历、中序遍历、后序遍历和层次遍历等&#xff0c;本文给出了C语言版本的层次遍历二叉树的算法。 层次遍历的原理很简单&#xff0c;总结为一句话就是“从上到下&#xff0c;从左到右”&#xff0c;就是从树根开始逐层访问二叉树的结点&…
阅读更多...
二叉树的四种遍历算法

二叉树的四种遍历算法

二叉树作为一种重要的数据结构&#xff0c;它的很多算法的思想在很多地方都用到了&#xff0c;比如STL算法模板&#xff0c;里面的优先队列、集合等等都用到了二叉树里面的思想&#xff0c;先从二叉树的遍历开始&#xff1a; 看二叉树长什么样子&#xff1a; 我们可以看到这颗…
阅读更多...
实现二叉树各种遍历算法

实现二叉树各种遍历算法

目录 前言一、题目1.二叉树的各种遍历过程及遍历算法设计。2.实现二叉树各种遍历算法 总结 前言 提示&#xff1a;记得关注我哦&#xff01;&#xff01;&#xff01; 一、题目 1.二叉树的各种遍历过程及遍历算法设计。 &#xff08;1&#xff09; 先序遍历二叉树&#xff1…
阅读更多...
算法分析之二叉树遍历

算法分析之二叉树遍历

算法相关数据结构总结&#xff1a; 序号数据结构文章1动态规划动态规划之背包问题——01背包 动态规划之背包问题——完全背包 动态规划之打家劫舍系列问题 动态规划之股票买卖系列问题 动态规划之子序列问题 算法&#xff08;Java&#xff09;——动态规划2数组算法分析之数…
阅读更多...
二叉树遍历算法总结

二叉树遍历算法总结

A. 二叉树的遍历 1.前序遍历二叉树&#xff1a; (1)若二叉树为空&#xff0c;则为空操作&#xff0c;返回空。 (2)访问根结点。 (3)前序遍历左子树。 (4)前序遍历右子树。 a.二叉树前序遍历的递归算法&#xff1a; void PreOrderTraverse(BiTree BT)…
阅读更多...
二叉树的遍历算法

二叉树的遍历算法

遍历是对树的一种最基本的运算&#xff0c;所谓遍历二叉树&#xff0c;就是按一定的规则和顺序走遍二叉树的所有节点&#xff0c;使每一个节点都被访问一次&#xff0c;而且只被访问一次。由于二叉树是非线性结构&#xff0c;因此&#xff0c;树的遍历实质上是将二叉树的各个节…
阅读更多...
【算法】二叉树遍历算法总结：前序中序后序遍历

【算法】二叉树遍历算法总结：前序中序后序遍历

前言 二叉树遍历是非常经典的算法题&#xff0c;也是二叉树的一道基础算法题。 但是在平常的笔试面试中&#xff0c;其出现的频率其实并不是特别的高&#xff0c;我推测是这种题目相对来说比较基础&#xff0c;算是一个基础知识点。 比如剑指offer中出现的后序遍历题目&…
阅读更多...
二叉树遍历算法的应用

二叉树遍历算法的应用

目录 一、二叉树遍历算法的应用——二叉树的创建 二、二叉树遍历算法的应用——复制二叉树 三、二叉树遍历算法的应用——计算二叉树的深度 四、二叉树遍历算法的应用——计算二叉树节点总数 五、二叉树遍历算法的应用——计算二叉树叶子节点数 一、二叉树遍历算法的应用—…
阅读更多...
一文弄懂二叉树的三种遍历方式

一文弄懂二叉树的三种遍历方式

关注公众号【高性能架构探索】&#xff0c;后台回复【pdf】&#xff0c;免费获取计算机必备经典书籍 俗话说&#xff1a;学如逆水行舟,不进则退;心似平原走马,易放难收。这句话对程序员而言&#xff0c;体会更深。这行已经越来越卷了&#xff0c;时刻准备着&#xff0c;&#x…
阅读更多...
二叉树遍历算法

二叉树遍历算法

目录 先序遍历 中序遍历 后序遍历 层序遍历 938. 二叉搜索树的范围和 110. 平衡二叉树 114. 二叉树展开为链表 117. 填充每个节点的下一个右侧节点指针 II 116. 填充每个节点的下一个右侧节点指针 1&#xff0c;三种遍历都是先把二叉树的最左结点循环入栈(DFS迭代)&am…
阅读更多...
二叉树的四种遍历算法（结构体数组）

二叉树的四种遍历算法（结构体数组）

一、二叉树的定义 以字符串的形式定义一棵二叉树的先序序列&#xff0c;若字符是‘#’&#xff0c;表示该二叉树是空树&#xff0c;否则该字符是相应结点的数据元素。 例&#xff1a;ABDG##HI####CE#J##F## 对应的二叉树&#xff1a; 思路讲解&#xff1a; 想要遍历二叉树&am…
阅读更多...
推荐文章

Copyright @ 2022~2023