第6章 数据库设计

2018-03-02 00:10:10 《PHP与MySQL动态网站开发》笔记 阅读: 134 评论:

[TOCM]

数据库设计流程

  1. 定义应用程序作用,设计原始表
  2. 指定主键
  3. 第一范式(1NF)
  4. 第二范式(2NF)
  5. 第三范式(3NF)
  6. 审查设计

指定主键

主键满足的规则:

  • 有值(非NULL)
  • 不变值(永不改变)
  • 在表中唯一(唯一性)
  • 没有满足以上规则的列则创建一个

主键使用整数可以提高数据库性能

外键:表A中主键在表B中的代表

第一范式(1NF)

对表的横向约束,规则:

  • 每一列只有一个值,不满足则打散成多列
  • 不能有相关数据重复列,不满足则将相关的列转化成单独的表

第二范式(2NF)

对表的纵向约束,规则:

  • 满足1NF
  • 表中非键列必须依赖主键(非键列的存在不依靠主键存在,即不存在一对多关系),不满足则创建新表

第三范式(3NF)

对表的纵向约束,规则:

  • 满足2NF
  • 表中非键列必须相互独立(非键列直接没有关系),不满足则创建新表

审查设计

  • 确认数据库中保存了足够的数据
  • 确定各表列的类型和名字

索引

  • 索引用户提高数据的读取速度,但减慢了数据的修改速度
  • 索引可以设置在一列或多列上

适合设置的列:

  • 查询中WHERE频繁的列
  • 查询中ORDER BY频繁的列

不适合设置的列:

  • 允许为空的列
  • 字段范围小的列,如ENUM枚举类型少的列

索引类型:

  • INDEX 普通索引
  • UNIQUE 每行的索引值唯一
  • FULLTEXT 用于FULLTEXT查找
  • PRIMARY KEY 主键列索引,特殊的UNIQUE索引

创建索引的方式:
-修改表结构:

ALTER table_name
INDEX_TYPE [index_name] (`column1_name1`(length)[, `column2_name2`(length), ...])

创建表时指定:

CREATE `table_name`(
    ...,
    NDEX_TYPE [index_name] (`column1_name1`(length)[,` column2_name2`(length), ...])
)

不指定索引名index_name时则默认使用列名作为索引名。

一般怎么添加索引:

  • 为主键添加PRIMARY KEY索引
  • 为列值不重复的列添加UNIQUE索引
  • 添加FULLTEXT
  • WHERE和ORDER BY经常中使用的列添加INDEX索引
  • JOIN中经常使用的添加INDEX索引

表类型(表引擎)

显示可用的表引擎:SHOW ENGINES;

  1. MyISAM类型:处理SELECT和INSERT很快; 但不能处理事务; 缺少行级锁定,很容易损坏并且崩溃时数据容易丢失。
  2. InnoDB类型:可用处理事务; 并且处理UPDATE很快; 支持外检约束和行级锁定; 不支持FULLTEXT索引; InnoDB引擎比MyISAM慢并且需要更多的磁盘空间。
    各种类型的表的操作是相同的。一般MySQL表引擎默认为MyISAM或InnoDB

指定引擎的方法:

创建的时指定:

CREATE TABLE `table_name`(
    `column1_name` descript,
    `column2_name` descript,
    ...,
) ENGINE = type;

修改表类型:

ALTER TABLE `table_name` ENGINE = type;

为各种表使用合适的引擎能够提高性能。

一般怎么设定引擎:

  • 为需要FULLTEXT查找的表设定MyISAM
  • 为需要事务的表设定InnoDB
  • 其它表使用默认

MySQL字符编码

显示支持的编码:SHOW CHARACTER SET;
collation列表示编码的比较规则,后缀ci表示不区分大小写,cs区分大小写,bin二进制比较。

显示各种编码的比较规则:SHOW COLLATION;

各种的编码的字符比较价格不相同。一般使用utf-8编码。

每个数据库、表、字符串列都可以合作编码和比较规则,一般使用默认即可。

php脚本编码须和MySQL编码一致。

外键约束

外键联系了两张表,外键的主键所在的表称主表,外键所在的表称子表。主键约束设定了当约束被破坏时的规则。

创建外键约束,在CREATE TABLE或ALTER TABLE句中添加:

FOREIGN KEY (item_name) REFERENCES table_name(column_name)
[ON DELETE action]
[ON UPDATE action]

其中item_name是表中外键列,table_name是主键列所在表,column_name是主键列。

aciton的取值:

  • RESTRICT、NOT ACTION: 无动作(默认值)
  • SET DEFAULT: 无作用
  • SET NULL: 删除父记录导致子表中相应的外键别设置为NULL。如果该列指定了NOT NULL则产生一个错误。
  • CASCADE: 同步变化,删除父记录时会删除外键的所在的记录。
    父表和字表必须都是InnoDB类型才能使用外键约束。

功能
最新评论