Data Dictionary
SELECT * FROM DICT; // 데이터 딕셔너리 조회
DBA_, USER_, ALL_ // 권한
SELECT * FROM USER_TABLES; // USER 권한의 TABLE 조회
SELECT * FROM USER_TAB_COLUMNS; // USER 권한의 TABLE의 COLUMN 조회
SELECT * FROM USER_CONSTRAINTS; // USER 권한의 CONSTRAINT 조회
DDL
CREATE
CREATE TABLE TABLE1(
COLUMN_NAME DATA_TYPE,
CONSTRAINT CONSTRAINT1 CHECK(COLUMN1, ~), // CHECK 제약조건 생성
CONSTRAINT CONSTRAINT2 PRIMARY KEY(COLUMN2), // PRIMARY KEY 제약조건 생성
CONSTRAINT CONSTRAINT3 UNIQUE(COLUMN3) // UNIQUE 제약조건 생성
CONSTRAINT CONSTRAINT4 FOREIGN KEY(COLUMN4) REFERENCES 부모 테이블(참조 컬럼) ON OPTION // FOREIGN KEY 제약조건 생성
);
CREATE TABLE TABLE1 AS ~ // 컬럼 및 데이터만 생성
CREATE SYNONYM TABLE1 FOR A.TABLE1 // SYNONYM 생성
// SEQUENCE 생성
CREATE SEQUENCE SEQUENCE1
INCREMENT 1 // 1씩 증가, 생략하면 1씩 증가
START WITH 1; // 1부터 시작, 생략하면 1부터 시작
ALTER
ALTER TABLE TABLE1 ADD COLUMN1 DATA_TYPE; // 컬럼 추가
ALTER TABLE TABLE1 ADD CONSTRAINT CONSTRAINT1 CONSTRAINT_TYPE; // 제약조건 추가
ALTER TABLE TABLE1 MODIFY COLUMN1 DATA_TYPE CONSTRAINT_TYPE; // 컬럼 데이터 타입, 제약조건 변경
ALTER TABLE TABLE1 DROP COLUMN COLUMN1; // 컬럼 삭제
ALTER TABLE DROP CONSTRAINT CONSTRAINT1; // 제약조건 삭제
DROP
DROP TABLE TABLE1;
TRUNCATE
TRUNCATE TABLE TABLE1; // 데이터만 삭제, LOG를 남기지 않아 ROLLBACK이 가능하지 않음
DML
INSERT
INSERT INTO TABLE1(COLUMN1) VALUES(SEQUENCE1.NEXTVAL); // 시퀀스 입력
다중 테이블 INSERT
1.
INSERT ALL
INTO TABLE1 VALUES(COLUMN1, COLUMN2, ...)
INTO TABLE2 VALUES(COLUMN1, COLUMN2, ...)
SELECT * FROM TABLE3 // TABLE1, 2에 TABLE3의 데이터를 입력
2.
INSERT ALL
WHEN ~ THEN INTO TABLE1 VALUES(COLUMN1, COLUMN2, ...)
WHEN ~ THEN INTO TABLE2 VALUES(COLUMN1, COLUMN2, ...)
ELSE INTO TABLE3 VALUES(COLUMN1, COLUMN2, ...)
SELECT * FROM TABLE4 // 조건에 해당하면 TABLE1, 2에, 조건에 해당하지 않으면 TABLE3에 TABLE4의 데이터를 입력
3.
INSERT FIRST
WHEN ~ THEN INTO TABLE1 VALUES(COLUMN1, COLUMN2, ...)
WHEN ~ THEN INTO TABLE2 VALUES(COLUMN1, COLUMN2, ...)
ELSE INTO TABLE3 VALUES(COLUMN1, COLUMN2, ...)
SELECT * FROM TABLE4 // 첫 번째 테이블에 입력이 되면 두 번째 테이블 이후의 입력을 생략(else if)
SELECT
SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY // 작성 순서
FROM - CONNECT BY - WHERE - GROUP BY - HAVING - SELECT - ORDER BY // 실행 순서
별칭은 실행 순서가 뒤인 절에서만 사용 가능(SELECT절에서 지정한 별칭은 ORDER BY절에서만 사용 가능)
ROWNUM // SELECT 하여 결과 집합을 만들 때(WHERE절이 실행될 때) 생성, ORDER BY 사용 시 순서가 변경
SELECT * FROM(SELECT ROWNUM, NOTICE.* FROM NOTICE) WHERE ROWNUM BETWEEN 6 AND 10;
// ()외부 SELECT의 ROWNUM이 적용
SELECT * FROM(SELECT ROWNUM NUM, NOTICE.* FROM NOTICE) WHERE NUM BETWEEN 6 AND 10;
// ()내부 SELECT의 ROWNUM이 적용
ORDER BY 뒤에 2개 이상의 컬럼을 사용하면 선행 컬럼을 기준으로 후행 컬럼이 정렬
DISTINCT 뒤에 2개 이상의 컬럼을 사용하면 DISTINCT 뒤에 오는 모든 컬럼에 대해 하나의 행으로 인식하여 그 행의 중복을 제거
JOIN
// ANSI INNER JOIN
SELECT * FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.COLUMN1 = T2.COLUMN1
// ORACLE INNER JOIN
SELECT * FROM TABLE1 T1, TABLE2 T2 WHERE T1.COLUMN1 = T2.COLUMN1
// ANSI OUTER JOIN
SELECT * FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 ON T1.COLUMN1 = T2.COLUMN1
// ORACLE OUTER JOIN
SELECT * FROM TABLE1 T1, TABLE2 T2 WHERE T1.COLUMN1 = T2.COLUMN1(+)
SELECT * FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 PARTITION BY(T2.COLUMN2) ON T1.COLUMN1 = T2.COLUMN1 // T1.COLUMN2 GROUP 후 조인
ORACLE SQL의 경우 FULL OUTER JOIN은 지원하지 않음
// ANSI CROSS JOIN
SELECT * FROM TABLE1 T1 CROSS JOIN TABLE2 T2 // TABLE1 * TABLE2
// ORACLE CROSS JOIN
SELECT * FROM TABLE1 T1, TABLE2 T2
// NATURAL JOIN
SELECT * FROM TABLE1 NATURAL JOIN TABLE2
// 두 테이블의 컬럼명과 데이터 타입이 같은 컬럼을 기준으로 JOIN
SELECT * FROM TABLE1 JOIN TABLE2 USING(COLUMN1, COLUMN2)
// USING절을 사용하여 기준 컬럼 선택 가능, 이때 기준 컬럼 앞에는 테이블명을 명시할 수 없음
EXISTS // 교집합
NOT EXISTS // 차집합
// IN/NOT IN으로 대체 가능
// ROW를 기준으로 합치는 작업
UNION // 합집합(같은 ROW는 하나만 남김)
MINUS // 차집합
INTERSECT //교집합
UNIONALL // 합집합 + 교집합(같은 ROW도 전부 남김)
// 합쳐지는 컬럼끼리 데이터 타입이 같아야 하며, 첫번째 ALIAS가 적용, UNIONALL을 제외한 나머지 연산에서는 중복 제거를 위해 SORT UNIQUE 연산 추가
View
// VIEW 생성
CREATE VIEW VIEW1 AS ~
// VIEW 조회
SELECT * FROM VIEW1
// VIEW 삭제
DROP VIEW VIEW1
Subquery
스칼라 서브 쿼리(Scalar Subquery) // 한 개의 row당 한 개의 컬럼값을 반환 하는 서브 쿼리(SELECT절에 사용되는 경우가 많음)
인라인 뷰(Inline View) // FROM절에 사용하는 서브 쿼리
중첩 서브 쿼리(Nested Subquery) // WHERE절에 사용하는 서브 쿼리
WITH절 // 서브 쿼리에 ALIAS 부여
WITH SQ AS {
서브 쿼리
}
SELECT * FROM SQ
// 두 번 이상 사용되는 WITH절은 결과값이 temp 영역에 저장되어 디스크 I/O 발생(서브 쿼리의 실행 비용이 큰 경우에만 WITH절을 사용하는 것이 성능상 유리)
Oracle 12.2버전부터는 PGA영역에 저장 후 용량이 부족할 경우에만 temp 영역 사용
PIVOT
SELECT * FROM(
SELECT COLUMN1, COLUMN2 FROM TABLE1
) PIVOT(COUNT(*) FOR COLUMN1 IN(VALUE1, VALUE2, VALUE3))
ORDER BY COLUMN2
// SELECT COLUMN2,
COUNT(CASE WHEN COLUMN1 = VALUE1 THEN 1 END) AS VALUE1,
COUNT(CASE WHEN COLUMN1 = VALUE2 THEN 1 END) AS VALUE2,
COUNT(CASE WHEN COLUMN1 = VALUE3 THEN 1 END) AS VALUE3
FROM TABLE1
GROUP BY COLUMN2
ORDER BY COLUMN2
UPDATE
UPDATE TABLE1 SET COLUMN1 = VALUE1 WHERE ~
UPDATE TABLE1 SET(COLUMN1, COLUMN2, COLUMN3) = (SELECT VALUE1, VALUE2, VALUE3) WHERE ~
UPDATE TABLE1 SET COLUMN1 = CASE WHEN ~ THEN 'V1' WHEN ~ THEN 'V2' ELSE 'V3' END
MERGE INTO TABLE1 T1
USING TABLE2 T2
ON T1.COLUMN1 = T2.COLUMN1
WHEN MATCHED THEN
UPDATE SET ~
DELETE WHERE ~
WHEN NOT MATCHED THEN
INSERT ~ VALUES ~
DELETE
DELETE FROM TABLE1
데이터 복구
SHOW PARAMETER UNDO_RETENTION // UNDO_RETENTION 반환(초 단위)
INSERT INTO TABLE1
SELECT * FROM TABLE1
AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '15' MINITE)
WHERE ~
TRIGGER
CREATE OR REPLACE TRIGGER TRIGGER1
AFTER INSERT OR UPDATE OR DELETE ON TABLE1
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO TABLE2 VALUES(:NEW.COLUMN1, :NEW.COLUMN2, SYSDATE)
END IF;
IF UPDATING THEN
~
END IF; // :OLD를 사용하여 변경 전 데이터 사용 가능
IF DELETING THEN
~
END IF;
END;
DCL
// GRANT, REVOKE
Built-In Datatypes
Character 형식
CHAR(N), CHAR(N CHAR) // 고정 길이 데이터에 적합, 가변 길이 데이터를 사용할 때는 공간이 낭비되는 경우가 많다.
VARCHAR2(N), VARCHAR2(N CHAR) // 가변 길이 데이터에 적합, 검색하는데 시간이 오래 걸린다.
N인 경우 byte, N CHAR인 경우 문자 길이, 영어 1byte, 한글 3byte
NCHAR(N), NVARCHAR2(N) // 다국어 데이터에 적합, N은 문자 길이, 영어, 한글 모두 2byte
최대 4000byte까지 사용 가능
CLOB // 대용량 텍스트 데이터 타입(최대 4GB)
NCLOB // 대용량 텍스트 유니코드 데이터 타입(최대 4GB)
Numeric 형식
NUMBER // NUMBER(38, *), 최대 38자리까지 사용 가능
NUMBER(N) // 최대 N자리까지 사용 가능
NUMBER(N, M) // 소수 M번째 자리까지 반올림하여 최대 N자리까지 사용 가능
Date 형식
DATE // 날짜 표현
TIMESTAMP // 날짜, 시간 표현
연산자
산술 연산자
숫자 + 문자 = 숫자 // +는 숫자 덧셈 기호
숫자 || 문자 = 문자 // ||는 문자열 덧셈 기호
논리 연산자
AND 연산자가 OR 연산자보다 우선순위가 높음
A OR B AND C는 A OR (B AND C)와 같이 연산
비교 연산자
^=(ORACLE), <>(ANSI) // !=
패턴 비교 연산자
_ // 한 자릿수
LIKE '김_' // 김씨 중 이름이 외자인 사람
정규식
[] // 하나의 문자 표현
[09] // 0 or 9
[0-9], \d // 0 ~ 9
01[016-9]-\d\d\d\d-\d\d\d\d
{} // 반복횟수
01[016-9]-\d{3, 4}-\d{4}
^ // 시작
$ // 끝
^01[016-9]-\d{3, 4}-\d{4}$
REGEXP_LIKE(COLUMN_NAME, PATTERN) // 정규식 사용 시 사용되는 함수
REGEXP_LIKE(PHONE, '^01[016-9]-\d{3, 4}-\d{4}$') // 형식과 일치하는 경우
REGEXP_LIKE(PHONE, '01[016-9]-\d{3, 4}-\d{4}') // 형식이 포함된 경우
\w // [a-zA-Z_0-9]
* // 0자 이상
+ // 1자 이상
? // 0자 또는 1자
| // 문자열 구분
\D, [^0-9] // 0-9가 아닌 것
\D\w*@\D\w*.(org|net|com)
REGEXP_LIKE(EMAIL, '^\D\w*@\D\w*.(org|net|com)$')
함수
문자열 함수
// 문자열 추출 함수
SUBSTR(문자열, 시작위치)
SELECT SUBSTR('HELLO', 3) FROM DUAL; // LLO
SUBSTR(문자열, 시작위치, 길이)
SELECT SUBSTR('HELLO', 3, 2) FROM DUAL; // LL
SUBSTRB(문자열, 시작위치), SUBSTRB(문자열, 시작위치, 길이) // byte 단위 조회
CONCAT(문자열, 문자열) // 문자열 덧셈 함수, 문자열 || 문자열
// 문자열 트림 함수
LTRIM(문자열) // 왼쪽 공백 제거
RTRIM(문자열) // 오른쪽 공백 제거
TRIM(문자열) // 양쪽 공백 제거
LOWER(문자열) // 문자열 소문자로 변경 함수
UPPER(문자열) // 문자열 대문자로 변경 함수
// 문자열 대치 함수
REPLACE(문자열, 찾는 문자열, 대치할 문자열)
SELECT REPLACE('HELLO WORLD', 'WORLD', 'SQL') FROM DUAL; // HELLO SQL
SELECT REPLACE('HELLO WORLD'', ' ', '') FROM DUAL; // HELLOWORLD
TRANSLATE(문자열, 찾는 문자, 대치할 문자)
SELECT TRANSLATE('HELLO WORLD', 'WORLD', 'SQL') FROM DUAL;
// HELLQ SQLLD, W를 S로, O를 Q로, R을 L로 변경
// 문자열 패딩 함수
LPAD(문자열, byte, 패딩 문자)
SELECT LPAD('HELLO', 10, 0) FROM DUAL; // 00000HELLO
RPAD(문자열, byte, 패딩 문자)
SELECT RPAD('HELLO', 10, 0) FROM DUAL; // HELLO00000
LPAD(문자열, byte), RPAD(문자열, byte) // 패딩 문자가 공백
INITCAP(문자열) // 첫 글자를 대문자로 바꾸는 함수
SELECT INITCAP('hello world') FROM DUAL; // Hello World
// 문자열 검색 함수
INSTR(문자열, 검색 문자열)
SELECT INSTR('HELLO', 'L') FROM DUAL; // 3, 첫 번째 위치부터 시작하여 나오는 첫 번째 L이 몇 번째 문자인지 반환
INSTR(문자열, 검색 문자열, 위치)
SELECT INSTR('HELLO', 'L', 4) FROM DUAL; // 4, 네 번째 위치부터 시작하여 나오는 첫 번째 L이 몇 번째 문자인지 반환
INSTR(문자열, 검색 문자열, 위치, 찾을 수)
SELECT INSTR('HELLO', 'L', 1, 2) FROM DUAL; // 4, 첫 번째 위치부터 시작하여 나오는 두 번째 L이 몇 번째 문자인지 반환
LENGTH(문자열) // 문자열 길이를 반환하는 함수
LENGTHB(문자열) // byte 길이를 반환하는 함수
ASCII(문자) // 코드값을 반환하는 함수
SELECT ASCII('A') FROM DUAL; // 65
CHR(코드 값) // 문자를 반환하는 함수
SELECT CHR(65) FROM DUAL; // A
숫자 함수
ABS(N) // 절대값을 반환하는 함수
SELECT ABS(35), ABS(-35) FROM DUAL; // 35, 35
SIGN(N) // 음수/양수를 알려주는 함수
SELECT SIGN(35), SIGN(-35), SIGN(0); // 양수는 1, 음수는 -1, 0은 0
// 반올림 값을 반환하는 함수
ROUND(N)
SELECT ROUND(34.456789), ROUND(34.56789) FROM DUAL; // 34, 35
ROUND(N, I)
SELECT ROUND(12.3456789, 2), ROUND(12.3456789, 3) // 12.35, 12.346
// 버림 값을 반환하는 함수
TRUNK(N)
SELECT TRUNK(17/5) FROM DUAL; // 3
TRUNK(N, I)
SELECT TRUNK(57.39, 1) FROM DUAL // 57.3
SELECT TRUNK(57.39, -1) FROM DUAL // 50
MOD(N1, N2) // 나머지를 반환하는 함수
SELECT MOD(17, 5) FROM DUAL; // 2
POWER(N1, N2) // 제곱을 반환하는 함수
SELECT POWER(5, 2) FROM DUAL; // 25
SQRT(N) // 제곱근을 반환하는 함수
SELECT SQRT(25) FROM DUAL; // 5
GREATEST(N1, N2, N3, ...) // 최댓값을 반환하는 함수
SELECT GREATEST(1, 2, 3, 4, 5) FROM DUAL; // 5
SELECT GREATEST(1, 2, 3, 4, null) FROM DUAL; // null 반환
LEAST(N1, N2, N3, ...) //최솟값을 반환하는 함수
SELECT LEAST(1, 2, 3, 4, 5) FROM DUAL; // 1
날짜 함수
// 현재 시간을 반환하는 함수
// DBMS의 시간
SYSDATE // 연월일시분초
SYSTIMESTAMP // 연월일시분초+밀리세컨드
// 사용자 세션의 시간
CURRENT_DATE // 연월일시분초
CURRENT_TIMESTAMP // 연월일시분초+밀리세컨드
// 세션 시간대와 포맷 변경
ALTER SESSION SET TIME_ZONE = '09:00'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
EXTRACT(YEAR/MONTH/DAY/HOUR/MINUTE/SECOND FROM ...) // 날짜 추출 함수
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
ADD_MONTH(날짜, 정수) // 날짜를 누적하는 함수
SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL;
MONTHS_BETWEEN(날짜, 날짜) // 날짜의 차이를 반환하는 함수
SELECT MONTHS_BETWEEN (SYSDATE, TO_DATE('2013-12-25')) FROM DUAL;
NEXT_DAY(현재 날짜, 다음 요일) // 다음 요일에 해당하는 날짜를 반환하는 함수
SELECT NEXT_DAY(SYSDATE, '토') FROM DUAL;
SELECT NEXT_DAY(SYSDATE, '토요일') FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 7) FROM DUAL;
LAST_DAY(날짜) // 월의 마지막 날짜를 알려주는 함수
SELECT LAST_DAY(SYSDATE) FROM DUAL;
// 지정된 범위에서 날짜를 반올림하는/자르는 함수
ROUND(날짜, 형식), TRUNK(날짜, 형식)
CC, YEAR, Q, MONTH, W, DAY, HH, MI // 세기, 연, 분기, 월, 주, 일 ,시, 분
SELECT ROUND(SYSDATE, 'DAY') FROM DUAL; // 시간이 정오 이후이면 내일 날짜, 이전 이면 오늘 날짜
SELECT TRUNK(SYSDATE, 'DAY') FROM DUAL; // 오늘 날짜(시간 제외)
ROUND(날짜), TRUNK(날짜) // ROUND(날짜, 'DAY'), TRUNK(날짜, 'DAY')
변환 함수
// NUMBER 형식을 문자열(VARCHAR2)로 변환
TO_CHAR(NUMBER)
TO_CHAR(NUMBER, 형식)
TO_CHAR(12345678.90, '$099,999,999.99') // $012,345,678.90
9, 0 // 숫자, 빈 자리를 채우는 문자
왼쪽 변환 대상을 오른쪽 형식에 맞추어 변환한다. 변환 대상의 자릿수와 형식의 자릿수가 같거나 변환 대상의 자릿수보다 형식의 자릿수가 더 커야한다. 형식의 자릿수가 더 큰 경우 형식 앞에 0이 있으면 나머지 자리는 0으로 변환되고 형식 앞에 0이 없으면 나머지 자리가 공백으로 변환된다.
소수점에서 0과 9의 의미는 같다. 변환 대상의 소수점 자릿수가 형식의 소수점 자릿수 보다 더 큰 경우 형식에서 0과 9는 숫자의 의미를 가지며 형식의 소수점 자릿수에 맞추어 반올림 되어 변환된다. 변환 대상의 소수점 자릿수가 형식의 소수점 자릿수 보다 더 작은 경우 형식에서 0과 9는 빈 자리를 채우는 문자의 의미를 가지며 나머지 자리는 0으로 변환된다.
// DATE 형식을 문자열(VARCHAR2)로 변환
TO_CHAR(DATETIME)
TO_CHAR(DATETIME, 형식)
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS')
TO_DATE(문자열) // 문자열을 DATE 형식으로 변환하는 함수
TO_DATE(문자열, 형식) // 문자열이 NLS_DATE_FORMAT이 아닌 경우 형식 설정
TO_DATE(SYSDATE, 'YYYY-MM-DD HH:MI:SS')
TO_TIMESTAMP(문자열) // 문자열을 TIMESTAMP 형식으로 변환하는 함수
TO_TIMESTAMP(문자열, 형식) // 문자열이 NLS_TIMESTAMP_FORMAT이 아닌 경우 형식 설정
TO_TIMESTAMP(SYSTIMESTAMP, 'YYYY-MM-DD HH:MI:SS')
TO_NUMBER(문자열) // 문자열을 NUMBER 형식으로 변환하는 함수
NULL 관련 함수
NVL(입력값, 대체값) // 입력값이 NULL일 경우에 대체값을 제공하는 함수
SELECT NVL(AGE, 0) FROM MEMBERS;
NVL2(입력값, NOT NULL 대체값, NULL 대체값) // 입력값이 NOT NULL, NULL일 경우에 대체값을 제공하는 함수
SELECT NVL2(AGE, 100/AGE, 0) FROM MEMBERS;
NULLIF(입력값1, 입력값2) // 두 값이 같은 경우 NULL, 그렇지 않은 경우 첫 번째 값을 반환하는 함수
SELECT NULLIF(AGE, 19) FROM MEMBERS;
COALESCE(입력값1, 입력값2, ..., 입력값N) // NULL이 아닌 최초의 값을 출력
DECODE(입력값, 비교값1, 출력값1, 비교값2, 출력값2, ..., 비교값이 아닌 경우 출력값) // 조건에 따른 값 선택하기
SELECT DECODE(SUBSTR(PHONE, 1, 3),
'011', 'SK',
'016', 'KT',
'기타') FROM MEMBERS;
≒
SELECT CASE COLUMN1 WHEN 'A' THEN 'B' WHEN 'C' THEN 'D' ELSE 'E' END FROM TABLE1
SELECT CASE WHEN COLUMN1 = 'A' THEN 'B' WHEN COLUMN1 = 'C' THEN 'D' ELSE 'E' END FROM TABLE1
집계 함수
COUNT()
SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME // NULL 제외 개수
SELECT COUNT(*) FROM TABLE_NAME // NULL 포함 개수
ROW_NUMBER()
ROW_NUMBER() // 정렬 이후 ROWNUM을 붙일 때 사용
SELECT ROW_NUMBER() OVER(ORDER BY HIT) FROM NOTICE; // HIT을 기준으로 정렬하고 ROWNUM을 붙인다.
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY REGDATE DESC) NUM, NOTICE.* FROM NOTICE)
WHERE NUM BETWEEN 6 AND 10;
=
SELECT * FROM(SELECT ROWNUM NUM, N.* FROM (SELECT * FROM NOTICE ORDER BY REGDATE DESC) N)
WHERE NUM BETWEEN 6 AND 10;
SELECT ROW_NUMBER() OVER(PARTITION BY WRITER_ID ORDER BY HIT) FROM NOTICE; // WRITER_ID를 기준으로 그룹화(이때 WRITER_ID를 기준으로 오름차순 정렬)한 후 HIT을 기준으로 정렬하고 ROWNUM을 붙인다.
RANK(), DENSE_RANK()
SELECT RANK() OVER(ORDER BY HIT) FROM NOTICE; // HIT을 기준으로 정렬하고 순위를 매긴다.(공동 순위가 있으면 다음 순위 생략)
SELECT DENSE_RANK() OVER(ORDER BY HIT) FROM NOTICE; // HIT을 기준으로 정렬하고 순위를 매긴다.(공동 순위가 있어도 다음 순위 인정)
SELECT DENSE_RANK() OVER(PARTITION BY WRITER_ID ORDER BY HIT) FROM NOTICE; // WRITER_ID를 기준으로 그룹화(이때 WRITER_ID를 기준으로 오름차순 정렬)한 후 HIT을 기준으로 정렬하고 순위를 매긴다.
PERCENT_RANK()
(RANK - 1) / (TOTAL_ROWS - 1)
ROLLUP()
SELECT COLUMN1, COUNT(*) FROM TABLE GROUP BY ROLLUP(COLUMN1) // GROUP BY COLUMN1 합계 + 총 합계
SELECT COLUMN1, COLUMN2, COUNT(*) FROM TABLE GROUP BY ROLLUP(COLUMN1, COLUMN2) // GROUP BY COLUMN1, COLUMN2 합계 + GROUP BY COLUMN1 합계 + 총 합계
SELECT COLUMN1, COLUMN2, COUNT(*) FROM TABLE GROUP BY ROLLUP((COLUMN1, COLUMN2)) // GROUP BY COLUMN1, COLUMN2 합계 + 총 합계
CUBE()
SELECT COLUMN1, COUNT(*) FROM TABLE GROUP BY CUBE(COLUMN) // GROUP BY COLUMN1 합계 + 총 합계
SELECT COLUMN1, COLUMN2, COUNT(*) FROM TABLE GROUP BY CUBE(COLUMN1, COLUMN2);
SELECT COLUMN1, COLUMN2, COUNT(*) FROM TABLE GROUP BY CUBE(COLUMN2, COLUMN1);
(ROLLUP 함수와 달리 컬럼의 순서가 결과에 영향을 주지 않는다.)
// SELECT COLUMN1, COLUMN2, COUNT(*) FROM TABLE GROUP BY COLUMN1, COLUMN2
UNION ALL
SELECT COLUMN1, NULL, COUNT(*) FROM TABLE GROUP BY COLUMN1
UNION ALL
SELECT NULL, COLUMN2, COUNT(*) FROM TABLE GROUP BY COLUMN2
UNION ALL
SELECT NULL, NULL, COUNT(*) FROM TABLE
ORDER BY 1, 2;
NTILE()
SELECT NTILE(N) OVER(ORDER BY COLUMN1) FROM TABLE1 // 대상 데이터를 N만큼 분할하여 N개의 숫자 부여, 대상 데이터가 N개 초과 시 1부터 중복 부여
제약조건
도메인 // 유효한 값의 범위
도메인 제약조건 // COLUMN에 도메인이 아닌 값이 올 수 없도록 하는 제약조건
NOT NULL 제약조건, DEFAULT 제약조건, CHECK 제약조건
엔티티 제약조건 // 테이블에 중복된 ROW가 없도록 하는 제약조건
PRIMARY KEY // 한 개만 설정 가능, NOT NULL, 중복X, UNIQUE INDEX 생성
UNIQUE // 두 개 이상 설정 가능, NULL 허용, 중복 X, UNIQUE INDEX 생성
FOREIGN KEY // 부모 테이블의 참조 컬럼은 PK나 UK, 참조 컬럼과 데이터 타입 및 내용이 일치해야 함
// OPTION(DELETE/UPDATE ~)
1. 학습 안내(SQL, DB, DBMS의 의미와 필요성)
SQL(Structured Query Language): DBMS에게 (구조화된 데이터를)질의하는 명령어
DBMS: '동시성, 성능, 보안' 문제 해결
DDL: CREATE/ALTER/DROP
DML: INSERT/SELECT/UPDATE/DELETE
DCL: GRANT/REVOKE
2. 오라클 DBMS 18c XE 설치하기
SQLPLUS 접속
사용자명: sys as sysdba
비밀번호:
3. SQL Developer 설치하기
4. 오라클 PDB 서버에 접속하기
PDB(가상 데이터베이스) 접속
select name from v$pdbs; // pluggable database 확인
SID: xe(CDB)에서 서비스 이름: xepdb1(PDB)으로 변경
원격 접속을 위한 설정 변경
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
호스트 이름을 데이터베이스가 설치되어 있는 pc의 ip로 변경
5. 수업용 사용자와 데이터베이스 생성하기
DBA 접속 - 저장 영역 - 테이블 스페이스 생성(기본, 임시)
- 보안 - 사용자 생성
6. MEMBER 테이블 생성하기
CREATE TABLE MEMBER(
NAME VARCHAR2(50),
AGE NUMBER,
REGDATE DATE
);
7. 오라클 데이터 형식 #1 (문자 형식)
Oracle Built-In Datatypes
Character 형식
문자를 나타낼 때 홑따옴표(') 사용
CHAR(N), CHAR(N CHAR) // 고정 길이 데이터에 적합, 가변 길이 데이터를 사용할 때는 공간이 낭비되는 경우가 많다.
VARCHAR2(N), VARCHAR2(N CHAR) // 가변 길이 데이터에 적합, 검색하는데 시간이 오래 걸린다.
N인 경우 byte, N CHAR인 경우 문자 길이, 영어 1byte, 한글 3byte
NCHAR(N), NVARCHAR2(N) // 다국어 데이터에 적합, N은 문자 길이, 영어, 한글 모두 2byte
최대 4000byte까지 사용 가능
LENGTH() // 문자 길이
LENGTHB() // byte 길이
8. 오라클 데이터 형식 #2 (숫자, 날짜 형식)
LONG // 한 TABLE당 한 개의 COLUMN에만 사용 가능(최대 2GB), 최근에는 잘 사용하지 않는다.
CLOB // 대용량 텍스트 데이터 타입(최대 4GB)
NCLOB // 대용량 텍스트 유니코드 데이터 타입(최대 4GB)
Numeric 형식
NUMBER // NUMBER(38, *), 최대 38자리까지 사용 가능
NUMBER(N) // 최대 N자리까지 사용 가능
NUMBER(N, M) // 소수 M번째 자리까지 반올림하여 최대 N자리까지 사용 가능
Date 형식
DATE // 날짜 표현
TIMESTAMP // 날짜, 시간 표현
지역 정보 확인
SELECT * FROM NLS_DATABASE_PARAMETERS
9. 테이블 수정하기(ALTER TABLE)
ALTER TABLE MEMBER MODIFY ID NVARCHAR2(20);
ALTER TABLE MEMBER DROP COLUMN AGE;
ALTER TABLE MEMBER ADD EMAIL VARCHAR2(200);
10. SQL 쿼리 연습 테이블 준비하기
부적합한 테이블명, 컬럼명(예약어, 공백을 포함한 경우 등)은 "로 감싸서 입력한다.
11. 데이터 조작하기 #1 (SELECT/INSERT)
명령어, 컬럼명은 대소문자 구분을 하지 않는다.
EXISTS // 교집합
NOT EXISTS// 차집합
// IN/NOT IN으로 대체 가능
SELECT CASE COLUMN1 WHEN 'A' THEN 'B' WHEN 'C' THEN 'D' ELSE 'E' END FROM TABLE1
SELECT CASE WHEN COLUMN1 = 'A' THEN 'B' WHEN COLUMN1 = 'C' THEN 'D' ELSE 'E' END FROM TABLE1
다중 테이블 INSERT
1.
INSERT ALL
INTO TABLE1 VALUES(COLUMN1, COLUMN2, ...)
INTO TABLE2 VALUES(COLUMN1, COLUMN2, ...)
SELECT * FROM TABLE3 // TABLE1, 2에 TABLE3의 데이터를 입력
2.
INSERT ALL
WHEN ~ THEN INTO TABLE1 VALUES(COLUMN1, COLUMN2, ...)
WHEN ~ THEN INTO TABLE2 VALUES(COLUMN1, COLUMN2, ...)
ELSE INTO TABLE3 VALUES(COLUMN1, COLUMN2, ...)
SELECT * FROM TABLE4 // 조건에 해당하면 TABLE1, 2에, 조건에 해당하지 않으면 TABLE3에 TABLE4의 데이터를 입력
3.
INSERT FIRST
WHEN ~ THEN INTO TABLE1 VALUES(COLUMN1, COLUMN2, ...)
WHEN ~ THEN INTO TABLE2 VALUES(COLUMN1, COLUMN2, ...)
ELSE INTO TABLE3 VALUES(COLUMN1, COLUMN2, ...)
SELECT * FROM TABLE4 // 첫 번째 테이블에 입력이 되면 두 번째 테이블 이후의 입력을 생략(else if)
12. 데이터 조작하기 #2 (UPDATE/DELETE)
MERGE INTO TABLE1 T1
USING TABLE2 T2
ON T1.COLUMN1 = T2.COLUMN1
WHEN MATCHED THEN
UPDATE SET ~
DELETE WHERE ~
WHEN NOT MATCHED THEN
INSERT ~ VALUES ~
TRUNCATE TABLE TABLE_NAME; // 테이블 초기화, ROLLBACK 불가능
13. 트랜잭션 처리를 위한 COMMIT과 ROLLBACK
트랜잭션: 업무 실행 단위, 논리적인 명령 단위
작업 중이면 LOCK이 걸리고 COMMIT이나 ROLLBACK이 되어야 UNLOCK이 된다.
14. 연산을 통한 데이터 조회(산술 연산자)
DUAL // 더미테이블
숫자 + 문자 = 숫자(다른 언어에서 숫자 + 문자 = 문자인 것과 차이가 있음) // +는 숫자 덧셈 기호
|| 문자열 덧셈 기호
숫자 || 문자 = 문자
15. 비교 연산자
!=, ^=(oracle), <>(ansi) // 같지 않다는 의미
16. 관계 연산자
BETWEEN N AND M: N이상 M이하
17. 패턴 비교 연산자
LIKE '박_': 박씨 중 이름이 외자인 사람
_: 자릿수 한정
18. 정규식을 이용한 패턴 비교
[]: 하나의 문자 표현
[016789]: 0 or 1 or 6 or 7 or 8 or 9
[0-9], \d: 0~9
01[016-9]-\d\d\d\d-\d\d\d\d
{}: 반복횟수
01[016-9]-\d{3, 4}-\d{4}
^ // 시작
$ // 끝
^01[016-9]-\d{3, 4}-\d{4}$ // 정확하게 ^$ 사이에 있는 형식, ^$가 없으면 해당 형식이 포함되기만 하면 되는 경우
REGEXP_LIKE(COLUMN, PATTERN): 정규식 사용 시 사용되는 함수
REGEXP_LIKE(PHONE, 01[016-9]-\d{3, 4}-\d{4})
19. 문자열 비교를 위한 정규식
\w // [a-zA-Z_0-9]
* // 0자 이상
+ // 1자 이상
? // 0자 또는 1자
| // 문자열 구분
\D, [^0-9] // 0-9가 아닌 것
\D\w*@\D\w*.(org|net|com)
20. ROWNUM 그리고 행 제한하기
ROWNUM: SELECT 하여 결과 집합을 만들 때 생성
SELECT * FROM(SELECT ROWNUM, NOTICE.* FROM NOTICE) WHERE ROWNUM BETWEEN 6 AND 10;
()바깥 부분의 ROWNUM이 적용된다.
SELECT * FROM(SELECT ROWNUM NUM, NOTICE.* FROM NOTICE) WHERE NUM BETWEEN 6 AND 10;
()안쪽 부분의 ROWNUM이 적용된다.
21. 중복 값 제거하기 DISTINCT
하나의 컬럼을 조회할 때만 사용 가능
22. 중간 요약과 함수 단원 안내
23. 문자열 내장 함수 #1
// 문자열 추출 함수
SUBSTR(문자열, 시작위치, 길이)
SELECT SUBSTR('HELLO', 1, 3) FROM DUAL; // HEL
SUBSTR(문자열, 시작위치)
SELECT SUBSTR('HELLO', 3) FROM DUAL; // LLO
SUBSTRB(문자열, 시작위치, 길이), SUBSTRB(문자열, 시작위치) // byte 단위 조회
SELECT SUBSTRB('HELLO', 3) FROM DUAL; // LLO
CONCAT(문자열, 문자열) // 문자열 덧셈 함수, 문자열 || 문자열
// 문자열 트림 함수
LTRIM(문자열) // 왼쪽 공백 제거
RTRIM(문자열) // 오른쪽 공백 제거
TRIM(문자열) // 양쪽 공백 제거
// 문자열 소문자 또는 대문자로 변경 함수
LOWER(문자열) // 문자열 소문자로 변경 함수
UPPER(문자열) // 문자열 대문자로 변경 함수
// 문자열 대치 함수
SELECT REPLACE(문자열, 찾는 문자열, 대치할 문자열) FROM DUAL;
SELECT REPLACE('WHERE WE ARE', 'WE', 'YOU') FROM DUAL; // WHERE YOU ARE
SELECT REPLACE('WHERE WE ARE', ' ', '') FROM DUAL; // WHEREWEARE(\n 제거 시 CHR(10))
SELECT TRANSLATE(문자열, 찾는 문자, 대치할 문자) FROM DUAL;
SELECT TRANSLATE('WHERE WE ARE', 'WE', 'YOU') FROM DUAL; // W를 Y로, E를 O로 변경, 'YHORO YO ARO'
24. 문장열 내장 함수 #2
// 문자열 패딩 함수
LPAD(문자열, 길이, 패딩 문자) // byte 단위 조회
SELECT LPAD('HELLO', 10, 0) FROM DUAL; // 00000HELLO
RPAD(문자열, 길이, 패딩 문자)
SELECT RPAD('HELLO', 10, 0) FROM DUAL; // HELLO00000
LPAD(문자열, 길이), RPAD(문자열, 길이) // 패딩 문자가 공백
한글인 경우 길이가 글자 수 * 2
INITCAP(문자열) // 첫 글자를 대문자로 바꾸는 함수
SELECT INITCAP('hello world') FROM DUAL; // Hello World
INSTR(문자열, 검색 문자열, 위치, 찾을 수) // 문자열 검색 함수
SELECT INSTR('HELLO', 'L') FROM DUAL; // 3, 첫 번째 위치부터 시작하여 첫 번째 L이 몇 번째 문자인지 반환
SELECT INSTR('HELLO', 'L', 4) FROM DUAL; // 4, 네 번째 위치부터 시작하여 나오는 L이 몇 번째 문자인지 반환
SELECT INSTR('HELLO', 'L', 1, 2) FROM DUAL; // 4, 첫 번째 위치부터 시작하여 두 번째 L이 몇 번째 문자인지 반환
LENGTH(문자열) // 문자열 길이를 얻는 함수
ASCII(문자) // 코드값을 반환하는 함수
SELECT ASCII('A') FROM DUAL; // 65
CHR(코드 값) // 코드 값으로 문자를 반환하는 함수
SELECT CHR(65) FROM DUAL; // A
25. 숫자 내장 함수
ABS(N) // 절대값을 반환하는 함수
SELECT ABS(35), ABS(-35) FROM DUAL; // 35, 35
SIGN(N) // 음수/양수를 알려주는 함수
SELECT SIGN(35), SIGN(-35), SIGN(0); // 양수는 1, 음수는 -1, 0은 0
// 숫자의 반올림 값을 반환하는 함수
ROUND(N)
SELECT ROUND(34.456789), ROUND(34.56789) FROM DUAL; // 34, 35
ROUND(N, I)
SELECT ROUND(12.3456789, 2), ROUND(12.3456789, 3) // 12.35, 12.346
// 숫자의 버림값을 반환하는 함수
TRUNK(N)
SELECT TRUNK(17/5) FROM DUAL; // 3
TRUNK(N, I)
SELECT TRUNK(57.39, 1) FROM DUAL // 57.3
SELECT TRUNK(57.39, -1) FROM DUAL // 50
MOD(N1, N2) // 숫자의 나머지를 반환하는 함수
SELECT MOD(17, 5) FROM DUAL; // 2
POWER(N1, N2) // 숫자의 제곱을 반환하는 함수
SELECT POWER(5, 2) FROM DUAL; // 25
SQRT(N) // 숫자의 제곱근을 반환하는 함수
SELECT SQRT(25) FROM DUAL; // 5
GREATEST(N1, N2, N3, ...) // 최댓값을 반환하는 함수
SELECT GREATEST(1, 2, 3, 4, 5) FROM DUAL; // 5
SELECT GREATEST(1, 2, 3, 4, null) FROM DUAL; // null 반환
LEAST(N1, N2, N3, ...) //최솟값을 반환하는 함수
SELECT LEAST(1, 2, 3, 4, 5) FROM DUAL; // 1
26. 날짜 함수
// 현재 시간을 얻는 함수
// DBMS의 시간
SYSDATE // 연월일시분초
SYSTIMESTAMP // 연월일시분초+밀리세컨드
// 사용자의 세션 시간
CURRENT_DATE // 연월일시분초
CURRENT_TIMESTAMP // 연월일시분초+밀리세컨드
세션 시간과 포맷 변경
ALTER SESSION SET TIME_ZONE = '09:00'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
EXTRACT(YEAR/MONTH/DAY/HOUR/MINUTE/SECOND FROM ...) // 날짜 추출 함수
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; // 년
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(MINUTE FROM SYSDATE) FROM DUAL;
SELECT EXTRACT(SECOND FROM SYSDATE) FROM DUAL;
ADD_MONTH(날짜, 정수) // 날짜를 누적하는 함수
SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL; // 한 달 이후
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL; // 한 달 이전
MONTHS_BETWEEN(날짜, 날짜) // 날짜의 차이를 알아내는 함수
SELECT MONTHS_BETWEEN (SYSDATE, TO_DATE('2013-12-25')) FROM DUAL;
NEXT_DAY(현재 날짜, 다음 요일) // 다음 요일에 해당하는 날짜를 알려주는 함수
SELECT NEXT_DAY(SYSDATE, '토') FROM DUAL;
SELECT NEXT_DAY(SYSDATE, '토요일') FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 7) FROM DUAL;
LAST_DAY(날짜) // 월의 마지막 날짜를 알려주는 함수
SELECT LAST_DAY(SYSDATE) FROM DUAL; // SYSDATE이 속한 달의 마지막 날짜
// 지정된 범위에서 날짜를 반올림하는/자르는 함수
ROUND(날짜, 형식), TRUNK(날짜, 형식)
SELECT ROUND(SYSDATE, 'CC'), TRUNK(SYSDATE, 'CC') FROM DUAL; // 세기
SELECT ROUND(SYSDATE, 'YEAR'), TRUNK(SYSDATE, 'YEAR') FROM DUAL; // 년
SELECT ROUND(SYSDATE, 'Q'), TRUNK(SYSDATE, 'Q') FROM DUAL; // 분기
SELECT ROUND(SYSDATE, 'MONTH'), TRUNK(SYSDATE, 'MONTH') FROM DUAL; // 월
SELECT ROUND(SYSDATE, 'W'), TRUNK(SYSDATE, 'W') FROM DUAL; // 주
SELECT ROUND(SYSDATE, 'DAY'), TRUNK(SYSDATE, 'DAY') FROM DUAL; // 일
SELECT ROUND(SYSDATE, 'HH'), TRUNK(SYSDATE, 'HH') FROM DUAL; // 시
SELECT ROUND(SYSDATE, 'MI'), TRUNK(SYSDATE, 'MI') FROM DUAL; // 분
ROUND(날짜), TRUNK(날짜) // 일
SELECT TRUNK(SYSDATE) FROM DUAL; // 현재 날짜(시간 제외)
27. 형식 변환 함수
TO_CHAR() // 숫자, 날짜를 문자로 변환
TO_CHAR(NUMBER) // NUMBER 형식을 문자열(VARCHAR2)로 변환
TO_CHAR(NUMBER, 형식)
TO_CHAR(1234567890, '$099,999,999.99') // $012,345,678.90
9, 0 // 숫자, 빈 자리를 채우는 문자
왼쪽 변환 대상을 오른쪽 형식에 맞추어 변환한다. 변환 대상의 자릿수와 형식의 자릿수가 같거나 변환 대상의 자릿수보다 형식의 자릿수가 더 커야한다. 형식의 자릿수가 더 큰 경우 형식 앞에 0이 있으면 나머지 자리는 0으로 변환되고 형식 앞에 0이 없으면 나머지 자리가 공백으로 변환된다.
소수점에서 0과 9의 의미는 같다. 변환 대상의 소수점 자릿수가 형식의 소수점 자릿수 보다 더 큰 경우 형식에서 0과 9는 숫자의 의미를 가지며 형식의 소수점 자릿수에 맞추어 반올림 되어 변환된다. 변환 대상의 소수점 자릿수가 형식의 소수점 자릿수 보다 더 작은 경우 형식에서 0과 9는 빈 자리를 채우는 문자의 의미를 가지며 나머지 자리는 0으로 변환된다.
TO_CHAR(DATETIME) // DATE 형식을 문자열(VARCHAR2)로 변환
TO_CHAR(DATETIME, 형식)
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS')
TO_DATE(문자열) // 문자열을 날짜 형식으로 변환하는 함수
TO_DATE(문자열, 형식) // 문자열이 NLS_DATE_FORMAT이 아닌 경우 형식 설정
TO_DATE(SYSDATE, 'YYYY-MM-DD HH:MI:SS')
TO_TIMESTAMP(문자열) // 문자열을 날짜 형식으로 변환하는 함수
TO_TIMESTAMP(문자열, 형식) // 문자열이 NLS_TIMESTAMP_FORMAT이 아닌 경우 형식 설정
TO_TIMESTAMP(SYSTIMESTAMP, 'YYYY-MM-DD HH:MI:SS')
TO_NUMBER(문자열) // 문자열을 숫자 형식으로 변환하는 함수
28. NULL 관련 함수와 DECODE 함수
NVL(입력값, 대체값) // 입력값이 NULL일 경우에 대체값을 제공하는 함수
SELECT NVL(AGE, 0) FROM MEMBERS;
NVL2(입력값, NOT NULL 대체값, NULL 대체값) // 입력값이 NOT NULL, NULL일 경우에 대체값을 제공하는 함수
SELECT NVL2(AGE, 100/AGE, 0) FROM MEMBERS;
NULLIF(입력값1, 입력값2) // 두 값이 같은 경우 NULL, 그렇지 않은 경우 첫 번째 값을 반환하는 함수
SELECT NULLIF(AGE, 19) FROM MEMBERS;
COALESCE(COLUMN_NAME1, COLUMN_NAME2, ..., COLUMN_NAMEN) // NULL이 아닌 최초의 컬럼값을 출력
DECODE(입력값, 비교값1, 출력값1, 비교값2, 출력값2, ..., 비교값이 아닌 경우 출력값) // 조건에 따른 값 선택하기
SELECT DECODE(SUBSTR(PHONE, 1, 3),
'011', 'SK',
'016', 'KT',
'기타') FROM MEMBERS;
29. SELECT 구절과 정렬(ORDER BY)
SELECT문의 구절
SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY
ORDER BY에서 ,로 구분하여 정렬 기준을 추가할 수 있다.
30. 집계 함수와 GROUP BY
SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME // NULL 제외 개수
SELECT COUNT(*) FROM TABLE_NAME // NULL 포함 개수
SELECT COLUMN1, COUNT(*) FROM TABLE GROUP BY ROLLUP(COLUMN1) // GROUP BY COLUMN1 합계 + 총 합계
SELECT COLUMN1, COLUMN2, COUNT(*) FROM TABLE GROUP BY ROLLUP(COLUMN1, COLUMN2) // GROUP BY COLUMN1, COLUMN2 합계 + GROUP BY COLUMN1 합계 + 총 합계
SELECT COLUMN1, COLUMN2, COUNT(*) FROM TABLE GROUP BY ROLLUP((COLUMN1, COLUMN2)) // GROUP BY COLUMN1, COLUMN2 합계 + 총 합계
SELECT COLUMN1, COUNT(*) FROM TABLE GROUP BY CUBE(COLUMN) // GROUP BY COLUMN1 합계 + 총 합계
SELECT COLUMN1, COLUMN2, COUNT(*) FROM TABLE GROUP BY CUBE(COLUMN1, COLUMN2);
SELECT COLUMN1, COLUMN2, COUNT(*) FROM TABLE GROUP BY CUBE(COLUMN2, COLUMN1);
(ROLLUP 함수와 달리 컬럼의 순서가 결과에 영향을 주지 않는다.)
// SELECT COLUMN1, COLUMN2, COUNT(*) FROM TABLE GROUP BY COLUMN1, COLUMN2
UNION ALL
SELECT COLUMN1, NULL, COUNT(*) FROM TABLE GROUP BY COLUMN1
UNION ALL
SELECT NULL, COLUMN2, COUNT(*) FROM TABLE GROUP BY COLUMN2
UNION ALL
SELECT NULL, NULL, COUNT(*) FROM TABLE
ORDER BY 1, 2;
SELECT NTILE(N) OVER(ORDER BY COLUMN1) FROM TABLE1 // 대상 데이터를 N만큼 분할하여 N개의 숫자 부여, 대상 데이터가 N개 초과 시 1부터 중복 부여
누적 합계
실행순서
FROM - CONNECT BY - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
뒤에서 지은 별칭은 앞에서 쓸 수 없다.(SELECT절에서 지정한 ALIAS를 GROUP BY절에서 사용할 수 없다.)
31. HAVING 절
32. ROW_NUMBER(), RANK(), DENSE_RANK()
ROWNUM // WHERE 절에서 생성되며 ORDER BY 사용 시 순서가 바뀐다.
ROW_NUMBER() // 정렬 이후 ROWNUM을 붙일 때 사용
SELECT ROW_NUMBER() OVER(ORDER BY HIT) FROM NOTICE; // HIT을 기준으로 정렬하고 ROWNUM을 붙인다.
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY REGDATE DESC) NUM, NOTICE.* FROM NOTICE)
WHERE NUM BETWEEN 6 AND 10;
=
SELECT * FROM(SELECT ROWNUM NUM, N.* FROM (SELECT * FROM NOTICE ORDER BY REGDATE DESC) N)
WHERE NUM BETWEEN 6 AND 10;
SELECT ROW_NUMBER() OVER(PARTITION BY WRITER_ID ORDER BY HIT) FROM NOTICE; // WRITER_ID를 기준으로 그룹화(이때 WRITER_ID를 기준으로 오름차순 정렬)한 후 HIT을 기준으로 정렬하고 ROWNUM을 붙인다.
SELECT RANK() OVER(ORDER BY HIT) FROM NOTICE; // HIT을 기준으로 정렬하고 순위를 매긴다.(공동 순위가 있으면 다음 순위 생략)
SELECT DENSE_RANK() OVER(ORDER BY HIT) FROM NOTICE; // HIT을 기준으로 정렬하고 순위를 매긴다.(공동 순위가 있어도 다음 순위 인정)
SELECT DENSE_RANK() OVER(PARTITION BY WRITER_ID ORDER BY HIT) FROM NOTICE; // WRITER_ID를 기준으로 그룹화(이때 WRITER_ID를 기준으로 오름차순 정렬)한 후 HIT을 기준으로 정렬하고 순위를 매긴다.
33. 부조회(서브쿼리)
수학 연산에서 소괄호를 사용하여 연산의 순서를 바꾸듯이 먼저 실행해서 결과를 남겨야 하는 경우 소괄호를 사용하여 나타낸다.
스칼라 서브 쿼리(Scalar Subquery) : 한 개의 레코드 당 한 개의 값을 반환 하는 쿼리
인라인 뷰(Inline View) : FROM절에 사용하는 서브 쿼리
중첩 서브 쿼리(Nested Subquery) : WHERE절에 사용하는 서브 쿼리
WITH절 // 서브 쿼리에 ALIAS 부여
WITH SQ AS {
서브 쿼리
}
SELECT * FROM SQ
두 번 이상 사용되는 WITH절은 결과값이 temp 영역에 저장되어 디스크 I/O 발생(서브 쿼리의 실행 비용이 큰 경우에만 WITH절을 사용하는 것이 성능상 유리)
Oracle 12.2버전부터는 PGA영역에 저장 후 용량이 부족할 경우에만 temp 영역 사용
34. INNER 조인
서로 관계가 있는 레코드들은 INNER
서로 관계가 없는 레코드들은 OUTER
INNER JOIN: 참조키를 기준으로 일치하는 행만 조인(관계가 있는 레코드들만 합치는 조인,) OUTER 제외 후 자식 레코드 수 많큼 부모 레코드 복제
35. LEFT/RIGTH/FULL OUTER 조인
OUTER JOIN: 참조키를 기준으로 일치하지 않는 행도 포함시키는 조인, INNER JOIN에서 OUTER에 해당 하는 레코드 추가, 왼쪽 테이블의 OUTER가 추가되면 LEFT OUTER JOIN, 오른쪽 테이블의 OUTER가 추가되면 RIGHT OUTER JOIN, 왼쪽 테이블의 OUTER와 오른쪽 테이블의 OUTER가 모두 추가되면 FULL OUTER JOIN, OUTER가 추가되지 않는 부분의 테이블 내용은 NULL값이 된다.
SELECT * FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 PARTITION BY(T1.COLUMN1) ON T1.ID = T2.ID // T1.COLUMN1으로 GROUP 후 조인
36. OUTER JOIN을 이용한 게시글 목록 조회
37. SELF JOIN
한 테이블의 데이터가 서로 포함 관계를 가지는 경우 사용
SELECT M.*, B.NAME BOSS_NAME FROM MEMBER M LEFT OUTER JOIN MEMBER B ON M.BOSS_ID = B.ID
38. 오라클 OLD JOIN
ANSI INNER JOIN
SELECT N.ID, N.TITLE, M.NAME FROM MEMBER M INNER JOIN NOTICE N ON M.ID = N.WRITER_ID
ORACLE INNER JOIN
SELECT N.ID, N.TITLE, M.NAME FROM MEMBER M, NOTICE N WHERE M.ID = N.WRITER_ID
ANSI OUTER JOIN
SELECT N.*, M.NAME WRITER_NAME FROM NOTICE N LEFT OUTER JOIN MEMBER M ON M.ID = N.WRITER_ID
Oracle OUTER JOIN
SELECT N.*, M.NAME WRITER_NAME FROM NOTICE N, MEMBER M WHERE M.ID(+) = N.WRITER_ID
Oracle SQL의 경우 FULL OUTER JOIN은 지원하지 않음
ANSI CROSS JOIN
SELECT N.*, M.NAME WRITER_NAME FROM NOTICE N CROSS JOIN MEMBER M
Oracle CROSS JOIN
SELECT N.*, M.NAME WRITER_NAME FROM NOTICE N, MEMBER M
NATURAL JOIN
SELECT * FROM TABLE1 NATURAL JOIN TABLE2 USING(COLUMN1, COLUMN2)
// 두 테이블의 컬럼명과 데이터 타입이 같은 컬럼을 기준으로 JOIN, USING절을 사용하여 기준 컬럼 선택 가능
이때 기준 컬럼 앞에는 테이블명을 명시할 수 없음
39. 유니온(UNION)
// ROW를 기준으로 합치는 작업
UNION // 합집합(같은 ROW는 하나만 남김)
MINUS // 차집합
INTERSECT //교집합
UNIONALL // 합집합 + 교집합(같은 ROW도 전부 남김)
40. View의 의미와 생성 방법
VIEW 생성
CREATE VIEW VIEW1 AS ~
VIEW 조회
SELECT * FROM VIEW1
VIEW 삭제
DROP VIEW VIEW1
41. 데이터 딕셔너리
SELECT * FROM DICT; // 데이터 딕셔너리 조회
DBA_, USER_, ALL_ // 권한
SELECT * FROM USER_TABLES; // USER 권한의 TABLE
SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE1'; // USER 권한의 TABLE1의 COLUMN
SELECT * FROM USER_CONSTRAINT WHERE TABLE_NAME = 'TABLE1';
42. 도메인 제약조건
도메인 // 유효한 값의 범위
도메인 제약조건 // COLUMN에 도메인이 아닌 값이 올 수 없도록 하는 제약조건
NOT NULL 제약조건, DEFAULT 제약조건, CHECK 제약조건
43. 체크 제약조건
CHECK(PHONE LIKE '010-%-____')
44. 정규식을 이용한 체크 제약조건
CHECK(REGEXP_LIKE(PHONE, '^010-\d{3, 4}-\d{4}$'))
ALTER TABLE DROP CONSTRAINT CONSTRAINT1;
45. Entity 제약조건(Primary Key, Unique)
엔티티 제약조건 // 테이블에 중복된 ROW가 없도록 하는 제약조건
PRIMARY KEY(NOT NULL, 중복X), UNIQUE(NULL 허용, 중복 X), UNIQUE INDEX 생성
CONSTRAINT PK_NAME PRIMARY KEY(PK_COLUMN)
CONSTRAINT UNIQUE_NAME UNIQUE(UNIQUE_COLUMN)
FK 설정 시 부모 테이블이 생성되어 있어야 하며 참조 컬럼은 PK나 UK로 설정되어 있어야 한다. 또한 FK와 참조 컬럼은 데이터 타입과 내용이 일치해야 함
CONSTRAINT FK_NAME FOREIGN KEY(FK_COLUMN) REFERENCES 부모 테이블(참조 컬럼)
46. 시퀀스(Sequence)
SEQUENCE // 일련번호
CREATE SEQUENCE NOTICE_ID_SEQ(시퀀스 명) INCREMENT 1(1씩 증가, 생략하면 1씩 증가) START WITH 1(1부터 시작, 생략하면 1부터 시작);
INSERT INTO NOTICE(ID, TITLE, WRITER_ID) VALUES(NOTICE_ID_SEQ.NEXTVAL, ' ', ' ')
SEQUENCE값을 자동으로 증가시켜 입력하기 위해서 열 시퀀스 사용
SYNONYM
CREATE SYNONYM TABLE1 FOR A.TABLE1
트리거
CREATE OR REPLACE TRIGGER TRIGGER1
AFTER INSERT OR UPDATE OR DELETE ON TABLE1
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO TABLE2 VALUES(:NEW.COLUMN1, :NEW.COLUMN2, SYSDATE)
END IF;
IF UPDATING THEN
~
END IF; // :OLD를 사용하여 변경 전 데이터 사용 가능
IF DELETING THEN
~
END IF;
END;
데이터 복구
SHOW PARAMETER UNDO_RETENTION // UNDO_RETENTION 반환(초 단위)
INSERT INTO TABLE1
SELECT * FROM TABLE1
AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '15' MINITE)
WHERE ~
정규화
제1 정규형
원자성 // 모든 속성은 반드시 하나의 값만 가져야 한다.(다중 값, 반복 그룹X)
제2 정규형
부분 종속 // 모든 속성은 반드시 모든 기본키에 종속되어야 한다.(기본키 일부에만 종속되어서는 안됨)
제3 정규형
이행 종속 // 기본키가 아닌 모든 속성간에는 서로 종속될 수 없다.
반정규화
1. 테이블 반정규화
테이블 병합 // 비지니스 로직 상 JOIN 되는 경우가 많아 통합하는 것이 성능 측면에서 유리할 경우 고려
1) 1:1 관계 테이블 병합
2) 1:M 관계 테이블 병합
3) 슈퍼 서브 타입 테이블 병합
테이블 분할
1) 수직 분할 // 컬럼 단위로 테이블을 1:1 분리
2) 수평 분할 // row 단위로 테이블 분리
테이블 추가
1) 중복 테이블 추가 // 타 업무 또는 타 서버에 있는 테이블과 동일한 구조의 테이블 추가, 원격 JOIN 방지
2) 통계 테이블 추가 // 통계값을 미리 계산해서 저장하는 테이블 추가
3) 이력 테이블 추가 // 마스터 테이블에 존재하는 row를 트랜잭션 발생 시점에 따라 복사해두는 테이블 추가
4) 부분 테이블 추가 // 자주 조회되는 컬럼들만 별도로 모아놓은 테이블 추가
2. 컬럼 반정규화
중복 컬럼 추가 // JOIN 프로세스를 줄이기 위해 중복 컬럼 추가, SELECT 비용은 감소하나 UPDATE 비용은 증가
파생 컬럼 추가 // 계산을 통해 얻어지는 결과값을 테이블에 컬럼으로 저장
이력 테이블 컬럼 추가 // 이력 테이블에 기능성 컬럼 추가(최신 여부, 시작일/종료일 등)
3. 관계 반정규화
중복관계 추가 // 데이터 처리를 위해 여러 경로를 거쳐야할 경우 관계를 중복시킴으로써 성능 개선
'Web Programming' 카테고리의 다른 글
| Unable to compile class for JSP (0) | 2021.11.14 |
|---|---|
| 테스트 (0) | 2021.06.26 |
| 배포 (0) | 2021.06.21 |
| CGI를 이용하여 아파치(Apache)와 파이썬(Python) 연동하기 (0) | 2021.01.15 |