MySQLの基本操作を図解で確認しました。データベース初心者が確認することを想定とした備忘録です。
データベース操作
データベースの作成・削除・選択
-- データベース作成
CREATE DATABASE company_db;
-- データベース一覧表示
SHOW DATABASES;
-- データベース選択
USE company_db;
-- データベース削除
DROP DATABASE company_db;
構造図:
[MySQL Server]
├── information_schema
├── mysql
├── performance_schema
├── sys
└── company_db ← 新規作成
├── employees (テーブル)
├── departments (テーブル)
└── projects (テーブル)
テーブル操作
テーブル作成
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
department_id INT,
salary DECIMAL(10,2),
hire_date DATE
);
テーブル構造:
employees テーブル
┌─────────────┬──────────────┬─────────────┬─────────────┐
│ id (PK) │ name │ email │ department_id│
│ INT │ VARCHAR(50) │ VARCHAR(100)│ INT │
│ AUTO_INC │ NOT NULL │ UNIQUE │ │
└─────────────┴──────────────┴─────────────┴─────────────┘
│ salary │ hire_date │
│ DECIMAL │ DATE │
└─────────────┴──────────────┘
テーブル情報確認
-- テーブル一覧表示
SHOW TABLES;
-- テーブル構造確認
DESCRIBE employees;
-- または
SHOW COLUMNS FROM employees;
データ挿入（INSERT）
基本的な挿入
-- 単一レコード挿入
INSERT INTO employees (name, email, department_id, salary, hire_date)
VALUES ('田中太郎', 'tanaka@example.com', 1, 500000, '2024-01-15');
-- 複数レコード一括挿入
INSERT INTO employees (name, email, department_id, salary, hire_date)
VALUES
('佐藤花子', 'sato@example.com', 2, 550000, '2024-02-01'),
('鈴木次郎', 'suzuki@example.com', 1, 480000, '2024-03-10');
挿入プロセス図:
[データ] → [バリデーション] → [テーブルに追加]
↓
・NOT NULL チェック
・UNIQUE チェック
・データ型チェック
データ検索（SELECT）
基本検索
-- 全データ取得
SELECT * FROM employees;
-- 特定カラムのみ取得
SELECT name, salary FROM employees;
-- 条件付き検索
SELECT name, salary
FROM employees
WHERE salary > 500000;
検索オプション
-- 並び替え
SELECT name, salary
FROM employees
ORDER BY salary DESC;
-- 件数制限
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
-- グループ化
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id;
SELECT処理フロー:
[テーブル] → [WHERE条件] → [GROUP BY] → [ORDER BY] → [LIMIT] → [結果]
↓ ↓ ↓ ↓ ↓
全レコード 条件絞込み グループ化 並び替え 件数制限
データ更新（UPDATE）
-- 特定レコードの更新
UPDATE employees
SET salary = 520000
WHERE name = '田中太郎';
-- 複数カラムの更新
UPDATE employees
SET salary = salary * 1.1,
email = 'new_tanaka@example.com'
WHERE id = 1;
更新処理図:
[UPDATE文] → [WHERE条件でレコード特定] → [SET句で値変更] → [コミット]
データ削除（DELETE）
-- 条件付き削除
DELETE FROM employees
WHERE department_id = 3;
-- 全データ削除（構造は保持）
DELETE FROM employees;
-- テーブル削除
DROP TABLE employees;
削除処理比較:
DELETE FROM table; → データのみ削除、構造は残る
DROP TABLE table; → テーブル全体を削除
TRUNCATE table; → 高速でデータ削除、AUTO_INCREMENTリセット
結合（JOIN）
-- 内部結合
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
-- 左結合
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
JOIN概念図:
employees departments
┌────┬─────────┬────┐ ┌────┬─────────────┐
│ id │ name │dept│ │ id │ dept_name │
├────┼─────────┼────┤ ├────┼─────────────┤
│ 1 │ 田中太郎 │ 1 │───│ 1 │ 営業部 │
│ 2 │ 佐藤花子 │ 2 │ │ 2 │ 開発部 │
│ 3 │ 鈴木次郎 │ 1 │───│ 3 │ 人事部 │
└────┴─────────┴────┘ └────┴─────────────┘
便利な関数とコマンド
集計関数
SELECT
COUNT(*) as 総従業員数,
AVG(salary) as 平均給与,
MAX(salary) as 最高給与,
MIN(salary) as 最低給与,
SUM(salary) as 給与総額
FROM employees;
日付関数
-- 現在日時
SELECT NOW(), CURDATE(), CURTIME();
-- 日付計算
SELECT name, hire_date,
DATEDIFF(NOW(), hire_date) as 勤続日数
FROM employees;
実践的なTips
インデックス作成
-- インデックス作成（検索高速化）
CREATE INDEX idx_department ON employees(department_id);
CREATE INDEX idx_email ON employees(email);
トランザクション
-- トランザクション開始
START TRANSACTION;
UPDATE employees SET salary = salary + 10000 WHERE department_id = 1;
UPDATE departments SET budget = budget - 50000 WHERE id = 1;
-- 確定またはロールバック
COMMIT; -- 確定
-- ROLLBACK; -- 取り消し
まとめ
MySQLの基本操作は以下の4つのCRUD操作が中心です：
- CREATE（作成）: INSERT文でデータ追加
- READ（読取）: SELECT文でデータ検索
- UPDATE（更新）: UPDATE文でデータ変更
- DELETE（削除）: DELETE文でデータ削除
これらのコマンドを組み合わせることで、データベースを効率的に操作できます。まずは基本的なSELECT文から始めて、徐々に複雑な操作に挑戦してみましょう！
動作確認用のサンプル
google colabでの動作確認用
# MySQL Google Colab サンプルコード
# 記事で紹介したMySQLコマンドを実際に試すことができます
# 必要なライブラリのインストールとMySQL設定
!apt update
!apt install mysql-server -y
!service mysql start
# MySQLのroot設定
!mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';"
!mysql -e "FLUSH PRIVILEGES;"
# Pythonライブラリのインストール
!pip install mysql-connector-python
import mysql.connector
import pandas as pd
from IPython.display import display
import time
# MySQLサービスの開始を待機
time.sleep(3)
# MySQLに接続
def create_connection():
try:
# まずrootパスワードを設定
import subprocess
subprocess.run(['sudo', 'mysql', '-e', "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';"],
capture_output=True)
subprocess.run(['sudo', 'mysql', '-e', "FLUSH PRIVILEGES;"], capture_output=True)
# 接続
connection = mysql.connector.connect(
host='localhost',
user='root',
password='password',
auth_plugin='mysql_native_password'
)
print("✅ MySQL接続成功")
return connection
except Exception as e:
print(f"❌ 接続エラー: {e}")
# 代替接続方法を試す
try:
connection = mysql.connector.connect(
host='localhost',
user='root',
password='',
auth_plugin='mysql_native_password'
)
print("✅ MySQL接続成功（パスワードなし）")
return connection
except Exception as e2:
print(f"❌ 代替接続もエラー: {e2}")
return None
# SQLクエリ実行関数
def execute_query(connection, query, fetch=False):
cursor = connection.cursor()
try:
cursor.execute(query)
if fetch:
return cursor.fetchall()
else:
connection.commit()
print(f"✅ クエリ実行成功: {query[:50]}...")
except Exception as e:
print(f"❌ エラー: {e}")
print(f"クエリ: {query}")
finally:
cursor.close()
# データフレーム形式で結果表示
def show_table(connection, query, columns=None):
cursor = connection.cursor()
try:
cursor.execute(query)
data = cursor.fetchall()
if columns is None:
columns = [desc[0] for desc in cursor.description]
df = pd.DataFrame(data, columns=columns)
display(df)
except Exception as e:
print(f"❌ エラー: {e}")
finally:
cursor.close()
# メイン実行
print("🚀 MySQL サンプルコード実行開始")
print("=" * 50)
# 接続
conn = create_connection()
if conn is None:
print("❌ MySQL接続に失敗しました。以下を確認してください：")
print("1. MySQLサービスが起動しているか")
print("2. 認証設定が正しいか")
# SQLiteを使った代替デモを提供
print("\n🔄 代替として、SQLiteでデモを実行します...")
import sqlite3
# SQLite接続
conn = sqlite3.connect(':memory:')
def execute_query_sqlite(connection, query, fetch=False):
cursor = connection.cursor()
try:
cursor.execute(query)
if fetch:
return cursor.fetchall()
else:
connection.commit()
print(f"✅ クエリ実行成功: {query[:50]}...")
except Exception as e:
print(f"❌ エラー: {e}")
finally:
cursor.close()
def show_table_sqlite(connection, query, columns=None):
cursor = connection.cursor()
try:
cursor.execute(query)
data = cursor.fetchall()
if columns is None:
columns = [desc[0] for desc in cursor.description]
df = pd.DataFrame(data, columns=columns)
display(df)
except Exception as e:
print(f"❌ エラー: {e}")
finally:
cursor.close()
# 関数を置き換え
execute_query = execute_query_sqlite
show_table = show_table_sqlite
print("✅ SQLite接続成功（MySQL代替モード）")
# 1. データベース操作（SQLiteの場合はスキップ）
if 'mysql.connector' in str(type(conn)):
print("\n📁 1. データベース操作")
execute_query(conn, "CREATE DATABASE IF NOT EXISTS company_db")
execute_query(conn, "USE company_db")
else:
print("\n📁 1. データベース操作（SQLiteでは自動作成）")
# 2. テーブル作成
print("\n📋 2. テーブル作成")
# SQLite対応のテーブル作成文
if 'sqlite3' in str(type(conn)):
create_table_query = """
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
department_id INTEGER,
salary REAL,
hire_date DATE
)
"""
create_dept_table = """
CREATE TABLE IF NOT EXISTS departments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
department_name TEXT NOT NULL
)
"""
else:
create_table_query = """
CREATE TABLE IF NOT EXISTS employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
department_id INT,
salary DECIMAL(10,2),
hire_date DATE
)
"""
create_dept_table = """
CREATE TABLE IF NOT EXISTS departments (
id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL
)
"""
execute_query(conn, create_table_query)
execute_query(conn, create_dept_table)
# 3. データ挿入
print("\n➕ 3. データ挿入")
# 部署データ
dept_data = [
"INSERT INTO departments (department_name) VALUES ('営業部')",
"INSERT INTO departments (department_name) VALUES ('開発部')",
"INSERT INTO departments (department_name) VALUES ('人事部')"
]
for query in dept_data:
execute_query(conn, query)
# 従業員データ
emp_data = [
"INSERT INTO employees (name, email, department_id, salary, hire_date) VALUES ('田中太郎', 'tanaka@example.com', 1, 500000, '2024-01-15')",
"INSERT INTO employees (name, email, department_id, salary, hire_date) VALUES ('佐藤花子', 'sato@example.com', 2, 550000, '2024-02-01')",
"INSERT INTO employees (name, email, department_id, salary, hire_date) VALUES ('鈴木次郎', 'suzuki@example.com', 1, 480000, '2024-03-10')",
"INSERT INTO employees (name, email, department_id, salary, hire_date) VALUES ('高橋三郎', 'takahashi@example.com', 3, 520000, '2024-01-20')"
]
for query in emp_data:
execute_query(conn, query)
# 4. データ検索
print("\n🔍 4. データ検索")
print("全従業員データ:")
show_table(conn, "SELECT * FROM employees")
print("\n給与が50万円以上の従業員:")
show_table(conn, "SELECT name, salary FROM employees WHERE salary >= 500000")
print("\n給与順（降順）:")
show_table(conn, "SELECT name, salary FROM employees ORDER BY salary DESC")
# 5. JOIN操作
print("\n🔗 5. JOIN操作")
print("従業員と部署の結合:")
join_query = """
SELECT e.name, e.salary, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
"""
show_table(conn, join_query)
# 6. 集計関数
print("\n📊 6. 集計関数")
print("給与統計:")
stats_query = """
SELECT
COUNT(*) as 総従業員数,
AVG(salary) as 平均給与,
MAX(salary) as 最高給与,
MIN(salary) as 最低給与,
SUM(salary) as 給与総額
FROM employees
"""
show_table(conn, stats_query)
# 7. データ更新
print("\n✏️ 7. データ更新")
execute_query(conn, "UPDATE employees SET salary = 530000 WHERE name = '田中太郎'")
print("田中太郎の給与更新後:")
show_table(conn, "SELECT name, salary FROM employees WHERE name = '田中太郎'")
# 8. 部署別集計
print("\n📈 8. 部署別集計")
group_query = """
SELECT d.department_name, COUNT(*) as 人数, AVG(e.salary) as 平均給与
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.department_name
"""
show_table(conn, group_query)
# 9. テーブル構造確認
print("\n🔧 9. テーブル構造確認")
print("employeesテーブル構造:")
show_table(conn, "DESCRIBE employees")
# 接続終了
conn.close()
print("\n🎯 サンプルコード実行完了!")
print("=" * 50)
print("📝 このコードで記事のMySQLコマンドが実際に動作することを確認できます。")
print("💡 各セクションのコードを個別に実行して動作を確認してみてください！")
実行例
参考情報