-- DBMS Version 확인
select @@version; --또는 select version();

-- 모든 시스템 변수 리스트
show global variables;

-- database(=schema) 목록
SELECT DISTINCT table_schema FROM INFORMATION_SCHEMA.TABLES;

-- 사용자 정보
SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILIEGES;
-- 실행중인 커맨드
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

-- 테이블 구조/설명 조회
DESC INFORMATION_SCHEMA.TABLES;

-- 테이블/컬럼 구조
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'XXX'
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

-- 간단히 변수 선언해서 사용하기
SET @PARAM1 = 1; -- 변수명 앞에 @골뱅이 필수!!
SELECT @PARAM1;

-- 날짜 다루기
SELECT CAST(NOW()) as DATE);
SELECT STR_TO_DATE('20230111', '%Y%m%d');
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); // MINUTE, MONTH, YEAR
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY); // 또는 DATE_ADD의 INTERVAL 값을 음수로 넣으면 같은 결과
SELECT DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y%m%d%H%i%s');

-- 유효체크
SELECT CASE WHEN STR_TO_DATE('2023XXXX') IS NOT NULL THEN '정상' ELSE '비정상' END; // 날짜문자열인지 체크
SELECT CASE WHEN '99' REGEXP '^[0-9]+$ = 1 THEN '숫자' ELSE '숫자아닌문자열포함' END;

MariaDB 레퍼런스: https://mariadb.com/kb/en/ (MariaDB Server > Tutorials)

..

-- Oracle Version 확인
select * from v$version;

-- 접속계정(현재세션)의 정보 조회
SELECT  SYS_CONTEXT('USERENV', 'IP_ADDRESS') -- 접속한IP(The IP address of the client machine.)
FROM DUAL;

-- 테이블 컬럼 정보
  SELECT t.TABLE_NAME, tc.COMMENTS, t.COLUMN_ID, t.COLUMN_NAME
        ,t.DATA_TYPE
         ||NVL2(t.DATA_LENGTH, '('||TO_CHAR(t.DATA_LENGTH)
                                  ||NVL2(t.DATA_PRECISION, ','||TO_CHAR(t.DATA_PRECISION),'')
                              ||')', '') AS DATA_TYPE
        ,t.NULLABLE, cc.COMMENTS, t.DATA_DEFAULT AS DATA_DEFAULT
        ,A.POSITION AS PK_ORDER
    FROM USER_TAB_COLS t --테이블별 컬럼정보
        ,USER_TAB_COMMENTS tc -- 테이블 코멘트
        ,USER_COL_COMMENTS cc  -- 컬럼 코멘트
        ,(SELECT ct.TABLE_NAME, ct_col.COLUMN_NAME, ct_col.POSITION
            FROM ALL_CONSTRAINTS  ct --제약조건
                ,ALL_CONS_COLUMNS ct_col --제약조건 컬럼
           WHERE ct.CONSTRAINT_TYPE = 'P' 
             AND ct.OWNER = ct_col.OWNER
             AND ct.CONSTRAINT_NAME = ct_col.CONSTRAINT_NAME
         ) A
   WHERE 1=1
     AND tc.TABLE_TYPE = 'TABLE'
     AND t.TABLE_NAME = tc.TABLE_NAME(+)
     AND t.TABLE_NAME = cc.TABLE_NAME(+) AND t.COLUMN_NAME = cc.COLUMN_NAME(+)
     AND t.TABLE_NAME = tc.TABLE_NAME(+)
	 AND t.TABLE_NAME = A.TABLE_NAME(+)
     AND t.COLUMN_NAME = A.COLUMN_NAME(+)
     AND t.TABLE_NAME IN ('대상테이블명')
ORDER BY t.TABLE_NAME, t.COLUMN_ID;


-- 시퀀스 조회
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME LIKE '%시퀀스명%';

--제약조건 조회 :CONSTRAINT_TYPE=P(PK)/C(제약조건)
SELECT A.TABLE_NAME, A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE
     , B.COLUMN_NAME , B.POSITION
  FROM ALL_CONSTRAINTS  A
     , ALL_CONS_COLUMNS B
 WHERE A.TABLE_NAME      = '테이블명'
   AND A.CONSTRAINT_TYPE = 'P' --P(PrimaryKey)|C(제약조건)|..
   AND A.OWNER           = B.OWNER
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 ORDER BY B.POSITION;
 
--프로시저 내용 검색
SELECT T1.OBJECT_ID, T1.OBJECT_NAME, T1.OBJECT_TYPE, T2.TEXT
  FROM USER_OBJECTS T1
 INNER JOIN USER_SOURCE T2
    ON T1.OBJECT_NAME = T2.NAME
 WHERE T1.OBJECT_TYPE IN ('PROCEDURE')--, 'FUNCTION')
   AND T2.TEXT LIKE '%EXECUTE%'  /* 검색어 */
  ;

--CREATE TABLE 컬럼정의 구문 뽑기
SELECT COLUMN_NAME||' '
    ||DATA_TYPE||CASE DATA_TYPE WHEN 'NUMBER' THEN DECODE(DATA_LENGTH, 22, '', '('||DATA_LENGTH||')')
                     WHEN 'VARCHAR2' THEN '('||DATA_LENGTH||')' END
    ||DECODE(NULLABLE, 'Y', ' NOT NULL ', '')||', '  
    FROM USER_TAB_COLS WHERE TABLE_NAME = '테이블명';
/* REF_VALUE_18 VARCHAR2(100) NOT NULL , 
   REF_VALUE_19 VARCHAR2(100) NOT NULL , 
   REF_VALUE_20 VARCHAR2(100) NOT NULL ,   */
   
--INSERT문 컬럼목록 만들기
SELECT LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID) 
  FROM USER_TAB_COLS WHERE TABLE_NAME = '테이블명';