数据查询之连接查询、子查询和联合查询

一、连接查询

• 基本含义：将两个以上的表（数据源），连接起来成为一个数据源。
• 基本形式：from 表1 [连接方式] join 表2 [on 连接条件]
  • join 为连接查询关键字--必须
  • [连接方式] [on 连接条件]为可选性 
• 交叉连接： from 表1 [cross] join 表2 
  • 交叉连接为两两横向连接，假如表1有m条记录，表2有n条记录，则交叉连接后有m*n条记录
  • 示例：
内连接：     from 表1 [inner] join 表2 on 连接条件；在交叉连接基础上，选取符合on 连接条件的记录
• on 连接条件 为内连接必选项
• 示例：
•  左外连接：from 表1 left [outer] join 表2 on 连接条件；内连接基础上，加上表1中不符合条件的记录（右侧部分全为null）
  • 示例：
• 右外连接：from 表1 right [outer] join 表2 on 连接条件；内连接基础上，加上表2中不符合条件的记录（右侧部分全为null）
  • 示例：
• 在时间应用中，交叉连接结果并不是都具有实际意义，更过使用的是内连接，举例：
  • 　表1：商品信息表（production）

    mysql> select * from production;
    +--------+-----------------------------------+------+-------+-------+--------+
    | pro_id | name                              | type | price | brand | origin |
    +--------+-----------------------------------+------+-------+-------+--------+
    |      1 | 康佳（KONKA）42英寸全高清液晶电视 |    1 |  1999 | 康佳  | 深圳   |
    |      2 | 索尼（SONY）4G手机（黑色）        |    2 |  3238 | 索尼  | 深圳   |
    |      3 | 海信（Hisense）55英寸智能电视     |    1 |  4199 | 海信  | 青岛   |
    |      4 | 联想（Lenovo）14.0英寸笔记本电脑  |    3 |  5499 | 联想  | 北京   |
    |      5 | 索尼（SONY）13.3英寸触控超极本    |    3 | 11499 | 索尼  | 天津   |
    |      6 | 索尼（SONY）60英寸全高清液晶电视  |    1 |  6999 | 索尼  | 北京   |
    |      7 | 联想（Lenovo）12.0英寸笔记本电脑  |    3 |  2999 | 联想  | 北京   |
    |      8 | 联想 双卡双待3G手机               |    2 |   988 | 联想  | 北京   |
    |      9 | 惠普（HP）黑白激光打印机          |    3 |  1169 | 惠普  | 天津   |
    +--------+-----------------------------------+------+-------+-------+--------+
    9 rows in set (0.01 sec)

     

  • 表2：商品类型表（product_type）

    mysql> select *  from product_type;
    +------------+--------------+
    | protype_id | protype_name |
    +------------+--------------+
    |          1 | 家用电器     |
    |          2 | 手机数码     |
    |          3 | 电脑办公     |
    |          4 | 图书音像     |
    |          5 | 家居家具     |
    |          6 | 服饰配饰     |
    |          7 | 个护化妆     |
    |          8 | 运动户外     |
    |          9 | 汽车用品     |
    |         10 | 食品酒水     |
    |         11 | 营养保健     |
    +------------+--------------+
    11 rows in set (0.01 sec)

     

  • 请给出索尼 4G手机所属商品类型名称：

    • mysql> select protype_name from production as p1 join product_type as p2 on p1.type=p2.protype_id wh
      ere p1.name like '%SONY%'and p1.name like '%4G%';
      +--------------+
      | protype_name |
      +--------------+
      | 手机数码     |
      +--------------+
      1 row in set (0.00 sec)

       

二、子查询

• 基本含义
  • 一般一个查询，就出现一次select 语句，但如果又出现select 语句，此时就称后者为‘子查询’，前者为‘主查询’
• 形式
  • select 字段或者表达式或者(子查询1) [as 别名] from 表名或者(子查询2)  where 字段或者表达式或者子(查询3)
  • 通常每个位置的子查询，应该符合该位置的数据需求
    
    • 子查询1 应该是一个‘数据结果’
    • 子查询2 可以是‘任意结果’
    • 子查询3 可以是一个数据或者一列数据
• 按结果分类
  • 表子查询：子查询结果为‘多行多列’，通常可以当做‘表’放在from 后面使用
  • 行子查询：子查询结果为‘一行多列’，通常可以当做‘行’来使用，放在‘行比较语法’
  • 列子查询：子查询结果为‘多行一列’，通常可以当做‘多个值’来使用，类似（1,3,33,66）
  • 标量子查询：子查询结果为‘一行一列’，通常可以当做‘一个值’来使用，当做字段使用
• 常见子查询及相关关键字
  • 比较运算符中使用子查询
    • 形式： 操作数 比较运算符 （标量子查询）；操作数通常为一个字段
    • 含义：判断操作数（字段）的值是否满足比较运算符所设定的结果
    • 举例：表

      mysql> select * from production;
      +--------+-----------------------------------+------+-------+-------+--------+
      | pro_id | name                              | type | price | brand | origin |
      +--------+-----------------------------------+------+-------+-------+--------+
      |      1 | 康佳（KONKA）42英寸全高清液晶电视 |    1 |  1999 | 康佳  | 深圳   |
      |      2 | 索尼（SONY）4G手机（黑色）        |    2 |  3238 | 索尼  | 深圳   |
      |      3 | 海信（Hisense）55英寸智能电视     |    1 |  4199 | 海信  | 青岛   |
      |      4 | 联想（Lenovo）14.0英寸笔记本电脑  |    3 |  5499 | 联想  | 北京   |
      |      5 | 索尼（SONY）13.3英寸触控超极本    |    3 | 11499 | 索尼  | 天津   |
      |      6 | 索尼（SONY）60英寸全高清液晶电视  |    1 |  6999 | 索尼  | 北京   |
      |      7 | 联想（Lenovo）12.0英寸笔记本电脑  |    3 |  2999 | 联想  | 北京   |
      |      8 | 联想 双卡双待3G手机               |    2 |   988 | 联想  | 北京   |
      |      9 | 惠普（HP）黑白激光打印机          |    3 |  1169 | 惠普  | 天津   |
      +--------+-----------------------------------+------+-------+-------+--------+
      9 rows in set (0.00 sec)

    • 问题：找出大于平均价的商品
      • 第一步：找平均价

        • mysql> select avg(price) from production;
          +-------------------+
          | avg(price)        |
          +-------------------+
          | 4287.666666666667 |
          +-------------------+
          1 row in set (0.00 sec)

      • 第二步：找商品（大于平均价）

        • mysql> select * from production where price > (select avg(price) from production);
          +--------+----------------------------------+------+-------+-------+--------+
          | pro_id | name                             | type | price | brand | origin |
          +--------+----------------------------------+------+-------+-------+--------+
          |      4 | 联想（Lenovo）14.0英寸笔记本电脑 |    3 |  5499 | 联想  | 北京   |
          |      5 | 索尼（SONY）13.3英寸触控超极本   |    3 | 11499 | 索尼  | 天津   |
          |      6 | 索尼（SONY）60英寸全高清液晶电视 |    1 |  6999 | 索尼  | 北京   |
          +--------+----------------------------------+------+-------+-------+--------+
          3 rows in set (0.00 sec)

  • 使用in 子查询
    • 形式：where 操作数 in （列子查询）
    • 举例：问题找出所有带‘电’字类别的商品
      • 第一步：找出带‘电’字的类别ID

        • mysql> select protype_id from product_type where protype_name like '%电%';
          +------------+
          | protype_id |
          +------------+
          |          1 |
          |          3 |
          +------------+
          2 rows in set (0.00 sec)

      • 第二步：找商品

        • mysql> select * from production where production.type in (select protype_id from product_type where
          protype_name like '%电%');
          +--------+-----------------------------------+------+-------+-------+--------+
          | pro_id | name                              | type | price | brand | origin |
          +--------+-----------------------------------+------+-------+-------+--------+
          |      1 | 康佳（KONKA）42英寸全高清液晶电视 |    1 |  1999 | 康佳  | 深圳   |
          |      3 | 海信（Hisense）55英寸智能电视     |    1 |  4199 | 海信  | 青岛   |
          |      4 | 联想（Lenovo）14.0英寸笔记本电脑  |    3 |  5499 | 联想  | 北京   |
          |      5 | 索尼（SONY）13.3英寸触控超极本    |    3 | 11499 | 索尼  | 天津   |
          |      6 | 索尼（SONY）60英寸全高清液晶电视  |    1 |  6999 | 索尼  | 北京   |
          |      7 | 联想（Lenovo）12.0英寸笔记本电脑  |    3 |  2999 | 联想  | 北京   |
          |      9 | 惠普（HP）黑白激光打印机          |    3 |  1169 | 惠普  | 天津   |
          +--------+-----------------------------------+------+-------+-------+--------+
          7 rows in set (0.00 sec)

  • 使用any 子查询
    • 形式：where 操作数 比较运算符  any（列子查询）
    • 含义：该操作数的值只要跟列子查询其中的任意一个值满足给定的比较运算，就算满足条件
    • 举例：问题找出所有带‘电’字类别的商品

      mysql> select * from production where production.type = any(select protype_id from product_type wher
      e
          -> protype_name like '%电%');
      +--------+-----------------------------------+------+-------+-------+--------+
      | pro_id | name                              | type | price | brand | origin |
      +--------+-----------------------------------+------+-------+-------+--------+
      |      1 | 康佳（KONKA）42英寸全高清液晶电视 |    1 |  1999 | 康佳  | 深圳   |
      |      3 | 海信（Hisense）55英寸智能电视     |    1 |  4199 | 海信  | 青岛   |
      |      4 | 联想（Lenovo）14.0英寸笔记本电脑  |    3 |  5499 | 联想  | 北京   |
      |      5 | 索尼（SONY）13.3英寸触控超极本    |    3 | 11499 | 索尼  | 天津   |
      |      6 | 索尼（SONY）60英寸全高清液晶电视  |    1 |  6999 | 索尼  | 北京   |
      |      7 | 联想（Lenovo）12.0英寸笔记本电脑  |    3 |  2999 | 联想  | 北京   |
      |      9 | 惠普（HP）黑白激光打印机          |    3 |  1169 | 惠普  | 天津   |
      +--------+-----------------------------------+------+-------+-------+--------+
      7 rows in set (0.00 sec)

  • all 子查询
    • 形式：where 操作数 比较运算符  all（列子查询）
    • 含义：该操作数的值必须跟列子查询其中的所有值满足给定的比较运算，才算满足条件
    • 举例：找出价格最高的产品
      • 思路一：找出价格大于等于所有价格的商品

        mysql> select * from production where price >= all(select price from production);
        +--------+--------------------------------+------+-------+-------+--------+
        | pro_id | name                           | type | price | brand | origin |
        +--------+--------------------------------+------+-------+-------+--------+
        |      5 | 索尼（SONY）13.3英寸触控超极本 |    3 | 11499 | 索尼  | 天津   |
        +--------+--------------------------------+------+-------+-------+--------+
        1 row in set (0.00 sec)

      • 思路二：找出最大的价格，对比最大价格找出对应的商品 上文中有提及不在重复
  • some 子查询
    • some 是 any 的同义词，用法相同
  • exists 和 not exists 子查询
    • 形式：where exist （子查询）
    • 含义：如果该子查询有结果数据（无论什么数据，只要大于等于1行），则就为true，否则就为false
    • 举例：找出具有在售商品的类别信息

      mysql> select * from product_type where exists(select * from production as p1 where p1.type = produc
      t_type.protype_id);
      +------------+--------------+
      | protype_id | protype_name |
      +------------+--------------+
      |          1 | 家用电器     |
      |          2 | 手机数码     |
      |          3 | 电脑办公     |
      +------------+--------------+
      3 rows in set (0.00 sec)

    • 举例：找出具有没有在售商品的类别信息

      mysql> select * from product_type where not exists(select * from production as p1 where p1.type = pr
      oduct_type.protype_id);
      +------------+--------------+
      | protype_id | protype_name |
      +------------+--------------+
      |          4 | 图书音像     |
      |          5 | 家居家具     |
      |          6 | 服饰配饰     |
      |          7 | 个护化妆     |
      |          8 | 运动户外     |
      |          9 | 汽车用品     |
      |         10 | 食品酒水     |
      |         11 | 营养保健     |
      +------------+--------------+
      8 rows in set (0.00 sec)

    • 实际上，这种exists 子查询，如果涉及2个表（或以上），其内部其实会自动进行“连接查询”，且其逻辑过程较为复杂，而且不明确，通常认为效率较低，尽量少用

三、联合查询

• 形式：select 语句1  union  select 语句2
• 基本含义：将2个select 语句查询的结果“层叠”为一个“大结果”
• 说明：
  • 具有相同输出字段--通常字段类型也相同，具有现实意义
  • 结果集中的字段以第一个select 语句的输出字段为准
  • 第一个select 语句的字段可以做别名，单若果做别名，则后续的where，group，order等子句也要用该别名
  • 示例：

    mysql> (select * from join1) union (select * from join2);
    +----+------+------+
    | id | f1   | f2   |
    +----+------+------+
    |  1 | a1   | b1   |
    |  2 | a12  | b2   |
    |  3 | a3   | b3   |
    | 11 | a1   | b11  |
    | 12 | a12  | b12  |
    | 13 | a13  | b13  |
    +----+------+------+
    6 rows in set (0.00 sec)