MySQL之视图外连接、内连接和子查询的使用

目录

一、视图

1.1 含义

1.2 操作

1.3 SQL数据

二、连接查询案例

（1）查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

（2）查询同时存在" 01 "课程和" 02 "课程的情况

（3）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

（4）查询不存在" 01 "课程但存在" 02 "课程的情况

（5）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

（6）查询在t_mysql_score表存在成绩的学生信息

（7）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

三、思维导图

一、视图

1.1 含义

虚拟表，和普通表一样使用

1.2 操作

  • 创建视图

create view 视图名 as 

  • 修改视图

方式一：

create or replace view 视图名 as 

【查看视图相关字段】

方式二：

alter view 视图名 as   

【查看的SQL语句】

  • 查看视图

方式一：

desc 视图名【看视图字段】

方式二：

show create view 视图名

  • 删除视图

drop view 视图名

1.3 SQL数据

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80018
 Source Host           : localhost:3306
 Source Schema         : mybatis_ssm

 Target Server Type    : MySQL
 Target Server Version : 80018
 File Encoding         : 65001

 Date: 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;
 

二、连接查询案例

（1）查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

        方式一：

SELECT
    * 
FROM

        t_mysql_student s,
        ( SELECT * FROM t_mysql_score    WHERE cid = '01' ) t1,
        ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 

WHERE
        s.sid = t1.sid 
        AND t1.sid = t2.sid 
        AND t1.score > t2.score

        方式二：

SELECT
    s.*,
    (case when t1.cid = '01' then t1.score end ) 语文,
    (case when t2.cid = '02' then t2.score end ) 数学

FROM
        t_mysql_student s,
        ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
        ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 
WHERE
        s.sid = t1.sid 
        AND t1.sid = t2.sid 
        AND t1.score > t2.score

（2）查询同时存在" 01 "课程和" 02 "课程的情况

SELECT
    s.*,
    (case when t1.cid = '01' then t1.score end ) 语文,
    (case when t2.cid = '02' then t2.score end ) 数学

FROM
        t_mysql_student s,
        ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
        ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 
WHERE
        s.sid = t1.sid 
        AND t1.sid = t2.sid

（3）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

SELECT s.*,
    (case when t1.cid = '01' then t1.score end ) 语文,
    (case when t2.cid = '02' then t2.score end ) 数学
FROM
        t_mysql_student s INNER JOIN
        ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1
        on s.sid = t1.sid left JOIN
        ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 
        on t1.sid =t2.sid

（4）查询不存在" 01 "课程但存在" 02 "课程的情况

SELECT
     s.*,
    (case when sc.cid = '01' then sc.score end ) 语文,
    (case when sc.cid = '02' then sc.score end ) 数学
FROM
    t_mysql_student s,
    t_mysql_score sc 
WHERE
    s.sid = sc.sid and
    s.sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' ) 
AND sc.cid='02'

（5）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT
    s.sid,
    s.sname,
    ROUND(AVG(sc.score),2) n
FROM
    t_mysql_student s LEFT JOIN
    t_mysql_score sc on s.sid =sc.sid
    GROUP BY s.sid,s.sname
    HAVING n>= 60

（6）查询在t_mysql_score表存在成绩的学生信息

  SELECT
    s.sid,
    s.sname
FROM
    t_mysql_student s INNER JOIN
    t_mysql_score sc on s.sid =sc.sid
    GROUP BY s.sid,s.sname

（7）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT
    s.sid,
    s.sname,
    COUNT(sc.score) 选课总数,
    SUM(sc.score) 总成绩
FROM
    t_mysql_student s LEFT JOIN
    t_mysql_score sc on s.sid =sc.sid
    GROUP BY s.sid,s.sname

三、思维导图

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

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

docker安裝gocd-server，并配置gitlab授权登录

docker安裝gocd-server，并配置gitlab授权登录

gocd的地址：Installing GoCD server on Windows | GoCD User Documentation gocd文档：GitHub - gocd/docker-gocd-server: Docker server image for GoCD 一、docker拉取gocd镜像 #拉取server镜像 docker pull gocd/gocd-server:v21.1.0docker pull g…
阅读更多...
java SSM水质历史数据可视化设计myeclipse开发mysql数据库springMVC模式java编程计算机网页设计

java SSM水质历史数据可视化设计myeclipse开发mysql数据库springMVC模式java编程计算机网页设计

一、源码特点 java SSM水质历史数据可视化设计是一套完善的web设计系统（系统采用SSM框架进行设计开发，springspringMVCmybatis），对理解JSP java编程开发语言有帮助，系统具有完整的源代码和数据库，系统主…
阅读更多...
QT常用控件使用及布局

QT常用控件使用及布局

QT常用控件使用及布局 文章目录 QT常用控件使用及布局1、创建带Ui的工程2、ui界面介绍1、界面设计区2、对象监视区3、对象监属性编辑区4、信号与槽5、布局器6、控件1、Layouts1、布局管理器2、布局的dome 2、Spacers3、Buttons4、项目视图组(Item Views)5、项目控件组(Item Wid…
阅读更多...
“数据要素×”正式来袭｜美创“全栈能力、深入场景”保障数据价值安全释放

“数据要素×”正式来袭｜美创“全栈能力、深入场景”保障数据价值安全释放

千呼万唤，1月4日，国家数据局等17部门联合印发的《“数据要素”三年行动计划（2024—2026年）》（下称《三年行动计划》）正式发布！ 作为国家数据局成立以来公开发布的首个重磅文件，《三年…
阅读更多...
大数据StarRocks(四) ：常用命令

大数据StarRocks(四) ：常用命令

这次主要介绍生产工作中使用Starrocks时的常用命令 4.1 连接StarRocks 4.1.1 Linux命令行连接 [roothadoop1011 fe]# yum install mysql -y [roothadoop1011 fe]# mysql -h hadoop101 -uroot -P9030 -p4.1.2 Windows客户端 DBeaver 连接 4.2 常用命令 4.2.1 查看状态 1. 查…
阅读更多...
uniapp获取手机当前信息及应用版本

uniapp获取手机当前信息及应用版本

appVersion 是app端查询的数据信息 appWgtVersion 是浏览器端查询的数据信息 onLoad() {const systemInfo uni.getSystemInfoSync();console.log(systemInfo);// #ifdef H5const uniAppVersion systemInfo.appVersion;// #endif// #ifndef H5const uniAppVersion systemIn…
阅读更多...
案例分享：Qt多国语言输入法软键盘

案例分享：Qt多国语言输入法软键盘

若该文为原创文章，转载请注明出处 本文章博客地址：https://hpzwl.blog.csdn.net/article/details/135346374 红胖子(红模仿)的博文大全：开发技术集合（包含Qt实用技术、树莓派、三维、OpenCV、OpenGL、ffmpeg、OSG、单片机、软硬结…
阅读更多...
vue-springboot基于java的实验室安全考试系统

vue-springboot基于java的实验室安全考试系统

本系统为用户而设计制作实验室安全考试系统，旨在实现实验室安全考试智能化、现代化管理。本实验室安全考试管理自动化系统的开发和研制的最终目的是将实验室安全考试的运作模式从手工记录数据转变为网络信息查询管理，从而为现代管理人员的使用提供更多的…
阅读更多...
模板管理支持批量操作，DataEase开源数据可视化分析平台v2.2.0发布

模板管理支持批量操作，DataEase开源数据可视化分析平台v2.2.0发布

2024年1月8日，DataEase开源数据可视化分析平台正式发布v2.2.0版本。 这一版本的功能升级包括：在“模板管理”页面中，用户可以通过模板管理的批量操作功能，对已有模板进行快速重新分类、删除等维护操作；数据大屏中&…
阅读更多...
大数据 - Doris系列《二》- Doris安装（亲测成功版）

大数据 - Doris系列《二》- Doris安装（亲测成功版）

目录 🐶2.1 安装前准备 🥙1.设置系统最大文件打开句柄数 >启动一个程序的时候，打开文件的数量就是句柄数 🥙2.设置文件包含限制一个进程可以拥有的VMA(虚拟内存区域)的数量 🥙3.时钟同步 🥙4.关闭交…
阅读更多...
labelImg的安装与使用

labelImg的安装与使用

目录 1、查看本机是否安装labelImg 2、安装labelImg 3、创建自己的数据集 3.1 建立新文件夹 3.2 打开labelImg 注意：出现闪退的情况处理。 4、文件格式转换 4.1 修改文件夹路径 4.2 新建datasets文件夹 4.3 修改图片路径 4.4 执行 1、查看本机是否安装la…
阅读更多...
uniCloud 云函数

uniCloud 云函数

相对于云函数，官方更推荐使用 云对象 新建云函数 编辑云函数 uniCloud-aliyun/cloudfunctions/hello_func/index.js use strict; exports.main async (event, context) > {let {name} eventreturn 你好，${name}! };云函数接收的参数从event中解构获…
阅读更多...
部署可道云网盘的一个漏洞解决

部署可道云网盘的一个漏洞解决

目录 1漏洞展示 2.防范措施 1漏洞展示 因为可道云网盘的上传文档有保存在 /data/Group/public/home/文档/ 中,当别有用心之人知道个人部署的域名与上次的文件后，可以进行访问拿到uid。例我在我部署的网盘上上次一个aa.php 文件，然后拿来演示 然后通过…
阅读更多...
密码学中的Hash函数

密码学中的Hash函数

目录 一. 介绍 二. hash函数的五个基本性质 （１）压缩性 （２）正向计算简单性 （３）逆向计算困难性 （４）弱无碰撞性 （&…
阅读更多...
RabbitMQ（八）消息的序列化

RabbitMQ（八）消息的序列化

目录 一、为什么需要消息序列化？二、常用的消息序列化方式1）Java原生序列化（默认）2）JSON格式3）Protobuf 格式4）Avro 格式5）MessagePack 格式 三、总结 RabbitMQ 是一个强大的消息中间…
阅读更多...
安全基础~信息搜集3

安全基础~信息搜集3

文章目录 知识补充APP信息搜集php开发学习理解漏洞 知识补充 端口渗透总结 python Crypto报错：https://blog.csdn.net/five3/article/details/86160683 APP信息搜集 1. AppInfoScanner 移动端(Android、iOS、WEB、H5、静态网站)信息收集扫描工具 使用教程 演示&…
阅读更多...
【Harmony OS - 网络请求】

【Harmony OS - 网络请求】

在一个应用开发中，网络请求是必不可少的，我们一般用的fetch、axios来进行http请求，在鸿蒙中也可以通过createHppt来发生一个http请求，它们都是异步请求返回的Promise，下面我们将介绍’ohos.net.http’和axios这两种方式…
阅读更多...
网络端口(包括TCP端口和UDP端口)的作用、定义、分类，以及在视频监控和流媒体通信中的定义

网络端口(包括TCP端口和UDP端口)的作用、定义、分类，以及在视频监控和流媒体通信中的定义

目 录 一、什么地方会用到网络端口？ 二、端口的定义和作用 （一）TCP协议和UDP协议 （二）端口的定义 （三）在TCP/IP体系中，端口(TCP和UDP)的作用 （…
阅读更多...
SSM框架学习笔记01 | 注解开发

SSM框架学习笔记01 | 注解开发

文章目录 1. 注解形式定义bean2.纯注解开发3.bean管理4. 依赖注入5. 第三方bean管理总结 1. 注解形式定义bean Compoenet ControllerServiceRepository 配合代码块 <context:component-scan /> 使用 2.纯注解开发 Configuration ComponentScan AnnotationConfigApplicati…
阅读更多...
k8s笔记1- 初步认识k8s

k8s笔记1- 初步认识k8s

k8s简介&#xff1a; kubernetes&#xff0c;俗称k8是&#xff0c;用于自动部署&#xff0c;扩缩和管理容器化应用程序的开源系统&#xff0c;它将组成应用程序的容器&#xff0c;组合成逻辑单元&#xff0c;便于管理和服务发现。 k8s的作用 自动化上线和回滚、存储编排…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023