1. 관계형 데이터베이스(Relation Database)
01. 관계형 데이터베이스
- 1970년대 E.F. Codd박사의 논문에서 처음 소개된 데이터베이스로 릴레이션(Relation)과 릴레이션의 조인 연산을 통해 합집합, 교집합, 차집합 등을 만들 수 있는 특징이 있다.
- 현재 기업에서 가장 많이 사용하는 데이터베이스로 Oracle, MS-SQL, MySQL, Sybase등의 다양한 RDBMS가 있다
관계형 데이터베이스 집합 연산과 관계 연산
- 집합 연산
| 집합 연산 | 설명 |
| 합집합(Union) | - 두 개의 릴레이션을 하나로 합하는 것 - 중복된 행(튜플)은 한 번만 조회 |
| 차집합(Difference) | - 본래 릴레이션에는 존재하지만 다른 릴레이션에는 존재하지 않는 튜플을 조회 |
| 교집합(Intersection) | - 두 개의 릴레이션 간에 공통된 튜플을 조회 |
| 곱집합(Cartesian product) | - 각 릴레이션에 존재하는 모든 데이터를 조합하여 연산 |
- 관계 연산
| 관계 연산 | 설명 |
| 선택 연산(Selection) | - 릴레이션에서 조건에 맞는 행(튜플)만을 조회 |
| 투영 연산(Projection) | - 릴레이션에서 조건에 맞는 속성만을 조회 |
| 결합 연산(Join) | - 여러 릴레이션의 공통된 속성을 사용해서 새로운 릴레이션을 만듦 |
| 나누기 연산(Division) | - 기준 릴레이션에서 나누는 릴레이션이 가지고 있는 속성과 동일한 값을 가지는 행(튜플)을 추출하고 나누는 릴레이션의 속성을 삭제한 후 중복된 행을 제거하는 연산 |
02. 테이블(Table)의 구조
- 관계형 데이터베이스는 릴레이션에 데이터를 저장하고 릴레이션을 이용해 집합 연산 및 관계 연산을 수행해 다양한 형태로 데이터를 조회할 수 있음
- 릴레이션은 최종적으로 테이블로 만들어짐
| 구조 | 설명 |
| 기본키(Primary key) | - 하나의 테이블에서 유일성(Unique), 최소성, Not null을 만족시키면서 해당 테이블의 대표성을 가지는 것 |
| 행(Row), 튜플(Tuple) | - 하나의 테이블에 저장되는 값 |
| 칼럼(Column), 속성(Attribute) | - 어떤 데이터를 저장하기 위한 필드(Field) |
| 외래키(Foreign key) | - 다른 테이블의 기본키를 참조(조인)하는 칼럼 - 관계 연산 중에서 결합 연산(Join)을 하기 위해 사용 |
2. SQL(Structured Query Language) 종류
01. SQL
- 관계형 데이터베이스에 대해서 데이터의 구조를 정의, 데이터 조작, 데이터 제어 등을 할 수 있는 절차형 + 비절차형 언어
- SQL은 ANSI/ISO 표준을 준수하기 때문에 데이터베이스 관리 시스템이 변경되어도 그대로 사용할 수 있음
02. SQL 종류
| 종류 | 설명 |
| DDL(Data Definition Language) | - 관계형 데이터베이스의 구조를 정의 - CREATE, ALTER, DROP, RENAME, TRUNCATE |
| DML(Data Manipulation Language) | - 테이블에서 데이터를 입력, 수정, 삭제, 조회 - INSERT, UPDATE, DELETE, SELECT |
| DCL(Data Control Language) | - 데이터베이스 사용자에게 권한을 부여하거나 회수 - GRANT, REVOKE |
| TCL(Transaction Control Language) | - 트랜잭션을 제어하는 명령어 - COMMIT, ROLLBACK, SAVEPOINT |
트랜잭션(Transaction)
- 데이터베이스의 작업을 처리하는 단위
- ACID특성이 있음
| 트랜잭션 특성 | 설명 |
| 원자성(Atomicity) | - 트랜잭션은 데이터베이스 연산의 전부가 실행되거나 전혀 실행되지 않아야 한다(All or Nothing) - 즉, 트랜잭션의 처리가 완전히 끝나지 않았을 경우는 실행되지 않은 상태와 같아야 한다 |
| 일관성(Consistency) | - 트랜잭션의 실행 결과로 데이터베이스의 상태가 모순되지 않아야 한다 - 트랜잭션 실행 후에도 일관성이 유지되어야 한다 |
| 고립성(Isolation) | - 트랜잭션 실행 중에 생성하는 연산의 중간 결과는 다른 트랜잭션이 접근할 수 없다 - 즉, 부분적인 실행 결과를 다른 트랜잭션이 볼 수 없다 |
| 영속성(Durability) | - 트랜잭션이 그 실행을 성공적으로 완료하면 그 결과는 영구적 보장이 되어야 한다 |
03. SQL문의 실행 순서
| SQL 실행 순서 | 설명 |
| 파싱(Parsing) | - SQL문의 문법을 확인하고 구문 분석한다 - 구문 분석한 SQL 문은 Library Cache에 저장한다 |
| 실행(Execution) | - 옵티마이저(Optimizer)가 수립한 실행 계획에 따라 SQL을 실행한다 |
| 인출(Fetch) | - 데이터를 읽어서 전송한다 |
3. DDL(Data Definition Language)
01. CREATE
데이터베이스 생성
오라클에서 CREATE DATABASE xxx;처럼 데이터베이스를 생성하는 명령어를 입력하면 다음과 같은 오류가 발생한다
MySQL에서는 CREATE DATABASE XXX character set utf8mb4 collate utf8mb4_general_ci; 이런 식의 명령어로 새로운 데이스베이스를 쉽게 생성할 수 있었던 것과는 달리 오라클에서는 하나의 데이터베이스를 소유하며, 기존 데이터베이스에 tablespace를 생성하고 유저에게 접근권한을 부여해 마치 동일 서버 내 다른 데이터베이스를 사용하는 방법을 취한다.
CREATE USER c##유저 이름
IDENTIFIED BY 유저 비밀번호
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP;
위 명령어를 이용해 유저를 생성하고 기본 테이블스페이스와 임시 테이블스페이스를 지정한다. 위 코드는 기존에 존재하는 users와 TEMP를 이용해 만들었지만 관리적인 측면이나 성능적인 측면에서 따로 할당해서 쓰는 것이 좋다고 한다.
또한 오라클12c부터 유저 이름 앞에 c##을 붙여야 생성이 가능하다. 그냥 작성하면 아래와 같은 오류를 볼 수 있다.
GRANT CONNECT, RESOURCE, DBA TO 유저 이름;
이제 유저에게 해당 테이블 스페이스에 접근할 수 있는 CONNECT 롤, 테이블, 뷰, 시퀀스 등의 데이터베이스 오브젝트를 생성할 수 있는 RESOURCE 롤, 데이터베이스 관리자 권한인 DBA 롤을 부여한다
이제 새 접속으로 해당 유저 이름과 비밀번호를 이용해 고유한 테이블 스페이스에 접속할 수 있다.
오라클에서 데이터베이스를 새로 생성하고자 한다면 Database Configuration Assistant(DBCA)와 같은 프로그램으로 추가해줄 수 있긴 하다. 하지만 일반적인 상황에서는 잘 쓰이지 않는다.
테이블 생성
CREATE TABLE EMP
(
empno number(10),
ename varchar2(20),
sal number(10, 2) default 0,
created_date date default sysdate,
constraint emppk primary key(empno)
);
CREATE TABLE 테이블명 ();를 이용해 테이블을 생성한다
constraint 명령어로 emppk라는 이름의 기본키를 설정해 줄 수 있고 기본키로 지정된 열의 이름이 empno다
만약 두 개의 컬럼을 기본키로 지정하고자 한다면 primary key(empno, ename) 로 지정하면 된다
default는 칼럼의 기본값을 지정한다
sysdate는 오라클에서 현재 날짜를 나타낸다
- DEFAULT
CREATE TABLE T_ORDER
(
C1 NUMBER(10),
C2 DATE,
C3 VARCHAR(10),
C4 NUMBER DEFAULT 1000
);
INSERT INTO T_ORDER VALUES(2, SYSDATE - 1, 'ABC', DEFAULT);
SELECT * FROM T_ORDER;
테이블을 생성할 때 디폴트 값을 설정해주었다면 값을 입력할 때 DEFAULT 키워드로 디폴트 값을 쓰겠다고 명시를 해주어야 한다.
명시하지 않는다면 다음과 같은 ORA-00947에러가 발생한다.
혹은 INSERT INTO T_ORDER(C1, C2, C3) VALUES(2, SYSDATE - 1), 'ABC'); 처럼 입력할 행을 명시해주면 남은 속성에는 디폴트값이 들어간다.
- 외래키(Foreign key) 관계
CREATE TABLE DEPT
(
deptno varchar2(4) primary key,
deptname varchar2(20)
);
INSERT INTO DEPT VALUES ('1000', '생산팀');
INSERT INTO DEPT VALUES ('1001', '총무팀');
INSERT INTO DEPT VALUES ('1002', '인사팀');
SELECT * FROM DEPT;
CREATE TABLE EMP
(
empno number(10),
ename varchar2(20),
sal number(10, 2) default 0,
deptno_id varchar2(4) not null,
created_date date default sysdate,
constraint emppk primary key(empno),
constraint deptfk foreign key(deptno_id)
references dept(deptno)
on delete cascade
);
INSERT INTO EMP VALUES (1, '김철수', 300, '1000', sysdate);
INSERT INTO EMP VALUES (2, '박기철', 400, '1001', sysdate);
INSERT INTO EMP VALUES (3, '오명덕', 500, '1002', sysdate);
INSERT INTO EMP VALUES (4, '최익덕', 600, '1002', sysdate);
SELECT * FROM EMP;
DELETE FROM DEPT WHERE DEPTNO = '1002';
SELECT * FROM EMP;
CREATE TABLE EMP
(
empno number(10),
ename varchar2(20),
sal number(10, 2) default 0,
deptno_id varchar2(4) not null,
created_date date default sysdate,
constraint emppk primary key(empno),
constraint deptfk foreign key(deptno_id)
references dept(deptno)
on delete cascade
);
다른 테이블의 기본키를 참조해 외래키로 사용하기 위해서는 다음과 같은 코드를 사용할 수 있다
deptfk를 외래키의 이름으로 지정하고 deptno_id가 해당 외래키의 칼럼이 된다.
references 명령어를 이용해 어느 테이블의 어떤 키를 가져올 것인지 선택하면 된다
마지막에 ON DELETE CASCADE 옵션을 사용하면 자신이 참조하고 있는 테이블(DEPT)의 데이터가 삭제되면 자동으로 자신(EMP)의 데이터도 함께 삭제시킬 수 있다. 이는 참조 무결성을 준수할 수 있도록 도움을 준다
02. ALTER
테이블명 변경
ALTER TABLE EMP
RENAME TO EMP_TEST;
SELECT * FROM EMP;
SELECT * FROM EMP_TEST;
테이블명 변경은 ALTER TABLE ~ RENAME TO ~구문으로 추가할 수 있다
칼럼 추가
ALTER TABLE EMP
ADD (age number(2) default 1);
SELECT * FROM EMP;
이미 존재하는 테이블에 칼럼을 추가하기 위해서는 ALTER TABLE ~ ADD (~) 구문을 사용할 수 있다.
칼럼 변경
ALTER TABLE EMP
MODIFY (ename varchar2(20) not null);
SELECT * FROM EMP;
칼럼의 데이터 타입 혹은 그 외 제약조건들을 수정하기 위해서는 ALTER TABLE ~ MODIFY (~) 구문을 사용할 수 있다.
칼럼 삭제
ALTER TABLE EMP
DROP COLUMN age;
SELECT * FROM EMP;
테이블의 칼럼을 삭제하기 위해서는 ALTER TABLE ~ DROP COLUMN ~ 구문을 사용할 수 있다.
칼럼 이름 변경
ALTER TABLE EMP
RENAME COLUMN sal TO sall;
SELECT * FROM EMP;
칼럼의 이름을 변경하기 위해서는 ALTER TABLE ~ RENAME COLUMN A TO B 구문을 사용할 수 있다.
03. 테이블 삭제
DROP TABLE DEPT;
테이블의 구조와 데이터를 모두 삭제하기 위해서는 DROP TABLE ~ 구문을 사용할 수 있다.
다만, 삭제하려는 테이블의 기본키가 다른 테이블의 외래키로 참조되고 있을 경우는 다음과 같은 오류가 발생한다.
이때는 다음과 같은 구문으로 해당 제약조건을 삭제하면서 해당 테이블도 삭제한다
DROP TABLE DEPT CASCADE CONSTRAINT;
이제 DEPT 테이블은 삭제되고 DEPT테이블을 참조하고 있던 EMP 테이블의 제약조건이 삭제되었으므로 EMP테이블은 그대로 남아있다.
04. 뷰(View) 생성과 삭제
- 뷰는 테이블로부터 유도된 가상의 테이블임
- 실제 데이터를 가지고 있는 것이 아니라 테이블을 참조해서 원하는 칼럼만을 조회할 수 있게 함
- 뷰는 데이터 딕셔너리(Data Dictionary)에 SQL문 형태로 저장하되 실행 시에 참조됨
🔍뷰의 특징
- 참조한 테이블이 변경되면 뷰도 함께 변경됨
- 뷰의 검색은 참조한 테이블과 동일하게 할 수 있지만, 뷰에 대한 입력, 수정, 삭제에는 제약이 있음
- 특정 칼럼만 조회시켜서 보안성을 향상시킴
- 한번 생성된 뷰는 변경할 수 없고 변경을 원하면 삭제 후 재생성해야 함
- 따라서 ALTER문을 이용해 뷰를 변경할 수 없음
뷰의 생성
SELECT * FROM EMP;
CREATE VIEW V_EMP AS
(SELECT EMPNO, ENAME FROM EMP);
SELECT * FROM V_EMP;
뷰는 다음과 같이 CREATE VIEW ~ AS (SELECT문) 구문으로 생성하는데, 이때 SELECT문으로 특정 칼럼들이나 기타 조건들을 지정해 줄 수 있다.
뷰의 삭제
DROP VIEW V_EMP;
DROP VIEW ~ 구문을 이용해 간단하게 삭제할 수 있다. 이때 뷰를 삭제했다고 해서 참조했던 테이블이 삭제되는 것은 아니다.
🔍뷰의 장점과 단점
| 장점 | 단점 |
| - 특정 칼럼만 조회할 수 있기 때문에 보안 기능이 있음 - 데이터 관리가 간단해짐 - SELECT문이 간단해짐 - 하나의 테이블에 여러 개의 뷰를 생성할 수 있음 |
- 뷰는 독자적인 인덱스를 만들 수 없음 - 삽입, 수정, 삭제 연산이 제약됨 - 데이터 구조를 변경할 수는 없음 |
'프로그래밍 > DataBase' 카테고리의 다른 글
| [SQLD] SQL 기본 및 활용 Section 01. SQL 기본 (3) 내장형 함수부터 끝까지 (0) | 2023.08.27 |
|---|---|
| [SQLD] SQL 기본 및 활용 Section 01. SQL 기본 (2) DML부터 형변환까지 (0) | 2023.08.27 |
| [SQLD] 데이터 모델링의 이해 Section 02. Data Model and Performance (0) | 2023.08.23 |
| [SQLD] 데이터 모델링의 이해 Section 01. Data Modeling (0) | 2023.08.23 |
| [MySQL] 03. 데이터 타입 - 숫자 타입 (0) | 2023.02.25 |