mysql上机20题_mysql多表查询20题

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

| Tables_in_dapeng3 |

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

| class |

| course |

| s1 |

| score |

| student |

| teacher |

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

6 rows in set (0.00 sec)

mysql> select * from class;

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

| id | caption |

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

| 1 | 三年二班 |

| 2 | 三年三班 |

| 3 | 一年二班 |

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

3 rows in set (0.11 sec)

mysql> select * from course;

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

| id | cname | teacher_id |

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

| 1 | 生物 | 1 |

| 2 | 物理 | 2 |

| 3 | 体育 | 3 |

| 4 | 美术 | 2 |

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

4 rows in set (0.00 sec)

mysql> select * from student;

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

| id | sname | gender | class_id |

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

| 1 | 铁蛋 | male | 2 |

| 2 | 钢蛋 | female | 1 |

| 3 | 铜蛋 | male | 3 |

| 4 | 银弹 | male | 1 |

| 5 | ety | male | 1 |

| 6 | yuu | female | 2 |

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

6 rows in set (0.11 sec)

mysql> select * from teacher;

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

| id | tname |

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

| 1 | 张磊老师 |

| 2 | 李平老师 |

| 3 | 刘海燕老师 |

| 4 | 朱云海老师 |

| 5 | 李杰老师 |

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

5 rows in set (0.11 sec)

mysql> select * from score;

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

| id | student_id | course_id | number |

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

| 1 | 1 | 1 | 100 |

| 2 | 2 | 1 | 58 |

| 3 | 2 | 2 | 56 |

| 4 | 3 | 3 | 90 |

&