Oracle基本的SQL语句

1.最基本的增删改查

1.1.新增 insert

1.1.1.单表新增

INSERT INTO table_count_output (data_date,table_name,table_count
) VALUES ('2023-03-15','FMCUSLVL',351
);COMMIT;

1.1.2.关联新增

INSERT INTO table_count_output (data_date,table_name,table_count
)SELECTdata_date,table_name,table_countFROMtable_count_output;COMMIT;

注意：
（1）SQL1中有VALUES 关键字，SQL2中没有。
（2）执行完SQL记得提交，否则会锁表。

1.2.修改 update

1.2.1.单表更新

UPDATE table_count_outputSETtable_name = 'FMCUSLVL',table_count = 2WHEREid = 1;COMMIT;

1.2.2.多表关联更新

MERGE INTO target t
USING source s ON ( t.id = s.aid )  --用source去更新target
WHEN MATCHED THEN UPDATE			--如果source中的数据在target中存在，则更新SET t.year = s.year
WHEN NOT MATCHED THEN  			    --如果source中的数据在target中不存在，则新增
INSERT (t.id,t.name,t.year )
VALUES( s.aid,s.name,s.year );COMMIT;

1.3.删除 delete

1.3.1.删除

DELETE FROM table_count_outputWHEREid = 1;COMMIT;

优点：可以精确的指定行删除
缺点：删除慢

1.3.2.截断

TRUNCATE TABLE table_count_output;

优点：删除快
缺点：只能清空表，无法精确的行级删除

1.3.复制表

（1）复制表结构和数据

CREATE TABLE table_count_output_copASSELECT*FROMtable_count_output;

（2）只复制表结构

CREATE TABLE table_count_output_copASSELECT*FROMtable_count_outputWHERE1 = 2;

1.4.查询

1.4.1.条件查询

SELECT*FROMtable_count_outputWHEREdata_date = DATE '2023-03-15';

1.4.2.统计数据量

SELECTCOUNT(1)FROMtable_count_outputWHEREdata_date = DATE '2023-03-15';

1.4.3.去重后统计数据量

SELECTCOUNT(DISTINCT table_name)FROMtable_count_output;

1.4.4.查询字符数与字节数

SELECTtable_name,             --原字段length(table_name),     --字符数lengthb(table_name)     --字节数FROMtable_count_output;

1.4.5.分组函数，查询某个字段是否重复

SELECTtable_name,COUNT(1)FROMtable_count_outputWHEREdata_date = DATE '2023-03-15'GROUP BYtable_name
HAVINGCOUNT(1) > 1;

1.4.6.查询交易表中每个账户最新的一条交易记录

SELECT*FROM( SELECTt.ntransaccountid,    --账号IDdtexecute,            --交易日期stransno,             --交易号ROW_NUMBER() OVER(PARTITION BY ntransaccountidORDER BYdtexecute DESC, stransno DESC) row_noFROMsett_transaccountdetail t)WHERErow_no = 1;

1.4.7.注意

Oracle中的null既不属于in（...），也不属于not in（...）

3.SQL规范

3.1.建议用（+）代替 LEFT JOIN，RIGHT JOIN，INNER JOIN

SELECT*FROMsource.sett_account      aLEFT JOIN source.sett_subaccount   b ON a.id = b.naccountidWHEREa.id < 500;--可以替换为：    
SELECT*FROMsource.sett_account      a,source.sett_subaccount   bWHEREa.id = b.naccountid (+)AND a.id < 500;

3.2.不要在select字段里面写查询语句

SELECTa.id,a.saccountno,(select 1 from dual)FROMsource.sett_account a;
--可替换为
SELECTa.id,a.saccountnoFROMsource.sett_account a,(select 1 from dual) b;
--因为表B只有一条数据，不需要加任何关联条件

3.3.查询条件中不建议写in

SELECT*FROMsett_account aWHEREid IN ( SELECTnaccountidFROMsett_subaccount);
--可以替换为：          SELECT*FROMsett_account      a,sett_subaccount   bWHEREa.id = b.naccountid (+)AND b.naccountid IS NULL; 
--或者       SELECT*FROMsett_account aWHEREEXISTS ( SELECT1FROMsett_subaccount bWHEREa.id = b.naccountid);

4.SQL优化

4.1.建索引

4.1.1.联合索引的特点

索引有序+高度较低+存储列值

4.1.2.联合索引的好处

避免回表。两个单列查询返回行较多，同时查返回行较少，联合索引更高效。

4.1.3.什么时候该用联合索引以及如何设计组合索引更高效

（1）等值查询中，查询条件a返回的条目比较多，查询条件b返回的条目比较多，而同时查询a、b返回的条目比较少，那么适合建立联合索引；
（2）对于有等值查询的列和范围查询的列，等值查询的列建在前、范围查询的列建在后比较实用；
（3）如果联合索引列的前置列与索引单列一致，那么单列查询可以用到索引，这样就避免了再建单列索引，因此联合索引的前置列应尽量与单列一致；

4.1.4.使用索引需要注意的地方

（1）超过3个列的联合索引不合适，否则虽然减少了回表动作，但索引块过多，查询时就要遍历更多的索引块了；
（2）建索引动作应谨慎，因为建索引的过程会产生锁，不是行级锁，而是锁住整个表，任何该表的DML操作都将被阻止，在生产环境中的繁忙时段建索引是一件非常危险的事情；
（3）对于某段时间内，海量数据表有频繁的更新，这时可以先删除索引，插入数据，再重新建立索引来达到高效的目的。

4.1.5.另外有些情况不适合建索引

（1）很少参与查询的列。
（2）对于增、删、改操作远大于查询的列。
（3）对于很少数据值的列，例如性别。
（4）对于那些结果集占了表数据总量很大比例的查询。
（5）对于备注、文本框等长度很大的列。

5.查询锁表并解锁

--查询被锁的表
SELECTl.session_id sid,s.serial#,l.locked_mode,l.oracle_username,s.user#,l.os_user_name,s.machine,s.terminal,a.sql_text,a.actionFROMv$sqlarea         a,v$session         s,v$locked_object   lWHEREl.session_id = s.sidAND s.prev_sql_addr = a.addressORDER BYsid,s.serial#;--解锁
ALTER SYSTEM KILL SESSION 'sid,serial#' immediate;

6.数据泵导入与导出

6.1.创建表空间

CREATE TABLESPACE idms DATAFILE '/opt/oracle/oradata/smcw/idms.dbf' SIZE 2048M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT;

6.2.创建用户

CREATE USER idms PROFILE DEFAULT IDENTIFIED BY idms DEFAULT TABLESPACE idms TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;

6.3.用户授权

1.grant resource,connect,dba to idms;
2.grant read,write on directory IDMS_DIR to idms;

6.4.导入

1.impdp idms2/idms2@10.0.11.85:1521/ora19c directory=IDMS_DIR DUMPFILE=idms_20230419.dmp REMAP_SCHEMA=idms:idms2 remap_tablespace=idms:idms2 TABLE_EXISTS_ACTION=REPLACE
2.imp target/target@10.10.2.51:1521/db file='target_20230302.dmp' fromuser='target' touser='target' ignore=y;

6.5.导出

--expdp导出
expdp idms/idms@10.0.11.85:1521/ora19c schemas=idms directory=IDMS_DIR dumpfile=idms_20230419.dmp logfile=idms_20230419.log;
--exp导出表结构和数据
exp target/target@10.10.2.51:1521/db file=E:\target_20230510.dmp owner=('target');
--exp只导出表结构 rows=n
exp target/target@10.10.2.51:1521/db file=E:\target_20230510.dmp owner=('target') rows=n;