Oops!!

プログラミングとかのラフなブログ

MySQLの勉強会の準備

MySQLの勉強会の準備

MySQLの構造を理解する

MySQLにログインするとそこからどこのデータベースを使うか選択する事になります。

その各データベースの中には Table（テーブル） と呼ばれるデータを分類する箱のようなものがあります。

その中にあるデータに対して作成、更新、抽出、削除などを行なっていきます。

MySQLの中にデータベースがあって、その中にテーブルがあるという構造になっています。

データベースを作る

データベースはCREATE DATABASE データベース名;で作成できます。

例えばCREATE DATABASE sample_db;みたいな感じでsample_dbというデータベースを作れます。

データベースが出来たのかの確認はSHOW DATABASES;で確認できます。CREATE DATABASEで作成したデータベースの名前があればOKです。

テーブルを作る

データベースが出来たら、USE データベース名;で使用するデータベースを選択します。今回はUSE sample_db;で先ほど作成したデータベースを利用します。

今回はuserというテーブルを作ってみたいと思います。

テーブルの作成は以下のSQL文でできます。

CREATE TABLE user (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `address` varchar(255),
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4;

userテーブルには名前と住所を保存する場所があります。このidやname、addressのようなものを カラム(column) と呼びます。

idはPRIMARY KEY (id)という定義がされています。これはidは絶対に重複しないものしか入らないという意味になります。

定義されたid、name、addressの後にintやvarchar(255)などがあります。これは型と呼びます。

intはintegerの略で整数しか登録できない事を指します。

varcharはCHARACTER VARYINGの略で可変長の文字列という意味で文字列を意味します。255というのは255文字まで登録できるという意味になります。他にもTEXTなど文字数に制限のない（制限はあるけど）ものもありますが、メモリの都合上できるだけ文字数は制限できた方が良いです。

NOT NULLが付いているものがあります。これが空のデータの時は登録に失敗します。

idについているAUTO_INCREMENTというのは自動的に1からプラスして増えていくという意味になります。その為、絶対に被らないのでPRIMARY KEYに使用される事が多いです。

DEFAULT CHARSET=utf8mb4というのは何も指定しない場合は、utf8mb4という文字コードを使用するという意味になります。文字コードはたくさんある為、説明は省きますが一般的にutf8mb4を使用していただければ問題ないと思います。

データを投入する

データの投入はINSERT文を使用します。INSERT INTO テーブル名 (カラム名1) VALUES ('データ1');で記述します。

INSERT
  INTO user
    (name, address)
  VALUES 
    ('鈴木', '愛知県');

一気に複数の入力をする場合はvaluesをカンマ区切りで複数記述します。

INSERT
  INTO user 
    (name, address) 
  VALUES 
    ('佐藤', '北海道'),
    ('高橋', '東京都'), 
    ('田中', '大阪');

これで鈴木、佐藤、高橋、田中の４つのデータができたと思います。

データを抽出する

データを抽出はSELECT文を使用します。SELECT カラム名 FROM テーブル名;で記述します。

SELECT name FROM user;

これでnameの一覧を取得する事ができます。SELECTで*（アスタリスク）を使うとワイルドカードと呼ばれ、全てのカラムという意味になります。

以下の場合は、userのid, name, addressの全てを表示します。

SELECT * FROM user;

WHERE文で絞り込む

WHERE文を使うと指定したいものを絞り込む事ができます。

SELECT * FROM user
  WHERE id = 1;

ユーザーに紐付く情報を作成する

postというユーザーのブログの投稿を保存するテーブルを作成します。

user_idというカラムを利用してユーザーのidと紐付けます。

CREATE TABLE post (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `article` varchar(255),
  `user_id` INT NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4;

postにデータを投入します。

INSERT
  INTO post
    (title, article, user_id) 
  VALUES 
    ('普通のタイトル', '普通の記事', 2), 
    ('タイトル', '退屈な記事', 3), 
    ('素晴らしいタイトル', '素晴らしい記事', 3), 
    ('不思議なタイトル', '不思議な記事', 4),
    ('何かのタイトル', '何かの記事', 1), 
    ('悲しいタイトル', '悲しい記事', 3);

データを紐付けて抽出する

誰がどの投稿をしたのか今の状況だとわかりにくいです。その為、ユーザー情報と投稿情報を紐付けたいと思います。

紐付ける時はJOINやLEFT JOIN、RIGHT JOINという文をSELECT文に組み合わせて使います。

SELECT * 
  FROM user
  JOIN post
    ON user.id = post.user_id;

やってる順番としては、まずFROM userでユーザー情報を呼び出します。 その情報に対してJOIN postで投稿を紐付ける宣言をします。 どの情報を紐付けるかをONで記述します。

書き方はテーブル名.カラム名になります。今回はuser.idやpost.user_idになります。

複数のテーブルを跨いだSELECTの書き方

今までのSELECT文は一つのテーブルだったので、そのままカラム名を書けばいいだけでした。

しかし複数のテーブルを組み合わせた場合にカラム名が重複する場合があります。

その場合はテーブル名.カラム名表現できます。

しかし以下のSQLを実行するとuser.idとpost.idが同じidというカラム名で表記されてしまいます。

SELECT user.id, user.name, post.id
  FROM user
  JOIN post
    ON user.id = post.user_id;

その場合はasというのを使って、以下のように別名に変換できます.

SELECT user.id as 'ユーザーのid', user.name, post.id as '投稿ののid'
  FROM user
  JOIN post
    ON user.id = post.user_id;

重複しない場合はテーブル名を書かずSELECTできます。

postのデータの問題点

このデータの持ち方には問題が発生する場合があります。

今回投稿は1投稿者に対して記事を複数持つことができますが、共同編集など記事を共有する事ができません。

その為、中間テーブルという手法を用いる事で解決していきます。

中間テーブルを作成するためのデータ作り

今回は記事に対して、複数のユーザーを紐付けたいと思います。

それと記事に対して、タグを付けていきたいと思います。

userとpostの中間テーブル

userのidとpostのidの関係のみを抽出します。

# テーブル作成
CREATE TABLE user_post (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `post_id` INT NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4;

# データ投入
INSERT 
  INTO user_post
    (user_id, post_id)
  VALUES 
    (1, 1),
    (2, 1),
    (1, 2),
    (3, 3),
    (2, 4),
    (4, 4),
    (1, 5),
    (3, 6);

tagテーブル

# テーブル作成
CREATE TABLE tag (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4;

# データ投入
INSERT 
  INTO tag 
    (name) 
  VALUES
    ('スポーツ'), 
    ('ビジネス'), 
    ('エンタメ'), 
    ('ファッション'), 
    ('海外');

postとtagの中間テーブル

postのidとtagのidの関係のみを抽出します。

# テーブル作成
CREATE TABLE post_tag (
  `id` int NOT NULL AUTO_INCREMENT,
  `post_id` INT NOT NULL,
  `tag_id` INT NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4;

# データ投入
INSERT 
  INTO post_tag
    (post_id, tag_id)
  VALUES 
    (2, 1),
    (2, 3),
    (2, 5),
    (3, 2),
    (3, 3),
    (4, 4),
    (4, 5),
    (5, 1),
    (5, 4),
    (6, 2);

user_postテーブルを使ってユーザーに紐づいてる記事を抽出する

ちょっと複雑なSQL文の考え方

1. まずはSELECT * FROM テーブル名;をする

SELECT * FROM `user`;

2. 次に紐付けるものをJOINする

いきなりpostとJOINしたくなるけど、先に中間テーブルとJOINする。

SELECT * FROM `user`
  JOIN user_post
    ON `user`.id = user_post.user_id;

3. 中間テーブルと本当にJOINしたかったものとJOINする

SELECT * FROM `user`
  JOIN user_post
    ON `user`.id = user_post.user_id
  JOIN post
    ON post.id = user_post.id;

4. 最後に抽出したいものだけSELECTにし、見やすくする

SELECT user.id as 'ユーザーid', name as '投稿者', title as '記事のタイトル', article as '本文' FROM `user`
  JOIN user_post
    ON `user`.id = user_post.user_id
  JOIN post
    ON post.id = user_post.id;

練習問題

タグから検索して、タグ名、投稿者、記事のタイトル、本文を抽出してください。

SELECT 
    tag.`name` as 'タグ名',
    `user`.name as '投稿者',
    post.title as '記事のタイトル',
    post.article as '本文'
  FROM tag
  JOIN post_tag
    ON post_tag.tag_id = tag.id
  JOIN post
    ON post.id = post_tag.post_id
  JOIN user_post
    ON user_post.post_id = post.id
  JOIN user
    ON user.id = user_post.user_id;
  WHERE tag.id = 1;
プロフィール画像

すずき ゆうた

愛知県でフリーランスのフロントエンド・エンジニアをしています。Reactを用いた開発が得意です。 他にもプロジェクトマネジメントや組織マネジメントも行ってきました。エビデンスのない事でも自分の経験から書いていくので話半分くらいでお願いします。

前のページに戻る