SQL 기본

관계형 데이터베이스 개요

SQL 문장들의 종류

• DML (Data Manipulation Language: 데이터 조작어)
  • 데이터베이스 테이블의 데이터를 삽입, 수정, 조회, 삭제 하는 명령어
  • SELECT, INSERT, UPDATE, DELETE
• DDL (Data Definition Language: 데이터 정의어)
  • 테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어
  • CREATE, ALTER, DROP, RENAME
• DCL (Data Control Language: 데이터 제어어)
  • 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어
  • GRANT, REVOKE
• TCL (Transaction Control Language: 트랜잭션 제어어)
  • 논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어
  • COMMIT, ROLLBACK

순수 관계 연산자

• SELECT
• PROJECT
• (NATURAL) JOIN
• DIVIDE

일반 집합 연산자

• UNION
• INTERSECTION
• DIFFERENCE
• PRODUCT(CROSS JOIN)

SELECT 문

• SELECT [ALL, DISTINCT] "조회 컬럼1", "조회 컬럼2" ... FROM "조회 테이블1", "조회 테이블2", ...
  • ALL: Default 옵션으로 중복된 데이터가 있어도 모두 출력
  • DISTINCT: 중복된 데이터가 있는 경우 1건으로 처리해서 출력

실행순서

• FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

ALIAS(AS)

• 컬럼명 바로 뒤에 위치
• 컬럼명과 ALIAS 사이에 AS 키워드 사용 가능
  • e.g. SELECT COL1 AS C1
• 이중 인용부호는 ALIAS가 공백, 특수문자를 포함하는 경우나 대소문자 구분이 필요할 때 사용

합성연산자

• || (Oracle) -> 문자열1 || 문자열2
• • (SQL Server) -> 문자열1 + 문자열2
• CONCAT -> CONCAT(문자열1, 문자열2)

함수

LTRIM

• LTRIM(인자1, 인자2)
• '인자1' 문자열의 왼쪽 첫 문자부터 확인해서 '인자2' 값인 지정문자가 나타나면 나타나지 않을때까지 계속해서 지운다.

SELECT LTRIM('xxYYxZ', 'x')
FROM TBL1;

결과는 'YYxZ'이다.

CASE WHEN

• CASE WHEN 조건1 THEN 결과1 WHEN 조건2 THEN 결과2 ELSE 기본값 END
• 각 조건이 참이면 해당하는 결과를 반환하고 어느 조건에도 해당되지 않으면 기본값으로 나온다

SELECT 	CASE 
          WHEN COL1 >= 90 THEN 'A'
          WHEN COL1 >= 80 THEN 'B'
          ELSE 'F'
      	END
FROM TBL1

DECODE (ORACLE에서만 사용이 가능)

• DECODE(컬럼, 값1, 결과1, 값2, 결과2, ..., 기본값)
• 컬럼이 각각에 설정한 값(값1, 값2 ....) 와 같다면 바로 바로 옆의 결과의 값으로 나온다

SELECT 
    DECODE(COL1, 
        30, '만점', 
        0, 'A',  
        'F'
    ) AS 등급
FROM TBL1;

NVL(Oracle), ISNULL(SQL Server)

• NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2)
• 표현식1의 결괏값이 NULL이면 표현식2의 값을 출력

SELECT NVL(COL1, 20)
FROM TBL1;

NULLIF

• NULLIF(표현식1, 표현식2)
• 표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴

SELECT NULLIF(COL1, 30)
FROM TBL1;

COALESCE

• COALESCE(표현식1, 표현식2, ....)
• 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다

COALESCE('A', 'B', NULL) => A
COALESCE(NULL, 'B', 'A') => B
COALESCE(NULL, NULL, NULL) => NULL

WHERE 절

• 데이터베이스에서 조회되는 데이터에 대한 조건을 설정하여 원하는 데이터만을 검색하기 위해 사용하는 절

특징

• 집계 함수를 사용할 수 없다.
• FROM절 다음에 위치

조건식 구성

• 칼럼명
• 비교 연산자
  • 부정 비교 연산자
    • "!=" : 같지 않다
    • "^=" : 같지 않다
    • "<>" : 같지 않다
    • "NOT 칼럼명 = ~" : ~와 같지 않다
    • "NOT 칼럼명 > ~" : ~보다 크지 않다
• 문자, 숫자, 표현식
• 비교 칼럼명

NULL 값과의 연산

• 특정 값보다 크다, 적다라고 표현할 수 없다
• NULL 값과의 비교연산 (=, >, <, >=, <=)은 FALSE(거짓)을 리턴

GROUP BY, HAVING 절

특징

• GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용
• 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행
• GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.
• GROUP BY 절은 WHERE 절보다 늦게 실행된다.
• HAVING 절은 GROUP BY절로 만들어진 소그룹 집계 데이터 중 제한 조건을 두는 명령어이다.
• HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.
• GROUP BY 절 없이 HAVING 절만 있다고 오류가 생기진 않는다.

ORDER BY 절

특징

• 기본적인 정렬 순서는 오름차순(ASC)
• 오라클에서는 NULL을 최댓값, SQL Server에서는 최솟값
• SELECT절에서 오직 한 개만 올 수 있다.
• 날짜형 데이터 타입은 오름차순으로 정렬했을 경우 날짜 값이 가장 이른 값이 먼저 출력된다.
  • e.g. 20240101 은 20240901 보다 먼저 출력된다.
• 집합 연산자를 사용한 SQL에서는 최종 결과를 정렬하며, 가장 마지막 줄에 한번만 사용 가능하다.

조인

특징

• 여러 테이블로부터 원하는 데이터를 조회하기 위해서는 전체 테이블 개수에서 최소 N-1 개 만큼의 JOIN 조건 필요
• 일반적으로 조인은 PK와 FK의 연관성에 의해 성립된다.(어떤 경우 논리적인 값들의 연관만으로도 성립됨)
• DBMS 옵티마이저는 FROM절에 나열된 데이터들을 항상 2개로 묶어서 처리
• EQUI JOIN은 조인에 관여하는 테이블들의 값이 정확하게 일치할 때 = 사용된다. 이외는 NON EQUI JOIN임(설계상 불가능한 경우가 있다)

종류

종류	설명
INNER JOIN	동일한 값만 반환 디폴트 값, 쉼표 혹은 조건절로 수행
NATURAL JOIN	동일한 이름의 컬럼에 대해 수행
USING/ON 조건절	원하는 컬럼 조건 단, EQUI 또는 NATURAL JOIN에서 USING 절을 사용하는 경우 ALIAS를 사용할 수 없다.
CROSS JOIN	카타시안 조합
OUTER 조인	(+) 표기 A.COL1=B.COL2(+) 인 경우 A LEFT JOIN B 이다.

SQL 활용

서브쿼리

특징

• 메인쿼리에 속해 있으며 부모와 자식같은 계층적인 관계
• SELECT, FROM , WHERE, HAVING, ORDER BY 절 등에서 사용 가능

종류

• 연관 서브쿼리: 서브쿼리가 메인쿼리 컬럼을 참조하거나 가진다. (Where 절에만 사용)
• 비연관 서브쿼리: 메인쿼리에 값을 제공하기 위한 목적으로 사용
• 단일 행 서브쿼리: 실행결과가 항상 1건 이하, 단일 행 비교 연산자(=,<,> 등 부등호) 또는 다중 행 비교연산자 사용
• 다중 행 서브쿼리: 실행 결과가 여러 건인 서브쿼리, (IN, ANY, ALL, EXISTS)
• 다중 컬럼 서브쿼리: 여러 컬럼 반환, 메인쿼리 조건절에 따라 여러 컬럼 동시에 비교 가능 (SQL Server에서는 현재 지원하지 않는다.)
• 스칼라 서브쿼리: SELECT 절에 위치, 한 레코드당 정확히 하나의 값을 반환(단일행, 단일컬럼)
• 인라인 뷰: FROM 절에 위치, 서브쿼리의 결과로 반드시 하나의 테이블이 리턴

집합 연산자

• UNION
  • 여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만든다.
• UNION ALL
  • 여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행도 그대로 결과에 표시된다.
• INTERSECTION (INTERSECT)
  • 여러 개의 SQL문의 결과에 대한 교집합
• DIFFERENCE(EXCEPT: SQL Server, MINUS: ORACLE)
  • 앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합
• PRODUCT(CROSS JOIN)

그룹 함수

• 예시 테이블

  

ROLLUP(COL1, COL2 ...., COLN)

• 계층적인 그룹화를 수행하여, 점직전인 집계를 생성한다.
• ROLLUP에서 사용하는 컬럼의 개수에 따라 점진적으로 줄여가며 기준을 설정하고 그룹핑하여 집계한다.

SELECT COL1, COL2 AS "월", SUM(COL3) AS "매출액"
FROM TBL1
GROUP BY ROLLUP(COL1, COL2);

• 결과

  

CUBE(COL1, COL2 ...., COLN)

• ROLLUP과 다르게 모든 가능한 조합의 그룹을 생성
• N개의 열을 CUBE로 그룹화하면, 2^N개의 집계 결과를 생성

SELECT COL1, COL2 AS "월", SUM(COL3) AS "매출액"
FROM TBL1
GROUP BY CUBE(COL1, COL2);

• 결과

  

GROUPING SETS(COL1, COL2 ...., COLN)

• ROLLUP, CUBE보다는 유연한 방식으로, 특정 그룹 조합만 선택적으로 집계

SELECT COL1, COL2 AS "월", SUM(COL3) AS "매출액"
FROM TBL1
GROUP BY GROUPING SETS(COL1, COL2);

• 결과

  

SELECT COL1, COL2 AS "월", SUM(COL3) AS "매출액"
FROM TBL1
GROUP BY GROUPING SETS((COL1, COL2), COL1, COL2);

• 결과

  

윈도우 함수

• PARTITON BY 절과 GROUP BY 절은 의미적으로 유사
• PARTITON BY 절이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일하다.
• 윈도우 함수는 결과에 대한 함수처리이기 때문에 결과 건수는 줄지 않는다.
• 윈도우 함수 적용 범위는 Partiton을 넘을 수 없다.

PARTITION BY

• aggregate_function() OVER (PARTITON BY COL1 [ORDER BY COL2])
  • aggregate_function()은 윈도우 함수(SUM, AVG, ROW_NUMBER() 등)를 사용
• 특정 컬럼을 기준으로 데이터를 그룹화하여 각 그룹 내에서 개별 행에 대한 계산을 수행

SELECT COL1, COL2 AS "월", COL3 AS "매출액", AVG(COL3) OVER (PARTITION BY COL2) AS "월별 매출액"
FROM TBL1;

• 결과

  

• order by에 따라 로우별로 aggregate_function() 함수를 실행한다.

  • e.g sum(급여) over (partiton by 부서코드 order by 사원번호) 해당 쿼리는 부서코드로 그룹핑하여 사원번호별로 오름차순으로 정렬하고 각 사원번호 별로 급여 합계를 누적하여 합산

    

  • order by를 생략할 경우 부서코드별 전체 급여 합산으로 계산

    

RANK

• 동일한 값(동순위)이 있을 경우 순위를 건너 뛰는 랭킹 함수

SELECT emp_id, emp_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM TBL1;

• 결과

  

DENSE_RANK

• 동순위가 있어도 순위를 건너뛰지 않는 랭킹 함수

SELECT emp_id, emp_name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM TBL1;

• 결과

  

PERCENT_RANK

• 전체 데이터 중 특정 값의 상대적인 백분위 랭킹을 계산하는 함수
• 백분율 순위 (순위 - 1) / (전체 - 1)

SELECT emp_id, emp_name, salary,
       PERCENT_RANK() OVER (ORDER BY salary DESC) AS percent_rank
FROM TBL1;

• 결과

  

CUME_DIST

• 현재 값이 전체 데이터에서 차지하는 누적 백분율
• 현재 순위 이하의 행 개수 / 전체 행 개수

SELECT emp_id, emp_name, salary,
       CUME_DIST() OVER (ORDER BY salary DESC) AS cume_dist
FROM TBL1;

• 결과

  

RATIO_TO_REPORT

• 현재 값이 전체 합에서 차지하는 비율을 계산
• 현재 값 / 전체 값의 합

SELECT emp_id, emp_name, salary,
       RATIO_TO_REPORT(salary) OVER () AS ratio
FROM TBL1;

• 결과
  • 비교 대상인 salary 값 / salary 전체 값의 합

    

LAG

• 현재 행에 대해 이전 행의 값을 반환하는 함수

SELECT emp_id, emp_name, salary, LAG(salary) OVER (ORDER BY emp_id) AS salary2
FROM TBL1;

• 결과

  

Top N 쿼리

• 쿼리 결과에서 상위 N개의 행을 선택할 때 사용하는 키워드
• 주로 정렬된 결과에서 특정 개수의 행만을 반환하거나, 성능 최적화를 목적으로 사용
• SQL Server에서 사용되는 문법이며, ORACLE에서는 FETCH FIRST {N} ROWS ONLY 구문이 사용된다.

SELECT emp_id, salary
-- SELECT TOP 3 emp_id, salary     ## SQL Server
FROM TBL1
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;

• 결과

  

계층형 질의와 셀프 조인

계층형 질의

• SQL Server 계층형 질의문은 CTE(Common Table Expression)ㄹㄹ 재귀 호출함으로써 계층 구조를 전개
• SQL Server 계층형 질의문은 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행
• 오라클의 계층형 질의문에서 WHERE 절은 모든 전개를 진행한 이후 필터 조건으로서 조건을 만족하는 데이터만을 추출하는데 활용
• 오라클 계층형 질의문에서 PRIOR 키워드는 SELECT, WHERE 절에서도 사용할 수 있다.

CONNECT BY PRIOR

• ORACLE에서 계층적인(재귀적인) 관계를 탐색할 때 사용하는 구문
  • 일반적인 SQL에서는 WITH RECURSIVE를 사용
• ORDER SIBLINGS BY
  • ORACLE에서 CONNECT BY PRIOR와 함께 사용하여 계층적 데이터를 정렬하는 구문
• CONNECT BY 절에 작성된 조건절은 WHERE 절에 작성된 조건절과 다르다. START WITH 절에서 필터링된 시작 데이터는 결과목록에 포함된다.

SELECT LEVEL, emp_name, emp_id, manager_id
FROM TBL1
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name DESC;

• 결과

  

셀프 조인

• 동일 테이블 사이의 조인
• FROM 절에 동일 테이블이 두 번 이상 나타난다.
• 동일 테이블 사이의 조인을 수행하려면 테이블과 컬럼 이름이 모두 동일하기 때문에 식별을 위해 별칭(Alias)를 사용해야 한다.

뷰

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

관리 구문

DML

MERGE INTO {TARGET_TABLE} USING {SOURCE_TABLE}

• SOURCE_TABLE 에서 TARGET_TABLE로 데이터를 병합할 때 사용되는 SQL문법
• 주로 업데이트, 삭제, 삽입을 결합하여 처리
• id 값이 서로 일치할 때, 데이터를 업데이트하고, 일치하지 않으면 데이터를 삽입하는 예시

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET t.column1 = s.column1, t.column2 = s.column2
WHEN NOT MATCHED THEN
    INSERT (id, column1, column2)
    VALUES (s.id, s.column1, s.column2);

INSERT

• DATE 타입에 숫자값은 입력하지 못한다.

UPDATE

• ORACLE 서버 기준 연관 서브쿼리를 활용한 UPDATE에서 WHERE 절은 UPDATE 대상이 되는 데이터의 범위를 결정
  • WHERE 절이 누락되어 모든 데이터가 UPDATE 대상이 되므로 데이터가 서브쿼리에 의해 NULL이 나온다면 NULL로 저장된다.
  • e.g. 별도의 WHERE절이 누락되어 부서 테이블의 모든 데이터가 업데이트 대상이 된다. 만약 부서 테이블에 있는 부서코드가 부서임시 테이블에 없다면 NULL이 반환되어 담당자에 NULL로 저장된다.

UPDATE 부서 A 
SET 담당자 = (
    SELECT C.담당자 
    FROM (SELECT 부서코드, MAX(변경일자) AS 변경일자 
          FROM 부서임시 
          GROUP BY 부서코드) B, 부서임시 C 
    WHERE B.부서코드 = C.부서코드 
    AND B.변경일자 = C.변경일자
    AND A.부서코드 = C.부서코드
);

TCL

• 트랜잭션
  • DB의 논리적 연산 단위

트랜잭션의 특성

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

DDL

테이블 생성시 주의 사항

• 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고
• 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
• 한 테이블 내에서는 컬럼명이 중복되게 지정될 수 없다.
• 테이블 이름을 ㅈ정하고 각 컬럼들은 괄호"()" 로 묶어 지정한다.
• 각 컬럼들은 콤마로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 으로 끝난다.
• 컬럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.
• 컬럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
• 벤더에서 사전에 정의한 예약어는 쓸 수 없다.
• A-Z, a-z, 0-9, _, $, # 문자만 허용한다.

PRIMARY KEY

• 테이블 변경
  • ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME PRIMARY KEY (COLUMN_NAME)
• 테이블 생성
  • CONSTRAINT CONSTRAINT_NAME PRIMARY KEY (COLUMN_NAME)
  • COLUMN_NAME NUMBER PRIMARY KEY

컬럼 정의 변경

• 동시에 여러 컬럼들의 정의를 변경하는 구문은 존재하지 않아 하나씩 변경해주어야 한다.
• ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_DEFINITION

참조 동작

DELETE Action

• Cascade: MAster 삭제 시 Child 같이 삭제
• Set Null: Master 삭제 시 Child 해당 필드 Null
• Set Default: Master 삭제 시 Child 해당 필드 Default 값으로 설정
• Restrict: Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용
• No Action: 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음

INSERT Action

• Automatic: Master 테이블에 PK가 없는 경우 Master PK를 생성 후 Child 입력
• Set Null: Master 테이블에 PK가 없는 경우 Child 외부키를 Null값으로 처리
• Set Default: Master 테이블에 PK가 없는 경우 Child 외부키를 지정된 기본값으로 입력
• Dependent: Master 테이블에 PK가 존재할 때만 Child 입력 허용
• No Action: 참조무결성을 위반하는 입력 액션을 취하지 않음

DCL

권한

• WITH GRANT OPTION과 함꼐 권한을 허가 받으면 해당 권한을 WITH GRANT OPTION 유무와 관계없이 다른 사용자에게 허가할 수 있다.
• PUBLIC을 사용하면 자신에게 허가된 권한을 모든 사용자들에게 허가할 수 있다.
• REVOKE문을 사용하여 권한을 취소하면 권한을 취소당한 사용자가 WITH GRANT OPTION을 통해서 다른 사용자에게 허가했던 권한들도 모두 연쇄적으로 취소된다.

참고 자료

• https://for-my-wealthy-life.tistory.com/44 (ROLLUP, GROUPING SETS, CUBE)