MYSQL学习笔记08:联合查询union,连接查询[交叉连接,内连接,左外连接,右外连接,自然连接],子查询[标量子查询,列子查询,表子查询,exist子查询],in,any,some,all

联合查询union,连接查询[交叉连接,内连接,左外连接,右外连接,自然连接],子查询[标量子查询,列子查询,表子查询,exist子查询],in,any,some,all

联合查询

基本概念:联合查询是可合并多个相似的选择查询的结果.等同于将一个表追加到另一个表,从而实现将两个表的查询组合到一起,使用谓词为union或union all

联合查询:将多个查询的结果合并到一起(纵向合并):字段数不变,多个查询的记录数合并.

应用场景

1. 将同一张表中不同的结果(需要对应多条查询语句来实现),合并到一起展示数据.

例如:男生身高升序排序,女生身高降序排序

2. 最常见:在数据量大的情况下,会对表进行分表操作.需要对每张分表进行部分数据统计,使用联合查询来将数据存放到一起显示.

例如:qq1表获取在线数据;qq2表获取在线数据...==>将所有在线的数据显示出来.

基本语法:

select 语句1

union [union选项]   -- union选项:1.distinct去重,去除完全重复的数据(为默认选项);2.all:保留所有数据.

select 语句2;

如上,union选项不指定默认即为去重,我们看看不去重的效果,如下:

如上,不去重则保留所有数据.

注意:union理论上只要保证字段数一样,不需要每次拿到的数据对应的字段类型一致.系统永远只保留第一条select语句对应的字段名字。

如上,所以我们不这样使用union.因为字段和数据不一致,这样就没有意义.应避免这种情况.

order by的使用

1. 在联合查询中,如果要使用order by,那么对应的select语句必须使用括号括起来.

实例:男生身高升序排序,女生身高降序排序

如上,使用union进行联合查询,出错!为什么会出错呢?因为没有加括号.

加上括号语句正常运行，如下：

但是order by规定的升序或降序排序并没有生效,这是为什么呢?

2. order by在联合查询中若要生效,必须配合使用limit: 而limit后面必须跟对应的限制数量(通常可以使用1个较大的值:大于对应表的记录数)

如上,我们加了limit 10,order by语句生效了,排序正常.

连接查询

连接查询:将多张表liandao连到一起进行查询(会导致记录数行和字段数列发生改变)

连接查询的意义:

在关系型数据库设计过程中,实体(表)和实体之间是存在很多联系的.在关系型数据库表的设计过程中,遵循着关系来设计:1对1,1对多,多对多.通常在实际操作的过程中,需要利用这层关系来保证数据的完整性.

连接查询的分类

交叉连接.

内连接.

外连接:分为左外连接(左连接),右外连接(右连接).

自然连接.

交叉连接

将两张表的数据与另外一张表彼此交叉

原理

1. 从第一张表依次取出每一条记录,

2. 取出每一条记录之后,与另外一张表的全部记录挨个匹配

3. 没有任何匹配条件,所有的结果都会进行保留

4. 记录数=第一张表记录数x第二张表记录数;字段数=第一张表字段数+第二张表字段数(笛卡尔积)

//前面说过,笛卡尔积没有意义,应该避免使用

语法:

表1 cross join 表2;

如上,交叉连接产生49条记录(7x7),14条字段(8+6)

应用:

交叉连接产生的结果是笛卡尔积,没有实际应用.

本质:from 表1,表2;

结论:不要使用交叉连接产生笛卡尔积

内连接

内连接:inner join,从一张表中取出所有记录去另外一张表中匹配:利用匹配条件进行匹配.成功则保留;失败则放弃.

原理

1. 从第一张表中取出1条记录,然后去另外一张表中去匹配

2. 利用匹配条件匹配:

2.1 匹配到:保留,继续向下匹配

2.2 匹配失败:向下继续.如果全表匹配失败,结束.

语法:

表1 [inner] join 表2 on 匹配条件;

1. 如果内连接没有匹配条件(允许),那么其实就是交叉连接(应避免).

如上,有学生表和班级表.

 

如上,内连接两张表.由于没有匹配条件,则返回笛卡尔积.(没有意义)

2.使用匹配条件进行匹配

如上,匹配条件为学生表中学生所属的class_id =班级表中的id(班级id),满足则保留该记录.不满足则舍弃.

3. 因为表的设计通常容易产生同名字段,尤其是id,所以为了避免重名出现错误,通常使用表名.字段名来确保唯一性.

如上,虽然结果和刚才所举例子结果一样,但是使用表名.字段名安全清晰易读无误.

由于可能会有两张表字段名重复(id),这时候使用表名.字段名是必须的选择.

4. 如果条件中使用到对应的表名,而表名比较长,可以使用表别名.

5. 内连接匹配的时候,必须保证匹配到才会保存

如上的例子班级表中还有3班,由于匹配不到,3班被舍弃.

6.内连接因为不强制使用匹配条件(on),因此可以在数据匹配完成之后,使用where条件来限定,效果与on一样.(建议使用on) 因为用where等于先交叉连接(笛卡尔积)再用where筛选,浪费.

如上,用where虽然看起来效果和用on一样,但是浪费性能,应避免.

应用

内连接通常是在对数据有精确要求的地方使用:必须保证两张表中都能进行数据匹配

外连接

outer join:按照某一张表作为主表(表中所有记录在最后都会保留),根据条件去连接另外一张表,从而得到目标数据.

外连接分两种:左外连接left join;右外连接right join

左连接:左表是主表.

右连接:右表是主表.

原理

1. 确定连接主表是左表还是右表;

2. 拿主表的每一条记录,去匹配另外一张表(从表)的每一条记录;

3. 如果满足匹配条件,保留;不满足则舍弃;

4. 如果主表记录在从表中一条都没有匹配成功,那么也要保留该记录:从表对应的字段值为null

语法:

左连接:主表 left join 从表 on 连接条件;

右连接:从表 right join主表 on 连接条件;

左连接对应的主表数据在左边;右连接对应的主表数据在右边.

以上是左连接,以下是右连接:

如上,右连接,班级表为主表.班级表里有3班,但是由于从表学生表中没有匹配到数据(没有3班的学生),故而对应数据为null

特点:

1. 外连接中主表数据记录一定会保存:连接后不会出现记录数少于主表(内连接有这种可能);

2. 左连接和右连接可以互相转换,但是数据对应的位置(表顺序)会改变

上例来自https://www.bilibili.com/video/av30169480/?p=72。

注意：on后使用and附加条件,理论还是属于外连接部分,因此一定会保留主表数据;如果使用where是针对外连接结果再进行筛选.

全外连接:

来自https://www.bilibili.com/video/av30169480/?p=72。

full outer join:mysql不支持.即没有主表从表关系,匹配不上的都会保留,如果对应的数据不存在置空null保留.

外连接应用

非常常用的一种获取数据的方式:作为数据获取对应主表以及其他数据(关联)

外连接总结

自然连接

来自https://www.bilibili.com/video/av30169480/?p=73

定义:自然连接是一种系统自动设定匹配条件的连接方式,匹配的原理是两张表的同名字段.

1.自然连接不是一种特定的连接方式,而是一种自动匹配条件的连接方式,自然连接支持内连接和外连接

自然内连接:natural join

自然外连接:natural left/right join

如上,使用自然连接匹配,匹配不上.两张表id字段的值有相同,name字段的值完全不同,匹配不上

2. 自然连接是自动匹配,匹配逻辑是所有同名字段,因此如果要使用自然连接必须让表非常规范

注意:自然连接会将同名字段只保留1个.(即只保留一个同名字段)

3.其实内连接和外连接也可以达到同样的自然连接效果(保留1个列),这个时候需要使用using关键字

总结

1. 自然连接是一种自动匹配连接条件的连接方式,匹配逻辑是同名字段;

2. 自然连接是实际开发中需要使用的话,要保证字段设计的规范,即有关系的字段同名

3.内连接和外连接可以通过使用using关键字代替on来实现自然连接效果.

using关键字

是在连接查询中用来代替对应的on关键字的,进行条件匹配.

原理:

1. 在连接查询时,使用on的地方用using代替.

2. 使用using的前提是对应的两张表连接的字段是同名(类似自然连接自动匹配)

3. 如果使用using关键字,那么对应的同名字段,最终只会结果中保留1个.

语法:

基本语法:表1 [inner, left, right] join 表2 using(同名字段列表);//连接字段

子查询

什么是子查询

子查询概念

子查询:sub query

子查询是一种计算机语言 select-sql语言中嵌套查询下层的程序模块.当一个查询是另一个查询的条件时,称之为子查询.

子查询:在一条select语句中,嵌入了另外一条select语句,那么被嵌入的select语句成为子查询语句.

主查询概念

主查询:主要的查询对象,第一条select语句,确定的用户所有获取的数据目标(数据源),已经要具体得到的字段信息

子查询和主查询关系

1.子查询是嵌入到主查询中的

2. 主查询辅助主查询:要么作为条件,要么作为数据源

3. 子查询其实可以独立存在:是一条完整的select语句

子查询分类

按功能分

标量子查询:子查询返回的结果是一个数据(1行1列)

列子查询:返回的结果是一列(1列多行)

行子查询:返回的结果是一行(1行多列)

表子查询:返回的结果是多行多列(多行多列)

exist子查询:返回的结果是1或0(类似布尔操作)

按位置划分

where子查询:子查询出现在where条件中

from子查询:子查询出现的位置在from数据源中(做数据源)

标量子查询

概念:

标量子查询:子查询得到的结果是1个数据(1行1列)

语法:

基本语法:select * from 数据源 where 条件判断 =或<>(select 字段名 from 数据源 where 条件判断);//子查询得到的结果只有1个值

列子查询

概念:

列子查询:列子查询得到的结果是一列数据(一列多行)

语法:

基本语法: 主查询 where 条件 in (列子查询);

以上是班级表和学生表.明显3班没有学生.如下使用列子查询,果然3班不在列:

行子查询

概念

行子查询:行子查询返回的结果是一行多列

行元素:

字段元素是指一个字段对应的值,那么行元素对应的就是多个字段:多个字段合起来作为一个元素参与运算,把这种情况称之为行元素.

语法:

基本语法: 主查询 where 条件 [(构造一个行元素)]=(行子查询);

如上，只能使用构造行元素,而不能使用having/order by语句.注意:是替代不了的.

总结:

已经学过3个子查询.标量子查询,列子查询,行子查询都属于where子查询.

表子查询

概念:

表子查询:子查询返回结果是多行多列,表子查询与行子查询非常相似.只是行子查询需要产生行元素,而表子查询没有.

行子查询是用于where条件判断:where子查询

表子查询是用于from数据源:from子查询

语法

select 字段表 from (表子查询)as 别名 [where] [group by] [having] [order by] [limit];

exist子查询

概念:exist子查询查询的结果只有0或1.1代表成立,0代表不成立.

语法: where exist (查询语句);//exist就是根据查询得到的结果进行判断:如果存在返回1,否则返回0

注意mysql中where 1:永远为真

子查询中特定关键字的使用

in:

主查询 where 条件 in (列子查询);

any:

任意一个

  =any(列子查询):条件在查询结果中有任意一个匹配即可,等价于in

<>any(列子查询):条件在查询结果中不等于任意1个.

1 = any(1,2,3)   ====>true

1 <> any(1,2,3)   ====>true //1不等于1,结果为假;1不等于2,结果为真;1不等于3,结果为真==>任意结果为真,总结果为真.

↑请一定要好好理解<>any的逻辑方式↑

不妨这样理解: 张 不等于 (张,王,李)之一;//结果为真

some:

与any完全一样:英文中some与any正面含义一致,但是否定就大不相同:not any与not some不同

开发者为了让使用者不要在语法上纠结:重新设计了some

all:

全部

=all(列子查询):等于里面所有

<>all(列子查询):不等于里面所有

如上,正面意义上in和any等价.

如上,演示<>any.

为什么结果和any一致?此处须深刻理解<>any的含义:(学生表中学生班级id为1,2;班级表中班级id为1,2,3)

执行select学生表时,判断 当前学生class_id(1或2) 不等于 所有class_id(1,2,3)之一;//true

 

如上,是all的示例(学生表中学生班级id为1,2;班级表中班级id为1,2,3)

任何一个学生表中学生的班级id(1或2)等于班级表里面所有(班级id1,2,3) =>不成立.

任何一个学生表中学生的班级id(1或2)不等于班级表里面所有(班级id1,2,3)=> 不成立.

反过来演示all的示例.(学生表中学生班级id为1,2;班级表中班级id为1,2,3)

如果对应的匹配字段有null,则不参与匹配.

我们把学生7班级id改为null,执行any匹配,发现null不参与匹配.