首先写一个三个表的联合查询
查询所有同学的每门课成绩，及同学的个人信息
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);
希望能对大家有所帮助！！！！
