0123

학교

[DB] DB06 - 요약

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

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

  • 각 관계의 스키마

  • 각 속성에 대한 값의 유형

  • 무결성 제약 조건

  • 각 관계에 대해 유지해야 할 인덱스 집합

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

  • 디스크에서 각 관계의 물리적 저장 구조

Database 생성

  1. CREATE DATABASE: 새로운 데이터베이스를 초기화하는 명령어이다.

  2. 기본 문법:

SQL
CREATE DATABASE database_name;

  1. 문자 인코딩 지정: 데이터베이스 생성 시 기본 문자 인코딩을 설정할 수 있다.

code
CREATE DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

  1. Collation (정렬 규칙): 문자열을 비교하고 정렬하는 방식을 정의하는 규칙이다.

  2. 데이터베이스 사용: 데이터베이스를 생성한 후 USE database_name; 명령어를 사용하여 해당 데이터베이스를 활성화할 수 있다.

테이블 생성 (CREATE TABLE)

: CREATE TABLE: 새로운 테이블을 생성하는 명령어이다.

1. 기본 문법:

code
CREATE TABLE table_name (
	column1_name data_type[(size)],
	column2_name data_type[(size)]
);

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

  • ISBN CHAR(20): ISBN을 저장하는 문자열(최대 20자)

  • Title CHAR(50): 책 제목을 저장하는 문자열(최대 50자)

  • AuthorID INTEGER: 저자 ID를 저장하는 정수

  • Price FLOAT: 책 가격을 저장하는 실수

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

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

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

  1. 문자열 데이터 (String data)

    • 문자와 텍스트를 저장하는 데이터 타입이다. 예: CHAR, VARCHAR, TEXT 등

  2. 숫자 데이터 (Numeric data)

    • 정수 및 실수를 저장하는 데이터 타입이다. 예: INTEGER, FLOAT, DOUBLE 등

  3. 시간 데이터 (Temporal data)

    • 날짜 및 시간 정보를 저장하는 데이터 타입이다. 예: DATE, TIME, DATETIME, TIMESTAMP 등

  4. 대용량 객체 (Large objects)

    • 이미지, 비디오, 오디오와 같은 대용량 데이터를 저장하는 데이터 타입이다. 예: BLOB, CLOB 등

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 바이트

  1. CHAR와 VARCHAR의 차이

: CHAR(4)는 항상 4바이트를 사용하여 저장되며, VARCHAR(4)는 실제 저장된 문자열의 길이에 따라 메모리를 사용한다.

: 예를 들어, "abcdefg"와 같이 CHAR(4)보다 긴 문자열은 잘리게 되어 4바이트만 사용되고, VARCHAR(4)는 전체 길이만큼 메모리를 사용하게 된다.

  1. “\”%ab%\””

주어진 문자열 "\"%ab%\""는 SQL에서 사용되는 와일드카드 문자와 이스케이프 문자와 관련된 내용이다. 아래는 이 문자열의 구성 요소와 그 의미에 대한 설명이다.

1) 문자열의 구성:

  • \ : 이스케이프 문자. 뒤에 오는 특수 문자를 문자 그대로 해석하도록 돕는다.

  • % : SQL에서 사용되는 와일드카드 문자. 0개 이상의 임의의 문자와 일치한다.

  • ab : 일반 문자, 문자열 내에서 "ab"라는 값을 나타낸다.

  • \ : 다시 이스케이프 문자로 사용되어 뒤에 오는 %를 문자 그대로 인식하게 한다.

  • % : 마지막의 와일드카드 문자.

  1. 의미:

  • 이 문자열 "\\\\%ab\\\\%"는 SQL 쿼리에서 "ab"라는 문자열을 포함하고, %는 어떤 문자도 포함할 수 있음을 나타낸다.

  • 이스케이프 문자를 사용하여 %를 문자 그대로 인식하게 하여 "ab" 앞뒤에 어떤 문자가 올 수 있음을 표현한다.

  1. 사용 예:

  • 예를 들어, SQL 쿼리에서 이 패턴을 사용하면 LIKE 연산자를 통해 "ab" 앞뒤에 어떤 문자가 올 수 있는지 검색할 수 있다.

SQL
SELECT * FROM 테이블명 WHERE 열명 LIKE '%ab%';

  • 이 쿼리는 "ab"가 포함된 모든 레코드를 검색한다.

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

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

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

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

  1. TINYINT 및 MEDIUMINT도 사용 가능

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

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

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

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

  • 예: NUMERIC(3,1)은 최대 3자리 숫자 중 소수점 이하에 1자리까지 표현할 수 있다. 따라서 44.5는 저장할 수 있지만, 444.5(자릿수 초과)나 0.32(소수점 이하 자릿수 초과)는 저장할 수 없다

  • MySQL에서 DECIMAL은 NUMERIC이다

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

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

  • > NUMERIC은 정확한 소수점 숫자를 저장할 수 있는 반면, FLOAT, REAL, DOUBLE은 근사값을 저장하는 부동 소수점 숫자이다.

  1. DECIMAL vs INT/FLOAT/DOUBLE

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

  2. DECIMAL 값은 정확하다

  1. 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으로 변환된다)

  1. 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에 따라 표시 패턴이 변경된다

  1. 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

: 새로운 데이터 테이블은 CREATE TABLE 명령어를 사용하여 생성한다.

  1. 테이블의 기본 구조는 다음과 같다:

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

  • 여기서 r은 생성할 테이블의 이름이다.

  • Ai는 테이블의 속성(컬럼) 이름을 나타내고, Di는 해당 속성의 데이터 유형을 나타낸다. 각 속성은 특정한 데이터 형식으로 값을 저장할 수 있다.

  • 무결성 제약 조건은 데이터의 일관성을 유지하기 위해 설정하는 규칙으로, 테이블의 마지막 부분에 추가된다.

테이블 생성의 무결성 제약 조건

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

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

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

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

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

  • 유니크 키: UNIQUE

  • 널이 아님: NOT NULL

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

Declaring KEY AN UNIQUE Constraints

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

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

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

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

  1. 비교: PRIMARY KEY vs UNIQUE

PRIMARY KEY (기본키)UNIQUE (고유)

각 관계(relation)의 행을 고유하게 식별하는데 사용됨

행을 고유하게 결정하지만 기본키가 아님

NULL 값을 허용하지 않음

NULL 값을 허용함 (일부 DBMS는 하나의 NULL 값만 허용)

관계는 오직 하나의 기본키만 가질 수 있음

관계는 둘 이상의 고유 속성을 가질 수 있음

클러스터형 인덱스

비클러스터형 인덱스

Integrity Constraints Recap

: 기본 키(Primary key), 외래 키(Foreign key), 고유 키(Unique 또는 Candidate key)는 DDL로 지정할 수 있다.

  • 하나의 열 또는 여러 개의 열을 키로 지정할 수 있다.

  • 고유한 열 집합이 선언되면, 중복 입력은 거부된다.

  1. PRIMARY KEY / UNIQUE

code
CREATE TABLE studio(
  ID		NUMERIC(5, 0),
  name		VARCHAR(20),
  city		VARCHAR(200,
  state		CHAR(2)

# case1UNIQUE(name),
  UNIQUE(city, state)

# case2
  PRIMARY KEY(ID),
  UNIQUE(name),
  UNIQUE(city, state)
)

CREATE TABLE studio(
  ID		NUMERIC(5, 0) PRIMARY KEY,
  name		VARCHAR(20) UNIQUE,
  city		VARCHAR(200),
  state		CHAR(2)
  UNIQUE(city, state)
)

  • PRIMARY KEY(ID)는 ID 속성이 기본 키로 설정되어 각 레코드의 고유성을 보장한다. 기본 키는 NULL 값을 가질 수 없다.

  • UNIQUE(name)는 name 속성이 유일해야 함을 나타내어 중복 입력을 방지한다.

  • UNIQUE(city, state)는 city와 state 조합이 유일해야 함을 나타내어, 동일한 도시와 주 조합이 중복될 수 없도록 한다.

  1. FOREIGN KEY

code
CREATE TABLE studio(
  ID		NUMERIC(5, 0) PRIMARY KEY,
  name		VARCHAR(20) UNIQUE,
  city		VARCHAR(200),
  state		CHAR(2)
  UNIQUE(city, state),
  FOREIGN KEY (state) REFERENCES states
)

  • FOREIGN KEY (state) REFERENCES states는 state 속성이 다른 테이블인 states의 기본 키와 연결되어 있음을 나타낸다. 즉, state에 입력되는 값은 states 테이블에 존재해야 한다.

  1. NOT NULL

SQL
CREATE TABLE studio(
  ID		NUMERIC(5, 0) PRIMARY KEY,
  name		VARCHAR(20) NOT NULL,
  city		VARCHAR(200) NULL.
  state		CHAR(2) NOT NULL,
)

  • NOT NULL 제약 조건은 해당 속성이 NULL 값을 가질 수 없음을 나타낸다. 여기서 name과 state는 필수 입력값이며, 레코드 생성 시 반드시 값이 제공되어야 한다.

  • city는 NULL을 허용하므로, 입력하지 않아도 된다.

  1. DEFAULT

: 기본값은 이 키워드가 있는 모든 열에 삽입할 수 있다.

  1. E.g.

code
CREATE TABLE movies(
  movie_title VARCHAR(40) NOT NULL,
  release_date DATE DEAFULT sysdate NULL,
  genre VARCHAR(20) DEFAULT 'Comeday' CHECK gener IN('Comedy', 'Action', 'Drama')
)

  • DEFAULT 키워드는 열에 기본값을 설정할 수 있게 해준다.

  • release_date는 기본값으로 현재 날짜를 사용하고, genre는 기본값으로 'Comeday'를 사용하며, 'Comedy', 'Action', 'Drama' 중 하나여야 한다는 추가 제약 조건이 있다.

  1. In MySQL

code
CREATE TABLE movies(
  movie_title VARCHAR(40) NOT NULL,
  release_date DATE DEAFULT CURRENT_TIMESTAMP NULL,
# CURRENT _TIMESTAMP -> NOW()
  genre VARCHAR(20) DEFAULT 'Comeday' CHECK gener IN('Comedy', 'Action', 'Drama')
)

  • MySQL에서는 CURRENT_TIMESTAMP를 사용하여 기본값으로 현재 시간을 자동으로 입력받을 수 있다.

  1. CHECK

: 데이터 입력 시 특정 조건을 만족하는지 검증할 수 있게 해준다.

  1. E.g.

SQL
CREATE TABLE movies(
	movie_title VARHCHAR(40 PRIMARY KEY,
    release_date DATE,
    budget INTEGER CHECK(budget > 50000)
)

  • 이 테이블에서 budget 속성은 CHECK(budget > 50000) 제약 조건에 따라 50,000보다 큰 값만 입력할 수 있다. 즉, 예산이 50,000 이하인 영화는 저장할 수 없다.

  1. 테이블 수준의 제약 조건 정의 가능 : 예제

SQL
CREATE TABLE movies(
	movie_title VARHCHAR(40 PRIMARY KEY,
    release_date DATE,
    budget INTEGER CHECK(budget > 50000),
    CONSTRAINT release_date_const
    CHECK (release_date BETWEEN '01-JAN-2000' AND '31-Desc-2009')
)

  • 이 예시에서는 release_date 속성에 대해 CHECK 제약 조건을 사용하여 2000년 1월 1일부터 2009년 12월 31일 사이의 날짜만 입력할 수 있도록 제한하고 있다.

  • CONSTRAINT release_date_const는 이 제약 조건에 이름을 붙여주어 나중에 참조하거나 수정하기 쉽게 한다.

Table Updates (Updating Tuples)

  1. INSERT

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

  1. DELETE

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

Table Updates (Updating Table Schemas)

  1. DROP TABLE

: DROP TABLE 명령어는 지정된 테이블(관계)을 제거하는 데 사용된다.

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

  • 이 명령어는 테이블 r과 그 안의 모든 데이터를 삭제한다. 삭제된 테이블은 복구할 수 없으므로 주의해야 한다.

  1. ALTER

: ALTER TABLE 명령어는 기존 테이블의 구조를 변경하는 데 사용된다.

code
ALTER TABLE rADD A D

  • 여기서 A는 새로 추가할 속성의 이름이며, D는 해당 속성의 데이터 유형이다.

  • 기존의 모든 레코드는 새로운 속성에 대해 NULL 값을 할당받는다.

code
ALTER TABLE rDROP A

  • A는 제거할 속성의 이름이다.

  • 많은 데이터베이스 시스템에서 속성을 제거하는 기능은 지원되지 않지만, MySQL에서는 이 기능을 지원한다.

  1. 예시

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

  • 첫 번째 명령어는 time_slot_backup 테이블을 삭제한다.

  • 두 번째 명령어는 time_slot_backup 테이블에 remark라는 새로운 속성을 추가하고, 데이터 유형은 VARCHAR(20)으로 설정한다.

  • 세 번째 명령어는 remark 속성을 time_slot_backup 테이블에서 제거한다.