방송대 데이터베이스시스템 - 4강. SQL (1)

---

1. 데이터베이스 언어

2. 데이터 정의 언어

---

1. 데이터베이스 언어

SQL

SQL(Structured Query Language, 구조화된 질의 언어

관계대수에 기초하여 RDBMS의 데이터 관리를 위해 설계된 언어

1986년 ANSI, 1987년 ISO에서 표준으로 제정

SQL 86, SQL 89, SQL 92, SQL:1999, SQL:2003, SQL:2008

상용 DBMS의 특성에 맞게 국제표준을 확장한 독자적 버전이 존재함.

 

특징

- 비절차적(선언형) 언어, 필요한 데이터만 기술 : What (O), How (X)

- 인간의 언어와 매우 유사하고 간단, 명료 → 관계형, 비관계형 DB에서도 표준

 

구성

데이터 정의 언어(DDL: Data Definition Language)

- 데이터베이스 내의 객체를 생성 및 삭제하고 그 구조를 조작하는 명령어의 집합

- 데이터가 준수해야 하는 제약조건을 기술

- CREATE, ALTER, DROP문 등

 

데이터 조작 언어(DML: Data Manipulation Language)

- DDL에 의해 정의된 테이블에 데이터를 조작하는 명령어의 집합

- 데이터에 대한 CRUD(생성, 검색, 삭제, 수정) 명령을 포함

- INSERT, UPDATE, DELETE, SELECT 문 등

 

데이터 제어 언어(DCL: Data Control Language)

- DBMS의 동작 설정 및 DBMS 접근에 대한 사용자의 권한을 관리하는 명령어의 집합

- 주로 데이터베이스 관리자(DBA)가 사용함.

- GRANT, REVOKE, BEGIN 등

---

2. 데이터 정의 언어

데이터 정의 언어(DDL)

데이터베이스 객체를 생성, 삭제 또는 구조를 수정하는 명령어의 집합

 

데이터베이스 객체의 종류

· 데이터 저장 - 테이블, 인덱스, 뷰

· 데이터 조작 - 트리거, 프로시저, 함수 등

 

데이터 정의 명렁어의 종류

· CREATE : 객체 생성

· ALTER : 객체 수정

· DROP : 객체 삭제

 

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

CREATE	SCHEMA 또는 DATABASE	스키마 (데이터베이스) 생성
	TABLE	테이블 생성
	INDEX	인덱스 생성
	VIEW	뷰 생성
ALTER	SCHEMA 또는 DATABASE	스키마 (데이터베이스) 수정
	TABLE	테이블 수정
DROP	SCHEMA 또는 DATABASE	스키마 (데이터베이스) 삭제
	TABLE	테이블 삭제
	INDEX	인덱스 삭제
	VIEW	뷰 삭제

---

스키마 

스키마 = 데이터베이스(데이터의 집합)

한 조직의 데이터베이스 시스템의 운영에 필요한 테이블, 인덱스, 뷰 등의 데이터베이스 객체의 집합

DDL문으로 생성 가능한 가장 상위 단계의 개체

 

스키마 관리 방법

· Forward Engineer : 자동으로 스키마 생성이 완료된 후 테이블까지 생성됨.

· SQL 에디터 : SQL 쿼리 패널에서 직접 스키마 생성문을 입력 (CREATE SCEHMA)

· 내비게이터 패널 : 생성된 스키마의 'Schema Inspector'을 통해 확인할 수 있음.

 

스키마 관리 구문 형식

스키마 생성	CREATE SCEHMA 스키마 이름
스키마 삭제	DROP SCEHMA 스키마 이름 (스키마 내부의 모든 데이터 삭제됨)

 

ALTER SCEHMA 가 없는 이유

- 일반적으로 데이터베이스관리시스템은 데이터베이스를 수정하도록 하는 기능은 제공하지 않음.

- 수정하고 싶으면 삭제 후 다시 생성함.

---

테이블 정의

새로운 2차원 형태의 테이블을 생성, 즉 릴레이션에 동일하게 상응하는 구조

테이블 생성 구문과 예시

---

데이터 타입의 개념

도메인(컬럼이 가질 수 있는 값의 범위)을 결정

프로그래밍 언어에서의 변수를 생성하는 데이터 타입의 사용목적과 방법이 매우 유사

 

기본 데이터 타입

종류	데이터 타입	기능
정수	TINYINT	-128~127 사이의 정수, 1byte 정수 ex. 나이, 학년 등 크기가 작은 정수
	SMALLINT	-32768~32767 사이의 정수, 2byte 정수 물품번호, 인원 등 중간 크기의 정수
	MEDIUMINT	-8388608~8388607 사이의 정수
	INT	-2147483648~2147483647 사이의 정수, 4yte 정수 ex. 물품의 금액, 전화번호 등의 일반 크기의 정수
	BIGINT	-9223372036854775808~9223372036854775807 사이의 정수, 8yte 정수 ex. 계좌의 잔고, 천문학적인 크기의 정수
고정 소수형	DECIMAL	DECIMAL(M,N) : 전체 M자리, 소수점 이하 N자리 ex. DECIMAL(5,2)는 -999.99~999.99 사이의 십진수 표현 ex. DECIMAL(10,2)는 전체 10자리 중 8자리는 정수, 2자리는 소수점 이하의 수
	NUMERIC	DECIMAL과 동일
부동 소수형	FLOAT	4byte의 크기를 갖는 부동 소수
	FLOAT(P)	유효숫자를 저장하기 위한 자릿수가 P개인 부동 소수
	DOUBLE	8byte의 크기를 갖는 부동 소수
문자	CHAR(n)	길이가 n으로 고정된 문자열
	VARCHAR(n)	최대 n개로 구성된 가변길이 문자열
	TEXT	길이가 긴 가변길이 문자열
	ENUM	유한 개의 문자열 집합 중 하나의 값을 선택
날짜/시간	DATE	'CCYY-MM-DD'
	TIME	'HH:MI:SS'
	DATETIME	'CCYY-MM-DD HH:MI:SS', 8byte '1000-01-01 00:00:00'부터 '9999-12-31 23:59:59'까지의 범위 날짜와 시간이 항상 일정함. → 되도록 DATETIME 사용 권장
	TIMESTAMP	'CCYY-MM-DD HH:MI:SS', 4byte '1970-01-01 00:00:01'부터 '2038-01-09 03:14:07'까지의 범위 MySQL 서버의 타임존에 따라 날짜와 시간이 변경됨.
	YEAR	'CCYY'

 

문자 데이터 타입

CHAR(n)	VARCHAR(n)
최대 길이 초과 : 초과한 만큼의 문자가 지워짐
선언된 컬럼 길이가 고정, 빈 공간은 공백문자로 채워짐	각 컬럼값의 길이에 맞춰 컬럼 길이가 유지됨.
별도로 실제 문자열 길이를 관리하지 않음	별도의 문자열 길이에 대한 정보를 관리함
수정 및 검색 속도가 빠름	수정 및 검색 속도가 느림
컬럼값의 길이 변화가 크지 않은 값 ex. 주민등록번호, 학생번호 등	-

한글은 1글자가 2byte라서 2칸씩 차지함. CHAR(30)으로 쓰면 30칸 모두 차지함. VARCHAR(30)으로 쓰면 6칸만 차지함. → 이후 데이터 수정이 발생하면 공간을 당기거나 밀면서 이동함.

TEXT, CLOB

- 길이가 최대 2~4GB인 가변길이 문자열, 최대 20억자까지 들어갈 수 있음
- 길이가 굉장히 길어질 때만 사용할 수 있는 타입

ENUM 

- 유한개의 문자열 집합 중 하나의 값을 선택

- 다른 값들이 실수로 들어가지 않도록 할 수 있음.

ex. 성별 - ENUM('남', '여'), 혈액형 - ENUM('A', 'B', 'O', 'AB')

---

테이블 수정

생성된 테이블에 컬럼을 추가, 수정(이름, 데이터 타입, 제약조건) 또는 삭제하는 명령

컬럼 삭제 또는 컬럼의 데이터 타입 수정 시 데이터에 대한 소실이 발생하므로 많은 주의가 요구됨.

 

테이블 수정 방법

· SQL 구문

· 내비게이터 패널

 

구문형식

새로운 컬럼을 추가, 삭제 및 수정하는 등의 테이블의 구조를 변경

출처 : 4강 강의록

ADD COLUMN	주어진 컬럼의 이름과 데이터 타입에 해당하는 새로운 컬럼을 추가함.	교수 테이블에 데이터 타입이 INT인 칼럼을 추가     ALTER TABLE 교수         ADD 나이 INT;
DROP COLUMN	지정된 컬럼을 테이블에서 삭제함.	교수 테이블에서 '직위' 컬럼을 삭제     ALTER TABLE 교수         DROP COLUMN 직위;
CHANGE COLUMN	컬럼의 이름이나 데이터 타입 또는 제약조건을 변경함.	교수 테이블에서 '나이' 컬럼의 이름을 '직위'로 변경하고, 데이터 타입을 VARCHAR(50)으로 지정     ALTER TABLE 교수         CHANGE COLUMN 나이 직위 VARCHAR(50);
MODIFY COLUMN	컬럼의 데이터 타입을 변경함.	교수 테이블의 '나이' 컬럼의 데이터 타입을 INT로 변경     ALTER TABLE 교수         MODIFY COLUMN 나이 INT;

---

테이블 삭제

존재하는 테이블을 스키마에서 삭제

삭제할 테이블의 모든 데이터가 소실, 복구가 불가능한 연산이므로 각별한 주의가 요구됨.

 

테이블 삭제 방법

· SQL 구문

· 내비게이터 패널

 

구문형식

DROP TABLE 테이블 이름

테이블을 데이터베이스에서 제거

교수 테이블을 삭제 DROP TABLE 교수

---

제약조건

테이블과 테이블에 존재하는 데이터를 보다 무결하게 관리하기 위한 목적으로 사용

제약조건과 맞지 않은 경우 입력되지 않도록 함

DBMS는 테이블 조작 시 테이블에 정의된 제약조건을 만족시키는지 지속적으로 검사

DBMS는 적용하려는 제약의 유형에 따라 다양한 제약 조건을 지원

PRIMARY KEY	기본키 지정, 모든 레코드는 해당 컬럼에 대해서 UNIQUE와 NOT NULL 특성
FOREIGN KEY	외래키 지정, 참조 컬럼 정의 기본키, 외래키의 참조관계를 나타냄 참조하는 컬럼(FK)의 값이 참조되는 컬럼(PK)에 존재하지 않는 값일 경우 → 참조 무결성 제약조건에 의해 DBMS 실행을 거절함. FOREIGN KEY (참조하는 컬럼 이름)     REFERENCES 참조되는 테이블 이름 (참조되는 컬럼 이름)
NOT NULL (NN)	NULL이 될 수 없는 컬럼에 지정
UNIQUE	동일한 컬럼값을 가질 수 없음을 지정
AUTO_INCREMENT	레코드가 추가될 때 자동적으로 속성값이 1부터 1씩 증가되어 입력 INT 계열의 데이터 타입에만 적용 가능함. 처음 추가된 레코드의 경우 AUTO_INCREMENT 조건이 부여된 컬럼의 값이 1로 자동입력됨.
DEFAULT	해당 컬럼에 어떤 값이 입력되지 않으면 자동으로 지정된 값이 입력되도록 하는 조건
CHECK	컬럼값이 특정 조건 준수 여부 지정 ex. CHECK(나이>18) · CHECK (조건)  · CONSTRAINT id CHECK (조건)

 

제약조건의 응용

 

기본키 제약조건 작성 방법

· 컬럼의 제약조건을 기술하는 부분: ID CHAR(20) PRIMARY KEY

· 맨 하단: PRIMARY KEY (ID)

 

통신사에 들어가는 컬럼의 값은 이 세 개 중 하나에 포함되어야 한다

· CHECK (통신사 IN('SKT', 'KT', 'LGT')

· ENUM('SKT', 'KT', 'LGT')