hive笔记（四）：查询、分组-运算符/limit/where/like/rlike/group by/having

目录

基本查询

全表和特定列查询

列别名

算术运算符

比较运算符

逻辑运算符

常用函数

limit语句

where语句

like和Rlike语句

分组

group by语句

having语句

---

基本查询

全表和特定列查询

（1）数据准备

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

（2）建表

dept

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

emp

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 ' ';

（3）导入数据

#dept
load data local inpath '/root/datas/dept.txt' into table dept;

#emp
load data local inpath '/root/datas/emp.txt' into table emp;

（4）全表查询

select * from dept;

select * from emp;

 （5）特定列查询

select empno,ename from emp;

列别名

重命名一个列便于计算，列别名紧跟列名，也可以在列名和别名之间加入关键字AS。

select empno AS pno,ename name from emp;

算术运算符

运算符	描述
A+B	加
A-B	减
A*B	乘
A/B	除
A%B	取余
A&B	按位取与
A|B	按位取或
A^B	按位取异或
~A	按位取反

比较运算符

操作符	数据类型	描述
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（C>=A>=B）则结果为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。 例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

例子： 

#查询工资在500到1000的员工
select * from emp where sal between 500 and 1000;

#查询comm为空的所有员工
select * from emp where comm is null;

#查询工资是500或5000的员工
select * from emp where sal IN (500, 5000);

逻辑运算符

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

例子：

#查询薪水大于1000，部门是30
select * from emp where sal>1000 and deptno=30;

#查询薪水大于1000，或者部门是30
select * from emp where sal>1000 or deptno=30;

#查询除了20部门和30部门以外的员工信息
select * from emp where deptno not IN(30, 20);

常用函数

（1）总行数（count）

select count(*) con from emp;

（2）最大值（max）

select max(sal) max_sal from emp;

（3）最小值（min）

select min(sal) min_sal from emp;

（4）总和（sum）

select sum(sal) sum_sal from emp;

 

（5）平均值（avg）

select avg(sal) avg_sal from emp;

limit语句

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

select * from emp limit 3;

where语句

使用WHERE子句，将不满足条件的行过滤掉，WHERE子句紧随FROM子句。

查询sal大于2000的员工：

select * from emp where sal > 2000;

like和Rlike语句

使用LIKE运算选择类似的值，选择条件可以包含字符或数字：

% 代表零个或多个字符(任意个字符)；

_ 代表一个字符；

#查找名字以A开头的员工信息
select * from emp where ename LIKE 'A%';

#查找名字中第二个字母为A的员工信息
select * from emp where ename LIKE '_A%';

RLIKE 子句

RLIKE子句是Hive中这个like功能的一个扩展，可以通过Java的正则表达式来指定匹配条件。

#查找名字中带有A的员工信息
select * from emp where ename RLIKE '[A]';

分组

group by语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

#计算emp表每个部门的平均工资
select deptno,avg(sal) avg_sal from emp t group by deptno;

#计算emp每个部门中每个岗位的最高薪水
select deptno,job,max(sal) max_sal from emp group by deptno,job;

 

having语句

where后面不能写分组函数，having后面可以使用分组函数；

having只用于group by分组统计语句；

#求部门的平均工资大于500的部门
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 500;

本文仅仅是学习笔记！！！