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。

相关文章

MySQL的ALTER TABLE命令的使用解读

MySQL的ALTER TABLE命令的使用解读

《MySQL的ALTERTABLE命令的使用解读》：本文主要介绍MySQL的ALTERTABLE命令的使用,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、查看所建表的编China编程码格式2、修改表的编码格式3、修改列队数据类型4、添加列5、修改列的位置5.1、把列
阅读更多...
Mybatis嵌套子查询动态SQL编写实践

Mybatis嵌套子查询动态SQL编写实践

《Mybatis嵌套子查询动态SQL编写实践》：本文主要介绍Mybatis嵌套子查询动态SQL编写方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言一、实体类1、主类2、子类二、Mapper三、XML四、详解总结前言MyBATis的xml文件编写动态SQL
阅读更多...
解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

《解决mysql插入数据锁等待超时报错:Lockwaittimeoutexceeded;tryrestartingtransaction》：本文主要介绍解决mysql插入数据锁等待超时报... 目录报错信息解决办法1、数据库中执行如下sql2、再到 INNODB_TRX 事务表中查看总结报错信息Lock
阅读更多...
Apache 高级配置实战之从连接保持到日志分析的完整指南

Apache 高级配置实战之从连接保持到日志分析的完整指南

《Apache高级配置实战之从连接保持到日志分析的完整指南》本文带你从连接保持优化开始,一路走到访问控制和日志管理,最后用AWStats来分析网站数据,对Apache配置日志分析相关知识感兴趣的朋友... 目录Apache 高级配置实战：从连接保持到日志分析的完整指南前言 一、Apache 连接保持 - 性
阅读更多...
MySQL启动报错：InnoDB表空间丢失问题及解决方法

MySQL启动报错：InnoDB表空间丢失问题及解决方法

《MySQL启动报错：InnoDB表空间丢失问题及解决方法》在启动MySQL时,遇到了InnoDB:Tablespace5975wasnotfound,该错误表明MySQL在启动过程中无法找到指定的s... 目录mysql 启动报错：InnoDB 表空间丢失问题及解决方法错误分析解决方案1. 启用 inno
阅读更多...
MySQL 安装配置超完整教程

MySQL 安装配置超完整教程

《MySQL安装配置超完整教程》MySQL是一款广泛使用的开源关系型数据库管理系统（RDBMS）,由瑞典MySQLAB公司开发,目前属于Oracle公司旗下产品,：本文主要介绍MySQL安装配置... 目录一、mysql 简介二、下载 MySQL三、安装 MySQL四、配置环境变量五、配置 MySQL5.1
阅读更多...
MySQL 添加索引5种方式示例详解(实用sql代码)

MySQL 添加索引5种方式示例详解(实用sql代码)

《MySQL添加索引5种方式示例详解(实用sql代码)》在MySQL数据库中添加索引可以帮助提高查询性能,尤其是在数据量大的表中,下面给大家分享MySQL添加索引5种方式示例详解(实用sql代码),... 在mysql数据库中添加索引可以帮助提高查询性能，尤其是在数据量大的表中。索引可以在创建表时定义，也可
阅读更多...
Mybatis Plus JSqlParser解析sql语句及JSqlParser安装步骤

Mybatis Plus JSqlParser解析sql语句及JSqlParser安装步骤

《MybatisPlusJSqlParser解析sql语句及JSqlParser安装步骤》JSqlParser是一个用于解析SQL语句的Java库,它可以将SQL语句解析为一个Java对象树,允许... 目录【一】jsqlParser 是什么【二】JSqlParser 的安装步骤【三】使用场景【1】sql语
阅读更多...
MySQL 存储引擎 MyISAM详解(最新推荐)

MySQL 存储引擎 MyISAM详解(最新推荐)

《MySQL存储引擎MyISAM详解(最新推荐)》使用MyISAM存储引擎的表占用空间很小,但是由于使用表级锁定,所以限制了读/写操作的性能,通常用于中小型的Web应用和数据仓库配置中的只读或主要... 目录mysql 5.5 之前默认的存储引擎️‍一、MyISAM 存储引擎的特性️‍二、MyISAM 的主
阅读更多...
Linux lvm实例之如何创建一个专用于MySQL数据存储的LVM卷组

Linux lvm实例之如何创建一个专用于MySQL数据存储的LVM卷组

《Linuxlvm实例之如何创建一个专用于MySQL数据存储的LVM卷组》：本文主要介绍使用Linux创建一个专用于MySQL数据存储的LVM卷组的实例,具有很好的参考价值,希望对大家有所帮助,... 目录在Centos 7上创建卷China编程组并配置mysql数据目录1. 检查现有磁盘2. 创建物理卷3. 创
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2