查询练习
练习1
2022-08-07
1,liuyan
2,tangyan
3,jinlian
4,dalang
5,ximenqing
2022-08-08
1,liuyan
2,tangyan
4,dalang
6,wusong
-- 创建分区表记录每天用户登陆信息
create table tb_login
(
uid int,
name string
) partitioned by (dt string)
row format delimited fields terminated by ",";
load data local inpath '/root/2022-08-07' into table tb_login partition (dt = '2022-08-07');
load data local inpath '/root/2022-08-08' into table tb_login partition (dt = '2022-08-08');
select *
from tb_login;
-- 查询7号和8号都登陆人的信息
select t1.uid, t1.name
from (select * from tb_login where dt = '2022-08-07') t1
join
(select * from tb_login where dt = '2022-08-08') t2
on t1.uid = t2.uid;
-- 查询7号登陆 8号没登录人的信息
select *
from (select * from tb_login where dt = '2022-08-07') t1
left join
(select * from tb_login where dt = '2022-08-08') t2
on t1.uid = t2.uid
where t2.uid is null;
-- 查询8号登陆 7号没登陆人的信息
select t2.*
from (select * from tb_login where dt = '2022-08-07') t1
right join
(select * from tb_login where dt = '2022-08-08') t2
on t1.uid = t2.uid
where t1.uid is null;
-- 查询7号登陆 8号没登陆 和 8号登陆 7号没登陆人的信息
select *
from (select * from tb_login where dt = '2022-08-07') t1
full join
(select * from tb_login where dt = '2022-08-08') t2
on t1.uid = t2.uid
where t1.uid is null
or t2.uid is null;
练习2
vi mark.txt
liuyan,语文,100
liuyan,数学,99
liuyan,英语,100
tangyan,语文,80
tangyan,数学,98
tangyan,英语,60
create table stu_mark
(
sname string,
subject string,
score double
) row format delimited fields terminated by ",";
load data local inpath '/root/mark.txt' into table stu_mark;
select * from stu_mark;
-- 使用case when 查询每个人的语文成绩
SELECT sname, case subject WHEN '语文' THEN score else 0 END as `语文` FROM stu_mark ;
-- 查询每个人的每科成绩
SELECT sname,
case subject WHEN '语文' THEN score else 0 END as `语文`,
case subject WHEN '数学' THEN score else 0 END as `数学`,
case subject WHEN '英语' THEN score else 0 END as `英语`
FROM stu_mark ;
-- 得到最终结果
SELECT sname,
max(case subject WHEN '语文' THEN score else 0 END) as `语文`,
max(case subject WHEN '数学' THEN score else 0 END) as `数学`,
max(case subject WHEN '英语' THEN score else 0 END) as `英语`
FROM stu_mark group by sname;
练习3
yg.txt
uid,name,age,gender
1,liuyan,23,M
2,tangyan,33,F
3,jinlian,28,M
4,dalang,37,F
5,ximenqing,17,M
bm.txt
bid,bname
1,财务部
2,销售部
3,后勤部
4,技术部
gz.txt
uid,jb,jj,tc,bid
1,2000,3000,5000,1
2,1000,4000,1000,2
3,5000,1000,5000,1
4,4000,300,7000,3
-- 创建员工表
create table yg
(
uid int,
name string,
age int,
gender string
)
row format delimited fields terminated by ',';
-- 加载员工数据
load data local inpath '/root/yg.txt' into table yg;
-- 创建部门表
create table bm
(
bid int,
bname string
)
row format delimited fields terminated by ',';
-- 加载部门数据
load data local inpath '/root/bm.txt' into table bm;
-- 创建工资表
create table gz
(
uid int,
jb double,
jj double,
tc double,
bid int
)
row format delimited fields terminated by ',';
-- 加载工资数据
load data local inpath '/root/gz.txt' into table gz;
select * from yg;
select * from gz;
select * from bm;
**1.查询每个部门的员工数 男员工个数 女员工个数 显示部门名称**
-- 查询每个员工的 工号 姓名 性别 部门id 部门名称.
select yg.uid,
yg.name,
yg.gender,
bm.bid,
bm.bname
from yg
inner join gz on yg.uid = gz.uid
inner join bm on gz.bid = bm.bid;
-- 查询每个部门的总人数 部门名称 部门总人数
with t1 as (select yg.uid,
yg.name,
yg.gender,
bm.bname
from yg
inner join gz on yg.uid = gz.uid
inner join bm on gz.bid = bm.bid)
select t1.bname,
count(bname) `总人数`
from t1
group by t1.bname;
-- 查询每个部门的总人数 部门名称 部门总人数 男员工个数 女员工个数
with t1 as (select yg.uid,
yg.name,
yg.gender,
bm.bname
from yg
inner join gz on yg.uid = gz.uid
inner join bm on gz.bid = bm.bid)
select t1.bname,
count(bname) `总人数`,
sum(`if`(gender = 'M', 1, 0)) `男`,
sum(`if`(gender = 'F', 1, 0)) `女`
from t1
group by t1.bname;
------------------------------------------------------------------------------------------------
-- 查询 每个部门的总人数 男员工个数 女员工个数
select bid,
count(bid) total_num,
sum(`if`(gender = 'M', 1, 0)) nan,
sum(`if`(gender = 'F', 1, 0)) nv
from yg
inner join gz on yg.uid = gz.uid
group by bid;
-- 显示部门名称
with t1 as (select bid,
count(bid) total_num,
sum(`if`(gender = 'M', 1, 0)) nan,
sum(`if`(gender = 'F', 1, 0)) nv
from yg
inner join gz on yg.uid = gz.uid
group by bid)
select bm.bname, t1.*
from t1
inner join bm on t1.bid = bm.bid;
+-----------+---------+---------------+---------+--------+
| bm.bname | t1.bid | t1.total_num | t1.nan | t1.nv |
+-----------+---------+---------------+---------+--------+
| 财务部 | 1 | 2 | 2 | 0 |
| 销售部 | 2 | 1 | 0 | 1 |
| 后勤部 | 3 | 1 | 0 | 1 |
+-----------+---------+---------------+---------+--------+