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;