0033

학교

DB06 - SQL DDL

아젠다

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

SQL 명령어

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

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

- 각 관계의 스키마

- 각 속성에 대한 값의 유형

- 무결성 제약 조건

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

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

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

2. 주요 세 가지 명렁

- CREATE

- ALTER

- DROP

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

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

1. 기본 문법

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

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

SQL
CREATE DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

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

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

SQL
USE 데이터베이스_이름;

테이블 생성 (CREATE TABLE)

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

1. 기본 문법

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

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

SQL
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 명령어를 사용하여 정의된다:

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

- r은 관계의 이름이다

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

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

2. 예시

SQL
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. 예시:

SQL
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. 예시

SQL
-- 학생 정보를 저장하는 테이블
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 테이블의 기본 키와 연결
);
SQL
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
);
SQL
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
);
SQL
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
);
SQL
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을 적용하지 않은 예시

SQL
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을 적용한 예시

SQL
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

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

2. DELETE

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

Table Updates (Updating Table Schemas)

1. DROP TABLE

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

2. ALTER

SQL
ALTER TABLE r ADD A D

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

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

SQL
ALTER TABLE r DROP A

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

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

3. 예시

SQL
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

SQL
ALTER TABLE instructor
DROP COLUMN salary;

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

2) DROP a PRIMARY KEY Constraint

code
ALTER TABLE instructor
DROP PRIMARY KEY;

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

3) DROP a FOREIGN KEY Constraint

SQL
ALTER TABLE instructor
DROP FOREIGN KEY instructor_ibfk_1;

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

4) DROP DEFAULT

code
ALTER TABLE student
ALTER tot_cred DROP DEFAULT;

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

5) DROP a database

SQL
DROP DATABASE university;

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

EOF

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