MySQL学习笔记（四）数据库设计

数据库设计就是根据业务规划表、字段、表和表之间的关系。目标少存重复数据、数据不出错、查询速度够用。一、4个核心设计目标减少数据冗余同样的数据不要重复存多份避免改一处、别处没同步保证数据完整不让脏数据存入数据库性能够用查询不要太慢方便后续改需求二、数据库设计完整流程需求分析搞清楚系统里有哪些事物实体、它们有什么信息、事物之间有什么联系。不要上来就写建表SQL概念设计 ER模型实体现实对象例学生、课程属性对象的信息例学生姓名、手机号关系一对一一个A对应一个B一对多一个A对应多个B最常用多对多A和B互相多对多必须新建中间表不能直接存逗号分割id逻辑设计把ER模型翻译成表定主键、外键。物理设计写SQL确定字段类型、约束、索引。维护迭代测试后续根据需求修改。三、三大范式范式是一套设计规范考试常考开发中不用死板死守。1NF 第一范式原子性一个单元格只存一份信息不能把多个信息塞同一个字段。错误contact13800001111;zhangsanqq.com正确拆成手机号、邮箱两个字段。2NF 第二范式消除部分依赖一张表尽量只描述一件事物。如果是联合主键所有字段都要完整依赖全部主键。3NF 第三范式消除传递依赖表中字段不要间接推导出来。比如学生表不要写学院名称只存学院id学院名称放在学院表。重点允许反范式。为查询更快可以少量冗余字段但要自己保证数据同步。四、主键、外键零基础要点主键PRIMARY KEY唯一标识一行优先使用AUTO_INCREMENT自增数字。不要拿手机号、身份证做主键业务信息会修改。外键FOREIGN KEY用来关联两张表。注意MySQL外键会降低性能。五、SQL示例多对多中间表是重点-- 学生表CREATETABLEstudent(stu_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT学生编号主键自增,stu_nameVARCHAR(32)NOTNULLCOMMENT学生姓名不能为空,phoneVARCHAR(16)UNIQUECOMMENT手机号不能重复)ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 课程表CREATETABLEcourse(course_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT课程编号,course_nameVARCHAR(64)NOTNULLCOMMENT课程名称,creditTINYINTNOTNULLCOMMENT学分)ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 多对多中间表学生选课记录学生选了什么课、分数CREATETABLEstu_course(idINTPRIMARYKEYAUTO_INCREMENT,stu_idINTNOTNULLCOMMENT关联学生表主键,course_idINTNOTNULLCOMMENT关联课程表主键,scoreDECIMAL(5,2)COMMENT考试分数,-- 作业考试可以写外键约束FOREIGNKEY(stu_id)REFERENCESstudent(stu_id),FOREIGNKEY(course_id)REFERENCEScourse(course_id))ENGINEInnoDBDEFAULTCHARSETutf8mb4;六、易踩坑点什么数据都塞一张大表里拿手机号、姓名这类业务字段当主键字段大量允许NULL尽量给默认值多对多图省事用1,2,3字符串存id不建中间表无法做联表查询死磕三范式拆几十张表查询需要大量JOIN速度很慢字段类型选错手机号用int开头0丢失、时间存字符串小结做数据库设计先想业务实体和关系再写SQL。范式作为参考不是铁律平衡数据冗余、一致性、查询速度。