关系型数据库面试宝典

àì夳堔傛蜴生んèń 2022-05-14 04:34 231阅读 0赞

### 文章目录 ###

*  面试
 *   *  一、数据库基础
     *   *  1.1 事务
         *  1.2 事务具有的最基本的特性(ACID)
         *  2.1 事务并发执行会出现的问题
         *  2.2 数据库的四种隔离级别
     *  二、数据库设计
     *   *   *  好处
             *  设计过程:
             *  Mysql存储引擎
     *  三、常用SQL
     *   *   *  1.MYSQL查询每个分类的第一条数据
     *  四、连接种类
     *  五、排序 分页
     *  六、SQL
     *   *   *  空值与null
             *  防止sql注入的方法
             *  drop,delete与truncate的区别
             *  主键自动生成
             *  时间戳
     *  七、
     *  八、金额的类型一般怎么设置
     *  九、状态的类型怎么设置
     *  十、sql语句
     *   *  3.存储过程
         *  4.触发器
         *  5.视图
         *  6.日志
         *  7.union和union all
         *  between
         *  pgsql
         *  SQL之COLLATE 子句 排序规则
         *  包含
     *  十一、==索引==(索引是一种数据结构)
     *   *  索引种类
         *  索引类型
         *  B-Tree 是最常用的用于索引的数据结构。

--------------------

[<<------最新数据库排行榜连接------>>][------_------]

--------------------

# 面试 #

## 一、数据库基础 ##

数据查询 select
    数据操纵 insert update delete
    数据定义 create   drop  truncate
    数据控制 grant为用户授予系统权限
    		revoke 收回系统权限

*  WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
 *  GROUP BY 子句用来分组 WHERE 子句的输出。
 *  HAVING 子句用来从分组的结果中筛选行。

常用的SQL约束：  
● NOT NULL约束 - 确保列不能有NULL值 ID INT NOT NULL,  
● 默认值约束 - 提供未指定时为列的值时的默认值  
● 唯一值约束 - 确保了在一列中的所有的值是唯一的  
● 主键 - 唯一标识数据库表中的每一行/记录  
● 外键 - 唯一标识任何其他数据库表中的行/记录  
● 检查约束 - CHECK约束可以确保列中的所有值满足一定的条件  
● 索引 - 使用非常快速地创建和检索数据库中的数据。

### 1.1 事务 ###

事务由一组操作构成，我们希望这组操作能够全部正确执行，如果这一组操作中的任意一个步骤发生错误，那么就需要回滚之前已经完成的操作。也就是同一个事务中的所有操作，要么全都正确执行，要么全都不要执行。

### 1.2 事务具有的最基本的特性(ACID) ###

*  原子性(Atomicity):原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
 *  一致性(Consistency):如果事务执行之前数据库是一个完整性的状态,那么事务结束后,无论事务是否执行成功,数据库仍然是一个完整性状态.  
    (数据库的完整性状态:当一个数据库中的所有的数据都符合数据库中所定义的所有的约束,此时可以称数据库是一个完整性状态.)
 *  隔离性(Isolation):事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
 *  持久性  
    持久性要求，一个事务完成之后，事务的执行结果必须是持久化保存的。即使数据库发生崩溃，在数据库恢复后事务提交的结果仍然不会丢失。

### 2.1 事务并发执行会出现的问题 ###

我们先来看一下在不同的隔离级别下，数据库可能会出现的问题：

*  更新丢失  
    当有两个并发执行的事务，更新同一行数据，那么有可能一个事务会把另一个事务的更新覆盖掉。  
    当数据库没有加任何锁操作的情况下会发生。
 *  脏读  
    一个事务读到另一个尚未提交的事务中的数据。  
    该数据可能会被回滚从而失效。  
    如果第一个事务拿着失效的数据去处理那就发生错误了。
 *  不可重复读  
    不可重复度的含义：一个事务对同一行数据读了两次，却得到了不同的结果。它具体分为如下两种情况：
 *  虚读：在事务1两次读取同一记录的过程中，事务2对该记录进行了修改，从而事务1第二次读到了不一样的记录。
 *  幻读：事务1在两次查询的过程中，事务2对该表进行了插入、删除操作，从而事务1第二次查询的结果发生了变化。

### 2.2 数据库的四种隔离级别 ###

*  Read uncommitted 读未提交  
    在该级别下，一个事务对一行数据修改的过程中，不允许另一个事务对该行数据进行修改，但允许另一个事务对该行数据读。  
    因此本级别下，不会出现更新丢失，但会出现脏读、不可重复读。
 *  Read committed 读提交  
    在该级别下，未提交的写事务不允许其他事务访问该行，因此不会出现脏读；但是读取数据的事务允许其他事务的访问该行数据，因此会出现不可重复读的情况。
 *  Repeatable read 重复读  
    在该级别下，读事务禁止写事务，但允许读事务，因此不会出现同一事务两次读到不同的数据的情况（不可重复读），且写事务禁止其他一切事务。
 *  Serializable 序列化  
    该级别要求所有事务都必须串行执行，因此能避免一切因并发引起的问题

## 二、数据库设计 ##

#### 好处 ####

减少数据冗余,避免维护繁琐,高效的访问

#### 设计过程: ####

*  需求分析  
    实体间关系  
    唯一标识  
    数据量过大考虑分表分库  
    是否永久存储
 *  \->逻辑设计(建模)  
    ER图,注意属性的域,一对多等关系  
    [设计范式][Link 1]  
    BC范式
 *  \->物理设计

postgresql https://blog.csdn.net/qq\_36762677/article/details/88389624:  
遵循数据库命名规范:  
字符串类型,数字比字符处理快  
长度一致使用char,小于50byte使用char  
精确数据用decimal,非精确使用开销小的float  
避免使用触发器  
严禁使用预留字段

*  \->维护优化  
    维护数据字典,第三方维护数据字典|添加备注维护  
    维护索引,建立索引(在where和groupby和orderby),索引的列不要太长  
    维护表结构  
    水平拆分和垂直拆分

#### Mysql存储引擎 ####

查询搜索引擎命令`SHOW ENGINES`  
InnoDB存储引擎(**default**)

*  支持事务
 *  行锁

MyISAM存储引擎(5.5前默认)

*  批量操作效率高
 *  表锁
 *  支持全文检索
 *  不是真正删除,清理碎片化`optimize table 表名`

MEMORY存储引擎  
![这里写图片描述][70]

## 三、常用SQL ##

#### 1.MYSQL查询每个分类的第一条数据 ####

SELECT * 
    FROM	( SELECT * FROM score GROUP BY type, score DESC ) AS base 
    GROUP BY type

## 四、连接种类 ##

**内连接**：join 或 inner join

select * 
    from table1 
    join table2 on table1.id=table2.id

注释：只返回符合条件的table1和table2的列

等价：

1）select a.*,b.* from table1 a,table2 b where a.id=b.id
    
    2）select * from table1 cross join table2 
    where table1.id=table2.id (注：cross join后加条件只能用where,不能用on)

**外连接**  
1）左连接：left join 或 left outer join

select \* from table1 left join table2 on table1.id=table2.id

注释：包含table1的所有子句，根据指定条件返回table2相应的字段，不符合的以null显示

2）右连接：right join 或 right outer join

select * from table1 right join table2 on table1.id=table2.id

注释：包含table2的所有子句，根据指定条件返回table1相应的字段，不符合的以null显示

3）完整外部联接:full join 或 full outer join

select * from table1 full join table2 on table1.id=table2.id

注释：返回左右连接的和（见上左、右连接）

**交叉连接(完全)**

1） 概念：没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。（table1和table2交叉连接产生3\*3=9条记录）

2） 交叉连接：cross join (不带条件where…)

3） sql语句

select * from table1 cross join table2

注释：笛卡尔积

4） 等价（与下列执行效果相同）

select * from table1,table2

## 五、排序 分页 ##

asc 按升序排列 (不用写，默认使用这个)  
desc 按降序排列  
mysql  
select \* from yourtable where 查询条件 order by id desc limit 0,10;  
按id倒序排列，且取前10条。  
oracle

SELECT * FROM (
       SELECT e.*, rownum r FROM employee WHERE rownum <= 10
    ) t WHERE t.r > 5;

使用rownum的注意事项

不能对rownum使用>（大于1的数值）、>=（大于1的数值）、=（大于1的数值），否则无结果。  
在使用rownum时，只有当Order By的字段是主键时，查询结果才会先排序再计算rownum，但是，对非主键字段（如：name）进行排序时，结果可能就混乱了。出现混乱的原因是：oracle先按物理存储位置（rowid）顺序取出满足rownum条件的记录，即物理位置上的前5条数据，然后在对这些数据按照Order By的字段进行排序，而不是我们所期望的先排序、再取特定记录数。

## 六、SQL ##

#### 空值与null ####

“空值” 和"NULL"的概念：

1.空值('')是不占用空间的,判断空字符用 = '' 或者 <> '' 来进行处理;
    
     2.NULL值是未知的,是“无数据”或“未知数据”，且占用空间，不走索引;判断 NULL 用 IS NULL 或者 is not null , SQL 语句函数中可以使用 ifnull ()函数来进行处理.

**注**：在进行 count ()统计某列的记录数的时候，如果采用的 NULL 值，会别系统自动忽略掉，但是空值是统计到其中

#### 防止sql注入的方法 ####

[ 1. sql注入概念与解决][1. sql]  
[ 2. 防止SQL注入的五种方法][2. _SQL]

#### drop,delete与truncate的区别 ####

1.  DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。  
    TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
2.  表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。
3.  drop直接删掉表；truncate删除表中数据，再插入时自增长id又从1开始 ；delete删除表中数据，可以加where字句。
4.  一般而言，drop > truncate > delete

#### 主键自动生成 ####

<table> 
 <thead> 
  <tr> 
   <th>区别项</th> 
   <th>Mysql</th> 
   <th>Oracle</th> 
   <th>sqlServer</th> 
   <th>PGSql</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>序列号属性</td> 
   <td>AUTO_INCREMENT</td> 
   <td>SEQUENCE</td> 
   <td>IDENTITY/SEQUENCE</td> 
   <td>SEQUENCE</td> 
  </tr> 
 </tbody> 
</table>

Mysql系统提供主键自增

create table t1(
    	id int auto_increment not null,
    	primary key (id)
    );

#### 时间戳 ####

默认当前时间戳`default current_timestamp`  
修改后自动更新`on update current_timestamp`

## 七、 ##

## 八、金额的类型一般怎么设置 ##

8位2小数点  
\`amount\` decimal(8,2) not null comment ‘金额’

## 九、状态的类型怎么设置 ##

\`status\` tinyint(5) not null default ‘0’ comment ‘状态’

## 十、sql语句 ##

条件查询and优先级大于or  
mysql自增主键起始值

alter table users AUTO_INCREMENT=10000;

### 3.存储过程 ###

存储过程实际上是一组T-SQL语句  
存储过程预先被编译存放在服务器的系统中  
存储过程可以完成某一特定的业务逻辑

sql语句在执行时要先编译,然后被执行,为了提高效率,将完成特定功能的sql语句进行编译优化后,存储在数据库服务器中,用户通过指定存储过程名调用

create proceduce sp_name @[参数名][类型]
    as
    begin
    	...
    end

执行存储过程`exec sp_name [参数名]`  
删除存储过程`drop procedure sp_name`  
可以完成复杂的判断和运算,并且可以保证数据的安全性和完整性  
[mybatis调用存储过程][mybatis]

### 4.触发器 ###

使用触发器保证数据完整性,有效性  
instead of事前触发,做准备工作  
after事后触发,收尾工作  
语句级触发器,行级触发器

### 5.视图 ###

视图是从一个或多个基本表导出的表，它是虚表  
某一用户可以定义若干个视图  
视图可以用来定义新的视图  
[mybatis创建视图][mybatis 1]

### 6.日志 ###

重做日志（redo log）作用：确保事务的持久性。  
回滚日志（undo log）作用：保存了事务发生之前的数据的一个版本，可以用于回滚  
二进制日志（binlog）：作用：用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。用于数据库的基于时间点的还原。

### 7.union和union all ###

*  union表求并集合后去掉重复元素,对产生的结果集进行排序运算,删除重复数据再返回结果  
    \-union all合并两个结果集合后就会返回结果,速度会快很多

### between ###

between用法：用于where表达式中，选取两个值之间的数据，如：

SELECT id FROM user WHERE id BETWEEN value1 AND value2;

例子可以看出查出来的结果是 2=< id <=5；可见在mysql数据库中，between查出的两个值是闭区间。

### pgsql ###

postgreSQL 自增需要使用序列  
https://blog.csdn.net/u011042248/article/details/49422305

postgresql nextval  
https://blog.51cto.com/13000661/2125889  
字符串包含  
https://blog.csdn.net/SELECT\_BIN/article/details/83016685  
数组类型

### SQL之COLLATE 子句 排序规则 ###

https://blog.csdn.net/akof1314/article/details/4763752

### 包含 ###

https://blog.csdn.net/qq\_38225873/article/details/84379660

https://blog.csdn.net/linxiang1aa/article/details/81180647

## 十一、索引(索引是一种数据结构) ##

使用索引的全部意义就是通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。  
**加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录的辅助数据结构。**

一个索引是存储的表中一个特定列的值数据结构。  
索引是在表的列上创建。所以，要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中。

代价：增加了数据库的存储空间;二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)

### 索引种类 ###

普通索引：仅加速查询  
唯一索引：加速查询 + 列值唯一（可以有null）  
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个  
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并  
全文索引：对文本的内容进行分词，进行搜索

### 索引类型 ###

1.  FULLTEXT  
    即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。  
    全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
2.  HASH  
    由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。  
    HASH索引可以一次定位，不**需要像树形索引那样逐层查找,因此具有极高的效率**。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
3.  BTREE  
    BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
4.  RTREE  
    RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

- 简单索引
    CREATE INDEX mytable_categoryid ON mytable (category_id);
    -多重索引
    CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);

### B-Tree 是最常用的用于索引的数据结构。 ###

数据库索引怎么实现的  
b树和b+树的区别  
画一个b+树

[------_------]: https://db-engines.com/en/ranking
[Link 1]: https://blog.csdn.net/gui951753/article/details/79609874
[70]: /images/20220514/a7f4d3e7e1fd46298c4594b7f3c9e0bd.png
[1. sql]: https://blog.csdn.net/whp1473/article/details/79591629
[2. _SQL]: https://blog.csdn.net/qq_37787456/article/details/71479907
[mybatis]: https://www.cnblogs.com/xdp-gacl/p/4270352.html
[mybatis 1]: https://www.cnblogs.com/shihaiming/p/6255207.html