mysql强化练习_MySQL 强化练习四

mysql> select sid,avg(score) fromscore group by sid order by avg(score) desc;+-----+------------+

| sid | avg(score) |

+-----+------------+

| 8 | 94.0000 |

| 5 | 82.3333 |

| 3 | 79.8000 |

| 2 | 78.0000 |

| 1 | 76.0000 |

| 6 | 73.0000 |

| 4 | 72.5000 |

| 7 | 64.2000 |

+-----+------------+

8 rows in set (0.00sec)

mysql> select sid,score from score a inner join course b on a.course_id=b.id-> where b.course_name in ('chinese','math','english');+-----+-------+

| sid | score |

+-----+-------+

| 1 | 76 |

| 1 | 90 |

| 1 | 82 |

| 2 | 78 |

| 2 | 77 |

| 3 | 48 |

| 3 | 95 |

| 3 | 75 |

| 4 | 78 |

| 5 | 75 |

| 5 | 90 |

| 6 | 58 |

| 7 | 55 |

| 7 | 65 |

| 7 | 63 |

+-----+-------+

15 rows in set (0.00sec)

mysql> select * from

-> (select sid,score from score a inner join course b on a.course_id=b.id-> where b.course_name in ('chinese')) aa->inner join-> (select sid,score from score a inner join course b on a.course_id=b.id-> where b.course_name in ('math')) bb on aa.sid=bb.sid->inner join-> (select sid,score from score a inner join course b on a.course_id=b.id-> where b.course_name in ('english')) cc on aa.sid=cc.sid;+-----+-------+-----+-------+-----+-------+

| sid | score | sid | score | sid | score |

+-----+-------+-----+-------+-----+-------+

| 1 | 82 | 1 | 76 | 1 | 90 |

| 3 | 75 | 3 | 48 | 3 | 95 |

| 7 | 63 | 7 | 55 | 7 | 65 |

+-----+-------+-----+-------+-----+-------+

3 rows in set (0.00sec)

mysql> select sid,avg(score),sum(score) from score a inner join course b on a.course_id=b.id->group by sid order by avg(score) desc;+-----+------------+------------+

| sid | avg(score) | sum(score) |

+-----+------------+------------+

| 8 | 94.0000 | 188 |

| 5 | 82.3333 | 247 |

| 3 | 79.8000 | 399 |

| 2 | 78.0000 | 312 |

| 1 | 76.0000 | 304 |

| 6 | 73.0000 | 146 |

| 4 | 72.5000 | 145 |

| 7 | 64.2000 | 321 |

+-----+------------+------------+

8 rows in set (0.00sec)

mysql> selectsid,avg(score),sum(score),-> sum(case when b.course_name='chinese' then score else 0end),-> sum(case when b.course_name='math' then score else 0end),-> sum(case when b.course_name='english' then score else 0end)-> from score a inner join course b on a.course_id=b.id->group by sid order by avg(score) desc;+-----+------------+------------+--------------------------------------------------------------+-----------------------------------------------------------+--------------------------------------------------------------+

| sid | avg(score) | sum(score) | sum(case when b.course_name='chinese' then score else 0 end) | sum(case when b.course_name='math' then score else 0 end) | sum(case when b.course_name='english' then score else 0 end) |

+-----+------------+------------+--------------------------------------------------------------+-----------------------------------------------------------+--------------------------------------------------------------+

| 8 | 94.0000 | 188 | 0 | 0 | 0 |

| 5 | 82.3333 | 247 | 90 | 75 | 0 |

| 3 | 79.8000 | 399 | 75 | 48 | 95 |

| 2 | 78.0000 | 312 | 77 | 0 | 78 |

| 1 | 76.0000 | 304 | 82 | 76 | 90 |

| 6 | 73.0000 | 146 | 0 | 0 | 58 |

| 4 | 72.5000 | 145 | 78 | 0 | 0 |

| 7 | 64.2000 | 321 | 63 | 55 | 65 |

+-----+------------+------------+--------------------------------------------------------------+-----------------------------------------------------------+--------------------------------------------------------------+

8 rows in set (0.00sec)

mysql> selectsid,avg(score),sum(score),-> sum(case when b.course_name='chinese' then score else 0end) chinese_score,-> sum(case when b.course_name='math' then score else 0end) math_score,-> sum(case when b.course_name='english' then score else 0end) english_score-> from score a inner join course b on a.course_id=b.id->group by sid order by avg(score) desc;+-----+------------+------------+---------------+------------+---------------+

| sid | avg(score) | sum(score) | chinese_score | math_score | english_score |

+-----+------------+------------+---------------+------------+---------------+

| 8 | 94.0000 | 188 | 0 | 0 | 0 |

| 5 | 82.3333 | 247 | 90 | 75 | 0 |

| 3 | 79.8000 | 399 | 75 | 48 | 95 |

| 2 | 78.0000 | 312 | 77 | 0 | 78 |

| 1 | 76.0000 | 304 | 82 | 76 | 90 |

| 6 | 73.0000 | 146 | 0 | 0 | 58 |

| 4 | 72.5000 | 145 | 78 | 0 | 0 |

| 7 | 64.2000 | 321 | 63 | 55 | 65 |

+-----+------------+------------+---------------+------------+---------------+

8 rows in set (0.00sec)

mysql> select * from score where sid=8;+-----+-----------+-------+

| sid | course_id | score |

+-----+-----------+-------+

| 8 | 4 | 88 |

| 8 | 5 | 100 |

+-----+-----------+-------+

2 rows in set (0.00sec)

mysql> select * fromcourse;+----+-------------+------------+

| id | course_name | teacher_id |

+----+-------------+------------+

| 1 | math | 3 |

| 2 | english | 2 |

| 3 | chinese | 4 |

| 4 | history | 1 |

| 5 | biology | 5 |

+----+-------------+------------+

5 rows in set (0.01sec)

mysql> select * from score where sid=5;+-----+-----------+-------+

| sid | course_id | score |

+-----+-----------+-------+

| 5 | 1 | 75 |

| 5 | 3 | 90 |

| 5 | 4 | 82 |

+-----+-----------+-------+

3 rows in set (0.00 sec)