Hive的进阶(表的查询)

一、查询语句基本语法

1.1SQL查询语句的基本结构

select selection_list # 查询的列 
from table # 要查询的表
join on # 连接的表 
where # 查询条件 
group by # 分组查询 
having # 分组条件过滤 
order by # 字段排序 
sort by # 结果排序
limit # 限制结果数 
union/union all # 合并表

1.2SQL语句的执行顺序

from
on 
join
where
group by 
having
select 
distinct
order by
limit

1.3查询注意事项

尽量不要使用子查询、尽量不要使用 in not in select * from aa1
where id in (select id from bb);
查询尽量避免join连接查询，但是这种操作咱们是永远避免不了的。
查询永远是小表驱动大表（永远是小结果集驱动大结果集）

1.4数据库建模

关系型数据库最难的地方，就是建模（model）
错综复杂的数据，需要建立模型，才能储存在数据库。所谓"模型"就是两样东西：实体（entity）+ 关系（relationship）ER图。实体指的是那些实际的对象，带有自己的属性，可以理解成一组相关属性的容器。关系就是实体之间的联系，通常可以分成"一对一"、"一对多"和"多对多"等类型。

二、连接查询

在关系型数据库里面，每个实体有自己的一张表（table），所有属性都是这张表的字段（field），
表与表之间根据关联字段"连接"（join）在一起。所以，表的连接是关系型数据库的核心问题。

表的连接分成好几种类型。
内连接（inner join）
外连接（outer join）
左连接（left join）
右连接（right join）
全连接（full join）

所谓"连接"，就是两张表根据关联字段，组合成一个数据集。
问题是，两张表的关联字段的值 往往是不一致的，如果关联字段不匹配，怎么处理？
比如，表 A 包含张三和李四，表 B 包 含李四和王五，匹配的只有李四这一条记录。

很容易看出，一共有四种处理方法。 只返回两张表匹配的记录，这叫内连接（inner join）。
返回匹配的记录，以及表 A 多余的记录，这叫左连接（left join）。
返回匹配的记录，以及表 B 多余的记录，这叫右连接（right join）。
返回匹配的记录，以及表 A 和表 B 各自的多余记录，这叫全连接（full join）。

下图就是四种连接的图示。

上图中，表 A 的记录是 123，表 B 的记录是 ABC，颜色表示匹配关系。返回结果中，如果另 一张表没有匹配的记录，则用 null 填充。
这四种连接，又可以分成两大类：
内连接（inner join）表示只包含匹配的记录
外连接 （outer join）表示还包含不匹配的记录。所以，左连接、右连接、全连接都属于外连接。
此外，还存在一种特殊的连接，叫做"交叉连接"（cross join），指的是表 A 和表 B 的不 存在关联字段，这时表 A（共有 n 条记录）与表 B （共有 m 条记录）连接后，会产生一张 包含 n x m 条记录的新表（见下图）。

Hive中的内连接（inner join）、外连接（outer join）、左连接（left join）、右连接（right join）、全连接（full join）的用法与mysql的一致，这里我们介绍一下Hive中特有join。

1、left semi join

在hive中，有一种专有的join操作,left semi join,我们称之为半开连接。它是left join的一种优化形式，只能查询左表的信息，主要用于解决hive中左表的数据是否存在的问题。相当于exists关键字的用法。

select * from u1 
left semi join u2 on u1.id =u2.id;

2、子查询

# hive对子查询支持不是很友好，特别是 "="问题较多
不能使用= 
select e.* 
from emp e 
where e.deptno = ( 
select d.deptno 
	from dept d 
	limit 1 
);

可以使用in 
select e.* 
from emp e 
where e.deptno in ( 
	select d.deptno 
	from dept d 
);

三、表合并union

如果把两张表 结果联合在一起,可以用union,有下面两种用法

• union ：将多个结果集合并，去重，排序
• union all ：将多个结果集合并，不去重，不排序。

select d.deptno as deptno, 
d.dname as dname 
from dept d 
union 
select 
e.deptno as deptno, 
e.ename as dname 
from emp e; 

select d.deptno as deptno, 
d.dname as dname 
from dept d 
union all 
select 
d.dname as dname, 
d.deptno as deptno 
from dept d;

-- 单个union语句不支持：orderBy、clusterBy、distributeBy、sortBy、limit 
-- 单个union语句字段的个数要求相同，字段的顺序要求相同。

 如果想去重查询中的某列数据,可以使用 distinct关键字