
複数テーブルをリレーションするテスト用SQL文
公開日:2019年04月23日
更新日:2019年04月23日
テストテーブル作成
DROP DATABASE IF EXISTS test001; CREATE DATABASE test001; USE test001; CREATE TABLE shop ( id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(10), r_a INT, r_s INT ); INSERT INTO shop (name,r_a,r_s) VALUES ('店舗1',1,1); INSERT INTO shop (name,r_a,r_s) VALUES ('店舗2',1,2); INSERT INTO shop (name,r_a,r_s) VALUES ('店舗3',2,3); INSERT INTO shop (name,r_a,r_s) VALUES ('店舗4',2,4); CREATE TABLE area ( a_id INT, pos CHAR(10) ); INSERT INTO area (a_id,pos) VALUES (1,'豊島区'); INSERT INTO area (a_id,pos) VALUES (2,'台東区'); CREATE TABLE station ( s_id INT, pos CHAR(10) ); INSERT INTO station (s_id,pos) VALUES (1,'池袋'); INSERT INTO station (s_id,pos) VALUES (2,'目白'); INSERT INTO station (s_id,pos) VALUES (3,'上野'); INSERT INTO station (s_id,pos) VALUES (4,'鶯谷'); CREATE TABLE review ( id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(10), shop_id INT ); INSERT INTO review (name,shop_id) VALUES ('レビュー1',1); INSERT INTO review (name,shop_id) VALUES ('レビュー2',1); INSERT INTO review (name,shop_id) VALUES ('レビュー3',2); INSERT INTO review (name,shop_id) VALUES ('レビュー4',2);
shopテーブル
MariaDB [test001]> SELECT * FROM shop; +----+-------+------+------+ | id | name | r_a | r_s | +----+-------+------+------+ | 1 | 店舗1 | 1 | 1 | | 2 | 店舗2 | 1 | 2 | | 3 | 店舗3 | 2 | 3 | | 4 | 店舗4 | 2 | 4 | +----+-------+------+------+
areaテーブル
MariaDB [test001]> SELECT * FROM area; +------+--------+ | a_id | pos | +------+--------+ | 1 | 豊島区 | | 2 | 台東区 | +------+--------+
stationテーブル
MariaDB [test001]> SELECT * FROM station; +------+------+ | s_id | pos | +------+------+ | 1 | 池袋 | | 2 | 目白 | | 3 | 上野 | | 4 | 鶯谷 | +------+------+
reviewテーブル
MariaDB [test001]> SELECT * FROM review; +----+-----------+---------+ | id | name | shop_id | +----+-----------+---------+ | 1 | レビュー1 | 1 | | 2 | レビュー2 | 1 | | 3 | レビュー3 | 2 | | 4 | レビュー4 | 2 | +----+-----------+---------+
shopテーブルとstationテーブルをリレーション
MariaDB [test001]> SELECT * FROM shop -> LEFT JOIN station ON shop.r_s=station.s_id; +----+-------+------+------+------+------+ | id | name | r_a | r_s | s_id | pos | +----+-------+------+------+------+------+ | 1 | 店舗1 | 1 | 1 | 1 | 池袋 | | 2 | 店舗2 | 1 | 2 | 2 | 目白 | | 3 | 店舗3 | 2 | 3 | 3 | 上野 | | 4 | 店舗4 | 2 | 4 | 4 | 鶯谷 | +----+-------+------+------+------+------+
shopテーブルとareaテーブルとstationテーブルをリレーション
MariaDB [test001]> SELECT * FROM shop -> LEFT JOIN area ON shop.r_a=area.a_id -> LEFT JOIN station ON shop.r_s=station.s_id; +----+-------+------+------+------+--------+------+------+ | id | name | r_a | r_s | a_id | pos | s_id | pos | +----+-------+------+------+------+--------+------+------+ | 1 | 店舗1 | 1 | 1 | 1 | 豊島区 | 1 | 池袋 | | 2 | 店舗2 | 1 | 2 | 1 | 豊島区 | 2 | 目白 | | 3 | 店舗3 | 2 | 3 | 2 | 台東区 | 3 | 上野 | | 4 | 店舗4 | 2 | 4 | 2 | 台東区 | 4 | 鶯谷 | +----+-------+------+------+------+--------+------+------+
shopテーブルとareaテーブルとstationテーブルをリレーションして条件指定
MariaDB [test001]> SELECT * FROM shop -> JOIN area ON shop.r_a=area.a_id -> JOIN station ON shop.r_s=station.s_id -> WHERE shop.r_s=1; +----+-------+------+------+------+--------+------+------+ | id | name | r_a | r_s | a_id | pos | s_id | pos | +----+-------+------+------+------+--------+------+------+ | 1 | 店舗1 | 1 | 1 | 1 | 豊島区 | 1 | 池袋 | +----+-------+------+------+------+--------+------+------+
reviewテーブルとshopテーブルとareaテーブルとstationテーブルをリレーション
MariaDB [test001]> SELECT * FROM review -> JOIN shop ON review.shop_id=shop.id -> JOIN area ON shop.r_a=area.a_id -> JOIN station ON shop.r_s=station.s_id; +----+-----------+---------+----+-------+------+------+------+--------+------+------+ | id | name | shop_id | id | name | r_a | r_s | a_id | pos | s_id | pos | +----+-----------+---------+----+-------+------+------+------+--------+------+------+ | 1 | レビュー1 | 1 | 1 | 店舗1 | 1 | 1 | 1 | 豊島区 | 1 | 池袋 | | 2 | レビュー2 | 1 | 1 | 店舗1 | 1 | 1 | 1 | 豊島区 | 1 | 池袋 | | 3 | レビュー3 | 2 | 2 | 店舗2 | 1 | 2 | 1 | 豊島区 | 2 | 目白 | | 4 | レビュー4 | 2 | 2 | 店舗2 | 1 | 2 | 1 | 豊島区 | 2 | 目白 | +----+-----------+---------+----+-------+------+------+------+--------+------+------+
すべてのSQL文
DROP DATABASE IF EXISTS test001; CREATE DATABASE test001; USE test001; CREATE TABLE shop ( id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(10), r_a INT, r_s INT ); INSERT INTO shop (name,r_a,r_s) VALUES ('店舗1',1,1); INSERT INTO shop (name,r_a,r_s) VALUES ('店舗2',1,2); INSERT INTO shop (name,r_a,r_s) VALUES ('店舗3',2,3); INSERT INTO shop (name,r_a,r_s) VALUES ('店舗4',2,4); CREATE TABLE area ( a_id INT, pos CHAR(10) ); INSERT INTO area (a_id,pos) VALUES (1,'豊島区'); INSERT INTO area (a_id,pos) VALUES (2,'台東区'); CREATE TABLE station ( s_id INT, pos CHAR(10) ); INSERT INTO station (s_id,pos) VALUES (1,'池袋'); INSERT INTO station (s_id,pos) VALUES (2,'目白'); INSERT INTO station (s_id,pos) VALUES (3,'上野'); INSERT INTO station (s_id,pos) VALUES (4,'鶯谷'); CREATE TABLE review ( id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(10), shop_id INT ); INSERT INTO review (name,shop_id) VALUES ('レビュー1',1); INSERT INTO review (name,shop_id) VALUES ('レビュー2',1); INSERT INTO review (name,shop_id) VALUES ('レビュー3',2); INSERT INTO review (name,shop_id) VALUES ('レビュー4',2); SELECT * FROM shop; SELECT * FROM area; SELECT * FROM station; SELECT * FROM review; SELECT * FROM shop LEFT JOIN station ON shop.r_s=station.s_id; SELECT * FROM shop LEFT JOIN area ON shop.r_a=area.a_id LEFT JOIN station ON shop.r_s=station.s_id; SELECT * FROM shop JOIN area ON shop.r_a=area.a_id JOIN station ON shop.r_s=station.s_id; SELECT * FROM shop JOIN area ON shop.r_a=area.a_id JOIN station ON shop.r_s=station.s_id WHERE shop.r_s=1; SELECT * FROM review JOIN shop ON review.shop_id=shop.id JOIN area ON shop.r_a=area.a_id JOIN station ON shop.r_s=station.s_id;
同じカテゴリーのコンテンツ