当前位置:首页 > 数据库 > MySQL-多表查询

MySQL-多表查询

2022年11月08日 08:42:14数据库6

一、表架构

  • 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、多表查询语法

select  字段1,字段2... from 表1,表2... [where 条件]
  • 查询学生表和成绩表所有信息
select * from student,sc where student.sid = sc.sid

注意: 多表查询时,一定要找到两个表中相互关联的字段,并且作为条件使用
MySQL-多表查询 _ JavaClub全栈架构师技术笔记

2、多表链接查询

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

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

  • 查询学生表和成绩表所有信息
select * from student inner join sc  on student.sid = sc.sid;

内连接查询与多表联合查询的效果是一样的.
MySQL-多表查询 _ JavaClub全栈架构师技术笔记
MySQL-多表查询 _ JavaClub全栈架构师技术笔记

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

  • 查询学生表和成绩表所有信息
select * from student left join sc  on student.sid = sc.sid;

学生表中的数据全部都显示,而成绩表中的数据符合条件的才会显示,不符合条件的会以 null 进行填充
MySQL-多表查询 _ JavaClub全栈架构师技术笔记
MySQL-多表查询 _ JavaClub全栈架构师技术笔记
③右外连接查询 (右边表中的数据优先全部显示)

  • 查询学生表和成绩表所有信息
select * from student right join sc  on student.sid = sc.sid;

正好与[左外连接相反]

MySQL-多表查询 _ JavaClub全栈架构师技术笔记
④全连接查询(显示左右表中全部数据)

全连接查询:是在内连接的基础上增加 左右两边没有显示的数据
注意: 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;

注意: UNION 和 UNION ALL 的区别:UNION 会去掉重复的数据,而 UNION ALL 则直接显示结果
MySQL-多表查询 _ JavaClub全栈架构师技术笔记

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 = "英语";

MySQL-多表查询 _ JavaClub全栈架构师技术笔记

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

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

SELECT sid,AVG(score)

FROM sc

GROUP BY sid HAVING AVG(score)>60

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

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

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

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 = "王老师"

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="王老师")

作者:檬柠wan
来源链接:https://blog.csdn.net/qq_45664055/article/details/120853586

版权声明:
1、JavaClub(https://www.javaclub.cn)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。


本文链接:https://www.javaclub.cn/database/68471.html

标签: MySQL
分享给朋友:

“MySQL-多表查询” 的相关文章

MySQL学习(4)︱数据库的查询

MySQL学习(4)︱数据库的查询

在SQL中,使用select语句来查询数据。不同的关系数据库,select语法会有细微差别,在MySQL官网可以查询到支持的select语法。 SQL语法: SELECT column_name1, column_name2//...

mysql 查询或

想对一张表进行查询,满足任意一个条件即可,可以用union实现或查询。 id age gender 1 20 female 2 21 male 3 22 male  ...

mysql字段值中带空格、mysql查询参数带空格的查询方法

问题描述 当我们在使用mysql查询的时候,如果数据库中的这个字段的值含有空格(字符串内部,非首尾),或者我们查询的参数中间有空格,而字段值中没有空格。那么我们很有可能就什么都查不到。 假如有下面的一张表content:...

mysql查询条件为不等于某个值时,null 查询不到?

mysql 当查询是条件为 where status != ‘1’ 时 当status 为null时查询不到 的坑 今天又碰到了这坑 我要查询status !=‘1’ 的所有数据,数据库中符合要求有17条,可是查询结果只返回了7条,当时就很纳闷,经...

MYSQL查询一个月前的数据

日期格式 SELECT (DATE_ADD(now(),INTERVAL -1 month)) 时间戳格式 SELECT UNIX_TIMESTAMP(DATE_ADD(now(),INTERVAL -1 month)) 更多: ~~~ select * FRO...

关于mysql数据库连接异常处理

tomcat启动错误日志关键信息: 28-Aug-2019 14:22:55.014 SEVERE [localhost-startStop-1] org.apache.catalina.core.ContainerBase.addChildInternal ContainerB...

mysql查询某一个字段是否包含中文汉字

在使用mysql时候,某些字段会存储中文字符,或是包含中文字符的串,查询出来的方法是:   SELECT column FROM table WHERE length(column)!=char_length(column) 原理其实很简单,当字符集为UTF-8,并且...

mysql查询前几条记录

原文地址为: mysql查询前几条记录 SELECT   *   FROM   table   LIMIT   5;  &n...

MySQL查询优化

MySQL查询优化

一、查询优化 1.1 小表驱动大表 永远小表驱动大表,类似嵌套循环Nested Loop EXISTS SELECT ... FROM table WHERE EXISTS (subquery) 该语法可以理解为。将主查询的数...

mysql  查询当某一条件满足时 的那一条数据

mysql 查询当某一条件满足时 的那一条数据

查询当某一条件满足时的那一条数据 首先准备一个测试的表以及数据 -- 每日代码行数记录表 create table code_line_record( id_ int primary key auto_increment, name varchar(20)...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。