MySQL知识树-查询语句

时间:2023-12-30 10:11:50

在日常的web应用开发过程中,一般会涉及到数据库方面的操作,其中查询又是占绝大部分的。我们不仅要会写查询,最好能系统的学习下与查询相关的知识点,这篇随笔我们就来一起看看MySQL查询知识相关的树是什么样的。

MySQL查询知识树:

一、查询的方式

二、查询的原理

三、查询的应用场景

四、查询的效率比较

五、查询的优化

一、查询的方式

MySQL的查询可以分为交叉连接、内连接、外连接、自然连接

 

下述对于连接查询的学习,会用到以下两张表:

create table t_commodity_type(

`id` BIGINT(20) not null auto_increment comment '商品类别ID',

`time` TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP comment '入库时间',

`name` VARCHAR(32) not null DEFAULT '' comment '名称',

`is_use` bit(1) not null DEFAULT b'0' comment '是否上架',

primary key (`id`)

)engine=innodb DEFAULT CHARSET=utf8 comment '商品类型表';

create table t_commodity(

`id` BIGINT(20) not null auto_increment comment '商品ID',

`commodity_type_id` BIGINT(20) not null DEFAULT 0 COMMENT '商户所属类别ID',

`time` TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP comment '入库时间',

`name` varchar(64) not null DEFAULT '' comment '商品名称',

`price` DECIMAL(20,2) not null DEFAULT 0.00 comment '价格',

`is_use` bit(1) not null DEFAULT b'0' comment '是否上架',

PRIMARY key (`id`),

key `com_typ_id` (`commodity_type_id`) using BTREE

)engine=innodb DEFAULT charset=utf8 COMMENT '商品表';

CREATE TABLE `t_user_collect` (

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',

`user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户id',

`commodity_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商品id',

`name` varchar(64) NOT NULL DEFAULT '' COMMENT '商品名称',

`price` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '商品价格',

PRIMARY KEY (`id`),

KEY `index_user_id` (`user_id`) USING BTREE,

KEY `index_commodity_id` (`commodity_id`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户收藏表';

CREATE TABLE `t_user_order` (

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户订单id',

`user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户id',

`commodity_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商品id',

`name` varchar(64) NOT NULL DEFAULT '' COMMENT '商品名称',

`price` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '商品价格',

PRIMARY KEY (`id`),

KEY `index_user_id` (`user_id`) USING BTREE,

KEY `index_commodity_id` (`commodity_id`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户订单表';

①交叉连接(cross join)

对两表做笛卡尔积,若表A有m行数据,表B有n行数据,则cross join将返回m*n行数据。需要注意的是交叉连接本质就是想得到两表的笛卡尔积,虽然它也可以配合on子句来使用,但实际当我们拿到两表的笛卡尔积后再去使用on,则从行为上来说我们是想得到两表关联的数据,那么就符合了内连接(inner join)的使用条件,因此在这种情况下,我们更应该去选择内连接(inner join)。

那么cross join在实际中有哪些应用呢?例如快速生成测试数据,如下SQL:

insert into t_user_order(user_id,commodity_id,`name`,price)

select uc.user_id as user_id, c.id as commodity_id, uc.`name` as `name`, uc.price as price

from t_commodity c cross join t_user_collect uc;

额外知识点:

inner join后不跟on子句,也可以通过MySQL的语法解析,这时inner join就等价于cross join了,因为对于MySQL来说cross join和inner join就是同义词关系。

 

②内连接通过on子句来匹配两表的记录,查询出来的数据是两表的交集

拿上面两张表来举例子,从表结构上看t_commodity表通过commodity_type_id列和t_commodity_type表的id列产生了关联,我们通过一个SQL来表现内连接:

select c.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time

from t_commodity_type ct inner join t_commodity c on ct.id=c.commodity_type_id;

查询结果如下:

MySQL知识树-查询语句

以上SQL可以通过另外几种形式来实现,如下:

1)省略inner关键字的实现

select c.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time

from t_commodity_type ct join t_commodity c on ct.id=c.commodity_type_id;

2)不接on子句,通过逗号分隔表来实现

select c.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time

from t_commodity_type ct, t_commodity c where ct.id=c.commodity_type_id;

3)通过cross join和on子句来实现

select c.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time

from t_commodity_type ct cross join t_commodity c on ct.id=c.commodity_type_id;

额外知识点:

为什么可以通过第2)种形式来实现呢?

其实对于两表之间使用逗号分隔,且无on子句的SQL书写是ANSI SQL 89的规范,因为ANSI SQL 89在当时不支持join和on子句,而是直到ANSI SQL 92才引入的。MySQL对两者都是完全兼容的,两种书写方式无所谓好坏与否,因为优化器会为两者生成相同的执行计划,因此效率上、结果上是一致的,至于在实际项目中使用哪种则取决于你公司的SQL规范。

③外连接(outer join)有两种连接的方式,第一种是左外连接(left outer join),第二种是右外连接(right outer join)。

这种连接也是通过on子句来匹配两表的记录,但它与inner join不同的是,若是左外连接,则左表的记录会被全部获取,即便左表中的列无法通过on子句与右表发生匹配,这部分无法产生匹配关系但依然被获取出来的记录,我们称之为保留行

 

我们通过一个SQL来进行说明:

select ct.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time

from t_commodity_type ct left join t_commodity c on ct.id=c.commodity_type_id;

查询结果如下(只做了局部截图):

MySQL知识树-查询语句

这个查询通过t_commodity_type表的id和t_commodity表的commodity_type_id进行匹配,由于有些商品类型在商品表中无对应关系,但又需要保留这部分行显示出来,于是可以看到被筛选出的不属于t_commodity_type表的列值被填充为Null。

而对于右外连接,它的原理与左外连接一致,上面那个SQL用右外连接来实现就是:

select ct.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time

from t_commodity c right join t_commodity_type ct on c.commodity_type_id=ct.id;

 

可以看到我的左外连接和右外连接都没有加outer关键字,因为它是可以省略的,这使语法看起来更为简洁,而左外连接、右外连接也可以简称为左连接、右连接,但需要注意的是你得知道它俩都属于outer join。

另外outer join必须配合on子句来使用,否则MySQL会将其认定为语法错误。

④自然连接(natural join),它会将两表中具有相同名称的列进行匹配

我们以t_user_collect表和t_user_order表来做说明,t_user_collect表中的数据如下,

MySQL知识树-查询语句

t_user_order表的数据如下,

MySQL知识树-查询语句

对两表使用自然连接的SQL:select * from t_user_order natural join t_user_collect; 我们可以看到以下数据被筛选出来,

MySQL知识树-查询语句

怎么理解?由于两表的每列名称都相同,因此只有每列下的值都一致,才会匹配,而观察两表数据,仅有id为2的每列数据是一致的,假如我们将t_user_collect表中的几个列名称做修改,仅保留commodity_id,再看看会有怎样的匹配效果,修改后的t_user_collect表如下,

MySQL知识树-查询语句

我们再执行select * from t_user_order natural join t_user_collec; 会得到以下数据,

MySQL知识树-查询语句

可以看到有4行数据,因为commodity_id列在两表中是能产生4行数据匹配的,看到这里大家应该对natural join的使用效果较为明白了,接下来我们再探讨几个问题,

1)相同名称但数据类型不同的列会产生匹配吗?

2)是否能以其它的连接方式来实现natural join的效果?

关于第1)个问题,我们可以做如下实验,对t_user_collect表的commodity_id列修改其数据类型,再执行查询得到结果如下,

MySQL知识树-查询语句

我们可以看到查询出的4行结果与之前的4行结果一致,这说明同名称的列虽然数据类型不同,但不会对natural join的匹配产生影响。

关于第2)个问题,就拿刚刚那个查询SQL:select * from t_user_order natural join t_user_collec; 来说,它可以改写成如下形式:select * from t_user_order inner join t_user_collect using(commodity_id); 实际上natural join就等同于inner join和using的组合。

二、查询的原理

接下来我们来探讨一下查询的实现原理,首先我们要知道对于查询我们可以将其分为逻辑查询和物理查询,逻辑查询表示执行查询应该得到什么样的结果,可以理解为当我们在写出一个SQL时,我们对这个SQL所预期的结果;物理查询是指MySQL如何得到查询结果,即在执行SQL语句的过程中MySQL做了什么。

在这里我们需要了解下一个查询中可能包含有哪些内容:

1、select,2、distinct,3、join,4、on,5、from,6、where,7、having,8、group by,9、order by,10、limit

1)无论我们的查询是inner join还是left join,from操作总是最先执行的,form操作会得到关联表的笛卡尔积;

2)接着on操作会进行筛选,只有符合on后条件的记录才会被筛选出来;

3)join需要注意如果是left join或right join,则会保留表中未匹配的行(也称之为添加外部行),这是我们做两表联查时能理解的过程,其实做3表、5表联查过程也是一样的,将两表联查得到的记录与表3重复form、on、join的过程,完成后再对表4、表5,以此类推;

这里详细对多表联查的过程做下说明,假设我们联查表A、B、C、D

先是表A、B做笛卡尔积,即两表记录数相乘,然后通过on来筛掉不符合的记录,如果是left join或right join这种方式的联查,则还需要保留外部行,怎么理解保留外部行,就是保留表中被过滤条件过滤掉的数据,这样就得到了表A、B的联查数据,接下来再将得到的数据和表C做笛卡尔积,并重复这一过程,直到最后一张表完成这一过程,最后就得到了A、B、C、D四表联查的数据;

4)通过上述操作得到记录后,where会对记录进行过滤,只有满足where后条件的记录才会被筛选出来;

另外需要注意的是对于left join和right join的过滤,on过滤完之后还会添加保留表中被on条件过滤掉的记录,而被where条件过滤的记录则是永久过滤。

5)group by会对刚刚经过了where的记录进行分组;

6)having需要和group by配合使用,因为having使用的前提是group by已经对记录完成了分组,而having就是来对分组的记录再进行筛选,这里需要说明的是因为在分组前执行了where,因此若分组后的记录没有达到我们的预期,就需要使用having;

需要注意的是,若在left join和right join查询中,对select子句后使用count(1)或count(*),可能会把添加的行统计入内从而导致查询结果与预期结果不符合,对于这样的查询最好是count()具体列。

7)select在这里是将需要返回的列筛选出来,可以看到select的优先级并不高,是在最后几步才做的;

需要注意的是列的别名不能在select中的其他别名表达式中使用;

8)如果查询语句中带有了去重子句distinct,则会执行去掉重复记录的操作;

去掉重复记录的操作原理是对进行distinct操作的列增加一个唯一索引,如若SQL中使用了group by,则distinct是无效的,因为已经进行了分组,不会移除任何行。

9)接下来是order by,在我们得到了预期的记录后,就需要对记录进行排序,以方便阅读;

另外在order by中还可以指定select中列的序列号,通过指定序列号就能达到指定列的排序效果。

例如SQL:select id,commodity_type_id from t_commodity order by commodity_type_id,id;

等价于:select id,commodity_type_id from t_commodity order by 2,1;

需要注意的是若不使用order by则查询出来的数据是无序的,并非是按照主键有序排列,这是因为关系型数据库是基于数学来实现的,关系对应数学中的集合,集合本身是无序的,因此在不使用order by的情况下从集合中取数据无法保证是按顺序排列的。

还有在对列进行排序时,若列没有索引,则排序会造成一定的开销。

10)最后执行limit,拿到从指定位置开始(不包含指定位置)的指定行记录,limit常和order by一起使用,其使用方式是limit n,m,表示拿到从n行开始(不包含n行)的m行数据。

另外在大数据量下使用limit来分页效率是比较低的,因为需要在这么多数据量下去定位位置(即定位n),更好的解决方案是在应用层面使用缓存。

额外知识点:

在where子句后书写过滤条件时,有两种过滤情况是不允许发生的。

①数据没有分组前或者说在group by没有执行前,在where子句中不能使用分组函数,例如max()、min()、count()、sum()等,正因为这个限制的存在就能理解having子句存在的意义了,因为我们还存在对分组后的数据进行统计的需要;

错误的SQL:

select id from t_commodity c where count(commodity_type_id)>=5;

MySQL提示:

MySQL知识树-查询语句

②为select子句后的列取别名,并在where子句里直接使用列别名,是不被允许的。因为where的执行顺序是要高于select的,因为在列还没有被选取的情况下,就开始使用列别名明显是不行的。

错误的SQL:

select `name` as n from t_commodity where n='精进';

MySQL提示:

MySQL知识树-查询语句

额外知识点:

每张表的行中都有可能存在null值,但并不是每个null值都是一样的,或者说你将两个null值进行比较返回的会是0、1、null三种结果中的一种,毕竟关系型数据库中的null值比较和编程语言中的null值比较是不同的,但是有两种情况下我们可以认为两个null值是相等的:

①被group by子句分到同一组的null值;

②被order by子句排列在一起的null值,另外null值在order by中被认为是最小的,若按照升序排列则null值会排在最前面,例如我们将前面写的一个left join添加order by:

select ct.id as id, ct.`name` as type_name, c.`name` as `name`, c.time as time

from t_commodity_type ct left join t_commodity c on ct.id=c.commodity_type_id order by `name` asc;

得到如下结果:

MySQL知识树-查询语句

之前有提到查询分为逻辑查询和物理查询,虽然MySQL执行查询会遵从上面我们提到的步骤,但这并不是全部,因为MySQL除了通过SQL分析器完成对SQL语句的分析外,还会通过SQL优化器做SQL语句的优化,以最优的方式来选取数据,当然最终结果和逻辑查询是一致的。

因此上面所讲的步骤只是帮助我们去理解一个SQL语句的执行过程,在除了拿到我们所需要的数据外,我们也希望这个SQL的代价是非常低的,所以通过建立适当的索引会极大的提高查询效率,例如可以避免两表产生笛卡尔积,这是因为物理查询会根据索引来优化SQL的执行过程。

关于索引的详细讲解后面会写一篇“MySQL知识树--索引的设计和使用”的随笔。

--------------------未完,待更新--------------------