多对多表的连接关系

//建立三张表，学生表、课程表和中间表

//找出学生和和课程之间的联系

mysql> select * from stu left join electives on stu.id=electives.uid left join course on electives.coid=course.id ;
+----+--------+----------------------------------+-----+------+------+------+------------+-------+
| id | name   | password                         | cid | uid  | coid | id   | coursename | score |
+----+--------+----------------------------------+-----+------+------+------+------------+-------+
|  1 | 曾九峰 | 4bec52e77ce530aa470e302ef2d52556 |   2 |    1 |    3 |    3 | PHP        | 6     |
|  1 | 曾九峰 | 4bec52e77ce530aa470e302ef2d52556 |   2 |    1 |    4 |    4 | JS         | 4     |
|  1 | 曾九峰 | 4bec52e77ce530aa470e302ef2d52556 |   2 |    1 |    2 |    2 | HTML       | 3     |
|  2 | 林天福 | 4bec52e77ce530aa470e302ef2d52556 |   1 |    2 |    1 |    1 | PS         | 3     |
|  3 | 杨春辉 | 4bec52e77ce530aa470e302ef2d52556 |   1 |    3 |    2 |    2 | HTML       | 3     |
|  5 | 曾建平 | 4bec52e77ce530aa470e302ef2d52556 |   2 |    5 |    2 |    2 | HTML       | 3     |
|  4 | 郭海鹏 | 4bec52e77ce530aa470e302ef2d52556 |   1 |    4 |    3 |    3 | PHP        | 6     |
|  4 | 郭海鹏 | 4bec52e77ce530aa470e302ef2d52556 |   1 |    4 |    2 |    2 | HTML       | 3     |
|  3 | 杨春辉 | 4bec52e77ce530aa470e302ef2d52556 |   1 |    3 |    3 |    3 | PHP        | 6     |
|  2 | 林天福 | 4bec52e77ce530aa470e302ef2d52556 |   1 |    2 |    4 |    4 | JS         | 4     |
|  6 | 马志斌 | 4bec52e77ce530aa470e302ef2d52556 |   5 | NULL | NULL | NULL | NULL       | NULL  |
+----+--------+----------------------------------+-----+------+------+------+------------+-------+
11 rows in set (0.00 sec)

//找出海鹏的所有信息

mysql> select * from stu left join electives on stu.id=electives.uid left join course on electives.coid=course.id where stu.id=4;
+----+--------+----------------------------------+-----+------+------+------+------------+-------+
| id | name   | password                         | cid | uid  | coid | id   | coursename | score |
+----+--------+----------------------------------+-----+------+------+------+------------+-------+
|  4 | 郭海鹏 | 4bec52e77ce530aa470e302ef2d52556 |   1 |    4 |    2 |    2 | HTML       | 3     |
|  4 | 郭海鹏 | 4bec52e77ce530aa470e302ef2d52556 |   1 |    4 |    3 |    3 | PHP        | 6     |
+----+--------+----------------------------------+-----+------+------+------+------------+-------+
2 rows in set (0.00 sec)

//检索出学生海鹏的课程名称。
mysql> select coursename from stu left join electives on stu.id=electives.uid left join course on electives.coid=course.id where stu.id=4;
+------------+
| coursename |
+------------+
| HTML       |
| PHP        |
+------------+
2 rows in set (0.00 sec)

//检索出和海鹏都学一样课程的学生
mysql> select stu.name from stu left join electives on stu.id=electives.uid left join course on electives.coid=course.id where course.coursename=( sel
ect coursename from stu left join electives on stu.id=electives.uid left join course on electives.coid=course.id where stu.id=4);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select stu.name from stu left join electives on stu.id=electives.uid left join course on electives.coid=course.id where coursename=( select cou
rsename from stu left join electives on stu.id=electives.uid left join course on electives.coid=course.id where stu.id=4);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select stu.name from stu where coursename=( select coursename from stu left join electives on stu.id=electives.uid left join course on elective
s.coid=course.id where stu.id=4);
ERROR 1054 (42S22): Champ 'coursename' inconnu dans where clause
mysql> select stu.name from stu where course.coursename=( select coursename from stu left join electives on stu.id=electives.uid left join course on e
lectives.coid=course.id where stu.id=4);
ERROR 1054 (42S22): Champ 'course.coursename' inconnu dans where clause
mysql> select stu.name from stu where coursename=( select coursename from stu left join electives on stu.id=electives.uid left join course on elective
s.coid=course.id where stu.id=4);
ERROR 1054 (42S22): Champ 'coursename' inconnu dans where clause
mysql> select stu.name from stu left join electives on stu.id=electives.uid left join course on elective.coid=course.id  where coursename=( select cou
rsename from stu left join electives on stu.id=electives.uid left join course on electives.coid=course.id where stu.id=4);
ERROR 1054 (42S22): Champ 'elective.coid' inconnu dans on clause
mysql> select stu.name from stu left join electives on stu.id=electives.uid left join course on electives.coid=course.id  where coursename=( select co
ursename from stu left join electives on stu.id=electives.uid left join course on electives.coid=course.id where stu.id=4);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select stu.name from stu left join electives on stu.id=electives.uid left join course on electives.coid=course.id  where coursename in ( select
 coursename from stu left join electives on stu.id=electives.uid left join course on electives.coid=course.id where stu.id=4);

+--------+

+--------+
| name   |
+--------+
| 曾九峰 |
| 曾九峰 |
| 杨春辉 |
| 曾建平 |
| 郭海鹏 |
| 郭海鹏 |
| 杨春辉 |
+--------+
7 rows in set (0.00 sec)

//注意：当我们的结果条件是多个时（如上面获取的是html和php），不能用“=“进行比较，应该用”in“;