• 并(union) 执行多个查询并将结果作为单个查询结果返回。
    一般需要使用组合查询的情况

  • 单个查询中从不同的表类似返回结果数据

  • 单个表执行多个查询，按单个查询返回数据

检索 IL,IN,MI几个洲的客户报表。

  2.     -> FROM Customers
  3.     -> WHERE cust_name = 'Fun4All';
  4. +-----------+--------------------+-----------------------+
  5. | cust_name | cust_contact       | cust_email            |
  6. +-----------+--------------------+-----------------------+
  7. | Fun4All   | Jim Jones          | jjones@fun4all.com    |
  8. | Fun4All   | Denise L. Stephens | dstephens@fun4all.com |
  9. 2 rows in set (0.00 sec)

把上面两条语句进行组合

解释: 中介有UNION分割开，并把输出组合成儿一个查询结果

另一个检索

  1. mysql> SELECT cust_name, cust_contact, cust_email
  2.     -> FROM Customers
  3.     -> WHERE cust_state IN('IL','IN','MI')
  5. +---------------+--------------------+-----------------------+
  6. | cust_name     | cust_contact       | cust_email            |
  7. +---------------+--------------------+-----------------------+
  8. | Village Toys  | John Smith         | sales@villagetoys.com |
  9. | Fun4All       | Jim Jones          | jjones@fun4all.com    |
  10. | Fun4All       | Denise L. Stephens | dstephens@fun4all.com |
  11. | The Toy Store | Kim Howard         | NULL                  |
  12. +---------------+--------------------+-----------------------+
  13. 4 rows in set (0.00 sec)

使用UNION规则

  • 必须有两条以上SELECT语句组合，语句直接用关键字UNION分割。
  • UNION中每个查询必须包含相同的列，表单式，聚集函数。

如果想要所有行，可以使用UNION ALL 而不是UNION。

与上面例子比多了一行。

  1. mysql> SELECT cust_name, cust_contact, cust_email
  2.     -> FROM Customers
  3.     -> WHERE cust_state IN ('IL''IN','MI')
  5.     -> SELECT cust_name, cust_contact, cust_email
  6.     -> FROM Customers
  7.     -> WHERE cust_name = 'Fun4ALL'
  8.     -> ORDER BY cust_name, cust_contact;
  9. +--------------+--------------------+-----------------------+
  10. | cust_name    | cust_contact       | cust_email            |
  11. +--------------+--------------------+-----------------------+
  12. | Fun4All      | Denise L. Stephens | dstephens@fun4all.com |
  13. | Fun4All      | Jim Jones          | jjones@fun4all.com    |
  14. | Village Toys | John Smith         | sales@villagetoys.com |
  15. +--------------+--------------------+-----------------------+
  16. 3 rows in set (0.00 sec)