MySQ-表关系-外键-修改表结构-复制表-03
- 2019 年 10 月 7 日
- 筆記
目录
前言
不合理的表结构（案例）
先来看下面这张表
emp_info 员工信息表
|
id |
name |
gender |
dep_name |
dep_desc |
|---|---|---|---|---|
|
1 |
jason |
male |
教学部 |
教书育人 |
|
2 |
egon |
male |
外交部 |
漂泊流浪 |
|
3 |
tank |
male |
教学部 |
教书育人 |
|
4 |
kevin |
male |
技术部 |
技术开发 |
所有的信息都记录在一张表中了
带来的问题
- 表的结构不清晰
- 浪费硬盘空间，数据冗余较多
- 表的扩展性、可维护性极差（无法忽视的缺点）
如何解决问题？
拆表，确定表关系，建立表关联
确定表与表之间的关系，一定要换位思考（必须两方都考虑周全之后才能得出结论）
以员工表和部门表为例： 先站在员工表看能否有多个员工对应一个部门 翻译过来： 一个部门能付否有多个员工 可以！！！（暂时只能确定员工单向多对一部门） 再站在部门表看能否有多个员工一起对应一个部门 翻译过来 ....乱了
如何确定表关系？
查找表关系，一定要分别站在两张表的角度全部考虑完毕才能下结论，否则无法得出正确答案
表关系
- 一对多
- 多对多
- 一对一
或者两张表没有关系
一对多
单向的多对一就是“一对多”的外键关系
无论是一对多还是多对一都是一对多的关系，都叫一对多，没有多对一的关系
多对多
如果两边都是单向的多对一关系，那么两者就是多对多的关系
分析
多对多的关系，必须额外创建第三张表，用来专门记录两种表之间的关联关系
- 如果按一对一的思路来，两张表都必须关联对方，都必须让对方先建立，那就建不了表了，外键存的是关联关系，那就单独开一张表，存关联关系
案例建立
同步更新删除是在外键上的
一对一
应用场景
- 表特别庞大时拆表优化性能（用户详细信息与展示信息）
- 客户与学生（客户可能成为学生，学生一定是客户）
如果双向的一对多都不成立，那么两张表之间只有两种情况了
- 一对一的关系
- 根本没有关系
外键字段必须用foreign key + unique 来限制，唯一且必须出现
判断表关系最简单的语法
三种关系常见案例
一对多
有一个可以就是一对多的关系（有且只有一个）
图书与出版社
一本书可不可以有多个出版社？不可以
一个出版社可不可以初版多本书，可以！！！
一对多的关系
多对多
有两个可以就是多对多的关系
图书与作者表
一本书可不可以有多个作者 可以！！
一个作者可不可以写多本书 可以！！
多对多的关系
一对一 或 没有关系
两个都不可以，要么是一对一的关系，要么没有关系
作者与作者详情
一个作者可不可以有多个详情 不可以！！
一个作者详情可不可以有多个作者 不可以！！！
一对一的关系
如何建立表关系？
外键 foreign key
在MySQL中通过外键来建立表与表之间的硬性关系
通常将关系字段称之为外键字段
确定外键字段归属方
- 一对多的外键字段，应该建在“多”的那一方
- 多对多的外键字段建在额外的第三张表上
- 一对一的外键字段建在任意一方都行，但推荐建在查询频率较高的一方（外键字段必须保证唯一性）
有外键关系的注意点
- 在创建表的时候，必须先创建被关联表
- 插入数据的时候也应该先插入被关联数据
- 级联更新，级联删除
注意外键的逗号，（逗号代表一个字段的结束）（还是不要忘了建表的字段定义那块最后一个语句不要加 , 逗号）
外键虽然能够帮你强制建立表关系，但也会给两行表之间增加数据相关的约束
修改表
mysql 对大小写不敏感 一定要注意是英文符号
修改表名
ALTER TABLE 表名 RENAME 新表名; alter table 表名 rename 新表名;
增加字段
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; # 直接移到最前面 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; # 选择要插在哪个字段后面
删除字段
ALTER TABLE 表名 DROP 字段名;
修改字段
# modify只能改字段数据类型完整约束，不能改字段名，但是change可以！ ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
复制表
复制表结构+记录
key不会复制：主键、外键和索引
# 查询语句执行的结果也是一张表，可以看成虚拟表 # 复制表结构＋记录 （key不会复制: 主键、外键和索引） create table new_service select * from service; like 可以吗？ 试试
利用条件实现仅复制表结构
条件为假，查不出数据
select * from service where 1=2; //条件为假，查不到任何记录 # 只复制表结构 create table new1_service select * from service where 1=2; create table t4 like employees;
今日数据库操作语句
创建数据库
mysql> create database db1; Query OK, 1 row affected (0.01 sec) mysql> mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | just_test | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec)
拆分员工与部门信息成两张表
一个员工属于一个部门
一个部门有多个员工
—> 一对多
在员工那边保留部门编号
创建表(员工与部门信息表)
create table dep( id int primary key auto_increment, dep_name varchar(30), dep_comment varchar(60) ); create table emp( id int primary key auto_increment, name varchar(50), gender enum('male', 'famale', 'others') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) ); insert into dep (dep_name, dep_comment) values('研发部', '敲代码的那一群人'), ('教育部', '成天这啊那啊找麻烦的那群人'), ('后勤保障部', '搬水的关门的收拾卫生的'); insert into emp (name, dep_id) values('jason', 1), ('egon', 2), ('agong', '3'); alter table emp modify foreign key(dep_id) refrences dep(id) on update cacade on delete cacade;
select emp.id,name,gender,dep.dep_name,dep.dep_comment from emp left join dep on dep.id=emp.id;
