SQLD 02_01 SQL 기본 요약

관계형 데이터베이스 ( Relation DB )

1960년대 : 플로우차트 중심의 개발, 파일 구조를 통해 데이터를 저장하고 관리

1970년대 : DB 관리 기법이 태동되던 시기, 계층형 Hierachical, 망형 Network DB 상용화

1980년대 : 현재 대부분 기업에서 사용되는 관계형 DB의 상용화, Oracle, Sybase, DB2 등장

1990년대 : 인터넷 환경의 급속한 발전과 객체 지향 정보를 지원하기 위해 객체 관계형 DB로 발전

 

관계형 DB는 1970년대 E.F. Codd 박사의 논문에서 처음 소개되어

릴레이션에 데이터를 저장하고 관리하며 릴레이션을 사용해 연산한다

관계 Relation과 조인 연산을 통해 합집합 / 교집합 / 차집합 등을 만들 수 있다

 

DB와 DBMS 차이

DB 는 데이터를 어떤 형태의 자료구조로 사용하느냐에 따라 나뉜다

계층형 DB : 트리 형태에 데이터 저장하고 관리, 1:N 관계를 표현

네트워크형 DB : 오너와 멤버 형태로 데이터를 저장하고 관리

관계형 DB : 릴레이션에 대한 데이터를 저장하고 관리, 릴레이션을 사용해 집합, 관계 연산

 

DBMS 는 계층형 / 네트워크형 / 관계형 DB 등을 관리하기 위한 SW 이다

Oracle, MS-SQL, MySQL, Sybase 등이 여기에 속한다

 

관계형DB 집합연산

집합 연산	설명
합집합 ( Union )	두 릴레이션을 하나로 합치는 것 중복된 튜플은 한번만 조회
차집합 ( Diference )	본래 릴레이션에는 존재하고 다른 릴레이션에는 존재하지 않는 것을 조회
교집합 ( Intersection )	두 릴레이션 간 공통된 것을 조회
곱집합 ( Cartesian product )	각 릴레이션에 존재하는 모든 데이터를 조합하여 연산

관계형DB 관계연산

관계 연산	설명
선택 연산 ( Selection )	릴레이션에서 조건에 맞는 행만 조회
투영 연산 ( Projection )	릴레이션에서 조건에 맞는 속성만을 조회
결합 연산 ( Join )	여러 릴레이션의 공통된 속성을 사용해 새로운 릴레이션을 만듬
나누기 연산 ( Division )	기준 릴레이션에서 나누는 릴레이션이 가진 속성과 동일한 값을 가지는 행을 추출하고 나누는 릴레이션의 속성을 삭제한 후 중복된 행 제거

테이블의 구조

관계형 데이터베이스의 기본 단위인 테이블의 형태로 데이터가 저장된다

행 = 로우 = 튜플 / 열 = 칼럼 = 속성

기본키 PK는 한 테이블에서 유일성 ( Unique ) 괴 최소성 ( Not Null ) 를 만족하면서 테이블을 대표해야하고

외래기 FK는 다른 테이블의 PK를 참조하는 컬럼으로 결합 연산을 하기 위해 사용한다

---

SQL ( Structed Query Language )

 

관계형 데이터베이스에서 데이터를 정의/조작/제어 하기 위해 사용하는 절차형 언어이다

개발자가 작성한 SQL문을 3단계에 걸쳐 실행한다 ( 파싱 - 실행 - 인출 )

SQL 실행 순서	설명
파싱 Parsing	SQL 문의 문법확인, 구문분석 구문분석한 SQL 문은 Library Cache에 저장
실행 Execution	옵티마이저가 수립한 실행 계획에 따라 실행
인출 Fetch	데이터를 읽어서 전송

Parsing

소프트파싱

• SQL 파싱 정보를 저장하는 라이브러리 캐시에서 파싱된 정보를 찾을 수 있는 경우
• 파싱 단계를 거치지 않고 바로 실행 단계로 넘어간다
• 효율성이 높다

하드파싱

• 라이브러리 캐시에서 SQL문 파싱 정보를 찾을 수 없는 경우
• 파싱 후 실행 단계로 넘어간다
• 처음 하드 파싱 후 변수의 값만 다른 Select문은 소프트 파싱된다

SQL 종류

명령의 종류	명령어	설명
데이터 조작어 DML ( Data Manipulation Language )	SELECT	데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE 라고도 한다
	INSERT UPDATE DELETE	데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류의 명령어들을 말한다 데이터를 삽입, 수정, 삭제하는 것들의 명령어를 의미한다
데이터 정의어 DDL ( Data Difinition Language )	CREATE ALTER DROP RENAME	테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 그러한 구조를 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어
데이터 제어어 DCL ( Data Control Language )	GRANT REVOKE	데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어들을 DCL 이라 부른다
트랜잭션 제어어 TCL ( Transaction Control Language )	COMMIT ROLLBACK	논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위 ( 트랜젝션 ) 로 제어하는 명령어

DB는 DDL 과 DML 를 다르게 처리한다 ( 하지만 SQL Server 의 경우 모두 auto commit 처리 된다  )

DDL 명령어는 직접 DB 테이블에 영향을 미치기 때문에 즉시 완료 ( auto commit ) 된다

DML 명령어는 조작하려는 테이블 메모리를 버퍼에 올리고 작업하기 때문에

실시간으로 영향을 미치지 않고 COMMIT 명령어를 입력하여 TRANSACTION 을 종료해야한다

 

작업 순서는 DCL ( 권한부여 ) - DDL ( 데이터 구조정의 ) - DML ( 데이터 조회 ) 

TRANSACTION

DB의 작업을 처리하는 단위

트랜잭션의 특성	설명
원자성 ( Atomicity )	DB 연산의 전부 또는 일부 ( all or nothing ) 실행 트랜잭션의 처리가 완전히 끝나지 않았을때는 전혀 이루어지지 않은 것과 같다
일관성 ( Consistency )	트랜잭션 실행 결과로 DB 상태에 모순이 없고 실행 후에도 일관성 유지
고립성 ( Isolation )	트랜잭션 실행 중 생성하는 연산의 중간 결과는 다른 트랜잭션이 접근할 수 없다 부분적인 실행 결과를 다른 트랜잭션이 볼 수 없다
연속성 ( Durability )	트랜잭션이 그 실행을 성공적으로 완료하면 그 결과는 영구적으로 보장

---

DDL ( Data Definition Language )

DB 를 사용하기 위해서는 먼저 테이블을 생성해야한다

SQL 문	설명
CREATE TABLE	새로운 테이블을 생성 테이블을 생성할 때 기본키/ 외래키/ 제약사항 등을 설정할 수 있다
ALTER TABLE	생성된 테이블을 변경 칼럼을 추가/ 변경/ 삭제할 수 있고 기본키/ 외래키를 설정할 수 있다
DROP TABLE	해당 테이블을 삭제 데이블의 테이터 구조와 저장된 데이터 모두 삭제된다

CTAS

SELECT 문장을 통해 복제 테이블을 생성할 수 있고 이때 칼럼별로 데이터 유형을 다시 재정의하지 않아도 되지만

기본 테이블의 제약조건 중에 NOT NULL 외에 PK, FK, 고유키, CHECK 등의 다른 제약 조건은 없어진다

CREATE TABLE 테이블명 AS SELECT 칼럼명 FROM 복사할 테이블명;

CONSTRAINT

데이터 무결성을 위한 방법으로 사용자가 원하는 조건의 데이터만 유지하는 제약조건를 둘 수 있다

칼럼명 앞에 'CONSTRAINT' 를 입력하여 제약조건을 설정한다

구분	설명
PRIMARY KEY	테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 정의한다 하나의 테이블에 하나의 기본키 제약만 정의할 수 있다 ' 기본키 제약 = 고유키 제약 & NOT NULL 제약 '
UNIQUE KEY	테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의한다 단 NULL 은 고유키 제약의 대상이 아니므로 NULL 값을 가진 행이 여러 개가 있더라도 고유키 제약이 위반 되지 않는다
NOT NULL	NULL 값의 입력을 금지한다 NOT NULL 을 CHECK 의 일부분으로 이해할 수 있다
CHECK	입력할 수 있는 값의 범위를 제한한다 CHECK 제약으로는 TRUE or FALSE 로 평가할 수 있는 논리식을 지정한다
FOREIGN KEY	관계형 데이터베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성된다 외래키 지정시 참조 무결성 제약 옵션을 선택할 수 있다

CASCADE

참조관계인 PK와 FK가 있을 경우 참조되는 데이터를 자동으로 삭제할 수 있다

다른 테이블을 참조하는 컬럼 앞에 ' ON DELETE CASCADE ' 를 입력하여 사용할 수 있다

ALTER

테이블 변경 / 칼럼명 변경

ALTER TABLE 기존테이블명 RENAME TO 새로운테이블명;

ALTER TABLE 테이블명 RENAME COLUMN 기존칼럼명 TO 새로운 칼럼명;

 

칼럼 추가 /  변경 / 삭제

ALTER TABLE 테이블명 ADD COLUMN ( 컬럼명 테이터유형 );

ALTER TABLE 테이블명 MODIFY ( 칼럼명 데이터유형 );

ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;

제약조건 삭제

ALTER TABLE 테이블명 DROP CONSTARAINT 제약조건명;

DROP

테이블의 구조와 데이터를 모두 삭제한다

CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있던 참조되는 제약조건에 대해서도 삭제한다

DROP TABLE 테이블명 CASCADE CONSTRAINT;

TRUNCATE 

TRUNCATE 는 테이블 자체의 삭제가 아닌 해당 테이블내 모든 행들이 삭제되어 저장 공간을 재사용할 수 있도록 한다

빠른 삭제를 위해 로그도 기록하지 않는다

구분	테이블 정의 존재유무	저장 공간	작업 속도	SQL 구분
DROP	삭제	반납	빠름	DDL
TRUNCATE	존재	반납	빠름	DDL
DELETE	존재	유지	느림	DML

VIEW

테이블로 유도된 가상의 테이블이다

원하는 칼럼만 조회가 가능해지고 참조한 테이블이 변경되면 뷰도 변경된다

뷰는 특정 칼럼만 조회하므로 보안성을 향상시키고 한번 생성된 뷰는 변경이 불가능하다

---

DML ( Data Manipulation Language )

SELECT

테이블에 입력된 데이터를 조회하기 위해 사용한다 특정한 칼럼이나 특정 행만을 조회할 수 있다

중복된 데이터를 한 번만 조회하는 DISTINCT 와 ALIAS 를 사용하여 별칭을 부여하여 조회할 수 있다

SECECT A || '맛' FROM FLAVOUR;
/* A 칼럼 뒤에 맛이라는 문자를 결합하여 조회한다

INSERT

테이블에 데이터를 입력하는 DML문으로 최종적으로 TCL COMMIT 를 실행해야한다

INSERT INTO 테이블명 ( 칼럼명1, 칼럼명2 ... ) VALUES ( 값1, 값2 .. );
/* 칼럼명을 생략할 경우 칼럼 순서대로 데이터가 빠짐없이 입력되어야한다

 

 

UPDATE

값을 수정하려면 UPDATE 문을 사용해야한다 조건을 입력하지 않으면 모든 데이터가 수정된다

UPDATE 테이블명 SET 수정할컬럼명 = 새로운값 WHERE 조건;

DELETE

테이블 삭제와 테이블 내 행을 삭제할 때 사용한다

DELETE 삭제할테이블명;

DELETE FROM 테이블명 WHERE 조건;

---

WHERE

WHERE 문 연산자의 종류

연산자의 우선순위

LIKE

와일드 카드를 사용하여 데이터를 조회할 수 있다

와일드 카드	설명
%	어떤 문자를 포함한 모든 것들을 조회한다 예를들어 '%es%' 는 문자 중간에 es 가 포함된 모든 문자를 조회
-	한 개인 단일 문자를 의미한다 예를들어 'name_' 는 name 뒤에 한 글자만 더 붙는 것을 조회
*	all 모든 것을 의미한다

BETWEEN

지정된 범위에 있는 값을 조회하고 양 끝 조건을 포함한다

SELECT 칼럼명 FROM 테이블명 WHERE 컬럼명 BETWEEN 조건1 AND 조건2;

IN

'OR' 의 의미를 가지고 있어 한 조건만 만족해도 조회가 가능하다

SELECT * FROM EMP WHERE (JOB, NAME) IN (('CLERK','SALES'),('AAA','BBB'));
/* IN 조건에 여러 개의 컬럼을 사용할 수도 있다 JOB이 둘 중 하나이거나 NAME이 둘 중 하나라면 조회

NULL

모르는 값, 값의 부재를 의미하고 모든 비교는 ' 알 수 없음 ' 을 반환한다

NULL 에 숫자/ 날짜를 더하면 NULL 이 되고 비교연산은 FALSE 가 나온다

NULL 관련함수

NULL 함수	예시
NVL()	NVL(MGR,0) MGR 칼럼이 NULL 일 경우 0으로 변경
NVL2()	NVL(MGR,1,0) MGR 칼럼이 NULL 아닌 경우 1 맞으면 0
NULLIF()	NULLIF(n1, n2) n1과 n2 가 같으면 NULL 아니면 n1
COALESCE()	COALESCE(MGR,1) MGR이 NULL 이 아니면 1

---

SELECT 실행순서

GROUP BY 절과 ORDER BY 절이 같이 사용될 때 SELECT 문장은 아래 6개의 절로 구성된다

SELECT 칼럼명 AS 별칭

FROM 테이블명

WHERE 조건식

GROUP BY 칼럼/ 표현식

HAVING 그룹조건식

ORDER BY 칼럼/ 표현식;

SELECT 문장의 수행 단계

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY 이다

---

BUILT-IN FUNCTION

모든 DB는 SQL 에서 사용할 수 있는 내장형 함수를 가진다

DUAL 테이블은 Oracle DB에 의해 자동으로 생성되는 테이블이다 Dummy 테이블 !

내장형 함수 종류

자주 출제되는 내장형 함수를 기억해두자 !

---

DECODE 와 CASE

DECODE 문은 IF 문을 구현할 수 있고 특정 조건이 참이면 A 거짓이면 B로 응답한다

SELECT DECODE(DPT_ID, 23, 'TRUE', 'FALSE') FROM EMP;

CASE 문은 IF ~ THEN ELSE-END 구문을 조건문으로 사용한다

SELECT CASE
		THEN DPT_ID = 23 THEN 'GROUP A'
		THEN DPT_ID = 24 THEN 'GROUP B'
		ELSE 'GROUP C'
	FROM EMP;
/* DPT_ID 가 23 이면 A 그룹을 24 이면 B 그룹 그렇지 않으면 C 그룹 출력

---

ROWNUM

SELECT 문의 결과에 대해 논리적인 일렬번호를 부여한다

조회되는 행 수를 제한할때 사용하며 여러 개의 행을 가져올 경우 인라인뷰를 사용하며 별칭을 사용해야한다

SELECT * FROM EMP WHERE ROWNUM <= 1; # 한 행 조회

SELECT * FROM ( SELECT ROWNUM list, ENAME FROM EMP ) WHERE list <= 5;

ROWID

Oracle DB 내에서 데이터를 구분할 수 있는 유일한 값이다

데이터가 어떤 데이터 파일, 어떤 블록에 저장되어 있는지 알 수 있다

---

DCL ( Data Control Language )

GRANT

DB 사용에 권한을 부여해 연결/ 입력/ 수정/ 삭제/ 조회를 할 수 있다

GRANT 권한 ON 테이블명 TO 사용자;

REVOKE

DB 사용자에게 부여된 권한을 회수한다

REVOKE 권한 ON 테이블명 FROM 사용자;

---

TCL ( Transaction Control Language )

COMMIT

입력/ 수정/ 삭제한 자료에 대해 전혀 문제가 없다고 판단되면 트랜잭션을 완료할 때 사용한다

COMMIT 이후 데이터에 대한 변경 사항이 데이터 베이스에 반영되고 이전 데이터는 영원히 잃는다

모든 사용자가 결과를 볼 수 있고 관련된 행에 대한 잠금이 풀린다

다른 사용자들이 행을 조작할 수 있게 되고 하나의 트랜젝션 과정이 종료된다

ROLLBACK

데이터에 대한 변경 사용을 모두 취소하고 트랜젝션을 종료한다

COMMIT 과 ROLLBACK 을 통해 데이터의 무결성이 보장된다

 

SAVEPOINT

트랜젝션을 작게 분할하여 관리하는 것으로 지정된 위치까지만 트랜잭션을 ROLLBACK 할 수 있다

SAVEPOINT 세이브포인트명; # 세이브포인트 정의

ROLLBACK TO 세이브포인트명; # 세이브포인트까지 ROLLBACK 

# 세이브포인트 없이 ROLLBACK 할 경우 변경된 모든 데이터를 취소한다

---

참고

https://cafe.naver.com/sqlpd

데이터 전문가 포럼 (빅데이터분석기사... : 네이버 카페