DB06 - SQL DDL

아젠다

- SQL DDL (데이터 정의 언어)

 

SQL 명령어

 

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

1. SQL 데이터 정의 언어(DDL)는 관계에 대한 정보를 지정할 수 있도록 하며, 포함 내용은 다음과 같다

- 각 관계의 스키마

- 각 속성에 대한 값의 유형

- 무결성 제약 조건

- 각 관계에 대해 유지해야 할 인덱스 집합 (Set of indices)

- 각 관계에 대한 보안 및 권한 정보

- 디스크에서 각 관계의 물리적 저장(physical storage) 구조

 

2. 주요 세 가지 명렁

- CREATE

- ALTER 

- DROP

 

데이터베이스 생성 (CREATE DATABASE)

: 새로운 데이터베이스를 초기화하기 위해 사용한다.

1. 기본 문법

CREATE DATABASE 데이터베이스_이름;

이 명령어와 함께 기본 문자 인코딩 방식을 지정할 수도 있다.

CREATE DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

 

• Collation(정렬 규칙): 문자 문자열을 비교하고 정렬하는 규칙의 집합이다.

2. 데이터베이스를 생성한 후, 이를 사용하려면 다음 명령어를 실행한다.

USE 데이터베이스_이름;

테이블 생성 (CREATE TABLE)

: 새로운 테이블을 생성하기 위해 사용한다.

1. 기본 문법

CREATE TABLE 테이블_이름(
    열1_이름 데이터_타입[(크기)],
    열2_이름 데이터_타입[(크기)]
);

2. 예: 네 개의 열을 가진 테이블 생성

CREATE TABLE books(
    ISBN CHAR(20),
    Title CHAR(50),
    AuthorID INTEGER,
    Price FLOAT
);

SQL의 데이터 타입 (Data Types in SQL)

: 대부분의 데이터베이스 관리 시스템(DBMS)에서 존재하는 데이터 타입의 범주는 다음과 같다.

• 문자열 데이터 (String data)
• 숫자 데이터 (Numeric data)
• 시간 데이터 (Temporal data)
• 대용량 객체 (Large objects)

Domain Types in SQL (SQL의 도메인 유형)

1. SQL 데이터 유형 - (1)

1) CHAR(n): 사용자가 지정한 길이 n의 고정 길이 문자(Fixed length) 문자열

- 최대 길이 n = [0, 255]

2) VARCHAR(n): 사용자가 지정한 최대 길이 n의 가변 길이 문자(Variable length) 문자열

- 최대 길이 n = [0, 65,535]

- 길이가 항상 같은 경우 CHAR 타입 속성을 사용하고, 길이가 크게 변동하는 문자열을 저장하는 경우 VARCHAR 타입 속성을 사용하라

3) TEXT: VARCHAR 범위를 초과하는 문자열

- TINYTEXT : 0 – 255 바이트

- TEXT : 0 – 65,535 바이트

- MEDIUMTEXT : 0 – 16,777,215 바이트

- LONGTEXT : 0 – 4,294,967,295 바이트

 

2. CHAR와 VARCHAR의 차이

 

3. “\”%ab%\””

-> "%ab%"가 출력이 된다.

 

 

4. SQL 데이터 유형 - (2)

1) INT, INTEGER: 정수(기계 의존적인 유한 정수 집합)

2) SMALLINT: 작은 정수(정수 도메인 타입의 기계 의존적 하위 집합)

3) BIGINT: 큰 정수(정수 도메인 타입의 기계 의존적 하위 집합)

4) TINYINT 및 MEDIUMINT도 사용 가능

 

5. 다양한 R-DBMS는 이러한 정수 유형의 조합을 다르게 지원한다

- 예를 들어, Oracle은 NUMBER 데이터 유형만 있다

 

6. SQL 데이터 유형 - (3)

1) NUMERIC(p,d): 사용자가 지정한 p 자리의 정밀도로 고정 소수점 숫자(정확한 값)이며, 소수점 오른쪽에 d 자리의 숫자가 있다

- 예: NUMERIC(3,1)은 44.5를 정확히 저장할 수 있지만 444.5 또는 0.32는 저장할 수 없다

- MySQL에서 DECIMAL은 NUMERIC이다

2) FLOAT: 단정도 부동 소수점 숫자(근사값)

3) REAL, DOUBLE: 이중 정밀도의 부동 소수점 숫자(근사값)

 

7. DECIMAL vs INT/FLOAT/DOUBLE

1) FLOAT와 DOUBLE은 DECIMAL보다 빠르다

2) DECIMAL 값은 정확하다

(1) 예시

- SELECT SUM(…)에서 DECIMAL 값은 정확하다

 

---

 

8. SQL 데이터 유형 - (4)

1) DATE: ‘YYYY-MM-DD’

(1) 범위: 1000-01-01 ~ 9999-12-31

(2) 예: ‘2020-03-01’은 2020년 3월 1일을 나타낸다

2) TIME: ‘HH:MM:SS’

(1) 범위: -838:59:59 ~ 838:59:59

(2) 예: ’14:30:03.5’는 오후 2시 30분 3.5초를 나타낸다

3) DATETIME: ‘YYYY-MM-DD HH:MM:SS’

(1) 범위: 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

4) YEAR: ‘YYYY’

(1) 범위: 1901 ~ 2155 또는 0000(불법 연도 값은 0000으로 변환된다)

 

9. SQLx 데이터 유형 - (5) 

1) TIMESTAMP(n): Unix 시간(1970년 1월 1일 이후 시간)

(1) 범위: 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC

(2) 일반적으로 로그 기록(모든 시스템 이벤트의 기록 유지)에 사용된다

(3) 크기 n에 따라 표시 패턴이 변경된다

 

 

10. SQL 데이터 유형 - (6)

1) BINARY(n): 사용자가 지정한 길이 n의 이진 바이트 데이터 타입

(1) 바이트 문자열(문자열이 아닌) 포함

(2) 최대 길이 n = [0, 255]

2) VARBINARY(n): 사용자가 지정한 최대 길이 n의 이진 바이트 데이터 타입

(1) 최대 길이 n = [0, 65,535]

3) BLOB: 이진 대용량 개체 데이터 타입

(1) TINYBLOB : 0 – 255 바이트

(2) BLOB0  : – 65,535 바이트(65 KB)

(3) MEDIUMBLOB 0 : – 16,777,215 바이트(16 MB)

(4) LONGBLOB : 0 – 4,294,967,295 바이트(4 GB)

 

CREATE TABLE Construct

1. 새로운 관계는 CREATE TABLE 명령어를 사용하여 정의된다:

CREATE TABLE r(
  A1 D1, A2 D2, …, An Dn,
  (무결성 제약 조건1),
  …,
  (무결성 제약 조건k)
)

- r은 관계의 이름이다

- 각 Ai는 관계 r의 스키마에서 속성 이름이다

- 각 Di는 속성 Ai의 도메인 내 값의 데이터 유형이다

 

2. 예시

CREATE TABLE instructor(
  ID CHAR(5),
  name VARCHAR(20),
  dept_name VARCHAR(20),
  salary NUMERIC(8,2)
)

 

Integrity Constraints in CREATE TABLE

1. SQL은 무결성 제약 조건을 위반하는 데이터베이스 업데이트를 방지한다

- 무결성 제약 조건을 통해 우리에게 의미 있는 데이터를 지정할 수 있다

 

2. 무결성 제약 조건의 종류

- 기본 키: PRIMARY KEY (A1, ..., An)

- 외래 키: FOREIGN KEY (Am, ..., An) REFERENCES r

- 유니크 키: UNIQUE

- 널이 아님: NOT NULL

- 값 제약 조건 : CHECK (제약 조건), DEFAULT

 

3. 예시:

CREATE TABLE instructor(
  ID CHAR(5),
  name VARCHAR(20) NOT NULL,
  dept_name VARCHAR(20),
  salary NUMERIC(8, 2),
  PRIMARY KEY (ID),
  FOREIGN KEY (dept_name) REFERENCES department
);

 

Declaring KEY AN UNIQUE Constraints

1. 속성 또는 속성 목록은 PRIMARY KEY 또는 UNIQUE로 선언될 수 있다

1) 의미: 관계의 두 튜플이 목록의 모든 속성에서 일치하지 않도록 한다

(1) 즉, 속성은 값의 중복을 허용하지 않는다

(2) PRIMARY KEY/UNIQUE는 각 행의 식별자로 사용될 수 있다

2) 비교: PRIMARY KEY vs UNIQUE

PRIMARY KEY (기본키)	UNIQUE (고유)
각 관계(relation)의 행을 고유하게 식별하는데 사용됨	행을 고유하게 결정하지만 기본키가 아님
NULL 값을 허용하지 않음	NULL 값을 허용함 (일부 DBMS는 하나의 NULL 값만 허용)
관계는 오직 하나의 기본키만 가질 수 있음	관계는 둘 이상의 고유 속성을 가질 수 있음
클러스터형 인덱스	비클러스터형 인덱스

2. 예시 

-- 학생 정보를 저장하는 테이블
CREATE TABLE student (
  ID VARCHAR(5),  -- 학생의 고유 ID
  name VARCHAR(20) NOT NULL,  -- 학생 이름 (NULL 불가)
  dept_name VARCHAR(20),  -- 학과 이름
  tot_cred NUMERIC(3,0),  -- 총 학점
  PRIMARY KEY (ID),  -- ID를 기본 키로 설정
  FOREIGN KEY (dept_name) REFERENCES department  -- 학과 이름을 department 테이블의 기본 키와 연결
);

 

CREATE TABLE student (
  ID VARCHAR(5) PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  dept_name VARCHAR(20),
  tot_cred NUMERIC(3,0),
  FOREIGN KEY (dept_name) REFERENCES department
);

CREATE TABLE takes (
  ID VARCHAR(5),
  course_id VARCHAR(8),
  sec_id VARCHAR(8),
  semester VARCHAR(6),
  year NUMERIC(4,0),
  grade VARCHAR(2),
  PRIMARY KEY (ID, course_id, sec_id, semester, year),
  FOREIGN KEY (ID) REFERENCES student,
  FOREIGN KEY (course_id, sec_id, semester, year) REFERENCES section
);

CREATE TABLE course (
  course_id VARCHAR(8),
  title VARCHAR(50),
  dept_name VARCHAR(20),
  credits NUMERIC(2,0),
  PRIMARY KEY (course_id),
  FOREIGN KEY (dept_name) REFERENCES department
);

CREATE TABLE course (
  course_id VARCHAR(8),
  title VARCHAR(50),
  dept_name VARCHAR(20) DEFAULT ‘Comp. Sci’,
  credits NUMERIC(2,0),
  PRIMARY KEY (course_id),
  FOREIGN KEY (dept_name) REFERENCES department
);

 

(1) NOT NULL을 적용하지 않은 예시

CREATE TABLE neighbors(
  name CHAR(30) PRIMARY KEY,
  addr CHAR(50) DEFAULT ‘123 Sesame St.’,
  phone CHAR(16)
);

-- 엘모를 이웃으로 추가:
INSERT INTO neighbors (name) VALUES (‘Elmo’);

 

name	addr	phone
'Elmo'	'123 Sesame Street.'	NULL

 

(2) NOT NULL을 적용한 예시

CREATE TABLE neighbors(
  name CHAR(30) PRIMARY KEY,
  addr CHAR(50) DEFAULT ‘123 Sesame St.’,
  phone CHAR(16) NOT NULL
);

-- 엘모를 이웃으로 추가:
INSERT INTO neighbors (name) VALUES (‘Elmo’);

 

- 만약 phone이 NOT NULL이라면, 이 삽입은 거부되었을 것이다

 

---

 

Table Updates (Updating Tuples)

1. INSERT

INSERT INTO instructor VALUES (‘10211’, ‘Smith’, 'Biology', 66000);

 

2. DELETE

-- 학생 관계에서 모든 튜플을 제거한다.
DELETE FROM student

 

Table Updates (Updating Table Schemas)

 

1. DROP TABLE

-- 관계 r를 제거한다
DROP TABLE r

 

2. ALTER

ALTER TABLE r ADD A D

1) A는 관계 r에 추가할 새로운 속성의 이름이며, D는 A의 도메인이다

2) 기존의 모든 튜플은 새로운 속성에 대해 null 값을 할당받는다

ALTER TABLE r DROP A

1) A는 r의 속성 이름이다

2) 많은 데이터베이스에서 속성을 제거하는 기능은 지원되지 않지만(MySQL은 지원함)

 

3. 예시

DROP TABLE time_slot_backup;
ALTER TABLE time_slot_backup ADD remark VARCHAR(20);
ALTER TABLE time_slot_backup DROP remark;

 

1) DROP a column

ALTER TABLE instructor
DROP COLUMN salary;

• 이 명령어는 instructor 테이블에서 salary라는 열(컬럼)을 삭제한다. 해당 열에 저장된 모든 데이터도 함께 삭제된다.

2) DROP a PRIMARY KEY Constraint

ALTER TABLE instructor
DROP PRIMARY KEY;

• 이 명령어는 instructor 테이블에서 기본 키 제약 조건을 삭제한다. 기본 키가 제거되면 해당 열은 더 이상 고유성을 보장하지 않으며 NULL 값을 허용할 수 있다.

3) DROP a FOREIGN KEY Constraint

ALTER TABLE instructor
DROP FOREIGN KEY instructor_ibfk_1;

• 이 명령어는 instructor 테이블에서 외래 키 제약 조건인 instructor_ibfk_1을 삭제한다. 외래 키 제약 조건이 제거되면, 해당 열은 참조 무결성 검사를 하지 않게 된다.

4) DROP DEFAULT

ALTER TABLE student
ALTER tot_cred DROP DEFAULT;

• 이 명령어는 student 테이블에서 tot_cred 열의 기본값을 삭제한다. 이후에는 tot_cred 열에 값을 입력하지 않으면 NULL로 저장된다.

5) DROP a database

DROP DATABASE university;

• 이 명령어는 university 데이터베이스를 삭제한다.

 

EOF

- 다음 내용: 데이터베이스 설계