Hive的基本查询
写在前面
- Linux版本：
CentOS7.5 - Hive版本：
Hive-3.1.2
正文
基本查询即：
Select…From
表和特定列查询
数据准备
（0）原始数据
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
（1）创建部门表
create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
（2）创建员工表
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）导入数据
load data local inpath '/opt/module/datas/dept.txt' into table dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;
- 全表查询
hive (default)> select * from emp;
hive (default)> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp ;
- 选择特定列查询
hive (default)> select empno, ename from emp;
注意： （1）SQL 语言大小写不敏感。 （2）SQL 可以写在一行或者多行 （3）关键字不能被缩写也不能分行 （4）各子句一般要分行写。 （5）使用缩进提高语句的可读性。
列别名
- 重命名一个列
- 便于计算
- 紧跟列名，也可以在列名和别名之间加入关键字‘AS’
举例说明：查询名称和部门
hive (default)> select ename AS name, deptno dn from emp;
算术运算符
| 运算符 | 描述 | |
|---|---|---|
| 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按位取反 |
案例实操：查询出所有员工的薪水后加1显示。
hive (default)> select sal +1 from emp;
常用函数
1）求总行数（count）
hive (default)> select count(*) cnt from emp;
2）求工资的最大值（max）
hive (default)> select max(sal) max_sal from emp;
3）求工资的最小值（min）
hive (default)> select min(sal) min_sal from emp;
4）求工资的总和（sum）
hive (default)> select sum(sal) sum_sal from emp;
5）求工资的平均值（avg）
hive (default)> select avg(sal) avg_sal from emp;
Limit语句
典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
hive (default)> select * from emp limit 5;
hive (default)> select * from emp limit 2,3;
Where语句
- 用WHERE子句，将不满足条件的行过滤掉
- WHERE子句紧随FROM子句
案例实操：查询出薪水大于1000的所有员工
hive (default)> select * from emp where sal >1000;
注意：where子句中不能使用字段别名。
比较运算符（Between/In/ Is Null）
1）下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。
| 操作符 | 支持的数据类型 | 描述 |
|---|---|---|
| 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，则结果为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相匹配，而不是只需与其字符串匹配。 |
2）案例实操 （1）查询出薪水等于5000的所有员工
hive (default)> select * from emp where sal =5000;
（2）查询工资在500到1000的员工信息
hive (default)> select * from emp where sal between 500 and 1000;
（3）查询comm为空的所有员工信息
hive (default)> select * from emp where comm is null;
（4）查询工资是1500或5000的员工信息
hive (default)> select * from emp where sal IN (1500, 5000);
Like和RLike
1）使用LIKE运算选择类似的值 2）选择条件可以包含字符或数字:
- % 代表零个或多个字符(任意个字符)。
- _ 代表一个字符。 3）RLIKE子句 RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。 4）案例实操 （1）查找名字以A开头的员工信息
hive (default)> select * from emp where ename LIKE 'A%';
（2）查找名字中第二个字母为A的员工信息
hive (default)> select * from emp where ename LIKE '_A%';
（3）查找名字中带有A的员工信息
hive (default)> select * from emp where ename RLIKE '[A]';
逻辑运算符（And/Or/Not）
| 操作符 | 含义 |
|---|---|
| AND | 逻辑并 |
| OR | 逻辑或 |
| NOT | 逻辑否 |
案例实操
- 查询薪水大于1000，部门是30
hive (default)> select * from emp where sal>1000 and deptno=30;
- 查询薪水大于1000，或者部门是30
hive (default)> select * from emp where sal>1000 or deptno=30;
- 查询除了20部门和30部门以外的员工信息
hive (default)> select * from emp where deptno not IN(30, 20);
全文结束！！！
