[SQLD] 시험 전반적인 개념

Yujin Bae·2022년 3월 13일
SQLD
0

기출 및 노랭이 풀면서 시험에 필요한 개념위주로 정리했습니다.
모든 개념을 담지 않았습니다. 대강 이런게 나오는구나하고 참고해주시면 감사합니다.

💡 1과목 공부 꼭 해야하는 것

  • 일반적으로 1과목 내용은 그냥 다 알아야함, 그럼에도 꼭 공부해야하는 것을 꼽자면 밑의 세가지가 있음
  1. 엔티티
  2. 속성 - 종류관련
  3. 식별자 - 종류관련

💡 2과목 공부 꼭 해야하는 것 - SQL 문은 대부분 Oracle 기준으로 씀(Oracle 이나 SQL Server 기준으로 쿼리 알면 됨)

  • DML, DDL, DCL, TCL
  • ROLLBACK, SAVEPOINT - 문제 및 예제를 보기
  • null 처리관련 연산자, 함수
    1. 연산자
      • IN , NOTIN 의 null 처리관련
    2. 함수
      • null처리함수(NVL, ISNULL, NULLIF, COALESCE)
      • 집계함수(SUM,AVG,COUNT 등)이 null에 대해 어떻게 처리하는지
  • 윈도우함수(Window Function) - 윈도우함수로 될 수 있는 것 및 BETWEEN 절에 들어갈 수 있는 것 등등 전반적으로 공부
  • 그룹함수 (ROLLUP, CUBE, GROUPING SETS)
  • 조건관련 (CASE, DECODE)
  • JOIN - 조인종류(natural join, inner join, cross join, ...)에 따른 특징
  • 서브쿼리 - 서브쿼리 관련해서는 SQL 오류 관련으로 많이 나옴 (해당 서브쿼리를 썼을때 오류가 나지 않는지)
  • 계층형 쿼리 - 무조건 많은 문제 및 예시를 보는게 답
  • ORDER BY - order by 절에 올 수 있는 것들이 무엇이 있는지
  • TOP(), ROWNUM
  • Nested Loop Join, Sort Merge Join, Hash Join - 차이 및 개념정도

1과목. 데이터 모델의 이해

1-1. 데이터 모델링 이해

모델링

모델링 : 시스템 구현+ 업무,업무형상화 목적

  • 특징
    • 추상화, 단순화, 정확화(명확화)
  • 주의
    • 중복
      • 여러 장소에 같은 정보 저장 X
    • 비유연성
      • 데이터 정의와 사용프로세스를 분리해야함
      • 유지보수성관련
    • 비일관성
      • 데이터간에 상호 연관 관계 설명 명확히 해야함
  • 데이터 모델링의 3가지 요소
    • Thing(저장이 되어 관리가 필요한 어떤 것), Attributes(속성), Relationship(관계)
  • DB 스키마 구조
    • 개념적 데이터 모델링 : 통합 관점, 포괄적
    • 논리적 데이터 모델링 : 관계 명확히
      • 논리 모델링의 외래키는 반드시 구현되지는 않음
    • 물리적 데이터 모델링 : 이식, 성능, 저장등 고려 실제 물리적 고민

엔터티

  • 이름짓기
    • 현업에서 사용하는 용어 사용
    • 약어 X
    • 단수 명사, 고유한 이름 사용
  • 특징
    • 2개 이상의 인스턴스 집합 인스턴스 : 엔터티 구현된거? (환자1,환자2)
    • 2개 이상의 속성 집합
    • 하나의 속성은 하나의 속성값만 가짐
  • 종류
    • 유무형에 따른 분류
      • 유형 엔터티 : 물리적 엔터티 (사원, 물품, 강사)
      • 개념 엔터티 : 개념적 정보, 물리적인 형태가 아님 (보험상품, 조직)
      • 사건 엔터티 : 업무 수행시 발생, 통계자료 이용 (주문, 청구, 미납)
    • 발생시점에 따른 분류
      • 기본 엔터티 : 부모가 되는 엔터티, 주식별자를 상속받지 않고, 자신의 고유한 식별자를 가짐
        • 행위의 주체이자 제일 처음 고려대상
        • 예시 : 사원, 자재, 부서, 고객, 상품
      • 중심 엔터티 : 기본엔터티로부터 발생되고, 업무에 있어서 중심적인 역할함
        • 데이터 양 많음, 많은 행위 엔터티 발생시킴
        • 예시 : 매출, 계약, 주문
      • 행위 엔터티 : 2개 이상의 부모 엔터티로부터 발생됨, 내용자주 바뀜
        • 예시 : 장비점검목록(점검번호, 날짜), 주문번호, 사원변경이력

관계 (엔터티간의)

  • 사용하는 개념
    • 관계명
    • 관계차수 (1:1, 1:M)
    • 관계 선택사양 : 필수관계, 선택관계
  • 관계간에 중요한 것은 명사가 아닌 ‘동사’
  • 데이터 모델링 vs. UML
    • 데이터 모델링 : 존재간 관계, 행위간 관계를 구분 안함
      • 존재에 의힌 관계 : 소속된다
      • 행위에 의한 관계 : 주문한다
    • UML : 연관관계(실선), 의존관계(점선) 구분함
      • 연관관계 : 존재적 관계
      • 의존관계 : 상대방 행위에 의한 관계

속성

  • 정의
    • 의미상 더이상 분리되지 않는 최소의 데이터 단위
  • ⭐ 도메인 : 속성이 가질 수 있는 값 범위
  • 특징
    • 하나의 속성값만 가져야함
    • 속성의 명칭 : 전체 데이터 모델에서 유일성을 확보해야함 → 반정규화,통합등 작업시 혼란 방지할 수 있도록 ‘복합 명사' 사용해서 엔터티마다 비슷한 값을 가지더라도 명칭을 다 다르게 해야함
      • 1 엔터티의 name, 2 엔터티의 name : X (1_name, 2_name O)
  • 종류
    • 기본속성
    • 파생속성 : 빠른성능 위함. 본래 속성값 계산해서 저장가능하게 만든 속성
    • 설계속성 : 업무를 규칙화하기 위해 새로만들거나 변형하거나 정의하는 속성 (ex : 일련번호)

식별자

  • 종류
    • 대표성
      • 주식별자
        • 유일성, 최소성, 불변성, 존재성
      • 보조식별자
    • 엔터티 내에서 스스로 생겼는가
      • 내부식별자
      • 외부식별자 : 타 엔터티의 관계로 인해 타 엔터티에서 받아온 속성
    • 단일 속성으로 식별이 되는가
      • 단일식별자
      • 복합식별자 : 2개 이상의 속성으로 식별자 구성
    • 업무적으로 의미있는 식별자 대체한 식별자인가
      • 본질식별자
      • 인조식별자 : 본래 업무적으로 의미가 있던 식별자 속성 대체한 일련번호처럼 인위적으로 새롭게 만든 식별자
        • 시스템적으로 부여된 식별자
  • 식별자관계, 비식별자 관계
    • 식별자 관계 : 강한 연결관계 표현, 엔티티간 실선표현, 자식의 주식별자에 반드시 포함(종속관계있음)
    • 비식별자 관계 : 약한 연결관계 표현, 엔티티간 점선표현, 자식의 일반 속성에 포함됨
      • 복합식별자가 많은 경우 부모와 자식간의 조인시에 sql 문이 복잡해질 수 있음. 해당 부분은 비식별자로 할건지 고민 중 가장 최하위 우선순위를 가짐(가장 마지막에 고려한다는 것, 식별자관계여야하는데 단순 sql 문이 복잡해진다고 비식별자로 하면 안 됨)

1-2. 데이터 모델과 성능

정규화

  1. 1차 정규형
    • 하나의 속성은 하나의 속성값만 가져야 함
      • 같은 속성을 가지는 속성값들은 동일 형식이어야 함(정수면 정수만)
      • 속성은 유일한 이름을 가져야함
  2. 2차 정규형
    • 식별자가 아닌 속성들은 식별자에 완전 종속되어야 함
      • = PK 아닌 속성들은 PK에 종속되는 속성이어야 함
  3. 3차 정규형
    • 식별자가 아닌 속성끼리는 종속관계가 있으면 안됨

반정규화

  • 반정규화 대상
    • 조인으로 인한 성능저하
    • 디스크 I/O량이 많아 성능저하
    • 칼럼 계산량이 많아 성능 저하 → 계산된 칼럼을 새로 추가
    • 통계성 정보가 따로 필요할 때, 통계 테이블 만드는 것을 고려함
  • 반정규화 대상이 아닌 것
    • Sorting, Order by가 많은 경우
  • 반정규화 전에 고려해야하는 것
    • VIEW : 조인이 너무 많이 걸려있는 경우 조인을 한 View를 만들어서 활용
    • 인덱스 조정 / 클러스터링 적용 : 대량의 데이터처리에 의해 성능 저하되는 경우

대량 데이터 성능

  • 많은 컬럼으로 인한 성능 저하
    • Row Chaining (신규 데이터 입력시 발생)
      • row 길이가 길어서 하나의 블록에 저장되지 못하고, 두 개 이상의 블록에 하나의 row가 저장되는 현상
      • chaining ? c++에서 포인터로 데이터 가리키듯이 너무 긴 수정데이터를 포인터 형식으로 가리켜서 그런 이름인 듯?
    • Row Migration (데이터 수정시 발생)
      • 데이터 블록에서 수정할 때, 수정된 데이터를 해당 블록에서 찾지 못하고 다른 블록의 빈 공간에서 찾아서 저장하는 방식

분산 DB

  • 정의
    • DB가 물리적으로 여러곳에 분산되어 있음 하지만 하나의 논리적으로 동일한 가상 시스템
  • 투명성
    • 위치 투명성 : 저장 장소 명시가 불필요
    • 지역 사상 투명성 : 지역 DBMS - 물리 DB 간의 맵핑 보장, 각 지역 시스템과 무관한 이름 사용 가능
    • 중복 투명성 : DB 객체가 여러 장소에 중복되어 있는지 알 필요 없는 성질
    • 장애 투명성 : 구성요소의 장애에 무관한 트랜잭션 원자성 유지
    • 병행 투명성 : 다수 트랜잭션 동시 수행시에 결과의 일관성 유지

2. SQL 기본 및 활용

RDBMS

  • DML(데이터 조작어)

    • SELECT

    • INSERT, DELETE, UPDATE

      INSERT INTO 테이블명 (칼럼리스트) VALUES (칼럼 값);
UPDATE 테이블명 SET 수정되어야할 칼럼명 = 값; DELETE FROM 테이블명;
SELECT 칼럼리스트 FROM 테이블명;
  • DDL(데이터 정의어)

    • DROP, CREATE, ALTER, RENAME, TRUNCATE

      ALTER TABLE DEPT ALTER COLUMN VARCHAR(30) NOT NULL;

ALTER TABLE 테이블명 ADD 칼럼명 데이터 유형; - 여러개 컬럼 동시수정 불가(SQL Server)
ALTER TABLE 테이블명 DROP COLUMN 칼럼명;
ALTER TABLE 테이블명 MODIFY (칼럼명 데이터유형 DEFAULT식 NOT NULL); - 칼럼 데이터 유형, 조건 등 변경 / Oracle
ALTER TABLE 테이블명 ALTER (칼럼명 데이터유형 DEFAULT식 NOT NULL); / SQL Server
ALTER TABLE 테이블명 RENAME COLUMN 변경전칼 럼명 TO 뉴칼럼명; Ora
ALTER TABLE 테이블명 DROP CONSTRAINT 조건명; 제약조건 삭제
ALTER TABLE 테이블명 ADD CONSTRAINT 조건명 조건 (칼럼명); 조건 추가
RENAME 변경전테이블명 TO 변경후테이블명; Ora 
sp_rename ‘db0.TEAM’,‘TEAM_BACKUP’; SQL
sp_rename 변경전칼럼명, 뉴칼럼명, ‘COLUMN’; SQ
DROP TABLE 테이블명 [CASCADE CONSTRAINT] CASCADE CONSTRAINT : 참조되는 제약조건 삭제
TRUNCATE TABLE 테이블명; 행 제거, 저장공간 재 사용
  • DCL(데이터 제의어) - 유저생성 및 권한 제어
    • GRANT - 권한 부여
    • REVOKE - 권한 회수
  • TCL(트랜잭션 제의어)
    • COMMIT, ROLLBACK, SAVEPOINT
    • DCL 로 표현하기도 함

Transaction (TCL관련)

  • Transaction 속성
    • 원자성 : 트랜잭션에 정의된 속성은 모두 성공이던지 모두 실패던지 둘 중에 하나여야함
    • 일관성 : 트랜잭션이 실행되기 전 결과가 잘못되지 않았다면, 후에도 잘못되면 안됨
    • 지속성 : 트랜잭션이 성공적이면 그 결과는 영구적으로 저장됨
    • 고립성 : 다른 트랜잭션에 영향받아 현재 트랜잭션의 결과가 이상하게 되면 안됨
  • Auto Commit
    • DDL이 일어나면 암묵적으로 commit 됨
      - 01 : CREATE TABLE TAB1 (N1 NUMBER, N2 NUMBER);
- 02 : INSERT INTO TAB1 VALUES (1,1);
- 03 : INSERT INTO TAB1 VALUES (2,2);
- 04 : CREATE TABLE TAB2 (V1 VARCHAR2(10), V2 VARCHAR2(10));
- 05 : ROLLBACK;
- 06 : SELECT COUNT(*) FROM TAB1; // 2

권한 (DCL관련)

SQL 실행순서

SELECT DEPTNO, COUNT(EMPNO) 
FROM SCOTT.EMP
WHERE SAL >= 500
GROUP BY DEPTNO
HAVING COUNT(EMPNO) > 2 
ORDER BY DEPTNO;
  • FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
  • 최대한 앞에서 거를 수 있으면 거르는게 좋음
  • 각 절 상세 설명

    1. FROM과 JOIN

      JOIN이 먼저 실행되어 데이터가 SET으로 모아지게 됌.여기에는 서브쿼리도 함께 포함되어 임시적인 테이블을 만들 수 있게 도와줌.

    2. WHERE

      데이터셋을 형성하게 되면 WHERE의 조건이 개별 행에 적용이 된다.

      이 WHERE절의 제약 조건은 FROM절로 가져온 테이블에 적용이 될 수 있다.

    3. GROUP BY

      WHERE의 조건 적용 후 나머지 행은 GROUP BY절에 지정된 열의 공통 값을 기준으로 그룹화된다.

      쿼리에 집계 기능이 있는 경우에만 이 기능을 사용해야 한다.

    4. HAVING

      GROUP BY 절이 쿼리에 있을 경우 HAVING 절의 제약조건이 그룹화된 행에 적용됌.

    5. SELECT

      SELECT에 표현된 식이 마지막으로 적용됌.

    6. DISTINCT

      표현된 행에서 중복된 행은 삭제됌

    7. ORDER BY

      지정된 데이터를 기준으로 오름차순, 내림차순을 지정

    8. LIMIT / OFFSET

      LIMIT와 OFFSET에서 벗어나는 행들이 제외되어서 출력됨

데이터 유형

  • 문자
    • CHAR
      • 고정된 길이
      • 비교 : 길이가 다른 경우 스페이스(공백) 하나 추가해서 비교
        • 예를들어, CHAR(8)이고 ‘AA’ 가 저장된 경우, ‘AA’에 공백 6자리 붙인 ‘AA ‘로 타 문자와 비교
    • VARCHAR
      • 가변길이
      • 비교 : 공백또한 한 문자로 취급해서 비교, 길이 짧은거 끝나면 그대로 비교 종료함

INSERT

  • Identity
    • SQL SERVER : IDENTITY [ ( seed , increment ) ]
      • seed : 시작
      • increment : 증가값
    • insert 문에 indentity인 컬럼에 값 넣으면 ERROR
  • check
    • check 조건 만족못하면 ERROR, NULL은 무시

JOIN

  • JOIN 종류
    • Natural Join
      • 두 테이블간 동일한 이름과 형식의 컬럼이 하나 있는 경우 사용가능
      • 식별자 가질 수 없음 ( EMP.DEPTNO 같은 owner 명 기재 불가)
        SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT; // ERROR
    • Using

      • 두 테이블간 동일한 이름과 형식의 컬럼이 둘 이상인 경우 natural join 사용 불가. 이때 using 사용

        SELECT *
FROM DEPT JOIN DEMPT_TEMP
USING (DEPTNO);

Sub Query

SELECT col1, (SELECT ...)     -- 스칼라 서브쿼리(Scalar Sub Query): 하나의 컬럼처럼 사용 (표현 용도)
FROM (SELECT ...)             -- 인라인 뷰(Inline View): 하나의 테이블처럼 사용 (테이블 대체 용도)
WHERE col = (SELECT ...)    -- 일반 서브쿼리: 하나의 변수(상수)처럼 사용 (서브쿼리의 결과에 따라 달라지는 조건절)

  • Inline View
    • from 절 내에서 쓰이는 서브쿼리
    • select 절을 마치 테이블처럼 쓰고 싶을 때 사용
    • 메인쿼리에서 서브쿼리 컬럼 사용 가능

여러 함수

  • NULL 처리 함수
    • ⭐ NVL(v1,v2) : Null 이 컬럼에 포함된 경우 어떠한 값으로 치환하는 함수

      • v1 에 null 이 있다면 v2 로 치환 보통 문자면 ‘X’, 숫자면 0 으로 치환함

        SELECT COL1, COL2
FROM TAB1
WHERE NVL(COL1, 'X') <> 'A01';

      • COL1이 NULL 인 경우 ‘X’ 로 치환, 그 후 COL1값이 ‘A01’이 아니면 출력 → 보통 Null 행도 출력하기 위해 사용함(해당 함수를 사용하지 않는다면 Null인 행은 자동으로 배제되고 출력됨)

      • 실행결과

        <TAB1 테이블 구조>
COL1 COL2
A01 10
A02 20
A03 30 
NULL 40 
NULL 50

<실행 결과>
CO1 CO2 
A02 20 
A03 30 
NULL 40 
NULL 50
    • ISNULL(v1,v2) : NVL과 동일
    • COALESCE(v1,v2,...,vn) : 모든 v들이 null이면 null 반환하지만, 하나라도 아닌 경우 null이 아닌 최초의 표현식을 반환
    • NULLIF(v1,v2) : 특정값을 NULL로 대치하기 위한 함수
      • v1이 v2이라면 NULL로 치환
  • 문자열 관련 함수
    • 문자열 이어붙이기 : +, ||(연산자), CONCAT,
    • 공백제거 : trim
  • length(), len()
    • length() : ORACLE
    • len() : SQL SERVER
  • 집계함수
    • AVG
      • 해당 칼럼값이 NULL 인 것 제외해서 결과 반환
    • SUM
      • 해당 칼럼값이 NULL 인 것 제외해서 결과 반환
    • COUNT
      • 아무 결과도 반환안하면 0을 반환함

        • 이외의 다른 집계함수는 아무 결과가 없다면 NULL → NVL함수 필요

          SELECT nvl(count(*), 9999) from table where 1=2 // 0
      • COUNT(*) : NULL 포함해서 결과 반환
      • COUNT(column) : 해당 컬럼값이 NULL 인것 제외해서 결과 반환
  • SQL의 IF-ELSE
    • DECODE(칼럼,조건1,결과1,조건2,결과2,...)
      • 칼럼 == 조건1 → 결과1 반환
      • 해당 조건이 없는경우 NULL 반환
    • CASE : 최근 권장
      • ELSE 없고 해당값이 아니면 NULL 반환
  • WINDOW FUNCTION

    • 윈도우 함수 개념 정리
      - https://velog.io/@yewon-july/Window-Function

      SELECT WINDOW_FUNCTION(ARGUMENTS)
  OVER (PARTITION BY 칼럼
			ORDER BY WINDOWING절
			RANGE BETWEEN start_point AND end_point)
FROM 테이블명;

    • WINDOW_FUNCTION 가능한 것

      • 집계함수 ( COUNT, AVG, ... )
      • 순위함수
        • RANK() : 4,5,5,5,8 (동일 순위에 대해 하나의 등수로 매기지만, 그 다음 등수는 중복값 개수 쳐서 진행)
        • RANK_DENSE() : 4,5,5,5,6 (동일 등수에 대해 하나의 등수로 매기고, 그 다음 등수는 해당 등수 다음)
        • ROW_NUMBER() : 4,5,6,7,8 (동일 등수에 대해서도 순위 매김)
        • PERCENT_RANK() : 값이 아닌 백분율로 순위 매김 (0~1 사이값 반환)

    • PARTITION_BY : group by 과 비슷

    • RANGE BETWEEN start_point AND end_point : 누적 평균 낼 수 있음

      • default : RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 누적평균
      • UNBOUNDED PRECEDING : start_point만 들어갈 수 있으며, 파티션의 first row
      • UNBOUNDED FOLLOWING : end_point만 들어갈 수 있으며, 파티션의 last row
      • CURRENT ROW : start, end_point 둘다 가능. 윈도우는 CUREENT ROW에서 start하거나 end 함
      • 누적평균 예제
        SELECT COL1, COL2,
ROUND ( AVG (COL3) 
	OVER( PARTITION BY COL1 ORDER BY COL3
				RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	) , 0)
	AS 누적평균
FROM TAB1 
ORDER BY COL1, COL3;
        • COL1 로 파티션되어서 파티션 내의 누적평균
  • 그룹 함수

연산자

  • IN
    • COL1 IN(NULL, 'A') : COL1 = Null or COL1 = ‘A’ 로 치환됨. 결과는 null인 행은 출력안함
    • COL1 NOT IN(NULL, 'A') : NOT IN의 조건절에 NULL 이 하나라도 포함되면 아무행도 출력안함
    • IN, ANY, ALL
      • IN(10,20,30) : 10,20,30 값 중 하나
      • ANY(10,20,30) : 10,20,30 어떤 값보다 - 비교연산자 사용
      • ALL(10,20,30) : 10,20,30 모두보다 - 비교연산자 사용
    • IN, EXISTS
      • IN : 컬럼 값 비교할 때 좋음, 서브쿼리는 안넣는게 좋음
        • 일치하지 않더라도 서브쿼리를 모두 수행하기 때문에 별로 안좋음
        • SELECT * FROM A WHERE A.v1 IN (SELECT B.v1 FROM B) : not good
      • EXISTS : 서브쿼리 넣어서 비교할 때 좋음(성능상)
        • 한 건이라도 만족하면 true
        • 일치하지 않다면 더이상 서브쿼리를 실행하지 않는다. - 뭔말인지 이해안감
        • SELECT * FROM A WHERE EXISTS (SELECT 'X' FROM B WHERE A.v1 = B.v1) : good
          • 서브쿼리절의 컬럼은 불필요함 - 의미없는 값(’X’) 넣음
  • =
    • COL1=NULL : NULL 을 비교시 항상 False를 함. 따라서 실제 Null인 행이 있어도 출력 암것도 안함
  • IS, IS NOT
    • 유일하게 NULL 을 잡아낼 수 있는 연산자
  • SUM
    • SUM(NULL, 1) : 1
  • SET 연산자
    • INTERSECT
    • UNION
      • UNION ALL : 중복 제거하기 위한 정렬 안함
        • 문제
          [데이터]
TABLE A 
COL1 COL2 
-----------
   1    2
   1    2
   1    3
TABLE B 
COL1 COL2 
-----------
   1    2 
   1    4 
   1    5
[SQL]
SELECT DISTINCT COL1, COL2 FROM TAB_A 
UNION ALL
SELECT COL1, COL2 FROM TAB_B;

// 결과 : 5

Having

  • HAVING COUNT(*)
    SELECT 주문일자, 고객명, COUNT(*) 
FROM TAB1
GROUP BY 주문일자,고객명
HAVING COUNT(*) >= 2;
    • 2건 이상 주문하는 고객리스트

Order by

  • order by 절에는 숫자 가능
    • 0 : 해당 column 을 가장 위에 오게 함 (CASE 와 함께 쓰임 order by case ROWNUM when 3 then 0)
    • 1,2,3,... : select a,b,c 일때 a 가 1, b가 2, c가 3, 만약 여기서 order by 4 하면 오류남(4에 해당하는 칼럼이 없으므로)
  • group by 가 있는 경우 order by 에 집계함수 사용 가능
    • group by col1 order by count(*) : col1에 따라 개수 셈
  • select 절에서 alias 로 명명지은 이름 사용가능
    • select user_name as name from dept order by name
    • SQL 실행순서와 관련있음 (select 이후에 order by를 행하므로 select 시에 alias 로 지은 이름을 사용할 수 있는 것)
    • 비교 : group by 절 내에서는 alias 로 명명지은 이름 사용 불가 (group by 는 select 이전에 실행되기 때문)

SQL Server ↔ Oracle

  • TOP(SQL Server), ROWNUM : Oracle
    • oracle
      SELECT ENAME, SAL
FROM (SELECT ENAME, SAL
			FROM SCOTT.EMP
			ORDER BY SAL DESC) 
WHERE ROWNUM < 4 ;
    • SQL server
      SELECT TOP(3) ENAME,SAL 
FROM SCOTT.EMP
ORDER BY SAL DESC

ANSI SQL 표준

  • 모든 DBMS에 통하는 SQL문
  • Inner Join 표준
    • 오라클
      SELECT *
FROM SCOTT.EMP A,
SCOTT.DEPT B
WHERE A.DEPTNO = B.DEPTNO AND B.DNAME = 'SALES'
    • ANSI 표준
      SELECT *
FROM SCOTT.EMP A INNER JOIN SCOTT.DEPT B
ON A.DEPTNO = B.DEPTNO 
WHERE 1=1
AND B.DNAME ='SALES';

VIEW(뷰)

  • 개념
    • 사용자에게 제한적으로 자료를 보여주기 위한 가상 테이블
      • 필요한 데이터만 뷰로 정의 가능
    • 물리적으로 존재X, 임시작업(데이터 보정, 처리과정)위한 용도
    • 뷰정의 변경 불가(ALTER VIEW 안됨)
      • 변경이 필요하다면 삭제후에 정의 다시해야함
    • 뷰로 구성된 내용삽입,제거,연산,갱신 제약O
    • 예제
      • 정의문
        --문법--
CREATE VIEW 뷰이름[(속성이름[,속성이름])]AS SELECT문;

--고객 테이블에서 주소가 서울시인 고객들의 성명과 전화번호를 서울고객이라는 뷰로 만들어라--
CREATE VIEW 서울고객(성명, 전화번호)
AS SELECT 성명 전화번호
FROM 고객
WHERE 주소 = '서울시';
      • 삭제문
        --문법--
DROP VIEW 뷰이름 RESTRICT or CASCADE

--서울고객이라는 뷰를 삭제해라--
DROP VIEW 서울고객 RESTRICT;
        RESTRICT : 뷰를 다른곳에서 참조하고 있으면 삭제가 취소된다.
        CASCADE : 뷰를 참조하는 다른 뷰나 제약 조건까지 모두 삭제된다.

계층형 질의 (계층형 SQL)

Trigger

  • 특정 테이블에서 DML(select,insert,update,delete)이 일어나게 된다면 자동적으로 데이터베이스에서 수행되도록 만들어진 프로그램
    • 결국 해당 DML과는 트랜잭션 관계
    • begin,end 절 내에서는 TCL(COMMIT, ROLLBACK) 사용 불가
  • :OLD : 기존 데이터
  • :NEW : 변경될 데이터

최적화

  • Optimizer (옵티마이저)
    • 비용 기반 옵티마이저 : 쿼리 수행시 소요되는 시간 및 일량 기준으로 최적화 수행
  • 실행계획
  • INDEX(인덱스)
    • 효율적으로 숫자 인덱스를 사용하는 방법?
      • = 으로 찾기 (WHERE COL1 = 10)
      • 비효율적인 방법 WHERE COL1 LIKE '2%' : LIKE - 문자로 변환해서 인덱스 사용 못함
        WHERE COL1 IS NOT NULL : 인덱스 풀 조회하지만, 효율 떨어짐
        WHERE COL1 <> 10 : 부정형 비교(<>, !=, ^=, NOT)는 인덱스 사용 불가
  • JOIN
    • ⭐Nested Loop Join, Sort Merge Join, Hash Join
      • Nested Loop Join
        • C++의 중첩된 반복문처럼 조인 수행
        • INDEX 사용
        • 데이터 읽는 방식 : 랜덤 액세스
          • ex ) Index scan
        • 대량의 데이터에 적합 X
        • 빨리 조인 결과를 보여줘야 하는 경우 굳
      • Sort Merge Join
        • 먼저 조인할 두 테이블의 SORT 부터 수행 후 merge 해서 join
          • sort 로 인해 disk 를 사용할 수 있어 성능이 그렇게 좋지 않음(느리다)
        • INDEX 사용 안함
        • Non Equal Join 가능
        • 대량의 데이터는 성능상 Hash Join 이 유리함
      • Hash Join
        • NL Join(랜덤 액세스), Sort Merge Join(선 sort로 인한 느림) 문제점 해결
        • INDEX 사용 안함
        • Non Equal Join 불가능
        • 대량의 데이터 처리시 굳! (CPU 작업 위주로 처리하기 때문)
      • https://hoon93.tistory.com/46
profile
Yujin Bae
안녕하세요
이전 포스트

[mistake][python] 이중 리스트 초기화 시 복사관련 문제

0개의 댓글