现实生活中,实体与实体之间肯定是有关系的,比如:老公和老婆,部门和员工,用户和订单、订单和商品、学生和课程等等。那么我们在设计表的时候,就应该体现出表与表之间的这种关系!表和表之间的关系分成三种:
例如:
双11当天,马哥和东哥两个用户分别在淘宝上下了一些订单,已知马哥下了1个订单,订单总金额为999元. 东哥下了2个订单,订单金额分别为1314元和10元.
思考: 数据库该如何存放这些数据呢?
没有建立关系前: 通过表数据不能得知数据间的联系,这样存放数据是没有意义的
image-20200529100830282
建立关系后:
通过对该业务的分析,可得知一个用户可以有多个订单,一个订单只属于一个用户.
我们管1的一方,叫主表或1表. 我们管多个一方,叫从表或多表.
通常要在多的一方添加一个字段,用于存放主表主键的值,我们管这个字段叫外键字段.
外键字段的值必须为主表主键的值,若为其他值,则没有意义.
image-20200529101003797
用于限制外键字段取值必须为主表主键的值的约束叫做-外键约束.
一对多(1:n) 例如:班级和学生,部门和员工,客户和订单,分类和商品 一对多建表原则: 在从表(多方)创建一个字段,指向主表(一方)的主键.我们把这个字段称之为外键.
多对多(m:n) 例如:老师和学生,学生和课程,用户和角色 多对多关系建表原则: 需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键。
一对一(1:1) 在实际的开发中应用不多.因为一对一可以创建成一张表。两种建表原则:
UNIQUE
一张表中的某个字段引用另一个表的主键 主表:约束别人 副表/从表:使用别人的数据,被别人约束
具体操作:
-- 先创建部门表
CREATE TABLE department (
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');
-- 然后创建员工表,添加外键约束
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT,
-- 添加一个外键
-- 外键取名公司要求,一般fk结尾
CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
);
执行如下:
-- 先创建部门表
mysql> CREATE TABLE department (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> dep_name VARCHAR(20),
-> dep_location VARCHAR(20)
-> );
Query OK, 0 rows affected (0.02 sec)
-- 添加2个部门
mysql> INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
-- 查看部门的数据
mysql> select * from department;
+----+-----------+--------------+
| id | dep_name | dep_location |
+----+-----------+--------------+
| 1 | 研发部 | 广州 |
| 2 | 销售部 | 深圳 |
+----+-----------+--------------+
2 rows in set (0.00 sec)
-- 然后创建员工表,添加外键约束
mysql> CREATE TABLE employee (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> NAME VARCHAR(20),
-> age INT,
-> dep_id INT,
-> -- 添加一个外键
-> -- 外键取名公司要求,一般fk结尾
-> CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>
INSERT INTO employee (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);
执行如下:
-- 插入数据到 employee
mysql> INSERT INTO employee (NAME, age, dep_id) VALUES
-> ('张三', 20, 1),
-> ('李四', 21, 1),
-> ('王五', 20, 1),
-> ('老王', 20, 2),
-> ('大王', 22, 2),
-> ('小王', 18, 2);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
-- 查看 employee 表数据
mysql> select * from employee;
+----+--------+------+--------+
| id | NAME | age | dep_id |
+----+--------+------+--------+
| 1 | 张三 | 20 | 1 |
| 2 | 李四 | 21 | 1 |
| 3 | 王五 | 20 | 1 |
| 4 | 老王 | 20 | 2 |
| 5 | 大王 | 22 | 2 |
| 6 | 小王 | 18 | 2 |
+----+--------+------+--------+
6 rows in set (0.00 sec)
mysql>
INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);
执行如下:
mysql> select * from department;
+----+-----------+--------------+
| id | dep_name | dep_location |
+----+-----------+--------------+
| 1 | 研发部 | 广州 |
| 2 | 销售部 | 深圳 |
+----+-----------+--------------+
2 rows in set (0.00 sec)
-- 可以发现插入的数据外键 dep_id=5, 而 department 表并没有 dep_id=5 的数据。
mysql> INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`employee`, CONSTRAINT `emp_depid_ref_dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))
mysql>
ALTER TABLE 从表 drop foreign key 外键名称;
具体操作:
ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;
ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);
执行如下:
-- 查看当前 employee 的表结构
mysql> show create table employee;
+----------+-----------------------+
| Table | Create Table |
+----------+-----------------------+
| employee | CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `emp_depid_ref_dep_id_fk` (`dep_id`),
CONSTRAINT `emp_depid_ref_dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`) -- 外键
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+----------+-----------------------+
1 row in set (0.00 sec)
-- 删除外键 emp_depid_ref_dep_id_fk
mysql> ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看删除外键后的 employee
mysql> show create table employee;
+----------+------------------------+
| Table | Create Table |
+----------+------------------------+
| employee | CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `emp_depid_ref_dep_id_fk` (`dep_id`) -- 外键已经被删除了
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+----------+-------------------------+
1 row in set (0.00 sec)
-- 在employee表存在况下添加外键
mysql> ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);
Query OK, 6 rows affected (0.34 sec)
Records: 6 Duplicates: 0 Warnings: 0
-- 查看当前employee结构
mysql> show create table employee;
+----------+----------------------+
| Table | Create Table |
+----------+----------------------+
| employee | CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `emp_depid_ref_dep_id_fk` (`dep_id`),
CONSTRAINT `emp_depid_ref_dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`) -- 重新添加的外键
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+----------+-----------------------+
1 row in set (0.00 sec)
mysql>
添加数据时:
先添加主表中的数据,再添加从表中的数据
删除数据时:
先删从表中的数据,再删主表中的数据
修改数据时:
如果主表中的主键被从表引用了,不能修改此主键的值