【DB授業二日目】MariaDB - クロピグログ

データベースで昨日のテーブル表示

●まずMariaDBクライアントプログラム起動

mysql -u root -p

●データベース領域の変更

USE lesson

●テーブル表示

SELECT * FROM stationery;

条件をつけて検索する　WHERE

●goods内のitemが「米」を表示

SELECT * FROM goods WHERE item='米';
+----+------+-------+
| id | item | price |
+----+------+-------+
| 6 | 米 | 2000 |
+----+------+-------+

●goods内のIDの3以上を表示

SELECT * FROM goods WHERE id>=3;
+----+---------------+-------+
| id | item | price |
+----+---------------+-------+
| 3 | カフェ'チョコ | 150 |
| 4 | アボカド | 260 |
| 5 | チョコパン | 240 |
| 6 | 米 | 2000 |
| 7 | たまねぎ | 0 |
| 8 | NULL | 9800 |
+----+---------------+-------+

●goods内のIDの6以外を表示

1.パターン1

SELECT * FROM goods WHERE id!=6;
+----+----------------------+-------+
| id | item | price |
+----+----------------------+-------+
| 1 | おいしい水 | 190 |
| 2 | ポテトチップスバター | 120 |
| 3 | カフェ'チョコ | 150 |
| 4 | アボカド | 260 |
| 5 | チョコパン | 240 |
| 7 | たまねぎ | 0 |
| 8 | NULL | 9800 |
+----+----------------------+-------+

2.パターン2

SELECT * FROM goods WHERE id<>6;
+----+----------------------+-------+
| id | item | price |
+----+----------------------+-------+
| 1 | おいしい水 | 190 |
| 2 | ポテトチップスバター | 120 |
| 3 | カフェ'チョコ | 150 |
| 4 | アボカド | 260 |
| 5 | チョコパン | 240 |
| 7 | たまねぎ | 0 |
| 8 | NULL | 9800 |
+----+----------------------+-------+

●goods内のitemが「NULL」を表示

SELECT * FROM goods WHERE item IS NULL;
+----+------+-------+
| id | item | price |
+----+------+-------+
| 8 | NULL | 9800 |
+----+------+-------+

●goods内のitemが「NULL」じゃないものを表示

SELECT * FROM goods WHERE item IS NOT NULL;
+----+----------------------+-------+
| id | item | price |
+----+----------------------+-------+
| 1 | おいしい水 | 190 |
| 2 | ポテトチップスバター | 120 |
| 3 | カフェ'チョコ | 150 |
| 4 | アボカド | 260 |
| 5 | チョコパン | 240 |
| 6 | 米 | 2000 |
| 7 | たまねぎ | 0 |
+----+----------------------+-------+

範囲・条件を組み合わせ検索する

●BETWEEN：範囲

SELECT　フィールド名　FROM　テーブル名

WHERE　フィールド名　BETWEEN　値　AND　値;

SELECT * FROM goods WHERE id BETWEEN 2 AND 5;

+----+----------------------+-------+
| id | item | price |
+----+----------------------+-------+
| 2 | ポテトチップスバター | 120 |
| 3 | カフェ'チョコ | 150 |
| 4 | アボカド | 260 |
| 5 | チョコパン | 240 |
+----+----------------------+-------+

●AND：複数の条件をすべて満たすレコードの検索

SELECT　フィールド名　FROM　テーブル名

WHERE　条件1　AND　条件2;

SELECT * FROM goods WHERE id>=2 AND price<200;
+----+----------------------+-------+
| id | item | price |
+----+----------------------+-------+
| 2 | ポテトチップスバター | 120 |
| 3 | カフェ'チョコ | 150 |
| 7 | たまねぎ | 0 |
+----+----------------------+-------+

●OR：複数の条件のどれかを満たすレコードの検索

SELECT　フィールド名　FROM　テーブル名

WHERE　条件1　OR　条件2;

SELECT * FROM goods WHERE id<2 OR price>=2000;
+----+------------+-------+
| id | item | price |
+----+------------+-------+
| 1 | おいしい水 | 190 |
| 6 | 米 | 2000 |
| 8 | NULL | 9800 |
+----+------------+-------+

・同じフィールドに対して複数の値指定

SELECT * FROM goods WHERE id=1 OR id=2 OR id=6;

↓この書き方でもOK

SELECT * FROM goods WHERE id IN(1,2,6);

●NOT：条件を反転させる

SELECT　フィールド名　FROM　テーブル名

WHERE　NOT　いままでの条件式;

SELECT * FROM goods WHERE NOT id BETWEEN 2 AND 6;

+----+------------+-------+
| id | item | price |
+----+------------+-------+
| 1 | おいしい水 | 190 |
| 7 | たまねぎ | 0 |
| 8 | NULL | 9800 |
+----+------------+-------+

問題8

1.価格が「150円」より安い商品とカフェ'チョコのレコードを表示しなさい

SELECT * FROM goods WHERE item='カフェ\'チョコ' OR price<150;
+----+----------------------+-------+
| id | item | price |
+----+----------------------+-------+
| 2 | ポテトチップスバター | 120 |
| 3 | カフェ'チョコ | 150 |
| 7 | たまねぎ | 0 |
+----+----------------------+-------+

2.id番号「3」以下、かつ価格が「150円」以上のレコードを表示しなさい

SELECT * FROM goods WHERE id<=3 AND price>=150;
+----+---------------+-------+
| id | item | price |
+----+---------------+-------+
| 1 | おいしい水 | 190 |
| 3 | カフェ'チョコ | 150 |
+----+---------------+-------+

3.商品名に値を入れ忘れているレコードと価格が「0」のレコードを表示しなさい

SELECT * FROM goods WHERE item IS NULL OR price=0;
+----+----------+-------+
| id | item | price |
+----+----------+-------+
| 7 | たまねぎ | 0 |
| 8 | NULL | 9800 |
+----+----------+-------+

部分一致検索する　LIKE

SELECT　フィールド名　FROM　テーブル名

WHERE　フィールド名　LIKE　部分一致検索文;

●任意の文字列をあらわす「%」

SELECT * FROM customer WHERE name LIKE '木村%';
+----+------------+---------+
| id | name | address |
+----+------------+---------+
| 3 | 木村大五郎 | 北海道 |
| 5 | 木村太郎 | 大阪府 |
+----+------------+---------+

SELECT * FROM customer WHERE address LIKE '%県';
+----+------------------+----------+
| id | name | address |
+----+------------------+----------+
| 2 | Shaquille O'Neal | 栃木県 |
| 6 | 佐々木美樹 | 和歌山県 |
+----+------------------+----------+

SELECT * FROM customer WHERE name LIKE '%木%';
+----+------------+----------+
| id | name | address |
+----+------------+----------+
| 3 | 木村大五郎 | 北海道 |
| 5 | 木村太郎 | 大阪府 |
| 6 | 佐々木美樹 | 和歌山県 |

+----+------------+----------+

●任意の1文字をあらわす「_」アンダースコア

SELECT * FROM customer WHERE name LIKE '木村_郎';
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 5 | 木村太郎 | 大阪府 |
+----+----------+---------+

●もし「木村_拓哉」という「_」アンダースコア入りの名前を検索するときは...

SELECT * FROM customer WHERE name LIKE '%\_%';

検索件数の制限　LIMIT

SELECT　フィールド名　FROM　テーブル名　LIMIT　件数件数;

●検索件数の指定

SELECT * FROM goods LIMIT 5;
+----+----------------------+-------+
| id | item | price |
+----+----------------------+-------+
| 1 | おいしい水 | 190 |
| 2 | ポテトチップスバター | 120 |
| 3 | カフェ'チョコ | 150 |
| 5 | チョコパン | 240 |
| 6 | 米 | 2000 |
+----+----------------------+-------+

●開始行と検索件数の指定

SELECT * FROM goods LIMIT 5,5;

+----+----------+-------+
| id | item | price |
+----+----------+-------+
| 7 | たまねぎ | 0 |
| 8 | NULL | 9800 |
+----+----------+-------+

※5レコード分表示したいが、今回の場合は実際に表示されるのは2行だけ

並び替え　ORDER BY

●高い順

SELECT　フィールド名　FROM　テーブル名　ORDER BY　フィールド名;

SELECT * FROM goods ORDER BY price;
+----+----------------------+-------+
| id | item | price |
+----+----------------------+-------+
| 7 | たまねぎ | 0 |
| 2 | ポテトチップスバター | 120 |
| 3 | カフェ'チョコ | 150 |
| 1 | おいしい水 | 190 |
| 5 | チョコパン | 240 |
| 6 | 米 | 2000 |
| 8 | NULL | 9800 |
+----+----------------------+-------+

●安い順

SELECT　フィールド名　FROM　テーブル名　ORDER BY　フィールド名　DESC;

SELECT * FROM goods ORDER BY price DESC;
+----+----------------------+-------+
| id | item | price |
+----+----------------------+-------+
| 8 | NULL | 9800 |
| 6 | 米 | 2000 |
| 5 | チョコパン | 240 |
| 1 | おいしい水 | 190 |
| 3 | カフェ'チョコ | 150 |
| 2 | ポテトチップスバター | 120 |
| 7 | たまねぎ | 0 |
+----+----------------------+-------+

◇問題

価格が500円以下の商品を安い順に表示しなさい

SELECT * FROM goods WHERE price<500 ORDER BY price DESC;
+----+----------------------+-------+
| id | item | price |
+----+----------------------+-------+
| 5 | チョコパン | 240 |
| 1 | おいしい水 | 190 |
| 3 | カフェ'チョコ | 150 |
| 2 | ポテトチップスバター | 120 |
| 7 | たまねぎ | 0 |
+----+----------------------+-------+

計算や集計

●合計値の出力

SELECT　SUM(合計値を出力するフィールド名)　　FROM　テーブル名;

SELECT SUM(price) FROM goods;
+------------+
| SUM(price) |
+------------+
| 12500 |
+------------+

●平均値の出力

SELECT　AVG(平均値を出力するフィールド名)　　FROM　テーブル名;

SELECT AVG(price) FROM goods;
+------------+
| AVG(price) |
+------------+
| 1785.7143 |
+------------+

●最大値の出力

SELECT　MAX(最大値を出力するフィールド名)　　FROM　テーブル名;

SELECT MAX(price) FROM goods;
+------------+
| MAX(price) |
+------------+
| 9800 |
+------------+

●最小値の出力

SELECT　MIN(最小値を出力するフィールド名)　　FROM　テーブル名;

SELECT MIN(price) FROM goods;
+------------+
| MIN(price) |
+------------+
| 0 |
+------------+

●該当件数取り出す

SELECT　COUNT(該当件数を出力するフィールド名)　　FROM　テーブル名;

SELECT COUNT(id) FROM goods;
+-----------+
| COUNT(id) |
+-----------+
| 7 |
+-----------+

※NULL値は該当件数に含まれないので注意！

●フィールド名に別名つける

SELECT　フィールド名　AS　別名　FROM　テーブル名;

SELECT COUNT(id) AS total FROM goods;
+-------+
| total |
+-------+
| 7 |
+-------+

・わかりやすくするために別名つける

●問題9

「ポテトチップバターし」と「チョコパン」と「米」の値段の合計値をSUMを使って表示せよ。

SELECT SUM(price) FROM goods WHERE item='ポテトチップバターし' OR item='チョコパン' OR item='米';

これでもOK

SELECT SUM(price) FROM goods WHERE item In('ポテトチップバターし','チョコパン','米');
+------------+
| SUM(price) |
+------------+
| 2360 |
+------------+

●問題10

200円以下の商品の数を表示しなさい。なお、フィールド名は「sales_item」にすること。

SELECT COUNT(id) AS sales_item FROM goods WHERE price<200;
+------------+
| sales_item |
+------------+
| 4 |
+------------+

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

●ADD：フィールドの追加

ATLER　TABLE　テーブル名　ADD　フィールド名　型　[DEFAULT　値];

ALTER TABLE goods ADD stock INT DEFAULT 0;

・「DEFAULT 0」を省略すると、「NULL」が入る

●MODIFY：フィールドのデータ型の変更

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

ALTER TABLE goods MODIFY item CHAR(20) ;

・文字列型(char)から整数型(int)に変更するような場合も使えるが、格納できない値は切り捨てられるため基本使わない

・文字数増やしたりするときに基本的に使う

●CHANGE：フィールドとデータ型の変更

ATLER　TABLE　テーブル名　CHANGE　

旧フィールド名　新フィールド名　新データ型;

ALTER TABLE goods CHANGE

item item_new VARCHAR(255);

●DROP：フィールドの削除

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

ALTER TABLE goods DROP stock;

●RENAME AS：テーブル名の変更

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

ALTER TABLE goods RENAME AS commodity;

リレーション

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

・商品管理テーブル

id	item	price	maker
1	おいしい水	190	3
2	ポテトチップバターしょうゆ味	120	4
3	カフェ'チョコ	150	4
5	チョコパン	240	1
6	米	2000	2
7	たまねぎ	0	NULL
8	NULL	9800	NULL

・メーカー管理テーブル

id	company	address	tel
1	東京パン	東京都	03-0000-0000
2	宇都宮米店	栃木県	028-111-1111
3	札幌農場	北海道	01-222-2222
4	浦安製菓	千葉県	047-XXX-3333

・この二つを準備する

●JOIN：テーブルの結合（内部結合）

・書式1

SELECT　フィールド名　FROM　テーブル名1　JOIN　テーブル名2　

ON　テーブル1照合用フィールド名=テーブル2照合用フィールド名;

・書式2

SELECT　フィールド名　FROM　テーブル名1,　テーブル名2　

WHERE　テーブル1照合用フィールド名=テーブル2照合用フィールド名;

SELECT * FROM commodity JOIN trader

ON maker = id;

↓こちらのほうがよい

SELECT * FROM commodity JOIN trader

ON commodity.maker = trader.id;
+----+------------------------------+-------+-------+----+------------+---------+--------------+
| id | item | price | maker | id | company | address | tel |
+----+------------------------------+-------+-------+----+------------+---------+--------------+
| 1 | おいしい水 | 190 | 3 | 3 | 札幌農場 | 北海道 | 011-222-2222 |
| 2 | ポテトチップバターしょうゆ味 | 120 | 4 | 4 | 浦安製菓 | 千葉県 | 047-XXX-3333 |
| 3 | カフェ'チョコ | 150 | 4 | 4 | 浦安製菓 | 千葉県 | 047-XXX-3333 |
| 5 | チョコパン | 240 | 1 | 1 | 東京パン | 東京都 | 03-0000-0000 |
| 6 | 米 | 2000 | 2 | 2 | 宇都宮米店 | 栃木県 | 028-111-1111 |
+----+------------------------------+-------+-------+----+------------+---------+--------------+

※どのテーブルのフィールドかわかるようにするため

「テーブル名」+「.」+「フィールド名」でつないでおく

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

SELECT * FROM commodity c JOIN trader t

ON c.maker = t.id;

・「commodity c」で「c」、「trader t」で「t」を短縮することを宣言して、そのあと使うときに短く使える

●LEFT JOIN：テーブルの結合（外部結合）

SELECT　フィールド名　FROM　テーブル名1　LIFT　JOIN　テーブル名2　

ON　テーブル1照合用フィールド名=テーブル2照合用フィールド名;

SELECT * FROM commodity c LEFT JOIN trader t

ON c.maker = t.id;
+----+------------------------------+-------+-------+------+------------+---------+--------------+
| id | item | price | maker | id | company | address | tel |
+----+------------------------------+-------+-------+------+------------+---------+--------------+
| 1 | おいしい水 | 190 | 3 | 3 | 札幌農場 | 北海道 | 011-222-2222 |
| 2 | ポテトチップバターしょうゆ味 | 120 | 4 | 4 | 浦安製菓 | 千葉県 | 047-XXX-3333 |
| 3 | カフェ'チョコ | 150 | 4 | 4 | 浦安製菓 | 千葉県 | 047-XXX-3333 |
| 5 | チョコパン | 240 | 1 | 1 | 東京パン | 東京都 | 03-0000-0000 |
| 6 | 米 | 2000 | 2 | 2 | 宇都宮米店 | 栃木県 | 028-111-1111 |
| 7 | たまねぎ | 0 | NULL | NULL | NULL | NULL | NULL |
| 8 | NULL | 9800 | NULL | NULL | NULL | NULL | NULL |
+----+------------------------------+-------+-------+------+------------+---------+--------------+

●RIGHT JOIN：テーブルの結合（外部結合）

SELECT　フィールド名　FROM　テーブル名1　RIGHT　JOIN　テーブル名2　

ON　テーブル1照合用フィールド名=テーブル2照合用フィールド名;

SELECT * FROM commodity c RIGHT JOIN trader t

ON c.maker = t.id;
+------+------------------------------+-------+-------+----+--------------+---------+--------------+
| id | item | price | maker | id | company | address | tel |
+------+------------------------------+-------+-------+----+--------------+---------+--------------+
| 1 | おいしい水 | 190 | 3 | 3 | 札幌農場 | 北海道 | 011-222-2222 |
| 2 | ポテトチップバターしょうゆ味 | 120 | 4 | 4 | 浦安製菓 | 千葉県 | 047-XXX-3333 |
| 3 | カフェ'チョコ | 150 | 4 | 4 | 浦安製菓 | 千葉県 | 047-XXX-3333 |
| 5 | チョコパン | 240 | 1 | 1 | 東京パン | 東京都 | 03-0000-0000 |
| 6 | 米 | 2000 | 2 | 2 | 宇都宮米店 | 栃木県 | 028-111-1111 |
| NULL | NULL | NULL | NULL | 5 | ハイサイパン | 沖縄県 | 098-444-XXXX |
| NULL | NULL | NULL | NULL | 6 | 出雲ファーム | 島根県 | 0853-55-5555 |
+------+------------------------------+-------+-------+----+--------------+---------+--------------+

・内部結合と外部結合との違いは、照合できないレコードは表示されるかされないか

・基本的に左のテーブルが基準になるので、「RIGHT」は使わない

新規ユーザの追加(権限追加)　GRANT

GRANT 権限1,　権限2....

ON　データベース領域名,　テーブル名

TO　ユーザ@ホスト名　IDENTIFIED　BY　パスワード;

GRANT SELECT, INSERT

ON lesson. commodity

TO DBWriter@localhost IDENTIFIED BY 'Kakikomi';

ユーザの追加できたかの確認

●まずMariaDBクライアントプログラム起動

mysql -u DBWriter -p

●とりあえずデータベースのテーブル確認

●データベース領域の変更

USE lesson

●テーブル表示

SHOW TABLES;

+------------------+
| Tables_in_lesson |
+------------------+
| commodity |
+------------------+

●テーブル表示

INSERT INTO commodity SET item='フランスパン', price=230;

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

GRANT ALL ON lesson. * TO LessonMaster@localhost IDENTIFIED BY 'LMPass';

データベースで昨日のテーブル表示

条件をつけて検索する WHERE

範囲・条件を組み合わせ検索する

問題8

部分一致検索する LIKE

検索件数の制限 LIMIT

並び替え ORDER BY

◇問題

計算や集計

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

リレーション

新規ユーザの追加(権限追加) GRANT

ユーザの追加できたかの確認

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

条件をつけて検索する　WHERE

部分一致検索する　LIKE

検索件数の制限　LIMIT

並び替え　ORDER BY

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

新規ユーザの追加(権限追加)　GRANT