[SQLD 자격증 대비반] 챕터 7. 관계형 데이터베이스와 DDL

관계형 데이터베이스(Relational Database)

• 데이터베이스를 체계적으로 관리하고 운영하는 소프트웨어
• 사용자가 정해진 방식으로 요청하고 행과 열의 2차원 관계로 정의된 데이터(테이블 형태)의 생성, 수정, 삭제, 조회 가능(=CRUD)
• 관계형 데이터베이스 관리 시스템(RDBMS)
  • RDBMS는 메타 데이터를 총관 관리할 수 있기 때문에 데이터의 성격, 속성 또는 표현 방법 등을 체계화할 수 있고, 데이터 표준화를 통해 데이터 품질 확보 가능
  • RDBMS는 인증된 사용자만이 참조할 수 있도록 보안 기능 제공, 사용자가 실수로 조건에 위배되는 행동을 할 경우 이를 방지하여 데이터 무결성 보장
  • RDBMS는 시스템의 갑작스러운 장애로부터 사용자가 입력, 수정, 삭제하던 데이터가 제대로 반영될 수 있도록 보장해 주는 기능과 시스템 다운, 재해 등의 상황에서도 데이터를 회복/복구할 수 있는 기능 제공

 

🌟 SQL 언어 분류

• 데이터 정의 언어(DDL : Data Definition Language)
  • 관계형 데이터데이스 구조를 정의하는데 사용
  • 데이터를 저장하기 위한 데이터베이스의 기본 구조를 잡는데 사용
  • 데이터를 저장하는 테이블을 만들거나 수정
  • 테이블 생성, 변경, 삭제 가능
• 데이터 조작 언어(DML : Data Manipulation Language)
  • 테이블에 저장된 데이터를 조작하는 데 사용
  • 데이터 정의 언어로 생성된 구조를 실제 데이터로 채우는데 사용
  • 데이터 삽입, 조회, 수정, 삭제 가능
• 데이터 제어 언어(DCL : Data Control Language)
  • 데이터베이스에 접근하여 이를 제어하기 위한 권한을 관리
  • 특정한 사용자의 데이터베이스 사용 권한을 부여 or 박탈
• 트랜잭션 제어어(TCL : Transaction Control Language)
  • 논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 트랜잭션별로 제어
  • 이때 COMMIT, ROLLBACK 등의 명령어 수행 가능
  • 트랜잭션 특징(ACID)
    • 원자성 : 하나의 논리적인 단위로 묶여있는 트랜잭션이 데이터베이스에 모두 적용되거나 아니거나 둘 중 하나의 선택지만 있어야 함
    • 일관성 : 트랜잭션의 작업 처리 결과는 항상 일관되어야 함
    • 독립성 : 둘 이상의 트랜잭션이 동시에 진행되고 있는 상황에서 하나의 트랜잭션 결과가 다른 트랜잭션 결과에 영향 X
    • 지속성 : 트랜잭션을 통해 특정한 작업이 마무리된 경우 결과는 데이터베이스에 영구적으로 반영

 

SQL Syntax

• 모든 SQL문(Statement)은 SELECT, INSERT, UPDATE 등과 같은 키워드로 시작
• 하나의 Statement는 세미콜론(;)으로 끝남
• 세미콜론(;)은 각각의 SQL문을 구분하는 표준 방법
• SQL 키워드는 대소문자를 구분하지 않지만 대문자로 작성하는 것을 권장

SELECT col_name FROM table_name;

• 문(Statement) : 독립적으로 실행할 수 있는 완전한 SQL 코드 조각, 문(Statement)은 절(Clause)로 구성
• 절(Clause) : 문(Statement)의 하위 단위

 

테이블 Table

• 관계형 데이터베이스에서 데이터를 저장하는 기본 단위
• 행과 열로 구성된 2차원의 데이터 저장 구조
• 세로 방향을 칼럼(Column), 가로 방향을 행(Row)라고 부르고 이들이 교차하는 하나의 공간을 필드(Field)라 부름
  • 열(column, 속성) : 테이블에 저장되는 데이터의 특징, 더 이상 나눌 수 없는 하나하나의 속성
  • 행(row, 레코드) : 테이블에 저장되는 실제 데이터(인스턴스)

 

ERD(Entity-Relationship Diagram)

• 테이블 간 상관 관계를 이미지로 도식화한 것
• Entity(개체), Relationship(관계), Attribute(속성)으로 구성
• 바커 표기법(Baker Notation)
  • Entity(개체) : 데이터베이스를 사용하는 주체가 지속적으로 보관하고 관리하는 대상
  • Attribute(속성) : 하나의 Entity에 종속되는 명사적 단어
    - * (Mandatory) : 어떤 속성에 특정한 값을 반드시 저장해야 하는 경우
    - o (Optional) : 값이 존재할 수도 아닐 수도 있는 경우
  • Relationship(관계) : 두 개의 Entity 간의 조건 관계를 표기한 후 해당하는 Entity의 가까운 위치에 관계 명칭 표기
• I/E 표기법(Information Engineering Notation)
  • Entity(개체) : 바커 표기법과 마찬가지로 사용자가 추적하고자 하는 어떤 대상 의미
  • Attribute(속성) : 또한 바커 표기법과 마찬가지로 Entity의 특징을 기술해 주는 여러 개의 속성을 가질 수 있음
  • Relationship(관계) : 까마귀 발 부호를 통해 나타낼 수 있다.

    

    Indentifying(식별관계)
    - 식별 관계의 경우 A 개체의 기본키가 B 개체의 외래키면서 동시에 기본키가 되는 관계
    - 비식별 관계의 경우 A 개체의 기본키가 B 개체의 비기본키 영역에서 외래키가 되는 관계

 

DDL

• 주로 데이터를 저장하는 데이터베이스의 골격인 테이블을 다루는 언어로 구성
• 테이블의 구성 요소 중 가장 중요한 것은 칼럼을 정의하는 일
• 데이터 유형
  • 데이터베이스에 데이터를 저장할 때 어떠한 데이터를 받을지 결정하는 기준
  • 숫자, 문자열, 참/거짓 등의 자료형이 있음
• 데이터 크기
  • 특정 칼럼을 설정할 때 데이터 종류를 설정하면서 크기 지정 가능
  • 사전에 지정한 데이터 크기를 넘어서는 자료가 입력될 경우 에러 발생 or 의도하지 않은 데이터 저장
• NUMERIC Type
  • 숫자형 타입은 DB를 제공하는 회사별로 다른 기준 제공
  • SQL Server의 경우 ISO/ANSI 기준에 맞춰 작은 정수, 정수, 큰 정수, 실수 등으로 제공
  • Oracle의 경우 숫자형 타입은 NUMBER 타입이 대표적
  • NUMERIC.DECIMAL : 고정 소수점 숫자를 나타내는 데 사용
  • SMALLINT : 2Byte로 표현되는 작은 범위 정수
  • BIGINT : 4Byte로 표현되는 큰 범위 정수
  • FLOAT : 부동 소수점 숫자를 나타내는 데 사용
  • REAL : 4Byte 부동 소수점 숫자를 나타내는 데 사용
  • 저장하고자 하는 숫자의 크기와 정밀도를 고려하여 선택

대표적인 데이터 유형

• CHARACTER(S)
  • 고정 길이의 문자열 정보이며 CHAR로 표현
  • 기본 길이 1 Byte로 설정
  • 최대 길이 Oracle 2,000 Bytes, SQL Server 8,000 Bytes
  • 고정된 길이를 갖기 때문에 입력되는 값의 길이가 해당 길이보다 작은 경우 남은 부분 차이만큼 공백으로 채워짐
• VARCHAR(S)
  • 가변 길이의 문자열 정보이며 VARCHAR로 표현, Oracle의 경우 VARCHAR2로 표현
  • 기본 길이 1 Byte로 설정
  • 최대 길이 Oracle 4,000 Bytes, SQL Server 8,000 Bytes
  • 최대 길이만큼의 크기를 가질 수 있지만 길이는 가변적으로 조정되기 때문에 입력되는 값의 Byte만큼만 적용
• NUMERIC
  • 정수, 실수 등의 숫자 정보
  • SQL Server의 경우 다양한 숫자 타입을 사용하지만, Oracle의 경우 NUMBER 숫자 타입이 대표적
• DATETIME
  • 날짜, 시각 등과 관련된 정보
  • SQL Server의 경우 DATETIME으로 표현하고, Oracle의 경우 DATE로 표현

문자열 비교

 

제약 조건

• 테이블에 부적절한 값이 들어오지 않도록 하는 규칙
• 마치 칼럼의 속성처럼 사용하지만 데이터베이스 입장에서는 각각의 개체이므로 고유의 이름(제약조건 명) 지정해주어야 함. 지정하지 않을 경우 DBMS가 자동으로 부여
• NOT NULL
  - 필수로 데이터가 입력되어야 하는 칼럼에 설정
• UNIQUE
  - 해당 컬럼에 들어가는 값이 테이블 전체에서 유일해야 함 ➡️ 중복값 허용 X
  - NOT NULL과 함께 사용 가능
• PRIMARY KEY
  - 테이블 기본키 설정
  - 기본적으로 NOT NULL + UNIQUE 제약조건을 함께 갖으며 테이블당 1개의 기본키만 설정 가능
  - 여러 칼럼을 묶어 하나의 기본키로 사용 가능

CONSTRAINT PK_example PRIMARY KEY(col1, col2, col3)

-- CONSTRAINT : 제약 조건

• FOREIGN KEY
  - 외래키 지정
  - 참조 관계를 이용해 테이블의 데이터 조작 가능
  - 참조하는 테이블이 먼저 생성되어 있어야 함
  - 외래키가 참조하는 칼럼은 참조하는 테이블의 기본키(PK)여야 함

CONSTRAINT FK_example FOREIGN KEY(col4) REFERENCES table_example(col1)

• DEFAULT
  - 해당 속성에 값을 입력하지 않을 경우 부여될 기본 값 지정

...
column2 VARCHAR2(50) DEFAULT 'Default Text',
...

• CHECK
  - 미리 지정한 조건에 맞는 데이터 입력해야 함. 주로 특정한 값, 범위 지정

-- 컬럼 정의에 함께 사용할 경우
...
age NUMBER(2) CHECK(age>14),
...

-- 제약 조건을 따로 명시할 경우
...
CONSTRAINT CK_age CHECK(age>14)
...

 

 

테이블 생성 CREATE TABEL

• 테이블 작성 전에 어떤 데이터를 저장할 것인지 분류
• 이후 어떤 제약조건이 필요한지 설정한 뒤 테이블 생성

-- Create a new table in the database

CREATE TABLE table_name (
column_1 data_type constraints,
column_2 data_type constraints,
column_3 data_type constraints
);

-- (예시) 연락처 테이블
CREATE TABLE contact (
name VARCHAR2(10) NOT NULL,
age NUMBER(2),
email VARCHAR2(40) NOT NULL UNIQUE
);

 

 

ALTER TABLE

• COLUMN 추가 : 새롭게 추가되는 칼럼은 테이블 마지막에 위치하게 되며 직접 지정 불가 ADD

ALTER TABLE 테이블명 ADD 칼럼명 데이터_유형;

-- (예시) player 테이블에 address 칼럼 추가하기
ALTER TABLE player ADD address VARCHAR2(80);

• CONSTRAINT(제약조건) 추가 : 칼럼 생성 후 미처 제약조건을 추가하지 못했을 경우 ADD CONSTRAINT

ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약_조건_이름 제약조건 (컬럼_이름);

-- (예시) player 테이블에 team 테이블의 team_id를 FK로 추가하기
ALTER TABLE player ADD CONSTRAINT player_fk FOREIGN KEY team_id
REFERENCES TEAM(team_id);

• COLUMN 삭제 : 생선한 칼럼이 필요하지 않을 경우 DROP COLUMN

ALTER TABLE 테이블_이름 DROP COLUMN 삭제_할_컬럼_이름;

• CONSTRAINT 삭제 : 특정 칼럼에 더는 제약조건이 필요하지 않은 경우 DROP CONSTRAINT

ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약_조건_이름;

• COLUMN 수정
  - 칼럼의 데이터 유형, DEFAULT 값, NOT NULL 제약조건에 대한 변경 가능
  - 크기 변경 시 크기를 늘릴 수는 있지만 데이터 훼손 우려로 인해 줄이지는 못함
  - Oracle : MODIFY
  - SQL Server : ALTER COLUMN

-- Oracle
ALTER TABLE 테이블_이름 MODIFY (
	컬럼_이름_1 데이터_유형 [DEFAULT 식] [NOT NULL], 
	컬럼_이름_2 데이터_유형 [DEFAULT 식] [NOT NULL] 
	...);

-- SQL Server 
ALTER TABLE 테이블_이름 ALTER COLUMN (
	컬럼_이름_1 데이터_유형 [DEFAULT 식] [NOT NULL], 
	컬럼_이름_2 데이터_유형 [DEFAULT 식] [NOT NULL] 
	...);

• COLUMN 이름 변경 : 칼럼 이름 수정 RENAME COLUMN

-- Oracle
ALTER TABLE 테이블_이름 RENAME COLUMN 변경_할_컬럼_이름 TO 새로운_컬럼_이름;

-- SQL Server
SP_RENAME 테이블_명.변경_할_컬럼_이름, 새로운_컬럼_이름, 'COLUMN';

• TABLE 이름 변경 : 테이블 이름을 수정할 필요가 있을 때 사용. ALTER TABLE 없이 이름 변경 가능 RENAME

-- Oracle
RENAME 기존_테이블_이름 TO 새로운_테이블_이름;

-- SQL Server
SP_RENAME 기존_테이블_이름, 새로운_테이블_이름;

 

DROP TABLE

• 테이블 전체를 삭제할 때 사용하며 저장된 데이터는 같이 삭제됨
• CASCADE CONSTRAINT
  • 해당 테이블을 참조하고 있는 제약조건에 대해 해당 테이블이 삭제될 때 같이 삭제할지 여부를 결정
  • SQL Server는 위 옵션이 존재하지 않기 때문에 참조 테이블 관계가 있을 시 제약 조건 혹은 참조 테이블을 먼저 삭제해야 테이블 삭제 가능
  • ON UPDATE CASCADE : 참조하고 있는 모든 레코드 함께 수정
  • ON DELETE CASCADE : 참조하고 있는 모든 레코드 함께 삭제
  • ON DELETE SET NULL : 참조하던 레코드가 사라지면 NULL 값으로 변경

DROP TABLE 테이블_이름 [CASCADE CONSTRAINT];

 

TRUNCATE TABLE

• 테이블은 남기고 데이터만 지우고 싶을 때 사용
• 테이블에 저장된 모든 데이터는 삭제되며 테이블 구조는 그대로 남음

TRUNCATE TABLE 테이블_이름;