文章目录
IBIM的数据库
1、DB2 修改列属性 设置默认值
1.更改类型(设置为主键的列不能更改类型)
ALTER TABLE "SCHEMA"."TABLENAME" ALTER COLUMN "COL" SET DATA TYPE VARCHAR(32);
2.更改默认值
ALTER TABLE "SCHEMA"."TABLENAME" ALTER COLUMN "COL" SET DEFAULT 'ABC';
系统默认值:
ALTER TABLE "SCHEMA"."TABLENAME" ALTER COLUMN "COL" SET DEFAULT; --设置默认值
ALTER TABLE "SCHEMA"."TABLENAME" ALTER COLUMN "COL" DROP DEFAULT; --删除默认
值
3.更改是否允许空值
ALTER TABLE "SCHEMA"."TABLENAME" ALTER COLUMN "COL" SET NOT NULL;
ALTER TABLE "SCHEMA"."TABLENAME" ALTER COLUMN "COL" DROP NOT NULL;
更改列类型,是否允许空值后,需要执行REORG TABLE “TABLENAME”;
更改默认值后,通常需要执行
UPDATE "SCHEMA"."TABLENAME" SET "COL" = DEFAULT WHERE "COL" IS NULL;
4.sum
SELECT MANUFACTORY_ID as "manuFactoryId",
DEVICE_ID as "deviceId",
DEVICE_NAME as "deviceName",
SUM(QTYD_01) as "qtyd01",
SUM(QTYD_02) as "qtyd02",
group by MANUFACTORY_ID,DEVICE_ID,DEVICE_NAME;
5.decimal
decimal(SUM(QTYD_01),2) as "qtyd01",
6.版本查看
-- 查看版本
SELECT service_level, fixpack_num FROM TABLE(sysproc.env_get_inst_info()) as INSTANCEINFO
7.CASE WHEN
CASE WHEN QTYD_01 = 0 THEN 1 END
8.格式化字符日期格式
-- START_DATE = YYYYMMDDHH24
to_date(START_DATE, 'YYYY-MM-DD HH24')
9字符串转DB2日期格式
TIMESTAMP_FORMAT(START_DATE, 'YYYYMMDDHH24')
-- 日期格式可以直接加减日期值
START_DATE + 1 DAY - HOUR(START_DATE) HOURS
10.时间格式只保留日期
DATE (START_DATE) as START_DATE,
-- 2023-11-01 00:00:00.000000 转为2023-11-01
11. substr的不同
substr是没有为0的下标的,是从1开始的
12. 拼接两个字段并添加中间字符 || 运算符
也可以添加
PACKING_TYPE || '_' ||PACKING_SPECIFICATION as "packageType"
SELECT CONCAT(field1, ' ', field2) as combinedField
FROM yourTable;
13.字符串日期加减 (这个月的某一天,上个月的一天等等)
-- 上个月最后一天
SELECT TO_CHAR(LAST_DAY(TIMESTAMP_FORMAT('202401','YYYYMM') - 1 MONTHS),'yyyyMMdd') as a FROM
-- 上个月第一天
SELECT TO_CHAR(LAST_DAY(TIMESTAMP_FORMAT('202401','YYYYMM') - 2 MONTHS)+1 DAY ,'yyyyMMdd') as a FROM
-- 这个月第一天
SELECT TO_CHAR(LAST_DAY(TIMESTAMP_FORMAT('202401','YYYYMM') - 1 MONTHS)+1 DAY ,'yyyyMMdd') as a FROM
-- 这个月最后一天
SELECT TO_CHAR(LAST_DAY(TIMESTAMP_FORMAT('202401','YYYYMM'))-1 DAY ,'yyyyMMdd') as a FROM
14、查看存储过程
-- 查看所有存储过程
SELECT ROUTINENAME, TEXT
FROM SYSCAT.ROUTINES
WHERE ROUTINETYPE = 'P' and ROUTINESCHEMA = 'schema' ;
15、删除存储过程
-- 删除存储过程
DROP PROCEDURE 名称;
16、创建临时表
DECLARE GLOBAL TEMPORARY TABLE session.表名 (
MANUFACTORY_ID VARCHAR(40),
PLAN_VERSION VARCHAR(40)
) ON COMMIT PRESERVE ROWS;
ON COMMIT PRESERVE ROWS
事务提交后保留临时表
ON COMMIT DELETE ROWS;
提交后结束
17、存储过程
CREATE OR REPLACE PROCEDURE GENERATEANDQUERYMONTHSERIES()
BEGIN
end;
end;
18,创建视图(名称备注等)
CREATE VIEW 视图名称 AS 查询sql
CREATE VIEW employee_view AS
SELECT id, name
FROM employee
WHERE department = 'Sales';
给视图设置备注和普通表设置备注一样
参考文章
个人笔记,不同意见,望有交流
直接可以点击跳转连接
参考文章
个人笔记,不同意见,望有交流
直接可以点击跳转连接
作者