表关系与连表
表关系与连表
一：背景
前面大多数是单表里面的一顿操作，但是实际用的最大的还是连表查询。
那么多张表如何连呢？
首先表之间有关系，那么关系就是靠外键或者约定字段进行建立的。
1:连表查询之方向连
动作视为将两个表的记录，在同一行进行横向连接
还是纵向连接，将两个表的同数目字段，在纵向连接
将多张表连在一起-------a,b表
select * from a,b; # 无论连表查询怎么变，加其他任何的条件，都是在此基础上进行的，一定发生笛卡尔乘积。
会把两个表进行笛卡尔乘积,a表的每个记录,先和b表的所有记录挨个乘积,例如a表10条记录,b表10条记录
就会有100条记录的产生.a表在前,用a表记录去乘以b表记录,反之亦然.
下面两张表,一张员工表,一张部门表
select * from employee,department;
张三和每个部分进行了匹配,其他人也一样,图太长,老八的没有截取上
下面无论怎么连接都是在笛卡尔积结果的基础上进行了过滤,因此需要条件来进行过滤.
1.1内连接 inner join
特点：对应两表中任何一个，没有匹配上的表记录，用了null填充的，剔除掉。
select * from employee inner join department on employee.dep_id=department.id;
两张表匹配的项才会出现在最终的查询结果里,发现部门表里面的5没有,因为员工表里面没有5部门的员工.
1.2外连接
左外连接 left join，左表全部记录都显示，employee中假如有人没有部门，那么关联的右表用null填充。
select * from employee left join department on employee.dep_id=department.id;
左边表的所有记录都会显示
右外连接 right join，右表的全部记录全部显示
select * from employee right join department on employee.dep_id=department.id;
--使用别名,as可以省略
select * from employee e right join department d on e.dep_id=d.id;
右外连接,右表的全部显示,坐标匹配不上的用null填充，记住一个就行了
纵向连接
用的很少
select id,name from employeeunion select name,age from employee;
只要查询字段的数目一样就可以了，用的很少。
左右表都全量显示
总结:外连接记住一个就行了,表的位置换一下就行了,想全量显示那个表就放左边
1.2子查询
先根据条件找出部门表中经理的id,然后在员工表中选出dep_id=id的数据
select id from department where namel="经理";
select name from emp where id =4;
合并
select name from emp where id =(select id from department where namel="经理")
# 只要是有字段的地方都可以用子sql进行替换，只要数目一致即可。
子查询是先查一张表,根据查询结果,再去查另一张表
需求是查询经理的名字,
1不知道经理的id,先查出经理的id
2根据经理的id到员工表中查出dep_id = 经理的id的记录中的name叫什么
子查询就是单表查询,完全可以用连表查询,但是效率没有连表查询效率高,
连表查询是从一张大表里面查出想要的数据,子查询如果需求是两个部门,拿到两个部分的id,会分别的扫描全表拿到与部分id相同的记录,会多次扫描全表,先进行的子查询查出来多少个,后面的全表扫描就会进行多少次.
二：表关系
一对一
why?一对一放一个表中，多加一列不就完了么？
权限表设计-----单一权限
用户表一万用户，但只有2人有后台权限，需要密码登录才能改后台数据，其他用户没有密码，只能看数据。
难道加一列password列，只填写两个密码，其他全部用null或空字符串填充？那样就浪费了大量的空间,检索时如果是更庞大的数据，在空数据中浪费大量时间，这个时间是完全没有必要浪费的。
此时一个用户表，一个密码表，密码表中添加外键字段user_id关联到用户表的id，此时密码表只有2行，user_id被用户表的id约束。
可见外键并非只用在存在多的关系管理中。
多对多
权限表设计-----多权限
一个用户多个权限，一个权限被多用用户使用，明显的多对多。建立第三张中间表，存放用户和权限的对应关系。且中间表中，还要对user_id和author_id加联合唯一约束。
貌似是和以往的场景一样。但是如果来一个新人就会有权限，例如一个人5个权限，来一个人用户表插入一条，关系表插入五条数据，这还可以，如果修改呢？
一万个员工，每个员工多加一个权限，或者修改几个权限，你就要动用几万条数据的修改量，大动干戈的意思。
此时为员工表加user_type字段，关联到一个员工身份表，例如普通员工，经理，总监等很少的几个记录，撑死一百个身份。
再来一个中间表存放员工身份和权限的对应关系表，记录算下来最多也就五百条数据。
此时四张表，员工表（数据是最庞大的），员工身份表（被关联到员工表的user_type字段），权限表，员工身份-权限关系表（两个外键分别关联到员工身份表的id，权限表的id）
前两个一对多，后三个多对多
四个表完全关联了起来，且达到变动数据量很小。
来新员工，员工表插入一条即可，因为分配一个user_type字段，其他表不用动。
修改权限，那么改动的只是权限表，其他表不用动。
权限变更，所有员工的权限都修改，那么只需要修改员工身份-权限关系表，改动最多也是几百条，不需要像之前为所有的员工变动权限，那样太打动干戈。
基于用户的权限管理，变成基于角色的权限管理
宗旨：小表驱动大表。用的就是员工的身份不会频繁的发生变动，且改动的只是小表的数据。
一对多
why？
表中一个字段的data太多的重复，且数据量又很大，例如一个年级二十个班，却有1000个学生，那个班级里面的50条数据都是重复的，占用了大量的空间。
分拆出去，将他们放在一个单独的表中，在学生表中加class_id的外键，关联到班级表的id上。这样班级不用大量的占用表空间，可以加快检索的速度。
这是最常见的一对多了。
一对多变种，相信表
用户表中男女都有，相信记录表中，记录了相亲过的男女记录。
用户表中的数据之间是一男相亲多女，一女相亲多男，那么相亲记录表中的，user1_id和user2_id都关联到用户表的主键ID
表与自己的双向一对多，却是多对多的关系。
综述
表与表之间的关系没有说是确定的，而是看实际的业务来进行表的设计，业务的不同设计是完全不同的。
开发时数据库的设计，程序的设计会花费大量的时间，至于程序的开发占用的时间其实是很少的。
