月別: 2012年3月

練習のため
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
がショートカット宣言しているところです
これについては、応用がききそうです
株価表示とか、
ゲームのアイテム購入画面とか
ネットショップの計算とかにも使えそうです

SELECT * FROM `makers` ,my_items where my_items.id=1 AND makers.id=my_items.maker_id;
を
テーブル名をショートカットして
SELECT * FROM `makers` m, my_items i WHERE i.id=1 AND i.id=i.maker_id;
とすることも可能です
どちらかというと、サンプルなどを書籍でみると
ショートカットしているほうが多いかも
やり方は
テーブル名　ショートカット名
というようにします
今回だと
my_items m
C とか java の変数宣言のようなかんじです

リレーションを使用してみました
リレーションは Relation つまり、関連性で
複数のテーブルをその関連性からつないで１つのテーブルのように扱う機能のこと
まずは、実践
SELECT * FROM `makers` ,my_items where my_items.id=1 AND makers.id=my_items.maker_id;
これで
一発で生産者情報と、商品情報が得られる
うーん、便利
続いて実験その２
SELECT * FROM `makers` ,my_items where makers.id=my_items.maker_id;
ちなみに、phpMyAdmin の場合、該当する部分が黄色い枠で囲われるので
わかりやすい
このリレーションのポイントとなるのが、FROM のところ
検索したいDBテーブルを , で区切って指定することで複数のテーブルから
条件検索が可能になります
複数テーブルを指定した場合、必ずキーとなるフィールドを使わないとダメ
例えば、今回のように　WHERE　の後にある
AND makers.id=my_items.maker_id
これは
maker テーブル の id と
my_items テーブルの maker_id が
一致したデータを検索という意味になります
これ、 java とかでプログラミング経験あるとわかりやすいかも
javascript とかでもよくある書き方です
書き方は、
テーブル名と　カラム名を .でつないで記述します
つまり、今回なら
makers テーブル　と　id カラムなので
makers.id となります
なぜ、こんな面倒なことを指定するかというと
id が
makers テーブルなのか
my_items テーブルなのかがわからないからです
仕事で、部品を持ってきてと言われても、同じ名前の部品でしたら
どのテーブルかわからないと持ってこれません
つまり、わかりやすい細かい指示をしましょうということです
～のテーブルにある～という部品をもってきて
というように指示するようなものです
アレもってきて
では意味がわかりませんので
それはさておき、
複数のテーブルをつないでデータ検索することが
リレーションをはるということになります

DBであつかう量が増えると
さすがに１つのテーブルでは難しくなります
このため
プログラムコードを複数にわけるように
テーブルも複数に用途ごとにわけるようになっていきます
というわけで書籍を参考にしてテーブルを追加します
phpAyAdmin でDBを選択して。テーブルの追加をします
テーブル名　makers
フィールド数　４
id INT
name TEXT
address TEXT
tel TEXT
として
id のインデックスで　PRIMARY を選んで
プライマリキーを設定
A_I にチェックを入れてオートインクリメントを有効にします
以上の設定をしたら、保存をクリック
次に、SQLで
insert を使って各種情報を入力します
INSERT INTO `makers`set name=’山田さん’, address=’東京都港区’,tel=’000-111-222′;
というように、
,　で区切って入力していきます
ある程度入力したら
次にテーブルを結びつけるフィールドを作成
今度は my_items へ戻って
ｉｄ　の後にカラムを追加します
カラム名 maker_id
種類　INT
ALTER TABLE `my_items` ADD `maker_id` INT NOT NULL AFTER `id`
でもOKです
できたら、次に update で数値を入力します
UPDATE `my_items` SET maker_id=1 where id=1;

mysql を使って計算も可能
SELECT sum(price) FROM `my_items`;
とすると、price の合計を出せます
これとwhere を組み合わせて、年刊合計とか、
週間の売上を出したりできます
ほかにも
SELECT max(price) FROM `my_items` ;
で最高値表示
select min(price) from my_items;
最安値表示
SELECT count(id) FROM `my_items`;
ヒットしたデータの件数取り出し
SELECT AVG(price) FROM my_items;
平均値を表示
これらを使うと、気温などにも使えそうですが
IDを株のコード
price を株価とすれば、自分でデータベースにして分析もできそうです
MySQL側で計算してもらってレコードを更新できるか？？
に載っていましたが
UPDATE `table_name` SET `point` = `point` + 1 WHERE `id` = xxx
というようにして
MySQL で計算してレコード更新も可能なようです
既存の値を計算して、結果をカラムに入力する方法
でレコード更新の例も載っていました
mysql 計算
と検索すると、いろいろとでてきます

社員番号やISBNのように新しさと一致しないケースの場合
別途　データ入力した日を記録しておくとよい
ということで　DATETIME　についてです
カラム追加で
カラム名　created
種類　DATETIME
とします
次に、データの更新
UPDATE `my_items` set created = ‘2010-07-01‘where id-1;
とすることで
データ更新可能
なお、日付は　指定のルールありで
2010-07-01
という書式で入力する
ちなみに、当日でよいなら
UPDATE `my_items` SET created = now() where id-2;
とすればOK
続いて、更新日を記録するので
TIMESTAMP型フィールドを作成します
カラム名 modified
データ型 TIMESTAMP
では、データ更新して実験
UPDATE `my_items` SET sales=18 where id=3;
これを実行すると、id-3 に該当するところの更新時刻がでます
これを使えば、ゲームなどにも応用できそうです
ということで、便利な TIMESTAMP を覚えて置こうと思います
これ、シミュレーションゲームなどに使えそうです
とくに、リアルタイムシミュレーションとか
タイムスタンプは参考書籍の１６１Pに載っていました

引き続き、MySQL 関連
今回はデータのソート
ORDER BY を使うことで並び替えができます
昇順、つまり小さい順で並べるなら
select * from my_items ORDER BY id ASC
これでIDの数値の小さい順に並べれます
逆に降順にするなら
select * from my_items ORDER BY id DESC
ちなみに where と組み合わせもできます
SELECT * FROM `my_items` where price <=180 order by price;
とすると
１８０円以下が昇順（小さい順）で表示されます

に応用として　１５９Pに
ランキング情報の作り方が載っていました
１５０Pでカラム追加を行い、内容を UPDATE を使って追加します
カラムは　ｒａｎｋ
種類は　INT
としました
あとは
update my_items set rank=2 where id=2;
というように、値を追加していきます
rank　情報を更新したら
select * from my_items ORDER BY rank ASC;
でランキング表示できます
ただし、この方法では面倒になるため、一度 rank 情報を削除します
（売上が変わると、毎回ランキング更新は面倒なので）
削除するには
ALTER TABLE `my_items` DROP `rank`
で削除します
DROP　で削除できますが、中身のデータも一緒に消えるうえ、復元できないようですので
注意
次に、カラム sales を追加して
update my_items set ｓａｌｅｓ=20 where id=2;
というように値を入力
SELECT * FROM my_items ORDER BY sales DESC;
で検索すれば、売上ランキングができます
DBの設定をするときには、なるべく絶対情報を記録して
ランキングなどを行うようにします
絶対情報は、ランキングのように他のものに左右されないものです
ランキングは相対情報なので、どんどん変化してしまいます
とりあえず、今日はここまでとして
明日は
DATETIME　と TIMESTAMP あたりをすすめてみます

今回は。LIKE で検索するときのメモ
select * from my_items where keyword like ‘%甘い%’;
このように　％で囲むと一致するキーワードで検索します
select * from my_items where keyword like ‘%甘い’;
だと、最初に
甘いとなっている部分しか検索できないので注意
あと、AND検索で
select * from my_items where price>=50 AND price<150; ならOK で select * from my_items where price=>50 AND price<150; だとNG ちょっとした違いだけど、はまりました あと ちょっと複雑なのとして SELECT * FROM `my_items` WHERE (id=1 OR id=3) AND price<150 AND keyword LIKE '%甘い%'; これは IDが１または３ １５０円未満で 甘いが含まれる商品 と言う意味です ちょっと複雑に見えますが Linux でシェルスクリプトとか コマンドのパイプを使っていろいろとやっている人には 見慣れた光景かも.... 注意事項として　OR　と　AND　を混ぜるときには （）で囲わないと、全然違う意味になるようです SELECT * FROM `my_items` WHERE id=1 OR id=3 AND price<150 AND keyword LIKE '%甘い%'; とすると id=1 OR id=3 AND price<150 AND keyword LIKE '%甘い%' という意味になります もし、これの意味を id=1 もしくは id=3 としたいのなら
SELECT * FROM `my_items` WHERE （id=1 OR id=3） AND price<150 AND keyword LIKE '%甘い%'; というように （）で囲むようにします