一、用户管理和表空间
注意:做开发时,建立好表空间,建立用户,再通过角色赋权限(
grant connect to zhangsan
,grant resource to zhangsan
)
- oracle中如何新建数据库
- oracle中一个数据库实例就对应一个数据库
- 如果多个项目使用同一个oracle实例,则会通过新建不同用户,分配不同表空间来区分
- 新建表空间
- 表空间是一个概念逻辑
- 表空间会指向一个或多个物理磁盘中的数据文件
-
create tablespace tableSpaceName datafile 'd:xxx.dbf' size 10M autoExtend on/off -- autoExtend是自动增长,on是开启
- 删除表空间
drop tablespace tableSpaceName
- oracle中的用户
- sys:超级用户,属于管理员中的管理员,维护系统信息,很少用
- system:管理员,拥有DBA权限,管理普通用户,不建议用它来建表一类操作
- scott:普通用户,数据库安装完成系统默认新建的示例用户
- 新建用户
create user zhangsan
identified by 123
default tablespace xxx
temporary tablespace xxx
-- identified by:标识,密码
-- default:默认表空间
-- temporary:临时表空间
- 删除用户,如果用户下面有新建表或者其他对象,则需要级联删除
drop user zhangsan cascade
二、数据库权限管理
- 用户新建完成,分配好表空间,但是缺乏权限,也无法做操作
- 数据库权限的分类
- 系统权限
- create session 连接数据库
- create table 创建表
- create view 创建视图
- createsequence 创建序列
- 对象权限(针对某张表的操作权限)
- 给用户赋权限
- 管理员直接向用户赋权限
grant create session to zhangsan
grant create table to zhangsan
- 管理员向角色赋权限,然后把角色赋予用户
grant connect to zhangsan
grant resource to zhangsan
- 为了方便起见,避免繁琐的权限授予,一般会直接授予角色给用户
- 常见角色
- connect:连接登录数据库的角色
- resource:对数据库资源进行操作的角色
- dba:拥有所有系统权限,属于数据库管理员,一般不会授予
- 撤销权限的语法
revoke create table from zhangsan
revoke connect from zhangsan
- 数据库用户安全设计原则
- 数据库用户权限授权按照最小分配原则
- 数据库用户分为管理、应用、维护、备份四类用户
- 不允许使用sys和system用户简历数据库应用对象
- 禁止grant dba to user
三、序列
- 序列:sequence(序号)
2.序列是用来生成序号的数据库对象,生成的序号有如下特点:
- 唯一,生成过了下一次的肯定不同
- 连续
- 整数
- 创建序列
create sequence sequenceName
[start with integer]
[increment by integer]
[maxValue integer | noMaxValue]
[minValue integer | noMinValue]
[cycle | nocycle]
[cache integer | noCache]
- 序列的值只能访问,不能人工操纵
- 序列的NextVal属性
- 序列创建后第一次使用nextVal时,将返回该序列的初始值
- 以后再访问,则使用increment by子句来增加序列值,并返回这个新值
6.序列的CurrVal属性 - 返回序列的当前值,即最后一次访问NextVal时返回的值
- 序列通常用于生成自动增长的主键值
- 删除序列
drop sequence sequenceName
四、同义词
- 同义词:synonym
- 同义词其实就是给数据库中的对象起别名
- 同义词本身不占用存储空间
- 同义词的分类:私有同义词和公有同义词
- 创建私有同义词
create synonym sy_emp for emp
- 使用同义词
select * from sy_emp
- 创建公有同义词
create public synonym sy_emp2 for emp
- 私有同义词
- 只能被当前模式的用户访问
- 名字不能和数据库中已有对象同名
- 公有同义词
- 公有同义词可以被所有的数据库用户访问
- 删除同义词
drop [public] synonym xxx
五、索引
- 数据库的索引类似于书本的目录
- 查找数据前先查找目录
- 通过目录来定位数据,可以缩小搜索范围,不然查找整本书
- 最终目的是为了提高查询效率
- 索引的分类:
- 物理分类
- B书索引(标准索引),基于二叉树
create [unique] index index_name on tableName(column_list) [tablespace tableSpaceName]
- 反向键索引:将索引列的值进行反向
- 位图索引:为性别,婚姻状况,行政区等等这些基础值比较小的列建立索引。(bitmap)
- 逻辑分类
- 唯一索引:索引关键字只能指向表中的一行
- 非唯一索引:单个关键字可以有多个与其关联的行
- 不同索引适合不同场景和不同类型的数据
- 创建索引create index
- 删除索引drop index
6.什么列适合建索引
- 频繁作为条件搜索的列
- 经常用作查询选择的列
- 经常排序,分组的列
- 经常用作连接的列
- 索引可能提高查询效率,但是可能会在一定程度上降低更新的效率,因为增删改可能需要同时更新索引
六、分区表
- 什么是分区表
- 把表中的所有行分为几个部分
- 并将这些部分存储在不同的位置
- 被分区的表叫做分区表
- 分成的每一个部分叫做一个分区
- 分区表的有点
- 改善查询性能,分区后查询可以访问特定分区,缩小搜索范围
- 表更容易管理
- 便于备份和恢复,可以独立的备份和恢复每个分区
- 提高数据高全性,将每个分区放在不同盘,减少所有分区同事损坏的概率
- 分区表的常用分类
- 范围分区(range),以列值来划分区间
create table salesInfo(
id number,
productId,
sales_date date
...
)
partition by range(sales_date)
(
partition p1 values less than (to_date('2013-01-01','yyyy-MM-dd')),
partition p2 values less than (to_date('2014-01-01','yyyy-MM-dd')),
partition p3 values less than (to_date('2015-01-01','yyyy-MM-dd')),
partition p4 values less than (to_date('2016-01-01','yyyy-MM-dd')),
);
-- 查询2014年的数据:select * from salesInfo partition(p2)
-- 删除2014年的数据:delete from salesInfo partition(p2)
- 间隔分区(interval),11g的新分区方法,实现自动分区
create table salesInfo(
id number,
productId,
sales_date date
...
)
partition by range(sales_date)
Interval(NumToYmInterval(3,'MONTH'))
( -- 起始区间
partition p1 values less than (to_date('2013-01-01','yyyy-MM-dd')) )
-- 这个例子是以间隔时间间隔分区,每满三个月自动分区
七、数据的导入导出
- 通过数据的导入导出,可以实现数据备份,防止数据被破坏或意外丢失
- 数据的导入导出方式很多
- 用oracle自带工具exp导出和imp导入(export/import)
- 用PL/SQL Developer来完成数据的导入导出
- oracle export(本质上就是用oracle的导出命令完成):导出为.dmp的文件格式,.dmp文件是二进制的,可以跨平台,还能包含权限,用的最广。
- SQL Insert:导出为.sql文件的,可用文本编辑器查看,通用性比较好,但效率不如第一种,适合小数据量导入导出。尤其注意的是表中不能有大字段(blob,clob,long),如果有,会提示不能导出
- PL/SQL Developer:导出为.pde格式的,.pde为PL/SQL Developer自有的文件格式,只能用此工具导入导出,不能用编辑器查看。