如何用UNION操作符将多条SELECT语句组合成一个查询
13.1. 组合查询
并(union
) 执行多个查询并将结果作为单个查询结果返回。一般需要使用组合查询的情况:
- 单个查询中从不同的表类似返回结果数据
- 单个表执行多个查询,按单个查询返回数据
13.2. 创建组合查询
检索 IL,IN,MI几个洲的客户报表。
mysql> SELECT cust_name, cust_contact, cust_email
-> FROM Customers
-> WHERE cust_state IN ('IL', 'IN', 'MI');
+---------------+--------------+-----------------------+
| cust_name | cust_contact | cust_email |
+---------------+--------------+-----------------------+
| Village Toys | John Smith | [email protected] |
| Fun4All | Jim Jones | [email protected] |
| The Toy Store | Kim Howard | NULL |
+---------------+--------------+-----------------------+
3 rows in set (0.01 sec)
SELECT
利用 =
符合,检索出所有Fun4All单位
mysql> SELECT cust_name, cust_contact, cust_email
-> FROM Customers
-> WHERE cust_name = 'Fun4All';
+-----------+--------------------+-----------------------+
| cust_name | cust_contact | cust_email |
+-----------+--------------------+-----------------------+
| Fun4All | Jim Jones | [email protected] |
| Fun4All | Denise L. Stephens | [email protected] |
+-----------+--------------------+-----------------------+
2 rows in set (0.00 sec)
把上面两条语句进行组合
mysql> SELECT cust_name, cust_contact, cust_email
-> FROM Customers
-> WHERE cust_state IN ('IL','IN','MI')
-> UNION
-> SELECT cust_name, cust_contact, cust_email
-> FROM Customers
-> WHERE cust_name = 'Fun4All';
+---------------+--------------------+-----------------------+
| cust_name | cust_contact | cust_email |
+---------------+--------------------+-----------------------+
| Village Toys | John Smith | [email protected] |
| Fun4All | Jim Jones | [email protected] |
| The Toy Store | Kim Howard | NULL |
| Fun4All | Denise L. Stephens | [email protected] |
+---------------+--------------------+-----------------------+
4 rows in set (0.02 sec)
解释: 中介有 UNION
分割开,并把输出组合成儿一个查询结果
另一个检索
mysql> SELECT cust_name, cust_contact, cust_email
-> FROM Customers
-> WHERE cust_state IN('IL','IN','MI')
-> OR cust_name = 'Fun4All';
+---------------+--------------------+-----------------------+
| cust_name | cust_contact | cust_email |
+---------------+--------------------+-----------------------+
| Village Toys | John Smith | [email protected] |
| Fun4All | Jim Jones | [email protected] |
| Fun4All | Denise L. Stephens | [email protected] |
| The Toy Store | Kim Howard | NULL |
+---------------+--------------------+-----------------------+
4 rows in set (0.00 sec)
13.3. 使用UNION规则
- 必须有两条以上
SELECT
语句组合,语句直接用关键字UNION
分割。 UNION
中每个查询必须包含相同的列,表单式,聚集函数。- 列的数据必须兼容。
13.3.1. 是否带有重复行
UNION
默认去掉重复行。如果想要所有行,可以使用 UNION ALL
而不是 UNION
。
mysql> SELECT cust_name, cust_contact, cust_email
-> FROM Customers
-> WHERE cust_state IN ('IL','IN','MI')
-> UNION ALL
-> SELECT cust_name, cust_contact, cust_email
-> FROM Customers
-> WHERE cust_name = 'Fun4All';
+---------------+--------------------+-----------------------+
| cust_name | cust_contact | cust_email |
+---------------+--------------------+-----------------------+
| Village Toys | John Smith | [email protected] |
| Fun4All | Jim Jones | [email protected] |
| The Toy Store | Kim Howard | NULL |
| Fun4All | Jim Jones | [email protected] |
| Fun4All | Denise L. Stephens | [email protected] |
+---------------+--------------------+-----------------------+
5 rows in set (0.00 sec)
与上面例子比多了一行。
13.4. 对组合查询结果排序
mysql> SELECT cust_name, cust_contact, cust_email
-> FROM Customers
-> WHERE cust_state IN ('IL''IN','MI')
-> UNION
-> SELECT cust_name, cust_contact, cust_email
-> FROM Customers
-> WHERE cust_name = 'Fun4ALL'
-> ORDER BY cust_name, cust_contact;
+--------------+--------------------+-----------------------+
| cust_name | cust_contact | cust_email |
+--------------+--------------------+-----------------------+
| Fun4All | Denise L. Stephens | [email protected] |
| Fun4All | Jim Jones | [email protected] |
| Village Toys | John Smith | [email protected] |
+--------------+--------------------+-----------------------+
3 rows in set (0.00 sec)
下一节:利用INSERT语句将数据插入表中