포스트

최종 정리(1)

게시
By tonyleekorea 16 분읽는 시간

SQL 명령문 개괄 - 연산 순서 정렬, 종류

① FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

② DML - SELECT, INSERT, DELETE, UPDATE

DDL - ALTER, CREATE, MODIFY, DROP

TCL - ROLLBACK, COMMIT

DCL - GRANT, REVOKE

이미지

SELECT

  • DISTINCT(집약)
    • 원하는 정보를 집약 - 중복된 값 존재 시 중복 제거
    • DISTINCT deptno, mgr는 괄호를 친 것과 동일하다.(GROUP BY와 비슷)

  • AS

    ① SELECT - AS 생략 가능, 컬럼명에 띄어쓰기

    Ex) 직원 AS 번호 → “직원 번호”

    ② FROM - AS 사용 불가

  • CONCAT

    • 연산자 기호

      ① SQL SERVER: +

      ② ORACLE: ||

    • 인수가 반드시 2개

      • concat( , )

이미지

논리연산자

  • 의미
    • NOT: A, B
    • AND: A and B
    • OR: A or B
  • 연산자 우선순위: NAO
    • NOT을 적용한 조건에 AND를 만든 후 OR 조건을 적용한다.

이미지

SQL 연산자

  • BETWEEN
    • A BETWEEN 1 AND 2 → 1 ≤ A ≤ 2
  • IN
    • A IN (1, 2, 3) → A = 1 OR A = 2 OR A = 3
  • LIKE(3문제)
    • _ : 미지의 한 글자
    • % : 0 이상의 글자
      • Ex) ‘_L%’ : 이름의 두 번째 글자가 L인 모든 사원
    • ESCAPE
      • 와일드카드(_, %)를 문자로 취급하는 함수
      • ENAME LIKE ‘A_A’의 ESCAPE 경우 → ENAME LIKE ‘A@_A’ ESCAPE ‘@’
      • @이 아닌 아무 문자 가능
  • ROWNUM(오라클) - 테이블, 쿼리 제시 후 가장 위에 있는 SAL 구하기
    • WHERE 조건절에서 ROWNUM이 1인 경우를 포함해야 한다.(ROWNUM은 적층구조로 간다. 테이블이 있으면 1부터 부여, 1 존재 시 2 부여, … )
    1
2
3
4

      SELECT EMPNO, SAL
  FROM EMP
  WHERE ROWNUM <= 3
  ORDER BY SAL DESC;
    • 정렬 전에 ROWNUM에 대한 조건절 실행되어 정렬되지 않은 3행을 선택, 가장 마지막에 ORDER BY 적용
  • TOP(SQL Server)
    • SELECT절에서 사용
    • SELECT TOP N 컬럼명 - 컬럼 출력 시 결과값의 상위 N개의 행을 가져온다.

이미지

이미지

NULL - 5문제 이상

① NULL의 정의

  • 부재, 모르는 값

② NULL의 산술연산 = NULL

  • NULL + 2
  • NULL - 4
  • NULL x NULL

③ NULL의 비교연산 = FALSE

  • 논리가 알 수 없음(UNKNOWN 논리)
  • WHERE 조건절의 조건이 UNKNOWN 논리라면 조건을 알 수 없기 때문에 사실상 ‘FALSE’와 비슷한 기능을 한다.
  • 조건을 알 수 없기에 조건이 맞지 않다고 판단하는 것

④ 정렬에서의 의미

  • 오라클: 무한대
    • NULL에 대해 정렬 시 오름차순의 경우 NULL이 가장 마지막에 나온다.
  • SQL Server: -무한대
    • 최소의 값, 오름차순의 경우 NULL이 가장 먼저 나온다.

⑤ NVL, NVL2, ISNULL, NULLIF, COALESCE

  • NVL(값1, 값2) - 널뛰기
    • 값1 IS NULL 값2
    • 값1 IS NOT NULL 값1
  • NVL2(값1, 값2, 값3)
    • 값1 IS NULL 값3
    • 값1 IS NOT NULL 값2
  • ISNULL(값1, 값2)
    • NVL과 동일
  • NULLIF(값1, 값2) - 같이 놀자, 다르면 처음
    • 두 개의 값이 같으면 NULL
    • 두 개의 값이 다르면 값1
  • COALESCE(값1, 값2, …무한대)
    • NULL 아닌 첫 번째 값 찾기
  • NULL은 유니크 제약을 받지 않는다.

이미지

정렬 - ORDER BY

① 정렬의 특성(1문제)

  • 항상 가장 마지막에 실행
  • 성능이 많이 느려질 가능성
  • NULL값과의 관계 - 오라클에서는 무한대로 취급된다.

② 컬럼 번호로 정렬

  • 출력되는 컬럼의 수보다 큰값으로 컬럼 번호 정렬을 불허한다.
    • 출력되는 컬럼이 SELECT 학과, 학번, 이름, 이렇게 3개면 ORDER BY 1은 학과 기준 정렬, ORDER BY 2는 학번 기준 정렬, …. , 여기서 ORDER BY 4는 불가하다.

  • 출력되지 않는 컬럼명으로 정렬 가능

    1
2
3

      SELECT ENAME
  ...
  ORDER BY SAL;

③ 인수 두개 정렬

  • SAL DESC, ENAME ASC: SAL이 같으면, ENAME 오름차순

이미지

숫자 함수

  • Round, 반올림 함수 - 자릿수 확인
    • Round(138.94, 인수): 8부터 0을 기준으로 1, 2, … 반올림
      • Round(138.94, 1) 소수점 둘째자리에서 반올림하여 첫째자리까지 출력
      • Round(138.94, 0) 소수점 첫째자리에서 반올림하여 정수만 출력
        • 1 파라미터는 1의 자리에서 반올림하여 정수를 출력(.을 기준으로 올라감 -1, -2, …) Ex, Round(123.13, -2) → 100
  • Ceil(oracle)/ceiling(SQL Server), 올림 함수, 파라미터 사용법은 Round와 같음
  • Truncate(oracle)/Floor(SQL Server): 버림 함수, 파라미터 사용법은 Round와 같음

이미지

문자 함수

이미지

날짜 함수 - 다음 중 데이터의 형변환을 일으키는 함수는?

이미지

  • TO_CHAR, TO_DATE - 실습하기
  • 현재의 시간 출력해주는 함수
    • SYSDATE - 오라클
    • GETDATE() - SQL Server
  • 날짜 데이터 + 100
    • 숫자 처리 시 기본적으로 DAY로 인식, 100일 이후

DECODE(실습)/CASE(만 시험)

1
2
3
4
5
6

CASE
	WHEN 조건1 THEN 1
	WHEN 조건2 THEN 2
	ELSE
END 
-- ELSE가 없고, 조건1, 조건2 만족하지 않는 경우 NULL 출력

이미지

집계함수(2-3문제)

  • NULL과의 관계

    ABCA+B+C
    NULLNULL1NULL
    3227
    NULL23NULL
    • SUM(A): 3
    • SUM(B): 4
    • COUNT(A): 1
    • COUNT(*): 3 ← 하나의 행을 전부 하나씩 처리
    • SUM(A+B+C): 7 ← NULL의 연산은 NULL
    • SUM(A) + SUM(B) + SUM(C): 3+4+6
  • NULL은 연산할 경우 결괏값은 NULL이다.
  • COUNT 함수를 사용하면 NULL 값을 제외한 튜플들의 숫자를 반환한다.

이미지

GROUP BY

  • 집약 기능 존재
    • PARTITION BY 구에는 GROUP BY절이 가진 집약 기능이 없으며, 이로 인해 레코드가 줄어들지 않음
    • PARTITION BY를 통해 구분된 레코드 집합을 윈도라고 함
    • 윈도 함수에는 OVER 문구가 필수적으로 포함
  • WHERE 다음에 실행, 그룹 수준으로 정보를 바꾼다.
  • HAVING은 GROUP BY에 대한 조건식
class학생
1A
1B
1C
2D
2E
2F
1
2
3

SELECT CALSS, COUNT(*)
FROM TABLE
GROUP BY CLASS; // class 값이 같은 것끼리 묶음
classcount(*)
13
23

이미지

JOIN

  • NATURAL JOIN, USING JOIN
    • 중복된 컬럼 제거(하나만 출력), 중복된 컬럼이 가장 앞에 등장
    • TABLE AS 사용 불가

    이미지

  • LEFT OUTER JOIN의 의미
    • A LEFT OUTER JOIN B
    • COL1이 조인키 컬럼일 경우, A COL1 = B COL1(+) - 반대
    • 선행 테이블이 선행 조건절 앞에, 후행 테이블은 후행 조건절 뒤에 있을 경우

    이미지

  • 조인은 하면 할수록 컬럼이 늘어나 테이블이 커진다.

    이미지

  • 조인 순서 - FROM A, B, C의 경우, A와 B 테이블을 먼저 조인한 결과 테이블로 C 테이블과 조인한다.

    이미지

이미지

서브쿼리 - 위치에 따른 구분(중요)

1
2
3
4
5
6

SELECT Scalar -단일행 서브쿼리
FROM Inlne Views -메인쿼리에 컬럼 사용가능
WHERE Nested Sub-Query -거의 모든 서브쿼리
GROUP BY X
HAVING Nested Sub-Query -거의 모든 서브쿼리
ORDER BY Scalar
  • 위치에 따른 서브 쿼리 유형은 다음과 같다.
    • SELECT절 서브 쿼리
      • 서브 쿼리가 SELECT절 안에 들어있는 형태
      • 스칼라 서브 쿼리(Scalar Sub-Query)라고도 불림
      • SELECT절에 오는 서브쿼리는 반드시 단일 행을 리턴해야 함
      • SUM, COUNT, MIN, MAX 등과 같은 집계 함수가 많이 쓰임
    • FROM절 서브 쿼리
      • 서브 쿼리가 FROM절 안에 들어있는 형태
      • 인라이 뷰(Inline Views)라고 불림
      • 뷰(View)처럼 결과가 동적으로 생성된 테이블 형태로 사용할 수 있음
    • WHERE절 서브 쿼리
      • 서브 쿼리가 WHERE절 안에 들어있는 형태
      • 중첩 서브 쿼리(Nested Sub-Query)라고도 불림

  • 하나의 테이블 행에 대해서 서브쿼리 전체를 돌린다. 컬럼 COL1의 값이 A인 서브쿼리만 남고, 다중행 서브쿼리(IN, ANY, ALL)로 처리

    1
2
3
4
5
6
7
8
9
10

      SELECT ..
  FROM A
  WHERE 
  	(SELECT ..
  	 FROM B.COL1
  	 = A.COL1)
    	 
  // A를 찾을 때까지 = A.COL1에 대입
  // 즉, 서브쿼리에 한해서는 A.COL1 값이 동일하게 고정(A.COL1 = 'A')
  // B.COL1 중에서 'A'인 경우를 전부 출력
  • 개별 행에 대해 적용(메인쿼리의 한 행에 대해서 서브쿼리 실행)
    • IN
    • ANY / SOME
    • ALL
    • EXIST 존재하다
      • ’1’, ‘x’, ‘a’, …모든 문자 사용가능 → 하나라도 출력 시 TRUE
      • 0 ROWS → 미 출력 시 FALSE

이미지

집합연산자

  • UNION 합집합
  • UNION ALL 중복 데이터 존재
    • 정렬 작업 없음, 빠름
    • UNION과 UNION ALL의 비교
  • INTERSECT 교집합
  • MIINUS(SQL Server의 경우, EXCEPT)

이미지

DDL - TCL과 연관지어 생각

  • TRUNCATE와 DROP
    • 입주민 퇴거/ 건물 철거
    • 구조(로그 데이터)가 남음/ 구조도 삭제
  • TRUNCATE와 DELETE
    • DDL과 DML - ROLLBACK, COMMIT과 엮어서 출제

이미지

DML - TCL과 연관지어 출제

  • INSERT - 인수 타입, 갯수 불일치 오류
  • UPDATE
  • DELETE
  • MERGE - 신유형 37회
    • 데이터 병합
    • 참조 테이블과 동일하게 맞추는 작업
      • 참조 테이블에만 있는 데이터에 대해서는 데이터가 입력됨
      • 참조 테이블에도 있는 데이터에 대해서는 데이터가 수정됨
    • INSERT, UPDATE, DELETE 작업을 동시에 수행
    1
2
3
4
5
6
7

      MERGE INTO 테이블명
  USING 참조테이블
     ON (연결조건)
    WHEN MATCHED THEN
         UPDATE SET 수정할 내용
    WHEN NOT MATCHED THEN
         INSERT VALUES(삽입할 내용)
    • 수정할 테이블명을 MERGE INTO 절에 명시, 참조 테이블을 USING 절에 명시
    • 두 테이블의 데이터를 참조할 참조 조건을 ON 절에 명시(괄호 필수)
    • 이미 MERGE INTO절에 수정할 테이블명이 명시되어 있으므로 UPDATE 문에서는 테이블명을 명시하지 않음
    • SET 절의 왼쪽이 수정 테이블, 오른쪽이 참조 테이블 컬럼
    • 이미 USING 절에 참조할 테이블명이 명시되어 있으므로 INSERT 문에는 INTO절 없이 VALUES로 참조 컬럼명 전달

    이미지

    • OUTER JOIN으로 3개 행이 변경된다.

이미지

제약조건

  • PK: UNIQUE + NOT NULL
  • UNIQUE
  • NOTNULL

이미지

DCL

  • GRANT
  • REVOKE

① 정의, 문법(그온투, 리온프)

② ROLE

  • 명령어가 아닌 객체 중 하나로, 객체란 데이터베이스를 구성하는 물질들(테이블, 인덱스, 뷰, 롤, …)
  • 5가지 특징
    • 롤은 롤에게 부여 가능
    • 롤은 사람에게도 부여 가능
    • 사람들은 같은 롤을 가질 수 있음
    • 롤을 부여할려면 권한이 필요함
    • 롤은 권한이 있는 사용자한테 만들어짐

③ WITH ADMIN OPTION, WITH GRANT OPTION

  • WITH GRANT OPTION은 사용자가 권한을 받고 난 후 다른 사람들과 권한을 나누어 가질 수 있는 옵션

이미지

VIEW - 독편보

  • 독립성: 기존 테이블의 구조가 변경되면 뷰 구조도 같이 변경되어 뷰 따로 업데이트 불필요
  • 편리성: 테이블을 계속 조작할 필요 없이, 사용할 때마다 나온다.
  • 보안성: 원하는 정보만 노출 가능
  • 테이블 자체가 아닌 SQL 명령문 저장하므로 따로 저장공간이 필요하나, 기존 테이블보단 적게 필요하다.

이미지

그룹함수 - 결과값 주고 무엇을 사용했는가, 비교 테이블

  • ROLL UP
  • CUBE
  • GROUPINGSETS
  • GROUPING
  • ROLLUP(A, B)과 ROLLUP(B, A)는 다른 결과(계층 구조)
  • CUBE(A, B)의 경우 동일한 결과

  • 표로 판단하기

    ① NULL 전부 찾기

    ② 총합 행 찾기

    • 있으면 ROLL UP(한 쪽만 결과가 나와 계층식, 행의 수가 적으면), CUBE(양 쪽으로 결과 도출, 행의 수가 많으면)
    • 없으면 GROUPINGSETS

이미지

TCL

  • COMMIT, ROLLBACK
  • SQL Server - AUTO COMMIT OFF와 BEGIN TRANSACTION은 DDL의 COMMIT 기능 제거

이미지


참고 자료

Certificate, [SQLD] 개념 및 문제풀이
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.