数据库开发面试题库---搬运

最新推荐文章于 2024-05-08 11:22:33 发布

M_3616

最新推荐文章于 2024-05-08 11:22:33 发布

阅读量465

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/M_3616/article/details/104767056

版权

数据库开发面试题库

助理

一. 表四种连接方式简介

内连接 (表A inner join 表B on 条件)
返回表A（左表）和表B（右表）相匹配的数据；
左外连接(表A left join 表B on 条件)
表A(左表)的记录全部表示出来,而表B(右表)只会显示符合条件的记录,表B(右表)记录不足的地方均为null；
右外连接(表A right join 表B on 条件)
和左外连接的结果刚好相反，是以表B（右表）为基础，显示表B（右表）的全部记录，在加上表A（左表）符合条件的记录，表A（左表）记录不足的地方均为null；
全连接（表A full join 表B on 条件）
返回表A（左表）和表B（右表）中的所有行

二、简述oracle中 dml、ddl、dcl的使用

Dml 数据操纵语言，如select、update、delete，insert
Ddl 数据定义语言，如create table 、drop table 等等
Dcl 数据控制语言，如 commit、 rollback、grant、 invoke等

三、说说Oracle中经常使用到的函数

length长度、lower小写、upper大写、to_date转化日期、to_char转化字符、to_number转化数字Ltrim去左边空格、rtrim去右边空格、substr截取字符串、add_month增加或减掉月份

四、带条件的查询语句

Select * from xx where …;

五、主外键

主键是表格里的(一个或多个)字段，只用来定义表格里的行;主键里的值总是唯一的。外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表格(尽管可能是同一个表格)里的一系列相连的字段。那么这些相连的字段就是外键。

六、常用聚合函数

5 个常用函数：

COUNT：计算表中的记录（行）数。
SUM：计算表中数值列的数据合计值。
AVG：计算表中数值列的数据平均值。
MAX：求出表中任意列中数据的最大值。
MIN：求出表中任意列中数据的最小值。

七、分组排序过滤

Select …… from table group by …… order by …… having ……

初级

一、数据库三范式及五约束

三范式
- 第一范式（1NF）：数据表中的每一列（每个字段）必须是不可拆分的最小单元，也就是确保每一列的原子性；
- 第二范式（2NF）：满足1NF后，要求表中的所有列，都必须依赖于主键，而不能有任何一列与主键没有关系，也就是说一个表只描述一件事情；
- 第三范式（3NF）：必须先满足第二范式（2NF），要求：表中的每一列只与主键直接相关而不是间接相关，（表中的每一列只能依赖于主键）；
五约束
- primary KEY:设置主键约束；
- UNIQUE：设置唯一性约束，不能有重复值；
- DEFAULT 默认值约束，height DOUBLE(3,2)DEFAULT 1.2 height不输入是默认为1,2
- NOT NULL：设置非空约束，该字段不能为空；
- FOREIGN key :设置外键约束。

二、事务的特性（ACID）是指什么

原子性（Atomic）：事务中的各项操作，要么全做要么全不做，任何一项操作的失败都会导致整个事务的失败
一致性（Consistent）：事务结束后系统状态是一样的
隔离性（Isolated）：并发执行的事务彼此无法看到对方的中间状态
持久性（Durable）：事务完成后，即使发生灾难性的故障，通过日志和同步备份可以在故障发生后重建数据

三、比较truncate和delete命令

Truncate 和delete都可以将数据实体删掉，truncate 的操作并不记录到 rollback日志，所以操作速度较快，但同时这个数据不能恢复
Delete操作不腾出表空间的空间
Truncate 不能对视图等进行删除
Truncate是数据定义语言（DDL），而delete是数据操纵语言(DML)

四、rowid, rownum的定义

rowid和rownum都是虚列
rowid是物理地址，用于定位oracle中具体数据的物理存储位置
rownum则是sql的输出结果排序，从下面的例子可以看出其中的区别。

五、使用oracle 伪列删除表中重复记录

Delete table t where t.rowid!=(select max(t1.rowid) from table1 t1 where t1.name=t.name)

六、Oracle是怎样分页

Oracle中使用rownum来进行分页, 这个是效率最好的分页方法，hibernate也是使用rownum来进行oralce分页的
select * from
( select rownum r,a from tabName where rownum <= 20 )
where r > 10

七、Oracle中where条件查询和排序的性能比较

Order by使用索引的条件极为严格，只有满足如下情况才可以使用索引，

order by中的列必须包含相同的索引并且索引顺序和排序顺序一致
不能有null值的列
所以排序的性能往往并不高，所以建议尽量避免order by

八、怎样创建一个一个索引,索引使用的原则,有什么优点和缺点

创建标准索引：
CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名;
创建唯一索引:
CREATE unique INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名;
创建组合索引:
CREATE INDEX 索引名 ON 表名 (列名1,列名2) TABLESPACE 表空间名;
创建反向键索引:
CREATE INDEX 索引名 ON 表名 (列名) reverse TABLESPACE 表空间名;

索引使用原则：

索引字段建议建立NOT NULL约束

经常与其他表进行连接的表，在连接字段上应该建立索引；
经常出现在Where子句中的字段且过滤性很强的，特别是大表的字段，应该建立索引；
可选择性高的关键字，应该建立索引；
可选择性低的关键字，但数据的值分布差异很大时，选择性数据比较少时仍然可以利用索引提高效率
复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
正确选择复合索引中的第一个字段，一般是选择性较好的且在where子句中常用的字段上；
复合索引的几个字段经常同时以AND方式出现在Where子句中可以建立复合索引；否则单字段索引；
如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
频繁DML的表，不要建立太多的索引；
不要将那些频繁修改的列作为索引列；

索引的优缺点：

优点：

创建唯一性索引，保证数据库表中每一行数据的唯一性
大大加快数据的检索速度，这也是创建索引的最主要的原因
加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

缺点：

索引创建在表上，不能创建在视图上
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

九、oracle中存储过程，游标和函数的区别

游标类似指针，游标可以执行多个不相关的操作.如果希望当产生了结果集后,对结果集中的数据进行多种不相关的数据操作
函数可以理解函数是存储过程的一种；函数可以没有参数,但是一定需要一个返回值，存储过程可以没有参数,不需要返回值；两者都可以通过out参数返回值, 如果需要返回多个参数则建议使用存储过程；在sql数据操纵语句中只能调用函数而不能调用存储过程

十、EXISTS与IN的执行效率问题

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下，如果另一个表是小表用in 效率高，是大表用exists 效率高。

十一、PL/SQL包含哪些部分

Pl/sql 的块结构包括：定义部分，执行部分，异常处理部分。

中级

一、解释data block , extent 和 segment的区别

data block 数据块，是oracle最小的逻辑单位，通常oracle从磁盘读写的就是块
extent 区，是由若干个相邻的block组成
segment段，是有一组区组成
tablespace表空间，数据库中数据逻辑存储的地方，一个tablespace可以包含多个数据文件

二、oracle的锁又几种,定义分别是什么

行共享锁 (ROW SHARE)
行排他锁(ROW EXCLUSIVE)
共享锁(SHARE)
共享行排他锁(SHARE ROW EXCLUSIVE)
排他锁(EXCLUSIVE)

使用方法：

SELECT * FROM order_master WHERE vencode=“V002”
FOR UPDATE WAIT 5;
LOCK TABLE order_master IN SHARE MODE;
LOCK TABLE itemfile IN EXCLUSIVE MODE NOWAIT;

ORACLE锁具体分为以下几类：

按用户与系统划分，可以分为自动锁与显示锁
自动锁：当进行一项数据库操作时，缺省情况下，系统自动为此数据库操作获得所有有必要的锁。
显示锁：某些情况下，需要用户显示的锁定数据库操作要用到的数据，才能使数据库操作执行得更好，显示锁是用户为数据库对象设定的。
按锁级别划分，可分为共享锁与排它锁
共享锁：共享锁使一个事务对特定数据库资源进行共享访问——另一事务也可对此资源进行访问或获得相同共享锁。共享锁为事务提供高并发性，但如拙劣的事务设计+共享锁容易造成死锁或数据更新丢失。
排它锁：事务设置排它锁后，该事务单独获得此资源，另一事务不能在此事务提交之前获得相同对象的共享锁或排它锁。
按操作划分，可分为DML锁、DDL锁
DML锁又可以分为，行锁、表锁、死锁
行锁：当事务执行数据库插入、更新、删除操作时，该事务自动获得操作表中操作行的排它锁。
表级锁：当事务获得行锁后，此事务也将自动获得该行的表锁(共享锁),以防止其它事务进行DDL语句影响记录行的更新。事务也可以在进行过程中获得共享锁或排它锁，只有当事务显示使用LOCK TABLE语句显示的定义一个排它锁时，事务才会获得表上的排它锁,也可使用LOCK TABLE显示的定义一个表级的共享锁(LOCK TABLE具体用法请参考相关文档)。
死锁：当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就出现死锁。
如事务1在表A行记录#3中有一排它锁，并等待事务2在表A中记录#4中排它锁的释放，而事务2在表A记录行#4中有一排它锁，并等待事务; 1在表A中记录#3中排它锁的释放，事务1与事务2彼此等待，因此就造成了死锁。死锁一般是因拙劣的事务设计而产生。死锁只能使用SQL下:alter system kill session “sid,serial#”；或者使用相关操作系统kill进程的命令，如UNIX下kill -9 sid,或者使用其它工具杀掉死锁进程。
DDL锁又可以分为：排它DDL锁、共享DDL锁、分析锁
排它DDL锁：创建、修改、删除一个数据库对象的DDL语句获得操作对象的排它锁。如使用alter table语句时，为了维护数据的完成性、一致性、合法性，该事务获得一排它DDL锁。
共享DDL锁：需在数据库对象之间建立相互依赖关系的DDL语句通常需共享获得DDL锁。
如创建一个包，该包中的过程与函数引用了不同的数据库表，当编译此包时，该事务就获得了引用表的共享DDL锁。
分析锁：ORACLE使用共享池存储分析与优化过的SQL语句及PL/SQL程序，使运行相同语句的应用速度更快。一个在共享池中缓存的对象获得它所引用数据库对象的分析锁。分析锁是一种独特的DDL锁类型，ORACLE使用它追踪共享池对象及它所引用数据库对象之间的依赖关系。当一个事务修改或删除了共享池持有分析锁的数据库对象时，ORACLE使共享池中的对象作废，下次在引用这条SQL/PLSQL语句时，ORACLE重新分析编译此语句。
内部闩锁
内部闩锁：这是ORACLE中的一种特殊锁，用于顺序访问内部系统结构。当事务需向缓冲区写入信息时，为了使用此块内存区域，ORACLE首先必须取得这块内存区域的闩锁，才能向此块内存写入信息。

三、解释什么是死锁，如何解决Oracle中的死锁

简言之就是存在加了锁而没有解锁，可能是使用锁没有提交或者回滚事务，如果是表级锁则不能操作表，客户端处于等在状态，如果是行级锁则不能操作锁定行

四、怎样优化Oracle数据库，有几种方式

个人理解，数据库性能最关键的因素在于IO，因为操作内存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化

物理优化的一些原则：

Oracle的运行环境（网络，硬件等）
使用合适的优化器
合理配置oracle实例参数
建立合适的索引（减少IO）
将索引数据和表数据分开在不同的表空间上（降低IO冲突）
建立表分区，将数据分别存储在不同的分区上（以空间换取时间，减少IO）
逻辑优化
- 可以对表进行逻辑分割，如中国移动用户表，可以根据手机尾数分成10个表，这样对性能会有一定的作用
- Sql语句使用占位符语句，并且开发时候必须按照规定编写sql语句（如全部大写，全部小写等）oracle解析语句后会放置到共享池中
  如： select * from Emp where name=? 这个语句只会在共享池中有一条，而如果是字符串的话，那就根据不同名字存在不同的语句，所以占位符效率较好
数据库不仅仅是一个存储数据的地方，同样是一个编程的地方，一些耗时的操作，可以通过存储过程等在用户较少的情况下执行，从而错开系统使用的高峰时间，提高数据库性能
尽量不使用*号，如select * from Emp，因为要转化为具体的列名是要查数据字典，比较耗时
选择有效的表名
对于多表连接查询，可能oracle的优化器并不会优化到这个程度， oracle 中多表查询是根据FROM字句从右到左的数据进行的，那么最好右边的表（也就是基础表）选择数据较少的表，这样排序更快速，如果有link表（多对多中间表），那么将link表放最右边作为基础表，在默认情况下oracle会自动优化，但是如果配置了优化器的情况下，可能不会自动优化，所以平时最好能按照这个方式编写sql
Where字句规则
Oracle 中Where字句时从右往左处理的，表之间的连接写在其他条件之前，能过滤掉非常多的数据的条件，放在where的末尾，另外!=符号比较的列将不使用索引，列经过了计算（如变大写等）不会使用索引（需要建立起函数）， is null、is not null等优化器不会使用索引
使用Exits Not Exits 替代 In Not in
合理使用事务，合理设置事务隔离性
数据库的数据操作比较消耗数据库资源的，尽量使用批量处理，以降低事务操作次数

五、Fact table上需要建立何种索引

位图索引（bitmap index）

六、如何在不影响子表的前提下，重建一个母表

子表的外键强制失效，重建母表，激活外键

七、解释FUNCTION,PROCEDURE和PACKAGE区别

function 和procedure是PL/SQL代码的集合，通常为了完成一个任务。procedure 不需要返回任何值而function将返回一个值
在另一方面，Package是为了完成一个商业功能的一组function和proceudre的集合

八、解释materialized views的作用

Materialized views 用于减少那些汇总，集合和分组的信息的集合数量。它们通常适合于数据仓库和DSS系统。

九、描述什么是 redo logs

解答：Redo Logs 是用于存放数据库数据改动状况的物理和逻辑结构。可以用来修复数据库

十、创建用户时，需要赋予新用户什么权限才能使它联上数据库

解答：CONNECT

十一、解释什么是Partitioning（分区）以及它的优点

Partition将大表和索引分割成更小，易于管理的分区

十二、你刚刚编译了一个PL/SQL Package但是有错误报道，如何显示出错信息

SHOW ERRORS

十三、PL/SQL包含哪些部分

Pl/sql 的块结构包括：定义部分，执行部分，异常处理部分。

十四、如何使用CBO,CBO与RULE的区别

2种方式，系统参数设置，在SQL中单独加提示的方式设置。区别在于RULE是依据sql编写的规则来确定执行的路径；COST是参考访问成本来产生最佳路径。RULE的结果取决于开发者的编写能力，COST取决于系统的智能化程度，数据库的管理优化度。需要定期进行统计数据的整理，分析，取得最新的数据

十五、绑定变量是什么，绑定变量有什么优缺点

绑定变量是指在SQL语句中使用变量，改变变量的值来改变SQL语句的执行结果。

优点：

使用绑定变量，可以减少SQL语句的解析，能减少数据库引擎消耗在SQL语句解析上的资源。提高了编程效率和可靠性。减少访问数据库的次数, 就能实际上减少ORACLE的工作量。

缺点：

经常需要使用动态SQL的写法，由于参数的不同，可能SQL的执行效率不同；

绑定变量是相对文本变量来讲的,所谓文本变量是指在SQL直接书写查询条件，

这样的SQL在不同条件下需要反复解析,绑定变量是指使用变量来代替直接书写条件，查询bind value在运行时传递，然后绑定执行。

优点是减少硬解析,降低CPU的争用,节省shared_pool

缺点是不能使用histogram,sql优化比较困难

高级

一、如何增加buffer cache的命中率

在数据库较繁忙时，适用buffer cache advisory 工具，查询v$db_cache_advice . 如果有必要更改，可以使用 alter system set db_cache_size 命令

二、pctused and pctfree 表示什么含义有什么作用

PCTUSED使用比例，PCTFREE空闲比例。数据块中使用空间的比例高于PCTUSED时候被标记为满；空闲比例大于PCTFREE的时候被标记为闲

三、表关联方式及使用场景

HASH JOIN , NESTED LOOP , SORT MERGE JOIN , 笛卡尔积

四、回滚段的作用是什么

回滚段用于保存数据修改前的映象，这些信息用于生成读一致性数据库信息、在数据库恢复和Rollback时使用。一个事务只能使用一个回滚段。

事务回滚：当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。
事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在undo表空间中，ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。
读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改（语句级读一致性）

当ORACLE执行SELECT语句时，ORACLE依照当前的系统改变号（SYSTEM CHANGE NUMBER-SCN）来保证任何前于当前SCN的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时，若其他会话改变了该查询要查询的某个数据块，ORACLE将利用回滚段的数据前影像来构造一个读一致性视图

五、简要介绍oracle的体系结构

Oracle Server = 实例（instance）+数据库(database)。

实例是由一组内存结构（SGA system global area ）+后台进程+PGA

数据库是由一组OS文件组成，它由数据文件、参数文件、控制文件、重做日志文件组成。

SGA是所有服务器进程和后台进程共享的内存区域，当instance启动时创建该区域。它在内在区域中有且只有一个。它由数据高速缓存（databasebuffer cache）、重做日志缓存区（redo log buffer）、共享池（shared pool）、大池（large pool）、java pool等组成。

六、Oracle 备份方式有那几种？

Oracle数据库有三种标准的备份方法，它们分别是冷备份、热备份和导出/导入（EXP/IMP）。冷备份和热备份是物理备份，导出备份是一种逻辑备份，。

冷备份
冷备份是Oracle最简单的一种备份；执行冷备份前必须关闭数据库；然后使用操作系统实用工具或者第三方工具备份所有相关的数据库文件。

优点:
能简单快速地备份。能简单快速地恢复。执行简单。

缺点:
必须关闭数据库，不能进行点恢复。
热备份
热备份是当数据库正在运行时进行数据备份的过程。执行热备份的前提是：数据库运行在可归档日志模式。适用于24 x 7不间断运行的关键应用系统。

优点:
备份时数据库可以是打开的。热备份可以用来进行点恢复。初始化参数文件、归档日志在数据库正常运行时是关闭的，可用操作系统命令拷贝。

缺点:
执行过程复杂。由于数据库不间断运行，测试比较困难。不能用操作系统实用工具拷贝打开的文件。必须使用Oracle提供的copy工具来拷贝打开的文件。热备份可能造成CPU、I/O过载，应在数据库不太忙时进行。
Export导出数据库对象
冷备份和热备份都备份物理数据库文件，因而被称为物理备份。而export备份的是数据库对象，因此被称为逻辑备份。
优点：
能执行对象或者行恢复。备份和恢复速度更快。能够跨操作系统平台迁移数据库。数据库可一直运行。

缺点:
export并不是冷备份和热备份的替代工具。冷、热备份可保护介质失效。export备份可保护用户或应用错误。

M_3616

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
数据库开发面试题库---搬运

数据库开发面试题库助理一、表四种连接方式简介内连接(表A inner join 表B on 条件)返回表A（左表）和表B（右表）相匹配的数据；左外连接(表A left join 表B on 条件)表A(左表)的记录全部表示出来,而表B(右表)只会显示符合条件的记录,表B(右表)记录不足的地方均为null；右外连接(表A right join 表B on 条件)和左外连接的结果刚...
复制链接

扫一扫