소개
이 랩에서는 MySQL 테이블 구조와 데이터 유형의 기본 사항을 살펴봅니다. 테이블을 생성, 수정 및 관리하는 방법을 이해하는 것은 데이터베이스를 사용하는 모든 사람에게 중요합니다. MySQL 의 다양한 데이터 유형, 필요에 맞는 적절한 데이터 유형을 선택하는 방법, 그리고 기본적인 테이블 작업을 수행하는 방법에 대해 배우게 됩니다. 이 랩을 마치면 테이블 생성, 적절한 데이터 유형으로 열 정의, 테이블 구조 수정에 대한 실질적인 경험을 얻게 될 것입니다.
목표
이 랩을 완료하면 다음을 수행할 수 있습니다.
- MySQL 의 핵심 데이터 유형을 이해하고 언제 사용해야 하는지 알 수 있습니다.
- 적절한 열 정의로 테이블을 생성할 수 있습니다.
- 기존 테이블 구조를 수정할 수 있습니다.
- 더 이상 필요하지 않은 테이블을 제거할 수 있습니다.
- 테이블 메타데이터를 보고 이해할 수 있습니다.
MySQL 데이터 유형 이해
이 단계에서는 가장 일반적으로 사용되는 MySQL 데이터 유형을 살펴봅니다. 데이터 유형을 이해하는 것은 매우 중요합니다. 열에 적합한 데이터 유형을 선택하는 것이 데이터 무결성과 데이터베이스 성능에 모두 영향을 미치기 때문입니다.
이 단계에는 많은 SQL 명령이 포함되어 있으므로 웹 터미널을 사용하는 것이 좋습니다. "Terminal" 탭을 클릭하여 엽니다. 데스크톱 터미널과 똑같이 작동합니다.
MySQL 에 연결하는 것부터 시작해 보겠습니다.
sudo mysql -u root
이제 연결되었으므로 실험을 위해 새 데이터베이스를 만들어 보겠습니다.
CREATE DATABASE store;
USE store;
MySQL 데이터 유형의 주요 범주를 살펴보겠습니다.
숫자 유형:
- INT: 정수용
- DECIMAL: 정확한 소수점 숫자용
- FLOAT/DOUBLE: 근사 소수점 숫자용
문자열 유형:
- VARCHAR: 가변 길이 문자열용
- CHAR: 고정 길이 문자열용
- TEXT: 긴 텍스트용
날짜 및 시간 유형:
- 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)
);
사용된 제약 조건을 살펴보겠습니다.
- PRIMARY KEY: 각 레코드의 고유한 식별을 보장합니다.
- UNIQUE: 열에 중복 값을 방지합니다.
- NOT NULL: 열에 NULL 값이 포함될 수 없도록 합니다.
- CHECK: 데이터를 삽입하기 전에 유효성을 검사합니다.
- FOREIGN KEY: 테이블 간의 참조 무결성을 보장합니다.
- 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 테이블을 수정해 보겠습니다.
- 새 열 추가:
ALTER TABLE products
ADD COLUMN manufacturer VARCHAR(100) AFTER name;
이 명령은 name 열 뒤에 manufacturer라는 새 열을 추가합니다.
- 기존 열 수정:
ALTER TABLE products
MODIFY COLUMN description VARCHAR(500) NOT NULL DEFAULT 'No description available';
이 명령은 description 열을 최대 길이가 500 자인 가변 길이 문자열로 변경합니다. 또한 새 행에 대해 'No description available'의 기본값을 설정합니다.
- 열 이름 변경:
ALTER TABLE products
CHANGE COLUMN weight product_weight DECIMAL(8,2);
이 명령은 weight 열의 이름을 product_weight로 바꾸고 데이터 유형을 총 8 자리와 소수점 2 자리가 있는 10 진수로 변경합니다.
- 열 삭제:
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';
테이블 삭제 및 정리
이 마지막 단계에서는 더 이상 필요하지 않은 테이블을 안전하게 제거하는 방법을 배웁니다. 이는 중요한 기술이지만, 테이블을 삭제하면 모든 데이터가 영구적으로 삭제되므로 주의해서 사용해야 합니다.
테이블을 삭제하기 전에 다음을 수행하는 것이 좋습니다.
- 올바른 데이터베이스에 있는지 확인
- 테이블이 존재하는지 확인
- 필요한 경우 백업이 있는지 확인
현재 데이터베이스와 테이블을 확인하는 것으로 시작해 보겠습니다.
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 테이블 및 데이터 유형 작업의 필수적인 측면을 다루었습니다.
- 다양한 MySQL 데이터 유형 이해 및 사용
- 적절한 제약 조건으로 테이블 생성
- ALTER TABLE 을 사용하여 테이블 구조 수정
- 테이블 메타데이터 및 정보 검색
- 더 이상 필요하지 않은 테이블을 안전하게 삭제
이러한 기술은 MySQL 데이터베이스를 효과적으로 작업하기 위한 기반을 형성합니다.
