数据库基础09——组合查询

数据库基础09——组合查询、临时表

一、组合查询

1、UNION 和UNION ALL

区别：

UNION操作符可以组合两个或多个 SELECT 语句的结果， 不包含重复的记录。

UNION ALL操作符可以组合两个SELECT语句的结果， 并且包含重复的结果。

------------UNION-----------------
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';
------------UNION ALL------------------------
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';

union的注意事项

• union两边的select语句的字段个数必须一致

• union两边的select语句的字段名可以不一致，最终按第一个select语句的字段名。

• union两边的select语句中的数据类型可以不一致。

2、INTERSECT

INTERSECT 可以组合两个 SELECT 语句， 但只返回第一个 SELECT 语句里与第二个SELECT语句里一样的记录。 其使用规则与UNION操作符一样。 目前MySQL5.0尚不支持INTERSECT， 但SQL Server和Oracle全都提供支持。

3、EXCEPT

EXCEPT 操作符组合两个 SELECT 语句， 返回第一个 SELECT 语句里有但第二个SELECT语句里没有的记录。 同样的， 它的使用规则与UNION操作符一样。 目前MySQL并不支持EXCEPT。 而在Oracle中， 则使用MINUS操作符来实现同样的功能。 SQL server 支持。

4、对组合进行排序 ORDER BY

SELECT 语句的输出用 ORDER BY 子句排序。在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须位于最后一条 SELECT 语句之后。 因此组合查询里虽然可能包含多个查询或SELECT语句， 但只能有一个ORDER BY子句， 而且它只能以别名或数字来引用字段。

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;

5、组合使用 GROUP BY

与ORDER BY不同的是， GROUP BY子句可以用于组合查询中的每一个 SELECT语句， 也可以用于全部查询结果。 另外， HAVING子句也可以用于组合查询里的每个SELECT语句。

SELECT 列名
FROM  表名
[WHERE]
[GROUP BY]
[HAVING]
UNION
SELECT 列名
FROM  表名
[WHERE]
[GROUP BY]
[HAVING]
[ORDER BY]

二、临时表的使用

1、sql server 下的 临时表使用

SELECT * INTO #tmp FROM TABLE_A WHERE  <条件筛选语句>;

sql server 从现有数据创建表 使用 select into ，临时表已 #开头， 又分为局部临时表和全局临时表，一个#表示局部临时表，两个# 表示全局临时表。

局部临时表只对当前连接有效，当前连接断开时自动删除。

全局临时表对其他连接也有效，在当前连接和其他访问过他的连接都断开时自动删除。

好处就是思路会非常清楚，相对与子查询的多层嵌套性能上也比较给力，唯一的确定就是当数据量比较大的时候，存放在临时表中就不是一个明智的选择。所以如果遇到数据量非常大的，可以尝试分批放到临时表中。

2、mysql 临时表使用

相对于sql server ，mysql 从查询出的数据建表可以使用 create table 表名 as ...，那么创建临时表的sql就是：

CREATE TEMPORARY TABLE 临时表名 AS
(
	select *  from  其他表名 limit 1,10000
);

注意 使用 temporary 这个关键字就可以， mysql临时表支队当前连接可见，比如java代码执行脚本，执行完成后，临时表也会销毁。