本文主要是介绍Hive(10):hive支持常用HQL语句，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
1、过滤
(1)where：select,update,delete,insert into
select * from emp where sal > 3000;(2)limit
select * from emp limit 1;(3)distinct,count/group by
select distinct deptno from emp;select count(distinct empno) as uv from emp;select min(sal),deptno from emp group by deptno;(4)between and
select * from emp where sal between 2000 and 3000;(5)is null & is not null(/n,"")
select ename from emp where comm is null;select ename from emp where comm is not null;(6)having
分组语句之后
select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;select deptno,avg from (select deptno,avg(sal) as avg from emp group by deptno) a where avg > 2000;注意：where和having可以在一条语句中执行，where先执行
2、聚合函数
(1)count
select count(1) from emp;(2)sum
(3)avg
select avg(sal) avg_sal from emp;(4)max
(5)min
select deptno,avg(sal),empno from emp group by deptno,empno;
select deptno,avg(sal),max(empno) from emp group by deptno;3、group by分组(去重)
select deptno,avg(sal) from emp group by deptno;select count(userid) as uv from log(日志表) group by userid4、join
分类：
等值join inner
左join left
右join right
全join full
semi join 半关联
map join 优化（大表和小表join的优化）
A:emp表
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 10B:dept表
dept.deptno dept.dname dept.loc
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
(1)等值join语句：
select e.empno,e.ename,d.deptno,e.sal from emp e join dept d on e.deptno=d.deptno;
【结果：】
e.empno e.ename d.deptno e.sal
7369 SMITH 20 800.0
7499 ALLEN 30 1600.0
7521 WARD 30 1250.0
7566 JONES 20 2975.0
7654 MARTIN 30 1250.0
7698 BLAKE 30 2850.0
7782 CLARK 10 2450.0
7788 SCOTT 20 3000.0
7839 KING 10 5000.0
7844 TURNER 30 1500.0
7876 ADAMS 20 1100.0
7900 JAMES 30 950.0
7902 FORD 20 3000.0
7934 MILLER 10 1300.0(2)左join语句：
select e.empno,e.ename,d.deptno,e.sal from emp e left join dept d on e.deptno=d.deptno;
【结果：】
e.empno e.ename d.deptno e.sal
7369 SMITH 20 800.0
7499 ALLEN 30 1600.0
7521 WARD 30 1250.0
7566 JONES 20 2975.0
7654 MARTIN 30 1250.0
7698 BLAKE 30 2850.0
7782 CLARK 10 2450.0
7788 SCOTT 20 3000.0
7839 KING 10 5000.0
7844 TURNER 30 1500.0
7876 ADAMS 20 1100.0
7900 JAMES 30 950.0
7902 FORD 20 3000.0
7934 MILLER 10 1300.0(3)右join语句：
select e.empno,e.ename,d.deptno,e.sal from emp e right join dept d on e.deptno=d.deptno;
【结果：】
e.empno e.ename d.deptno e.sal
7782 CLARK 10 2450.0
7839 KING 10 5000.0
7934 MILLER 10 1300.0
7369 SMITH 20 800.0
7566 JONES 20 2975.0
7788 SCOTT 20 3000.0
7876 ADAMS 20 1100.0
7902 FORD 20 3000.0
7499 ALLEN 30 1600.0
7521 WARD 30 1250.0
7654 MARTIN 30 1250.0
7698 BLAKE 30 2850.0
7844 TURNER 30 1500.0
7900 JAMES 30 950.0
NULL NULL 40 NULL(4)全join语句：
select e.empno,e.ename,d.deptno,e.sal from emp e full join dept d on e.deptno=d.deptno;
【结果：】e.empno e.ename d.deptno e.sal
7934 MILLER 10 1300.0
7839 KING 10 5000.0
7782 CLARK 10 2450.0
7876 ADAMS 20 1100.0
7788 SCOTT 20 3000.0
7369 SMITH 20 800.0
7566 JONES 20 2975.0
7902 FORD 20 3000.0
7844 TURNER 30 1500.0
7499 ALLEN 30 1600.0
7698 BLAKE 30 2850.0
7654 MARTIN 30 1250.0
7521 WARD 30 1250.0
7900 JAMES 30 950.0
NULL NULL 40 NULL
这篇关于Hive(10):hive支持常用HQL语句的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
