MySQL入門 | Mityの趣味のサイト

MySQLとは

MySQLは、表形式のデータを保存・検索するリレーショナルデータベース管理システムです。利用者、商品、注文のようなデータをテーブルへ分け、SQLで操作します。

この講座で意識すること

SQLを実行する前に、対象のテーブルと行を言葉で説明する
結果を確認し、入力値を変えてもう一度実行する
設計、権限、バックアップもデータベースの仕事だと考える

MySQLはSQLの方言を持つため、他のデータベースと細部が異なります。この講座ではMySQL 8.4 LTSの環境で実行し、違いが出やすい箇所は補足します。

学習の準備

MySQLサーバーとクライアントを用意し、専用の練習用データベースで実行します。インストールは環境構築と接続で説明します。Dockerを使う場合も、データを削除して困らない練習用環境にしてください。

接続後に確認するMYSQL

SELECT VERSION();
SHOW DATABASES;
CREATE DATABASE study_mysql
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
USE study_mysql;

実行結果の例OUTPUT

VERSION()
8.4.x

Database
information_schema
mysql
performance_schema
sys
study_mysql

本番環境で試さない DROP、DELETE、UPDATEはデータを変えます。学習では専用のデータベースへ接続し、実行前に同じ条件のSELECTで対象を確認します。

SQLの基本

CREATE TABLEで構造を作り、INSERTで行を追加し、SELECTで検索します。最初は商品テーブルを一つ作って、SQLの流れを確認します。

テーブルを作り、データを追加するMYSQL

CREATE TABLE products (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE = InnoDB;

INSERT INTO products (name, price, stock) VALUES
    ('MySQL入門書', 2800.00, 5),
    ('ノート', 300.00, 12),
    ('ボールペン', 150.00, 0);

SELECT id, name, price, stock
FROM products
ORDER BY id;

実行結果OUTPUT

id  name          price    stock
1   MySQL入門書   2800.00  5
2   ノート        300.00   12
3   ボールペン    150.00   0

DECIMALは金額のように正確さが必要な値に向きます。詳しくはSQLと基本操作で、型、条件、追加・更新・削除を順に学びます。

検索・集計・結合

必要な列だけを選び、WHEREで行を絞り、ORDER BYで並べ替えます。複数のテーブルから結果を作るときはJOINを使います。

在庫がある商品だけMYSQL

SELECT name, price
FROM products
WHERE stock > 0
ORDER BY price DESC;

実行結果OUTPUT

name          price
MySQL入門書   2800.00
ノート        300.00

読む順番 まずFROMでどの表を使うか、次にWHEREでどの行か、最後にSELECTで何を表示するかを確認します。詳しくは検索・集計・結合へ進みましょう。

設計と整合性

テーブルを作るときは、列名だけでなく、何を一行で表すか、どの値を必須にするか、重複をどう防ぐかを考えます。PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECKなどの制約は、間違ったデータを保存しにくくする仕組みです。

注文と商品を一つの表に詰め込むのではなく、商品、注文、注文詳細へ分けると、商品名の変更や一つの注文に複数商品が含まれるケースを扱いやすくなります。テーブル設計と制約で図とSQLを使って確認します。

安全な更新

注文登録のように複数の変更をまとめるときは、トランザクションを使います。全部成功すればCOMMIT、途中で問題があればROLLBACKします。

在庫更新を取り消せる形で試すMYSQL

START TRANSACTION;

UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock >= 1;

SELECT id, name, stock
FROM products
WHERE id = 1;

ROLLBACK;

最後をROLLBACKにしたので、確認後に変更は元へ戻ります。実際に確定するときだけCOMMITします。更新とトランザクションでは、ロックと失敗時の考え方も扱います。

アプリケーションから使う

PHPなどのアプリケーションからMySQLを使うときは、SQLへ文字列を直接連結せず、プレースホルダーを使います。データベースのユーザーには必要な権限だけを与え、接続パスワードを公開リポジトリへ置きません。

PHP PDOで安全に検索するPHP

$pdo = new PDO($dsn, $user, $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->prepare(
    'SELECT name, price FROM products WHERE price >= :price'
);
$stmt->execute(['price' => 500]);
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);

PHPでの接続、入力値の扱い、トランザクションはPHPからMySQLを使うで一つの例を完成させます。

次に学ぶこと

この講座では、公式マニュアルの構文をそのまま並べるのではなく、同じ練習データを使って、作る、読む、設計する、更新する、アプリから使う順に学びます。最後に練習問題を解き、答えを変形させてください。

MySQL 8.4公式マニュアル構文・仕様・設定を調べる MySQL練習問題検索・設計・更新を総復習する おすすめ書籍MySQL徹底入門を読む

MySQL入門の講座一覧