1. 데이터베이스 객체
관계형 데이터베이스(RDBMS, Relational Database Management System)는 데이터가 여러개의 2차원 테이블에 저장되고 관리된다. 실제로 웹 서비스에서 필요한 데이터를 체계적으로 데이터베이스 시스템에 저장하기 위해서 정규화된 2차원 테이블을 작성해서 구축해나가는 스키마(Schema) 설계 작업(논리적)이 선행된다. 이후 실제 물리적 서버에 RDBMS software (ex. MySQL)를 설치하고 Table, View, Index, Procedure 등의 데이터베이스 객체를 작성해서 데이터베이스(Schema)를 구축한다.
✅ Database Object
**데이터베이스 객체**란 2차원 테이블(Table), 뷰(View), 인덱스(Index) 등 데이터베이스 내에 정의하는 모든 것을 일컫는 말이다. 더 쉽게 얘기하면, 데이터베이스 내에 실체를 가지는 어떤 것을 말합니다. 테이블이 데이터베이스 객체의 대표적인 예시이다. 또한, 객체의 종류에 따라 객체 내에 저장되는 데이터도 달라진다. 데이터베이스 객체는 이름을 갖고, 이름을 붙일 때는 다음과 같은 명명규칙에 맞게 지정해야 한다.
- 기존 이름이나 예약어와 중복하지 않는다.
- 숫자로 시작할 수 없다.
- 언더스코어(_) 이외의 기호는 사용할 수 없다.
- 한글을 사용할 때는 더블쿼트(MySQL에서는 백쿼트)로 둘러싼다.
- 시스템이 허용하는 길이를 초과하지 않는다.
- 객체에 이름을 정할때는 객체가 담고 있는 정보를 명학하게 표현할 수 있는 이름을 선택하도록 한다.
✅ Schema
데이터베이스 내의 객체(table, view, index)는 스키마(Schema)라는 컨테이너 안에 만들어진다. mysql(클라이언트 프로그램)으로 mysqld(서버 프로그램)에 접속한 후 show databases; 명령을 통해 MySQL 서버 내의 데이터베이스 목록을 출력해 보면 information_schema, performance_schema, mysql과 같이 MySQL 소프트웨어를 설치하면 기본적으로 생성되는 데이터베이스가 존재하는 것을 확인할 수 있다.
$ mysql -u root -pmysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 Homebrew
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW DATABASES;
+--------------------------+
| Database |
+--------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------------+
5 rows in set (0.00 sec)해당 스키마 내에는 mysql 서버 관리를 위해서 필요한 여러가지 테이블, 뷰, 인덱스와 같은 데이터베이스 객체들이 담겨있다. 아래와 같이 명령 프롬프트에서 information_schmea를 사용하겠다고 MySQL 서버에 알려주고, SHOW TABLES; 명령을 전달하면 해당 스키마 내에 테이블(table) 객체 목록을 출력해 볼 수 있다.
mysql> USE information_schema;
mysql> SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_CACHED_INDEXES |
| INNODB_CMP |
| TABLES |
| TABLES_EXTENSIONS |
| TABLESPACES |
| TABLESPACES_EXTENSIONS |
| TRIGGERS |
| USER_ATTRIBUTES |
| USER_PRIVILEGES |
| VIEW_ROUTINE_USAGE |
| VIEW_TABLE_USAGE |
| VIEWS |
+---------------------------------------+
79 rows in set (0.00 sec)MySQL에서는 이와 같이 CREATE DATABASE;라는 DDL이라는 SQL명령으로 작성한 데이터베이스(Database)가 스키마(Schema)가 된다.
mysql> CREATE DATABASE instagram;
mysql> SHOW DATABASES;
+--------------------------+
| Database |
+--------------------------+
| information_schema |
| mysql |
| performance_schema |
| instagram |
| sys |
+--------------------------+
5 rows in set (0.00 sec)
정리하면, 스키마는 데이터베이스 내의 객체(table, view, index)를 담는 컨테이너라고 정의할 수 있다. 테이블과 스키마는 무엇인가를 담는 그릇 역할을 한다는 점에서 비슷하다. 테이블 안에는 열을 정의할 수 있고 스키마 안에는 테이블을 정의할 수 있습니다.
2. DDL (Data Definition Language)
✅ DDL이란?
DDL은 Data Define Language의 약자로써 Schema 내의 객체를 정의하고 관리할 때 사용되는 쿼리문을 의미한다. CREATE, ALTER, DROP 이 대표적인 DDL이다. DDL에 속하는 쿼리문의 이름에서도 알 수 있듯이 데이터를 구성하는 논리적인 구조(데이터베이스 또는 테이블)를 추가하고 수정하고 삭제할 수 있다. 또한 메타데이터(데이터에 대한 데이터)를 다루는 쿼리문이라고 할 수 있습니다.
[그림1] SQL statements 종류
✅ 스키마 생성
Instagram의 미니어쳐 버전인 Westagram System을 만든다고 가정할 때, Westagram System에서 사용되는 스키마, 테이블을 관리하는 방법에 대해서 알아보자. 서비스에 필요한 데이터를 저장할 수 있는 데이터베이스 시스템을 구축할 때, 가장 먼저 테이블을 정의하기 전에 테이블 객체를 담을 수 있는 스키마를 생성한다.
$ mysql -u root -p #명령어로 mysql 서버에 접속해 프롬프트 창이 나타난 상태로 있어야 한다.mysql> CREATE DATABASE westagram; # Database 를 생성하는 명령어
mysql> SHOW DATABASES; # 현재 MySQL 에 저장되어있는 데이터베이스를 보는 명령어
+--------------------------+
| Database |
+--------------------------+
| westagram |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------------+아래와 같이 명령 프롬프트에서 instagram 스키마를 사용하겠다고 MySQL 서버에 알려주고, SHOW TABLES; 명령을 전달하면 해당 스키마 내에 테이블(table) 객체 목록을 출력해볼 수 있다. 아직까지는 테이블을 생성하지 않았으니 테이블의 정보가 없는 것을 확인할 수 있다.
mysql> USE westagram;
mysql> SHOW TABLES;
Empty set (0.00 sec)여기까지 SQL의 DDL 문법 중에 하나인 CREATE DATABASE를 instagram 스키마를 생성했다. 이어서 DDL을 활용해서 instagram schema 내에 필요한 테이블 객체를 생성, 수정, 삭제하는 방법을 살펴보자.
✅ Table 생성
RDBMS에서 스키마 내에서 가장 먼저 만드는 객체중에 하나가 바로 테이블이다. 미리 준비된 스키마 설계도(ERD, Entity Relationship Diagram)를 참고해서 위스타그램의 회원 정보를 저장할 사용자 테이블(users table)과 사용자가 등록한 게시글 정보가 담길 게시글 테이블(posts table) 두 개를 기준으로 DDL을 사용해 실제 테이블을 구축한다. 여기서, users와 posts table은 1:N 관계다. 즉, 하나의 사용자가 여러개의 게시물을 등록할 수 있다.
[그림2] 사용자 테이블과 게시글 테이블
DDL(Data Define Language)을 사용해서 데이터베이스에 사용자 테이블(users)을 생성한다. 이때 CREATE TABLE {테이블명} 명령을 사용하여 작성하고 싶은 테이블 이름을 지정하고 열(column)을 정의할 수 있다. 열(column)에는 컬럼명과 함께 자료형과 제약 조건을 지정해줄 수 있다.
자료형으로는 INTEGER, VARCHAR, DATETIME 등이 있는데, 특히 자주 사용되는 VARCHAR나 CHAR와 같은 문자열형으로 지정할 때는 최대길이를 정해줘야 한다.제약 조건으로는 자료형 뒤에 오는 NULL, NOT NULL, PRIMARY KEY 등이 있다. 문자 그대로 테이블 혹은 컬럼에 제약을 설정함으로써 저장될 데이터를 제한할 수 있다. 예를 들어 NOT NULL 제약은 해당 열에 NULL 값이 저장되지 않도록 제한하는 조건이다.
mysql> CREATE TABLE users
(
id INT NOT NULL AUTO_INCREMENT, # id 칼럼을 정수, NULL을 허락하지 않으며 값을 자동증가하게 설정.
name VARCHAR(50) NOT NULL, # name 칼럼을 50 bytes의 크기를 가지는 문자열, NULL을 허락하지 않는다.
email VARCHAR(200) NOT NULL,
profile_image VARCHAR(1000) NULL,
password VARCHAR(200) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(), # 데이터의 이력관리를 위해서 생성 시간을 기록.
updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP, # 데이터의 이력관리를 위해서 변경 시간을 기록.
PRIMARY KEY (id) # 이 테이블의 Primary Key를 id 로 설정.
); # 쿼리문은 ; 로 구분되기 때문에 엔터를 쳐서 쿼리문을 멀티라인으로 작성할 수 있다.
테이블을 생성한 후에 원하는 대로 잘 생성 되었는지 확인한다.
mysql> DESC users; # Description을 축약해 놓은 명령어. 테이블의 정보를 보여준다.
+---------------+---------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+-------------------+-----------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| email | varchar(200) | NO | | NULL | |
| profile_image | varchar(1000) | YES | | NULL | |
| password | varchar(200) | NO | | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
+---------------+---------------+------+-----+-------------------+-----------------------------+이번에는 DDL을 사용해서 데이터베이스에 게시물 테이블(posts)을 생성한다. posts 테이블을 생성할 때는 한 가지 더 고려해야될 조건이 있다. 사용자와 게시물이 1:M 관계이므로, 게시물 테이블에는 어떤 사용자가 작성한 게시글인지 테이블 간에 부모 자식 관계를 지정(데이터의 정합성을 위함)하기 위해서 외부키(FOREIGN KEY) 제약 조건을 설정해줘야 한다.아래와 같이 자식 테이블(posts)에 FOREIGN KEY를 지정해 부모 테이블을 참조하도록 한다. 이때, 부모 테이블에서 참조될 열은 반드시 유일성을 가져야 하므로 보통 PRIMARY KEY를 지정한다.
mysql> CREATE TABLE posts
(
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
content VARCHAR(2000) NULL,
user_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users (id) # 외래키를 걸어줄 칼럼과 참조할 테이블과 칼럼을 순서대로 적어준다.
);테이블을 생성한 후에 항상 원하는 대로 잘 생성 되었는지 확인한다.
mysql> DESC posts;
+------------+---------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+-------------------+-----------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| content | varchar(3000) | YES | | NULL | |
| user_id | int | NO | MUL | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
+------------+---------------+------+-----+-------------------+-----------------------------+출력된 테이블을 확인해보면, user_id field의 Key column을 보면 MUL 값이 들어가 있다. 다른 테이블의 PK를 참조하는 FK는 이처럼 MUL로 표현되고 MULTIPLE 이라는 뜻이다. posts 테이블의 여러 행(row)이 동일한 값을 가질 수 있음을 의미한다. 예를 들어, 다수의 게시물(post)이 동일한 user_id를 가질 수 있기 때문.
✅ Table 변경
기존에는 users 테이블에 name, email, profile_images, password만 필요했지만, 서비스를 이용하는 사용자들의 나이에 따라서 게시글을 추천해주는 기능을 만들기 위해서 회원가입시 나이(age)가 필요해졌고, 추가로 더이상 profile_image 컬럼이 필요 없어졌다고 가정해보자. 또한 posts 테이블에서 사용중이던 content의 저장 공간이 부족하여, 기존에 정의되어 있던 content VARCHAR(3000) 열의 자료형을 content TEXT 자료형으로 변경해야 되는 경우도 함께 가정해보자.
[그림3] 수정된 사용자 테이블과 게시글 테이블
간단하게 정리해보면, 테이블 변경 사항은 다음과 같다.
- users 테이블에 age 열을 추가. (자료형 → INT, 제약 조건 → NOT NULL)
- users 테이블의 profile_image 열을 삭제.
- posts 테이블의 content 열의 자료형을 VARCHAR에서 TEXT로 변경.
위 3개의 변경사항을 적용하는 가장 간단한 방법은 DROP TABLE 명령어로 테이블을 완전히 삭제하고 변경된 구조에 맞게 CREATE TABLE 명령으로 다시 생성하는 것이다. 하지만 이 방법은 초기에 처음 데이터베이스를 구축할 때 이거나 테이블이 비어있는 경우에 사용할 수 있는 방법이다. 이미 서비스되고 있는 경우에는 테이블에 많은 데이터가 존재하는 경우가 대부분이기 때문에, 데이터를 그대로 유지하면서 테이블의 구조를 변경할 수 있는 방법을 적용해야 한다.
MySQL에서 DDL(Data Define Language)를 사용해서 열(column)과 제약 조건(constraint)을 변경할 수 있다. 이때 사용하는 DDL은 ALTER TABLE 명령이다.
# 테이블 변경 Syntax
ALTER TABLE 테이블명 변경명령어📍 열(column) 추가
ALTER TABLE 명령에 변경하고자하는 테이블명(users) / 행위(ADD) / 열 이름(age) / 자료형(INT) / 제약 조건(NOT NULL) 순서로 지정하면, 새롭게 나이를 저장할 수 있는 컬럼을 users 테이블에 추가할 수 있습니다.
mysql> ALTER TABLE users ADD age INT NOT NULL;
Query OK, 0 rows affected (0.02 sec)테이블 구조가 정상적으로 변경되었는지 확인한다.
mysql> desc users;
+---------------+---------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+-------------------+-----------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| email | varchar(200) | NO | | NULL | |
| profile_image | varchar(1000) | YES | | NULL | |
| password | varchar(200) | NO | | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
| age | int | NO | | NULL | |
+---------------+---------------+------+-----+-------------------+-----------------------------+정상적으로 age 열이 생성된다. age 열의 Default 속성을 보시면 NULL로 되어 있다. 이는 ALTER TABLE ADD로 열을 추가할 때, 기존에 존재하는 데이터(row 혹은 record)의 age 열은 모두 NULL로 처리된다는 것이다. 지금처럼 NOT NULL 제약을 붙인 열을 추가하고 싶다면 먼저 NOT NULL 제약을 건 뒤에 존재하는 데이터들의 age 열의 값을 NULL 이외의 값으로 기본값을 지정해줘야 한다.
📍 열(column) 삭제
ALTER TABLE로 열을 삭제할 때는 변경하고자하는 테이블명(users) / 행위(DROP) / 열 이름(profile_image) 순서로 삭제하고 싶은 열을 지정할 수 있습니다.
mysql> ALTER TABLE users DROP profile_image;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc users;
+---------------+---------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+-------------------+-----------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| email | varchar(200) | NO | | NULL | |
| password | varchar(200) | NO | | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
| age | int | NO | | NULL | |
+---------------+---------------+------+-----+-------------------+-----------------------------+📍 열(column) 수정
ALTER TABLE로 열의 자료형을 변경할 경우에는 변경하고자하는 테이블명(posts) / 행위(MODIFY or CHANGE) / 열 이름(content) / 자료형(TEXT) / 제약 조건(NULL) 순서로 변경하고 싶은 열을 지정할 수 있습니다.
mysql> ALTER TABLE posts MODIFY content TEXT NULL;+------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+-------------------+-----------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| content | varchar -> text | YES | | NULL | |
| user_id | int | NO | MUL | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
+------------+------------------+------+-----+-------------------+-----------------------------+기존에 테이블 객체 내에 데이터(row)가 존재하는 경우, 자료형(Type) 변경에 따라 데이터의 자료형 역시도 변환된다. 다만 처리과정에서 에러가 발생하면 ALTER TABLE 명령이 적용되지 않을 수 있다. 예제에서는 테이블의 자료형 변경에 대한 내용만 다루었지만, MODIFY가 아닌 CHANGE 명령을 통해서도 열 이름도 변경할 수 있다. MySQL 공식문서를 참고하자.
✅ Table 삭제
필요 없는 테이블 객체는 DROP TABLE 명령으로 스키마(Schema)에서 지울 수 있다.
mysql> DROP TABLE posts;DROP TABLE 명령을 사용하는 경우에는 테이블 객체안에 포함된 모든 데이터도 함께 삭제된다. 그렇기 때문에 실수로 테이블을 삭제하지 않도록 신중하게 실행해야 하는 명령이다. 추가로, 테이블은 그대로 두고 데이터만 전부 삭제해야 되는 경우가 있을 수 있다. 예를 들어 테스트용 데이터베이스의 경우 매번 테스트 시작 전 테이블의 값 전부를 지워주는 작업이 필수로 필요하다. 이때는 DELETE 명령을 사용해 테이블 내의 데이터만 삭제할 수 있다.
# 테이블 내의 데이터만 삭제. row가 많을시 처리 속도 느릴 수 있음.
mysql> DELETE FROM posts WHERE condition;하지만 DELETE 명령은 행(row) 단위로 내부적으로 처리하기 때문에 삭제할 행이 많으면 처리 속다가 상당히 늦어진다. 이런 경우엔 TRUNCATE TABLE 명령 을 사용해서 모든 행을 삭제해 주는 것이 속도 측면에서 장점이 있습니다.
# 모든 행 데이터 삭제. 속도 측면에서 편리.
mysql> TRUNCATE TABLE posts;3. 정리
- 데이터베이스 객체(Database object)란 데이터베이스 내에 실체를 가지는 것을 말하며, 2차원 테이블(Table), 뷰(View), 인덱스(Index) 등이 있다.
- 스키마(Schema)는 데이터베이스 내의 객체(table, view, index)를 담는 컨테이너다.
- DDL(Data Define Language)는 Schema 객체를 정의하고 관리할 때 사용되는 쿼리문을 의미한다.
- MySQL에 원하는 데이터베이스를 생성하는 명령어는
CREATE DATABASE {database_name};다. - 생성된 데이터베이스에 접근하는 명령어는
USE {database_name}다. - 데이터베이스 내부에 테이블을 생성하는 명령어는
CREATE TABLE {table_name} (field, field, field, constraint);다. - 테이블의 구조를 변경하는 명령어는
ALTER TABLE {table_name} MODIFY {column} {data structure} {constraint};다. - 테이블 삭제 명령어는
DROP TABLE {table_name};이고, 테이블 객체 내의 모든 데이터(row)를 삭제하는 명령어는TRUNCATE TABLE {table_name};다.
