-- #################SQL的查詢###########################

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

-- 2、聚合函數(shù)
/*
   如果在本地模式下不能執(zhí)行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
-- 區(qū)分大小寫
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]';

-- 查詢學(xué)號不是 1 3 5 的學(xué)生
select *
from student
where sid not in ('01', '03', '05');

-- 查詢每一個學(xué)生的平均分?jǐn)?shù)
-- 分組之后，不管每一組有多少條數(shù)據(jù)，每一組最后只剩下一條數(shù)據(jù)
select sid, trunc(avg(sscore), 0) avg
from score
group by sid;

-- 如果有分組，則select的后邊只能跟分組字段和聚合函數(shù)
-- 執(zhí)行報錯
-- select sid, cid, avg(sscore)
-- from score
-- group by sid;

-- 統(tǒng)計每一個州的確診病例總?cè)藬?shù)，并降序排序
select state, sum(cases) total_cases
from covid2
group by state
order by total_cases desc;

-- 統(tǒng)計每一個州，每一個縣的確診病例總?cè)藬?shù)
select state, county, sum(cases) total_cases
from covid2
group by state, county
order by total_cases desc;

-- 統(tǒng)計每一個州的總確診人數(shù)，并篩選出總確診人數(shù)大于100萬的州
-- 對分組后的結(jié)果再進(jìn)行篩選，就必須使用having
select state, sum(cases) total_cases
from covid2
group by state
having total_cases > 1000000
order by total_cases desc;