hive，

hive，

 
-------------------------------------------创建一个外部表-----------------------------------------
 create external table if not exists employee_external(
name string,
work_place array<string>,
sex_age struct<sex:string,age:int>,
skills_score map<string,int>,
depart_title map<string,array<string>>)
comment 'this is an external table'
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by '：'
stored as textfile;

//DDL
 create table cte_score as with  
 r1 as (select sclass from r2  where sno =108 ),
 r2 as (select * from score where sno > 105),
 r3 as (select sclass from score where sno <106)
 select * from r1 union all select * from r3;

create table score_like like score;

drop view score4;

drop table score_like;

--查看数据库的地址
desc database yao;

desc score;

//alter  语句只能更改表的元数据，而不能更改表的数据
--修改用户名
alter database yao set owner user yaoyao;
--修改表名
alter table score rename to score_internal ;
--清空内部表
 truncate table ctas_score;

alter table score set tblproperties('sno'='new name,sno');
?
alter table score internal set serdeproperties('field.delim'='$')
?
alter table score  set fileformat rcfile
?
alter table ymanaged_table change name ymanaged string  sno ;
--增加字段
alter table score add columns(work string);

alter table score replace columns(sno_sclass string);

---------------------------------------------------------father--------------------------------------

create table father(
  ID  int,
  Name  String,
  Gender char,
  Age   int,
  FatherID  int,
  MatherID int
    )
row format delimited
fields terminated by '\t'

load data inpath '/home/cloudera/Desktop/father' into table father;

//father表数据
1    John Smith    M    58  
2    Michelle Dougles    F    57
3    Catherine Joe    F    37
4    David Smith    M    19    1    2
5    Kathy Smith    F    11    1    3
6    Cissy Smith    F    8    1    3

//order by 后跟聚合函数时注意一定要用别名。
/which fathers hava the most of children?
select fatherid, count(1) as c from father group by fatherid order by c desc limit 1 ;

/who are the youngest mother?
select distinct b.id,b.age from father a left  outer join father  b on a.matherid = b.id where b.age = min(b.age) ;?

select min(age) from (select distinct b.id,b.age from father a left  outer join father  b on a.matherid = b.id );????

/who hava the same fatehr but different mother?
select id from father a join father b on a.id = b.id  where a.fatherid = b.fatherid and a.matherid ！= b.matherid;

select motherid ,max(age) from father  group by motherid