常用sql语句

1. 查改增删

1.1 插入一条数据

insert into table_name values 
    ('Jack',100),
    ('Tom',90);

INSERT OVERWRITE 语句从一个表复制数据，然后把数据插入到一个已存在的表中，目标表会先被清空。

insert overwrite table_name  
[PARTITION (partcol1=val1, partcol2=val2 ...) 
[IF NOT EXISTS]] 
select * from table1;

非覆盖

INSERT INTO TABLE tablename
select * from table1;

1.2 更新一条数据

1.3  删除一条数据

1.4 创建表

use db_name;
drop table if exists table_name;
create table table_name as
select a.*
from table_name1 a;

2.  表数据清空及装填

2.1 删除表的所有数据（截断表）

truncate table  table_name;

truncate table是清空一个表，是一个DDL语言，速度快，效率高，它的主要功能就是彻底删除数据，使其不能进行回滚。

它与delete有如下区别：

1. DELETE  语句的作用是在指定表或指定视图的基表中删除记录行。用户可以删除位于用户自己模式中的表的记录行，也可以删除在该表上具有 DELETE 权限的表的记录行，并且在删除指定表的记录行时，必须满足该表上的完整性约束条件。
2. TRUNCATE TABLE 语句用于删除表的所有数据（截断表）。
3. DELETE 删除表的所有数据时，不会释放表所占用的空间，并且操作是可以撤销的。
4. TRUNCATE TABLE 删除表的所有数据时，执行速度更快，而且还会释放表、段所占用的空间，并且操作是不能撤销的。

2.2 重新装填数据

# 清空表数据
truncate table table_name;
# 将本地数据放到hive仓库
hadoop fs -put data.csv /user/hive/warehouse/zz.db/table_name
# 修复表
msck repair table zz.table_name;

3. 两表jion

 表a

11
12

表b

11
11
11

select id
from a
join b
on a.id=b.id;
输出结果：
11
11
11

如果b表数据未去重，a join b 输出的结果也不会去重的。

4.  where 与 having

筛选出人数大于10的班级，以下两种方式等价。

4.1 where

select *
from (
    select class_name, count(1) as cnt
    from table_name
    group by class_name
) t
where cnt>10;

4.2 having

select class_name, count(1)
from table_name
group by class_name
having count(1)>10;

5. 有序的选择

要求根据where 条件顺序，保证查询结果也按此序，并给出各个uid是否匹配到。

select uid, rn, uid1, uid2, uid3
from (
    select uid
    case when uid='11' then 1 else 0 end as uid1,
    case when uid='33' then 1 else 0 end as uid2,
    case when uid='22' then 1 else 0 end as uid3,
    (case when uid='11' then 1
          when uid='33' then 2
          when uid='22' then 3
    ) as rn
    from table_name
    where uid='11' or uid='33' or uid='22'
) t
order by rn
limit 100;

输出结果

uid,rn,uid1,uid2,uid3

11,1,1,0,0

33,2,0,1,0

22,3,0,0,1