oracle 学习摘记

------------------------------------------------------------
常用函数

1字符函数
1.1转换函数
lower 大写转小写
upper 小写转大写
initcap 首字母转为大写
1.2 字符操作函数
concat 连接字符串
substr 取子串
length 取长度
instr 取得位置值
lpad 将一个字符串按照某中模式来显示 lpad(sal,10,'*') *****50000
2.数值函数

round 返回四舍五入 round(45.926,2) = 45.93
trunc 截取小数 trunc945.926,2) = 45.92
mod 求余

3.日期函数
sysdate 返回日期和时间
DUAL是用来显示日期的名义表

months_between 返回两个日期的月份的差值
add_months 在日期上加上月份数
next_day 下一天的日期
last_day 月份的最后一天
round 四舍五入日期
trunc 截断日期

4.转换函数

隐式转换
from    to
VARCHAR2 OR CHAR NUMBER
VARCHAR2 OR CHAR DATE
NUMBER   VARCHAR2
DATE   VARCHAR2
显示转换
form   to   函数
number   VARCHAR2 to_char
date   varchar2 to_char
varchar2  number  to_number
varchar2  date  to_date

NVL函数
将NULL值转换成一个实际的值
eg. nvl(comm,0) 当comm的值为NULL时转换成0

DECODE函数
有点类似 switch case
eg.
    decode(job, 'analyst',sal*1.1,
         'clerk',sal*1.15,
    sal)
----------------------------------------------------------------------------------
数据库锁和表分区

手工锁定
1.共享锁
-锁定表
-仅允许其他用户执行查询操作
-不能插入,更新和删除
-多个用户可以同时在同一表中放置此锁

Lock table 表名 in share mode ;
2.共享更新锁
-锁定要被更新的行
-允许其他用户同时做其他的所有操作

lock table 表名 in share update mode;
3.排他锁
lock table 表名 in exclusive mode;

-----------------------------------------------------------------------

oracle数据库存储数据的方式

规则表,分区表,索引组织表,簇.
表分区优点:
-增强可用性. 单个分区出现故障,不影响其他分区
-均衡的I/O.不同的分区可以映射到不同的磁盘
-改善性能.
1.范围分区
-这种分区方法是根据表中列的值的范围对表进行分区
-分区时,首先依据列中的值可能的范围进行划分
create table Student
        (
          StudentId integer not null,
   Studentname varchar2(20)
   Score integer
)
Partition by range(Score)
(
          Partition p1 values less than(60),
   Partition p2 values less than(75),
   Partition p2 values less than(85),
   Partition p2 values less than(MaxValue)
);
2.散列分区法

-提供了一种通过指定分区编号来均匀的分布数据的方法.
-它是通过Hash函数将数据映射到相应的分区
-它可以使得数据均匀的分布到各个分区中.
Create table department
(
  Deptno int,
  Deptname varchar2(14)
)
Partition by hash(deptno)
(
  Partition p1,
  Partition p2
);
3.复合分区
它是先对数据进行范围分区,然后在每个子分区又进行散列分区.
Create table salgrade
( grate number,losal number, hisal number)
Partition by range(grade)
Subpartition by hash(losal,hisal)
(
Partition p1 values less than (10),
  (subpartition sp1,subpartition sp2),
Partition p2 values less than(20)
  (subpartition sp3,subpartition sp4)

)

4.列表分区
-列表分区允许用户明确的控制行到分区的映射
-eg.
Create table customer
(
  custNo int,
  custname varchar(20),
  custState varchar(20)
)
Partition by list(custState)
(
   Partition asia values('中国','韩国','新加坡');
   Partition Europe values('英国','法国','德国');
)
表分区的使用
插入和没有分区是一样的.
select * from student partition(p1);---查询单个分区的数据
维护用ALTER语句

表分区的相关数据字典
USER_TAB_PARTITIONS
USER_IND_PARTITIONS
--------------------------------------------------------------------

oracle中的数据库对象
1.同义词 = 数据库的替换名称
优点:
  -简化SQL语句
  -隐藏对象的名称和所有者
  -为分布式数据库的远程对象提供透明性
  -提供对对象的公共访问
同义词类型:
-私有
-公有
*CREATE SYNONYM name for oldname 创建同义词
数据字典
User_Synonym
2.序列
   它是能够自动产生连续唯一值的数据库共享对象
    用于为主键提供值

   Create sequence sequencename
     [increment by n]
  [start with n]
  [{maxvalue n| nomaxvalue}]
  [{minvalue n| nominvalue}]
  [{cycle | nocycle}]
  [{cache n | nocache}];
   两个重要的属性
     * nextval 返回下一个可用的值
     * currval 获取当前的序列中值
对于一新定义的序列必须先用nextval产生值,再可以用CURRVAL返回当前值
    Alter Sequence 修改序列
    Drop Sequence 删除序列
    User_Sequences 序列的数据字典
3.视图
creat view or replace

问题: 查询出前几条记录,在sqlserver中有 top关键字.
oracle 中的表都有两个隐藏字段:rowid ,rownum

4.索引
*与表关联的可选结构
*加快SQL语句执行
*减少磁盘I/O
*CREATE INDEX 语句用于创建索引
*在逻辑上和物理上独立于表中的数据
*Oracle 自动维护索引
4.1 唯一索引
-确保在定义索引的列中没有重复的值
-Oracle自动为主键和唯一键列创建唯一索引
-CREATE UNIQUE INDEX 语句用于创建唯一索引
4.2 组合索引=连接索引
-组合索引中的列可以按任意
-CREATE INDEX
4.3 反向键索引
-反转索引列中的每一个字节
-将数据插入操作分布在整个索引上
-在创建索引时使用REVERSE关键字
    实用场景:字段的数据的前几位比较固定,为了加快通过该字段检索.可以在该字段中创建反向键索引.
4.4 位图索引
  *为低基数列(重复数据较多的列)
   *BITMAP INDEX 创建
优点
  -减少响应时间
  -降低空间占用
4.5索引组织表
*按照索引的顺序来组织和存储数据的表
*organization index;
eg.
create table indorg
(
  vencode int primary key
  )
organization index;
普通表 VS. 索引组织表
rowid唯一标识行主键唯一的标识行
隐式的ROWID列没有ROWID列
基于ROWID访问基于主键访问
顺序扫描完全索引扫描
可以存储在簇中不能存储在簇中
支持分发,复制,分区不支持
4.6基于函数的索引
-基于一个或多个列上的函数或表达式创建的索引
-表达式中不能包含聚集函数(SUM,COUNT,AVG,MIN,MAX)
-不能在LOB,REF 或嵌套表列上创建
eg. create index idx_ename on emp(Lower(ename));

4.7 键压缩索引
-将索引键拆分为前缀项和后缀项
-在一个索引块中,通过后缀项共享前缀项即可达到压缩的目的
-节省磁盘空间
-在一个索引块中可以存储更多的键
-COMPRESS子句用于创建索引
我的理解是一种组合索引的优化.使得索引占用的空间更小

eg. create index idx_emp on emp(job,ename) compress 1;

4.8 分区索引

分区索引的类型
  本地索引
   本地前缀索引
   本地无前缀索引
  全局索引
   全局前缀索引
   全局无前缀索引

5 簇
簇:
*在公共列的两个或多个表的集合
*簇表中的数据存储在公共数据块中
簇键
*簇中行的唯一标识符
*用于获取行
创建簇:
*create cluster 语句创建簇
*首先创建簇,然后创建组成簇的表
优点:
  *
缺点:
  *插入效率降低
eg. create cluster class_cluster
(
   classno varchar2(10)
  )
  tablespace users;
    //为簇创建索引
    create index idxclasscluster on cluster class_cluster;
    create table stuclass(classno varchar2(10),classname varchar(20)) cluster class_cluster(classno);
    create table stu(classno varchar2(10),stuname varchar(20),stuid integer) cluster class_cluster(classno);

常用数据字典:
USER_TABLES 用户所有表信息
USER_VIEWS
USER_CONSTRAINTS
USER_DEPENDENCIES
USER_TAB_COLUMNS
USER_TRIGGERS
USER_TYPES
USER_SYNONYMS
USER_SEQUENCES
USER_OBJECTS
USER_LOBS
USER_SOURCES
USER_ERRORS

------------------------------------------
常用命令( sql plus)
desc 列出表结构
save 保存到某个文件
get 从文件中读取到缓存区
connect
@
/
run
ed 查看缓存区
exit

------------------------------------------------------------

posted @ 2007-10-09 18:45 kevin &&tiger 阅读(518) 评论(0) 编辑收藏举报

刷新页面返回顶部

oracle 学习摘记

公告