HiveQL的基础操作全集

第一章:sql数据操作和查询

– 1.语句命令组成：

 1. 数据定义语言（ddl）,包括create（创建）命令、alter（修改）命令、drop（删除）命令等。     -- 主要针对对象的结构
        
 2. 数据操纵语言（dml）,包括insert（插入）命令、update（更新）命令、delete（删除）命令等。  -- 主要针对表

 3. 数据查询语言（dql）,包括基本查询语句、order by子句、group by子句等。

 4. 事务控制语言（tcl）,包括commit（提交）命令、rollback（回滚）命令。

 5. 数据控制语言（dcl）,grant（授权）命令、revoke（撤销）命令。

– 2.查询结构

   select *|列名|表达式 from 表名 where 条件 group by 列名 having 条件 order by 列名 [asc/desc]  -- * 表示所有的列名称; asc 升序(默认),desc 降序

– 3.查询顺序

   select     字段或表达式（多个字段用逗号隔开）                                       -- 5
   from       表（视图或者数据集）                                                     -- 1
   where      字段的限定（多个限定用or或者and隔开,后面不能跟聚合函数）                 -- 2
   group by   字段或表达式（多个字段用逗号隔开）                                       -- 3
   having     表达式(前面必须有group by,多个表达式用or或者and隔开）                    -- 4
   order by   字段或表达式（多个字段用逗号隔开,如col1 asc, col2 desc或者1 asc,2 desc） -- 6

– 虚拟表

select 10/2 from dual;
select 1 as 数字1, '1' as 字符1 from dual;

-- select 中有*时候,并且还有其它字段或表达式,必需在*号前加表名.或表别名.
select sysdate as 当前日期,t.* from student_info t;  -- 列别名可以加as , 表的别名不能加as

– 4.聚合函数

聚合函数：聚合函数同时可以对多行数据进行操作,并返回一个结果。
聚合函数与group by 使用（字段需要分组,聚合函数必须要跟group by 联用）
注意：select 中除了聚合函数包裹的内容,其它字段必须包含于group by 后面的字段,select 只是选取聚合后的字段和值

avg	     平均值
sum	     求和
min、max 最小值、最大值
count	 统计个数


-- 计算员工表的总体平均工资、总工资、总人数、工龄最大和最小的入职日期
select avg(sal) as 平均工资,
	   sum(sal) as 总工资,
	   count(empno) as 总人数,
	   min(hiredate) as 最早入职日期,
	   max(hiredate) as 最晚入职日期
  from emp;
  
  
-- 每个部门的平均工资、总工资、总人数、工龄最大和最小的入职日期
select deptno,
       avg(sal) as 平均工资,
	   sum(sal) as 总工资,
	   count(empno) as 总人数,
	   min(hiredate) as 最早入职日期,
	   max(hiredate) as 最晚入职日期
  from emp
 group by deptno; 
 
 
-- 按照部门编号和工作类型分组统计人数
select deptno,job,
	   count(empno)
  from emp
 group by deptno,job;
 


-- count对列和对*的区别（count对列时,有空值的列不会计数）  

select count(*), --15
count(1), --15
count(1000), --15
count(comm), --4 – 不计算空值
count(job), --15
count(distinct job) – 6 – 去重
from emp;

– 5.操作符

算术运算,关系运算,和逻辑运算
   (1)算术运算: +、-、*、/
	  select e.*, (nvl(e.sal,0)+nvl(e.comm,0))*12 as 年薪 
		from emp e;
   (2)关系运算: =、!=、<>、>、>=、<、<=
	  select * from emp where empno=7369;
   (3)逻辑运算: not>and>or
	  select (case when 1=1 or 1=3 and 1=2
				   then 1
				   else 2 
			  end) as flag
		from dual;  -- 1

– 6.null操作（’’,null）

在查询条件中null值用is null作条件,非null值用 is not null做条件
空值的一些特性：
1、空值跟任何值进行算术运算,得到的结果都为空值
2、空值跟任何值进行关系运算,得到的结果都为不成立
3、空值不参与任何聚合运算
4、排序的时候,空值永远是最大的
-- 查询奖金为空
select * from emp where comm is null;
-- 1
select e.*, comm+100, sal + comm from emp e;
-- 2
select * from emp where comm<>0;
-- 3
select count(comm) from emp;
-- 4
select * from emp order by comm asc;

– 7.去重（distinct, group by, rowid）

-- 查看部门编号（去重）

	select distinct deptno from emp;
	select deptno from emp group by deptno;

-- distinct 只能出现在最前,不能一个字段去重一个字段不去重
select distinct job, deptno from emp;
select job, deptno from emp group by job, deptno;
-- 错误1
select job,
       distinct deptno
  from emp;
-- 错误2
select distinct job,
       distinct deptno
  from emp;

– 8.in 操作

查询出工作职责是'salesman'或者'president'或者'analyst'的员工信息。

 	select ename,job from emp where job = 'salesman' or job = 'president' or job = 'analyst';
	select ename,job from emp where job in ('salesman','president','analyst');
    select ename,job from emp where job = any('salesman','president','analyst'); -- 不建议这种写法

– 9.between…and…（包含边界从小到大顺序）

查询列值包含在指定区间内的行,包含边界。
查询工资大于等于1500且小于等于2000的员工信息。
select *
  from emp
 where sal >=1500 and sal <=2000;
 
select *
  from emp
 where sal between 1500 and 2000;
 
查询工资在1500到2000之间的员工信息
select *
  from emp
 where sal >1500 and sal <2000;
 
-- 查询不到值
select *
  from emp
 where sal between 2000 and 1500;

– 10.like模糊查询（对字符串）

字符匹配操作可以使用通配符'%'和'_':
  %：代表任意个字符。
  _：代表任意一个字符。 

	select * from student_info where sname like '李_';
	select * from student_info where sname like '李_%';
	select * from student_info where sname like '李__';

– 11.集合运算

交集：intersect              -- 两者共有部分
并集（去重）：  union        
并集（不去重）：union all    
补集：minus                  -- 前者有后者无
当使用集合操作的时候,要注意：
1.查询所返回的列数以及列的类型必须匹配,列名可以不同。
2.只有union all不会去重。其他三个都需要排序后去重,性能比较差



例题：
1.求员工表和部门表中的共有的部门编号（去重）
select deptno
  from emp
intersect
select deptno
  from dept;
  
  
2.求员工表或者部门表中所包含的部门编号（不去重）
select deptno
  from emp
 union all 
select deptno
  from dept;

3.求员工表或者部门表中所包含的部门编号（去重）
select deptno
  from emp
 union
select deptno
  from dept;

4.求部门表中不在员工表中的部门编号
select deptno
  from dept
 minus
select deptno
  from emp;
-- 
select deptno 
  from emp
minus
select deptno
  from dept;

– 12.子查询

    子查询在select、update、delete语句内部可以出现select语句。
    1.单行子查询：不向外部返回结果,或者只返回一行结果。
    2.多行子查询：向外部返回零行、一行或者多行结果。
	-- 单行查询
	select * from emp where empno=7369;
	-- 多行查询
	select * from emp where deptno=10;
	-- 查询工资和7369相同的员工信息
    select * from emp where sal = (select sal from emp where empno=7369);     -- 单行结果"=",并且字段要对应
	-- 查询工资和10号部门员工工资相同的其它部门的员工信息
    select * from emp where sal in (select sal from emp where deptno = 10) and deptno <> 10;   -- 单行结果"in",并且字段要对应
    select * from emp where sal = any(select sal from emp where deptno = 10) and d