Oracle数据库工作中常用sql语法

1、给表格中的字段去除空格： 例如：表： T_SCZC_ARC_ELECTRIC_CUSTOM update T_SCZC_ARC_ELECTRIC_CUSTOM set areaid= trim(areaid);
2、排序且解锁 select * from T_SCZC_ELECTRIC_CUSTOM order by to_number(substr(customid,2)) for update
3、从T_SCZC_ARC_ELECTRIC_DLSS 表插到T_SCZC_ELECTRIC_DLSS 表： insert into T_SCZC_ELECTRIC_DLSS (CUSTOMID,CUSTOMNAME,DYDJ,ZYDRL,BYQRL,GDPNH,NYDL,JSDJ,YWQF,ZBDY,ZJRL,NFDL,FDCB,USERID,DEFINETIME) (select CUSTOMID,CUSTOMNAME,DYDJ,ZYDRL,BYQRL,GDPNH,NYDL,JSDJ,YWQF,ZBDY,ZJRL,NFDL,FDCB,BIDUSER,BIDTIME from T_SCZC_ARC_ELECTRIC_DLSS )
4、把？去除： update tmda_ywsj_jjc_lxaqjc_month t set t.jcnr = replace(t.jcnr, ‘?’, ‘’)
5、提交把T_SCZC_ARC_FDDY_UNIT表中数据插到T_BAS_FDDY字段含义相同的表中： insert into T_BAS_FDDY(FDDYID,FDDYNAME,PLANTID,UNITID,LOGTIME) " (select FDDYID,FDDYNAME,PLANTID,UNITID,sysdate from T_SCZC_ARC_FDDY_UNIT) ";
6、模糊查询出有jshh中带有“ ，”且排序： select * from T_BAS_ELECTRIC_CUSTOM where jshh like ‘%,%’ order by to_number(substr(customid,2))
7、从T_SCZC_ARC_ELECTRIC_CUSTOM_TMP 表插入T_SCZC_ARC_ELECTRIC_CUSTOM 表中需要修改的字段update T_SCZC_ARC_ELECTRIC_CUSTOM b set b.JSHH=(select JSHH from T_SCZC_ARC_ELECTRIC_CUSTOM_TMP a where a.customid=b.customid),b.BIDUSER=(select BIDUSER from T_SCZC_ARC_ELECTRIC_CUSTOM_TMP a where a.customid=b.customid)
8、去除重复的数： select distinct customid from T_SCZC_ARC_DYHH where hh in (:ids)) and bidsign=0
9、按行业分类统计个数 select hyxz,count() companyCount from T_BAS_ELECTRIC_CUSTOM group by hyxz
10.查询出发电企业所属的发电集团每个发电集团下面有多少个发电企业并关联出发电集团的名字 select sum(a.SUMCAPACITY), a.POWERCOID, count() companyCount,b.POWERCONAME from T_BAS_POWERPLANT a,T_BAS_POWERCO_GENERATE b where a.POWERCOID=b.POWERCOID group by a.POWERCOID,b.POWERCONAME

11、查询一个结算户号对应多个企业的情况select jshh from T_BAS_ELECTRIC_CUSTOM group by jshh having count() >1
12、按类型查询出相应数据，由时间降序排序并取出前五条信息 select * from (select * from T_XXFB_TZGGXX t where t.infotype = ? order by t.issuetime desc ) where rownum<=5
13、核对T_BAS_ELECTRIC_CUSTOM中所有相同的SELECT * FROM T_BAS_ELECTRIC_CUSTOM A WHERE ((SELECT COUNT() FROM T_BAS_ELECTRIC_CUSTOM WHERE CUSTOMNAME = A.CUSTOMNAME) > 1) ORDER BY CUSTOMNAME DESC
14、powercoid加S加1200 update T_BAS_POWERCO_SUPPLY set powercoid=‘S’||(powercoid+1200)
15、截取id数字前的标识进行插入如：S123截取成123进行插入insert into T_SCZC_BODY_ROLL (ERID,FlAG,ENAME,AREAID,LOGTIME,LOGUSER) (select substr(CUSTOMID,2) ,‘2’,CUSTOMNAME,AREAID, to_date(‘21-03-2016 03:15:57’, ‘dd-mm-yyyy hh24:mi:ss’), ‘anping’ from T_SCZC_ELECTRIC_CUSTOM )
16、想要删除 "T_BAS_ELECTRIC_CUSTOM " 表中的 “customname” 列。alter table T_BAS_ELECTRIC_CUSTOM drop column customname
17、在 "T_BAS_ELECTRIC_CUSTOM " 表中添加一个名为 “DateOfBirth” 的列ALTER TABLE T_BAS_ELECTRIC_CUSTOM ADD DateOfBirth date
18、改变 “Persons” 表中 “DateOfBirth” 列的数据类型ALTER TABLE Persons ALTER COLUMN DateOfBirth year
19、清字空数据库表中的一个段（清除T_BAS_ELECTRIC_CUSTOM 中的 zccustomid 字段）UPDATE T_BAS_ELECTRIC_CUSTOM SET zccustomid = ‘’;
20、按时间查询数据SELECT * FROM TSECURITY_USER_TMPWHERE shtime<TO_DATE( ‘2016-07-13 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)AND shtime>TO_DATE(‘2016-07-12 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’) 20 、导入导出数据库
导入imp dljy/dljy@DLJY_192.168.1.15 file=E:\software\app\Administrator\admin\orcl\dpdump\dljy20160204.dmp fromuser=dljy导出expdb dljy/dljy@ORCL_192.168.1.15_dljy file=E:\dljy20160401.dmp
21、恢复数据库某一时刻某一张表的记录insert into T_JYSB_GDGP_TMP_GPselect * from T_JYSB_GDGP_TMP_GP AS OF TIMESTAMP to_timestamp(‘20140917 10:00:00’,‘yyyymmdd hh24:mi:ss’);
select * from T_BAS_FDDY as of timestamp to_timestamp(‘20160306 12:00:00’, ‘yyyymmdd HH:mi:ss’)
NVL( string1, replace_with)如果string1为NULL，则NVL函数返回replace_with的值，否则返回原来的值。string1和replace_with必须为同一数据类型，除非显式的使用TO_CHAR函数。NVL(yanlei777, 0) 的意思是 如果 yanlei777 是NULL， 则取 0值
22、第一：//查询出所有和USERID相关的表的信息 select * from user_tab_cols where column_name =‘USERID’ or column_name =‘USER_ID’ or column_name=‘CHECKUSER’ or column_name=‘loguser’ 第二：//新建一个供电局账号临时表tmp_usermap把老的用户名更新为新的用户名能和临时表tmp_usermap中的旧的userid匹配上的更换成新的userId update T_MOBILE_CODE_DATE t set t.userid= (select newuserid from tmp_usermap t1 where t1.olduserid = t.userid) where exists (select 1 from tmp_usermap t1 where t1.olduserid = t.userid);
23、查询某个字段长度select frdm from t_sczc_arc_electric_custom where length(frdm) <1924、多个参数以逗号隔开查询（统计用电企业信息时用）SELECT T1.ZCBH AS 注册编号, T1.CUSTOMNAME AS 企业名称, T2.POWERCOID 供电局, T3.POWERCOID 行业, T4.AREANAME 行政区域 FROM T_BAS_ELECTRIC_CUSTOM T1,
(SELECT A.CUSTOMID, SUBSTR(LISTAGG(’,’ || C.POWERCONAME) WITHIN GROUP(ORDER BY B.POWERCOID), 2) AS POWERCOID FROM T_BAS_ELECTRIC_CUSTOM A, (SELECT DISTINCT AA.CUSTOMID, BB.POWERCOID FROM T_BAS_ELECTRIC_CUSTOM AA, T_BAS_YDHH BB WHERE AA.CUSTOMID = BB.CUSTOMID) B, T_BAS_POWERCO_SUPPLY C WHERE A.CUSTOMID = B.CUSTOMID AND B.POWERCOID = C.POWERCOID GROUP BY A.CUSTOMID) T2,
(SELECT A.CUSTOMID, SUBSTR(LISTAGG(’,’ || C.JY_TRADETYPE) WITHIN GROUP(ORDER BY A.CUSTOMID), 2) AS POWERCOID FROM T_BAS_ELECTRIC_CUSTOM A, (SELECT DISTINCT AA.CUSTOMID, BB.HYLX FROM T_BAS_ELECTRIC_CUSTOM AA, T_BAS_YDHH BB WHERE AA.CUSTOMID = BB.CUSTOMID) B, T_YX_TRADETYPE C WHERE A.CUSTOMID = B.CUSTOMID AND B.HYLX = C.YX_TRADETYPE GROUP BY A.CUSTOMID) T3, T_BAS_ADMINISTRATIVE_AREA T4
WHERE T1.CUSTOMID = T2.CUSTOMID AND T1.CUSTOMID = T3.CUSTOMID AND T1.AREAID = T4.AREAID25、查询有customname 字段的表有哪些select T.TABLE_NAME from cols t where t.COLUMN_NAME = upper(‘CUSTOMNAME’);

26、查询有序列select SEQ_T_DATA_MGR.Nextval from dual;
27、存储过程位置

create or replace procedure P_HN_COMPUTE is v_sql varchar2(1000):=’’;begin
for erow in (select A.TABLE_NAME from (select T.TABLE_NAME from cols t where t.COLUMN_NAME = upper(‘CUSTOMNAME’) )A, (select T.TABLE_NAME from cols t where t.COLUMN_NAME = upper(‘customid’) )B where A.TABLE_NAME=B.TABLE_NAME)loop v_sql:='update ‘||erow.TABLE_NAME||’ a set a.customname= '|| ‘(select b.customname from t_bas_electric_custom b where b.customid=a.customid) where a.customid in (select customid from t_bas_electric_custom)’;
– dbms_output.put_line(v_sql); BEGIN execute immediate v_sql; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(v_sql); end;

end loop; commit;end P_HN_COMPUTE;
把数据库导入本机步骤.note
28、oracle查询单表占用空间的大小SELECT segment_name AS TABLENAME,
BYTES B,
BYTES / 1024 KB,
BYTES / 1024 / 1024 MB
FROM user_segments
where segment_name = upper(‘tablename’);
oracle分区表知识在F5中查看执行计划的时候总是看到很多信息:range分区 执行计划中出现的：分区表，按 n1 ,n2 分区partition range single:访问单个分区partition range iterator:访问多个分区 partition range inlist: 分区键中用了in 例如： where n1 in(X1,X2) and n2=X3partition range all: 所有的分区 partition range empty: 条件在分区中不存在 （或者说是找不到数据）partition range or: 分区键中用了or 例如 where n1=X1 or n2=X2partition range subquery:partition range join-filter:partition range multi-column:hash分区可用的操作：partition hash single:partition hash iterator:partition hash inlistpartition hash allpartition hash subquerypartition hash join-filter比range少了partition range or和partition range multi-column list分区 ：partition list singlepartition list iteratorpartition list inlistpartition list allpartition list emptypartition list orpartition list subquerypartition list join-filter29、sql拼接字符串select POWERCOID, ‘发电集团测试’||rownum as POWERCONAME from T_BAS_POWERCO_GENERATE30、改写表空间名alter tablespace TEST rename to TEST1;