데이터 조작어(DML)

오라클은 사용자가 수동으로 COMMIT 해야 해서 ROLLBACK이 가능하다.

SQL Server(기본적으로 자동 커밋)는 AUTO COMMIT OFF를 하면 DML, DDL 모두 사용자가 수동으로 COMMIT해야 한다.

  • SELECT
  • INSERT
  • UPDATE
  • DELETE : ROLLBACK으로 복구가 가능하다.
  • MERGE

 

데이터 정의어(DDL)

오라클은 자동으로 COMMIT 되어서 ROLLBACK이 불가능하다.

SQL Server(기본적으로 자동 커밋)는 AUTO COMMIT ON를 하면 DML, DDL 모두 자동으로 COMMIT 된다.

  • CREATE
  • ALTER
    • 오라클 : ALTER TABLE 테이블명 MODIFIY (컬럼명1 데이터유형 [DEFAULT 식] [NOT NULL], 컬럼명2 데이터유형 [DEFAULT 식] [NOT NULL] ...);
    • SQL Server : ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 데이터유형 [DEFAULT 식] [NOT NULL];
    • SQL Server는 여러 개의 컬럼에 대한 정의를 한 번에 바꿀 수 없다.
  • DROP
    • ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
    • 오라클에서는 CASCADE를 지원한다.
    • SQL Server는 CASCADE를 지원하지 않는다. 따라서 외래키 제약조건을 삭제한 후 테이블을 삭제해야 한다.
  • RENAME
  • TRUNCATE : 시스템 활용 측면에서 시스템 부하가 적다.

 

DELETE ACTION

  • CASCADE : 부모 삭제 시 자식도 같이 삭제
  • SET NULL : 부모 삭제 시 자식 필드를 NULL로 설정함
  • SET DEFAULT : 부모 삭제 시 자식 필드 디폴트 값으로 설정함
  • RESTRICT : 자식 테이블이 참조하고 있는 경우 삭제가 불가능함
  • NO ACTION : 참조 무결성을 위반하는 삭제/수정 액션을 취하지 않음

 

INSERT ACTION

  • AUTOMATIC : 부모 테이블에 PK 값이 없는 경우 부모 PK를 생성 후 자식을 입력
  • SET NULL : 부모 테이블에 PK 값이 없는 경우 자식 외부키를 NULL로 설정함
  • SET DEFAULT : 부모 테이블에 PK 값이 없는 경우 자식 외부키를 디폴트 값으로 설정함
  • DEPENDENT : 부모 테이블에 PK 값이 존재할 때만 자식 입력할 수 있음
  • NO ACTION : 참조 무결성을 위반하는 입력 액션을 취하지 않음

 

기본키 설정하는 4가지 방법

  • 테이블 생성 시 바로 PK 설정(단, PK로 만들 속성이 단일 속성일 경우)
CREATE TABLE 테이블 (
	컬럼1 VARCHAR(10) PRIMARY KEY,
    컬럼2 VARCHAR(10)
);
  • 테이블 먼저 생성 후 제약조건으로 추가
CREATE TABLE 테이블 (
	컬럼1 VARCHAR(10),
    컬럼2 VARCHAR(10)
);

ALTER TABLE 테이블 ADD CONSTRAINT 제약조건명 PRIMARY KEY (컬럼1);
  • 테이블 생성 시 동시에 PK 설정(PK 만들 속성의 개수 상관 없음)
CREATE TABLE 테이블 (
	컬럼1 VARCHAR(10) NOT NULL,
    컬럼2 VARCHAR(10),
    PRIMARY KEY (컬럼1)
);
  • 테이블 생성 시 동시에 PK 설정(PK 만들 속성의 개수 상관 없음, 이름 지정 가능)
CREATE TABLE 테이블 (
	컬럼1 VARCHAR(10) NOT NULL,
    컬럼2 VARCHAR(10),
    CONSTRAINT 제약조건명 PRIMARY KEY (컬럼1)
);

 

WITH GRANT OPTION

  • 해당 권한을 특정 사용자에게도 부여할 수 있는 권한 부여
  • 객체에 대한 권한 (SELECT, INSERT, UPDATE, DELETE 등)

 

WITH ADMIN OPTION

  • 지정된 테이블에 대한 모든 권한을 부여하고 특정 사용자에게도 부여할 수 있는 권한 부여
  • 시스템에 대한 권한 (CREATE TABLE, DROP USER 등) 

 

데이터 제어어(DCL)

  • GRANT
    • GRANT 권한명 ON 객체명 TO 유저명
  • REVOKE
    • REVOKE 권한명 ON 객체명 FROM 유저명
    • WITH GRANT OPTION을 REVOKE 하면 해당 사용자가 다른 사용자에게 부여한 권한도 모두 회수한다.
    • WITH ADMIN OPTION을 REVOKE 하면 해당 사용자의 권한만 회수한다.

 

ROLE

  • ROLE을 이용하면 권한 부여와 회수를 쉽게 할 수 있다.
  • ROLE은 CREATE ROLE 권한을 가진 유저에 의해 생성된다.
  • 한 사용자가 여러 개의 ROLE을 가질 수 있고, 여러 사용자들에게 동일한 ROLE을 부여할 수 있다.
  • GRANT와 REVOKE로 유저에게 ROLE을 부여하고 취소가 가능하다.
  • 유저는 ROLE에 ROLE을 부여하는 것도 가능하다.

 

VIEW의 장점

  • 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용프로그램은 변경하지 않아도 된다.
  • 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.
  • 보안성 : 숨기고 싶은 정보가 있다면 뷰를 생성할 시에 해당 컬럼을 빼고 생성하여 사용자에게 정보를 감출 수 있다.

 

트랜잭션 제어어(TCL)

  • COMMIT
  • ROLLBACK
  • SAVEPOINT : 동일 이름을 지정하면 덮어쓴다.

 

트랜잭션의 특성

  • 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되든지 아니면 전혀 실행되지 않은 상태로 남아있어야 한다.
  • 고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
  • 지속성 : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.
  • 일관성 : 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 일관성 있는 데이터베이스를 유지해야만 한다.

 

순수 관계 연산자

  • SELECT
  • PROJECT
  • JOIN
  • DIVISION

 

일반 집합 연산자

  • UNION : 합집합, 중복을 배제한다.
  • UNION ALL : 합집합, 중복 데이터를 모두 가져온다.
  • INTERSECT : 교집합, 중복을 배제한다.
  • EXCEPT(SQL Server) 또는 MINUS(오라클) : 차집합, 중복을 배제한다.

 

테이블명, 컬럼명 명명 규칙

  • 테이블명과 컬럼명은 반드시 문자로 시작해야한다.
  • A-Z, a-z, 0-9, 특수문자는 '_', '$', '#'만 사용이 가능하다.

 

SQL문 작성 주의사항

  • 문자 상수, 날짜 상수 표현 시 작은따옴표를 사용한다.
  • 오라클은 빈 문자열('') 입력 시 NULL로 입력된다.
  • SQL Server는 빈 문자열('') 입력 시 그대로 빈 문자열이 입력된다.
  • 문자와 숫자를 비교할 때 문자를 숫자로 파싱이 가능해서(TO_NUMBER 함수) 비교할 수 있으면 SQL문이 올바르게 실행된다.
  • 오라클은 문자 상수의 대소문자를 구분한다.
  • MSSQL, MySQL은 기본적으로 문자 상수의 대소문자를 구분하지 않는다.
  • SQL 문 연산 순서 : FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
  • ORDER BY 절에서만 SELECT 에서 사용된 별칭을 사용할 수 있다.
  • 오라클의 시간/분/초 표현 방법
    • 1/24 : 1시간 (1일을 24시간으로 나누기)
    • 1.5/24 : 1시간 30분 (1.5일을 24시간으로 나누기)
    • 1/24/60 : 1분 (1일을 24시간으로 나누고 60분으로 나누기)
    • 1/24(60/10) : 10분 (1일을 24시간으로 나누고 6분으로 나누기)
    • 1.5/24/(60/10) : 15분 (1.5일을 24시간으로 나누고 6분으로 나누기)
    • 1/24/60/60 : 1초 (1일을 24시간으로 나누고 60분으로 나누고 60초로 나누기)

 

AS(Alias)

  • Column Header를 변경하는 기능을 한다.
  • 쌍따옴표를 공백, 특수문자를 표현할 때 사용해야만 한다.
  • 오라클에서는 별칭을 사용할 때 AS를 사용할 수 없다. (생략해서 쓰면 됨)
  • SQL Server는 사용해도 되고, 생략해도 된다.

 

FROM 절

  • 오라클에서는 FROM절을 생략할 수 없다. 의미상 테이블이 필요 없다면 DUAL 테이블을 사용한다. (... FROM DUAL;)

 

단일행 함수

문자 함수

  • LOWER(대상)
  • UPPER(대상)
  • SUBSTR(대상, m, n)
  • INSTR(대상, 찾을문자열, m, n) : m위치에서 시작해서 n번째 발견된 문자열의 위치를 반환한다. m이 음수일 경우 스캔 방향 또한 바뀐다.
  • LTRIM(대상, 삭제문자열) : 삭제할 문자열을 왼쪽에서 삭제한다.
  • RTRIM(대상, 삭제문자열) : 삭제할 문자열을 오른쪽에서 삭제한다.
  • TRIM(대상) : 양쪽에서 공백을 삭제한다.
  • LPAD(대상, n, 문자열) : 대상의 왼쪽에 대상의 총 길이가 n이 되도록 문자열을 추가한다.
  • RPAD(대상, n, 문자열) : 대상의 오른쪽에 대상의 총 길이가 n이 되도록 문자열을 추가한다.
  • CONCAT(대상1, 대상2) : 대상1 뒤에 대상2를 이어붙인다.
  • LENGTH(대상) : 대상의 총 길이를 반환한다.
  • REPLACE(대상, 찾을문자열, 바꿀문자열) : 찾을문자열을 바꿀문자열로 치환한다.
  • TRANSLATE(대상, 찾을문자열, 바꿀문자열) : 1대1로 치환한다. 3번째 인자값을 생략하면 NULL이 반환된다.

 

숫자 함수

  • ABS(숫자)
  • ROUND(숫자, 자릿수) : 숫자를 자릿수까지 반올림한다. 자릿수가 음수이면 정수자리에서 반올림한다.
    • ROUND(123.456, 2) -> 123.46
    • ROUND(123.456, -2) -> 100
    • ROUND(272, -1) -> 270
  • TRUNC(숫자, 자릿수) : 숫자를 자릿수까지 유지하고 나머지는 버린다.
  • SIGN(숫자) : 양수면 1, 음수면 -1, 0이면 0을 반환한다.
  • FLOOR(숫자) : 작거나 같은 최대 정수를 반환한다.
  • CEIL(숫자) : 크거나 같은 최소 정수를 반환한다.
  • MOD(숫자1, 숫자2) : 숫자1을 숫자2로 나눈 나머지를 반환한다.
  • POWER(숫자1, 숫자2) : 숫자1을 숫자2만큼 거듭제곱한 수를 반환한다.
  • SQRT(숫자) : 제곱근을 반환한다.

 

날짜 함수

  • SYSDATE : 현재 날짜와 시간을 반환한다.
    • 2025/02/27 14:30:00
  • CURRENT_DATE : 현재 날짜를 반환한다.
    • 2025/02/27
  • CURRENT_TIMESTAMP : 현재 타임스탬프를 반환한다.
    • 2025/02/27 14:30:00 +09:00
  • ADD_MONTHS(날짜, n) : 날짜에서 n개월 후의 날짜와 시간을 반환한다. n이 음수인 경우 n개월 이전 날짜와 시간을 반환한다.
    • ADD_MONTHS(SYSDATE, 3) -> 2025/05/27 14:30:00
  • MONTHS_BETWEEN(날짜1, 날짜2) : 날짜1과 날짜2 사이의 개월 수를 반환한다.
  • LAST_DAY(날짜) : 날짜의 마지막 날짜와 시간을 반환한다.
  • NEXT_DAY(날짜, n) : 날짜 이후의 n 요일의 첫 번째 날짜와 시간을 반환한다. (n=1 일요일, n=2 월요일, n=3 화요일, n=7 토요일)

 

변환 함수

  • TO_NUMBER(문자) : 숫자 타입으로 변경한다.
  • TO_CHAR(대상, 포맷) : 대상이 날짜인 경우 날짜의 포맷을 변경한다. 대상이 숫자인 경우 숫자의 포맷을 변경한다.
    • TO_CHAR(SYSDATE, 'MM/DD-YYY') -> 02/25-2025
    • TO_CHAR(9000, '9,999') -> 9,000 : (천 단위 구분자)
    • TO_CHAR(9000, '09999') -> 09000 : (5자리로 반환하기 위해 대상의 앞을 0으로 채운다)
  • TO_DATE(문자, 포맷) : 문자를 포맷에 맞게 날짜와 시간을 반환한다.
    • TO_DATE('2025/02/25', 'YYYY/MM/DD') -> 2025/02/25 00:00:00
  • FORMAT(날짜, 포맷) : 날짜의 포맷을 변경하여 문자로 반환한다.
  • CAST(대상 AS 데이터타입) : 대상을 데이터타입으로 변경한다.

 

일반 함수

  • DECODE(대상, 값1, 리턴1, 값2, 리턴2, ..., 기본리턴값) : 대상이 값1이면 리턴1을 반환하고, 값2이면 리턴2를 반환하고, 매칭되지 않으면 기본리턴값을 반환한다. (기본리턴값을 생략하면 NULL이 반환된다.)
  • NVL(대상, 치환값) : 대상이 NULL이면 치환값을 반환한다.
  • NVL2(대상, 치환값1, 치환값2) : 대상이 NULL이면 치환값2로 치환하고, NULL이 아니면 치환값1로 치환한다.
  • COALESCE(대상1, 대상2, ..., 기본리턴값) : 대상들 중 가장 처음으로 NULL이 아닌 값을 반환한다. 모두 다 NULL이라면 기본리턴값을 반환한다.
  • ISNULL(대상, 치환값) : 대상이 NULL이면 치환값을 반환하고, NULL이 아니면 대상을 반환한다. (SQL Server 함수)
  • NULLIF(대상1, 대상2) : 두 값이 같으면 NULL을 반환하고, 다르면 대상1을 반환한다.

 

CASE문

SELECT
    CASE 
        WHEN expression = value1 THEN result1
        WHEN expression = value2 THEN result2
        ELSE default_result
    END
FROM table;


/* 축약형 */
SELECT
    CASE expression
        WHEN value1 THEN result1
        WHEN value2 THEN result2
        ELSE default_result
    END
FROM table;

 

복수행(다중행) 함수

집계 함수

NULL을 제외하고 연산하는 것이 원칙이다. (단, COUNT(*) == COUNT(1) == COUNT(0) ... 은 예외적으로 NULL을 포함해서 카운트한다.)

  • COUNT(대상)
  • SUM(대상)
  • AVG(대상)
  • MIN(대상)
  • MAX(대상)
  • MAX(대상)
  • VARIANCE(대상) : 분산 반환한다.
  • STDDEV(대상) : 표준편차 반환한다.

 

그룹 함수

  • GROUPING SETS(컬럼1, 컬럼2) : 각 인수들의 GROUP BY 결과를 합친 결과이다.
    • GROUPING SETS(()) 또는 GROUPING SETS(NULL)은 전체에 대한 총합 행을 출력한다.
    • GROUPING SETS의 인수는 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 동일하다.
  • ROLLUP(컬럼1, 컬럼2) : GROUPING SETS((컬럼1, 컬럼2)) + GROUPING SETS(컬럼1) + GROUPING SETS(NULL) 이다.
    • ROLLUP의 인수는 계층구조이므로 인수의 순서가 바뀌면 결과도 바뀌게 된다.
  • CUBE(컬럼1, 컬럼2) : GROUPING SETS((컬럼1, 컬럼2)) + GROUPING SETS(컬럼1) + GROUPING SETS(컬럼2) + GROUPING SETS(NULL) 이다.
    • CUBE의 인수는 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 동일하다.
    • ORDER BY를 사용해서 정렬할 수 있다.
    • 연산이 매우 많다.

 

윈도우 함수

  • PARTITION BY : GROUP BY와 비슷한 역할을 한다. 기준 컬럼에 따라서 윈도우 처리 범위를 모은다.
  • ORDER BY : 정렬작업으로 행과 행 사이의 관계를 정의해주는 역할을 한다. ORDER BY를 먼저 처리하고 윈도우 함수를 적용하자.
  • WINDOWING 절
    • ROWS : 현재 ROW 위치에서 물리적인 범위
    • RANGE : 현재 ROW 값을 기준으로 논리적인 범위
    • UNBOUNDED PRECENDING : 처음 행부터 포함
    • n PRECENDING : 현재 행에서 n개 이전까지 포함
    • CURRENT ROW : 현재 행 포함
    • n FOLLOWING : 현재 행에서 n개 이후까지 포함
    • UNBOUNDED FOLLOWING : 마지막 행까지 포함
  • RANK : 중복하는 등수가 존재하면, 다음 등수는 건너뛰어 매겨진다. (1, 2, 3, 3, 5)
  • DENSE_RANK : 중복하는 등수가 존재해도 다음 등수는 연속적으로 매겨진다. (1, 2, 3, 3, 4)
  • ROW_NUMBER : 중복하는 등수를 허용하지 않는다. (1, 2, 3, 4, 5)
  • FIRST_VALUE : 윈도우에서 가장 처음에 나오는 값
  • LAST_VALUE : 윈도우에서 가장 나중에 나오는 값
  • LAG(컬럼, 숫자1, 숫자2) : 파티션에서 값에 대해서 현재행 이전 숫자1의 행을 구한다. 그 행이 없다면 숫자2(디폴트값)를 반환한다.
  • LEAD(컬럼, 숫자1, 숫자2) : 파티션에서 값에 대해서 현재행 이후 숫자1(오프셋)의 행을 구한다. 그 행이 없다면 숫자2(디폴트값)를 반환한다.
  • NTILE(n) : 파티션 별로 n등분하여 조를 나눈다. 이때 나머지는 상위 조에게 순서대로 배정되게 한다.
  • RATIO_TO_REPORT : 파티션 내 주어진 컬럼 값의 합계에 대한 행별 백분율을 소수점으로 구하는 함수
  • PERCENT_RANK : 파티션 별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나온느 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구하는 함수
  • CUME_DIST : 파티션 별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구하는 함수

 

WHERE 절

  • 집계 함수를 사용할 수 없다.
  • AND와 OR로 여러 조건을 동시에 전달할 수 있다.

연산자

  • =
  • !=, <>, ^= : 같지 않다.
  • >
  • >=
  • <
  • <=
  • BETWEEN a AND b
  • IN(a, b, c) : a 또는 b 또는 c인 조건을 검색한다.
  • LIKE
    • % : 자릿수 제한이 없는 모든 문자를 의미한다.
    • _ : 한 자릿수를 의미하여 하나의 문자를 의미한다.
    • ENAME LIKE 'S%' : S로 시작하는 ENAME
    • ENAME LIKE '%S' : S로 끝나는 ENAME
    • ENAME LIKE '_S%' : 두 번째 글자가 S로 시작하는 ENAME
  • ESCAPE
    • LIKE와 같이 쓰인다.
    • 문자열을 검색할 때 '_', '%'와 같은 와일드카드를 진짜 문자열로 검색하고 싶을 때 사용한다.
    • ENAME LIKE 'A_#%' ESCAPE '#' : '#' 기호 뒤의 '%'는 와일드카드가 아니라 내가 검색하길 원하는 특수문자이다.
  • IS NULL
  • IS NOT NULL
  • a AND b
  • a OR B
  • NOT a

 

연산자 우선 순위

  1. 비교 연산자(=, !=, <, > 등)
  2. SQL 연산자(IS NULL, LIKE, BETWEEN, IN, EXISTS 등)
  3. NOT
  4. AND
  5. OR

 

GROUP BY 절

  • 여러개의 행들을 그룹으로 묶는다고 생각하자.
  • GROUP BY 절에 포함되지 않은 컬럼을 SELECT 절에 사용할 수 없다. (집계 함수로는 사용이 가능하다.)
  • 그룹화를 한 이후의 조건문은 HAVING 절에서 사용해야 한다.
  • 별칭 사용이 불가능하다.

 

HAVING 절

  • 집계 함수 결과를 조건으로 사용할 때 사용한다.
  • SELECT 절에서 사용된 별칭을 사용할 수 없다.
  • GROUP BY 수행 후 HAVING 절에 만족하는 그룹이 없으면 아무것도 출력하지 않는다.(공집합을 출력한다.)
  • HAVING 절은 GROUP BY 절을 사용하지 않아도 집계 함수와 함께 사용할 수 있다.

 

ORDER BY 절

  • 출력될 행의 순서를 변경하기 위해서 사용한다.
  • ASC(오름차순), DESC(내림차순)으로 순서를 결정할 수 있다. (생략 시 오름차순으로 정렬된다.)
  • GROUP BY가 포함되어 있으면 SELECT List, GROUP BY List에 포함된 컬럼만 사용할 수 있다.
  • 유일하게 SELECT 절에서 정의한 별칭을 사용할 수 있다.
  • SELECT 절에 선언된 컬럼 순서대로의 숫자를 전달할 수 있다.
  • 오라클은 NULL을 포함한 값을 오름차순 정렬 시 NULL을 마지막에 배치한다. 내림차순 정렬 시 NULL을 처음에 배치한다. (NULLS LAST, NULLS FIRST로 변경 가능하다.)
  • SQL Server는 NULL을 포함한 값을 정렬 시 NULL을 처음에 배치한다. 내림차순 정렬 시 NULL을 마지막에 배치한다.

 

JOIN

  • FROM 절에 조인할 테이블을 나열한다.
  • 오라클 표준은 테이블 나열 순서가 중요하지 않지만, ANSI 표준은 OUTER JOIN 시 테이블 나열 순서가 중요하다.
  • 오라클 표준은 WHERE 절에서 조인 조건을 작성한다.
  • N개의 테이블을 조인하려면 최소 N-1개의 조인 조건이 필요하다.

 

JOIN 종류

  • 조건의 형태에 따른 구분
    • EQUI JOIN : NULL 끼리 같은 것은 결과로 표현되지 않는다.
    • NON EQUI JOIN
  • 조인 결과에 따른 구분
    • INNER JOIN
    • OUTER JOIN : LEFT, RIGHT, FULL
  • NATURAL JOIN
  • CROSS JOIN
  • SELF JOIN

 

EQUI JOIN

SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1, 테이블2
WHERE 테이블1.컬럼 = 테이블2.컬럼;

 

NON EQUI JOIN

SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1, 테이블2
WHERE 테이블1.컬럼 비교조건 테이블2.컬럼;

 

 

SELF JOIN

SELECT T1.컬럼1, T2.컬럼1
FROM 테이블 T1, 테이블 T2
WHERE T1.컬럼2 = T2.컬럼1;

 

 

표준 JOIN

  • ANSI 표준으로 작성되는 INNER JOIN, OUTER JOIN, CROSS JOIN, NATURAL JOIN 을 말한다.

 

INNER JOIN

  • 조인 조건이 일치하는 행들을 출력한다.
  • 오라클 표준에서의 기본 조인이다.
  • ANSI 표준의 경우는 FROM 절에 INNER JOIN 또는 JOIN 키워드를 명시한다.
  • ANSI 표준은 USING 또는 ON 조건절을 반드시 사용해야 한다.

 

ON 절

  • 조인할 컬럼명이 서로 다르더라도 사용할 수 있다.
  • 괄호를 생략할 수 있다.
  • 컬럼명이 같을 경우에는 테이블 이름 또는 별칭을 사용해서 명확하게 지정해야 한다.
  • ON 절에서는 조인 조건을 명시하고, WHERE 절에서는 일반 조건을 명시한다.
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1 INNER JOIN 테이블2
ON 테이블1.조인컬럼 = 테이블2.조인컬럼;

 

USING 절

  • 조인할 컬럼명이 같을 경우에만 사용할 수 있다.
  • 별칭이나 테이블 이름과 같은 접두사를 사용할 수 없다.
  • 괄호를 반드시 사용해야 한다.
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1 INNER JOIN 테이블2
USING (동일한컬럼명);

 

 

OUTER JOIN

  • JOIN 조건에서 동일한 값이 없는 행도 결과 테이블에 포함하기 위해 사용한다.
  • 테이블 기준 방향에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN으로 구분한다.
  • OUTER는 생략 가능하다.

 

LEFT OUTER JOIN

  • 오라클 표준에서는 WHERE 절에서 기준이 되는 테이블의 반대 테이블의 조건 컬럼 뒤에 (+)를 붙인다.
  • 왼쪽 테이블은 모두 다 결과 테이블에 포함하고 오른쪽 테이블에서 같은 값이 없는 경우 NULL로 컬럼을 채운다.
  • Outer의 대상이 되는 테이블(LEFT)의 조건절이 ON 절에 위치하면 원하는 결과가 출력되지 않는다.
/* 오라클 표준 */
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1, 테이블2
WHERE 테이블1.조인컬럼명 = 테이블2.조인컬럼명(+);

/* ANSI 표준 */
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1 LEFT OUTER JOIN 테이블2
ON 테이블1.조인컬럼명 = 테이블2.조인컬럼명;

 

FULL OUTER JOIN

  • FULL OUTER JOIN은 LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과의 UNION 연산과 동일하다. (오라클에서 FULL OUTER JOIN을 사용하려면 이 방식으로 사용해야 한다.)
  • LEFT OUTER JOIN의 결과와 RIGHT OUTER JOIN의 결과를 동시에 출력하되, 중복 데이터는 한 번만 포함된다.
  • 오라클 표준에서 FULL OUTER JOIN은 없다. ((+) 기호를 양 쪽 테이블에 모두 명시할 수 없다.)
  • 성능적으로도 좋지 않다.

 

NATURAL JOIN

  • 두 테이블 간의 동일한 컬럼명들에 대해 EQUI JOIN을 수행한다.
  • USING, ON, WHERE 절에서 조건을 정의할 수 없다.
  • 조인에 사용되는 컬럼들은 서로 데이터 유형이 동일해야 하고, 접두사를 사용할 수 없다.
  • NULL 값의 경우에는 서로 같더라도 결과 테이블에 포함되지 않는다.
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1 NATURAL JOIN 테이블2;

 

CROSS JOIN

  • 두 테이블 간에 JOIN 조건이 없는 경우 생성 가능한 모든 데이터들의 조합을 출력한다. (카테시안 곱)
  • NULL 값 또한 결과 테이블에 포함된다.
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1 CROSS JOIN 테이블2;

 

 

TOP N 쿼리

  • 페이징 처리를 효과적으로 수행하기 위해 사용한다.
  • 전체 결과에서 특정 N개를 추출한다.
  • 오라클에서 행의 번호를 나타내는 가상 컬럼은 ROWNUM 이다.
  • SQL Server에서 상위 행을 출력하는 함수는 TOP 함수이다.

 

ROWNUM

  • 출력된 데이터를 기준으로 행 번호가 부여된다.
  • 서브쿼리를 이용해서 정렬을 먼저 하고 난 후에 수행해야 한다.
/* 급여가 높은 순서대로 상위 5명의 직원 출력 */
SELECT *
FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= 5
ORDER BY sal DESC;

 

TOP 함수

  • 별도의 서브쿼리를 이용해서 정렬하지 않고 ORDER BY 절의 정렬 후 원하는 데이터를 추출할 수 있다.
  • WITH TIES 옵션은 동일한 데이터가 있을 경우 함께 출력하는 옵션이다.
/* 급여가 높은 순서대로 상위 2명의 직원 출력 */
SELECT TOP(2) ename, sal
FROM emp
ORDER BY sal DESC;

/* 급여가 높은 순서대로 상위 2명의 직원을 출력하는데 두 번째로 높은 급여의 직원이 여러명이라면 같이 출력 */
SELECT TOP(2) WITH TIES ename, sal
FROM emp
ORDER BY sal DESC;

 

OFFSET, FETCH

  • 출력될 행의 수를 제한한다.
  • ORDER BY 절 뒤에 사용한다.
  • 오라클 12 C 버전 이상부터 지원한다.
  • SQL Server도 지원한다.
  • OFFSET은 N개의 행을 건너뛴다.
  • FETCH는 M개의 행을 가져온다.
ORDER BY 컬럼 OFFSET N {ROW | ROWS}
FETCH {FIRST | NEXT} M {ROW | ROWS} ONLY

 

계층형 질의

  • 부모와 자식과 같은 구조를 계층형 구조라고 부른다.
  • 오라클에서 지원하고, SQL Server에서는 지원하지 않는다.
  • START WITH 절을 먼저 보고, CONNECT BY 절을 보고 WHERE 절을 보면 된다.
  • START WITH : 계층 구조 전개의 시작 위치이다. 무조건 결과 테이블에 포함된다.
  • CONNECT BY : 다음에 전개될 자식 데이터를 지정한다.
  • PRIOR : CONNECT BY 절, SELECT 절, WHERE 절에 사용되며, 이전 레벨 데이터를 지정한다. PRIOR이 붙은 쪽이 부모 역할을 한다고 보면 된다!
    • 부모 = PRIOR 자식 : 순방향
    • 자식 = PRIOR 부모 : 역방향
    • PRIOR 자식 = 부모 : 순방향 (프자부순)
    • PRIOR 부모 = 자식 : 역방향 (프부자역)
    • 자식 쪽에 PRIOR가 붙어 있으면 순방향이다.
  • WHERE : 모든 전개 수행 후에 지정된 조건을 만족하는 데이터만을 추출한다.
  • ORDER SIBLINGS BY : 계층구조를 유지하면서 정렬한다. 즉, 계층 끼리 비교한다.

 

가상 컬럼

  • LEVEL : 루트 데이터가 1이면 하위 데이터는 2이다. 리프 데이터까지 1씩 증가한다.
  • CONNECT_BY_ISLEAF : 전개 과정에서 해당 데이터가 리프 데이터이면 1, 아니면 0이다.
  • SYS_CONNECT_BY_PATH(컬럼명, '경로분리자') : 루트 데이터로부터 현재 전개할 데이터까지의 경로를 표시한다.
  • CONNECT_BY_ROOT(컬럼명) : 현재 전개할 데이터의 루트 데이터를 표시한다.

 

서브 쿼리

  • 스칼라 서브 쿼리 : 서브 쿼리의 결과가 1행, 1열인 서브쿼리를 의미한다.
    • SELECT 절에 사용하는 서브 쿼리이다. 
    • ORDER BY를 사용할 수 없다.
    • 메인 쿼리의 컬럼을 사용할 수 없다.
  • 단일 행 서브 쿼리 : 서브 쿼리의 결과가 단 1건 이하인 서브 쿼리를 의미한다. =, <, > 등 비교 연산자를 사용한다.
  • 다중 행 서브 쿼리 : 서브 쿼리의 결과가 여러 행인 서브 쿼리를 의미한다. IN, ALL, ANY, SOME, EXIST 비교 연산자를 사용한다.
    • IN : 서브 쿼리의 결과 중 하나라도 일치하면 조건이 TRUE
    • ANY, SOME : 서브 쿼리의 결과와 하나 이상 일치하면 조건이 TRUE
    • ALL : 서브 쿼리의 결과와 모두 일치하면 조건이 TRUE
    • EXISTS : 데이터의 존재 여부를 확인할 때 사용함. 존재하면 TRUE
  • 다중 컬럼 서브 쿼리 : 서브 쿼리의 결과로 여러 컬럼을 반환하는 경우이다.

 

INLINE VIEW(인라인 뷰) == DYNAMIC VIEW(동적 뷰)

  • FROM 절에 SELECT 문을 사용하여 가상의 테이블을 만드는 효과를 말한다.
  • FROM 절에 사용하는 서브 쿼리이다.
  • 인라인 뷰에서는 메인 쿼리의 값을 사용할 수 없다.
  • 메인 쿼리에서 인라인 뷰의 컬럼을 사용할 수 있다.
  • 인라인 뷰에서 집계 함수를 사용하게 될 경우 별칭을 반드시 지정해야만 한다.
  • 대소 비교가 가능해진다.

 

제약 조건

  • UNIQUE KEY
  • NOT NULL
  • PRIMARY KEY : UNIQUE & NOT NULL
  • CHECK : 입력 값 범위를 제한한다.
  • FOREIGN KEY : NULL을 허용한다.

 

PIVOT

  • 행을 열로 바꾼다.
SELECT *
FROM 테이블
PIVOT (집계함수(대상) FOR 피벗할컬럼 IN (열1, 열2, ...));

 

UNPIVOT

  • 열을 행으로 바꾼다.
SELECT *
FROM 테이블
UNPIVOT (대상컬럼 FOR 피벗해제할컬럼 IN (열1, 열2, ...)

 

정규표현식

  • 메타 문자와 리터럴 문자로 구분된다.
  • 메타 문자 : 문자 그 자신이 가진 의미가 아니라 다른 의미로 사용되는 문자이다.
    • \ : 메타 문자를 리터럴 문자로 표시할 때 사용. Escape Character
    • \d : 숫자
    • \D : 숫자가 아닌 모든 것
    • \w : 글자(영어, 숫자, 한글)와 _(언더바)
    • \W : 글자가 아닌 모든 것(특수기호(언더바가 포함되지 않는다.) 또는 공백)
    • \s : 공백
    • \t : 탭
    • \n : 개행
    • [[:digit:]] : 숫자
    • [[:alpha:]] : 문자
    • [[:alnum:]] : 숫자와 문자
    • [[:punct:]] : 특수기호(언버바를 포함한다.)
    • ^ : 개행으로 나뉜 문자열의 시작 지점
    • $ : 개행으로 나뉜 문자열의 끝 지점
    • . : 임의의 하나의 문자
    • ? : 선행 문자가 1개 이하(0개 또는 1개)
    • * : 선행 문자가 0개 이상
    • + : 선행 문자가 1개 이상
    • {n} : n회
    • {n,} : n회 이상
    • {n,m} : n회 이상 m회 이
    • [ ] : 대괄호 안의 문자들 중 하나와 일치
    • [ ^ ] : 대괄호 안의 문자들을 제외한 나머지 문자들 중 하나와 일치
    • [ - ] : 연속 문자의 범위를 지정
    • ( ) : 소괄호로 묶인 표현식을 한 단위로 다룸
  • 리터럴 문자 : 문자 그 자체가 가진 의미 그대로 사용되는 문자로, 정규표현식에서 패턴 매칭을 수행할 때 처리되는 최소 단위이다.
  • 오라클 10g 버전부터 완전한 정규표현식을 지원한다. 
    • REGEXP_LIKE(대상, 찾을문자열, [옵션]) : WHERE 절에서만 사용이 가능하다.
    • REGEXP_REPLACE(대상, 찾을문자열, [바꿀문자열], [검색위치], [발견횟수], [옵션])
    • REGEXP_INSTR(대상, 찾을문자열, [시작위치], [발견횟수])
    • REGEXP_SUBSTR(대상, 패턴, [검색위치], [발견횟수], [옵션], [추출그룹번호])오라클 10g 버전부터 완전한 정규표현식을 지원한다.
    • REGEXP_COUNT(대상, 찾을문자열, [시작위치], [옵션])
      • 옵션은 아래와 같다.
      • c : 대소문자를 구분하여 검색
      • i : 대소문자를 구분하지 않고 검색
      • m : 패턴을 다중라인으로 선언 가능
  • SQL Server는 완전한 정규표현식을 지원하지 않고, 유사한 패턴 문자열을 지원한다. PATINDEX 함수와 LIKE 연산자에 사용할 수 있다.
    • % : 0개 이상의 문자
    • _ : 임의의 한 문자
    • [ ] : 대괄호 안의 문자들 중 하나와 일치
    • [ - ] : 연속 문자의 범위를 지정
    • [ ^ ] : 대괄호 안의 문자들을 제외한 나머지 문자들 중 하나와 일치
저작자표시 비영리 변경금지 (새창열림)

티스토리툴바