カテゴリー: MySQL

DBのテーブルでの構造について
すべてのデータを1つのテーブルにまとめようとして
各カラムには値を1つだけというようにした場合
あるカラムで
同じデータが複数回
繰り返して格納されるというような必要性がでるときもある
このようなデータが何度も繰り返し入力されるのを解決するのが
正規化
これは同じデータを複数回繰り返すという状態を解決するために
テーブルを増やす操作のこと
正規化は
第1～５正規化
とか
ボイス・コッド正規化がある
一般的には第3正規化までが多いらしい

10日でおぼえるFacebookアプリ開発入門教室 (10日でおぼえるシリーズ)
を参考に、
phpMyAdmin にログインし
SQL を実行したところ、
書籍では　種別のところが
InnoDB
となっていたのに、
実際に実行したら
MyISAM
となっていたので、これを調べてみました
参考サイトは
MyISAMとInnoDB
どうやら
MyISAMはMySQLのデフォルトストレージエンジンで、
ストレージエンジンを指定せずにテーブルを作成すると
MyISAMが選択されるようです

DBに記録するには INSERT文を使います
構文は
INSERT INTO テーブル名 VALUES(データ);
これは
データを単純に追加する
データのところには
テーブルを定義した際に
指定したカラムの順番で
データを , で区切って記述します
次に
INSERT INTO テーブル名 VALUES(データ) ON
DUPLICATE KEY UPDATE カラム名 = データ
これだと
データを追加する際に重複値の生成を避ける
こちらの場合
primary key のカラムにすでに同じ値の
データがあった場合、特定のカラムデータを書き換えるように支持する
ようにします

構文は
SELECT カラム名 FROM テーブル名 WHERE 条件
ほとんどの場合、例題だと
カラム名は　＊
これは、全部を意味しています
ちなみに、
＊
ではなくて、
集合関数とよばれる
SUM
MAX
MIN
AVG
COUNT
などの指定もできます
集合関数は、EXCEL などでも使うので
馴染みがあるかもしれません

今日はちょっと難しめですが、フォームから入力した情報をDBへ保存する場合の処理です
$sql = sprintf(‘INSERT INTO my_items SET maker_id=%d,
item_name=“%s”, price=%d, keyword=“%s”‘,
mysql_real_escape_string($_POST[‘maker_id’]),
mysql_real_escape_string($_POST[‘item_name’]).
mysql_real_escape_string($_POST[‘price’]),
mysql_real_escape_string($_POST[‘keyword’])
);
mysql_query($sql) or die(mysql_error());
このように
%d や　%s というようにすれば、入力した数値や文字などを
DBへ POST 送信できるようになります
ちなみに、%s が “” で囲ってあるのは、文字列だからです
%d は数値
%s は文字列の入力に使います
C 言語とかでも使いますので覚えておくと便利です
$_POST は POST 送信するという定数です
GET と POST がありますが
GET だと
GETでは日本語などの全角文字を送信できない
とか
GETには送信できる情報量に制限がある
そして
GETの場合は送信内容がURLとして渡される
つまり内容丸見えです
これは非常にセキュリティを考えるとまずいので
極力POSTを使いましょう
もっと詳しい解説として
GETとPOSTの違い
に解説が載っています
また、重要なことですが
mysql_real_escape_string() は
SQLインジェクション対策です
これは
フォームからの情報で
‘ OR ”=’
つまり
SELECT * FROM testtable WHERE password=” OR ”=”;
という実行と同じで
意味は
password フィールドが空　または空が空の場合という条件となっています
これが実行されると、すべてのレコードに条件があてはまり
パスワードによる認証が無効化され、データが盗み出されます
このため、対策として
mysql_real_escape_string（）
を使うことで、入力される文字列でSQLにとって危険とされる文字を無効化しておきます
そして、今回は sprintf() を使って書き込みしていますが
$sql = sprintf(‘INSERT INTO my_items SET maker_id=%d,
item_name=”%s”, price=%d, keyword=”%s”‘,
mysql_real_escape_string($_POST[‘maker_id’]),
mysql_real_escape_string($_POST[‘item_name’]).
mysql_real_escape_string($_POST[‘price’]),
mysql_real_escape_string($_POST[‘keyword’])
);
を
$sql = ‘INSERT INTO my_items SET maker_id=’
.mysql_real_escape_string($_POST[‘id’]) .’ ,
‘.item_name=”‘ .mysql_real_escape_string[‘item_name’]) .'”,
.price=’ .mysql_real_escape_string($_POST[‘price’]) . ‘,
keyword=”‘ .mysql_real_escape_string($_POST[‘keyword’]). ‘”‘;
としても
同じ意味になります

mysql_query() の返り値を
レコードセットと言います
これは、セットという名前のとおりでレコードのセットです
前回実行した
mysql_query(‘SELECT * FROM my_imtems’);
の場合、この検索結果すべてが
レコードセットとして格納されます
この中からデータをとってくるのが
mysql_fetch_assoc()
ですが、
書籍によると言葉の意味が載っていました
fetch とってくる
assoc 連想
ということで、連想配列としてとってくるという意味になるそうです
こういう解説をみていると英語の単語の意味がわかるようになると
プログラムの解読もしやすいのかもしれません
ちなみに、
$recordSet = mysql_query(‘SELECT * FROM my_items’);
$data = mysql_fetch_assoc($recordSet);
echo $data[‘item_name’];
$data = mysql_fetch_assoc($recordSet);
echo ‘<br/>’.$data[‘item_name’];
というようにすると
続けて次のデータが表示されます
.$data のところですが
PHP　では . を使うことで、文字列をつなげることができます
主に変数に格納したものを表示するときなどにつかわれます
とくに、HTTPSとHTTPの区別をしたいときとか、切り替え要素のときに
つかうことが多いです
ただし、さすがに毎回書き足していくと、facebook アプリなどで
これを行っていたら、さすがに人力では限界があります
facebook の友達は５０００人まで増やせますが
５０００回も繰り返すのは正気ではできません
なので、繰り返し処理を行う
while 文なども使います
while($data = mysql_fetch_assoc($recordSet)){
echo $data[‘item_name’];
echo ‘<br/>’;
}
となります
これで、すべての item_name の内容が記述されます
ほかの関数もありますが、代表的なのが
mysql_fetch_row()
レコードを単純配列として取得
フィールド名にこだわらずに処理するときに使用
mysql_fetch_array()
通常配列と、連想配列の両方を得られる
たぶん、これが一番つかうかも
http://php.net/manual/ja/book.mysql.php
に mysql 関連が載っていますので、用途に応じて使いましょう

SELECT i.item_name ,SUM(c.count) FROM my_items i, carts c WHERE i.id=c.item_id GROUP BY c.item_id;
これを実行すると
SUM(c.count)
という名前で表示されますが
意味がわかりにくいです
そんなときに使うのが　AS
これを使うことで
出力される名前を
SUM(c.count) から変更できます
ということで
SELECT i.item_name ,SUM(c.count) AS sales_count FROM my_items i, carts c WHERE i.id=c.item_id GROUP BY c.item_id;
とすれば
SUM(c.count) から
sales_count というように名前が変更され出力されます
（ちなみに、表示だけで、実際のデータは変わりません）
これでわかりやすくなります
とくにこまらないのなら、あまり使わないかもしれませんが

DISTINCT　は　重複をなくす
SELECT item_id　FROM carts
を実行すると、全部でてきますが
SELECT DISTINCT item_id FROM carts;
とすれば重複するID　がない状態で表示されます
お次に
BETWEEN
これは～の間という使い方をします
SELECT * FROM `my_items` WHERE price>=50 AND price<150; だと 50円以上、１５０円以下のものがでます でもBETWEENを使うともっと簡単にかけます SELECT * FROM my_items WHERE price BETWEEN 50 AND 149;
これで 50　～ 149　までの指定になります
で IN の解説
INは複数の値を一気に指定したい場合に使用します
１と３を取り出すには
SELECT * FROM my_items WHERE id=1 OR id=3;
でしたが
INを使えば
SELECT * FROM `my_items` WHERE id IN (1,3);
でOKです
今回は少ないのであまり効果が実感できませんが、後々役立ちます
LIMIT　は件数制限をかけます
何万件もあると、検索するのが大変になってくるので
LIMITをつけて制限します
SELECT * FROM carts LIMIT 2;
なら2件に絞れます
また、範囲指定も加納です
SELECT * FROM carts LIMIT 30.１0;
とすれば３１～４０のみに絞り込みできます
３０からにはならず、指定した次の番号からになるので注意です
顧客名簿とかの管理とかに使うこともあります
使い方は LIMIT 開始位置, 件数
となります
30,40 というように範囲指定じゃないので注意しましょう
ちなみに、３０としたのに３１からなのは
データベースなどの配列は０からカウントするからです
プログラムを組むときにもこのルールが摘要されるので
注意しましょう

UPDATE `carts`set item_id=1,count=2 WHERE id=6;
を実行して
SELECT i.item_name, SUM(c.count) FROM my_items i , carts c WHERE i.id=c.item_id GROUP BY c.item_id;
を実行して、もし値が０だとデータが表示されない
この場合、０を表示するには外部結合を使います
ちなみに、いままで実行したのは内部結合で
こちらは
両方のテーブルにデータが存在していないと結合されないので
データがでてこない
外部結合なら、一方のテーブルにデータがあれば必ず表示されるというもの
ゲーム開発とかには使うかも
では、今度は外部結合してみます
SELECT i.item_name, SUM(count) FROM my_items i LEFT JOIN carts c ON i.id=c.item_id GROUP BY c.item_id;
とすると、０で表示されなかった
ブルーベリー　がNULLで表示されます
外部結合には
LEFT JOIN … ON というように使います
書式で書くと
SELECT　FROM …　テーブル1 LEFT JOIN テーブル２ ON 結合条件 WHERE …
テーブル１はメインなので、全部表示
そのうえで
テーブル２で追加データがあれば、表示するというもの
つまり、今回の例なら
FROM my_items i
なので、
my_items は全部表示
LEFT JOIN carts c
なので、
差分で
carts を表示
差分条件は
ON i.id=c.item_id GROUP BY c.item_id;
なので、
id の値が同じものということになります
ちなみに、RIGHT JOIN というのもあるけど、
要は基準が逆になって右がメインになる
ということです

練習のため
carts テーブルを作成
id INT PRIMARY A_I
item_id INT
count INT
でカラム作成し
INSERT INTO carts SET item_id=1,count=5;
で情報入力
いきなり UPDATE しようとしてもデータないので無理です
id を省いているのは
オートインクリメントなので、自動で入力されるからです
今回は数字しか入力していないけど
これはリレーションを前提としているのでOK
item_id に入っている数値は
my_items テーブルの id と一致しているので
これで商品がいくつ売れたかわかる
ではお試しということで
my_items テーブルとリレーションを張ってみる
SELECT my_items.item_name, carts.count FROM my_items, carts WHERE my_items.id=carts.item_id;
これで、商品名と個数が表示される
では、ここで応用
SUMを使えば、合計が得られます
ということで
SELECT SUM(count) FROM carts;
とすれば、counts の合計が得られます
これをさらに
GROUP BY を使って
～ごとの集計を表示できるようにします
SELECT item_id, SUM(count) FROM carts GROUP BY item_id;
でも、これだと数値だけなのでわかりにくい…
ということで
リレーションを使い
my_items テーブルの item_name を引っ張ってくるようにする
このSQLは
SELECT i.item_name,SUM(c.count) FROM my_items i, carts c WHERE i.id=c.item_id GROUP BY c.item_id;
これで、商品名と売上個数がでるようになります
実行してみてわかったのですが
FROM で　ショートカットを宣言すれば
ほかのところで　省略形式で記述できるようです
FROM my_items i, carts c
がショートカット宣言しているところです
これについては、応用がききそうです
株価表示とか、
ゲームのアイテム購入画面とか
ネットショップの計算とかにも使えそうです