• MySQL-多表查询

    一、表架构

    • student(sid,sname,sage,ssex) 学生表
    • course(cid,cname,tid) 课程表
    • sc(sid,cid,score) 成绩表
    • teacher(tid,tname) 教师表

    二 、建表sql语句

    SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `cid` int(11) NOT NULL COMMENT '课程号',
  `cname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '课程名',
  `tid` int(11) NULL DEFAULT NULL COMMENT '老师号',
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (3001, '英语', 1);
INSERT INTO `course` VALUES (3002, '数学', 2);
INSERT INTO `course` VALUES (3003, '物理', 3);
INSERT INTO `course` VALUES (3004, '语文', 4);

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `sid` int(11) NOT NULL COMMENT '学生号',
  `cid` int(11) NOT NULL COMMENT '课程号',
  `score` int(11) NULL DEFAULT NULL COMMENT '成绩'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (101, 3001, 90);
INSERT INTO `sc` VALUES (102, 3001, 85);
INSERT INTO `sc` VALUES (103, 3001, 76);
INSERT INTO `sc` VALUES (104, 3002, 87);
INSERT INTO `sc` VALUES (105, 3001, 92);
INSERT INTO `sc` VALUES (101, 3002, 81);
INSERT INTO `sc` VALUES (102, 3002, 93);
INSERT INTO `sc` VALUES (103, 3002, 73);
INSERT INTO `sc` VALUES (104, 3002, 65);
INSERT INTO `sc` VALUES (105, 3002, 96);
INSERT INTO `sc` VALUES (101, 3003, 85);
INSERT INTO `sc` VALUES (102, 3003, 76);
INSERT INTO `sc` VALUES (103, 3003, 63);
INSERT INTO `sc` VALUES (104, 3003, 59);
INSERT INTO `sc` VALUES (105, 3003, 56);
INSERT INTO `sc` VALUES (101, 3004, 100);
INSERT INTO `sc` VALUES (102, 3004, 83);
INSERT INTO `sc` VALUES (103, 3004, 75);
INSERT INTO `sc` VALUES (104, 3004, 69);
INSERT INTO `sc` VALUES (105, 3004, 50);
INSERT INTO `sc` VALUES (106, 3001, 60);
INSERT INTO `sc` VALUES (106, 3001, 60);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int(11) NOT NULL COMMENT '学生号',
  `sname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
  `sage` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `ssex` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (101, '王明', 18, '女');
INSERT INTO `student` VALUES (102, '王天', 19, '男');
INSERT INTO `student` VALUES (103, '张三', 18, '男');
INSERT INTO `student` VALUES (104, '李四', 19, '女');
INSERT INTO `student` VALUES (105, '王五', 20, '男');
INSERT INTO `student` VALUES (107, '万源', 17, '男');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `tid` int(11) NOT NULL COMMENT '老师号',
  `tname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '老师名称',
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '王老师');
INSERT INTO `teacher` VALUES (2, '李老师');
INSERT INTO `teacher` VALUES (3, '赵老师');
INSERT INTO `teacher` VALUES (4, '宋老师');

SET FOREIGN_KEY_CHECKS = 1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99

    三、查询

    1、多表查询语法

    select  字段1,字段2... from 表1,表2... [where 条件]

    • 1

    • 查询学生表和成绩表所有信息

      select * from student,sc where student.sid = sc.sid

    注意: 多表查询时,一定要找到两个表中相互关联的字段,并且作为条件使用
    在这里插入图片描述

    2、多表链接查询

    多表连接查询语法(重点)
SELECT 字段列表
    FROM 表1  INNER|LEFT|RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

    • 1
    • 2
    • 3
    • 4

    ①内连接查询 (只显示符合条件的数据)

    • 查询学生表和成绩表所有信息

      select * from student inner join sc on student.sid = sc.sid;

    内连接查询与多表联合查询的效果是一样的.
    在这里插入图片描述
    在这里插入图片描述

    ②左外连接查询 (左边表中的数据优先全部显示)

    • 查询学生表和成绩表所有信息

      select * from student left join sc on student.sid = sc.sid;

    学生表中的数据全部都显示,而成绩表中的数据符合条件的才会显示,不符合条件的会以 null 进行填充
    在这里插入图片描述
    在这里插入图片描述
    ③右外连接查询 (右边表中的数据优先全部显示)

    • 查询学生表和成绩表所有信息

      select * from student right join sc on student.sid = sc.sid;

    正好与[左外连接相反]

    在这里插入图片描述
    ④全连接查询(显示左右表中全部数据)

    MySQL数据库高级查询和多表查询
    pdf 0星 超过10%的资源 283KB
    下载
    全连接查询：是在内连接的基础上增加 左右两边没有显示的数据
注意: mysql并不支持全连接 full JOIN 关键字
注意: 但是mysql 提供了 UNION 关键字.使用 UNION 可以间接实现 full JOIN 功能


select * from student left join sc on student.sid = sc.sid
UNION
select * from student right join sc on student.sid = sc.sid;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    注意: UNION 和 UNION ALL 的区别:UNION 会去掉重复的数据,而 UNION ALL 则直接显示结果
    在这里插入图片描述

    3、三表查询

    ①查询所有人的英语成绩

    语法：
select 表1.字段,表2.字段,表3.字段 from 表1 join 表2 on 表1.关联字段 = 表2.关联字段 join 表3 on 表2.关联字段 = 表3.关联字段 where....;


select student.sname,course.cname,sc.score from student join sc on student.sid = sc.sid join course on sc.cid = course.cid where course.cname = "英语";

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述

    4、其它查询

    1、查询“3001”课程比“3003”课程成绩高的所有学生的学号与分数；

    SELECT a.sid,a.score FROM (SELECT sid,score FROM sc WHERE cid="3001") a,

      (SELECT sid,score FROM sc WHERE cid="3003") b

     WHERE a.score>b.score AND a.sid=b.sid

    • 1
    • 2
    • 3
    • 4
    • 5

    2、查询平均成绩大于60分的同学的学号和平均成绩

    数据库MySQL-多表查询
    pdf 0星 超过10%的资源 64KB
    下载
    SELECT sid,AVG(score)

FROM sc

GROUP BY sid HAVING AVG(score)>60

    • 1
    • 2
    • 3
    • 4
    • 5

    3、查询所有同学的学号、姓名、选课数、总成绩

    select s.sid as 学号,s.sname as 姓名,count(sc.cid) as 选课数,SUM(sc.score) as 总成绩
from student s INNER JOIN sc sc
on s.sid=sc.sid
GROUP BY s.sid

    • 1
    • 2
    • 3
    • 4

    4、查询姓“李”的老师的个数；

    select count(distinct(Tname))
  from teacher
  where tname like '李%';

    • 1
    • 2
    • 3

    5、查询学过“王老师”课的同学的学号、姓名

    SELECT s.sid AS 学号,s.sname AS 姓名 
FROM student s, sc sc, course c, teacher t 
WHERE s.sid = sc.sid AND sc.cid = c.cid AND c.tid = t.tid AND tname = "王老师"

    • 1
    • 2
    • 3

    6、查询没有学过“王老师”课的同学的学号、姓名

    SELECT s.sid, s.sname
FROM student s
WHERE s.sid NOT IN (
SELECT s.sid
FROM student s, sc sc, course c, teacher t
WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="王老师")

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦

  • 相关阅读:
    【附源码】计算机毕业设计SSM腾讯网游辅助小助手
    High Dimensional Continuous Control Using Generalized Advantage Estimation
    每日算法刷题Day11-最大公约数、数组去重
    软件测试学习（一）基础概念、实质、说明书测试、分类、动态黑盒测试
    面试题之JavaScript经典for循环（var let）
    猴子也能学会的jQuery第一期——什么是jQuery
    单例模式有几种写法？
    基于三维GIS的场数据模型研究与实践
    boa和cgi使用总结
    OpenCV（应用） —— 目标轮廓的相关应用
  • 原文地址：https://blog.csdn.net/m0_54864585/article/details/126113229