查询（一）Select...From+Where+分组查询

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available starting with Hive 0.13.0)
SELECT
FROM
WHERE
GROUP BY
ORDER BY
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
LIMIT

一、基本查询（Select…From）

1. 全表和特定列查询

1）全表查询

hive (default)> select * from emp;

2）选择特定列查询

hive (default)> select empno, ename from

注意：

（1）SQL 语言大小写不敏感。 

（2）SQL 可以写在一行或者多行

（3）关键字不能被缩写也不能分行

（4）各子句一般要分行写。

（5）使用缩进提高语句的可读性。

2. 列别名

1）重命名一个列。

2）便于计算。

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

4）案例实操:

查询名称和部门

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

OK

name dn

SMITH 20

ALLEN 30

WARD 30

JONES 20

MARTIN 30

BLAKE 30

CLARK 10

SCOTT 20

KING 10

TURNER 30

ADAMS 20

JAMES 30

FORD 20

MILLER 10

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按位取反

案例实操

查询出所有员工的薪水后加1000显示。

hive (default)> select sal +1000 from emp;

4. 常用函数

1）求总行数（count）

hive (default)> select count(1) cnt from emp;

count后的括号里的数字没有效用（比如count(1)和count(0)结果是一样的），count()就是计算指定列的行数。

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;

5. Limit语句

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

hive (default)> select * from emp limit

这里是返回表中所有数据的前5行数据。

二、 Where语句

1）使用WHERE子句，将不满足条件的行过滤掉。

2）WHERE子句紧随FROM子句。

3）案例实操：

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

hive (default)> select ename , sal from emp where

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

1）下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。

操作符

支持的数据类型

描述

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是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

2）案例实操

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

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

（2）查询工资在500到1000的员工信息，注意between ... and 是闭区间-是个范围

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

（3）查询comm(奖金)为空的所有员工信息

hive (default)> select * from emp where comm is

（4）查询工资是1500和5000的员工信息-是具体的数

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

2. Like和RLike

1）使用LIKE运算选择类似的值

2）选择条件可以包含字符或数字:

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

_ 代表一个字符。

3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

4）案例实操

（1）查找以2开头薪水的员工信息

hive (default)> select * from emp where sal LIKE '2%';

（2）查找第二个数值为2的薪水的员工信息

hive (default)> select * from emp where sal LIKE '_2%';

（3）查找薪水中含有2的员工信息-两种写法

hive (default)> select * from emp where sal RLIKE '[2]';

hive (default)> select * from emp where sal LIKE'%2%';

3. 逻辑运算符（And/Or/Not）

操作符

含义

AND

逻辑并

OR

逻辑或

NOT

逻辑否

案例实操

（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

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

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

三、分组

1. Group By语句

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

案例实操：

（1）计算emp表每个部门的平均工资

下面t作为表的别名-省略as

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

结果：

OK
t.deptno avg_sal
10 2916.6666666666665
20 2175.0
30 1566.6666666666667
Time taken: 72.134 seconds, Fetched: 3 row(s)

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

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

2. Having语句

1）having与where不同点

（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。

（2）where后面不能写分组函数，而having后面可以使用分组函数。

（3）having只用于group by分组统计语句。

2）案例实操：

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

求每个部门的平均工资

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

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

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