2024年数据库面试题(开发者必看)_程序员账户交易面试题，真是经典中的经典

什么是事务？一个Session中所进行所有的操作，要么同时成功，要么同时失败ACID — 数据库事务正确执行的四个基本要素包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法

2401_84563354

718人浏览 · 2024-05-06 14:26:26

2401_84563354 · 2024-05-06 14:26:26 发布

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

索引表把数据变成是有序的….
这里写图片描述

快速定位到硬盘中的数据文件…

这里写图片描述

rowid特点

rowid的特点

（1）位于每个表中，但表面上看不见，例如：desc emp是看不见的
（2）只有在select中，显示写出rowid，方可看见
（3）它与每个表绑定在一起，表亡，该表的rowid亡，二张表rownum可以相同，但rowid必须是唯一的
（4）rowid是18位大小写加数字混杂体，唯一表代该条记录在DBF文件中的位置
（5）rowid可以参与=/like比较时，用”单引号将rowid的值包起来，且区分大小写
（6）rowid是联系表与DBF文件的桥梁

索引特点

索引的特点

（1）索引一旦建立,* Oracle管理系统会对其进行自动维护*, 而且由Oracle管理系统决定何时使用索引
（2）用户不用在查询语句中指定使用哪个索引
（3）在定义primary key或unique约束后系统自动在相应的列上创建索引
（4）用户也能按自己的需求，对指定单个字段或多个字段，添加索引

需要注意的是：Oracle是自动帮我们管理索引的，并且如果我们指定了primary key或者unique约束，系统会自动在对应的列上创建索引…

什么时候【要】创建索引

（1）表经常进行 SELECT 操作
（2）表很大(记录超多)，记录内容分布范围很广
（3）列名经常在 WHERE 子句或连接条件中出现

什么时候【不要】创建索引

（1）表经常进行 INSERT/UPDATE/DELETE 操作
（2）表很小(记录超少)
（3）列名不经常作为连接条件或出现在 WHERE 子句中

索引优缺点：

索引加快数据库的检索速度
索引降低了插入、删除、修改等维护任务的速度(虽然索引可以提高查询速度，但是它们也会导致数据库系统更新数据的性能下降，因为大部分数据更新需要同时更新索引)
唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
索引需要占物理和数据空间

索引分类：

唯一索引：唯一索引不允许两行具有相同的索引值
主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

深入理解索引可参考：

什么是事务？

什么是事务？

事务简单来说：一个Session中所进行所有的操作，要么同时成功，要么同时失败

ACID — 数据库事务正确执行的四个基本要素

包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易。

举个例子:A向B转账，转账这个流程中如果出现问题，事务可以让数据恢复成原来一样【A账户的钱没变，B账户的钱也没变】。

事例说明：



        /\*
 \* 我们来模拟A向B账号转账的场景
 \* A和B账户都有1000块，现在我让A账户向B账号转500块钱
 \*
 \* \*/
            //JDBC默认的情况下是关闭事务的，下面我们看看关闭事务去操作转账操作有什么问题

            //A账户减去500块
            String sql = "UPDATE a SET money=money-500 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            //B账户多了500块
            String sql2 = "UPDATE b SET money=money+500";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

从上面看，我们的确可以发现A向B转账，成功了。可是**如果A向B转账的过程中出现了问题呢？**下面模拟一下


            //A账户减去500块
            String sql = "UPDATE a SET money=money-500 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            //这里模拟出现问题
            int a = 3 / 0;


            String sql2 = "UPDATE b SET money=money+500";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

显然，上面代码是会抛出异常的，我们再来查询一下数据。A账户少了500块钱，B账户的钱没有增加。这明显是不合理的。

我们可以通过事务来解决上面出现的问题


            //开启事务,对数据的操作就不会立即生效。
            connection.setAutoCommit(false);

            //A账户减去500块
            String sql = "UPDATE a SET money=money-500 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            //在转账过程中出现问题
            int a = 3 / 0;

            //B账户多500块
            String sql2 = "UPDATE b SET money=money+500";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

            //如果程序能执行到这里，没有抛出异常，我们就提交数据
            connection.commit();

            //关闭事务【自动提交】
            connection.setAutoCommit(true);


        } catch (SQLException e) {
            try {
                //如果出现了异常，就会进到这里来，我们就把事务回滚【将数据变成原来那样】
                connection.rollback();

                //关闭事务【自动提交】
                connection.setAutoCommit(true);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

上面的程序也一样抛出了异常，A账户钱没有减少，B账户的钱也没有增加。

注意：当Connection遇到一个未处理的SQLException时，系统会非正常退出，事务也会自动回滚，但如果程序捕获到了异常，是需要在catch中显式回滚事务的。

事务隔离级别

数据库定义了4个隔离级别：

Serializable【可避免脏读，不可重复读，虚读】
Repeatable read【可避免脏读，不可重复读】
Read committed【可避免脏读】
Read uncommitted【级别最低，什么都避免不了】

分别对应Connection类中的4个常量

TRANSACTION_READ_UNCOMMITTED
TRANSACTION_READ_COMMITTED
TRANSACTION_REPEATABLE_READ
TRANSACTION_SERIALIZABLE

脏读：一个事务读取到另外一个事务未提交的数据

例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多。

不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改

注：A查询数据库得到数据，B去修改数据库的数据，导致A多次查询数据库的结果都不一样【危害：A每次查询的结果都是受B的影响的，那么A查询出来的信息就没有意思了】

虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

注：和不可重复读类似，但虚读(幻读)会读到其他事务的插入的数据，导致前后读取不一致

简单总结：脏读是不可容忍的，不可重复读和虚读在一定的情况下是可以的【做统计的肯定就不行】。

数据库的乐观锁和悲观锁是什么？

数据库的乐观锁和悲观锁是什么？

确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
- 在查询完数据的时候就把事务锁起来，直到提交事务
- 实现方式：使用数据库中的锁机制
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
- 在修改数据的时候把事务锁起来，通过version的方式来进行锁定
- 实现方式：使用version版本或者时间戳

悲观锁：

乐观锁：

参考资料：

http://www.open-open.com/lib/view/open1452046967245.html

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

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

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

候选码和主码：

例子：邮寄地址（城市名，街道名，邮政编码，单位名，收件人）

它有两个候选键:{城市名，街道名} 和 {街道名，邮政编码}
如果我选取{城市名，街道名}作为唯一标识实体的属性，那么{城市名，街道名} 就是主码(主键)

SQL 约束有哪几种？

SQL 约束有哪几种？

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

数据库运行于哪种状态下可以防止数据的丢失？

数据库运行于哪种状态下可以防止数据的丢失？

在archivelog mode(归档模式)只要其归档日志文件不丢失，就可以有效地防止数据丢失。

Mysql存储引擎

Mysql的存储引擎有以下几种：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

我的是5.7.15版本，默认使用的是Innodb版本！

常用的存储引擎有以下：

Innodb引擎，Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎),不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

同一个数据库也可以使用多种存储引擎的表。如果一个表修改要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

参考资料：

MyIASM和Innodb两种引擎所使用的索引的数据结构是什么？

MyIASM和Innodb两种引擎所使用的索引的数据结构是什么？

答案:都是B+树!

MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

varchar和char的区别

varchar和char的区别

Char是一种固定长度的类型，varchar是一种可变长度的类型

mysql有关权限的表都有哪几个

mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

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

数据表损坏的修复方式有哪些？

数据表损坏的修复方式有哪些？

使用 myisamchk 来修复，具体步骤：

1）修复前将mysql服务停止。
2）打开命令行方式，然后进入到mysql的/bin目录。
3）执行myisamchk –recover 数据库所在路径/*.MYI

使用repair table 或者 OPTIMIZE table命令来修复，REPAIR TABLE table_name 修复表 OPTIMIZE TABLE table_name 优化表 REPAIR TABLE 用于修复被破坏的表。
OPTIMIZE TABLE 用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）

MySQL中InnoDB引擎的行锁是通过加在什么上完成

MySQL中InnoDB引擎的行锁是通过加在什么上完成

InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,

如果 id 不是索引键那么InnoDB将完成表锁,并发将无从谈起

数据库优化的思路

SQL优化

在我们书写SQL语句的时候，其实书写的顺序、策略会影响到SQL的性能，虽然实现的功能是一样的，但是它们的性能会有些许差别。

因此，下面就讲解在书写SQL的时候，怎么写比较好。

①选择最有效率的表名顺序

数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处理

在FROM子句中包含多个表的情况下：

如果三个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推
也就是说：选择记录条数最少的表放在最后

如果有3个以上的表连接查询：

如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推。
也就是说：被其他表所引用的表放在最后

例如：查询员工的编号，姓名，工资，工资等级，部门名

emp表被引用得最多，记录数也是最多，因此放在form字句的最后面


select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
from salgrade,dept,emp
where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)

②WHERE子句中的连接顺序

数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。

emp.sal可以过滤多条记录，写在WHERE字句的最右边


      select emp.empno,emp.ename,emp.sal,dept.dname
 from dept,emp
 where (emp.deptno = dept.deptno) and (emp.sal > 1500)

③SELECT子句中避免使用*号

我们当时学习的时候，“*”号是可以获取表中全部的字段数据的。

但是它要通过查询数据字典完成的，这意味着将耗费更多的时间
使用*号写出来的SQL语句也不够直观。

④用TRUNCATE替代DELETE

这里仅仅是：删除表的全部记录，除了表结构才这样做。

DELETE是一条一条记录的删除，而Truncate是将整个表删除，保留表结构，这样比DELETE快

⑤多使用内部函数提高SQL效率

例如使用mysql的concat()函数会比使用||来进行拼接快，因为concat()函数已经被mysql优化过了。

⑥使用表或列的别名

如果表或列的名称太长了，使用一些简短的别名也能稍微提高一些SQL的性能。毕竟要扫描的字符长度就变少了。。。

⑦多使用commit

comiit会释放回滚点…

⑧善用索引

索引就是为了提高我们的查询数据的，当表的记录量非常大的时候，我们就可以使用索引了。

⑨SQL写大写

我们在编写SQL 的时候，官方推荐的是使用大写来写关键字，因为Oracle服务器总是先将小写字母转成大写后，才执行

⑩避免在索引列上使用NOT

因为Oracle服务器遇到NOT后，他就会停止目前的工作，转而执行全表扫描

①①避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，这样会变得变慢

①②用 `>=` 替代 `>`

      低效：
      SELECT \* FROM EMP WHERE DEPTNO > 3 
 首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录
 高效：
 SELECT \* FROM EMP WHERE DEPTNO >= 4 
 直接跳到第一个DEPT等于4的记录

①③用IN替代OR


      select \* from emp where sal = 1500 or sal = 3000 or sal = 800;
      select \* from emp where sal in (1500,3000,800);

①④总是使用索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列被WHERE子句引用时，优化器才会选择使用该索引。当只引用索引的第二个列时，不引用索引的第一个列时，优化器使用了全表扫描而忽略了索引


      create index emp\_sal\_job\_idex
 on emp(sal,job);
      ----------------------------------
      select \*
 from emp 
 where job != 'SALES'; 


      上边就不使用索引了。

数据库结构优化

1）范式优化：比如消除冗余（节省空间。。）
2）反范式优化：比如适当加冗余等（减少join）
3）拆分表：垂直拆分和水平拆分

服务器硬件优化

这个么多花钱咯！

SQL练习题

下列练习题参考自公众号Java知音：

基本表结构：

student(sno,sname,sage,ssex)学生表
course(cno,cname,tno) 课程表
sc(sno,cno,score) 成绩表

teacher(tno,tname) 教师表


![img](https://img-blog.csdnimg.cn/img_convert/db5f3be43a78bd720596d267d10c1afb.png)
![img](https://img-blog.csdnimg.cn/img_convert/ac2835d2b16cdd021e8dc51de3a80ba9.png)

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化的资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618608311)**

**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**

93185db690b4fdfba22bb10ca87ea2cd6fa774&scene=21#wechat_redirect>
* <https://mp.weixin.qq.com/s?__biz=MzI4Njc5NjM1NQ==&mid=2247483696&idx=1&sn=5f472ce7720aede89e2e15ea64bed1bc&chksm=ebd63e1cdca1b70ad18dec268c9903b2cbe11f9ce7b0633980c78a28bd5b1b57c4efbe7a3411&scene=21#wechat_redirect>


基本表结构：

student(sno,sname,sage,ssex)学生表
course(cno,cname,tno) 课程表
sc(sno,cno,score) 成绩表

teacher(tno,tname) 教师表

[外链图片转存中…(img-hCODJgpY-1714976776026)]
[外链图片转存中…(img-44LSqGgp-1714976776027)]

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以戳这里获取