MySQL数据库基础与实例教程（第2版 微课版）课件 3MySQL表管理与索引优化

MySQL表管理与索引优化数据类型选择约束设置及索引设计原则详解汇报人:孔祥盛目录数据类型选择01约束设置方法02表结构操作03索引设计原则04实验目标掌握05选课系统实现06数据库操作演示07外键功能验证0801数据类型选择整数类型设置符号属性01整数类型符号属性整数类型有5种，可设符号属性。小数类型依精度选用小数类型依精度选用小数分精确（decimal）和近似（float/double），依精度需求选用；财务用decimal保精确。01字符串类型分定变长字符串类型分定变长字符串含定长char及变长varchar等。日期类型本质可运算日期类型本质可运算MySQL中日期本质是数值，可进行算术运算；datetime与timestamp类型区别通过时区设置测试明确。02约束设置方法设置唯一性约束设置唯一性约束创建表时可设唯一性约束，通过查询系统表`information_schema.table_constraints`或使用`showindexfrom`命令可查看这些约束。添加外键约束外键约束添加方法通过ALTERTABLE实现外键约束添加，需先建父表后子表，InnoDB支持外键级联删除功能。外键级联操作InnoDB外键级联选项使父表记录删除时子表对应记录自动删除，MyISAM不支持该功能。外键约束查看使用`information_schema.table_constraints`系统表或`showindexfrom`命令查看外键约束。外键与存储引擎关系InnoDB支持外键级联删除，MyISAM不支持外键但可通过触发器模拟该功能。模拟检查约束模拟检查约束MySQL中检查约束需用触发器模拟，通过查询系统表或showindex命令可查看约束。查看约束信息查看约束信息方法通过查询系统表`information_schema.table_constraints`或使用`showindexfrom`命令可查看约束。约束类型包括唯一性约束、外键约束和检查约束（需用触发器模拟），确保数据完整性与一致性。03表结构操作创建表指定选项创建表指定选项创建表时可指定存储引擎、字符集等选项，如字符集设为utf8mb4、存储引擎用InnoDB以支持中文与外键。复制表方式复制表有like和select两种方式，不同复制方式对约束等的保留存在差异。表结构修改通过ALTERTABLE实现字段增删改、约束添加删除、主键自增、字段位置调整等操作。存储引擎切换演示存储引擎切换操作，如InnoDB支持外键级联删除而MyISAM不支持但可通过触发器模拟。复制表两种方式复制表两种方式复制表有like和select两种方式，不同复制方式对约束等的保留存在差异。修改表结构字段01020304修改表结构字段altertable用于修改表结构，包括字段增删改、约束管理等。字段位置调整通过ALTERTABLE实现主键自增、字段位置调整、数据类型变更等操作。约束添加删除处理外键与唯一性约束，演示如何通过ALTERTABLE添加或删除约束。数据类型变更使用ALTERTABLE修改字段数据类型，如将整数类型改为字符串类型。删除表处理关系删除表处理关系删除父表时需先解除外键依赖，MyISAM可直接删除父表记录且子表不受影响。04索引设计原则主键适合作索引主键适合作索引主键天然适合作索引（如MyISAM的主索引、InnoDB的聚簇索引），其值唯一且有序。选择离散度高的字段离散度高字段优先优先选择离散度高、存储空间小且固定的字段作为索引，能显著提升查询效率。避免频繁更新字段避免频繁更新字段频繁更新的字段不适合建索引，会降低写操作性能。复合索引最左前缀01复合索引最左前缀复合索引遵循最左前缀原则，可替代多个单列索引。常用于WHERE/GROUPBY/ORDERBY或连接的字段。05实验目标掌握修改表字段约束010203修改表字段约束通过ALTERTABLE实现主键自增、字段位置调整、数据类型变更等操作，并处理外键与唯一性约束。外键级联选项InnoDB支持外键级联删除，父表记录删除时子表对应记录自动删除；MyISAM不支持外键但可通过触发器模拟。约束管理操作演示添加唯一索引、全文索引、前缀索引及查看、删除索引操作，强调数据库设计的规范性和约束管理重要性。处理父子表关系外键级联删除功能InnoDB支持外键级联删除，父表记录删除时子表对应记录自动删除。MyISAM外键限制MyISAM不支持外键级联删除，可直接删除父表记录且子表记录不受影响。触发器模拟外键MyISAM可通过触发器模拟外键功能，实现父子表关系维护。删除父表依赖处理删除父表时需先解除外键依赖，确保数据库操作规范性。测试时间类型特性时间类型特性测试通过不同时区设置测试now()函数、datetime与timestamp类型区别，明确其特性。timestamp字段实验设置explicit_defaults_for_timestamp变量（ON/OFF），探究对timestamp字段数量、默认值及约束的影响。current_timestamp行为演示插入和更新操作下current_timestamp的行为，包括手动设置默认值的效果。创建选课系统表选课系统表创建要点字符集设为utf8mb4，存储引擎用InnoDB以支持中文与外键，先建父表后子表，特定字段设默认值。表结构查看方法通过多种命令查看表结构、约束、索引，总结不同约束类型特点及索引情况。复制表结构操作对比不同复制方式对约束等的保留差异，为后续数据库管理与应用奠定基础。外键与级联操作InnoDB支持外键级联删除，父表记录删除时子表对应记录自动删除；MyISAM不支持外键但可通过触发器模拟。06选课系统实现设置字符集引擎0102字符集设为utf8mb4选课系统数据库表创建时字符集设为utf8mb4以支持中文存储。存储引擎用InnoDB选课系统采用InnoDB存储引擎以支持外键约束功能。先建父表后子表先建父表后子表选课系统数据库创建时需先建父表后子表，字符集设为utf8mb4、存储引擎用InnoDB以支持中文与外键。查看结构约束查看约束方法通过查询系统表information_schema.table_constraints或使用showindexfrom命令可查看约束。约束类型特点不同约束类型特点及索引情况可通过命令查看，复制表结构时对约束的保留存在差异。外键级联操作InnoDB支持外键级联删除，父表记录删除时子表对应记录自动删除；MyISAM不支持但可通过触发器模拟。对比复制方式差异复制表结构方式复制表有like和select两种方式，不同复制方式对约束等的保留存在差异。07数据库操作演示变更表结构字段变更表结构字段altertable用于修改表结构，包括字段增删改、约束管理等。字段位置调整通过ALTERTABLE实现主键自增、字段位置调整、数据类型变更等操作。外键约束处理处理外键与唯一性约束，删除父表时需先解除外键依赖。数据类型变更演示了如何通过ALTERTABLE实现数据类型变更等操作。处理外键约束外键级联删除功能InnoDB支持外键级联删除，父表记录删除时子表对应记录自动删除。MyISAM外键限制MyISAM不支持外键级联删除，可直接删父表记录且子表记录不受影响。触发器模拟外键MyISAM虽不支持外键，但可通过触发器模拟该功能。切换存储引擎01存储引擎切换通过ALTERTABLE实现存储引擎切换，如从MyISAM改为InnoDB以支持外键功能。创建各类索引04030201索引类型与创建MySQL可创建单字段、复合、前缀索引，主键天然适合作索引（如MyISAM的主索引、InnoDB的聚簇索引）。索引设计原则优先选择离散度高、存储空间小且固定的字段；常用于WHERE/GROUPBY/ORDERBY或连接的字段；避免频繁更新的字段建索引。复合索引特性复合索引遵循最左前缀原则，可替代多个单列索引。过多索引会降低写操作性能，需权衡读写需求。全文索引应用全文索引需ngram中文支持，通过ALTERTABLE实现创建，适用于文本检索场景。08外键功能验证添加级联删除添加级联删除演示InnoDB外键级联选项，添加级联删除后父表记录删除时子表对应记录自动删除。对比引擎差异InnoDB与MyISAM外键支持差异InnoDB支持外键级联删除，父表记录删除时子表对应记录自动删除；MyISAM不支持外键级联删除，可直接删父表记录且子表记录不受影响。MyISAM外键功能模拟MyISAM虽不支持外键但可通过