AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2018-08-24 1163
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
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
文章标签：
云数据库 RDS MySQL 版
关系型数据库
SQL
MySQL
关键词：
云数据库 RDS MySQL 版sql
SQL MySQL
云数据库 RDS MySQL 版多表查询
SQL多表查询
多表查询SQL
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
Mr.Elliot
目录
相关文章
拉丁解牛说技术
|
11天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中，系统性能优化，从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化，除了DBA在集群性能、服务器调优需要投入精力，我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后，SQL执行效率可能就会出现瓶颈，影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能，在工作中解决SQL性能问题显得非常关键。
拉丁解牛说技术
80 13
东阳马生架构
|
11天前
|
SQL 存储 关系型数据库
MySQL原理简介—1.SQL的执行流程
本文介绍了MySQL驱动、数据库连接池及SQL执行流程的关键组件和作用。主要内容包括：MySQL驱动用于建立Java系统与数据库的网络连接；数据库连接池提高多线程并发访问效率；MySQL中的连接池维护多个数据库连接并进行权限验证；网络连接由线程处理，监听请求并读取数据；SQL接口负责执行SQL语句；查询解析器将SQL语句解析为可执行逻辑；查询优化器选择最优查询路径；存储引擎接口负责实际的数据操作；执行器根据优化后的执行计划调用存储引擎接口完成SQL语句的执行。整个流程确保了高效、安全地处理SQL请求。
东阳马生架构
131 75
东阳马生架构
|
6天前
|
SQL 存储 关系型数据库
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划，它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型，如const、ref、range、index和all等，并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略，包括驱动表和被驱动表的选择。此外，文章讨论了全表扫描和索引的成本计算方法，以及MySQL如何通过成本估算选择最优执行计划。最后，介绍了explain命令的各个参数含义，帮助理解查询优化器的工作机制。通过这些内容，读者可以更好地理解和优化SQL查询性能。
东阳马生架构
45 6
蓝染-惣右介
|
2月前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法，包括数据定义（DDL）、数据操作（DML）、数据查询（DQL）和数据控制（DCL）四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作，以及通过图形化工具DataGrip进行数据库管理和查询。此外，还讲解了数据的增、删、改、查操作，以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
蓝染-惣右介
134 2
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
蓝易云
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 高级(进阶) SQL 语句
MySQL 提供了丰富的高级 SQL 语句功能，能够处理复杂的数据查询和管理需求。通过掌握窗口函数、子查询、联合查询、复杂连接操作和事务处理等高级技术，能够大幅提升数据库操作的效率和灵活性。在实际应用中，合理使用这些高级功能，可以更高效地管理和查询数据，满足多样化的业务需求。
蓝易云
303 3
游客r2tgrg3ez7yzc
|
23天前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示：“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后，连接时提示不允许连接问题解决
游客r2tgrg3ez7yzc
156 69
蓝易云
|
10天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤，希望对您有所帮助。
蓝易云
81 42
蓝易云
|
1天前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍，您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限，以及识别和解决长时间运行的事务和死锁问题，可以有效地保障应用的稳定运行。
蓝易云
40 25
潜意识Java
|
28天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧，涵盖基础优化、高级技巧及性能监控。
潜意识Java
220 0
栈江湖
|
2月前
|
存储 Oracle 关系型数据库
数据库传奇：MySQL创世之父的两千金My、Maria
《数据库传奇：MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建，现归Oracle所有，广泛应用于阿里巴巴、腾讯等企业。2009年，Widenius因担心Oracle收购影响MySQL的开源性，创建了MariaDB，提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB，以确保更好的性能和社区支持。掌握MariaDB作为备用方案，对未来发展至关重要。
栈江湖
72 3

热门文章

最新文章

  • 1
    美团面试：MySQL为什么 不用 Docker部署？
  • 2
    【深入了解MySQL】优化查询性能与数据库设计的深度总结
  • 3
    详解MySQL字符集和Collation
  • 4
    docker pull mysql:8.0.26提示Error response from daemon: Get “https://registry-1.docker.io/v2/“: EOF错误
  • 5
    数据库连接工具连接mysql提示：“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
  • 6
    MySQL底层概述—1.InnoDB内存结构
  • 7
    MySQL底层概述—2.InnoDB磁盘结构
  • 8
    嵌入式C++、STM32、MySQL、GPS、InfluxDB和MQTT协议数据可视化
  • 9
    MySQL 分库分表方案
  • 10
    MySQL原理简介—3.生产环境的部署压测
  • 1
    MySQL原理简介—10.SQL语句和执行计划
    45
  • 2
    【SQL技术】不同数据库引擎 SQL 优化方案剖析
    64
  • 3
    MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
    80
  • 4
    MySQL原理简介—1.SQL的执行流程
    131
  • 5
    如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能？
    63
  • 6
    ASPX+MSSQL注如；SQL盲注
    40
  • 7
    Java使用sql查询mongodb
    45
  • 8
    Flink SQL 详解：流批一体处理的强大工具
    158
  • 9
    【潜意识Java】MyBatis中的动态SQL灵活、高效的数据库查询以及深度总结
    91
  • 10
    如何用 Java 校验 SQL 语句的合法性？
    57

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    PAI Model Gallery 支持云上一键部署 DeepSeek-V3、DeepSeek-R1 系列模型