MySql:多表设计---外键

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/javy_codercoder/article/details/49151951
在实际开发过程中,一张表显然是不能满足我们的需求,通常我们需要多张表来存储数据。那么下面就举个栗子~
一家公司有多个部门,而每个部门有多个员工,现在我们来新建一个部门表:dept   以及一个员工表:emp。
建表的操作这里不再演示,如有需要请参考:
《MySql:操作表的语句以及常用的字段类型》http://blog.csdn.net/javy_codercoder/article/details/49099271
以下为dept表的结构:
+-------+-------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra                  |
+-------+-------------+------+-----+---------+----------------+
| id      | int(11)         | NO   | PRI  | NULL    | auto_increment |
| name| varchar(20) | NO   |        | NULL     |                            |
+-------+-------------+------+-----+---------+----------------+
以下为emp表的结构:
+--------+-------------+------+-----+---------+----------------+
| Field  | Type           | Null | Key | Default | Extra                   |
+--------+-------------+------+-----+---------+----------------+
| id      | int(11)         | NO   | PRI | NULL    | auto_increment |
| dept  | varchar(20) | YES  |       | NULL    |                             |
| name | varchar(20) | YES  |      | NULL    |                             |
| salary | double       | YES  |       | NULL    |                             |
+--------+-------------+------+-----+---------+----------------+
接下来就插入数据,插入数据也不再多说,有需要的可以看:
《MySql:增删改查(CRUD)以及乱码编码解决(1)》http://blog.csdn.net/javy_codercoder/article/details/49121459
以下为dept表的数据:
+----+--------+
| id | name   |
+----+--------+
|  1 | 人事部 |
|  2 | 后勤部 |
|  3 | 财务部 |
|  4 | 行政部 |
+----+--------+
假设有两位员工分别叫做:小明和小芳分别属于人事部 和后勤部。
有一天老板发现后勤部没什么用然后就把后勤部砍掉了,对于数据库来说就是dept表把id为2的这一行数据删掉。
到了发工资的日期,小芳来到财务部领工资,那么财务部的妹子在电脑上发现没有后勤部啊。这个时候小芳就蒙蔽了~~我人还在部门就没了~~~
显然这种多表设计是可行的,但是是不完善的,不严谨的。
为了能够无时无刻维持着两个表的关系,我们需要引入一种叫做外键约束的技术。
我们在创建表时候可以声明表和表之间的关系,命令数据库帮我们维持这种关系。
如果有外键约束的话就不会发生以上案例的事情。在表dept删除id为2的数据时候会发生错误,因为在emp表还有一个小芳对应着dept表中id为2的数据。
显然上面的emp表是要重新建表了,因为在开始建表的时候没有加入外键约束,
那么我们来看看emp表的正确建表语句是怎样的:
create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int,
salary double,
foreign key(dept_id ) references dept (id)
);
新建的emp表:
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | YES  |     | NULL    |                |
| dept_id | int(11)     | YES  | MUL | NULL    |                |
| salary  | double      | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
接下来我们插入两条数据:
insert into emp values(null,'曹操',1,2000);
insert into emp values(null,'荀彧',2,2000);
现在表里面的数据是这样的:
+----+------+---------+--------+
| id | name | dept_id | salary |
+----+------+---------+--------+
|  1 | 曹操 |       1 |   2000 |
|  2 | 荀彧 |       2 |   2000 |
+----+------+---------+--------+
我们依旧要删除掉二号部门:
delete from dept where id=2;
然后就会发现抛出一下错误:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`mydb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENC
ES `dept` (`id`))

显然不然我们删除。这就是外键约束,能够避免人还在部门就没了的事情,那么外键约束能否避免无中生有的事情发生呢?
也就是我们尝试插入一个隶属于5号部门的员工到emp表中,注意这个时候dept表只有四个部门而已。
我们试试:
insert into emp values(null,'郭嘉',5,2000);
会发现也会抛出错误:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`mydb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `
dept` (`id`))

要删除2号部门必须要确保emp表没有属于2号部门的员工,要插入属于5号部门的员工必须确保dept表有5号部门。

多表设计:
要懂得多表设计就要先明白什么是多对一,多对多,一对一这三种关系。而且要清楚什么情况是多对一,多对多,一对一。
这里就不再叙述,可以百度一下~
多对一:
在多对一的情况下则在“多”的一方那个表参照”一“的那个表,也就是说在”多“的那个表设计一个外键关联”一“的那个表。
一对一:
而一对一的这种情况,是随意的~那个表参照哪个表都是没有所谓的。
多对多:
多对多这种情况就比较复杂,显然无论哪一方保存另一方的id都不合适,一个比较经典的案例就是老师和学生之间的关系,要保存哪个老师教过哪些学生怎么保存呢?一个学生可能被多个老师教过,而一个老师也不可能只教一个学生,那么对于这种情况怎么解决呢?
这个时候就需要新建多一张表出来,用于分别保存学生和老师的id作为外键:

id    stu_id   teach_id
1       1             6 
2       2             3
3       3             2
4       3             1
5       3             3
6       6             1

如上表所示,如果需要查询3号学生被哪些老师教过~那么就查stu_id=3的~就能查出1,2,3这三位老师都教过3号学生
如果想查1号老师教过哪些学生那么只需查teach_id=1的数据就能查出3,6学生被1号老师教过~




展开阅读全文

没有更多推荐了,返回首页