官方文档：https://cwiki.apache.org/confluence/display/hive/languagemanual+select 查询语句语法：
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]
[LIMIT number]
1.基本查询
1.1 全表和特定列查询
0）数据准备
（0）原始数据 dept:
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700
emp:
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
7134 MILLER CLERK 7782 1982-1-23 1300.00 50
（1）创建部门表
create table if not exists dept(deptno int,dname string, loc int)
row format delimited fields terminated by '\t';
（2）创建员工表
create table if not exists emp(empno int,ename string, job string, mgr int,
hiredate string, sal double,
comm double, deptno int)
row format delimited fields terminated by '\t';
（3）导入数据
load data local inpath '/opt/module/datas/dept.txt' into table dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;
1）全表查询
hive (hive)> select * from emp;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
7134 MILLER CLERK 7782 1982-1-23 1300.0 NULL 50
2）选择特定列查询
hive (hive)> select empno, ename from emp;
OK
empno ename
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
7134 MILLER
注意： （1）SQL 语言大小写不敏感。 （2）SQL 可以写在一行或者多行 （3）关键字不能被缩写也不能分行 （4）各子句一般要分行写 （5）使用缩进提高语句的可读性
1.2 列别名
1）重命名一个列
（1）便于计算 （2）紧跟列名，也可以在列名和别名之间加入关键字‘AS’
2）案例实操
hive (hive)> select ename AS name, deptno dn from emp;
OK
name dn
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
MILLER 50
1.3 算术运算符
| 运算符 | 描述 | | — | — | | A+B | A 和 B 相加 | | A-B | A 减去 B | | A*B | A 和 B 相乘 | | A/B | A 除以 B | | A%B | A 对 B 取余 | | A&B | A 和 B 按位取与 | | A|B | A 和 B 按位取或 | | A^B | A 和 B 按位取异或 | | ~A | A 按位取反 |
1）案例实操：
查询出所有员工的薪水后加 1 显示
hive (hive)> select sal +1 from emp;
OK
_c0
801.0
1601.0
1251.0
2976.0
1251.0
2851.0
2451.0
3001.0
5001.0
1501.0
1101.0
951.0
3001.0
1301.0
1301.0
####
1.4 常用函数
1）求总行数（count）
select count(*) cnt from emp;
--MapReduce--
OK
cnt
15
2）求工资的最大值（max）
select max(sal) max_sal from emp;
OK
max_sal
5000.0
3）求工资的最小值（min）
select min(sal) min_sal from emp;
OK
min_sal
800.0
4）求工资的总和（sum）
select sum(sal) sum_sal from emp;
OK
sum_sal
30325.0
5）求工资的平均值（avg）
select avg(sal) avg_sal from emp;
OK
avg_sal
2021.6666666666667
##
1.5 Limit 语句
典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。
hive (hive)> select * from emp limit 5;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
###
1.6 Where 语句
（1）使用 WHERE 子句，将不满足条件的行过滤掉 （2）WHERE 子句紧随 FROM 子句
1）案例实操
查询出薪水大于 3000 的所有员工
hive (hive)> select * from emp where sal >3000;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
注意：where 子句中不能使用字段别名
hive (hive)> select ename,sal as salary from emp where salary>3000;
FAILED: SemanticException [Error 10004]: Line 1:42 Invalid table alias or column reference 'salary': (possible column names are: empno, ename, job, mgr, hiredate, sal, comm, deptno)
1.7 比较运算符（Between/In/ Is Null）
1）下面表中描述了谓词操作符，这些操作符同样可以用于 JOIN…ON 和 HAVING 语句中。
| 操作符 | 支持的数据类型 | 描述 | | — | — | — | | A=B | 基本数据类型 | 如果 A 等于 B 则返回 TRUE，反之返回 FALSE | | A<=>B | 基本数据类型 | 如果 A 和 B 都为 NULL，则返回 TRUE，如果一边为 NULL， 返回 False | | A<>B, A!=B | 基本数据类型 | A 或者 B 为 NULL 则返回 NULL；如果 A 不等于 B，则返回 TRUE，反之返回 FALSE | | A<B | 基本数据类型 | A 或者 B 为 NULL，则返回 NULL；如果 A 小于 B，则返回 TRUE，反之返回 FALSE | | A<=B | 基本数据类型 | A 或者 B 为 NULL，则返回 NULL；如果 A 小于等于 B，则返 回 TRUE，反之返回 FALSE | | A>B | 基本数据类型 | A 或者 B 为 NULL，则返回 NULL；如果 A 大于 B，则返回 TRUE，反之返回 FALSE | | A>=B | 基本数据类型 | A 或者 B 为 NULL，则返回 NULL；如果 A 大于等于 B，则返 回 TRUE，反之返回 FALSE | | A [NOT] BETWEEN B
AND C | 基本数据类型 | 如果 A，B 或者 C 任一为 NULL，则结果为 NULL。如果 A 的 值大于等于 B 而且小于或等于 C，则结果为 TRUE，反之为 FALSE。 如果使用 NOT 关键字则可达到相反的效果。 | | A IS NULL | 所有数据类型 | 如果 A 等于 NULL，则返回 TRUE，反之返回 FALSE | | A IS NOT NULL | 所有数据类型 | 如果 A 不等于 NULL，则返回 TRUE，反之返回 FALSE | | IN(数值 1, 数值 2) | 所有数据类型 | 使用 IN 运算显示列表中的值 | | A [NOT] LIKE B | STRING 类型 | B 是一个 SQL 下的简单正则表达式，也叫通配符模式，如 果 A 与其匹配的话，则返回 TRUE；反之返回 FALSE。B 的表达式 说明如下：‘x%’表示 A 必须以字母‘x’开头，‘%x’表示 A 必须以字母’x’结尾，而‘%x%’表示 A 包含有字母’x’,可以 位于开头，结尾或者字符串中间。如果使用 NOT 关键字则可达到 相反的效果。 | | A RLIKE B, A REGEXP B | STRING 类型 | B 是基于 java 的正则表达式，如果 A 与其匹配，则返回 TRUE；反之返回 FALSE。匹配使用的是 JDK 中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和 整个字符串 A 相匹配，而不是只需与其字符串匹配。 |
#### 2）案例实操 （1）查询出薪水等于 5000 的所有员工
hive (hive)> select * from emp where sal =5000;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
（2）查询工资在 500 到 1000 的员工信息
hive (hive)> select * from emp where sal between 500 and 1000;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
（3）查询 comm 为空的所有员工信息
hive (hive)> select * from emp where comm is null;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
7134 MILLER CLERK 7782 1982-1-23 1300.0 NULL 50
（4）查询工资是 1500 或 5000 的员工信息
hive (hive)> select * from emp where sal IN (1500, 5000);
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
1.8 Like 和 RLike
1）使用 LIKE 运算选择类似的值
2）选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。 _ 代表一个字符。
3）RLIKE 子句
RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大 的语言来指定匹配条件。
4）案例实操
（1）查找名字以 A 开头的员工信息
hive (hive)> select * from emp where ename LIKE 'A%';
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
（2）查找名字中第二个字母为 A 的员工信息
hive (hive)> select * from emp where ename LIKE '_A%';
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
（3）查找名字中带有 A 的员工信息
hive (hive)> select * from emp where ename RLIKE '[A]';
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
##
1.9 逻辑运算符（And/Or/Not）
| 操作符 | 含义 | | — | — | | AND | 逻辑并 | | OR | 逻辑或 | | NOT | 逻辑否 |
1）案例实操
（1）查询薪水大于 1000，部门是 30
hive (hive)> select * from emp where sal>1000 and deptno=30;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
（2）查询薪水大于 1000，或者部门是 30
hive (hive)> select * from emp where sal>1000 or deptno=30;
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
7134 MILLER CLERK 7782 1982-1-23 1300.0 NULL 50
（3）查询除了 20 部门和 30 部门以外的员工信息
hive (hive)> select * from emp where deptno not IN(30, 20);
OK
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
7134 MILLER CLERK 7782 1982-1-23 1300.0 NULL 50
2. 分组
2.1 Group By 语句
GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然 后对每个组执行聚合操作。
1）案例实操：
（1）计算 emp 表每个部门的平均工资
select deptno, avg(sal) avg_sal from emp group by deptno;
deptno avg_sal
10 2916.6666666666665
20 2175.0
30 1566.6666666666667
50 1300.0
（2）计算 emp 每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;
t.deptno t.job max_sal
20 ANALYST 3000.0
10 CLERK 1300.0
20 CLERK 1100.0
30 CLERK 950.0
50 CLERK 1300.0
10 MANAGER 2450.0
20 MANAGER 2975.0
30 MANAGER 2850.0
10 PRESIDENT 5000.0
30 SALESMAN 1600.0
2.2 Having 语句
1）having 与 where 不同点
（1）where 后面不能写分组函数，而 having 后面可以使用分组函数。 （2）having 只用于 group by 分组统计语句。
2）案例实操
（1）求每个部门的平均薪水大于 2000 的部门 求每个部门的平均工资
select deptno, avg(sal) from emp group by deptno;
deptno _c1
10 2916.6666666666665
20 2175.0
30 1566.6666666666667
50 1300.0
求每个部门的平均薪水大于 2000 的部门
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
deptno avg_sal
10 2916.6666666666665
20 2175.0
3. Join 语句
3.1 等值 Join
Hive 支持通常的 SQL JOIN 语句。
3.2 表的别名
1）好处
（1）使用别名可以简化查询。 （2）使用表名前缀可以提高执行效率。
3.3 案例实操
1）内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；
select
e.empno,
e.ename,
d.dname,
d.deptno
from
emp e
join
dept d
on
e.deptno = d.deptno;
e.empno e.ename d.dname d.deptno
7369 SMITH RESEARCH 20
7499 ALLEN SALES 30
7521 WARD SALES 30
7566 JONES RESEARCH 20
7654 MARTIN SALES 30
7698 BLAKE SALES 30
7782 CLARK ACCOUNTING 10
7788 SCOTT RESEARCH 20
7839 KING ACCOUNTING 10
7844 TURNER SALES 30
7876 ADAMS RESEARCH 20
7900 JAMES SALES 30
7902 FORD RESEARCH 20
7934 MILLER ACCOUNTING 10
2）左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。
根据员工表和部门表中的部门编号相等，查询员工编号、员工名称、部门编号和部门名称； 如果没有部门名称则补充NULL
select
e.empno,
e.ename,
e.deptno,
d.dname
from
emp e
left join
dept d
on
e.deptno=d.deptno;
e.empno e.ename e.deptno d.dname
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
7134 MILLER 50 NULL
3）右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。
查询所有部门中对应的员工信息
select
e.empno,
e.ename,
d.deptno,
d.dname
from
emp e
right join
dept d
on
e.deptno=d.deptno;
e.empno e.ename d.deptno d.dname
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7369 SMITH 20 RESEARCH
7566 JONES 20 RESEARCH
7788 SCOTT 20 RESEARCH
7876 ADAMS 20 RESEARCH
7902 FORD 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7844 TURNER 30 SALES
7900 JAMES 30 SALES
NULL NULL 40 OPERATIONS
4）满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字 段没有符合条件的值的话，那么就使用 NULL 值替代。
两张表所有数据都会出现 查询所有员工信息和所有部门信息
select
e.empno,
e.ename,
d.deptno,
d.dname
from
emp e
full join
dept d
on
e.deptno=d.deptno;
e.empno e.ename d.deptno d.dname
7782 CLARK 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7369 SMITH 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7788 SCOTT 20 RESEARCH
7566 JONES 20 RESEARCH
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
NULL NULL 40 OPERATIONS
7134 MILLER NULL NULL
--也可以--
select
e.empno,
e.ename,
nvl(e.deptno,d.deptno),
d.dname
from
emp e
full join
dept d
on
e.deptno=d.deptno;
e.empno e.ename _c2 d.dname
7782 CLARK 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7369 SMITH 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7788 SCOTT 20 RESEARCH
7566 JONES 20 RESEARCH
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
NULL NULL 40 OPERATIONS
7134 MILLER 50 NULL
5）左内连接：左表独有的
查询员工信息，所在部门为NULL
select
e.empno,
e.ename,
e.deptno,
d.dname
from
emp e
left join
dept d
on
e.deptno=d.deptno
where d.dname is NULL;
e.empno e.ename e.deptno d.dname
7134 MILLER 50 NULL
--也可以 ，减去右表的信息--
select
e.empno,
e.ename,
e.deptno
from
emp e
where e.deptno not in (
select
deptno
from
dept
);
e.empno e.ename e.deptno
7134 MILLER 50
6）右内连接 右表独有部分
查询部门中不存在任何员工的部门信息
select
d.deptno,
d.dname
from
emp e
right join
dept d
on
e.deptno=d.deptno
where e.empno is NULL;
d.deptno d.dname
40 OPERATIONS
7）取左右两表特有的数据
查询员工特有信息和部门特有信息
--利用满外连接，扣掉中间部分--
select
e.empno,
e.ename,
--nvl(e.deptno,d.deptno),
e.deptno,
d.deptno,
d.dname
from
emp e
full join
dept d
on e.deptno=d.deptno
where
e.deptno is NULL or d.deptno is NULL;
e.empno e.ename e.deptno d.deptno d.dname
NULL NULL NULL 40 OPERATIONS
7134 MILLER 50 NULL NULL
--或者利用左内连接和右内连接，用union拼接一下--
select *
from(
select
e.empno,
e.ename,
e.deptno,
d.deptno,
d.dname
from
emp e
left join
dept d
on
e.deptno=d.deptno
where d.deptno is NULL
union
select
e.empno,
e.ename,
e.deptno,
d.deptno,
d.dname
from
emp e
right join
dept d
on
e.deptno=d.deptno
where e.deptno is NULL)tmp;
tmp.empno tmp.ename tmp.deptno tmp.dname
NULL NULL NULL 40
7134 MILLER 50 NULL
union 、union all：区别在于去重，共同点在于都是拼接查询结果
union:去重 union all:不去重 如果需求本身不存在重复数据，那么使用 union,union all 效果相同，使用 union all，效率更高。 union多做了一次去重，但结果和不做去重的union all相同，故效率比不上union all。
8）多表连接
注意：连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接条件。 先创建一个城市表，导入数据
create table if not exists location( loc int,
loc_name string
)
row format delimited fields terminated by '\t';
hive (hive)> select * from location;
OK
location.loc location.loc_name
1700 Beijing
1800 London
1900 Tokyo
查询员工姓名，部门名称及部门所在城市名称
select
e.ename,
d.dname,
l.loc_name
from
emp e
join
dept d
on e.deptno=d.deptno
join
location l
on d.loc=l.loc;
...
Total jobs = 1
...
Number of reduce tasks is set to 0 since there's no reduce operator
e.ename d.dname l.loc_name
SMITH RESEARCH London
ALLEN SALES Tokyo
WARD SALES Tokyo
JONES RESEARCH London
MARTIN SALES Tokyo
BLAKE SALES Tokyo
CLARK ACCOUNTING Beijing
SCOTT RESEARCH London
KING ACCOUNTING Beijing
TURNER SALES Tokyo
ADAMS RESEARCH London
JAMES SALES Tokyo
FORD RESEARCH London
MILLER ACCOUNTING Beijing
大多数情况下，Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。本例中会首先启动一个 MapReduce job 对表 e 和表 d 进行连接操作，然后会再启动一个 MapReduce job 将 第一个 MapReduce job 的输出和表 l;进行连接操作。 注意：为什么不是表 d 和表 l 先进行连接操作呢？这是因为 Hive 总是按照从左到右的 顺序执行的。 优化：当对 3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接键的话，那么只会产生一个 MapReduce job
3.4 笛卡尔积
1）笛卡尔集会在下面条件下产生
（1）省略连接条件 （2）连接条件无效 （3）所有表中的所有行互相连接
2）案例实操
select ename,dname from emp,dept;
或
select
e.ename,
d.dname
from emp e
join dept d;
ename dname
SMITH ACCOUNTING
SMITH RESEARCH
SMITH SALES
SMITH OPERATIONS
ALLEN ACCOUNTING
ALLEN RESEARCH
ALLEN SALES
ALLEN OPERATIONS
WARD ACCOUNTING
WARD RESEARCH
WARD SALES
WARD OPERATIONS
JONES ACCOUNTING
JONES RESEARCH
JONES SALES
JONES OPERATIONS
MARTIN ACCOUNTING
MARTIN RESEARCH
MARTIN SALES
MARTIN OPERATIONS
BLAKE ACCOUNTING
BLAKE RESEARCH
BLAKE SALES
BLAKE OPERATIONS
CLARK ACCOUNTING
CLARK RESEARCH
CLARK SALES
CLARK OPERATIONS
SCOTT ACCOUNTING
SCOTT RESEARCH
SCOTT SALES
SCOTT OPERATIONS
KING ACCOUNTING
KING RESEARCH
KING SALES
KING OPERATIONS
TURNER ACCOUNTING
TURNER RESEARCH
TURNER SALES
TURNER OPERATIONS
ADAMS ACCOUNTING
ADAMS RESEARCH
ADAMS SALES
ADAMS OPERATIONS
JAMES ACCOUNTING
JAMES RESEARCH
JAMES SALES
JAMES OPERATIONS
FORD ACCOUNTING
FORD RESEARCH
FORD SALES
FORD OPERATIONS
MILLER ACCOUNTING
MILLER RESEARCH
MILLER SALES
MILLER OPERATIONS
MILLER ACCOUNTING
MILLER RESEARCH
MILLER SALES
MILLER OPERATIONS
4. 排序
4.1 全局排序（Order By）
Order By：全局排序，只有一个 Reducer
1）使用ORDER BY 子句排序
ASC（ascend）: 升序（默认） DESC（descend）: 降序
2）ORDER BY 子句在 SELECT 语句的结尾
3）案例实操
（1）查询员工信息按工资升序排列
hive (hive)> select * from emp order by sal;
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7134 MILLER CLERK 7782 1982-1-23 1300.0 NULL 50
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
（2）查询员工信息按工资降序排列
select * from emp order by sal desc;
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
7134 MILLER CLERK 7782 1982-1-23 1300.0 NULL 50
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
4.2 按照别名排序
按照员工薪水的 2 倍排序
hive (hive)> select ename, sal*2 twosal from emp order by twosal;
ename twosal
SMITH 1600.0
JAMES 1900.0
ADAMS 2200.0
MARTIN 2500.0
WARD 2500.0
MILLER 2600.0
MILLER 2600.0
TURNER 3000.0
ALLEN 3200.0
CLARK 4900.0
BLAKE 5700.0
JONES 5950.0
FORD 6000.0
SCOTT 6000.0
KING 10000.0
4.3 多个列排序
按照部门和工资升序排序
hive (hive)> select ename, deptno, sal from emp order by deptno, sal;
ename deptno sal
MILLER 10 1300.0
CLARK 10 2450.0
KING 10 5000.0
SMITH 20 800.0
ADAMS 20 1100.0
JONES 20 2975.0
SCOTT 20 3000.0
FORD 20 3000.0
JAMES 30 950.0
MARTIN 30 1250.0
WARD 30 1250.0
TURNER 30 1500.0
ALLEN 30 1600.0
BLAKE 30 2850.0
MILLER 50 1300.0
4.4 每个 Reduce 内部排序（Sort By）
Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排 序，此时可以使用 sort by。 Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集 来说不是排序。
1）设置 reduce 个数
hive (hive)> set mapreduce.job.reduces=3;
2）查看设置 reduce 个数
hive (hive)> set mapreduce.job.reduces;
mapreduce.job.reduces=3
3）根据部门编号降序查看员工信息
hive (hive)> select * from emp sort by deptno desc;
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7134 MILLER CLERK 7782 1982-1-23 1300.0 NULL 50
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
4）将查询结果导入到文件中（按照部门编号降序排序）
insert overwrite local directory '/opt/module/datas/sortby-result'
select * from emp sort by deptno desc;
[mhk@hadoop102 sortby-result]$ ll
总用量 12
-rw-r--r--. 1 mhk mhk 334 1月 23 17:15 000000_0
-rw-r--r--. 1 mhk mhk 282 1月 23 17:15 000001_0
-rw-r--r--. 1 mhk mhk 91 1月 23 17:15 000002_0
[mhk@hadoop102 sortby-result]$ cat 000000_0
7134MILLERCLERK77821982-1-231300.0\N50
7844TURNERSALESMAN76981981-9-81500.00.030
7654MARTINSALESMAN76981981-9-281250.01400.030
7698BLAKEMANAGER78391981-5-12850.0\N30
7788SCOTTANALYST75661987-4-193000.0\N20
7839KINGPRESIDENT\N1981-11-175000.0\N10
7782CLARKMANAGER78391981-6-92450.0\N10
[mhk@hadoop102 sortby-result]$ cat 000001_0
7900JAMESCLERK76981981-12-3950.0\N30
7521WARDSALESMAN76981981-2-221250.0500.030
7499ALLENSALESMAN76981981-2-201600.0300.030
7876ADAMSCLERK77881987-5-231100.0\N20
7566JONESMANAGER78391981-4-22975.0\N20
7934MILLERCLERK77821982-1-231300.0\N10
[mhk@hadoop102 sortby-result]$ cat 000002_0
7902FORDANALYST75661981-12-33000.0\N20
7369SMITHCLERK79021980-12-17800.0\N20
4.5 分区（Distribute By）
Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。distribute by **子句可以做这件事。distribute by **类似 MR 中 partition（自定义分区），进行分区，结合 sort by 使用。 对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。
1）案例实操：
先按照部门编号分区，再按照员工编号降序排序。
hive (hive)> set mapreduce.job.reduces=3;
hive (hive)> insert overwrite local directory './distribute-by'
select
deptno,ename,sal
from emp
distribute by deptno
sort by sal;
[mhk@hadoop102 hive]$ cd distribute-by/
[mhk@hadoop102 distribute-by]$ ll
总用量 12
-rw-r--r--. 1 mhk mhk 96 1月 20 14:30 000000_0
-rw-r--r--. 1 mhk mhk 48 1月 20 14:30 000001_0
-rw-r--r--. 1 mhk mhk 95 1月 20 14:30 000002_0
[mhk@hadoop102 distribute-by]$ cat 000000_0
30 JAMES 950.0
30 WARD 1250.0
30 MARTIN 1250.0
30 TURNER 1500.0
30 ALLEN 1600.0
30 BLAKE 2850.0
[mhk@hadoop102 distribute-by]$ cat 000001_0
10 MILLER 1300.0
10 CLARK 2450.0
10 KING 5000.0
[mhk@hadoop102 distribute-by]$ cat 000002_0
20 SMITH 800.0
20 ADAMS 1100.0
50 MILLER 1300.0
20 JONES 2975.0
20 FORD 3000.0
20 SCOTT 3000.0
注意：
- distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后， 余数相同的分到一个区。
- Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
4.6 Cluster By
当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。 cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序 排序，不能指定排序规则为 ASC 或者 DESC。 （1）以下两种写法等价
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
insert overwrite local directory './cluster-by'
select deptno,ename,sal from emp cluster by deptno;
[mhk@hadoop102 cluster-by]$ ll
总用量 12
-rw-r--r--. 1 mhk mhk 96 1月 20 14:39 000000_0
-rw-r--r--. 1 mhk mhk 48 1月 20 14:39 000001_0
-rw-r--r--. 1 mhk mhk 95 1月 20 14:39 000002_0
[mhk@hadoop102 cluster-by]$ cat 000000_0
30 BLAKE 2850.0
30 MARTIN 1250.0
30 WARD 1250.0
30 ALLEN 1600.0
30 TURNER 1500.0
30 JAMES 950.0
[mhk@hadoop102 cluster-by]$ cat 000001_0
10 MILLER 1300.0
10 KING 5000.0
10 CLARK 2450.0
[mhk@hadoop102 cluster-by]$ cat 000002_0
20 SMITH 800.0
20 FORD 3000.0
20 ADAMS 1100.0
20 SCOTT 3000.0
20 JONES 2975.0
50 MILLER 1300.0