ORACLE基本操作

最近学做项目用到ORACLE数据库，但是sql语句老是记不住，所以整理近一年所用到的所有的sql基本操作、如后期越到更多的问题，将持续更新；

一、表操作（注：添加字段、表名全都是大写字母，JAVA有时无法连接）

1、创建表

DROP TABLE 表名;

CREATE TABLE 表名(

"ID" VARCHAR2(40 BYTE) NOT NULL ,

"NAME" VARCHAR2(255 BYTE) NULL ,

字段

)

LOGGING

NOCOMPRESS

NOCACHE

;

COMMENT ON COLUMN 表名.字段名 IS '注解';

ALTER TABLE 表名 ADD CHECK ("ID" IS NOT NULL);

ALTER TABLE 表名 ADD PRIMARY KEY ("ID");

2、修改表

ALTER TABLE 表名

ADD ( "ID" VARCHAR2(20) NULL  ) ;

COMMENT ON COLUMN 表名.字段名 IS '归口领域编码';

3、删除表

DROP TABLE 表名;

4、创建和数据库中表名一样的表

CREATE TABLE 表名1 

AS select * from 表名2

5、恢复表

flashback table test_drop to before drop;

create table t_table_recove --新表
as
select * from 旧表--你操作的那张表 
as of timestamp TO_TIMESTAMP('2017-12-29 9:00:00','YYYY-MM-DD HH24:MI:SS');
insert into 旧表 select * from t_table_recove

二、视图

CREATE OR REPLACE VIEW 表名
AS 

select * from 表名2

三、数据操作

（一）Oracle删除重复数据只留一条

查询及删除重复记录的SQL语句

1、查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断

select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1)

 

 

2、删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录

DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);

 

3、查找表中多余的重复记录（多个字段）

select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)

 

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

 

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录

select * from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

 6、从一表数据添加到另一表

INSERT Into 表名1
(字段1,字段2。。。)
SELECT 字段1,字段2。。。 from 表名2

（二）、排序null值排在最前或者最后

nulls lastNulls first和nulls last是Oracle Order by支持的语法

将nulls始终放在最前 ；

select * from zl_cbqc order by cb_ld nulls first //cb_ld是空值的始终放到最前 。

将nulls始终放在最后 

select * from zl_cbqc order by cb_ld desc nulls last。

 

（三）、从一个表中获取数据插入另外一个表中

Insert into 表名(字段)Select 字段 from 表名  where 条件;

 

（四）日期操作

1、取出数据转为字符串

to_date(字段,'yyyy/mm/dd') as 字段

2、字符串数据插入数据库

to_date(字段,'yyyy/mm/dd') as 字段

 

（五）字符串操作

1、字符查找函数instr

格式一：instr( string1, string2 )    /   instr(源字符串, 目标字符串)

select instr('helloworld','l') from dual; --返回结果：3    默认第一次出现“l”的位置

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] )   /   instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

select instr('helloworld','l',2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置

 

2、字符截取函数substr

1）substr函数格式   (俗称：字符截取函数)

　　格式1：substr(string string, int a, int b);

　　格式2：substr(string string, int a) ;

解释：

格式1
1、string 需要截取的字符串 
2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
3、b 要截取的字符串的长度
格式2
1、string 需要截取的字符串
2、a 可以理解为从第a个字符开始截取后面所有的字符串。

 

3、字符串连接concat

select concat('aa','bb') from test;

4、执行存储过程，执行函数

declare
p_name varchar2(100);
p_batch varchar2(100);
begin
 p_name:='257BD9DB-0FC8-42F1-B5BB-2C8CA36EA861';
p_batch:='';
 CLEAR_FLOW(p_name,p_batch);
end;