Learning site for website creation

複数テーブルをリレーションするテスト用SQL文

  • 投稿日：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;
B!

関連コンテンツ：MySQL

MySQL管理者にパスワード設定

2020年02月14日

  • MySQL

phpMyAdminを使ってデータベースを復元

2020年02月07日

  • MySQL

phpMyAdminを使ってデータベースをバックアップ

2020年02月07日

  • MySQL
MySQLコンテンツをもっと見る