はじめに
MySQLのViewは、複雑なクエリを仮想的なテーブルとして扱える機能です。今回は、Google Colab上でMySQLを動かし、Viewの基本的な使い方を実際に試してみました。
環境
- Google Colab / MySQL 8.0 / Python 3.x + PyMySQL
事前準備
# MySQLセットアップ
!apt update && apt install mysql-server -y
!service mysql start
!mysql -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';"
!pip install pymysql
1. テストデータの準備
import pymysql
connection = pymysql.connect(host='localhost', user='root', password='password', charset='utf8mb4')
try:
with connection.cursor() as cursor:
cursor.execute("CREATE DATABASE IF NOT EXISTS view_test")
cursor.execute("USE view_test")
# レストランテーブル
cursor.execute("""
CREATE TABLE IF NOT EXISTS restaurants (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
location VARCHAR(100),
rating DECIMAL(3,2),
cuisine_type VARCHAR(50)
)
""")
# メニューテーブル
cursor.execute("""
CREATE TABLE IF NOT EXISTS menu_items (
id INT AUTO_INCREMENT PRIMARY KEY,
restaurant_id INT,
item_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(10,2),
is_available BOOLEAN DEFAULT TRUE,
FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
)
""")
# 注文テーブル
cursor.execute("""
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
restaurant_id INT,
menu_item_id INT,
order_date DATE,
quantity INT,
total_price DECIMAL(10,2),
FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
)
""")
# サンプルデータ挿入
restaurants_data = [
("カレーハウス太郎", "東京都新宿区", 4.2, "インド料理"),
("パスタ工房", "東京都渋谷区", 4.5, "イタリア料理"),
("寿司処まる", "東京都銀座", 4.8, "日本料理"),
]
menu_items_data = [
(1, "チキンカレー", "メイン", 1200, True),
(1, "ナン", "サイド", 300, True),
(2, "カルボナーラ", "メイン", 1300, True),
(2, "シーザーサラダ", "サイド", 800, True),
(3, "特上寿司", "メイン", 3000, True),
(3, "茶碗蒸し", "サイド", 500, True),
]
orders_data = [
(1, 1, "2024-01-15", 2, 2400),
(2, 3, "2024-01-16", 1, 1300),
(3, 5, "2024-01-17", 1, 3000),
]
# データ挿入処理
for data in restaurants_data:
cursor.execute("INSERT IGNORE INTO restaurants (name, location, rating, cuisine_type) VALUES (%s, %s, %s, %s)", data)
for data in menu_items_data:
cursor.execute("INSERT IGNORE INTO menu_items (restaurant_id, item_name, category, price, is_available) VALUES (%s, %s, %s, %s, %s)", data)
for data in orders_data:
cursor.execute("INSERT IGNORE INTO orders (restaurant_id, menu_item_id, order_date, quantity, total_price) VALUES (%s, %s, %s, %s, %s)", data)
connection.commit()
print("データベース準備完了")
finally:
connection.close()
2. 基本的なViewの作成
connection = pymysql.connect(host='localhost', user='root', password='password', database='view_test', charset='utf8mb4')
try:
with connection.cursor() as cursor:
# 1. レストランメニュー一覧View
cursor.execute("DROP VIEW IF EXISTS restaurant_menu_view")
cursor.execute("""
CREATE VIEW restaurant_menu_view AS
SELECT
r.name AS restaurant_name,
r.cuisine_type,
m.item_name,
m.category,
m.price
FROM restaurants r
JOIN menu_items m ON r.id = m.restaurant_id
WHERE m.is_available = TRUE
ORDER BY r.name, m.category
""")
# 2. 高評価レストランView
cursor.execute("DROP VIEW IF EXISTS high_rated_restaurants")
cursor.execute("""
CREATE VIEW high_rated_restaurants AS
SELECT
name,
location,
rating,
cuisine_type,
CASE
WHEN rating >= 4.5 THEN '最高評価'
WHEN rating >= 4.0 THEN '高評価'
ELSE '普通'
END AS rating_category
FROM restaurants
WHERE rating >= 4.0
ORDER BY rating DESC
""")
connection.commit()
print("基本Viewの作成完了")
finally:
connection.close()
3. 集計処理のView
connection = pymysql.connect(host='localhost', user='root', password='password', database='view_test', charset='utf8mb4')
try:
with connection.cursor() as cursor:
# レストラン別売上集計View
cursor.execute("DROP VIEW IF EXISTS restaurant_sales_summary")
cursor.execute("""
CREATE VIEW restaurant_sales_summary AS
SELECT
r.name AS restaurant_name,
r.cuisine_type,
COUNT(o.id) AS total_orders,
COALESCE(SUM(o.total_price), 0) AS total_sales,
COALESCE(AVG(o.total_price), 0) AS avg_order_value
FROM restaurants r
LEFT JOIN orders o ON r.id = o.restaurant_id
GROUP BY r.id, r.name, r.cuisine_type
ORDER BY total_sales DESC
""")
connection.commit()
print("集計Viewの作成完了")
finally:
connection.close()
4. Viewの実行とデータ確認
connection = pymysql.connect(host='localhost', user='root', password='password', database='view_test', charset='utf8mb4')
try:
with connection.cursor() as cursor:
# 1. レストランメニュー一覧
print("=== レストランメニュー一覧 ===")
cursor.execute("SELECT * FROM restaurant_menu_view")
results = cursor.fetchall()
for row in results:
print(f"{row[0]} - {row[2]} ({row[3]}): {row[4]:,.0f}円")
# 2. 高評価レストラン
print("\n=== 高評価レストラン ===")
cursor.execute("SELECT * FROM high_rated_restaurants")
results = cursor.fetchall()
for row in results:
print(f"{row[0]} - 評価: {row[2]} ({row[4]})")
# 3. 売上集計
print("\n=== レストラン別売上集計 ===")
cursor.execute("SELECT * FROM restaurant_sales_summary")
results = cursor.fetchall()
print("レストラン名 | 料理種類 | 注文数 | 売上合計")
print("-" * 45)
for row in results:
print(f"{row[0][:15]:15} | {row[1][:8]:8} | {row[2]:5} | {row[3]:8.0f}")
finally:
connection.close()
5. Viewの管理
connection = pymysql.connect(host='localhost', user='root', password='password', database='view_test', charset='utf8mb4')
try:
with connection.cursor() as cursor:
# View一覧の確認
cursor.execute("SHOW FULL TABLES WHERE Table_type = 'VIEW'")
views = cursor.fetchall()
print("=== 作成されたView一覧 ===")
for view in views:
print(f"📊 {view[0]}")
# 特定のView定義確認
cursor.execute("SHOW CREATE VIEW restaurant_menu_view")
result = cursor.fetchone()
print(f"\n=== View定義例 ===")
print(f"View名: {result[0]}")
print(f"定義: {result[1][:80]}...") # 最初の80文字のみ表示
finally:
connection.close()
Viewのメリット・デメリット
Viewの利用には、再利用性やセキュリティの強化、クエリの可読性向上といった利点があります。一方で、複雑なViewはパフォーマンスの低下や更新制限、デバッグの難しさといった課題もあるため、用途に応じた適切な設計が求められます。
まとめ
Google Colab上でMySQLのViewを試すことで、その柔軟性と実用性を体感できました。通常のクエリとViewを目的に応じて使い分けることで、開発効率を高めつつ、セキュリティ対策にもつなげられます。複雑な処理やアクセス制限にはViewが特に効果的です。