このセミナーは「リレーショナルデータベースの必須技術「正規化」を学ぼう」で学んだテーブル定義を用いてSQLの実践的な書き方を学びます。「リレーショナルデータベースの必須技術「正規化」を学ぼう」を視聴している方がテーブル定義の背景などを理解して臨めますのでベストですが視聴していなくても問題なくSQL文の勉強ができるセミナーになっています。
エンジニア志望学生であり、SQL初心者から中級者
単一テーブルに対してSELECT文が書ける、条件句で絞り込みができる。UPDATE、INSERT、DELETE文が書ける、条件句で絞り込みができる。
複数テーブルを結合してSELECT文が書ける。同じ結果となるSQL文を複数書き分けることができる。
SQL文をパフォーマンス視点で最適なSQL文を導き出せる
このセミナーではMySQLをベースにSQL文について解説しています。
このセミナーはライブコーディング形式で進めます。セミナー中は自身でSQLを書く時間はありませんので事前に回答を元にSQLを書いてみるか、ライブコーディング後に書いてみることをおすすめします。
MySQLが動作し、SQLが発行できる環境(ライブコーディングの視聴のみでも学べる構成にしてありますが実際に自分でSQLを書く方が学びが多いため推奨)
概要に基づいて制約など盛り込んだテーブル定義が以下です。以下のCREATE TABLE文を任意のMySQL DATABASEで実行し作成しましょう。
DROP TABLE IF EXISTS order_details;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
address VARCHAR(100),
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE items (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE orders (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
order_date DATE NOT NULL,
customer_id INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE order_details (
order_id INT UNSIGNED NOT NULL,
item_id INT UNSIGNED NOT NULL,
item_quantity INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY(order_id,item_id),
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (item_id) REFERENCES items(id)
);
insert into customers(id,name,address,created_at,updated_at) values(1,'A商事','東京都',now(),now()),(2,'B商会','埼玉県',now(),now()),(3,'C商店','神奈川県',now(),now());
insert into items(id,name,price,created_at,updated_at) values(1,'シャツ',1000,now(),now()),(2,'パンツ',950,now(),now()),(3,'マフラー',1200,now(),now()),(4,'ブルゾン',1800,now(),now());
insert into orders(id,order_date,customer_id,created_at,updated_at) values(1 , '2013-10-01',1,now(),now()),(2 , '2013-10-01',2,now(),now()),(3 , '2013-10-02',2,now(),now()),(4 , '2013-10-02',3,now(),now());
insert into order_details(order_id,item_id,item_quantity,created_at,updated_at) values(1 , 1 ,3,now(),now()),(1 , 2 ,2,now(),now()),(2 , 1 ,1,now(),now()),(2 , 3 ,10,now(),now()),(2 , 4 ,5,now(),now()),(3 , 2 ,80,now(),now()),(4 , 3 ,25,now(),now());
抽出結果
+----------------+
| Tables_in_test |
+----------------+
| customers |
| items |
| order_details |
| orders |
+----------------+
4 rows in set (0.00 sec)
+----+---------+--------------+---------------------+---------------------+
| id | name | address | created_at | updated_at |
+----+---------+--------------+---------------------+---------------------+
| 1 | A商事 | 東京都 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 2 | B商会 | 埼玉県 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 3 | C商店 | 神奈川県 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
+----+---------+--------------+---------------------+---------------------+
3 rows in set (0.00 sec)
+----+--------------+-------+---------------------+---------------------+
| id | name | price | created_at | updated_at |
+----+--------------+-------+---------------------+---------------------+
| 1 | シャツ | 1000 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 2 | パンツ | 950 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 3 | マフラー | 1200 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 4 | ブルゾン | 1800 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
+----+--------------+-------+---------------------+---------------------+
4 rows in set (0.00 sec)
+----------+---------+---------------+---------------------+---------------------+
| order_id | item_id | item_quantity | created_at | updated_at |
+----------+---------+---------------+---------------------+---------------------+
| 1 | 1 | 3 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 1 | 2 | 2 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 2 | 1 | 1 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 2 | 3 | 10 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 2 | 4 | 5 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 3 | 2 | 80 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 4 | 3 | 25 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
+----------+---------+---------------+---------------------+---------------------+
7 rows in set (0.00 sec)
+----+------------+-------------+---------------------+---------------------+
| id | order_date | customer_id | created_at | updated_at |
+----+------------+-------------+---------------------+---------------------+
| 1 | 2013-10-01 | 1 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 2 | 2013-10-01 | 2 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 3 | 2013-10-02 | 2 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
| 4 | 2013-10-02 | 3 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
+----+------------+-------------+---------------------+---------------------+
4 rows in set (0.00 sec)
抽出結果
+----------+
| proceeds |
+----------+
| 4000 |
+----------+
1 row in set (0.01 sec)
結合順序を変えてみましょう
抽出結果(ランダムに抽出するため同じになるとは限りません)
+----+-----------+-------+---------------------+------------+
| id | name | price | created_at | updated_at |
+----+-----------+-------+---------------------+------------+
| 2 | パンツ | 950 | 2017-09-08 16:47:09 | NULL |
+----+-----------+-------+---------------------+------------+
1 row in set (0.00 sec)
ランダム抽出の内部の動作について調べましょう。調査を踏まえ注意すべき点などないかなど考えてみましょう
SELECT文での「*
(アスタリスク」についてどのようなケースで利用して良いか考えてみましょう
抽出結果
+----------+
| order_id |
+----------+
| 3 |
| 2 |
| 1 |
+----------+
3 rows in set (0.00 sec)
Q3のSQL文をunion句を用いて同じ結果になるSELECT文を構築しましょう
抽出結果
+----------+
| order_id |
+----------+
| 3 |
| 2 |
| 1 |
+----------+
3 rows in set (0.00 sec)
Q3のSQL文をgroup by句を用いて同じ結果になるSELECT文を構築しましょう
抽出結果
+----------+
| order_id |
+----------+
| 3 |
| 2 |
| 1 |
+----------+
3 rows in set (0.00 sec)
抽出結果
+-----------------+
| avg_order_price |
+-----------------+
| 33225.0000 |
+-----------------+
1 row in set (0.00 sec)
受注の件数も一緒に取得しましょう
抽出結果
+-----------------+-------------+
| avg_order_price | order_count |
+-----------------+-------------+
| 33225.0000 | 4 |
+-----------------+-------------+
1 row in set (0.00 sec)
抽出結果
+----+-------------+
| id | order_price |
+----+-------------+
| 3 | 76000 |
+----+-------------+
1 row in set (0.00 sec)
今回の回答が正しいか考えてみましょう
商品 | 単価 |
---|---|
タンクトップ | 1300 |
ジャンパー | 2500 |
ソックス | 600 |
ヒント バルクインサートを検索しましょう
+----+--------------------+-------+---------------------+---------------------+
| id | name | price | created_at | updated_at |
+----+--------------------+-------+---------------------+---------------------+
| 1 | シャツ | 1000 | 2020-04-15 10:11:39 | 2020-04-15 10:11:39 |
| 2 | パンツ | 950 | 2020-04-15 10:11:39 | 2020-04-15 10:11:39 |
| 3 | マフラー | 1200 | 2020-04-15 10:11:39 | 2020-04-15 10:11:39 |
| 4 | ブルゾン | 1800 | 2020-04-15 10:11:39 | 2020-04-15 10:11:39 |
| 5 | タンクトップ | 1300 | 2020-05-06 01:25:01 | 2020-05-06 01:25:01 |
| 6 | ジャンパー | 1500 | 2020-05-06 01:25:01 | 2020-05-06 01:25:01 |
| 7 | ソックス | 600 | 2020-05-06 01:25:01 | 2020-05-06 01:25:01 |
+----+--------------------+-------+---------------------+---------------------+
7 rows in set (0.00 sec)
今回の回答に至ったinsert文のメリットを考えてみましょう
抽出例
+-----------+
| sum_price |
+-----------+
| 98000 |
+-----------+
1 row in set (0.00 sec)
抽出例
+---------+--------------+
| item_id | name |
+---------+--------------+
| 1 | シャツ |
| 2 | パンツ |
| 3 | マフラー |
| 4 | ブルゾン |
+---------+--------------+
4 rows in set (0.00 sec)
ヒント:「inner join」「exists」「in」で一つずつ構築しましょう
抽出例
+----+--------------------+
| id | name |
+----+--------------------+
| 5 | タンクトップ |
| 6 | ジャンパー |
| 7 | ソックス |
+----+--------------------+
3 rows in set (0.00 sec)
Q10 全アイテム(items)に存在する商品を受注明細(order_details)から各商品ごとの注文回数(抽出例はitem_idが1~7固定のものとする)を求めよ、全注文数(all_order)も求めよ
抽出例
+-----------+------+------+------+------+------+------+------+
| all_order | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
+-----------+------+------+------+------+------+------+------+
| 7 | 2 | 2 | 2 | 1 | 0 | 0 | 0 |
+-----------+------+------+------+------+------+------+------+
1 row in set (0.00 sec)
抽出例
+----+-------+
| id | count |
+----+-------+
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 1 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
+----+-------+
7 rows in set (0.00 sec)
抽出例
+-----------+-------+
| all_order | count |
+-----------+-------+
| all_order | 7 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 1 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
+-----------+-------+
8 rows in set (0.00 sec)
抽出例
+----+----------+
| id | order_id |
+----+----------+
| 1 | 1,2 |
| 2 | 1,3 |
| 3 | 2,4 |
| 4 | 2 |
| 5 | NULL |
| 6 | NULL |
| 7 | NULL |
+----+----------+
7 rows in set (0.00 sec)