MySQL之子查询、连接查询（内外）以及分页查询（实操）

2024-01-09 08:36
文章标签 mysql 查询 连接 实操 分页 内外 database 之子

本文主要是介绍MySQL之子查询、连接查询（内外）以及分页查询（实操），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

  • 前言
  • 一、SQL脚本
  • 二、实操以及实现思路

前言

续上篇博主MySQL之视图&索引&执行计划这篇给大家讲解MySQL之子查询、连接查询(内&外)以及分页查询

一、SQL脚本

/*Navicat Premium Data TransferSource Server         : localhostSource Server Type    : MySQLSource Server Version : 80018Source Host           : localhost:3306Source Schema         : mybatis_ssmTarget Server Type    : MySQLTarget Server Version : 80018File Encoding         : 65001Date: 04/07/2023 23:53:33
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for t_mysql_course
-- ----------------------------
DROP TABLE IF EXISTS `t_mysql_course`;
CREATE TABLE `t_mysql_course`  (`cid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程编号',`cname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程名称',`tid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师编号',PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程信息表' ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of t_mysql_course
-- ----------------------------
INSERT INTO `t_mysql_course` VALUES ('01', '语文', '02');
INSERT INTO `t_mysql_course` VALUES ('02', '数学', '01');
INSERT INTO `t_mysql_course` VALUES ('03', '英语', '03');-- ----------------------------
-- Table structure for t_mysql_score
-- ----------------------------
DROP TABLE IF EXISTS `t_mysql_score`;
CREATE TABLE `t_mysql_score`  (`sid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生编号，外键',`cid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程编号，外键',`score` float NULL DEFAULT 0 COMMENT '成绩',INDEX `sid`(`sid`) USING BTREE,INDEX `cid`(`cid`) USING BTREE,CONSTRAINT `t_mysql_score_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `t_mysql_student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT `t_mysql_score_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `t_mysql_course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '成绩信息表' ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of t_mysql_score
-- ----------------------------
INSERT INTO `t_mysql_score` VALUES ('01', '01', 80);
INSERT INTO `t_mysql_score` VALUES ('01', '02', 90);
INSERT INTO `t_mysql_score` VALUES ('01', '03', 99);
INSERT INTO `t_mysql_score` VALUES ('02', '01', 70);
INSERT INTO `t_mysql_score` VALUES ('02', '02', 60);
INSERT INTO `t_mysql_score` VALUES ('02', '03', 80);
INSERT INTO `t_mysql_score` VALUES ('03', '01', 80);
INSERT INTO `t_mysql_score` VALUES ('03', '02', 80);
INSERT INTO `t_mysql_score` VALUES ('03', '03', 80);
INSERT INTO `t_mysql_score` VALUES ('04', '01', 50);
INSERT INTO `t_mysql_score` VALUES ('04', '02', 30);
INSERT INTO `t_mysql_score` VALUES ('04', '03', 20);
INSERT INTO `t_mysql_score` VALUES ('05', '01', 76);
INSERT INTO `t_mysql_score` VALUES ('05', '02', 87);
INSERT INTO `t_mysql_score` VALUES ('06', '01', 31);
INSERT INTO `t_mysql_score` VALUES ('06', '03', 34);
INSERT INTO `t_mysql_score` VALUES ('07', '02', 89);
INSERT INTO `t_mysql_score` VALUES ('07', '03', 98);-- ----------------------------
-- Table structure for t_mysql_student
-- ----------------------------
DROP TABLE IF EXISTS `t_mysql_student`;
CREATE TABLE `t_mysql_student`  (`sid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生编号',`sname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生名称',`sage` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生年龄',`ssex` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生性别',PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '学生信息表' ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of t_mysql_student
-- ----------------------------
INSERT INTO `t_mysql_student` VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `t_mysql_student` VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `t_mysql_student` VALUES ('03', '孙风', '1990-12-20', '男');
INSERT INTO `t_mysql_student` VALUES ('04', '李云', '1990-12-06', '男');
INSERT INTO `t_mysql_student` VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `t_mysql_student` VALUES ('06', '吴兰', '1992-01-01', '女');
INSERT INTO `t_mysql_student` VALUES ('07', '郑竹', '1989-01-01', '女');
INSERT INTO `t_mysql_student` VALUES ('09', '张三', '2017-12-20', '女');
INSERT INTO `t_mysql_student` VALUES ('10', '李四', '2017-12-25', '女');
INSERT INTO `t_mysql_student` VALUES ('11', '李四', '2012-06-06', '女');
INSERT INTO `t_mysql_student` VALUES ('12', '赵六', '2013-06-13', '女');
INSERT INTO `t_mysql_student` VALUES ('13', '孙七', '2014-06-01', '女');-- ----------------------------
-- Table structure for t_mysql_teacher
-- ----------------------------
DROP TABLE IF EXISTS `t_mysql_teacher`;
CREATE TABLE `t_mysql_teacher`  (`tid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师编号',`tname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师名称',PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '教师信息表' ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of t_mysql_teacher
-- ----------------------------
INSERT INTO `t_mysql_teacher` VALUES ('01', '张三');
INSERT INTO `t_mysql_teacher` VALUES ('02', '李四');
INSERT INTO `t_mysql_teacher` VALUES ('03', '王五');SET FOREIGN_KEY_CHECKS = 1;

二、实操以及实现思路

  • 09）查询学过「张三」老师授课的同学的信息
SELECTs.*,c.cname,t.tname,sc.score 
FROMt_mysql_teacher t,t_mysql_course c,t_mysql_student s,t_mysql_score sc 
WHEREt.tid = c.tid AND c.cid = sc.cid AND sc.sid = s.sid AND t.tname = '张三'

在这里插入图片描述

  • 10）查询没有学全所有课程的同学的信息
    分析：没有学全，学全了有多少门，统计一共有多少门学科
    统计每一个学生学了多少门
SELECTs.sid,s.sname,count( sc.score ) n 
FROMt_mysql_student sLEFT JOIN t_mysql_score sc ON s.sid = sc.sid 
GROUP BYs.sid,s.sname 
HAVINGn <(SELECTcount( 1 ) FROMt_mysql_course)

在这里插入图片描述

  • 11）查询没学过"张三"老师讲授的任一门课程的学生姓名
    分析：子查询
    张三教了哪些课程
    找学过张三的课程有哪些人
SELECTs.sid,s.sname 
FROMt_mysql_score sc,t_mysql_student s 
WHEREs.sid = sc.sid AND sc.cid NOT IN ( SELECT cid FROM t_mysql_course c, t_mysql_teacher t WHERE c.tid = t.tid AND t.tname = '张三' ) 
GROUP BYs.sid,s.sname

在这里插入图片描述

  • 12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
    分析：聚合函数 内连接 要先筛选再分组
SELECTs.sid,s.sname,avg( sc.score ) 
FROMt_mysql_student s,t_mysql_score sc 
WHEREs.sid = sc.sid AND sc.score < 60 
GROUP BYs.sid,s.sname

在这里插入图片描述

  • 13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECTs.*,sc.score 
FROMt_mysql_student s,t_mysql_score sc 
WHEREs.sid = sc.sid AND sc.cid = '01' AND sc.score < 60 
ORDER BYsc.score DESC

在这里插入图片描述

  • 14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    分析：聚合函数 GROUP BY
    从高到低 ORDER BY DESC
    所有学生的所有课程的成绩
    姓名 语文 数学 英语 平均成绩
SELECTs.sid,s.sname,sum((case when sc.cid='01' then sc.score end))语文,sum((case when sc.cid='02' then sc.score end))数学,sum((case when sc.cid='03' then sc.score END))英语,round(avg(sc.score),2) 平均分FROMt_mysql_score scRIGHT JOIN t_mysql_student s ON sc.sid = s.sid 
GROUP BYs.sid,s.sname

在这里插入图片描述

	SELECTs.sid,s.sname,sum(if(sc.cid='01',sc.score,0))语文,sum(if(sc.cid='02',sc.score,0))数学,sum(if(sc.cid='03',sc.score,0))英语,round(avg(sc.score),2) 平均分FROMt_mysql_score scRIGHT JOIN t_mysql_student s ON sc.sid = s.sid 
GROUP BYs.sid,s.sname

在这里插入图片描述

  • 15）查询各科成绩最高分、最低分和平均分：
    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
    分析：聚合函数 GROUP BY
    大量使用函数
SELECTc.cid,c.cname,count(sc.score) 人数,max(sc.score) 最高分,min(sc.score) 最低分,round(avg(sc.score),2) 平均分,concat(round(sum(if(sc.score>=60,1,0))/(select count(1) from t_mysql_student)*100,2),'%') 及格率,concat(round(sum(if(sc.score>=70 and sc.score<=80,1,0))/(select count(1) from t_mysql_student)*100,2),'%') 中等率,concat(round(sum(if(sc.score>80 and sc.score<=90,1,0))/(select count(1) from t_mysql_student)*100,2),'%') 优良率,concat(round(sum(if(sc.score>=90,1,0))/(select count(1) from t_mysql_student)*100,2),'%') 优秀率
FROMt_mysql_score scLEFT JOIN t_mysql_course c ON sc.cid = c.cid
group by
c.cid,c.cname

在这里插入图片描述

这篇关于MySQL之子查询、连接查询（内外）以及分页查询（实操）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/586523。

相关文章

SQL中redo log 刷⼊磁盘的常见方法

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例（伪代码）在数据库系统中，r
阅读更多...
mysql中的group by高级用法

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使
阅读更多...
Mysql用户授权(GRANT)语法及示例解读

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》：本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权（GRANT）语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT
阅读更多...
Mysql如何解决死锁问题

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》：本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1
阅读更多...
SQL BETWEEN 的常见用法小结

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中，BETWEEN是一个操作符，用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用
阅读更多...
MySQL索引的优化之LIKE模糊查询功能实现

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》：本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分
阅读更多...
MySql match against工具详细用法

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引（Full-Textindex）的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七
阅读更多...
数据库面试必备之MySQL中的乐观锁与悲观锁

数据库面试必备之MySQL中的乐观锁与悲观锁

《数据库面试必备之MySQL中的乐观锁与悲观锁》：本文主要介绍数据库面试必备之MySQL中乐观锁与悲观锁的相关资料,乐观锁适用于读多写少的场景,通过版本号检查避免冲突,而悲观锁适用于写多读少且对数... 目录一、引言二、乐观锁（一）原理（二）应用场景（三）示例代码三、悲观锁（一）原理（二）应用场景（三）示例
阅读更多...
SQL表间关联查询实例详解

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括：左连接（leftjoin）、右连接（rightjoin）、全连接（fulljoin）、内连接（innerjoin）、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表
阅读更多...
SQL server配置管理器找不到如何打开它

SQL server配置管理器找不到如何打开它

《SQLserver配置管理器找不到如何打开它》最近遇到了SQLserver配置管理器打不开的问题,尝试在开始菜单栏搜SQLServerManager无果,于是将自己找到的方法总结分享给大家,对SQ... 目录方法一：桌面图标进入方法二：运行窗口进入方法三：查找文件路径方法四：检查 SQL Server 安
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2