【MySQL】关系模型__主键 外键 索引笔记

关系模型

关系模型是一种数据库模型，它以二维表格的形式来组织数据，并且每一行都有一个唯一的标识，称为主键。每一列都代表一个特定的字段。这种模型的主要优点是它的简单性和灵活性。在关系模型中，数据可以通过主键、外键和索引进行关联。关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。

• 表的每一行称为记录，记录是一个逻辑意义上的数据。
• 表的每一列称为字段，同一个表的每一行记录都拥有相同的若干字段。
• 字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串’'。

与Excel表有所不同的是，关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。

1. 一对多（One-to-Many）：在一对多关系中，一个表中的记录可以在另一个表中有多个相关记录。例如，一个作者可以写多本书，所以在作者表和书籍表之间存在一对多的关系。
2. 多对一（Many-to-One）：多对一关系是一对多关系的反向。在这种关系中，多个记录可以关联到另一个表的单个记录。例如，多本书可以由一个作者编写，所以在书籍表和作者表之间存在多对一的关系。
3. 一对一（One-to-One）：在一对一关系中，一个表中的记录只能在另一个表中有一个相关记录。例如，每个人都有一个社会保障号，所以在人员表和社会保障号表之间存在一对一的关系。

主键

在MySQL中，主键（Primary Key）是一个或多个字段，它们唯一标识数据库表中的每一行记录。主键的值必须是唯一的，并且不能为NULL。每个表只能有一个主键。

主键的主要作用是：
1、提供一种在表中快速访问特定记录的方法。
2、为表中的其他行提供一个引用点，以创建表与表之间的关系。

对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响

选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。
因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。

在创建表时设置主键：

CREATE TABLE Employees (
    ID INT NOT NULL,
    Name VARCHAR(100),
    PRIMARY KEY (ID)
);

在表创建后添加主键：

ALTER TABLE Employees
ADD PRIMARY KEY (ID);

联合主键

联合主键（Composite Key）是由两个或更多的列组成的，用于唯一标识数据库表中的每一行记录。这些列的组合必须是唯一的，但是单独的列不必是唯一的。

对于联合主键，允许一列有重复，只要不是所有主键列都重复即可：

id_num		id_type		other columns...
1			A			...
2			A			...
2			B			...

如果我们把上述表的id_num和id_type这两列作为联合主键，那么上面的3条记录都是允许的，因为没有两列主键组合起来是相同的。

没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

外键

在MySQL中，外键（Foreign Key）是一个或多个字段，它们在一个表中引用另一个表的主键。外键用于建立和强制执行两个表之间的关系。

外键的主要作用是：

1、保证数据的一致性和完整性。
2、允许在表之间进行导航，以获取或操作数据。

在创建表时设置外键：

CREATE TABLE Orders (
    OrderID INT NOT NULL,
    CustomerID INT,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

在这个例子中，CustomerID字段被设置为外键，它引用Customers表的CustomerID字段。

在表创建后添加外键：

ALTER TABLE Orders
ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

在这个例子中，我们向已存在的Orders表添加了外键CustomerID，它引用Customers表的CustomerID字段。

要删除一个外键约束，也是通过ALTER TABLE实现的:

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列是通过**DROP COLUMN ** 实现的。

多对多

在关系数据库中，多对多关系是指多个记录在一个表中可以关联到另一个表中的多个记录，反之亦然。例如，一个学生可以选修多门课程，同时一门课程也可以被多个学生选修。

直接在两个表之间创建多对多关系在技术上是不可能的，因此，我们通常通过引入第三个表（称为联接表或桥接表）来实现多对多关系。这个联接表通常包含两个外键，分别引用原来的两个表。

例如，如果我们有Students和Courses两个表，我们可以创建一个Enrollments联接表，其中包含student_id和course_id两个外键：

CREATE TABLE Enrollments (
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

在这个例子中，Enrollments表的每一行都代表一个学生选修一门课程的情况，student_id和course_id的组合是唯一的（因此它们被设置为联合主键）。这样，我们就可以通过Enrollments表来查询学生选修的课程，或者查询选修某门课程的所有学生

一对一

在关系数据库中，一对一关系是指一个表中的记录与另一个表中的记录有且只有一个对应关系。例如，一个人和他的社会保障号之间就是一对一的关系。

在实现一对一关系时，通常会在一个表（称为从表）中设置一个字段作为外键，这个外键引用另一个表（称为主表）的主键。并且，这个外键还需要被设置为唯一的，以确保每个主表的记录只能与从表中的一个记录关联。

例如，如果我们有Persons和SSNs两个表，我们可以在SSNs表中设置一个person_id的外键，这个外键引用Persons表的person_id主键，并且被设置为唯一的：

CREATE TABLE SSNs (
    ssn_id INT NOT NULL,
    person_id INT,
    PRIMARY KEY (ssn_id),
    FOREIGN KEY (person_id) REFERENCES Persons(person_id),
    UNIQUE (person_id)
);

在这个例子中，SSNs表的每一行都代表一个人和他的社会保障号的关系，person_id字段是唯一的，这样就确保了每个人只能有一个社会保障号。

索引

在数据库中，索引是一种数据结构，它可以帮助数据库系统更快地访问数据。

在数据库中，索引通常在一个或多个列上创建，用于快速查找表中的数据。索引可以显著提高查询性能，特别是在大型数据库中。

但是，索引也有其代价。首先，索引需要占用存储空间。其次，当你插入、更新或删除表中的数据时，索引也需要被更新，这可能会降低写操作的性能。

在MySQL中，你可以使用CREATE INDEX命令来创建索引，例如：

CREATE INDEX idx_lastname
ON Employees (LastName);

在这个例子中，我们在Employees表的LastName列上创建了一个名为idx_lastname的索引。这样，当我们查询特定姓氏的员工时，数据库可以使用这个索引来快速找到相关的记录。

对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

唯一索引

唯一索引是一种特殊类型的数据库索引，它要求索引列的所有值都是唯一的。这意味着你不能在唯一索引的列中插入重复的值。

唯一索引不仅可以提高查询性能，还可以帮助保证数据的完整性。例如，如果你有一个users表，你可能希望确保每个用户的电子邮件地址都是唯一的。你可以在email列上创建一个唯一索引来实现这个需求。

在MySQL中，你可以使用CREATE UNIQUE INDEX命令来创建唯一索引，例如：

CREATE UNIQUE INDEX idx_email
ON Users (Email);

在这个例子中，我们在Users表的Email列上创建了一个名为idx_email的唯一索引。这样，当我们尝试插入一个已存在的电子邮件地址时，数据库会返回一个错误。

通过UNIQUE关键字我们就添加了一个唯一索引：

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

可以只对某一列添加一个唯一约束而不创建唯一索引：

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

这种情况下，name列没有索引，但仍然具有唯一性保证。

小结

• 主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。
• 可以使用多个列作为联合主键，但联合主键并不常用。
• 关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。
• 通过对数据库表创建索引，可以提高查询速度。
• 通过创建唯一索引，可以保证某一列的值具有唯一性。
• 数据库索引对于用户和应用程序来说都是透明的。