SQL联表查询

student表数据如下：（student.cid不设置外键关联class.cid)
+-------+-----+-----+-----+-----+
| name  | age | sex | sid | cid |
+-------+-----+-----+-----+-----+
| Cindy |  18 | F   |   1 |   1 |
| Alice |  20 | M   |   2 |   2 |
| Mike  |  19 | F   |   3 |   4 |
+-------+-----+-----+-----+-----+
class表数据如下：
+-----------+--------+------+-----+
| classname | grade  | num  | cid |
+-----------+--------+------+-----+
| class1    | grade1 |   40 |   1 |
| class2    | grade1 |   38 |   2 |
| class3    | grade1 |   37 |   3 |
+-----------+--------+------+-----+
先看没有筛选和关联的链表查询，输入
select *  from class,student;
或select *  from class inner join student
+-----------+--------+------+-----+-------+-----+-----+-----+-----+
| classname | grade  | num  | cid | name  | age | sex | sid | cid |
+-----------+--------+------+-----+-------+-----+-----+-----+-----+
| class1    | grade1 |   40 |   1 | Cindy |  18 | F   |   1 |   1 |
| class2    | grade1 |   38 |   2 | Cindy |  18 | F   |   1 |   1 |
| class3    | grade1 |   37 |   3 | Cindy |  18 | F   |   1 |   1 |
| class1    | grade1 |   40 |   1 | Alice |  20 | M   |   2 |   2 |
| class2    | grade1 |   38 |   2 | Alice |  20 | M   |   2 |   2 |
| class3    | grade1 |   37 |   3 | Alice |  20 | M   |   2 |   2 |
| class1    | grade1 |   40 |   1 | Mike  |  19 | F   |   3 |   4 |
| class2    | grade1 |   38 |   2 | Mike  |  19 | F   |   3 |   4 |
| class3    | grade1 |   37 |   3 | Mike  |  19 | F   |   3 |   4 |
+-----------+--------+------+-----+-------+-----+-----+-----+-----+
从查询结果来看是将两表数据组合一起，共有3*3=9条记录。
以cid作为链接对象，左表是class,右表是student,inner join，left join，right join的区别如下：
1、inner join 或 join——左表和右表的cid匹配的记录
（1）select student.sid, student.name,class.cid,class.classname,class.grade from class inner join student on class.cid=student.cid;
（2） select student.sid, student.name,class.cid,class.classname,class.grade from class,student where class.cid=student.cid;
+-----+-------+-----+-----------+--------+
| sid | name  | cid | classname | grade  |
+-----+-------+-----+-----------+--------+
|   1 | Cindy |   1 | class1    | grade1 |
|   2 | Alice |   2 | class2    | grade1 |
+-----+-------+-----+-----------+--------+

2、left join———左表作为联结扣子，保留所有记录，右表没有对应cid的记录填入空值
select student.sid, student.name,class.cid,class.classname,class.grade from class left join student on class.cid=student.cid;
+------+-------+-----+-----------+--------+
| sid  | name  | cid | classname | grade  |
+------+-------+-----+-----------+--------+
|    1 | Cindy |   1 | class1    | grade1 |
|    2 | Alice |   2 | class2    | grade1 |
| NULL | NULL  |   3 | class3    | grade1 |
+------+-------+-----+-----------+--------+

3、right join——右表作为联结扣子，保留所有记录，左表没有对应cid的记录填入空值
select student.sid, student.name,class.cid,class.classname,class.grade from class right join student on class.cid=student.cid;
+-----+-------+------+-----------+--------+
| sid | name  | cid  | classname | grade  |
+-----+-------+------+-----------+--------+
|   1 | Cindy |    1 | class1    | grade1 |
|   2 | Alice |    2 | class2    | grade1 |
|   3 | Mike  | NULL | NULL      | NULL   |
+-----+-------+------+-----------+--------+

4、full join——MySQL没有这个关键字,可用union关键字，将left join和right join数据集合并实现