#学生表 create table student( s_id string, s_name string, s_birth string, s_sex string ) row format delimited fields terminated by ' '; #sid,sname,sbirth,ssex #学生信息 01 George 1990-01-01 男 02 honey 1990-12-21 男 03 kk 1990-05-20 男 04 ll 1990-08-06 男 05 Georgedage 1991-12-01 女 06 lm 1992-03-01 女 07 lucy 1989-07-01 女 08 lili 1990-01-20 女 #课程表 create table course( c_id string, c_name string, t_id string ) row format delimited fields terminated by ' '; #cid,cname,tid #课程信息 01 c 02 02 java 01 03 python 03 #教师表 create table teacher( t_id string, t_name string ) row format delimited fields terminated by ' '; #tid,tname #教室信息 01 wu 02 li 03 yuan #成绩表 create table score( s_id string, c_id string, s_score int ) row format delimited fields terminated by ' '; #sid cid score #成绩信息 01 01 80 01 02 90 01 03 99 02 01 70 02 02 60 02 03 80 03 01 80 03 02 80 03 03 80 04 01 50 04 02 30 04 03 20 05 01 76 05 02 87 06 01 31 06 03 34 07 02 89 07 03 98 #2、导入数据 load data local inpath "/root/student" into table student; load data local inpath "/root/course" into table course; load data local inpath "/root/teacher" into table teacher; load data local inpath "/root/score" into table score; select * from course,score; select student.*,score.s_score from student,score where student.s_id=score.s_id; – 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数: *第一种写法 select student.*,a.s_score as 01_score,b.s_score as 02_score from student join score a on student.s_id=a.s_id and a.c_id='01' left join score b on student.s_id=b.s_id and b.c_id='02' where a.s_score>b.s_score; *第二种写法 select student.*,sc1.s_score,sc2.s_score from student,score sc1,score sc2 where student.s_id=sc1.s_id and student.s_id=sc2.s_id and sc1.c_id='01' and sc2.c_id='02' and sc1.s_score>sc2.s_score; ~结果： 02 honey 1990-12-21 男 70 60 04 ll 1990-08-06 男 50 30 – 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数: *第一种写法 select student.*,sc1.s_score,sc2.s_score from student,score sc1,score sc2 where student.s_id=sc1.s_id and student.s_id=sc2.s_id and sc1.c_id='01' and sc2.c_id='02' and sc1.s_score<sc2.s_score; *第二种写法 select student.*,sc1.s_score,sc2.s_score from student join score sc1 on student.s_id=sc1.s_id and sc1.c_id='01' join score sc2 on student.s_id=sc2.s_id and sc2.c_id='02' where sc1.s_score<sc2.s_score; ~结果： 01 George 1990-01-01 男 80 90 05 Georgedage 1991-12-01 女 76 87 select score.s_id,avg(score.s_score) from score group by s_id having avg(score.s_score)>60; select student.s_id,student.s_name,round(avg (score.s_score),1) as 平均成绩 from student join score on student.s_id = score.s_id group by student.s_id,student.s_name having avg (score.s_score) >= 60; – 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩: *第一种写法 select student.s_id,student.s_name,sc.avg2 from student,(select score.s_id id,avg(score.s_score) avg2 from score group by s_id having avg(score.s_score)>60) as sc where student.s_id=sc.id; *第二种写法【既然不让我用where，那我就用on】 select student.s_id,student.s_name,round(avg(score.s_score),1) as avg2 from student join score on student.s_id=score.s_id group by student.s_id,student.s_name having avg(score.s_score)>60; ~结果： 01 George 89.66666666666667 02 honey 70.0 03 kk 80.0 05 Georgedage 81.5 07 lucy 93.5 – 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩: – (包括有成绩的和无成绩的) *第一种写法 select student.s_id,student.s_name,round(avg(score.s_score),1) as avg2 from student left join score on student.s_id= score.s_id group by student.s_id,student.s_name having avg(score.s_score)<60; ~结果： 04 ll 33.3 06 lm 32.5 – 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩: *第一种写法 select student.s_id,student.s_name,count(score.c_id),sum(score.s_score) from student left join score on student.s_id=score.s_id group by student.s_id,student.s_name; ~结果： 01 George 3 269 02 honey 3 210 03 kk 3 240 04 ll 3 100 05 Georgedage 2 163 06 lm 2 65 07 lucy 2 187 08 lili 0 NULL – 6、查询"李"姓老师的数量: *第一种写法 select count(t_id) from teacher t where t.t_name like '李%'; ~结果： 1 #编码问题，所以改变老师名字。 – 7、查询学过"吴老大"老师授课的同学的信息: *第一种写法 select s.* from student s where s.s_id in (select s_id from score sc,course c,teacher t where sc.c_id = c.c_id and c.t_id = t.t_id and t.t_name='wu'); ~结果： – 8、查询没学过"吴老大"老师授课的同学的信息: select s.* from student s where s.s_id not in (select s_id from score sc,course c,teacher t where sc.c_id = c.c_id and c.t_id = t.t_id and t.t_name = 'wu'); ~结果： 06 lm 1992-03-01 女 08 lili 1990-01-20 女 – 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息: *第一种写法 select s.* from student s,(select s_id from score where score.c_id=01) sc1,(select s_id from score where score.c_id=02) sc2 where sc1.s_id=sc2.s_id and sc1.s_id=s.s_id; ~结果： 01 George 1990-01-01 男 02 honey 1990-12-21 男 03 kk 1990-05-20 男 04 ll 1990-08-06 男 05 Georgedage 1991-12-01 女
