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