2017/07/22

MYSQL5.7.17データベースを作成してテーブルを作成、レコードの追加

ちなみに、
DB歴は初心者レベルの1年ユーザなので全部を信用しないで下さい。

aidayota-no-MacBook-Pro:~ maseda$ /usr/local/mysql/bin/mysql -u root -p
Enter password: ma***(隠してます)
・DBの作成
mysql> create database test2017 CHARACTER SET utf8;
Query OK, 1 row affected (0.01 sec)

・ユーザとパスワード設定

(まだテーブルを作っていないのでDBには空の状態
データベースに接続します。）

（無事接続できました。）

次はテーブルを作成します。

・テーブルの作成

今回作成するテーブルは
ユーザ
商品リスト
産地リスト
購入品リスト
です。
単に、SELECT文の練習のためのデータ作成です。
購入者が購入した商品がどれくらいなのかを抽出します。
例えば、売れている商品の産地はどこか？
どのユーザが一番購入しているのか？
売れていない商品は何か？
を探す練習をしたいと思います

・ユーザuser：顧客情報
id:int
name:nvarchar
address:nvarchar

userテーブルの作成create

・商品リストproduct：野菜のリスト
id:int
name:nvarchar
price:int
id_area:産地:int

productテーブルの作成create

・産地リストarea
id:int
area:例　宮崎県:nvarchar

・購入品リストperchase
id:int
id_user:int
id_product:int
count:int
buy_date:datetime

sqlserverでは日付を取得のにGETDATE()を使いますが、MYSQLではNOW()になるそうです。

mysql> show tables;
+——————–+
| Tables_in_test2017 |
+——————–+
| area |
| perchase |
| product |
| user |
+——————–+
4 rows in set (0.00 sec)
テーブルを作成しました。

次にデータを挿入します。

ユーザー情報から
id:1
name:山田
address:東京都渋谷区

次は産地テーブル
・産地リストarea
id:1
area:宮崎県
id:2
area:東京都
id:3
area:福岡県

複数あるので、今度は insert valueで挿入してみます.insert into select でも同じ内容を複数行にわたって記載すればOKです。

次は、
・商品リストproduct：野菜のリスト
id:1
name:玉ねぎ
price:100
id_area:3

id:2
name:じゃがいも
price:50
id_area:1

id:3
name:トマト
price:500
id_area:2
今度はinsert into selectで挿入します。

mysql> select * from product;
+—-+—————–+——-+———+
| id | name | price | id_area |
+—-+—————–+——-+———+
| 1 | 玉ねぎ | 100 | 3 |
| 2 | じゃがいも | 50 | 1 |
| 3 | トマト | 500 | 2 |
+—-+—————–+——-+———+
3 rows in set (0.00 sec)

mysql>
次は購入品のリストです。

・購入品リストperchase
id:1
id_user:1
id_product:3
count:1
buy_date:now()

これでほぼテーブルが作成できました。
まだちょっと足りないので、少しずつ追加していきますがとりあえず、この程度でなにかクエリを作ってみます。

まず、購入者が何を購入したのかいまのPerchaseテーブルでは番号しかわかりません。
それで、何を購入したのか商品名を表示したいと思います。
購入品リストperchaseの
id:1の商品名を表示します。
inner joinを使えば良さそうです。

購入品リストのid_productと商品のidが同じという条件で検索します。
つまり購入者リストの商品番号id_productと商品のidと一致すれば、商品名がわかります。

select product.name from perchase inner join product on
product.id = perchase.id_product
where perchase.id = 1;

id_product3はトマトであることがわかりました。

今度はトマトとその産地を出力してみましょう。同じようにAreaをINNER JOINで追加します。

select product.name,area.area from perchase inner join product on
product.id = perchase.id_product inner join area on
area.id = product.id_area
where perchase.id = 1;

トマトの産地が東京都であることがわかりました。
このように、購入者リストでは番号しかわかりませんでした。
それが、テーブルを結合することによってわかりやすくなりました。

現在のテーブルのレコード数ではもっといろいろなことができないのでデータをもっと挿入してみましょう。

ユーザー情報を２名ほど追加して、
購入者リストも３件ほど追加してみましょう。

ユーザー情報から
id:2
name:黒木
address:宮崎県宮崎市

id:3
name:松本
address:神奈川県横浜市

購入者リストを３件追加

・購入品リストperchase
id:2
id_user:2
id_product:1
count:2
buy_date:now()

id:3
id_user:3
id_product:2
count:10
buy_date:now()

id:4
id_user:1
id_product:3
count:6
buy_date:now()

mysql> select * from perchase;
+—-+———+————+——-+———————+
| id | id_user | id_product | count | buy_date |
+—-+———+————+——-+———————+
| 1 | 1 | 3 | 1 | 2017-07-22 20:49:57 |
| 2 | 2 | 1 | 2 | 2017-07-22 21:48:18 |
| 3 | 3 | 2 | 10 | 2017-07-22 21:48:18 |
| 4 | 1 | 3 | 6 | 2017-07-22 21:48:18 |
+—-+———+————+——-+———————+
4 rows in set (0.00 sec)

今日の商品の購入金額の合計を算出してみましょう。
まずは、購入者リストからどの商品を何個購入されたのかを出力してみましょう。
select product.name,perchase.count from perchase inner join product on
product.id = perchase.id_product;

次に、１レコードあたりの小計を算出してみましょう。
select product.name,perchase.count,perchase.count*product.price as allKingaku from perchase inner join product on
product.id = perchase.id_product;

mysql> select product.name,perchase.count,perchase.count*product.price as allKingaku from perchase inner join product on
-> product.id = perchase.id_product;
+—————–+——-+————+
| name | count | allKingaku |
+—————–+——-+————+
| トマト | 1 | 500 |
| 玉ねぎ | 2 | 200 |
| じゃがいも | 10 | 500 |
| トマト | 6 | 3000 |
+—————–+——-+————+
4 rows in set (0.00 sec)

次に合計を求めます。
これはSUMが使えそうです。

select sum(perchase.count*product.price) as allKingaku from perchase inner join product on
product.id = perchase.id_product;

mysql> select sum(perchase.count*product.price) as allKingaku from perchase inner join product on
-> product.id = perchase.id_product;
+————+
| allKingaku |
+————+
| 4200 |
+————+
1 row in set (0.01 sec)
これで、現在の商品の合計金額がでました。

今度は商品別に合計金額を算出してみましょう。
これはGroup by を使えば良さそうです。

select product.name,sum(perchase.count*product.price) as allKingaku from perchase inner join product on
product.id = perchase.id_product
group by product.name
;

mysql> select product.name,sum(perchase.count*product.price) as allKingaku from perchase inner join product on
-> product.id = perchase.id_product
-> group by product.name
-> ;
+—————–+————+
| name | allKingaku |
+—————–+————+
| じゃがいも | 500 |
| トマト | 3500 |
| 玉ねぎ | 200 |
+—————–+————+
3 rows in set (0.01 sec)

これで、どの商品がいくら売れたかわかりました。

次にユーザー毎に購入金額を算出してみましょう。
group by perchase.id_userでまとめてみましょう。

select perchase.id_user,sum(perchase.count*product.price) as allKingaku from perchase inner join product on
product.id = perchase.id_product
group by perchase.id_user
;

+———+————+
| id_user | allKingaku |
+———+————+
| 1 | 3500 |
| 2 | 200 |
| 3 | 500 |
+———+————+
3 rows in set (0.00 sec)
とりあえず、３名の購入金額がわかりましたが、誰かはわかりません。

誰かわかるようにしましょう。
そのためには、userテーブルを追加する必要があります。user.idとperchase.id_user が一致する条件を付け加えます。

これで、誰がいくら購入したのかわかるようになりました。

でも黒木さんの文字にスペースが入り込んでいるようです。最初のデータ挿入でスペースが入っていることに気づかずに挿入したようです。

ちょっと訂正しましょう。
更新はupdateです。

update user
set name = ‘黒木’
where
id = 2;

もう一度確認します。
mysql> select perchase.id_user,user.name,sum(perchase.count*product.price) as allKingaku from perchase inner join product on
-> product.id = perchase.id_product inner join user on
-> user.id = perchase.id_user
-> group by perchase.id_user
-> ;
+———+——–+————+
| id_user | name | allKingaku |
+———+——–+————+
| 1 | 山田 | 3500 |
| 2 | 黒木 | 200 |
| 3 | 松本 | 500 |
+———+——–+————+
3 rows in set (0.00 sec)

今度は購入数が５件を越えた購入者を検索したいと思います。

これはHavingを使えば良さそうです。

select perchase.count,user.name from perchase inner join product on
product.id = perchase.id_product inner join user on
user.id = perchase.id_user
having perchase.count >=5
;
+——-+——–+
| count | name |
+——-+——–+
| 10 | 松本 |
| 6 | 山田 |
+——-+——–+
2 rows in set (0.00 sec)

これでも良いのですが、ユーザーが１回に購入した件数になるので、ユーザーがまとめて購入した場合で検索するようにしたいと思います。

例えば山田さんは、合計個数は７個ですが、いまでは５件以上の場合で6件のみ検索されています。
これを7件として合計個数で検索されるようにします。

これはperchase.id_userをGroup byでまとめればよいのでしょうか？
やってみます。
（これから）

GROUPでまとめます。

HAVINGを外しておきます。

同じIDは合計にしたいですね。
SUMを使えばよいでしょうか。

☓間違い
select user.id,sum(perchase.count),user.name from perchase inner join product on
product.id = perchase.id_product inner join user on
user.id = perchase.id_user
group by user.id,perchase.count
;

+—-+———————+——–+
| id | sum(perchase.count) | name |
+—-+———————+——–+
| 1 | 1 | 山田 |
| 1 | 6 | 山田 |
| 2 | 2 | 黒木 |
| 3 | 10 | 松本 |
+—-+———————+——–+
4 rows in set (0.00 sec)

☓だめですね。

よくよく見ると、ここで同じユーザの購入数を出したいのに、必要のない商品テーブルproductを使ってみますね。これを使わずにSelect文を書いてみます。

☓まだだめですね。

select id_user,sum(perchase.count) as goukei from perchase
group by id_user,perchase.count;

+———+——–+
| id_user | goukei |
+———+——–+
| 1 | 1 |
| 1 | 6 |
| 2 | 2 |
| 3 | 10 |
+———+——–+
4 rows in set (0.00 sec)

☓間違い
countが合計になっていません。

正しい。うまくいきました。Groupの使い方がよくわかっていない証拠ですね。

成功です。つまり、GroupByに、まとめてはいけないカラムCountをくわえていました。
ユーザでまとめるだけにしておけばよいのに、Countの数でもまとめようとしたため、SUMが計算されていませんでした。
GroupにCountを加えると、購入数毎のリストを作るのです。
つまり、2件購入したリスト、6件のリストと言った風に件数を目的にまとめたリストになるのです。
今回は、ユーザーごとに購入者数をまとめたいので、GroupからCountを外します。
これで、とりあえず、ユーザで購入した件数をまとめることができました。

一応番号でなくて名前で表示するようにします。

これで、山田さんは１＋６＝７個購入したことがわかりました。

今度はINNERとOUTERの違いを見ていきたいと思います。

ユーザ情報を追加します。今は3人しかいませんので、後二人を追加してみます。

id:4
name:佐々木
address:沖縄県

id:5
name:木下
address:鹿児島県

これで5人になりました。
これで、購入リストについて照らし合わせみます。

userテーブルが基準になりますので、ユーザ情報と購入者リストとを結合しています。
なにも購入していない佐々木さんと木下さんは購入リストはNULLになっています。
今度はRight outerにしてみます。

すると、
購入者リストにない佐々木さんと木下さんは表示されなくなりました。
つまり、右側のperchaseテーブルの情報が基準になるので、購入していないユーザ情報は表示されません。
今度は、Innerにしてみましょう。

Right OuterとInnerが同じ結果になりました。

このようにOuterとInnerで違いが出たり、同じ結果になったります。
右か左かInnerかで表示される結果が変わるので、どのテーブルを基準にするのかデータの内容を注意しながら検索する必要があります。

・Groupbyの使い方

Group　BYでSUMをCountで計算すると問題ないのですが、
select user.id,user.name,sum(perchase.count) from user Left outer join perchase on
perchase.id_user = user.id
group by user.id;
+—-+———–+———————+
| id | name | sum(perchase.count) |
+—-+———–+———————+
| 1 | 山田 | 7 |
| 2 | 黒木 | 2 |
| 3 | 松本 | 10 |
| 4 | 佐々木 | NULL |
| 5 | 木下 | NULL |
+—-+———–+———————+
5 rows in set (0.00 sec)

SUMをはずすと
select user.id,user.name,perchase.count from user Left outer join perchase on
perchase.id_user = user.id
group by user.id;

とすると、エラーになります。
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test2017.perchase.count’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

つまり、購入者リストPerchaseは複数の購入情報が含まれていて、GroupBYで一括りにした時、Countは何かの関数でまとめておく必要があります。
つまり、山田さんは、2件の購入情報がありました。そのためCountは合計する必要があります。
LeftOuterなので、ユーザー情報のリスト表示させるので、佐々木さんと木下さんが購入していなくともユーザ情報にデータが存在すれば、表示されます。
これがInnerであれば、表示されません。
表示させる必要がある場合とない場合で、使い方が変わってきます。

今度は、購入金額をユーザーごとにまとめて表示したいと思います。

select user.id,user.name,sum(perchase.count*product.price) from user Left outer join perchase on
perchase.id_user = user.id left outer join product on
product.id = perchase.id_product
group by user.id;

mysql> select user.id,user.name,sum(perchase.count*product.price) from user Left outer join perchase on
-> perchase.id_user = user.id left outer join product on
-> product.id = perchase.id_product
-> group by user.id;
+—-+———–+———————————–+
| id | name | sum(perchase.count*product.price) |
+—-+———–+———————————–+
| 1 | 山田 | 3500 |
| 2 | 黒木 | 200 |
| 3 | 松本 | 500 |
| 4 | 佐々木 | NULL |
| 5 | 木下 | NULL |
+—-+———–+———————————–+
5 rows in set (0.00 sec)
この金額が正しいかを確認します。

select * from perchase inner join user on
user.id = perchase.id_user;
+—-+———+————+——-+———————+—-+——–+———————–+
| id | id_user | id_product | count | buy_date | id | name | address |
+—-+———+————+——-+———————+—-+——–+———————–+
| 1 | 1 | 3 | 1 | 2017-07-22 20:49:57 | 1 | 山田 | 東京都渋谷区 |
| 2 | 2 | 1 | 2 | 2017-07-22 21:48:18 | 2 | 黒木 | 宮崎県宮崎市 |
| 3 | 3 | 2 | 10 | 2017-07-22 21:48:18 | 3 | 松本 | 神奈川県横浜市 |
| 4 | 1 | 3 | 6 | 2017-07-22 21:48:18 | 1 | 山田 | 東京都渋谷区 |
+—-+———+————+——-+———————+—-+——–+———————–+
4 rows in set (0.00 sec)

次に、値段をInnerJOINしてみます。

select user.id,user.name,product.name,product.price,perchase.count from perchase inner join user on
user.id = perchase.id_user
inner join product on
perchase.id_product = product.id
;

+—-+——–+—————–+——-+——-+
| id | name | name | price | count |
+—-+——–+—————–+——-+——-+
| 1 | 山田 | トマト | 500 | 1 |
| 2 | 黒木 | 玉ねぎ | 100 | 2 |
| 3 | 松本 | じゃがいも | 50 | 10 |
| 1 | 山田 | トマト | 500 | 6 |
+—-+——–+—————–+——-+——-+
4 rows in set (0.00 sec)

山田さんはトマト500円を7個購入しました。3500円です。
黒木さんは玉ねぎ100円を2個購入しました。200円です。
松本さんはじゃがいも50円を10個購入しました。500円です。

先程、Outerで出力した内容と一致しました。

この条件の合計をした出力とほぼおなじということがわかりました。
select user.id,user.name,sum(perchase.count*product.price) from user Left outer join perchase on
perchase.id_user = user.id left outer join product on
product.id = perchase.id_product
group by user.id;

もう少し購入リストのレコード数を増やしましょう。
黒木さんがジャガイモを2個買いました。
松本さんが玉ねぎ3個買いました。

+—-+——–+—————–+——-+——-+
| id | name | name | price | count |
+—-+——–+—————–+——-+——-+
| 1 | 山田 | トマト | 500 | 1 |
| 2 | 黒木 | 玉ねぎ | 100 | 2 |
| 3 | 松本 | じゃがいも | 50 | 10 |
| 1 | 山田 | トマト | 500 | 6 |
| 2 | 黒木 | じゃがいも | 50 | 2 |
| 3 | 松本 | 玉ねぎ | 100 | 3 |
+—-+——–+—————–+——-+——-+
6 rows in set (0.00 sec)

こんな感じになりました。
では、玉ねぎを買ったのは誰かを出力したいと思います。

idで検索しても良いですが、name = ‘玉ねぎ’で検索します。

mysql> select * from perchase left outer join user on perchase.id_user = user.id left outer join product on perchase.id_product = product.id where product.name = ‘玉ねぎ’;
+—-+———+————+——-+———————+——+——–+———————–+——+———–+——-+———+
| id | id_user | id_product | count | buy_date | id | name | address | id | name | price | id_area |
+—-+———+————+——-+———————+——+——–+———————–+——+———–+——-+———+
| 2 | 2 | 1 | 2 | 2017-07-22 21:48:18 | 2 | 黒木 | 宮崎県宮崎市 | 1 | 玉ねぎ | 100 | 3 |
| 6 | 3 | 1 | 3 | 2017-07-26 20:15:09 | 3 | 松本 | 神奈川県横浜市 | 1 | 玉ねぎ | 100 | 3 |
+—-+———+————+——-+———————+——+——–+———————–+——+———–+——-+———+
2 rows in set (0.00 sec)

これで、黒木さんと松本さんが買ったことがわかりました。