数据库 概述（mysql为例）

这里主要说明一下本人对于数据库理论的理解，以及如何体现sql语句中。主要针对关系数据库。

什么叫做关系型数据库？

计算机中很多实现都是有在一定的数学模型基础上建立起来的。关系数据库也是如此。这个过程更像是这样的（如图1）：
图1：（关系数据库产生过程）

理解关系型 数据库，就在 将关系型数据库 放到 其整个产生过程中去理解。这里主要介绍现实世界，数学建模，以及关系模型到实现过度这3点内容。（三者的关系如图2.）

图2：（现实世界 ，数学模型，数据库之间关系简要说明）

个体 && 环境

　　当我们理解、刻画一个事物的时候，往往会将其放到一定的环境中。环境本质上是一种与待研究事物 相关联的 集合（其他事物，关系）。这是理解现实世界的一个基本的思想，有了这个思想，我们就需要通过数学的方式将其量化出来。假设，我们知道一个事物person1,那么我们应该如何去刻画这个事物呢？我们会刻画出person1 所属于的集合P ，然后还有刻画person 与其他事物之间的关系，符号化为（person1,thing）。这样子就数学化 说明了person 与周围事物之间的关系。

　　建立数学模型之后，我们就可以进行相应的计算机处理了。我们需要我们搜集到的现实的信息，通过某种方式，存储下来。数据结构就是为了表示不同的关联关系，这里顺便说说数据结构能够带来的好处：

　　因为计算机是有一套运作机制的（CPU,内存，输入，输出等），这些机制决定了计算机擅长什么，不擅长什么。所以我们必须将现实生活中的数据转化为计算机中的数据，同时也要方便计算机进行处理（适合计算机的特点，特长）。即数据的组织方式应该适应于计算机的操作，只有这样才能提升效率。举个简单的例子，在现实生活中，你有两个物品A，B。那么在计算机中可以进行哪些存储方案：

【方案1：损失关系】只把A,B放入到存储中。这样子做会把关系（“A，B都属于我”）给损失掉。【方案2：保存关系】把A，B存储，同时把关系也进行存储。即，A，B, (A,B)【方案3：计算机特点利用】在A,B存储在相邻的物理位置，利用在存储中的位置信息（计算机中地址）来进行刻画关系 。（在方案2的基础上，有了更加的改进）

　　上面的三种方案体现为三种不同的数据结构。方案3比方案2要快，因其利用了计算机的优势。

　　关系数据库也可以看作是数据结构的一种表现形式，在这种结构中，能够更加方便人的操作（查询，插入），更加方便机器的运作。

　　关系模型就是指二维表格模型,因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。

分类与模式

　　为了更好的，统一的认识事物，我们将事物进行划分为类。类此处可以理解为符合某种共同特征的集合。

　　现实中的分类如何刻画到数学中呢？（见文章：聚类是什么？【这里面有相应得说明】）

　　分类体现了共性与个性。我们应该如何将这种现象刻画到计算机中呢？模式，实例。

【模式】类，共性【实例】个体，个性

关系模型

关系数据库 是建立在关系模型的基础之上的。更多关于关系模型的信息见：（【百度百科】关系模型）

综上所述，前面所说的内容主要是为了说明关系型数据库存在的意义，其合理性以及其模型基础。

关系数据库工作原理

参考：
关系型数据库工作原理简述

事务

何为数据库事务？

【逻辑工作单元：（结果角度）不可拆分】数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

【作用】事务处理可以确保除非事务性单元内的所有操作都成功完成*，否则不会永久更新面向数据的资源*。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。

【事务特性】一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。

注意：

与 并发中的一些概念进行区分。（比如，原子性）

事务应用的场景（举例）

事务的特性（ACID）

【概述】

原子性（Atomic）

事务的原子性指的是，事务中包含的程序作为数据库的逻辑工作单位，它所做的对数据改操作要全部执行，要么全部不执行。这种特性称为原子性。

使用原子性的情况，是指一些指令之间关系十分密切，其中的约束条件要求他们必须一同改变，而原子中的封装保证了，这些语句从一定程度上维护这种约束条件。（举个简单的例子：在银行转账的例子中，A的付款 = B的收款 是本例中的约束条件，我们将其A付款与B收款封装为原子操作，是因为这两者在语义上是不可分割的）

注意：

【原子性：非多线程】这里的原子性，并未涉及到多线程。可以理解为单个线程执行一系列动作（封装成了原子），原子性指的是这一系列动作，必须全部完成或者全部完成。在原子内部的某条指令处出现了故障，故障破坏了原子性，这时候再次开机的时候就需要数据库自动将数据库恢复，以保证原子性要求。（数据库不允许停在原子内部的某条指令）

一致性（Consistent）

含义：

一致性，是指 数据处于一种有意义的状态，这种状态是语义上的而非语法上的。（举例：A向B转账，A的钱减少了，B的钱却没有增加，那么此处认为数据处于不一致的状态）

【语义的表示：不变性条件】还是上面转账的例子，如果将其表达为计算机可理解的形式，一致性就可以表达为一种约束条件： A减少的值 = B增加的值 。无论何种场景下，都要符合这个条件。（此处的约束的条件，就是我们实现时考虑的需求，而这些需求则是我们程序需要实现的语义）

隔离性（Isolation）

指并发的事务是相互隔离的。

【多线程情况下的性质】隔离性 主要是 为了解决并发场景中的一些问题。

隔离性 特点 与锁：

数据库事务有不同的隔离级别，不同的隔离级别对锁的使用是不同的，锁的应用最终导致不同事务的隔离级别。按照数据库的理论，隔离级别可以被分为以下四种，从上向下依次增强：(下面说明来自知乎：原文地址)

【读不提交，造成脏读(Read Uncommitted)】： 一个事务中的读操作可能读到另一个事务中未提交修改的数据，如果事务发生回滚就可能造成错误。

例子1：A打100块给B，B看账户，这是两个操作，针对同一个数据库，两个事物，如果B读到了A事务中的100块，认为钱打过来了，但是A的事务最后回滚了，造成损失。

例子2：游戏当中剩余挑战次数，读取次数是个事务中的操作，如果写同时并发，则可能导致两个事务同时修改数据，同时读到数据是1，但是当再次修改的时候就一个事务修改为0，另一个事务修改为1，就可能造成误判。避免这些事情的发生就需要我们在写操作的时候加锁，使读写分离，保证读数据的时候，数据不被修改，写数据的时候，数据不被读取。从而保证写的同时不能被另个事务写和读。

【读提交(Read Committed)】：我们加了写锁，就可以保证不出现脏读，也就是保证读的都是提交之后的数据，但是会造成不可重读，即读的时候不加锁，一个读的事务过程中，如果读取数据两次，在两次之间有写事务修改了数据，将会导致两次读取的结果不一致，从而导致逻辑错误。

【Repeatable Read（可重读）】：解决不可重复读问题， 一个事务中如果有多次读取操作，读取结果需要一致（指的是固定一条数据的一致，幻读指的是查询出的数量不一致）。 这就牵涉到事务中是否加读锁，并且读操作加锁后是否在事务commit之前持有锁的问题，如果不加读锁，必然出现不可重复读，如果加锁读完立即释放，不持有，那么就可能在其他事务中被修改，若其他事务已经执行完成，此时该事务中再次读取就会出现不可重复读，所以读锁在事务中持有可以保证不出现不可重复读，写的时候必须加锁且持有，这是必须的了，不然就会出现脏读。Repeatable Read（可重读）也是MySql的默认事务隔离级别，上面的意思是读的时候需要加锁并且保持

注意：【在事务执行期间一直持有读锁】 需要持有读锁一段时间。（“持有”）

Serializable（可串行化） ： 解决幻读问题， 在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。幻读是由于并发事务增加记录导致的，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

持久性（Duration）

事务的实现

参考：
数据库事务原子性、一致性是怎样实现的？
数据库的事务隔离与锁机制的差别和联系
【维基百科】数据库事务

并发控制 - 锁

乐观锁 & 悲观锁

乐观锁 ： 版本控制 + 版本验证

悲观锁 ： 互斥锁

读锁 & 写锁

读锁 ： 加读锁后，其他线程（事务）可加读锁，但不可以加写锁。（其他线程只能读，不能写）

写锁：相当于互斥锁，其他线程的读、写锁都不可添加。（其他线程不能访问（不能读写））

数据库锁机制
深入理解乐观锁与悲观锁
【维基百科】乐观并发控制

并发控制 - 锁的粒度

表锁

行锁

细节操作

选择

limit使用

1、查询第一行记录：
select * from table limit 1

2、查询第n行到第m行记录
select * from table1 limit n-1,m-n;
SELECT * FROM table LIMIT 5,10；返回第6行到第15行的记录
select * from employee limit 3,1; // 返回第4行

3、查询前n行记录
select * from table1 limit 0,n;
或
select * from table1 limit n;

4、查询后n行记录

select * from table1 order by id desc dlimit n；//倒序排序，取前n行 id为自增形式

5、查询一条记录($id)的下一条记录

select * from table1 where id>$id order by id asc dlimit 1

6、查询一条记录($id)的上一条记录

select * from table1 where id

多表查询

示例1

SELECT app_category,topic_number, AVG(num_downloads) FROM((SELECT app_category,docid,version_code,COUNT(*) as topic_number,num_downloads FROM topic_app GROUP BY app_category,docid,version_code) as t1)GROUP BY app_category,topic_number;

常见错误

【mysql】Every derived table must have its own alias

多表查询（UNION）

初步介绍MySQL中的集合操作
MYSQL差集 交集 并集

插入

mysql忽略主键冲突、避免重复插入的几种方式

插入多个值

insert 插入多条语句

增加一个字段

alter table 表名 add column sex char (8); 如果要在哪个字段后面加可以：after 某字段

查询优化

where 语句

在mysql中，where语句是自左向右执行的。当数据量很大（达到数百万级别时），需要考虑执行顺序。相关建议：

• 有索引情况下，尽量和索引相结合，且索引尽量放前面。
• 排除越多的条件放到第一个。

on 语句

与where语句相似。
当其与 left join 相结合时，on语句的索引往往能够降低left join的复杂度。（on语句在where语句之前执行）
相关文章：
sql语句中JOIN ON 的使用