Hive查询

一、创建表并录入数据，再进行全表查询

create table t1(
  id int,
  name string
);
Insert into table t1 values(1,'Zhangsan');
Insert into table t1 values(2,'Lisi');
Insert into table t1 values(3,'Wang');

create table t2(
  id int,
  age int
);
Insert into table t2 values(1,20);
Insert into table t2 values(2,30);
Insert into table t2 values(4,40);

二、where查询

三、distinct 去重

四、limit(限制五条数据查询)

五、排序

1. group by

2 .order 全局排序

select age from t2 order by age asc ;

select * from t1 order by id limit 5;

3.sort 全局未必有序

select age from t2 sort by age asc limit 1;

hive查询2

create database test1104;

use test1104

create table t1(id int,name string);

insert into table t1 values(1,'zhangsan');
insert into table t1 values(2,'lisi');
insert into table t1 values(3,'wangwu');

create table t2(id int,age string);
insert into table t2 values(1,'201');
insert into table t2 values(2,'30');
insert into table t2 values(3,'40');

1 内连接

[inner] join

select t1.id,t1.name,t2.age from t1 inner join t2 on (t1.id=t2.id) where t1.name='zhangsan';

2 左外连接

left [outer] join

select t1.id,t1.name,t2.age from t1 left outer join t2 on (t1.id=t2.id) where t1.name='wangwu';

3 右外连接

right [outer] join

select t1.id,t1.name,t2.age from t1 right outer join t2 on (t1.id=t2.id) where t1.name='wangwu';

select t1.id,t1.name,t2.age from t2 right outer join t1 on (t1.id=t2.id) where t1.name='wangwu';

4 全外连接

full [outer] join

select t1.id,t1.name,t2.age from t2 full outer join t1 on (t1.id=t2.id);

5 笛卡尔积

[cross] join 没有on 子句

6 左半连接

left semi join

select t1.id,t1.name from t1 left semi join t2 on(t1.id=t2.id);

select t1.id,t1.name from t1 where t1.id in (select id from t2);

/***
 *             ,%%%%%%%%,
 *           ,%%/\%%%%/\%%
 *          ,%%%\c "" J/%%%
 * %.       %%%%/ o  o \%%%
 * `%%.     %%%%    _  |%%%
 *  `%%     `%%%%(__Y__)%%'
 *  //       ;%%%%`\-/%%%'
 * ((       /  `%%%%%%%'
 *  \\    .'          |
 *   \\  /       \  | |
 *    \\/         ) | |
 *     \         /_ | |__
 *     (___________))))))) 攻城湿
 */