一、查询语句
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
1、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 [offset,] rows] ######## select [ALL | DISTINCT] #all:查所有;DISTINCT:去重 select_expr, select_expr #要查的字段 FROM table_reference #从哪张表 [WHERE where_condition] #where条件 [LIMIT [offset,] rows] #查询的条数,感觉有点像tain -n ....
Queries
全表查询、指定字段查询 =、 >=、 <=、 between and、limit (not)in/is(not)null max/min/count/sum/avg group by/having join
2、全表查询、指定字段查询
全表查询
select * from table_name;
指定字段查询
select t.empno, t.ename, t.deptno from emp t;
3、=、 >=、 <=、 between and、limit
#between and hive (default)> select t.empno, t.ename, t.deptno from emp t where t.sal between 800 and 1500; #limit hive (default)> select * from emp limit 3;
4、(not)in/is(not)null
## hive (default)> select t.empno, t.ename, t.deptno from emp t where comm is null;
5、max/min/count/sum/avg
## hive (default)> select count(*) cnt from emp; ## hive (default)> select max(sal) max_sal from emp; ## hive (default)> select sum(sal) from emp; ## hive (default)> select avg(sal) from emp;
6、group by/having
############ group by ########### ##每个部门的平均工资 hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno; ##每个部门中每个岗位的最高薪水,要查的字段必须出现在group by中 hive (default)> select t.deptno, t.job, max(t.sal) avg_sal from emp t group by t.deptno, job; ########### having ############# where是针对单条记录进行筛选 having是针对分组结果进行筛选 #求每个部门的平均薪水大于2000 hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000; ########### join ############# 两个表进行连接 m n m表中一条记录和n表中的一条记录组成一条记录 #等值join join ... on hive (default)> select e.empno, e.ename, e.deptno from emp e join dept d on e.deptno = d.deptno; #左连接 left join hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e left join dept d on e.deptno = d.deptno; #右连接 right join
hive (default)>select e.empno, e.ename, e.deptno, d.dname from emp e right join dept d on e.deptno = d.deptno;
#全连接
hive (default)> select e.empno, e.ename, e.deptno, d.dname from emp e full join dept d on e.deptno = d.deptno;