当前位置：首页 > article >正文

MySQL：联合查询(2)

article 2024/11/23 8:42:23

首先写一个三个表的联合查询

查询所有同学的每门课成绩，及同学的个人信息

1.我们首先要确定使用哪些表

学生表，课程表，成绩表

2.取笛卡尔积

 select * from score,student,course;

3. 确定表与表之间的联合条件

select * from score,student,course where score.student_id=student.student_id and course.course_id=score.course_id;

4.确定过滤条件（无）

5.精简查询字段

 select student.student_id,student.sn,score.score,course.name from score,student,course where score.student_id=student.student_id and course.course_id=score.course_id;

外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

使用前

select * from student,class  where student.class_id=class.class_id;

右外连接：

 select * from student right join class on student.class_id=class.class_id;

右外连接，是以join 右边的表为基准，这个标的数据会全部的显示出来，左边的表没有与之匹配的记录全部会用null去填充。

左外连接：

 select * from student left join class on student.class_id=class.class_id;

查询哪些同学没有成绩

 select * from student left join score on student.student_id=score.student_id;

都可以用来找出没有成绩的人

select * from student left join score on student.student_id=score.student_id where score_id is null;

select * from student left join score on student.student_id=score.student_id where score.score is null;

自连接

原理：自己和自己的表进行连接

注：可以把行转为列，通过where进行过滤，进行行与行之间的比较。

显示所有的“计算机原理”成绩比“Java”成绩高的成绩信息

比较相同的表时，要将表给与别名，否则无法比较

 select * from score sc1,score sc2;

判断连接的条件

select * from score sc1,score sc2 where sc1.student_id=sc2.student_id;

找出同时有计算机原理和Java的信息

select * from score sc1,score sc2 where sc1.student_id=sc2.student_id and sc1.course_id=3 and sc2.course_id=1;

找出计算机原理比Java成绩高的

select * from score sc1,score sc2 where sc1.student_id=sc2.student_id and sc1.course_id=3 and sc2.course_id=1 and sc1.score>sc2.score;

子查询

也叫嵌套查询，子查询是指嵌入在其他sql语句中的select语句。

select * from table where table_id=(select id from table2 where  ......)

单行子查询

查询"不想毕业同学"的同班同学

1.参与查询的表

学生表

2.找到这个同学的班级编号

 select class_id from student where name='不想毕业';

3.找出与"不想毕业"同学班级编号相同的同学

 select student_id,name,class_id from student where class_id=1;

4.使用子查询，通过嵌套查询

select student_id,name,class_id from student where class_id=(select class_id from student where name='不想毕业');

5.除去这个同学

select student_id,name,class_id from student where class_id=(select class_id from student where name='不想毕业') and name!='不想毕业';

多行子查询

查询语文或英语的课程成绩信息

1.需要的表

课程表，成绩表

2.获取语文和英语的课程编号

select name,course_id from course where name='语文'or name ='英文';

3.根据获得的编号在成绩表中查询成绩

 select * from score where course_id=4 or course_id=6;

4.将上述的分布查询并起来，成子查询

select * from score where course_id in(select course_id from course where name='语文' or name ='英文');

注：如果查询不包括语文和英文的课程成绩信息

select * from score where course_id not in(select course_id from course where name='语文' or name ='英文');

查询重复的分数

 select * from score where (student_id,course_id,score)
    -> in (select student_id,course_id,score from score group by student_id,course_id,score having count(*) >1);

[NOT] EXISTS关键字：

exists 后面的括号中的查询语句，如果有结果返回，则执行外层的查询，如果返回是一个空结果集，则不执行外层的查询（相当于if（）语句，进行判断，有结果返回true，无结果返回false）

select * from student where exists(select * from student where student_id=1);

如果exists内返回是空结果集，则不执行外层查询

查询所有比“中文系2019级3班”平均分高的成绩信息

1.获得"中文系2019级3班的平均分"

先从班级中根据班级名找到班级编号
根据班级编号在学生表中找到班里的学生及学生编号
根据学生的编号在成绩表中计算出平均分

select avg(score.score) from score,student,class where class.class_id=student.class_id and student.student_id=score.student_id and class.name='中文系2019级3班';

2.再用表中的真实成绩和上表的平均值做比较

 select * from score,( select avg(score.score) score from score,student,class where class.class_id=student.class_id and student.student_id=score.stude
nt_id and class.name='中文系2019级3班') tmp where score.score>tmp.score;

合并查询

(UNUIN) (UNION all)

原理：将多个查询结果合并到一个结果集中

将两个表合并

select * from student where student_id=1 union  select * from student where name= '许仙';

注：

在单表中推荐用or去连接不同的条件

再多表中，就无法用or，如果最终的结果是从多个表中获得到的，必须用union进行合并

union和union all的差别

1.根据一张表的结构创建新表

create table student2 like student;

2.将新表中添加几个数据

通过union把student表和student2表并在一起

通过union all把student表和student2表并在一起

我们可以看出union合并时会去掉重复行，union all 不会去掉重复行

上述讲解用到的几个表

DROP TABLE IF EXISTS `score`;
DROP TABLE IF EXISTS `student`;
DROP TABLE IF EXISTS `class`;
DROP TABLE IF EXISTS `course`;
 
 
 
 
CREATE TABLE `class`  (
  `class_id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
 
 
 
INSERT INTO `class` VALUES (1, '计算机系2019级1班');
INSERT INTO `class` VALUES (2, '中文系2019级3班');
INSERT INTO `class` VALUES (3, '自动化2019级5班');
 
 
 
 
CREATE TABLE `course`  (
  `course_id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
 
 
 
INSERT INTO `course` VALUES (1, 'Java');
INSERT INTO `course` VALUES (2, '中国传统文化');
INSERT INTO `course` VALUES (3, '计算机原理');
INSERT INTO `course` VALUES (4, '语文');
INSERT INTO `course` VALUES (5, '高阶数学');
INSERT INTO `course` VALUES (6, '英文');
 
 
 
 
CREATE TABLE `student`  (
  `student_id` bigint NOT NULL AUTO_INCREMENT,
  `sn` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `mail` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `class_id` bigint NULL DEFAULT NULL,
  PRIMARY KEY (`student_id`) USING BTREE,
  UNIQUE INDEX `sn`(`sn` ASC) USING BTREE,
  INDEX `class_id`(`class_id` ASC) USING BTREE,
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
 
 
INSERT INTO `student` VALUES (1, '09982', '黑旋风李逵', 'xuanfeng@qq.com', 1);
INSERT INTO `student` VALUES (2, '00835', '菩提老祖', NULL, 1);
INSERT INTO `student` VALUES (3, '00391', '白素贞', NULL, 1);
INSERT INTO `student` VALUES (4, '00031', '许仙', 'xuxian@qq.com', 1);
INSERT INTO `student` VALUES (5, '00054', '不想毕业', NULL, 1);
INSERT INTO `student` VALUES (6, '51234', '好好说话', 'say@qq.com', 2);
INSERT INTO `student` VALUES (7, '83223', 'tellme', NULL, 2);
INSERT INTO `student` VALUES (8, '09527', '老外学中文', 'foreigner@qq.com', 2);
 
 
 
CREATE TABLE `score`  (
  `score_id` bigint NOT NULL AUTO_INCREMENT,
  `student_id` bigint NULL DEFAULT NULL,
  `course_id` bigint NULL DEFAULT NULL,
  `score` decimal(5, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`score_id`) USING BTREE,
  INDEX `student_id`(`student_id` ASC) USING BTREE,
  INDEX `course_id`(`course_id` ASC) USING BTREE,
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `score_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
 
INSERT INTO `score` VALUES (1, 1, 1, 70.50);
INSERT INTO `score` VALUES (2, 1, 3, 98.50);
INSERT INTO `score` VALUES (3, 1, 5, 33.00);
INSERT INTO `score` VALUES (4, 1, 6, 98.00);
INSERT INTO `score` VALUES (5, 2, 1, 60.00);
INSERT INTO `score` VALUES (6, 2, 5, 59.50);
INSERT INTO `score` VALUES (7, 3, 1, 33.00);
INSERT INTO `score` VALUES (8, 3, 3, 68.00);
INSERT INTO `score` VALUES (9, 3, 5, 99.00);
INSERT INTO `score` VALUES (10, 4, 1, 67.00);
INSERT INTO `score` VALUES (11, 4, 3, 23.00);
INSERT INTO `score` VALUES (12, 4, 5, 56.00);
INSERT INTO `score` VALUES (13, 4, 6, 72.00);
INSERT INTO `score` VALUES (14, 5, 1, 81.00);
INSERT INTO `score` VALUES (15, 5, 5, 37.00);
INSERT INTO `score` VALUES (16, 6, 2, 56.00);
INSERT INTO `score` VALUES (17, 6, 4, 43.00);
INSERT INTO `score` VALUES (18, 6, 6, 79.00);
INSERT INTO `score` VALUES (19, 7, 2, 80.00);
INSERT INTO `score` VALUES (20, 7, 6, 92.00);

希望能对大家有所帮助！！！！