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
)
    SELECT
        data_date,
        table_name,
        table_count
      FROM
        table_count_output;

COMMIT;

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

1.2.修改 update

1.2.1.单表更新

UPDATE table_count_output
   SET
    table_name = 'FMCUSLVL',
    table_count = 2
 WHERE
    id = 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_output
 WHERE
    id = 1;

COMMIT;

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

1.3.2.截断

TRUNCATE TABLE table_count_output;

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

1.3.复制表

（1）复制表结构和数据

CREATE TABLE table_count_output_cop
    AS
        SELECT
            *
          FROM
            table_count_output;

（2）只复制表结构

CREATE TABLE table_count_output_cop
    AS
        SELECT
            *
          FROM
            table_count_output
         WHERE
            1 = 2;

1.4.查询

1.4.1.条件查询

SELECT
    *
  FROM
    table_count_output
 WHERE
    data_date = DATE '2023-03-15';

1.4.2.统计数据量

SELECT
    COUNT(1)
  FROM
    table_count_output
 WHERE
    data_date = DATE '2023-03-15';

1.4.3.去重后统计数据量

SELECT
    COUNT(DISTINCT table_name)
  FROM
    table_count_output;

1.4.4.查询字符数与字节数

SELECT
    table_name,             --原字段
    length(table_name),     --字符数
    lengthb(table_name)     --字节数
  FROM
    table_count_output;

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

SELECT
    table_name,
    COUNT(1)
  FROM
    table_count_output
 WHERE
    data_date = DATE '2023-03-15'
 GROUP BY
    table_name
HAVING
    COUNT(1) > 1;

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

SELECT
    *
  FROM
    ( SELECT
          t.ntransaccountid,    --账号ID
          dtexecute,            --交易日期
          stransno,             --交易号
          ROW_NUMBER() OVER(
              PARTITION BY ntransaccountid
               ORDER BY
                  dtexecute DESC, stransno DESC
          ) row_no
        FROM
          sett_transaccountdetail t
    )
 WHERE
    row_no = 1;

1.4.7.注意

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

3.SQL规范

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

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

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

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

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

SELECT
    *
  FROM
    sett_account a
 WHERE
    id IN ( SELECT
                naccountid
              FROM
                sett_subaccount
          );
--可以替换为：          

SELECT
    *
  FROM
    sett_account      a,
    sett_subaccount   b
 WHERE
    a.id = b.naccountid (+)
       AND b.naccountid IS NULL; 
--或者       

SELECT
    *
  FROM
    sett_account a
 WHERE
    EXISTS ( SELECT
                 1
               FROM
                 sett_subaccount b
              WHERE
                 a.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.查询锁表并解锁

--查询被锁的表
SELECT
    l.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.action
  FROM
    v$sqlarea         a,
    v$session         s,
    v$locked_object   l
 WHERE
    l.session_id = s.sid
       AND s.prev_sql_addr = a.address
 ORDER BY
    sid,
    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;