데이터 베이스 생성
mysql> create database if not exists ShopSphere;
Query OK, 1 row affected (0.04 sec)
mysql> show create database ShopSphere \G
*************************** 1. row ***************************
Database: ShopSphere
Create Database: CREATE DATABASE `ShopSphere` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| ShopSphere |
| sys |
+--------------------+
5 rows in set (0.01 sec)예제 코드 적용
git clone https://github.com/PacktPublishing/Database-Design-and-Modeling-with-PostgreSQL-and-MySQL.git
cd Database-Design-and-Modeling-with-PostgreSQL-and-MySQL/ch03/MySQL
mysql -u root -p ShopSphere < ShopSphere_schema.sqlmysql> show tables;
+-----------------------------------+
| Tables_in_shopsphere |
+-----------------------------------+
| customers |
| geolocation |
| order_items |
| order_payments |
| order_reviews |
| orders |
| product_category_name_translation |
| products |
| sellers |
+-----------------------------------+
9 rows in set (0.00 sec)Customers table (ShopSphere customers dataset)
mysql> show create table customers\G
*************************** 1. row ***************************
Table: customers
Create Table: CREATE TABLE `customers` (
`customer_id` varchar(45) NOT NULL,
`customer_unique_id` varchar(45) NOT NULL,
`customer_zip_code_prefix` int DEFAULT NULL,
`customer_city` varchar(25) DEFAULT NULL,
`customer_state` char(2) DEFAULT NULL,
PRIMARY KEY (`customer_id`),
UNIQUE KEY `customer_id_UNIQUE` (`customer_id`),
UNIQUE KEY `customer_unique_id_UNIQUE` (`customer_unique_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)mysql> SELECT * FROM ShopSphere.customers WHERE customer_city = "New York" LIMIT 1\G
*************************** 1. row ***************************
customer_id: 023b064e-1731-4725-b975-090c06e64e4d
customer_unique_id: 5a64c675-4728-4d7c-bf1b-9552c999aebc
customer_zip_code_prefix: 10028
customer_city: New York
customer_state: NY
1 row in set (0.00 sec)Orders table (ShopSphere orders table)
mysql> SELECT * FROM ShopSphere.orders LIMIT 1\G
*************************** 1. row ***************************
order_id: 009ceb00-a768-47da-9b05-ea9de50d3a9c
customer_id: f58ba72f-5397-4c2f-907a-65110523225c
order_status: shipped
order_purchase_timestamp: 2023-12-11 14:41:31
order_approved_at: 2023-12-12 06:41:31
order_delivered_carrier_date: 2023-12-13 06:41:31
order_delivered_customer_date: 2023-12-19 06:41:31
order_estimated_delivery_date: 2023-12-25 14:41:31
1 row in set (0.07 sec)
집계된 데이터를 생성하는 법
mysql> SELECT COUNT(*) FROM ShopSphere.orders WHERE order_status = 'delivered';
+----------+
| COUNT(*) |
+----------+
| 210 |
+----------+
1 row in set (0.02 sec)mysql> show create table orders \G
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`order_id` varchar(45) NOT NULL,
`customer_id` varchar(45) DEFAULT NULL,
`order_status` varchar(15) DEFAULT NULL,
`order_purchase_timestamp` datetime DEFAULT NULL,
`order_approved_at` datetime DEFAULT NULL,
`order_delivered_carrier_date` datetime DEFAULT NULL,
`order_delivered_customer_date` datetime DEFAULT NULL,
`order_estimated_delivery_date` datetime DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)Joining Tables
조인(Join)은 두 개 이상의 테이블에 있는 데이터를 하나로 합쳐서 조회하는 기능인데, 관계형 데이터베이스에서는 보통 데이터를 정규화를 통해 여러 테이블에 나눠 저장하기 때문에, 조인을 사용해서 관련 정보를 함께 볼 수 있어.
예를 들어:
orders테이블: 주문 정보만 있음 (주문 ID, 주문 날짜 등)order_items테이블: 각 주문에 어떤 상품이 포함되어 있는지 정보가 있음 (order_id,product_id, 등)
두 테이블을 order_id로 연결해서, 한 주문에 어떤 상품이 있는지 알 수 있음.
다만, 이 책에서는 조인의 종류에 대해 깊이 다루지는 않으므로, 다양한 방식의 조인 방법을 찾아보고 익히는 것이 좋다.
중요한 참고 사항
여기에서 설명하는 조인은 카티션 곱(Cartesian Product)이 발생할 수 있습니다. 이는 소스 테이블의 일치하는 행을 찾기 위해 대상 테이블의 모든 행을 읽는 것을 의미합니다.
카티션 곱이란?
카티션 곱(Cartesian Product)은 조인 조건 없이 두 테이블을 조인하면 생긴다.
예를 들어:
orders에 10개 행이 있고order_items에 50개 행이 있다면
10 x 50 = 500개의 결과가 나올 수 있다. 그래서 조인을 할 때는 조건을 정확히 지정하는 게 중요
예시로, orders 테이블과 order_items 테이블에서 order_id와 product_id를 선택하되, order_id는 10개 행으로 제한해 보겠습니다.
mysql> SELECT o.order_id, oi.product_id FROM ShopSphere.orders o JOIN
-> ShopSphere.order_items oi ON o.order_id = oi.order_id LIMIT 10;
+--------------------------------------+--------------------------------------+
| order_id | product_id |
+--------------------------------------+--------------------------------------+
| 0ff9db1c-c4ac-421b-afec-4da25ce21083 | 12f12498-606b-4cb6-a7d4-7afc439c802e |
| 37a60af4-2f38-4e59-ae44-148458f7a147 | 59843816-7ef2-4e14-8fcf-b7d29ca82054 |
| 4ab9a2c4-d3c2-453c-8eb6-f94f8c5f42a9 | 8ced67c7-4014-4357-9ee0-a25823f7a767 |
| 5ba3adbe-9175-4a86-b555-652a89471c9a | 77b282b0-8b2d-4255-bc77-1d0b8e2744b8 |
| 609b7a62-f124-4ae8-a116-d0258768f209 | 89ee2351-daf8-4af0-8741-0485247a9a9b |
| 6431449c-8272-403e-ad37-54732356859b | b74707db-bd98-4370-908a-ede26ba62772 |
| 768c478b-4f75-4fad-b097-033459ae01f0 | 0483d9d6-2238-4a1e-849c-6013b99fda1f |
| 8804e621-b70c-4f7f-87b1-c83eeff28671 | 5906ec35-dc5b-4db7-bac2-373a8a6696fc |
| a5608924-67a8-4320-b64b-46495afc2eb1 | 1d686d63-f9fe-4dc7-9d21-26ca5dd92a1c |
| afcc7ed6-bffc-4da8-a116-ba95e23019de | ae3bfbd3-861f-4be3-843f-b1537d624176 |
+--------------------------------------+--------------------------------------+
10 rows in set (0.07 sec)Count and group by
mysql> SELECT seller_city, COUNT(*) FROM ShopSphere.sellers GROUP BY seller_city LIMIT 5;
+-------------+----------+
| seller_city | COUNT(*) |
+-------------+----------+
| San Antonio | 103 |
| San Diego | 103 |
| Chicago | 99 |
| Houston | 114 |
| Los Angeles | 99 |
+-------------+----------+
5 rows in set (0.07 sec)Sorting data
mysql> SELECT * FROM ShopSphere.products ORDER BY product_weight_g DESC LIMIT 2 \G
*************************** 1. row ***************************
product_id: c54527a8-e6a6-4cbc-b6b8-ee3dc85d848a
product_category_name: 09sjVw25pRlBmvUVVYUNX7ki4s7HWCrXZztIwRMF8BmUD
product_name_lenght: 80
product_description_lenght: 201
product_photos_qty: 2
product_weight_g: 19995.000
product_length_cm: 39
product_height_cm: 107
product_width_cm: 167
*************************** 2. row ***************************
product_id: 051a010f-5a2f-475d-8366-a33081f5fe4b
product_category_name: 5e2l9DLNufgjpeN0c1pSUMilbDEF20uLshG4o02784myo
product_name_lenght: 94
product_description_lenght: 28
product_photos_qty: 2
product_weight_g: 19972.000
product_length_cm: 101
product_height_cm: 99
product_width_cm: 42
2 rows in set (0.02 sec)Complex queries
mysql> SELECT c.customer_id, COUNT(o.order_id) FROM ShopSphere.customers c JOIN
-> ShopSphere.orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id LIMIT 5;
+--------------------------------------+-------------------+
| customer_id | COUNT(o.order_id) |
+--------------------------------------+-------------------+
| f58ba72f-5397-4c2f-907a-65110523225c | 3 |
| 045a6755-1c51-494a-bd1e-56203fc92a3e | 1 |
| 65b0260b-3004-4b74-9f8b-a76c1179ab83 | 4 |
| 0850456f-0925-4a9d-a468-ee1523f056fb | 2 |
| 8280aa56-1151-45c7-82f8-1738986ce279 | 2 |
+--------------------------------------+-------------------+
5 rows in set (0.03 sec)mysql> SELECT customer_state, COUNT(*) AS number_of_customers FROM ShopSphere.customers
-> GROUP BY customer_state ORDER BY number_of_customers DESC LIMIT 10;
+----------------+---------------------+
| customer_state | number_of_customers |
+----------------+---------------------+
| TX | 310 |
| CA | 295 |
| NY | 122 |
| PA | 106 |
| AZ | 85 |
| IL | 82 |
+----------------+---------------------+
6 rows in set (0.01 sec)
추영욱입니다.