数据库的相关概念
1、数据库的基本概念
1.1、数据库的基本概念
数据库（Database，简称DB）是按照数据结构来组织、存储和管理数据的仓库。我们也可以将数据存储在文件中或者是内存中，但是内存存储的数据都是临时的，在服务器关机后就会被清除，而文件的读写数据速度相对较慢。所以，我们更多的是使用数据库来存储数据。其实数据库就是一个文件系统。
2、数据库的三种模型（关系模型、层次模型、网状模型）
数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：
- 层次模型
- 网状模型
- 关系模型
层次模型就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树：
网状模型把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网：
关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表：
相比层次模型和网状模型，关系模型理解和使用起来最简单并且也最流行。
3、关系型数据库
所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。我们可以使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。
RDBMS 即关系型数据库管理系统(Relational Database Management System)的特点：
- 1.数据以表格的形式出现
- 2.每行为各种记录名称
- 3.每列为记录名称所对应的数据域
- 4.许多的行和列组成一张表单
- 5.若干的表单组成database
一个关系型数据是由一个个关系表组成的，对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等：
| 名称 | 类型 | 说明 |
|---|---|---|
| INT | 整型 | 4字节整数类型，范围约+/-21亿 |
| BIGINT | 长整型 | 8字节整数类型，范围约+/-922亿亿 |
| REAL | 浮点型 | 4字节浮点数，范围约+/-1038 |
| DOUBLE | 浮点型 | 8字节浮点数，范围约+/-10308 |
| DECIMAL(M,N) | 高精度小数 | 由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算 |
| CHAR(N) | 定长字符串 | 存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串 |
| VARCHAR(N) | 变长字符串 | 存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串 |
| BOOLEAN | 布尔类型 | 存储True或者False |
| DATE | 日期类型 | 存储日期，例如，2018-06-22 |
| TIME | 时间类型 | 存储时间，例如，12:20:59 |
| DATETIME | 日期和时间类型 | 存储日期+时间，例如，2018-06-22 12:20:59 |
上面的表中列举了最常用的数据类型。很多数据类型还有别名，例如，REAL又可以写成FLOAT(24)。还有一些不常用的数据类型，例如，TINYINT（范围在0~255）。各数据库厂商还会支持特定的数据类型，例如JSON。
选择数据类型的时候，要根据业务规则选择合适的类型。通常来说，BIGINT能满足整数存储的需求，VARCHAR(N)能满足字符串存储的需求，这两种类型是使用最广泛的。
3.1、主流关系数据库
目前，主流的关系数据库主要分为以下几类：
- 商用数据库，例如：Oracle，SQL Server，DB2等；
- 开源数据库，例如：MySQL，PostgreSQL等；
- 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
- 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。
4、关系模型
关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。表的每一行称为记录（Record），记录是一个逻辑意义上的数据。表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串''。
和Excel表有所不同的是，关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。
例如，班级表和学生表的关系：一个班级表的每一行对应着一个班级，而一个班级又对应着多个学生，所以班级表和学生表就是“一对多”的关系。反过来，如果我们先在学生表中定位了一行记录，要确定他的班级，只需要根据这条记录的“班级ID”对应的值找到班级表中相同ID对应的记录即可确定班级。所以，学生表和班级表是“多对一”的关系。
在关系数据库中，关系是通过主键和外键来维护的。
5、约束（Constraints）
约束是在表中定义的用于维护数据库完整性的一些规则，用于限制加入表的列的数据，可以防止将错误的数据插入表中。约束是用来保证数据的正确性、有效性和完整性的。若某个约束条件只作用于单独的列，可以将其定义为列约束也可定义为表约束；若某个约束条件作用域多个列，则必须定义为表约束。
SQL Server中的约束用来确保系统的完整性。一般约束可以分为：主键约束、非空约束、唯一约束、外键约束、检查约束、默认约束。
约束可以在创建表时规定（通过 CREATE TABLE 语句），也可以在表创建之后再规定（通过 ALTER TABLE 语句）。
示例：
CREATE TABLE students2( id INT, NAME VARCHAR(20) NOT NULL -- 非空约束 ) ALTER TABLE students NAME VARCHAR(20) NOT NULL; -- 在表创建之后再通过 ddl 语句添加约束也行
5.1、非空约束（NOT NULL）
非空约束即定义某个字段不能为空，如果一条记录该字段为空则无法添加成功。
5.1.1、创建非空约束
非空约束的创建语法：
CREATE TABLE students2( id INT, NAME VARCHAR(20) NOT NULL -- 非空约束 ) ALTER TABLE students MODIFY NAME VARCHAR(20) NOT NULL; -- 在表创建之后再通过DDL语句添加约束也行
5.1.2、删除非空约束
删除非空约束只需通过 DDL 语句修改表字段结构，不添加非空约束即可
ALTER TABLE students MODIFY NAME VARCHAR(20);
5.2、唯一约束（unique）
唯一约束即定义某个字段的值不能重复，如果一条记录的该字段跟其他记录的值重复，则无法添加成功。
5.2.1、创建唯一约束
唯一约束的创建语法：
CREATE TABLE students2( id INT, phone VARCHAR(20) UNIQUE ) alter table students modify phone varchar(20) unique;
注意，MySQL 中唯一约束的字段可以是 null，而且多个记录的该字段可以同时为 null 值，并不会提示重复。
5.2.2、删除唯一约束
删除唯一约束的语法：
ALTER TABLE students DROP INDEX phone;
5.3、主键约束（primary key）
主键约束：非空且唯一。
对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。
由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。比如我们以身份证号作为主键，但是，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。
作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有：
-
自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
-
全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似
8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。
对于大部分应用来说，通常自增类型的主键就能满足需求。我们在students表中定义的主键也是BIGINT NOT NULL AUTO_INCREMENT类型。（如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录）
5.3.1、联合主键（不建议使用）
关系数据库实际上还允许通过多个字段唯一标识记录，即将两个或更多的字段都设置为主键，这种主键被称为联合主键。对于联合主键，允许一列有重复，只要不是所有主键列都重复即可。
没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。
5.3.2、创建主键
创建主键的语法：
CREATE TABLE stu( id int PRIMARY KEY, -- 创建表时指定主键。可以通过 auto_increment 关键字来指定主键自增长：id int PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ) ALTER TABLE stu MODIFY id INT PRIMARY KEY; --先创建表，后面再添加主键
5.3.3、删除主键
删除主键的语法：
ALTER TABLE stu DROP PRIMARY KEY;
5.4、外键约束（foreign key）
在一个表中，通过某个字段，可以把数据与另一张表关联起来，这个字段就称为外键。比如在students表中，通过class_id的字段可以关联到classes表，可以确定某个学生属于哪个班级，那么 class_id 就可以称为外键。
关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库语法来进行定义设置，也可以不设置约束，仅依靠应用程序的逻辑来保证，这样速度会更快。
5.4.1、创建外键
比如：student 表
classes 表：
我们希望 student 表能和 classes 表关联起来。可以将 student 表中的 class_id 定义为外键，关联 classes 表中的主键 id。
通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录。同样的，如果 classes 表中 id= 99 有被 student表的一些记录关联到，则 classes 表中的 id=99 这条数据无法被删除，只有先全部删除 student 表中关联到 classes 的 id=99 的数据后，才能删除 classes 表的 id=99 的记录。外键不能是关联的主键所没有的值，但外键可以是 null。
定义外键的语法：
create table 表名( id int, 外键列 int, ..., constraint 外键名称 foreign key(外键列) references 其他表(其他表的列) ) ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列) REFERENCES 其他表(其他表的列); -- 先建表，后添加外键
外键约束的名称可以任意定义，通过 foreign key 可以指定某个字段作为外键，references 其他名(字段名) 指定这个外键将关联到另一个表的某列（一般是另一个表的主键）。
需要设置外键的表一般称为外键表，被关联的表称为主键表（因为一般是关联主键）。
代码示例：
CREATE TABLE stu( id INT PRIMARY KEY, name VARCHAR(20), class_id INT, CONSTRAINT stu_class_fk FOREIGN KEY (class_id) REFERENCES classes(id) -- 定义 class_id 为外键，并且关联 classes 表的 id 字段 )
由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。
有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度。
5.4.2、删除外键
要删除一个外键约束，也是通过ALTER TABLE实现的：
ALTER TABLE students DROP FOREIGN KEY stu_class_fk;
注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。
5.4.3、级联更新（on update cascade）
当我们设置了外键，即在外检表中将某个字段设置为外键，并且关联主键表中的主键，如果此时我们修改主键表中和外键表进行关联的字段(一般是主键表的主键，mssql好像必须是主键)，如果我们没有设置级联更新，那么这个时候会提示不能更新，因为外键表还有数据正在和这条数据进行关联，但是如果设置了级联更新，那么外键表的数据会自动帮我们更新。
ALTER TABLE stu ADD CONSTRAINT stu_classes_fk FOREIGN KEY (class_id) REFERENCES classes(id) on update cascade; -- 外键级联更新
此时如果更新 classes 表中的 id 字段，那么 stu 表中的 class_id 字段会自动随之更新。
5.4.4、级联删除（on delete cascade）
当我们没有对键加级联删除的时候，删除主键表中的数据（外键表有引用的数据）时，会报错，不能删除，必须先把相关联的外键数据删除了，才能删除主键表的数据，但如果新建外键的时候设置了级联删除，那么当我们删除主键表的数据时，数据库就会自动帮我们把相关联的外键表数据删除掉。
ALTER TABLE stu ADD CONSTRAINT stu_classes_fk FOREIGN KEY (class_id) REFERENCES classes(id) on delete cascade; -- 级联删除。可以同时使用级联更新和删除：on update cascade on delete cascade
此时如果删除 classes 表中的某个记录（即删除了一个 id 值），那么 stu 表中所有 class_id 是被删除的 id 值的记录都会被自动删除掉。
定义级联操作后，修改一条记录可能导致多条记录被修改，所以需要谨慎使用。
6、索引（INDEX）
在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。
例如，如果要经常根据 student 表的 score 列进行查询，就可以对 score 列创建索引：
ALTER TABLE students ADD INDEX idx_score (score); -- 使用列score创建一个名称为idx_score的索引。索引的名称是任意的 ALTER TABLE students ADD INDEX idx_name_score (name, score); -- 索引如果有多列，可以在括号里依次写上
索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果某列存在大量相同重复的值，那么对该列创建索引也没有什么意义。
可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。
对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。
数据库索引对于用户和应用程序来说都是透明的，即无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别。当我们在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率，如果没有索引，查询也能正常执行，只是速度会变慢。因此，索引可以在使用数据库的过程中再逐步优化。
6.1、唯一索引
在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。
例如，我们假设students表的name不能重复：
ALTER TABLE students ADD UNIQUE INDEX uni_name (name); -- 添加唯一索引
7、多表关系设计
在设计表时，应该把该表本身的基本字段考虑进去，然后再考虑和其他表之间的关系，考虑是否需要建立外键或者建中间表。
多表之间的关系有：一对一、一对多、多对多，不同关系在设计表时的思路如下：
- 在设计一对一关系时，只需在任意一方建立外键，且该外键需建立唯一约束，然后指向另一方的主键即可。比如：学生和身份证表，一个学生只有一个身份证，一个身份证也只属于一个学生，此时在任意一方比如学生建立外键且在该外键添加唯一约束，然后指向身份证表的主键即可。一对一关系用的比较少，因为实际上可以合成一张表。
- 在设计一对多关系时，我们可以在“多”的一方建立外键，关联“一”的一方的主键。比如：员工和部门表，一个员工只属于一个部门，一个部门有多个员工，此时我们可以在员工表建立 “部门ID” 字段作为外键，指向部门表的主键。
- 在设计多对多关系时，我们应该建立一个中间表，这个中间表应该至少包含两个字段，且这两个字段作为外键分别指向多表关系中两张表的主键。比如学生和课程表，一个学生可以选多门课程，一门课程也可以被多个学生选择，此时我们可以建立一个中间表，该表有两个字段，分别指向学生表和课程表的主键。
一般我们设计表时，可以先考虑表中的必要字段，然后再考虑表和表之间的关系，添加外键等其他字段。
