MySQL 테이블 구조 및 데이터 유형

소개

이 랩에서는 MySQL 테이블 구조와 데이터 유형의 기본 사항을 살펴봅니다. 테이블을 생성, 수정 및 관리하는 방법을 이해하는 것은 데이터베이스를 사용하는 모든 사람에게 중요합니다. MySQL 의 다양한 데이터 유형, 필요에 맞는 적절한 데이터 유형을 선택하는 방법, 그리고 기본적인 테이블 작업을 수행하는 방법에 대해 배우게 됩니다. 이 랩을 마치면 테이블 생성, 적절한 데이터 유형으로 열 정의, 테이블 구조 수정에 대한 실질적인 경험을 얻게 될 것입니다.

목표

이 랩을 완료하면 다음을 수행할 수 있습니다.

• MySQL 의 핵심 데이터 유형을 이해하고 언제 사용해야 하는지 알 수 있습니다.
• 적절한 열 정의로 테이블을 생성할 수 있습니다.
• 기존 테이블 구조를 수정할 수 있습니다.
• 더 이상 필요하지 않은 테이블을 제거할 수 있습니다.
• 테이블 메타데이터를 보고 이해할 수 있습니다.

MySQL 데이터 유형 이해

이 단계에서는 가장 일반적으로 사용되는 MySQL 데이터 유형을 살펴봅니다. 데이터 유형을 이해하는 것은 매우 중요합니다. 열에 적합한 데이터 유형을 선택하는 것이 데이터 무결성과 데이터베이스 성능에 모두 영향을 미치기 때문입니다.

이 단계에는 많은 SQL 명령이 포함되어 있으므로 웹 터미널을 사용하는 것이 좋습니다. "Terminal" 탭을 클릭하여 엽니다. 데스크톱 터미널과 똑같이 작동합니다.

MySQL 에 연결하는 것부터 시작해 보겠습니다.

sudo mysql -u root

이제 연결되었으므로 실험을 위해 새 데이터베이스를 만들어 보겠습니다.

CREATE DATABASE store;
USE store;

MySQL 데이터 유형의 주요 범주를 살펴보겠습니다.

1. 숫자 유형:

   • INT: 정수용
   • DECIMAL: 정확한 소수점 숫자용
   • FLOAT/DOUBLE: 근사 소수점 숫자용

2. 문자열 유형:

   • VARCHAR: 가변 길이 문자열용
   • CHAR: 고정 길이 문자열용
   • TEXT: 긴 텍스트용

3. 날짜 및 시간 유형:

   • DATE: 날짜용 (YYYY-MM-DD)
   • TIME: 시간용 (HH:MM:SS)
   • DATETIME: 날짜와 시간 모두용

이러한 다양한 데이터 유형을 보여주는 간단한 테이블을 만들어 보겠습니다.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    weight FLOAT,
    in_stock BOOLEAN,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

이 테이블 구조를 자세히 살펴보겠습니다.

• id: 기본 키 역할을 하는 자동 증가 정수
• name: NULL 이 될 수 없는 가변 길이 문자열
• price: 총 10 자리와 소수점 2 자리가 있는 정확한 소수점 숫자
• description: 더 긴 설명을 위한 텍스트 필드
• weight: 근사 소수점 값에 대한 부동 소수점 숫자
• in_stock: 부울 필드 (TRUE/FALSE)
• created_at: 생성 타임스탬프를 자동으로 저장합니다.
• last_updated: 레코드가 변경될 때 자동으로 업데이트됩니다.

테이블 구조를 보려면 다음을 수행합니다.

DESCRIBE products;

다음과 같은 출력이 표시됩니다.

+--------------+---------------+------+-----+---------------------+-------------------------------+
| Field        | Type          | Null | Key | Default             | Extra                         |
+--------------+---------------+------+-----+---------------------+-------------------------------+
| id           | int(11)       | NO   | PRI | NULL                | auto_increment                |
| name         | varchar(100)  | NO   |     | NULL                |                               |
| price        | decimal(10,2) | NO   |     | NULL                |                               |
| description  | text          | YES  |     | NULL                |                               |
| weight       | float         | YES  |     | NULL                |                               |
| in_stock     | tinyint(1)    | YES  |     | NULL                |                               |
| created_at   | datetime      | YES  |     | current_timestamp() |                               |
| last_updated | timestamp     | NO   |     | current_timestamp() | on update current_timestamp() |
+--------------+---------------+------+-----+---------------------+-------------------------------+
8 rows in set (0.001 sec)

제약 조건으로 테이블 생성

이 단계에서는 테이블 제약 조건과 데이터 무결성을 유지하는 데 어떻게 도움이 되는지 배웁니다. 다양한 유형의 제약 조건을 보여주는 더 복잡한 테이블 구조를 만들 것입니다.

관계와 제약 조건을 이해하기 위해 두 개의 관련 테이블을 만들어 보겠습니다.

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200),
    active BOOLEAN DEFAULT TRUE
);

CREATE TABLE inventory_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    sku VARCHAR(20) NOT NULL UNIQUE,
    item_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL CHECK (quantity >= 0),
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

사용된 제약 조건을 살펴보겠습니다.

1. PRIMARY KEY: 각 레코드의 고유한 식별을 보장합니다.
2. UNIQUE: 열에 중복 값을 방지합니다.
3. NOT NULL: 열에 NULL 값이 포함될 수 없도록 합니다.
4. CHECK: 데이터를 삽입하기 전에 유효성을 검사합니다.
5. FOREIGN KEY: 테이블 간의 참조 무결성을 보장합니다.
6. DEFAULT: 지정된 값이 없는 경우 기본값을 제공합니다.

제약 조건이 있는 테이블 구조를 보려면 다음을 수행합니다.

SHOW CREATE TABLE inventory_items;

이렇게 하면 모든 제약 조건을 포함하는 전체 CREATE TABLE 문이 표시됩니다.

MariaDB [store]> SHOW CREATE TABLE inventory_items;

<!-- Sample output -->

CREATE TABLE `inventory_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `sku` varchar(20) NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `quantity` int(11) NOT NULL CHECK (`quantity` >= 0),
  `unit_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `inventory_items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

테이블 구조 변경

이 단계에서는 ALTER TABLE 명령을 사용하여 기존 테이블을 수정하는 방법을 배웁니다. 이는 데이터 요구 사항이 시간이 지남에 따라 변경될 때 일반적인 작업입니다.

새 열을 추가하고 기존 열을 수정하기 위해 products 테이블을 수정해 보겠습니다.

1. 새 열 추가:

ALTER TABLE products
ADD COLUMN manufacturer VARCHAR(100) AFTER name;

이 명령은 name 열 뒤에 manufacturer라는 새 열을 추가합니다.

2. 기존 열 수정:

ALTER TABLE products
MODIFY COLUMN description VARCHAR(500) NOT NULL DEFAULT 'No description available';

이 명령은 description 열을 최대 길이가 500 자인 가변 길이 문자열로 변경합니다. 또한 새 행에 대해 'No description available'의 기본값을 설정합니다.

3. 열 이름 변경:

ALTER TABLE products
CHANGE COLUMN weight product_weight DECIMAL(8,2);

이 명령은 weight 열의 이름을 product_weight로 바꾸고 데이터 유형을 총 8 자리와 소수점 2 자리가 있는 10 진수로 변경합니다.

4. 열 삭제:

ALTER TABLE products
DROP COLUMN in_stock;

이 명령은 테이블에서 in_stock 열을 제거합니다.

쿼리 성능을 향상시키기 위해 복합 인덱스를 추가해 보겠습니다.

ALTER TABLE products
ADD INDEX idx_name_manufacturer (name, manufacturer);

이 명령은 name 및 manufacturer 열에 복합 인덱스를 생성합니다.

변경 사항을 모두 보려면 다음을 수행합니다.

DESCRIBE products;
SHOW INDEX FROM products;

업데이트된 테이블 구조와 인덱스가 표시됩니다.

MariaDB [store]> DESCRIBE products;
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| Field          | Type          | Null | Key | Default                  | Extra                         |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| id             | int(11)       | NO   | PRI | NULL                     | auto_increment                |
| name           | varchar(100)  | NO   | MUL | NULL                     |                               |
| manufacturer   | varchar(100)  | YES  |     | NULL                     |                               |
| price          | decimal(10,2) | NO   |     | NULL                     |                               |
| description    | varchar(500)  | NO   |     | No description available |                               |
| product_weight | decimal(8,2)  | YES  |     | NULL                     |                               |
| created_at     | datetime      | YES  |     | current_timestamp()      |                               |
| last_updated   | timestamp     | NO   |     | current_timestamp()      | on update current_timestamp() |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
8 rows in set (0.001 sec)

MariaDB [store]> SHOW INDEX FROM products;
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| products |          0 | PRIMARY               |            1 | id           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            1 | name         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            2 | manufacturer | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.000 sec)

테이블 정보 작업

이 단계에서는 테이블 및 구조에 대한 정보를 검색하는 다양한 방법을 살펴봅니다.

먼저 테이블 상태 정보를 살펴보겠습니다.

SHOW TABLE STATUS FROM store;

이 명령은 데이터베이스의 각 테이블에 대한 정보를 표시하며, 다음을 포함합니다.

• 스토리지 엔진 (Storage engine)
• 행 형식 (Row format)
• 행 수 (Number of rows)
• 평균 행 길이 (Average row length)
• 데이터 길이 (Data length)
• 인덱스 길이 (Index length)

데이터베이스의 모든 테이블을 보려면 다음을 수행합니다.

SHOW TABLES;

특정 열에 대한 자세한 정보를 보려면 다음을 수행합니다.

SHOW FULL COLUMNS FROM products;

이는 다음을 포함하여 각 열에 대한 추가 정보를 제공합니다.

• 열 유형 (Column type)
• 정렬 (Collation)
• 권한 (Privileges)
• 주석 (Comments)

테이블의 모든 인덱스를 보려면 다음을 수행합니다.

SHOW INDEX FROM products;

INFORMATION_SCHEMA 데이터베이스에서 테이블에 대한 정보를 얻을 수도 있습니다.

SELECT
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    INDEX_LENGTH
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'store';

테이블 삭제 및 정리

이 마지막 단계에서는 더 이상 필요하지 않은 테이블을 안전하게 제거하는 방법을 배웁니다. 이는 중요한 기술이지만, 테이블을 삭제하면 모든 데이터가 영구적으로 삭제되므로 주의해서 사용해야 합니다.

테이블을 삭제하기 전에 다음을 수행하는 것이 좋습니다.

1. 올바른 데이터베이스에 있는지 확인
2. 테이블이 존재하는지 확인
3. 필요한 경우 백업이 있는지 확인

현재 데이터베이스와 테이블을 확인하는 것으로 시작해 보겠습니다.

SELECT DATABASE();
SHOW TABLES;

테이블을 안전하게 삭제하려면 IF EXISTS 절을 사용할 수 있습니다.

DROP TABLE IF EXISTS inventory_items;

외래 키 제약 조건이 있는 테이블을 먼저 삭제해야 합니다. categories 테이블을 먼저 삭제하려고 하면 inventory_items 가 이를 참조하기 때문에 오류가 발생합니다.

이제 나머지 테이블을 삭제할 수 있습니다.

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;

테이블이 삭제되었는지 확인하려면 다음을 수행합니다.

SHOW TABLES;

모든 테이블이 삭제되었음을 나타내는 빈 집합이 표시됩니다.

요약

이 랩에서는 MySQL 테이블 및 데이터 유형 작업의 필수적인 측면을 다루었습니다.

1. 다양한 MySQL 데이터 유형 이해 및 사용
2. 적절한 제약 조건으로 테이블 생성
3. ALTER TABLE 을 사용하여 테이블 구조 수정
4. 테이블 메타데이터 및 정보 검색
5. 더 이상 필요하지 않은 테이블을 안전하게 삭제

이러한 기술은 MySQL 데이터베이스를 효과적으로 작업하기 위한 기반을 형성합니다.