MySQL数据分析进阶（十二）设计数据库——PART4

；※食用指南：文章内容为‘CodeWithMosh’SQL进阶教程系列学习笔记，笔记整理比较粗糙，主要目的自存为主，记录完整的学习过程。（图片超级多，慎看！）

【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！https://www.bilibili.com/video/BV1UE41147KC/?spm_id_from=333.1007.0.0&vd_source=b287f1f4a1fa54cc438e31a0f87ef4e2

目录：

第十二章：设计数据库——PART4

25、CREATING AND DROPPING DATABASES——创建和删除数据库

26、CREATING TABLES——创建表

27、ALTERING TABLES——更改表

28、CREATING RELATIONSHIPS——创建关系

29、ALTERING PRIMARY AND FOREIGN KEYS——更改主键和外键约束

30、CHARACTER SETS AND COLLATIONS——字符集和排序规则

31、STORAGE ENGINES——存储引擎

---

第十二章：设计数据库——PART4

25、CREATING AND DROPPING DATABASES——创建和删除数据库

如果想成为数据库管理员需要了解这些工具生成的脚本

每次它们生成一个脚本，需要检查，确保它不会对你的数据库产生任何不良影响

学习更多SQL语句，徒手创建或修改数据库，不用依赖于这些视像工具

①创建数据库

CREATE DATABASE：一个没有任何表的空数据库

②再次执行可能会报错，最好加上IF NOT EXISTS

③删除数据库

26、CREATING TABLES——创建表

在sql_store2重建sql_store数据库中的customers表

①逐一添加列比较费时，只添加关键列

②确保每位顾客都有唯一的电子邮件

🔺无法重新只能执行此表，因为数据库中已经有这张表

重建此表方法一

DROP TABLE IF EXISTS customers;

重建此表方法二

CREATE TABLE IF NOT EXISTS customers;

27、ALTERING TABLES——更改表

修改一张已经存在的表

假设忘记添加last_name，或者给定列设定了错误的数据类型

使用ALTER TABLE语句

如果直接执行，这列将被放在末尾

想要把该列放在first_name后：AFTER first_name

一开始就要避免列中有空格，如果出现空格必须使用反引号来表示列名

但列名就不该有空格，这是不雅陋习

在写ALTER TABLE语句时，可以一次进行多项更改（添加、删除更改一列或多列数据类型和属性）

COLUMN关键字可写可不写

不要在生产环境下更改表，会造成非常严重的后果，只在测试数据库上尝试更改

确保执行正常，不会产生任何不良影响以后，再在生产数据库中执行他们

28、CREATING RELATIONSHIPS——创建关系

创建订单表，并在顾客和订单之间添加一组关系

order_id没有NOT NULL

当我们对某列应用主键属性时，MySQL会默认该列不允许出现空值，主键列中存在空值不合理

定义两个表的关系用FOREIGN KEY，对customer_id列应用外键约束

fk_orders_customers(customer_id)：orders是子表名，customers是母表名，(customer_id)外键的列

REFERENCES customers(customer_id)：告诉MySQL这一列引用customers表的customer_id列

 指定更新和删除行为，级联还是拒绝

想让订单表中对应的顾客记录自动更新CASCADE

不想意外删除订单NO ACTION

有人认为不应该级联更新操作，因为主键不能变，理论上同意，实操中就不一定，有时候主键会变（人为失误、应用BUG）

29、ALTERING PRIMARY AND FOREIGN KEYS——更改主键和外键约束

如何在建表之后再添加外键

使用ALTER TABLE语句，添加或删除给定表的外键

也可以在添加或删除主键

DROP PRIMARY KEY不用打列名

30、CHARACTER SETS AND COLLATIONS——字符集和排序规则

①字符集、排序规则

字符集：将每个字符映射到数字的表

存储一个字符串，MySQL会把使用字符集将每个字符转为为它的数值表示

不同的字符集：仅支持拉丁语/仅支持亚洲语言；支持所有国际语言

此表可以看到当前版本MySQL支持的所有字符集

big5：繁体中文

utf8：存储几乎所有国际语言（MySQL版本5或以上使用的默认字符集）

Default collation（排序规则）：决定了某类语言的字符如何排序

utf8排序规则：utf8_general_ci

ci（case-insensitive，不区分大小写）：MySQL在排序时大小写同等优先级，多数是我们想要的

多为用户以大写或小写字符输入他们的名字，想要用字母顺序排序时，就不用考虑它们的大小写字母

所以99%的情况，不需要更改排序规则，

Maxlen（最大长度列）

utf8最大长度为3，即MySQL存储每个自负的时候最多保留3个字节

English(1)：英文字母占用1个字节

European、Middle-eastern(2)：欧洲和中东语言占用2个字节

Asian(3)：亚洲语言，中文、日文占用3个字节

多数情况不用考虑用不用更改字符集，保持默认设置就好了

某些情况下，可能要更改字符集来减少数据库大小

🔺假设表中一列的类型是CHAR（10）

CHAR表示固定长度的字符串，所以不管字符1还是字符10，MySQL都会保留10个字符串的空间

使用utf8字符集，MySQL预留的最大字节数时3

如果列的类型是CHAR（10），MySQL会为这一列的每个单元格预留10*3的空间，30字节

要是这张表有一百万条记录，就会配置三千万字节的空间

要是不需要支持国际语言，比如只需支持拉丁语

'latin1：被用于大多数拉丁语语言，预留1个字节

在此情况下，更改这一列、这张表或这个数据库的字符集，可以减少MySQL占用磁盘的字节数

②更改字符集

默认字符集：utf8

可以通过鼠标更改表或列级别的字符集

多数情况为了保持一致性，想要在数据库级别设置字符集，要么可以支持国际语言，要么不支持

❗如何使用SQL在数据库级别更改字符集

方法一

创建数据库时或之后设置字符集和排序规则

方法二

更改现有数据库的字符集

方法三

在表级别更改字符集

如果有一个现有的表

为特定列设置字符集

假设想要为名字列设置字符集——latin1

31、STORAGE ENGINES——存储引擎

MySQL有几个存储引擎：决定了数据图如何被存储，哪些功能可供我们使用

最常用的两个存储引擎MyISAM和InnoDB

MyISAM：旧版存储引擎（版本5.5之前）

InnoDB：更高级的存储引擎，支持事务等功能

目前为止谈论的所有内容都是InnoDB的一部分，MyISAM不支持这些功能（别用MyISAM了）

但有时使用的数据库使用更老版本的MySQL版本搭建的，这种情况可能想升级数据库，改用InnoDB，这样允许添加外键

外键对保持引用完整性非常重要

当在两张表之间添加关系时，不能给外键列添加无效值（外键的好处）

❗如何更换存储引擎

在表级别设置存储引擎，这样在同一个数据库中，就可以使用多个存储引擎

如果此表是基于更来版本的MySQL构建的话，可能就会是MyISAM

使用SQL语句更改一张表的存储引擎

更改一张表的存储引擎可能会花很大功夫，因为MySQL必须重建这张表，在此期间无法访问表，不要在产出阶段更改存储引擎，除非要定期维护

————TBC