總目錄:http://www.reibang.com/p/e406a9bc93a9

Hadoop - 子目錄：http://www.reibang.com/p/9428e443b7fd

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 number]

基礎(chǔ)查詢

全局查詢

全表查詢

select * from emp;
使用*號來表示全部列。

全表查詢

指定列查詢

select ename,job from emp;

指定列查詢

注意事項(xiàng)

（1）SQL 語言大小寫不敏感幕帆。
（2）SQL 可以寫在一行或者多行
（3）關(guān)鍵字不能被縮寫也不能分行
（4）各子句一般要分行寫常熙。
（5）使用縮進(jìn)提高語句的可讀性纽竣。

別名

select empno as id,ename as name from emp;

別名

limit語句

LIMIT 子句用于限制返回的行數(shù)幽勒。
select * from emp limit 5;

limit

常用函數(shù)

• count

  
  
  例子

• max

  
  
  例子

• min

  
  
  例子

• sum

  
  
  例子

• avg

  
  
  例子

運(yùn)算符

算術(shù)運(yùn)算符

常用算術(shù)運(yùn)算符一覽：

select ename,sal*2 as sal from emp;

將所有員工工資翻倍

比較運(yùn)算符

常用比較運(yùn)算符一覽：

• 查詢出薪水等于 5000 的所有員工
  select * from emp where sal =5000;
• 查詢工資在 500 到 1000 的員工信息
  select * from emp where sal between 500 and 1000;
• 查詢 comm 為空的所有員工信息
  select * from emp where comm is null;
• 查詢工資是 1500 或 5000 的員工信息
  select * from emp where sal IN (1500, 5000);
• 查找以 2 開頭薪水的員工信息
  select * from emp where sal LIKE '2%';
• 查找薪水中含有 2 的員工信息
  select * from emp where sal RLIKE '[2]';

邏輯運(yùn)算符

常用邏輯運(yùn)算符一覽：

• 查詢薪水大于 1000救湖，部門是 30
  select * from emp where sal>1000 and deptno=30;
• 查詢薪水大于 1000愧杯，或者部門是 30
  select * from emp where sal>1000 or deptno=30;
• 查詢除了 20 部門和 30 部門以外的員工信息
  select * from emp where deptno not IN(30, 20);

join操作

等值Join

Hive 支持通常的 SQL JOIN 語句，但是只支持等值連接鞋既，不支持非等值連接力九。

• 根據(jù)員工表和部門表中的部門編號相等耍铜，查詢員工編號、員工名稱和部門名稱
  select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
  

內(nèi)連接

內(nèi)連接：只有進(jìn)行連接的兩個表中都存在與連接條件相匹配的數(shù)據(jù)才會被保留下來跌前。
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

左外連接

左外連接：JOIN 操作符左邊表中符合 WHERE 子句的所有記錄將會被返回棕兼。
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

右外連接

右外連接：JOIN 操作符右邊表中符合 WHERE 子句的所有記錄將會被返回。
select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

滿外連接

滿外連接：將會返回所有表中符合 WHERE 語句條件的所有記錄抵乓。如果任一表的指定字段沒有符合條件的值的話伴挚，那么就使用 NULL 值替代。
select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

多表連接

注意：連接 n 個表灾炭，至少需要 n-1 個連接條件茎芋。例如：連接三個表，至少需要兩個連接條件咆贬。
SELECT a.name, b.age, c.gender FROM a1 a JOIN b1 b ON b.deptno = a.deptno JOIN c1 c ON c.loc = c.loc;

分組查詢

Group By 語句

GROUP BY 語句通常會和聚合函數(shù)一起使用败徊，按照一個或者多個列隊(duì)結(jié)果進(jìn)行分組，然后對每個組執(zhí)行聚合操作掏缎。

• 計(jì)算 emp 表每個部門的平均工資
  select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
  

• 計(jì)算 emp 每個部門中每個崗位的最高薪水
  select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;
  

Having語句

having和where的不同點(diǎn)：
（1）where 針對表中的列發(fā)揮作用皱蹦，查詢數(shù)據(jù)；having 針對查詢結(jié)果中的列發(fā)揮作用眷蜈，
篩選數(shù)據(jù)沪哺。
（2）where 后面不能寫聚合函數(shù)，而 having 后面可以使用聚合函數(shù)酌儒。
（3）having 只用于 group by 分組統(tǒng)計(jì)語句辜妓。

• 求每個部門的平均薪水大于 2000 的部門
  select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
  

排序

ORDER BY語句

• 查詢員工信息按工資升序排列
  select * from emp order by sal;
  

• 查詢員工信息按工資降序排列
  select * from emp order by sal desc;
  

多列排序

select ename, deptno, sal from emp order by deptno, sal ;

Sort By語句

Sort By：每個 Reducer 內(nèi)部進(jìn)行排序，對全局結(jié)果集來說不是排序忌怎。
首先我們要設(shè)置一下reduce的數(shù)量籍滴。
set mapreduce.job.reduces=3;
當(dāng)然，我們也可以查看當(dāng)前設(shè)置的reduce數(shù)量榴啸。
set mapreduce.job.reduces;

隨便查詢一下
select * from emp sort by empno desc;

截圖的第一行中，map數(shù)量1鸥印，reduce數(shù)量3勋功。

Distribute By語句

Distribute By：類似 MR 中 partition，進(jìn)行分區(qū)库说，結(jié)合 sort by 使用狂鞋。

• 先按照部門編號分區(qū)，再按照員工編號降序排序潜的。
  select * from emp distribute by deptno sort by empno desc;
  

Cluster By語句

當(dāng) distribute by 和 sorts by 字段相同時骚揍，可以使用 cluster by 方式。
cluster by 除了具有 distribute by 的功能外還兼具 sort by 的功能啰挪。但是排序只能是升序排序信不，不能指定排序規(guī)則為 ASC 或者 DESC纤掸。
比如下面兩種寫法是等價的。
select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;

分桶

分桶表

• 創(chuàng)建分桶表

create table stu_buck(id int, name string)
clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';

• 創(chuàng)建個普通表

create table stu(id int, name string)
row format delimited fields terminated by '\t';

• 導(dǎo)入數(shù)據(jù)

load data local inpath '/usr/hdk/student.txt' into table stu;

數(shù)據(jù)集關(guān)注微信公眾號hx_bigdata,回復(fù)1437.

• 設(shè)置兩個屬性

set hive.enforce.bucketing=true;
set mapreduce.job.reduces=-1;

• 子查詢導(dǎo)入數(shù)據(jù)

insert into table stu_buck select id, name from stu;

四個桶

抽樣查詢

對于非常大的數(shù)據(jù)集浑塞，有時用戶需要使用的是一個具有代表性的查詢結(jié)果而不是全部結(jié)果借跪。Hive 可以通過對表進(jìn)行抽樣來滿足這個需求。
語法：TABLESAMPLE(BUCKET x OUT OF y)
y 必須是 table 總 bucket 數(shù)的倍數(shù)或者因子酌壕。hive 根據(jù) y 的大小掏愁，決定抽樣的比例。
例如卵牍，table 總共分了 4 份果港，當(dāng) y=2 時，抽取(4/2=)2 個 bucket 的數(shù)據(jù)糊昙，當(dāng) y=8 時辛掠，抽取(4/8=)1/2
個 bucket 的數(shù)據(jù)。
x 表示從哪個 bucket 開始抽取释牺，如果需要取多個分區(qū)萝衩，以后的分區(qū)號為當(dāng)前分區(qū)號加上y。
例如没咙，table 總 bucket 數(shù)為 4猩谊，tablesample(bucket 1 out of 2)，表示總共抽燃栏铡（4/2=）2 個bucket 的數(shù)據(jù)牌捷，抽取第 1(x)個和第 3(x+y)個 bucket 的數(shù)據(jù)。

• 抽取一個桶
  select * from stu_buck tablesample(bucket 1 out of 4 on id);