突击 | 数据库理论20题

比眉伴天荒 2022-09-12 11:58 122阅读 0赞

## 前言 ##

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

![d9c2dba1ffcba869e07511889e6a0f33.png][]

> 大家好，我是蓝蓝。数据库在国企，银行，研究所的面试过程中，是占比非常大的，也是及其可能被问的知识点，我将其分为四个部分，分为基础理论篇，事务篇，优化篇，大概一共100个题目，最后一部分是最高频的题目，大家加油哈

# 一 关于数据库理论 #

## 1 char、varchar的区别是什么？ ##

平时使用 **char** 类型定义字段时，往往会指定其长度 **M**，即 **char(M)**。其实 **M** 指的是字符数，即这个字段最多存储多少个字符，**M** 可不指定，默认为 **1**，范围是\[0,255\]，单个字母、数字、中文等都是占用一个字符。**utf8** 字符集下一个中文字符占用3个字节。这个怎么去测试呢。举个例子

先来创建一个表

CREATE TABLE `char_lanlan1` (
      `col1` char DEFAULT NULL,
      `col2` char(5) DEFAULT NULL,
      `col3` char(10) DEFAULT NULL
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

执行下方语句来测试 **char(x)** 中 x 的范围。

alter table char_tb1 add column col4 char(0);
    alter table char_tb1 add column col4 char(0);

如果是下方的语句，则会报错了

alter table char_tb1 add column col5 char(256);

**VARCHAR** 用于存储可变字符串，是最常见的字符串数据类型。它比 **CHAR** 更节省空间，因为它仅使用必要的空间。**VARCHAR** 需要 **1** 或 **2** 个额外字节记录字符串长度，如果列的最大长度不大于 **255** 字节则只需要 **1** 字节。

**CHAR** 是定长的，根据定义的字符串长度分配足够的空间。**CHAR** 会删除末尾空格。假如申请了`char(10)`的空间,那么无论实际存储多少内容.该字段都占用10个字符,而varchar是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间.

**CHAR** 适合存储很短的字符串，或所有值都接近同一个长度，例如存储密码的 **MD5** 值。对于经常变更的数据，**CHAR** 也比 **VARCHAR** 更好，因为定长的 CHAR 不容易产生碎片。对于非常短的列，**CHAR** 在存储空间上也更有效率，例如用 **CHAR** 来存储只有 **Y** 和 **N** 的值只需要一个字节，但是 **VARCHAR** 需要两个字节，因为还有一个记录长度的额外字节。

在检索效率上来讲，**CHAR > VARCHAR**，因此在使用中，如果确定某个字段的值的长度，可以使用char，否则应该尽量使用 **VARCHAR**.例如存储用户 **MD5** 加密后的密码,则应该使用 **CHAR** 。

**适用场景**：字符串列的最大长度比平均长度大很多、列的更新很少、使用了 **UTF8** 这种复杂字符集，每个字符都使用不同的字节数存储。

## 2 数据库中事务的四大特性（ACID） ##

> 什么是事务

事务由单独单元的一个或多个 **SQL** 语句组成，在这个单元中，每个 **SQL** 语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条 **SQL** 语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有 **SQL** 语句均执行成功，则事物被顺利执行。

*  原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。

**如果无法保证原子性会怎么样？** **OK**，就会出现**数据不一致**的情形，**A** 账户减去 **50** 元，而 **B** 账户增加 **50** 元操作失败。系统将无故丢失 **50** 元~

*  一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

拿转账来说，假设用户 **A** 和用户 **B** 两者的钱加起来一共是 **2000**，那么不管 **A** 和 **B** 之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是 **2000**，这就是事务的一致性。

**如果无法保证一致性会怎么样？** 例一:A账户有 **200** 元，转账 **300** 元出去，此时A账户余额为 **-100** 元。你自然就发现了此时数据是不一致的，为什么呢？因为你定义了一个状态，余额这列必须大于 **0**。例二 : **A** 账户 **200** 元，转账 **50** 元给 **B** 账户，**A** 账户的钱扣了，但是 **B** 账户因为各种意外，余额并没有增加。你也知道此时数据是不一致的，为什么呢？因为你定义了一个状态，要求A+B的余额必须不变。

*  隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

即要达到这么一种效果：对于任意两个并发的事务 **T1** 和 **T2**，在事务 **T1** 看来，**T2** 要么在 **T1** 开始之前就已经结束，要么在 **T1** 结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

*  持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

## 3 什么是关系型数据库？ ##

关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。

> **什么是 SQL**

结构化查询语言 (Structured Query Language) 简称 SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

> **什么是MySQL？**

MySQL 是一个关系型数据库管理系统，MySQL 是最流行的关系型数据库管理系统之一，常见的关系型数据库还有 Oracle 、SQL Server、Access 等等。

MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，广泛地应用在 Internet 上的中小型网站中。

> **MySQL 有哪些数据类型？**

MySQL 数据类型非常丰富，常用类型简单介绍如下：

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT。

浮点数类型：FLOAT、DOUBLE、DECIMAL。

字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB。

日期类型：Date、DateTime、TimeStamp、Time、Year。

其他数据类型：BINARY、VARBINARY、ENUM、SET…

## 4 FLOAT和DOUBLE的区别是什么？ ##

float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型；

MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，如float(7,4)的 可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001。

FLOAT和DOUBLE在不指 定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。

## 5 超键、候选键、主键、外键分别是什么？ ##

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

举个例子，比如有如下数据

> 超键

在关系中能**唯一**标识元组的属性集称为关系模式的超键。于是我们从例子中可以发现 学号是标识学生实体的唯一标识。那么该元组的**超键就为学号**。  
除此之外我们还可以把它跟其他属性组合起来，比如：(学号，性别)(学号，年龄)，这样也是超键.

> 候选键

不含多余属性的超键为候选键。根据例子可知，学号是一个可以唯一标识元组的唯一标识，因此学号是一个候选键，实际上，候选键是超键的子集，比如 （学号，年龄）是超键，但是它不是候选键。因为它还有了额外的属性。

> 主键

用户选择的候选键作为该元组的唯一标识，那么它就为主键。简单的说，例子中的元组的候选键为学号，但是我们选定他作为该元组的唯一标识，那么学号就为主键。

> 外键

外键是相对于主键的，比如在学生记录里，主键为学号，在成绩单表中也有学号字段，因此学号为成绩单表的外键，为学生表的主键。所以，主键为候选键的子集，候选键为超键的子集，而外键的确定是相对于主键的。

## 6 SQL 约束有哪几种 ##

*  NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
 *  UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
 *  PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
 *  FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
 *  CHECK: 用于控制字段的值范围。

CREATE TABLE test1(
    uid INT NOT NULL ,
    `name` VARCHAR(50) ,
    `password` VARCHAR(20) NOT NULL DEFAULT '0',
    class_id INT NOT NULL,
    UNIQUE(uid),
    PRIMARY KEY(uid),
    FOREIGN KEY(class_id) REFERENCES class_table(class_id),
    CHECK (class_id < 1000)
    );

## 7 MySQL的四种隔离级别 ##

*  未提交读(READ UNCOMMITTED)

这就是上面所说的例外情况了,这个隔离级别下,其他事务可以看到本事务没有提交的部分修改.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).

这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用.

*  已提交读(READ COMMITTED)

其他事务只能读取到本事务已经提交的部分.这个隔离级别有 不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改.

*  REPEATABLE READ(可重复读)

可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是 幻读,当你读取 id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条 id=11 的数据,因为是新插入的,所以不会触发上面的锁的排斥,那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.

*  SERIALIZABLE(可串行化)

这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.

## 8 说说三个范式 ##

1范式：1NF 是对属性的原子性约束，要求属性具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

2范式：2NF 是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

3范式：3NF 是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余，没有冗余的数据库设计可以做到。

但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据，具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑，降低范式就是增加字段，允许冗余。

## 9 什么是子查询 ##

*  条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果
 *  嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

## 10 连接分为内连接、左连接、右连接 ##

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

> 举个例子 先创建表

CREATE TABLE `a_table` (
      `a_id` int(11) DEFAULT NULL,
      `a_name` varchar(10) DEFAULT NULL,
      `a_part` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    CREATE TABLE `b_table` (
      `b_id` int(11) DEFAULT NULL,
      `b_name` varchar(10) DEFAULT NULL,
      `b_part` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    select * from a_table;
    
    INSERT into  a_table VALUES("1","蓝蓝","总裁部");
    INSERT into  a_table VALUES("2","绿绿","秘书部");
    INSERT into  a_table VALUES("3","红红","设计部");
    INSERT into  a_table VALUES("4","花花","运营部");
    
    INSERT into  b_table VALUES("2","绿绿","秘书部");
    INSERT into  b_table VALUES("3","红红","设计部");
    INSERT into  b_table VALUES("5","青青","人事部");
    INSERT into  b_table VALUES("6","黄黄","生产部");

两个表长啥样子勒。

![fab295216b00d2daf87163bd6662a30c.png][] ![47252a754e55db5a21b621694388a31e.png][]

*  内连接

内连接使用关键字 Inner join on，关联条件为a.a\_id = b.b\_id;

select * from a_table a inner join b_table b on a.a_id = b.b_id;

结果就是下面这个样子

![cec20493385f08e80184007ceebacf6f.png][]

这就说明了，内链接是组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

*  左链接

左连接使用的是 left join on，关联条件和上方一样 a.a\_Id=b.b\_id。

select * from a_table a left join b_table b on a.a_id = b.b_id;

结果是下面这个样子

![64e0e2d0819288b4ee121e62ecf9f8dd.png][]

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。

左(外)连接，左表(a\_table)的记录将会全部表示出来，而右表(b\_table)只会显示符合搜索条件的记录。**右表记录不足的地方均为NULL**。

*  右链接

和左连接对应，使用关键字 right join on。

select * from a_table a  right outer join b_table b  on a.a_id = b.b_id;

![b02112220970ccb80ae4e494b2412832.png][]

综上可知，right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。  
与左(外)连接相反，右(外)连接，左表(a\_table)只会显示符合搜索条件的记录，而右表(b\_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

## 11 存储过程(procedure)和函数(function)区别 ##

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

相同点

*  存储过程和函数都是为了可重复的执行操作数据库的 SQL 语句的集合。
 *  存储过程和函数都是一次编译后缓存起来，下次使用就直接命中已经编译好的 sql 语句，减少网络交互提高了效率。

不同点

*  标识符不同，函数的标识符是 function，存储过程是 procedure。
 *  函数返回单个值或者表对象，而存储过程没有返回值，但是可以通过 OUT 参数返回多个值。
 *  函数限制比较多，比如不能用临时表，只能用表变量，一些函数都不可用等，而存储过程的限制相对就比较少。
 *  一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
 *  函数的参数只能是 IN 类型，存储过程的参数可以是 IN OUT INOUT 三种类型。
 *  存储函数使用 select 调用，存储过程需要使用 call 调用。

## 12 关系数据库哪些特点 ##

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

## 13 什么是存储过程及如何调用 ##

存储过程和视图一样，也是对 SQL 代码进行了封装，可以反复利用。优点同样是清晰且安全，同时还可以减少网络传输量。

和视图不一样的一点在于，视图是虚拟表，不会对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，能够实现一些更加复杂的处理。

简单的说，存储过程是 SQL 语句和控制语句构成的语句结合，也可以将其理解为函数，可以接受参数，也可以返回输出参数给调用者。

> 什么是存储过程

存储过程的英文为 Stored Procedure。一旦创建了存储过程，我们就像调用函数一样调用存储过程，直接调用存储过程名即可。

> 如何创建一个存储过程

CREATE PROCEDURE 存储过程名称([参数列表])
    BEGIN
        需要执行的语句
    END

上述即存储过程的定义方式，由 BEGIN 和 END 定义需要执行的语句，如果要删除存储过程，使用 DROP PROCEDURE。如果要更新存储过程，我们使用 ALTER PROCEDURE。

亥，做个例子，我如果想实现一个简单的存储过程，累加运算，1+2+…+n，如何实现

CREATE PROCEDURE `add_num`(IN N INT)
    BEGIN
        DECLARE i INT;--- 使用DECLARE 声明变量
        DECLARE sum INT;
        SET i = 1;--- 使用 SET 赋值语句
        SET sum = 0;
        while i<=n do
            SET sum = sum + i;
            SET i = i+1;
        END WHILE;
        SELECT sum;
    END

写完了以后，我们就可以通过 **CALL** add\_sum(50) 调用即可。

> 在工作都会怎么样使用存储过程，优点是什么

一个非常明显的优点即存储过程可以一次编译多次使用，存储过程只是在创造的时候进行编译，之后的使用就不需要重新编译，这直接提升 **SQL** 的执行效率。

另外，将代码封装成模块，这种模块化的思想可以减少很多重复的工作，减少开发工作量的同时，还能保证代码的结构清晰。

最后，存储过程的安全性较高，在设定存储过程的时候可以设置用户的使用权限。

> 缺点是什么呢

它的可移植性差，存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。其次调试困

难，只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。此外，存储过程的版本管理也很困难，比如数据表索引发生变化

了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。最后它不适合高并

发的场景，高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加

数据库的压力，显然就不适用了。

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

**调用：**

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

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

## 14 存储过程的优点有哪些 ##

**优点：**

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

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

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

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

**缺点：**移植性差

## 15 存储过程和函数的区别 ##

![bfb6513c783eff4104cfe57eb6127e4a.png][] img

## 16.drop、truncate、 delete区别 ##

**相同点：**

truncate和不带where子句的delete,以及drop都会删除表内的数据

**不同点：**

*  truncate会清除表数据并重置id从1开始,delete就只删除记录,drop可以用来删除表或数据库并且将表所占用的空间全部释放
 *  truncate和delete只删除数据不删除表的结构。drop语句将删除表的结构被依赖的约(constrain),触发器(trigger),依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
 *  速度上一般来说: drop> truncate > delete
 *  使用上,想删除部分数据行用 delete，想删除表用 drop,想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。
 *  delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。

## 17 什么是触发器，MySQL中都有哪些触发器？ ##

触发器是指一段代码，当触发某个事件时，自动执行这些代码。在MySQL数据库中有如下六种触发器：

*  1、Before Insert
 *  2、After Insert
 *  3、Before Update
 *  4、After Update
 *  5、Before Delete
 *  6、After Delete

## 18 视图的作用 ##

视图以方便帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户指定不同的查询视图，举个例子，针对公司的 HR，我们只想她看部分数据，而某些特殊的数据，就不会提供同给她。

> 使用视图的优点

*  安全性

虚拟表是基于底层数据表的，我们在使用视图时，一般不会轻易通过视图对底层数据进行修改，即使是使用单表的视图，也会受到限制，比如计算字段，类型转  
换等是无法通过视图来对底层数据进行修改的，这也在一定程度上保证了数据表的数据安全性。同时，我们还可以针对不同用户开放不同的数据查询权限，比如人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。

*  简单清晰

视图是对 SQL 查询的封装，它可以将原本复杂的 SQL 查询简化，在编写好查询之后，我们就可以直接重用它而不必要知道基本的查询细节。同时我们还可以在视图之上再嵌套视图。这样就好比我们在进行模块化编程一样，不仅结构清晰，还提升了代码的复用率。

## 19.视图的作用，视图可以更改么? ##

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询;不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据;视图创建后，可以使用与表相同的方式利用它们。

视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有 **order by** 则对视图再次 **order by** 将被覆盖。

**MySQL** 有关权限的表都有哪几个 **MySQL** 服务器通过权限表来控制用户对数据库的访问，权限表存放在 **MySQL** 数据库里，由 **mysql\_install\_db** 脚本初始化。这些权限表分别**user，db，table\_priv，columns\_priv** 和 **host**。下面分别介绍一下这些表的结构和内容：

**user** 权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。  
**db** 权限表：记录各个帐号在各个数据库上的操作权限。  
**table\_priv** 权限表：记录数据表级的操作权限。  
**columns\_priv** 权限表：记录数据列级的操作权限。  
**host** 权限表：配合 db 权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受 **GRANT** 和 **REVOKE** 语句的影响。

## 20 非关系型数据库和关系型数据库区别，优势比较? ##

（1）关系型数据库

*  优点：

二维表格，容易理解。容易操作。易于维护。支持 **SQL**。

*  缺点：

读写性能较差。固定的表结构，不够灵活。应对高并发场景，磁盘 **I/O** 存在瓶颈。海量数据的读写性能差。

（2）非关系型数据库

*  优点：

不需要 **SQL** 解析，读写性能高。可以使用内存或者硬盘作为载体，速度快。基于键值对，数据没有耦合性，方便扩展。部署简单。

*  缺点：

不支持SQL，增加了学习成本。没有事务。

## 21 关于三个范式 ##

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段 x → 非关键字段y

> 第一范式：要求每列都是最小的数据单元，不可分割。

比如学生表（学号、姓名、性别、出生年月），出生年月还可以分为（出生年、出生月、出生日），那么它就不符合第一范式了。

> 第二范式：在第一范式的基础上，要求每列都和主键相关。

比如学生表（学号、姓名、课程号、学分），这里姓名依赖学号、学分依赖课程号，第二范式强调非主键字段必须依赖主键，所以不符合第二范式。

可能会存在的问题：（1）数据冗余：每条记录都含有相同的信息，比如所有学生都选了同一门课程。（2）删除异常：删除学生，则对应的课程也被删除了。（3）插入异常：学生未选课，则无法插入数据库。（4）更新异常：调整课程学分，可能所有行都要更新。

正确的设计应该如下：学生表（学号、姓名） 课程表（课程号、学分） 学生选课表（学号、课程号、成绩）

> 第三范式：在第二范式的基础上，要求每列都和主键直接相关，而不是间接相关。

比如学生表（学号、姓名、年龄、学院名称、学院电话）

因为存在依赖关系：学号->学生->所在学院->学院电话，而第三范式要求任何字段都不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖。

可能存在的问题：（1）数据冗余：重复保存学院信息 （2）更新异常：更新学院信息时，可能需要更新多条记录，不然会出现数据不一致的情况。

正确的设计应该如下：学生表（学号、姓名、年龄、所在学院） 学院表（学院名称、学院电话）

> 同时有多个事务在进行会怎么样呢?

多事务的并发进行一般会造成以下几个问题:

*  脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.
 *  不可重复读: 当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询,结果竟然不一样,因为在此期间B事务进行了提交操作.
 *  幻读: A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成"幻觉".

## 22 Mysql 有哪些数据类型，使用的时候有没有什么注意点 ##

（1）整数类型：`BIT`、`BOOL`、`TINY INT`、`SMALL INT`、`MEDIUM INT`、`INT`、`BIG INT`。（2）浮点数类型：`FLOAT`、`DOUBLE`、`DECIMAL`。（3）字符串类型：`CHAR`、`VARCHAR`、`TINY TEXT`、`TEXT`、`MEDIUM TEXT`、`LONGTEXT`、`TINY BLOB`、`MEDIUM BLOB`、`LONG BLOB`。（4）日期类型：`DATE`、`DATETIME`、`TIMESTAMP`、`TIME`、`YEAR`

使用的时候建议遵循从小原则。

（1）使用`char`或者`varchar`的时候，注意`char`会去掉字符串末尾的空格。（2）使用`text`或者`blob`的时候，注意定期清理碎片空间，使用`OPTIMIZE TABLE`命令。（3）浮点数会造成精度丢失，尽量使用`decimal`。

## 23 有没有使用过数据库的视图 ##

使用 `create view view_name as select * from tb`创建视图。使用`create or replace view view_name as select * from tb`修改视图。使用`select * from view_name`正常查询视图。使用`drop view view_name`删除视图。

> **SQL 语句有哪些分类？**

1.  DDL：数据定义语言（create alter drop）
2.  DML：数据操作语句（insert update delete）
3.  DTL：数据事务语句（commit collback savapoint）
4.  DCL：数据控制语句（grant revoke）

## 24 mysql中 in 和 exists 区别 ##

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

*  如果查询的两个表大小相当，那么用in和exists差别不大。
 *  如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
 *  not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

## 25 关于触发器了解吗尽量说 ##

好家伙，尽量说就使劲儿说呗。从是什么，什么作用都给安排了

> 什么是触发器

触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。简单理解为：你执行一条sql语句，这条sql语句的执行会自动去触发执行其他的sql语句。

> 触发器作用

*  可在写入数据表前，强制检验或转换数据。
 *  触发器发生错误时，异动的结果会被撤销。
 *  部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器。
 *  可依照特定的情况，替换异动的指令 (INSTEAD OF)。

> 触发器四要素

*  监视地点（table）
 *  监视事件（insert、update、delete）
 *  触发时间（after、before）
 *  触发事件（insert、update、delete）

> 触发器基本语法

**before/after：** 触发器是在增删改之前执行，还是之后执行  
**delete/insert/update：** 触发器由哪些行为触发（增、删、改）  
**on 表名：** 触发器监视哪张表的（增、删、改）操作  
**触发SQL代码块：** 执行触发器包含的SQL语句

**注意：** 触发器也是存储过程程序的一种，而触发器内部的执行SQL语句是可以多行操作的，所以在MySQL的存储过程程序中，要定义结束符。

## 26 MySQL 的基础操作命令: ##

MySQL 是否处于运行状态:Debian 上运行命令 service mysqlstatus，在 RedHat上运行命令 service mysqld status

1.  开启或停止 MySQL 服务 :运行命令 service mysqld start 开启服务；运行命令service mysqld stop 停止服务
2.  Shell 登入 MySQL: 运行命令 mysql -u root -p
3.  列出所有数据库:运行命令 show databases;
4.  切换到某个数据库并在上面工作:运行命令 use database*name; 进入名为database*name 的数据库
5.  列出某个数据库内所有表: show tables;
6.  获取表内所有 Field 对象的名称和类型 :describe table\_name;

至此，前 26 个基础理论篇的 Mysql 告一段

[d9c2dba1ffcba869e07511889e6a0f33.png]: /images/20220828/448f8bbb14074f4d946485169c15dea0.png
[fab295216b00d2daf87163bd6662a30c.png]: https://img-blog.csdnimg.cn/img_convert/fab295216b00d2daf87163bd6662a30c.png
[47252a754e55db5a21b621694388a31e.png]: /images/20220828/75dacc9ec7b341bab59129b6404aad49.png
[cec20493385f08e80184007ceebacf6f.png]: /images/20220828/205b0623b1d84b1f8811c5f7dec081fb.png
[64e0e2d0819288b4ee121e62ecf9f8dd.png]: /images/20220828/0b2773084ab74487a8f639287d265332.png
[b02112220970ccb80ae4e494b2412832.png]: /images/20220828/1a961935405c4165bcb279f5ee9cbf07.png
[bfb6513c783eff4104cfe57eb6127e4a.png]: /images/20220828/9a84a1eae918486390fa3bd6db2d5a76.png