이 랩에서는 PostgreSQL 에서 관계와 조인을 탐구합니다. 데이터 무결성을 보장하기 위해 외래 키 제약 조건을 사용하여 테이블을 생성하는 방법을 배우게 됩니다.
먼저 customers와 orders 두 개의 테이블을 생성하고, 이들 간에 외래 키 관계를 설정하는 것으로 시작합니다. 그런 다음, 정의된 관계를 준수하도록 데이터를 삽입합니다. 마지막으로, INNER JOIN을 사용하여 데이터를 검색하는 방법을 배우고, LEFT, RIGHT, 그리고 FULL OUTER JOIN 연산의 결과를 비교하여 서로 다른 데이터 관계를 어떻게 처리하는지 이해하게 됩니다.
이 단계에서는 customers와 orders 두 개의 테이블을 생성하고, 이들 간에 외래 키 제약 조건을 설정합니다. 이 제약 조건은 테이블 간의 관계가 유지되도록 하여 잘못된 데이터가 입력되는 것을 방지합니다.
외래 키 이해
외래 키는 다른 테이블의 기본 키를 참조하는 한 테이블의 열입니다. 두 테이블 간의 링크를 설정합니다. 외래 키를 포함하는 테이블을 "자식" 테이블이라고 하고, 기본 키를 포함하는 테이블을 "부모" 테이블이라고 합니다.
단계 1: PostgreSQL 에 연결
LabEx VM 에서 터미널을 엽니다. psql 명령을 사용하여 PostgreSQL 데이터베이스에 연결합니다.
sudo -u postgres psql이제 PostgreSQL 프롬프트 (postgres=#) 가 표시됩니다.
단계 2: customers 테이블 생성
다음 열을 사용하여 customers 테이블을 생성합니다.
customer_id: 각 고객에 대한 고유 식별자 (기본 키).first_name: 고객의 이름.last_name: 고객의 성.email: 고객의 이메일 주소 (고유해야 함).psql 셸에서 다음 SQL 명령을 실행합니다.
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE
);이 명령은 customers 테이블을 생성합니다. SERIAL 키워드는 customer_id에 대해 자동으로 일련의 숫자를 생성하여 자동 증가하게 합니다. PRIMARY KEY는 customer_id를 기본 키로 지정합니다. NOT NULL은 first_name 및 last_name 열이 비어 있을 수 없도록 하고, UNIQUE는 각 이메일 주소가 고유하도록 합니다.
단계 3: 외래 키가 있는 orders 테이블 생성
다음 열을 사용하여 orders 테이블을 생성합니다.
order_id: 각 주문에 대한 고유 식별자 (기본 키).customer_id: 주문을 한 고객의 ID ( customers를 참조하는 외래 키).order_date: 주문 날짜.total_amount: 주문 총액.psql 셸에서 다음 SQL 명령을 실행합니다.
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
order_date DATE NOT NULL,
total_amount NUMERIC(10, 2) NOT NULL
);이 명령은 orders 테이블을 생성합니다. customer_id 열은 REFERENCES 키워드를 사용하여 외래 키로 정의됩니다. REFERENCES customers(customer_id)는 orders 테이블의 customer_id 열이 customers 테이블의 customer_id 열을 참조하도록 지정합니다. 이것은 외래 키 관계를 설정합니다.
단계 4: 테이블 생성 확인
데이터베이스의 테이블을 나열하여 테이블이 성공적으로 생성되었는지 확인합니다. psql 셸에서 다음 명령을 실행합니다.
\dtcustomers 및 orders 테이블이 나열되어야 합니다.
단계 5: 테이블 설명
테이블의 구조를 보려면 테이블 이름 뒤에 \d 명령을 사용합니다. 예를 들어, customers 테이블을 설명하려면 다음을 실행합니다.
\d customers이렇게 하면 customers 테이블에 대해 정의된 열, 데이터 유형 및 제약 조건이 표시됩니다. 마찬가지로, orders 테이블을 설명할 수 있습니다.
\d orders이렇게 하면 customer_id 열에 대한 외래 키 제약 조건이 표시됩니다.
이제 외래 키 제약 조건이 있는 두 개의 테이블을 성공적으로 생성했습니다.
이 단계에서는 customers 및 orders 테이블에 데이터를 삽입하여 참조 무결성이 유지되도록 합니다. 즉, customers 테이블에 존재하지 않는 고객에 대한 주문을 추가할 수 없습니다.
참조 무결성 이해
참조 무결성은 테이블 간의 관계가 일관성을 유지하도록 합니다. 이 경우, orders 테이블의 customer_id가 customers 테이블에 존재해야 함을 의미합니다.
단계 1: customers 테이블에 데이터 삽입
psql 셸에서 다음 SQL 명령을 사용하여 customers 테이블에 데이터를 삽입합니다.
INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com'),
('David', 'Lee', 'david.lee@example.com');이 명령은 customers 테이블에 세 명의 고객을 추가합니다. customer_id는 자동으로 생성됩니다.
단계 2: customers 테이블에 데이터 삽입 확인
customers 테이블을 쿼리하여 데이터가 올바르게 삽입되었는지 확인합니다.
SELECT * FROM customers;방금 삽입한 세 명의 고객과 자동으로 생성된 customer_id 값을 볼 수 있습니다. 출력은 다음과 유사해야 합니다.
customer_id | first_name | last_name | email
-------------+------------+-----------+------------------------
1 | John | Doe | john.doe@example.com
2 | Jane | Smith | jane.smith@example.com
3 | David | Lee | david.lee@example.com
(3 rows)단계 3: orders 테이블에 데이터 삽입
customers 테이블의 customer_id 값을 참조하여 orders 테이블에 데이터를 삽입합니다.
INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-11-01', 100.00),
(2, '2023-11-05', 250.50),
(1, '2023-11-10', 75.25),
(3, '2023-11-15', 120.00);이 명령은 orders 테이블에 네 개의 주문을 추가합니다. 각 주문은 customers 테이블의 customer_id와 연결됩니다.
단계 4: orders 테이블에 데이터 삽입 확인
orders 테이블을 쿼리하여 데이터가 올바르게 삽입되었는지 확인합니다.
SELECT * FROM orders;방금 삽입한 네 개의 주문을 볼 수 있습니다. 출력은 다음과 유사해야 합니다.
order_id | customer_id | order_date | total_amount
----------+-------------+------------+--------------
1 | 1 | 2023-11-01 | 100.00
2 | 2 | 2023-11-05 | 250.50
3 | 1 | 2023-11-10 | 75.25
4 | 3 | 2023-11-15 | 120.00
(4 rows)단계 5: 잘못된 데이터 삽입 시도 (참조 무결성 시연)
참조 무결성 제약 조건을 시연하기 위해 customers 테이블에 존재하지 않는 customer_id를 사용하여 주문을 삽입해 봅니다.
INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(4, '2023-11-20', 50.00);다음과 유사한 오류 메시지가 표시되어야 합니다.
ERROR: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL: Key (customer_id)=(4) is not present in table "customers".이 오류 메시지는 외래 키 제약 조건이 작동하고 있음을 확인합니다. 데이터베이스는 customer_id 4 가 customers 테이블에 존재하지 않기 때문에 주문 삽입을 방지합니다.
이제 참조 무결성을 보장하면서 customers 및 orders 테이블에 데이터를 성공적으로 삽입했습니다.
이 단계에서는 PostgreSQL 에서 INNER JOIN 절을 사용하여 여러 테이블에서 데이터를 검색하는 방법을 배웁니다. INNER JOIN은 관련 열을 기반으로 두 개 이상의 테이블에서 행을 결합합니다.
INNER JOIN 이해
INNER JOIN은 조인되는 두 테이블 모두에서 일치하는 행만 반환합니다. 일치하는 항목이 없으면 해당 행은 결과에서 제외됩니다.
단계 1: PostgreSQL 에 연결
psql 명령을 사용하여 PostgreSQL 데이터베이스에 연결되어 있는지 확인합니다.
sudo -u postgres psql단계 2: INNER JOIN 쿼리 실행
psql 셸에서 다음 SQL 쿼리를 실행합니다.
SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;이 쿼리를 분석해 보겠습니다.
SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount: orders 및 customers 테이블에서 검색하려는 열을 지정합니다. 테이블 이름을 접두사로 사용하면 (예: orders.order_id) 각 열이 어떤 테이블에서 왔는지 명확하게 알 수 있습니다.FROM orders: 쿼리하려는 첫 번째 테이블을 지정합니다.INNER JOIN customers ON orders.customer_id = customers.customer_id: 조인할 두 번째 테이블 (customers) 과 조인 조건 (orders.customer_id = customers.customer_id) 을 지정합니다. ON 절은 행이 포함되려면 orders 테이블의 customer_id가 customers 테이블의 customer_id와 일치해야 함을 지정합니다.단계 3: 결과 분석
쿼리는 각 주문에 대한 주문 ID, 고객의 이름, 주문 날짜 및 총액을 포함하는 결과 집합을 반환합니다. 출력은 다음과 유사해야 합니다.
order_id | first_name | order_date | total_amount
----------+------------+------------+--------------
1 | John | 2023-11-01 | 100.00
3 | John | 2023-11-10 | 75.25
2 | Jane | 2023-11-05 | 250.50
4 | David | 2023-11-15 | 120.00
(4 rows)쿼리는 customer_id를 기반으로 orders 및 customers 테이블을 성공적으로 조인하고 요청된 정보를 검색했습니다. customers 테이블에 해당하는 고객이 있는 주문만 포함됩니다.
단계 4: 별칭 사용 (선택 사항)
더 복잡한 쿼리의 경우 별칭을 사용하여 쿼리의 가독성을 높일 수 있습니다. 이전 쿼리는 별칭을 사용하여 다시 작성할 수 있습니다.
SELECT o.order_id, c.first_name, o.order_date, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;이 쿼리에서 o는 orders의 별칭이고 c는 customers의 별칭입니다. 결과는 동일하지만 쿼리가 더 간결합니다.
이제 INNER JOIN 절을 사용하여 여러 테이블에서 데이터를 성공적으로 쿼리했습니다.
이 단계에서는 PostgreSQL 에서 LEFT OUTER JOIN, RIGHT OUTER JOIN 및 FULL OUTER JOIN의 결과를 살펴보고 비교합니다. 이러한 조인은 다른 테이블에 일치하는 값이 없더라도 하나 이상의 테이블에서 모든 행을 검색합니다.
OUTER JOIN 이해
NULL 값이 반환됩니다.NULL 값이 반환됩니다.NULL 값이 반환됩니다.단계 1: PostgreSQL 에 연결
psql 명령을 사용하여 PostgreSQL 데이터베이스에 연결되어 있는지 확인합니다.
sudo -u postgres psql단계 2: 주문이 없는 새 고객 삽입
주문을 하지 않은 새 고객을 customers 테이블에 삽입합니다.
INSERT INTO customers (first_name, last_name, email) VALUES
('Alice', 'Brown', 'alice.brown@example.com');단계 3: 새 고객 확인
새 고객이 customers 테이블에 추가되었는지 확인합니다.
SELECT * FROM customers;결과에 Alice Brown 이 새 customer_id (아마도 4) 와 함께 표시되는 것을 볼 수 있습니다.
단계 4: LEFT OUTER JOIN 수행
다음 SQL 쿼리를 실행하여 customers 및 orders 테이블 간에 LEFT OUTER JOIN을 수행합니다.
SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id;이 쿼리는 모든 고객과 고객이 주문한 모든 주문을 반환합니다. 고객이 주문을 하지 않은 경우 order_id 및 order_date 열에는 NULL 값이 포함됩니다. 출력은 다음과 유사해야 합니다.
first_name | order_id | order_date
------------+----------+------------
John | 1 | 2023-11-01
John | 3 | 2023-11-10
Jane | 2 | 2023-11-05
David | 4 | 2023-11-15
Alice | |
(5 rows)Alice Brown 은 주문을 하지 않았음에도 포함되어 있음을 확인하십시오. order_id 및 order_date 열은 그녀에게 NULL입니다.
단계 5: RIGHT OUTER JOIN 수행
다음 SQL 쿼리를 실행하여 customers 및 orders 테이블 간에 RIGHT OUTER JOIN을 수행합니다.
SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
RIGHT OUTER JOIN orders ON customers.customer_id = orders.customer_id;이 쿼리는 모든 주문과 각 주문을 한 고객의 이름을 반환합니다. 모든 주문에 해당하는 고객이 있으므로 이 경우 결과는 INNER JOIN과 동일합니다. 출력은 다음과 유사해야 합니다.
first_name | order_id | order_date
------------+----------+------------
John | 1 | 2023-11-01
Jane | 2 | 2023-11-05
John | 3 | 2023-11-10
David | 4 | 2023-11-15
(4 rows)단계 6: FULL OUTER JOIN 수행
다음 SQL 쿼리를 실행하여 customers 및 orders 테이블 간에 FULL OUTER JOIN을 수행합니다.
SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;이 쿼리는 모든 고객과 모든 주문을 반환합니다. 고객이 주문을 하지 않은 경우 order_id 및 order_date 열에는 NULL 값이 포함됩니다. 주문에 해당하는 고객이 없는 경우 (외래 키 제약 조건으로 인해 현재 설정에서는 불가능함) first_name 열에 NULL 값이 포함됩니다. 출력은 다음과 유사해야 합니다.
first_name | order_id | order_date
------------+----------+------------
John | 1 | 2023-11-01
John | 3 | 2023-11-10
Jane | 2 | 2023-11-05
David | 4 | 2023-11-15
Alice | |
(5 rows)Alice Brown 이 order_id 및 order_date에 대해 NULL 값으로 포함되어 있음을 확인하십시오.
단계 7: 차이점 이해
LEFT OUTER JOIN은 일치하는 주문이 없더라도 customers 테이블의 모든 행을 포함합니다.RIGHT OUTER JOIN은 orders 테이블의 모든 행을 포함합니다. 이 경우 모든 주문에 해당하는 고객이 있으므로 INNER JOIN처럼 동작합니다.FULL OUTER JOIN은 두 테이블의 모든 행을 포함합니다.이제 PostgreSQL 에서 LEFT OUTER JOIN, RIGHT OUTER JOIN 및 FULL OUTER JOIN의 결과를 살펴보고 비교했습니다.
이 랩에서는 데이터 무결성을 유지하기 위해 PostgreSQL 에서 외래 키 제약 조건으로 테이블을 만드는 방법을 배웠습니다. customers 및 orders 테이블을 만들고 외래 키를 사용하여 테이블 간의 관계를 설정했습니다. 그런 다음 이러한 테이블에 데이터를 삽입하여 외래 키 제약 조건이 적용되도록 했습니다. 마지막으로, 관련 테이블에서 데이터를 검색하고 다양한 데이터 관계를 처리하는 방식을 이해하기 위해 다양한 유형의 JOIN 연산 (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, 및 FULL OUTER JOIN) 을 탐색했습니다.
