oracle sql面试题及答案

oraclesql面试题及答案OracleSQL面试题及答案一、选择题（共30分，每题2分）1.下列哪个关键字用于在Oracle中创建表？A.CREATETABLEB.MAKETABLEC.NEWTABLED.ADDTABLE2.在Oracle中，以下哪个聚合函数可以返回指定列中的最大值？A.MIN()B.MAX()C.SUM()D.AVG()3.以下哪个子句用于对结果集进行排序？A.WHEREB.GROUPBYC.HAVINGD.ORDERBY4.在Oracle中，以下哪个操作符用于进行模糊查询？A.=B.<>C.LIKED.IN5.以下哪个约束用于确保列中的值唯一？A.PRIMARYKEYB.FOREIGNKEYC.UNIQUED.NOTNULL6.在Oracle中，以下哪个函数用于连接两个字符串？A.CONCAT()B.JOIN()C.LINK()D.MERGE()7.以下哪个子查询被称为相关子查询？A.不依赖于外部查询的子查询B.依赖于外部查询的子查询C.只包含在WHERE子句中的子查询D.只包含在SELECT子句中的子查询8.在Oracle中，以下哪个命令用于删除表中的所有数据但保留表结构？A.DROPTABLEB.DELETETABLEC.TRUNCATETABLED.REMOVETABLE9.以下哪种连接类型会返回两个表中所有匹配的行，以及左表中没有匹配的右表行？A.INNERJOINB.LEFTJOINC.RIGHTJOIND.FULLJOIN10.在Oracle中，以下哪个伪列用于生成唯一的序列号？A.ROWNUMB.ROWIDC.SEQD.ID11.以下哪个函数用于截断数字的小数部分？A.ROUND()B.TRUNC()C.FLOOR()D.CEIL()12.在Oracle中，以下哪个命令用于提交当前事务？A.COMMITB.ROLLBACKC.SAVEPOINTD.TRANSACTION13.以下哪个子句用于对分组后的结果进行过滤？A.WHEREB.GROUPBYC.HAVINGD.ORDERBY14.在Oracle中，以下哪个数据类型用于存储可变长度的字符串？A.CHARB.VARCHARC.VARCHAR2D.NCHAR15.以下哪个操作符用于检查一个值是否在指定的列表中？A.BETWEENB.LIKEC.IND.EXISTS二、填空题（共20分，每题2分）1.在Oracle中，用于创建索引的关键字是________。2.SQL语言中，用于从表中检索数据的命令是________。3.在Oracle中，用于计算平均值聚合函数是________。4.在Oracle中，用于修改表中数据的命令是________。5.在Oracle中，用于删除表中数据的命令是________。6.在Oracle中，用于创建视图的关键字是________。7.在Oracle中，用于对结果进行分组的子句是________。8.在Oracle中，用于连接两个表并返回两个表中匹配行的连接类型是________。9.在Oracle中，用于返回当前日期的函数是________。10.在Oracle中，用于创建序列的关键字是________。三、判断题（共10分，每题1分）1.在Oracle中，DELETE语句可以删除表结构及其所有数据。（）2.在Oracle中，COUNT()函数会计算NULL值。（）3.在Oracle中，外键约束可以确保引用的值在父表中存在。（）4.在Oracle中，ROLLBACK命令用于撤销尚未提交的事务。（）5.在Oracle中，INNERJOIN只返回两个表中匹配的行。（）6.在Oracle中，TRUNCATETABLE比DELETE更快，因为它不记录日志。（）7.在Oracle中，视图是物理存储数据的表。（）8.在Oracle中，HAVING子句可以在GROUPBY子句之前使用。（）9.在Oracle中，UNION操作符会自动去除结果中的重复行。（）10.在Oracle中，SYSDATE函数返回当前服务器的日期和时间。（）四、简答题（共30分，每题6分）1.简述Oracle中主键约束和唯一约束的区别。2.解释Oracle中内连接（INNERJOIN）和外连接（OUTERJOIN）的区别，并举例说明。3.简述Oracle中事务的ACID特性。4.解释Oracle中ROWNUM和ROWID的区别。5.简述Oracle中索引的类型及其适用场景。五、论述题/编程题（共10分）1.编写一个OracleSQL查询，从包含员工信息的表中检索出每个部门的员工数量，并按员工数量降序排列。表结构如下：-employees表：employee_id（员工ID），employee_name（员工姓名），department_id（部门ID），salary（薪资）-departments表：department_id（部门ID），department_name（部门名称）2.解释OracleSQL性能优化的一些关键策略，包括索引使用、查询重写、表连接方式等方面。答案：一、选择题1.答案：A解释：在Oracle中，创建表使用CREATETABLE语句。MAKETABLE、NEWTABLE和ADDTABLE都不是有效的OracleSQL关键字。2.答案：B解释：MAX()函数用于返回指定列中的最大值。MIN()返回最小值，SUM()返回总和，AVG()返回平均值。3.答案：D解释：ORDERBY子句用于对结果集进行排序。WHERE用于过滤行，GROUPBY用于分组，HAVING用于过滤分组。4.答案：C解释：LIKE操作符用于进行模糊查询，通常与通配符（%和_）一起使用。=用于精确匹配，<>用于不等于匹配，IN用于检查值是否在列表中。5.答案：C解释：UNIQUE约束用于确保列中的值唯一。PRIMARYKEY约束也确保值唯一，但它还隐含NOTNULL约束。FOREIGNKEY用于引用完整性，NOTNULL确保列不能有NULL值。6.答案：A解释：CONCAT()函数用于连接两个字符串。JOIN()不是Oracle函数，LINK()和MERGE()也不是用于字符串连接的函数。7.答案：B解释：相关子查询是依赖于外部查询的子查询，通常在外部查询的每一行执行时都会执行一次。不依赖于外部查询的子查询称为非相关子查询。8.答案：C解释：TRUNCATETABLE命令用于删除表中的所有数据但保留表结构。DROPTABLE会删除表结构及其所有数据，DELETETABLE不是有效命令，REMOVETABLE也不是有效命令。9.答案：B解释：LEFTJOIN（左连接）会返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配，则结果中右表的列将为NULL。INNERJOIN只返回两个表中匹配的行，RIGHTJOIN返回右表中的所有行，FULLJOIN返回两个表中的所有行。10.答案：A解释：ROWNUM是Oracle中的伪列，用于为结果集中的行生成唯一的序列号。ROWID是行的物理地址，不是用于生成序列号的。11.答案：B解释：TRUNC()函数用于截断数字的小数部分。ROUND()用于四舍五入，FLOOR()向下取整，CEIL()向上取整。12.答案：A解释：COMMIT命令用于提交当前事务，使其更改永久化。ROLLBACK用于撤销事务，SAVEPOINT用于设置事务中的保存点，TRANSACTION不是有效的Oracle命令。13.答案：C解释：HAVING子句用于对分组后的结果进行过滤。WHERE用于过滤行，GROUPBY用于分组，ORDERBY用于排序。14.答案：C解释：VARCHAR2是Oracle中用于存储可变长度字符串的数据类型。CHAR用于固定长度字符串，VARCHAR不是标准Oracle数据类型，NCHAR用于固定长度国家字符集数据。15.答案：C解释：IN操作符用于检查一个值是否在指定的列表中。BETWEEN用于检查值是否在范围内，LIKE用于模糊匹配，EXISTS用于检查子查询是否返回任何行。二、填空题1.答案：CREATEINDEX解释：在Oracle中，使用CREATEINDEX语句创建索引以提高查询性能。2.答案：SELECT解释：SELECT语句用于从表中检索数据，是SQL中最常用的命令之一。3.答案：AVG()解释：AVG()函数用于计算指定列的平均值，是Oracle中的聚合函数之一。4.答案：UPDATE解释：UPDATE语句用于修改表中的现有数据，可以更新一个或多个列的值。5.答案：DELETE解释：DELETE语句用于从表中删除一行或多行数据。6.答案：CREATEVIEW解释：CREATEVIEW语句用于创建视图，视图是虚拟表，基于SQL查询的结果集。7.答案：GROUPBY解释：GROUPBY子句用于将结果集按一个或多个列进行分组，通常与聚合函数一起使用。8.答案：INNERJOIN解释：INNERJOIN是用于连接两个表并返回两个表中匹配行的连接类型。9.答案：SYSDATE解释：SYSDATE是Oracle函数，返回当前服务器的日期和时间。10.答案：CREATESEQUENCE解释：CREATESEQUENCE语句用于创建序列，序列是用于生成唯一数字值的对象。三、判断题1.答案：×解释：DELETE语句只能删除表中的数据，不能删除表结构。要删除表结构及其所有数据，应使用DROPTABLE语句。2.答案：×解释：COUNT()函数计算结果集中的行数，不包括NULL值。COUNT(column)会计算非NULL值的数量。3.答案：√解释：外键约束确保引用的值在父表中存在，维护引用完整性。4.答案：√解释：ROLLBACK命令用于撤销尚未提交的事务，将数据库恢复到事务开始前的状态。5.答案：√解释：INNERJOIN只返回两个表中匹配的行，不包含不匹配的行。6.答案：√解释：TRUNCATETABLE比DELETE更快，因为它不记录每行删除的日志，而是直接释放整个数据段的存储空间。7.答案：×解释：视图是虚拟表，不物理存储数据，而是基于底层表或视图的查询结果。8.答案：×解释：HAVING子句必须在GROUPBY子句之后使用，用于过滤分组后的结果。9.答案：×解释：UNION操作符不会自动去除结果中的重复行，UNIONALL才会保留所有行（包括重复行）。要去除重复行，应使用UNION。10.答案：√解释：SYSDATE函数返回当前服务器的日期和时间，不包括时区信息。四、简答题1.答案：主键约束和唯一约束的主要区别如下：-唯一性：主键约束确保列中的值唯一且不能为NULL，而唯一约束只确保值唯一，但允许NULL值（尽管Oracle中每个唯一约束只允许一个NULL值）。-数量：一个表只能有一个主键约束，但可以有多个唯一约束。-索引：主键自动创建唯一索引，唯一约束也自动创建唯一索引。-外键引用：只有主键可以被外键引用，唯一约束不能被外键引用。-应用场景：主键通常用于标识表中的唯一实体，如ID字段；唯一约束用于确保业务规则中的唯一性，如邮箱地址或用户名。2.答案：内连接（INNERJOIN）和外连接（OUTERJOIN）的主要区别在于它们处理不匹配行的方式：-内连接（INNERJOIN）：只返回两个表中匹配的行。如果某行在一个表中没有匹配的行，则该行不会出现在结果中。例如：查询员工及其部门信息，只有那些有对应部门的员工才会被返回。示例：```sqlSELECTe.employee_name,d.department_nameFROMemployeeseINNERJOINdepartmentsdONe.department_id=d.department_id;```-外连接（OUTERJOIN）：返回两个表中至少有一个表匹配的行，并根据外连接的类型，可能包含不匹配的行。-左外连接（LEFTJOIN/LEFTOUTERJOIN）：返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配，则结果中右表的列将为NULL。例如：查询所有员工及其部门信息，包括那些没有分配部门的员工。示例：```sqlSELECTe.employee_name,d.department_nameFROMemployeeseLEFTJOINdepartmentsdONe.department_id=d.department_id;```-右外连接（RIGHTJOIN/RIGHTOUTERJOIN）：返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配，则结果中左表的列将为NULL。例如：查询所有部门及其员工信息，包括那些没有员工的部门。示例：```sqlSELECTe.employee_name,d.department_nameFROMemployeeseRIGHTJOINdepartmentsdONe.department_id=d.department_id;```-全外连接（FULLJOIN/FULLOUTERJOIN）：返回两个表中的所有行。如果某行在另一个表中没有匹配，则结果中对应表的列将为NULL。例如：查询所有员工和所有部门的信息，包括那些没有分配部门的员工和没有员工的部门。示例：```sqlSELECTe.employee_name,d.department_nameFROMemployeeseFULLJOINdepartmentsdONe.department_id=d.department_id;```3.答案：事务的ACID特性是关系数据库管理系统的四个基本特性，确保事务的可靠性和一致性：-原子性（Atomicity）：事务是一个不可分割的工作单元，事务中的所有操作要么全部成功，要么全部失败回滚。如果事务中的任何操作失败，整个事务将回滚到事务开始前的状态。-一致性（Consistency）：事务必须使数据库从一个一致的状态转变到另一个一致的状态。事务执行前后，数据库的完整性约束没有被破坏。-隔离性（Isolation）：并发执行的事务是相互隔离的，一个事务的执行不应影响其他事务的执行。隔离性通过锁机制和多版本并发控制（MVCC）等技术实现。-持久性（Durability）：一旦事务提交，它对数据库的更改就是永久性的，即使系统发生故障，这些更改也不会丢失。持久性通常通过日志和恢复机制实现。在Oracle中，可以使用COMMIT提交事务，使用ROLLBACK回滚事务，使用SAVEPOINT设置保存点。4.答案：ROWNUM和ROWID是Oracle中的两个重要伪列，但它们有本质的区别：-ROWNUM：-是一个逻辑行号，表示查询结果集中行的位置。-在查询结果生成后分配，从1开始递增。-用于限制返回的行数，如"SELECTFROMtableWHEREROWNUM<=10"。-在排序前分配，因此"ORDERBYROWNUM"没有意义。-是会话特定的，不存储在数据库中。-ROWID：-是一个物理地址，唯一标识表中的一行。-表示数据在数据文件中的物理位置，包括数据文件号、块号和行号。-用于快速访问特定行，是最快的访问方式之一。-在表的生命周期内保持不变，除非行被移动或表被重建。-可以用于唯一标识表中的每一行，即使主键被修改。使用示例：```sql--使用ROWNUM限制结果SELECTFROMemployeesWHEREROWNUM<=5;--使用ROWID快速访问特定行SELECTFROMemployeesWHEREROWID='AAABd+AAEAAAAGvAAA';```5.答案：Oracle中索引的类型及其适用场景如下：-B-树索引（B-TreeIndex）：-最常用的索引类型，默认创建的索引类型。-结构类似于平衡树，适合范围查询、精确匹配和排序操作。-适用于高基数（唯一值多）的列。-示例：创建在主键或唯一约束上的索引。-位图索引（BitmapIndex）：-使用位图表示键值的存在情况，适合低基数（重复值多）的列。-在数据仓库和决策支持系统中特别有用。-不适合高并发DML操作，因为会导致索引锁定。-示例：创建在性别、状态等低基数列上的索引。-反向键索引（ReverseKeyIndex）：-将索引键值的字节顺序反转，避免热块问题。-适用于序列生成的ID列，避免索引集中在同一块上。-示例：创建在自增ID列上的索引。-基于函数的索引（Function-BasedIndex）：-基于列上的函数或表达式创建索引。-适用于经常在查询中对列使用函数的情况。-示例：创建在UPPER(last_name)列上的索引。-全局索引（GlobalIndex）：-索引范围跨越分区表的所有分区。-适合频繁范围查询的分区表。-示例：在按范围分区的表上创建全局索引。-本地索引（LocalIndex）：-索引与分区表一一对应，每个分区有自己的索引分区。-适合分区维护操作，如添加、删除或合并分区。-示例：在按列表分区的表上创建本地索引。-索引组织表（Index-OrganizedTable,IOT）：-表数据本身存储在B-树索引结构中。-适合基于主键频繁访问的表。-示例：创建一个经常通过主键访问的小型配置表。五、论述题/编程题1.答案：要编写一个OracleSQL查询，从包含员工信息的表中检索出每个部门的员工数量，并按员工数量降序排列，可以使用以下查询：```sqlSELECTd.department_id,d.department_name,COUNT(e.employee_id)ASemployee_countFROMdepartmentsdLEFTJOINemployeeseONd.department_id=e.department_idGROUPBYd.department_id,d.department_nameORDERBYemployee_countDESC;```这个查询的工作原理：-首先从departments表和employees表进行左连接，确保所有部门都会出现在结果中，即使没有员工。-然后使用GROUPBY子句按部门ID和部门名称进行分组。-使用COUNT(e.employee_id)函数计算每个部门的员工数量。-最后使用ORDERBY子句按员工数量降序排列结果。如果需要只显示有员工的部门，可以将LEFTJOIN改为INNERJOIN：```sqlSELECTd.department_id,d.department_name,COUNT(e.employee_id)ASemployee_countFROMdepartmentsdINNERJOINemployeeseONd.department_id=e.department_idGROUPBYd.department_id,d.department_nameORDERBYemployee_countDESC;```2.答案：OracleSQL性能优化是确保数据库高效运行的关键。以下是一些关键优化策略：-索引使用：-为经常用于WHERE子句、连接条件和排序操作的列创建适当的索引。-避免在索引列上使用函数，因为这会导致索引失效。例如，WHEREUPPER(column)='VALUE'不会使用索引，除非创建基于函数的索引。-对于大型表，考虑使用复合索引来处理多个列的查询条件。-定期分析索引使用情况，删除未使用的索引以减少DML操作的开销。-查询重写：-避免使用SELECT，只查询需要的列，减少数据传输量。-将复杂的子查询重写为连接，通常连接比子查询更高效。-使用EXISTS代替IN进行子查询检查，特别是在处理大型数据集时。-避免在WHERE子句中对列使用函数，这会导致全表扫描。-使用UNIONALL代替UNION，除非确实需要去除重复行，因为UNIONALL性能更好。-表连接方式：-根据数据分布和查询条件选择合适的连接方式（嵌套循环、哈希连接或排序合并连接）。-对于小表和大表的连接，嵌套循环通常更高效。-对于两个大表的等值连接，哈希连接通常更高效。-对于非等值连接或需要排序的连