DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`s_id` varchar(20),`s_name` varchar(20),`s_birth` varchar(20),`s_sex` varchar(10))row format delimitedfields terminated by ','lines terminated by '\n';
load data local inpath '/root/data/student.csv' into table student;

DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (  `c_id` varchar(20),  `c_name` varchar(20),  `t_id` varchar(20))row format delimitedfields terminated by ','lines terminated by '\n';
load data local inpath '/opt/install/data/course.csv' into table course;

DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher`(`t_id` varchar(20),`t_name` varchar(20))row format delimitedfields terminated by ','lines terminated by '\n';
load data local inpath '/opt/install/data/teacher.csv' into table teacher;

DROP TABLE IF EXISTS `score`;CREATE TABLE `score`(`s_id` varchar(20),`c_id` varchar(20),`s_score` int)row format delimitedfields terminated by ','lines terminated by '\n';
load data local inpath '/opt/install/data/score.csv' into table score;

1.启动Hadoop[hadoop@bigdata01 bin]$ sh zkhadoop.sh start
2.启动Hive[hadoop@bigdata03 install]$ nohup hive --service hiveserver2&[1] 22213[hadoop@bigdata03 install]$ nohup: 忽略输入并把输出追加到"nohup.out"
3.beeline连接hiveserver2服务[hadoop@bigdata03 install]$ beeline --color=trueBeeline version 3.1.3 by Apache Hivebeeline> !connect jdbc:hive2://bigdata03:10000Connecting to jdbc:hive2://bigdata03:10000Enter username for jdbc:hive2://bigdata03:10000: hadoopEnter password for jdbc:hive2://bigdata03:10000: Connected to: Apache Hive (version 3.1.3)Driver: Hive JDBC (version 3.1.3)Transaction isolation: TRANSACTION_REPEATABLE_READ0: jdbc:hive2://bigdata03:10000> show tables;+-----------+| tab_name  |+-----------+| course    || score     || student   || teacher   |+-----------+4 rows selected (3.649 seconds)

select s.s_id,s.s_name,c.c_id,c.s_score
from student s inner join score c on s.s_id = c.s_id
where s.s_id in 
(
select sr.s_id 
from (
select s_id,c_id,s_score, rank() over(partition by s_id order by s_score desc) as rn
from score 
where c_id ='01' or c_id='02') sr 
where sr.rn = 2 and sr.c_id='02'
);

select s.s_id,s.s_name,c.c_id,c.s_score
from student s inner join score c on s.s_id = c.s_id
where s.s_id in 
(
select sr.s_id 
from (
select s_id,c_id,s_score, rank() over(partition by s_id order by s_score asc) as rn
from score 
where c_id ='01' or c_id='02') sr 
where sr.rn = 2 and sr.c_id='02');

select s.s_id,s.s_name,sv.avg_score from student s inner join (
select s_id,avg(s_score) as avg_score
from score 
group by s_id
having avg(s_score) > 60) sv
on s.s_id = sv.s_id;

select s.s_id,s.s_name,sv.avg_score from student s inner join (
select s_id,avg(s_score) as avg_score
from score 
group by s_id
having avg(s_score) < 60) sv
on s.s_id = sv.s_id
union all
select s.s_id,s.s_name,0 as avg_score 
from student s left join score sc on s.s_id = sc.s_id
where sc.s_score is null;

select s.s_id,s.s_name,count(sc.c_id) as course_count,sum(sc.s_score) as score_sum
from student s inner join score sc on s.s_id = sc.s_id
group by s.s_id,s.s_name;

select count(*)
from teacher
where t_name like '李%';

select s.*
from student s,score sc,course c, teacher t
where s.s_id = sc.s_id and sc.c_id = c.c_id and c.t_id = t.t_id and t.t_name = '张三';

select * from student
where s_id not in (
select s.s_id
from student s,score sc,course c, teacher t
where s.s_id = sc.s_id and sc.c_id = c.c_id and c.t_id = t.t_id and t.t_name = '张三');

select *
from student
where s_id in (
select s_id
from score
where c_id='01' or c_id='02'
group by s_id
having count(c_id)>1);

select *
from student
where s_id in (
select s_id
from score
where c_id='01'
) and s_id not in (
select s_id
from score
where c_id='02');

select * from student
where s_id in (
select s.s_id
from student s left join (select s_id, count(c_id) as cnt from score group by s_id) sc on s.s_id = sc.s_id
where sc.cnt is NULL or sc.cnt < (select count(*) from course)) ;

select * from student where s_id in (
select s1.s_id from (select s_id,c_id from score where s_id <> '01') s1 inner join 
(select s_id,c_id from score where s_id = '01') s2 on s1.c_id = s2.c_id);

select * from student
where s_id in (
select b.s_id from (
select s_id, concat_ws(',', collect_set(c_id)) c_ids
from score
where s_id='01'
group by s_id) a inner join (
select s_id, concat_ws(',', collect_set(c_id)) c_ids
from score
group by s_id) b on a.c_ids = b.c_ids );

select s_name 
from student 
where s_id not in (
select sc.s_id 
from course c inner join teacher t on c.t_id = t.t_id 
inner join score sc on c.c_id = sc.c_id
where t.t_name = '张三')

select s.s_id,s.s_name, np.avg_score
from student s inner join (
select s_id, avg(s_score) as avg_score 
from score 
where s_id in (
select s_id
from score
where s_score <60
group by s_id
having count(*) >1)
group by s_id) np on s.s_id = np.s_id;