데이터베이스 설계와 구축 2부 물리 설계 - 10. SQL

데이터베이스 설계와 구축 - 성능까지 고려한 데이터 모델링 Part 2 물리설계

 

 

Chapter 10. SQL

 

1. SQL 개요

- SQL(Structured Query Language)은 관계형 데이터베이스에 접근(생성, 변경, 삭제, 조회)하기 위한 비절차적 표준 언어

 

2. SQL 구문별 상세 설명-DML

- 데이터를 조회하는 SELECT문

(1) SELECT : 추출할 데이터 항목을 기술. 사용자가 원하는 데이터 항목을 지정.

(2) FROM : 어떤 데이터오브젝트에서 무슨 테이블의 데이터를 조회할 것인지 기술.

(3) WHERE : 대상 테이블에서 가져올 데이터에서 조건을 부여하여 데이터를 조회하는 경우

(4) HIERACHY : 부모와 자식으로 연결된

(5) GROUP BY : 가져온 데이터를 그룹으로 조합하여 그룹에 대한 데이터 생성

(6) HAVING : GROUP BY에서 그룹으로 구성한 애용 중 조건을 부여하여 조건에 해당하는 데이터 생성

(7) UNION : 동일한 구성의 SQL 문장을 작성하여 통합 OR 제외시키는 일을 수행

(8) ORDER BY : 가져온 데이터를 주어진 컬럼값에 맞게 오름/내림차순으로 정렬

(9) FOR UPDATE : 데이터를 가져온 테이블에 잠금을 설정하여 다른 SQL 문장이 해당 테이블의 데이터를 수정하지 못하게 함

 

- SELECT 문장의 처리방법

* DBMS 내부에서 [FROM ➛ WHERE ➛ GROUP BY ➛ HAVING ➛ SELECT ➛ ORDER BY] 순으로 실행

(1) FROM 실행

 

(2) WHERE 실행

- WHERE 이하 조건에 맞지 않는 데이터는 제외된다

 

(3) GROUP BY 실행

- GROUP BY 이하에 나열된 컬럼에 의해 그룹화

- 반드시 SELECT 절에 존재하는 컬럼이어야함

- SUM, COUNT 같은 그룹함수와 함께 사용

 

(4) HAVING 실행

- GROUP BY 절까지 대상이 되는 데이터 중 다시 조건을 부여하여 조건에 해당하는 데이터만 남김

 

(5) ORDER BY 실행

- 가져온 데이터를 주어진 컬럼 조건에 맞게 정렬하며 기본값은 순방향 정렬(ASC)

 

(6) (5)까지 실행한 결과값을 생성

 

* SELECT문장이 실행되면 DBMS는 2차원 테이블 형태로 출력하는데 DBMS마다 알고리즘의 차이가 있음 (오라클 : parse ➛ execute ➛ fetch)

 

- SELECT 문장의 종류

* 단순 SELECT 문

➀ 테이블 전체 내용 조회

➁ 컬럼 이름 변경

➂ 컬럼에 문자열 삽입

➃ NULL 값 판단

➄ DISTINCT 사용

➅ LIKE/SUBSTR 사용

➆ BETWEEN 사용

➇ IN/OR 사용

 

* 조인 SELECT 문

➀ 카르테시안 조인 : 조인된 테이블 사이 조건이 걸리지 않을 경우에 발생하며 테이블간 모든 경우의 수에 대해 로우가 생성

➁ 내부 조인 : 테이블간 서로 연결되는 컬럼이 존재하여 이것에 의해 연결된 로우의 데이터가 출력

➂ 외부 조인 : 항상 양쪽에 데이터가 연결되는 데이터값이 존재해야함. (오라클에서는 ‘+’ 기호로 외부 조인을 사용)

 

* 그룹 SELECT 문

- 조회하고자 하는 컬럼값의 합계나 평균을 구하는 등의 일정 그룹에 의해 가공하여 출력함

➀ COUNT : 전체 로우 개수

➁ SUM : 총 합계

➂ AVG : 평균

➃ MIN : 최소값

➄ MAX : 최대값

 

* 다중 SELECT 문

- 네스티드 서브쿼리 : WHERE, HAVING 절에서 사용. 메인쿼리 절을 수행하기 위해 또 다른 SELECT문장이 필요한 경우

➀ 비교연산자(=,<>,<,>,>=,<=)를 이용한 단일 값 비교

➁ IN을 이용한 다중값 비교

➂ EXISTS/NOT EXISTS : 존재여부 확인

- 인라인뷰 : FROM절에서 사용되며 SQL문장이 실행중에 뷰를 만드므로 일반적으로 뷰와 성격이 비슷함

➀ 개발 중 뷰가 필요한 판단이 들어 모든 경우에 뷰를 생성할 경우 관리해야할 양이 많아질 때

➁ SQL 문장을 나누기는 트랜잭션 관리나 성능상 곤란한 경우에 인라인 뷰를 사용

 

* 집합 연산 SELECT 문

➀ UNION : 두 개의 테이블에서 조회하여 중복을 제외한 모든 로우 출력

➁ UNION ALL : 두 개의 테이블에서 조회하여 중복을 포함한 모든 로우 출력

➂ INTERSECT : 두 개의 테이블에 모두 존재하는 데이터에 대해 한 로우만 출력

➃ MINUS : 첫 번째 테이블에만 존재하는 데이터에 대해 한 로우만 출력

 

- 데이터를 입력하는 INSERT 문

* INSERT 문장의 구성

➀ INSERT INTO 절 : 데이터가 입력되어야 할 테이블이나 뷰를 지정

➁ VALUES 절 : INSERT INTO 절에 정의한 테이블이나 뷰에 입력될 데이터를 기술

 

* INSERT 문장 유형에 따른 예제

➀ 단건 INSERT : 한 개의 로우만 입력

- 전체 컬럼에 대해 입력할 때 테이블명 뒤 컬럼을 생각해도 무방하지만 VALUES에서 컬럼의 순서와 데이터타입을 일치시켜야함

- 문자 형식은 작은 따음표( ‘ ’ )를 사용한다. 생략해도 입력되지만 내부적으로 문자열 인식에 추가 작업이 발생하므로 사용하는 편이 좋음

- 숫자는 작은 따옴표를 사용하지 않는다. 사용하더라도 인식은 하나 내부적으로 숫자형으로 변환하는 작업이 발생함

- 날짜 형식은 작은 따옴표를 사용한다

➁ NULL INSERT : 테이블 특정 컬럼에 NULL을 입력

- 테이블 구조가 NOT NULL 제약이 걸려있는 경우에는 NULL을 입력할 수 없다

- NULL 대상 컬럼의 이름에서 제외하고 VAULES에서 컬럼의 순서와 타입을 일치시켜도 된다.

- 숫자 형식도 컬럼을 지정할 때는 반드시 작은 따옴표를 사용해야 한다.

➂ 다건 INSERT : 테이블에 여러개의 로우를 입력

- 읽어오는 테이블에서 ORDER BY절을 사용할 수 없다

- 입력 테이블과 읽는 테이블의 컬럼 타입이 동일해야 하며, 컬럼 생략을 위해서는 두 테이블이 동일한 스키마 구조를 가져야함

- 두 테이블이 다르고 테이블의 일부 컬럼이 동일하다면 반드시 컬럼명을 명시해서 문장을 작성

- 보통 데이터를 임시로 백업받은 이후에 테이블에 배치 작업이나 스키마 변경 작업을 수행할 때 많이 이용

- 분산 환경에서 데이터베이스 링크가 설정되어 있는 경우 원격지에 있는 동일한 구조의 테이블에 데이터를 입력하기 위해 사용

 

- 데이터를 수정하는 UPDATE문

➀ UPDATE 절 : 데이터 수정이 필요한 테이블이나 뷰를 지정

➁ SET 절 : 수정해야 할 컬럼을 기술하고 수정해야 할 값 지정

➂ WHERE 절 : 대상이 되는 테이블에서 가져올 데이터에 대해 조건을 부여하여 데이터를 조회하는 경우

 

* UPDATE 문장 유형에 따른 예제

➀ 조건에 따른 UPDATE : 일정한 조건에 따라 데이터 수정

* WHERE 조건절에 PK가 조건으로 들어오거나 조건이 걸린 값 중에 유일한 값일 경우는 한 개의 로우만 데이터를 수정

* WHERE 조건문을 생략하여 테이블의 모든 데이터를 한꺼번에 수정하지 않도록 주의

➁ 테이블의 전체 UPDATE

* WHERE 조건절을 생략함으로써 모든 테이블의 데이터를 수정

 

- 데이터를 삭제하는 DELETE 문

* DELETE 문장의 구성

➀ DELETE 절 : 삭제할 테이블이나 뷰를 지정

➁ WHERE 절 : 대상 테이블에 조건을 부여

 

* DELETE 문장 유형에 따른 예제

➀ 조건에 따른 DELETE

➁ 테이블 전체 DELETE

 

3. SQL 구분별 상세 설명 – DDL

- 테이블스페이스 관리

* 테이블 스페이스 특징

➀ 공간 관리, 데이터의 사용 가능 관리, I/O 성능 개선, 경합 감소, 부분 백업 등에 사용

➁ 하나 이상의 파일로 구성되어 db 실행 동안 온라인될 수 있다

➂ 시스템 테이블 스페이스나 활동 롤백 세그먼트를 갖는 테이블 스페이스를 제외하고 오프라인 될 수 있다.

➃ 읽기-쓰기 또는 읽기-전용으로 변경될 수 있다.

➄ 데이터베이스는 최소 하나의 테이블 스페이스로 구성(SYSTEM)

 

* 테이블 스페이스의 변경

ALTER TABLESPACE tablespace

DATAFILE filespec

 

* 테이블 스페이스의 삭제

DROP TABLESPACE tablespace [INCLUDING CONTENTS [CASCADE CONSTRAINTS]]

 

- 테이블 관리

* 테이블 생성

CREATE TABLE [schema.]table ( ...... )

(1) 테이블생성

(2) FOREIGN KEY 지정

 

* 테이블 변경

➀ 컬럼의 데이터타입, 기본값, NULL 허용 등을 바꿀 때 많이 이용

➁ 신큐 컬럼을 추가할 수 있으나 컬럼명을 바꿀 수는 없다.

➂ 테이블 컬럼의 순서는 바꿀 수 없다. 순서를 바꾸기 위해서는 테이블을 DROP했다가 재생성 해야한다.

➃ 데이터가 있다면 임시 테이블이나 컨트롤 파일에 백업하였다가 다시 입력. 이때 컬럼이 재생성 되므로 해당 컬럼은 최하단에 나타난다.

(1) 테이블 변경

(2) 테이블 컬럼 변경

 

* 테이블 삭제

➀ DROP 은 DELETE와 다르게 롤백되지 않는다.

➁ 테이블만 삭제할 수 있고, 참조되는 참조 무결성 제약을 삭제할 수도 있다.

➂ CASCADE CONSTARINTS는 FK로 연결되어 있는 자식 테이블에 데이터가 있음에도 자신의 테이블을 삭제할 수 있다.

(1) 테이블 삭제

 

* 테이블 이름 변경

RANAME old TO new

 

- 뷰 관리

* 뷰 생성(CREATE) : 뷰는 스스로 존재하지 않고 반드시 대상이 되는 테이블이나 뷰가 필요

(1) 뷰 생성

CREATE VIEW view_table_name AS

SELECT ....

FROM ....

WHERE ....

(2) 조인 뷰 생성

(3) 그룹 뷰 생성

 

* 뷰 삭제

DROP VIEW [schema.]view

 

4. SQL 구문별 상세 설명 – 트랜잭션 컨트롤

TIP) 오라클에서 트랜잭션을 종료시키는 경우

➀ 커밋이나 롤백이 실행될 때

➁ DML 문장이 실행되었고 트랜잭션이 종료되지 않았는데, CREATE, DROP, RENAME, ALTER와 같은 DDL문장이 실행되는 경우 자동 커밋

➂ 트랜잭션이 종료되지 않았는데 사용자가 오라클 접속에서 빠져나갈 때 트랜잭션은 자동으로 커밋

➃비정상적으로 시스템이 종료되는 경우 현 트랜잭션은 롤백

 

- 데이터베이스에 작업 내용을 반영하는 COMMIT

- 데이터베이스에 작업 내용을 취소하는 ROLLBACK

- 긴 트랜잭션을 나누는 SAVEPOINT

- 트랜잭션을 읽기 전용으로 할것인지 읽기/쓰기 전용으로 할 것인지 지정하는 SET TRANSACTION

 

5. SQL 구문별 상세 설명 – 세션 컨트롤

- 현재 세션을 변경하는 ALTER SESSION : 현재 수행되고 있는 세션을 변경한다.

test)

(1) 언어를 영어로 지정

(2) 존재하지 않는 테이블을 SELECT하면 영어로 에러 메시지가 출력됨

(3) 언어를 한글로 지정

(4) 존재하지 않는 테이블을 SELECT하면 한글로 에러 메시지가 출력됨

 

- 현재 세션을 활성/비활성화시키는 SET ROLE

(1) 현재 자신이 소유한 모든 역할 활성화 : SET ROLE ALL;

(2) 현재 자신이 소유한 모든 역할 비활성화 : SET ROLE NONE;

 

5. SQL 구문별 상세 설명 – 시스템 컨트롤

- 데이터베이스 가동 중 상태에서 다양하게 인스턴스 성격을 바꾸는 명령어

* ALTER SYSTEM TEST