1.前置知识—笛卡尔积
实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积。
笛卡尔积是联合查询也就是多表查询的基础。
笛卡尔积又称直积,表示XY,比如A表中的数据有m行,B表中的数据有n行,那么A和B做笛卡尔积,结果为mn行
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/b4853b41ba473577172f8821ae2cf471.png)
创建一个数据库和表:
-- 创建数据库
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 是将结果集全部合并,即使有重复的数据结果也不会进行合并操作