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@qq.com',1),(2,'CN002','李四','lisi@qq.com',2),(3,'CN003','王五','wangwu@qq.com',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 是将结果集全部合并，即使有重复的数据结果也不会进行合并操作