wiki:database:h2

차이

문서의 선택한 두 판 사이의 차이를 보여줍니다.

차이 보기로 링크

--- wiki:database:h2 [2020/06/04 13:56]
dhan
+++ wiki:database:h2 [2023/06/28 13:48] (현재)
dhan
@@ 줄 12: / 줄 12: @@
 <code sql>
 sql> show tables;
+</code>
+\\
+컬럼 목록 확인하기
+<code sql>
+sql> show columns from T_LOG_SYSTEM;
 </code>
@@ 줄 71: / 줄 76: @@
 	TEXT VARCHAR(255) DEFAULT NULL,
 	CREATED_AT VARCHAR(64) DEFAULT NULL,
-	"SOURCE" VARCHAR(64) DEFAULT NULL,
+	"SOURCE" VARCHAR(256) DEFAULT NULL,
 	USERID VARCHAR(64) DEFAULT NULL,
 	USERSCREENNAME VARCHAR(64) DEFAULT NULL,
@@ 줄 82: / 줄 87: @@
 </code>
+Instagram
+<code sql>
+CREATE TABLE PUBLIC.TB_INSTAGRAM (
+	ID VARCHAR(64) NOT NULL,
+	PERMALINK VARCHAR(1024) DEFAULT NULL,
+	CAPTION TEXT,
+	CREATED_TIME VARCHAR(64) DEFAULT NULL,
+	MEDIA_TYPE VARCHAR(64) DEFAULT NULL,
+	MEDIA_URL VARCHAR(1024) DEFAULT NULL,
+	THUMBNAIL_URL VARCHAR(1024) DEFAULT NULL,
+	USERNAME VARCHAR(64) DEFAULT NULL
+);
+</code>
-===== Backup =====
+===== Case Study =====
+컬럼 크기 변경(TOTAL_SIZE, USED_SIZE, NAME)
+<code sql>
+ALTER TABLE T_LOG_SYSTEM ALTER COLUMN TOTAL_SIZE VARCHAR(32) NOT NULL;
+ALTER TABLE T_LOG_SYSTEM ALTER COLUMN USED_SIZE VARCHAR(32) NOT NULL;
+ALTER TABLE T_LOG_SYSTEM ALTER COLUMN NAME VARCHAR(64);
+</code>
+컬럼 추가(INFLOW)
+<code sql>
+ALTER TABLE T_ADDON_TOPKEYWORD ADD COLUMN INFLOW VARCHAR(64) DEFAULT '';
+UPDATE T_ADDON_TOPKEYWORD SET INFLOW='inflow' WHERE INFLOW IS NULL;
+ALTER TABLE T_ADDON_TOPKEYWORD_EXCEPT ADD COLUMN INFLOW VARCHAR(64) DEFAULT '';
+UPDATE T_ADDON_TOPKEYWORD_EXCEPT SET INFLOW='inflow' WHERE INFLOW IS NULL;
+</code>
-===== Tip =====
+인덱스(INDEX) 추가, 조회
-<do 2020-02-10 김토피아>H2 실행 및 설정방법</do>
+<code sql>
+CREATE INDEX {인덱스명} ON {테이블명} ({컬럼명})
+-- 예시(온나라) --
+CREATE INDEX TB_BMS_DCT_ENF_2021_IDX ON PUBLIC.TB_BMS_DCT_ENF_2021 (DOCID,ENFDOCID);
+CREATE INDEX TB_BMS_DCT_ENF_2021_DOCID_IDX ON PUBLIC.TB_BMS_DCT_ENF_2021 (DOCID);
+CREATE INDEX TB_BMS_DCT_ENF_2021_ENFDOCID_IDX ON PUBLIC.TB_BMS_DCT_ENF_2021 (ENFDOCID)
+-- 인덱스 조회
+SELECT * FROM information_schema.CONSTRAINTS c WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME='{테이블명}'
+or
+SELECT * FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME ='{테이블명}';
+</code>
+===== Backup =====
+===== Tip =====
-===== Troubleshooting =====
+<do 2020-02-10 김토피아>H2 실행 및 설정방법</do>
+===== Troubleshooting =====

/volume1/web/dokuwiki/data/attic/wiki/database/h2.1591246571.txt.gz · 마지막으로 수정됨: 2022/03/10 19:52 (바깥 편집)