数据库基本概念

事务

事务：就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

锁

锁：在所有的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

ACID

• 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
• 隔离性：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
• 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

超键、候选键、主键、外键

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。

在一个关系中如果有这样一个属性存在，它的值能唯一地标识关系中的每一个元组，则称这个属性为候选码；

百度百科：
若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为（超级码）候选码。
若关系中的一个属性或属性组的值能够唯一地标识一个元组，且他的真子集不能唯一的标识一个元组，则称这个属性或属性组做候选码。

存储引擎

主要有InnoDB和MyISAM两种。
MyISAM引擎是MySQL 5.1及之前版本的默认引擎，特点：

1. 不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁；
2. 不支持事务；
3. 不支持外键；
4. 不支持崩溃后的安全恢复；
5. 在表有读取查询的同时，支持往表中插入新纪录
6. 支持BLOB和TEXT的前500个字符索引，支持全文索引
7. 支持延迟更新索引，极大提升写入性能
8. 对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用

InnoDB
InnoDB在MySQL 5.5后成为默认索引，特点：

1. 支持行锁，采用MVCC来支持高并发
2. 支持事务
3. 支持外键
4. 支持崩溃后的安全恢复
5. 不支持全文索引

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表。

触发器

触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。
维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？
尽可能使用约束，如check，主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下

完整性约束

定义：数据的正确、有效和相容称之为数据的完整性；数据库的完整性是指数据的正确性和相容性；

从按触发方式角度，关系约束可以分为静态和动态约束；动静约束又分别可以分为：列级，元组级和关系级；
是故有6类约束：列级静态约束、元祖级静态约束、关系级静态约束、列级动态约束、元祖级动态约束、关系级动态约束。
动态约束通常由应用软件来实现，不同DBMS支持的数据库完整性基本相同。
下面的实体完整性和参照完整性属于静态关系约束。

数据库关系模型有三类完整性约束：
一、实体完整性
实体完整性要求每个表都有唯一标识符，每一个表中的主键字段不能为空或者重复的值；实体完整性要求主属性不能取空值，通过定义主码/主键来保证。实体完整性将每一条记录定义为表中的唯一实体，即不能重复。
二、参照完整性
参照完整性要求关系中不允许引用不存在的实体。设定相应的更新删除插入规则来更新参考表。
参照完整性规则是指要求通过定义的外关键字和主关键字之间的引用规则来约束两个关系之间的联系，包括更新、删除和插入规则。这条规则要求不引用不存在的实体。其形式定义如下：如果属性集K是关系模式R1的主键，K也是关系模式R2的外键，那么R2的关系中，K的取值只允许有两种可能，或者为空值，或者等于R1关系中某个主键值。这条规则的实质是不允许引用不存在的实体。对于此规则有三点需要注意：

1. 外键和相应的主键可以不同名，但要定义在相同的值域上。
2. 当R1和R2是同一个关系模式时，表示同一个关系中不同元组之间的联系。
3. 外键值是否允许为空，应视具体问题而定。若外键是模式主键中的成分，则外键值不允许为空，否则允许为空。

例如表student(id, username, password)，表student_info(id, name, age, sex)。其中表student_info参照表student。id作为外键。那么当student表删除一行时，表student_info对应的id那一列将被删除或者置空（根据设定的规则而定）。同样，表student_info增加一行，其中的id必须等于student表中的id。

三、用户自定义完整性
用户自定义完整性是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。
也就是说：某个表你设置这一列为null，not null，default，check等。包括：非空约束、唯一约束、检查约束、主键约束 、外键约束。
1、非空约束（Not Null Constraint） 就是我们在添加那个表 userinfo，在id、name后面都添加了not null，就是有些列不能把它设为空。

2、唯一约束（Unique Constraint） 一个唯一约束并不包括一个NULL值。直接在字段定义后加入UNIQUE即可定义该唯一约束。 1) 一个表只能创建一个主键约束，但一个表可以根据需要对不同的列创建若干unique约束 2)主键字段不允许为null ，unique允许为空 3)一般创建主键约束时，系统自动产生簇索引，unique约束自动产非簇索引

3、检查约束（The Check Clause） 通过在定义数据库表里，在字段级或者是在表级加入的检查约束，使其满足特定的要求。举例：

create table student (
    name         varchar(15) not null,
    student_id   varchar(15),
    degree_level varchar(15),
    primary key (student_id),
    check (degree_level in ('Bachelors', 'Masters', 'Doctorate'))
);

4、主键约束（Primary Key Constraint） 其实主键约束就是一张表只能建立一个主键约束，其实就是唯一约束+非空约束。

5、外键约束（ Foreign Key Constraint）主要是确保同一个表或者不同表之间的引用完整性，所以必须引用一个PRIMARY KEY或者UNIQUE约束，用户必须在应用表上具有REFERENCES权限；一个表中最多可以有31个外部键约束；在临时表中，不能使用外部键约束；主键和外部键的数据类型必须严格匹配。

范式

数据库的三大特性可谓是：实体属性和关系。
实体：表；
属性：表中的数据（字段）；
关系：表与表之间的关系；

范式：关系数据库中满足不同规范化程度的关系模式的类。也叫模式。
数据库设计三大范式
第一范式（1NF）：数据表中的每一列（每个字段）必须是不可拆分的最小单元，也就是确保每一列的原子性；第一范式的目标是确保每列的原子性；如果每列都是不可再分的最小数据单元（最小原子单元），则满足1NF；
第二范式（2NF）：满足1NF后，要求表中的所有列，都必须依赖于主键，而不能有任何一列与主键没有关系，也就是说一个表只描述一件事情；
第三范式（3NF）：满足2NF后，要求表中的每一列只与主键直接相关而不是间接相关，表中的每一列只能依赖于主键；如果一个关系满足2NF，每个非键属性只功能依赖于主键，不功能依赖于其他属性，则满足3NF；
BCNF：
第四范式（4NF）：如果有且只有一个表符合BCNF，同时多值依赖为函数依赖，此表才符合第四范式；删除不必要的数据结构：多值依赖；除对一个候选键扩展集存在属性组函数依赖外，不存在其他非平凡多值函数依赖；其函数依赖集中所有候选关键字都是决定因素。
第五范式（5NF）：如果有且只有一个表符合4NF，同时其中的每个连接依赖被候选键所包含，此表才符合第五依赖；如果关系模式R中的每一个连接依赖都是由R的候选键所包含，则称R是第五范式；如果只有一个表符合BCNF，同时多值依赖为函数依赖，此表才符合第五范式；要求能从由原始表分解和转换而得的新表中精确重建出原始表；利用第五范式可以确定在分解和转换过程中有没有数据丢失；将表分割成尽可能小的块，是为了排除在表中所有的冗余；

3NF可以在保证无损连接的同时保持函数依赖，而BCNF和4NF则可能不会保持函数依赖。因此，一般而言进行关系模式设计时，满足3NF的标准即可。

三者关系：3NF⊂2NF⊂1NF；
关系模型中的关系模式至少需要满足1NF；
消除部分函数依赖的INF的关系模式，必定是2NF；
在关系模式R中的属性全部是主属性，则R的最高范式必定是3NF；

第一范式和第二范式在于有没有分出两张表，第二范式是说一张表中包含多种不同的实体属性，那么要必须分成多张表， 第三范式是要求已经分成多张表，那么一张表中只能有另一张表中的id（主键），而不能有其他的任何信息（其他的信息一律用主键在另一表查询）。

模式分解

关系模式的规范化过程实际上就是按照不同级别范式的要求条件对模式进行逐渐分解的过程。
模式分解的等价标准：分解既要保持依赖，又要具有无损连接；
模式分解的算法：合成法（3NF，保持函数依赖）、在合成法基础上进行调整、分解法（BCNF，无损）和分解法（4NF，无损）。
模式分解的合成法内容：
函数依赖集F极小化处理和对F按相同左部原则分组；
处理不出现在F中的属性，这些属性单独构成一个关系，并从U中去掉它们；
如有X→A∈F且XA=U，则算法终止。

要求分解保持函数依赖，模式分离总可以达到3NF，不一定能达到BCNF；
要求分解既保持函数依赖又具有无损连接性，可以达到3NF，不一定能达到BCNF；
要求分解具有无损连接性，可以达到4NF

关系模式的分解不仅仅是属性集合的分解，它是对关系模式上的函数依赖集以及关系模式的当前值分解的具体表现；
分解过程中，保持关系模式的一个分解是等价的：模式分解的无损连接，关系模式的函数依赖集在分解后仍在数据库模式中保持不变；
所有分解出的模式所满足的函数依赖的全体应当等价于原模式的函数依赖集；

数据依赖

数据依赖是指在程序引用数据之前处理过的数据的状态，在编译学中，数据依赖是数据分析的一部分，数据依赖有三种:流依赖、反依赖和输出依赖；数据内在关系的体现，数据内在的性质和语义的体现，现实世界属性间相互联系的抽象；

三级模式

视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
使用到视图的场景：
（1）不希望访问者获取整个表的信息，只暴露部分字段给访问者，所以就建一个虚表，就是视图。
（2）查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异。
注：这个视图是在数据库中创建的而不是用代码创建的。

drop,delete与truncate的区别

drop直接删掉表。
truncate删除表中数据，再插入时自增长id又从1开始。
delete删除表中数据，可以加where字句。
（1） DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
（2） 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。
（3） 一般而言，drop > truncate > delete
（4） 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view
（5） TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。
（6） truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
（7） delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。
（8） truncate、drop是DLL（data define language)，操作立即生效，原数据不放到 rollback segment中，不能回滚。
（9） 在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。
（10） Truncate table 表名 速度快而且效率高。因为：
truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
（12） 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

横表和竖表（纵表）

横表就是普通的建表方式，表结构为：主键、字段1、字段2…。
优点：一行表示一个实体记录，清晰可见，一目了然。
缺点：如果现在要给这个表加一个字段，那么就必须重建表结构。

纵表的表结构为：主键、字段代码、字段值。而字段代码则为字段1、字段2…。在系统应用中，经常碰到不确定的属性字段等应用场景。常规的作法是直接将属性字段设计成纵表，可以不限制属性的数量，来满足业务需求。
表1 属性定义表

字段名	字段编码	数据类型
主键	id	bigint
对象	object	varchar(20)
属性	attribute	varchar(100)
属性值	value	varchar(100)
表2 存放数据示例
主键	对象	属性
–	–	–
1	张三	性别
2	张三	身高
3	李四	身高
4	李四	收入
5	李四	年龄
优点：如果现在要给这个表加一个字段，只需要添加一些记录。
缺点：数据描述不是很清晰，而且会造成数据库数据很多。另如果需要分组统计，要先group by，较繁琐。

结论
应该把不容易改动表结构的设计成横表，把容易经常改动不确定的表结构设计成纵表。

以电信行业用户帐单表为例，一般出账时用户有很多费用，其数据一般存储为：时间，客户ID，费用科目，费用。这种存储结构一般称为纵表，其特点是行数多，字段少。纵表在使用时由于行数多，统计用户数或对用户进行分档时还需要进行GROUP BY 操作，性能低，且操作不便，为了提高性能，通常根据需要将纵表进行汇总，形成横表，比如：时间、客户ID、基本通话费、漫游通话费、国内长途费、国际长途费…。

纵表转横标

横表转纵表

上面提到纵表的检索/查询效率问题，那么如何解决呢？

1. 纵表+文件索引
   采用Lucene等全文检索框架，将纵表数据转换为文件索引，通过属性分词等，加快检索效率。
   优点：检索灵活，响应快速，能够满足海量数据的灵活检索；
   缺点：单独维护一套文件索引
2. 纵表设计为横表
   如果数据量不是太大，属性总量也不是太多，单独维护一套文件索引那就有点大材小用。可以考虑通过映射表的方式，将原有纵表设计成横表，满足属性检索的性能要求。
   模型设计主要分为3张表：1、属性定义表；2、属性实例存储横表；3、属性定义映射表；
   属性定义表主要是描述属性的信息，如表1；
   属性实例存储横表主要是描述对象的具体属性值：
   属性定义映射表主要描述对象的具体属性存储在实例表中的字段信息，如表2；
   通过以上3张表，可以将原有纵表的设计转换成横表，加快检索效率。
   优点：检索灵活，效率较高；
   缺点：海量数据及海量属性不太适合，且需要拼装动态SQL；

参考：
纵表和横表的概念及其相互转换
提升数据库纵表检索效率

left join、right join、inner join、full join

连接分为两种：内连接（即最常见的等值连接）与外连接。外连接分为左外连接，右外连接和全外连接。
左外连接就是在等值连接的基础上加上主表中的未匹配数据，
右外连接是在等值连接的基础上加上被连接表的不匹配数据，
全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上，其等价写法，对同一表先做左连接，然后右连接
left join(左联接)：返回包括左表中的所有记录和右表中联结字段相等的记录，left join是以A表的记录为基础的，A可以看成左表，B可以看成右表，left join是以左表为准的。左表(A)的记录将会全部表示出来，而右表(B)只会显示符合搜索条件的记录(A.aID = B.bID)。B表记录不足的地方均为NULL。
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录，和left join的结果刚好相反，以右表(B)为基础的，A表不足的地方用NULL填充。
inner join(等值连接)，可以简写为join。只返回两个表中联结字段相等的行，只显示符合条件的记录。

一张图

参考