Hive数据查询语言-DQL-含示例演练（Select查询数据、Join查询）

---

1. Select查询数据

语法树

• 从哪里查询取决于FROM关键字后面的table_reference。
• 表名和列名不区分大小写

---------select语法树------------
[WITH CommonTableExpression (, CommonTableExpression)*]
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 [offset,] rows];

1.1 基础语法

示例数据：

1.1.1 select_ecpr

• select_expr表示检索查询返回的列，必须至少有一个select_expr。

示例：

• 查询所有字段或者指定字段

select * from t_usa_covid19_p;
select county, cases, deaths from t_usa_covid19_p;

• 查询匹配正则表达式的所有字段

SET hive.support.quoted.identifiers = none; --反引号不在解释为其他含义，被解释为正则表达式

• 查询当前数据库
  
• 查询使用函数
  

1.1.2 ALL、DISTINCT

• 用于指定查询返回结果中重复的如何出来
  • 如果不指定则默认为ALL（返回所有匹配的行）。
  • DISTINCT指定从结果中删除重复的列。

示例：

• 返回所有匹配的行
  
• 返回所有匹配的行 去除重复的结果
  
• 多个字段distinct 整体去重
  

1.1.3 WHERE

• WHERE后面是一个布尔表达式，用于查询过滤。
• 在WHERE表达式中，可以使用Hive支持的任何函数和运算符，但聚合函数除外

示例：

• where条件中使用函数 找出州名字母长度超过10位的有哪些
  
• 统计死亡人数大于100的州
  

1.1.4 分区查询、分区裁剪

• 针对Hive分区表，在查询时可以指定分区查询，减少全表扫描，也叫做分区裁剪。
• 所谓分区裁剪指：对分区表进行查询时，会检查WHERE字句或JOIN中的ON字句是否存在对分区字段的过滤，如果存在，则仅访问查询符合条件的分区，即裁剪掉没必要访问的分区。

示例：

• 找出来自加州，累计死亡人数大于1000的县 state字段就是分区字段 进行分区裁剪 避免全表扫描

--多分区裁剪
select * from t_usa_covid19_p where count_date = "2021-01-28" and state ="California" and deaths > 1000;

1.1.5 GROUP BY

• GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
• 出现在GROUP BY中select_expr的字段：要么是GROUP BY分组的字段；要么是被聚合函数应用的字段。
  示例：
• 根据state州进行分组
  

1.1.6 HAVING

• having字句可以筛选分组后的各组数据，且可以使用聚合函数。

示例：

• 统计死亡病例数大于10000的州
  

HAVING和WHERE区别

• having是在分组后对数据进行过滤
• where是在分组前对数据进行过滤
• having后面可以使用聚合函数
• where后面不可以使用聚合函数

1.1.7 LIMIT

• LIMIT用于限制SELECT语句返回的行数。
• LIMIT接受一个或两个数字参数，这两个参数都必须是非负整数常量。

示例：

• 返回结果集的前5条
  
• 返回结果集从第1行开始 共3行
  

1.1.8 执行顺序

• 在查询过程中执行顺序：from > group（含聚合）> having > order > select ;
  • 聚合语句（sum, min, max, avg, count）要比having字句优先执行
  • where字句在查询过程中执行优先界别优先于聚合语句（sum, min, max, avg, count）

1.2 高阶语法

1.2.1 ORDER BY

• ORDER BY会对输出的结果进行全局排序
• 默认排序为升序（ASC），也可以