基础语法及执行顺序
基础语法
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference -- 从什么表查
[WHERE where_condition] -- 过滤
[GROUP BY col_list] -- 分组查询
[HAVING col_list] -- 分组后过滤
[ORDER BY col_list] -- 排序 统称为hive中4个by
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number] -- 限制输出的行数
执行顺序
from ->join on -> where -> group by -> having -> select -> distinct -> order by -> limit -> union
基本查询
全表和特定列查询
- 全表查询
select * from test_table;
- 特定列查询
select col1, col2 from test_table;
列别名
select col1 AS aa from test_table;
其中AS关键字可以省略
Limit语句
典型的查询会返回多行数据。limit子句用于限制返回的行数
select * from test_table limit 5;
select * from test_table limit 2,3; -- 表示从第2行开始，向下抓取3行
Where语句
select * from test_table where col1 > 1000;
注意：where子句中不能使用字段别名。
关系运算函数
| 操作符 | 支持的数据类型 | 描述 |
|---|---|---|
| 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相匹配，而不是只需与其字符串匹配。 |
逻辑运算函数
| 操作符 | 含义 |
|---|---|
| and | 逻辑并 |
| or | 逻辑或 |
| not | 逻辑否 |
聚合函数
| 函数名 | 说明 |
|---|---|
| count(*) | 表示统计所有行数，包含null值； |
| count(某列) | 表示该列一共有多少行，不包含null值； |
| max() | 求最大值，不包含null，除非所有值都是null； |
| min() | 求最小值，不包含null，除非所有值都是null； |
| sum() | 求和，不包含null。 |
| avg() | 求平均值，不包含null。 |
分组
Group By语句
Group By语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
select count(*)
from test_table t
group by t.col1;
Having语句
having与where不同点
- where后面不能写分组聚合函数，而having后面可以使用分组聚合函数
- having只用于group by分组统计语句
select count(*) sum
from test_table t
group by t.col1
having sum > 2
Join语句
Hive支持通常的sql join语句，但是只支持等值连接，不支持非等值连接，关键字 join ... on ...
| 连接类型 | 说明 |
|---|---|
| 内连接 | 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来 |
| 左外连接 | join操作符左边表中符合where子句的所有记录将会被返回 |
| 右外连接 | join操作符右边表中符合where子句的所有记录将会被返回 |
| 多表连接 | 连接n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件 |
| 满外连接 | 将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用null值替代 |
联合（union & union all）
union和union all都是上下拼接sql的结果，这点是和join有区别的，join是左右关联，union和union all是上下拼接。union去重，union all不去重。 union和union all在上下拼接sql结果时有两个要求：
- 两个sql的结果，列的个数必须相同
- 两个sql的结果，上下所对应列的类型必须一致
排序
全局排序 Order By
全局排序，只有一个Reduce
-
asc（ascend）：升序（默认）
-
desc（descend）：降序
单个reducer排序 sort by
注意：因为单独使用sort by的话，只能给每个分区内的数据排序，但是不能指定分区内的数据都有哪些，属于随机给数据分配分区
因此没人会单独使用sort by来排序，因为出来的结果集没有意义
分区 distribute by
指定分区字段，一般在sort by之前都会加上 distribute by
分区排序 Cluster by
cluster by属于 distribute by + sort by的结合，但是前提是 分区字段和排序字段是同一个的时候，才可以替换。并且排序只能升序排，不能再指定desc和asc
