차이

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

--- wiki:database:h2 [2020/04/02 08:58]
dhan [Ref]
+++ wiki:database:h2 [2023/06/28 13:48] (현재)
dhan
@@ 줄 1: / 줄 1: @@
 ====== H2 ======
+<WRAP left notice 80%>
+  * description : H2 데이터 베이스 사용 방법
+  * author      : 주레피
+  * email       : dhan@repia.com
+  * lastupdate  : 2020-04-02
+</WRAP>
+<WRAP clear/>
+===== DML =====
+테이블 목록 확인하기
+<code sql>
+sql> show tables;
+</code>
+\\
+컬럼 목록 확인하기
+<code sql>
+sql> show columns from T_LOG_SYSTEM;
+</code>
+===== RSA DDL =====
+Related
+<code sql>
+CREATE TABLE PUBLIC.TB_RELATED (
+	ID INTEGER NOT NULL,
+	MORPH VARCHAR(200),
+	KEYWORD VARCHAR(512),
+	DISPLAY_KEYWORD VARCHAR(255),
+	DISPLAY_EXCEPT_KEYWORD VARCHAR(255),
+	EXCEPT_KEYWORD VARCHAR(100),
+	ONLY_DISPLAY_KEYWORD VARCHAR(255),
+	USE_YN CHAR(1),
+	ADD_KEYWORD VARCHAR(255)
+);
+</code>
+Youtube
+<code sql>
+CREATE CACHED TABLE PUBLIC.TB_YOUTUBE
+(
+    VIDEOID VARCHAR(64) NOT NULL,
+    VIDEOTITLE VARCHAR(512),
+    VIDEODESCRIPTION TEXT,
+    THUMBNAIL VARCHAR(512),
+    NOTE VARCHAR(1024),
+    LINK VARCHAR(512),
+    CHANNELID VARCHAR(64),
+    PUBLISHEDAT VARCHAR(64)
+);
+</code>
+Facebook
+<code sql>
+CREATE TABLE PUBLIC.TB_FACEBOOK (
+	ID VARCHAR(64) NOT NULL,
+	FROMNAME VARCHAR(64) DEFAULT NULL,
+	FROMCATEGORY VARCHAR(64) DEFAULT NULL,
+	FROMID VARCHAR(64) DEFAULT NULL,
+	MESSAGE CLOB,
+	PICTURE VARCHAR(2048),
+	LINK VARCHAR(512) DEFAULT NULL,
+	NAME VARCHAR(255) DEFAULT NULL,
+	"TYPE" VARCHAR(64) DEFAULT NULL,
+	OBJECTID VARCHAR(64) DEFAULT NULL,
+	APPLICATIONNAME VARCHAR(64) DEFAULT NULL,
+	APPLICATIONID VARCHAR(64) DEFAULT NULL,
+	CREATEDTIME VARCHAR(64) DEFAULT NULL,
+	UPDATEDTIME VARCHAR(64) DEFAULT NULL
+);
+</code>
+Twitter
+<code sql>
+CREATE TABLE PUBLIC.TB_TWITTER (
+	ID VARCHAR(64) NOT NULL,
+	TEXT VARCHAR(255) DEFAULT NULL,
+	CREATED_AT VARCHAR(64) DEFAULT NULL,
+	"SOURCE" VARCHAR(256) DEFAULT NULL,
+	USERID VARCHAR(64) DEFAULT NULL,
+	USERSCREENNAME VARCHAR(64) DEFAULT NULL,
+	USERNAME VARCHAR(64) DEFAULT NULL,
+	USERURL VARCHAR(255) DEFAULT NULL,
+	USERLOCATION VARCHAR(64) DEFAULT NULL,
+	USERPROFILEIMAGEURL VARCHAR(255) DEFAULT NULL,
+	USERSTATUSESCOUNT VARCHAR(11) DEFAULT NULL
+);
+</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>
+===== 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>
+인덱스(INDEX) 추가, 조회
+<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 =====
-===== RSA =====
-  * 연관검색어
 ===== Tip =====
 <do 2020-02-10 김토피아>H2 실행 및 설정방법</do>
+===== Troubleshooting =====
 ===== Ref =====
 {{tag>주레피 DBMS h2 h2-tip}}

문제를 잘 정의하는 것은 문제를 절반 해결한 것이다. - 2023.12

사용자 도구

사이트 도구

차이

문서 도구