データベース　３日目

テーブルの構成を変更する
ALTER TABLE

※後からの変更はなるべくしない方がよい。
　どうしても変更が必要な時に使うようにする。

フィールドの追加（ADD）

既存のテーブルに新たなフィールドを追加する場合は、次の書式を使う。

ALTER TABLE テーブル名 ADD フィールド名 型 DEFAULT 値;

goodsテーブルに在庫数を管理するためのフィールド「stock」を追加する。
データ型は「INT」、値が入力されなかった場合の初期値(DEFAULT)は「0」とする。

SHOW COLUMNS文を使って「stock」フィールドが追加されたことを確認する。

初期値を省略してフィールドを追加した場合は、既存レコードに「NULL」が入る。

フィールドのデータ型の変更（MODIFY）

既存のテーブルのデータ型や格納できる文字数の変更は、次の書式を使う。

ALTER TABLE テーブル名 MODIFY フィールド名 新しい型;

goodsテーブル内のitemフィールドの文字数を20字に変更する。

以前フィールドに入りきらなかったidフィールド「2」の「ポテトチップスバターしょうゆ味」を再度変更する。

今回は文字数を増やしたので文字列が切り捨てられず変更された。

（実際やることはないが、確認だけ）

文字数を減らすのは危険！！

20字で管理されているitemフィールドを10字に戻してみる。

「ポテトチップスバターしょうゆ味」が新しいデータ型で指定した10字を超えてしまうため、Warningがカウントされている。

SELECTで表示させると10字を超えた文字列が切り捨てられている。

225字以上の値を管理したい場合

CHAR型は最大255文字までしか保存できないので、それ以上の長さの文字列を保持したい時はVARCHAR型、またはTEXT型に変更する。

フィールド型とデータ型の変更（CHANGE）

MODIFYはデータ型のみの変更
CHANGEはフィールド名とデータ型両方を変更できる

ALTER TABLE テーブル名
CHANGE 旧フィールド名 新フィールド名 新データ型;

goodsテーブルのitemフィールドをitem_nameという名前に変更し、データ型を最大255字の可変長文字列に設定する。

フィールドが変更されたかSHOW COLUMNSで確認する。
（DESC goods;でもOK）

フィールド名だけ変更したい

フィールド名だけ変更したい場合は、現状のデータ型をそのまま指定する。
item_nameフィールドをデータ型は変更せずに元のフィールド名「item」に戻す。

フィールドの削除（DROP）

テーブル内の不要なフィールドを削除するにはDROPを使う。

ALTER TABLE テーブル名 DROP フィールド名;

goodsテーブルのkeywordフィールドを削除する。

問題11：stockフィールドを削除

テーブル名の変更（RENAME AS）

ALTER TABLE 旧テーブル名 RENAME AS 新テーブル名;

まず、現在のテーブルをSHOW TABLE文で確認する。

goodsテーブルをRENAME ASの書式を使って「commodity」という名前に変更する。

SHOW TABLE文を使って確認。

リレーション

商品を管理する commodity テーブルがある。
この商品を納入する業者も管理したいと考えた時、どうしたらいい？

commodityテーブルにメーカー用フィールドを追加して管理する方法が考えられる。

しかし、この方法でレコードを管理すると「浦安製菓」のように複数の商品を納入している場合、同じ住所や電話番号を何度も登録しなければいけない。

このような場合は、「商品管理テーブル」と「メーカー管理テーブル」を分けて管理し、必要な時にテーブルを連結して使用する。

リレーションの章で使用するテーブル

商品管理用のcommodityテーブル
メーカー管理用のtraderテーブル

テーブルを結びつけるためのフィールド

関連性のあるテーブルを結合するにはテーブル同士を結びつけるフィールドを作成する。

連結用フィールドの作成

commodityテーブルに、商品とメーカーを結びつけるために使用するmakerフィールドを追加する。

追加した直後のmakerフィールドにはNULLがセットされている。

商品とメーカーの結びつけに利用するため、makerフィールドにはtraderテーブルにあるid番号をセットしていく。

商品とメーカーの対応表は以下のとおり。「たまねぎ」と「NULL」はメーカーを指定せず、makerフィールドをNULLのままにしておく。

これでリレーションの準備完了。

テーブルの結合：内部結合
JOIN

関連性を持った連結用フィールドを使って、二つのテーブルを結合して表示する場合は次の書式を使う。

【書式1】
SELECT フィールド名 FROM テーブル名1　JOIN テーブル名2
ON テーブル1.照合用フィールド名=テーブル2.照合用フィールド名;

【書式2】
SELECT フィールド名 FROM テーブル名1, テーブル名2
WHERE テーブル1.照合用フィールド名=テーブル2.照合用フィールド名;

書式1：二つのテーブルを結合して表示する

SELECT * FROM commodity JOIN trader ON commodity.maker = trader.id;

【参考】必要なフィールドのみ表示する

-- ID番号を表示する
SELECT id, item, company,tel
FROM commodity JOIN trader
ON commodity.maker = trader.id;
-- ERROR 1052 (23000): Column 'id' in field list is ambiguous
-- ambiguous → あいまい
-- commodityテーブルにもtraderテーブルにも
-- idフィールドがあるので、DB管理人が困っている
-- ★どのテーブルのidフィールドか、明確に指定する
-- 「テーブル名.フィールド名」
SELECT commodity.id, item, company,tel
FROM commodity JOIN trader
ON commodity.maker = trader.id;

書式2：二つのテーブルを結合して表示する

SELECT * FROM commodity, trader WHERE commodity.maker = trader.id;

このように関連のあるテーブル同士を連結してデータ検索させることを「リレーションを張る」という。

テーブル名のショートカット

照合用フィールド名に毎回テーブル名を書くと長いSQL文になりがち。次のようにテーブル名のショートカットを使って短くすることができる。

SELECT * FROM commodity c, trader t WHERE c.maker = t.id;

FROM部分の「commodity c」でcommodityをc、「trader t」でtraderとtと短縮することを宣言している。以降、「c.maker」は「commodity.maker」、「t.id」は「trader.id」と同じ意味を持つ。

テーブルの結合：外部結合
LEFT JOIN,RIGHT JOIN

内部結合の場合、照合できないレコードは表示されない。外部結合を使用すると照合できなかったレコードも表示させることができる。
commodityテーブルの商品名「たまねぎ」と「NULL」は連結用のmakerフィールドがNULLのため表示されていない。同様にtraderテーブルの「ハイサイパン」や「出雲ファーム」も該当する商品がないため表示されない。

左外部結合

左のテーブルをすべて表示した上で、右テーブルと結合する。左テーブルは「LEFT JOIN」の左側に記述したテーブルを指し、今回はcommodityテーブルになる。右テーブルは「LEFT JOIN」の右側に記述したテーブルでtraderテーブルになる。

左テーブル（commodityテーブル）は全て表示されるので、内部結合では表示されなかった「たまねぎ」と「NULL」が表示される。

右外部結合

新規ユーザの追加（権限追加）
GRANT

XAMPPはインストール時に管理者（root）アカウントを作成する。しかし、このアカウントはDBに関するすべての権限を持っているため悪用されると取り返しがつかない。アプリケーション用に権限を制限した新しいユーザを作って運用する。

GRANT 権限1,権限2・・・
ON データベース領域名.テーブル名
TO ユーザ名@ホスト名 IDENTIFIED BY パスワード;

■代表的な権限

権限を制限したユーザを作る

lessonデータベース領域のcommodityテーブルに対してテーブル参照権限とレコード追加権限を持つユーザを作る。ユーザ名を「DBWriter」、パスワードを「Kakikomi」とする。ユーザ名とパスワードは大文字と小文字を違う文字として扱うので間違えないようにする。

★黒画面がユーザ「root」、赤画面がユーザ「DBWriter」

使用できるDB領域

SHOW DATABASESを使ってDB領域を表示させてみる。

使用できるテーブル

USEでlessonDB領域を選択した後、SHOW TABLEを使ってテーブルを表示させる。ユーザ作成時に指定したテーブル「commodity」のみ表示される。

DBを操作する権限

次にDBを操作する権限について確認する。まずは「フランスパン」のレコードを追加する。レコードの追加を実行した後、確認のためSELECT文でテーブルの内容を表示させる。

DBWriterはSELECTとINSERTの権限を持っているため、どちらも実行できる。

次に、DELETE文を使って追加した「フランスパン」を削除してみる。

DBWriterはDELETEを実行する権限を持っていないのでエラーメッセージが表示される。

このようにアクセス権限や実行権限を絞ったユーザを作成して運用することで、不正なアクセスや不用意なコマンドの実行を防ぐことができる。

データベース領域のみ制限したユーザを作る

lessonデータベース領域に対してすべてのコマンドを実行できるユーザを作成する。ユーザ名は「LessonMaster」、パスワードは「LMPass」にする。
※localhostで使用するユーザなのでユーザ名の後に「@localhost」を忘れない！

★黒画面がユーザ「root」、紫画面がユーザ「LessonMaster」

使用できるDB領域

使用できるテーブル

DBを操作する権限

LessonMasterは捜査権限「ALL」でユーザを作成しているので、lessonデータベース領域に対してすべてのコマンドを実行できる。先ほど追加した「フランスパン」を削除してみる。

先ほど「DBWriter」ではできなかった削除コマンドも実行できる。

ユーザ情報を表示

SELECT * FROM mysql.user;

ユーザ名を管理しているuserフィールドとホスト名を管理しているhostフィールドを表示してみる。

ユーザ名を表示する

-- ユーザ情報を表示する
--これだとフィールドが多すぎて表示がｳﾞｧｰｰっとなる
SELECT * FROM mysql.user;
-- 必要なフィールドのみ表示する
SELECT user, host FROM mysql.user;

今まで作成したユーザ名が表示された。

フィールド数が多すぎてうまく表示されない時の対処法

-- フィールド数が多すぎてうまく表示されない時の対処法
-- SQL文の最後に「¥G」オプションを付ける
SELECT * FROM mysql.user \G;
-- すべてのフィールド情報が改行されて表示される

ユーザの権限を表示する

SHOW GRANTS FOR ユーザ名@ホスト名;

2行目にユーザ情報が表示される

ユーザの削除
DROP USER

DROP USER ユーザ名@ホスト名;

-- ◆ユーザの削除◆
-- 削除前にユーザ情報を確認
SELECT user, host FROM mysql.user;

--ユーザの削除
DROP USER DBWriter@localhost;

-- 削除後にユーザ情報表示
SELECT user, host FROM mysql.user;

phpMyadminの設定

MariaDBの使い方の学習として、まずはコマンドプロンプトから命令を入力して確認した。
実際に利用する際には1つ1つコマンドを入力していくのは大変なので、ブラウザから簡単に操作できるようにXAMPPでは「phpMyadmin」というツールがある。

★VSCodeで開く

/* Authentication type and info */
// $cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'admin';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = true;
$cfg['Lang'] = '';

データベース領域のバックアップ

◆phpMyadminを使ってバックアップを取得
◆phpMyadminの設定は教科書8ページを参照

0.XAMPP「Apache」「MySQL」を起動

1.ブラウザからphpMyadminにアクセス
  http://localhost/phpmyadmin/

2.左メニューからバックアップを取得したい
  DB領域をクリックして選択

3.上部メニュー「エクスポート」をクリック

4.設定は変更せず、画面下部の「実行」をクリック

5.バックアップファイルからDLされる
  選択したDB領域内のテーブル情報のみバックアップされている
  ・テーブル作成
  ・テーブルにレコード挿入
 （以下は含まれていない）
  ・DB領域作成
  ・既存テーブルの削除

テーブルの削除
DROP TABLE

DROP TABLE テーブル名;

テーブルを削除する

削除済み(存在しない)テーブルを削除しようとするとエラーが出る。
「もし存在したら削除する」という記述をするとエラーが出ない。

-- テーブルを削除
-- テーブルが存在しないとエラーが出る
DROP TABLE stationery;

-- テーブルの存在を確認して削除
-- スクリプトファイル等でよく使う
-- 対象テーブルが存在しなくてもエラーを出さない
DROP TABLE IF EXISTS stationery;

SHOW TABLES;

データベース領域の削除
DROP DATABASE

DROP DATABASE データベース領域名;

データベース領域を削除する

-- ◆DB領域の削除◆

-- DB領域の確認
SHOW DATABASES;
-- DB領域の削除
-- DBが動かなくなる可能性があるので
-- 元からある(自分で作ったのではない)DB領域は
-- 削除しないこと！
DROP DATABASE lesson;
-- DB領域の確認
SHOW DATABASES;

バックアップファイルを使用して復元する

phpMyadminから復元する。

1.DB領域を作成する
2.インポート→バックアップファイルを選択して実行
　（授業ではXAMPP・htdocs・databaseの中）

◆phpMyadminを使って復元する
0.XAMPP「Apache」「MySQL」を起動

1.ブラウザからphpMyadminにアクセス
  http://localhost/phpmyadmin/

2.DB領域が「1.ある」「2.ない」

  2-1.DB領域がある：既存のテーブルを削除する
    1.左メニューから対象のDB領域選択
    2.メイン画面下部の「すべてチェックする」にチェック
    3.セレクトボックスから「削除」を選択
    4.DROP TABLEを【実行】して既存テーブルを削除

  2-2.DB領域がない：DB領域作成
    1.左メニュー「新規作成」
    2.メイン画面でDB領域名を入力
    3.「作成」ボタンをクリックしてDB領域作成

3.左メニューから対象DB領域を選択
  (誤って違うDB領域を選択していないよう注意)

4.画面上部「インポート」を選択

5.「ファイルを選択」からバックアップファイルを選択

6.画面下部の【実行】をクリック
  ※SQLファイル実行には時間がかかるのでしばし待つ

  ★テーブル復元完了★