目前有三个表:student 学生信息表 ,course选课课程信息表,exame选课情况信息表。其中 stduent 和course 是exame子表。
我们先用外连接看一下标的基本信息,其中1-4都有选课。5号只插入了学生表的信息。
mysql> select s.*,e.* from student s left join exame e on s.uid = e.uid;
+-----+--------+-----+--------+------+------+------------+-------+
| uid | name | age | sex | uid | cid | time | score |
+-----+--------+-----+--------+------+------+------------+-------+
| 1 | Alice | 20 | female | 1 | 1 | 2023-08-25 | 85 |
| 1 | Alice | 20 | female | 1 | 2 | 2023-08-26 | 92 |
| 2 | Bob | 22 | male | 2 | 1 | 2023-08-25 | 78 |
| 2 | Bob | 22 | male | 2 | 3 | 2023-08-27 | 88 |
| 3 | Claire | 21 | female | 3 | 2 | 2023-08-26 | 95 |
| 3 | Claire | 21 | female | 3 | 4 | 2023-08-28 | 90 |
| 4 | David | 23 | male | 4 | 1 | 2023-08-25 | 82 |
| 4 | David | 23 | male | 4 | 4 | 2023-08-28 | 91 |
| 5 | Eeboy | 25 | male | NULL | NULL | NULL | NULL |
+-----+--------+-----+--------+------+------+------------+-------+
9 rows in set (0.00 sec)
#我们对e.uid进行筛选一下。
mysql> select s.*,e.* from student s left join exame e on s.uid = e.uid and e.uid is NULL;
+-----+--------+-----+--------+------+------+------+-------+
| uid | name | age | sex | uid | cid | time | score |
+-----+--------+-----+--------+------+------+------+-------+
| 1 | Alice | 20 | female | NULL | NULL | NULL | NULL |
| 2 | Bob | 22 | male | NULL | NULL | NULL | NULL |
| 3 | Claire | 21 | female | NULL | NULL | NULL | NULL |
| 4 | David | 23 | male | NULL | NULL | NULL | NULL |
| 5 | Eeboy | 25 | male | NULL | NULL | NULL | NULL |
+-----+--------+-----+--------+------+------+------+-------+
5 rows in set (0.01 sec)
#引出了问题 为什么在exame信息成null了?
#换成where就好了。
mysql> select s.*,e.* from student s left join exame e on s.uid = e.uid where e.uid is NULL;
+-----+-------+-----+------+------+------+------+-------+
| uid | name | age | sex | uid | cid | time | score |
+-----+-------+-----+------+------+------+------+-------+
| 5 | Eeboy | 25 | male | NULL | NULL | NULL | NULL |
+-----+-------+-----+------+------+------+------+-------+
1 row in set (0.00 sec)