MySql多表查询详解(Ⅰ):提升一下你的sql书写能力

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySql8.0数据准备(温馨提示:请自备数据库O(∩_∩)O) SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0; -- -------------------------...
img_74c1c304f0de1bfd40899193b041bbda.png
MySql8.0

数据准备

(温馨提示:请自备数据库O(∩_∩)O)

    SET NAMES utf8;
    SET FOREIGN_KEY_CHECKS = 0;

    -- ----------------------------
    --  Table structure for `class`
    -- ----------------------------
    DROP TABLE IF EXISTS `class`;
    CREATE TABLE `class` (
      `cid` int(11) NOT NULL AUTO_INCREMENT,
      `caption` varchar(32) NOT NULL,
      PRIMARY KEY (`cid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

    -- ----------------------------
    --  Records of `class`
    -- ----------------------------
    BEGIN;
    INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
    COMMIT;

    -- ----------------------------
    --  Table structure for `course`
    -- ----------------------------
    DROP TABLE IF EXISTS `course`;
    CREATE TABLE `course` (
      `cid` int(11) NOT NULL AUTO_INCREMENT,
      `cname` varchar(32) NOT NULL,
      `teacher_id` int(11) NOT NULL,
      PRIMARY KEY (`cid`),
      KEY `fk_course_teacher` (`teacher_id`),
      CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

    -- ----------------------------
    --  Records of `course`
    -- ----------------------------
    BEGIN;
    INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
    COMMIT;

    -- ----------------------------
    --  Table structure for `score`
    -- ----------------------------
    DROP TABLE IF EXISTS `score`;
    CREATE TABLE `score` (
      `sid` int(11) NOT NULL AUTO_INCREMENT,
      `student_id` int(11) NOT NULL,
      `course_id` int(11) NOT NULL,
      `num` int(11) NOT NULL,
      PRIMARY KEY (`sid`),
      KEY `fk_score_student` (`student_id`),
      KEY `fk_score_course` (`course_id`),
      CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
      CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

    -- ----------------------------
    --  Records of `score`
    -- ----------------------------
    BEGIN;
    INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
    COMMIT;

    -- ----------------------------
    --  Table structure for `student`
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student` (
      `sid` int(11) NOT NULL AUTO_INCREMENT,
      `gender` char(1) NOT NULL,
      `class_id` int(11) NOT NULL,
      `sname` varchar(32) NOT NULL,
      PRIMARY KEY (`sid`),
      KEY `fk_class` (`class_id`),
      CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

    -- ----------------------------
    --  Records of `student`
    -- ----------------------------
    BEGIN;
    INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
    COMMIT;

    -- ----------------------------
    --  Table structure for `teacher`
    -- ----------------------------
    DROP TABLE IF EXISTS `teacher`;
    CREATE TABLE `teacher` (
      `tid` int(11) NOT NULL AUTO_INCREMENT,
      `tname` varchar(32) NOT NULL,
      PRIMARY KEY (`tid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

    -- ----------------------------
    --  Records of `teacher`
    -- ----------------------------
    BEGIN;
    INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
    COMMIT;

    SET FOREIGN_KEY_CHECKS = 1;
img_15cce7b6dd4b8e8e6feb38b3b445a5fd.png
关联关系图
1.查询所有课程的名称以及对应的任课老师姓名
-- 1.查询所有课程的名称以及对应的任课老师姓名
SELECT course.cname,teacher.tname
FROM course
INNER JOIN teacher ON course.cid = teacher.tid;
img_9531344dd84224d5970a8ab46effe6e0.png
1
2、查询学生表中男女生各有多少人
-- 2、查询学生表中男女生各有多少人
SELECT gender,
    COUNT(1)
FROM student
GROUP BY
gender;
img_5bdca1433a690378f96260408d753adb.png
2
3、查询物理成绩等于100的学生的姓名
--  3.1、查询物理成绩等于100的学生的姓名
SELECT sname 姓名
FROM student
WHERE sid IN(
    SELECT
    s.student_id
    FROM
    score s
    INNER JOIN (
        SELECT
        cid
        FROM
        course
        WHERE
        cname = '物理'
    )t2 ON s.course_id = t2.cid
    WHERE
    s.num = 100
);
-- sql拆分 1. 通过科目的名字 查询 id 结果表
SELECT cid FROM course WHERE cname = '物理';
-- sql拆分 2.把科目查询结果表  作为查询条件 然后起个别名 内连接条件 过滤条件
SELECT s.student_id
FROM score s
INNER JOIN (SELECT cid FROM course WHERE cname = '物理') t2 ON 
s.course_id = t2.cid
WHERE s.num = 100;
-- sql拆分 3.IN的使用方法 in 是一个数据集,可能包含多个值,= 只是一个值
SELECT sname 姓名
FROM student
WHERE sid IN(
    SELECT s.student_id
    FROM score s
    INNER JOIN (SELECT cid FROM course WHERE cname = '物理') t2 ON 
    s.course_id = t2.cid
    WHERE s.num = 100
);
img_9fa5a8583e16e00f06c498d6e1db58e3.png
3.1
3.2查询美术成绩大于80的同学的姓名
-- 3.2 练习  查询美术成绩大于80的同学的姓名
    -- 3.2.1 根据 ‘美术’ 查询 课程表  得到这门课程的  id--> cid
    SELECT cid FROM course WHERE cname = '美术';
    -- 3.2.2 结果表中的cid 即为 course_id  作为连接条件查询 学生id结果集
    SELECT s.student_id
    FROM score s
    INNER JOIN (SELECT cid FROM course WHERE cname = '美术') t2
    ON s.course_id = t2.cid
    WHERE s.num > 80
    -- 3.2.3 查询学生表 获得结果集
    SELECT sname 姓名
    FROM student
    WHERE sid IN(SELECT s.student_id
    FROM score s
    INNER JOIN (
    SELECT cid FROM course WHERE cname = '美术') t2
    ON s.course_id = t2.cid
    WHERE s.num > 80)
img_f035530ba8cbb0c05d40fbd7a79db17a.png
3.2
4、查询平均成绩大于八十分的同学的姓名和平均成绩
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
    -- 4.1 字段 学生姓名 平均成绩 
    SELECT sname avg_num  FROM student 
    -- 4.2
    SELECT s.student_id,AVG(s.num) AS avg_num
    FROM score s
    GROUP BY s.student_id
    HAVING AVG(s.num) > 80
    -- 4.3
    SELECT sname AS 姓名,avg_num AS 平均成绩 FROM student
    INNER JOIN (SELECT s.student_id,AVG(s.num) AS avg_num
    FROM score s
    GROUP BY s.student_id
    HAVING AVG(s.num) > 80) t2 ON student.sid = t2.student_id
img_1c91024284e5d217f9cb08d4b2545828.png
4
5 查询所有学生的学号,姓名,选课数,总成绩(注意:对于那些没有选修任何课程的学生也算在内)
-- 5 查询所有学生的学号,姓名,选课数,总成绩(注意:对于那些没有选修任何课程的学生也算在内)
    -- 5.1字段 学号 姓名  选课数  总成绩 
    SELECT sid,sname,count_course,SUM
    FROM student
    -- 5.2
    SELECT s.student_id,COUNT(s.course_id) AS count_course,SUM(s.num) AS sum_num
    FROM score s
    GROUP BY s.student_id
    -- 5.3
    SELECT st.sid AS 学号,st.sname AS 姓名,t2.count_course AS 选课数,t2.sum_num AS 总成绩
    FROM student AS st
    INNER JOIN (SELECT s.student_id,COUNT(s.course_id) AS count_course,SUM(s.num) AS sum_num
    FROM score s
    GROUP BY s.student_id) t2 ON st.sid = t2.student_id
img_2fa2e97daa50d29055a5c27d7dcd94f7.png
5
6.查询姓李老师的个数
SELECT COUNT(0)
    FROM teacher
    WHERE tname LIKE "李%"
img_94a999a33a358e1d6c616058f93a9b70.png
6
7、 查询没有报李平老师课的学生姓名
-- 7、 查询没有报李平老师课的学生姓名
-- 查询选择 李平老师的课程的学生的姓名
SELECT c.cid FROM course c INNER JOIN teacher t ON c.teacher_id = t.tid
WHERE t.tname = "李平老师"

SELECT DISTINCT s.student_id
FROM score s WHERE s.course_id IN(SELECT c.cid FROM course c INNER JOIN teacher t ON c.teacher_id = t.tid
WHERE t.tname = "李平老师"
)

SELECT t1.sname 姓名
FROM student t1
INNER JOIN (SELECT DISTINCT s.student_id
FROM score s WHERE s.course_id IN(SELECT c.cid FROM course c INNER JOIN teacher t ON c.teacher_id = t.tid
WHERE t.tname = "李平老师"
)) t2 ON t1.sid = t2.student_id

SELECT t1.sname 姓名
FROM student t1
WHERE t1.sid NOT IN (SELECT DISTINCT s.student_id
FROM score s WHERE s.course_id IN(SELECT c.cid FROM course c INNER JOIN teacher t ON c.teacher_id = t.tid
WHERE t.tname = "李平老师"
))
8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表,然后连表即可)
-- #8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表,然后连表即可)
SELECT * FROM course

-- 查出物理课程的id
SELECT cid FROM course WHERE cname = "物理"

SELECT student_id,num  FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = "物理"
)
SELECT student_id,num  FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = "生物"
)

SELECT t1.student_id AS 学号,t1.num AS 物理分数,t2.num AS 生物分数
FROM (SELECT student_id,num  FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = "物理"
)) t1 
INNER JOIN (SELECT student_id,num  FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = "生物"
)) t2 ON t1.student_id = t2.student_id WHERE t1.num > t2.num
img_264e8b3196cc1711f71f88601e61d1ad.png
8
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
SQL 关系型数据库 MySQL
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
|
1天前
|
SQL 存储 关系型数据库
RDS for MySQL的SQL分类与数据类型
小明需在MySQL中管理商品信息,使用SQL完成业务操作。SQL分为DQL(查询)、DML(增删改)、DDL(定义)、DCL(权限控制)和TCL(事务)五大类。DDL用于创建、修改和删除数据库结构,DML处理数据,DCL控制权限,TCL管理事务,DQL则用于查询数据。MySQL有多种数据类型,如数值型(整数、小数)、日期型和字符串型等,选择合适的数据类型是高效开发的关键。
12 0
|
3天前
|
SQL 关系型数据库 MySQL
查询mysql版本sql - 蓝易云
执行这个命令后,MySQL将返回当前正在运行的版本信息。
41 0
|
5天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之从MySQL到Flink 1.16.2 Flink-SQL的数据同步工作出现了一个异常如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
21 0
|
5天前
|
SQL 存储 数据处理
实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-sql-connector-mysql-cdc有什么区别
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
15 1
|
5天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
80 0
|
7天前
|
关系型数据库 MySQL 数据库
docker MySQL删除数据库时的错误(errno: 39)
docker MySQL删除数据库时的错误(errno: 39)
60 0
|
7天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(下)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
44 0
|
7天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(上)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
47 0
|
1天前
|
存储 关系型数据库 MySQL
MySQL中数据库的相关操作
MySQL中数据库的相关操作
221 62