💡해당 게시글은 방송통신대학교 정재화 교수님의 '데이터베이스 시스템' 강의를 개인 공부 목적으로 메모하였습니다.

학습 개요

DBMS의 데이터 관리 기능이 우수하다 하더라도 DBMS에 데이터 요청을 명확한 전달하고 결과를 받을 수 있는 방법이 없다면 DBMS의 높은 활용도를 기대할 수 없음
- 사용자와 DBMS 간 의사 소통할 수 있는 효율적인 명령 체계가 무엇보다도 중요함
데이터베이스 언어의 정의와 역할에 대하여 살펴보고 문법 체계가 자연어와 유사하여 관계형 DBMS에 국제 표준으로 사용되고 있는 SQL과 SQL의 데이터 정의 기능에 대하여 학습함

주요 용어

SQL
- 관계 대수에 기초하여 RDBMS의 데이터 관리를 위해 설계된 언어
DDL
- 데이터베이스 객체를 생성, 삭제 또는 구조를 수정하는 명령어의 집합
스키마
- 한 조직의 데이터베이스 시스템의 운영에 필요한 테이블, 인덱스, 뷰 등의 데이터베이스 객체의 집합
테이블
- 데이터를 저장하기 위한 2차원 표 형태의 구조

강의록

데이터베이스 언어의 이해

데이터베이스 사용의 의미

데이터 사용 계층
- 학적, 성적, 수강 애플리케이션을 통해 각 업무(학적, 성적, 수강)를 처리 함
데이터 관리 계층
- 이러한 애플리케이션들은 DBMS(데이터베이스 관리 시스템)를 통해 상호 작용 함
데이터 저장 계층
- DBMS는 디스크에 저장된 학적 파일, 성적 파일, 수강 파일 등의 데이터를 관리함

SQL의 개념

SQL (Structured Query Language)
- 관계 대수(Relational Algebra)에 기초하여 RDBMS의 데이터 관리를 위해 1970년대 초 IBM에서 설계
1986년 ANSI, 1987년 ISO에서 표준으로 제정
- SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2008
- 상용 DBMS의 특성에 맞게 국제 표준을 확장한 독자적 SQL 버전 존재
SQL의 특징
- 비절차적(선언형) 언어, 필요한 데이터만 기술
- 인간의 언어와 매우 유사하고 간단, 명료

SQL의 구성

데이터 정의 언어 (DDL: Data Definition Language)
- 데이터베이스 내의 객체(테이블, 인덱스, 뷰 등)를 생성(CREATE) 및 삭제(DROP)하고 그 구조를 조작(ALTER)하는 명령어 집합
- 데이터가 준수해야 하는 제약 조건을 기술
데이터 조작 언어 (DML: Data Manipulation Language)
- DDL에 의해 정의된 테이블에 데이터를 조작(삽입, 검색, 수정, 삭제)하는 명령어의 집합
- 데이터에 대한 CRUD (Create, Read, Update, Delete) 명령을 포함
데이터 제어 언어 (DCL: Data Control Language)
- DBMS의 동작, 접근 권한 등을 관리하는 SQL 명령어의 집합
  - 데이터베이스 접근 권한 부여/회수, 트랜잭션 관리 등
  - ex) GRANT, REVOKE, COMMIT, ROLLBACK

데이터 정의 언어 (DDL)

데이터 정의 언어의 개념

데이터베이스 객체(Object)를 생성, 삭제 또는 구조를 수정하는 명령어의 집합
데이터베이스 객체의 종류
- 데이터 저장 관련: 스키마(데이터베이스), 테이블, 인덱스, 뷰
- 데이터 조작 관련: 트리거, 프로시저, 함수 등
데이터 정의 명령어의 종류
명령어 설명
CREATE 객체 생성
ALTER 객체 수정
DROP 객체 삭제

명령어	설명
CREATE	객체 생성
ALTER	객체 수정
DROP	객체 삭제

데이터 정의 언어의 구문 형식

명령어	객체 종류	설명
CREATE	TABLE	테이블 생성
CREATE	INDEX	인덱스 생성
CREATE	VIEW	뷰 생성
CREATE	SCHEMA	스키마 생성
ALTER	TABLE	테이블 변경
DROP	TABLE	테이블 삭제
DROP	INDEX	인덱스 삭제
DROP	VIEW	뷰 삭제
DROP	SCHEMA	스키마 삭제

스키마 정의

스키마(Schema) = 데이터베이스(Database)
- 개념적으로 동일하게 사용될 수 있음
한 조직의 데이터베이스 시스템 운영에 필요한 테이블, 인덱스, 뷰 등 데이터베이스 객체들의 집합

스키마 관리 구문 형식

스키마 생성

  
  CREATE SCHEMA 스키마_이름;
  -- 또는
  CREATE DATABASE 스키마_이름;

스키마 삭제

  
  DROP SCHEMA 스키마_이름;
  -- 또는
  DROP DATABASE 스키마_이름;

릴레이션과 테이블

RDBMS의 기본적 데이터 저장 구조
2차원 형태의 저장 구조인 점에서 유사
릴레이션
- 데이터를 구성하는 추상적인 개념
- 집합론에 기반한 여러 속성을 갖는 레코드의 집합
- 레코드의 순서가 없으며 중복된 레코드가 존재하지 않음
테이블
- 물리적으로 데이터를 저장하는 구체적인 개념
- 레코드의 순서가 존재 가능
- 키 제약에 따라 중복된 레코드가 존재 가능

테이블 정의 (CREATE TABLE)

새로운 2차원 형태의 테이블을 생성

테이블 생성 기본 구문

  
  CREATE TABLE 테이블_이름 (
      속성명1 데이터_타입 [속성_레벨_제약조건],
      속성명2 데이터_타입 [속성_레벨_제약조건],
      ...
      [테이블_레벨_제약조건1],
      [테이블_레벨_제약조건2],
      ...
  );

테이블 생성의 예

교수번호	교수이름	직위	소속학과	연봉
186432-760829	최우성	조교수	생활과학과	52000000
189414-790829	한용운	조교수	법학과	45000000
191924-730620	이동휘	부교수	행정학과	51000000
194634-810228	김규식	정교수	컴퓨터과학과	70000000
194834-760517	정재화	부교수	컴퓨터과학과	53000000

  
CREATE TABLE 교수 (
    교수번호 CHAR(13),
    교수이름 CHAR(30),
    직위 CHAR(10),
    소속학과 CHAR(50),
    연봉 INT
);

데이터 타입의 개념

컬럼이 가질 수 있는 값의 범위, 즉 도메인을 결정
- 테이블의 각 속성(열)에 저장될 데이터의 종류를 지정
프로그래밍 언어에서의 변수를 생성 시 사용하는 데이터 타입의 사용 목적과 방법이 매우 유사
기본 데이터 타입
- 문자
  - CHAR(n)
  - VARCHAR(n)
  - TEXT
- 숫자
  - INT
  - FLOAT
  - DOUBLE
  - DECIMAL(m, n)
- 날짜/시간
  - DATE
  - TIME
  - DATETIME
  - TIMESTAMP

정수 데이터 타입

TINYINT
- 1바이트 정수(-128 ~ 127)
- ex) 나이, 학년 등의 크기가 작은 정수
SMALLINT
- 2바이트 정수(-32768 ~ 32767)
- ex) 물품 번호, 인원 등 중간 크기의 정수
INT
- 4바이트 정수(약 -20억 ~ 20억)
- ex) 물품의 금액, 전화 번호 등의 일반 크기의 정수
BIGINT
- 8바이트 정수(-약 9000경 ~ 9000경)
- ex) 계좌의 잔고, 천문학적인 크기의 정수

실수형 데이터 타입

부동 소수형
- FLOAT
  - 4바이트 크기 부동 소수
- FLOAT(P)
  - 소수점 이하 P개 자리의 부동 소수
- DOUBLE
  - 8바이트 크기 부동 소수
고정 소수형
- DECIMAL(M, N)
  - 전체 M자리, 소수점 이하 N자리의 소수를 저장
  - ex) DECIMAL(5, 2)는 -999.99 ~ 999.99
- NUMERIC
  - DECIMAL과 유사

날짜 및 시간 데이터 타입

날짜 데이터 타입
- DATE
  - YYYY-MM-DD 형식의 시간
- YEAR
  - YYYY 형식의 연도
시간 데이터 타입
- TIME
  - HH:MIl:SS 형식의 시간
날짜 및 시간 데이터 타입
- DATETIME
  - YYYY-MM-DD HH:MIl:SS 형식의 날짜 및 시간
- TIMESTAMP
  - DATETIME 과 유사
  - 유닉스 시간 기반 1970년 ~ 2038년 표현 가능
  - DBMS 서버의 시간대에 따라 시간 변경

문자 데이터 타입

CHAR(N)
- 최대 길이가 N인 고정 길이 문자열
VARCHAR(N)
- 최대 길이가 N인 가변 길이 문자열
ex) DATABASE 문자열 저장 시
TEXT
- 길이가 최대 2 ~ 4GB인 가변 길이 문자열
CLOB(Character Large OBject)
- 수백 MB ~ 수 GB의 데이터 저장을 위한 타입
- 레코드 단위가 아닌 별도의 저장 공간을 부여하는 외부 저장 방식
ENUM
- 유한 개의 문자열 집합 중 하나의 값을 선택
- 효율적인 저장 및 처리를 위해 내부적으로 숫자로 저장
  - 성별 : ENUM('남', '여')
  - 혈액형 : ENUM('A', 'B', 'O', 'AB)

CHAR와 VARCHAR

테이블 정의 예제

학생(Student) 테이블 생성:

  
  CREATE TABLE 학생 (
      학번 CHAR(8) NOT NULL PRIMARY KEY, -- 기본키, NULL 불가
      이름 VARCHAR(20) NOT NULL, -- NULL 불가
      학년 INT,
      학과 VARCHAR(30),
      주소 VARCHAR(100) DEFAULT '미기입', -- 기본값 설정
      생년월일 DATE
  );

과목(Course) 테이블 생성:

  
  CREATE TABLE 과목 (
      과목번호 CHAR(4) NOT NULL,
      과목명 VARCHAR(50) NOT NULL,
      학점 INT CHECK (학점 >= 1 AND 학점 <= 4), -- CHECK 제약조건
      담당교수 VARCHAR(20),
      PRIMARY KEY (과목번호) -- 테이블 레벨 기본키 지정
  );

수강(Enrollment) 테이블 생성:

  
  CREATE TABLE 수강 (
      학번 CHAR(8) NOT NULL,
      과목번호 CHAR(4) NOT NULL,
      수강년도 INT NOT NULL,
      학기 INT NOT NULL CHECK (학기 IN (1, 2)), -- CHECK 제약조건 (1 또는 2만 가능)
      성적 CHAR(2),
      PRIMARY KEY (학번, 과목번호, 수강년도, 학기), -- 복합 기본키
      FOREIGN KEY (학번) REFERENCES 학생(학번), -- 외래키 (학생 테이블 참조)
      FOREIGN KEY (과목번호) REFERENCES 과목(과목번호) -- 외래키 (과목 테이블 참조)
  );

제약 조건

테이블에 존재하는 데이터를 무결하고 세밀하게 관리하기 위한 목적으로 사용
- 데이터베이스에 저장되는 데이터의 무결성(Integrity)을 보장하기 위한 규칙
- 잘못된 데이터가 입력되거나 수정되는 것을 방지
DBMS는 테이블 조작 시 테이블에 정의 된 제약 조건을 만족 시키는지 지속적으로 검사
- 레코드의 입력, 수정, 삭제 요청 시 정의 된 제약 조건의 준수 여부를 검사한 후 실행
- 데이터의 일관성과 무결성 훼손을 방지
DBMS는 적용하려는 제약의 유형에 따라 다양한 제약 조건을 지원

제약 조건의 종류

PRIMARY KEY
- 기본키 지정
  - 테이블에서 각 튜플(행)을 고유하게 식별하는 속성(들)
- UNIQUE와 NOT NULL 특성
  - 자동으로 NOT NULL과 UNIQUE 속성을 가짐
FOREIGN KEY
- 외래키 지정
- 참조 컬럼 정의
- 한 테이블의 속성(들)이 다른 테이블의 기본 키를 참조하도록 하여 테이블 간의 관계를 정의하고, 참조 무결성을 보장
NOT NULL
- NULL이 될 수 없는 컬럼에 지정
UNIQUE
- 동일한 컬럼 값을 가질 수 없음을 지정
- 해당 속성(들)의 값이 테이블 내에서 고유해야 함을 보장
AUTO_INCREMENT
- 레코드가 추가될 때 자동적으로 속성 값이 1부터 1씩 증가되어 입력
CHECK
- 컬럼 값이 특정 조건 준수 여부 지정
- ex) 성별은 ‘남’ 또는 ‘여’만 가능, 학점은 1에서 4 사이

제약 조건의 사용

교수번호	교수이름	직위	소속학과	연봉
186432-760829	최우성	조교수	생활과학과	52000000
189414-790829	한용운	조교수	법학과	45000000
191924-730620	이동휘	부교수	행정학과	51000000
194634-810228	김규식	정교수	컴퓨터학과	70000000
194834-760517	정재화	부교수	컴퓨터학과	53000000

  
CREATE TABLE 교수 (
    교수번호 CHAR(13) NOT NULL,
    교수이름 CHAR(30) NOT NULL,
    직위 ENUM('조교수', '부교수', '정교수'),
    소속학과 CHAR(50) NOT NULL,
    연봉 INT NOT NULL,
    PRIMARY KEY(교수번호),
    FOREIGN KEY (소속학과) REFERENCES 학과(학과이름)
);

제약 조건의 응용

  
CREATE TABLE 개인구매회원 (
    회원번호 INT AUTO_INCREMENT,
    이름 CHAR(10) NOT NULL,
    ID CHAR(20) UNIQUE,
    비밀번호 VARCHAR(30) NOT NULL,
    이메일 VARCHAR(100) NOT NULL,
    통신사 CHAR(10) NOT NULL CHECK (통신사 IN ('SKT', 'KT', 'LGT')),
    휴대폰번호 CHAR(11) NOT NULL,
    SMS수신 CHAR(1) DEFAULT 'N',
    이메일수신 CHAR(1) DEFAULT 'N',
    PRIMARY KEY(회원번호)
);

제약 조건 정의 방법

속성 레벨(Column Level) 제약 조건

속성을 정의할 때 함께 기술

형식

  속성명 데이터_타입 [CONSTRAINT 제약조건_이름] 제약조건_유형

ex)

  학번 CHAR(8) CONSTRAINT PK_학생 PRIMARY KEY

테이블 레벨(Table Level) 제약 조건

모든 속성 정의 후에 별도로 기술
주로 복합키(여러 속성으로 구성된 키)나 외래키, CHECK 제약조건 정의 시 사용

형식

  [CONSTRAINT 제약조건_이름] 제약조건_유형 (대상_속성_리스트)

ex)

  
  CONSTRAINT PK_수강 PRIMARY KEY (학번, 과목번호, 수강년도, 학기)
  CONSTRAINT FK_수강_학생 FOREIGN KEY (학번) REFERENCES 학생(학번)

테이블 수정 (ALTER TABLE)

  
ALTER TABLE 테이블이름
    [ADD COLUMN 컬럼 데이타타입 [제약조건]]
    [DROP COLUMN 컬럼이름]
    [CHANGE COLUMN 수정전컬럼 수정후컬럼]
    [MODIFY COLUMN 컬럼 데이타타입]

CREATE문에 의해 생성된 테이블에 컬럼을 추가, 수정(이름, 데이터 타입, 제약 조건) 또는 삭제
- 이미 생성된 테이블의 구조(속성, 제약조건 등)를 변경할 때 사용
컬럼 삭제 또는 컬럼의 데이터 타입 수정 시 데이터에 대한 소실이 발생하므로 많은 주의가 요구

새로운 칼럼 추가

  
  ALTER TABLE 테이블_이름 ADD COLUMN 칼럼명 데이터_타입 [제약조건];

ex) 학생 테이블에 ‘연락처’ 속성(VARCHAR(15)) 추가

  
  ALTER TABLE 학생 ADD COLUMN 연락처 VARCHAR(15);

기존 속성 삭제

  
  ALTER TABLE 테이블_이름 DROP COLUMN 칼럼명;

ex) 과목 테이블에서 ‘담당 교수’ 속성 삭제
1 ALTER TABLE 과목 DROP COLUMN 담당교수;

칼럼 정의 변경

  
  ALTER TABLE 테이블_이름 ALTER COLUMN 칼럼명 TYPE 새로운_데이터_타입;
  ALTER TABLE 테이블_이름 ALTER COLUMN 칼럼명 SET DEFAULT 새로운_기본값;
  ALTER TABLE 테이블_이름 ALTER COLUMN 칼럼명 DROP DEFAULT;
  ALTER TABLE 테이블_이름 ALTER COLUMN 칼럼명 SET NOT NULL;
  ALTER TABLE 테이블_이름 ALTER COLUMN 칼럼명 DROP NOT NULL;

제약 조건 추가

  
  ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약조건_이름 제약조건_유형 (대상_칼럼);

ex) 학생 테이블의 ‘학년’ 속성에 1~4 사이 값만 허용하는 CHECK 제약 조건 추가
1 ALTER TABLE 학생 ADD CONSTRAINT CK_학년 CHECK (학년 >= 1 AND 학년 <= 4);

제약 조건 삭제

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

테이블 수정 질의의 사용

교수 테이블에서 전공 칼럼을 추가하시오

  
  ALTER TABLE 교수
      ADD COLUMN 전공 VARCHAR(100);

교수번호	교수이름	직위	소속학과	연봉	전공
186432-760829	최우성	조교수	생활과학과	52000000	NULL
189414-790829	한용운	조교수	법학과	45000000	NULL
191924-730620	이동휘	부교수	행정학과	51000000	NULL
194634-810228	김규식	정교수	컴퓨터학과	70000000	NULL
194834-760517	정재화	부교수	컴퓨터학과	53000000	NULL

교수 테이블에서 ‘직위’ 컬럼을 삭제하시오

  
  ALTER TABLE 교수
      DROP COLUMN 직위;

교수번호	교수이름	소속학과	연봉
186432-760829	최우성	생활과학과	52000000
189414-790829	한용운	법학과	45000000
191924-730620	이동휘	행정학과	51000000
194634-810228	김규식	컴퓨터학과	70000000
194834-760517	정재화	컴퓨터학과	53000000

테이블 삭제 (DROP TABLE)

존재하는 테이블을 스키마에서 삭제
삭제 된 테이블에 저장 된 모든 데이터가 소실, 복구가 불가능한 연산이므로 각별한 주의가 요구
- 테이블의 구조 정의와 해당 테이블에 저장된 모든 데이터를 영구적으로 삭제
기본 구문
1 DROP TABLE 테이블_이름 [CASCADE | RESTRICT];
- CASCADE
  - 해당 테이블을 참조하는 다른 테이블의 외래 키 제약 조건 등 종속된 객체들을 함께 삭제
- RESTRICT
  - 해당 테이블을 참조하는 다른 객체가 있을 경우 삭제를 수행하지 않음
- ex) 교수 테이블을 삭제하시오
  1 DROP TABLE 교수

ex)

  
  -- 수강 테이블 삭제 (참조하는 다른 객체가 없다면 성공)
  DROP TABLE 수강;
    
  -- 학생 테이블 삭제 (학생 테이블을 참조하는 외래키가 있을 경우 CASCADE 옵션으로 함께 삭제 시도)
  DROP TABLE 학생 CASCADE;

연습 문제

DDL명령에서 릴레이션의 변경을 위한 명령은?
a. ALTER TABLE
- CRATE TABLE에 의해 생성된 테이블의 구조를 변경하기 위한 SQL문은 ALTER TABLE 테이블 이름으로 시작 됨
- 새로운 컬럼이나 제약조건 등을 추가/제거/삭제할 수 있음
다음 요청을 수행하기 위한 SQL문으로 올바른 것은?
1 학생 테이블에서 '외모' 컬럼을 삭제하시오
a. ALTER TABLE 학생 DROP 외모
- 컬럼을 추가하거나 제거하는 것은 삭제가 아닌 테이블의 구조를 변경하는 것으로 ALTER 문을 사용함
- ALTER TABLE 구문을 사용하고 변경 작업 중 컬럼 삭제를 의미하는 DROP 절을 덧붙여서 ALTER TABLE 학생 DROP 외모와 같이 완성함
다음 중 테이블 정의 시 ‘동일한 컬럼 값을 가질 수 없음을 지정’하는 제약 조건은? a. UNIQUE
- SQL은 데이터 일관성과 무결성을 위해 테이블 정의 시 데이터에 대한 조건을 제약 조건으로 기술할 수 있으며 이 중 UNIQUE는 서로 다른 레코드가 UNIQUE로 지정된 컬럼에 대해 동일한 값을 가질 수 없는 조건을 생성함

정리 하기

구조화된 질의 언어(structured query language)라는 의미의 SQL은 사람과 DBMS 간의 의사소통을 위한 대표적인 DBMS 언어임
- SQL에는 데이터 정의 언어와 데이터 조작 언어로 구성되며, 이 외에도 DBMS를 관리하기 위한 다양한 기능을 지원함
데이터 정의 언어(DDL)는 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 객체를 정의하는 데 이용됨
- CREATE, ALTER, DROP 명령어를 사용하여 데이터베이스 객체를 생성, 수정, 추가할 수 있음
스키마는 데이터베이스와 동일한 의미로 사용되며 한 조직의 데이터베이스 시스템의 운영에 필요한 테이블, 인덱스, 뷰 등의 데이터베이스 객체의 집합을 의미 함
CREATE TABLE 문을 사용하여 테이블의 컬럼 정의 시 컬럼이 가질 수 있는 값의 범위를 데이터 타입을 지정하며 문자, 숫자, 날짜에 대한 여러 데이터 타입을 제공 함
ALTER TABLE 문은 컬럼의 추가 및 삭제, 데이터 타입 변경 등 테이블 수정을 지시 함
- DROP TABLE 문은 테이블의 삭제를 지시 함
테이블 정의 시 데이터의 일관성과 무결성 유지를 위해 데이터가 가져야 하는 다양한 제약 조건을 기술할 수 있으며, 종류로는 PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, AUTO INCREMENT, DEFAULT 등이 있음

체크 포인트

SQL에서 스키마(schema), 도메인(domain), 테이블(table), 뷰(view), 인덱스(index)를 정의하거나 변경 또는 삭제할 때 사용하는 언어는?
a. DDL(Data Definition Language)
- 데이터베이스 객체 조작하기 위한 언어
아래의 DML, DCL, DDL이 잘못 짝 지어진 것은?
a. DDL : INSERT
- DDL : CREATE, ALTHER, DROP
- DML : UPDATE
- DCL : ROLLBACK
- DCL : GRANT

[데이터베이스 시스템] 4강 - SQL