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
浙公网安备 33010602011771号