MySQL 提供強大的功能來建立和管理資料函式庫。本文以 ShopSphere 資料函式庫為例，示範如何使用 MySQL CLI 建立資料表、載入資料，並執行各種 SQL 查詢進行資料分析。首先，透過 show create database 指令確認資料函式庫設定，接著使用 SQL 指令碼建立 ShopSphere 資料函式庫的八個資料表，並利用 Python 指令碼載入範例資料。客戶資料表包含客戶 ID、唯一 ID、郵遞區號、城市和州等重要欄位，訂單資料表則記錄了訂單 ID、客戶 ID、訂單狀態和日期等資訊。文章也示範瞭如何使用 SQL 查詢特定城市的客戶、選取訂單資料、計算已交付訂單數量等操作。此外，文章還介紹瞭如何使用聚合函式統計資料、連線多個資料表進行關聯查詢、排序資料以及執行更複雜的查詢，例如統計每個客戶的訂單數量和每個州的客戶數量等。透過這些實務操作範例，讀者可以更深入地理解 MySQL 資料函式庫的應用和 SQL 查詢技巧。
資料函式庫建立與管理
在 MySQL 中建立資料函式庫是資料分析與管理的第一步。以下將使用 MySQL 的命令列介面（CLI）來建立範例資料函式庫 ShopSphere。
建立與檢查資料函式庫
首先，檢查 ShopSphere 資料函式庫是否已建立：
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)
這個指令顯示了 ShopSphere 資料函式庫的詳細語法，包括字元集、校對規則和加密狀態。
建立資料表
執行以下指令來建立所有資料表：
mysql -u yourusername -p ShopSphere < /path/to/ShopSphere_schema.sql
客戶資料表（ShopSphere_customers_dataset）
客戶資料表包含以下重要欄位：
customer_id：訂單資料集的主鍵，每個訂單都有一個獨特的customer_id值。customer_unique_id：客戶的唯一識別碼，用於識別重複購買的客戶。customer_zip_code_prefix：客戶郵遞區號的前五位數字。customer_city：客戶所在的城市名稱。customer_state：客戶所在的州或省份。
客戶資料表結構
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.00 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)
訂單資料表（ShopSphere_orders_table）
訂單資料表包含以下重要欄位：
order_id：訂單資料表的主鍵，也是 ShopSphere 資料函式庫的核心。customer_id：與客戶資料表相關聯的外部索引鍵，用於識別訂單所屬的客戶。
範例查詢：選取一筆訂單資料
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.00 sec)
資料聚合
聚合技術用於匯總資料，例如計算數量、平均值等。以下範例用於計算已交付的訂單數量：
mysql> SELECT COUNT(*) FROM ShopSphere.orders WHERE order_status = 'delivered';
+
---
-
---
---
+
| COUNT(*) |
+
---
-
---
---
+
| 203 |
+
---
-
---
---
+
1 row in set (0.05 sec)
訂單資料表結構
mysql> show create table orders \G
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`order_id` varchar(45) NOT NULL,
# 其他欄位定義...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
程式碼解密：
此段程式碼展示瞭如何使用 MySQL 的聚合函式來計算特定條件下的訂單數量。首先，我們使用 SELECT COUNT(*) 陳述式來計算符合條件的列數。在這個例子中，條件是 order_status = 'delivered'，即訂單狀態為已交付。這個查詢幫助我們瞭解有多少訂單已經成功交付給客戶。
這個查詢的邏輯是：
- 使用
SELECT COUNT(*)從ShopSphere.orders資料表中選取符合條件的列數。 - 使用
WHERE order_status = 'delivered'子句篩選出訂單狀態為已交付的訂單。 - 傳回計算結果，即已交付的訂單數量。
這種聚合查詢在實際應用中非常有用，可以幫助業務人員或分析師快速瞭解業務狀況或進行資料分析。
資料函式庫實作：PostgreSQL 與 MySQL 的探索
在探討資料函式庫設計與建模的過程中，實際操作是不可或缺的一環。本章節將重點介紹如何使用 MySQL 進行資料函式庫的操作，包括建立表格、連線多個表格、統計與分組資料、排序資料以及執行複雜的查詢。
建立表格與理解資料函式庫結構
首先，我們來觀察 orders 表格的結構。這個表格是整個資料函式庫的核心，與其他多個表格（如 payments、products、items、customers 和 reviews）都有關聯。
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;
內容解密：
order_id是主鍵，用於唯一標識每一筆訂單。customer_id與客戶資訊相關聯。- 各種日期欄位記錄了訂單的處理進度。
接下來，我們檢視 sellers 表格的結構。
CREATE TABLE `sellers` (
`seller_id` varchar(45) NOT NULL,
`seller_zip_code_prefix` decimal(5,0) DEFAULT NULL,
`seller_city` varchar(45) DEFAULT NULL,
`seller_state` char(2) DEFAULT NULL,
PRIMARY KEY (`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
內容解密：
seller_id是主鍵，用於唯一標識每一位賣家。seller_zip_code_prefix、seller_city和seller_state提供了賣家的地理位置資訊。
連線表格
在關聯式資料函式庫中，連線多個表格是常見的操作。以下是一個例子，展示如何從 orders 和 order_items 表格中選取資料。
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;
內容解密：
- 使用
JOIN將orders和order_items表格連線起來，條件是兩者的order_id相同。 - 結果顯示了訂單ID和對應的產品ID。
統計與分組資料
統計與分組是資料分析的重要部分。以下查詢統計了不同城市的賣家數量。
SELECT seller_city, COUNT(*)
FROM ShopSphere.sellers
GROUP BY seller_city
LIMIT 5;
內容解密：
- 使用
GROUP BY將賣家按照城市分組。 COUNT(*)統計了每個城市的賣家數量。
排序資料
排序可以幫助我們更好地理解資料。以下查詢按照產品重量降序排列產品資訊。
SELECT *
FROM ShopSphere.products
ORDER BY product_weight_g DESC
LIMIT 2 \G;
內容解密：
- 使用
ORDER BY將產品按照重量降序排列。 \G用於格式化輸出，使結果更易讀。
複雜查詢
複雜查詢結合了多種操作，如連線、過濾和聚合。以下查詢統計了每個客戶的訂單數量。
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;
內容解密：
- 連線
customers和orders表格，統計每個客戶的訂單數量。 - 使用
GROUP BY對客戶進行分組。
另一個例子是統計每個州的客戶數量，並按數量降序排列。
SELECT customer_state, COUNT(*) AS number_of_customers
FROM ShopSphere.customers
GROUP BY customer_state
ORDER BY number_of_customers DESC;
內容解密：
- 使用
GROUP BY對客戶按照州進行分組。 COUNT(*)統計了每個州的客戶數量，並使用ORDER BY按數量降序排列結果。
資料函式庫查詢實戰：PostgreSQL 與 MySQL
在進行資料分析與預處理的過程中，掌握 SQL 查詢技巧至關重要。以下將透過 ShopSphere 資料函式庫中的範例，展示如何使用 MySQL 進行資料查詢和分析。
客戶資料分析
首先，我們來分析客戶資料的分佈情況。透過以下查詢，我們可以瞭解各州客戶的分佈數量：
SELECT
customer_state,
COUNT(*) AS number_of_customers
FROM
ShopSphere.customers
GROUP BY
customer_state
ORDER BY
number_of_customers DESC
LIMIT 10;
內容解密：
SELECT customer_state, COUNT(*) AS number_of_customers：選擇客戶州別並計算每個州的客戶數量。FROM ShopSphere.customers：指定資料來源為customers表。GROUP BY customer_state：根據customer_state欄位進行分組。ORDER BY number_of_customers DESC：將結果按客戶數量降序排列。LIMIT 10：限制輸出結果為前 10 筆。
執行結果如下：
+
---
-
---
-
---
-
---
-+
---
-
---
-
---
-
---
-
---
--+
| customer_state | number_of_customers |
+
---
-
---
-
---
-
---
-+
---
-
---
-
---
-
---
-
---
--+
| TX | 310 |
| CA | 295 |
| NY | 122 |
| PA | 106 |
| AZ | 85 |
| IL | 82 |
+
---
-
---
-
---
-
---
-+
---
-
---
-
---
-
---
-
---
--+
6 rows in set (0.00 sec)
訂單專案分析
接下來，我們分析 order_items 表中的資料。該表包含以下欄位：
order_id：訂單編號order_item_id：訂單中的專案序號product_id：產品編號seller_id：賣家編號shipping_limit_date：賣家出貨期限price：商品價格freight_value：運費
銷售商品統計
SELECT
product_id,
COUNT(*) AS total_items_sold
FROM
ShopSphere.order_items
GROUP BY
product_id
LIMIT 5;
內容解密：
SELECT product_id, COUNT(*) AS total_items_sold：選擇產品編號並計算每個產品的總銷售數量。GROUP BY product_id：根據產品編號進行分組。LIMIT 5：限制輸出結果為前 5 筆。
執行結果如下：
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
---
+
| product_id | total_items_sold |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
---
+
| 12f12498-606b-4cb6-a7d4-7afc439c802e | 1 |
| 59843816-7ef2-4e14-8fcf-b7d29ca82054 | 1 |
| 8ced67c7-4014-4357-9ee0-a25823f7a767 | 1 |
| 77b282b0-8b2d-4255-bc77-1d0b8e2744b8 | 2 |
| 89ee2351-daf8-4af0-8741-0485247a9a9b | 1 |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
---
+
5 rows in set (0.00 sec)
總銷售額統計
SELECT
product_id,
SUM(price) AS total_sales
FROM
ShopSphere.order_items
GROUP BY
product_id
LIMIT 5;
內容解密：
SELECT product_id, SUM(price) AS total_sales：選擇產品編號並計算每個產品的總銷售額。- 其餘部分與前一個查詢相同。
執行結果如下：
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
--+
| product_id | total_sales |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
--+
| 12f12498-606b-4cb6-a7d4-7afc439c802e | 366.13 |
| 59843816-7ef2-4e14-8fcf-b7d29ca82054 | 425.03 |
| 8ced67c7-4014-4357-9ee0-a25823f7a767 | 168.35 |
| 77b282b0-8b2d-4255-bc77-1d0b8e2744b8 | 634.20 |
| 89ee2351-daf8-4af0-8741-0485247a9a9b | 213.43 |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
--+
5 rows in set (0.01 sec)
平均價格計算
SELECT
AVG(price) AS average_price
FROM
ShopSphere.order_items;
內容解密：
SELECT AVG(price) AS average_price：計算所有商品的平均價格。
執行結果如下：
+
---
-
---
-
---
-
---
+
| average_price |
+
---
-
---
-
---
-
---
+
| 305.152857 |
+
---
-
---
-
---
-
---
+
1 row in set (0.00 sec)
高運費商品分析
SELECT
order_id,
product_id,
freight_value
FROM
ShopSphere.order_items
ORDER BY
freight_value DESC
LIMIT 10;
內容解密：
SELECT order_id, product_id, freight_value：選擇訂單編號、產品編號和運費。ORDER BY freight_value DESC：將結果按運費降序排列。LIMIT 10：限制輸出結果為前 10 筆。
執行結果如下：
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
+
| order_id | product_id | freight_value |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
+
| afcc7ed6-bffc-4da8-a116-ba95e23019de | ae3bfbd3-861f-4be3-843f-b1537d624176 | 45.96 |
| f4645faf-8062-4904-8871-a07d07edb6f1 | aaf77b3e-4cec-40d6-8a3d-98a049c5a887 | 43.78 |
| ... | ... | ... |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
+
10 rows in set (0.00 sec)
資料函式庫結構分析
ShopSphere 資料函式庫包含多個表格，並以第二正規化（2NF）形式設計。以下為主要表格的欄位說明：
圖表說明
此圖示呈現了 ShopSphere 資料函式庫的實體關係圖，各表格之間的關聯清晰可見。