mysql-多表记录查询(六)

多表查询

• 基本查询语句：
  如果用户需要查询多张表中不同实体的数据，可以使用关键字JOIN对表执行连接查询操作，
  但前提条件是，这些表中必须存在具有相同意义的字段。

连接查询主要包括：内连接查询和外连接查询，

内连接查询

**内连接查询（inner join）**是使用比较运算符对多个表间的某些列数据进行比较，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。

表之间的连接条件由表中具有相同意义的字段组成。

1. 普通内连接查询

格式： 
select *|列名 from 表名1 inner join 表名2 on 连接条件;

例：
select id,name,num,add_time from goods 
inner join orders on goods.id=orders.goods_id;

另外，使用where子句也可以给出连接条件，以下语句将返回与前面完全相同的结果。

select id,name,num,add_time from goods,orders where goods.id=orders.goods_id;

缺陷：使用where子句定义连接条件简单明了，但在某些时候会影响查询性能，而使用inner join语法能够确保不会忘记连接条件。

注意: 使用符号“.”将表名和字段名拼接的作用是明确指定字段所属的数据表，
防止因为字段名重名而造成系统无法识别。

2. 自连接查询

格式：
select 表1名*,表2名* from 表名 as 表1名 inner join 表名 as 表2名 on 连接条件;

例：
select g1.id,g2.name,g1.num from goods as g1 inner join goods as g2 on g1.id=g2.id and g1.num>5;

外连接查询

**外连接查询（outer join）**是以一张表为基表，根据连接条件，与另外一张表的每一行进行匹配，如果没有匹配上，则在相关联的结果行中，另一张表的所有选择列均返回空值。

外连接查询通常分为两种：左连接查询（left join）和右连接查询（right join）。

格式：select *|列名 from 表1  left|right  [outer]  join  表2 on 连接条件;

添加知识：
关闭表外键约束， set foreign_key_checks=0;
开启表外键约束， set foreign_key_checks=1;

1. 左连接查询
   在外连接查询语句中，left join关键字之前的表称为左表，左连接查询会以左表为基表，与另外一张表的每一行进行匹配，
   如果符合连接条件，则返回两张表相对应的行；如果不符合，则只返回左表中的行，并且其对应的行为一个空值。

①先确定左表（题干要求查询的表）
②左表无null值
③left join关键字之前的为左表

格式：
select 左列*,另列* from 左表 left join 表名 on 连接条件;

例：
select id,name,o_id from goods left join orders on goods.id=orders.goods_id;

2. 右连接查询
   在外连接查询语句中，right join关键字之后的表称为右表，右连接查询会以右表为基表，与另外一张表的每一行进行匹配，
   如果符合连接条件，则返回两张表相对应的行；如果不符合，则只返回右表中的行，并且其对应的行为一个空值。

格式：
select 右列*,另列* from 表名 right join 右表 on 连接条件;

例： 
select o_id,add_time,name from goods right join orders on orders.goods_id=goods.id;

复合条件连接查询

复合条件连接查询是通过在连接查询中添加过滤条件，以达到限制查询结果和筛选数据的目的

格式：
select  *|列名  from  表名1  inner|left|right join 表名2 on 连接条件
where 条件... [group by ... [having ...]  order by ... limit ...];

例1：
select id,type,name,price,amount from good inner join orders1 
on good.id=orders1.gid where good.type='书籍';

例2：
select id,name,oid from good left join orders1 on good.id=orders1.gid
order by good.id;

子查询(嵌套查询)

如果一个查询语句中嵌套了一个或若干个其他的查询语句，那么在整个语句中，外层查询称为主查询，内层查询称为子查询或者嵌套查询。该类查询可以基于一个表或多个表。

在此类查询中，系统会先执行子查询，将子查询的结果作为主查询的过滤条件。

from 子句中的子查询

格式： 
select *|列名 from (select * from 表名) as 表别名 [where 条件];

例：
select id,name from (select * from goods where id>3) as g1 where num>1;

where 子句中的子查询

包含在WHERE子句中的子查询，其查询结果通常是单列数据，系统执行子查询后，子查询的结果会作为主查询的筛选条件

1. 使用IN关键字的子查询
   当子查询返回是一个数据集合，主查询需要返回符号集合

格式: 
select *|列名 from 表名1 where 字段名 in (select 表2字段 from 表名2 [where 条件]);

例1：
select * from goods where id in(select goods_id from orders);

例2：
select * from goods where id not in(select goods_id from orders);

2. 使用ANY、SOME关键字的子查询
   ANY和SOME是同义词，表示满足其中任一条件。
   该类查询会创建一个表达式对子查询的返回值列表进行比较，只要满足子查询中的任一个比较条件，就返回一个结果。

格式：
select *|列名 from 表名1 where 表1字段 < any(select 表2列名 from 表名2 [where 条件])；

使用“=any”与使用关键字in的效果实际上是相同的，除“=”外，any关键字前面可以使用的条件判断符还有“<”“>”“<=”和“>=”，读者可根据需要进行选择

例：
select goods_id from orders where add_time>'2020-8-7';

select * from goods where id=any(select goods_id from orders 
where add_time>'2020-8-7');

3. 使用ALL关键字的子查询
   使用关键字all的子查询，表示当一条记录符合子查询结果中所有的条件时，才会返回该记录。

格式：
select *|列名 from 表名1 where 表1字段 > all(select 表2列名 from 表名2 [where 条件]);

例：
select * from goods where id>all(select goods_id from orders where add_time<'2020-10-9');

all关键字之前可以使用的条件判断符有“<”“>”“<=”和“>=”，但一般不会使用“=”。

4. 使用EXISTS关键字的子查询 (判断语句是否为真)
   使用exists关键字，系统会对子查询的返回结果进行判断,
   如果子查询至少返回一行记录，那么exists的结果为true，此时主查询语句将会执行；
   如果子查询没有返回任何记录，那么exists的结果为false，此时主查询语句将不会执行。

格式：
select *|列名 from 表名1 where exists(select * from 表名2);

例：
select * from goods where id<5 and exists(select o_id from orders where goods_id=5);

5. 使用条件判断符的子查询
   在子查询中，还可以单独使用条件判断符

格式：select *|列名 from 表名1 where 表1字段 条件判断符 (select 表2列名 from 表名2);
例：select * from goods where id=(select goods_id from orders where o_id=3);

合并查询结果

合并查询结果就是使用union关键字，将多条查询语句的结果合并在一起显示。
union有两种使用方法，一种是查询结果不重复（过滤掉重复的记录），另一种是保留所有查询结果。

注意：使用该查询结果时，两个语句查询的字段数量必须相同，否则系统会报错。

使用 union 关键字的合并操作

单独使用UNION关键字的合并操作，查询结果集会合并在一起，并将重复的记录删除。

格式：
select *|列名 from 表名1 union select *|列名 from 表名2;

例：
select * from music union select * from dance;

使用union all 关键字的合并操作

使用union all关键字的合并操作，查询结果集会直接合并在一起，并不会删除重复记录。

格式：
select *|列名 from 表名1 union all select *|列名 from 表名2;

例：
select * from music union all select * from dance;