mysql foreign key_MySQL 之 foreign key

前段回顾

create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件]

);

#解释：

类型：使用限制字段必须以什么样的数据类型传值

约束条件：约束条件是在类型之外添加一种额外的限制

# 注意：

1. 在同一张表中，字段名是不能相同

2. 宽度和约束条件可选，字段名和类型是必须的

3、最后一个字段后不加逗号

把所有数据都存放于一张表的弊端

1、表的组织结构复杂不清晰

2、浪费空间

3、扩展性极差

补充知识：

如果要清空表，使用truncate tb1；

作用：将整张表重置(包括id)

一、foreign key (重点)

表关系之多对一

1、寻找表与表之间的关系的套路

举例：emp表 dep表

步骤一：

part1：

1、先站在左表emp的角度

2、去找左表emp的多条记录能否对应右表dep的一条记录

3、翻译2的意义：

左表emp的多条记录==》多个员工

右表dep的一条记录==》一个部门

最终翻译结果：多个员工是否可以属于一个部门？

如果是则需要进行part2的流程

part2：

1、站在右表dep的角度

2、去找右表dep的多条记录能否对应左表emp的一条记录

3、翻译2的意义：

右表dep的多条记录==》多个部门

左表emp的一条记录==》一个员工

最终翻译结果：多个部门是否可以包含同一个员工

如果不可以，则可以确定emp与dep的关系只一个单向的多对一

如何实现？

在emp表中新增一个dep_id字段，该字段指向dep表的id字段

# foreign key会带来什么样的效果？

#1、约束1：在创建表时，先建被关联的表dep，才能建关联表emp

create table dep(

id int primary key auto_increment,

dep_name char(10),

dep_comment char(60)

);

create table emp(

id int primary key auto_increment,

name char(16),

gender enum('male','female') not null default 'male',

dep_id int,

foreign key(dep_id) references dep(id)

);

#2、约束2：在插入记录时，必须先插被关联的表dep，才能插关联表emp

insert into dep(dep_name,dep_comment) values

('sb教学部','sb辅导学生学习，教授python课程'),

('外交部','老男孩上海校区驻张江形象大使'),

('nb技术部','nb技术能力有限部门');

insert into emp(name,gender,dep_id) values

('alex','male',1),

('egon','male',2),

('lxx','male',1),

('wxx','male',1),

('wenzhou','female',3);

#3、约束3：更新与删除都需要考虑到关联与被关联的关系

解决方案：

1、先删除关联表emp，再删除被关联表dep，准备重建

mysql> drop table emp;

Query OK, 0 rows affected (0.11 sec)

mysql> drop table dep;

Query OK, 0 rows affected (0.04 sec)

2、重建：新增功能，同步更新，同步删除

create table dep(

id int primary key auto_increment,

dep_name char(10),

dep_comment char(60)

);

create table emp(

id int primary key auto_increment,

name char(16),

gender enum('male','female') not null default 'male',

dep_id int,

foreign key(dep_id) references dep(id)

on update cascade

on delete cascade

);

insert into dep(dep_name,dep_comment) values

('sb教学部','sb辅导学生学习，教授python课程'),

('外交部','老男孩上海校区驻张江形象大使'),

('nb技术部','nb技术能力有限部门');

insert into emp(name,gender,dep_id) values

('alex','male',1),

('egon','male',2),

('lxx','male',1),

('wxx','male',1),

('wenzhou','female',3);

# 同步删除

mysql> select * from dep;

+----+------------------+------------------------------------------------------------------------------------------+

| id | dep_name | dep_comment |

+----+------------------+------------------------------------------------------------------------------------------+

| 1 | sb教学部 | sb辅导学生学习，教授python课程 |

| 2 | 外交部 | 老男孩上海校区驻张江形象大使 |

| 3 | nb技术部 | nb技术能力有限部门 |

+----+------------------+------------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)

mysql> select * from emp;

+----+------------------+--------+--------+

| id | name | gender | dep_id |

+----+------------------+--------+--------+

| 1 | alex | male | 1 |

| 2 | egon | male | 2 |

| 3 | lxx | male | 1 |

| 4 | wxx | male | 1 |

| 5 | wenzhou | female | 3 |

+----+------------------+--------+--------+

5 rows in set (0.00 sec)

mysql> delete from dep where id=1;

Query OK, 1 row affected (0.02 sec)

mysql> select * from dep;

+----+------------------+------------------------------------------------------------------------------------------+

| id | dep_name | dep_comment |

+----+------------------+------------------------------------------------------------------------------------------+

| 2 | 外交部 | 老男孩上海校区驻张江形象大使 |

| 3 | nb技术部 | nb技术能力有限部门 |

+----+------------------+------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> select * from emp;

+----+------------------+--------+--------+

| id | name | gender | dep_id |

+----+------------------+--------+--------+

| 2 | egon | male | 2 |

| 5 | wenzhou | female | 3 |

+----+------------------+--------+--------+

2 rows in set (0.00 sec)

#同步更新

mysql> select * from emp;

+----+------------------+--------+--------+

| id | name | gender | dep_id |

+----+------------------+--------+--------+

| 2 | egon | male | 2 |

| 5 | wenzhou | female | 3 |

+----+------------------+--------+--------+

2 rows in set (0.00 sec)

mysql> update dep set id=200 where id =2;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from dep;

+-----+------------------+------------------------------------------------------------------------------------------+

| id | dep_name | dep_comment |

+-----+------------------+------------------------------------------------------------------------------------------+

| 3 | nb技术部 | nb技术能力有限部门 |

| 200 | 外交部 | 老男孩上海校区驻张江形象大使 |

+-----+------------------+------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> select * from emp;

+----+------------------+--------+--------+

| id | name | gender | dep_id |

+----+------------------+--------+--------+

| 2 | egon | male | 200 |

| 5 | wenzhou | female | 3 |

+----+------------------+--------+--------+

2 rows in set (0.00 sec)

表关系之一对多

1、什么是多对多

两张表之间是一个双向的多对一关系，称之为多对多

如何实现？

建立第三张表，该表中有一个字段fk左表的id，还有一个字段是fk右表的id

create table author(

id int primary key auto_increment,

name char(16)

);

create table book(

id int primary key auto_increment,

bname char(16),

price int

);

insert into author(name) values

('egon'),

('alex'),

('wxx')

;

insert into book(bname,price) values

('python从入门到入土',200),

('葵花宝典切割到精通',800),

('九阴真经',500),

('九阳神功',100)

;

create table author2book(

id int primary key auto_increment,

author_id int,

book_id int,

foreign key(author_id) references author(id)

on update cascade

on delete cascade,

foreign key(book_id) references book(id)

on update cascade

on delete cascade

);

insert into author2book(author_id,book_id) values

(1,3),

(1,4),

(2,2),

(2,4),

(3,1),

(3,2),

(3,3),

(3,4);

表关系之一对一

左表的一条记录唯一对应右表的一条记录，反之也一样

本质就是在对应的存储id的字段加unique 来保证唯一存在

create table customer(

id int primary key auto_increment,

name char(20) not null,

qq char(10) not null,

phone char(16) not null

);

create table student(

id int primary key auto_increment,

class_name char(20) not null,

customer_id int unique, #该字段一定要是唯一的

foreign key(customer_id) references customer(id) #外键的字段一定要保证unique

on delete cascade

on update cascade

);

insert into customer(name,qq,phone) values

('李飞机','31811231',13811341220),

('王大炮','123123123',15213146809),

('守榴弹','283818181',1867141331),

('吴坦克','283818181',1851143312),

('赢火箭','888818181',1861243314),

('战地雷','112312312',18811431230)

;

#增加学生

insert into student(class_name,customer_id) values

('脱产3班',3),

('周末19期',4),

('周末19期',5)

;

二 插入数据INSERT

1. 插入完整数据(顺序插入)

语法一：

INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

语法二：

INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据

语法：

INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录

语法：

INSERT INTO 表名 VALUES

(值1,值2,值3…值n),

(值1,值2,值3…值n),

(值1,值2,值3…值n);

4. 插入查询结果

语法：

INSERT INTO 表名(字段1,字段2,字段3…字段n)

SELECT (字段1,字段2,字段3…字段n) FROM 表2

WHERE …;

三 更新数据UPDATE

语法：

UPDATE 表名 SET

字段1=值1,

字段2=值2,

WHERE CONDITION;

示例：

UPDATE mysql.user SET password=password(‘123’)

where user=’root’ and host=’localhost’;

四 删除数据DELETE

语法：

DELETE FROM 表名

WHERE CONITION;

示例：

DELETE FROM mysql.user

WHERE password=’’;

练习：

更新MySQL root用户密码为mysql123

删除除从本地登录的root用户以外的所有用户