SQLD - 2과목 SQL 기본

1. 관계형 데이터베이스 개요

 

데이터베이스와 DBMS(Database Management System)

- 데이터베이스 : 데이터의 집합, 꼭 형식을 갖추지 않아도 엑셀 파일을 모아 둔다면 그것 또한 데이터베이스

- DBMS : 데이터를 효과적으로 관리하기 위한 시스템 개인이 파일을 여러 개 묶어서 폴더에 보관하면 데이터를 찾고 관리하는데 많은 비용이 발생 이를 보다 시스템적으로 작동하게 만든 시스템을 DBMS (Oracle, Mysql ...)

 

관계형 데이터베이스 구성 요소

- 계정 : 데이터의 접근 제한을 위한 여러 업무별/시스템별 계정이 존재

- 테이블 : DBMS의 DB안에서 데이터가 저장되는 형식

- 스키마 : 테이블이 어떠한 구성으로 되어있는지, 어떠한 정보를 가지고 있는지에 대한 기본적인 구조를 정의

 

테이블

- 하나의 테이블은 반드시 하나의 유저(계정) 소유여야 함

테이블간 관계는 1:1, 1:N, N:M 관계를 가질 수 있음

 

- 테이블명은 중복될 수 없지만, 소유자가 다른 경우 같은 이름으로 생성 가능

 

SQL

 - 관계형 데이터베이스에서 데이터 조회, 조작, DBMS 시스템 관리 기능을 명령하는 언어

 DDL : 데이터 정의

 DML : 데이터 조작

 DCL : 데이터 제어 언어

 

관계형 데이터베이스 특징

 - 데이터의 분류, 정렬, 탐색, 속도가 빠름

 - 신뢰성이 높고, 데이터의 무결성 보장

 - 기존의 작성된 스키마를 수정하기 어려움

 - 데이터베이스의 부하를 분석하는 것이 어려움

 

데이터 무결성(integrity)

 - 데이터의 정확성과 일관성을 유지하고, 데이터에 결손과 부정합이 없음을 보증하는 것

 - 저장된 값과, 현실의 비즈니스 모델의 값이 일치하는 정확성, 데이터 무결성을 유지하는 것이 DBMS의 중요한 기능

 

데이터 무결성 종류

 1. 개체 무결성 : 테이블의 기본키를 구성하는 컬럼은 NULL 값이나 중복 값을 가질 수 없음

 2. 참조 무결성 : 외래키 값은 NULL 이거나 참조 테이블의 기본키 값과 동일해야 한다. (외래키란 참조 테이블의 기본키에 정의된 데이터만 허용하는 구조)

3. 도메인 무결성 : 주어진 속성 값이 정의된 도메인에 속한 값 이어야 함

4. NULL 무결성 : 특정 속성에 대해 NULL을 허용하지 않는 것이 특징

5. 고유 무결성 : 특정 속성에 대해, 값이 중복되지 않는 특징

6. 키 무결성 : 하나의 관계에는 적어도 하나의 키가 존재해야 함

(테이블이 서로 관계를 가질 경우 반드시 하나 이상의 조인키를 가짐)

 

ERD(Entity Relationship Diagram)

 - ERD란 테이블 간 서로의 상관 관계를 그림으로 표현한 것

 - ERD의 구성요소는 엔터티, 관계, 속성이 있다. (현실 세계의 데이터는 3가지 구성으로 모두 표현 가능)

 

SQL 종류

 

- DDL ( Definition ) - CREATE, ALTER, DROP, TRUNCATE

 

- DML ( Manipulation ) - INSERT, DELETE, UPDATE, MERGE

 

- DCL ( Control ) - GRANT, REVOKE

 

- TCL (Transaction Control) - COMMIT, ROLLBACK

 

- DQL (Query) - SELECT (어디에도 속하지 않아 SELECT를 위한 정의가 생김)

 

SELECT문 구조

 

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

 

테이블 탐색 -> 조건 확인 -> ( 세부 조건 확인) -> 추출할 컬럼 정의 -> 순서 정의

 

ORDER BY가 제일 마지막에 오기 때문에, SELECT 문에서 AS를 사용한 별칭을 사용해서 결과 값 오름차순,내림차순 변경 가능

 

SELECT 절

SELECT 문장을 사용하여 불러올 컬럼명, 연산 결과를 작성하는 절

이미 존재하는 예약어는 별칭으로 사용 불가 - ( avg, count, decode, SELECT, FROM 등등...)

 

별칭에 공백이나 특수문자가 포함되는 경우 쌍따옴표로 구분해줘야함

 

FROM 절

 - 데이터를 불러올 테이블 명 또는 뷰명 전달

 - 테이블 여러 개 컴마로 구분해서 전달 -> 조인 조건 없이 테이블 명만 나열 시 카티시안 곱 발생 주의!

 

함수 정의

 - input value 와 output value의 관계를 정의한 객체

 

함수 기능

- 데이터의 계산을 수행

 - 개별 데이터의 항목을 수정

 

단일행 함수 : input과 output의 관계가 1:1

복수행 함수 여러 건의 데이터를 동시에 입력 받아서 하나의 요역값을 리턴 (그룹함수 또는 집계함수라고도 함)

 

문자함수 종류

LOWER(str) 문자열을 소문자로

UPPER(str) 문자열을 대문자로

SUBSTR(str,m,n) 문자열 중 m 위치에서 n개의 문자열 추출 (n 생략 시 끝까지 추출 m이 음수일 경우 뒤에서 m번째 부터 오른쪽 n개의 문자열)

INSTR(str, 찾을문자열, m, n) 찾을 문자열 위치 반환 ( m 위치에서 시작 n번째 발견된 문자열 위치)

LTRIM(str, 삭제문자열) 문자열 중 특정 문자열을 왼쪽에서 삭제

RTRIM(str,삭제문자열) 문자열 중 ''' 오른쪽에서 삭제

TRIM (str) 특정 문자열을 양쪽에서 삭제

LPAD(str, n, 문자열) 대상 왼쪽에 문자열을 추가하여 총 n의 길이 리턴

RPAD(str, n, 문자열) 대상 오른쪽에 ''

CONCAT(str1, str2) str1 + str2

LENGTH(str) 문자열 길이

REPLACE(str, 찾을 문자열, 바꿀 문자열) 문자열 치환 및 삭제

TRANSLATE(str, 찾을 문자열, 바꿀 문자열) 글자를 1:1로 치환

 

숫자함수

ABS() 절대값 반환

ROUND(int, N) 소수점 특정 자리 N 에서 반올림 N이 음수면 정수자리에서 반올림

TRUNC(int, N) 소수점 특정 자리에서 버림

SIGN(int) 숫자가 양수면 1 음수면 -1 0이면 0반환

FLOOR(int) 작거나 같은 최대 정수 리턴

CEIL(int) 크거나 같은 최소 정수 리턴

MOD(int1, int2) int1을 int2로 나누어 나머지 반환

POWER(int1, int2) int1의 int2 거듭제곱 int1^int2

SQRT(int) 루트값 리턴

 

그룹함수

- 여러 값이 input 값으로 들어가서 하나의 요약된 값으로 리턴

- count, sum, avg, min, max, variance 분삭, stddev 표준편차 - ( NULL을 무시하고 연산)

 

일반함수

DECODE(Col, 10, '인사부', 20, '재무부') - 10이면 인사부 20이면 재무부

NVL(Col, 0) - Col 값이 Null일 경우 치환값 0으로 치환하여 리턴

NVL2(Col, a, b) - Col 값이 Null 이면 b로 치환, Null이 아니면 a로 치환

ISNULL(Col, A) - Col 이 Null이면, A가 리턴

NULLIF(A1, A2) - A1 A2 값이 같으면 NULL리턴, 다르면 A1리턴

CASE문 - 조건별 치환 및 연산 수행

NULLIF는 널을 갖는 값을 치환하기 위한 못적이 아닌 특정 값과 일치하는 대상을 NULL로 치환하기 위해 사용

 

Where 절

NULL 조회 시 IS NULL/ IS NOT NULL 사용 ( = 연산자로 조회 불가)

 

!= / <> ( 같지 않은 조건 검색)

 

ORACLE은 문자 상수의 경우 대소문자를 구분

MSSQL은 기본적으로 문자상수의 대소문자를 구분하지 X

 

BETWEEN A AND B 연산자

A <= X <= B 조건 / 반드시 A가 B보다 작아함, 반대로 작성시 아무것도 출력되지 X

 

WHERE COL BETWEEN A AND B;

 

LIKE 연산자

- 정확하게 일치하지 않아도 되는 패턴 조건 전달 시 사용

- % / _ 와 함께  사용 % = 모든 / _ = 1개당 1자리

 

- S% S로 시작

- %S% S를 포함하는

- %S S로 끝나는

- __S__ 길이가 5글자인 가운데 S인 

 

GROUP BY

- 각 행을 특정 조건에 따라 그룹으로 분리하여 계산하도록 하는 구문식

- 그룹에 대한 조건은 WHERE절에서 사용 불가

- GROUP BY절을 사용하면 데이터가 요약되므로 요약되기 전 데이터와 함께 출력할 수 없음

 

HAVING 절

- 그룹 함수 결과를 조건으로 사용할 때 사용되는 절

- 내부 연산 순서가 SELECT 절 보다 먼저이므로 SELECT 절에서 사용된 Alias 사용 불가

 

ORDER BY 절

- 마지막에 수행되기 떄문에 ALIAS 명 사용 가능

- 정렬 순서 ASC 오름, DESC 내림, 생략시 오름차순 정렬

 

오름차순

가나다라/1234/abcd/과거부터~현재순

 

복합정렬

- 먼저 정렬한 값의 동일한 결과가 있을 경우 추가적으로 정렬 가능

 

ORDER BY 1 DESC, 2 ASC

SELECT 절의 1번째 COL에 대해 내림차순 먼저 정렬 후, 같은 값이 있으면 2번째 COL값으로 오름차순 정렬

 

NULL의 정렬

- NULL 을 포함한 값의 정렬 시 ORACLE은 기본적으로 NULL을 마지막에 배치 SQL Server는 처음에 배치

- ORACLE은 ORDER BY절에 NULLS LAST | NULLS FIRST를 명시하여 NULL 정렬 순서 변경 가능

 

JOIN

- 여러 테이블의 데이터를 사용하여 동시 출력하거나 참조 할 경우 사용

- FROM 절에 조인할 테이블 나열

- 동일한 열 이름이 여러 테이블에 존재할 경우 열 이름 앞에 테이블 이름이나 테이믈 Alias 붙임

 

조인 종류

1) 조건의 형태에 따라

- EQUI JOIN 등가 조인 : JOIN 조건이 동등 조건인 경우

- NON EQUI JOIN : JOIN 조건이 동등 조건이 아닌 경우

 

2. 조인 결과에 따라

- INNER JOIN : JOIN 조건에 성립하는 데이터만 출력하는 경우

- OUTER JOIN : JOIN 조건에 성립하지 않는 데이터도 출력하는 경우 ( LEFT/RIGHT/FULL OUTER)

 

3. NATURAL JOIN : 조인 조건 생략 시 두 테이블에 같은 이름으로 자연 연결되는 조인

4. CROSS JOIN : 조인 조건 생략 시 두 테이블의 발생 가능한 모든 행을 출력하는 조인

5. SELF JOIN : 하나의 테이블을 두 번 이상 참조하여 연결하는 조인

 

EQUI JOIN

- 조인 조건이 = equal 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 얻는 조인 방법

- WHERE 절에 두 테이블의 공통 컬럼에 대한 조인 조건을 나열

 

NON EQUI JOIN

- 테이블을 연결짓는 조인 컬럼에 대한 비교 조건이 =이 아닌 연산자를 사용하는 조인 조건

 

카티시안 곱 - 정상 조인보다 더 많은 수의 행이 리턴

 

SELF JOIN

- 한 테이블 내 각 행끼리 관계를 갖는 경우 조인

- 한 테이블을 참조 할 때마다 명시해야 함

- 테이블 명이 중복되므로 반드시 테이블 별칭 사용

 

예시 EMP 테이블에서 상위관리자 보다 급여가 많은 직원 출력 (WHERE E1.MGR = E2.EMPNO AND ~~)

테이블 한 번 스캔 시 매니저 정보는 없으므로 셀프조인 필요

원하는 정보를 모두 한 행으로 출력 후 조건 선택 가능

 

INNER JOIN

- 내부 조인이라고 하며, 조인 조건이 일치하는 행만 추출(ORACLE 조인 기본)

- FROM 절에 INNER JOIN or JOIN을 명시

- USING 이나 ON 조건절을 필수적으로 사용

 

ON 절

- 조인할 양 컬럼의 컬럼명이 서로 다르더라도 사용 가능

- ON 조건의 괄호는 옵션(생략가능)

 

ORACLE 표준은 FROM 절에 테이블을 컴마로 구분, WHERE 절에 조인 조건 나열

ORACLE은 INNER JOIN이 기본 조인 연산이므로 별도의 문법 존재 안함

 

USING 조건절

- 조인할 컬럼명이 같을 경우 사용

- Alias나  테이블 이름 같은 접두사 붙이기 불가

- 괄호 필수

 

USING (DEPTNO)

 

NATURAL JOIN

- 두 테이블 간의 동일한 이름을 가지는 모든 컬럼들에 대해 EQUI JOIN을 수행

- USING, ON, WHERE 절에서 조건 정의 불가

- JOIN에 사용된 컬럼들은 데이터 유형이 동일해야 하며, 접두사를 사용 불가

 

 

NATURAL JOIN은 동일한 이름의 모든 컬럼을 조인 컬럼으로 사용하므로 조인 컬럼의 값이 모두 같을 때만 결과가 리턴 됨

 

CROSSJOIN

- 테이블 간 JOIN 조건이 없는 경우 생성 가능한 모든 데이터들의 조합 ( 카타시안 곱 출력)

- 양쪽 테이블 행의 수의 곱한 수의 데이터 조합이 발생 

 

OUTER JOIN

- JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용

- LEFT OUTER JOIN, RIGHT, FULL

- OUTER 생략 가능

 

LEFT OUTER JOIN - 왼쪽 테이블이 기준

- 우측 값에서 같은 값이 없는 경우 NULL 값으로 출력

 

FULL OUTER JOIN

- 두 테이블 전체 기준으로 결과를 생성하여, 중복 데이터는 삭제 후 리턴

- LEFT 와 RIGHT의 결과의 UNION  연산 리턴과 동일

- ORACALE 표준에는 없음 (+) 양방향 전달 시 오류  

ORACLE은 FULL OUTER JOIN이 없기 떄문에 LEFT / RIGHT 구해서 서로 UNION

 

ORACLE 표준은 WHERE 절에 조인 조건을 작성하므로, LEFT OUTER JOIN을 기술하지 X

WHERE절에서 기준이 되는 테이블 반대 테이블 조건 컬럼 뒤에 (+)를 붙임

 

FROM S, P

WHERE S.COL = P.COL(+)