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

作者: Mr_Elliot | 来源:发表于2018-08-24 11:52 被阅读21次

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

网友评论

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

本文链接：https://www.haomeiwen.com/subject/dhjbiftx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

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

数据准备

1.查询所有课程的名称以及对应的任课老师姓名

2、查询学生表中男女生各有多少人

3、查询物理成绩等于100的学生的姓名

3.2查询美术成绩大于80的同学的姓名

4、查询平均成绩大于八十分的同学的姓名和平均成绩

5 查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)

6.查询姓李老师的个数

7、查询没有报李平老师课的学生姓名

8、查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

晴空万里

数据库

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

数据准备

1.查询所有课程的名称以及对应的任课老师姓名

2、查询学生表中男女生各有多少人

3、查询物理成绩等于100的学生的姓名

3.2查询美术成绩大于80的同学的姓名

4、查询平均成绩大于八十分的同学的姓名和平均成绩

5 查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)

6.查询姓李老师的个数

7、 查询没有报李平老师课的学生姓名

8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

晴空万里

数据库

7、查询没有报李平老师课的学生姓名

8、查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)