Hive 条件查询SQL操作大全

三 Hive 查询SQL大全

本文用到的数据包，在文章末尾

一、基本查询

1. 创建表

创建部门表

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

导入数据

load data local inpath '/opt/module/datas/dept.txt' into table
dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;

2. 全表查询

select * from emp;

3. 选择特定列查询

select empno, ename from emp;

4. 特定列查询（as可以省略）

select ename as name, deptno dn from emp;

5. 算术运算符

运算符	描述
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按位取反

举例：

select sal +1 from emp;

6. 常用函数

1. 求总行数（count）

select count(*) cnt 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;

7. Limit语句

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

select * from emp limit 5;

二、Where语句

1. 什么是Where语句

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

hive (default)> select * from emp where sal >1000;

注意：where子句中不能使用字段别名。

2. 比较运算符（Between/In/ Is Null）

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，反之返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
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相匹配，而不是只需与其字符串匹配。正则表达式参考链接：http://blog.edkso.cn/?p=269

举例：
（1）查询出薪水等于5000的所有员工

select * from emp where sal =5000;

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

select * from emp where sal between 500 and 1000;

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

 (default)> select * from emp where comm is null;

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

select * from emp where sal IN (1500, 5000);

三、附件包

链接: https://pan.baidu.com/s/1D4G5WkuVQSRnhLv3ic3tlg 密码: uaho