3.Hive基础命令练习

创建表格如下:

部门表：

create table if not exists dept(
    deptno int,    -- 部门编号
    dname string,  -- 部门名称
    loc int        -- 部门位置
)
row format delimited fields terminated by '\t';

员工表：

create table if not exists emp(
    empno int,      -- 员工编号
    ename string,   -- 员工姓名
    job string,     -- 员工岗位（大数据工程师、前端工程师、java工程师）
    sal double,     -- 员工薪资
    deptno 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.基本查询

起别名（as可省略）：

select ename as name, sal salary from emp;

运算符：比如将查询的工资提升10元

select ename, sal + 10 from emp;

 常用函数：

select count(1) cnt,               -- 数据库表的行数，等于count(*)
       sum(sal) sum_sal,           -- 求和
       avg(sal) avg_sal,           -- 求平均
       min(sal) min_sal,           -- 最小值
       max(sal) max_sal            -- 最大值
from emp;

 ps.count(*)返回的是数据表的行数，而count(sal)返回的是行数-null的个数

ps.当sum/avg/min/max()的数据中存在null,会自动把null忽略

查询工资在800-1000的人：

select ename from emp where sal between 800 and 1000; //[800,1000]左闭右闭
select enmae from emp where sal>=800 and sal<=1000;

工资不在800-1000的人： 

select ename from emp where sal not between 800 and 1000;
select enmae from emp where not sal>=800 and sal<=1000;

limit:

-- 显示前5
select ename from emp limit 5;
-- 显示2-5名
select ename from emp limit 1,4;

模糊查询：

查询以张开头的人

-- 通配符
select * from emp where ename like "张%";

2.分组与过滤（group by & having）

ps.where只能筛选原始数据，而having可以筛选group by后的数据

查询各个部门的平均工资：

select deptno ,avg(sal) avg_sal from emp 
group by deptno;

查询平均工资大于2000的部门和其平均工资

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

3.连接        

两表连接(默认就是内连接)

select emp.ename, dept.dname 
from emp inner join dept on emp.deptno = dept.deptno

ps.连接相当于把两张表左右拼接到了一起，拼接的条件就在on后面       

 多表连接:

select e.ename,
       d.dname,
       l.loc_name
from emp e
    right join dept d on e.deptno = d.deptno
    left join location l on d.loc = l.loc;

4.全局排序

按照工资从高到低排序

select * from emp order by sal desc;

先按照部门编号升序排序，相同部门按照工资降序排序 

select * from emp order by deptno asc, sal desc;

按照部门平均工资降序给部门排序 

select deptno , avg(sal) avg_sal from dept 
 group by deptno order by avg_sal desc;

为避免排序返回的结果太多，一般结合limit使用

 例：求薪资最高的5组数据

select * from emp order by sal desc limit 5;

内部逻辑：每个mapTask求出各自前五，然后reduceTask汇总mapTask，求出全局前五 

5.分区排序(用的少)

首先设置reduce的个数为3：

复习如何分三个区：除以三余数分别为0，1，2.

set mapreduce.job.reduces=3;

 将empno按照hash值分为3个区，区内按照工资的降序排序

select * from emp distribute by empno sort by sal desc;

cluster by：当distribute by和sort by字段相同且为升序时，可以用cluster by代替

select * from emp distribute by empno sort by empno;
等价于
select * from emp cluster by empno;

6.练习题

       编写一个SQL查询来报告 Person表中每个人的姓、名、城市和州。如果personId 的地址不在Address表中，则报告为空null

select firstname,lastname,city,state
from  person P 
left join address a on p.personId = a.personId;

解释：表格中的managerID对应的就是这个员工的经理的ID

查询收入比经理高的员工：

 select e.name   // 查询条件都是放到最后写(这与sql的执行顺序有关)

 from emplyee e

   join emplyee m on e.managerId = m.id
  
   where e.salary >= m.salary

 编写一个SQL查询，查找Person表中所有重复的电子邮箱

select  email
from Person group by email 
having count(1)>1;

编写一个SQL查询，找出所有从不订购任何东西的客户

select c.name
from Customers c 
left join Orders o  on c.id = o.customerId   //要保证所有customer留下来
where o.id is null;