数据库常见的一些表操作方法函数汇总



1、查询哪些数据事重复的
select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1
删除重复数据
create table 临时表 as
select a.字段1,a.字段2,MAX(a.ROWID) dataid from 正式表 a GROUP BY a.字段1,a.字段2;

delete from 表名 a
where a.rowid !=
(
select b.dataid from 临时表 b
where a.字段1 = b.字段1 and
a.字段2 = b.字段2
);
---- ①查找重复记录:
　SELECT DRAWING,DSNO FROM EM5_PIPE_PREFAB
　　WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D
　　                                WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND
　　                                EM5_PIPE_PREFAB.DSNO=D.DSNO);
　　---- 执行上述SQL语句后就可以显示所有DRAWING和DSNO相同且重复的记录。
---- 删除重复记录:
DELETE FROM EM5_PIPE_PREFAB
 WHERE ROWID != (SELECT MAX(ROWID)
                   FROM EM5_PIPE_PREFAB D 　　
                  WHERE EM5_PIPE_PREFAB.DRAWING = D.DRAWING
                    AND 　　 EM5_PIPE_PREFAB.DSNO = D.DSNO);
---- 执行上述SQL语句后就可以?除所有DRAWING和DSNO相同且重复的记录。

--查找某个device_name下record_type的个数 
select B.Device_Name
  from TB_PERFORMANCE_KPISET A, oapapp.TB_SYS_DEVICE_INFO B
 where A.Device_Name = B.DEVICE_NO
   and B.app_id = 'D3279C58A56D4CAFA6A8A02B6B031DE9'
   and A.Rpt_Date = to_date('2013-10-07', 'yyyy-mm-dd')
   and A.Cycle = '30m'
 group by B.Device_Name
having count(distinct A.Record_Type) >= 3
-----------------------------------------------------------------------------------
2、Database link的创建和删除

--创建Database link
create database link GT
  connect to zhbb identified by zhbb
  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 74.0.111.40)(PORT = 1521)) )(CONNECT_DATA =(SERVICE_NAME = odsbptdb)) )'
--删除Database link
drop database link GT
-------------------------------------------------------------------------------------------------

3、查看实例所用的字符编码
 select userenv('language') from dual;
 SELECT * FROM V$NLS_PARAMETERS;

--------------------------------------------------------------------------------------------------
4、查看实例所用的表
select * from user_tables where table_name like '%DZYH%';

------------------------------------------------------------------------------------
5、查看表记录数
最常用的方法：
select count(*) from BF_AGT_DEP_ACCT_SAP;
也可以从user_tables表中查询：
select num_rows "COUNT(*)" ,last_analyzed,sysdate from user_tables where table_name='BF_AGT_DEP_ACCT_SAP';

6、查看本机IP
select sys_context('userenv','ip_address') from dual;

7、查看数据库实例下所对应的所有表

select table_name from all_tables a where a.owner = 'ODSDATA'

--检索具体凡人表信息，其中upper函数是将小写转换成大写
 select b.COLUMN_NAME, b.DATA_TYPE, b.DATA_LENGTH, b.NULLABLE, b.COLUMN_ID
  from ALL_TAB_COLUMNS b
 where b.OWNER = upper('odsdata')
   and b.TABLE_NAME = 'SYSM_ROLE'
-------------------------------------
8、创建同义词
create or replace synonym BF_CM_IEMP_TLR
  for ODSBDATA.BF_CM_IEMP_TLR;
--------------------------------------------
9、查询数据库中的所有索引
select * from dba_indexes where rownum<10;
注意：必须用管理员用户登录才能运行这张表的信息
--------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
--用左关联去实现新增三列的信息
 SELECT I.ACCTNO,
               I.ACTIID,
               B.INTESUM NETINTE,
               C.INTESUM TELINTE,
               D.INTESUM CALINTE
          FROM (SELECT DISTINCT ACCTNO, ACTIID FROM I_ACCTINTESUM) I
          LEFT JOIN I_ACCTINTESUM B ON I.ACCTNO = B.ACCTNO
                                   AND I.ACTIID = B.ACTIID
                                   AND B.CHANNEL = '01'
          LEFT JOIN I_ACCTINTESUM C ON I.ACCTNO = C.ACCTNO
                                   AND I.ACTIID = C.ACTIID
                                   AND C.CHANNEL = '02'
          LEFT JOIN I_ACCTINTESUM D ON I.ACCTNO = D.ACCTNO
                                   AND I.ACTIID = D.ACTIID
                                   AND D.CHANNEL = '03'

----------------------------------------------------------------------------------------------
--左关联的使用，在on后面不能少查询条件

 select a1.fckjgh as 机构号,
        b610_get_jgName(a1.fckjgh) as 机构名称,
        a2.code_desc as 币种名称,
        a1.fckkhmc as 客户名称,
        a1.fckye2 as 余额
   from b_m_kjb_ckyeb a1
   LEFT JOIN F_CM_BP_CODE_DESC a2 ON a1.fckbb = a2.code_id
                                 and a2.code_type_id = 'CMIS000010'
                                 and a1.fckrq = '20070309'
----------------------------------------------------------------------------------------------
-- 增加一个表字段
alter   table   i_sysuser   add   contralflag   varchar(1)
-- 增加一个表字段
alter   table   i_sysuser   add   contralflag   varchar(1) default '1'
--删除一个表字段
alter table   i_sysuser  drop   column contralflag
    alter table OAPAPP.TB_PERFORMANCE_KPISET drop column device_id;
--修改一个字段的默认值
alter table i_sysuser  modify  contralflag default '1';
   alter table OAPAPP.TB_SYS_DEVICE_INFO rename column order_num1 to ORDER_NUM;
--只取当前行的数据
select * from i_sysuser where rownum=1;
------------------------------------------------------------------------------------------------
--左关联用户信息,where条件的使用
select b1.usercode,b1.username,b1.orgcode,b1.status, b1.remark
  from i_sysuser b1
  left join (select a1.usercode,a1.roleid,a2.systype
               from i_sysuserrole a1, i_sysrole a2
              where a1.roleid = a2.roleid
                 ) b2 on b1.usercode = b2.usercode
 where b1.status = 0 and (b2.systype = '1' or  b2.systype is null)
-------------------------------------------------------------------------------------------------
--增加表的容量
SQL> alter table AL modify DICTIONARY_END VARCHAR2(30);

表已更改。

SQL> alter table AL modify DICTIONARY_END VARCHAR2(300);
------------------------------------------------------------------------------------------------
--拼接字符串
select to_number(10008||'01') from dual
------------------------------------------------------------------------------------------------
-- 将null转化成1
select nvl(max(ruleid),1) from I_INTELRULE
------------------------------------------------------------------------------------------------
--截取字符串，从后两位进行截取
substr(F.Actiid,1,length(F.Actiid)-2)
如：select substr(custno,1,length(custno)-2) from i_custinte where custno = 'T6100000000005894687'
------------------------------------------------------------------------------------------------
--拼接字符串用 ||
如：select custno||actiid from i_custinte  where custno = 'T6100000000005894687'
------------------------------------------------------------------------------------------------
--检索至少选择了两个科目号的学生证件号
方法一：select aa.证件号 from cj as aa ,cj as bb where aa.证件号 = bb.证件号 AND aa.科目号 <> bb.科目号
方法二：select aa.证件号 from cj as aa group by aa.科目号 having count(*)>1
-------------------------------------------------------------------------------------------------

--Oracle取余函数、取整函数和四舍五入函数
select mod(407,20) from b_s_tysh_crsresre
select floor(407/20)+1 from b_s_tysh_crsresre
select round(407/20)+1 from b_s_tysh_crsresre
------------------------------------------------------------------------------------------------------
 --获得当前时间
  select TO_CHAR (SYSDATE, 'yyyyMMdd HH24:MI:SS')  from dual;
  --获得星期天
  select TO_CHAR (SYSDATE, 'DY') from dual ;
------------------------------------------------------------------------------------------------------

10、创建一个分区表
drop table B_F_AGT_INN_CCBS_ACA_H;
create table B_F_AGT_INN_CCBS_ACA_H
(
  CCBS_ACCT_NO       VARCHAR2(28) not null,
  CUST_ACCT_NO       VARCHAR2(26) not null,
  ODS_LOGIC_DT       VARCHAR2(10),
  OPR_UNIT_CD        VARCHAR2(9),
  GL_OPAC_INSTN      VARCHAR2(9),
  BUSN_TYP           VARCHAR2(3),
  CURR_CD            VARCHAR2(3),
  GL_LG_NO           VARCHAR2(8),
  SEQ_ID             VARCHAR2(6),
  ACCT_STAT          VARCHAR2(1),
  GL_BAL_DIRE_FLG    VARCHAR2(2),
  GL_ACCT_NAME       VARCHAR2(40),
  GL_ACCT_FACE_BAL   NUMBER(20,2),
  GL_AFC_BAL         NUMBER(20,2),
  CCOUNT_THISDAY     NUMBER(20,2),
  CAMOUNT_THISDAY    NUMBER(20,2),
  GL_CRNT_DAY_DR_QTY NUMBER(12),
  GL_CRNT_DAY_CR_QTY NUMBER(12),
  CUST_OPEN_DT       VARCHAR2(10),
  GL_CLOSE_DT        VARCHAR2(10),
  ETL_DT             VARCHAR2(10)
)
partition by list (ETL_DT)
(
  partition P_20100902 values ('2010-09-02'),
  partition P_20100903 values ('2010-09-03')
);

--删除分区
alter table B_F_AGT_INN_CCBS_ACA_H drop partition P_20100902;
--增加分区
alter table B_F_AGT_INN_CCBS_ACA_H add partition P_20100902 values ('2010-09-02')

11、like用法
select * from B_F_POS_CONFLICT_RULE where '01,03,02' like '%'||postlist||'%'

12、去重操作
select *
  from ZXTD_CUST_MGR_CUST_REL_TMP2
 where rowid in (select rid
      from (select inst_no,cust_mgr_no,cust_no,
                   cert_no,chn_no,d_signdate,sign_sts,
                   active_dt,dcc_custno,cost_sum,
                   max(rowid) rid
                   from ZXTD_CUST_MGR_CUST_REL_TMP2
                   group by inst_no,cust_mgr_no,cust_no,
                            cert_no,chn_no,d_signdate,
                            sign_sts,active_dt,dcc_custno,cost_sum))
13、将统计后的数据装入现金客户交易表，如果当日交易过去未交易客户
    --插入；如果过去发生交易则更新客户交易比数
merge into BF_EVT_DEP_SAP_0 c
using BF_EVT_DEP_SAP_ZZ cc
on (cc.CUST_NO = c.CUST_NO)
when MATCHED THEN
  UPDATE SET c.CNT = C.CNT + cc.CNT, c.SA_TX_DT = cc.SA_TX_DT
when NOT MATCHED THEN
  INSERT (CUST_NO, CNT, SA_TX_DT) VALUES (cc.CUST_NO, cc.CNT, cc.SA_TX_DT)

14、第一列为null时取第二列，如果第二列为null时取第三列
CREATE TABLE test123 (
col1  NUMBER(3),
col2  NUMBER(3),
col3  NUMBER(3));

INSERT INTO test123 VALUES (1, NULL, NULL);
INSERT INTO test123 VALUES (NULL, 2, NULL);
INSERT INTO test123 VALUES (NULL, NULL, 3);
INSERT INTO test123 VALUES (1, NULL, 3);
INSERT INTO test123 VALUES (NULL, 2, 3);

SELECT * FROM test123;

SELECT COALESCE(col1, col2, col3) FROM test123;