foreign key(表与表之间的关系)
引子
把所有的数据存在一张表的弊端：
1、表的组织结构复杂不清晰
2、浪费空间
3、扩展性极差
语法示例
1#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
2create table department(
3id int primary key,
4name varchar(20) not null
5)engine=innodb;
6
7#dpt_id外键，关联父表（department主键id），同步更新，同步删除
8create table employee(
9id int primary key,
10name varchar(20) not null,
11dpt_id int,
12constraint fk_name foreign key(dpt_id)
13references department(id)
14on delete cascade
15on update cascade
16)engine=innodb;
17
18#先往父表department中插入记录
19insert into department values
20(1,'欧德博爱技术有限事业部'),
21(2,'艾利克斯人力资源部'),
22(3,'销售部');
23
24#再往子表employee中插入记录
25insert into employee values
26(1,'egon',1),
27(2,'alex1',2),
28(3,'alex2',2),
29(4,'alex3',2),
30;
31
32#删父表department，子表employee中对应的记录跟着删
33mysql> delete from department where id=3;
34mysql> select * from employee;
35+----+-------+--------+
36| id | name | dpt_id |
37+----+-------+--------+
38| 1 | egon | 1 |
39| 2 | alex1 | 2 |
40| 3 | alex2 | 2 |
41| 4 | alex3 | 2 |
42+----+-------+--------+
43
44#更新父表department，子表employee中对应的记录跟着改
45mysql> update department set id=22222 where id=2;
46mysql> select * from employee;
47+----+-------+--------+
48| id | name | dpt_id |
49+----+-------+--------+
50| 1 | egon | 1 |
51| 3 | alex2 | 22222 |
52| 4 | alex3 | 22222 |
53| 5 | alex1 | 22222 |
54+----+-------+--------+
foreign key会带来什么样的效果？
约束1：在创建表时，先建被关联的表，才能建关联表
约束2：在插入记录时，必须先给被关联的表插入记录，才能给关联表插入记录
约束3：更新与删除都需要考虑到关联与被关联的关系
约束3的解决方案：同步更新 on update cascade 同步删除on delete cascade
如何找出表之间的关系
1>分析步骤：
2**1、先站在左表的角度去找**
3是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
4**2、再站在右表的角度去找**
5是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
6**3、总结：**
7#多对一：
8如果只有步骤1成立，则是左表多对一右表
9如果只有步骤2成立，则是右表多对一左表
10#多对多
11如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
12#一对一:
13如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
建立表之间的关系
一对多或者多对一
三张表：出版社，作者信息，书
一对多（或多对一）：一个出版社可以出版多本书
关联方式：foreign key
1=====================多对一=====================
2create table press(
3id int primary key auto_increment,
4name varchar(20)
5);
6
7create table book(
8id int primary key auto_increment,
9name varchar(20),
10press_id int not null,
11foreign key(press_id) references press(id)
12on delete cascade
13on update cascade
14);
15
16insert into press(name) values
17('北京工业地雷出版社'),
18('人民音乐不好听出版社'),
19('知识产权没有用出版社')
20;
21
22insert into book(name,press_id) values
23('九阳神功',1),
24('九阴真经',2),
25('九阴白骨爪',2),
26('独孤九剑',3),
27('降龙十巴掌',2),
28('葵花宝典',3)
29;
多对多
三张表：出版社，作者信息，书
多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
关联方式：foreign key+一张新的表
1====================多对多=====================
2create table author(
3id int primary key auto_increment,
4name varchar(20)
5);
6
7#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
8create table author2book(
9id int not null unique auto_increment,
10author_id int not null,
11book_id int not null,
12constraint fk_author foreign key(author_id) references author(id)
13on delete cascade
14on update cascade,
15constraint fk_book foreign key(book_id) references book(id)
16on delete cascade
17on update cascade,
18primary key(author_id,book_id)
19);
20
21#插入四个作者，id依次排开
22insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
23
24#每个作者与自己的代表作如下
251 egon:
26 1 九阳神功
27 2 九阴真经
28 3 九阴白骨爪
29 4 独孤九剑
30 5 降龙十巴掌
31 6 葵花宝典
322 alex:
33 1 九阳神功
34 6 葵花宝典
353 yuanhao:
36 4 独孤九剑
37 5 降龙十巴掌
38 6 葵花宝典
394 wpq:
40 1 九阳神功
41
42insert into author2book(author_id,book_id) values
43(1,1),
44(1,2),
45(1,3),
46(1,4),
47(1,5),
48(1,6),
49(2,1),
50(2,6),
51(3,4),
52(3,5),
53(3,6),
54(4,1)
55;
一对一
两张表：学生表和客户表
一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系
关联方式：foreign key+unique
1#一定是student来foreign key表customer，这样就保证了：
2#1 学生一定是一个客户，
3#2 客户不一定是学生，但有可能成为一个学生
4
5create table customer(
6id int primary key auto_increment,
7name varchar(20) not null,
8qq varchar(10) not null,
9phone char(16) not null
10);
11
12create table student(
13id int primary key auto_increment,
14class_name varchar(20) not null,
15customer_id int unique, #该字段一定要是唯一的
16foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
17on delete cascade
18on update cascade
19);
20
21#增加客户
22insert into customer(name,qq,phone) values
23('李飞机','31811231',13811341220),
24('王大炮','123123123',15213146809),
25('守榴弹','283818181',1867141331),
26('吴坦克','283818181',1851143312),
27('赢火箭','888818181',1861243314),
28('战地雷','112312312',18811431230)
29;
30
31#增加学生
32insert into student(class_name,customer_id) values
33('脱产3班',3),
34('周末19期',4),
35('周末19期',5)
36;
表的其他操作
修改
1语法：
21. 修改表名
3 ALTER TABLE 表名
4 RENAME 新表名;
52. 增加字段
6 ALTER TABLE 表名
7 ADD 字段名 数据类型 [完整性约束条件…],
8 ADD 字段名 数据类型 [完整性约束条件…];
9 ALTER TABLE 表名
10 ADD 字段名 数据类型 [完整性约束条件…] FIRST;
11 ALTER TABLE 表名
12 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
13
143. 删除字段
15 ALTER TABLE 表名
16 DROP 字段名;
17
184. 修改字段
19 ALTER TABLE 表名
20 MODIFY 字段名 数据类型 [完整性约束条件…];
21 ALTER TABLE 表名
22 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
23 ALTER TABLE 表名
24 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
示例
11. 修改存储引擎
2mysql> alter table service
3 -> engine=innodb;
4
52. 添加字段
6mysql> alter table student10
7 -> add name varchar(20) not null,
8 -> add age int(3) not null default 22;
9
10mysql> alter table student10
11 -> add stu_num varchar(10) not null after name; //添加name字段之后
12
13mysql> alter table student10
14 -> add sex enum('male','female') default 'male' first; //添加到最前面
15
163. 删除字段
17mysql> alter table student10
18 -> drop sex;
19
20mysql> alter table service
21 -> drop mac;
22
234. 修改字段类型modify
24mysql> alter table student10
25 -> modify age int(3);
26mysql> alter table student10
27 -> modify id int(11) not null primary key auto_increment; //修改为主键
28
295. 增加约束（针对已有的主键增加auto_increment）
30mysql> alter table student10 modify id int(11) not null primary key auto_increment;
31ERROR 1068 (42000): Multiple primary key defined
32
33mysql> alter table student10 modify id int(11) not null auto_increment;
34Query OK, 0 rows affected (0.01 sec)
35Records: 0 Duplicates: 0 Warnings: 0
36
376. 对已经存在的表增加复合主键
38mysql> alter table service2
39 -> add primary key(host_ip,port);
40
417. 增加主键
42mysql> alter table student1
43 -> modify name varchar(10) not null primary key;
44
458. 增加主键和自动增长
46mysql> alter table student1
47 -> modify id int not null primary key auto_increment;
48
499. 删除主键
50a. 删除自增约束
51mysql> alter table student10 modify id int(11) not null;
52
53b. 删除主键
54mysql> alter table student10
55 -> drop primary key;
复制
1#复制表结构＋记录 （key不会复制: 主键、外键和索引）
2mysql> create table new_service select * from service;
3
4#只复制表结构
5mysql> select * from service where 1=2; //条件为假，查不到任何记录
6Empty set (0.00 sec)
7mysql> create table new1_service select * from service where 1=2;
8Query OK, 0 rows affected (0.00 sec)
9Records: 0 Duplicates: 0 Warnings: 0
10
11mysql> create table t4 like employees;
删除
1DROP TABLE 表名;
2#补充
3delete from tb1;
4强调：上面的这条命令确实可以将表里的所有记录都删掉，但不会将id重置为0，
5所以该条命令根本不是用来清空表的，delete是用来删除表中某一些符合条件的记录,
6如：delete from tb1 where id > 10;
7如果要清空表，使用truncate tb1；
8作用：将整张表重置