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

最新推荐文章于 2024-07-20 20:04:43 发布
最新推荐文章于 2024-07-20 20:04:43 发布
阅读量86 收藏 1
点赞数
文章标签： 数据库
原文链接：https://yq.aliyun.com/articles/637793
版权
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
weixin_34413065
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql select缓存机制使用详解
01-21
需要注意的是mysql query cache 是对大小写敏感的，因为Query Cache 在内存中是以 HASH 结构来进行映射，HASH 算法基础就是组成 SQL 语句的字符，所以 任何sql语句的改变重新cache,这也是项目开发中要建立sql语句...
mysql优化、sql语句
07-12
【MySQL优化和SQL语句详解】 在软件开发中，MySQL是一个不可或缺的数据库管理系统，它以其高效、稳定和开源的特点，广泛应用于各种规模的应用程序。掌握MySQL优化和SQL语句的使用，对于提升应用性能和效率至关重要...
高效率sql写法
lingxiao200的专栏
05-08 228
如何提高SQL查询效率 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表...
SQL编写能力练习，通关之后就不再是小学生
qq_38374562的博客
03-01 284
如题，个人记录的SQL练习，内容很基础，适合初中级javaweb之类的岗位开发日常，上代码： 这是我在NavicatForMySQL里面练习的，如有错误留言，代码如下： USE demoforstudy; #1.查询student表中的所有记录的sname，ssex，和class列。 select st.sname,st.ssex,st.class from students st #2....
SQL 编写能力提升-01 基础强化(Mysql)
学无止境
11-21 3436
数据库 :   mysql8 编辑器 :   navicate12        工作之余工作在网上找的sql练习题,看到还有些难度,练习下来会有不少提升.  原来的数据库是oracle, 我用的是mysql8(语法个别不同于oracle),  第三方sql连接工具用的是navicate12用的都是最新的 ,当然性能也很不错,例如navicate12 使用起来比以前的版本流畅许多! 第一部分:...
如何提升写作能力？
niuhaoman的博客
08-06 636
如何提升写作能力？ 1. 文笔差的原因 积累不够 没有进行刻意练习：套路 2. 输入：读书 1. 怎么读 读书在精，不在多。一本好书，读第一遍看情节、第二遍看手法、第三遍琢磨用词。 把觉着不错的段落/句子摘抄下下来，思考：我为什么觉得受启发？我想要叙述出来，需要什么例子去论证它？它引起我的共鸣了吗？之后复述乃至背诵。 2. 读什么 书单：茅盾文学奖获奖作品，培养对文字的感觉。 名著，以及...
mysql 多表查询详解_（转）Mysql 多表查询详解
weixin_35410099的博客
01-27 83
MySQL多表查询详解一.前言二.示例三.注意事项一.前言上篇讲到mysql中关键字执行的顺序，只涉及了一张表；实际应用大部分情况下，查询语句都会涉及到多张表格 :1.1多表连接有哪些分类?1.2针对这些分类有哪些连接方法?1.3这些连接方法分别作用于哪些应用场景?这篇针对这三个点通过实例来讲述，目的是穷尽所有的场景和所有的方法，并且对每个方法的使用做实例。首先先列举本篇用到的分类(内连接...
mysql 多表查询详解_MySql多表查询优化详解
weixin_30423205的博客
01-27 316
本文来源于：java后端编程对数据表的多表查询也是必不可少的，本篇内容主要给大家讲解多表联合查询的优化。一、多表查询连接的选择： 相信这内连接，左连接什么的大家都比较熟悉了，当然还有左外连接什么的，基本用不上我就不贴出来了。这图只是让大家回忆一下，各种连接查询。然后要告诉大家的是，需要根据查询的情况，想好使用哪种连接方式效率更高。二、MySQL的JOIN实现原理在MySQL 中，只有一种Join ...
MySQL基础——多表查询（各种join连接详解）
热门推荐
Gorden的博客
04-28 5万+
Mysql 多表查询详解 目录 Mysql 多表查询详解 一.前言 二.下面以实例进行分析 三 注意事项 一.前言 上篇讲到Mysql中关键字执行的顺序，只涉及了一张表；实际应用大部分情况下，查询语句都会涉及到多张表格 : 1.1多表连接有哪些分类? 1.2针对这些分类有哪些连接方法? 1.3这些连接方法分别作用于哪些应用场景? 这篇针对这三个点通过实例来讲述，目的是穷尽所有的场景和所有的方法，并且对每个方法的使用做实例。 首先先列举本篇用到的分类(内连接，外连接，交叉连接...
数据库技术及应用：第4章关系数据库语言SQL.ppt
06-03
【SQL（Structured Query Language）详解】 SQL，全称为结构化查询语言，是1986年由美国国家标准局ANSI制定的关系数据库管理系统标准语言，并随后被国际标准化组织ISO采纳。SQL的强大在于其作为数据库操作的标准，...
关于MySQL的基础篇章
05-10
【MySQL基础知识详解】 MySQL是一种广泛使用的开源关系型数据库管理系统（RDBMS），它以其高效、稳定和易用性而受到全球开发者的青睐。本篇笔记将深入探讨MySQL的基础概念、语法以及常用操作。 1. **MySQL启动与...
MySQL从入门到实战学习教程之10存储过程.pptx
10-31
MySQL存储过程详解 MySQL存储过程是MySQL中常用的数据库对象，它是一组为了完成特定功能的预编译的SQL语句集合。存储过程是一种命名的程序块，在第一次使用经过编译之后，再次调用时不用再编译，因此执行效率较高。...
如何编写复杂的多表查询SQL
bosee520的专栏
11-25 374
1、 用程序中，保证在实现功能的基础上，尽量减少对数据库的访问次数；通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；能够分开的操作尽量分开处理，提高每次的响应速度；在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；算法的结构尽量简单；在查询时，不要过多地使用通配符如SELECT * FROM T1语句，要用到几列就选择几列如：SELECT COL1,COL2 FROM T1；在可能的情况下尽量限制尽量结果集行数如：SELECT TOP 300 COL1,COL2,COL3 FRO
摘抄SQL执行效率
dlkfg0052的博客
09-05 313
我们做软件开发的，大部分人都离不开跟数据库打交道，特别是erp开发的，跟数据库打交道更是频繁，存储过程动不动就是上千行，如果数据量大，人员流动大，那么我么还能保证下一段时间系统还能流畅的运行吗？我么还能保证下一个人能看懂我么的存储过程吗？那么我结合公司平时的培训和平时个人工作经验和大家分享一下，希望对大家有帮助。 　　要知道SQL语句，我想我们有必要知道SQL Server查询分析器怎...
OAuth2.0 or Spring Session or 单点登录流程
最新发布
qq_53374893的博客
07-20 254
一句话精辟解释： 在过滤器放行的时候，偷偷的把原生的 request 和 response 对象换成了它的实现，也就是 调用getSession 的时候拿到的 其实是对redis 操作的Session。但其他的操作还是使用原生的，只不过重写的方法，调用的是子类的，也就是往 redis 里放入 Session，把原生的操作给替换了！分布式Session原理，使用子域名，的时候放入父域名的 JsessionId 然后将这个ID 的所存的内容放入Redis ，这样实现不同域名共享同一sessionID.
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 658
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
Spring框架之DI依赖注入
G81948577的博客
07-18 975
我们在下面构建spring的过程中体会依赖注入；从上面的图中我们知道，在ssm框架中服务层（server）无法直接操作数据库，持久层（dao）是直接操作数据库进行数据处理的，但是我们如果在服务层有一个持久层的对象，然后我们在服务层通过对象去调用持久层的方法就可以操作数据库了。@OverrideSystem.out.println("持久层，你好");UserServiceImpl类中的代码如下@Override。
MySQL入门指南：安装、配置与SQL详解
MySQL笔记.pdf是一份全面的MySQL学习资料，主要涵盖了数据库的基础概念、MySQL数据库软件的安装、卸载和配置过程，以及SQL语言的详细介绍。以下是其中的重要知识点： 1. **数据库基本概念**: - 数据库（DataBase, ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值