MySQL连接查询

_小菜鸟
155 阅读4分钟

image.png image.png

创建表

create table student(
uid int unsigned primary key not null auto_increment,
name varchar(50) not null,
age tinyint unsigned not null,
sex enum('W','M') not null);

create table cource(
cid int unsigned primary key not null auto_increment,
cname varchar(50) not null,
credit tinyint unsigned not null);

create table exame(
uid int unsigned not null,
cid int unsigned not null,
time date not null,
score float not null,
primary key(uid,cid));

插入数据

insert into student(name,age,sex) values
('zhangsan',18,'M'),
('gaoyang',20,'W'),
('chenwei',22,'M'),
('linfeng',21,'W'),
('liuxiang',19,'W');

insert into cource(cname,credit) values
('C++基础课程',5),
('C++高级课程',10),
('C++项目开发',8),
('C++算法课程',12);

insert into exame(uid,cid,time,score) values
(1,1,'2021-04-09',99.0),
(1,2,'2021-04-10',80.0),
(2,2,'2021-04-10',90.0),
(2,3,'2021-04-12',85.0),
(3,1,'2021-04-09',56.0),
(3,2,'2021-04-10',93.0),
(3,3,'2021-04-12',89.0),
(3,4,'2021-04-11',100.0),
(4,4,'2021-04-11',99.0),
(5,2,'2021-04-10',59.0),
(5,3,'2021-04-12',94.0),
(5,4,'2021-04-11',95.0);

一、内连接查询

join默认是inner join内连接

  1. 要想知道学生的基本信息和成绩

image.png 要把上面这两个表一次做一个查询，而不是两个SQL分别查询，性能会比较低。两张表放到一个SQL里面查，首先需要区分表，所以要把两个表重命名使用。

image.png 然后合并成一个语句进行查询，连接时会区分大表和小表，按数据量来区分，小表永远是整表扫描，然后去大表搜索，所以小表建索引好。

image.png 查询结果

image.png 强调加where过滤条件后大小表是哪个就不一定，因为先要执行where条件，执行后在满足条件的结果条数确定大小表。

image.png

image.png 对于inner join内连接，过滤条件写在where的后面和on连接条件里，效果是一样的。

  1. 对student和cource表进行合并查询，因为这两个表没有关系，所以还需要通过exame来连接。

image.png 查询结果

image.png

image.png

image.png

image.png

image.png

image.png

image.png 我们前一篇博客也说了limit偏移m，这个效率是很低的，我们一般通过where加索引把这个偏移m的性能消耗去掉，但有的时候没有办法加，如何优化？首先要明确一个前提就是对于选择一个字段和多个字段它的效率是不一样的。如果说就是想选很多字段，还想让花费的时间和选一个字段差不多，怎么办？

  • 第一种办法 通过对索引字段的这个列加where进行索引数的快速过滤 image.png
  • 第二种办法 有的时候不知道这个id值没有办法用第一种方法，但还是需要得到很多字段。 使用内连接优化sql语句 image.png 我们之前进行连接的时候连接的都是具体的表，现在没有第二张表，那连接的这张表从哪来的，还是从t_user里来的，只不过先把它查了一遍，然后生成了一张临时表来存储，括号里对表的查询是相当于小表（因为这个只取了10行），而且这个小表选择一个字段并且带有索引，效率还是不错的，找出来以后这里的id在大表进行查询，因为id是有索引的id是主键，索引都是常量时间，所以这个sql语句不仅可以得到更多字段数据，而且效率也可以达到和查询一个id字段一样。 image.png 用explain查看sql的执行计划 image.png

二、外连接查询

1. 左连接查询

image.png 把left这边的表所有的数据显示出来，在有表中不存在相应数据，则显示NULL。

image.png

2. 右连接查询

image.png 把right这边的表所有的数据显示出来，在左表中不存在的数据，则显示NULL。

image.png

三、外连接的应用

外连接和内连接的区别 image.png

在有些数据不存在的场景会用到带in的子查询来完成，in会用到索引，但是not in是否会用到索引和select选择的列有关系，但是我们很难在任何情况在都知道not in能不能用到索引，对于索引命中率不高，并且not in会产生一张中间表存储结果供外面的sql来查询，中间表也不一定会产生，因为Mysql Server都会做优化的。我们一般都推荐外连接来完成，因为它可以省略中间表的生成，效率高。

查询哪些同学没有参加过考试（分别用带in子查询和外连接） image.png 没有参加过课程号cid=3考试的同学的信息，

image.png 我们用explain查看一下

image.png 所以我们在写外连接语句，想查这种不存在的场景，还带有一定的限制条件的话，限制条件都用and加载on连接条件后面，where的过滤条件只写null值判断就行

avatar
文章
阅读
粉丝