【大数据之Hive】十一、Hive-HQL查询之基本查询

基础语法

select [all | distinct] select_expr,select_expr, ...
    from table)name          --从什么表查
    [where where_condition]  --过滤
    [group by col_list]      --分组查询
    [having col_list]        --分组后过滤
    [order by col_list]      --排序
    [cluster by col_list | [distribute by col_list] [sort by col_list] ]
    [limit number];          --限制输出的行数

基本查询（select … from …）

原始数据：
  在/opt/module/hive/datas/下创建dept.txt文件和emp.txt文件

# dept.txt下字段为：部门编号 部门名称 部门位置id
vim dept.txt
10    行政部    1700
20    财务部    1800
30    教学部    1900
40    销售部    1700

#emp.txt下字段为：员工编号 姓名 岗位 薪资 部门
vim emp.txt
7369    张三    研发    800.00    30
7499    李四    财务    1600.00    20
7521    王五    行政    1250.00    10
7566    赵六    销售    2975.00    40
7654    侯七    研发    1250.00    30
7698    马八    研发    2850.00    30
7782    金九    \N    2450.0    30
7788    银十    行政    3000.00    10
7839    小芳    销售    5000.00    40
7844    小明    销售    1500.00    40
7876    小李    行政    1100.00    10
7900    小元    讲师    950.00    30
7902    小海    行政    3000.00    10
7934    小红明    讲师    1300.00    30

创建表并导入数据：

-- 创建部门表：
create table if not exists dept(
    deptnum int,    --部门编号
    dname string,    --部门名称
    locid int    --部门位置id
)
row format delimited fields terminated by '\t';

-- 创建员工表：
create table if not exists emp(
    empnum int,    --员工编号
    ename string,    --员工姓名
    job string,    --员工岗位
    sal double,    --员工薪资
    deptnum int    --部门编号
)
row format delimited fields terminated by '\t';

--导入数据：
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 全表和特定列查询

注意：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行。
（3）关键字不能被缩写也不能分行。
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。

-- 全表查询：
selectr * from emp;

-- 选择特定列查询：
select empnum,ename from emp;

2 列别名

  重命名一个列，便于计算。
语法：

select old_col_name as new_col_name from tablename;
select old_col_name new_col_name from tablename;

如：

select ename as name,deptnum dn from emp;

3 limit语句

  查询返回多行数据，用limit限制返回的行数。

select * from emp limit 5;

--从第2行开始返回3行：
select * from emp limit 1,3;

4 where语句

  用where过滤不满足条件的行，只针对一行一行的数据，在from句后，且where句子不能用字段别名。

select * from emp where sal > 1000;

5 关系运算函数

  关系运算操作符主要用于where和having语句中。

对于模糊匹配like：用show查tables用通配符*，用select查表数据用通配符%。

6 逻辑运算函数

语法：

--查找薪水大于1000且部门是30的员工信息
select * from emp where sal > 1000 and deptnum = 30;

--查找薪水大于1000或部门是30的员工信息
select * from emp where sal > 1000 or deptnum = 30;

--查询除了部门20和30以外的员工信息：
select * from emp where deptnum not in (30,20);

7 聚合函数

--求总行数（count）：
select count(*) cnt from emp;

--求工资最大值（max）：
select max(sal) max_sal from emp;

--求工资最小值：
select min(sal) min_sal from emp;

--求工资总和：
select sum(sal) sum_sal from emp;

--求工资平均值：
select avg(sal) avg_sal from emp;

sql语句翻译成MapReduce的执行计划，在Map端读入数据，在每个Map端上对读入的数据进行聚合，就减少了shuffle的数据量，然后将每个Map聚合之后的结果汇总到一个Reduce中，在Reduce上进行统计。