【面试】数据库原理

什么是数据库

数据库简单来讲就是存放数据的地方，面试过程中，数据库一般都会被问到，这里主要是自己查阅的资料和一些总结，主要为了巩固下自己的对数据库的理解和认识。这里主要结合MySQL数据库进行整理。

存储过程（Store Procedure）

MySQL数据库是一种常用的关系型数据库，操作语言是SQL语句，SQL语句一般在执行的时候需要先编译，然后执行。而存储过程一般是有预先定义的SQL语句集，经编译后存储在数据库中，用户指定存储过程的名字并给定参数（如果存储过程有参数的话），然后直接可以执行。
一个存储过程像是一个可编程的函数，它在数据库中创建并保存，由SQL语句和一些控制结构组成。
当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对面向对象方法的模拟，它允许控制数据的访问方式。

存储过程的优点

(1)存储过程增强了SQL语言的功能和灵活性：存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
(2)存储过程允许标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且可以随时对存储过程进行修改，对应用程序源代码毫无影响。
(3)存储过程能实现较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
(4)存储过程能减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织成存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
(5)存储过程可被作为一种安全机制来充分利用：系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

索引

有过编程经验的同学，一提起索引并不陌生，其实就是帮助我们高效进行数据访问的方式。在数据库中，索引指的是帮助MySQL高效获取数据的数据结构；简要来说，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，可以在这些数据结构上实现高级查找算法，提高查询速度，这种数据结构，就是索引。

索引存储分类

索引是在MySQL的存储引擎层中实现的，而不是在服务层实现的。所以各种存储引擎支持的索引并不相同，MySQL目前提供了以下4种索引。
1 B-Tree 索引：最常见的索引类型，大部分引擎都支持B树索引。
2 HASH 索引：只有Memory引擎支持，使用场景简单。
3 R-Tree索引(空间索引)：空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。
4 Full-text (全文索引)：全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6版本提供对全文索引的支持。

B-TREE索引

这个是通用的索引存储方式，主要包括以下三种常用的形式:
1 普通索引
这是最基本的索引类型，而且它没有唯一性之类的限制，可以通过以下几种方式创建：
（1）创建索引: CREATE INDEX 索引名 ON 表名(列名1，列名2,…);
（2）修改表: ALTER TABLE 表名 ADD INDEX 索引名 (列名1，列名2,…);
（3）创建表时指定索引：CREATE TABLE 表名 ( […], INDEX 索引名 (列名1，列名 2,…) );
2 UNIQUE索引
表示唯一的，不允许重复的索引，若某一字段的信息不能重复（例如身份证号），可以将该字段的索引设置为unique：
（1）创建索引：CREATE UNIQUE INDEX 索引名 ON 表名(列名1，列名2,…);
（2）修改表：ALTER TABLE 表名ADD UNIQUE 索引名 (列名1，列名2,…);
（3）创建表时指定索引：CREATE TABLE 表名( […], UNIQUE 索引名 (列名1，列名2,…));
3 主键：PRIMARY KEY索引
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。可以将其理解为 索引名固定为 PRIMARY KEY 的 UNIQUE索引。
（1）主键一般在创建表的时候指定：“CREATE TABLE 表名( […], PRIMARY KEY (列的列表) ); ”。
（2）但是，我们也可以通过修改表的方式加入主键：“ALTER TABLE 表名 ADD PRIMARY KEY (列的列表); ”。
每个表只能有一个主键。 （主键相当于聚合索引，是查找最快的索引）
注：不能用CREATE INDEX语句创建PRIMARY KEY索引

常用语法

设置索引

在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为数据表增加索引。
1．ALTER TABLE - ALTER TABLE可以用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE index_name (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
2．CREATE INDEX - CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER
TABLE内部作为一条语句处理，语法如下。
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中名为index_name的索引。 第3条语句只在删除PRIMARY
KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY
KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

查看索引

show index from tblname;

设置索引的原则

1 较频繁的作为查询条件的字段应该创建索引
2 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
3 更新非常频繁的字段不适合创建索引
4 不会出现在 WHERE 子句中的字段不该创建索引
5 索引的选择性较低不宜建索引
注：所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数的比值，显然选择性的取值范围为(0, 1]：
命令：SELECT count(DISTINCT(column_name))/count(*) AS Selectivity FROM table_name;

索引的弊端

索引是一种空间换时间的操作，是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。

平衡多路查找树——B-Tree

B-Tree是为磁盘等外存储设备设计的一种平衡查找树
系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：
show variables like ‘innodb_page_size’;

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。
B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。

为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。
一棵m阶的B-Tree有如下特性：

1. 每个节点最多有m个孩子。
2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
3. 若根节点不是叶子节点，则至少有2个孩子
4. 所有叶子节点都在同一层，且不包含其它关键字信息
5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
7. ki(i=1,…n)为关键字，且关键字升序排序。
8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

如何使用b-tree查找数据

模拟查找关键字29的过程：

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。
分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

B+树

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

相较于B-Tree

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

1 非叶子节点只存储键值信息。
2 所有叶子节点之间都有一个链指针。
3 数据记录都存放在叶子节点中

举例

1.通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点。
2. 而且所有叶子节点（即数据节点）之间是一种链式环结构。
3.因此可以对B+Tree进行两种查找运算：
3.1一种是对于主键的范围查找和分页查找
3.2 另一种是从根节点开始，进行随机查找。
InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。
也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

事务

事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，由一条或者多条sql语句组成，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务的特性-ACID

事务应该具有4个属性：原子性、一致性、隔离性、持久性。
1 原子性（Atomicity）：指整个数据库事务是不可分割的工作单位。只有事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个sql语句执行失败，那么已经执行成功的sql语句也必须撤销，数据库状态应该退回到执行事务前的状态。
2 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束，也就是说在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
3 隔离性（Isolation）：隔离性也叫做并发控制、可串行化或者锁。事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见，这通常使用锁来实现多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
4 持久性（Durability）：表示事务一旦提交了，其结果就是永久性的，也就是数据就已经写入到数据库了，如果发生了宕机等事故，数据库也能将数据恢复。

事务的分类

1 扁平事务；
扁平事务是最简单的一种，也是实际开发中使用的最多的一种事务。在这种事务中，所有操作都处于同一层次，最常见的方式如下：

 BEGIN WORK
     Operation 1
     Operation 2
     Operation 3
     ...
     Operation N
 COMMIT WORK

或者

BEGIN WORK
     Operation 1
     Operation 2
     Operation 3
     ...
     Operation N
     (Error Occured)
 ROLLBACK WORK

扁平事务很简单，但有一个主要缺点是不能提交或回滚事务的某一部分，或者分几个独立的步骤去提交。
比如有这样的一个例子，我从呼和浩特去深圳，为了便宜，我可能这么干：

BEGIN WORK
	Operation1:呼和浩特---火车--->北京
     Operation2:北京---飞机--->深圳
 ROLLBACK WORK

但是，如果在Operation1中，从呼和浩特到北京的火车晚点了，错过了航班，怎么办？因为扁平事务的特性，那我就需要回滚，我需要再回到呼和浩特，这样做的成本太高，

2 带有保存点的扁平事务；
这种事务除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态，这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效，放弃整个事务不合乎要求，开销也太大。保存点用来通知系统应该记住事务当前的状态，以便以后发生错误时，事务能回到该状态。

3 链事务；
链事务，就是指回滚时，只能恢复到最近一个保存点；而带有保存点的扁平事务则可以回滚到任意正确的保存点。

4 嵌套事务；

BEGIN WORK
     SubTransaction1:
             BEGIN WORK
                 SubOperationX
             COMMIT WORK
     SubTransaction2:
             BEGIN WORK
                 SubOperationY
             COMMIT WORK
     ...
     SubTransactionN:
             BEGIN WORK
                 SubOperationN
             COMMIT WORK
 COMMIT WORK

这就是嵌套事务，在事务中再嵌套事务，位于根节点的事务称为顶层事务。事务的前驱称为父事务，事务的下一层称为子事务。

子事务既可以提交也可以回滚，但是它的提交操作并不马上生效，除非由其父事务提交。因此就可以确定，任何子事务都在顶层事务提交后才真正的被提交了。同理，任意一个事务的回滚都会引起它的所有子事务一同回滚。

5 分布式事务
分布式事务通常是指在一个分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点，比如：通过建设银行向招商银行转账，建设银行和招商银行肯定用的不是同一个数据库，同时二者的数据库也不在一个网络节点上，那么当用户跨行转账，就是通过分布式事务来保证数据的ACID的。

MySQL中使用事务

在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。因此要显示地开启一个事务须使用命令BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
来看看我们可以使用哪些事务控制语句。
BEGIN或START TRANSACTION；显示地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier；把事务回滚到标记点；
SET TRANSACTION；用来设置事务的隔离级别。

事务的隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。这些隔离级别之间的区别如下：

隔离级别越低，事务请求的锁越少或保持锁的时间就越短。InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ；在这种默认的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABLE级别隔离。
我们可以可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。具体操作可以访问点击

几种一致性异常

1 丢失修改：T1和T2两个事务对数据修改，T1先修改，T2后修改，T2的修改覆盖了T1的修改
2 脏读：一个事务读取到了另外一个事务没有提交的数据；
比如：事务T1更新了一行记录的内容，但是并没有提交所做的修改。事务T2读取到了T1更新后的行，然后T1执行回滚操作，取消了刚才所做的修改。现在T2所读取的行就无效了；
不可重复读：在同一事务中，两次读取同一数据，得到内容不同；
比如：事务T1读取一行记录，紧接着事务T2修改了T1刚才读取的那一行记录。然后T1又再次读取这行记录，发现与刚才读取的结果不同。这就称为“不可重复”读，因为T1原来读取的那行记录已经发生了变化；
幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同；
比如：事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入 一行记录，这行记录恰好可以满足T1所使用的查询条件中的WHERE子句的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突然出现的一样。

视图

视图是一种虚拟的表，具有和物理表相同的功能，可以对视图进行增，改，查操作，视图通常是有一个表或者多个表的行或列的子集，对视图的修改不影响基本表，它使得我们获取数据更容易，相比多表查询。
尝试运行以下命令：
1 创建视图
create or replace view dbtest_view as select * from dbtest;
2 查询
select * from dbtest_view;
3 删除
drop dbtest_view;

超键 候选键 主键 外键

1 超键：在关系中能唯一标识元组(数据库中的一条记录)的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
2 候选键：是最小超键，即没有冗余元素的超键。
3 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合，用户选作元组标识的一个侯选键称为主键。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
4 外键：在一个表中存在的另一个表的主键称此表的外键，外键主要是用来描述两个表的关系。
例子：
身份证 姓名 性别 年龄
身份证唯一，所以是一个超键
姓名唯一，所以是一个超键
（姓名，性别）唯一，所以是一个超键
（姓名，性别，年龄）唯一，所以是一个超键
–这里可以看出，超键的组合是唯一的，但可能不是最小唯一的
身份证唯一，而且没有多余属性，所以是一个候选键
姓名唯一，而且没有多余属性，所以是一个候选键
–这里可以看出，候选键是没有多余属性的超键
考虑输入查询方便性，可以选择 身份证 为主键
也可以 考虑习惯 选择 姓名 为主键
–主键是选中的一个候选键

数据库三范式

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在”A → B → C”的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y。
说明：
1NF:字段不可分;
2NF:有主键，非主键字段依赖主键;
3NF:非主键字段不能相互依赖;
解释:
1NF:原子性 字段不可再分,否则就不是关系数据库;
2NF:唯一性 一个表只说明一个事物;
3NF:每列都与主键有直接关系，不存在传递依赖;
三范式旨在避免一些数据的冗余。

反三范式

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于DML的比例。但是反范式化一定要适度，并且在原本已满足三范式的基础上再做调整的。

E-R图

E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。
E-R方法是“实体-联系方法”（Entity-Relationship Approach）的简称。它是描述现实世界概念结构模型的有效方法，是表示概念模型的一种方式，
1 用矩形表示实体型，矩形框内写明实体名；
2 用椭圆表示实体的属性，并用无向边将其与相应的实体型连接起来，对于主属性名（主键），则在其名称下划一下划线。；
3 用菱形表示实体型之间的联系，在菱形框内写明联系名，并用无向边分别与有关实体型连接起来，
4 同时在无向边旁标上联系的类型（1:1,1:n或m:n）。
联系可分为以下 3 种类型：
(1) 一对一联系(1 ∶1)
例如，一个班级有一个班长，而每个班长只在一个班级任职，则班级与班长的联系是一对一的。
(2) 一对多联系(1 ∶N)
例如，某校教师与课程之间存在一对多的联系“教”，即每位教师可以教多门课程，但是每门课程只能由一位教师来教。
(3) 多对多联系(M ∶N)
例如，学生与课程间的联系(“学 ”)是多对多的，即一个学生可以学多门课程，而每门课程可以有多个学生来学。联系也可能有属性。例如，学生“ 学” 某门课程所取得的成绩，既不是学生的属性也不是课程的属性。由于“ 成绩” 既依赖于某名特定的学生又依赖于某门特定的课程，所以它是学生与课程之间的联系“ 学”的属性。

作图步骤

⑴确定所有的实体集合
⑵选择实体集应包含的属性
⑶确定实体集之间的联系
⑷确定实体集的关键字，用下划线在属性上表明关键字的属性组合
⑸确定联系的类型，在用线将表示联系的菱形框联系到实体集时，在线旁注明联系的类型。

处理重复数据

(1)查找表中多余的重复记录，重复记录是根据单个字段(column_name)来判断。

select * from table_name where column_name in (select column_name from table_name group by column_name having count(column_name) > 1)

(2)删除表中多余的重复记录，重复记录是根据单个字段(column_name)来判断。

delete from table_name where column_name in (select b.column_name from (select column_name from table_name group by column_name having count(column_name)>1)b);

(3)查找表中多余的重复记录（多个字段）。

select * from table_name a where (a.column_name1,a.column_name2) in (select column_name1,column_name2 from vitae group by column_name1,column_name2 having count(*) > 1)

(4)删除表中多余的重复记录（多个字段），只留有rowid最小的记录 。

delete from table_name a where (a.column_name1,a.column_name2) in (select column_name1,column_name2 from table_name group by column_name1,column_name2 having count(*) > 1) and rowid not in (select min(rowid) from table_name group by column_name1,column_name2 having count(rowid)>1)

批处理SQL

#创建表和单处理
create table test(id int,name varchar(20));
insert into test values(1,'watson');

有些时候需要批处理操作，如果手动逐条运行较复杂，可以采用以下方式：
(1) 定义一个test.txt文本（以下命令不适合插入含主键的表，由于是反复的查然后添加重复的元素），假设此文本是在 /home/test.txt

insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;

批处理

mysql -uroot -p -D MyZone < /home/test.txt

将查询结果写在文件中

select * from test limit 200;

命令行运行
mysql -uroot -p -D MyZone < /home/test.txt >/home/mysql0716.out

MyISAM与InnoDB的区别是什么？

1、存储结构

MyISAM：每个MyISAM表在磁盘上存储成三个文件，文件的名字以表的名字开始，扩展名指出文件类型：.frm文件存储表定义；数据文件的扩展名为.MYD (MYData)；索引文件的扩展名是.MYI (MYIndex)。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

2、存储空间

MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3、可移植性、备份及恢复

MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

4、事务支持

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
InnoDB：支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

这一点是非常重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

5、AUTO_INCREMENT

MyISAM:可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
InnoDB: InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

6、表锁差异

MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的性能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

7、 全文索引

MyISAM：支持 FULLTEXT类型的全文索引。
InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

8、表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

9、 表的具体行数

MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。
InnoDB：没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

10、CURD操作

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

11、 外键

MyISAM：不支持
InnoDB：支持
通过上述的分析，基本上可以考虑使用InnoDB来替代MyISAM引擎了，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体视情况而定。

乐观锁 与 悲观锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和一致性以及数据库的统一性。

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制采用的主要技术手段。

无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。其实不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念，像memcache、hibernate、tair等都有类似的概念。

针对不同的业务场景，应该选用不同的并发控制方式。所以，不要把乐观并发控制和悲观并发控制狭义的理解为仅在DBMS中存在的概念，更不要把他们和数据库中提供的锁机制（行锁、表锁、排他锁、共享锁）混为一谈。其实，在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。

悲观锁

在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作在某行数据上应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。

悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

悲观锁：正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。 悲观锁的实现，往往依靠数据库提供的锁机制 （也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

悲观锁的流程：
1.在对某一记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。
2.如果加锁失败，说明该记录正在被修改，那么当前操作可能要等待或者抛出异常， 具体响应方式由开发者根据实际情况决定。
3.如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
4.其间如果有其他事务要对该记录做修改或加排他锁，都会等待该事务将该记录解锁或直接抛出异常。

MySQL InnoDB中使用悲观锁

注意：要使用悲观锁，必须先关闭mysql数据库的自动提交功能，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

set autocommit=0;//禁止自动提交
//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;

上面的查询语句中，我们使用了select…for update的方式，这样就通过开启排他锁的方式实现了悲观锁。此时在t_goods表中，id为1的 那条数据就被我们锁定了，其它事务必须等本次事务提交之后才能对该记录进行操作。这样我们可以保证当前的数据不会被其它事务修改。

注意：上面提到，使用select…for update会把数据给锁住，不过我们需要注意一下锁的级别，MySQL InnoDB默认为行级锁。行级锁都是基于索引的，如果一条SQL语句没有用到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

优点与不足：

优点：悲观并发控制实际上是采用“先取锁再访问”的保守策略，为数据处理的安全性提供了保证；
缺点：在效率方面，处理加锁的机制会让数据库产生额外的开销，同时会增加产生死锁的机率；另外，在只读型事务中由于不会产生冲突，也没必要使用锁，这样做只会增加系统负载；还会降低并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据。

乐观锁

在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理数据时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务对该数据做过修改。如果其他事务更新过该数据的话，正在提交的事务会进行回滚。

乐观锁（ Optimistic Locking ）是相对悲观锁而言，乐观锁假设数据一般情况下不会造成冲突，所以在事务对数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回错误信息，让用户决定如何去做。

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制，一般用记录数据版本的方式实现乐观锁。

数据版本：为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，便对版本标识进行一次更新。当事务提交更新的时候，需要判断数据库表对应记录的当前版本信息与第一次取出来的版本标识是否一致，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。

使用版本号实现乐观锁

使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新的版本号。

1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

乐观并发控制假设事务之间的数据竞争(data race)概率比较小，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

左 右 连接 全连接 内连接

SQL连接可以分为内连接、外连接、交叉连接。

内连接

内连接：内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。包含以下三种：
1.1.等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
1.2.不等值连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
1.3.自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

select * from book as a,stu as b where a.sutid = b.stuid
或者
select * from book as a inner join stu as b on a.sutid = b.stuid

内连接可以使用上面两种方式，其中第二种方式的inner可以省略。

其连接结果如上图，是按照a.stuid = b.stuid进行连接。红色框为重复列。

左连接

1.左联接：是以左表为基准，将a.stuid = b.stuid的数据进行连接，然后将左表没有的对应项显示，右表的列为NULL
2.右连接：是以右表为基准，将a.stuid = b.stuid的数据进行连接，然以将右表没有的对应项显示，左表的列为NULL
3全连接：完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

1 左连接

select * from book as a left join stu as b on a.sutid = b.stuid

2 右连接

select * from book as a right join stu as b on a.sutid = b.stuid

3 全连接

select * from book as a full outer join stu as b on a.sutid = b.stuid

交叉连接

交叉连接：交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

select * from book as a cross join stu as b order by a.id

【参考借鉴】
【1】参考一点击
【2】参考二点击
【3】参考三点击
【4】参考四点击