1、基本查询 select * from emp; 查询注意： （1）SQL 语言大小写不敏感。 （2）SQL 可以写在一行或者多行 （3）关键字不能被缩写也不能分行 （4）各子句一般要分行写。 （5）使用缩进提高语句的可读性。 2、别名查询 as或者空格 select ename AS name, deptno dn from emp; 好处： 便于计算 1）使用别名可以简化查询。 （2）使用表名前缀可以提高执行效率。 3、算数运算符 算术运算符及运算的字段是数字类型 A+B A和B 相加 A-B A减去B AB A和B 相乘 A/B A除以B A%B A对B取余 A&B A和B按位取与 A|B A和B按位取或 A^B A和B按位取异或 ~A A 按位取反 4、常用函数 表中总行数、总条数 count select count() cnt from emp; 最大值 max select max(sal) max_sal from emp; 最小值 min select min(sal) min_sal from emp; 求和 sum select sum(sal) sum_sal from emp; 平均值 avg select avg(sal) avg_sal from emp; 5、查询前几行 limit select * from emp limit 5; 6、条件查询where select * from emp where sal >1000; 紧跟from 1、计较运算符 条件后比较跟比较 A=B A<=>B A<>B, A!=B A<B A<=B A>B A>=B A [NOT] BETWEEN B AND C A IS NULL A IS NOT NULL IN(数值1, 数值2) A [NOT] LIKE B 模糊查询 %代表任意多个字符 select * from emp where sal LIKE ‘2%’; _代表任意一个 select * from emp where sal LIKE ‘_2%’; A RLIKE B, A REGEXP B RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。 select * from emp where sal RLIKE ‘[2]’; 逻辑运算符 AND 逻辑并 OR 逻辑或 NOT 逻辑否 7、表连接join 只支持等值连接，不支持非等值连接。 内连接 select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno; 左外连接 select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno; 右外连接 select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno; 满外连接 select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno; 多表连接 注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。 SELECT e.ename, d.deptno, l. loc_name FROM emp e JOIN dept d ON d.deptno = e.deptno JOIN location l ON d.loc = l.loc; 大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。 注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。 笛卡尔积 select empno, dname from emp, dept; 8、分组 group by elect t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno; 按两个字段分组 select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job; having having与where不同点 （1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。 （2）where后面不能写分组函数，而having后面可以使用分组函数。 （3）having只用于group by分组统计语句。 select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000; 9、排序 全局排序（Order By） Order By：全局排序，一个Reducer ASC（ascend）: 升序（默认） DESC（descend）: 降序 select * from emp order by sal desc; 按别名 按多列排序 每个MapReduce内部排序（Sort By） Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。 要设置reduce数量 set mapreduce.job.reduces=3; 分区排序（Distribute By） Distribute By：类似MR中partition，进行分区，结合sort by使用。 注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。 要有多个reduce Cluster By 当distribute by和sorts by字段相同时，可以使用cluster by方式。 cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。 select * from emp cluster by deptno; select * from emp distribute by deptno sort by deptno; 10、分桶以抽样 分区、分通做大区别： partition分区针对的是数据的存储路径（目录）；buck分桶针对的是数据文件。 分桶是将数据集分解成更容易管理的若干部分的另一个技术 适合大量数据 注意： 数据量太小默认不分桶 分桶一定要走mapreduce 分桶后方便进行抽样调查 分桶原理 将要分桶的数据字段的值（猜测或者hash值）对分桶数取余，（0–第一个桶…）。 抽样查询原理 抽样查询的底层是把所有数据按照字段的hash值/y 分成y个分区（相当于hadoop里边的分区），然后取第x个去中的数据。数据量大的时候效果明显。 操作 创建分桶表 通过子查询添加数据 set hive.enforce.bucketing=true 分桶抽样查询 select * from stu_buck tablesample(bucket 1 out of 4 on id); 注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。 y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。 x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。 注意：x的值必须小于等于y的值， 步骤 创建分桶表 开启分桶 使用子查询导入数据 查询 11、其他常用查询函数 空字段赋值NVL 格式是NVL( string1, replace_with) 它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。 select nvl(comm,-1) from emp; CASE [] WHEN [] then [] else [] end 行转列 相关函数说明 CONCAT(string A/col, string B/col…)： 返回输入字符串连接后的结果，支持任意个输入字符串; CONCAT_WS(separator, str1, str2,…) COLLECT_SET(col) 作用是将某字段的值进行去重汇总，产生array类型字段。 例子 数据 结果 语句
