2과목
part1. SQL 기본
✔️ DB
특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것
✔️ DBMS
효율적인 데이터 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 SW
✔️ SQL
관계형 DB에서 데이터 정의, 조작, 제어를 위해 사용하는 언어
✔️ SQL 문장들의 종류
- DML : SELECT, INSERT, UPDATE, DELETE 등 데이터 조작어
- DDL : CREATE, ALTER, DROP, RENAME 등 데이터 정의어
- DCL : GRANT, REVOKE 등 데이터 제어어
- TCL : COMMIT, ROLLBACK 등 트랜잭션 제어어
✔️ 테이블
데이터를 저장하는 객체
- 로우와 칼럼으로 구성
✔️ 정규화
데이터의 정합성 확보와 데이터 입력/수정/삭제 시 발생할 수 있는 이상현상 방지 목적
✔️ 기본키
테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 칼럼
✔️ 외부키
다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 칼럼
✔️ ERD 구성요소
엔터티, 관계, 속성
✔️ 데이터 유형 (oracle / sql server)
- CHAR(s) : 고정 길이 문자열 정보
- VARCHAR(s) : 가변 길이 문자열 정보
- NUMBER / NUMERIC : 정수, 실수 등 숫자 정보
- DATE / DATETIME : 날짜와 시각 정보
✔️ 제약조건
- PRIMARY KEY(기본키) : 기본키 정의
- UNIQUE KEY(고유키) : 고유키 정의
- NOT NULL : NULL 값 입력금지
- CHECK : 입력 값 범위 제한 / NULL 무시
- FOREIGN KEY(외래키) : 외래키 정의
✔️ 테이블 구조 변경(칼럼 추가, 삭제 등) DDL
# SQL SERVER
ALTER TABLE 테이블명 ADD 칼럼명 데이터 유형
ALTER TABLE 테이블명 DROP COLUMN 칼럼명
ALTER TABLE 테이블명 MODIFY 칼럼명 데이터유형 (DEFAULT식 NOT NULL)
ALTER TABLE 테이블명 ALTER 칼럼명 데이터유형 (DEFAULT식 NOT NULL)
# ORACLE
ALTER TABLE 테이블명 MODIFY 칼럼명 데이터유형 (DEFAULT식 NOT NULL)
ALTER TABLE 테이블명 RENAME COLUMN 변경전 칼럼명 TO 뉴칼람명
ALTER TABLE 테이블명 DROP CONSTRAINT 조건명 : 제약조건 삭제
ALTER TABLE 테이블명 ADD CONSTRAINT 조건명 조건 (칼럼명) : 조건 추가
DROP TABLE 테이블명[CASCADE CONSTRAINT]
TRUNCATE TABLE 테이블명;행제거, 저장공간 재사용
-- CASCADE CONSTRAINT: 참조되는 제약조건 삭제
-- DEPENDENT : 관계형 데이터베이스에서 Child Table의 FK 데이터 생성시 Parent Table에 PK가 없는 경우, Child Table 데이터 입력을 허용하지 않는 참조동작
DROP DDL 정의 자체를 완전 삭제
| TRUNCATE | DDL(일부 DML) | 테이블 구조 비우기 |
| DELETE | DML | 데이터만 삭제 |
✔️ DML
- oracle : DDL - AUTO COMMIT
- sql server : DDL, DML - AUTO COMMIT
INSERT INTO 테이블명 (컬럼 리스트) VALUES (컬럼값);
UPDATE 테이블명 SET 수정할 컬럼명 = 값;
DELETE FROM 테이블명;
SELECT 컬럼리스트 FROM 테이블명;
✔️ 와일드카드
-
- : 모든 / % : 모든 / - : 한 글자
✔️ 합성 연산자
문자와 문자 연결
- | | (oracle), + (sql server)
✔️ TCL
트랜잭션 : 밀접히 관련되어 분리될 수 없는 1개 이상의 DB 조작
- COMMIT : 저장
- ROLLBACK : 트랜잭션 시작 이전의 상태로 되돌림
- SAVEPOINT : 저장 시점
✔️ 트랜잭션의 특성
- 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되거나 전혀 실행되지 않아야 함
- 일관성 : 트랜잭션 실행 전 DB 내용이 잘못되지 않으면 실행 후도 잘못되지 않아야 함
- 고립성 : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨
- 지속성 : 트랜잭션이 성공적으로 수행되면 DB의 내용은 영구적으로 저장됨
✔️ 연산자의 종류
- != 같지 않다
- <> 같지 않다
- BETWEEN A AND B : A와 B 사이의 값
- LIKE ‘비교문자열’ : 비교문자열과 형태가 일치
- IS NULL : NULL 값인 경우
- NOT IN (list) : list의 값과 일치하지 않음
- IS NOT NULL : NULL 값을 갖지 않음
✔️ 연산자 우선순위
( ) → NOT → 비교연산자 → AND → OR
- NULL 값과의 수치연산 → NULL 값 리턴
- NULL 값과의 비교연산 → FALSE 리턴
- ROWNUM : 원하는 만큼의 행을 가져올 때 사용
- TOP : (SQL SERVER)
SELECT TOP(1) PLAYER_NAME FROM PLAYER;
✔️ 문자형 함수
- ASCII : 문자의 ASCII 값 반환
- CHR/CHAR : ASCII 값에 해당하는 문자 변환
- CONCAT : 문자열 1, 2를 연결
- SUBSTR / SUBSTRING : 문자열 중 m 위치에서 n개의 문자 반환
SUBSTR(‘SQL Expert’,5,3)
-> ‘Exp’
LTRIM(‘xxxYYZZxYZ’,‘x’)
-> ‘YYZZxYZ’
RTRIM(‘XXYYzzXYzz’,‘z’)
-> ‘XXYYzzXY’
TRIM(‘x’ FROM ‘xxYYZZxYZxx’)
->
‘YYZZxYZ’
✔️ 숫자형 함수
- SIGN : 숫자가 양수면 1, 음수면 -1, 0이면 0 반환
- MOD(숫자1, 숫자2) : 숫자1을 2로 나누어 나머지 반환
- CEIL / CEILING(n) : 크거나 같은 최소 정수 반환
- FLOOR(n) : 작거나 같은 최대 정수 리턴
- ROUND(숫자, 표현 자릿수) : 반올림
- TRUNC(숫자, 표현 자릿수) : 버림
✔️ 날짜형 함수
- SYSDATE / GETDATE( ) : 현재 날짜와 시각 출력
- EXTRACT / DATEPART : 날짜에서 데이터 출력
- TO_NUMBER(TO_CHAR(d, ‘YYYY’)) / YEAR(d)
✔️ CASE
CASE WHEN ~
✔️ NULL 관련 함수
- NVL(식1, 식2) / ISNULL(식1, 식2) : 식1의 값이 NULL이면 식 2 출력
- NULLIF(식1, 식2) : 식1이 식2와 같으면 NULL을, 아니면 식1을 출력
- COALESCE(식1, 식2) : 임의의 개수표현식에서 NULL이 아닌 최초의 표현식, 모두 NULL이면 NULL 반환 ex) COALESCE(NULL, NULL, ‘abc’) ⇒ ‘abc’
✔️ 집계 함수
여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수
- GROUP BY 절은 행들을 소그룹화
- SELECT, HAVING, ORDER BY 절에 사용 가능
- ALL / DISTINCT 옵션
<aside> ✏️ 집계함수
- COUNT(*) : NULL 포함 행의 수
- COUNT(표현식) : NULL 제외 행의 수
- SUM. AVG : NULL 제외 합계, 평균 연산
- STDDEV : 표준편차
- VARIAN : 분산
- MAX, MIN : 최대값, 최소값
</aside>
✔️ ORDER BY 특징
- 조회된 데이터들을 다양한 목적에 맞게 정렬하여 출력
- ORDER BY 절에 컬럼명 대신 ALIAS 명이나 컬럼 순서를 나타내는 정수 사용 간으
- DEFAULT 값으로 오름차순 정렬
- SQL 문장의 제일 마지막에 위치
- SELECT 절에서 정의하지 않은 컬럼 사용 가능
※ oracle에서는 NULL을 가장 큰 값으로 취급, sql server에서는 NULL을 가장 작은 값으로 취급
✔️ 문장 실행 순서
SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY
SELECT TOP(2) WITH TIES EMAME, SAL
FROM EMP
ORDER BY SAL DESC;
# 급여가 높은 2명을 내림차순으로 출력하는데 같은 급여를 받는 사원은 같이 출력(WITH TIES)
✔️ JOIN
두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것
- n가지 테이블을 조인하기 위해서는 최소 n-1번의 조인이 필요
✔️ EQUI JOIN
2개의 테이블 간에 컬럼 값들이 서로 정확하게 일치하는 경우에 사용
SELECT PLAYER.PLAYER_NAME
FROM PLAYER
# 컬럼명 앞에 테이블 명 기술해야 함
✔️ NON EQUI JOIN
2개의 테이블 간에 컬럼 값들이 서로 정확하게 일치하지 않는 경우에 사용
- ‘=’ 연산자가 아닌 BETWEEN, >, ≤ 등의 연산자 사용
SELECT E.ENAME, E.JOB, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HSAL
✔️ NATURAL JOIN
WHERE 절에서 JOIN 조건 추가 불가
- SELECT EMP.DEPT처럼 OWNER명 사용 x
✔️ CROSS JOIN
WHERE 절에서 JOIN 조건 추가 가능
part2. SQL 활용
✔️ 집합 연산자
두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회할 때 사용
✔️ 일반 집합 연산자
- UNION : 합집합(중복 행은 1개로 처리)
- UNION ALL : 합집합(중복 행도 표시)
- INTERSECT : 교집합
- EXCEPT, MINUS : 차집합
- CROSS JOIN : 곱집합
✔️ FROM절 JOIN 형태
- INNER JOIN
- NATURAL JOIN → using, on, where 절에서 join 조건 x, SQL server 지원 x
- USING 조건절 → alias 사용 x
- ON 조건절 → alias이나 테이블명 반드시 사용
- CROSS JOIN
- OUTER JOIN
✔️ 계층형 질의
테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용
✔️ START WITH
계층 구조 전개의 시작 위치 지정, 결과에 포함
✔️ CONNECT BY
다음에 전개될 자식 데이터 지정
✔️ PRIOR
CONNECT BY 절에 사용되며, 현재 읽은 컬럼 지정
PRIOR 부모 = 자식 : ↑ 역방향
PRIOR 자식 = 부모 : ↓ 순방향
✔️ NOCYCLE
동일한 데이터가 전개되지 않음
✔️ ORDER SIBLINGS BY
형제 노드간의 정렬 수행
✔️ LEVEL
루트 데이터이면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가
✔️ CONNECT_BY_ISLEAF
해당 데이터가 리프 데이터면 1, 그렇지 않으면 0
✔️ CONNECT_BY_ISCYCLE
해당 데이터가 조상이면 1, 아니면 0
✔️ 셀프 조인
- 동일한 테이블 사이의 조인,
- FROM 절에 동일한 테이블이 2번 이상 나타남
- 반드시 테이블 별칭 사용해야 함
✔️ 서브쿼리
하나의 SQL문 안에 포함되어 있는 또 다른 SQL문
- 서브쿼리는 메인쿼리의 컬럼 모두 사용 가능, 메인 쿼리는 서브쿼리의 컬럼에 사용 x
✔️ 서브 쿼리 사용시 주의사항
- 서브쿼리를 괄호로 감싸서 사용
- 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능
- 서브쿼리에서는 ORDER BY 사용 불가
✔️ 단일 행 비교 연산자
=, <, > 등 IN, ALL, ANY, SOME 등도 가능, 반드시 1건 이하
✔️ 다중 행 비교 연산자
IN, ALL, ANY, SOME 등 2건 이상
✔️ 연관 서브쿼리
서브쿼리 내에 메인쿼리 컬럼이 사용된 서브쿼리, EXIST는 항상 연관 서브쿼리 사용
✔️ 스칼라 서브쿼리
SELECT : 한 행, 한 컬럼만을 반환하는 서브쿼리
✔️ 인라인 뷰
FROM : 임시적으로 생기는 동적 뷰, 테이블명이 올 수 있는 곳에 사용, ORDER BY 사용 가능
✔️ 뷰
테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지지 않음
✔️ 뷰 사용 장점
- 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 됨
- 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성 가능
- 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재할 때 사용
✔️ ROLLUP(A, B)
GROUP BY A, B
UNION ALL
GROUP BY A
UNION ALL
모든 집합 그룹 결과
- n+1 레벨의 subtotal 생성
- ROLLUP(A, B) ≠ ROLLUP(B, A)
✔️ CUBE(A, B)
GROUP BY A, B
UNION ALL
GROUP BY A
UNION ALL
GROUP BY B
UNION ALL
모든 집합 결과
- 결합 가능한 모든 값에 대하여 다차원 집계를 생성
- ROLLUP에 비해 시스템에 부하 심함
✔️ GROUPING SETS
GROUP BY A
UNION ALL
GROUP BY B
- 인수들에 대한 개별 집계를 구할 수 있음
- 다양한 소계 집합 생성 가능
- 순서 바뀌어도 상관 x
✔️ 윈도우 함수
행과 행간의 관계를 정의하거나 비교, 연산하는 함수 / OVER 필수
- RANK
- 특정 항목에 대한 순위를 구하는 함수
- 동일한 값에 대해 동일한 순위 부여(1, 2, 2, 4)
- DENSE_RANK - 동일한 순위를 하나의 등수로 간주(1, 2, 2, 3)
- ROW_NUMBER
- 동일한 값이라도 고유한 순위 부여
- SUM
- 파티션별 윈도우의 합 구할 수 있음 ex) 같은 매니저를 두고 있는 사원들의 월급 합
- MAX, MIN - 파티션별 윈도우의 최대, 최소값 ex) 같은 매니저를 두고 있는 사원들 중 최대 값
- AVG
- 원하는 조건에 맞는 데이터에 대한 통계 값 ex) 같은 매니저 내에서 앞의 사번과 뒤의 사번의 평균 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING → 현재 행을 기준으로 파티션 내에서 앞의 1건, 현재행, 뒤의 1건을 범위로 지정
- COUNT
- FIRST_VALUE
- 파티션별 윈도우에서 가장 먼저 나온 값
- SQL SERVER 지원 x
- LAST_VALUE
- 파티션별 윈도우에서 가장 나중에 나온 값
- SQL SERVER 지원 x
- LAG
- 파티션별 윈도우에서 이전 몇 번째 행의 값 가져옴
- SQL SERVER 지원 x
- LEAD - 파니션별 윈도우에서 이후 몇 번째 행의 값을 가져옴
- SQL SERVER 지원 x
- RATIO_TO_REPORT
- 파티션 내 전체 SUM 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있음
- 결과값은 0보다 크고 1보다 작거나 같음
- PERCENT_RANK
- 파티션별 윈도우에서 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여 행의 순서별 백분율을 구함
- CUME_DIST
- 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구함
- NTILE
- 파티션별 전체 건수를 인수 값으로 N등분한 결과 도출
✔️ Trigger
특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML 문이 수행되었을 때, DB에서 자동으로 동작하도록 작성된 프로그램
✔️ 프로시저와 트리거의 차이점
- 프로시저는 BEGIN-END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용 가능, EXCUTE 명령어로 실행
- 트리거는 BEGIN-END 절 내에 사용 불가, 생성 후 자동 실행, COMMIT, ROLLBACK 실행 안됨
part3. SQL 기본
✔️ 옵티마이저
사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할 수행
✔️ 규칙기반 옵티마이저
- 우선순위를 가지고 실행계획 생성
- 우선 순위가 높은 규칙이 적은 일량으로 해당작업을 수행한다고 판단
- 우선순위는 ROWID를 활용하여 테이블 액세스
- 나중순위는 전체 테이블 스캔
✔️ 비용기반 옵티마이저
- 현재 대부분의 DB에서 사용
- SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식
- 여기서 비용은 SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량을 의미
✔️ SQL 처리흐름도
SQL 내부적인 처리 절차를 시각적으로 표현한 도표
- 조인순서, 액세스기법, 조인기법 등 표현 가능
✔️ 인덱스
- 원하는 데이터를 쉽게 찾을 수 있도록 돕는 개념
- 검색 성능의 최적화를 목적으로 두고 있지만 느려질 수 있다는 단점이 존재
- 데이터 입력 후 인덱스 생성
✔️ NL Join
- 프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인을 수행
- 랜덤 엑세스 방식으로 데이터를 읽음
✔️ Sort Merge Join
- 조인 컬럼을 기준으로 데이터를 정렬하여 조인 수행
- 스캔 방식으로 데이터 읽음
✔️ Hash Join
- CPU 작업 위주로 처리
- NL Join의 랜덤 액세스 문제와 SMJ의 정렬 작업 부담을 해결하기 위한 대안으로 등장
'SQL > [SQLD]' 카테고리의 다른 글
| [SQLD] SQLD 개념 및 요약본 모음 (0) | 2023.06.12 |
|---|---|
| [SQLD] SQLD 기출 모음 34회, 35회, 38회, 39회, 45회, 노랭이 문제 (0) | 2023.06.12 |
| [SQLD] 1과목 요약 (2) | 2023.05.30 |