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
);

-- in 写法
SELECT stu1.Sname FROM student stu1 WHERE stu1.Sdept in
(
  SELECT stu2.Sdept FROM student stu2 WHERE stu2.Sname = 'licheng'
);

-- 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)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏抠抠空间

MySQL之多表查询

阅读目录 一 多表联合查询 二 多表连接查询 三 复杂条件多表查询 四 子语句查询 五 其他方式查询 六 SQL逻辑查询语句执行顺序(重点) 七 ...

42612
来自专栏calmound

操作数据表

打开数据库    use 数据库 ---- use mydata 查看数据库    show databases 显示当前用户打开的数据库   SELECT ...

3166
来自专栏一个会写诗的程序员的博客

SELECT 语句中的 子查询(Sub Query)

子查询(Sub Query)或者说内查询(Inner Query),也可以称作嵌套查询(Nested Query),是一种嵌套在其他 SQL 查询的 WHERE...

1282
来自专栏林欣哲

常见电商项目的数据库表设计(MySQL版)

9.4K8
来自专栏Netkiller

新闻数据库分表案例

文章节选自《Netkiller Architect 手札》 6.3. 新闻数据库分表案例 这里我通过一个新闻网站为例,解决分表的问题 避免开发中经常拼接表,我采...

3956
来自专栏同步博客

MySQL常用语句

  今天天气很好,大晴天,心情也好好的。就将MySQL常用的语句总结一下,记录在随笔里,也顺便分享分享。日后,这篇随笔我将会持续更新,作为我自己的MySQL语句...

922
来自专栏ml

行受影响 是什么意思

这就是说明你写的语句没问题,此句实现插入一条name为a kecheng为d fenshu为d  的数据 说明你插入的数据成功... CREATE TABLE ...

3155
来自专栏技术博文

discuz数据字典

CREATE TABLE pre_common_admincp_cmenu ( `id` SMALLINT(6) UNSIGNED NOT NULL AUT...

3164
来自专栏北京马哥教育

【干货】zabbix报警信息提取 |从数据库入手

zabbix报警信息提取 在日常的监控中,我们除了日常的zabbix操作外,我们有的时候还涉及到与其他公司 进行数据对接。由于别的公司的数据对接很多时候并不是按...

3775
来自专栏跟着阿笨一起玩NET

SQL Server 2008中的Pivot和UnPivot

今天给新成员讲解PIVOT 和 UNPIVOT示例,顺便整理了一下其用法。这是自SQL Server 2005起提供的新功能。

722

扫码关注云+社区

领取腾讯云代金券