大数据技术之Hive 第6章 查询 基本查询

大数据技术之Hive 第6章 查询 基本查询

第6章 查询

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+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 number]

6.1 基本查询(Select…From)

6.1.1 全表和特定列查询

0)数据准备

(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 "	";

(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 "	";

(3)导入数据

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

1)全表查询

hive (default)> select * from emp;
hive (default)> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp ;

2)选择特定列查询

hive (default)> select empno, ename from emp;

注意:

(1)SQL 语言大小写不敏感。

(2)SQL 可以写在一行或者多行

(3)关键字不能被缩写也不能分行

(4)各子句一般要分行写。

(5)使用缩进提高语句的可读性。

6.1.2 列别名

1)重命名一个列

2)便于计算

3)紧跟列名,也可以在列名和别名之间加入关键字‘AS’

4)案例实操

查询名称和部门

hive (default)> select ename AS name, deptno dn from emp;

6.1.3 算术运算符

运算符 描述
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;

6.1.4 常用函数

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;

6.1.5 Limit语句

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

hive (default)> select * from emp limit 5;
hive (default)> select * from emp limit 2,3;

6.1.6 Where语句

1)使用WHERE子句,将不满足条件的行过滤掉

2)WHERE子句紧随FROM子句

3)案例实操

查询出薪水大于1000的所有员工

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

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

6.1.7 比较运算符(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);

6.1.8 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]";

6.1.9 逻辑运算符(And/Or/Not)

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

1)案例实操

(1)查询薪水大于1000,部门是30

hive (default)> select * from emp where sal>1000 and deptno=30;

(2)查询薪水大于1000,或者部门是30

hive (default)> select * from emp where sal>1000 or deptno=30;

(3)查询除了20部门和30部门以外的员工信息

hive (default)> select * from emp where deptno not IN(30, 20);

6.2 分组

6.2.1 Group By语句

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

1)案例实操:

(1)计算emp表每个部门的平均工资

hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

(2)计算emp每个部门中每个岗位的最高薪水

hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by
 t.deptno, t.job;

6.2.2 Having语句

1)having与where不同点

(1)where后面不能写分组函数,而having后面可以使用分组函数。

(2)having只用于group by分组统计语句。

2)案例实操

(1)求每个部门的平均薪水大于2000的部门

求每个部门的平均工资

hive (default)> select deptno, avg(sal) from emp group by deptno;

求每个部门的平均薪水大于2000的部门

hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having
 avg_sal > 2000;

IT学习网站

牛牛IT网站

大数据高薪训练营 完结

搜狗截图20220608012235

链接:https://pan.baidu.com/s/1ssRD-BYOiiMw30EV_BLMWQ
提取码:dghu
失效加V:x923713

QQ交流群 欢迎加入

WechatIMG135

hmoban主题是根据ripro二开的主题,极致后台体验,无插件,集成会员系统
自学咖网 » 大数据技术之Hive 第6章 查询 基本查询