数据库基础

1.数据库中事务的四大特性（ACID）

事务概念：
事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个SQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。
（1）原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
（2）一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
（3）隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
（4）持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

2.MySQL中myisam与innodb的区别

1. InnoDB支持事物，而MyISAM不支持事物
2. InnoDB支持行级锁，而MyISAM支持表级锁
3. InnoDB支持MVCC, 而MyISAM不支持
4. InnoDB支持外键，而MyISAM不支持
5. InnoDB不支持全文索引，而MyISAM支持。

InnoDB提供提交、回滚、崩溃恢复能力的事务安全（ASID）能力，实现并发控制。
MyISAM提供较高的插入和查询记录的效率，主要用于插入和查询。
memory用于临时存放数据，数据量不大并且不需要较高数据安全性。
archive：如果只有插入和查询可以用，支持高并发的插入操作，但本身不是事务安全。

二者select count(*)哪个更快
MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

mysql中四个存储引擎：innodb/myisam/memory/archive

3.innodbd的事务与日志的实现方式

错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
二进制日志：记录对数据库执行更改的所有操作。
实现方式：事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的。

4.mysql的三级模式

（1）模式（逻辑模式）：是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。
（2）外模式（用户模式）：是数据库用户的数据视图，是局部数据的逻辑结构和特征的描述。
（3）内模式（存储模式）：一个数据库只有一个内模式，是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。

5.表操作命令：create/alter/drop

数据操作指令：select/insert/delete/update
select 表名 from … where …
insert into table values()
update 表名 set …
delete from 表名 where …

6.外连接分为内连接、左连接、右连接

内连接是根据某个条件连接两个表共有的数据；
左连接是根据某个条件以及左边的表连接数据，右边的表没数据的话则填null；
右连接是根据某个条件以及右边的表连接数据，左边的表没数据的话则填null；

7.mysql中视图和表的区别以及联系

区别：
(1)视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化的表，而表不是。
(2)视图没有实际的物理记录，而表有。
(3)视图是窗口，表是内容。
(4)视图是逻辑概念的存在，不占用物理空间；而表占用物理空间。
(5)表可以及时对它进行修改；而视图只能用创建语句来修改。
(6)视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。
(7)从安全来说，视图可以防止用户直接接触表，因而用户不知道表结构。
(8)表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
(9)视图的建立和删除只影响视图本身，不影响对应的表。
联系：
视图是在表之上建立的虚表，它的结构（所定义的列）和内容（所有记录）都来自表，视图依据表存在而存在。一个视图可以对应多个表。视图是表的抽象和在逻辑意义上建立的新关系。
删除视图中的数据,数据库中表的数据会一起被删除。

8.安全性操作

授权：grant 权限（列） on 表名 to 用户
所有权限：all priviliges
收回权限：revoke 权限（列） on 表名 from 用户

9.完整性约束

主键约束：primary key
外键约束：foreign key
唯一约束：unique
检查约束：check
非空约束: not null

10.存储过程（procedure）和函数（function）区别

本质上它们都是存储程序。函数只能通过return语句返回单个值或表对象；而存储过程不允许执行return语句，但是可以通过output参数返回多个值。函数限制比较多，不能用临时变量，只能用表变量，还有一些函数都不可用等等；而存储过程的限制相对就比较少。函数可以嵌入在SQL语句中使用，可以在select语句中作为查询语句的一个部分调用；而存储过程一般是作为一个独立的部分来执行。

11.触发器和约束的区别

触发器是由服务器自动激活的，类似于约束，但是比约束更加灵活，可以实施比约束更加复杂的检查和操作，具有更强大的数据控制能力。

12.事务隔离级别

（1）Read uncommitted 未提交读（RU）
最弱的隔离级别，事务中的修改即使没有提交，对其他事务也都是可见的。（即脏读）
（2）Read committed 提交读 不可重复读（RC）
大多数数据库系统的默认隔离级别。
解决了脏读的问题，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。
一个事务两次执行同样的查询，可能会得到不一样的结果。
（3）Repeatable read 可重复读（RR）
mysql默认隔离级别。
解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。
该级无法解决幻读的问题，幻读是当某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻读。
innodb和xtradb存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。
（4）Serializable 可串行化
该级是最高的级别，通过强制事务串行执行，避免了幻读的问题，该级会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。

13.索引

MYSQL中索引文件以B树结构存储，索引可分为单列索引和多列索引。
对于多列索引中，一个SQL语句是否用到了索引取决于其数据是否符合最左前缀原则。
MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。例如，“SELECT id FROM people WHERE firstname LIKE ‘Li%’;”这个查询将使用索引，但“SELECT id FROM people WHERE firstname LIKE ‘%ike’;”这个查询不会使用索引。

14.关系数据库的特点

1）数据集中控制。
在文件管理方法中，文件是分散的，每个用户或每种处理都有各自的文件，这些文件之间一般是没有联系的，因此，不能按照统一的方法来控制、维护和管理。而数据库则很好地克服了这一缺点，可以集中控制、维护和管理有关数据。
2）数据独立性高。
数据库中的数据独立于应用程序，包括数据的物理独立性和逻辑独立性，给数据库的使用、调整、优化和进一步扩充提供了方便，提高了数据库应用系统的稳定性。
3）数据共享性好。
数据库中的数据可以供多个用户使用，每个用户只与库中的一部分数据发生联系；用户数据可以重叠，用户可以同时存取数据而互不影响，大大提高了数据库的使用效率。
4）数据冗余度小。
数据库中的数据不是面向应用，而是面向系统。数据统一定义、组织和存储，集中管理，避免了不必要的数据冗余，也提高了数据的一致性。
5）数据结构化。
整个数据库按一定的结构形式构成，数据在记录内部和记录类型之间相互关联，用户可通过不同的路径存取数据。
6）统一的数据保护功能。
在多用户共享数据资源的情况下，对用户使用数据有严格的检查，对数据库规定密码或存取权限，拒绝非法用户进入数据库，以确保数据的安全性、一致性和并发控制。

15.join和union的区别

join是两张表做交连后里面条件相同的部分记录产生一个记录集
union是产生的两个记录集（字段要一样的）并在一起，成为一个新的记录集
JOIN用于按照ON条件联接两个表，主要有四种：
INNER JOIN：内部联接两个表中的记录，仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。我理解的是只要记录不符合ON条件，就不会显示在结果集内。
LEFT JOIN/LEFT OUTER JOIN：外部联接两个表中的记录，并包含左表中的全部记录。如果左表的某记录在右表中没有匹配记录，则在相关联的结果集中右表的所有选择列表列均为空值。理解为即使不符合ON条件，左表中的记录也全部显示出来，且结果集中该类记录的右表字段为空值。
RIGHT JOIN/RIGHT OUTER JOIN：外部联接两个表中的记录，并包含右表中的全部记录。简单说就是和LEFT JOIN反过来。
FULL JOIN/FULL OUTER JOIN：完整外部联接返回左表和右表中的所有行。就是LEFT JOIN和RIGHT JOIN和合并，左右两表的数据都全部显示。

JOIN的基本语法：
Select table1.* FROM table1 JOIN table2 ON table1.id=table2.id

sql写法
内连接inner join：
SELECT msp.name, party.name
FROM msp JOIN party ON party=code
或
SELECT msp.name, party.name
FROM msp inner JOIN party ON party=code

左连接left join ：
SELECT msp.name, party.name
FROM msp LEFT JOIN party ON party=code

右连接right join ：
SELECT msp.name, party.name
FROM msp RIGHT JOIN party ON msp.party=party.code

全连接(full join)：
SELECT msp.name, party.name
FROM msp FULL JOIN party ON msp.party=party.code

UNION运算符
将两个或更多查询的结果集组合为单个结果集，该结果集包含联合查询中的所有查询的全部行。UNION的结果集列名与UNION运算符中第一个Select语句的结果集的列名相同。另一个Select语句的结果集列名将被忽略。
其中两种不同的用法是UNION和UNION ALL，区别在于UNION从结果集中删除重复的行。如果使用UNION ALL将包含所有行并且将不删除重复的行。
UNION和UNION ALL的区别：
union 检查重复
union all 不做检查
比如 select ‘a’ union select ‘a’ 输出就是一行 a
比如 select ‘a’ union all select ‘a’ 输出就是两行 a

16.数据库范式

第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。如电话列可进行拆分—-家庭电话、公司电话
第二范式（2NF）：首先是（1NF），另外包含两部分内容，一是表必须有主键；而是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
第三范式（3NF）：首先是2NF，另外非主键列必须直接依赖于主键，不能存在依赖传递。
比如Student表（学号，姓名，年龄，性别，所在院校，院校电话）这样一个表结构，就存在上述关系。学号–>所在院校–>（院校地址，院校电话）这样的表结构，我们应该拆开来，如下。
（学号，姓名，年龄，性别，所在院校）–（所在院校，院校地址，院校电话）
满足这些规范的数据库是简洁的、结构明晰的；同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常。

17.数据类型选择

数字类型
float和double选择（尽量选择float）
区分开TINYINT/INT/BIGINT，能确定不会使用负数的字段，建议添加unsigned定义
能够用数字类型的字段尽量选择数字类型而不用字符串类型

字符类型
char,varchar,TEXT的选择：非万不得已不要使用 TEXT 数据类型，定长字段，建议使用 CHAR 类型（填空格），不定长字段尽量使用 VARCHAR（自动适应长度，超过阶段），且仅仅设定适当的最大长度

时间类型
按选择优先级排序DATE（精确到天）、TIMESTAMP、DATETIME（精确到时间）

ENUM
对于状态字段，可以尝试使用 ENUM 来存放

避免使用NULL字段，很难查询优化且占用额外索引空间

18.字符编码

同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。
1.纯拉丁字符能表示的内容，选择 latin1 字符编码
2.中文可选用utf-8
3.MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率

19.SQL优化

1)只返回需要的数据
不要写SELECT *的语句
合理写WHERE子句，不要写没有WHERE的SQL语句。

2)尽量少做重复的工作
可以合并一些sql语句

3)适当建立索引（不是越多越好）但以下几点会进行全表扫描
左模糊查询’%…’
使用了不等操作符！=
Or使用不当，or两边都必须有索引才行
In 、not in
Where子句对字段进行表达式操作
对于创建的复合索引（从最左边开始组合），查询条件用到的列必须从左边开始不能间隔。否则无效，复合索引的结构与电话簿类似
全文索引：当于对文件建立了一个以词库为目录的索引（文件大全文索引比模糊匹配效果好）
能在char、varchar、text类型的列上面创建全文索引
MySQL 5.6 Innodb引擎也能进行全文索引
搜索语法：MATCH (列名1, 列名2,…) AGAINST (搜索字符串 [搜索修饰符])
如果列类型是字符串，但在查询时把一个数值型常量赋值给了一个字符型的列名name，那么虽然在name列上有索引，但是也没有用到。

4)使用join代替子查询

5)使用union代替手动创建临时表

20.索引优化

1）创建索引，以下情况不适合建立索引
表记录太少
经常插入、删除、修改的表
数据重复且分布平均的表字段

2）复合索引
如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引

21.索引

索引是对数据库表中一列或多列的值进行排序的一种结构。
优点：
大大加快数据的检索速度
创建唯一性索引，保证数据库表中每一行数据的唯一性
可以加速表和表之间的连接

缺点：
索引需要占物理空间。
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，
降低了数据的维护速度。

索引分类：
普通索引
create index zjj_temp_index_1 on zjj_temp_1(first_name);
drop index zjj_temp_index_1;

唯一索引，索引列的值必须唯一，但允许有空值
create unique index zjj_temp_1 on zjj_temp_1(id);

主键索引，它是一种特殊的唯一索引，不允许有空值。

组合索引

22.锁

锁模式包括：
共享锁:（读取）操作创建的锁。其他用户可以并发读取数据，但任何事物都不能获取数据上的排它锁，直到已释放所有共享锁。

排他锁（X锁）：对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

更新锁:
更新(U)锁可以防止通常形式的死锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则两个事务需都要转换共享锁为排它(X)锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。
若要避免这种潜在的死锁问题，请使用更新(U)锁。一次只有一个事务可以获得资源的更新(U)锁。如果事务修改资源，则更新(U)锁转换为排它(X)锁。否则，锁转换为共享锁。

锁的粒度主要有以下几种类型：
行锁：粒度最小，并发性最高
页锁：一次锁定一页。25个行锁可升级为一个页锁。
表锁：粒度大，并发性低
数据库锁：控制整个数据库操作

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

悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

MyISAM和InnoDB索引实现

MYISAM索引实现
MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
主索引：MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
辅助索引：在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同.

主索引：InnoDB表数据文件本身就是主索引。
InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

辅助索引：InnoDB的所有辅助索引都引用主键作为data域。
聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白
1、为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
2、用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

InnoDB索引和MyISAM索引的区别：
一是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。