mysql 表给字段建立外键索引 mysql表设置外键

知识要点：

1. 外键约束要求
2. 一对多表关系
3. 一对一表关系
4. 多对多表关系
5. 外键约束的参照操作

mysql 介绍

mysql是关系型数据库，关系数据库，是建立在关系模型基础上的数据库，

现实世界中的各种实体，以及实体之间的各种联系，均用关系模型（table）来表示。

#关系模型就是指二维表格模型,因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。

# 实体：就是数据对象 （可以是有型的，也可以是无型的）

 

 

E-R 图 （实体关系模型）

E-R图也称实体-联系图(Entity Relationship Diagram)， 提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。 它是描述现实世界关系概念模型的有效方法。 是表示概念关系模型的一种方式。 用“矩形框”表示实体型，矩形框内写明实体名称； 用“椭圆图框”表示实体的属性，并用“实心线段”将其与相应关系的“实体型”连接起来； 用”菱形框“表示实体型之间的联系成因，在菱形框内写明联系名， 并用”实心线段“分别与有关实体型连接起来，同时在”实心线段“旁标上联系的类型（1:1,1:n或m:n）。

 

外键约束FOREING KEY

外键约束FOREIGN KEY，保持数据一致性，完整性实现一对一或一对多关系。

（因为一个表只存一类信息。用外键来做参照，保证数据的一致性，可以减少数据冗余）

外键约束的要求：

数据表的存储引擎只能为InnoDB外键列和参照列数据类型一致外键必须关联到键上面去，一般情况是关联到，另一张表的主键

 

#例：

##表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;

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)

);

#删除外键

alter table `b` drop foreign key AB_id;

#增加外键

mysql> alter table `b`

-> add constraint AB_id foreign key(fy_id) references `a`(a_id);

#一个表只存一类信息。

一对多关系

举例，学校中一个学院可以有很多的学生，而一个学生只属于某一个学院（通常情况下），学院与学生之间的关系就是一对多的关系，通过外键关联来实现这种关系。

#例：

##创建学院表

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

 

一对一关系

举例，学生表中有学号、姓名、学院，但学生还有些比如电话，家庭住址等比较私密的信息，这些信息不会放在学生表当中，会新建一个学生的详细信息表来存放。这时的学生表和学生的详细信息表两者的关系就是一对一的关系，因为一个学生只有一条详细信息。用外键加主键的方式来实现这种关系。

#例：

#学生表：

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');

 

多对多关系

举例，学生要报名选修课，一个学生可以报名多门课程，一个课程有很多的学生报名，那么学生表和课程表两者就形成了多对多关系。对于多对多关系，需要创建第三张关系表，关系表中通过外键加主键的形式实现这种关系。

 

#例：

#建立课程表：

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,

-> `crs_id` INT,

# 防止同一个学生，多次选同一门课程

-> 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的数据

查询中一些较为常见的函数

#求最大年龄

mysql> SELECT MAX(`age`) FROM `student_details`;

#求最小年龄

mysql> SELECT MIN(`age`) FROM `student_details`;

#求和

mysql> SELECT SUM(`age`) FROM `student_details`;

#求平均数

mysql> SELECT AVG(`age`) FROM `student_details`;

#四舍五入

mysql> SELECT ROUND(AVG(`age`)) FROM `student_details`;

#统计

mysql> select count(s_id) from student;

#查看当前时间

mysql> SELECT NOW();

mysql> create table tb1(

-> id INT,

-> name VARCHAR(20),

-> dates DATETIME

  -> );

mysql> insert into tb1 value(1,'佳能',now());