数据库&SQL基础学习总结

Reference Website：https://www.liaoxuefeng.com/wiki/1177760294764384

一、关系数据库概述

随着应用程序的功能越来越复杂，数据量越来越大，如何管理这些数据就成了大问题：
1.读写文件并解析出数据需要大量重复代码；
2.从成千上万的数据中快速查询出指定数据需要复杂的逻辑。
数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：
1.层次模型就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树
2.网状模型把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网
3.关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表

对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等：

目前，主流的关系数据库主要分为以下几类：
商用数据库，例如：Oracle，SQL Server，DB2等；
开源数据库，例如：MySQL，PostgreSQL等；
桌面数据库，以微软Access为代表，适合桌面应用程序使用；
嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

什么是SQL？SQL是结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。不同的数据库，都支持SQL，这样，我们通过学习SQL这一种语言，就可以操作各种不同的数据库。
总的来说，SQL语言定义了这么几种操作数据库的能力：
1.DDL：Data Definition Language
DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。
2.DML：Data Manipulation Language
DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
3.DQL：Data Query Language
DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

和其他关系数据库有所不同的是，MySQL本身实际上只是一个SQL接口，它的内部还包含了多种数据引擎，MySQL接口和数据库引擎的关系就好比某某浏览器和浏览器引擎（IE引擎或Webkit引擎）的关系。对用户而言，切换浏览器引擎不影响浏览器界面，切换MySQL引擎不影响自己写的应用程序使用MySQL的接口。命令行程序mysql实际上是MySQL客户端，真正的MySQL服务器程序是mysqld，在后台运行。

关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表，和Excel表有所不同的是，关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。
1.表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
2.表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

二、关系模型

在关系数据库中，关系是通过主键和外键来维护的。
1.主键
对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。主键是关系表中记录的唯一标识。选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。我们一般把这个字段命名为id。可以使用多个列作为联合主键，但联合主键并不常用。
2.外键
在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。
通过一个表的外键关联到另一个表，我们可以定义出一对多和多对一关系。
多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。
一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。
关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。
3.索引
索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。
可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。
通过对数据库表创建索引，可以提高查询速度。
通过创建唯一索引，可以保证某一列的值具有唯一性。
数据库索引对于用户和应用程序来说都是透明的。

三、查询数据

基本查询 条件查询投影查询 排序 分页查询
聚合查询
COUNT(*)表示查询所有列的行数
SUM 计算某一列的合计值，该列必须为数值类型
AVG 计算某一列的平均值，该列必须为数值类型
MAX 计算某一列的最大值
MIN 计算某一列的最小值
GROUP BY 自动分组

多表查询
SELECT * FROM <表1> <表2>
这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。
连接查询

SELECT * FROM <表名> SELECT * FROM students;
SELECT * FROM <表名> WHERE<条件表达式>
SELECT * FROM students WHERE score >= 80;
SELECT * FROM students WHERE score >= 80 AND gender = ‘M’; OR NOT
SELECT 列1, 列2, 列3 FROM … ORDER BY … DESC LIMIT OFFSET
SELECT * FROM <表名>GROUP BY 列名
SELECT * FROM <表1> <表2> SELECT * FROM <表1> INNER JOIN <表2> ON <条件…>

四、修改数据

关系数据库的基本操作就是增删改查，即CRUD：Create、Retrieve、Update、Delete

SELECT：查询记录；
INSERT：插入新记录；
UPDATE：更新已有记录；
DELETE：删除已有记录。

INSERT INTO <表名> (字段1, 字段2, …) VALUES (值1, 值2, …)，(值1, 值2, …);
UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE …;
DELETE FROM <表名> WHERE…;

五、事务

把多条语句作为一个整体进行操作的功能，被称为数据库事务。
数据库事务具有ACID这4个特性：
A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。
对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。
要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务。
对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

1.Read Uncommitted是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。
2.在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。
3.在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。
幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。
4.Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。
虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。
如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。