oracle SQl 总结

 

 

 oracle SQl 总结

 

1、查询数据表中某个字段重复出现两次以上的数据

 

 

select * from zf_fundinorderpayt where MAINORDERSYSTEMNO in(select MAINORDERSYSTEMNO from zf_fundinorderpayt group by MAINORDERSYSTEMNO having count(MAINORDERSYSTEMNO)>=2)  

 

2、删除表主键

 

ALTERTABLE 表名 drop CONSTRAINT PK_表名

 

 

3、为已经存在的表增加或者删除一列

 

 

 

alter table dz_cpcnsqlconfig drop CONSTRAINT PK_dz_cpcnsqlconfig

alter table dz_cpcnsqlconfig add StatementSystemType NUMBER(2) default 10 not null; 
alter table dz_cpcnsqlconfig drop column StatementMiddleInnerStatus
alter table dz_cpcnsqlconfig add StatementResultInnerStatus NUMBER(2)

alter table dz_cpcnsqlconfig add StatementMiddleInnerStatusType NUMBER(2)
alter table dz_cpcnsqlconfig add StatementMiddleInnerStatus NUMBER(2)

4、为表和表字段增加注释

 

 

添加表注释：
COMMENT ON table t1 IS '个人信息';

添加字段注释：
comment on column t1.id is 'id';
comment on column t1.nameis '姓名';
comment on column t1.age is '年龄';

comment on column dz_cpcnsqlconfig.StatementMiddleInnerStatusType is '对账中间结果类型,10=来源多账 15=目标多账 20=状态不一致 30=金额不一致';

comment on column dz_cpcnsqlconfig.StatementSystemType is '10=系统内对账 20=系统间对账'

 

5. 修改/增加一列属性

 

 

ALTER TABLE ZF_FundInOrderPayt MODIFY PartitionCode NULL;
ALTER TABLE ZF_FundInBGateOrderSub MODIFY PartitionCode NULL;

ALTER TABLE dz_bankstatementresult ADD BankFlatCount   NUMBER(8)   DEFAULT 0 NOT NULL 

 

 

 

 

 

 

 

6. PLSQL 更改表数据   

for update 是锁整个表 一般不推荐使用

 

select t.* , t.rowid from DZ_BankStatementResult t

select * fom tablename for update 

 

7.重命名表字段

 

 

说明：alter table 表名 rename  column  列名 to 新列名   （其中：column是关键字）

   例：alter table sf_InvoiceApply rename column PIC to NEWPIC;

 

 

 

8. 主键和唯一索引的区别

 

1）主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键；
2）主键不允许为空值，唯一索引列允许空值；
3）一个表只能有一个主键，但是可以有多个唯一索引；
4）主键可以被其他表引用为外键，唯一索引列不可以；
5）主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本质的差别

 

9. 给表增加外键

语法：
alter table 表1-表名
add constraint 外键名称(一般外键名称为”fK_”开头) foreign key (要设为外键的列名)
references 表2-表名(与哪个表有关联) (表2中该列列名);

栗子：

 

ALTER TABLE DZ_BankStmtDataTransferTx  ADD CONSTRAINT FK_EDataTransferCfgSystemNo FOREIGN KEY (EDataTransferCfgSystemNo) REFERENCES DZ_BankStmtDataTransferConfig(SystemNo);

 

10. 查找某个字段出现两次或两次以上的数据

select accountid ,count(*) from KJ_AccountingItem  group by(accountid)  having count(*)>1

 

11. 查找某个字段为空的数据

 

select count(*) from kj_accountingitem where accountid is null

 

12.查找在一个表中存在在另外一个表中不存在的数据

方法1：需要两个表的字段完全一致

select * from t1
where id not in(select id from t2)

 

方法2：

select * from t1
where id not in(select id from t2)

 

方法3：

 

select * from t1
where id not in(select id from t2)

 

方法4：需要t2.id不能为空

select * from t1
where id not in(select id from t2)

 

13.如果有数据在某个时间被删除了oracle有一份快照，怎么获取被删除了的数据，并且重新插回来呢？

SELECT * FROM Qy_datasourceconfig AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '19' Hour) WHERE ....e

 

insert　into qy_datasourceconfig (SELECT * FROM Qy_datasourceconfig AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '19' Hour)where datasourceid not in('ods','genonehis','fwdg','genone') )

 

14.Sql语句 in 转换为 exists

select *
  from RMS_TRANS_PTN_IP_CROSSCONNECT  
 where RELATED_NE_ID in
       (select int_id from RMS_TRANS_ELEMENT where RELATED_EMS in (216337))

转换成exists:

select *
  from RMS_TRANS_PTN_IP_CROSSCONNECT t1
 where exists (select 1
          from RMS_TRANS_ELEMENT t2
         where t1.RELATED_NE_ID = t2.int_id
           and t2.RELATED_EMS = 216337)

 

15 、oracle分页查询：

SELECT * FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM ZF_FundTransOrderPayt) A  
WHERE ROWNUM <= 21 
)  
WHERE RN >= 17

 

16 、查看oracle数据库最大连接数

select count(*) from v$process; --当前的连接数

select value from v$parameter where name ='processes'; --数据库允许的最大连接数

 

17 、外键使用异常：

 

truncatetable  ep_point

错误报告:

SQL错误: ORA-02266: 表中的唯一/主键被启用的外键引用

02266.00000 -  "unique/primary keys in table referenced by enabled foreignkeys"

*Cause:   An attempt was made to truncate a table with unique or

          primary keys referenced by foreign keys enabled in another table.

          Other operations not allowed are dropping/truncating a partition of a

          partitioned table or an ALTER TABLE EXCHANGE PARTITION.

*Action:  Before performing the above operations the table, disable the

          foreign key constraints in other tables. You can see what

          constraints are referencing a table by issuing the following

          command:

          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

”。

而改成delete则删除成功。

网上给出的解释是ddl与dml的区别（because truncate isn't going to verify the constraint, truncate is ddl.）。

解决办法：

SQL> alter table ep_pointdisable primarykey cascade;          

表已更改。

SQL>truncate table ep_point;

表已截掉。

SQL> alter table ep_point enable primary key;

 

表已更改。

SQL>ALTER TABLE '子表' ENABLE CONSTRAINT '外键约束名';

*特别注意的是在ENABLE主键后不会自动恢复外键（没有cascade选项），因此需要手工对引用该键的约束进行ENABLE。

由于此解决方法会disable掉关联子表的外键，所以慎用。

18 、使用 insert into  select 循环插入

 

declare
  i         int := 3000000;
  tmpamount int;
  ysystemno varchar2(32);
  bankno    varchar2(32);
  ycontextid varchar2(32);
Begin
  while i <= 6000000 loop
    tmpamount := abs(mod(dbms_random.random, 20)) * 100 + 30;
    ysystemno := '4011' || to_char(sysdate, 'yyyymmddhh24missSSS') || '0' || i;
    bankno    := '1857' || to_char(trunc(dbms_random.value(10000, 11000))) || i;
    ycontextid:= '3511' || to_char(sysdate, 'yyyymmddhh24missSSS') ||i;
  
    insert into yh_gathering
      (systemno,
       systemtime,
       rootid,
       sourcetxsn,
       contextid,
       sourcetxtime,
       status,
       bankgatewaytime,
       responsecode,
       responsemessage)
      select ysystemno systemno,
             to_char(sysdate, 'yyyymmddhh24missSSS') systemtime,
             '2211' || to_char(sysdate, 'yyyymmddhh24missSSS') ||
             to_char(trunc(dbms_random.value(10000, 11000))) rootid,
             to_char(sysdate, 'yymmddhh24missSSS') ||
             to_char(trunc(dbms_random.value(10000, 11000))) sourcetxsn,
             ycontextid  contextid,
             to_char(sysdate, 'yyyymmddhh24missSSS') sourcetime,
             30 status,
             to_char(sysdate, 'yyyymmddhh24missSSS') bankgatewaytime,
             '2000' responsecode,
             '交易成功' responsemessage
        from dual
      connect by rownum <= 1;
    insert into yh_gatheringbankinfo
      (systemno,
       banksystemno,
       sendtime,
       querycount,
       bankresponsetime,
       bankresponsecode,
       bankresponsemessage,
       financechannelcode,
       financeinstcode,
       financesystemcode,
       gatheringway)
      select ysystemno systemno,
             bankno banksystemno,
             to_char(sysdate, 'yyyymmddhh24missSSS') sendtime,
             0 querycount,
             to_char(sysdate, 'yyyymmddhh24missSSS') bankresponsetime,
             '00042' bankresponsecode,
             'chenggong????' bankresponsemessage,
             '911_10210012000000011000012000110' financechannelcode,
             '911' financeinstcode,
             '9111201201' financesystemcode,
             11 gatheringway
        from dual;
    insert into yh_gatheringcustomerinfo
      (systemno,
       txtype,
       institutionid,
       amount,
       accounttype,
       cardtype,
       issuebankid,
       bankaccountname,
       bankaccountnumber,
       branchname,
       province,
       city,
       identificationtype,
       identificationnumber)
      select ysystemno systemno,
             '2011' txtype,
             '000020' institutionid,
             tmpamount amount,
             '11' accounttype,
             '10' cardtype,
             '102' issuebankid,
             'C46611D158B0AEB4A9C7C87A47502E57' bankaccountname,
             '64FF1D295F5AE944854112CFC2FE9507394E30B53BDC46A4' bankaccountnumber,
             '北京市宣武支行' branchname,
             '北京' province,
             '北京' city,
             '0' identificationtype,
             'D2FC2266EA432208930193BD70D2C12B3D8C2279F125DB44' identificationnumber
        from dual;
    i := i + 1;
  end loop;
  commit;
end;

存储过程

-- 建表
CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `score` int(11) NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `name_score` (`name`(191),`score`),
  KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 存储过程插入数据

CREATE PROCEDURE insert_person()
begin
    declare c_id integer default 1;
    while c_id<=100000 do
    insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
    set c_id=c_id+1;
    end while;
end

-- 执行计划
EXPLAIN SELECT COUNT(*) FROM person

MySQL 优化器会选择成本最小的辅助索引进行计数

 

 

19、查询一个表中某个字段不为空的数据

 

select * from   表名  where id  is  not  null;

20、数据导入导出

expdp roommanage_hb/roommanage_hb directory=DATA_PUMP_DIR dumpfile=dev20180326.dmp  schemas=roommanage_hb

impdp roommanage_sx/roommanage_sx directory=DATA_PUMP_DIR dumpfile=dev20180326.dmp remap_schema=roommanage_hb:roommanage_sx TRANSFORM=segment_attributes:n table_exists_action=replace

 

21、管理查询是否失效的问题

 

select * from
td
  left join (
select case_id as sup_case_id , count(*) supervise_number from 
 td_kcdc_case_sup_info 
 group by  case_id
  ) sup
 on  
   sup.sup_case_id = td.case_id
 where 1=1   /*不能去掉， 否则认为and 后的条件为 联合查询时的条件， 不能起过滤作用，由于left join因此td表中记录将全部查出来*/

 and td.con = 'xxxx'   

1. 对于left join，不管on后面跟什么条件，左表的数据全部查出来，因此要想过滤需把条件放到where后面

2. 对于inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用。也可以把条件放到where后面。

 

21.SQL语句中SUM函数返回NULL的解决办法

 

SELECT COALESCE(SUM(name),0) FROM person WHERE id > 0
 
 
 
行了，这下就不用费事去处理返回结果是否为NULL的情况了。
 
COALESCE 函数的意思是返回参数列表中第一个为空的值，该方法允许传入多个参数，该函数也是SQL中的标准函数。

 

 CASE  WHEN 语句

SELECT  SystemNo,FeeRuleSystemNo,MinBound,CASE MaxBound
         WHEN -1 THEN
          0
         ELSE
          MaxBound  END AS MaxBound  ,FixedCharge,FeeRate,FeeRateUnit FROM  CP_CreditFeeRuleItem

 

查询离某个时间点最近的一笔 交易，并汇总

WITH tabs AS
 (SELECT AccountNumber,
         TXTIME,
         Balance,
         ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY TXTIME DESC) rank
    FROM Paymentaccounttx
   WHERE TXTIME < '20190701000000')
SELECT SUM(Balance)
  FROM tabs t1
  LEFT JOIN InstitutionPaymentAccount t2
    on t1.AccountNumber = t2.paymentaccountnumber
 WHERE rank = 1
   AND t2.usage = 10

 

 

ORA-01810格式代码出现两次 的解决方案

 

正确的格式应该是这样的,用MI来代表分钟。

SQL代码 ：

TO_CHAR('2011-09-01 00:00:00','YYYY-MM-DD HH:MI:SS') 

对于24小时制,HH支持的不好,改成一下格式。

SQL代码 ：

TO_CHAR('2011-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS') 

update 语句更新date, timestamp字段的格式

UPDATE Order
   SET BANKNOTIFICATIONTIME = TO_TIMESTAMP('2019-8-31 15:12:00','yyyy-mm-dd hh24:mi:ss')
 WHERE Collected = 0
   AND (TxType = '1111' OR TxType = '1112')
   AND SplitType = 10
   AND Status = 20
   AND ConfirmRequired = 0

 

删除库中不在列表TEMPLATECHANNEL中的表

select 'drop table ' || table_name || ';' from tabs where table_name not in ('TEMPLATECHANNEL')