外键

目录

• 一、外键
  • • 一、一对多
    • 二、多对多
    • 三、一对一
• 二、修改表操作

一、外键

外键是用于建立两张表之间的关系

• 将所有数据存放在一张表中的弊端：
  • 结构不清晰
  • 浪费空间
  • 可扩展性极差

这个问题就类似于Python代码存放在一个py文件中，强耦合到一起，扩展性差

所以拆分表就可解决以上的弊端问题，但需要给两张表之间，建立一种强有力的关系，使用"外键"

外键：语法：

​ foreign key（当前表中建立关系的外键字段） references 被关联表名（id）

• 如何确认表与表之间的关系（一对多、多对多、一对一）
• 注意：要确立两张表之间的关系，必须站在两个位置去思考

---

一、一对多

比如员工与部门

• 站在员工表的位置：多个员工是否可以对应一个部门
  • 可以
• 站在部门表的位置：多个部门是否可以对应一个员工
  • 不可以
• 说明员工表单向 "多对一" 部门表
• 总结：凡是单向 "多对一" 的表关系，称之为 "一对多" 的外键关系

创建两张表

注意：必须先建立被关联表，再建立关联表

# 被关联表：部门表（id，部门名称，部门描述）
mysql> create table dep(id int primary key auto_increment, dep_name varchar(16), dep_desc
varchar(255));
Query OK, 0 rows affected (0.34 sec)

mysql> desc dep;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| dep_name | varchar(16)  | YES  |     | NULL    |                |
| dep_desc | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

# 关联表：员工表（id，姓名，年龄，性别，部门id）
mysql> create table emp(id int primary key auto_increment, name varchar(16), age int, gend
er enum('male', 'female') default 'male', dep_id int not null, foreign key(dep_id) referen
ces dep(id));
Query OK, 0 rows affected (0.55 sec)

mysql> desc emp;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | varchar(16)           | YES  |     | NULL    |                |
| age    | int(11)               | YES  |     | NULL    |                |
| gender | enum('male','female') | YES  |     | male    |                |
| dep_id | int(11)               | NO   | MUL | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

插入数据

注意：必须先插入被关联表（dep）的数据，再插入关联表（emp）的数据

mysql> insert into dep(dep_name, dep_desc) values('外交部', '国际形象大使'),('教学部','教
育部门'),('技术部','提供一切技术支持');
Query OK, 3 rows affected (0.35 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from dep;
+----+-----------+--------------------------+
| id | dep_name  | dep_desc                 |
+----+-----------+--------------------------+
|  1 | 外交部    | 国际形象大使             |
|  2 | 教学部    | 教育部门                 |
|  3 | 技术部    | 提供一切技术支持         |
+----+-----------+--------------------------+
3 rows in set (0.00 sec)

mysql> insert into emp(name, age, gender, dep_id) values('yang', 18, 'male', 1),('tank', 1
9, 'male',2),('sean', 20, 'female', 2),('jason', 21, 'female', 2),('egon',22,'male',3);
Query OK, 5 rows affected (0.38 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+----+-------+------+--------+--------+
| id | name  | age  | gender | dep_id |
+----+-------+------+--------+--------+
|  1 | yang  |   18 | male   |      1 |
|  2 | tank  |   19 | male   |      2 |
|  3 | sean  |   20 | female |      2 |
|  4 | jason |   21 | female |      2 |
|  5 | egon  |   22 | male   |      3 |
+----+-------+------+--------+--------+
5 rows in set (0.00 sec)

关联成功后，更新或删除数据，就比较麻烦，因为关联关系，所以会报错，只能先删除已关联的dep_id字段，才能修改dep表中的关联id字段

所以有了级联更新与级联删除

• on update cascade
• on delete cascade

---

二、多对多

这里也必须站在两张表的位置去思考

比如，图书与作者的关系表

- 错误示范:
		- 创建book表
		    create table book(
		        id int primary key auto_increment,
		        title varchar(20),
		        price int,
		        book_content varchar(255),
		        author_id int,
		        foreign key(author_id) references author(id)
		        on update cascade
		        on delete cascade
		
		- 创建author表
		    create table author(
		        id int primary key auto_increment,
		        name varchar(16),
		        age int,
		        book_id int,
		        foreign key(book_id) references book(id)
		        on update cascade
		        on delete cascade
		    );

问题在于：根本不知道哪张表是被关联表

利用第三张表，为两张表建立"多对多的外键关系"

创建图书表

# 图书表（id，标题，价格，图书简介）
mysql> create table book(id int primary key auto_increment, title varchar(20), price int,book_content varchar(255));
Query OK, 0 rows affected (0.52 sec)

创建作者表

# 作者表（id，姓名，年龄）
mysql> create table author(id int primary key auto_increment, namevarchar(16), age int);
Query OK, 0 rows affected (0.65 sec)

创建第三张表

# 第三张表（id，图书id，作者id，外键关联，练级更新、级联删除）
mysql> create table book_to_author(id int primary key auto_increment, book_id int, author_id int, foreign key(book_id) references book(id) on update cascade on delete cascade, foreign key(author_id) references author(id) on update cascade on delete cascade);
Query OK, 0 rows affected (0.59 sec)

插入数据

图书

mysql> insert into book(title, price, book_content) values('金ping梅', 199, '古代长篇世情小说'),('python从入门到断气', 299, '学习如何一夜变秃头'),('三体', 399, '跟着刘慈欣进入宇宙奇幻世界');
Query OK, 3 rows affected (0.36 sec)
Records: 3  Duplicates: 0  Warnings: 0

作者

mysql> insert into author(name, age) values('兰亭笑笑生', 38),('刘慈欣', 48);
Query OK, 2 rows affected (0.35 sec)
Records: 2  Duplicates: 0  Warnings: 0

第三张表

mysql> insert into book_to_author(book_id, author_id) values(1, 1),(1, 2),(2, 2),(3, 1);
Query OK, 4 rows affected (0.35 sec)
Records: 4  Duplicates: 0  Warnings: 0

example：

mysql> insert into book2author(book_id, author_id) values (4, 4); ERROR 1146 (42S02): Table 'db1.book2author' doesn't exist

报错，插入的数据，book_id, author_id必须存在

更新

mysql> update book set price=599 where id=1;
Query OK, 1 row affected (0.34 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update book set id=4 where id=1;
Query OK, 1 row affected (0.37 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from book where id=1;
+----+-----------+-------+--------------------------+
| id | title     | price | book_content             |
+----+-----------+-------+--------------------------+
|  4 | 金ping梅    |   599 | 古代长篇世情小说         |
+----+-----------+-------+--------------------------+
1 row in set (0.00 sec)

删除

mysql> delete from book where id=4;
Query OK, 1 row affected (0.12 sec)

mysql> select * from book where id=4;
Empty set (0.00 sec)

---

三、一对一

两张表之间的关系，一一对应，将一张数据量比较大的表，拆分成两张表

例如：

- user_info:
		id, name, age, gender, hobby, id_card
- user:
		id, name, age, detail_id（外键）
- detail:
		id, gender, hobby, id_card

user与detail表建立了一对一的外键关系，foreign key 应该建在 使用频率比较高的一方

创建被关联表

mysql> create table customer(id int primary key auto_increment, name varchar(16), media varchar(32));
Query OK, 0 rows affected (0.56 sec)

创建关联表

mysql> create table student(id int primary key auto_increment, addr varchar(255), phone char(11), id_card char(18), customer_id int unique, foreign key(customer_id) references customer(id) on update cascade on delete cascade);
Query OK, 0 rows affected (0.56 sec)

插入数据

mysql> insert into customer(name, media) values('张三','facebook'),('李四','QQ'),('王麻子','微信');
Query OK, 3 rows affected (0.35 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into student(addr, phone,id_card,customer_id) values('上海','13913513135','8888888888888888888',1),('北京', '13843813138', '777777777777777777', 2);
Query OK, 2 rows affected, 1 warning (0.38 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from student;
+----+--------+-------------+--------------------+-------------+
| id | addr   | phone       | id_card            | customer_id |
+----+--------+-------------+--------------------+-------------+
|  1 | 上海   | 13913513135 | 888888888888888888 |           1 |
|  2 | 北京   | 13843813138 | 777777777777777777 |           2 |
+----+--------+-------------+--------------------+-------------+
2 rows in set (0.00 sec)

---

二、修改表操作

• 修改表的操作

  • 语法：注意：mysql关键字不区分大小写

  • 修改表名

    • alter table 表名 rename 新表名

    mysql> alter table author rename zuozhe;
    Query OK, 0 rows affected (0.43 sec)
    
    mysql> show tables;
    +----------------+
    | Tables_in_db1  |
    +----------------+
    | zuozhe         |
    +----------------+
    7 rows in set (0.00 sec)
    

  • 增加字段

    • alter table 表名 add 字段名 数据类型[完整性约束条件]; 默认添加到最后一列

    mysql> alter table zuozhe add hobby varchar(255);
    Query OK, 0 rows affected (0.74 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

    • alter table 表名 add 字段名 数据类型[完整性约束条件] first; 添加到第一列
    • alter table 表名 add 字段名 数据类型[完整性约束条件] after; 添加到某一列之后

  • 删除字段

    • alter table 表名 drop 字段名;

    mysql> alter table zuozhe drop hobby;
    Query OK, 0 rows affected (0.44 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

  • 修改字段

    • alter table 表名 modify 字段名 数据类型[完整性约束条件];修改数据类型

    mysql> alter table zuozhe modify name char(10);
    Query OK, 2 rows affected (0.96 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    

    • alter table 表名 change 旧字段名 新字段名 旧数据类型[完整性约束条件];修改字段名，保留字段类型

    mysql> alter table zuozhe change name username char(10);
    Query OK, 0 rows affected (0.41 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

    • alter table 表名 change 旧字段名 新字段名 新数据类型[完整性约束条件];修改字段名与字段类型

    mysql> alter table zuozhe change username name varchar(16);
    Query OK, 2 rows affected (0.96 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    

• 复制表的操作

  • 复制表结构+记录（key不会复制：主键、外键和索引）

    • create table new_zuozhe select * from zuozhe;

    mysql> desc zuozhe;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(16) | YES  |     | NULL    |                |
    | age   | int(11)     | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    3 rows in set (0.01 sec)
    
    mysql> create table new_zuozhe select * from zuozhe;
    Query OK, 2 rows affected (0.45 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> desc new_zuozhe;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | NO   |     | 0       |       |
    | name  | varchar(16) | YES  |     | NULL    |       |
    | age   | int(11)     | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    

  • 只复制表结构

    • create table new_zuozhe1 select * from zuozhe where 1=2;

    mysql> select * from zuozhe;
    +----+-----------------+------+
    | id | name            | age  |
    +----+-----------------+------+
    |  1 | 兰亭笑笑生      |   38 |
    |  2 | 刘慈欣          |   48 |
    +----+-----------------+------+
    2 rows in set (0.00 sec)
    
    mysql> create table new_zuozhe1 select * from zuozhe where 1=2;
    Query OK, 0 rows affected (0.28 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> select * from new_zuozhe1;
    Empty set (0.00 sec)