기억력이 나쁜 나에게 주는 페이지

-- 값에서 검색값의 시작위치를 반환

oracle : select instr('달력1,달력2,달력3', ',', 1) from dual; ----> 4를 반환

mssql : select charindex(',', '달력1,달력2,달력3') ----> 4를 반환

​

-- 조건 비교 후 처리시

oracle : decode(조건, true값, false값)

mssql : case when 조건 then true값 else false값 end

​

-- 현재일에서 1달 뒤 구하기

oracle : SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;

mssql : SELECT DATEADD(mm, 1, GETDATE())

​

-- 날짜로 변환

oracle : SELECT TO_DATE('202005' || '01','YYYYMMDD' ) FROM DUAL;

mssql : SELECT CAST('202005' + '01' AS DATE)

​

-- 널값시 처리

oracle : NVL(END_YN, 'N')

mssql : ISNULL(END_YN, 'N')

​

-- 사용된 바이트 수를 반환합니다

oracle : vsize(expression)

mssql : DATALENGTH ( expression )

​

-- 조건문

oracle : IF 조건1 THEN 처리1 ELSIF 조건2 THEN 처리2 ELSE 처리3 END IF;

mssql : IF 조건1 처리1 ELSE IF 조건2 처리2 ELSE 처리3

​

-- 변수에 값 할당시

oracle : param := 0;

mssql : SET @param = 0;

​

-- 문자열 자르기

oracle : substr('20200520', 1, 4) --- 2020

mssql : substring('20200520', 1, 4)

​

oracle : substr('20200520', 0, 4) --- 2020

mssql : substring('20200520', 1, 4) -- 변환 작업시 주의해서 1로 수정해야 함

​

-- 절삭하기

oracle : SELECT TRUNC(150.75, 1) FROM DUAL;

mssql : SELECT ROUND(150.75, 1, 1);

​

-- 나머지 구하기

oracle : SELECT MOD(12,5) FROM DUAL;

mssql : SELECT 12%5

​

-- 두 기간의 월 수 구하기

oracle : SELECT TRUNC(MONTHS_BETWEEN(TO_DATE('2020-06-05','RRRR-MM-DD'), TO_DATE('2020-05-01','RRRR-MM-DD'))) "month" FROM DUAL;

mssql : SELECT DATEDIFF(MONTH, CAST('2020-05-01' AS DATE), CAST('2020-06-05' AS DATE))

​

-- 마지막 일자 구하기

oracle : select TO_CHAR(LAST_DAY(sysdate), 'DD') from dual

mssql : select DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(CONVERT(CHAR(6),GETDATE(),112)+'01' AS DATE))))

​

-- 올림

oracle : select ceil(1.1) from dual; -- 2

mssql : select ceiling(1,1) -- 2

​

-- ORACLE

SELECT LEVEL_CODE,

LAG(LEVEL_CODE) OVER (ORDER BY LEVEL_CODE) UPLEVEL,

LAG(GRADE_CODE) OVER (ORDER BY LEVEL_CODE) UPGRADE

FROM HR_CODE_LEVEL

​

-- MSSQL

SELECT A.LEVEL_CODE, B.LEVEL_CODE UPLEVEL, B.GRADE_CODE UPGRADE

FROM

(SELECT ROW_NUMBER() OVER(ORDER BY LEVEL_CODE) ROWID, LEVEL_CODE, LEVEL_NAME, GRADE_CODE FROM HR_CODE_LEVEL) A

LEFT OUTER JOIN

(SELECT ROW_NUMBER() OVER(ORDER BY LEVEL_CODE) ROWID, LEVEL_CODE, LEVEL_NAME, GRADE_CODE FROM HR_CODE_LEVEL) B

ON

A.ROWID = B.ROWID+1

[출처] blog.naver.com/dbbible/221969929542

mssql  줄바꿈 치환

 REPLACE(REPLACE(CONTENT, char(13) , '' ) ,  char(10), '' ) 

* 줄바꿈, 탭문자 제거 방법(text 타입도 포함)

- 변경 스트링 함수

replace([컬럼명], [변경할 문자], [변경될 문자])

- 엔터, 탭 문자 제거 함수 활용

Tab : char(9)

Line feed: char(10)

Carriage return: char(13)

>엔터 : replace(replace([컬럼명], char(13), ''), char(10), '')

>탭 : replace([컬럼명], char(9), '')

 텍스트 타입일 경우, [replace 함수의 인수 1에 대한 인수 데이터 형식 text이(가) 잘못되었습니다.] 에러를 확인할 수 있다.

=> replace(convert(varchar(max), [컬럼명]), [변경할 문자], [변경될 문자])

TEXT 타입 컬럼명 모두 치환

replace(replace(replace(convert(varchar(max),isnull([컬럼명], '-')), char(9), ' '),char(10), ' '), char(13), '') AS [컬럼명] 

출처: https://ngio.co.kr/3253

정렬되지 않은 컬럼데이터를 row_number로 다시 넣는 쿼리
해당 쿼리를 통해 PK값에 잡혀 있던 컬럼의 값을 다시 정렬해서 넣을 수 있을거 같음

  UPDATE A
  SET A.NUM = A.RowNum
   FROM ( 
SELECT NUM, ROW_NUMBER() OVER (ORDER BY 정렬기준의컬럼 ASC) AS RowNum 
FROM 테이블명  
WHERE  (1 = 1) AND ownerID IN ('62') : 원하는 조건넣는 부분

)
) AS A

SELECT * FROM (
SELECT (CASE WHEN MAIN.COLUMN_ID = 1 THEN MAIN.TABLE_COMMENTS ELSE '' END)  table_kor_name
     , (CASE WHEN MAIN.COLUMN_ID = 1 THEN MAIN.TABLE_NAME ELSE '' END ) table_eng_name
     , MAIN.COLUMN_ID column_id
     , MAIN.COLUMN_NAME column_name
     , MAIN.COLUMN_COMMENTS column_comments
     , (CASE WHEN MAIN.DATA_TYPE = 'VARCHAR2' THEN 'VARCHAR' ELSE MAIN.DATA_TYPE END ) AS data_type
     , (CASE MAIN.DATA_TYPE WHEN 'NUMBER' THEN ''
                            WHEN 'DATE'   THEN '' ELSE TO_CHAR(MAIN.DATA_LENGTH) END) data_type_length
     , (CASE NVL(MAIN.NULL_FLAG, 'N') WHEN 'N' THEN 'N' ELSE '' END) null_flag
     , (SELECT CONST.CONSTRAINT_NAME FROM USER_CONSTRAINTS CONST, USER_CONS_COLUMNS COLS, USER_CONS_COLUMNS R_COLS
         WHERE CONST.CONSTRAINT_NAME   = COLS.CONSTRAINT_NAME
           AND CONST.CONSTRAINT_TYPE = 'P'
           AND CONST.R_CONSTRAINT_NAME = R_COLS.CONSTRAINT_NAME(+)
           AND CONST.TABLE_NAME  = MAIN.TABLE_NAME
           AND COLS.COLUMN_NAME = MAIN.COLUMN_NAME) AS PK
      , ( SELECT DISTINCT CONST.CONSTRAINT_NAME||' ['||COLS.COLUMN_NAME||'('||CONST.R_CONSTRAINT_NAME||')]'
              FROM USER_CONSTRAINTS CONST
                 , USER_CONS_COLUMNS COLS
                 , USER_CONS_COLUMNS R_COLS
             WHERE CONST.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
               AND CONST.CONSTRAINT_TYPE = 'R'
               AND CONST.R_CONSTRAINT_NAME = R_COLS.CONSTRAINT_NAME(+)
               AND CONST.TABLE_NAME  = MAIN.TABLE_NAME
               AND COLS.COLUMN_NAME = MAIN.COLUMN_NAME
              -- AND rownum < 2
          ) AS FK
--     , (CASE SUB.CONSTRAINT_TYPE WHEN 'P' THEN 'PK' WHEN 'R' THEN 'FK ('||SUB.R_CONST_NAME||')' END) key_flag
  FROM (SELECT T_COMT.COMMENTS AS table_comments
             , T_COLS.TABLE_NAME AS table_name
             , C_COMT.COMMENTS AS column_comments
             , T_COLS.COLUMN_NAME AS column_name
             , (CASE T_COLS.NULLABLE WHEN 'Y' THEN 'Y' END) AS null_flag
             , T_COLS.DATA_TYPE AS data_type
             , T_COLS.DATA_LENGTH AS data_length
             , T_COLS.COLUMN_ID AS column_id
             , T_COLS.DATA_PRECISION AS data_precision
             , T_COLS.DATA_SCALE AS data_scale
          FROM USER_TAB_COLUMNS T_COLS
             , USER_TAB_COMMENTS T_COMT
             , USER_COL_COMMENTS C_COMT
         WHERE T_COLS.TABLE_NAME  = T_COMT.TABLE_NAME
           AND T_COLS.TABLE_NAME  = C_COMT.TABLE_NAME
           AND T_COLS.COLUMN_NAME = C_COMT.COLUMN_NAME
           AND T_COMT.TABLE_TYPE  = 'TABLE' ) MAIN
 WHERE MAIN.TABLE_NAME  = UPPER('테이블명')  /*특정 테이블 조회일 경우 사용.*/
)
 ORDER BY table_eng_name, PK, FK, COLUMN_ID 
;


-- 인덱스 조회
SELECT c.index_name
     , c.column_name
     , c.column_position
     , i.uniqueness 
  FROM user_indexes i
     , user_ind_columns c 
 WHERE c.index_name = i.index_name 
   AND c.table_name = '테이블명'; 


-- Foreing Key가 여러개 일 경우 조회
select distinct *
  from (
 SELECT CONST.CONSTRAINT_NAME, COLS.COLUMN_NAME, cols.position
  FROM USER_CONSTRAINTS CONST
     , USER_CONS_COLUMNS COLS
     , USER_CONS_COLUMNS R_COLS
 WHERE CONST.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
   AND CONST.CONSTRAINT_TYPE = 'R'
   AND CONST.R_CONSTRAINT_NAME = R_COLS.CONSTRAINT_NAME(+)
   AND CONST.TABLE_NAME  = '테이블명'
               --AND COLS.COLUMN_NAME = MAIN.COLUMN_NAME
 order by const.constraint_name, cols.position
 )
  order by constraint_name, position
   

MSSQL 에서 sp_help "테이블명" 으로 하면 테이블의 정보를 확인할 수 있으나
컬럼의 Comment까지 같이 확인은 안된다.
아래는 테이블의 컬럼Comment를 확인할 수 있는 쿼리이다.
 
SELECT A.TABLE_NAME, 
		C.VALUE AS TABLE_COMMENT,
		A.COLUMN_NAME, A.DATA_TYPE, 
		ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR),  
				CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' +
				CAST(A.NUMERIC_SCALE AS VARCHAR)) AS COLUMN_LENGTH,
		A.COLUMN_DEFAULT, A.IS_NULLABLE,
		B.VALUE AS COLUM_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS A LEFT OUTER JOIN
	SYS.EXTENDED_PROPERTIES B
	ON B.major_id = object_id(A.TABLE_NAME) 
	AND A.ORDINAL_POSITION = B.minor_id
	LEFT OUTER JOIN
		(SELECT object_id(objname) AS TABLE_ID,
					VALUE
		FROM ::FN_LISTEXTENDEDPROPERTY
			(NULL, 'User','dbo','table',
				NULL, NULL, NULL)
			) C
	ON object_id(A.TABLE_NAME) = C.TABLE_ID
WHERE A.TABLE_NAME = '테이블명'
ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION 
 

SELECT A.COLUMN_ID,
       A.COLUMN_NAME,
       A.DATA_TYPE,
       CASE WHEN A.DATA_TYPE = 'NUMBER' THEN A.DATA_PRECISION ||','|| A.DATA_SCALE
            WHEN A.DATA_TYPE = 'DATE' THEN '7'
            ELSE TO_CHAR(DATA_LENGTH)
       END DATA_SIZE,
       B.COMMENTS COMMENTS,
       DECODE(D.CONSTRAINT_TYPE, 'P', 'PK') AS KEY_FIELD
FROM ALL_TAB_COLUMNS A INNER JOIN ALL_COL_COMMENTS B
                       ON  A.TABLE_NAME = B.TABLE_NAME
                       AND A.OWNER = B.OWNER
                       AND A.COLUMN_NAME= B.COLUMN_NAME
                     LEFT OUTER JOIN ALL_CONS_COLUMNS C
                       ON A.TABLE_NAME = C.TABLE_NAME
                       AND A.OWNER = C.OWNER
                       AND A.COLUMN_NAME = C.COLUMN_NAME
                       AND C.POSITION IS NOT NULL
                     LEFT OUTER JOIN ALL_CONSTRAINTS D
                       ON  C.TABLE_NAME = D.TABLE_NAME
                       AND C.OWNER = D.OWNER
                       AND C.CONSTRAINT_NAME  = D.CONSTRAINT_NAME
                       AND D.CONSTRAINT_TYPE = 'P'
WHERE A.TABLE_NAME = '테이블명'
ORDER BY A.COLUMN_ID

출처 : http://blog.naver.com/PostView.nhn?blogId=2zerox&logNo=90150539116
[출처] MSSQL 테이블 정보 쿼리(Comment 포함)|작성자 수정이아빠

 SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC


mssql 테이블 목록과 각 테이블의 데이터수 쿼리

추가)
1. 테이블 목록 가져오기
 select distinct table_name from information_schema.columns

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE 1=1
--AND TABLE_TYPE ='VIEW'
AND TABLE_TYPE ='BASE TABLE'
--AND TABLE_NAME NOT LIKE '%TEST%'
AND TABLE_SCHEMA = 'dbo'
ORDER BY TABLE_NAME 

2. 프로시져 목록 가져오기
 select distinct specific_name from INFORMATION_SCHEMA.PARAMETERS

MSSQL 주석달기

1. 주석추가 (add)

--테이블

EXEC   sp_addextendedproperty 'MS_Description', '테이블설명', 'user', dbo, 'table',테이블명

--컬럼들

EXEC   sp_addextendedproperty 'MS_Description', '컬럼설명', 'user', dbo, 'table', 테이블명, 'column', 컬럼명

 2. 주석수정 (update)

--테이블

EXEC   sp_updateextendedproperty 'MS_Description', '테이블설명', 'user', dbo, 'table',테이블명

--컬럼들

EXEC   sp_updateextendedproperty 'MS_Description', '컬럼설명', 'user', dbo, 'table', 테이블명, 'column', 컬럼명

 ORACLE 주석달기

--테이블에 주석달기

COMMENT ON TABLE 테이블명 IS '주석'

--컬럼에 주석달기

COMMENT ON COLUMN 테이블명.컬럼명 IS '주석'

주석삭제

COMMENT ON COLUMN 테이블명.컬럼명 IS ''

/////////////디코드 수정/////////////////

DECODE(Sport,'Cricket','England','Hockey','India','Base Ball','America') AS 'Originating Country'



CASE Sport
    WHEN 'Cricket' THEN 'England' 
    WHEN 'Hockey' THEN 'India' 
    WHEN 'Base Ball' THEN 'America' 
    ELSE NULL 
END AS 'Originating Country'


///////////////////////////////////////


/////////////TO_CHAR 수정//////////////

TO_CHAR(SYSDATE, 'YYYY-MM-DD')
> CONVERT(VARCHAR(10), GETDATE(), 120)

TO_CHAR(SYSDATE, 'YYYYMMDD')
> CONVERT(VARCHAR, GETDATE(), 112)

TO_CHAR(CON_STRDATE,'YYYY')
>CONVERT(VARCHAR(4),GETDATE(),112)

TO_CHAR(CON_STRDATE,'MM')
>CONVERT(VARCHAR(2),GETDATE(),1)

TO_CHAR(CON_STRDATE,'DD')
>CONVERT(VARCHAR(2),GETDATE(),5)

TO_CHAR(CON_STRDATE,'D')
>DATEPART(W, GETDATE())


/** SYSDATE -> GETDATE()

///////////////////////////////////////



///////////TO_DATE 수정///////////////

TO_DATE('2008-01-01','YYYY-MM-DD')

CONVERT(DATETIME,'2008-01-01')




TO_DATE('20080101','YYYYMMDD')

CONVERT(DATETIME,'20080101')

//////////////////////////////////////



/////////////////날짜 - 날짜/////////////

TO_DATE('20081210','YYYYMMDD') - TO_DATE('20081110','YYYYMMDD')


DATEDIFF(DD,CONVERT(DATETIME,'20081110'),CONVERT(DATETIME,'20081210'))

/////////////////////////////////////////




//////////////// 999,999,999 포멧지정///////////////
TO_CHAR(999999999,'fm999,999,999')

REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,999999999),1),'.00','')

///////////////////////////////////////////////////


///////////////////// ROWNUM ///////////////////////////////

ROWNUM

ROW_NUMBER() OVER(ORDER BY '정렬기준 컬럼')

///////////////////////////////////////////////////////////



/////////////////// || 를 + 로 변경할것//////////////////


////////////////// NVL -> ISNULL ////////////////////////


////////////////// LENGTH -> LEN ////////////////////////

////////////////// SUBSTR -> SUBSTRING ////////////////////////

//////////////// INSERT안에 서브쿼리가 들어가있는 경우//////////////
INSERT INTO TEST(COUNT) VALUES(SELECT COUNT(*) FROM TEST)
-> 
DECLARE @COUNT INT;
SELECT @COUNT = COUNT(*) FROM TEST
INSERT INTO TEST(COUNT) VALUES(@COUNT)


/////////////// LPAD(RPAD) 처리 ///////////////////////////////
SELECT LPAD('31',5,'0')
-> REPLICATE('0',5 - LEN('31')) + '31'


////////////////////// CONNECT BY ////////////////////////////
WITH 사용하여 마소를 욕하며 변환

///////////////////// (+)를 이용한 JOIN /////////////////////////
무조건 표준 JOIN문을 이용해 변환 =* 사용시 호환성문제와 조인된 테이블을 참조할 수 없음

UPDATE /*+ BYPASS_UJVC */

(SELECT TARGET_PATH ORG,  '/sb/'||TARGET_PATH UPD FROM WCM_FOLDER_MAP WHERE SERVER_ID = 1) A

SET A.ORG = A.UPD

update 의 서브쿼리로 자신이 필요로 하는  기존컬럼과 변경하는 컬럼들을 select해놓고 as 를 주어

 값을 뷰들로 매칭시켜서 대입한다.

오라클 힌트//

- 오라클 tns설정하는 방법

오라클이 있는 폴더를 연다.

/ORACLE/product/9.2.0/network/admin/tnsnames.ora 
연결하고자 하는 오라클 host를 다음과 같이 입력한다.

DBTEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =xxx.xxx.xxx.xxx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbtest)
    )
  )

2대의 오라클 서버로 failover기능 부여하여 설정 시
  
TESTDB =
  (DESCRIPTION =
        (LOAD_BALANCE=OFF)
        (FAILOVER=ON)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xx1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xx2)(PORT = 1521))
    )
 (CONNECT_DATA =
                 (SERVER = DEDICATED)
                 (SERVICE_NAME =testdb)
                 (failover_mode=(type=none)(method=basic))
          )
)

- 유저에 대한 테이블 스페이스 변경

alter user user명 default tablespace TBS명;

- 유저 생성과 함께 테이블 스페이스 지정

create user user명

identified by 패스워드
default tablespace TBS명;

요즘 툴들이 좋아서 클릭만으로도 할 수 있지만 기본을 잊지 않기 위해서 ~~~

컬럼명 변경

※board 는 테이블명 입니다.

오라클용 쿼리에  ROWNUM 를 MYSQL용으로 변환시에 아래 처럼 간단하게 적용하면 된다.

Oracle용 쿼리::::

SELECT A.* FROM ( 
  SELECT ROWNUM NUM,  *
     FROM WCM_CONTENT_SET cs 
  )A WHERE NUM >= ? AND NUM <= ?

MYSQL용 쿼리::::

SELECT A.* FROM ( 
  SELECT (@ROWNUM := @ROWNUM + 1) AS NUM, *
   FROM WCM_CONTENT_SET cs, (SELECT @ROWNUM := 0) rr 
  )A WHERE NUM >= ? AND NUM <= ?