SQL 개발자 (SQLD) 자격증 따기 Part.2 섹션 0.

SQL 기본 및 활용 : SQL의 종류와 WHERE문 사용방법, GROUP 연산, 내장형 함수 등을 학습한다

 

관계형 데이터베이스

1) 관계형 데이터베이스의 등장

- 1970년대 첫 등장

- 릴레이션과 릴레이션의 조인 연산을 통해 합집합, 교집합, 차집합 등을 만들 수 있다

2) 데이터베이스와 데이터베이스 관리시스템의 차이점

- 데이터베이스는 데이터를 특정 형태의 자료구조로 사용할 지에 따라 구분할 수 있다. 계층형, 네트워크형, 관계형 등이 있고, 계층형은 트리, 네트워크형은 오너와 멤버 형태로 데이터를 저장한다(계층, 네트워크는 거의 사용하지 않는다 ~ 데이터 베이스의 구조파악에 시간이 오래걸리기 때문에)

- 데이터베이스 관리시스템은 각 데이터베이스 유형을 관리하기 위한 소프트웨어를 의미한다, DBMS

 

3) 관계형 데이터베이스의 집합 연산과 관계 연산

- 집합 연산

합집합 Union : 두 개의 릴레이션을 하나로 합치는 것이다. 중복 값은 하나만 표시(*Union All : 모두 표시)

차집합 Difference : 본래 릴레이션에는 존재하고 다른 릴레이션에는 존재하지 않는 것을 조회

교집합 Intersection : 두 개의 릴레이션 간 공통 값 조회

곱집합 Cartesian Product : 존재하는 모든 데이터를 조합하여 연산

- 관계 연산

선택 연산 Selection : 릴레이션에서 조건에 맞는 행만을 조회

투영 연산 Projection : 릴레이션에서 조건에 맞는 속성만 조회

결합 연산 Join : 여러 릴레이션의 공통된 속성을 사용해 새로운 릴레이션을 만들어낸다.

나누기 연산 Division : 기준 릴레이션에서 나누는 릴레이션이 가지고 있는 속성과 동일한 값을 가지는 행을 추출하고 나누는 릴레이션의 속성을 삭제한 후 중복되는 행을 제거

 

- 관계형 데이터베이스는 릴레이션에 데이터를 저장하고 릴레이션을 사용해 집합 연산 및 관계 연산을 지원해 다양한 형태로 데이터를 조회할 수 있다

- 릴레이션은 최종적으로 데이터베이스 관리 시스템에서 테이블로 만들어진다

기본 키는 하나의 테이블에서 유일성과 최소성, Not Null을 만족하면서 해당 테이블을 대표하는 것이다. 테이블은 행과 칼럼으로 구성된다. 칼럼은 어떤 데이터를 저장하기 위한 필드로 속성이라고도 한다. 외래 키는 다른 테이블의 기본 키를 참조(조인)하는 칼럼이다. 외래 키는 관계 연산 중 Join 하기위해 사용한다

 

+ sql developer 실습

 

SQL 종류

1) SQL : 관계형 데이터베이스에 대해 데이터의 구조를 정의, 데이터 조작, 데이터 제어 등을 할 수 있는 절차형 언어이다.

종류

- DDL : 관계형 데이터베이스의 구조 정의, CREATE, ALTER, DROP, RENAME, 데이터베이스 테이블을 생성하거나 변경, 삭제하는 것으로 데이터를 저장할 구조를 정의하는 언어이다.

- DML : 테이블에서 데이터를 입력, 수정, 삭제, 조회한다. INSERT, UPDATE, DELETE, SELECT, 데이터 구조가 DDL로 정의되면 해당 데이터 구조에 데이터를 입력, 수정, 삭제, 조회할 수 있는 언어

- DCL : 데이터베이스 사용자에게 권한을 부여하거나 회수, GRANT, REVOKE, TRUMCATE, DCL은 DDL로 정의된 구조에 어떤 사용자가 접근할 수 있는 지 권한을 부여하는 것.

- TCL : 트랜젝션 제어, COMMIT, ROLLBACK, SAVEPOINT

 

트랜젝션 : 데이터베이스의 작업을 처리하는 단위

- 특성(ACID)

1) 원자성

- 트랜젝션은 데이터베이스 연산의 전부가 실행되거나 전혀 실행되지 않아야 한다(ALL OR NOTTHING)

- 즉, 트랜젝션의 처리가 완전히 끝나지 않았을 경우는 실행되지 않은 상태와 같아야 한다

2) 일관성

- 트랜젝션 실행 결과로 데이터베이스의 상태가 모순되지 않아야 한다

- 트랜젝션 실행 후에도 일관성이 유지되어야 한다

3) 고립성

- 트랜젝션 실행 중에 생성하는 연산의 중간 결과는 다른 트랜젝션이 접근할 수 없다

- 즉, 부분적인 실행 결과를 다른 트랜젝션이 볼 수 없다

4) 영속성

- 트랜젝션이 그 실행을 성공적으로 완료하면 그 결과는 영구적 보장이 되어야 한다

 

SQL문의 실행 순서 : 3단계

1) Parsing : SQL문의 문법을 검사하고 구문분석을 한다

2) Execution : 구문분석 이후 SQL문을 실행한다

3) Fetch : SQL문이 실행되면 데이터를 추출한다

 

DDL

1) 테이블 생성

- 데이터베이스를 사용하기 위해 테이블을 생성한다. 테이블 생성은 Create Table문을 사용하고 테이블 변경은 Alter Table문을 사용한다. 마지막으로 생성된 테이블을 삭제하고 싶을 때는 Drop Table문을 사용하면 된다

cf) Varchar vs Char : Varchar는 가변형 ~ 데이터의 용량이 남게 되면 남는 용량은 메모리로 활용할 수 있다. Char는 고정형이므로 불가능하다

2) 기본적인 테이블 생성

- 테이블의 구조를 확인하고 싶다면, SQL 중에 DESC문이 있다. DESC문은 테이블의 구조를 확인할 때 사용된다. 즉, create table로 생성된 테이블의 구조를 보고 싶을 때 사용한다

3) 제약 조건 사용

- 기본 키, 외래 키, 기본 값, not null 등은 테이블을 생성할 때 지정할 수 있다

- 외래 키를 지정하려면, 먼저 마스터 테이블이 생성되어야 한다. 즉, 사원과 부서 테이블에서는 부서가 마스터 테이블이 된다. 사원 테이블이 부서 테이블의 depno를 참조해야 하는 것이다. 그리고 EMP 테이블을 생성할 때 constraint를 사용해 외래 키이름인 "deptfk"를 입력 후 외래 키를 생성한다

4) 테이블 생성 시 CASCADE 사용

- 테이블을 생성할 때 CASCADE 옵션을 사용할 수 있다. CASCADE 옵션은 참조관계가 있을 경우 참조되는 데이터를 자동으로 반영할 수 있는 것이다. 먼저 마스터 테이블을 생성한다. 즉, DEPT 테이블을 생성하고 데이터를 입력한다

ON DELETE CASCADE 옵션은 자신이 참조하고 있는 테이블의 데이터가 삭제되면 자동으로 자신도 삭제되는 옵션이다. 이 옵션을 사용하면 참조 무결성을 준수할 수 있다. 참조 무결성이란, 마스터 테이블에는 해당 부서번호가 없는데, 슬레이브(서브) 테이블에는 해당 부서번호가 있는 경우가 무결성이 위배된 경우이다

(1) 테이블명 변경은 ALTER TABLE ~ RENAME TO문을 사용한다

(2) 칼럼 추가 : ADD

(3) 칼럼 변경 : 칼럼의 변경은 ALTER TABLE ~ MODIFY문을 사용하면 된다. 이를 통해 데이터 타입을 변경하고 제약 조건을 설정할 수도 있다. 다만 데이터 타입을 변경할 때 칼럼에 기존 데이터가 있는 경우는 에러가 발생한다

(4) 칼럼 삭제 : ALTER TABLE ~ DROP COLUMN

(5) 칼럼명 변경 : ALTER TABLE RENAME COLUMN ~ TO

5) 테이블 삭제

DROP TABLE : 테이블의 구조와 데이터를 모두 삭제, "CASCADE CONSTRAINT" 옵션을 사용할 수 있다. 해당 테이블의 데이터를 외래키로 참조한 슬레이브 테이블과 관련 제약사항도 함께 삭제한다

6) VIEW 생성과 삭제

- 뷰란 테이블로부터 유도된 가상의 테이블이다. 실제 데이터를 가지고 있지 않고 테이블을 참조해서 원하는 칼럼만을 조회할 수 있게 한다.

특징

- 참조한 테이블이 변경되면 뷰도 변경된다

- 뷰의 검색은 참조한 테이블과 동일하게 할 수 있지만 뷰에 대한 입력, 수정, 삭제엔 제약이 있다. 특정 칼럼만 조회해 보안을 향상할 수 있다

- 한번 생성된 뷰는 변경할 수 없고 변경을 원하면 삭제 후 재생성해야 한다

- ALTER문을 사용해 뷰를 변경할 수 없다

- 뷰를 생성할 때 CREATE VIEW문을 사용하며 참조 테이블은 SELECT문으로 지정한다

~ 뷰는 SELECT문을 사용해 일반 테이블처럼 조회할 수 있고, 삭제는 DROP VIEW문을 사용하고, 삭제해도 실제 테이블이 삭제되지 않는다

7) 뷰의 장점과 단점

- 장점

특정 칼럼만 조회할 수 있기 때문에 보안 기능이 있다

데이터 관리가 간단하다

SELECT문이 간단해진다

하나의 테이블에 여러 개의 뷰를 생성할 수 있다

-단점

뷰는 독자적인 인덱스를 만들 수 없다

삽입, 수정, 삭제 연산이 제약된다 -> 삽입 X, 데이터 삭제 O (비권장)

데이터 구조를 변경할 수 없다

 

DML

1) INSERT문

- 데이터를 입력할 때 문자역을 입력하는 경우 작은 따옴표를 사용해야 한다. 특정 테이블에 모든 칼럼에 단일 데이터를 삽입하는 경우 컬럼명을 생략할 수 있다 ex) INSERT INTO EMP VALUES(1000, '임베스트'); ~ 데이터 타입은 일치해야함

~ 최종적으로 데이터를 저장하려면 TCL문인 Commit을 실행해야 한다

2) SELECT문

- SELECT문을 사용해 데이터를 조회해 해당 테이블에 바로 삽입할 수 있다. 단, 입력되는 테이블은 사전에 생성되어 있어야 한다

3) NOLOGGING문

- 데이터베이스에 데이터를 입력하면 Check Point 이벤트가 발생하고 로그 파일에 그 정보를 입력하는데, NOLOGGING 옵션은 로그 파일의 기록을 최소화해 입력 성능을 향상시키는 방법이다. ~ Buffer Cache라는 메모리 영역을 생략하고 기록한다

4) UPDATE문

- 입력된 데이터의 값을 수정하려면, UPDATE문을 사용한다

- UPDATE문을 사용해 원하는 조건으로 데이터를 검색해 해당 데이터를 수정할 수 있다. 만약, UPDATE문에 조건문을 입력하지 않으면 모든 데이터가 수정되므로 유의해야한다.

5) DELETE문

- DELETE문은 원하는 조건을 검색해 해당 행을 삭제하는 것이다. 조건문을 입력하지 않으면 모든 데이터가 삭제된다. DELETE문으로 데이터를 삭제한다고 해서 테이블의 용량이 초기화되지는 않는다.(롤백 가능) vs TRUNCATE

6) ORDER BY문

- SELECT문을 사용할 때 ORDER BY를 같이 사용할 수 있다. 단, ORDER BY문은 맨 마지막에 실행된다. 데이터를 오름차순 또는 내림차순으로 출력하며 모든 실행이 끝난 후 데이터 출력 직전에 정렬한다. 이 때문에 정렬로 인한 성능 저하를 유발한다. 특별한 지정이 없으면 오름차순으로 지정한다

7) DISTINCT와 ALIAS

- DISTINCT : DISTINCT는 칼럼 앞에 지정하며 중복된 데이터를 한 번만 조회하게 한다

- ALIAS : 별칭, 테이블명이나 칼럼명을 간략하게 할 때 사용한다

8) WHERE문 사용하는 연산자

WHERE문이 사용하는 연산자는 비교 연산자, 논리 연산자, SQL 연산자, 부정 SQL 연산자가 있다

- 비교 연산자 : =, <, <=, >, >=

- 논리 연산자 : AND, OR, NOT

- SQL 연산자 : LIKE '%비교 문자열%', BETWEEN A AND B, IN (list), IS NULL

- 부정 SQL 연산자 : NOT BETWEEN A AND B, NOT IN (list), IS NOT NULL

9) LIKE : 와일드 카드를 사용해 데이터를 조회할 수 있다

% : 어떤 문자를 포함한 모든 것을 조회한다. ex) '조%' : 조로 시작하는 모든 문자 조회

_(underscore) : 한 개인 단일 문자 ex) 'test_' : test로 시작하고 하나의 글자만 더 있는 것을 조회

10) BETWEEN문 : 지정된 범위에 있는 값을 조회

11) IN문 : OR의 의미를 가지고 있어 하나의 조건만 만족해도 조회가 된다.