[DB-SQL] 複数テーブル内データを取得して出力結果にまとめて表示する

普段頻繁に触らない&忘れがちなSQL文を自分用に分かりやすくまとめたメモ。

[今回の主題] DBテーブルデータ参照 – 結果出力するSQL文の例

参照対象テーブルの中身の図

【目的】master_tableのデータをsep_table_1~4に分割。その分割したテーブルの情報を参照してmaster_tableと同じ情報を結果に出力できれば成功

【基礎】1つのテーブル内のデータを全て結果に出力


SELECT * FROM master_table;

【基礎】1つのテーブル内の指定カラムのデータが特定の値と同じデータを結果に出力


SELECT * FROM master_table WHERE (
 id = 10001 OR
 id = 10002 OR
 id = 10003 OR
 id = 10004 OR
 id = 10005
);

【複数テーブルからの値参照01】 sep_table_1内のidの値を検索して、sep_table_2内の値とidで一致したsep_table_2のデータを取得して結果に出力


SELECT
 tbl1.id,
 tbl1.name,
 tbl1.age,
 tbl1.gender,
 tbl2.mail
FROM
 sep_table_1 tbl1
INNER JOIN
 sep_table_2 tbl2
ON
 tbl1.id = tbl2.id 
WHERE (
 tbl1.id = 10001 OR
 tbl1.id = 10002 OR
 tbl1.id = 10003 OR
 tbl1.id = 10004 OR
 tbl1.id = 10005
);

【複数テーブルからの値参照02】 sep_table_1内のidの値を検索して、sep_table_2内の値とidで一致したsep_table_2のデータを取得して結果に出力
+ 更に別テーブルの情報も取得(合計4テーブルから情報を取得して結果でまとめて表示)


SELECT
 tbl1.id,
 tbl1.name,
 tbl1.age,
 tbl1.gender,
 tbl2.mail,
 tbl3.address,
 tbl4.memo
FROM ((
 sep_table_1 tbl1
INNER JOIN
 sep_table_2 tbl2
ON
 tbl1.id = tbl2.id
)
INNER JOIN
 sep_table_3 tbl3
ON
 tbl1.id = tbl3.id
)
INNER JOIN
 sep_table_4 tbl4
ON
 tbl1.name = tbl4.name
WHERE (
 tbl1.id = 10001 OR
 tbl1.id = 10002 OR
 tbl1.id = 10003 OR
 tbl1.id = 10004 OR
 tbl1.id = 10005
);

[環境準備] DB環境構築からDB接続までの事前準備

環境を準備する – docker

今回は docker-compose 環境のDBを使ってテストした。
初回の「docker-compose up -d」でサンプルテーブル作成までされる(はず…)

SQLクライアントツールでDBにアクセス 例)Windows – HeidiSQL


  # mysql
  db:
    image: mysql
    # container_name: mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: docker_db
      MYSQL_USER: docker_user
      MYSQL_PASSWORD: docker_pass

docker-compose.yml に書いてある情報を元に docker 内の DB へアクセス。
テーブルの表示が確認出来れば接続成功。

コマンドプロンプト(win) or ターミナル(mac) からDBにアクセス 例)今回はコマンドプロンプト(win)

docker-compose ps –servicedocker-composeで起動中のサービス名を表示する
docker-compose exec db bashdocker-composeで起動中の「DB」へアクセスする
mysql -u docker_user -pmysqlに「docker_user」としてログインする (このあとDBのパスワードを求められる)
use docker_dbdocker_db」へアクセス
show tables;DB内のテーブルを参照
exitmysqlやexecの処理を抜ける

show tables;」とコマンドを打ってもDB内にテーブルが無いと「Empty set」と表示される。
中身があればちゃんと上図のように表示される。

[環境準備] DBにテーブルと中身のデータを作成するSQL文

もしDB内にテーブルが存在しない場合は下記のSQLコマンドを実行すればサンプルと同じ状態を構築できる

サンプルテーブルと中身のデータ作成をするSQL文


-- docker-compose.yml内で設定したDB名
USE docker_db;
-- create test_table
create table IF not exists `test_table`
(
 `id`               INT(20) AUTO_INCREMENT,
 `name`             VARCHAR(20) NOT NULL,
 `created_at`       Datetime DEFAULT NULL,
 `updated_at`       Datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- insert sample data
INSERT INTO test_table VALUES (1,'suzuki',null,null);
INSERT INTO test_table VALUES (2,'tanaka',null,null);
INSERT INTO test_table VALUES (3,'satou',null,null);

-- create master_table
create table IF not exists `master_table`
(
 `id`               INT(5) AUTO_INCREMENT,
 `name`             VARCHAR(20) NOT NULL,
 `age`              INT(3) DEFAULT NULL,
 `gender`           VARCHAR(4) NOT NULL,
 `mail`             VARCHAR(50) NOT NULL,
 `address`          VARCHAR(50) NOT NULL,
 `memo`             VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- insert sample data
INSERT INTO master_table VALUES (10001,'佐藤',30,'男','satou@mail.com','アドレス1','メモ1_佐藤');
INSERT INTO master_table VALUES (10002,'鈴木',12,'女','suzuki@mail.com','アドレス2','メモ2_鈴木');
INSERT INTO master_table VALUES (10003,'田中',45,'男','tanaka@mail.com','アドレス3','メモ3_田中');
INSERT INTO master_table VALUES (10004,'遠藤',34,'男','endou@mail.com','アドレス4','メモ4_遠藤');
INSERT INTO master_table VALUES (10005,'山本',26,'女','yamamoto@mail.com','アドレス5','メモ5_山本');

-- create sep_table_1
create table IF not exists `sep_table_1`
(
 `id`               INT(5) AUTO_INCREMENT,
 `name`             VARCHAR(20) NOT NULL,
 `age`              INT(3) DEFAULT NULL,
 `gender`           VARCHAR(4) NOT NULL,
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- insert sample data
INSERT INTO sep_table_1 VALUES (10001,'佐藤',30,'男');
INSERT INTO sep_table_1 VALUES (10002,'鈴木',12,'女');
INSERT INTO sep_table_1 VALUES (10003,'田中',45,'男');
INSERT INTO sep_table_1 VALUES (10004,'遠藤',34,'男');
INSERT INTO sep_table_1 VALUES (10005,'山本',26,'女');

-- create sep_table_2
create table IF not exists `sep_table_2`
(
 `id`               INT(5) AUTO_INCREMENT,
 `mail`             VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- insert sample data
INSERT INTO sep_table_2 VALUES (10001,'satou@mail.com');
INSERT INTO sep_table_2 VALUES (10002,'suzuki@mail.com');
INSERT INTO sep_table_2 VALUES (10003,'tanaka@mail.com');
INSERT INTO sep_table_2 VALUES (10004,'endou@mail.com');
INSERT INTO sep_table_2 VALUES (10005,'yamamoto@mail.com');

-- create sep_table_3
create table IF not exists `sep_table_3`
(
 `id`               INT(5) AUTO_INCREMENT,
 `address`             VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- insert sample data
INSERT INTO sep_table_3 VALUES (10001,'アドレス1');
INSERT INTO sep_table_3 VALUES (10002,'アドレス2');
INSERT INTO sep_table_3 VALUES (10003,'アドレス3');
INSERT INTO sep_table_3 VALUES (10004,'アドレス4');
INSERT INTO sep_table_3 VALUES (10005,'アドレス5');

-- create sep_table_4
create table IF not exists `sep_table_4`
(
 `name`             VARCHAR(20) NOT NULL,
 `memo`             VARCHAR(50) NOT NULL,
    PRIMARY KEY (`name`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- insert sample data
INSERT INTO sep_table_4 VALUES ('佐藤','メモ1_佐藤');
INSERT INTO sep_table_4 VALUES ('鈴木','メモ2_鈴木');
INSERT INTO sep_table_4 VALUES ('田中','メモ3_田中');
INSERT INTO sep_table_4 VALUES ('遠藤','メモ4_遠藤');
INSERT INTO sep_table_4 VALUES ('山本','メモ5_山本');

Share