数据库

AquilaEAG

于 2020-07-22 00:00:42 发布

阅读量119

点赞数

分类专栏：基础知识

本文链接：https://blog.csdn.net/weixin_43202635/article/details/107501996

版权

基础知识专栏收录该内容

25 篇文章 0 订阅

订阅专栏

参考

1 、关系型和非关系型数据库的区别（各自优点）

在这里插入图片描述注意：

1：数据库事务必须具备ACID特性，ACID是Atomic原子性，Consistency一致性，Isolation隔离性，Durability持久性。
2：数据的持久存储，尤其是海量数据的持久存储，还是需要一种关系数据库。

2 、常用 SQL 语句（ DDL , DML , DCL , TCL )

数据定义语言（DDL）、数据操作语言（DML）、数据控制语言（DCL）和事物控制语言（TCL）

1.Data Definition Language(DDL)
DDL使我们有能力创建或删除表格。也可以定义索引（键），规定表之间的链接，以及施加表间的约束。
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 改变数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

2. Data Manipulation Language(DML)
DML供用户实现对数据的追加、删除、更新、查询等操作。
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

3. Data Control Language(DCL)
GRANT - 授权
REVOKE - 撤销授权
DENY - 拒绝授权

4. Transaction Control Language(TCL)
SAVEPOINT - 设置保存点
ROLLBACK - 回滚
COMMIT - 提交

3 、数据库中 join 的类型与区别（ innerjoin , outerjoin , crossjoin , naturatjoin , selfjoin )现有两张表，Table A 是左边的表。Table B 是右边的表。其各有四条记录，其中有两条记录name是相同的：

Table A 是左边的表。Table B 是右边的表。

1.INNER JOIN 产生的结果是AB的交集
SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name
INNER JOIN 产生的结果是AB的交集 INNER JOIN 产生的结果是AB的交集

2.LEFT [OUTER] JOIN 产生表A的完全集，而B表中匹配的则有值，没有匹配的则以null值取代。
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name
LEFT [OUTER] JOIN 产生表A的完全集，而B表中匹配的则有值 LEFT [OUTER] JOIN 产生表A的完全集，而B表中匹配的则有值

3.RIGHT [OUTER] JOIN 产生表B的完全集，而A表中匹配的则有值，没有匹配的则以null值取代。
SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.name = TableB.name
图标如left join类似。

4.FULL [OUTER] JOIN 产生A和B的并集。对于没有匹配的记录，则会以null做为值。
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name
你可以通过is NULL将没有匹配的值找出来：
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name
WHERE TableA.id IS null OR TableB.id IS null
FULL [OUTER] JOIN 产生A和B的并集 FULL [OUTER] JOIN 产生A和B的并集

5.CROSS JOIN 把表A和表B的数据进行一个N*M的组合，即笛卡尔积。如本例会产生4*4=16条记录，在开发过程中我们肯定是要过滤数据，所以这种很少用。
SELECT * FROM TableA CROSS JOIN TableB

https://www.cnblogs.com/wq3435/p/6677937.html

4 、数据库的索引类型

1.普通索引最基本的索引，没有限制。

2.唯一索引与普通索引类似，不同的是索引列的值必须唯一，但允许有null，如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

3.全文索引大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

4.单列索引，多列索引多个单列索引和单个多列索引查询的效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最严格的索引。

5.组合索引（最左前缀）平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引，例如创建一个索引指定（name（20），age（10）），建立这样的组合索引，相当于分别建立了下面两个索引（name，age）（name）

5 、聚集索引和非聚集索引的区别（叶节点存储内容）

SQL SERVER提供了两种索引：聚集索引和非聚集索引。

其中聚集索引表示表中存储的数据按照索引的顺序存储，检索效率比非聚集索引高，但对数据更新影响较大。非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，非聚集索引检索效率比聚集索引低，但对数据更新影响较小。

https://www.cnblogs.com/sx-xiaoxia/p/8616054.html

6 、唯一性索引和主码索引的区别

1 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

2 主键创建后一定包含一个唯一性索引，唯一性索引不一定就是主键。

3 唯一性索引列允许空值，而主键列不允许为空值。

4 主键可以被其他表引用为外键，而唯一索引不能。

5 一个表最多只能创建一个主键，但是可以创建多个唯一索引。

6 主键更适合那些不容易改变的唯一标识，如自动递增列，身份证号等。

7 在RBO 模式下，主键的执行计划优先级高于唯一索引。两者可以提高查询的速度。

7 、索引的优缺点，什么时候使用索引，什么时候不能使用索引（重点）

1.什么时候需要创建索引

主键自动建立唯一索引

频繁作为查询条件的字段应该创建索引

查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找

查询中统计或者分组的字段；

2.什么时候不需要创建索引

频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件

where条件里用不到的字段，不创建索引；

表记录太少，不需要创建索引；

经常增删改的表；

数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。

https://www.cnblogs.com/luffyxin/p/9898394.html

8 、索引的底层实现（B＋树，为何不采用红黑树， B 树）

AVL 树和红黑树这些二叉树结构的数据结构可以达到最高的查询效率这是毋庸置疑的。

既然如此，那么数据库索引为什么不用 AVL 树或者红黑树呢？

这就牵扯到一个问题了，不考虑每种数据结构的前提条件而选择数据结构都是在耍流氓。

AVL 数和红黑树基本都是存储在内存中才会使用的数据结构，那磁盘中会有什么不同呢？

这就要牵扯到索引的存储原理了

页是 InnoDB存储引擎管理数据库的最小磁盘单位。

一个页中包括很多数据行。

那么，现在问题就来了

一个父节点只有 2 个子节点，并不能填满一个页上的所有内容啊？那多余的内容岂不是要浪费了？我们怎么才能把浪费的这部分内容利用起来呢？哈哈，答案就是 B+ 树，让一个父节点有多个子节点就可以了。

由于 B+ 树分支比二叉树更多，所以相同数量的内容，B+ 树的深度更浅，深度代表什么？代表磁盘 io 次数啊！

所以，涉及到磁盘上查询的数据结构，一般都用 B+ 树啦。

9 、 B 树和 B ＋树具体实现

B 树可以看作是对2-3查找树的一种扩展，即他允许每个节点有M-1个子节点。

根节点至少有两个子节点
每个节点有M-1个key，并且以升序排列
位于M-1和M key的子节点的值位于M-1 和M key对应的Value之间
其它节点至少有M/2个子节点

B+树是对B树的一种变形树，它与B树的差异在于：

有k个子结点的结点必然有k个关键码；
非叶结点仅具有索引作用，跟记录有关的信息均存放在叶结点中。
树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录。

https://blog.csdn.net/fhy569039351/article/details/82976842

10 、索引最左前缀问题

mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：

如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；
如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；

https://www.cnblogs.com/wezheng/p/8399305.html

11 、数据库引擎介绍， innodb 和 myisam 的特点与区别

MyISAM引擎是一种非事务性的引擎，提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用。MyISAM中，一个table实际保存为三个文件，.frm存储表定义，.MYD存储数据,.MYI存储索引。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQL默认使用另外一个引擎。

InnoDB则是一种支持事务的引擎。给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。所以的数据存储在一个或者多个数据文件中，支持类似于Oracle的锁机制。一般在OLTP应用中使用较广泛。如果没有指定InnoDB配置选项，MySQL将在MySQL数据目录下创建一个名为ibdata1的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的日志文件。

https://www.cnblogs.com/kingxiaozi/p/7002084.html

12 、数据库中事务的 ACID（四大特性都要能够举例说明，理解透彻，比如原子性和一致性的关联，隔离性不好会出现的问题）

原子性：事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

一致性：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的。

隔离性：一个事务所做的修改在最终提交以前，对其它事务是不可见的。

持久性：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。可以通过数据库备份和恢复来实现，在系统发生奔溃时，使用备份的数据库进行数据恢复。

事务的 ACID 特性之间不是一种一种平级关系：

只有满足一致性，事务的执行结果才是正确的。
在无并发的情况下，事务串行执行，隔离性一定能够满足。此时要只要能满足原子性，就一定能满足一致性。
在并发的情况下，多个事务并发执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
事务满足持久化是为了能应对数据库奔溃的情况。

MySQL 默认采用自动提交模式。也就是说，如果不显式使用START TRANSACTION语句来开始一个事务，那么每个查询都会被当做一个事务自动提交。

在并发的情况下，事务的隔离性不能实现时，就会出现脏读，不可重复读，丢失修改，幻影读等问题。

https://www.icode9.com/content-2-602562.html

13 、数据库隔离性设置不同会出现的问题（脏读、不可重复读、丢失修改、幻读）

丢失修改：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。则T1丢失修改。可在T1修改时对数据加上X锁，直到T1结束锁释放，在此期间T2不可修改该数据。

脏读：事务T1在修改数据a后，未commit。事务T2读取数据a,随后T1 rollback撤销修改，则T2读到的数据a为脏数据。提交读隔离级别解决脏读问题。

不可重复读：同一个事务中多次读取同样数据的结果不一样。T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。可在T2读取数据时对数据加上S锁，直到T2结束锁释放，在此期间T1不可修改该数据。可重复读隔离级别解决了不可重复读的问题。

幻读：T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。同上，同样可以加S锁解决该问题。

MySQL 的 InnoDB 存储引擎，在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。

14 、数据库的隔离级别， mysql 和 oracle 的隔离级别分别是什么

Mysql有四个隔离级别：未提交读，提交读，可重复读，可串行化。

未提交读：事务中的修改，即使没有提交，对其它事务也是可见的。总是读取最新的数据行即是未提交读隔离级别。

提交读：一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。

可重复读：保证在同一个事务中多次读取同样数据的结果是一样的。MVCC（多版本并发控制）实现提交读和可重复读这两种隔离级别。

可串行化：强制事务串行执行。遵循两段锁协议实现可串行化。

15 、数据库连接池的作用

什么是数据库连接池：创建数据库连接是一个很耗时的操作，也容易对数据库造成安全隐患。所以，在程序初始化的时候，集中创建多个数据库连接，并把他们集中管理，供程序使用，可以保证较快的数据库读写速度，还更加安全可靠。数据库连接池负责分配,管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个。连接池必须要确保某一时间内一个 conn 只能分配给一个线程。不同 conn 的事务是相互独立的。