面试汇总（六）--Oracle数据库基础知识汇总

Oracle数据库基础知识汇总

1.一个实例对应一个数据库

2.模式

3.表空间(段>区>块)提高管理效率

从用户的角度看，一个库中有若干模式（模式唯一对应同名用户），一个模式下有表、视图和存储过程等数据库对象。

从存储的角度看，一个库中有若干表空间，负责存储数据库中的各种对象。

注意： 一个模式下的表可以存储于不同的表空间中。

         存储于同一表空间中的表可以在不同的模式下。

对数据库访问使用模式概念，对数据存储管理使用表空间概念。

 

表空间

表空间：逻辑结构，由多个数据文件组成，

           从物理上讲，表是存在于数据文件里

           从逻辑上讲，表是存在于表空间

/**

(oracle数据库逻辑结构上包括表空间、段、区和块，物理上由数据文件组成，一个表空间包含一个或多个数据文件。)

表空间用于从逻辑上组织数据库的数据，通过表空间可以控制数据库占用的磁盘空间，同时dba可以将不同的数据类型部署到不同的位置，这样有利于提高I/O性能，同时利于备份和恢复等操作。

**/

创建表空间一般由特权用户或dba角色来执行，需要有权限

 

4.授权使用grant,收回权限使用 revoke (解锁账户--使用profile管理用户)

grant connect to xiaoming (with admin option )

revoke select on emp from xiaoming;
alter user tea  account unlock; 

概述：profile是口令限制，资源限制的命令集合，当建立数据库的，oracle会自动建立名称为default的profile。当建立用户没有指定profile选项，那么oracle就会将default分配给用户。

 

5.实施步骤:

   a.创建用户

    create user user1 identified by u123 default tablespace aa;(密码必须以字母开头)

   b.为用户分配connect权限

    grant connect  to user1;

   c.分配表空间使用权限   

    grant unlimited tablespace to user1;

 

===============================================================

修改密码：

如果给自己修改密码可以直接使用
password 用户名
如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限
SQL> alter user 用户名 identified by 新密码

sys用户是超级用户，该用户默认的密码是change_on_install

system用户是管理操作员，默认的密码是manager

sys和system这两个用户最大的区别是在于有没有create database的权限

================================================

*如何取消重复行DISTINCT

*使用列的别名： ...............as .....

*如何处理null值,使用nvl函数来处理

*如何连接字符串（||）

*如何使用like操作符
%：表示0到多个字符  _：表示任意单个字符

*数据分组 ——max，min， avg， sum， count

*如果要删除的用户，已经创建了表，那么就需要在删除的时候带一个参数cascade;

 

6.insert  update   delete/truncate    drop

 

7.函数：日期 ,数学，字符串函数，系统函数 decode

（1）日期函数

     默认情况下日期格式是dd-mon月-yy 即12-7月-78
     sysdate: 该函数返回系统时间
     add_months(d,n)
     last_day(d)：返回指定日期所在月份的最后一天
（2）数学函数

数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round，

常用的有：

round(n,[m])该函数用于执行四舍五入，如果省掉m，则四舍五入到整数，如果m是正数，则四舍五入到小数点的m位后。如果m是负数，则四舍五入到小数点的m位前。
trunc(n,[m]) 该函数用于截取数字。如果省掉m，就截去小数部分，如果m是正数就截取到小数点的m位后，如果m是负数，则截取到小数点的前m位。
mod(m,n)
 floor(n) 返回小于或是等于n的最大整数
 ceil(n)   返回大于或是等于n的最小整数

（3）系统函数

sys_context
1）terminal：当前会话客户所对应的终端的标示符
2）lanuage: 语言
3）db_name： 当前数据库名称
4）nls_date_format： 当前会话客户所对应的日期格式
5）session_user： 当前会话客户所对应的数据库用户名
6）current_schema： 当前会话客户所对应的默认方案名
7）host： 返回数据库所在主机的名称
通过该函数，可以查询一些重要信息，比如你正在使用哪个数据库？
select sys_context('USERENV','db_name') from dual;
注意：USERENV是固定的，不能改的，db_name可以换成其它,比如select sys_context('USERENV','lanuage') from dual;又比如select sys_context('USERENV','current_schema') from dual;

 

 

8.分组函数  min max count ...

 

9.查询  分组查   连接查   子查询  

如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by

 子查询 ：
   子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
oracle的分页一共有三种方式 ：

（1）根据rowid来分 （执行时间0.03秒） 
（2）按分析函数来分 （执行时间1.01秒 ）

（3）按rownum来分 (执行时间0.1秒 )

Select * from （select s.*,rownum rn from

(select  *  from stuinfo where stu_id=’1001’ ）s  

where rownum<=10）

Where rn>=6

     

 

10.结果集   

  差集， minus 显示存在第一个集合中，而不存在第二个集合中的数据。  

  交集，intersect 使用该操作符用于取得两个结果集的交集。

  并集  union当使用该操作符时，会自动去掉结果集中重复行       union all它不会取消重复行，而且不会排序

 

11.pl  变量(几种数据类型，游标 系统游标)  条件   循环  

Oracle支持的数据类型：

（1）字符类

Char：定长 ，最大2000个字符。

varchar2(20)： 变长，最大4000个字符。

clob(character large object) 字符型大对象 最大4G
char 查询的速度极快，浪费空间，查询比较多的数据用。
varchar 节省空间

（2）数字型

 number范围 -10的38次方到10的38次方 ，可以表示整数，也可以表示小数

number(5,2) number(5)

（3）日期类型

date 包含年月日和时分秒 

timestamp 可以精确到毫秒

（4）图片

blob 二进制数据 可以存放图片/声音  4G 

 

定义游标：CURSOR 游标名 IS SELECT查询语句

打开游标：open 游标名

提取数据：fetch 游标名 into 变量名

关闭游标：CLOSE 游标名

 

游标：从表中检索出结果集，从中每次指向一条记录进行交互的机制。 关系数据库中的操作是在完整的行集合上执行的。

游标有什么作用？

 ①指定结果集中特定行的位置。

 ②基于当前的结果集位置检索一行或连续的几行。

 ③在结果集的当前位置修改行中的数据。

 ④对其他用户所做的数据更改定义不同的敏感性级别。

 ⑤可以以编程的方式访问数据库。

 

效率低（实际数据较多情况下）尽量避免在游标循环中再进行表连接的操作

 

12.存储过程  函数  包   触发器  序列  事务(原子性，一致性，隔离性，持久性)

存储过程:一组为了完成特定功能的SQL 语句集存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数来执行它。

触发器：触发器是一种特殊类型的存储过程，触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用

触发器作用:强化约束 跟踪变化  级联运行 存储过程的调用 触发器可以调用一个或多个存储过程

函数：函数用于返回特定数据。执行时得找一个变量接收函数的返回值

包：所以通过使用包就可以分类管理过程和函数 包分两部分，包规范和包体

 

序列： 是oacle提供的用于产生一系列唯一数字的数据库对象

       主要用于提供主键值 自动提供唯一的数值 提高访问效率

 

事务：事务的任务便是使数据库从一种状态变换成为另一种状态 commit rollback

 

过程(Procedure) 是一个存储在数据库中命名了的P L / S Q L语句集合。它可以包含输入输出参数，是可执行的代码集合，并可以被其他应用程序调用。

函数(Function)也是存储在数据库中的P L / S Q L语句集合。与过程的主要区别是函数必须有一个返回值。

包(Package) 是根据相同功能的特点把过程和函数捆绑在一起的集合。所有的内部过程和函数在数据字典中作为一个单一的存储包记录，便于程序的组织和管理。

数据库触发器(Trigger) 也是存储的可执行P L / S Q L语句集合。触发器主要是通过事件进行触发而被执行的，数据库触发器在插入、更改或删除语句之前或之后运行。

 

 

 

 

 

 

 

13.索引  视图

索引是一种供服务器在表中快速查找一个行的数据库结构

　　（1）快速存取数据。

　　（2）既可以改善数据库性能，又可以保证列值的唯一性。

　　（3）实现表与表之间的参照完整性

　　（4）在使用orderby、groupby子句进行数据检索时，利用索引可以减少排序和分组的时间。

 

视图：

    1.为用户集中数据，简化用户的数据查询和处理。

2.屏蔽数据库的复杂性，用户不必了解数据库的复杂性。

3.简化用户权限的管理，只授予用户使用视图的权限。

4.便于数据共享，多个用户不必都定义所需的数据。

5.可以重新组织数据，以便关联到其他应用中。

 

14.优化

 Oracle SQL性能优化：

   WHERE子句中的连接顺序．表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

   SELECT子句中避免使用 ‘ * ‘：

   尽量多使用COMMIT

   用TRUNCATE替代DELETE

   减少对表的查询

   使用表的别名

   整合简单,无关联的数据库访问

 

15.三范式

（1）字段的原子性（列不可再分/字段无二义性）；

（2）非主属性必须依赖主属性（一个表描述一个事物）；

（3）非主属性必须直接依赖主属性（不能存在传递依赖）。

 

第一范式就是无重复的列

第二范式就是非主属性非部分依赖于主关键字

第三范式就是属性不依赖于其它非主属性。

16.数字字典

 数据字典记录了数据库的系统信息，它是只读表和视图的集合，数据字典的所有者为sys用户。 
  用户只能在数据字典上执行查询操作（select语句），而其维护和修改是由系统自动完成的。
 数据字典的组成：数据字典包括数据字典基表和数据字典视图，其中基表存储数据库的基本信息，普通用户不能直接访问数据字典的基表。数据字典视图是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典视图取得系统信息。数据字典视图主要包括user_xxx，all_xxx，dba_xxx三种类型。