大数据【六十六】Linux_Mysql【三】-- 表、记录、字段 /表的关联 /约束 /索引

一、什么是关系型数据库

    关系型数据库以行和列的形式存储数据，以便于用户理解。这一系列的行和列被称为表，一组表组成了数据库。表与表之间的数据记录有关系。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。

 

二。表、记录、字段

实体-联系（E-R）模型中有三个主要概念是：实体集、联系集、属性。

 

一个实体集对应于数据库中的一个表table，一个实体则对应于数据库表中的一行，也称为一条记录。一个属性对应于数据库表中的一列，也称为一个字段。

三。表的关联

四种：一对一关联、一对多关联（多对一关联）、多对多关联、自我引用

（1）一对一关联（one-to-one）

 

（2）一对多关系（one-to-many）

 

（3）多对多（many-to-many）

比如在常见的订单管理数据库当中“产品”表和“订单”表之间的关系。单个订单中可以包含多个产品。另一方面，一个产品可能出现在多个订单中。因此，对于“订单”表中的每条记录，都可能与“产品”表中的多条记录对应。此外，对于“产品”表中的每条记录，都可以与“订单”表中的多条记录对应。这种关系称为多对多关系，因为对于任何产品，都可以有多个订单，而对于任何订单，都可以包含许多产品。

要表示多对多关系，您必须创建第三个表，该表通常称为联接表，它将多对多关系划分为两个一对多关系。将这两个表的主键都插入到第三个表中。例如，“订单”表和“产品”表有一种多对多的关系，这种关系是通过与“订单明细”表建立两个一对多关系来定义的。一个订单可以有多个产品，每个产品可以出现在多个订单中。

（4）自我引用(Self reference)

 

四。约束（重要）

     数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据的完整性要从以下四个方面考虑：

1. 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
2. 域完整性（Domain Integrity）：例如：年龄范围0-120
3. 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
4. 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等

为了达到这个目标，我们要对数据库表的设计增加约束，根据约束的作用范围分，主要是表级约束和列级约束。

1. 列级约束只能作用在一个列上，跟在列的定义后面定义，与列的定义用空格分隔
2. 表级约束可以作用在多个列上，不与列一起，而是单独定义，格式：[ CONSTRAINT <约束名> ] <约束类型>

根据约束的特点，分为几种：

1. 键约束：主键约束、外键约束、唯一键约束
2. Not NULL约束：非空约束
3. Check约束：检查约束
4. Default约束：默认值约束

（一）键约束之主键约束

 主键：Primary key，简称PK，数据库主键作用保证实体的完整性，可以是一个列或多列的组合。

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值，如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

1. 每个表最多只允许一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
2. MySQL的主键名总是PRIMARY。
3. 当创建主键约束时，MySQL默认会在所在的列和列组合上建立对应的主键索引。
4. 删除主键时，也会直接删除主键索引

（二）键约束之唯一键约束

唯一键：Unique key，简称UK

1. 同一个表可以有多个唯一约束。
2. 唯一约束可以是某一个列的值唯一，也可以多个列组合值的唯一。
3. 建立唯一约束可以在列级别创建，也可以在表级别上创建。
4. 在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。
5. MySQL会给唯一约束的列上默认创建一个唯一索引。
6. 删除唯一键只能通过删除唯一索引的方式删除

（三）键约束之外键约束

   外键：Foreign key，简称FK

　　外键是表与表之间的某种约定关系，由于这个关系的存在，我们能让表之间的数据更加完整，关联性更强。

1. 外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
2. 建立外键约束只能在表级别上创建。
3. 在创建外键约束时，如果不给外键约束名称，外键也有默认名，也可以指定外键名。
4. 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。
5. 删除外键时，关于外键列上的普通索引需要单独删除。

补充：【转】大数据【八十】Linux_Mysql【十】-- Mysql外键是什么？有哪些用处

注意：

1. 从表的外键列，在主表中引用的只能是主键或唯一键约束的列。
2. 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样
3. 从表的外键值必须"在主表中能找到"或者为空，从而约束了从表的外键值。
4. 当主表的记录被从表参照时，主表中被参考记录的删除和更新也会受到限制。
   1. （1）默认情况下，主表和从表是严格依赖关系RESTRICT。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。在MySQL中，外键约束是被立即检查的，所以NO ACTION和RESTRICT是同样的。拒绝对父表的删除或更新操作。
   2. （2）但是有一种是级联删除：
   3. ON DELETE SET NULL(级联置空)：当外键设置了SET NULL,当主表的相关记录删除时，从表对应的字段改为NULL。
   4. ON DELETE CASCADE(级联删除)：当外键设置了CASCADE（级联），当主表的相关记录删除时，从表对应的行都删除了。
   5. 对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式

（四）非空约束

• NOT NULL 非空约束，规定某个字段不能为空
• 非空约束只能出现在表对象的列上，即只能是列级约束。

（五）检查约束

• 注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果；
• 例如：age tinyint check(age >20)  或 sex char(2) check(sex in(‘男’,’女’))

（六）Default缺省约束

     default：默认值，在插入数据时某列如果没指定其他的值,那么会将默认值添加到所有的新记录。

五。索引

    索引：索引是对数据库表中一列或多列的值进行排序的一种结构。索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。由此可知，索引是要消耗数据库空间的。而约束是一种逻辑概念。

• 索引好处：加快了查询速度(select )
• 索引坏处: 降低了增,删,改的速度(update/delete/insert)，增大了表的文件大小(索引文件甚至可能比数据文件还大)

例如：一本字典，如何快速找到某个字，可以给字典加目录，对数据库来说,索引的作用即是给"数据"加目录。

设有N条随机记录,不用索引,平均查找N/2次,那么用了索引之后呢。如果是btree(二叉树)索引，，如果是hash(哈希)索引，时间复杂度是1。

MySQL提供多种索引类型供选择：

1. 普通索引：
2. 唯一性索引：
3. 主键索引：只有一个主键索引
4. 全文索引：MySQL5.X版本只有MyISAM存储引擎支持FULLTEXT，并且只限于CHAR、VARCHAR和TEXT类型的列上创建。

MySQL的索引方法：

1. HASH
2. BTREE

MySQL中多数索引都以BTREE的形式保存。

索引的使用原则：

（1）不过度索引

（2）索引条件列(where后面最频繁的条件比较适宜索引)

（3）索引散列值,过于集中的值不要索引，例如:给性别"男","女"加索引,意义不大

补充： 【转】大数据【八十一】Linux_Mysql【十一】-- Mysql索引的作用