sql多表查询及多表连接查询

多表关系有一对一、一对多、多对一、多对多

1.一对一的关系

一对一的关系实现 可以在任意一方添加外键 指向另一方的主键
并且让外键唯一

一对一关系举个例子一个学生有自己的学号，而这个学号只能属于这个学生。一般用在多对多关系时拆分出来的表，一般的一对一关系写在一张表里就可以，因为没必要写成两张表，在下面多对多关系里再介绍一下一对一的关联。

2.一对多(多对一)

实现关系 ：一对 多、多对一
在多的一方建立外键，通过外键进行关联 指向另一方的主键

例：学生和班级之间的关系，一个学生只能选一个班级而一个班级可被多个学生选。

在数据库中写一个学生表（id是主键自增、name是学生姓名、classid是建立关联的外键）和班级表（id是班级表主键也是通过学生表classid关联的外键、name是班级名）如下图:

 

 关联通过外键关联，如图：

        在学生表里点外键（因为班级可以被多个学生选，所以在学生这里引入外键classid，这样学生就可以选择一样的班级，而在班级里引入就只能关联一个学生或写很多条数据），字段选择classid,参考表就是要关联的表是班级表，关联学生表里的id，删除更新选RESTRICT ，如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 ，通俗讲就是有学生选择这个班级时这个班级是不允许更新和删除操作的。

表内信息如下图：

 

 

 

 

3.多对多

多对多
多对多关系的实现 需要借助第三张给中间表
中间表至少 包含两个字段，这两个字段作为第三张表的外键 分别指向两张表的主键

例：学生可以选多门课程，课程也可以被多个学生同时选，而这里就疑问了？两表之间多对一时可以在多的地方引入外键，而他们多对多在哪个表里引入，这里就用到了一对一关系

设计一个课程表course：

id是主键，name是课程名

我们在引入一个sc表，sid、cid都是外键不设置主键，他们分别关联学生表的id和课程表的id 

 

表内信息如图：

 

在表中的一些操作例子如下：

1、查询学生张三的班级

select name from class where id=(select classid from student where name="张三"); 

 

        如果想查询学生表里选择了的所有班级的名 ，用如下语句会报错

select name from class where id = (select classid from student);  

而用in的语法就可以查询出来，说明in可以查询多条数据而=不行 

select name from class where id in (select classid from student);

 

 ，mysql连接查询:

查询此条select class.name,student.name from class,student;列出来的数据并不是学生姓名对应的班级名，而是笛卡尔积个也就是所有可能的排列次数。所以我们需要用到连接查询

连接查询分为两种:

1.内连接（inner join）：

如上图 涂黑部分是内连接的部分(取中间相交的数据)：也就是学生和班级相对应的部分

格式：select 表名.数据名, 表名.数据名 from 表名 inner join 表名 on 表名.外键名=表名.外键名

 

2.外连接：

        外连接分为：

• 左外连接

取左边的数据包括中间重合部分：也就是如果某些学生没有选班级也把学生信息查询出来。 

格式：select 表名.数据名, 表名.数据名 from 表名 left join 表名 on 表名.外键名=表名.外键名 

• 右外连接

 

取右边边的数据包括中间重合部分：也就是如果某些班级没有被选也把这些班级查询出来。（）  

 格式：select 表名.数据名, 表名.数据名 from 表名 right join 表名 on 表名.外键名=表名.外键名

 三张表是同样的原理

1.查询所有的学生以及选择的班级 

•  内连接

select student.name,class.name from student inner join class on student.classid=class.id;

 

• 左外连接（如下左图）

select student.name,class.name from student left join class on student.classid=class.id;

• 右外连接 （如下右图）

select student.name,class.name from student right join class on student.classid=class.id;

 

查询所有学生选的课程 

 内连接：

select student.name,course.name from student inner join sc on student.classid=sc.sid inner join course on course.id=sc.cid;

 

 

• 左外连接（如下左图）

select student.name,course.name from student left join sc on student.classid=sc.sid left join course on course.id=sc.cid;

• 右外连接 （如下右图）

select student.name,course.name from student right join sc on student.classid=sc.sid right join course on course.id=sc.cid;