hive sql练习

参考

-- 创建学生表，学号，姓名，性别，年龄，班级
DROP TABLE student;
CREATE TEMPORARY TABLE  student(
  Sno int,
  Sname VARCHAR(20),
  Sex VARCHAR(20),
  Sage int,
  Sdept VARCHAR(20)
);

-- 创建 课程表， 课程编号，课程名称
DROP TABLE course;
create TEMPORARY TABLE course(
  Cno int,
  Cname VARCHAR(20)
);
-- 创建 学生 课程关联表，学号，课号，年级
create table sc(
  Sno int,
  Cno int,
  Grade int
);

-- 插入课程数据
INSERT into course values(1, 'database');
INSERT into course values(2, 'math');
INSERT into course values(3, 'info system');
INSERT into course values(4, 'system');
INSERT into course values(5, 'data structure');
INSERT into course values(6, 'data deal');

-- 插入学生选课数据和成绩
INSERT into sc values(95001,1,81);
INSERT into sc values(95001,2,85);
INSERT into sc values(95001,3,88);
INSERT into sc values(95001,4,70);
INSERT into sc values(95002,2,90);
INSERT into sc values(95002,3,80);
INSERT into sc values(95002,4,71);
INSERT into sc values(95002,5,60);
INSERT into sc values(95003,1,82);
INSERT into sc values(95003,3,90);
INSERT into sc values(95003,5,100);
INSERT into sc values(95004,1,80);
INSERT into sc values(95004,2,92);
INSERT into sc values(95004,4,91);
INSERT into sc values(95004,5,70);
INSERT into sc values(95005,1,70);
INSERT into sc values(95005,2,92);
INSERT into sc values(95005,3,99);
INSERT into sc values(95005,6,87);
INSERT into sc values(95006,1,72);
INSERT into sc values(95006,2,62);
INSERT into sc values(95006,3,100);
INSERT into sc values(95006,4,59);
INSERT into sc values(95006,5,60);
INSERT into sc values(95006,6,98);
INSERT into sc values(95007,3,68);
INSERT into sc values(95007,4,91);
INSERT into sc values(95007,5,94);
INSERT into sc values(95007,6,78);
INSERT into sc values(95008,1,98);
INSERT into sc values(95008,3,89);
INSERT into sc values(95008,6,91);
INSERT into sc values(95009,2,81);
INSERT into sc values(95009,4,89);
INSERT into sc values(95009,6,100);
INSERT into sc values(95010,2,98);
INSERT into sc values(95010,5,90);
INSERT into sc values(95010,6,80);
INSERT into sc values(95011,1,81);
INSERT into sc values(95011,2,91);
INSERT into sc values(95011,3,81);
INSERT into sc values(95011,4,86);
INSERT into sc values(95012,1,81);
INSERT into sc values(95012,3,78);
INSERT into sc values(95012,4,85);
INSERT into sc values(95012,6,98);
INSERT into sc values(95013,1,98);
INSERT into sc values(95013,2,58);
INSERT into sc values(95013,4,88);
INSERT into sc values(95013,5,93);
INSERT into sc values(95014,1,91);
INSERT into sc values(95014,2,100);
INSERT into sc values(95014,4,98);
INSERT into sc values(95015,1,91);
INSERT into sc values(95015,3,59);
INSERT into sc values(95015,4,100);
INSERT into sc values(95015,6,95);
INSERT into sc values(95016,1,92);
INSERT into sc values(95016,2,99);
INSERT into sc values(95016,4,82);
INSERT into sc values(95017,4,82);
INSERT into sc values(95017,5,100);
INSERT into sc values(95017,6,58);
INSERT into sc values(95018,1,95);
INSERT into sc values(95018,2,100);
INSERT into sc values(95018,3,67);
INSERT into sc values(95018,4,78);
INSERT into sc values(95019,1,77);
INSERT into sc values(95019,2,90);
INSERT into sc values(95019,3,91);
INSERT into sc values(95019,4,67);
INSERT into sc values(95019,5,87);
INSERT into sc values(95020,1,66);
INSERT into sc values(95020,2,99);
INSERT into sc values(95020,5,93);
INSERT into sc values(95021,2,93);
INSERT into sc values(95021,5,91);
INSERT into sc values(95021,6,99);
INSERT into sc values(95022,3,69);
INSERT into sc values(95022,4,93);
INSERT into sc values(95022,5,82);
INSERT into sc values(95022,6,100);

INSERT into student values(95001,"liyong","M",20,"CS");
INSERT into student values(95002,"licheng","F",19,"IS");
INSERT into student values(95003,"wangming","F",22,"MA");
INSERT into student values(95004,"zhangli","M",19,"IS");
INSERT into student values(95005,"liugang","M",18,"MA");
INSERT into student values(95006,"sunqing","M",23,"CS");
INSERT into student values(95007,"yisiling","F",19,"MA");
INSERT into student values(95008,"lina","F",18,"CS");
INSERT into student values(95009,"mengyuanyuan","F",18,"MA");
INSERT into student values(95010,"kongxiaotao","M",19,"CS");
INSERT into student values(95011,"baoxiaobo","M",18,"MA");
INSERT into student values(95012,"sunhua","F",20,"CS");
INSERT into student values(95013,"fengwei","M",21,"CS");
INSERT into student values(95014,"wangxiaoli","F",19,"CS");
INSERT into student values(95015,"wangjun","M",18,"MA");
INSERT into student values(95016,"qianguo","M",21,"MA");
INSERT into student values(95017,"wangfengjuan","F",18,"IS");
INSERT into student values(95018,"wangyi","F",19,"IS");
INSERT into student values(95019,"xingxiaoli","F",19,"IS");
INSERT into student values(95020,"zhaoqian","M",21,"IS");
INSERT into student values(95021,"zhouer","M",17,"MA");
INSERT into student values(95022,"zhangming","M",20,"MA");

SELECT * FROM course;

-- 查询学生总人数
SELECT count(*) FROM student;

-- 查询全体学生的学号与姓名
SELECT student.Sno, student.Sname FROM student;

-- 查询选修了课程的学生姓名,
SELECT student.Sname FROM student
JOIN sc
on student.Sno = sc.Sno
GROUP BY student.Sname;

-- 计算1号课程的学生平均成绩，sc表
SELECT avg(sc.Grade) FROM sc WHERE Cno = 1 GROUP BY Cno;

-- 这一个写法要快一点
select avg(Grade) from sc group by Cno having Cno=1;

-- 查询各科成绩平均分
-- 这个所有学系的各科平均分
SELECT course.Cname, avg(sc.Grade) FROM course
JOIN sc
on course.Cno=sc.Cno
GROUP BY course.Cname;

-- 这样写要快一点
SELECT course.Cname, avg(sc.Grade) FROM course
JOIN sc
on course.Cno=sc.Cno
GROUP BY course.Cname,sc.Cno;

-- 查询选修1号课程的学生最高分数
SELECT max(sc.Grade) FROM sc WHERE sc.Cno = 1;


-- 这个写法要快一点
select Grade from sc where Cno=1 order by Grade desc limit 1;

select Grade from sc where Cno=1 distribute by Grade sort by Grade desc limit 1;

-- 求各个课程号及相应的选课人数
SELECT sc.Cno, count(1) as num from sc GROUP BY Cno;

-- 查询选修了3门以上的课程的学生学号
SELECT sc.Sno FROM sc GROUP BY Sno HAVING count(1) > 3;

-- 查询学生信息，结果按学号全局有序
SELECT student.* FROM student ORDER BY student.Sno;


-- 查询学生信息，结果区分性别按年龄有序
SELECT * FROM student ORDER BY sex,Sage asc;

-- 查询每个学生及其选修课程的成绩情况
SELECT student.*,course.Cname,sc.Grade FROM student
JOIN sc
on sc.Sno = student.Sno
JOIN course
on course.Cno=sc.Cno
ORDER BY student.Sno;

-- 查询学生的得分情况
SELECT student.Sname,course.Cname,sc.Grade FROM student
JOIN sc
on sc.Sno = student.Sno
JOIN course
on course.Cno=sc.Cno
ORDER BY student.Sname;

-- 查询选修2号课程且成绩在90分以上的所有学生。
SELECT student.* FROM student
JOIN sc
on student.Sno=sc.Sno
WHERE sc.Cno=2 and sc.Grade > 90;

-- 查询所有学生的信息，如果在成绩表中有成绩，则输出成绩表中的课程号
SELECT student.*,sc.Cno FROM student
left JOIN sc
on student.Sno=sc.Sno;

-- 查询与“licheng”在同一个系学习的学生
-- exists写法于join差不多快
SELECT stu1.Sname FROM student stu1 WHERE exists
(
  SELECT * FROM student stu2 WHERE stu2.Sname = 'licheng' and stu1.Sdept = stu2.Sdept
);
exists 子查询语句如果为true就提交，如果为false不提交，先外循环stu1,然后在内循环stu2，如果内循环（子查询）为真，则提交（选择），所以子查询与select字段无关，只与后面的判断真伪有关

-- in 写法
SELECT stu1.Sname FROM student stu1 WHERE stu1.Sdept in
(
  SELECT stu2.Sdept FROM student stu2 WHERE stu2.Sname = 'licheng'
);
in子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出


-- join写法最快
SELECT stu.Sname FROM student stu
JOIN student st
on st.Sname = 'licheng' and st.Sdept = stu.Sdept and stu.Sname != 'licheng';

select s2.* from student as s1 inner join student as s2 on(s2.Sdept=s1.Sdept) where s1.Sname='licheng';

原先数据格式是这样,写了个脚本转成insert 形式

ss = '''95001,李勇,男,20,CS
95002,刘晨,女,19,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95005,刘刚,男,18,MA
95006,孙庆,男,23,CS
95007,易思玲,女,19,MA
95008,李娜,女,18,CS
95009,梦圆圆,女,18,MA
95010,孔小涛,男,19,CS
95011,包小柏,男,18,MA
95012,孙花,女,20,CS
95013,冯伟,男,21,CS
95014,王小丽,女,19,CS
95015,王君,男,18,MA
95016,钱国,男,21,MA
95017,王风娟,女,18,IS
95018,王一,女,19,IS
95019,邢小丽,女,19,IS
95020,赵钱,男,21,IS
95021,周二,男,17,MA
95022,郑明,男,20,MA
'''
rr = "INSERT into student values(%s);";
for el in ss.split('\n'):
    temp = ""
    i = 0
    for ell in el.split(','):
        if i == 0 or i==3:
            temp +=ell+','
        elif i == 1 or i == 2:
            temp +="\"%s\""%ell+','
        else:
            temp += "\"%s\""%ell
        i += 1
        
    print (rr%temp)
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,968评论 6赞 482
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,601评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 153,220评论 0赞 344
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,416评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,425评论 5赞 374
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,144评论 1赞 285
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,432评论 3赞 401
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,088评论 0赞 261
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,586评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,028评论 2赞 325
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,137评论 1赞 334
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,783评论 4赞 324
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,343评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,333评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,559评论 1赞 262
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,595评论 2赞 355
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,901评论 2赞 345

推荐阅读更多精彩内容