数据库基础

数据库基础

本文参考——廖雪峰的网站

1 数据库结构

• 层次模型
• 网状模型
• 关系模型，将数据看作一个二维表格，理解使用简单

2 SQL数据库语言

SQL(Structured Query Language)是结构化查询语言的缩写，用来访问和操作数据库系统.

通过学习SQL这一种语言，就可以操作各种不同的数据库。

SQL语言定义了这么几种操作数据库的能力：

• DDL：Data Definition LanguageDDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。
• DML：Data Manipulation LanguageDML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
• DQL：Data Query LanguageDQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

语法特点：
SQL语言关键字不区分大小写！！！为避免不同数据库以及操作系统的区别，约定：SQL关键字总是大写，以示突出，表名和列名均使用小写。

3 MySQL数据库

目前应用最广泛的开源关系型数据库。MySQL本身只是一个SQL接口，其内部包含多种数据库引擎。

四种最常用的：

• InonoDB，支持事务特性
• MyISAM，不支持事务、也不支持外键，优势是访问速度快
• MEMORY，出发点是速度
• ARCHIVE，归档，很多高级功能不再支持

4 事务

事物的四大特性

• A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
• C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
• I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
• D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。
要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务，例如，把上述的转账操作作为一个显式事务：

BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败：

数据库事务是由数据库系统保证的，我们只需要根据业务逻辑使用它就可以。

• 数据库事务具有ACID特性，用来保证多条SQL的全部执行。

隔离级别

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oZfEZIXz-1601952918118)(en-resource://database/2266:1)]

如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。

5 主键和外键

在关系数据库中，关系是通过主键和外键来维护的。

在关系数据库中，一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。例如，students表的两行记录：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2VlaC9Lh-1601952918120)(en-resource://database/2262:1)]

主键

同一个表的所有记录都有相同的字段定义。对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

• 作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。
  非业务字段id为BIGINT NOT NULL AUTO_INCREMENT类型。

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

联合主键：关系数据库实际上还允许通过多个字段唯一标识记录，但尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

外键

在关系数据库中，表与表之间可能存在一对一，一对多，多对一的关系。

一对多

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CtfdRmeG-1601952918122)(en-resource://database/2264:1)]

由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个classes的记录可以对应多个students表的记录。
为了表达这种一对多的关系，我们需要在students表中加入一列class_id，让它的值与classes表的某条记录相对应，这种列称为外键。

• 外键的实现
  通过定义外键约束实现的：

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。

多对多

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系

一对一

如果业务允许，完全可以把两个表合为一个表。

还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。

小结

关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

6 索引

场景：
索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录。

优点：
索引主要目的是提高SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间

建索引：

ALTER TABLE students 
ADD INDEX idx_score (score);

• 索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。

• 可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

索引的结构

• 聚集索引
• 非聚集索引

聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。

• 聚集索引一个表只能有一个（一个表只能有一个物理存储排序），而非聚集索引一个表可以存在多个。

• 索引建立时如果不指明采用的索引结构，则SQL Server系统默认为采用非聚集索引结构。

索引建立的原则

必须索引：
1) 定义主键的数据列一定要建立索引。
2) 定义有外键的数据列一定要建立索引。
建议索引：
3) 对于经常查询的数据列最好建立索引。
4) 对于需要在指定范围内的快速或频繁查询的数据列; 
5) 经常用在WHERE子句中的数据列。
6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。

不要索引：
7) 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
8) 对于定义为text、image和bit的数据类型的列不要建立索引。
9) 对于经常存储的列避免建立索引 
其他：
10) 限制表上的索引数目。

对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。

11) 对复合索引，按照字段在查询条件中出现的频度建立索引。

在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。

这些列根据业务要求具有唯一性约束时，可以给该列添加一个唯一索引。

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

小结

• 通过对数据库表创建索引，可以提高查询速度，但也增加了维护成本。
• 通过创建唯一索引，可以保证某一列的值具有唯一性。
• 数据库索引对于用户和应用程序来说都是透明的。