MySQL初心者入門講座

MySQLにはビュー(VIEW)という機能があります。ビューとは仮想のテーブルのようなものです。Accessではクエリに相当します。

ビューを使えば複数のテーブルを結合したり、抽出条件が複雑なSELECT文でも、まるで1つのテーブルのように扱うことができます。

一度ビューを作成してしまえば、普通のテーブルと同じように利用できます。言葉で説明すると難しそうですが、試してみるとすぐにメリットを実感できます。


【1】以下の図を参考にMySQLを開始・接続します。

1.コマンドプロンプトを起動します。
2.「net start mysql」と入力し、「Enterキー」を押します。
3.「mysql -u pgtop -p -b」と入力し、「Enterキー」を押します。
4.パスワードを求められるので「12345」入力し、「Enterキー」を押します。



＊「-b」オプションを付けて起動するとビープ音が出なくなります。


【2】以下のSQL文を入力し、使用するデータベースを選択します。

USE SampleDB040;




【3】まずは以下のSQL文を入力し、T07Slip テーブルのデータを確認します。

SELECT * FROM T07Slip;




T07Slip テーブルだけでは、顧客がどこの誰だかわかりませんよね。次は複数のテーブルを内部結合してデータを表示してみましょう。


【4】以下のSQL文を入力し、「Enterキー」を押します。





＊SQL文が長いのでコピー・ペーストしてください。
＊SQL文の意味については Access SQL初心者入門講座 で詳しく解説しています。


【5】テーブルが結合したデータが表示されました。



「;」のかわりに「\G」を使うとレコードが縦に表示されます。



縦に表示した場合



しかし毎回こんな長いSQL文を入力していては大変だしミスも出ます。そこでビューを作成してみることにします。


【6】以下のSQL文を入力し、「Enterキー」を押します。





＊先頭に「CREATE VIEW V01Slip AS」が付くだけです。


【7】メッセージが表示され、ビューが作成されました。




ここまでの手順では便利になった感じがしませんが、ビューを使うとデータを簡単に表示できます。

【8】以下のSQL文を入力し、「Enterキー」を押します。

SELECT * FROM V01Slip;




【9】テーブルが結合したデータが表示されました。



どうですか？最初に入力したSQL文に比べて、表示がものすごく簡単になったのではないでしょうか。一度ビューを作成すると、普通のテーブルと同じように扱うことができるのです。


【10】以下のSQL文を入力し、ビューのフィールドを選択して表示してみましょう。

SELECT SLIP_CD, SLIP_DATE, CST_CD, CST_NAME FROM V01Slip;




【11】以下のSQL文を入力し、ビューのレコードを抽出してみましょう。

SELECT * FROM V01Slip WHERE CST_NAME LIKE '%田%';



名前に「田」が含まれる顧客を抽出しました。普通のテーブルとまったく変わらずに扱えます。


次はビューの管理についてです。まずどんなビューが作成されているか確認します。

【12】以下のSQL文を入力し、「Enterキー」を押します。

SELECT * FROM information_schema.VIEWS;
＊データベースを選択していないので「データベース名.テーブル名」にしています。



少し見難いですが下のほうに、ビューを作成したデータベース、ビュー名、作成したときのSQL文が表示されています。


【13】ビューを削除するには以下のSQL文を入力し、「Enterキー」を押します。

DROP VIEW V01Slip;



＊ビューを削除してもテーブルは削除されませんので安心してください。


【14】ビューが削除されたか確認してみましょう。以下のSQL文を入力し、「Enterキー」を押します。

SELECT * FROM information_schema.VIEWS;



空なのでビューは1つもありません。


【15】以下の図を参考にMySQLを終了・停止します。

1.「exit」と入力し、「Enterキー」を押します。
2.「net stop mysql」と入力し、「Enterキー」を押します。




【16】コマンドプロンプトは閉じてください。


【ワンポイント】
作成済みのビューを変更するには「ALTER」を使います。

ALTER VIEW V01Slip AS
新しいSELECT文;

いったんビューを削除してから作り直しても同じです。

コマンドプロンプトからMySQLを操作するとき、画面が狭いとSQL文が入力し難く、結果リストの表示もレイアウトが崩れてしまいます。

コマンドプロンプトはマウスで画面を小さくできるのですが、拡大ができません。拡大するには設定が必要です。

「中級」ではSQL文が長くなり、表示するフィールド数も増えますので、事前にコマンドプロンプトを使いやすく設定しておきましょう。最適な環境を整えることで学習効率も上がります。

幅を広くした画面



【1】コマンドプロンプトのショートカットをマウスで右クリックし、コンテキストメニューから「プロパティ」を選択します。



＊このショートカットは、「Step3・MySQLの起動と停止 その1」で作成したものです。


【2】「レイアウト」タブをクリックします。




【3】「ウィンドウのサイズ」の幅と、「画面バッファのサイズ」の幅を「125」にし、「OK」ボタンをクリックします。



最適な幅はお使いのパソコンによって異なります。できるだけ大きめにとってください。あとでマウスでドラッグして縮小することができます。


【4】他にもフォントサイズや画面の背景色、文字色などの設定ができます。

フォント設定


色の設定


色はあまり派手にすると、読みにくくなり目が疲れます。基本的に「白地に黒」か「黒字に白」が読みやすいです。あなたが使いやすいように設定してください。

MySQLのようなデータベースは、以下の2つができればとりあえず基本的な操作はできます。初級としてはこれだけで十分です。

・データベース、ユーザー、テーブルなどの作成に関するSQL
・検索、追加、更新、削除などの操作に関するSQL

まず「MySQLの使い方　初級」の Step1〜Step18までを行なってください。次に Access SQL初心者入門講座 をMySQLに読み替えて行ないます。

「Access SQL初心者入門講座」では、複数のテーブルを結合したSQL文の考え方などが学べます。基本的なSQL文はどのデータベースでも同じなので、解説が重複するため共通して使うことにしました。

MySQLとAccessのSQL文の違いについては、「Step18・SQLの基礎を学ぶ」にまとめていますので一度ご覧ください。

＊ODBCを使った「SampleDB040Conn.mdb」を使えば、まったく同じ画面で学べます。

どうしてもMySQLにこだわりたい方はコマンドプロンプトから、間接的にMySQLを操作しても良いという方は SampleDB040Conn.mdb を使う方法をおすすめします。

実は上記に書いた「初級」で学ぶことだけで、ちょっとしたシステムなら作ることができます。通常MySQLはプログラミング言語から操作することが多いのですが、その時に使うSQL文はそれほど複雑なものでなくてもできるからです。

でもMySQLには様々な機能が用意されています。必ず使わなければならないものではありませんが、知っていると効率よくシステムを開発することができます。

例えば足し算だけでも計算できるけれど、掛け算を知っていると簡単に素早く答えが出せるような感じです。

MySQLでは複雑なSQL文を簡単にしたり、自分で関数を作ることもできます。「中級」では「初級」を習得した前提で解説しますので、まだの方は先に初級から行なってください。

データベースとして高機能なMySQLと、使いやすいAccessを組み合わせるとかなり便利です。一つ目のメリットはMySQLのデータを視覚的に操作できるので、開発が楽になります。

そして二つ目のメリットは、開発ツールとしてのAccessです。ODBCを設定して一度テーブルをリンクしてしまえば、クエリやフォーム、レポート、VBAなどAccessの機能がそのまま使えます。つまりAccessでユーザー側のシステムを構築することができるのです。

Accessは初心者でも使いやすいデータベースなので、MySQLのデータを表示するフォームやレポートを簡単に作れます。試しに今回はフォームを作ってみましょう。


【1】以下の図を参考にMySQLを開始・接続します。

1.コマンドプロンプトを起動します。
2.「net start mysql」と入力し、「Enterキー」を押します。
3.「mysql -u pgtop -p -b」と入力し、「Enterキー」を押します。
4.パスワードを求められるので「12345」入力し、「Enterキー」を押します。



＊「-b」オプションを付けて起動するとビープ音が出なくなります。


【2】以下のSQL文を入力し、使用するデータベースを選択します。

USE SampleDB040;




【3】Accessの「SampleDB040Conn.mdb」を開きます。




【4】オブジェクトから「フォーム」を選択します。




【5】「新規作成」をクリックします。




【6】「フォーム ウィザード」、「t01prefecture」を選択し、「OK」ボタンをクリックします。




【7】「>>」ボタンをクリックします。




【8】フィールドがすべて選択されたことを確認し、「次へ」ボタンをクリックします。




【9】「表形式」を選択し、「次へ」ボタンをクリックします。




【10】「標準」を選択し、「次へ」ボタンをクリックします。




【11】フォーム名に「F03Prefecture」、「フォームを開いてデータを入力する」にチェックし、「完了」ボタンをクリックします。




【12】フォームが開き、MySQL側のデータが表示されました。




【13】Access側のフォームから追加、更新、削除を行い、MySQL側のデータを変更できることを確認してください。

例


SELECT * FROM T01Prefecture;


フォームで削除する場合は、削除したいレコードを選択してから、ツールバーの「レコードの削除」ボタンをクリックするか「Deleteキー」を押します。マウスで右クリックして「切り取り」でも削除できます。




【14】フォームの動作を確認したら閉じます。



このようにAccessならウィザードに従うだけで作れる単純なフォームも、Javaなどのプログラミング言語ではかなりの量のソースコードを書かなければなりません。


次は販売伝票でAccessとMySQLの連携を確認してみましょう。販売伝票は少し複雑ですので、あらかじめフォームは作成してあります。作り方は最後の「ワンポイント」をお読みください。

【15】オブジェクトのフォームで「F01Slip」を選択し、「開く」ボタンをクリックします。




【16】販売伝票フォームの画面が表示されました。



＊この画面は F01Slip に F02Details が組み込まれる形になっています。


【17】販売伝票でもAccess側のフォームから追加、更新、削除を行い、MySQLのデータを変更できることを確認してください。

白いコントロールは値を変更できます。灰色はクエリの働きで自動的に表示されます。


＊SLIP_DATE と CST_CD のデータは、カーソルを明細部分に移動した時か、販売伝票のレコードを移動したときに確定します。

MySQLでの確認の仕方

SELECT * FROM T07Slip;


SELECT * FROM T08Details;


＊明細のレコードを新しく追加した場合は下のほうに表示されます。


【18】確認したら販売伝票フォームを閉じます。


【19】Accessのファイルを閉じます。


【20】以下の図を参考にMySQLを終了・停止します。

1.「exit」と入力し、「Enterキー」を押します。
2.「net stop mysql」と入力し、「Enterキー」を押します。




【21】コマンドプロンプトは閉じてください。


販売伝票のような画面をJavaで作るとしたら、数百から千行を超えるソースコードを書く必要があります。ユーザー側の画面をAccessで作るメリットを実感できたのではないでしょうか。

今回は取り上げませんでしたが、AccessではVBAによるプログラミングもできますので、より細かい制御ができます。Accessは開発ツールとしても重宝します。


【ワンポイント】

販売伝票は2つのクエリと2つのフォームからできています。



販売伝票フォームの作り方や仕組みはMicrosoft Access初心者入門講座で詳しく解説しています。

興味がある方は「Access クエリの使い方 中級」からご覧ください。Accessのテーブルを使うか、リンクしたMySQLのテーブルを使うかの違いだけです。試す場合はMySQLの開始をお忘れなく。

それでは実際にAccessからMySQLのデータベースを操作してみましょう。ODBCを使って一度テーブルをリンクしてしまえば、あとはAccessのテーブルと同様に操作できます。MySQLをGUIで操作できるメリットを実感できると思います。

今回AccessからMySQLに対して行なってみる操作は以下の通りです。

・データを表示する
・AccessからMySQLへ追加、更新、削除する
・テーブルの構造を表示する


【1】以下の図を参考にMySQLを開始・接続します。

1.コマンドプロンプトを起動します。
2.「net start mysql」と入力し、「Enterキー」を押します。
3.「mysql -u pgtop -p -b」と入力し、「Enterキー」を押します。
4.パスワードを求められるので「12345」入力し、「Enterキー」を押します。



＊「-b」オプションを付けて起動するとビープ音が出なくなります。


【2】以下のSQL文を入力し、使用するデータベースを選択します。

USE SampleDB040;




【3】「SampleDB040Conn.mdb」を開きます。




【4】「t01prefecture」を選択し、「開く」をクリックします。




【5】「t01prefecture」テーブルが開きました。これはMySQL側のデータベースです。




Access側からデータを操作して、MySQL側に反映されることを確認します。

【6】最後のレコードの下に、「99 ハワイ」と入力します。



＊この時点ではMySQLのデータは変わっていません。


【7】カーソルを他のレコードへ移動すると、データが確定します。



＊この時点でMySQLのデータが変わります。


【8】MySQLで追加したデータを確認してみましょう。

SELECT * FROM T01Prefecture;




【9】次は「グアム」に更新してください。




【10】MySQLで更新したデータを確認してみましょう。

SELECT * FROM T01Prefecture;




【11】次はレコードを削除します。削除したいレコードの左をマウスで右クリックし、「レコードの削除」を選択します。




【12】削除のメッセージが表示されたら、「はい」ボタンをクリックします。




【13】レコードが削除されました。




【14】MySQLで削除されているか確認してみましょう。

SELECT * FROM T01Prefecture;




【15】ここで一度テーブルを閉じます。


AccessでMySQLのテーブルの構造を確認することができます。

【16】「t01prefecture」をデザインで開きます。




【17】メッセージが表示されたら、「はい」ボタンをクリックします。




【18】デザインビューが表示されました。この画面でテーブルの構造が確認できます。




【19】デザインビューを閉じてください。


【20】Accessのファイルを閉じます。


【21】以下の図を参考にMySQLを終了・停止します。

1.「exit」と入力し、「Enterキー」を押します。
2.「net stop mysql」と入力し、「Enterキー」を押します。




【22】コマンドプロンプトは閉じてください。


AccessからMySQLを操作できることが、わかったのではないでしょうか。

前回までに全て準備が整ったので、Accessのリンク機能を使ってMySQLのデータベースへリンクします。

今回使用するAccessのファイルは、ダウンロードできるように準備しました。このファイルにはサンプルとして、MySQLのデータを表示するためのクエリとフォームが含まれています。

＊今回の手順を行なうには、Access2000以上がインストールされている必要があります。


【1】あらかじめMySQLを開始しておきます。

1.コマンドプロンプトを起動します。
2.「net start mysql」と入力し、「Enterキー」を押します。




【2】SampleDB040Conn.zipをダウンロードします。


【3】ダウンロードした圧縮ファイルを解凍します。




【4】「SampleDB040Conn.mdb」を開きます。




【5】まだテーブルオブジェクトは空です。




【6】メニューから「ファイル」→「外部データの取り込み」→「テーブルのリンク」を選択します。



＊インポートでは違う意味になりますのでご注意ください。インポートは元のデータベースとは切り離されます。(別のデータベースになる)


【7】リンクの画面が表示されたら、ファイルの種類で「ODBC Databases()」を選択します。




【8】「コンピュータ データソース」タブをクリックします。




【9】「SampleDB040」を選択し、「OK」ボタンをクリックします。



＊種類が「ユーザー」になっているのは、「ユーザーDNS」として登録したからです。


【10】「パスワードの保存」をチェックし、「すべて選択」ボタン、「OK」ボタンの順でクリックします。




【11】Accessのテーブルオブジェクトに、MySQLのテーブルが表示されました。




【12】Accessのファイルを閉じます。


【13】MySQLを停止します。

1.「net stop mysql」と入力し、「Enterキー」を押します。




これでAccessからMySQLが操作できるようになりました。あとはAccessのテーブルと同じように扱うことができます。

今回はMySQLに作成済みの「SampleDB040」データベースを、ODBCデータソースに登録します。登録するとAccessなどのアプリケーションからMySQLに接続できるようになります。

ODBCデータソースへの登録はデータベースごとになります。もしMySQLに新しいデータベースを作成してODBCを利用する場合は、新たに登録が必要です。

＊以下の登録作業を行なうには、MySQLが開始している必要がありますので、ご注意ください。


【1】あらかじめMySQLを開始しておきます。

1.コマンドプロンプトを起動します。
2.「net start mysql」と入力し、「Enterキー」を押します。




【2】コントロールパネルを開き、「管理ツール」をダブルクリックします。




【3】「データソース(ODBC)」をダブルクリックします。




ここからはデータソースを追加する設定です。

【4】「ユーザーDNS」の「追加」ボタンをクリックします。



＊ユーザーDNSとシステムDNSの違いは、登録したデータソースを利用できる範囲です。システムにすると、そのパソコンを使うすべてのユーザーが利用できることになります。


【5】ドライバに「MySQL ODBC 5.1 Driver」を選択し、「完了」ボタンをクリックします。




【6】以下の図のように設定します。

Data Source Name　SampleDB040
Server　localhost
User　pgtop
Password　12345
Database　SampleDB040　＊選択



＊MySQLが開始されていないとデータベースを選択できません。


【7】設定したら「OK」ボタンをクリックします。




【8】ユーザーデータソースに「SampleDB040」が追加されていることを確認して、「OK」ボタンをクリックします。




【9】設定が完了したら、MySQLを停止します。

1.「net stop mysql」と入力し、「Enterキー」を押します。




【ワンポイント】

「構成」ボタンでいつでも設定を変更できます。

今回はMySQL用のODBCドライバをインストールします。ウィザードにしたがって進めるだけで簡単にインストールできます。作業時間はほんの数分で済みます。


【1】前回ダウンロードした「mysql-connector-odbc-5.1.0-alpha-win32.msi」をダブルクリックして実行します。(ファイル名はバージョンにより変わります)




【2】ダイアログボックスが表示されたら、「実行」ボタンをクリックします。




【3】「Next」ボタンをクリックします。




【4】標準でインストールするので「Typical」を選択し、「Next」ボタンをクリックします。




【5】「Install」ボタンをクリックします。




【6】「Finish」ボタンをクリックします。




次は実際にインストールされているか確認してみましょう。

【7】コントロールパネルを開き、「管理ツール」をダブルクリックします。




【8】「データソース(ODBC)」をダブルクリックします。




【9】「ドライバ」タブをクリックします。




【10】ODBCドライバに「MySQL ODBC 5.1 Driver」が追加されていることを確認してください。




これでODBCドライバのインストールは完了です。次回はデータソース(ODBC)にMySQLのデータベースを登録します。

Windowsのデータソース(ODBC)には、あらかじめいくつかのODBCドライバがインストールされています。そのためExcelやAccess、Oracle、SQLServerなどのODBCドライバは標準で使えます。



しかし残念ながらMySQL用のドライバはありません。そこで今回はODBCドライバを入手する手順を解説します。MySQL用のODBCドライバはMySQLのサイトからダウンロードすることができます。

ODBCドライバのバージョンは選ぶことができるので、使っているMySQLに合わせてダウンロードしてください。

以下の図はMySQLサイトの更新やODBCドライバのバージョンアップにより変わる場合がありますのでご了承ください。


【1】以下のURLをクリックし、「MySQL」のダウンロードページにアクセスします。

http://dev.mysql.com/downloads/




【2】「Connectors」をクリックします。




【3】「Connector/ODBC」をクリックします。




【4】「5.1」をクリックします。



＊バージョンを選ぶことができます


【5】「Windows」をクリックします。




【6】Windows MSI Installer (x86) の「Download」をクリックします。




【7】ダウンロードのダイアログボックスが表示されたら、「保存」ボタンをクリックします。




【8】ダウンロードが完了し、「mysql-connector-odbc-5.1.0-alpha-win32.msi」が入手できました。(ファイル名はバージョンにより変わります)




次回はODBCドライバのインストールです。

MySQLは複数ユーザーの同時接続や大量のデータを扱えるため、大規模なシステムにも対応できる優れたデータベースです。しかしMySQLは基本的にコマンドプロンプトからCUI(Character User Interface)による操作になります。

一般のユーザーがコマンドプロンプトから直接MySQLを操作することは少なく、PHPやJavaなどのプログラミング言語で作ったシステムからGUI（Graphical User Interface)で操作します。

システムが完成したら問題ないのですが、開発中にテスト用のデータを追加したり変更したいときに、CUIではちょっと不便です。開発中はもっと簡単にデータを操作したいですね。

実はMySQLにはODBCドライバが用意されているので、Accessのようなデータベースから簡単に操作することができます。ODBC(Open DataBase Connectivity)とはデータベースにアクセスするためのAPIです。

ODBCを使えばMySQLのデータベースを、まるでAccessのテーブルのように扱うことができます。



高機能のMySQLと使いやすいAccessを組み合わせるとかなり便利です。一度設定してしまえばクエリやフォーム、レポートなどAccessの機能がそのまま使えます。

またAccessでユーザー側のシステムを開発することもできるし、Javaなどのプログラミング言語で開発する時にも、データベースが簡単に操作できると助かります。

AccessからMySQLを使えるようにするには以下の手順が必要です。

1.MySQL用のODBCドライバを入手
2.ODBCドライバのインストール
3.データソース(ODBC)を登録
4.Accessからテーブルのリンク

MySQL用のODBCドライバはMySQLのサイトからダウンロードすることができますので、次回に詳しく説明します。


【ワンポイント】

MySQLのデータベースをGUIで利用できるソフトは他にもあります。Accessの良いところはフォームやVBAを使ってプログラミングできることです。生産性のよいAccessを使えばMySQLを利用したシステムが簡単に作れます。

前回までにMySQLを学習する準備が整いました。MySQLのインストール、データベース作成、ユーザー作成、テーブル作成、データ追加まで行いましたね。

またフィールド数の少ないT01Prefecture(都道府県マスター)を例に、MySQLデータベースでの検索、追加、更新、削除を一通り体験しました。ここまで来たらあとは基本的なSQLはAccessとほとんど同じです。

このMySQL初心者入門講座で使っているデータベースと、Access SQL初心者入門講座のデータベースは、構造やデータは同じように設計しているので、SQL文がそのまま使えます。

MySQLはAccessに比べると学び始めるまでの準備が面倒ですが、一度環境を整えると同じように操作できます。プログラミング言語からデータベースを操作する場合も、MySQLとAccessではほんの数行変更するだけです。

SQLの基本的なことはAccess SQL初心者入門講座に詳しくまとめているので、利用してください。

以下の図のように「SQL SELECT文の基礎」から始めてください。


Accessの画面部分は無視して、SQL文とその解説を参考にします。ただしMySQLとAccessで異なる部分もありますので以下にまとめます。

【ワイルドカード】
あいまい検索に利用するワイルドカードが異なります。

Accessの場合
・「?」　任意の1文字
・「*」　0個以上の文字列

MySQLの場合
・「_」　任意の1文字
・「%」　0個以上の文字列


【フィールド名の変更】
AccessのT08Detailsテーブルでは「NOTE」になっていますが、MySQLでは「MEMO」に変更しました。

T08Details NOTE → MEMO

したがって以下の例のようにSQL文のフィールド名が変更になります。
Accessの場合
INSERT INTO T08Details(SLIP_CD,GOODS_CD,AMOUNT,[NOTE]) VALUES(24,1,2,'あああ');

MySQLの場合
INSERT INTO T08Details(SLIP_CD,GOODS_CD,AMOUNT,MEMO) VALUES(24,1,2,'あああ');


【文字の連結】
文字の連結はAccessの場合「&」を使いますが、MySQLの場合は「CONCAT関数」を使うところが異なります。

Accessの場合


MySQLの場合


＊CONCAT関数は引数を連結した結果の文字列を返します。
＊MySQLでは「;」を「\G」にするとレコードが縦に表示されます。

データベースごとに独自の機能があり、SQL文は全て共通というわけにはいきませんが、基本は同じです。MySQL独自の機能については中級で解説します。

前回に「学習用の環境を構築するには、私のデータベースのバックアップファイルを、あなたの環境でそのまま復元するのが一番簡単です」と書きましたが、今回はその手順を解説します。

MySQLで学習用の環境を構築するだけなら今回のほうが簡単です。前回とまったく同じデータベースが作成されるので、今回の作業は必ずしも行なう必要はありません。もちろん行なってもかまいません。

学習中に『データを最初の状態に戻したい場合』にも使えますので、一度は試しておいたほうがいいと思います。

復元に使うファイル「040backup.sql」は私のデータベースをバックアップしたもので、ダウンロードできるように準備しました。バックアップの方法は「Step14・バックアップ・復元する方法」で詳しく解説しています。

注意点としては、ダウンロードして解凍した「040backup.sql」は開かないでください。中身が数万行あるためパソコンがフリーズします。中にはテーブル作成とデータ追加のSQL文が数万行書かれています。


＊＊＊以下の手順は「SampleDB040」データベースが作成済みという条件です。＊＊＊

バックアップファイルの中にはテーブルを削除するSQL文が含まれているため、データベースにテーブルやデータがあっても実行できます。


【1】040backup.zipをダウンロードします。


【2】ダウンロードした圧縮ファイルを解凍します。



＊「040backup.sql」は絶対に開かないでください。パソコンがフリーズします。


【3】解凍したファイルを以下の場所に配置します。

C:\mysql\data\sampledb040


＊「\」はWindowsではエンマークのことです。


ここからはデータベースの復元を行なう手順です。

【4】MySQLを開始します。

1.コマンドプロンプトを起動します。
2.「net start mysql」と入力し、「Enterキー」を押します。



＊まだ接続はしません。


【5】「pgtop」ユーザーでデータベースの復元を行ないます。

1.以下のように入力し、「Enterキー」を押します。
mysql -u pgtop -p SampleDB040 < "C:\mysql\data\sampledb040\040backup.sql"

2.パスワードを求められるので「12345」入力し、「Enterキー」を押します。



エラーが出なければデータベースの復元は完了です。バックアップファイルの容量が大きいわりに、短時間で完了しますね。


【6】確認のためMySQLに接続します。

1.「mysql -u pgtop -p -b」と入力し、「Enterキー」を押します。
2.パスワードを求められるので「12345」入力し、「Enterキー」を押します。




【7】以下のSQL文を入力し、使用するデータベースを選択します。

USE SampleDB040;



【8】以下のSQL文を入力し、データベース内にあるテーブルを確認します。

SHOW TABLES;



【9】あとはテーブルの構造とデータを確認してください。

例
DESC T01Prefecture;
SELECT * FROM T01Prefecture;


【10】以下の図を参考にMySQLを終了・停止します。

1.「exit」と入力し、「Enterキー」を押します。
2.「net stop mysql」と入力し、「Enterキー」を押します。




【11】コマンドプロンプトは閉じてください。


【12】「C:\mysql\data\sampledb040」に配置した、「040backup.sql」はもう要らないので削除してください。

＊MySQLが作成したファイルを削除しないように注意。


【ワンポイント】

はじめの部分でも書きましたが、今回の方法は『データを最初の状態に戻したい場合』にも使えます。

MySQLでデータベースの学習を進めていくと、データの追加、更新、削除を行なうことで、どんどんデータが書き換えられます。そんなときに最初の状態に戻せる方法を知っておくと便利です。

間違えてデータを削除してもすぐに復元できるので、安心してデータベースを操作してください。

MySQLの学習に使うデータベースは、テーブル数が8つで、レコード数は多いもので12万件を超えるので、テーブル作成やデータの追加を手作業で行なうのは大変です。

そこで複数のSQL文をファイルにまとめて、バッチ処理でデータベースを構築することにします。仕組みは以前にやったデータベースの復元(リストア)と同じです。

学習用の環境を構築するには、私のデータベースのバックアップファイルを、あなたの環境でそのまま復元するのが一番簡単ですが、それではあなたがデータベースを作る時に応用できません。

今回はSQL文をまとめた「バッチ処理用ファイル」と対応する「CSVファイル」を用意してテーブル作成とデータの追加を一気に行なう方法を解説します。

データの追加はINSERT文を使う方法もありますが、大量のデータの場合はCSVファイルのほうがデータを準備しやすいです。

このSQL文とCSVファイルを変えるだけで、あなたのオリジナルデータベースを作るときにも使えます。


＊＊＊以下の手順は「SampleDB040」データベースが作成済みという条件です。＊＊＊


【1】SampleDB040.zipをダウンロードします。


【2】ダウンロードした圧縮ファイルを解凍します。




【3】解凍したファイルを以下の場所に配置します。

C:\mysql\data\sampledb040


＊「\」はWindowsではエンマークのことです。


ここからはバッチ処理を行なう手順です。

【4】MySQLを開始します。

1.コマンドプロンプトを起動します。
2.「net start mysql」と入力し、「Enterキー」を押します。



＊まだ接続はしません。


【5】「root」ユーザーでテーブルの作成とデータの追加を行ないます。

1.以下のように入力し、「Enterキー」を押します。
mysql -u root -p SampleDB040 < "C:\mysql\data\sampledb040\sampledb040.sql"

2.rootのパスワードを求められるので入力し、「Enterキー」を押します。



エラーが出なければテーブルの作成とデータの追加は完了です。


【6】確認のため「pgtop」ユーザーでMySQLに接続します。

1.「mysql -u pgtop -p -b」と入力し、「Enterキー」を押します。
2.パスワードを求められるので「12345」入力し、「Enterキー」を押します。




【7】以下のSQL文を入力し、使用するデータベースを選択します。

USE SampleDB040;



【8】以下のSQL文を入力し、データベース内にあるテーブルを確認します。

SHOW TABLES;


新しいテーブルが8つ作成されていますね。


【9】テーブルの構造を確認します。「sampledb040.sql」の CREATE文と比べてみてください。

DESC T01Prefecture;
DESC T02City;
DESC T03Town;
DESC T04Customer;
DESC T05Sex;
DESC T06Goods;
DESC T07Slip;
DESC T08Details;


【10】テーブルのデータを確認します。

SELECT * FROM T01Prefecture;
SELECT * FROM T02City;
SELECT * FROM T03Town;
SELECT * FROM T04Customer\G
SELECT * FROM T05Sex;
SELECT * FROM T06Goods;
SELECT * FROM T07Slip;
SELECT * FROM T08Details;

＊T03Townはレコード数が12万件を超えるので表示に数分かかります。できればWHERE句を付けて表示してください。
例　WHERE TOWN_CD BETWEEN 1 AND 10000;

＊T04Customerのようにフィールド数が多い場合は「\G」を付けるとレコードごとに表示されます。



【11】以下の図を参考にMySQLを終了・停止します。

1.「exit」と入力し、「Enterキー」を押します。
2.「net stop mysql」と入力し、「Enterキー」を押します。




【12】コマンドプロンプトは閉じてください。


【13】「C:\mysql\data\sampledb040」に配置した、sampledb040.sql と csvファイルはもう要らないので削除してください。

＊MySQLが作成したファイルを削除しないように注意。


【解説】

「sampledb040.sql」のSQL文については、ほとんど同じパターンの繰り返しなので、１つだけ取り上げます。

(1)テーブルが既にある場合は削除します。インポートが失敗した場合に、テーブルが残っていても再度実行できるようにするためです。

DROP TABLE IF EXISTS T01Prefecture;


(2)テーブルを作成しています。各フィールドの容量は大きめに見積もっています。

CREATE TABLE T01Prefecture (
PREF_CD INT,
PREF_NAME VARCHAR(10),
PRIMARY KEY (PREF_CD)
);

＊INTはデフォルトで -2147483648 〜 2147483647 の整数。


(2)テーブルにファイルからデータを取り込んでいます。

LOAD DATA INFILE "T01Prefecture.csv"
INTO TABLE T01Prefecture FIELDS TERMINATED BY ","
LINES TERMINATED BY "\r\n";

「C:\mysql\data\sampledb040」に配置するとファイル名だけの指定で済みます。


このように「バッチ処理用ファイル」と対応する「CSVファイル」を用意すると、簡単にデータを取り込めます。

今回はこれまでにMySQLで作成してきたものを削除する手順を解説します。基本的には逆の操作になります。

削除の操作ができると、また新しく作ることができます。設定を間違えてもやり直せばいいだけなので、心に余裕を持ってデータベースを扱えます。

何をするための操作か明確にするため、それぞれ「確認 → 削除 → 確認」という流れで行なっています。また本来はデータベースごと削除すれば済む操作も、フィールドの削除から行なっていますのでご了承ください。

MySQLに接続するユーザーは「root」ですので、間違えないように気を付けてください。


【1】以下の図を参考にMySQLを開始・接続します。

1.コマンドプロンプトを起動します。
2.「net start mysql」と入力し、「Enterキー」を押します。
3.「mysql -u root -p -b」と入力し、「Enterキー」を押します。
4.パスワードを求められるので入力し、「Enterキー」を押します。



＊「-b」オプションを付けて起動するとビープ音が出なくなります。


【2】以下のSQL文を入力し、ユーザーを確認します。

SELECT user FROM mysql.user;



これから「test」ユーザーの権限の取消し、削除を行います。

＊データベースを選択していないため、「mysql.user」と指定する必要があります。
(データベース名.テーブル名)


【3】以下のSQL文を入力し、「test」ユーザーの権限を確認します。

SHOW GRANTS FOR test@localhost;



「test」ユーザーはSampleDB040データベースの全てのテーブルに対し、SELECT権限があることがわかります。


【4】以下のSQL文を入力し、「test」ユーザーの権限を取り消します。

REVOKE SELECT ON SampleDB040.* FROM test@localhost;




【5】もう一度以下のSQL文を入力し、「test」ユーザーの権限を確認します。

SHOW GRANTS FOR test@localhost;



「test」ユーザーの権限が取り消されています。


【6】以下のSQL文を入力し、「test」ユーザーを削除します。

DROP USER test@localhost;




【7】もう一度以下のSQL文を入力し、ユーザーを確認します。

SELECT user FROM mysql.user;



「test」ユーザーが削除されています。


【8】以下のSQL文を入力し、使用するデータベースを選択します。

USE SampleDB040;




【9】以下のSQL文を入力し、テーブルの構造を確認します。

DESC T01Prefecture;



これから「AREA_CD」を削除します。


【10】以下のSQL文を入力し、フィールドを削除します。

ALTER TABLE T01Prefecture DROP AREA_CD;




【11】もう一度以下のSQL文を入力し、テーブルの構造を確認します。

DESC T01Prefecture;



「AREA_CD」が削除されています。


【12】以下のSQL文を入力し、テーブルのデータを確認します。

SELECT * FROM T01Prefecture;



「AREA_CD」フィールドが無くなり、データも消えています。


【13】以下のSQL文を入力し、データベース内にあるテーブルを確認します。

SHOW TABLES;




【14】以下のSQL文を入力し、テーブルを削除します。

DROP TABLE T00Area;




【15】もう一度以下のSQL文を入力し、データベース内にあるテーブルを確認します。

SHOW TABLES;



「T00Area」テーブルが削除されています。


【16】同様にT01Prefectureテーブルも削除します。

DROP TABLE T01Prefecture;


【17】もう一度以下のSQL文を入力し、データベース内にあるテーブルを確認します。

SHOW TABLES;



もうテーブルが無いのでデータベースの中は空です。


【18】以下のSQL文を入力し、MySQLのデータベースを確認します。

SHOW DATABASES;




【19】データベースを削除する時は「DROP DATABASE SampleDB040;」を使いますが、今後もこのデータベースを使用しますので、そのままにしておきます。



＊データベースを削除する時「C:\mysql\data\sampledb040」の中に、MySQLが自動的に作成したファイル以外(ユーザーが置いたファイルなど)があるとエラーになりますので、移動してください。ただしMySQLが作成したファイルはそのまま残します。


【20】以下の図を参考にMySQLを終了・停止します。

1.「exit」と入力し、「Enterキー」を押します。
2.「net stop mysql」と入力し、「Enterキー」を押します。




【21】コマンドプロンプトは閉じてください。


【ワンポイント】

既に削除されているテーブルを削除しようとするとエラーになります。

DROP TABLE T00Area;



しかし以下のように書くと、テーブルがあるときにしか削除しません。警告は出ますがエラーになりません。

DROP TABLE IF EXISTS T00Area;



バックアップファイルにはこのように書かれています。


次回はこの空のデータベースに複数のテーブルとデータをバッチ処理で追加します。

データベースを使っていると何らかのトラブルが原因で、これまでに蓄えたデータを失う可能性もあります。数十件くらいなら再度入力できますが、数千、数万件のレコード数になると大変な作業になります。しかも元のデータがない場合はもう不可能です。

そんな時のためにMySQLにはデータベースをバックアップ・復元(リストア)する機能があります。

コンピュータのハードディスクは消耗品ですので、いつかは壊れることを前提に考えて、定期的にバックアップしておけば、いざという時に復元できるので安心です。

データベースをバックアップ・復元するには、その権限を持ったユーザーで行ないます。「root」でも「pgtop」でもOKです。

今回はMySQLの接続、終了を繰り返します。手順がこれまでと違いますので気を付けてください。接続するユーザーは「pgtop」とします。


【1】MySQLを開始します。

1.コマンドプロンプトを起動します。
2.「net start mysql」と入力し、「Enterキー」を押します。



＊まだ接続はしません。


【2】データベースのバックアップを行ないます。

1.以下のように入力し、「Enterキー」を押します。
mysqldump -u pgtop -p SampleDB040 > "C:\mysql\data\sampledb040\backup.sql"

2.パスワードを求められるので「12345」入力し、「Enterキー」を押します。



バックアップはこれだけで完了です。簡単ですね。


【3】指定した場所にバックアップされているか確認してみましょう。
＊「\」はWindowsではエンマークのことです。

C:\mysql\data\sampledb040


バックアップファイルをTeraPadで開いた様子


バックアップファイルの中身は複数のSQL文だということがわかります。


【4】次はMySQLに接続します。

1.「mysql -u pgtop -p -b」と入力し、「Enterキー」を押します。
2.パスワードを求められるので「12345」入力し、「Enterキー」を押します。



＊「-b」オプションを付けて起動するとビープ音が出なくなります。


【5】以下のSQL文を入力し、使用するデータベースを選択します。

USE SampleDB040;



【6】以下のSQL文を入力し、データベース内にあるテーブルを確認します。

SHOW TABLES;


2つのテーブルがありますが、これから削除します。


【7】以下のSQL文を入力して実行し、データベース内にあるテーブルを削除します。

DROP TABLE T01Prefecture;
DROP TABLE T00Area;




【8】もう一度データベース内にあるテーブルを確認すると、データベースは空です。

SHOW TABLES;



【9】いったんMySQLを終了します。




ここからは復元(リストア)を行なう手順です。

【10】指定する場所「C:\mysql\data\sampledb040」にバックアップするファイル「backup.sql」があることを確認します。



＊「\」はWindowsではエンマークのことです。


【11】データベースの復元を行ないます。

1.以下のように入力し、「Enterキー」を押します。
mysql -u pgtop -p SampleDB040 < "C:\mysql\data\sampledb040\backup.sql"

2.パスワードを求められるので「12345」入力し、「Enterキー」を押します。



これでデータベースの復元が完了しました。


【12】確認のためMySQLに接続します。

1.「mysql -u pgtop -p -b」と入力し、「Enterキー」を押します。
2.パスワードを求められるので「12345」入力し、「Enterキー」を押します。




【13】以下のSQL文を入力し、使用するデータベースを選択します。

USE SampleDB040;



【14】以下のSQL文を入力し、データベース内にあるテーブルを確認します。

SHOW TABLES;



テーブルが元に戻っています。テーブルの中身はどうでしょうか？

【15】以下のSQL文を入力し、テーブルのデータを確認します。

SELECT * FROM T01Prefecture;


SELECT * FROM T00Area;


テーブルの中身も元に戻っていますね。


【16】以下の図を参考にMySQLを終了・停止します。

1.「exit」と入力し、「Enterキー」を押します。
2.「net stop mysql」と入力し、「Enterキー」を押します。




【17】コマンドプロンプトは閉じてください。


【解説】

(1)バックアップについて
mysqldump -u pgtop -p SampleDB040 > "C:\mysql\data\sampledb040\backup.sql"

ファイルの拡張子は「.sql」でなくてもかまいません。

パスを省いてファイル名だけ指定すると、Cドライブの直下にバックアップされます。
例　mysqldump -u pgtop -p SampleDB040 > "backup.sql"


(2)復元について
mysql -u pgtop -p SampleDB040 < "C:\mysql\data\sampledb040\backup.sql"

「backup.sql」の中身はSQL文です。つまりこのコマンドはSQL文をバッチ処理しているだけですので、復元以外にも使えます。

＊復元するにはデータベースは作成済みでなければならないため、テーブルは削除しましたがデータベースは削除しませんでした。


(3)「backup.sql」の中身
前半はほとんどコメントなのでSQL文の部分だけ説明します。

・既にテーブルが存在する場合は削除します。
DROP TABLE IF EXISTS `t00area`;
＊「`」は半角で「SHIFTキー＋@」です。書かなくても大丈夫です。

・テーブルを作成しています。
CREATE TABLE `t00area` (
`AREA_CD` int(3) NOT NULL default '0',
`AREA_NAME` varchar(10) default NULL,
PRIMARY KEY (`AREA_CD`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis;

＊「ENGINE=InnoDB」はデータベースのタイプ、「DEFAULT CHARSET=sjis;」は文字コードですが、書かなくてもデフォルトで設定されます。


・テーブルをロックしてデータを追加します。
LOCK TABLES `t00area` WRITE;
/*!40000 ALTER TABLE `t00area` DISABLE KEYS */;
INSERT INTO `t00area` VALUES (1,'北海道・東北'),(2,'関東'),(3,'信越・北陸'),(4,'東海'),(5,'近畿'),(6,'中国'),(7,'四国'),(8,'九州・沖縄');
/*!40000 ALTER TABLE `t00area` ENABLE KEYS */;
UNLOCK TABLES;


T01Prefectureについても上記と同様です。

MySQLのようなリレーショナルデータベースでは、複数のテーブルを結合することができます。

今回は「T01Prefectureテーブル」と「T00Areaテーブル」を結合して、まるで一つのテーブルのように表示してみます。

テーブルの結合には同じ値のフィールド同士を使います。結合に使われる「AREA_CD」のようなフィールドをキーと言います。

SELECT * FROM T01Prefecture;
SELECT * FROM T00Area;


結合の種類には、内部結合(インナージョイン)、外部結合(アウタージョイン)、自己結合(セルフジョイン)、交差結合(クロスジョイン)、和結合(ユニオン)などがあります。

今回は最もよく使われる内部結合(インナージョイン)を試してみましょう。内部結合は等結合とも言い、両方のテーブルにあるキーが一致したレコードだけが表示されます。


MySQLに接続するユーザーは「pgtop」ですので、間違えないように気を付けてください。


【1】以下の図を参考にMySQLを開始・接続します。

1.コマンドプロンプトを起動します。
2.「net start mysql」と入力し、「Enterキー」を押します。
3.「mysql -u pgtop -p -b」と入力し、「Enterキー」を押します。
4.パスワードを求められるので「12345」入力し、「Enterキー」を押します。



＊「-b」オプションを付けて起動するとビープ音が出なくなります。


【2】以下のSQL文を入力し、使用するデータベースを選択します。

USE SampleDB040;



【3】テーブルを内部結合します。以下のSQL文を入力し、「Enterキー」を押します。






【4】両方のテーブルにあるキーが一致したレコードが表示されます。



この例では NULL や 片方にだけあるデータは無いので、全件表示されます。


【5】以下のSQL文でもまったく同じ結果になります。WHERE句を使いフィールドのデータが同じレコードを抽出しています。等結合という感じがしますね。





＊「INNER JOIN」は結合するテーブル数が増えると複雑になるので、「WHERE」を使ったほうがシンプルです。


【6】テーブルを結合して、さらにレコードを絞り込みたいときは「AND」を使い抽出条件を続ければ簡単にできます。「AND」は複数使うことができます。

SELECT PREF_CD,PREF_NAME,T01Prefecture.AREA_CD,AREA_NAME
FROM T01Prefecture, T00Area
WHERE T01Prefecture.AREA_CD = T00Area.AREA_CD
AND PREF_CD BETWEEN 1 AND 7;




【7】以下の図を参考にMySQLを終了・停止します。

1.「exit」と入力し、「Enterキー」を押します。
2.「net stop mysql」と入力し、「Enterキー」を押します。




【8】コマンドプロンプトは閉じてください。


【解説】

(1)AREA_CDのようなフィールドをキーといいますが、テーブルによって役割が異なっています。

・T00AreaのAREA_CDは主キー　重複できずレコードを一意に特定できる
・T01PrefectureのAREA_CDは外部キー　重複でき他のテーブルと結合するために使う


(2)テーブルを結合する時、両方に同じ名前のフィールドがある場合は、「テーブル名.フィールド名」の形で書かなければなりません。そうしないとどちらのテーブルのフィールドなのかコンピュータが判断できないからです。

T01Prefecture.AREA_CD
T00Area.AREA_CD

あらかじめ別の名前を付けておけばフィールド名だけでかまいませんので、SQL文がシンプルになります。

P_AREA_CD
A_AREA_CD


またはFROMでテーブルに別名をつける方法もあります。

SELECT PREF_CD,PREF_NAME,P.AREA_CD,AREA_NAME
FROM T01Prefecture P, T00Area A
WHERE P.AREA_CD = P.AREA_CD
AND PREF_CD BETWEEN 1 AND 7;

MySQLではSQL文を使い、既存のテーブルへフィールドを追加したり、フィールド名の変更、データ型の変更、フィールドを削除することが簡単にできます。

今回は既存の T01Prefecture テーブルに新たに AREA_CD というフィールドを追加します。そしてエリアごとに一度にまとめてレコードを更新する方法を解説します。レコードをまとめて更新したり削除するには、「WHERE句」の使い方が大事です。

T01Prefecture の AREA_CD には数値を保存しますが、後でT00Area テーブルを参照することでエリア名を表示できます。

各都道府県は以下のエリアに分類することにします。

1　北海道・東北　1〜7
2　関東　8〜15
3　信越・北陸　16〜20
4　東海　21〜24
5　近畿　25〜30
6　中国　31〜35
7　四国　36〜39
8　九州・沖縄　40〜47


MySQLに接続するユーザーは「pgtop」ですので、間違えないように気を付けてください。


【1】以下の図を参考にMySQLを開始・接続します。

1.コマンドプロンプトを起動します。
2.「net start mysql」と入力し、「Enterキー」を押します。
3.「mysql -u pgtop -p -b」と入力し、「Enterキー」を押します。
4.パスワードを求められるので「12345」入力し、「Enterキー」を押します。



＊「-b」オプションを付けて起動するとビープ音が出なくなります。


【2】以下のSQL文を入力し、使用するデータベースを選択します。

USE SampleDB040;



【3】以下のSQL文を入力し、データベース内にあるテーブルを確認します。

SHOW TABLES;


今回は T01Prefecture テーブルを操作します。


【4】以下のSQL文を入力し、テーブルの構造を確認します。

DESC T01Prefecture;


まだフィールドは2つだけです。


【5】新しいフィールドを追加します。以下のSQL文を入力し、「Enterキー」を押します。

ALTER TABLE T01Prefecture ADD AREA_CD INT(3);




【6】メッセージが表示され、フィールドが追加されました。