~~~ insert into dept values(1,'Education'),(2,'Computer Science'),(3,'Mathematics'); insert into students values(1,'Andrew',0,1),(2,'Andy',0,1),(3,'Bob',0,1),(4,'Ruth',1,2),(5,'Mike',0,2),(6,'John',0,3),(7,'Cindy',1,3),(8,'Susan',1,3); insert into course values(1,'math',3),(2,'english',2),(3,'chinese',4),(4,'history',1),(5,'biology',5); insert into teacher values(1,'Zhang san',1),(2,'Li si',1),(3,'Wang wu',2),(4,'Liu liu',3),(5,'Ding qi',3); insert into score values(1,1,76),(1,2,90),(1,3,82),(1,5,56),(2,2,78),(2,4,92),(2,3,77),(2,5,65),(3,1,48),(3,2,95),(3,3,75),(3,4,89),(3,5,92),(4,3,78),(4,4,67),(5,1,75),(5,3,90),(5,4,82),(6,2,58),(6,4,88),(7,1,55),(7,2,65),(7,3,63),(7,4,68),(7,5,70),(8,4,88),(8,5,100); ~~~ 4.查看所有英语成绩超过数学成绩的学生的学号和姓名 5.查看平均成绩大于等于60的所有学生的姓名和平均成绩 6.查询所有同学的学号，姓名，选课数和总成绩 7.查询姓zhang的老师的个数 8.查询没学过zhang san老师课程的学生的学号和姓名 ~~~ 4、select aa.sid,aa.sname,aa.score as math,bb.score as english from (select a1.sid,a1.sname,score from students a1 inner join score b1 on a1.sid=b1.sid where course_id=(select id from course where course_name='math')) aa inner join (select a1.sid,a1.sname,score from students a1 inner join score b1 on a1.sid=b1.sid where course_id=(select id from course where course_name='english')) bb on aa.sid=bb.sid where aa.score < bb.score; 5、select a.sid,a.sname,avg(b.score) from students a inner join score b on a.sid=b.sid group by a.sid,a.sname having avg(b.score)>==60; 6、select a.sid,a.sname ,count(b.course_id),sum(b.score) from students a inner join score b on a.sid=b.sid group by a.sid,a.sname; 7、select count(*) from teacher where name like 'zhang%'; 8、Select sid,sname from students where Sid not in (Select b.sid From course a inner Join score b on a.id=b.course_id Inner join teacher c on a.teacher_id=c.id Where c.name='Zhang san'); ~~~ 9.查询既学过英语也学过语文的学生的学号和姓名 11.查询有学生的单科成绩小于60的姓名和课程名称 ~~~ 9、Select a.sid,sname,count(*) from students a inner join score b on a.sid=b.sid Inner join course c on b.course_id=c.id Where c.name in ('english', 'chinese') Group by Sid,sname Having count(*)>=2; 11、Select a.sname,c.course_name From students a inner join score b on a.sid=b.sid Inner join course c on b.course_id=c.id Where b.score<60; ~~~ 13.按平均成绩从高到低显示所有学生的姓名和语文，数学，英语三科成绩 14.查询各科成绩中的最高分和最低分 15.计算各科平均成绩和及格率百分比 16.查询不同老师所教不同课程平均分从高到低 ~~~ 13、Select a.sid,avg(score) score_avg,sum(case when b.course_name='chinese' then a.score else 0 end) a1,sum(case when b.course_name='math' then a.score else 0 end) a2,sum(case when b.course_name='English' then a.score else 0 end) a3 From score a inner join course b on a.course_id=b.id Group by a.sid order by score_avg desc; 14、Select course_id,min(score),max(score) from score group by course_id; 15、Select course_id,avg(score),sum(case when score>=60 then 1 else 0 end)/count(*)*100 From score Group by course_id 16、Select c.name,b.name,avg(score) From score a inner join course b on a.course_id=b.id Inner join teacher c on b.teacher_id=c.id Group by c.name,b.name Order by avg(score) desc ~~~ 17.查询英语课程成绩排名第5到第10位的学生姓名和成绩 18.统计按照各科成绩，分段统计每个课程在90分以上、80-90的、60~80、低于60分的人数 19.查看每门课程被选修的学生数 20.查看只学习了一门课程的学生的姓名和学号 ~~~ 17、Select c.sname,a.score From score a inner join course b on a.course_id=b.id Inner join students c on a.sid=c.sid Where b.name='English' Order by a.score Limit 4,6; 18、Select b.name,sum(case when score>=90 then 1 else 0 end),sum(case when score<90 and score>=80 then 1 else 0 end),sum(case when score<80 and score>=60 then 1 else 0 end),sum(case when score<60 then 1 else 0 end) From score a inner join course b on a.course_id=b.id Group by b.name; 19、Select course_id,count(*) From score Group by course_id; 20、Select a.sid,b.sname from (Select sid,count(*) count1 from score group by Sid having count(*)=1) a Inner join students b on a.sid=b.sid; ~~~ 21.查询名字相同的学生名单和个数 22.查询85年之后出生的学生人数 23.查询每门课程的平均成绩，按升序排序，如果平均成绩相同按课程ID降序排序 24.查询有不及格学生的课程和不及格学生个数 21 Select sname,count(*) from students group by sname having count(*)>=2; 22 Select * from students where birthday>='1985-01-01'; 23 Select course_id,avg(score) from score Group by course_id Order by avg(score),course_id desc; 24 Select course_id,count(*) From score Where score<60 Group by course_id; 25.将所有学生姓名中前后的空格去掉 26.将所有学生的考试成绩展示为课程名：成绩样式 27.将所有老师的名字拆分成姓和名两个字段显示 28.把所有学生的生日格式转换成年月日的格式，并计算每个学生年龄 ~~~ 25 Update students set sname=ltrim(rtrim(sname)); 26 Select a.sid,concat(b.name,':',a.score)From score a inner join course b on a.course_id=b.id; 27 Select name, substring(name,1,locate(' ',name)-1),substring(name,locate(' ',name)+1,50)from teacher; 28 Select name,birthday,date_format(birthday, '%Y%m%d'),year(now())-year(birthday),floor(datediff(now,birthday)/365)From students; ~~~