oracle_relative

最新推荐文章于 2024-09-26 15:19:34 发布

2301_78582814

最新推荐文章于 2024-09-26 15:19:34 发布

阅读量859

点赞数 13

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/2301_78582814/article/details/135746579

版权

15.sql语句修改某个字段的部分内容：
https://huaweicloud.csdn.net/63357550d3efff3090b5790e.html?spm=1001.2101.3001.6650.8&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Eactivity-8-119418740-blog-125792791.pc_relevant_multi_platform_whitelistv4&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Eactivity-8-119418740-blog-125792791.pc_relevant_multi_platform_whitelistv4&utm_relevant_index=9

update 表名 set 字段 = replace(字段, ‘要修改的内容’ , ‘修改后的内容’);

16.Oracle删除表中的字段:
https://blog.csdn.net/xue_yanan/article/details/78205008

alter table 表名 drop column 列名称;
删除表中的一列：alter table bl_yhsz drop column zcmc;

11.group by的使用：
https://blog.csdn.net/liu_shi_jun/article/details/51329472

SELECT "work_id" FROM (SELECT "work_id",COUNT(*) AS "a" FROM NAS_ARC_ARCHIVING_T WHERE "archiving_flag"='0' GROUP BY "work_id"
ORDER BY "a" ASC) WHERE ROWNUM=1;

4.三表 left join 写法：
https://blog.csdn.net/m0_63409362/article/details/125107017

select * from A inner join 表B on 表A.列1=表B.列2 inner join 表C on 表B.列1=表C.列1

6.ORA-00904:标识符无效:
Oracle字段名区分大小写,解决方案：直接从Oracle数据库生成sql

7.java.sql.Date.valueOf处理日期格式IllegalArgumentException异常：

nasArcArchivingT.setUpdateTime(java.sql.Date.valueOf(String.valueOf((rs.getDate(16)))));
rs后的getXXX需要注意类型是否匹配

3.impala 或hive 给指定kudu库中的表添加列：
alter TABLE fact_cpu_supply_category add columns(supplier_name string COMMENT '供应商名称');

https://blog.csdn.net/zy103118/article/details/103010989?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1-103010989-blog-82684584.t0_edu_mix&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1-103010989-blog-82684584.t0_edu_mix&utm_relevant_index=1

Q:Oracle ORA-01861: 文字与格式字符串不匹配解决方法：

注注注：如下的分号也有可能需要改为逗号

A:指定日期格式：to_date('2010-01-01';
'yyyy-mm-dd')

to_date('2010-01-01 10:10:10' ;'yyyy-mm-dd hh24:mi:ss')

分区建表：
-----------------------------------

CREATE TABLE m_r_mes_panel_mat_csm_t (
product_site STRING NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
process_site STRING NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
factory STRING NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
eqp_type STRING NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
oper_code STRING NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
panel_id STRING NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
glass_id STRING NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
event_time STRING NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
event_name STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
array_glass_id STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
cf_glass_id STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
consumable_type STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
consumable_id STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
consumable_spec STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
product_kind STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
product_group STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
product_id STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
owner_type STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
owner_code STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
eqp_id STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
unit STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
qty STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
event_user_id STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
bomver STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
last_event_flag STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
steel_bottle_id STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
required_qty STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
target_consumable_name STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
cartridge_compose_id STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
freebee_flag STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
machine_recipe_id STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
experiment_no STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
update_time STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,
PRIMARY KEY (product_site, process_site, factory, eqp_type, oper_code, glass_id, event_time)
)
PARTITION BY RANGE (event_time) (
PARTITION VALUES < "2022/07/01 07:30:00",
PARTITION "2022/07/01 07:30:00" <= VALUES < "2022/08/01 07:30:00",
PARTITION "2022/08/01 07:30:00" <= VALUES < "2022/09/01 07:30:00",
PARTITION "2022/09/01 07:30:00" <= VALUES < "2022/10/01 07:30:00",
PARTITION "2022/10/01 07:30:00" <= VALUES < "2022/11/01 07:30:00",
PARTITION "2022/11/01 07:30:00" <= VALUES < "2022/12/01 07:30:00",
PARTITION "2022/12/01 07:30:00" <= VALUES < "2023/01/01 07:30:00"
)
STORED AS KUDU

2022/11/24
1.修改列名：
https://www.csdn.net/tags/MtjaEgwsODc1OTUtYmxvZwO0O0OO0O0O.html

https://blog.csdn.net/qq_40493944/article/details/120782414
alter table NAS_ARC_ARCHIVING_T rename column "archiving_flag" to "process_state";

2.删除列：
alter table NAS_ARC_ARCHIVING_T drop column "archiving_end_time";

3.DELETE 删除语句：
DELETE FROM <表名> WHERE <筛选条件>;

4.sql 中对varchar类型进行求和（sum）方法：
https://blog.csdn.net/harryptter/article/details/95532040?spm=1001.2101.3001.6650.2&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-2-95532040-blog-124603185.pc_relevant_multi_platform_whitelistv3&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-2-95532040-blog-124603185.pc_relevant_multi_platform_whitelistv3&utm_relevant_index=3

select SUM(cast(changeData as numeric(12,0))) from testTable
where departmentID='REACH & DEVELOPMENT';

5.指定要更新数据的内容:
https://blog.csdn.net/qq_27093831/article/details/116428215
UPDATE <表名> SET [字段=值,字段=值…][WHERE 更新条件]
update myemp set sal=3000,comm=500 where empno=7

6.基于子查询的更新

UPDATE <表名> SET （列1,列2,…）=(SELECT 列1,列2,…FROM <表名>) [WHERE 查询条件]
-- 将雇员7369的职位、基本工资、雇佣日期更新为与7839相同的信息
update myemp set (job,sal,hiredate) = (select job,sal,hiredate from myemp where empno=7839) where empno=7369;

7.ORA-14402:updating partition key column would cause a partition change
https://blog.csdn.net/qq_40615403/article/details/125715336
解决方法：

打开表行迁移，启用row movement特性，使用如下语句：
ALTER TABLE table_name ENABLE ROW MOVEMENT
出现这个异常是因为我们更改了分区键，对于时间组成的范围分区，
在修改前在一个分区，修改后可能会到了另一个分区，所以必须启用行移动才可以进行更改；
如果是更改的除分区键之外的字段，则不必启用行移动。

8.sql insert语句：
INSERT INTO 表名 (字段1,字段2,...) values (value1,value2,...);

INSERT INTO NAS_ARC_WORKER_T ("work_id","ip","arc_type","update_time") VALUES
('10004','172.29.128.58','Delete','2022/12/17 16:22:12')

9.查看表分区：show RANGE PARTITIONs a_r_ftp_defect_t
10.添加表分区：ALTER TABLE m_r_mes_panel_wip_t ADD RANGE PARTITION "20230301" <=VALUES <"20230401"

11.查询列后面的字段需全部放在group by 后面（除聚合函数外），order by 中列，应该出现在group by 子句中。

12.GROUP by与ORDER by一起使用，GROUP by在前：
SELECT factory,eqp_type,gop_id,date_timekey,source_path FROM d_c_nas_archiving_hist_t
GROUP by factory,eqp_type,gop_id,date_timekey,source_path ORDER by date_timekey DESC;

13.ORA-02264 创建表时报错ORA-02264：名称已被一个现有约束条件占用:
https://blog.csdn.net/qq_42216781/article/details/91039420

报错原因是在同一个表内，有两个外键约束条件命名相同。同一个系统下，约束名不能重复。
constraint t_course_course foreign key (course_no) references db_course (course_no),
constraint t_course_course foreign key (course_name) references db_course (course_name));
命名相同的失误是因为我平时惯性的约束命名方式：constraint 表名_约束表名 foreign key references，但恰巧在这个表中，
有两个约束外键都来自同一个表。

修改为：
constraint t_course_course foreign key (course_no) references db_course (course_no),
constraint t_course_teacher foreign key (work_id) references db_teacher (work_id),
constraint t1_course_course foreign key (course_name) references db_course (course_name));

Oracle sql语句修改表名：
https://blog.csdn.net/qq_39233076/article/details/81080436
方法1：在原表上修改表名

ALTER TABLE [原表名称] RENAME TO [新表名称];

sql语句清空表数据三种方式：
https://www.cnblogs.com/lidar/p/16792815.html
Delete from tablename where 1=1

【SQL】查找最新一条数据：
select * from
(select * from 表名 where fd_yard='123' order times )
where ROWNUM = 1

java.sql.SQLException: 索引中丢失 IN 或 OUT 参数:12

参数索引可能不对

2023/03/17：
1.添加列：
oracle：
ALTER TABLE NAS_ARC_DISTRIBUTION_T ADD archived_retain_time VARCHAR2(10)

hadoop:

https://www.lmlphp.com/user/10231/article/item/418226/

alter TABLE a_s_ftp_param_summary_id_t add columns(3sigma_value string,cpk_value string);

2.
oracle修改列名：
ALTER TABLE NAS_ARC_DISTRIBUTION_T RENAME COLUMN archived_retain TO archived_retain_time;

impala修改列名：
https://blog.csdn.net/zy103118/article/details/103010989?spm=1001.2101.3001.6650.15&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-15-103010989-blog-116461610.pc_relevant_3mothn_strategy_recovery&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-15-103010989-blog-116461610.pc_relevant_3mothn_strategy_recovery&utm_relevant_index=21
ALTER TABLE a_s_ftp_param_summary_id_t CHANGE 3sigma_value three_sigma_value string;

3.oracle修改复合主键：

先删除主键约束： alter table NAS_ARC_DELETE_T drop constraint SYS_C0010718 cascade;

再 alter table NAS_ARC_DELETE_T add constraint SYS_C0010718 primary key
("source_folder_path", "is_folder", "delete_flag","UPDATE_TIME") using index;

4.union、union all 、distinct的区别和用途：

5. SQL查询时发生缺失右括号错误：
https://www.cnblogs.com/csnjava/p/14282706.html
非常多时候都是由于关于日期类型的转化少了单引號。

6.视图删除：
DROP VIEW view_name

7.执行hive SQL时报错 AnalysisException: select list expression not produced by aggregation output (missing from GROUP BY clause?)
https://www.cnblogs.com/dw-date/p/12710802.html

这个错误是由于查询列后面的字段没有全部放在group by 后面（除聚合函数以外）造成的。
case when 的字段也需要添加在group by 后面

case when写法：
case when t.array_product_id is null then null else t.sub_product_id end as array_product_id

8.用ResultSet.next()轮询结果集时，出现Object has been closed异常：
https://bbs.csdn.net/topics/60190530?list=lz

RS执行一次就结束了，resultset生命期依赖于statement

9.impala中可直接使用upsert sql语句:
https://www.modb.pro/db/437786

10.sum函数 string 转换成数字类型：
SELECT SUM(cast(source_size as numeric(12,0)))as total_source_size,
SUM(cast(zip_size as numeric(12,0)))as total_zip_size
from d_c_nas_archiving_hist_t
WHERE update_time BETWEEN '2023/05/25 00:00:00' and '2023/05/26 07:30:37'

2023/06/30:
设置NULL 即 set 字段=NULL

2023/07/27:
使用split_part()切割函数:
split_part(string 切割字段, delimiter 以某个字符进行切割, field 切割后取某一部分)
split_part(string column, delimiter str, field int)

https://blog.csdn.net/rosejeck/article/details/86577407

2023/08/04:
1.调用kudu api进数据，若主键值一致会导致原先的数据值被覆盖

2.Impala字符串截取left、right、substr/substring
https://blog.csdn.net/wang1qqqq/article/details/127789303

left(string,length) 返回从左侧开始计算，指定length长度的字符串
right(string,length) 返回从右侧开始计算，指定length长度的字符串
substr(string,start,length) 返回从指定start位置开始计算，指定length长度的字符串；其中，start从1开始
substring(string,start,length) 同上，效果一样

2023/11/08:
1.使用聚合函数时，select 后若跟聚合函数与非聚合函数，group by 后只能跟非聚合函数，
当group by与order by 同时使用时，group by需写在order by的前面，
order by后面可跟聚合或非聚合，聚合、非聚合可同时使用

1.oracle分组后取每组的前几行：

SELECT * FROM (
WITH tmp AS (
SELECT "eqp_type", "day_timekey", "save_time","work_id",count(1) num,
cast("day_timekey" as numeric(12,0))+cast("save_time" as numeric(12,0)) sumtimekey FROM NAS_ARC_ARCHIVING_T
WHERE "work_id" IS NOT NULL AND
"eqp_type" like 'FL%'
--"eqp_type" in ('CLAPI','MQAPI')
group by "eqp_type","day_timekey", "save_time","work_id" ORDER BY "eqp_type","day_timekey" asc
)
SELECT "eqp_type", "day_timekey", "save_time","work_id", num,sumtimekey,
ROW_NUMBER() OVER (PARTITION BY "eqp_type" ORDER BY "eqp_type","day_timekey" asc ) rwn FROM tmp
) t
WHERE t.rwn<=8