概要と目標
データベースにおける一連の処理と
正規化について学ぼう。
一連の処理を管理するトランザクションについてと、
テーブルの設計に役立つ正規化について学習しましょう。
コマンドプロンプト
mysql> create database sales_management default character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> use sales_management;
Database changed
mysql> create table detail (
-> id int(11) unsigned auto_increment primary key,
-> sales_id int(6) unsigned zerofill not null,
-> product_id int(11) unsigned not null,
-> quantity int(11) unsigned not null
-> ) engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> create table sales (
-> id int(6) unsigned zerofill auto_increment primary key,
-> order_date date not null,
-> customer_id int(11) unsigned not null
-> ) engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> create table product (
-> id int(11) unsigned auto_increment primary key,
-> name varchar(255) not null,
-> price int(11) unsigned not null
-> ) engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> create table customer (
-> id int(11) unsigned auto_increment primary key,
-> name varchar(255) not null,
-> pref varchar(255) not null
-> ) engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into detail (sales_id, product_id, quantity)
-> values (
-> 1, 1, 2
-> ),
-> (
-> 1, 2, 1
-> ),
-> (
-> 1, 3, 5
-> ),
-> (
-> 2, 4, 2
-> ),
-> (
-> 2, 5, 1
-> ),
-> (
-> 3, 1, 18
-> ),
-> (
-> 3, 5, 1
-> ),
-> (
-> 3, 6, 8
-> );
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into sales (order_date, customer_id)
-> values (
-> '2025-12-10', 1
-> ),
-> (
-> '2025-12-12', 2
-> ),
-> (
-> '2025-12-14', 3
-> );
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into product (name, price)
-> values (
-> 'えんぴつ', 50
-> ),
-> (
-> '消しゴム', 100
-> ),
-> (
-> 'のり', 60
-> ),
-> (
-> 'ものさし', 80
-> ),
-> (
-> 'ノート', 100
-> ),
-> (
-> '文鎮', 100
-> );
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into customer (name, pref)
-> values (
-> '秋元', '東京都'
-> ),
-> (
-> '前田', '東京都'
-> ),
-> (
-> '柴田', '大阪府'
-> );
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from product;
+----+--------------+-------+
| id | name | price |
+----+--------------+-------+
| 1 | えんぴつ | 50 |
| 2 | 消しゴム | 100 |
| 3 | のり | 60 |
| 4 | ものさし | 80 |
| 5 | ノート | 100 |
| 6 | 文鎮 | 100 |
+----+--------------+-------+
6 rows in set (0.00 sec)
mysql> select * from customer where pref = '東京都';
+----+--------+-----------+
| id | name | pref |
+----+--------+-----------+
| 1 | 秋元 | 東京都 |
| 2 | 前田 | 東京都 |
+----+--------+-----------+
2 rows in set (0.00 sec)
mysql> select count(*) as total from sales;
+-------+
| total |
+-------+
| 3 |
+-------+
1 row in set (0.00 sec)
mysql> select sales_id, sum(quantity) as total
-> from detail
-> group by sales_id;
+----------+-------+
| sales_id | total |
+----------+-------+
| 000001 | 8 |
| 000002 | 3 |
| 000003 | 27 |
+----------+-------+
3 rows in set (0.00 sec)
mysql> select d.sales_id, p.name, d.quantity
-> from detail as d join product as p
-> on d.product_id = p.id
-> order by d.sales_id asc;
+----------+--------------+----------+
| sales_id | name | quantity |
+----------+--------------+----------+
| 000001 | えんぴつ | 2 |
| 000001 | 消しゴム | 1 |
| 000001 | のり | 5 |
| 000002 | ものさし | 2 |
| 000002 | ノート | 1 |
| 000003 | えんぴつ | 18 |
| 000003 | ノート | 1 |
| 000003 | 文鎮 | 8 |
+----------+--------------+----------+
8 rows in set (0.01 sec)
mysql> select
-> d.sales_id,
-> s.order_date,
-> c.name as customer_name,
-> c.pref,
-> p.name as product_name,
-> p.price,
-> d.quantity
-> from
-> detail as d join
-> sales as s join
-> product as p join
-> customer as c
-> on
-> d.sales_id = s.id and
-> d.product_id = p.id and
-> s.customer_id = c.id
-> order by
-> d.sales_id asc;
+----------+------------+---------------+-----------+--------------+-------+----------+
| sales_id | order_date | customer_name | pref | product_name | price | quantity |
+----------+------------+---------------+-----------+--------------+-------+----------+
| 000001 | 2025-12-10 | 秋元 | 東京都 | えんぴつ | 50 | 2 |
| 000001 | 2025-12-10 | 秋元 | 東京都 | 消しゴム | 100 | 1 |
| 000001 | 2025-12-10 | 秋元 | 東京都 | のり | 60 | 5 |
| 000002 | 2025-12-12 | 前田 | 東京都 | ものさし | 80 | 2 |
| 000002 | 2025-12-12 | 前田 | 東京都 | えんぴつ | 50 | 1 |
| 000003 | 2025-12-14 | 柴田 | 大阪府 | えんぴつ | 50 | 18 |
| 000003 | 2025-12-14 | 柴田 | 大阪府 | ノート | 100 | 1 |
| 000003 | 2025-12-14 | 柴田 | 大阪府 | 文鎮 | 100 | 8 |
+----------+------------+--------+-----------+--------------+-------+----------+
8 rows in set (0.00 sec)
mysql> 
