文章目录
内连接（join）外连接（left join）on和where的区别自连接子查询（嵌套查询）合并查询(union)1.前置知识—笛卡尔积
实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积。
笛卡尔积是联合查询也就是多表查询的基础。
笛卡尔积又称直积，表示XY，比如A表中的数据有m行，B表中的数据有n行，那么A和B做笛卡尔积，结果为mn行
创建一个数据库和表：
-- 创建数据库drop database if exists java33;create database java33 default character set 'utf8mb4';-- 切换数据库use java33;-- 创建班级表drop table if exists class;create table class(id int primary key auto_increment comment '班级编号',classname varchar(250) not null comment '班级名称');-- 创建学生表drop table if exists student;create table student(id int primary key auto_increment comment '学生编号',sn varchar(50) comment '学号',username varchar(250) not null comment '学生名称',`mail` varchar(250) comment '邮箱',class_id int,foreign key (class_id) references class(id));-- 创建课程表drop table if exists course;create table course(id int primary key auto_increment comment '课程编号',name varchar(250) not null);-- 成绩表drop table if exists score_table;create table score_table(id int primary key auto_increment comment '成绩编号',score decimal(4,1),student_id int not null,course_id int not null,foreign key (student_id) references student(id),foreign key (course_id) references course(id));
添加测试数据：
-- 班级表添加数据insert into class(id,classname) values(1,'Java班级'),(2,'C++班级');-- 课程表添加数据insert into course(id,name) values(1,'计算机'),(2,'英语');-- 学生表添加数据insert into student(id,sn,username,mail,class_id) values(1,'CN001','张三','zhangsan@',1),(2,'CN002','李四','lisi@',2),(3,'CN003','王五','wangwu@',1);-- 成绩表添加数据insert into score_table(id,score,student_id,course_id) values(1,90,1,1),(2,59,1,2),(3,65,2,1),(4,NULL,2,2);
内连接（join）
内连接侧重于两个表之间的共性，它的作用是使用链接，比较两个（或多个）表之间的共有数据，然后进行返回。
比如我要查询学生的成绩，涉及到两张表：学生表和成绩表，使用内连接查询的数据是下图红色部分：
语法：
(最常用)1.select * from 表名1 join 表名2 [on 条件] [where 表达式];2.select * from 表名1 inner join 表名2 [on 条件] [where 表达式];3.select * from 表名1 cross join 表名2 [on 条件] [where 表达式];(常用)4.select * from 表名1,表名2 [where 表达式];
例：
1.查询张三成绩
（1）进行内连接查询（笛卡尔积）
12种组合
标红的为有效值，其他的都是无效值
（2）去掉无效的数据（on过滤条件），得到有意义的数据
（3）查询张三的成绩（where过滤条件）
（3）.1
（3）.2
（3）.3
（3）.4内连接的实现方法
不可以用on，只能用where
**不强制要求起别名，但建议加上别名；**如果没有别名，那么连表查询中出现多个相同字段的列名就会报错
MySQL不知道到底是学生表的id还是成绩表的id
如果连表查询中，所有中的字段名都不重复，那么可以不使用别名
别名作用：为了简化代替长表名
练习：
1，查询每个人的总成绩和个人信息
（1）联合学生表和成绩表进行查询（笛卡尔积）
（2）排除笛卡尔积中的无意义数据（join …on st.stydent_id=s.id）（两个表的关联关系[关联字段]）
(3) 使用sum得到成绩的综合
（4）根据学生（id）进行分组
2.查询每个人成绩+科目名+个人信息
成绩表：score_table 科目表：course 学生表：student
（1）连表查询（3张表）select * from t1 join t2 join t3(24条数据)
（2）过滤掉笛卡尔积中的无意义数据：select * from t1 join t2 [on 条件过滤] join t3
[on 条件过滤]
3.查询学生的成绩和科目及个人表，要求根据相同科目显示在一起，并按照成绩降序排列，并将null替换为0.
（1）实现3张表的联查（join…）
( 2 )得到笛卡尔积中的有效数据（join…
on…join…in…）
(3)将科目显示在一块（对行序有要求）使用排序–>order by c.name
（4）加联合排序–>order by c.name,st.score desc
(5)select ifnull (st…socre,0)…
外连接（left join）
外连接分为左(外)连接和右(外)连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
左外连接：
查询部分为红色
右外连接：
查询部分为红色
左连接语法：
-- 左外连接，表1完全显示select * from 表名1 left join 表名2 on 接条件 [where 连接条件];
例：
查询所有人成绩：
第一条使用左连接，第二条使用右连接
左连接和右连接只需要掌握一种语法即可，可以使用左链接实现“右连接”，只需要把表的查询顺序调换一下就可以实现左/右连接的转换
例：
查询所有人个人信息+课程名+分数
（1）.查询学生表、课程表、分数表
（2）.left join 的主表是学生表
中间表写在中间。
on和where的区别
1.内连接on是可以省略的，而外连接on不能省略 2.on在内链接中的执行效果和外连接中的执行效果是不一样的 3.在外连接中on和where有是不一样的
2.对于内连接和外连接的区别
left join on 查询不能过滤左表中的数据，而内连接on查询可以过滤全局数据
on查询不会对主表中的数据过滤
3. 在外连接查询中，on和where的意义是完全不同的
在外连接查询时，如果有多个查询条件，正确写法是将查询条件全部写在where表达式中，而不是写在on中，在on中一般情况下只需要写一个笛卡尔积无效数据的过滤条件即可
自连接
自连接是指在同一张表连接自身进行查询。
语法：
select * from 表1,表2 where条件；
例：
查询英语成绩<计算机成绩的数据
实现思路：
1.先根据科目名称查询出来科目id（成绩表中只有科目id没有科目名称）
2.自查询（笛卡尔积）
3.去除掉笛卡尔积中的无意义值（实际查询到的是两张表)(有效的数据：主键相同[非自查询]、学生id相同[有效的业务id进行笛卡尔积过滤])
4.设置where条件，让表1只查询英语成绩，表2查询计算机成绩
5.设置where多条件查询，让英语成绩<计算机成绩
子查询（嵌套查询）
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
将当前查询的结果作为另一个查询的where的选项
例：
1.查询张三的同班同学（学生表、班级表）
思路：
1.1查询张三的班级id
1.2.学生表里根据上一条查询的班级id查询出所有列表—>张三的同班同学
2.查询计算机或英语的成绩（成绩表和课程表）
2.1查询计算机或英语的科目id
2.2查询成绩表where科目id等于计算机id或者是英语id
in和=查询的区别：
=查询需要一个具体确定的值
in查询可以是一个或多个值，并且满足任意一个将返回true
3.查询分数比java班平均分高的学生 （班级表、学生表、分数表）
3.1查询java班的id
3.2根据班级id查询出所有的学生id
3.3根据学生id查询成绩列表
3.4使用聚合函数得到平均成绩
3.5查询成绩表，添加where条件>上一步得到的平均值
合并查询(union)
合并查询用于合并结果集相同的两张（多张）表，他有两个关键字：
union union all
例：
查询id小于2和名字为”英语“的课程
查询id<2:结果集1 +查询名称=‘英语’的课程：结果2
注意事项：union进行结果集的合并会去重
union all使用：
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果中的重复行。
union和union all 区别：
union会将1合并的结果集中的重复数据去重，只保留重复数据中的一条数据：而union all 是将结果集全部合并，即使有重复的数据结果也不会进行合并操作
