oracle面试宝典

本文主要是介绍oracle面试宝典，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

数据库

一、where与having

WHERE用于行过滤。
SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

二、字符串与日期，数字之间相互转化

到字符串：to_char()；到日期：to_date()；到数字：To_number(char,’format’)；

三、in与exsits区别

In：跟在WHERE 子句后面规定多个筛选值（where 列名 in (值1,值2,值3)）；
	（确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选）
Exsits：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。
	（指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。）
IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

四、like模糊查询

通配符：%代替0到多个字符，_代替一个字符

五、nvl函数

Nvl(e1,e2)：如果e1的值为null，返回e2，否则返回e1本身；
Nvl2(e1,e2,e3)：如果e1的值为null，返回e3，否则返回e2；

六、排序oder by

升序asc，降序desc，默认为升序；多列排序：oder by 列名,列名

七、快速建表，复制数据

1复制表中的数据到新建的表中，表不存在
create table tableName1 as select * from tableName2
2复制表中的数据
insert into tableName select * from tableName

八、大数据量

九、提高查询效率

1优化SQL查询，去掉不必要的列；2建立查询条件索引；

十、UNION, UNION ALL

UNION 操作符用于合并两个或多个 SELECT 语句的结果集；UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型，每条 SELECT 语句中的列的顺序必须相同。
UNION 操作符选取不同的值，UNION ALL可以选取重复的数值；

十一、常用函数

Dual，伪表；
CASCADE，级联删除；
||，连接字符串；
SUBSTR (char, n, len)，取子字符串，选取n位开始，长度为len的子串；
LENGTH(char)，找出一个字符串的长度；
INSTR (char, substr)，查找子串位置，
LPAD | RPAD (str,len,padstr)，返回字符串str，左/右填充至len长度，长度大于len则缩减；
TRIM()，移除掉一个字串中的字头或字尾，最常见的用途是移除字首或字尾的空白；
REPLACE (char, str1, str2)，字符串替换，将子串str1替换为str2；
LOWER(char)，变为小写；
UPPER(char)，变为大写
INITCAP(char)，首字母变为大写；
Floor()，向下取整；
Ceil()，向上取整；
Round()，四舍五入；

十二、日期相关的函数

SYSDATE，返回当前日期；
ADD_MONTHS(date,n)函数可以得到date之前或之后n个月的新日期；
LAST_DAY(date)，返回当前月的最后一天；
MONTHS_BETWEEN (date1, date2)，用于计算date1和date2之间有几个月；
NEXT_DAY(date,week),返回下周某一天的日期；
TO_CHAR(date,’day’)，返回日期date是周几；

例子：
返回两个日期之间的天数：
select floor(sysdate - to_date(‘20161018’,‘yyyymmdd’)) from dual;

十三、Case when与DECODE

Case函数一旦满足了某一个WHEN ，则这一条数据就会退出CASE WHEN，而不再考虑其他CASE；
decode（列名，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）；

十四、数据库三范式

1、所有字段值都是不可分解的原子值；
2、一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中；
3、每一列数据都和主键直接相关，而不能间接相关；

十五、左连接、右连接、内连接

内连接：INNER JOIN返回两个表中联结字段相等的行；
左连接：LEFT JOIN返回包括左表中的所有记录和右表中联结字段相等的记录；
右连接：RIGHT JOIN返回包括右表中的所有记录和左表中联结字段相等的记录；

十六、having与聚合函数

GROUP BY 语句可结合一些聚合函数来使用，HAVING语句在GROUP BY语句后筛选数据。
	SELECT column_name, aggregate_function(column_name)
	FROM table_name
	WHERE column_name operator value
	GROUP BY column_name;

常用的聚合函数
AVG()：返回数值列的平均值；
SELECT AVG(column_name) FROM table_name
COUNT()：返回匹配指定条件的行数；
SELECT COUNT(column_name) FROM table_name;
FIRST()/LAST() ：返回指定的列中第一个/最后一个记录的值；
SELECT FIRST/LAST (column_name) FROM table_name;
MAX()/MIN() ：返回指定列的最大值/最小值；
SELECT MIN/MAX(column_name) FROM table_name;
SUM() ：返回数值列的总数；
SELECT SUM(column_name) FROM table_name;

十七、视图

视图是基于 SQL 语句的结果集的可视化的表；视图中的字段就是来自一个或多个数据库中的真实的表中的字段。
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

十八、存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。
--1、一组为了完成特定功能的SQL语句集
--2、第一次编译后再次调用不需要再次编译（高效）
--3、不同的数据库存储过程定义的方式不同，思路是相同的，语法不同
CREATE OR REPLACE Procedure myProcedure(Name Varchar2)

Is

• 定义各种变量
  ename2 Varchar2(32);
  job2 Varchar2(32);
  Begin
• 写业务逻辑处理
  Select ename,job Into ename2,job2 From emp Where ename = Name;
  dbms_output.put_line(ename2);
  End myProcedure;

十九、触发器

触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器可以分为两类：DML触发器和DDL触发器，DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句，DML触发器分为：after触发器（之后触发），insert触发器，update触发器，delete触发器。

二十、约束类型

NOT NULL（非空）、
UNIQUE（唯一）、
主键、
外键、
CHECK

二十一、序列

序列是一种数据库对象，用来自动产生一组唯一的序号；
CREATE SEQUENCE sequencename
 [INCREMENT BY n]             	定义序列增长步长，省略为1
 [START WITH m]               	序列起始值，省略为1，一经创建不能修改
 [{MAXVALUE n | NOMAXVALUE}] 	序列最大值，
 [{MINVALUE n | NOMINVALUE}] 	序列最小值
 [{CYCLE | NOCYCLE}]         	到达最大值或最小值后，继续产生序列（默认NOCYCLE）
 [{CACHE n | NOCACHE}];      	CACHE默认是20

二十一、理解rownum

rownum用于标记结果集中结果顺序的一个字段，它的特点是按顺序标记，而且是连续的，换句话说就是只有有rownum=1的记录，才可能有rownum=2的记录。rownum关键字只能和<或者<=直接关联，如果是>或者=则需要给他起个别名。

二十二、oracle分页

select * from (
	select * from (
		select s.*,rownum rn from student s 
	) where rn<=5)
 where rn>0

二十三、Oracle数据库体系

1 Oracle数据库的基本类型：char，varchar2，number，date，clob；
2 Oracle的物理结构：数据文件，控制文件和日志文件；
3 Oracle的逻辑结构：
	表空间--最大的逻辑存储结构，与物理上的数据文件相对应；
	段--数据库终端用户将处理的最小单位；
	区--磁盘空间分配的最小单位；
	块--管理存储空间的最基本单位；

必背的sql语句

• 1：oracle 分页
  select * from (select t.*, rownum rn from (select * from menu order by id desc) t where rownum < 10) where rn >=5

• 2: mysql 分页
  select * from music where id limit 5,5

• 3：oracle中如何快速将一张表的数据复制到另外一张表中(另外一张表不存在，另外一张 表存在，但数据为空)
  1、.不存在另一张表时：
  create table 新表 as select * from 将要复制的表
  2、存在另一张表时：
  insert into 新表名 select 字段 from 将要复制的表名

• 4：音乐专辑
  查询出special app:ds:special表中的id 专辑名 并下面有多少首歌曲
  Select s.id , min(s.sname),count(m.mid) from special s inner
  join ms m on s.id=m.id group by s.id

• 5：快速删除一张表（不可事物回滚，也就是没有日志记录）
  TRUNCATE from 表名

• 6：inner join
  select 查找信息 from 表名 1 inner join 表名2 on 表名1.列名 = 表名2.列名

• 7：left join
  左外连接 select 查找信息 from 表名1 left join 表名2 on 表名1.列名 = 表名2.列名

• 8：right join
  右外连接 select 查找信息 from 表名1 right join 表名2 on 表名1.列名 = 表名2.列名

• 9：oracle中查询遍历树形结构（start with）
  select * from extmenu
  start with pid=1
  connect by prior id = pid
  快速删除父节点以及父节点下的所有节点：
  Delete from extmenu where id in (
  elect * from extmenu
  start with pid=1
  connect by prior id = pid
  )

• 10：查询出来60-70,80-90,95-100学生的信息
  select * from stu where chengji between 60 and 70 or between 80 and 90 or between 95 and 100
  select * from stu where chengji > 60 and chengji < 70 or chengji > 80 and chengji < 90 or chengji > 95 and chengji < 100

• 11：用exists替换in------进行联表查询
  select * from dept where exists(select * from emp where emp.deptno=dept.deptno);
  或
  select * from dept d inner join emp e on d.deptno = e.deptno(只查询出两表共同拥有的字段数据)

• 12：删除表中的重复数据：
  delete from xin a where a.rowid != (
  select max(b.rowid) from xin b
  where a.name = b.name
  );

• 13：row_number()，rank() over ，dense_rank() over 按工资排序
  select sal,
  row_number() over(order by sal desc) rank1,
  rank() over(order by sal desc) rank,
  dense_rank() over(order by sal desc) drank
  from emp

• 14：select * from (select emp.* from(
  dense_rank() over(partition by departNo order by sal desc)
  rk from emp )
  Where rk=4

这篇关于oracle面试宝典的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！