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

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;
关联关系图
1.查询所有课程的名称以及对应的任课老师姓名
-- 1.查询所有课程的名称以及对应的任课老师姓名
SELECT course.cname,teacher.tname
FROM course
INNER JOIN teacher ON course.cid = teacher.tid;
1
2、查询学生表中男女生各有多少人
-- 2、查询学生表中男女生各有多少人
SELECT gender,
    COUNT(1)
FROM student
GROUP BY
gender;
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
);
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)
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
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
5
6.查询姓李老师的个数
SELECT COUNT(0)
    FROM teacher
    WHERE tname LIKE "李%"
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
8
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • SQL语句大全
    50个常用的sql语句 Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cna...
    最美的太阳WW阅读 3,235评论 0赞 23
  • 50道SQL练习题及答案与详细分析
    网上流传较广的50道SQL训练，奋斗了不知道多久终于写完了。前18道题的难度依次递增，从19题开始的后半部分算是循...
    Kaidi_G阅读 476,847评论 312赞 452
  • 必知必回的SQL
    50个常用sql语句 Student(S#,Sname,Sage,Ssex) 学生表 （S#：学生学号；C#：课程...
    hemingkung阅读 84评论 0赞 0
  • 大神是怎样炼成的
    其实，你也可以成为大神 “程序盲”、“程序通”，看似两个不搭边界的词汇却出现在了同一个人的身上，她就是河北港口集团...
    诸暨吴彦祖丶hd阅读 197评论 0赞 0
  • 风停雨住正归时
    《我的散文集》目录 连续又颓废了几天。 每天和酷暑作斗争，骄阳似火，挥汗如雨，憔悴不堪，身心俱疲，昏昏欲睡。 追剧...
    潇湘月明阅读 502评论 0赞 8