ORACEL数据库基本操作
1 彻底删除表:
SQL> drop table tableName purge;
2 清空回收站:
SQL> purge recyclebin;
3 重命名表名或视图名:
SQL> rename oldtableName to newtableName;
4 批量更新:
update talbe1 set table1.field2=(select field2 from table2 where table1.filed1=table2.field1);
-- 把表table2的字段field1更新表tabel1的字段field1,条件为表table1的字段field2后六位等于table2的字段field2
update table1 set table1.field1=(select table2.field1 from table2
where substr( table1.field2,(length(table1.field2)-6+1),6 ) =substr( table2.field2,(length(table2.field2)-6+1),6 );
update table1 set table1.field1=(select table2.field1 from table2
where substr( table1.field2,(length(table1.field2)-6+1),6 ) =substr( table2.field2,(length(table2.field2)-6+1),6 );
5 查询时间:
(a)select to_char(clqssj,'yyyymmdd') as date1 from table1 where to_char(clqssj,'yyyymmdd') like '%2005101%';
(b)select clqssj as date1 from table1
where clqssj between to_date('2005-10-09','yyyy-mm-dd') AND to_date('2005-10-11','yyyy-mm-dd') ;
(b)select clqssj as date1 from table1
where clqssj between to_date('2005-10-09','yyyy-mm-dd') AND to_date('2005-10-11','yyyy-mm-dd') ;
6 将一表中的数据备份到新表中:
create table newtable as select * from oldtable;
6 获取某一字段的后八位:
SELECT NVL(field1,'') AS field1, substr( NVL(field2,''),(length(NVL(field2,''))-8 +1),8 ) AS field2 FROM table1;
7 批量插入:
insert into table1 select * from table2;
8 查询重复记录:
SELECT A.field1 FROM table1 A
GROUP BY CHINESENAMEA.field1
HAVING ( (SELECT COUNT(*) FROM table1 B WHERE b.chinesename = a.chinesename ) > 1 )
GROUP BY CHINESENAMEA.field1
HAVING ( (SELECT COUNT(*) FROM table1 B WHERE b.chinesename = a.chinesename ) > 1 )
9 在ORACLE 数据库中执行存储过程:
exec MY_PACKAGE.TABLE1_DELETE(参数1,参数2,,参数n);
11 给表增加字段:
alter table 表名 add (字段名 number(7,2));
--添加或修改表或字段注释
comment on column 表名.字段名 is '字段注释';
COMMENT ON TABLE 表名 IS '表注释';
--添加或修改表或字段注释
comment on column 表名.字段名 is '字段注释';
COMMENT ON TABLE 表名 IS '表注释';
12查询某一方案下的所有对象:
(object_type='TABLE'查所有的表对象)
select owner , object_name ,object_type from all_objects where OWNER = '用户名' and object_type='TABLE'
select owner , object_name ,object_type from all_objects where OWNER = '用户名' and object_type='TABLE'
13:在同一数据库复制表a,创建成表d:
select * into d from a
14:插入有日期类型的进行日期格式化:
--field1为DateTime数据类型
INSERT INTO lqshduserinfo(field1) VALUES(To_date(field1,'YYYY-MM-DD HH24:MI:SS '))
INSERT INTO lqshduserinfo(field1) VALUES(To_date(field1,'YYYY-MM-DD HH24:MI:SS '))