1. 建庫建表 2. 數據準備 相關數據 數據導入 3. 常用操作 3.1. 學生表基本查詢 查詢學號為95001, 95005, 95008的學生信息 查詢學號中有9500字元串的學生信息 查詢全體學生的學號與姓名 查詢學生的總人數 3.2. 成績表相關查詢 查詢選修了課程的學生姓名 計算1號課程 ...
1. 建庫建表
1 # 建庫 2 create database exercise; 3 # 建表 4 create table student(Sno int,Sname string,Sex string,Sage int,Sdept string) 5 row format delimited fields terminated by ','; 6 7 create table course(Cno int,Cname string) 8 row format delimited fields terminated by ','; 9 10 create table sc(Sno int,Cno int,Grade int) 11 row format delimited fields terminated by ','; 12 13 # 查看有哪些表 14 hive (exercise)> show tables; 15 OK 16 course 17 sc 18 student 19 Time taken: 0.033 seconds, Fetched: 3 row(s)
2. 數據準備
相關數據
1 [yun@mini01 exercise]$ pwd 2 /app/software/hive/exercise 3 [yun@mini01 exercise]$ ll /app/software/hive/exercise/ 4 total 12 5 -rw-rw-r-- 1 yun yun 81 Jul 18 17:44 course.dat 6 -rw-rw-r-- 1 yun yun 910 Jul 18 17:43 sc.dat 7 -rw-rw-r-- 1 yun yun 527 Jul 18 17:42 students.dat 8 [yun@mini01 exercise]$ cat /app/software/hive/exercise/course.dat 9 1,資料庫 10 2,數學 11 3,信息系統 12 4,操作系統 13 5,數據結構 14 6,數據處理 15 [yun@mini01 exercise]$ cat /app/software/hive/exercise/sc.dat 16 95001,1,81 17 95001,2,85 18 95001,3,88 19 95001,4,70 20 95002,2,90 21 95002,3,80 22 95002,4,71 23 95002,5,60 24 95003,1,82 25 95003,3,90 26 95003,5,100 27 95004,1,80 28 95004,2,92 29 95004,4,91 30 95004,5,70 31 95005,1,70 32 95005,2,92 33 95005,3,99 34 95005,6,87 35 95006,1,72 36 95006,2,62 37 95006,3,100 38 95006,4,59 39 95006,5,60 40 95006,6,98 41 95008,1,98 42 95008,3,89 43 95008,6,91 44 95010,2,98 45 95010,5,90 46 95010,6,80 47 95011,1,81 48 95011,2,91 49 95011,3,81 50 95011,4,86 51 95012,1,81 52 95012,3,78 53 95012,4,85 54 95012,6,98 55 95013,1,98 56 95013,2,58 57 95013,4,88 58 95013,5,93 59 [yun@mini01 exercise]$ cat /app/software/hive/exercise/students.dat 60 95001,李勇,男,20,CS 61 95002,劉晨,女,19,IS 62 95003,王敏,女,22,MA 63 95004,張立,男,19,IS 64 95005,劉剛,男,18,MA 65 95006,孫慶,男,23,CS 66 95007,易思玲,女,19,MA 67 95008,李娜,女,18,CS 68 95009,夢圓圓,女,18,MA 69 95010,孔小濤,男,19,CS
數據導入
1 # desc formatted course; 2 # desc formatted sc; 3 # desc formatted student; 4 # 查看location信息 5 # 數據導入 6 0: jdbc:hive2://mini01:10000> load data local inpath '/app/software/hive/exercise/course.dat' into table course; # 導入數據 7 INFO : Loading data to table exercise.course from file:/app/software/hive/exercise/course.dat 8 INFO : Table exercise.course stats: [numFiles=1, totalSize=81] 9 No rows affected (0.35 seconds) 10 0: jdbc:hive2://mini01:10000> load data local inpath '/app/software/hive/exercise/sc.dat' into table sc; # 導入數據 11 INFO : Loading data to table exercise.sc from file:/app/software/hive/exercise/sc.dat 12 INFO : Table exercise.sc stats: [numFiles=1, totalSize=910] 13 No rows affected (0.25 seconds) 14 0: jdbc:hive2://mini01:10000> load data local inpath '/app/software/hive/exercise/students.dat' into table student; # 導入數據 15 INFO : Loading data to table exercise.student from file:/app/software/hive/exercise/students.dat 16 INFO : Table exercise.student stats: [numFiles=1, totalSize=527] 17 No rows affected (0.381 seconds)
3. 常用操作
3.1. 學生表基本查詢
查詢學號為95001, 95005, 95008的學生信息
1 select * from student a where a.sno in(95001, 95005, 95008); 2 +--------+----------+--------+---------+----------+--+ 3 | a.sno | a.sname | a.sex | a.sage | a.sdept | 4 +--------+----------+--------+---------+----------+--+ 5 | 95001 | 李勇 | 男 | 20 | CS | 6 | 95005 | 劉剛 | 男 | 18 | MA | 7 | 95008 | 李娜 | 女 | 18 | CS | 8 +--------+----------+--------+---------+----------+--+ 9 3 rows selected (0.076 seconds)
查詢學號中有9500字元串的學生信息
1 select * from student a where a.sno like '%9500%'; 2 +--------+----------+--------+---------+----------+--+ 3 | a.sno | a.sname | a.sex | a.sage | a.sdept | 4 +--------+----------+--------+---------+----------+--+ 5 | 95001 | 李勇 | 男 | 20 | CS | 6 | 95002 | 劉晨 | 女 | 19 | IS | 7 | 95003 | 王敏 | 女 | 22 | MA | 8 | 95004 | 張立 | 男 | 19 | IS | 9 | 95005 | 劉剛 | 男 | 18 | MA | 10 | 95006 | 孫慶 | 男 | 23 | CS | 11 | 95007 | 易思玲 | 女 | 19 | MA | 12 | 95008 | 李娜 | 女 | 18 | CS | 13 | 95009 | 夢圓圓 | 女 | 18 | MA | 14 +--------+----------+--------+---------+----------+--+ 15 9 rows selected (0.081 seconds)
查詢全體學生的學號與姓名
1 select a.Sno, a.Sname from student a; 2 +--------+----------+--+ 3 | a.sno | a.sname | 4 +--------+----------+--+ 5 | 95001 | 李勇 | 6 | 95002 | 劉晨 | 7 | 95003 | 王敏 | 8 | 95004 | 張立 | 9 | 95005 | 劉剛 | 10 | 95006 | 孫慶 | 11 | 95007 | 易思玲 | 12 | 95008 | 李娜 | 13 | 95009 | 夢圓圓 | 14 | 95010 | 孔小濤 | 15 +--------+----------+--+ 16 10 rows selected (0.085 seconds)
查詢學生的總人數
1 select count(distinct sno) count from student; 2 +--------+--+ 3 | count | 4 +--------+--+ 5 | 10 | 6 +--------+--+ 7 1 row selected (21.355 seconds)
3.2. 成績表相關查詢
查詢選修了課程的學生姓名
1 # 表和表欄位，大小寫不明感 distinct 去重覆 2 select distinct a.sno, a.sname from student a inner join sc b on a.sno = b.sno; 3 +--------+----------+--+ 4 | a.sno | a.sname | 5 +--------+----------+--+ 6 | 95001 | 李勇 | 7 | 95002 | 劉晨 | 8 | 95003 | 王敏 | 9 | 95004 | 張立 | 10 | 95005 | 劉剛 | 11 | 95006 | 孫慶 | 12 | 95008 | 李娜 | 13 | 95010 | 孔小濤 | 14 +--------+----------+--+ 15 8 rows selected (32.694 seconds)
計算1號課程的學生平均成績
1 select avg(a.grade) from sc a where a.cno = 1; 2 +--------------------+--+ 3 | _c0 | 4 +--------------------+--+ 5 | 82.55555555555556 | 6 +--------------------+--+ 7 1 row selected (20.72 seconds)
查詢各科成績平均分
1 select a.cno, avg(a.grade) from sc a group by a.cno; 2 +--------+--------------------+--+ 3 | a.cno | _c1 | 4 +--------+--------------------+--+ 5 | 1 | 82.55555555555556 | 6 | 2 | 83.5 | 7 | 3 | 88.125 | 8 | 4 | 78.57142857142857 | 9 | 5 | 78.83333333333333 | 10 | 6 | 90.8 | 11 +--------+--------------------+--+ 12 6 rows selected (20.084 seconds)
查詢選修1號課程的學生最高分數【查課程和分數，不要學生信息】
1 select a.cno, a.grade from sc a where a.cno = 1 order by a.grade desc limit 1; 2 +--------+----------+--+ 3 | a.cno | a.grade | 4 +--------+----------+--+ 5 | 1 | 98 | 6 +--------+----------+--+ 7 1 row selected (19.005 seconds)
3.3. 函數查詢
求各個課程號及相應的選課人數
1 select a.cno, count(a.sno) from sc a group by a.cno; 2 +--------+------+--+ 3 | a.cno | _c1 | 4 +--------+------+--+ 5 | 1 | 9 | 6 | 2 | 8 | 7 | 3 | 8 | 8 | 4 | 7 | 9 | 5 | 6 | 10 | 6 | 5 | 11 +--------+------+--+ 12 6 rows selected (20.967 seconds)
查詢選修了3門以上的課程的學生學號
1 select * from (select a.sno, count(a.cno) countCno from sc a group by a.sno) x where x.countCno > 3; 2 或 select a.sno, count(a.cno) countCno from sc a group by a.sno having countCno > 3; 3 或 select a.sno, count(a.cno) countCno from sc a group by a.sno having count(a.cno) > 3; 4 +--------+-------------+--+ 5 | x.sno | x.countcno | 6 +--------+-------------+--+ 7 | 95001 | 4 | 8 | 95002 | 4 | 9 | 95004 | 4 | 10 | 95005 | 4 | 11 | 95006 | 6 | 12 | 95011 | 4 | 13 | 95012 | 4 | 14 | 95013 | 4 | 15 +--------+-------------+--+ 16 8 rows selected (19.556 seconds)
查詢學生信息，結果按學號全局有序
1 select * from student a order by a.sno; 2 +--------+----------+--------+---------+----------+--+ 3 | a.sno | a.sname | a.sex | a.sage | a.sdept | 4 +--------+----------+--------+---------+----------+--+ 5 | 95001 | 李勇 | 男 | 20 | CS | 6 | 95002 | 劉晨 | 女 | 19 | IS | 7 | 95003 | 王敏 | 女 | 22 | MA | 8 | 95004 | 張立 | 男 | 19 | IS | 9 | 95005 | 劉剛 | 男 | 18 | MA | 10 | 95006 | 孫慶 | 男 | 23 | CS | 11 | 95007 | 易思玲 | 女 | 19 | MA | 12 | 95008 | 李娜 | 女 | 18 | CS | 13 | 95009 | 夢圓圓 | 女 | 18 | MA | 14 | 95010 | 孔小濤 | 男 | 19 | CS | 15 +--------+----------+--------+---------+----------+--+ 16 10 rows selected (18.736 seconds)
查詢學生信息，按性別分區，在分區內按年齡有序
1 # 設置map數量 2 0: jdbc:hive2://mini01:10000> set mapreduce.job.reduces = 2; 3 No rows affected (0.007 seconds) 4 0: jdbc:hive2://mini01:10000> set mapreduce.job.reduces; 5 +--------------------------+--+ 6 | set | 7 +--------------------------+--+ 8 | mapreduce.job.reduces=2 | 9 +--------------------------+--+ 10 1 row selected (0.01 seconds) 11 ### 或者 ************** 12 0: jdbc:hive2://mini01:10000> set mapred.reduce.tasks; 13 +-------------------------+--+ 14 | set | 15 +-------------------------+--+ 16 | mapred.reduce.tasks=-1 | 17 +-------------------------+--+ 18 1 row selected (0.008 seconds) 19 0: jdbc:hive2://mini01:10000> set mapred.reduce.tasks=2; 20 No rows affected (0.004 seconds) 21 0: jdbc:hive2://mini01:10000> set mapred.reduce.tasks; 22 +------------------------+--+ 23 | set | 24 +------------------------+--+ 25 | mapred.reduce.tasks=2 | 26 +------------------------+--+ 27 1 row selected (0.008 seconds) 28 ################################################################## 29 # 具體操作 30 select * from student a distribute by a.sex sort by a.sage; 31 +--------+----------+--------+---------+----------+--+ 32 | a.sno | a.sname | a.sex | a.sage | a.sdept | 33 +--------+----------+--------+---------+----------+--+ 34 | 95005 | 劉剛 | 男 | 18 | MA | 35 | 95010 | 孔小濤 | 男 | 19 | CS | 36 | 95004 | 張立 | 男 | 19 | IS | 37 | 95001 | 李勇 | 男 | 20 | CS | 38 | 95006 | 孫慶 | 男 | 23 | CS | 39 | 95009 | 夢圓圓 | 女 | 18 | MA | 40 | 95008 | 李娜 | 女 | 18 | CS | 41 | 95007 | 易思玲 | 女 | 19 | MA | 42 | 95002 | 劉晨 | 女 | 19 | IS | 43 | 95003 | 王敏 | 女 | 22 | MA | 44 +--------+----------+--------+---------+----------+--+ 45 10 rows selected (22.323 seconds)
3.4. 多表查詢
查詢每個學生及其選修課程的情況
1 select a.sno, a.sname, c.cno, c.cname from 2 student a inner join sc b on a.sno = b.sno 3 inner join course c on b.cno = c.cno 4 order by a.sno, c.cno; 5 +--------+----------+--------+----------+--+ 6 | a.sno | a.sname | c.cno | c.cname | 7 +--------+----------+--------+----------+--+ 8 | 95001 | 李勇 | 1 | 資料庫 | 9 | 95001 | 李勇 | 2 | 數學 | 10 | 95001 | 李勇 | 3 | 信息系統 | 11 | 95001 | 李勇 | 4 | 操作系統 | 12 | 95002 | 劉晨 | 2 | 數學 | 13 | 95002 | 劉晨 | 3 | 信息系統 | 14 | 95002 | 劉晨 | 4 | 操作系統 | 15 | 95002 | 劉晨 | 5 | 數據結構 | 16 | 95003 | 王敏 | 1 | 資料庫 | 17 | 95003 | 王敏 | 3 | 信息系統 | 18 | 95003 | 王敏 | 5 | 數據結構 | 19 | 95004 | 張立 | 1 | 資料庫 | 20 | 95004 | 張立 | 2 | 數學 | 21 | 95004 | 張立 | 4 | 操作系統 | 22 | 95004 | 張立 | 5 | 數據結構 | 23 | 95005 | 劉剛 | 1 | 資料庫 | 24 | 95005 | 劉剛 | 2 | 數學 | 25 | 95005 | 劉剛 | 3 | 信息系統 | 26 | 95005 | 劉剛 | 6 | 數據處理 | 27 | 95006 | 孫慶 | 1 | 資料庫 | 28 | 95006 | 孫慶 | 2 | 數學 | 29 | 95006 | 孫慶 | 3 | 信息系統 | 30 | 95006 | 孫慶 | 4 | 操作系統 | 31 | 95006 | 孫慶 | 5 | 數據結構 | 32 | 95006 | 孫慶 | 6 | 數據處理 | 33 | 95008 | 李娜 | 1 | 資料庫 | 34 | 95008 | 李娜 | 3 | 信息系統 | 35 | 95008 | 李娜 | 6 | 數據處理 | 36 | 95010 | 孔小濤 | 2 | 數學 | 37 | 95010 | 孔小濤 | 5 | 數據結構 | 38 | 95010 | 孔小濤 | 6 | 數據處理 | 39 +--------+----------+--------+----------+--+ 40 31 rows selected (26.356 seconds)
查詢學生的得分情況
1 select a.sno, a.sname, c.cno, c.cname, b.grade from 2 student a inner join sc b on a.sno = b.sno 3 inner join course c on b.cno = c.cno 4 order by a.sno, c.cno; 5 +--------+----------+--------+----------+----------+--+ 6 | a.sno | a.sname | c.cno | c.cname | b.grade | 7 +--------+----------+--------+----------+----------+--+ 8 | 95001 | 李勇 | 1 | 資料庫 | 81 | 9 | 95001 | 李勇 | 2 | 數學 | 85 | 10 | 95001 | 李勇 | 3 | 信息系統 | 88 | 11 | 95001 | 李勇 | 4 | 操作系統 | 70 | 12 | 95002 | 劉晨 | 2 | 數學 | 90 | 13 | 95002 | 劉晨 | 3 | 信息系統 | 80 | 14 | 95002 | 劉晨 | 4 | 操作系統 | 71 | 15 | 95002 | 劉晨 | 5 | 數據結構 | 60 | 16 | 95003 | 王敏 | 1 | 資料庫 | 82 | 17 | 95003 | 王敏 | 3 | 信息系統 | 90 | 18 | 95003 | 王敏 | 5 | 數據結構 | 100 | 19 | 95004 | 張立 | 1 | 資料庫 | 80 | 20 | 95004 | 張立 | 2 | 數學 | 92 | 21 | 95004 | 張立 | 4 | 操作系統 | 91 | 22 | 95004 | 張立 | 5 | 數據結構 | 70 | 23 | 95005 | 劉剛 | 1 | 資料庫 | 70 | 24 | 95005 | 劉剛 | 2 | 數學 | 92 | 25 | 95005 | 劉剛 | 3 | 信息系統 | 99 | 26 | 95005 | 劉剛 | 6 | 數據處理 | 87 | 27 | 95006 | 孫慶 | 1 | 資料庫 | 72 | 28 | 95006 | 孫慶 | 2 | 數學 | 62 | 29 | 95006 | 孫慶 | 3 | 信息系統 | 100 | 30 | 95006 | 孫慶 | 4 | 操作系統 | 59 | 31 | 95006 | 孫慶 | 5 | 數據結構 | 60 | 32 | 95006 | 孫慶 | 6 | 數據處理 | 98 | 33 | 95008 | 李娜 | 1 | 資料庫 | 98 | 34 | 95008 | 李娜 | 3 | 信息系統 | 89 | 35 | 95008 | 李娜 | 6 | 數據處理 | 91 | 36 | 95010 | 孔小濤 | 2 | 數學 | 98 | 37 | 95010 | 孔小濤 | 5 | 數據結構 | 90 | 38 | 95010 | 孔小濤 | 6 | 數據處理 | 80 | 39 +--------+----------+--------+----------+----------+--+ 40 31 rows selected (24.469 seconds)
查詢選修2號課程且成績在90分以上的所有學生
1 select a.sno, a.sname, b.cno, b.grade 2 from student a inner join sc b on a.sno = b.sno 3 where b.cno = 2 and b.grade >= 90; 4 +--------+----------+--------+----------+--+ 5 | a.sno | a.sname | b.cno | b.grade | 6 +--------+----------+--------+----------+--+ 7 | 95002 | 劉晨 | 2 | 90 | 8 | 95004 | 張立 | 2 | 92 | 9 | 95005 | 劉剛 | 2 | 92 | 10 | 95010 | 孔小濤 | 2 | 98 | 11 +--------+----------+--------+----------+--+ 12 4 rows selected (16.728 seconds)
查詢所有學生的信息，如果在成績表中有成績，則輸出成績表中的課程號和成績
如果student的sno值對應的sc在中沒有值，則會輸出student.Sname null.如果用right out join會保留右邊的值，左邊的為null。 Join 發生在WHERE 子句之前。如果你想限制 join 的輸出，應該在 WHERE 子句中寫過濾條件——或是在join 子句中寫。
1 select a.sno, a.sname, b.cno, b.grade 2 from student a left join sc b on a.sno = b.sno; 3 +--------+----------+--------+----------+--+ 4 | a.sno | a.sname | b.cno | b.grade | 5 +--------+----------+--------+----------+--+ 6 | 95001 | 李勇 | 1 | 81 | 7 | 95001 | 李勇 | 2 | 85 | 8 | 95001 | 李勇 | 3 | 88 | 9 | 95001 | 李勇 | 4 | 70 | 10 | 95002 | 劉晨 | 2 | 90 | 11 | 95002 | 劉晨 | 3 | 80 | 12 | 95002 | 劉晨 | 4 | 71 | 13 | 95002 | 劉晨 | 5 | 60 | 14 | 95003 | 王敏 | 1 | 82 | 15 | 95003 | 王敏 | 3 | 90 | 16 | 95003 | 王敏 | 5 | 100 | 17 | 95004 | 張立 | 1 | 80 | 18 | 95004 | 張立 | 2 | 92 | 19 | 95004 | 張立 | 4 | 91 | 20 | 95004 | 張立 | 5 | 70 | 21 | 95005 | 劉剛 | 1 | 70 | 22 | 95005 | 劉剛 | 2 | 92 | 23 | 95005 | 劉剛 | 3 | 99 | 24 | 95005 | 劉剛 | 6 | 87 | 25 | 95006 | 孫慶 | 1 | 72 | 26 | 95006 | 孫慶 | 2 | 62 | 27 | 95006 | 孫慶 | 3 | 100 | 28 | 95006 | 孫慶 | 4 | 59 | 29 | 95006 | 孫慶 | 5 | 60 | 30 | 95006 | 孫慶 | 6 | 98 | 31 | 95007 | 易思玲 | NULL | NULL | 32 | 95008 | 李娜 | 1 | 98 | 33 | 95008 | 李娜 | 3 | 89 | 34 | 95008 | 李娜 | 6 | 91 | 35 | 95009 | 夢圓圓 | NULL | NULL | 36 | 95010 | 孔小濤 | 2 | 98 | 37 | 95010 | 孔小濤 | 5 | 90 | 38 | 95010 | 孔小濤 | 6 | 80 | 39 +--------+----------+--------+----------+--+ 40 33 rows selected (17.467 seconds)
查詢與“王敏”在同一個系學習的學生
1 select a.sno, a.sname 2 from student a inner join student b 3 on a.sdept = b.sdept
