Hive：HQL之DQL（2）

前言

你们好我是啊晨
今儿更新hive技术
废话不多说，内容很多选择阅读，详细。
请：

一、HQL语句之DQL使用

DQL查询语句语法：

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）原始数据
dept.txt

10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700

emp.txt

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

（2）建表语句
创建部门表

create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

创建员工表

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）查看创建的表

hive (default)> show tables;
OK
tab_name
dept
emp

（4）向外部表中导入数据
导入数据

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept;
hive (default)> load data local inpath '/opt/module/datas/emp.txt' into table default.emp;

1.1.1全表和特定列查询

1．全表查询

hive (default)> select * from emp;

2．选择特定列查询

hive (default)> select empno, ename from emp;

注意：
（1）SQL 语言大小写不敏感
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写
（5）使用缩进提高语句的可读性

1.1.2列别名

1．重命名一个列

2．便于计算
3．紧跟列名，也可以在列名和别名之间加入关键字‘AS’
4．案例实操
查询名称和部门
如果是中文的别名，要把中文别名用反引号括起来

hive (default)> select ename AS name, deptno dn from emp;

1.1.3算术运算符

案例实操
查询出所有员工的薪水后加1显示。

hive (default)> select sal +1 from emp;

1.1.4常用函数

1．求总行数（count）

hive (default)> select count(*) as count from emp;

2．求工资的最大值（max）

hive (default)> select max(sal) from emp;

3．求工资的最小值（min）

hive (default)> select min(sal) from emp;

4．求工资的总和（sum）

hive (default)> select sum(sal) from emp;

5．求工资的平均值（avg）

hive (default)> select avg(sal) from emp;

1.1.5limit语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

hive (default)> select * from emp limit 5;

1.2 where条件

1．使用WHERE子句，将不满足条件的行过滤掉
2．WHERE子句紧随FROM子句
3．案例实操
查询出薪水大于1000的所有员工

select * from emp where sal > 1000;

1.2.1 比较运算符

1）下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。
表13-2 比较运算符

2）案例实操
（1）查询出薪水等于5000的所有员工

select * from emp where sal = 5000;

（2）查询工资在500到1000的员工信息

select * from emp where sal between 500 and 1000;
select * from emp where sal >= 500 and sal <= 1000;

（3）查询comm为空的所有员工信息

select * from emp where comm is null;

（4）查询工资是1500或者5000的员工信息

select * from emp where sal in (1500,5000);
select * from emp where sal = 1500 or sal = 5000;

1.2.2 Like和Rlike

1）使用LIKE运算选择类似的值
2）选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
4）案例实操
（1）查找以2开头薪水的员工信息

select * from emp where sal like '2%';

（2）查找第二个数值为2的薪水的员工信息

select * from emp where sal like