外键
外键: 用来建立两张表之间的关系
语法: foreign key (当前表中建立关系的外键字段) references 被关联表名（id）
员工信息表有三个字段：工号 姓名 部门
公司有3个部门，但是有很多员工，说明部门这个字段需要重复存储，部门名字越长，越浪费
解决方法: 定义一个部门表，让员工信息表关联该表，使用foreign key 关联
#建立被关联表(部门表)
create table dep(
id int primart key auto_increment,
dep_name varchar(16) not null,
dep_desc varchar(50)
);
#建立关联表(员工表) dep_id外键，同步更新(删除)
create table emp(
id int primary key auto_increment,
name varchar(16) not null,
age int,
gender enum('male', 'female') default 'male',
dep_id int not null,
foreign key(dep_id) references dep(id)
on delete cascade
on update cascade
);
# 插入数据：
-必须先插入被关联表的数据，再插入关联表的数据
#dep
insert into dep(dep_name, dep_desc) values
('外交部', '出台'),('销售部','销售'),('才艺部','唱跳rap');
+----+-----------+-----------+
| id | dep_name | dep_desc |
+----+-----------+-----------+
| 1 | 外交部 | 出台 |
| 2 | 销售部 | 销售 |
| 3 | 才艺部 | 唱跳rap |
+----+-----------+-----------+
#emp
insert into emp(name, age, gender, dep_id) values
('yyh', 23, 'male', 1),
('xxt', 24, 'male', 2),
('ldh', 24, 'male', 3),
('hys', 26, 'male', 1);
+----+------+------+--------+--------+
| id | name | age | gender | dep_id |
+----+------+------+--------+--------+
| 1 | yyh | 23 | male | 1 |
| 2 | xxt | 24 | male | 2 |
| 3 | ldh | 24 | male | 3 |
| 4 | hys | 26 | male | 1 |
+----+------+------+--------+--------+
#报错
insert into emp(name, age, gender, dep_id) values
('ysk', 12, 'male', 5);
# 级联更新，级联删除
on update cascade
on delete cascade
#更新 删除记录
update dep set id=4 where id=1;
+----+-----------+-----------+
| id | dep_name | dep_desc |
+----+-----------+-----------+
| 2 | 销售部 | 销售 |
| 3 | 才艺部 | 唱跳rap |
| 4 | 外交部 | 出台 |
+----+-----------+-----------+
+----+------+------+--------+--------+
| id | name | age | gender | dep_id |
+----+------+------+--------+--------+
| 1 | yyh | 23 | male | 4 |
| 2 | xxt | 24 | male | 2 |
| 3 | ldh | 24 | male | 3 |
| 4 | hys | 26 | male | 4 |
+----+------+------+--------+--------+
delete from dep where id=2;
+----+-----------+-----------+
| id | dep_name | dep_desc |
+----+-----------+-----------+
| 3 | 才艺部 | 唱跳rap |
| 4 | 外交部 | 出台 |
+----+-----------+-----------+
+----+------+------+--------+--------+
| id | name | age | gender | dep_id |
+----+------+------+--------+--------+
| 1 | yyh | 23 | male | 4 |
| 3 | ldh | 24 | male | 3 |
| 4 | hys | 26 | male | 4 |
+----+------+------+--------+--------+
如何找出两张表之间的关系
分析步骤：
#先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段(id)
#再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表的一个字段(id)
#总结
#一对多：
如果只有步骤1成立，则是右表一对多左表
如果只有步骤2成立，则是左表一对多右表
#多对多
如果步骤1和2同时成立，则证明这两张表是一个双向的一对多，即多对多，需要另外定义一个这两张表的关系表来存放二者的关系
#一对一
如果1和2都不成立，而是左(右)表的一条记录唯一对应右(左)表的一条记录，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
#多对多
#关联方式 foreign key + 一张新的表
#一个作者可以写几本书，一本书也可以有几个作者
1.yyh:
1.社交宝典
2.达盖尔的旗帜
2.xxt:
2.达盖尔的旗帜
3.女朋友信耶稣
3.ldh:
2.达盖尔的旗帜
4.性冷淡浅谈
#book
create table book(
id int primary key auto_increment,
title varchar(20),
price int,
book_content varchar(50)
);
#auther
create table auther(
id int primary key auto_increment,
name varchar(16),
age int
);
#book2auther
create table book2auther(
id int primary key auto_increment,
book_id int,
auther_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade,
foreign key(auther_id) references auther(id)
on update cascade
on delete cascade
);
#插入数据
#book
insert into book(title, price, book_content) values
('社交宝典', 199, '3分钟教你把妹'),
('达盖尔的旗帜', 299, '精彩生活图集'),
('女朋友信耶稣', 399, '了解作者的手冲生活'),
('性冷淡浅谈', 499, '没有孩子到底是为什么');
+----+--------------------+-------+--------------------------------+
| id | title | price | book_content |
+----+--------------------+-------+--------------------------------+
| 1 | 社交宝典 | 199 | 3分钟教你把妹 |
| 2 | 达盖尔的旗帜 | 299 | 精彩生活图集 |
| 3 | 女朋友信耶稣 | 399 | 了解作者的手冲生活 |
| 4 | 性冷淡浅谈 | 499 | 没有孩子到底是为什么 |
+----+--------------------+-------+--------------------------------+
#auther
insert into auther(name, age) values
('yyh', 23),
('xxt', 24),
('ldh', 25);
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | yyh | 23 |
| 2 | xxt | 24 |
| 3 | ldh | 25 |
+----+------+------+
#book2auther
insert into book2auther(auther_id, book_id) values
(1, 1),
(1, 2),
(2, 2),
(2, 3),
(3, 2),
(3, 4);
+----+---------+-----------+
| id | book_id | auther_id |
+----+---------+-----------+
| 7 | 1 | 1 |
| 8 | 2 | 1 |
| 9 | 2 | 2 |
| 10 | 3 | 2 |
| 11 | 2 | 3 |
| 12 | 4 | 3 |
+----+---------+-----------+
#报错，插入的数据，book_id, auther_id必须存在
insert into book2auther(book_id, auther_id) values(4,4);
#更新或删除
#更新
update book set price=666 where id=1;
update book set id=5 where id=1;
+----+--------------------+-------+--------------------------------+
| id | title | price | book_content |
+----+--------------------+-------+--------------------------------+
| 2 | 达盖尔的旗帜 | 299 | 精彩生活图集 |
| 3 | 女朋友信耶稣 | 399 | 了解作者的手冲生活 |
| 4 | 性冷淡浅谈 | 499 | 没有孩子到底是为什么 |
| 5 | 社交宝典 | 666 | 3分钟教你把妹 |
+----+--------------------+-------+--------------------------------+
+----+---------+-----------+
| id | book_id | auther_id |
+----+---------+-----------+
| 1 | 5 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 2 |
| 4 | 3 | 2 |
| 5 | 2 | 3 |
| 6 | 4 | 3 |
+----+---------+-----------+
#删除
delete from book where id=5;
+----+--------------------+-------+--------------------------------+
| id | title | price | book_content |
+----+--------------------+-------+--------------------------------+
| 2 | 达盖尔的旗帜 | 299 | 精彩生活图集 |
| 3 | 女朋友信耶稣 | 399 | 了解作者的手冲生活 |
| 4 | 性冷淡浅谈 | 499 | 没有孩子到底是为什么 |
+----+--------------------+-------+--------------------------------+
#一对一，两张表的关系一一对应，将一张数据量比较大的表，拆分成两张
#关联方式：foreign key + unique
用户信息：id, name, age, gender, hobby, id_card
us表:id, name, age, detail_id(外键)
detail表:id, gender, hobby, id_card
#被关联表
create table us(
id int primary key auto_increment,
name varchar(16),
age int
);
#关联表
create table detail(
id int primary key auto_increment,
gender enum('male', 'female') default 'male',
hobby set('singing','dancing','rap','ball'),
user_id int unique,
foreign key(user_id) references us(id)
on update cascade
on delete cascade
);
#插入数据
insert into us(name, age) values
('yyh', 38),('xxt', 40);
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | yyh | 38 |
| 2 | xxt | 40 |
+----+------+------+
insert into detail(gender, hobby, user_id) values
('male','singing,rap', 1),('female','dancing,ball',2);
+----+--------+--------------+---------+
| id | gender | hobby | user_id |
+----+--------+--------------+---------+
| 1 | male | singing,rap | 1 |
| 2 | female | dancing,ball | 2 |
+----+--------+--------------+---------+
#修改表的操作
1.修改表名
alter table 表名 rename 新表名;
2.增加字段
alter table 表名 add 字段名 数据类型[完整性约束条件];
alter table 表名 add 字段名 数据类型[完整性约束条件] first; # 添加到第一列
alter table 表名 add 字段名 数据类型[完整性约束条件] after;# 添加到某一列后
3.修改字段
#修改数据类型
alter table 表名 modify 字段名 数据类型[完整性约束条件];
#修改字段名，保留字段类型
alter table 表名 change 旧字段名 新字段名 旧数据类型[完整性约束条件];
#修改字段名与字段类型
alter table 表名 change 旧字段名 新字段名 新数据类型[完整性约束条件];
#复制表的操作
复制表结构+记录 (key不会复制：主键、外键和索引)
create table new_service select * from service;
只复制结构
create table new_service select * from service where 1=2;
删除表
drop table 表名;
