SQL復習〜iタウンページ

みなさん前回の研修で習ったSQLは覚えてますか？
SELECT,INSERT,UPDATEの言葉は覚えてますか？
ここでは実際のWEBサイトではどのように使用されているのか見てみましょう。
※ここでは実装は行わないので内容を理解するようにしてください。

研修の課題の復習

MySql研修

実際のWEBサイトでは？

実際のWEBサイトではどのようにデータベースを使用しているのでしょうか？例で「iタウンページ」の構造を見てみましょう！

こちらの画像は「iタウンページ」の画面のスクリーンショットです。四角で囲んだ部分は一覧で表示されている店舗の中の一つで、これを「カセット」と呼びます。この１カセットの中身はどのようなデータ構造になっているか考えてみましょう。
まず表示されている情報を抜粋しました。

  • 画像
  • 動画マーク
  • 引越しサービス
  • 世田谷区の引越単身赴任の強い味方！前日までのご依頼翌日対応！
  • 住所 〒157-0074　東京都世田谷区大蔵５丁目３－９
  • 地図・ナビ
  • TEL 03-3415-6565
  • HP
  • くちコミする
  • お気に入りに登録

太文字以外の部分は全てデータベースから取得した情報になります。（太文字の部分はHTMLでベタで記述されているものでしょう）
これらのデータの構造は以下のようになっていると考えられます。（記述した名前等はあくまでも想定した名前です）

データベース

tenpoList

テーブル

tenpoDate

カラム名 型 値 備考
id INT(5) 1 ID
gazo VARCHAR http://***/gazo.png 画像のURL
movie VARCHAR 動画 ピクトに表示する文字
tenpo_nm VARCHAR 引越しサービス 店舗の名前
title VARCHAR 世田谷区の引越単身赴任の強い味方！前日までのご依頼翌日対応！ タイトル
jyusho VARCHAR 〒157-0074　東京都世田谷区大蔵５丁目３－９ 住所
pict_map_flg VARCHAR(5) 1 0.表示しない/1.地図・ナビ
tel INT(20) 03-3415-6565 店舗の電話番号
pict_hp_flg VARCHAR(2) 1 0.表示しない/1.HP
pict_email_flg VARCHAR 1 0.表示しない/1.E-mail
pict_kuchikomi_flg VARCHAR 1 0.表示しない/1.くちコミする
pict_okiniiri_flg VARCHAR 1 0.表示しない/1. お気に入りに登録

上記の内容を踏まえて、これらはどのように作成するのか考えてみましょう。

データベースの作成

まずは、iタウンページに関わるデータを管理するデータベースを作成します。
データベースの作成は以下の構文でできます。

CREATE DATABASE データベース名;

今回tenpoListという名前のデータベースを作成しようと思います。
そのため、実行するSQL文は下記になります。

CREATE DATABASE tenpoList;

データベースの一覧を見るためには、

SHOW DATABASES;

ちなみに…

余談ですが、大文字と小文字で分けて書いてありますね。
これはどういうことでしょうか？

大文字の箇所は MySQLのコマンド を表しており、その文に意味があります。
（例えば、CREATE DATABASE）
逆に小文字で書いてある部分は、開発者が任意で決める場所です。
（例えばデータベース名）

それではMySQLを書くときはこのように大文字、小文字を使い分けなければいけないのか？
そういうわけではありません。
MySQLのコマンドは小文字でも判別してくれます。
かくいう私も、普段自分で書くときは、

create database tenpoList;

とします。

ただ、今回は研修であるため、MySQLの文法が分かりやすくなるよう、あえて大文字と小文字を分けて記述します。

テーブルの作成（CREATE）

データベースを作成したら、まずはそのデータベースを使用する宣言をしなければなりません。
データベースの使用は、

USE データベース名;

つまり、tenpoListデータベースを使用したいのであれば、

USE `tenpoList`;

となります。

さて、それではテーブルの作成です。

テーブル作成の構文

テーブルを作成する場合は、以下の構文になります。

CREATE TABLE テーブル名(カラム定義1, カラム定義2, カラム定義3....);

テーブル名は分かりやすいですね。
今回であれば、tenpoDataになります。

さて、カラム定義とはなんでしょうか？
先ほどの表を見ると、カラム名の隣に 型 、 値 が記載されています。
テーブルを作成する際には、 各カラムにどのようなデータが入るか決めておく 必要があります。

カラム名 型 値 備考
id INT(5) 1 ID
gazo VARCHAR http://***/gazo.png 画像のURL
movie VARCHAR 動画 ピクトに表示する文字
tenpo_nm VARCHAR 引越しサービス 店舗の名前
title VARCHAR 世田谷区の引越単身赴任の強い味方！前日までのご依頼翌日対応！ タイトル
jyusho VARCHAR 〒157-0074　東京都世田谷区大蔵５丁目３－９ 住所
pict_map_flg VARCHAR(5) 1 0.表示しない/1.地図・ナビ
tel INT(20) 03-3415-6565 店舗の電話番号
pict_hp_flg VARCHAR(2) 1 0.表示しない/1.HP
pict_email_flg VARCHAR 1 0.表示しない/1.E-mail
pict_kuchikomi_flg VARCHAR 1 0.表示しない/1.くちコミする
pict_okiniiri_flg VARCHAR 1 0.表示しない/1. お気に入りに登録

それでは、カラム定義をする場合によく使用するキーワードを説明します。

型

これは今までの研修で習ったこともあるので、なんとなく想像ができるかもしれません。
数値なのか文字列なのか、ということですね。
（他にも種類がありますが、今回は数値と文字に絞ります。）

  • 数値の場合、INT
  • 文字列の場合、VARCHARもしくはTEXT

また、INT(2)、VARCHAR(5)など括弧がついているものがあります。
それは、入る文字の長さを制限しています。

たくさん文字が入ることを許容してしまうと、容量や速度などの制限に引っかかります。
はじめのうちは、そこまで気にする必要はありませんが、指示があったら指定できるようにはしましょう。

空欄を許容しない

例えば、FacebookやTwitterで、ユーザーの名前やパスワードは必ず聞かれますよね？
ユーザーデータとして、ユーザー名、パスワードが空欄などあってはいけないことです。
そのため、空欄を許さない定義にすることも可能です。

NOT NULL

また、NOT NULL制約をかけた場合は、次のデフォルト値が必要です。

デフォルト値

空欄で入ってきたとき、デフォルトの値を用意しておくと、自動でその値がセットされます。
例えば、デフォルトを0とします。

DEFAULT 0

tenpoDataを作成するSQL文

tenpoDataテーブルを作成するSQL文は下記になります。
見やすくなるよう改行しています。

CREATE TABLE tenpoData(id INT(5) NOT NULL AUTO_INCREMENT,
                       gazo VARCHAR(255),
                       movie VARCHAR(255),
                       title VARCHAR(255),
                       jyusho VARCHAR(255),
                       pict_map_flg VARCHAR(5) NOT NULL DEFAULT 1,
                       tel INT(20),
                       pict_hp_flg VARCHAR(2) NOT NULL DEFAULT 1,
                       pict_email_flg VARCHAR(255) NOT NULL DEFAULT 1,
                       pict_kuchikomi_flg VARCHAR(255) NOT NULL DEFAULT 1,
                       pict_okiniiri_flg VARCHAR(255) NOT NULL DEFAULT 1,
                       PRIMARY KEY (`id`)
);

1カラムごとにカンマ区切りで定義していきます。
また1つのカラムの中で複数の定義をする場合は半角スペースを空けます。

さて、ほとんどの意味はなんとなく分かると思います。
ただ、ふたつほど見慣れない文字があります。

AUTO_INCREMENTとPRIMARY KEY

AUTO_INCREMENT

AUTO_INCREMENT（オートインクリメント）とは、連番を作成するための機能です。
特にidのような、 一意にデータを特定することができる値 は重複した値があってはいけません。

例えば宝くじの当選番号がIDとして、同じIDの宝くじが2枚あったら当たったとき揉めますよね？
一般的に、テーブルには必ずidをつけて一意にデータを特定できるようにしておきます。

はじめのうちは、必ずidのカラムを定義する際にはAUTO_INCREMENTをつけておきましょう。

PRIMARY KEY

プライマリーキー（主キー）といって、レコードを一意に特定できるカラムを指定します。
今回の場合は、AUTO INCREMENTを設定したidのことですね。

AUTO INCREMENTを設定したはいいけど、プライマリーキーを指定してあげないと、テーブルを作成する際にエラーになるので、AUTO INCREMENTを指定する場合は忘れずに末尾につけましょう。

作成したテーブルの確認

作成したテーブルは、

SHOW TABLES;

で一覧確認できます。

レコードの削除（DELETE）

ここでテーブルからレコードを削除する方法を説明します。
まずデータベースからデータを削除する場合には「論理削除」と「物理削除」の２種類があります。

「 物理削除 」はDELETE構文を利用しデータベースから直接レコードを完全に削除してしまうことです。

「 論理削除 」はデータベースにフラグとなるフィールドを作成しておき、削除時に削除フラグを立てることにより、仮想的に見えなくしてしまう処理になります。実際のレコードは削除しないため万が一の場合には安心ですが、その分データベースに情報が残ったままなので、データベースへの負荷がかかりやすくなりますね。

ここでは物理削除の方法を説明します。

DELETE FROM テーブル名;

上記の構文では テーブル自体を削除 することができます。
もしテーブル中の指定したレコードのみを削除したい場合は上記の構文のあとに
条件を指定してあげます。

例えばid = 10のレコードを削除したい場合。

DELETE FROM テーブル名 WHERE id = 10;

これはidが10のデータを削除します。

例えばidが10以下のidを削除したい場合

DELETE FROM テーブル名 WHERE id < 10;

物理削除は何度も言うようにそのデータ自体を削除してしまうため削除する際は本当に削除しても問題ないか確認をしっかり行ってください。

本項の最後に

実際の例なので、自分でこのテーブルを作成しようとすると、やや骨が折れると思います。
安心してください。
次項でもう少しスケールダウンした、練習用のテーブルを作成していただきます。

カテゴリー

アーカイブ

Close Bitnami banner
Bitnami