-- #################SQL的查询###########################

-- 1、基本查询
select sid myid, sscore
from score;

-- 2、聚合函数
/*
   如果在本地模式下不能执行mr，则需要做以下配置:
    vim /export/server/hive/conf/hive-env.sh
    1: 将export HADOOP_HEAPSIZE=1024注释打开
    2：重启hive
 */
select count(sid)
from score;

select max(sscore)
from score;

-- 3、limit查询
select *
from student
limit 3;

-- 从索引为3的行（第4行）开始查询5行
select *
from student
limit 3,5;

-- 4、条件查询
select *
from score
where sscore is null;

select *
from score
where sscore is not null;

select *
from score
where sscore in (80, 90, 99);

select *
from covid2;

/**
  %:匹配多个任意字符
  _:匹配单个任意字符
 */

-- 查询以 'C'字母开头的县
select *
from covid2
-- 区分大小写
where county like 'C%';

-- 查询第二个字符是'c'字母的县
select *
from covid2
where county like '_c%';

-- 查询名字是四个字母的县
select *
from covid2
where county like '____';

-- 查询包含 'chi' 名字的州
select *
from covid2
where county like '%chi%';

-- 查询包含 'c' 名字的县
select *
from covid2
where county rlike '[c]';

-- 查询学号不是 1 3 5 的学生
select *
from student
where sid not in ('01', '03', '05');

-- 查询每一个学生的平均分数
-- 分组之后，不管每一组有多少条数据，每一组最后只剩下一条数据
select sid, trunc(avg(sscore), 0) avg
from score
group by sid;

-- 如果有分组，则select的后边只能跟分组字段和聚合函数
-- 执行报错
-- select sid, cid, avg(sscore)
-- from score
-- group by sid;

-- 统计每一个州的确诊病例总人数，并降序排序
select state, sum(cases) total_cases
from covid2
group by state
order by total_cases desc;

-- 统计每一个州，每一个县的确诊病例总人数
select state, county, sum(cases) total_cases
from covid2
group by state, county
order by total_cases desc;

-- 统计每一个州的总确诊人数，并筛选出总确诊人数大于100万的州
-- 对分组后的结果再进行筛选，就必须使用having
select state, sum(cases) total_cases
from covid2
group by state
having total_cases > 1000000
order by total_cases desc;