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

时间:2019-05-14 14:41 来源:linux.it.net.cn 作者:IT

一、联合（union）查询
含义：
联合查询是指将2个或2个以上的字段数量相同的查询结果，"纵向堆叠"后合并为一个结果。
语法形式：

select查询1
union [all或distinct]
select查询2
union [all或distinct]
select查询3
......
[order by 字段 [asc或desc]]
[limit 起始行号,数量];
说明：
①所有单个查询结果应该具有相等的列数。
②所有单个查询的列类型应该具有一致性（即每个查询的第n列的数据类型一致）。
③单个查询的列名可以不同，但最终的列名是第一个查询的列名（可以使用别名）。
④union可以带all或distinct参数，如果省略就是distinct，即默认已经消除重复行了。
⑤最后的order by或limit是对整个联合之后的结果数据进行排序或数量限定。
⑥order by子句中的排序字段应该使用第一个查询中的字段名，如果有别名就必须使用别名。

示例：

二、连接查询
1、交叉连接：

语法形式：
from 表1 [cross] join 表2
说明：
①交叉连接其实可以认为是连接查询的“完全版本”，即所有行都无条件地都连接起来了。
②关键字"cross"可以省略；
③交叉连接又称为"笛卡尔积"，通常应用价值不大。
示例：

2、内连接

语法形式：
from 表1 [inner] join 表2 on 连接条件
说明：
①内连接其实是交叉连接的基础上，再通过on条件而筛选出来的部分数据。
②关键字"inner"可以省略，但建议写上。
③内连接是应用最广泛的一种连接查询，其本质是根据条件筛选出“"有意义的数据"。

3、外链接

①左外连接
语法形式：
from 表1 left [outer] join 表2 on 连接条件
说明：
<1>左外连接其实是保证左边表的数据都能够取出来的一种连接。
<2>左外连接其实是在内连接的基础上，再加上左边表中所有不能满足条件的数据（对应右边部分使用null填充）
<3>关键字"outer"可以省略。
②右外连接
语法形式：
from 表1 right [outer] join 表2 on 连接条件
说明：
<1>右外连接其实是保证右边表的数据都能够取出的一种连接。
<2>右外连接其实是在内连接的基础上，再加上右边表中所有不能满足条件的数据。
<3>关键字"outer"可以省略。

4、自连接

语法形式：
from 表名 as a [连接形式] join 表名 as b on a.xx字段1=b.xx字段名
说明：
自连接其实还是两个表连接，只是将一个表用不同的别名，当做两个表。
自连接适用于一个表中的某个字段的值"来源于"当前表的另一个字段的情况。

三、子查询（subquery）
子查询就是指一个"正常查询语句"中的某个部分（比如select部分，from部分， where部分）又出现了查询的一种查询形式，比如：
select * from XX表名 where price >= (一个子查询语句);
此时，子查询所在上"上层查询"，就被称为主查询。
子查询分为：
标量子查询、列子查询、行子查询、表子查询

1、标量子查询

含义：
标量子查询就是指子查询的结果是"单个值"（一行一列）的查询。
使用：
标量子查询通常用在where子句中，作为主查询的一个条件判断的数据。
本质上，标量子查询的结果，就可以直接当做"一个值"来使用。

2、列子查询

含义：
列子查询查出的结果为"一列数据"，类似这样：
select pinpai from product where chandi = ‘北京’;
结果为：

使用：
列子查询通常用在where子句的 in 运算符中，代替in运算符中的"字面值"列表数据。

3、行子查询

含义：
行子查询查出的结果是一个行（有时也可以是多行），类似这样：
select distinct pinpai, chandi from product where pinpai=’联想’ and chandi = ‘北京’;
结果为：

使用：
行子查询的结果通常跟"行构造符"一起，在where条件子句中做为条件数据，类似这样：
where (字段1, 字段2) = (行子查询)
或
where row(字段1, 字段2) = (行子查询) //含义跟上一行是一样的，即row可以省略

4、表子查询

含义：
当一个子查询查出的结果是"多行多列"的时候，就是表子查询。
表子查询的结果相当于一个表，可以直接当做一个表来使用。
使用：
表子查询通常用在主查询的from子句中，作为一个"数据源"。
注意：
此时需要给该子查询设置一个别名，类似这样：
from (select ... 子查询 ) as tab1

5、有关子查询的特定关键字

①in关键字在子查询中主要用在列子查询中代替人为手工罗列出来的多个"字面值"数据。
②any关键字用在比较操作操符的后面，表示查询结果的多个数据中的任一个满足该比较操作符就算满足。
③all关键字用在比较操作操符的后面，表示查询结果的多个数据中的所有都满足该比较操作符才算满足。

6、exists子查询

形式：
where exists ( 任何子查询 )
含义：
该子查询如果"有数据"，则该exists()的结果为"true"，即相当于 where true （恒真）
该子查询如果“没有数据”，则该exists()的结果为"false"，即相当于where false（恒假）
说明：
此子查询语句通常需要用到主查询语句中的字段作为查询条件。
综合示例：

示例代码：
#内连接：
#找出所有的商品信息及其分类
select * from product inner join product_type
    on product.protype_id=product_type.protype_id;
#找出“孙悟空”的总的购物花销
select sum(order_total) as 总价 from user_info as u inner join order_info as o
    on u.user_id=o.user_id where real_name='孙悟空';
#1号用户总共买了几件产品
select sum(count) as 总数 from order_info as o
    inner join order_goods as g
    on o.order_id=g.order_id where user_id=1;

#外连接：
#统计计算出每类商品各有多少件
select protype_name,count(pro_name) as 件数 from product_type as t
    left join product as p
    on p.protype_id=t.protype_id group by protype_name
    order by count(pro_name) desc;

#自连接：
#查询每个城市及其所在省份
select a1.*,a2.areaName from areaList as a1 join areaList as a2
    on a1.parentID=a2.areaID;

#子查询：
#找出产品表中价格大于平均价的所有产品
select * from product where price>(select avg(price) from product);
#找出所有低于北京产地的商品的平均价的深圳的产品
select * from product where chandi='深圳' and
    price<(select avg(price) from product where chandi='北京');
#查出出产贵重商品（价格超过4000即为贵重商品）的那些产地的所有商品
select * from product where chandi in
    (select chandi from product where price>4000);
#查出出产奢侈商品的那些产地的所有商品
#奢侈品：其价格超过贵重品的平均价
#贵重品：其价格超过所有商品的平均价
select * from product where chandi in(
    select chandi from product where price>(
        select avg(price) from product where price>(
            select avg(price) from product
        )
    )
);
#找出跟单价最低的商品同品牌同产地的所有商品
select * from product where (pinpai,chandi)=(
    select pinpai,chandi from product where price=(
        select min(price) from product
    )
);
#查出商品价格大于4000的所有商品的数量和均价
select count(*),avg(price) from
    (select * from product where price>4000) as t1;
#找出在北京生产的但价格比在深圳生产的贵的商品
select * from product where chandi='北京' and price> any(
    select price from product where chandi='深圳'
);
#找出在北京生产的但价格比在深圳生产所有商品都贵的商品
select * from product where chandi='北京' and price> all(
    select price from product where chandi='深圳'
);
#查询商品分类名称中带“电”字的所有商品
select * from product where exists(
    select * from product_type where protype_name like '%电%'
    and product.protype_id=product_type.protype_id
);
#用内连接的方式查询
select * from product as p inner join product_type as t
    on p.protype_id=t.protype_id
    where t.protype_name like '%电%';

(责任编辑：IT)