Oracle总结

一、构造表主键的SQL操作

    SELECT
       'SC' || TO_CHAR(SYSDATE,'yyyymmdd') || LPAD(SEQ_SMSS_ID.NEXTVAL,9,'0')
        AS SEC_ID
    FROM DUAL.

 二、修改表中字段的长度的SQL操作
    alter table TL_SHOP_SHOPPER_OPER modify(ACCOUNT_ID varchar2(100));

 

三、 oracle nvl函数
      通过查询获得某个字段的合计值,如果这个值位null将给出一个预设的默认值
      select nvl(sum(t.dwxhl),1) from tb_jhde t where zydm=-1这里关心的nvl
      的用法,nvl（arg,value）代表如果前面的arg的值为null那么返回的值为后面
      的value 如:NVL(a,b)就是判断a是否是NULL,如果不是返回a的值,如果是返回b
      的值通过查询获得某个字段的合计值,如果这个值位null将给出一个预设的默认
      值。

 四、oracle 简单分页函数
      select * from (
        select rownum rn,bb.* from (
          select * from TD_EC_ADVICE
        ) bb where rownum <= 10
      ) aa where rn > 0


五、oracle数据库中的加号(+)外联接操作符的使用方法
 首先我们要回到inner join的概念,inner join是返回满足匹配条件的行,可以是两个或多个表进行join
 而outer join的结果则扩展了simple join(即是inner join)的结果,即是在返回满足匹配条件的行的同时      也返回那些不满足匹配条件的行的联接方式.由此有三种分类 left[outer]join、right[outer]join、  
 full[outer]join.

 左联接比如A左联接B(即是既返回满足匹配条件的行,同时返回A表中不满足匹配条件的行,而B表的行则用
 NULL补充)

 实现方法 ① 用  A left[outer]join B on condition

                ②用outer join operator(+),格式如下: where A.matchfield=B.matchfield(+)

 右联接类同于左联接

 A右联接B

 实现方法 ① 用  A right[outer]join B on condition

                ②用outer join operator(+),格式如下: where A.matchfield(+)=B.matchfield

 全外联接

 实现方法 ①用 A full[outer]join B on condition

                ②用 union方法(即是取A左联接B和A右联接B的并集) 格式如下:

                      ...   where A.matchfield=B.matchfield(+)

                    union                              

                      ...   where A.matchfield(+)=B.matchfield

 

六、 数据导出：
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中
exp system/manager@TEST file=d:daochu.dmp full=y

2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:daochu.dmp owner=(system,sys)

3 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:datanewsmgnt.dmp tables=(inner_notify,notify_staff_relat)

4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:daochu.dmp tables=(table1) query=" where filed1 like '00%'"

上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面 加上 compress=y 来实现。

 七、数据的导入
1 将D:daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:daochu.dmp
imp aichannel/aichannel@HUST full=y file=file= d:datanewsmgnt.dmp ignore=y
上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
在后面加上 ignore=y 就可以了。

2 将d:daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:daochu.dmp tables=(table1)

基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入。

注意：
操作者要有足够的权限，权限不够它会提示。
数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。

附录一：
给用户增加导入数据权限的操作
第一,启动sql*puls
第二，以system/manager登陆
第三，create user 用户名 IDENTIFIED BY 密码 （如果已经创建过用户，这步可以省略）
第四，GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO 用户名字
第五, 运行-cmd-进入dmp文件所在的目录,
imp userid=system/manager full=y file=*.dmp
或者 imp userid=system/manager full=y file=filename.dmp

执行示例:
F:WorkOracle_Databackup>imp userid=test/test full=y file=inner_notify.dmp

屏幕显示
Import: Release 8.1.7.0.0 - Production on 星期四 2月 16 16:50:05 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.

连接到: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

经由常规路径导出由EXPORT:V08.01.07创建的文件
已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入
导出服务器使用UTF8 NCHAR 字符集 (可能的ncharset转换)
. 正在将AICHANNEL的对象导入到 AICHANNEL
. . 正在导入表 "INNER_NOTIFY" 4行被导入
准备启用约束条件...
成功终止导入，但出现警告。


附录二：
Oracle 不允许直接改变表的拥有者, 利用Export/Import可以达到这一目的.
先建立import9.par,
然后，使用时命令如下：imp parfile=/filepath/import9.par
例 import9.par 内容如下：
FROMUSER=TGPMS
TOUSER=TGPMS2 （注：把表的拥有者由FROMUSER改为TOUSER，FROMUSER和TOUSER的用户可以不同）
ROWS=Y
INDEXES=Y
GRANTS=Y
CONSTRAINTS=Y
BUFFER=409600
file==/backup/ctgpc_20030623.dmp

***********************************************************************************************************

 八、常用命令

1.创建表空间
create tablespace 表空间名
default ‘filename’/path’服务器端路径’ [size integer [k | m]] [autoextend [off | on]];
size:制定文件的大小，autoextend：用来启动或禁用数据文件的自动扩展。

2.创建新用户
create user 用户名
identified by 密码
[default tablespace 表空间]
[temporary tablespace 临时表空间];

3.为用户授权
grant 权限 to 用户
grant 权限 on 表名 to 用户

4.修改用户密码
alter user 用户名
identified by 密码;

5.删除用户
drop user 用户名 casaed;

6.查看当前用户
show user

7.查看当前时间
select sysdate from dual;

8.查看当前用户下的所有表
select table_name from user_tables;

9.查看当前表的结构
desc 表名

10.修改上一条的内容
edit;

Oracle 数据库对象

1．同义词
   a.创建同义词
        私有同义词
          create [or replace] synonym 同义词名 for 对象名；
        共有同义词
          create [or replace] public synonym 同义词名 for 对象名；
   b.删除同义词
          drop synonym同义词名;

2．序列
   a．创建序列
      create sequence 序列名
         [start with integer]
         [increment by integer]
         [maxvalue integer | nomaxvalue]
         [minvalue   integer | nominvalue]
         [cycle | nocycle]
         [cache integer | nocache];
   b．访问序列
      select 序列名.nextval from dual;
      select 序列名.currval from dual;
   c．根改序列
      alter sequence 序列名
         [increment by integer]
         [maxvalue integer | nomaxvalue]
         [minvalue   integer | nominvalue]
         [cycle | nocycle]
         [cache integer | nocache];
   d．删除序列
      drop sequence序列名;

Oracle 数据表管理(一)

1．创建表
   create table 表名
(字段名1 类型，
   字段名2   类型…);
2．修改表命令
   更改现有列
       alter table 表名 modify (column definition….);
   向表中添加新列
      alter table 表名 add (column definition….);
   删除表中现有的列
      alter table 表名 drop column 列名;
3．删除表中的记录而不删除表结构
      truncate table 表名;
4．删除与表的所有内容
      drop table 表名 cascade;
5．数据操作语言（DML）
   SELECT
     Select * | {[distinct] 字段名 | 表达式[列别名],…}
     From 表明
     [where 条件]
     [order by 字段名];
                distinct:限制只返回不同的列
   CTAS
     Create table 新表名 as select 字段名 from 旧表名；
     //拷贝旧表的结构和记录，不拷贝约束
   INSERT
      Insert into 表名 [(字段名)] values (值)；
   IIS
      Insert into 表名1(字段名1) select 字段名2 from 表名2;
       //表结构已存在，从另一个表中复制记录
   UPDATE
      Update 表名
      Set 字段名＝新值
       [where 条件];
   DELETE
      Delete 表名
       [where 条件];

6．事务控制语言
COMMIT
    Commit;//提交
SAVEPOINT
    Savepoint 保存点;
ROLLBACK
    Rollback or Rollback work;

7．数据控制语言
GRANT
    grant 权限 on 表名 to 用户；
REVOKE
    Revoke 权限 on 表名 from 用户；

8．集合操作符
UNION :合并查询结果，并删除重复的行
Select 字段名1 from 表名1
   Union
Select 字段名2 from 表名2；
UNION ALL：合并查询结果，并包括重复的行
Select 字段名1 from 表名1
   Union all
Select 字段名2 from 表名2；
INTERSECT：返回两个查询都有的行
Select 字段名1 from 表名1
   Intersect
Select 字段名2 from 表名2；
MINUS：返回第一个查询有而第二个查询中没有的行
Select 字段名1 from 表名1
   Minus
Select 字段名2 from 表名2；

Oracle 数据表管理(二)

9．锁和表分区
A．锁
    行级锁
     select …for update[of 字段] [wait n | nowait]；
      wait n :等待的秒数
    表级锁
     lock 表名 in 锁定模式 mode [nowait]
       表级锁的模式：
              行共享     (row share,rs)
              行排他     (row exclusive,rx)
              共享       (share,s)
              共享行排他 (share row exclusive,srx)
              排他       (exclusive,x)
B．表分区
     范围分区
     partition by range (column_name)
     (
    partition 分区名1 value less then(分区的边界值) [tablespace 表空间1],
    partition 分区名2 value less then(分区的边界值) [tablespace 表空间2]
)；

     散列分区
      partition by hash (column_name)
      partitions 散列分区的数目 [store in (分区使用的表空间)]；
          or
      partition by hash (column_name)
     (
      partition 分区名1 [tablespace 表空间1],
      partition 分区名1 [tablespace 表空间1]
     )；

      复合分区
      partition by range (column_name1)
      subpartition by hash (column_name2)
         subpartitions 散列分区的数目 [store in (分区使用的表空间)]；
        (
         partition 分区名1 value less then(分区的边界值),
         partition 分区名2 value less then(分区的边界值),
         partition 分区名N value less then(maxvalue)
         )；

      列表分区
       partition by list (column_name)
       (
       partition分区名1 values (分区键值的列表1)，
       partition分区名2 values (分区键值的列表2)，
       partition分区名N values (default)
       );
      default:允许存储前面的分区不能存储的记录

10.分区维护操作
添加分区
    alter table 表名 add partition 分区名 values less then(分区的边界值);
删除分区
    alter table 表名 drop partition 分区名；
截断分区
    alter table 表名 truncate partition 分区名;
合并分区
    alter table 表名 merge partitions 分区名1, 分区名2 into 分区名；
   拆分分区
    alter table 表名 split partition 分区名at (value) into (partition分区名1,partition 分区名2)；
   分区重命名
    alter table 表名 rename partition 旧分区名 to 新分区名 ;

11． 视图
a． 创建视图
    create [or replace] [force | noforce] view 视图名[列别名]
         as select 字段名 from 表名
     [with check option [constraint 约束名]]
     [with read only];
     with check option:指定只能插入或更新视图可以访问的行，
     with read only：确保不能在此视图上执行任何修改操作。
b． 创建带有错误的视图
     create [or replace] force view 视图名[列别名]
         as select 字段名 from 表名
      [with check option [constraint 约束名]]
    [with read only];
      with check option:指定只能插入或更新视图可以访问的行，
    with read only：确保不能在此视图上执行任何修改操作。
C．删除视图
     drop view 视图名;

12． 索引
A． 普通索引
     create index 索引名 on 表名 (字段名) [tablespace 表空间];
B． 唯一索引
     create uniqe index 索引名 on 表名 (字段名);
C． 组合索引
     create index 索引名 on 表名(字段名1,字段名2);
D． 反向键索引
     create index 索引名 on表名 (字段名) revser;
E． 位图索引
     create bitmap index 索引名 on 表名 (字段名);
F． 索引组织表
     create table table_name (字段名 类型 约束) organization index;
G． 索引中的分区
    a．局部分区索引
       create index 索引名 on表名 (字段名) local;
     b．全局分区索引
       create index 索引名 on表名 (字段名) global;
     c．全局非分区索引
      create index 索引名 on表名 (字段名) ;
------------------------------------------------------------------------------------------------------------

九、规则表---〉分区表
索引组织表
簇
修改列名
alter table 表明 rename column rename 老列名 to 新列名
添加 字段
alter table 表名 add(字段名 类型)；
删除字段
alter table 表名 drop column 字段名
修改列属性
alter table 表名 modify （字段名 新属性）
设置某一个字段为无用状态
alter table 表名 set unuesed column 字段名
删除无用字段
alert table 表名 drop unused cloumns
添加主键
alert table 表名 add constraint pkKey primary key（字段列表）
表分区
oracle 允许用户对标进一步的规划，即对标进一步的拆分，将表分成若干个逻辑部分，
每个部分成其为标的分区
表分区的优点
增强可用性。单个分区出现故障不会影响到其它分区
均衡IO。不同的分区可以影射到不同的磁盘中
改善性能
范围分区法
这种分区方法是根据表中的列值的范围对标进行分区
分区时， 首先依据列中得值的可能的范围进行划分
例：
create table student
（
studentId integer not null,
studentName varchar2(20),
scroe integer
）
partition by rang(Score)
(
partition p1 values lessthan(60),
partition p2 values lessthan(70),
partition p3 values lessthan(80),
partition p4 values lessthan(maxvalue),
) ;
散列分区
散列分区法提供了一种通过制定分区编号来均匀地分布数据的方法
他通过hash 函数间数据映射到相应的分区上
它使得数据均匀的分配到各分区上，各分区大小趋向一致
例：
create table department
（
depno integer not null,
depname varchar2(20),
）
partition by hash(depno)
(
partition p1 ,
partition p2,
) ;
复合分区
复合分区是先对数据进行范围分区，然后在每个字分区理由进行散列分区的一种分区方法
例：
create table salgrade
（grade number ,losal number hisal number , hisal number ）
partition by rang(grade)
subpartition by hash (losal, hisal)(
partition p1 value lessthan (10),
(subpartition sp1,subpartition sp2),
partition p1 value lessthan (20),
(subpartition sp3,subpartition sp4),
)
列表分区
列表分区允许用户明确的控制行到分区的映射
不同的范围分区或列分区，他运许按自然的方式对无序和不相干的数据集进行分组的组织
例如：
partition by list(字段)
(
partition 列名称 values (值列表)，
partition 列名称 values (值列表)，
。。。
。。。
partition 列名称 values (值列表)
)
指定的分区中查询
select * from tableName partition(p1)
表分区的维护
移动，添加，删除，结合， 截断，拆分，合并，交换