SQL/기본

[SQL][SQLD][SQLP] 가끔 헷갈리는 문제

궝대 2021. 7. 21. 01:13
반응형

SQLD, SQLP 문제에서 자주 나오는 헷갈리는 문장 정리

CHECK

ALTER TABLE EMP ADD CONSTRAINT EMP_CK CHECK(LENGTH(NAME) > 2);

Oracle의 컬럼 제약조건인 CHECK는 조건을 만족하지 못하는 값이 들어올 경우 에러가 발생한다.

 

NULL은 CHECK 조건을 무시하여 항상 조건을 통과한다.

 

 

DML문

INSERT

  • ORACLE은 VARCHAR에 ''을 입력하면 NULL로 입력된다.
  • SQL Server는 VARCHAR에 ''을 입력하면 ''로 입력된다.

DELETE

  • FROM 키워드는 생략 가능하다. ex) DELETE FROM 주문; = DELETE 주문

SELECT

  • 두 개 이상의 테이블을 참조하는 FROM절에 테이블에 대한 ALIAS를 사용했을 때, 테이블들이 중복된 칼럼을 가지고 있을 경우 반드시 ALIAS명을 사용해야 함. 

 

 

로깅을 하고 싶지 않다면 NOLOGGING 옵션을 사용한다.

ALTER TABLE EMP NOLOGGING;

 

ESCAPE

LIKE 연산자에서 '%'와 '_'는 특수한 용도로 사용되는 문자인데, 만약 LIKE을 통해 검색하고자 하는 단어에 '%'와 '_'를 포함할 경우에는 'ESCAPE'를 사용하면 된다. 원하는 특수문자를 앞에 작성하고 '%'와 '_'를 사용하면 된다. 아래 예시는 '#'이라는 특수한 문자를 지정하여 '_'를 포함하는 NAME을 검색한다.

SELECT * FROM EMP WHERE NAME LIKE '%#_%' ESCAPE '#';

 

IN, NOT IN

IN 절에 NULL이 있을 경우, 무시함

 

NOT IN 절에 NULL이 있을 경우, 항상 true로 리턴하게 되어 NOT IN 결과가 항상 0건임.

 

 

INDEX

인덱스는 내림차순으로 생성 및 정렬된다.

 

인덱스 구조에 따른 특징

  • 클러스터 인덱스
    • 인덱스의 리프 데이터가 곧 데이터 페이지다.
  • 비트맵 인덱스
    • 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하는 구조이다.
    • 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해 설계 되었다.
  • B-tree 인덱스
    • 브랜치 블록과 리프 블록으로 구성되며 브랜치 블록은 분기를 목적으로 하고 리프 블록은 인덱스를 구성하는 칼럼의 값으로 정렬된다.

 

 

 

ORDER BY

ORDER BY COUNT(*)은 가능하다.

 

ORACLE 은 NULL 을 가장 큰 값으로 취급하여 ORDER BY 시 맨 뒤로 정렬되고 SQL SERVER 반대로 가장 앞으로 정렬한다.

NULL의 순서를 조절하고 싶으면 ORDEY BY 마지막에 NULLS FIRST 또는 NULLS LAST를 사용한다.

 

ORDER BY 1, COL1 과 같이 숫자와 컬럼을 혼용하여 사용할 수 있다

 

 

Random Access

Netsted Loop는 이중포문이기 때문에 n * m 번만큼 랜덤 엑세스가 가장 많이 발생한다.

 

 

SQL Server - IDENTITY

IDENTITY [ ( seed , increment ) ]

  • SQL Server에서 사용할 수 있는 데이터타입으로 데이터를 식별할 수 있는 식별자 역할을 한다.
  • seed : 첫번째 행이 입력될 때의 시작값
  • increment: 증가되는 값
  • 해당 컬럼에 값을 넣을 경우 Error 발생 (Error 발생을 막기 위해서는 IDENTITY_INSERT 를 OFF 로 하면 되나 이런 경우 IDENTITY 를 쓴 의미가 없어짐)

 

Sub Query (서브 쿼리)

서브쿼리는 SELECT, FROM, HAVING, ORDER BY절 등에서 사용 가능

 

서브쿼리 자체에는 ORDER BY절을 사용할 수 없음

 

연관 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태의 서브 쿼리이다.

 

비연관 서브쿼리는 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용한다.

 

인라인 뷰

  • FROM 절에 사용한 서브 쿼리.
  • Dynamic View(동적 뷰)라고도 함.

중첩된 서브쿼리

  • WHERE 절에 사용한 서브 쿼리

 

스칼라 서브 쿼리

  • 한 레코드당 정확히 하나의 값을 반환하는 서브 쿼리.
  • JOIN으로 동일한 결과를 추출할 수도 있음.

 

Single Row(단일 행) 서브쿼리

  • 실행 결과가 항상 1건 이하인 서브쿼리
  • =, <, <= 등 단일행 비교 연산자와 함께 사용됨

 

Multi Row(다중 행) 서브쿼리

  • 실행 결과가 여러 건인 서브쿼리
  • IN, ALL 등 다중행 비교 연산자와 함께 사용됨

 

Multi Column(다중 컬럼) 서브쿼리

  • 여러 컬럼을 반환하는 쿼리
  • 메인 쿼리의 조건절에 여러 컬럼을 동시에 비교할 수 있음. 단 컬럼 개수와 컬럼의 위치가 동일해야 함
  • Oracle에서만 지원하고, Server SQL에서는 지원하지 않음

 

Transaction

Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것

Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상이다. 트랜잭션 격리성이 낮은 경우 발생한다.

Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상을 말한다. 트랜잭션 격리성이 낮은 경우 발생한다. 

 

저장점

  • 저장점을 정의하면 롤백 할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 저장점까지 트랜잭션의 일부만 롤백할 수 있음
  • 동일한 이름의 저장점이 중복된다면, 가장 마지막의 저장점이 적용됨.
  • 아래는 SP2로 롤백하고 커밋하므로 SP2 이후에 쿼리문장은 적용되지 않는 예제임 
    -- ORACLE 
SAVEPOINT SP1;
...
SAVEPOINT SP2;
...
ROLLBACK TO SP2;
COMMIT; 

-- SQL Server
SAVE TRANSACTION SP1;
...
SAVE TRANSACTION SP2;
...
ROLLBACK TRANSACTION SP1;
COMMIt;

 

 

UNION ALL

중복 로우를 허용하는 합집합

 

칼럼 ALIAS는 첫번째 SQL 모듈 기준으로 표현 됨

 

정렬 기준은 마지막 SQL 모듈에 표시하면 됨

 

 

VIEW(뷰)

View는 데이터 딕셔너리에 SQL문 형태로 저장하되 실행 시에 참조된다.

 

참조한 테이블이 변경되면 View도 변경 된다.

 

View를 통해 입력, 수정, 삭제에는 제약이 있다.

 

한번 생성된 View는 Alter문을 통해 변경할 수 없고, 삭제 후 재 생성해야 한다.

 

하나의 테이블에 여러 개의 View를 생성 가능하다.

 

특정 컬럼만 조회할 수 있도록 할 수 있기에 보안 기능이 있다.

 

데이터 관리가 수월해지고, SELECT 문장이 간단하다.

 

View는 인덱스를 만들 수 없다.

계층형

ORACLE의 PRIOR은 CONNECT BY 뿐만 아니라, SELECT, WHERE절에서도 사용 가능함

 

CONNECT_BY_ISLEAF : leaf 데이터면 1, 아니면 0을 반환한다.

 

CONNECT_BY_ISCYCLE : 중복참조를 체크하는 함수로 CONNECT BY NOCYCLE일 경우에만 사용 가능하다. 서로가 서로를 부모로 가지는 경우를 찾을 때 사용하고, 중복참조 이면 1을 아니면 0을 반환한다. (루트까지의 경로에 존재하는 데이터를 의미한다라고도 사용함.)

 

CONNECT_BY_ROOT : 루트 노드의 정보

 

SYS_CONNECT_BY_PATH : 하위 레벨의 칼럼 표시

 

SQL Server는 계층형 질의문은 CTR(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개

 

 

비교연산자

CHAR형

  • 길이가 다르다면 작은 쪽에 SPACE 를 추가하여 길이를 같게 한 후에 비교한다

VARCHAR형

  • 서로 다른 문자가 나올 때까지 비교한다
  • 길이가 다르다면 짧은 것이 끝날 때까지만 비교한 후에 길이가 긴 것이 크다고 판단한다
  • 길이가 같고 다른 것이 없다면 같다고 판단한다

 

 

조인

NATURAL JOIN

  • 동일한 타입과 이름을 가진 컬럼을 조인 조건으로 이용하는 조인으로 반드시 조인하는 대상의 테이블들은 서로 같은 이름과 타입의 컬럼이 존재해야한다.
  • NATURAL JOIN에 사용된 컬럼은 식별자를 사용할 수 없다. 
    -- EMP와 DEPT 테이블에 DEPOTNO 컬럼이 각각 존재하므로 DEPTNO로 NATURAL JOIN한다.
-- 단, DEPNO를 가지고 식별자를 사용하였으므로 (EMP.DEPTNO) 아래 쿼리는 에러가 발생
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP NATURAL JOIN DEPT;

 

USING JOIN

  • 두 개의 테이블의 컬럼명이 같을 경우 조인 조건을 길게 적지 않고 간단하게 적을 수 있도록 하는 역할
  • NATURAL JOIN과 동일하게 USING JOIN에 사용된 컬럼은 식별자를 사용할 수 없다. 
    SELECT DEPT_NO
FROM EMP INNER JOIN DEPT USING(DEPT_NO)

 

 

 

FULL OUTER JOIN

  • = LEFT OUTER JOIN UNION RIGHT OUTER JOIN (UNION이므로 중복을 제거함)

 

오라클 (+) 부호

  • 해당 테이블에 null을 허용한다는 의미이다.
  • 등호의 오른쪽 테이블에 (+)가 붙으면 오른쪽 테이블에 null을 허용하는 것이므로 LEFT OUTER JOIN
  • 등호의 왼쪽 테이블에 (+)가 붙으면 왼쪽 테이블에 null을 허용하는 것이므로 RIGHT OUTER JOIN이다.

 

참조문

DELETE(MODIFY) (액션들) : 참조무결성에서 부모가 삭제될 경우, 행할 액션을 지정

  • CASCADE : 부모 삭제시, 자식도 삭제
  • SET NULL : 부모 삭제시, 해당 필드 NULL로 설정
  • SET DEFAULT : 부모 삭제시, 해당 필드의 기본값 설정
  • RESTRICT : 자식 테이블에 PK 값이 없는 경우만 부모의 값을 삭제 가능
  • NO ACTION : 참조 무결성을 해치는 삭제가 불가능. 즉 자식에 해당하는 값이 있을 경우, 부모 삭제 불가능

 

INSERT (액션들) : 참조무결성에서 자식이 삽입될 경우, 행할 액션을 지정

  • AUTOMATIC : 자식을 삽입했는데 부모의 값이 없을 경우, 부모값을 생성하고, 자식값을 삽입
  • SET NULL : 자식을 삽입했는데 부모의 값이 없을 경우, 해당 필드를 NULL로 설정
  • SET DEFAULT : 자식을 삽입했는데 부모의 값이 없을 경우, 해당 필드를 기본값 설정
  • DEPENDENT : 자식을 삽입했는데 부모의 값이 없을 경우에는 삽입 불가능. 즉, 부모에 값이 없으면 자식은 삽입 불가능
  • NO ACTION : 참조 무결성을 해치는 입력이 불가능. 

 

ETC

Oracle의 경우 Select시 조회되는 Header Column명은 디폴트로 대문자로 표현한다.  

반응형