mysql连接查询、联合查询、子查询以及执行explain后的type、key、rows

最新推荐文章于 2022-11-29 14:26:22 发布

向上吧，少年！

最新推荐文章于 2022-11-29 14:26:22 发布

阅读量501

点赞数

分类专栏：数据库相关

本文链接：https://blog.csdn.net/m0_38045306/article/details/115183261

版权

数据库相关专栏收录该内容

4 篇文章 0 订阅

订阅专栏

mysql连接查询、联合查询、子查询

来源：https://blog.csdn.net/weixin_39411321/article/details/90602030

【1】连接查询
连接查询的意义: 在用户查看数据的时候,需要显示的数据来自多张表.
内连接查询
基本语法：左表 [inner] join 右表 on 左表.字段 = 右表.字段;
内连接可以没有连接条件: 没有on之后的内容,这个时候系统会保留所有结果。
外连接查询
以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接: 不管能不能匹配上条件,最终都会保留: 能匹配,正确保留; 不能匹配,其他表的字段都置空NULL。
基本语法: from 左表 left join 右表 on 左表.字段 = 右表.字段;
左表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,右表的字段都置NULL。
right join: 右外连接(右连接), 以右表为主表
基本语法: from 左表 right join 右表 on 左表.字段 = 右表.字段;
右表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,左表的字段都置NULL。
【2】联合查询
联合查询
联合查询结果是将多个select语句的查询结果合并到一块因为在某种情况下需要将几个select语句查询的结果合并起来显示。
比如现在需要查询两个公司的所有员工的信息，这就需要从甲公司查询所有员工信息，再从乙公司查询所有的员工信息，然后将两次的查询结果进行合并。
可以使用union和union all关键字进行操作
语法格式如下:
select 语句1
union[union 选项]
select 语句2
union|[union 选项]
select 语句n
其中union选项有两个选项可选
all：表示无论重复都输出
distinct: 去重(整个重复)（默认的）

select *from addr
union all
select *from addr;

select id,addrid
from addr
union all
select id,addrid
from student;

联合查询order by的使用
在联合查询中: order by不能直接出现在union的子句中，但是可以出现在子句的子句中。
select *from
(select *from student
where sex="woman"
order by score)student
union
select *from
(select *from student
where sex="man"
order by score)student ;
【3】子查询
通常我们在查询的SQL中嵌套查询，称为子查询。
子查询通常会使复杂的查询变得简单，但是相关的子查询要对基础表的每一条数据都进行子查询的动作，所以当表单中数据过大时，一定要慎重选择。
带in关键字的子查询
使用in关键字可以将原表中特定列的值与子查询返回的结果集中的值进行比较
如果某行的特定列的值存在，则在select语句的查询结果中就包含这一行。
例:查询成绩大于80的学生的所有信息，
先在子查询中查出成绩大于80的结果集，然后将原成绩表中的成绩与结果集进行比较，如果存在，就输出这条学生的记录。

select *
from student
where score in
(select score from student where score>80);

带比较运算符的子查询

如果可以确认子查询返回的结果只包含一个单值，那么可以直接使用比较运算符连接子查询。
经常使用的比较运算符包括等于（=）、不等于（<>或!=）、小于（<）、大于（>）、小于等于（<=）和大于等于（>=）。
select *
from student
where score>
(select score
from scholarship
where dengji=1);
查询奖学金等级为1的学生信息

带exists的子查询

exists: 是否存在的意思, exists子查询就是用来判断某些条件是否满足(跨表),
exists是接在where之后
exists返回的结果只有0和1.

例：如果存在成绩大于90的人则列出整个表的记录

select *
from student
where exists
(select *from student where score>90);

带any关键字的子查询

any关键字表示满足其中的任意一个条件，使用any关键字时，只要满足内层查询语句结果的的任意一个，就可以通过该条件来执行外层查询语句。

select *
from student
where addrid<any
(select addrid
from addr);

带all关键字的子查询

all和any刚好是相反的，all关键字表示满足所有结果，使用all关键字，要满足内层查询语句的所有结果，才可以通过该条件来执行外层查询语句。

select *
from student
where addrid>all
(select addrid
from addr);

执行explain后的type、key、rows

来源：https://blog.csdn.net/dennis211/article/details/78170079

CREATE TABLE IF NOT EXISTS `student`(
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
`age` INT NOT NULL,
`gender` VARCHAR(1),
PRIMARY KEY ( `id` )
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO student VALUES (1,"小花",16,"男");
INSERT INTO student VALUES (2,"小名",16,"男");

EXPLAIN SELECT * FROM student WHERE age=16;

CREATE INDEX ageIndex ON student (age);

在没加索引之前，type为all，加了之后type为ref。

explain返回的结果集中key为ageIndex，key表明的是这次查找中所用到的索引。
explain返回的结果集中rows为2，rows是指这次查找数据所扫描的行数（这里可以先这样理解，但实际上是内循环的次数）。
而type则是本文要详细记录的连接类型，前两项重要而且简单，无需多说。

EXPLAIN SELECT * FROM student WHERE id=2;
type为const，key为PRIMARY，rows为1

mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all,index,range,ref,eq_ref，const。
从左到右，它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠右，但实际运用中还是要综合考虑各个方面的。

all
这便是所谓的“全表扫描”。
explain select * from employee where `no` = '20150001';
如果no列既不是主键也不是索引，只能采用全表扫描来查找目标no。

index
这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。
PRIMARY KEY (`rec_id`)
explain select * from employee order by rec_id ;
explain select rec_id from employee ;
执行上面两个语句，type均为index。

range
range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。
同时除了显而易见的between，and以及'>','<'外，in和or也是索引范围扫描。

ref
出现该连接类型的条件是：查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，
仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

eq_ref
eq_ref 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，
在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，
因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。

const
通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。