大数据仓库技术实训任务2_创建数据库empdb在数据库下新建分区表“emp_hr_partitioned”,字段有name、e(1)_Mysql


![image-20210630183651066](https://img-blog.csdnimg.cn/img_convert/67679efec3ae506780752589bc4e57dc.png)


#### 15. 为表emp\_hr3删除列salary

alter table emp_hr3 replace columns(
name string,
employee_ids string,
sin_number string,
start_date date,
sex string,
age int
);


![image-20210630184528915](https://img-blog.csdnimg.cn/img_convert/461a3e50df02e77c2855585c13c627ac.png)


#### 16. 创建一个新表emp\_hr2，并从已经存在的emp\_hr1表中插入数据name,start\_date,sex和age 到新表emp\_hr2

create table emp_hr2(
name string,
start_date date,
sex string,
age int
);


![image-20210630185015272](https://img-blog.csdnimg.cn/img_convert/2fdcb82f8fa4eda09eb76445ebbcd6b6.png)

insert into emp_hr2 select name,start_date,sex,age from emp_hr1;


![image-20210630185310917](https://img-blog.csdnimg.cn/img_convert/f6564cf10c1d2beaede3ca38501e6732.png)


简单方法：

create table emp_hr2 as select name,start_date,sex,age from emp_hr1;


#### 17. 创建新表emp\_hr4，包含字段name（名字，字符串）, start\_date（入职时间，date）, sex（性别，字符串），age（年龄，整形）每个字段之间由[ | ]分割,从emp\_hr2中向emp\_hr4表中插入数据。

–创建新表emp_hr4
create table emp_hr4(
name string,
start_date date,
sex string,
age int
)
row format delimited fields terminated by ‘|’;
–插入数据
insert into emp_hr4 select name,start_date,sex,age from emp_hr2;


![image-20210630190040945](https://img-blog.csdnimg.cn/img_convert/af61369391199c0a26bdaa867068ee7a.png)


#### 18. 求emp\_hr2的总行数（count）

select count(*) cnt from emp_hr2;


![image-20210630190311771](https://img-blog.csdnimg.cn/img_convert/18fa23af3309aae838dfc25520323e6c.png)


#### 19. 求emp\_hr2中年龄最大值和最小的值

select max(age) max_age from emp_hr2;
select min(age) min_age from emp_hr2;


![image-20210630190424444](https://img-blog.csdnimg.cn/img_convert/a84ec8f2c0f50b8c1f23e705f21df66a.png)


![image-20210630190650054](https://img-blog.csdnimg.cn/img_convert/ada08d84f38e5b4e8dd00668282c38c3.png)


#### 20. 求emp\_hr2中年龄的平均值

select avg(age) avg_age from emp_hr2;


![image-20210630190756019](https://img-blog.csdnimg.cn/img_convert/3727f0be97618c87937fa39b9212c60c.png)


#### 21. 查询emp\_hr2的前5行的名字和年龄

select name,age from emp_hr2 limit 5;


![image-20210630190857692](https://img-blog.csdnimg.cn/img_convert/3fc3b2d6bf20014e3a110cbfb23187d6.png)


#### 22. 查询出年龄大于50的所有员工名字与年龄

select name,age from emp_hr2 where age>50;


![image-20210630190927489](https://img-blog.csdnimg.cn/img_convert/8b6e740121a042e753e47de5eb16b267.png)


#### 23. 查询出年龄介于20到30之间的所有员工名字与年龄

select name,age from emp_hr2 where age between 20 and 30;


![image-20210630190956665](https://img-blog.csdnimg.cn/img_convert/9b73fd59a754bd873d319596c0bf5103.png)


#### 24. 查询出到退休年龄55岁的所有员工名字与年龄

select name,age from emp_hr2 where age=55;


![image-20210630191045473](https://img-blog.csdnimg.cn/img_convert/755c0def36d59283e868770f93bccc70.png)


#### 25. 查询出年龄在25到55岁以外的所有员工名字与年龄

select name,age from emp_hr2 where age not between 20 and 55;


![image-20210630191107727](https://img-blog.csdnimg.cn/img_convert/ecc63bb70470638df8e15bd702e39370.png)


#### 26. 查找年龄以5开头的员工姓名和性别信息

select name,sex from emp_hr2 where age like ‘5%’;


![image-20210630191149912](https://img-blog.csdnimg.cn/img_convert/53f3ae7c3c3607ee8ee2de6188c0ba78.png)


#### 27. 查找年龄第二位为2的员工姓名和性别信息

select name,sex from emp_hr2 where age like ‘_2’;


![image-20210630191226120](https://img-blog.csdnimg.cn/img_convert/9c9f50752aa9b06de8ebe74dc37990e7.png)


#### 28. 在emp\_hr2中查询年龄大于40且性别是male的员工信息

select * from emp_hr2 where age>40 and sex=‘male’;


![image-20210630191346131](https://img-blog.csdnimg.cn/img_convert/4f6b36577d9e6feda6e8fe995d5bff38.png)


#### 29. 在emp\_hr2中查询性别除了male以外的员工信息

select * from emp_hr2 where sex!=‘male’;
–或者
select * from emp_hr2 where sex=‘female’;


![image-20210630191433807](https://img-blog.csdnimg.cn/img_convert/c707b36548bf53d4d79a692a1ee897d0.png)


#### 30. 计算emp\_hr2表平均年龄按照男女分组

select avg(age), sex from emp_hr2 group by sex;


![image-20210630192221325](https://img-blog.csdnimg.cn/img_convert/206f163c697fa412c8fba68ed5a44322.png)


#### 31. 计算emp\_hr2表男女的最高，最低年龄

select max(age) max_age,min(age) min_age, sex from emp_hr2 group by sex;


![image-20210630192420862](https://img-blog.csdnimg.cn/img_convert/a6c26717ed20bf262b72935e627481e3.png)


#### 32. 计算emp\_hr2表平均年龄大于34的性别

select sex from emp_hr2 group by sex having avg(age)>34;


![image-20210630192922873](https://img-blog.csdnimg.cn/img_convert/2065e6513db728a2aa7b1a68b78ec7ee.png)


#### 33. 查询emp\_hr2表中姓名，按照员工离退休年龄55岁的相差岁数，降序排序。（大于55岁的为返聘人员不计入排序）

select name,(55-age) new_age from emp_hr2 where age=<55 order by new_age desc;


![image-20210630193709190](https://img-blog.csdnimg.cn/img_convert/b05d8f010e2649d4569c9715e5aaeccc.png)


注：返回数据过多，仅截取了部分数据。


#### 34. 查询emp\_hr2表中姓名，性别，年龄，按照性别进行分区，年龄升序排序。

select name, sex, age from emp_hr2 distribute by sex sort by age;


![image-20210630194457316](https://img-blog.csdnimg.cn/img_convert/50719cebdf56b70b55344277ddda0085.png)


注：返回数据过多，仅截取了部分数据。


#### 35. 在数据库empdb下新建分区表emp\_hr1\_part1, 分别包含字段name（名字，字符串）,employee\_id（id，字符串）,sin\_number（电话，字符串）,start\_date（入职时间，date），age（年龄，整形），以sex\_m（性别，字符串）来分区，每个字段之间由[ | ]分割.

create table emp_hr1_part1(
name string,
employee_id string,
sin_number string,
start_date date,
sex string,
age int
)
partitioned by (sex_m string)
row format delimited fields terminated by ‘|’;


![image-20210630194829277](https://img-blog.csdnimg.cn/img_convert/37d1dcc6688fbcf6977e8ad08a6c0257.png)


#### 36. 从emp\_hr1中将name, employee\_id, sin\_number, start\_date, age和sex=’male’的数据插入到分区表emp\_hr1\_part1的分区sex\_m=‘male’中。

–打开动态分区
set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict;

insert into emp_hr1_part1 partition(sex_m=‘male’)
select * from emp_hr1 where sex=‘male’;


![image-20210630204057670](https://img-blog.csdnimg.cn/img_convert/3cafc0157689ae526329abaca36d51e7.png)


#### 37. 为表emp\_hr1\_part1增加分区sex\_m=’female’

alter table emp_hr1_part1 add partition(sex_m=‘female’);


![image-20210630204323769](https://img-blog.csdnimg.cn/img_convert/12c23528ad55445807f665b4b387e46f.png)


#### 38. 从emp\_hr1中将name, employee\_id, sin\_number, start\_date, age和sex=’female’的数据插入到分区表emp\_hr1\_par1的分区sex\_m=‘female’中。

insert into emp_hr1_part1 partition(sex_m=‘female’)
select * from emp_hr1 where sex=‘female’;