MySQL进阶-增删查改(全网最详细sql教学)-3

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL进阶-增删查改(全网最详细sql教学)

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);


四张表的记录显示如下:

6f575f44b5cc958a41d7b8c8d9d70b12_ed792c5b257e4756aae335dd0d464201.png


classes

7842f9b4ec11d43dfbdb5df3b21a270e_c60b111843124c10bd93c9ecec115d93.png


student


5bde346a92a9ab27ff11d11ef379dc1d_e5518300bdb048a3bb3af15cc384ddbb.png

course

4b4e9d0b5a94fc900c27e2663b379ef5_f35283a33e4b4d3d93a895afb411df91.png

各表之间的关联如下:

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;

882bbe8a1b998f9b52b43ddee32b4de9_3bf8529d46404550a29b6fb199c57737.png

这个里面已经有了学神搞得各科成绩,但是我们需要的是总成绩 :


190bdb985c9f7edebacb51c370988817_b70c64450fe34c54b8d66cb8cc06d869.png

所以我们需要根据学生的名字或者学号进行分组(group by), 然后再针对分组进行求和:


 
select name, sum(score.score) from student, score where student.id = score.student_id group by student.id;

818faea543c827cf0d0afd71f1a5019c_7c49a938ab5d4c2ab82c0d6cbf400797.png


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填写:


例如: 存在两张表

160fc9bd21deda357c2d28df301b63c9_bcc345b0835f47619b63970ec111de34.png

使用左连接来进行笛卡尔积:

 
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:

7a203f778406feaf9782a04f74061dce_7be8fa41b4fa4ca5bc9e1b3f595bf2fc.png


因为生成的临时表, 系统不知道对应的字段为那个表的,需要使用 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语句的可行性.

多行子查询

有以下两张表:


要求: 查询"语文" 和"英文"课程的成绩信息

  1. 首先查询英文和语文的课程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 会去重

联合查询的一般步骤
  1. 分析清楚需求,所设计到的信息都在哪些表中
  2. 针对分析出来的这些表,来进行笛卡尔积
  3. 根据实际情况(关联字段), 筛选出有效数据
  4. 进一步加强条件查询, 查询更加精确的记录
  5. 针对需要对表进行简化
注意事项

在实际操作当中我们应该慎重使用


联合查询的基础是笛卡尔积,笛卡尔积是把两个表进行重组, 例如第一张表的记录量为20, 第二张表记录量为30, 那么这两张表格进行笛卡尔积计算, 那么新的笛卡尔积表的记录量就会达到20 x 30 = 600 条, 因为其中还包含了许多无效数据需要我们去主动过滤, 但是当数据量来到千万, 甚至几亿的级别的时候, 那么笛卡尔积的操作就会产生大量的数据, 这些数据可能是当前外存无法存储的.


数据量大的时候, 笛卡尔积的开销是非常大的, 会对机器的性能造成很大的影响.


b6d32dcd6b96643435b92265a5d5fc65_61438a77a2b147dda0163d27843618bb.jpeg

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
|
5天前
|
SQL 关系型数据库 MySQL
MySQL数据库进阶第五篇(锁)
MySQL数据库进阶第五篇(锁)
|
5天前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第三篇(MySQL性能优化)
MySQL数据库进阶第三篇(MySQL性能优化)
|
5天前
|
SQL 关系型数据库 MySQL
MySQL数据库基础第一篇(SQL通用语法与分类)
MySQL数据库基础第一篇(SQL通用语法与分类)
|
5天前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
5天前
|
SQL 存储 关系型数据库
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
|
5天前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
|
2天前
|
SQL 关系型数据库 MySQL
MySQL数据库数据模型概念入门及基础的SQL语句2024
MySQL数据库数据模型概念入门及基础的SQL语句2024
7 0
|
1天前
|
存储 关系型数据库 MySQL
|
1天前
|
SQL 关系型数据库 MySQL