经典sql语句整合
s
sid name
1 jim
2 kate
3 Jerry
c
cid name
1 chinese
2 english
3 maths
sc
sid cid score
1 1 90
1 2 85
2 1 90
1:选出平均分大于等于60分的学生的学号和姓名
mysql> select s.sid, s.name, avg(score) as '平均分' from s, sc where s.sid=sc.sid group by s
.sid, s.name having avg(score)>59;
结果:
+------+------+------------+
| sid | name | 平均分|
+------+------+------------+
| 1 | jim | 87.5000 |
| 2 | Kate | 90.0000 |
+------+------+------------+
2:选出学生的学号,姓名,以及选课数目,若未选课输出为0
mysql> select s.sid, s.name, count(sc.cid) as '选课数目' from s left join sc on
s.sid=sc.sid group by s.sid, s.name;
结果:
+------+-------+----------+
| sid | name | 选课数目 |
+------+-------+----------+
| 1 | jim | 2 |
| 2 | Kate | 1 |
| 3 | Jerry | 0 |
+------+-------+----------+
3:做一个成绩单表格,列分别为学生学号,姓名,语文成绩,英语成绩,数学成绩,平均分
mysql>select s.sid,s.name,max(if(c.name='chinese',score,0)) as '语文',min(if(
c.name='Maths',score,0)) as '数学',max(if(c.name='english',score,0)) as '英语',
avg(sc.score) as '平均分' from s,c,sc where s.sid=sc.sid and c.cid=sc.cid grou
p by s.sid,s.name;
结果:
+------+------+------+------+------+---------+
| sid | name | 语文 | 数学 | 英语 | 平均分 |
+------+------+------+------+------+---------+
| 1 | jim | 90 | 0 | 85 | 87.5000 |
| 2 | Kate | 90 | 0 | 0 | 90.0000 |
+------+------+------+------+------+---------+