oracle常用总结

replace

replace（'将要更改的字符串','被替换掉的字符串','替换字符串'）

--几个常用的chr()函数:
chr(9) --制表符
chr(10) --换行符
chr(13) --回车符
chr(32) --空格符
chr(34) --双引号“"”

replace('111<br/>333444',chr(13)||chr(10), ' ')

merge into

根据源表对目标表进行匹配查询，匹配成功时更新，不成功时插入

MERGE INTO 目标表 a
USING 源表 b
   ON (a.字段1 = b.字段2 and a.字段n = b.字段n)
 WHEN MATCHED THEN 
      UPDATE SET a.新字段 = b.字段 
       WHERE 限制条件
 WHEN NOT MATCHED THEN
      INSERT (a.字段名1，a.字段名n) VALUES(b.字段值1, b.字段值n) 
       WHERE 限制条件

execute immediate

create or replace procedure proc_test(
--参数区域
)
is 
--变量区域
    --sql脚本
    v_sql varchar2(2000) :='';
    --记录学生数量
    v_num number;
begin
--执行区域

    -- execute immediate用法1：立刻执行sql语句
    v_sql := 'create or replace view myview as select id,name from student';
    execute immediate v_sql;
    
    --- execute immediate用法2：立刻执行sql语句，并赋值给某个变量
    v_sql := 'select count(1) from student';
    execute immediate v_sql into v_num;
    
    -- execute immediate用法3：带参数的sql
    v_sql:='select * from student t where t.name=:1 and t.age=:2'; 
    execute immediate v_sql using 'ZhangSan',23;
    
end proc_test;
/

select into

赋值

 v_id NUMBER(12);
 
 --这里将select的结果赋值给v_id
 select id
 into v_id
 from tb1 where name='apple'

having

--HAVING子句过滤由GROUP BY子句返回的行分组
SELECT
    c1, 
    sum(c2) as c2_sum,
    count(c3) as c3_count
FROM
    T
GROUP BY
    c1
HAVING
    sum(c2)>100   --这里用聚合函数sum(c2)不能直接用别名c2_sum
ORDER BY
    order_value DESC;

oracle函数to_number,tochar转mysql函数date_format,cast

--mysql
cast(date_format(DATE_SUB(curdate(),INTERVAL 1 DAY),'%Y%m%d') as DECIMAL)
--oracle
TO_NUMBER(TO_CHAR(SYSDATE-1,''YYYYMMDD'')) 

oracle建表导数据

--建表
create table tb1(
    id number,
    name varchar2(20),
    birthday date,
    address varchar2(256)
);

--表名
comment on table tb1 is '测试表';

--字段注释
comment on column tb1.id is '学号';
comment on column tb1.name is '姓名';
comment on column tb1.ibirthdayd is '出生日期';
comment on column tb1.address is '家庭住址';

--手动导入数据
-- 表名-右键-编辑数据(可能要打开锁)-excel准备好数据(加上空的第一列)-粘贴进去-发布改变-提交事务

oracle复制表结构

--复制表结构
create table tb2 as select * from  tb1 where 1=0;
--添加字段
alter table tb1 add(colname coltype);
--

oracle重命名表

--将表tb1重命名为tb2
rename tb1 to tb2;
--插入数据
insert into tb2(c1,c2,c3)
select c1,c2,c3
from tb1

oracle删除重复数据

--第一步: 创建新表,并筛选出不重复数据写入新表
create table tableNew as  select * from tableOld where 1=0;
insert into  tableNew(LATN_ID,ACTIVITY_ID,ORDER_ID)
select 
   A.LATN_ID,
   A.ACTIVITY_ID,
   A.ORDER_ID
from
(select LATN_ID,ACTIVITY_ID,ORDER_ID,row_number() over(partition by LATN_ID,XXX  order by terminal_type,XXX desc)rank from tableOld where p_day=20210614)A where A.rank=1;
    -- partition by 是要判断重复的字段
    --order by 是排序,排序是为了对重复数据筛选:比如降序取第一个
    
--第二步:删除源表数据,将新表数据重新写回源表 
delete from tableOld where p_day=20210614;commit;
insert into  tableOld  select * from table1;commit;

查看所有表,建表语句

--查所有表,相当于show tables;
select * from user_tables;

--查看建表语句,相当于show create table
select dbms_metadata.get_ddl('TABLE','TICKET_INTERFACE_ERROR_LOG') from dual;

navivate连接oracle

(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1621)) (ADDRESS = (PROTOCOL = TCP)(HOST = 132.252.135.13)(PORT = 1621)) (LOAD_BALANCE = off))(CONNECT_DATA =(SERVICE_NAME = sjztdb1_sjzt)(SERVER = DEDICATED)))

其他

oracle,mysql中字符串，date，timestamp转换

https://blog.csdn.net/zhangfeng1742/article/details/78130247

多种window窗口

• PW（程序窗口）: 可以执行 sql,sqlplus 相关的语句，例如存储过程，方法，一般用来开发程序用的。 TW（测试窗口）: 一般是用来测试存储过程等的debug。
• SW（SQL窗口）: 执行的是dml,ddl语句,主要用户语句的查询、显示、执行统计信息等（应用最多的一个窗口）。
• 例如 desc table不能在SQL window中执行，必须在Command window中才能执行。
• RW（报告窗口）: 方便用于展示有聚合查询的用图表形式展示的窗口，例如sum(),count()等，有x,y轴的。
• CW（命令窗口）: 除了可以执行sql/sqlplus 相关的命令、sql脚本，还可以执行更多的命令，例如call 等。
• EPW（解释计划窗口）: 解释执行计划的，调优时，经常用到。
• DW（图表窗口）: 画图表的，如果后面用到，补充具体使用细节。

limit

在oracle中无limit, 用 where rownum<3 替代

select * from ZHYX.ACTIVITY_CUST_GROUP where rownum<=3;

update

把a表更新b表的数据

--把a表的score按b表相同id的score更新
update user1 a set a.score=(select b.score from user2 b where a.id=b.id and rownum<2) where exists(select 1 from user2 b where a.id=b.id)
--注意点1: 加上where exists
--注意点2: 报错 单列子查询返回多个行;
--可能原因: ①子查询表中数据本身就有重复行②表没有重复行,但是where中较少的关联条件导致查询重复行
--解决: 提高where条件精度;加上rownum<2