mysql整理表（MySQL表结构的管理）

mysql 架构是系统架构 。它包含存储 MySQL 服务器运行时所需信息的表。广义的分类是， mysql模式包含存储数据库对象元数据的数据字典表，以及用于其他操作目的的系统表。

《三国志》数据库各个表的实施过程掌握表结构管理。其中包含MySQL数据类型、如何创建表单、修改表结构、索引引用等内容。

MySQL数据类型数值类型（整数类型）

整数类型的数，默认情况下既可以表示正整数又可以表示负整数（此时称为有符号数）。如果只希望表示零和正整数，可以使用无符号关键字 unsigned 对整数类型进行修饰（此时称为无符号整数）。

数值类型（小数类型）

decimal(length,precision)用于表示精度确定（小数点后数字的位数确定）的小数类型，length决定了该小数的最大位数，precision用于设置精度（小数点后数字的位数）。

例如：

• decimal(5,2)表示小数取值范围：-999.99～999.99
• decimal(5,0)表示：-99999～99999的整数。

字符串类型

字符串类型的数据外观上使用单引号括起来，例如三国人物姓名 张飞、国家名称 蜀国 等。

char()与varchar() 的区别

例如对于简体中文字符集gbk的字符串而言，varchar(255)表示可以存储255个汉字，而每个汉字占用两个字节的存储空间。假如这个字符串没有那么多汉字，例如仅仅包含一个‘中’字，那么varchar(255)仅仅占用1个字符（两个字节）的储存空间；而char(255)则必须占用255个字符长度的存储空间，哪怕里面只存储一个汉字。

日期类型

• date表示日期 默认格式为 YYYY-MM-DD
• time表示时间 默认格式为 HH:ii:ss
• year表示年份
• datetime与timestamp 是日期和时间的混合类型 默认格式为 YYYY-MM-DDHH:ii:ss

datetime 与 timestamp 都是日期和时间的混合类型，区别在于：表示的取值范围不同，datetime 的取值范围远远大于 timestamp 的取值范围。将NULL插入timestamp字段后，该字段的值实际上是MySQL服务器当前的日期和时间。同一个timestamp类型的日期或时间，不同的时区，显示结果不同。

复合类型（包括enum类型和set类型）

MySQL支持两种复合数据类型：enum枚举类型和set集合类型。

• enum类型的字段类似于单选按钮的功能，一个enum类型的数据最多可以包含65535个元素。
• set类型的字段类似于复选框的功能，一个set类型的数据最多可以包含64个元素。

二进制类型

二进制类型的字段主要用于存储由 0 和 1 组成的字符串，因此从某种意义上将，二进制类型的数据是一种特殊格式的字符串。

二进制类型与字符串类型的区别在于：字符串类型的数据按字符为单位进行存储，因此存在多种字符集、多种字符序；而二进制类型的数据按字节为单位进行存储，仅存在二进制字符集binary。

选择合适的数据类型

选择合适的数据类型，不仅可以节省储存空间，还可以有效地提升数据的计算性能。

1. 在符合应用要求（取值范围、精度）的前提下，尽量使用“短”数据类型。
2. 数据类型越简单越好。
3. 在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储日期和时间。
4. 尽量采用精确小数类型（例如decimal），而不采用浮点数类型。使用精确小数类型不仅能够保证数据计算更为精确，还可以节省储存空间，例如百分比使用decimal(4,2)即可。
5. 尽量避免NULL字段，建议将字段指定为NOTNULL约束。

创建表单

Createtable 表名( 字段名1数据类型 [约束条件], … [其他约束条件],[其他约束条件] ) 其他选项（例如存储引擎、字符集等选项）

设置约束

设置主键（PrimaryKey）约束

如果一个表的主键是单个字段，直接在该字段的数据类型或者其他约束条件后加上 primarykey 关键字，即可将该字段设置为主键约束。也可以是多个主键共同约束，设置为复合主键。

CREATE TABLE san_wujiang ( ...... primarykey 姓名); CREATE TABLE san_wujiang ( ...... primarykey (姓名,性别));

设置自增型字段

默认情况下，MySQL自增型字段的值从1开始递增，且步长为1。

CREATE TABLE san_wujiang ( `序号` int(11) NOT NULL AUTO_INCREMENT,

其他选项的设置

设置表的存储引擎

CREATE TABLE san_wujiang (......) engine = 存储引擎类型

设置该表的字符集

CREATE TABLE san_wujiang (......) default charset = 字符集类型

设置索引关键字的压缩方式，可以通过设置 pack_keys 选项实现（注意仅对MyISAM存储引擎的表有效）。

• 压缩类型值为default： 表示只压缩索引中字符串类型的关键字（例如char、varchar、text等字段），但不压缩数值类型的关键字。
• 压缩类型值为0： 表示取消索引关键字的压缩。
• 压缩类型值为1： 表示压缩索引中所有关键字的储存空间，这样做通常会使检索速度加快，更新速度变慢。

CREATE TABLE san_wujiang (......) pack_keys=压缩类型

复制一个表结构

方法一，在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中。

create table 新表名 like 源表

方法二，在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。

create table 新表名 select * from 源表

修改表结构

成熟的数据库设计，数据库的表结构一般不会发生变化。数据库的表结构一旦发生变化，基于该表的视图、触发器、存储过程将直接受到影响，甚至导致应用程序的修改。

修改字段相关信息

删除 姓名 字段。

alter table san_wujiang drop 姓名

添加 昵称 新字段。

alter table san_wujiang add 昵称 varchart(255)

修改表的 姓名 和 数据类型。

alter table san_wujiang change 姓名 新姓名 varchart(255)

仅对 姓名 字段的数据类型进行修改。

alter table san_wujiang modify 姓名 varchart(100)

修改约束条件

添加约束条件

向表的某个字段添加约束条件的语法格式如下（其中约束类型可以是唯一性约束、主键约束及外键约束）。

alter table san_wujiang add constraint 约束名 primary key ('序号')

删除表的主键约束条件

alter table san_wujiang drop primary key

删除表的外键约束时，指定外键约束名称

alter table san_wujiang drop foreign key 约束名

删除表字段的唯一性约束

实际上只需删除该字段的唯一性索引即可。

alter table san_wujiang drop index 索引名;

修改表的其他选项

修改引擎

alter table san_wujiang engine=innodb

修改字符集

alter table san_wujiang default charset=utf8mb4

修改起始值为1

alter table san_wujiang auto_increment=1

修改压缩类型

alter table san_wujiang pack_keys=1

修改表名

rename table san_wujiang to san_wujiang_new alter table san_wujiang rename san_wujiang_new

删除表

如果表之间存在外键约束关系，此时需要注意删除表的顺序。

drop table san_wujiang

索引

创建数据库表时，初学者通常仅仅关注该表有哪些字段、字段的数据类型及约束条件等信息，数据库表中另一个重要的概念 索引 很容易被忽视。

什么是索引

想象一下现代汉语词典的使用方法，理解索引的重要性。

1. 索引的本质是什么？
2. MySQL数据库中，数据是如何检索的？
3. 一个数据库表只能创建一个索引吗？
4. 什么是前缀索引？
5. 索引可以是字段的组合吗？
6. 能跨表创建索引吗？想象一下现代汉语词典的使用方法，理解索引的重要性。
7. 索引数据需要额外的存储空间吗？
8. 表中的哪些字段适合选作表的索引？什么是主索引？什么是聚簇索引？
9. 索引与数据结构是什么关系？
10. 索引非常重要，同一个表，表的索引越多越好吗？

索引关键字的选取原则

索引的设计往往需要一定的技巧，掌握了这些技巧，可以确保索引能够大幅地提升数据检索效率，弥补索引在数据更新方面带来的缺陷。

1. 表的某个字段值离散度越高，该字段越适合选作索引的关键字。
2. 占用储存空间少的字段更适合选作索引的关键字。
3. 较频繁地作为where查询条件的字段应该创建索引，分组字段或者排序字段应该创建索引，两个表的连接字段应该创建索引。
4. 更新频繁的字段不适合创建索引，不会出现在where子句中的字段不应该创建索引。
5. 最左前缀原则。
6. 尽量使用前缀索引。

索引与约束

约束主要用于保证业务逻辑操作数据库时数据的完整性；约束是逻辑层面的概念。 索引则是将关键字数据以某种数据结构的方式存储到外存，用于提升数据的检索性能；索引既有逻辑上的概念，更是一种物理存储方式，且事实存在、需要耗费一定的储存空间。

索引种类

• 主索引、聚簇索引
• 唯一性索引
• 普通索引
• 复合索引
• 全文索引（fulltext）

创建索引

创建表的同时创建索引

create table 表名(字段名1 数据类型 [约束条件], …[其他约束条件],… [ unique | fulltext ] index [索引名] ( 字段名 [(长度)] [ asc | desc ] ) ) engine=存储引擎类型 default charset=字符集类型

使用姓名当作索引

CREATE TABLE san_wujiang ( '序号' CHAR ( 20 ) PRIMARY KEY, '姓名' CHAR ( 100 ) NOT NULL, UNIQUE INDEX name_index ( '姓名' ( 20 )), FULLTEXT INDEX brief_fulltext ('姓名'), ) ENGINE = MyISAM DEFAULT charset = gbk;

在已有表上创建索引

create [ unique | fulltext ] index 索引名 on 表名 ( 字段名 [(长度)] [ asc | desc ] ) alter table 表名 add [ unique | fulltext ] index index_name ( '姓名' [(varchar(20))] [ asc | desc ] )

删除索引

drop index index_name on san_wujiang

思考问题

问题1：如何创建一个剧本中需要的武将数据？

问题2：在新的剧本中如何创建我们自定义的武将？

,