MySQL概述四（exists+关联查询）

一（exists）

exists 用在子查询中
select ... from 表名 where exists(子查询)
子查询有返回结果:exists 子查询结果为True,就执行外层的sql语句
子查询没有返回结果:exists 子查询结果为False,就不会执行外层的sql语句

eg：查询出英雄数据,如果有等级大于10的英雄,显示前3条数据,如果没有就不显示
-- 1. 是否有等级大于10 的英雄
select level from t_hero where level >10;
-- 2. 显示前三条数据
select level from t_hero where level >10 limit 3;

select * from t_hero where exists(select level from t_hero where level >10)  limit 3;

-- 只显示 等级大于10的前三条数据
select * from t_hero where exists(select level from t_hero where level >15) and level>10 limit 3;

此时不会返回任何结果，因为子查询中的SQL语句执行返回false，所以外层SQL语句也不会执行。

二（关联查询）

将多个表联合起来进行查询，主要有内连接、外连接（全连接）、左（外）连接、右（外）连接。

多表查询时:select * from 表名…… ,* 代表查询出所有表里面的所有字段

1.笛卡尔积查询：查询的结果是两张表相乘的结果

如果左边表有m条数据，右边表有n条数据，则查询会得到m*n条数据

因笛卡尔积查询没有考虑两张表的对应关系，所以结果中包含大量无用的数据，无法直接使用。

-- 数据准备： 

-- 英雄表
create table t_hero(
            id int(3) primary key auto_increment,   -- 英雄编号
            name varchar(30),    -- 英雄名称
            addTime date,  -- 上架时间
            classId int(3),  -- 英雄类型编号
            growth double(5,2), -- 成长值
            level int   -- 等级
);


-- 英雄类型表
create table t_mode(
            classId int(3),  -- 类型编号
            name varchar(30)  -- 类型名称
);

解决方案一: 增加判断条件
select * from t_hero cross join t_mode where t_hero.classId = t_mode.classId;

eg：查询出英雄id,英雄的名字,英雄的分类名称

select t_hero.id,t_hero.name,t_mode.name from t_hero cross join t_mode where t_hero.classId = t_mode.classId;

给表取别名的方式

select th.id,th.name,tm.name from t_hero th cross join t_mode tm where th.classId = tm.classId;

select th.id '英雄ID',th.name '英雄名字' ,tm.name '英雄的分类' from t_hero th cross join t_mode tm where th.classId = tm.classId;

解决方案二: 增加 join on 关键字
join表示需要加入什么表
on 表示通过哪个字段,让两个表关联到一起
2.内连接的方式查询：

select th.id '英雄ID',th.name '英雄名字' ,tm.name '英雄的分类'from t_hero th inner join t_mode tm on th.classId = tm.classId;

inner 通常省略不写

select th.id '英雄ID',th.name '英雄名字' ,tm.name '英雄的分类' from t_hero th  join t_mode tm on th.classId = tm.classId;

-- 插一条数据,不写英雄的分类
insert into t_hero (name,addTime,level) values ('颇颇公主','2022-01-06',11);
select * from  t_hero;
select th.id '英雄ID',th.name '英雄名字' ,tm.name '英雄的分类'
    from t_hero th inner join t_mode tm on th.classId = tm.classId;

-- 插入一条英雄分类数据
insert into t_mode values (107,'全能');
select * from t_mode;
select th.id '英雄ID',th.name '英雄名字' ,tm.name '英雄的分类' from t_hero th inner join t_mode tm on th.classId = tm.classId;

总结：内连接获取的是两张表中的公共数据,如果和另外一张表没有关联的数据时,这条数据不会被查出.

where 和 join on的区别
1. from 语句把t_hero表和t_mode表从数据库文件中加载到内存中
2. join on 语句相当于对于两张表做了乘法运算,会将t_hero 中的数据按照顺序和t_mode中的数据依次对应

where 如果两张表的数据都比较大的话,那样就会占用很大的内存空间,显然where是不合理，所以,我们在进行表连接的时候一般会使用 join on的语法
on语句的执行是在join语句之前,也就是两张表数据之间进行匹配的时候执行。会先判断数据行是否符合on语句后面的条件,再决定是否join

关联查询
内连接 : inner join on

外连接:
           左外连接: left outer join on  以左边的表为驱动表(参照物),右边的表为从表,左侧的表数据都会全部显示出来，如果从表中没有数据和驱动表匹配的话,此时会显示null

            右外连接: right outer join on 以右边的表为驱动表,左边的表为从表,右侧的表数据都会全部显示出来，如果从表中没有数据和驱动表匹配的话,此时会显示null

            全外连接: full outer join on (oralce 数据库的方式) mysql 不支持
      union 或者 union all

 

eg：查询出所有英雄的编号,名称,以及分类名称,包括没有分类的英雄数据

select th.id '英雄ID',th.name '英雄名字' ,tm.name '英雄的分类' from t_hero th left outer join t_mode tm on th.classId = tm.classId;

outer 是可以省略的

select th.id '英雄ID',th.name '英雄名字' ,tm.name '英雄的分类' from t_hero th left  join t_mode tm on th.classId = tm.classId;

eg：查询出所有的英雄的分类,包括没有英雄的分类

select th.id '英雄ID',th.name '英雄名字' ,tm.classId '分类编号',tm.name '英雄的分类' from t_hero th right  join t_mode tm on th.classId = tm.classId;

eg：查询出所有永英雄的数据,包括没有分类的英雄,以及没有英雄的英雄分类
-- oracle(写法)
-- select th.id '英雄ID',th.name '英雄名字' ,tm.classId '分类编号',tm.name '英雄的分类'
--    from t_hero th full outer  join t_mode tm on th.classId = tm.classId;

全外连接
左外连接= 左表全部数据+相关的关联结果
右外连接= 右表全部数据+相关的关联结果
全外连接= 左表全部数据+右表全部数据+相关的关联结果 = 左外连接+右外连接-相关的关联结果
简单理解:全外连接的数据就是左外连接+右外连接然后去重之后的数据

select th.id '英雄ID',th.name '英雄名字' ,tm.name '英雄的分类' from t_hero th left  join t_mode tm on th.classId = tm.classId union select th.id '英雄ID',th.name '英雄名字' ,tm.name '英雄的分类' from t_hero th right  join t_mode tm on th.classId = tm.classId;

select th.id '英雄ID',th.name '英雄名字' ,tm.name '英雄的分类' from t_hero th left  join t_mode tm on th.classId = tm.classId union all select th.id '英雄ID',th.name '英雄名字' ,tm.name '英雄的分类' from t_hero th right  join t_mode tm on th.classId = tm.classId;

union 和 union all 的区别：
union all 只是简单的将两个结果合并后返回，这样,如果返回的两个结果中有重复数据，那么最终返回的结果集就会包含重复的数据。
union 会对两个结果集进行合并然后去除重复的数据