MySQL进阶-增删查改(全网最详细sql教学)-2
https://developer.aliyun.com/article/1517134
联合查询
下面创建和插入以下表格和数据:
drop table if exists classes; drop table if exists student; drop table if exists course; drop table if exists score; create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100)); create table student (id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20) , classes_id int); create table course(id int primary key auto_increment, name varchar(20)); create table score(score decimal(3, 1), student_id int, course_id int); insert into classes(name, `desc`) values ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'), ('中文系2019级3班','学习了中国传统文学'), ('自动化2019级5班','学习了机械自动化'); insert into student(sn, name, qq_mail, classes_id) values ('09982','黑旋风李逵','xuanfeng@qq.com',1), ('00835','菩提老祖',null,1), ('00391','白素贞',null,1), ('00031','许仙','xuxian@qq.com',1), ('00054','不想毕业',null,1), ('51234','好好说话','say@qq.com',2), ('83223','tellme',null,2), ('09527','老外学中文','foreigner@qq.com',2); insert into course(name) values ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文'); insert into score(score, student_id, course_id) values -- 黑旋风李逵 (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6), -- 菩提老祖 (60, 2, 1),(59.5, 2, 5), -- 白素贞 (33, 3, 1),(68, 3, 3),(99, 3, 5), -- 许仙 (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6), -- 不想毕业 (81, 5, 1),(37, 5, 5), -- 好好说话 (56, 6, 2),(43, 6, 4),(79, 6, 6), -- tellme (80, 7, 2),(92, 7, 6);
四张表的记录显示如下:
classes
student
course
各表之间的关联如下:
student和classes >一对多
student和course > 多对多关系
score相当于一张关联表, 起着关联student 和 course 的作用, 从图中可以明显看出来
我们需要实现以下功能:
查询"许仙"同学的成绩和其他相关信息
由于许仙同学的成绩和相关信息,存在于两张中表,就需要联合查询了, 首先我们需要score和student两张表进行笛卡尔积运算, 然后过滤掉无效信息
1.求两张表的笛卡尔积:
select * from student, score;
2.过滤无效数据:
去掉id 和student_id不匹配的情况,使用where语句
select * from student, score where student.id = score.student_id;
(此处通过表名.列名的形式来指定列, 因为如果两张表中有重名的列则会存在指定不明确的问题)
得到下表,一张学生id能匹配正确的表格.
在过滤掉无效信息后, 还需要指定为许仙同学的数据:
select * from student, score where student.id = score.student_id and name = "许仙";
于是就得到了我们所需要的许仙的成绩和相关信息表了.
也可以过滤掉除了name和score外的其他信息:
3.联合合并聚合查询
查询所有同学的总成绩, 及其个人信息
首先求笛卡尔积, 然后过滤掉无效数据
select * from student, score where student.id = score.student_id;
这个里面已经有了学神搞得各科成绩,但是我们需要的是总成绩 :
所以我们需要根据学生的名字或者学号进行分组(group by), 然后再针对分组进行求和:
select name, sum(score.score) from student, score where student.id = score.student_id group by student.id;
JOIN ON
有以下语句
select * from student, score;
此操作为获取student和score的笛卡尔积表, student和score中间用","逗号连接, 但是除了用逗号, 还可以使用关键字join, 也可以完成笛卡尔积表的操作:
select * from student join score;
不同于使用逗号获取的笛卡尔积表, join的条件筛选需要使用on关键字,而不是where
select student.name, score.score from student join score on student.id = score.student_id and student.name = "许仙";
以上这两种方法效果是一样的
内外连接
我们上面所说的例子, 都是两张表的记录都能一一对应, 但是在实际操作当中, 肯定存在数据量不一样的两张表, 或者是记录不能一一对应的两张表. 这种情况进行笛卡尔积, 没有一一对应的数据和其他数据进行笛卡尔积计算, 就必定只能产生无效数据,或者没有意义的数据, 这个时候不仅浪费空间,而且浪费性能,所以就要指定一个表为主的显示模式.
例如:
内连接
如果两张表的记录不一一对应, 这个时候使用内连接来连接两个表,两张表里面都有相互一一对应的记录的记录进行笛卡尔积.如 上图:将会忽略 student中id = 4, 和score表中student_id = 3 的记录.
内连接
select * from 表1, 表2 where 条件1 and 条件2;
语法如上
外连接
外连接分两种, 一种是左外连接, 一种是右外连接.顾名思义, 左外连接就是以左表为基础, 与表2进行笛卡尔积计算. 如果表1中的数据没有与表2中与之对应的, 那么这个数据任然后存在于生成的临时表, 对于找不到的字段记录, 一般使用NULL填写:
例如: 存在两张表
使用左连接来进行笛卡尔积:
select name, score from student left join score on student.id = score.student_id;
右连接同理:
select name, score from student right join score on student.id = score.student_id;
多表连接
使用join语法:
select * from 表1 join 表2 on 条件1 join 表3 on 条件2......;
执行逻辑为: 表1 先和 表2 进行笛卡尔积计算然后根据条件1筛选有效数据, 随后让表1和表2 生成的笛卡尔积表和表3进行笛卡尔积, 然后一句条件2进行有效数据的筛选. 根据情况进行左外连接或者右外连接或者是内连接
自连接
自连接是一种特殊情况的特殊操作, 如何理解? 也就是一张表, 自己和自己进行笛卡尔积表计算. 自己和自己笛卡尔积有什么用处呢??
首先存在一张表:
将这张表进行自连接:
select * from student, student;
如果直接这样写, 会出现warnings:
因为生成的临时表, 系统不知道对应的字段为那个表的,需要使用 as 来指定这两张表的别名加以区分, 同时过滤掉无效数据:
select * from student as student1, student as student2 where student1.id = student2.id;
此时 想要进行行与行之间的条件查询, 现在转到一个列来了
SQL中的条件查询, 都是指定列进行查询, 他没办法进行行与行之间的条件查询
子查询
多个查询语句合并成一个.
单行子查询
例如有student表, 如下:
想查询 许仙的同班同学, 该如何查询? 首先使用条件查询找到许仙所在的班级, 然后通过班级搜索在这个班级里面的同学:
select class_id from student where name = "许仙";
得知许仙同学的班级号为1后, 开始查看class_id 为1 的班级, 来搜寻他的同班同学:
select name, class_id from student where class_id = 1;
子查询该如何进行? 也就是将这个class_id = 1 的这个1 替换为上面第一个的查询语句:
select name, class_id from student where class_id = (select class_id from student where name = "许仙");
这种子查询虽然语言简便, 但是不方便理解, 当有多层次的子查询嵌套的时候, 非常影响SQL语句的可行性.
多行子查询
有以下两张表:
要求: 查询"语文" 和"英文"课程的成绩信息
- 首先查询英文和语文的课程id,
select id from course where name = "英文" or name = "语文";
2.根据课程id到student表中去查询成绩
select * from score where course_id in(4,6);
使用多行子查询, 合并为一句:
select * from score where course_id in (select id from course where name = "语文" or name = "英文");
具有相同的效果
合并查询
在实际操作当中, 为了合并多个select语句的执行结果,可以使用集合操作符union.
有如下表:
要求查询:课程id 小于3 或者课程名称为 英文的记录
- union
select * from course where id < 3 union select * from course where name = "英文";
- union all
union all 会去重
联合查询的一般步骤
- 分析清楚需求,所设计到的信息都在哪些表中
- 针对分析出来的这些表,来进行笛卡尔积
- 根据实际情况(关联字段), 筛选出有效数据
- 进一步加强条件查询, 查询更加精确的记录
- 针对需要对表进行简化
注意事项
在实际操作当中我们应该慎重使用
联合查询的基础是笛卡尔积,笛卡尔积是把两个表进行重组, 例如第一张表的记录量为20, 第二张表记录量为30, 那么这两张表格进行笛卡尔积计算, 那么新的笛卡尔积表的记录量就会达到20 x 30 = 600 条, 因为其中还包含了许多无效数据需要我们去主动过滤, 但是当数据量来到千万, 甚至几亿的级别的时候, 那么笛卡尔积的操作就会产生大量的数据, 这些数据可能是当前外存无法存储的.
数据量大的时候, 笛卡尔积的开销是非常大的, 会对机器的性能造成很大的影响.
