mysql是关系型数据库，关系数据库，是建立在关系模型基础上的数据库，
现实世界中的各种实体，以及实体之间的各种联系，均用关系模型（table）来表示。
- 关系模型就是指二维表格模型,因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。
- 实体：就是数据对象 （可以是有型的，也可以是无型的）；
E-R 图 （实体关系模型）
image.png
E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。它是描述现实世界关系概念模型的有效方法。是表示概念关系模型的一种方式。
- 用“矩形框”表示实体型，矩形框内写明实体名称；
- 用“椭圆图框”表示实体的属性，并用“实心线段”将其与相应关系的“实体型”连接起来；
- 用”菱形框“表示实体型之间的联系成因，在菱形框内写明联系名，并用”实心线段“分别与有关实体型连接起来，同时在”实心线段“旁标上联系的类型（1:1,1:n或m:n）。
外键约束FOREING KEY
外键约束FOREIGN KEY，保持数据一致性，完整性实现一对一或一对多关系。
（因为一个表只存一类信息。用外键来做参照，保证数据的一致性，可以减少数据冗余）
外键约束的要求：
数据表的存储引擎只能为InnoDB #此点已无需关注。
外键列和参照列数据类型一致
外键必须关联到键上面去，一般情况是关联到，另一张表的主键；
--用desc a;查看字段key是否有值，必须关联到key有值的字段；
#例：
##表a
mysql> create table `a`(
-> a_id int primary key,
-> a_name varchar(20) not null
-> );
insert into a values(1,'a1'),(2,'a2');
alter table a modify a_id int auto_increment;
show create table a; #因为前面已经插入2个值，这里修改为自增长，则从3开始；
alter table a auto_increment=6; # 只能调大，不可以调小，一个表里面只有一个auto_increment,一般和主键一起用。
insert into a(a_name) value('a6');
##表b
create table `b`(
b_id int primary key,
b_name varchar(20) not null,
fy_id int not null,
constraint AB_id foreign key(fy_id) references `a`(a_id) #fy_id只能取a表a_id字段已有的值，不可以取其他的；constraint可写可不写，用来取外键名的，不写则默认取的。删除是通过外键名来删除；
);
#删除外键
alter table `b` drop foreign key AB_id;
#增加外键
mysql> alter table `b`
-> add constraint AB_id foreign key(fy_id) references `a`(a_id);
#一个表只存一类信息。
注：
首先，外键引用的那个列在主表中必须是主键列或者唯一列。
所以1：n的肯定把外键建立在n的那张表上。
1：1，一般要看谁是主表，谁是附属表，外键当然建立在附属表中。
n：m的情况，需要建立一个关系表，两个原表和其关系分别是1：n，1：m
一对多关系（一对多的，一个表的记录与另一个表的多条记录连接。）
举例，学校中一个学院可以有很多的学生，而一个学生只属于某一个学院（通常情况下），学院与学生之间的关系就是一对多的关系，通过外键关联来实现这种关系。（1：n的在多的一方n建立外键，指向1的主键）
#例：
##创建学院表
mysql> create table department(
-> d_id int primary key AUTO_INCREMENT,
-> d_name varchar(20) not null
-> );
##创建学生表
mysql> create table student(
-> s_id int primary key AUTO_INCREMENT,
-> s_name varchar(20) not null,
-> dept_id int not null, # 一个不同列， 关联到另一种表的主键
-> constraint SD_id foreign key(dept_id) references department(d_id)
-> );
#插入数据
mysql> INSERT INTO `department`(`d_name`)
-> VALUES('计算机学院'),
-> ('外语学院')
-> ;
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `student`(`s_name`,`dept_id`)
-> VALUES('s1',1),
-> ('s2',2),
-> ('s3',2)
-> ;
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
一对一关系（一个表的记录只能与另一个表的一条记录连接）
举例，学生表中有学号、姓名、学院，但学生还有些比如电话，家庭住址等比较私密的信息，这些信息不会放在学生表当中，会新建一个学生的详细信息表来存放。这时的学生表和学生的详细信息表两者的关系就是一对一的关系，因为一个学生只有一条详细信息。用外键加主键的方式来实现这种关系。(1：1，一般要看谁是主表，谁是附属表，外键当然建立在附属表中。在附属表增加一个主键关联从表，增加一个主键关联主表主键) 。
#例：
#学生表：
mysql> DESC `student`;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| dept_id | int(11) | YES | MUL | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.06 sec)
#建立详细学生表：
mysql> CREATE TABLE `student_details`(
-> `id` INT PRIMARY KEY,
-> `sex` VARCHAR(10) not null,
-> `age` INT,
-> `address` VARCHAR(20) comment '家庭住址',
-> `parents` VARCHAR(20),
-> `home_num` VARCHAR(20),
-> FOREIGN KEY (`id`) REFERENCES `student`(`s_id`)
-> ); # 主键映射 主键
Query OK, 0 rows affected (0.67 sec)
insert into student_details value(2,'男',18,'湖南长沙','佳爸爸','123456789');
insert into student_details value(4,'男',20,'湖南长沙','李爸爸','12541656469');
insert into student_details value(5,'男',20,'湖南长沙','李爸爸','12541656469');
多对多关系
（多对多的关系，是通过中间表。中间表是多的一方，所以多的一方要包含指向一的一方的外键。所以中间表，作为两边多的一方，中间表要包含两边的外键，两个外键分别指向两张表的主键。）
举例，学生要报名选修课，一个学生可以报名多门课程，一个课程有很多的学生报名，那么学生表和课程表两者就形成了多对多关系。对于多对多关系，需要创建第三张关系表，关系表中通过外键加主键的形式实现这种关系。（n：m的情况，需要建立一个关系表，两个原表和其关系分别是1：n，1：m）
#例：
#建立课程表：
mysql> CREATE TABLE `course`(
-> `id` INT PRIMARY KEY AUTO_INCREMENT,
-> `name` VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (1.18 sec)
insert into course(name) values('python'),('java'),('英语'),('日语');
#学生与课程多对多关系表 (中间表)
mysql> CREATE TABLE `select`(
-> `s_id` INT, #用来对应学生id
-> `crs_id` INT, #用来对应课程id
# 防止同一个学生，多次选同一门课程
-> PRIMARY KEY (`s_id`,`crs_id`),
# 必须学生表的学生，才能选课程表中的课程
-> FOREIGN KEY (`s_id`) REFERENCES `student` (`s_id`),
# 只有课程存在，才能选
-> FOREIGN KEY (`crs_id`) REFERENCES `course` (`id`)
-> );
Query OK, 0 rows affected (0.50 sec)
insert into `select` values(1,1),(1,3),(4,4);
insert into `select` values(2,3),(2,1),(2,4);
外键约束的参照操作：
mysql> SELECT * FROM `department`;
+----+----------------------+------+
| d_id | name | code |
+-----+----------------------+------+
| 1 | 计算机学院 | 1 |
| 2 | 外国语学院 | 2 |
+-----+----------------------+------+
mysql> SELECT * FROM `student`;
+----+--------+--------+
| id | name | dep_id |
+----+--------+--------+
| 1 | budong | 1 |
| 2 | Tuple | 1 |
| 3 | Which | 2 |
+----+--------+--------+
mysql> DELETE FROM `department` WHERE `d_id`=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`student`, CONSTRAINT `stu_dep_for_key` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))
#因为外键关联的原因，不能删除父表中的记录
mysql> UPDATE `student` SET `dept_id`=1 WHERE `dept_id`=2;
mysql> DELETE FROM `department` WHERE `d_id`=2;
#因为student表中的dep_id没有值关联department中的id=2的数据，所以可以删除department表中id=2的数据
网友评论