MySQL8.0学习笔记（12）——Dateabase

简介

这节主要的内容是如何去创建一个合适的数据库

创建数据库的四个过程

1. 理解需求，详细分析。
2. 创建一个Conceptual Model，即需要创建哪些表，表内内容，表之间是一对多，还是多对多的形式
3. 创建一个Logical Model，表中内容的类型，是否需要中间表去连接，对应关系。
4. 创建一个Physical Model，使用workbench创建。

New Model

以一个学生登记课程的数据库为例，

1. 创建一个新的模型 File —— New Model
2. 创建Physical Model
   
   点击Add Diagram就可以进行创建model的界面
3. 在model界面创建各个表
   
   添加表与其中内容，注意主键（PK）在每一张表中都是唯一存在的

foreign key

foreign key表示一个表中有其他表的key的参考key，一般用于体现主从表之间的关系，比如有学生才有登记，即必须student表中有数据enrollments表才能有数据，而student与enrollments是一对多的形式，即一个学生可能有多个登记

点击框出的地方，先点击enrollments表，在点击student表建立联系，此时enrollments中有一个student_id,即为foreign key，course_id同理。

同时用student_id与course_id做混合主键

foreign key constrains

当parent table 主的主键改变时希望child table 关联的行为（尽管不建议修改表中具有唯一性数据的主键），以students表为例

总共四个行为

1. RESTRICT ：当student改变时，保持不变
2. CASCADE：当student改变时，随着变化
3. SET NULL：当student改变时，变为NULL，此时该数据为orphan record
4. NO ACTION:与RESTRICT 一致

关于Update与Delete选择哪个根据业务需求

Normalization

为了正常标准化，构建表时有7个范式，每个范式都要基于前面的范式，对于大部分的表来说，前3个范式足够了。

first normal from： 防止表内数据的冗余与重复

1. 每一个元素只能有一个单一的值，不能有重复的列
   以courses中的tags来看，可能每一个课程有多个tags，此时不能将所有的tags放在courses中，应该另外取一张分表来记录tags

2. 以两个一对多代关系表来形成多对多关系。
   在上述students与courses是多对多的关系，即一个学生可以登录多个课程，一个课程也可以被多个学生登录，但是多对多的关系过于冗余，因此使用link table用两个单对多来替代。 例如enrollments

3. 将courses中tags去掉，用分表tags来显示所有的tags用link table course_tags来维持两个表的多对多关系。

second normal from：每个表只表述一个实体，表中每一列只用来描述这个实体

1. 对于上述表中的course来说，不符合second normal from，因为instrcutor不是用于描述course的，可能其他地方也有使用instrcutor，
   用instrcutor会照成两个问题，1.浪费空间；2.当要对其修改时十分头痛，可能需要修改多个地方

2. 删除course的instrcutor，用一张Instructors表来表示instrcutor，并在courses用foreign key——instructor_id 来表示instrcutor，同时修改两张表中的关联行为。

third normal from：在表中的列不能由其他列派生出来

举个简单例子

full_name 依赖于first_name与last_name，这个是不合理的，舍去full_name即可

一个忠告，用现在的模型解决限制的问题，简单就好，而不需要一个模型包含整个宇宙能解决未来所以的需求。

Forward Engineering a Model

1. Database ——> forward engineer

2. next ——默认即可
   
3. next——>可以在show filter中排除自己不需要的表
   
4. next —— >可以将构建数据库的脚本保存

5. 查看创建的表

Synchronizing a Model with a Database

在enrollments上添加一列coupon表示登记课程的优惠
当模型发生变化时，如何同步关联的数据库

1. Database ——>Syncronize Model——>next
   
2. next
   
   3 next
   
   4 .next
   对应到要同步的数据库
   
   5 next ——> next
   可以看到这次修改对那几个表形成影响
   
3. execute
   先解除表之间foreign key的联系进行修改之后再重新建立联系
   

Reverse Engineering a Database

当没有模型只有数据库时，如何去修改表

1. Database ——> Reverse engineer
2. 一路next
   选择要变为模型的数据库，接着next
   
3. execute
   创建模型能够帮助我们更加直观的分析数据库。
   

使用命令代替可视化工具

创建数据库

• 创建一个数据库

create database if not exists sql_store2;

• 删除一个数据库

drop database if exists sql_store2;

创建表

create database if not exists sql_store2;
use sql_store2;
-- 下面两句可用 create table if not exists customers
drop table if exists customers;
create table customers
(-- integer类型，主键，自动递增customer_id INT PRIMARY KEY AUTO_INCREMENT,-- VARCHAR类型，不为空first_name VARCHAR(50) NOT NULL,-- INT类型，不为空，默认为0points 	INT NOT NULL DEFAULT 0,-- UNIQUE表示该类的数据没有重复的email VARCHAR(255) NOT NULL UNIQUE
);

修改表

ALTER TABLE customers-- 添加列last_name在first_name之后ADD last_name  VARCHAR(50) NOT NULL AFTER first_name,ADD city 		VARCHAR(50) NOT NULL,-- 修改列first_nameMODIFY COLUMN first_name VARCHAR(55) DEFAULT '',-- 删除列pointDROP points;

修改关系

• 创建foreign key

create database if not exists sql_store2;
use sql_store2;
DROP TABLE IF EXISTS orders;		//因为orders依赖于customers，删除时先要删除orders
drop table if exists customers;
create table customers
(customer_id INT PRIMARY KEY AUTO_INCREMENT,first_name VARCHAR(50) NOT NULL,points 	INT NOT NULL DEFAULT 0,email VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE orders
(order_id int primary key,customer_id int not null,-- 建立外部key customer_id，依托于orders与customers表.foreign key fk_orders_customers(customer_id)-- 参照于customers的customer_idreferences customers(customer_id)-- foreign key 的更新动作，参照前面的可视化工具操作on update cascadeon delete no action
) 

• 删除已有的foreign key或primary key

alter table ordersadd primary key(order_id),-- 不需要指定列drop primary key,drop foreign key fk_orders_customers,add foreign key fk_orders_customers(customer_id)references customers(customer_id)on update cascadeon delete no action

字符集

通过适当的修改字符集可以减小数据库大小，详细在
character set and collations

storage engines

不同的版本之后可能使用的存储引擎不一样，需要进行修改

Alter table customers
engine = innodb