【練習】MySQL(抽出篇)
【抽出】
mysql> select * from 商品表2;
+--------+--------------------+--------+
| 番号 | 品名 | 価格 |
+--------+--------------------+--------+
| 010 | パソコン本体 | 80,000 |
| 011 | ディスプレイ | 35,000 |
| 020 | プリンタ | 25,000 |
| 025 | キーボード | 10,000 |
| 030 | マウス | 3,000 |
+--------+--------------------+--------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from 商品表2
-> where 番号='010' or 番号='020' or 番号='030';
+--------+--------------------+--------+
| 番号 | 品名 | 価格 |
+--------+--------------------+--------+
| 010 | パソコン本体 | 80,000 |
| 020 | プリンタ | 25,000 |
| 030 | マウス | 3,000 |
+--------+--------------------+--------+
3 rows in set (0.00 sec)
mysql>
【～IN～】
→不連続な値や値の数が多い場合に指定する
mysql> select * from 商品表2
-> where 番号 in ('010','020','030');
+--------+--------------------+--------+
| 番号 | 品名 | 価格 |
+--------+--------------------+--------+
| 010 | パソコン本体 | 80,000 |
| 020 | プリンタ | 25,000 |
| 030 | マウス | 3,000 |
+--------+--------------------+--------+
3 rows in set (0.00 sec)
mysql>
【結合】
mysql> select * from 受注表;
+--------------+--------------+
| 顧客名 | 商品番号 |
+--------------+--------------+
| 大山商店 | TV28 |
| 大山商店 | TV28W |
| 大山商店 | TV32 |
| 小川商店 | TV32 |
| 小川商店 | TV32W |
+--------------+--------------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> select * from 商品表;
+--------------+----------------+--------+
| 商品番号 | 商品名 | 単価 |
+--------------+----------------+--------+
| TV28 | 28型テレビ | 25000 |
| TV28W | 28型テレビ | 25000 |
| TV32 | 32型テレビ | 30000 |
| TV32W | 32型テレビ | 30000 |
+--------------+----------------+--------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql> select 顧客名,商品名,単価
-> from 受注表,商品表
-> where 受注表.商品番号=商品表.商品番号;
+--------------+----------------+--------+
| 顧客名 | 商品名 | 単価 |
+--------------+----------------+--------+
| 大山商店 | 28型テレビ | 25000 |
| 大山商店 | 28型テレビ | 25000 |
| 大山商店 | 32型テレビ | 30000 |
| 小川商店 | 32型テレビ | 30000 |
| 小川商店 | 32型テレビ | 30000 |
+--------------+----------------+--------+
5 rows in set (0.01 sec)
mysql>
mysql>
【～distinct～】
→重複した行を一つにまとめることができる
mysql> select distinct 顧客名,商品名,単価
-> from 受注表,商品表
-> where 受注表.商品番号=商品表.商品番号;
+--------------+----------------+--------+
| 顧客名 | 商品名 | 単価 |
+--------------+----------------+--------+
| 大山商店 | 28型テレビ | 25000 |
| 大山商店 | 32型テレビ | 30000 |
| 小川商店 | 32型テレビ | 30000 |
+--------------+----------------+--------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from 出庫記録;
+--------------+------------+--------+
| 商品番号 | 日付 | 数量 |
+--------------+------------+--------+
| 200 | 2000-10-10 | 3 |
| 400 | 2000-10-11 | 1 |
| 100 | 2000-10-10 | 1 |
| 300 | 2000-10-11 | 2 |
+--------------+------------+--------+
4 rows in set (0.00 sec)
mysql>
【昇順＆降順】
mysql> select * from 出庫記録
-> order by 日付 asc;
+--------------+------------+--------+
| 商品番号 | 日付 | 数量 |
+--------------+------------+--------+
| 200 | 2000-10-10 | 3 |
| 100 | 2000-10-10 | 1 |
| 400 | 2000-10-11 | 1 |
| 300 | 2000-10-11 | 2 |
+--------------+------------+--------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from 出庫記録 order by 日付 desc;
+--------------+------------+--------+
| 商品番号 | 日付 | 数量 |
+--------------+------------+--------+
| 400 | 2000-10-11 | 1 |
| 300 | 2000-10-11 | 2 |
| 200 | 2000-10-10 | 3 |
| 100 | 2000-10-10 | 1 |
+--------------+------------+--------+
4 rows in set (0.01 sec)
mysql>
mysql>
mysql>
mysql> select * from 販売表;
+-----------+-----------------+--------------+
| 得意先 | 商品コード | 販売数量 |
+-----------+-----------------+--------------+
| K商会 | A5023 | 100 |
| S商店 | A5023 | 150 |
| K商会 | A5025 | 120 |
| K商会 | A5027 | 100 |
| S商店 | A5027 | 160 |
+-----------+-----------------+--------------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> select 商品コード, sum(販売数量) from 販売表;
+-----------------+-------------------+
| 商品コード | sum(販売数量) |
+-----------------+-------------------+
| A5023 | 630 |
+-----------------+-------------------+
1 row in set (0.00 sec)
【SUM(列名)】
→指定した列の合計を求める
mysql> select 商品コード, sum(販売数量) from 販売表
-> group by 商品コード;
+-----------------+-------------------+
| 商品コード | sum(販売数量) |
+-----------------+-------------------+
| A5023 | 250 |
| A5025 | 120 |
| A5027 | 260 |
+-----------------+-------------------+
3 rows in set (0.00 sec)
mysql>
mysql>
【MAX(列名)】
→指定した列の最大値を求める
mysql> select 商品コード, max(販売数量) from 販売表
-> group by 商品コード;
+-----------------+-------------------+
| 商品コード | max(販売数量) |
+-----------------+-------------------+
| A5023 | 150 |
| A5025 | 120 |
| A5027 | 160 |
+-----------------+-------------------+
3 rows in set (0.00 sec)
mysql>
【COUNT(*)】
→指定した行数を求める
mysql> select 商品コード, count(*) from 販売表
-> group by 商品コード;
+-----------------+----------+
| 商品コード | count(*) |
+-----------------+----------+
| A5023 | 2 |
| A5025 | 1 |
| A5027 | 2 |
+-----------------+----------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql>
【AVG(列名)】
→指定した列の平均を求める
mysql> select 商品コード, avg(販売数量) from 販売表
-> group by 商品コード;
+-----------------+-------------------+
| 商品コード | avg(販売数量) |
+-----------------+-------------------+
| A5023 | 125.0000 |
| A5025 | 120.0000 |
| A5027 | 130.0000 |
+-----------------+-------------------+
3 rows in set (0.00 sec)
mysql>
【MIN(列名)】
→指定した列の最小値を求める
mysql>
mysql> select 商品コード, min(販売数量) from 販売表
-> group by 商品コード
-> ;
+-----------------+-------------------+
| 商品コード | min(販売数量) |
+-----------------+-------------------+
| A5023 | 100 |
| A5025 | 120 |
| A5027 | 100 |
+-----------------+-------------------+
3 rows in set (0.00 sec)
mysql>
mysql>
【AS】
→別名をつける
mysql> select 商品コード, sum(販売数量) as 合計数量 from 販売表
-> group by 商品コード;
+-----------------+--------------+
| 商品コード | 合計数量 |
+-----------------+--------------+
| A5023 | 250 |
| A5025 | 120 |
| A5027 | 260 |
+-----------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> select 商品コード, AVG(販売数量) as 平均数量 from 販売表
-> group by 商品コード;
+-----------------+--------------+
| 商品コード | 平均数量 |
+-----------------+--------------+
| A5023 | 125.0000 |
| A5025 | 120.0000 |
| A5027 | 130.0000 |
+-----------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> select 商品コード, MAX(販売数量) as 最大数量 from 販売表
-> group by 商品コード;
+-----------------+--------------+
| 商品コード | 最大数量 |
+-----------------+--------------+
| A5023 | 150 |
| A5025 | 120 |
| A5027 | 160 |
+-----------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> select 商品コード, MIN(販売数量) as 最小数量 from 販売表
-> group by 商品コード;
+-----------------+--------------+
| 商品コード | 最小数量 |
+-----------------+--------------+
| A5023 | 100 |
| A5025 | 120 |
| A5027 | 100 |
+-----------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> select 商品コード, count(*) as 合計行数 from 販売表
-> group by 商品コード;
+-----------------+--------------+
| 商品コード | 合計行数 |
+-----------------+--------------+
| A5023 | 2 |
| A5025 | 1 |
| A5027 | 2 |
+-----------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> select 商品コード, sum(販売数量) as 合計行数 from 販売表
-> group by 商品コード having sum(販売数量)>200;
+-----------------+--------------+
| 商品コード | 合計行数 |
+-----------------+--------------+
| A5023 | 250 |
| A5027 | 260 |
+-----------------+--------------+
2 rows in set (0.00 sec)
mysql>
mysql>
【HAVING】
→グループに対する条件を付ける
mysql>
mysql> select 商品コード, sum(販売数量) as 合計行数 from 販売表
-> group by 商品コード having sum(販売数量)>100;
+-----------------+--------------+
| 商品コード | 合計行数 |
+-----------------+--------------+
| A5023 | 250 |
| A5025 | 120 |
| A5027 | 260 |
+-----------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from 商品表2;
+--------+--------------------+--------+
| 番号 | 品名 | 価格 |
+--------+--------------------+--------+
| 010 | パソコン本体 | 80,000 |
| 011 | ディスプレイ | 35,000 |
| 020 | プリンタ | 25,000 |
| 025 | キーボード | 10,000 |
| 030 | マウス | 3,000 |
+--------+--------------------+--------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from 商品表2
-> where 番号='010' or 番号='020' or 番号='030';
+--------+--------------------+--------+
| 番号 | 品名 | 価格 |
+--------+--------------------+--------+
| 010 | パソコン本体 | 80,000 |
| 020 | プリンタ | 25,000 |
| 030 | マウス | 3,000 |
+--------+--------------------+--------+
3 rows in set (0.00 sec)
mysql>
【～IN～】
→不連続な値や値の数が多い場合に指定する
mysql> select * from 商品表2
-> where 番号 in ('010','020','030');
+--------+--------------------+--------+
| 番号 | 品名 | 価格 |
+--------+--------------------+--------+
| 010 | パソコン本体 | 80,000 |
| 020 | プリンタ | 25,000 |
| 030 | マウス | 3,000 |
+--------+--------------------+--------+
3 rows in set (0.00 sec)
mysql>
【結合】
mysql> select * from 受注表;
+--------------+--------------+
| 顧客名 | 商品番号 |
+--------------+--------------+
| 大山商店 | TV28 |
| 大山商店 | TV28W |
| 大山商店 | TV32 |
| 小川商店 | TV32 |
| 小川商店 | TV32W |
+--------------+--------------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> select * from 商品表;
+--------------+----------------+--------+
| 商品番号 | 商品名 | 単価 |
+--------------+----------------+--------+
| TV28 | 28型テレビ | 25000 |
| TV28W | 28型テレビ | 25000 |
| TV32 | 32型テレビ | 30000 |
| TV32W | 32型テレビ | 30000 |
+--------------+----------------+--------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql> select 顧客名,商品名,単価
-> from 受注表,商品表
-> where 受注表.商品番号=商品表.商品番号;
+--------------+----------------+--------+
| 顧客名 | 商品名 | 単価 |
+--------------+----------------+--------+
| 大山商店 | 28型テレビ | 25000 |
| 大山商店 | 28型テレビ | 25000 |
| 大山商店 | 32型テレビ | 30000 |
| 小川商店 | 32型テレビ | 30000 |
| 小川商店 | 32型テレビ | 30000 |
+--------------+----------------+--------+
5 rows in set (0.01 sec)
mysql>
mysql>
【～distinct～】
→重複した行を一つにまとめることができる
mysql> select distinct 顧客名,商品名,単価
-> from 受注表,商品表
-> where 受注表.商品番号=商品表.商品番号;
+--------------+----------------+--------+
| 顧客名 | 商品名 | 単価 |
+--------------+----------------+--------+
| 大山商店 | 28型テレビ | 25000 |
| 大山商店 | 32型テレビ | 30000 |
| 小川商店 | 32型テレビ | 30000 |
+--------------+----------------+--------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from 出庫記録;
+--------------+------------+--------+
| 商品番号 | 日付 | 数量 |
+--------------+------------+--------+
| 200 | 2000-10-10 | 3 |
| 400 | 2000-10-11 | 1 |
| 100 | 2000-10-10 | 1 |
| 300 | 2000-10-11 | 2 |
+--------------+------------+--------+
4 rows in set (0.00 sec)
mysql>
【昇順＆降順】
mysql> select * from 出庫記録
-> order by 日付 asc;
+--------------+------------+--------+
| 商品番号 | 日付 | 数量 |
+--------------+------------+--------+
| 200 | 2000-10-10 | 3 |
| 100 | 2000-10-10 | 1 |
| 400 | 2000-10-11 | 1 |
| 300 | 2000-10-11 | 2 |
+--------------+------------+--------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from 出庫記録 order by 日付 desc;
+--------------+------------+--------+
| 商品番号 | 日付 | 数量 |
+--------------+------------+--------+
| 400 | 2000-10-11 | 1 |
| 300 | 2000-10-11 | 2 |
| 200 | 2000-10-10 | 3 |
| 100 | 2000-10-10 | 1 |
+--------------+------------+--------+
4 rows in set (0.01 sec)
mysql>
mysql>
mysql>
mysql> select * from 販売表;
+-----------+-----------------+--------------+
| 得意先 | 商品コード | 販売数量 |
+-----------+-----------------+--------------+
| K商会 | A5023 | 100 |
| S商店 | A5023 | 150 |
| K商会 | A5025 | 120 |
| K商会 | A5027 | 100 |
| S商店 | A5027 | 160 |
+-----------+-----------------+--------------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> select 商品コード, sum(販売数量) from 販売表;
+-----------------+-------------------+
| 商品コード | sum(販売数量) |
+-----------------+-------------------+
| A5023 | 630 |
+-----------------+-------------------+
1 row in set (0.00 sec)
【SUM(列名)】
→指定した列の合計を求める
mysql> select 商品コード, sum(販売数量) from 販売表
-> group by 商品コード;
+-----------------+-------------------+
| 商品コード | sum(販売数量) |
+-----------------+-------------------+
| A5023 | 250 |
| A5025 | 120 |
| A5027 | 260 |
+-----------------+-------------------+
3 rows in set (0.00 sec)
mysql>
mysql>
【MAX(列名)】
→指定した列の最大値を求める
mysql> select 商品コード, max(販売数量) from 販売表
-> group by 商品コード;
+-----------------+-------------------+
| 商品コード | max(販売数量) |
+-----------------+-------------------+
| A5023 | 150 |
| A5025 | 120 |
| A5027 | 160 |
+-----------------+-------------------+
3 rows in set (0.00 sec)
mysql>
【COUNT(*)】
→指定した行数を求める
mysql> select 商品コード, count(*) from 販売表
-> group by 商品コード;
+-----------------+----------+
| 商品コード | count(*) |
+-----------------+----------+
| A5023 | 2 |
| A5025 | 1 |
| A5027 | 2 |
+-----------------+----------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql>
【AVG(列名)】
→指定した列の平均を求める
mysql> select 商品コード, avg(販売数量) from 販売表
-> group by 商品コード;
+-----------------+-------------------+
| 商品コード | avg(販売数量) |
+-----------------+-------------------+
| A5023 | 125.0000 |
| A5025 | 120.0000 |
| A5027 | 130.0000 |
+-----------------+-------------------+
3 rows in set (0.00 sec)
mysql>
【MIN(列名)】
→指定した列の最小値を求める
mysql>
mysql> select 商品コード, min(販売数量) from 販売表
-> group by 商品コード
-> ;
+-----------------+-------------------+
| 商品コード | min(販売数量) |
+-----------------+-------------------+
| A5023 | 100 |
| A5025 | 120 |
| A5027 | 100 |
+-----------------+-------------------+
3 rows in set (0.00 sec)
mysql>
mysql>
【AS】
→別名をつける
mysql> select 商品コード, sum(販売数量) as 合計数量 from 販売表
-> group by 商品コード;
+-----------------+--------------+
| 商品コード | 合計数量 |
+-----------------+--------------+
| A5023 | 250 |
| A5025 | 120 |
| A5027 | 260 |
+-----------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> select 商品コード, AVG(販売数量) as 平均数量 from 販売表
-> group by 商品コード;
+-----------------+--------------+
| 商品コード | 平均数量 |
+-----------------+--------------+
| A5023 | 125.0000 |
| A5025 | 120.0000 |
| A5027 | 130.0000 |
+-----------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> select 商品コード, MAX(販売数量) as 最大数量 from 販売表
-> group by 商品コード;
+-----------------+--------------+
| 商品コード | 最大数量 |
+-----------------+--------------+
| A5023 | 150 |
| A5025 | 120 |
| A5027 | 160 |
+-----------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> select 商品コード, MIN(販売数量) as 最小数量 from 販売表
-> group by 商品コード;
+-----------------+--------------+
| 商品コード | 最小数量 |
+-----------------+--------------+
| A5023 | 100 |
| A5025 | 120 |
| A5027 | 100 |
+-----------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> select 商品コード, count(*) as 合計行数 from 販売表
-> group by 商品コード;
+-----------------+--------------+
| 商品コード | 合計行数 |
+-----------------+--------------+
| A5023 | 2 |
| A5025 | 1 |
| A5027 | 2 |
+-----------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> select 商品コード, sum(販売数量) as 合計行数 from 販売表
-> group by 商品コード having sum(販売数量)>200;
+-----------------+--------------+
| 商品コード | 合計行数 |
+-----------------+--------------+
| A5023 | 250 |
| A5027 | 260 |
+-----------------+--------------+
2 rows in set (0.00 sec)
mysql>
mysql>
【HAVING】
→グループに対する条件を付ける
mysql>
mysql> select 商品コード, sum(販売数量) as 合計行数 from 販売表
-> group by 商品コード having sum(販売数量)>100;
+-----------------+--------------+
| 商品コード | 合計行数 |
+-----------------+--------------+
| A5023 | 250 |
| A5025 | 120 |
| A5027 | 260 |
+-----------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql>