数据库面试知识点（一）基本概念

桃扇骨 2021-09-29 09:26 344阅读 0赞

> 1.  主键、外键、超键、候选键

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：是最小超键，即没有冗余元素的超键。

主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

外键：在一个表中存在的另一个表的主键称此表的外键。

注：只能能唯一标识元组的属性集就是超键，所以，超键不唯一，并且，超键可以是一个属性也可以是多个属性，同时候选键是最少的超键，那么候选键也是不唯一的，但是一个元组，主键是唯一的，是从候选键中选择一个作为主键。

> 1.  为什么用自增列作为主键

如果我们定义了主键 (PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引、

如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引、

如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐含的聚集索引 (ROWID 随着行记录的写入而主键递增，这个 ROWID 不像 ORACLE 的 ROWID 那样可引用，是隐含的)。

数据记录本身被存于主索引（一颗 B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL 会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB 默认为 15/16），则开辟一个新的页（节点）

如果表使用**自增主键**，那么每次插入新的记录，**记录就会顺序添加到当前索引节点的后续位置**，当一页写满，就会自动开辟一个新的页

如果使用**非自增主键**（如果身份证号或学号等），由于每次插入主键的值**近似于随机**，因此每次新纪录都要被插到现有索引页得中间某个位置，此时 MySQL 不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。

> 1.  触发器的作用

触发器是一种特殊的存储过程，主要是通过**事件来触发而被执行**的。它可以**强化约束**，来维护数据的**完整性和一致性**，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

> 1.  什么是存储过程？用什么来调用？

存储过程是一个预编译的 SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次 SQL，使用存储过程比单纯 SQL 语句执行要快。

调用：

1）可以用一个命令对象来调用存储过程。

2）可以供外部程序调用，比如：java 程序。

> 1.  存储过程的优缺点？

优点：

1）存储过程是预编译过的，执行效率高。

2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。

3）安全性高，执行存储过程需要有一定权限的用户。

4）存储过程可以重复使用，可减少数据库开发人员的工作量。

缺点：移植性差

> 1.  存储过程与函数的区别

存储过程是第一次编译之后就会被存储的下来的预编译对象，之后无论何时调用它都会去执行已经编译好的代码。而函数每次执行都需要编译一次。总结下来有下面几个区别：

*  函数必须有一个返回值而存储过程则不是必须的（存储过程可以返回 0 个到 n 个值）；
 *  函数只能有输入参数而存储过程可以同时又输入和输出参数；
 *  函数至少有一个参数而存储过程可能需要 n 个参数；
 *  函数可以被存储过程调用而存储过程不可以被函数调用；
 *  函数有且只有一个输入参数和一个返回值，而存储过程没有这个限制；
 *  返回表变量的函数可以当做 VIEW 或者临时表用在 WHERE/HAVING/SELECT/JOIN 语句中而存储过程不可以；
 *  存储过程中可以使用 try-catch 块和事务，而函数中不可以

> 1.  什么叫视图？游标是什么？

视图：

是一种虚拟的表，具有和物理表相同的功能。可以对视图进行**增，改，查，操**作，视图通常是有一个表或者多个表的行或列的**子集**。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。

游标：

是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

> 1.  视图的优缺点

优点：

1 对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。

1.  用户通过简单的查询可以从复杂查询中得到结果。
2.  维护数据的独立性，视图可从多个表检索数据。
3.  对于相同的数据可产生不同的视图。

缺点：

性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个**复杂的多表查询所定义**，那么，那么就无法更改数据

> 1.  drop、truncate、 delete 区别

最基本：

*  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.  什么是临时表，临时表什么时候删除？

临时表可以手动删除：  
DROP TEMPORARY TABLE IF EXISTS temp\_tb;

临时表只在当前连接可见，当关闭连接时，MySQL 会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表，并且操作属于本连接的临时表。  
创建临时表的语法与创建表语法类似，不同之处是增加关键字 TEMPORARY，

如：

CREATE TEMPORARY TABLE tmp\_table (

NAME VARCHAR (10) NOT NULL,

time date NOT NULL  
);

select \* from tmp\_table;

> 1.  非关系型数据库和关系型数据库区别，优势比较？

**非关系型数据库的优势：**

*  性能：NOSQL 是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过 SQL 层的解析，所以性能非常高。
 *  可扩展性：同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

**关系型数据库的优势：**

*  复杂查询：可以用 SQL 语句方便的在一个表以及多个表之间做非常复杂的数据查询。
 *  事务支持：使得对于安全性能很高的数据访问要求得以实现。

**其他：**

1.  对于这两类数据库，对方的优势就是自己的弱势，反之亦然。
2.  NOSQL 数据库慢慢开始具备 SQL 数据库的一些复杂查询功能，比如 MongoDB。
3.  对于事务的支持也可以用一些系统级的原子操作来实现例如乐观锁之类的方法来曲线救国，比如 Redis set nx。

> 1.  数据库范式，根据某个场景设计数据表？

第一范式:(确保每列保持原子性) **所有字段值都是不可分解的原子值**。

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到 “地址” 这个属性，本来直接将 “地址” 属性设计成一个数据库表的字段就行。但是如果系统经常会访问 “地址” 属性中的 “城市” 部分，那么就非要将 “地址” 这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。

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

第二范式:(**确保表中的每列都和主键相关**) 在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说**在一个数据库表中，一个表中只能保存一种数据**，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。

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

第三范式:(确保**每列都和主键列直接相关，而不是间接相关**) 数据表中的每一列数据都和主键直接相关，而不能间接相关。

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

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

BCNF: 符合 3NF，并且，主属性不依赖于主属性。

若关系模式属于第二范式，且每个属性都不传递依赖于键码，则 R 属于 BC 范式。

通常 BC 范式的条件有多种等价的表述：每个非平凡依赖的左边必须包含键码；每个决定因素必须包含键码。

BC 范式既检查非主属性，又检查主属性。当只检查非主属性时，就成了第三范式。满足 BC 范式的关系都必然满足第三范式。  
还可以这么说：**若一个关系达到了第三范式，并且它只有一个候选码，或者它的每个候选码都是单属性，则该关系自然达到 BC 范式**。  
一般，一个数据库设计符合 3NF 或 BCNF 就可以了。

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

第四范式: 要求把同一表内的多对多关系删除。

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

第五范式: 从最终结构重新建立原始结构。

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

> 1.  什么是 内连接、外连接、交叉连接、笛卡尔积等？

内连接: 只连接匹配的行

左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行,把匹配的信息进行笛卡尔积

右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行，把匹配的信息进行笛卡尔积

例如 1：  
SELECT a.,b. FROM luntan LEFT JOIN usertable as b ON a.username=b.username

例如 2：  
SELECT a.,b. FROM city as a FULL OUTER JOIN user as b ON a.username=b.username

全外连接: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。

交叉连接: 生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

例如：  
SELECT type,pub\_name FROM titles CROSS JOIN publishers ORDER BY type

注意：

很多公司都只是考察是否知道其概念，但是也有很多公司需要不仅仅知道概念，还需要动手写 sql, 一般都是简单的连接查询，

> 1.  varchar 和 char 的使用场景？

1.char 的长度是不可变的，而 varchar 的长度是可变的。

定义一个 char \[10\] 和 varchar \[10\]。  
如果存进去的是‘csdn’, 那么 char 所占的长度依然为 10，除了字符‘csdn’外，后面跟六个空格，varchar 就立马把长度变为 4 了，取数据的时候，char 类型的要用 trim () 去掉多余的空格，而 varchar 是不需要的。

2.char 的存取数度还是要比 varchar 要快得多，因为其长度固定，方便程序的存储与查找。  
char 也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。  
varchar 是以空间效率为首位。

3.char 的存储方式是：对英文字符（ASCII）占用 1 个字节，对一个汉字占用两个字节。  
varchar 的存储方式是：对每个英文字符占用 2 个字节，汉字也占用 2 个字节。

4.两者的存储数据都非 unicode 的字符数据。

> 1.  SQL 语言分类

SQL 语言共分为四大类：

*  数据查询语言 DQL
 *  数据操纵语言 DML
 *  数据定义语言 DDL
 *  数据控制语言 DCL。

1.  **数据查询语言 DQL**

数据查询语言 DQL 基本结构是由 SELECT 子句，FROM 子句，WHERE 子句组成的查询块：

SELECT  
FROM  
WHERE

1.  **数据操纵语言 DML**

数据操纵语言 DML 主要有三种形式：

(1) 插入：INSERT

(2) 更新：UPDATE

(3) 删除：DELETE

1.  **数据定义语言 DDL**

数据定义语言 DDL 用来创建数据库中的各种对象 ----- 表、视图、索引、同义词、聚簇等如：

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER  
表 视图 索引 同义词 簇

DDL 操作是隐性提交的！不能 rollback

1.  **数据控制语言 DCL**

数据控制语言 DCL 用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

(1) GRANT：授权。

(2) ROLLBACK \[WORK\] TO \[SAVEPOINT\]：回退到某一点。回滚 —ROLLBACK；回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK;

(3) COMMIT \[WORK\]：提交。

在数据库的插入、删除和修改操作时，只有当事务在提交到数据  
库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看  
到所做的事情，别人只有在最后提交完成后才可以看到。  
提交数据有三种类型：显式提交、隐式提交及自动提交。下面分  
别说明这三种类型。

(1) 显式提交  
用 COMMIT 命令直接完成的提交为显式提交。其格式为：  
SQL>COMMIT；

(2) 隐式提交  
用 SQL 命令间接完成的提交为隐式提交。这些命令是：  
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME

(3) 自动提交  
若把 AUTOCOMMIT 设置为 ON，则在插入、修改、删除语句执行后，  
系统将自动进行提交，这就是自动提交。其格式为：  
SQL>SET AUTOCOMMIT ON；

> 1.  like % 和 - 的区别

通配符的分类:

**% 百分号通配符**: 表示任何字符出现任意次数 (可以是 0 次).

**\_下划线通配符**: 表示只能匹配单个字符，不能多也不能少，就是一个字符.

**like 操作符**: LIKE 作用是指示 mysql 后面的搜索模式是利用通配符而不是直接相等匹配进行比较.

**注意**: 如果在使用 like 操作符时，后面的没有使用通用匹配符效果是和 = 一致的，SELECT \* FROM products WHERE products.prod\_name like ‘1000’;  
只能匹配的结果为 1000, 而不能匹配像 JetPack 1000 这样的结果.

*  % 通配符使用：匹配以 “yves” 开头的记录：(包括记录 “yves”) SELECT FROM products WHERE products.prod\_name like ‘yves%’;  
    匹配包含 “yves” 的记录 (包括记录 “yves”) SELECT FROM products WHERE products.prod\_name like ‘%yves%’;  
    匹配以 “yves” 结尾的记录 (包括记录 “yves”, 不包括记录 “yves”, 也就是 yves 后面有空格的记录，这里需要注意) SELECT \* FROM products WHERE products.prod\_name like ‘% yves’;
 *  下划线通配符使用: SELECT *FROM products WHERE products.prod\_name like ‘\_yves’; 匹配结果为：像 “yyves” 这样记录.  
    SELECT* FROM products WHERE products.prodname like ‘yves\_\_’; 匹配结果为：像 “yvesHe” 这样的记录.(一个下划线只能匹配一个字符，不能多也不能少)

**注意事项:**

*  注意大小写，在使用模糊匹配时，也就是匹配文本时，mysql 是可能区分大小的，也可能是不区分大小写的，这个结果是取决于用户对 MySQL 的配置方式。如果是区分大小写，那么像 YvesHe 这样记录是不能被 “yves\_\_” 这样的匹配条件匹配的.
 *  注意尾部空格，"% yves" 是不能匹配 “heyves” 这样的记录的.
 *  注意 NULL,% 通配符可以匹配任意字符，但是不能匹配 NULL, 也就是说 SELECT \* FROM products WHERE products.prod\_name like '%; 是匹配不到 products.prod\_name 为 NULL 的的记录.

**技巧与建议:**

正如所见， MySQL 的通配符很有用。但这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。

不要过度使用通配符。如果其他操作符能达到相同的目的，应该 使用其他操作符。

在确实需要使用通配符时，除非绝对有必要，否则不要把它们用 在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起 来是最慢的。  
仔细注意通配符的位置。如果放错地方，可能不会返回想要的数.

> 1.  count (\*)、count (1)、count (column) 的区别

*  count (\*) 对行的数目进行计算，包含 NULL
 *  count (column) 对特定的列的值具有的行数进行计算，不包含 NULL 值。
 *  count () 还有一种使用方式，count (1) 这个用法和 count (\*) 的结果是一样的。

**性能问题:**

1.  任何情况下 SELECT COUNT (\*) FROM tablename 是最优选择；
2.  尽量减少 SELECT COUNT (\*) FROM tablename WHERE COL = ‘value’ 这种查询；
3.  杜绝 SELECT COUNT (COL) FROM tablename WHERE COL2 = ‘value’ 的出现。

如果表没有主键，那么 count (\*) 比 count (1) 快。

如果有主键，那么 count (主键，联合主键) 比 count (\*) 快。

如果表只有一个字段，count (\*) 最快。

count (1) 跟 count (主键) 一样，只扫描主键。count (\*) 跟 count (非主键) 一样，扫描整个表。明显前者更快一些。

> 1.  最左前缀原则

多列索引：

ALTER TABLE people ADD INDEX lname\_fname\_age (lame,fname,age);

为了提高搜索效率，我们需要考虑运用多列索引，由于索引文件以 B－Tree 格式保存，所以我们不用扫描任何记录，即可得到最终结果。

注：在 mysql 中执行查询时，只能使用一个索引，如果我们在 lname,fname,age 上分别建索引，执行查询时，只能使用一个索引，mysql 会选择一个最严格 (获得结果集记录数最少) 的索引。

最左前缀原则：顾名思义，就是最左优先，上例中我们创建了 lname\_fname\_age 多列索引，相当于创建了 (lname) 单列索引，(lname,fname) 组合索引以及 (lname,fname,age) 组合索引。