MySQL 다중 테이블 연산

소개

이 랩에서는 MySQL 에서 여러 테이블을 사용하는 기본 사항을 살펴봅니다. 테이블이 서로 어떻게 관련되어 있는지, 그리고 여러 테이블의 데이터를 결합하는 방법을 이해하는 것은 실제 애플리케이션을 구축하는 데 매우 중요합니다. 테이블 관계, 다양한 유형의 JOIN 연산, 그리고 외래 키 제약 조건을 사용하여 데이터 무결성을 유지하는 방법에 대해 배우게 됩니다. 이 랩을 마치면 관련 테이블을 생성하고, 테이블 간에 데이터를 쿼리하며, 테이블 간의 관계를 관리하는 실질적인 경험을 얻게 될 것입니다.

테이블 관계 이해

이 단계에서는 테이블 관계의 개념을 살펴보고 테이블이 어떻게 연결되어 있는지 검토합니다. 테이블 관계를 이해하는 것은 관계형 데이터베이스를 사용하는 데 기본입니다.

먼저, MySQL 에 연결해 보겠습니다.

sudo mysql -u root

연결되면 데이터베이스를 선택합니다.

USE bookstore;

테이블의 구조와 관계를 살펴보겠습니다.

SHOW CREATE TABLE books;

외래 키 제약 조건이 포함된 출력을 볼 수 있습니다.

| books | CREATE TABLE `books` (
  `book_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `author_id` int(11) DEFAULT NULL,
  `publisher_id` int(11) DEFAULT NULL,
  `publication_year` int(11) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`book_id`),
  KEY `author_id` (`author_id`),
  KEY `publisher_id` (`publisher_id`),
  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`),
  CONSTRAINT `books_ibfk_2` FOREIGN KEY (`publisher_id`) REFERENCES `publishers` (`publisher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

데이터베이스의 관계를 분석해 보겠습니다.

1. 각 책은 한 명의 저자를 갖습니다 (다대일 관계).

   • books 테이블의 author_id는 authors 테이블의 author_id를 참조합니다. 즉, books 테이블의 author_id는 authors 테이블의 author_id로 존재해야 합니다.
   • 여러 책이 동일한 저자를 가질 수 있습니다.

2. 각 책은 한 명의 출판사를 갖습니다 (다대일 관계).

   • books 테이블의 publisher_id는 publishers 테이블의 publisher_id를 참조합니다.
   • 여러 책이 동일한 출판사를 가질 수 있습니다.

이러한 관계가 실제로 어떻게 작동하는지 확인하기 위해, 각 저자가 쓴 책의 수를 확인하는 간단한 쿼리를 시도해 보겠습니다.

SELECT author_id, COUNT(*) as book_count
FROM books
GROUP BY author_id;

출력은 다음과 같습니다.

+-----------+------------+
| author_id | book_count |
+-----------+------------+
|         1 |          2 |
|         2 |          1 |
|         3 |          1 |
|         4 |          1 |
+-----------+------------+

이것은 author_id 1 을 가진 저자가 2 권의 책을 가지고 있고, 저자 2, 3, 4 가 각각 1 권의 책을 가지고 있음을 알려줍니다. 이것은 다대일 관계를 보여줍니다: 여러 책 레코드가 단일 저자 레코드를 가리킬 수 있습니다.

기본 INNER JOIN 연산

이 단계에서는 중요한 조인 연산 유형인 INNER JOIN에 대해 배우겠습니다. INNER JOIN은 조인되는 두 테이블 모두에서 일치하는 행만 반환합니다. 두 테이블의 교집합을 제공하는 벤 다이어그램 (Venn diagram) 이라고 생각하면 됩니다. 한 테이블의 레코드가 다른 테이블에서 해당 일치 항목이 없으면 결과에서 제외됩니다.

저자 이름과 함께 책 제목을 얻기 위해 간단한 INNER JOIN으로 시작해 보겠습니다.

SELECT
    books.title,
    authors.first_name,
    authors.last_name
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;

이 쿼리는 다음과 같습니다.

1. books 테이블로 시작합니다 (FROM books).
2. authors 테이블과 조인합니다 (INNER JOIN authors).
3. ON books.author_id = authors.author_id로 조인 조건을 지정합니다. 이것이 데이터베이스가 books의 어떤 행이 authors의 어떤 행과 일치하는지 아는 방법입니다. 즉, author_id 값이 두 테이블에서 동일한 레코드를 찾습니다.
4. 표시하려는 열을 선택합니다: books.title, authors.first_name, 및 authors.last_name.

다음과 같은 출력을 볼 수 있습니다.

+----------------------------+------------+-----------+
| title                      | first_name | last_name |
+----------------------------+------------+-----------+
| The MySQL Guide            | John       | Smith     |
| Data Design Patterns       | Emma       | Wilson    |
| Database Fundamentals      | Michael    | Brown     |
| SQL for Beginners          | Sarah      | Johnson   |
| Advanced Database Concepts | John       | Smith     |
+----------------------------+------------+-----------+

SELECT 문에서 books.title, authors.first_name, 및 authors.last_name을 사용하고 있음을 알 수 있습니다. 이것은 각 열이 어떤 테이블에서 왔는지 MySQL 에 명시적으로 알려줍니다. 열 이름이 테이블 간에 고유한 경우 테이블 접두사 (예: title 만 사용) 를 삭제할 수 있습니다. 그러나 모호성을 피하기 위해, 특히 여러 조인을 사용할 때는 항상 테이블 접두사를 지정하는 것이 좋습니다.

publishers 테이블과도 조인할 수 있습니다.

SELECT
    books.title,
    publishers.name as publisher_name,
    books.publication_year,
    books.price
FROM books
INNER JOIN publishers ON books.publisher_id = publishers.publisher_id;

이 경우, publishers.name as publisher_name은 publishers 테이블에서 name 열을 선택하고 출력에서 publisher_name으로 이름을 변경한다는 의미입니다. 이렇게 하면 열이 무엇을 나타내는지 더 명확하게 알 수 있습니다.

+----------------------------+--------------------+------------------+-------+
| title                      | publisher_name     | publication_year | price |
+----------------------------+--------------------+------------------+-------+
| The MySQL Guide            | Tech Books Pro     |             2023 | 45.99 |
| Data Design Patterns       | Tech Books Pro     |             2022 | 39.99 |
| Database Fundamentals      | Database Press     |             2021 | 29.99 |
| SQL for Beginners          | Database Press     |             2023 | 34.99 |
| Advanced Database Concepts | Query Publications |             2023 | 54.99 |
+----------------------------+--------------------+------------------+-------+

LEFT JOIN 연산

이 단계에서는 LEFT JOIN 연산을 살펴보겠습니다. LEFT JOIN (때로는 LEFT OUTER JOIN이라고도 함) 은 "왼쪽" 테이블 (FROM 절에 처음 언급된 테이블) 의 모든 레코드와 "오른쪽" 테이블의 일치하는 레코드를 반환합니다. INNER JOIN과의 주요 차이점은 오른쪽 테이블에 일치하는 항목이 없더라도 왼쪽 테이블의 레코드가 결과에 포함된다는 것입니다. 오른쪽 테이블에 일치하는 항목이 없으면 NULL 값이 사용됩니다.

아직 책을 출판하지 않은 저자를 추가해 보겠습니다.

INSERT INTO authors (first_name, last_name, email)
VALUES ('David', 'Clark', 'david.clark@email.com');

이제 LEFT JOIN을 사용하여 책을 출판하지 않은 저자를 포함한 모든 저자를 살펴보겠습니다.

SELECT
    authors.first_name,
    authors.last_name,
    COUNT(books.book_id) as book_count
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id
GROUP BY authors.author_id;

다음과 같은 출력을 볼 수 있습니다.

+------------+-----------+------------+
| first_name | last_name | book_count |
+------------+-----------+------------+
| John       | Smith     |          2 |
| Emma       | Wilson    |          1 |
| Michael    | Brown     |          1 |
| Sarah      | Johnson   |          1 |
| David      | Clark     |          0 |
+------------+-----------+------------+

David Clark 이 책을 출판하지 않았음에도 불구하고 결과에 book count 가 0으로 표시되는 것을 확인하세요. 이것은 LEFT JOIN이 authors 테이블의 모든 행을 포함하고 David 의 경우 books 테이블에 일치하는 책이 없기 때문에 COUNT(books.book_id)가 NULL이 아닌 0을 반환하기 때문입니다. 이것은 중요한 차이점을 보여줍니다: LEFT JOIN은 왼쪽 테이블의 모든 행이 결과에 나타나도록 보장하는 반면, INNER JOIN은 두 테이블 모두에서 일치하는 행만 포함합니다. 책이 있으면 카운트는 정수가 되고, 그렇지 않으면 COUNT() 때문에 0 이 됩니다.

다중 테이블 조인

이 단계에서는 두 개 이상의 테이블을 함께 조인하는 방법을 배우겠습니다. 이는 여러 관련 테이블의 데이터를 결합하여 데이터에 대한 보다 전체적인 관점을 얻어야 할 때 일반적으로 필요합니다.

세 테이블의 정보를 모두 결합하는 쿼리를 만들어 보겠습니다.

SELECT
    b.title,
    CONCAT(a.first_name, ' ', a.last_name) as author_name,
    p.name as publisher_name,
    b.publication_year,
    b.price
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id
INNER JOIN publishers p ON b.publisher_id = p.publisher_id
ORDER BY b.title;

이 쿼리가 수행하는 작업을 자세히 살펴보겠습니다.

1. FROM books b: 이는 books 테이블에서 시작하고 별칭 b를 지정함을 나타냅니다. 별칭 (b, a, p) 을 사용하는 것은 쿼리를 더 짧고 읽기 쉽게 만드는 좋은 방법입니다.
2. INNER JOIN authors a ON b.author_id = a.author_id: 이는 author_id를 기반으로 books 테이블을 authors 테이블과 조인합니다. 두 테이블 모두에서 일치하는 author_id가 있는 경우에만 행이 반환됩니다. a는 authors 테이블의 별칭입니다.
3. INNER JOIN publishers p ON b.publisher_id = p.publisher_id: 이는 이전 조인의 결과를 publisher_id를 기반으로 publishers 테이블과 조인합니다. 두 테이블 모두에서 publisher_id가 일치하는 경우에만 행이 반환됩니다. p는 publishers 테이블의 별칭입니다.
4. SELECT b.title, CONCAT(a.first_name, ' ', a.last_name) as author_name, p.name as publisher_name, b.publication_year, b.price: books 테이블에서 제목을 선택하고, CONCAT을 사용하여 저자의 이름과 성을 author_name 열로 결합하고, 출판사 이름 열을 사용하고 publisher_name으로 이름을 바꾸고, 해당 테이블에서 출판 연도와 가격을 선택합니다.
5. ORDER BY b.title: 이는 책 제목을 오름차순으로 출력합니다.

+----------------------------+---------------+--------------------+------------------+-------+
| title                      | author_name   | publisher_name     | publication_year | price |
+----------------------------+---------------+--------------------+------------------+-------+
| Advanced Database Concepts | John Smith    | Query Publications |             2023 | 54.99 |
| Data Design Patterns       | Emma Wilson   | Tech Books Pro     |             2022 | 39.99 |
| Database Fundamentals      | Michael Brown | Database Press     |             2021 | 29.99 |
| SQL for Beginners          | Sarah Johnson | Database Press     |             2023 | 34.99 |
| The MySQL Guide            | John Smith    | Tech Books Pro     |             2023 | 45.99 |
+----------------------------+---------------+--------------------+------------------+-------+

이 쿼리는 서로 다른 테이블의 데이터를 결합하여 보다 완전한 그림을 얻는 방법을 보여줍니다. 각 책 항목은 저자 및 출판사 정보와 연결됩니다.

책이 없는 저자를 포함하여 책과 저자를 표시하고, 사용 가능한 경우 출판사 정보를 표시하는 또 다른 예를 시도해 보겠습니다.

SELECT
    CONCAT(a.first_name, ' ', a.last_name) as author_name,
    b.title,
    p.name as publisher_name
FROM authors a
LEFT JOIN books b ON a.author_id = b.author_id
LEFT JOIN publishers p ON b.publisher_id = p.publisher_id
ORDER BY author_name;

이 예에서는 LEFT JOIN을 사용합니다. 이렇게 하면 책을 출판했는지 여부에 관계없이 모든 저자가 표시됩니다. 책이 없는 저자의 출력은 제목과 출판사 이름에 대해 NULL을 표시합니다.

+---------------+----------------------------+--------------------+
| author_name   | title                      | publisher_name     |
+---------------+----------------------------+--------------------+
| David Clark   | NULL                       | NULL               |
| Emma Wilson   | Data Design Patterns       | Tech Books Pro     |
| John Smith    | The MySQL Guide            | Tech Books Pro     |
| John Smith    | Advanced Database Concepts | Query Publications |
| Michael Brown | Database Fundamentals      | Database Press     |
| Sarah Johnson | SQL for Beginners          | Database Press     |
+---------------+----------------------------+--------------------+

외래 키 제약 조건 사용

이 마지막 단계에서는 외래 키 제약 조건이 관련 테이블 간의 데이터 무결성을 유지하는 데 어떻게 도움이 되는지 살펴보겠습니다. 외래 키 제약 조건은 고아 레코드 또는 일관성 없는 데이터를 생성하는 작업을 방지하여 테이블 간의 관계가 유효하도록 보장합니다.

몇 가지 예를 통해 외래 키 제약 조건이 어떻게 작동하는지 이해해 보겠습니다.

먼저, 유효하지 않은 author_id로 책을 추가해 보겠습니다.

-- This will fail due to foreign key constraint
INSERT INTO books (title, author_id, publisher_id, publication_year, price)
VALUES ('Failed Book', 999, 1, 2023, 29.99);

author_id 999 가 authors 테이블에 존재하지 않기 때문에 오류 메시지가 표시됩니다.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookstore`.`books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`))

이 오류 메시지는 books 테이블의 author_id 열에 대한 외래 키 제약 조건이 authors 테이블에 존재하지 않는 author_id를 참조하는 책을 추가하는 것을 방지하고 있음을 나타냅니다.

마찬가지로, 책을 출판한 저자를 먼저 처리하지 않고는 삭제할 수 없습니다.

-- This will fail due to foreign key constraint
DELETE FROM authors WHERE author_id = 1;

author_id = 1인 저자를 직접 삭제하려고 하면 해당 author_id를 참조하는 책이 있기 때문에 외래 키 오류가 발생합니다. 이것은 데이터가 일관성을 유지하도록 보장하는 MySQL 의 방식입니다.

저자와 해당 책을 안전하게 삭제하려면 다음 중 하나를 수행해야 합니다.

1. 먼저 책을 삭제한 다음 저자를 삭제합니다. 이렇게 하면 고아 레코드가 없는지 확인합니다.
2. CASCADE DELETE를 사용합니다 (고급 랩에서 살펴보겠습니다).

먼저 책을 삭제하여 책과 저자를 올바르게 제거하는 방법을 살펴보겠습니다.

-- First, delete the books by this author
DELETE FROM books WHERE author_id = 1;

-- Now we can safely delete the author
DELETE FROM authors WHERE author_id = 1;

먼저 책을 삭제하면 외래 키 참조가 제거되어 제약 조건을 위반하지 않고 저자를 삭제할 수 있습니다.

요약

이 랩에서는 MySQL 에서 여러 테이블을 사용하는 데 필요한 필수적인 측면을 다루었습니다.

1. 기본 키와 외래 키를 사용하여 테이블 관계를 이해하고 구현하는 방법.
2. INNER JOIN을 사용하여 여러 테이블에서 일치하는 레코드를 결합하고, 접두사 및 별칭을 사용하여 관련 열을 선택하는 방법을 보여줍니다.
3. LEFT JOIN을 사용하여 다른 테이블에 일치하는 레코드가 없는 레코드를 포함하여 한 테이블의 모든 레코드를 포함하고, NULL 값을 해석하는 방법을 이해합니다.
4. 여러 조인을 사용하여 여러 테이블의 데이터를 결합하여 세 개의 테이블에서 한 번에 데이터를 가져오는 방법을 보여줍니다.
5. 외래 키 제약 조건을 사용하여 데이터 무결성을 유지하고, 고아 레코드를 방지하며, MySQL 이 테이블 간의 관계를 관리하는 방법을 보여줍니다.

이러한 기술은 관계형 데이터베이스를 효과적으로 사용하는 기반을 형성합니다. 여러 테이블에서 데이터를 결합하고 관련시키는 방법을 이해하는 것은 강력한 데이터베이스 애플리케이션을 구축하는 데 매우 중요합니다.