未経験・初心者向け「SQL鬼練１〜４」

ここでは、初心者から実践的に学べるプログラミングスクール「ウェブカツ!!」のPHP・MySQL部入門またはWEBサービス部を受講している生徒の方向けに、さらに「SQL」についての理解度を上げるため特訓をしていきます。

出題の回答は全て「SQL言語」を使って回答してください。

DBソフトの種類によってSQL言語は微妙に違いますが（と言っても基本の書き方はどれも同じなのでどのDBでも使える知識です）、ここでは「MySQL」を元にしたSQLの書き方を特訓していきます。

必ず実際にMySQLを動かしてphpMyAdminから問題のテーブルを作成してSQLを実行（「SQL」というタブから行えます）しましょう。（じゃないと練習にならないので）

鬼練１：データを挿入してみよう！part1

ここでは、下記の仕様書にあるテーブルを作って、そのテーブルにデータを作成してみましょう。

仕様書

下記要件に従ってSQLを記述してください。

下記の表に従ってテーブル「users」をphpMyAdminから作成し、そのテーブルにデータ（レコード）を１つ（１行）入れてください

データは「ユーザーID」はAUTO_INCREMENTの機能を使ってください

「ユーザー名」は「ウェブカツ」としてください

ヒント

SQLでテーブルにデータを入れるには「INSERT文」を使うんでしたね。

「レコード」「カラム」という単語の意味は部の方でやってるので、もうわかりますよね？

答え

まず、phpMyAdminでテーブルを作成する時の入力項目はこうなります。

phpMyAdminのバージョンによって多少管理画面のレイアウトが違う場合がありますがそこらへんは自分で判断してください。

ちなみにこの鬼練を作った時点のphpMyAdminのバージョンは「4.8.5」です。

MAMPを使っている人は管理画面がデフォルトで英語になってると思いますが日本語に変更できるのでググってくださいね。

まず、今回のテーブルを作る時の大事な点だけを解説します。

PRIMARY KEY(主キー)制約

「id」カラムにはPRIMARY KEY(主キー)制約をつけましょうね。

「PRIMARY KEY」または「主キー」とか、略して「PRIMARY」と言ったりもします。

この主キーというものは、

このテーブルで唯一、オリジナル（一意）なもの

という制約で、

テーブル内で一つのカラムにのみ指定できる

ものです。

なので、

主キー制約をつけたカラムに同じ値を入れることは出来ません。（同じ値が存在できない）

ということになります。

idが2のレコードが２つ重複して存在できないってことです。

なので、

既にidが2レコードがある状態でidが2のレコードを新規に作ろうとするとエラーになります。

INDEXについて

また、主キー制約をつけた場合

「INDEX（インデックス）」

というものが付与されます。

INDEXは日本語で「索引」ですね。

DBは「辞書」みたいなもんです。膨大なデータを１つの辞書の中に入っているわけです。

そうすると

「索引」

がないと１枚目からペラペラめくってDBは探さなきゃいけません。

もし、

１億レコードのデータがあった場合どうしましょ？

１レコードから１億レコード目まで一個一個ペラペラ辞書をめくるが如く探しますか？非効率ですよね？

なので、

よく検索に使うようなカラムには「INDEX（索引）」をつけておくと便利なんです。

通常、INDEXはカラム単体で好きなカラムにつけることができます。

なので、

「emailで検索したい」

とか

「idで検索したい」

という事が多い場合はそのカラムにINDEXをつけておくと便利なんですね。

じゃあ、なんでもかんでもつけときゃいいんじゃない？

と思いでしょうけどこのINDEXというのは実は

「主キーとインデッスクをつけたカラムの２カラムで構成されたテーブルをもう１つ別に作成する」

ということを内部でやっているわけなんです。

（実際はデータを検索しやすいように並べ替えたり、もうちょい複雑なことをやってるようです）

ということは、１億レコードのテーブルでカラムが100個あり、それぞれのカラムにINDEXをつけたらどうなるでしょう？

単純に101個のテーブル（元のテーブル＋100個のカラムにINDEXをつけたそれぞれのテーブル）が出来上がり

それぞれに１億ものレコードのデータをDBは保持しておかなきゃいけないわけですね。

DBくんが、101個もの辞書をずっと抱えてるようなもんです。かなりの力持ちでなきゃ大変ですね。（DBを置いているサーバーの容量がそれだけ大きくないといけない）

さらには、

INDEXをつけたカラムが更新される度にINDEX用の別テーブルも更新しなきゃならない

わけです。

更新が頻繁にされるカラムにINDEXをつけてしまうとINDEX用のテーブルも更新しなきゃいけないし、レコードが削除されればそっちのテーブルのレコードも削除しなきゃいけないわけで処理時間がその分かかるわけですね。

DBにおけるインデックスってなんぞや…？メリット・デメリットについて

なので、INDEXは

「主キー」

と

「よく検索で使用するカラム」

に対してだけ指定しておくのが普通です。

PRIMARY KEYをカラムに指定するだけでこの「INDEXの付与」も自動で行ってくれます。

MySQL初級者を脱するために勉強してること -INDEX編-

AUTO_INCREMENT

「インデックス」という項目の横に「A_I」という項目がありますね。

これが、「AUTO_INCREMENT」ですね。

これを設定したカラムには、データをわざわざ指定して挿入（作成）しなくても「カウントアップ（インクリメント）」して「連番」で値を入れてくれます。

（ちなみにレコードを作成することを業務では「レコードを挿入する」って言い方をすることがほとんどなので今後はそっちの言葉で話していきます）

なので、直近で挿入したレコードのidが「1」なら次に挿入するレコードのidは何も指定しなくても連番の「2」になります。

ちなみにA_Iを指定したカラムに値を指定して挿入したとしても、エラーにはならずに勝手に連番の値がつけられちゃいます。（やってみてね）

連番は必ず「１」からがスタートです。（プログラミングの配列だと0からでしたけどDBでは1からなんですよね）

照合順序

まずデータ型がVARCHAR型（文字列型）であれば、「照合順序」というものを設定する必要があります。

照合順序を指定することで、データを検索する時に「大文字や小文字を区別して検索がかかるか〜」とかって違いがあります。

詳しくは下記など記事を見てみてください。今は特段の理由がなければ「utf8_general_ci」を使う現場がほとんどです。

【MySQL】照合順序とは？

大事な項目はここらへんだけです。

VARCHARの「長さ/値」は255にする話は部活の練習の方でも話してるんでそっちみてくださいね。

で、テーブルが作られると「Structure（構造）」というタブに下図の様になっているはずです。

これでテーブルは完成ですね。

「id」というカラム名の横に鍵みたいなマークがついてますが、これが「主キー制約」がついているマークです。

では、このテーブルに対してデータを挿入しましょう。

phpMyAdminでSQLを実行させるには「SQL」というタブから実行できますね？

実行するSQLは？というとこれですね。

INSERT、INTO、VALUESという各句を大文字にしてますが小文字でも全く問題ないです。

全部小文字だと「見辛い」というだけなので、どちらでも構いません。（Shiftキー押すの面倒だったりするんで）

SQLの「INSERT文」は

で書くんでしたね。

もちろん、idカラムは指定せずとも連番で勝手に入ってくれるので大丈夫です。

シングルクウォートを使おう！

「カラムに入れる値」は「文字列」の場合には必ず「’（シングルクウォーテーション）」で囲いましょうね。

ここらへんは、jsやPHPと同じです。文字列はシングルクウォートで囲うことでパソコンに

「こっからここまで文字列だからね！」

と伝えられます。

もしシングルクウォーテーションがなければパソコンには

「これって命令なの？それとも値なの？」

と判断がつきませんからね。

また、

と書いてもOKです。

「`（バッククウォーテーション）」でテーブル名やカラム名を囲っていますね。

なぜこうしたかというと

テーブル名やカラム名がSQL言語の予約語だったとしても動くように

するためです。

予約語に気をつけよう！

予約語ってやりましたよね？

プログラミング言語の「命令」を表すために既にプログラミング言語の方で

「この言葉は使わないでね！僕が使うんだから！」

と予約されている命令言葉のことですね。

jsであれば変数を作る際に使う「var」だったり、jsやphp両方でも関数を作るために使う「function」といった言葉は使えません。

なんてできませんよね。

「私の名前は名前です」

って言ってるようなもんです。

PC「え、名前はなんですか？」

あなた「え、だから名前ですけど。」

PC「え、だから名前はなんですか？」

あなた「いや、だから名前は名前ですけど。」

PC「・・・・・・・・。」

PC「fuck you！」

みたいなやり取りになるわけです。

予約語を使われちゃうとパソコンは判断つかないわけですね。

話戻って今回のSQLのカラム名やテーブル名は「予約語」ではありません。

なので、バッククウォートを使う必要はないですが、そもそも予約語にどういったものがあるか調べるのも面倒ですよね。

例えばテーブル名なりカラム名に「like」や「on」はダメです。SQLにはlike句やon句といったものがあるからです。

なので必ずそういった名前にしている場合はバッククウォートで囲わないといけません。

そうしないとパソコン（この場合だとSQL言語を理解するのはデータベースくんです）は

「like」というものが「命令」の１つの意味なのか「カラム名やテーブル名」といった「ただの名前」なのか判別できないわけですね。

今回の例ならつける必要はありませんが、そもそも予約語がどんなものなのかいちいち調べて

「あ、これ予約語だからバッククウォートつけなきゃ」

なんてやってるのも面倒なんで、

「だったらテーブル名やカラム名には全部つけときゃいいっしょ」

ってやってもいいわけです。

そこらへんは現場次第です。

（そもそも、テーブル名やカラム名をつける時に予約語を使わないって事が大切ですけどね）

まぁ、最近はフレームワークで簡単に書けるため、こういったSQL（「生のSQL」って言ったりします）を直で書く事も少なくはなってきていますがまだまだ知っておく必要はあります。

半角スペースを必ず入れよう

また、

はそれぞれの単語の句ごとを半角スペースで区切っていますが、区切らなくてもいい場合もあります。

例えば

でもちゃんとDBには認識されてSQLに書かれた命令が実行されます。

人間からしてみても、カッコで区切ってるとこらへんは単語と次の単語との区別がつきやすいですよね。

逆に

こんな風に「単語同士」をくっつけちゃうとダメです。

phpMyAdminの「sql」のタブから実行してみましょう。エラーが出ますね？

単語がくっついちゃってDBからしたら「一つの単語（一つの命令）」と思われちゃうからです。

人間からしてもSQLに慣れた人ならわかりますが、初心者だと「こういう一つの句なのかな？」としか思わないですよね。

余談

ちなみにA_Iをつけたカラムの連番をリセットしたい場合はレコードを全部削除して、下記参考URLのSQLを実行するだけです。

https://qiita.com/sola-msr/items/d6c70995ddd95361dda7

まずは、簡単な問題でした。

鬼練２：データを挿入してみよう！part2

前回同様に下記の仕様書にあるテーブルを作って、そのテーブルにデータを作成してみましょう。

さっき作ったusersテーブルは一回削除して、また一からテーブルは作りましょうね。練習になんないんで。

仕様書

下記要件に従ってSQLを記述してください。

下記の表に従ってテーブル「users」をphpMyAdminから作成し、そのテーブルにデータ（レコード）を１つ（１行）入れてください

データは

「ユーザーID」はAUTO_INCREMENTの機能を使ってください

「ユーザー名」は「ウェブカツ」としてください

「email」は「test@test.com」としてください

 

ヒント

SQLでテーブルにデータを入れるには「INSERT文」を使うんでしたね。

複数のカラムにデータを入れたい場合はカンマで区切るんでしたね？

答え

テーブル作成のくだりは前回のと同じくカラムが１つ増えるだけなのでほとんど省略しちゃいます。

前回から見慣れないものは「UNIQUE」というものですね。これはテーブル作成時の「インデックス」という項目で選択できます。

UNIQUE INDEX

正式にはUNIQUE INDEX（ユニークインデックス）と言いますが、前回にも出てきた「INDEX」というものをこれも付与してくれます。

さらに「ユニーク」と呼ばれる様に指定したカラムは

重複したデータが存在できない

ようになります。

なので、重複したデータを挿入しようとするとエラーになります。

「id」カラムもそうですし、この「email」のカラムもそうですよね？

重複したデータが登録できちゃうとあとあとで困っちゃいます。（nameカラムの名前は同姓同名はいますよね）

世の中に複数の重複したemailが存在してしまったら大問題です。

夜中の2時に変なテンションで書いた恋人へのポエムも恋人以外が見れちゃいますから。

なので、emailのカラムは重複したemailをそもそも登録できないようにするわけです。

じゃあ、「主キーじゃダメなの？」というと

前にも話しましたが

主キーはテーブルに１つしかつけられない

わけです。通常各テーブルにはidカラムを作ってそこに主キーを設定します。

なので、他のカラムではもう使えないわけですね。

そこで登場するのがこの「UNIQUE」というものです。

これはテーブルにいくつも設定できるものなので、これを使ってやるわけですね。

では、そろそろテーブル作成の話は終わりにしましょう。

そのテーブルにデータを挿入するためのSQLは？というと

カンマで区切るだけですね。

っていう風に書きます。

順番が大切です。

とそれぞれのカッコ内は必ず順番が対応しているので注意しましょう。

くれぐれも

なんてやらないようにね。入る値が逆になっちゃうので。

また、カラムが３個、４個と増えたところでカンマをただ増やすだけです。

挿入（INSERT文）の書き方はとても簡単ですね。

わざと重複させてみよう！

ちなみに重複したemailのレコードを挿入してみるとどうなるでしょう？

再度下記のSQLを実行してみてください。

こんな感じのエラーが出るはずです。

phpMyAdminでは日本語かされてるのでエラーメッセージも日本語で出てますが

実際にログファイルをみた場合には

ってエラーが出ているはずです。

「重複（Duplicate）した登録（entry）をしようとしてるよ」って意味ですね。

こういったように

実際に開発をしていてDBに上手く登録されない場合は必ずログをみましょうね。

生徒の中にはログ見ずに質問してくる人がまだちょいちょいいたり、ログも見ずに一人で悩んでる生徒をTwitterで見かけますが、チャットの注意書きにあるのを見ながらログは見る様にしましょう。

MAMPであればこの記事のように「php_error.log」か「mysql_error.log」に何かしらのエラーのログが吐かれているはずなので。

MAMPでエラーログを確認する

原因切り分けを行おう

そういったログも見ながら、

１．SQL自体の書き方が悪いのか？（構文がおかしい）

２．そもそもSQLを実行する手前で入れているデータが悪いのか？（int型のカラムに文字列を入れようとしてるとか）

３．そもそもSQLを実行する処理までたどり着いていないのか？（SQLが実行される手前にif文があったとしたら、別の分岐に入っているかも。そもそもphp側でエラーが出てるかも。）

４．そもそもDBサーバーが動いてない（。。。。MAMPをちゃんと起動して「mysql」の項目がグリーンランプついてるか確認しましょう）

この「原因の切り分け」を必ずまず行いましょうね。

原因は大きく４つしかありません。

なので、まずは１が悪いのかもしれないので

SQLタブから直接SQLを実行してみることです。

そもそもphpMyAdminが開けなければDBサーバーが起動してないのでしょう。

また、SQLを実行してみて何かエラーが出てれば「SQLに渡すデータが悪いのか、SQLの書き方自体が悪いのか」分かりますね。

ウェブカツの部で「プレースホルダ」というものを使ってSQLを書いているハズです。

こんな感じですね。「:name」のところにあとあとで値が入って最終的に

のようなSQLが自動的に生成されて、そのSQLがDBで実行されるわけです。

なので、phpMyAdminの「SQL」のタブからプレースホルダの箇所に値を実際に入れてちゃんとしたSQLを作った上で実行してみればいいわけです。

エラーが何も出ずに登録されていれば、

SQLの書き方自体に問題はない。そもそもSQLを実行する処理まで辿りつく手前に原因がある。

という事がわかるわけです。

そしたらあとはphpでva_dump()とか関数つかってデバッグしまくるだけですね。

（もちろん、そもそもphp側でエラー出てるかもしれないのでまずログを見るところから。ですよ？）

鬼練３：データを更新してみよう！

前回同様に下記の仕様書にあるテーブルを作って、そのテーブルにあるデータを今度は「更新（UPDATE）」してみましょう。

またまた、一からテーブルは作りましょうね。

仕様書

下記要件に従ってSQLを記述してください。

前回と同じ構造のテーブル「users」をphpMyAdminから作成し、そのテーブルにデータ（レコード）を2つ入れてください

１つ目のデータは

「id」カラムの値は連番

「name」カラムの値は「ウェブカツ」

「email」カラムの値は「test@test.com」

としてください

２つ目のデータは

「id」カラムの値は連番

「name」カラムの値は「太郎」

「email」カラムの値は「test2@test.com」

としてください

２つ目のレコード（「id」カラムが2となるもの）に対して

「name」カラムの値を「ウェブカツ」に変えて（更新して）ください

ヒント

SQLでテーブルのレコードを更新するには「UPDATE文」を使うんでしたね。

答え

そのテーブルのレコードを更新するためのSQLは？というと

ですね。

UPDATE文は

っていう風に書きます。

が大切ですね。

更新するにしても「どのレコードを更新するの？」と指定しなきゃダメですからね。

人間だって指定してくれなきゃ分かりませんから。

なので「WHERE句」というものを使って

変更対象のレコードを指定する

必要があります。

指定の仕方はjsやphpといった一般のプログラミング言語の「if文」の書き方と同じです。

今回の場合だと

「idが2のもの」

が変更の対象でしたね。

なので、書き方としては

ってなるわけです。

もちろんカラム名のバッククウォートはつけてもつけなくてもお好きに。

これがもし、

「nameがウェブカツのもの」

が対象であれば

になりますよね。

「文字列」なので必ずシングルクウォートで囲わなきゃダメですよ？

jsでもphpでもjavaでも文字列が出てきたら「シングルクウォートで囲う（場合によりダブルクウォートを使う時もある）」と反射的にキーボートのキーに指が伸びるまでになってくださいね

鬼練４：データを更新してみよう！part2

前回同様に下記の仕様書にあるテーブルを作って、そのテーブルにあるデータをまた「更新（UPDATE）」してみましょう。

またまた、一からテーブルは作りましょうね。

また、データも１からinsert文つくって入れてみましょう。

仕様書

下記要件に従ってSQLを記述してください。

前回と同じ構造のテーブル「users」をphpMyAdminから作成し、そのテーブルにデータ（レコード）を4つ入れてください

挿入するデータは下記の４つです。

○１つ目のデータ

「id」の値は連番

「name」の値は「ウェブカツ」

「email」の値は「test@test.com」

○２つ目のデータ

「id」の値は連番

「name」の値は「ウェブカツ」

「email」の値は「test2@test.com」

○３つ目のデータ

「id」の値は連番

「name」の値は「二郎」

「email」の値は「test3@test.com」

○４つ目のデータ

「id」の値は連番

「name」の値は「三郎」

「email」の値は「test4@test.com」

としてください

 

上記データから「idが2以上」となるものに対して

「name」カラムの値を「変態おじさん」に変えてください

ヒント

「WHERE句」でどう条件を指定するか？ですよね。

jsやphpでやった「if文」の「条件式」を思い出しましょう。

答え

そのテーブルのレコードを更新するためのSQLは？というと

ですね。

の部分が

これで

「idが2以上」

という意味になります。

じゃあ、

「idが2を超える」

はどう書くでしょうか？

答えは、

ですね。

同じ様に

「idが2未満」

「idが2以下」

も分かりますね？

ちなみに

WHERE句で指定しない場合にどうなるか？

やってみましょう。

全レコードが更新されましたね？

変態おじさんのパラダイスです。

実際に稼働している本番のサービス上でこのSQLを実行してしまったら最悪な事態ですけどね。