MySQL マルチテーブルクエリ例の詳しい解説 [リンククエリ、サブクエリなど]

この記事では、例を挙げて MySQL のマルチテーブルクエリについて説明します。ご参考までに、詳細は以下の通りです。

準備: 部門テーブル（department）と従業員テーブル（employee）の2つのテーブルを準備します。

テーブル部門を作成(
id int、
名前varchar(20)
);
従業員テーブルを作成する(
id int 主キー auto_increment,
名前varchar(20),
性別 enum('男性','女性') は null ではない デフォルトは '男性'、
年齢 int、
dep_id 整数
);

#部門値にデータを挿入する
（200、「テクノロジー」）
（201、「人事」）
（202、「販売」）、
（203、「作戦」）
従業員(名前、性別、年齢、所属ID)の値を挿入する
('エゴン','男性',18,200),
('アレックス','女性',48,201),
('wupeiqi','男性',38,201),
('yuanhao','女性',28,202),
('nvshen','男性',18,200),
('xiaomage','女性',18,204)
;

# テーブル構造とデータを表示します。mysql> desc department;
+-------+-------------+------+------+--------+-------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+-------+-------------+------+------+--------+-------+
| id | int(11) | はい | | NULL | |
| 名前 | varchar(20) | はい | | NULL | |
+-------+-------------+------+------+--------+-------+
セット2行（0.19秒）

mysql> 従業員の説明;
+--------+-----------------------+------+-----+---------+----------------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | 自動増分 |
| 名前 | varchar(20) | はい | | NULL | |
| 性別 | enum('男性','女性') | NO | | 男性 | |
| 年齢 | int(11) | はい | | NULL | |
| dep_id | int(11) | はい | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
セット内の 5 行 (0.01 秒)

mysql> 部門から * を選択します。
+------+--------------+
| ID | 名前 |
+------+--------------+
| 200 | テクノロジー |
| 201 | 人事 |
| 202 | 販売 |
| 203 | オペレーション |
+------+--------------+
セット内の4行（0.02秒）

mysql> 従業員から * を選択します。
+----+----------+--------+-------+--------+
| ID | 名前 | 性別 | 年齢 | dep_id |
+----+----------+--------+-------+--------+
| 1 | エゴン | 男性 | 18 | 200 |
| 2 | アレックス | 女性 | 48 | 201 |
| 3 | wupeiqi | 男性 | 38 | 201 |
| 4 | yuanhao | 女性 | 28 | 202 |
| 5 | nvshen | 男性 | 18 | 200 |
| 6 | xiaomage | 女性 | 18 | 204 |
+----+----------+--------+-------+--------+
セット内の 6 行 (0.00 秒)

ps: 2 つのテーブルを観察すると、部門テーブルの id=203 の部門には従業員内に対応する従業員がいないことがわかり、従業員内の id=6 の従業員には部門テーブル内に対応する関係がないことがわかります。

1対多のテーブルリンククエリ

SELECTフィールドリスト
テーブル1からINNER|LEFT|RIGHT JOIN テーブル2
ON テーブル1.フィールド = テーブル2.フィールド;

（１）まず、クロスコネクションの最初のケースを見てみましょう。マッチング条件は適用されません。デカルト積を生成します。 ---> 繰り返しの最大回数

mysql> 従業員、部門から * を選択します。
+----+----------+--------+-------+-------+------+--------------+
| id | 名前 | 性別 | 年齢 | dep_id | id | 名前 |
+----+----------+--------+-------+-------+------+--------------+
| 1 | エゴン | 男性 | 18 | 200 | 200 | テクノロジー |
| 1 | egon | 男性 | 18 | 200 | 201 | 人事 |
| 1 | エゴン | 男性 | 18 | 200 | 202 | 販売中 |
| 1 | エゴン | 男性 | 18 | 200 | 203 | 操作 |
| 2 | アレックス | 女性 | 48 | 201 | 200 | テクノロジー |
| 2 | alex | 女性 | 48 | 201 | 201 | 人事 |
| 2 | アレックス | 女性 | 48 | 201 | 202 | セールス |
| 2 | アレックス | 女性 | 48 | 201 | 203 | オペレーション |
| 3 | wupeiqi | 男性 | 38 | 201 | 200 | テクノロジー |
| 3 | wupeiqi | 男性 | 38 | 201 | 201 | 人事 |
| 3 | wupeiqi | 男性 | 38 | 201 | 202 | セールス |
| 3 | wupeiqi | 男性 | 38 | 201 | 203 | オペレーション |
| 4 | yuanhao | 女性 | 28 | 202 | 200 | テクノロジー |
| 4 | yuanhao | 女性 | 28 | 202 | 201 | 人事 |
| 4 | yuanhao | 女性 | 28 | 202 | 202 | セールス |
| 4 | yuanhao | 女性 | 28 | 202 | 203 | オペレーション |
| 5 | nvshen | 男性 | 18 | 200 | 200 | テクノロジー |
| 5 | nvshen | 男性 | 18 | 200 | 201 | 人事 |
| 5 | nvshen | 男性 | 18 | 200 | 202 | セールス |
| 5 | nvshen | 男性 | 18 | 200 | 203 | オペレーション |
| 6 | xiaomage | 女性 | 18 | 204 | 200 | テクノロジー |
| 6 | xiaomage | 女性 | 18 | 204 | 201 | 人事 |
| 6 | xiaomage | 女性 | 18 | 204 | 202 | 販売 |
| 6 | xiaomage | 女性 | 18 | 204 | 203 | オペレーション |

（２）内部結合：両側に基づいて一致する行のみを結合する

# 2 つのテーブルの共通部分を検索します。これは、条件を使用して、直交積の結果から一致する結果をフィルター処理することと同じです。# 部門には部門 204 がないため、従業員テーブルの従業員 204 に関する従業員情報は一致しません。mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+---------+------+--------+--------------+
| ID | 名前 | 年齢 | 性別 | 名前 |
+----+---------+------+--------+--------------+
| 1 | エゴン | 18 | 男性 | テクノロジー |
| 2 | アレックス | 48 | 女性 | 人事 |
| 3 | wupeiqi | 38 | 男性 | 人事 |
| 4 | yuanhao | 28 | 女性 | 営業 |
| 5 | nvshen | 18 | 男性 | テクノロジー |
+----+---------+------+--------+--------------+
セット内の行数は 5 です (0.00 秒)

# 上記の SQL は、mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id; と同等です。

（３）外部リンクの左結合：左のテーブルのすべてのレコードが最初に表示される

# 左のテーブルを標準として、部門のない従業員を含むすべての従業員情報を検索します。# 要点は、内部結合に基づいて、左側の結果を追加し、右側の結果は追加しないことです。mysql> select employee.id,employee.name,department.name as department_name from employee left join department on employee.dep_id=department.id;
+----+----------+--------------+
| ID | 名前 | 出発地名 |
+----+----------+--------------+
| 1 | エゴン | テクノロジー |
| 5 | nvshen | テクノロジー |
| 2 | alex | 人事 |
| 3 | wupeiqi | 人事 |
| 4 | yuanhao | 販売 |
| 6 | xiaomage | NULL |
+----+----------+--------------+
セット内の 6 行 (0.00 秒)

（4）外部リンクの右結合：右テーブルのすべてのレコードが最初に表示される

# 右側のテーブルに基づいて、従業員のいない部門も含め、すべての部門情報を検索します。# 要点: 内部結合に基づいて、右側の結果を追加し、左側の結果は追加しません。mysql> select employee.id,employee.name,department.name as department_name from employee right join department on employee.dep_id=department.id;
+------+---------+--------------+
| ID | 名前 | 出発地名 |
+------+---------+--------------+
| 1 | エゴン | テクノロジー |
| 2 | alex | 人事 |
| 3 | wupeiqi | 人事 |
| 4 | yuanhao | 販売 |
| 5 | nvshen | テクノロジー |
| NULL | NULL | 操作 |
+------+---------+--------------+
セット内の 6 行 (0.00 秒)

（５）完全外部結合：左テーブルと右テーブルの全レコードを表示する（理解）

#外部結合: 内部結合に基づいて、左側にあるが右側にはないものと、右側にあるが左側にはないものの結果を追加します。
#注意: MySQLは完全外部結合をサポートしていません
#強調: MySQLはこの方法を使用して間接的に完全外部結合を実装できます
構文: select * from employee left join department on employee.dep_id = department.id
すべて結合
従業員から*を選択し、employee.dep_id = department.idで部門を結合します。

 mysql> 従業員の左から*を選択し、従業員の部門を結合します。従業員のdep_id = department.id
     連合
    従業員から*を選択し、従業員の右から部門を結合します。employee.dep_id = department.id
      ;
+------+----------+--------+-------+-------+------+--------------+
| id | 名前 | 性別 | 年齢 | dep_id | id | 名前 |
+------+----------+--------+-------+-------+------+--------------+
| 1 | エゴン | 男性 | 18 | 200 | 200 | テクノロジー |
| 5 | nvshen | 男性 | 18 | 200 | 200 | テクノロジー |
| 2 | alex | 女性 | 48 | 201 | 201 | 人事 |
| 3 | wupeiqi | 男性 | 38 | 201 | 201 | 人事 |
| 4 | yuanhao | 女性 | 28 | 202 | 202 | セールス |
| 6 | xiaomage | 女性 | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 操作 |
+------+----------+--------+-------+-------+------+--------------+
セット内の行数は 7 です (0.01 秒)

#unionとunion allの違いに注意してください。unionは同一のレコードを削除します。

2.条件を満たす接続をクエリする

内部結合を使用して従業員テーブルと部門テーブルを照会します。従業員テーブルの年齢フィールドの値は 25 より大きい必要があります。つまり、25 歳以上の従業員と、その従業員が所属する部門を検索します。

従業員内部結合部門から従業員名、部門名を選択します
  employee.dep_id = department.id の場合
  年齢が25歳を超える場合

3. サブクエリ

#1: サブクエリは、別のクエリステートメント内にネストされたクエリステートメントです。
#2: 内部クエリステートメントのクエリ結果は、外部クエリステートメントのクエリ条件を提供できます。
#3: サブクエリには、IN、NOT IN、ANY、ALL、EXISTS、NOT EXISTS などのキーワードを含めることができます。
#4: 比較演算子 (=、!=、>、< など) も含めることができます。

（１）inキーワードを使ったサブクエリ

#平均年齢が25歳以上の部署名を照会する select id,name from department
  IDはどこに
    (平均年齢が25を超えるdep_idで従業員グループからdep_idを選択します);
# 技術部門の従業員の名前を表示する従業員から名前を選択
  dep_idが
    (name='Technology' の部門から ID を選択)
#1人未満の部署名を表示するには、部署から名前を選択してください
  どこにも存在しない
    (従業員グループから dep_id を dep_id で選択)

（２）比較演算子を使ったサブクエリ

#比較演算子: =、!=、>、>=、<、<=、<>
#全従業員の平均年齢よりも年上の従業員の名前と年齢を照会します。mysql> select name,age from employee where age > (select avg(age) from employee);
+---------+------+
| 名前 | 年齢 |
+---------+------+
| アレックス | 48 |
| ウペイキ | 38 |
+---------+------+
#部門内の平均年齢よりも年上の従業員の名前と年齢を照会する

アイデア:

（1）まず、従業員テーブル（employee）の従業員をグループ化し、dep_idと平均年齢を照会します。
（２）検索結果を一時テーブルとして利用し、一時テーブルのdep_idと従業員のdep_idをフィルター条件として、従業員テーブルと一時テーブルの間で内部結合を実行します。
（３）最後に、平均年齢より高い年齢の従業員の名前と年齢をフィルタリングします。

mysql> 従業員から t1.name,t1.age を t1 として選択します
       内部結合
      (従業員グループから dep_id、avg(age) を avg_age として dep_id で選択) を t2 として
      t1.dep_id = t2.dep_idの場合
      ここで、t1.age > t2.avg_age;
+------+------+
| 名前 | 年齢 |
+------+------+
| アレックス | 48 |

（３）EXISTSキーワードを使ったサブクエリ

#EXISTS キーワードは存在を示します。 EXISTS キーワードを使用すると、内部クエリ ステートメントはクエリされたレコードを返しません。代わりに、true または false の値を返します。真実か嘘か
#True が返された場合、外部クエリ ステートメントはクエリを実行します。False が返された場合、外部クエリ ステートメントはクエリを実行しません。#department テーブルには dept_id=203、True があります。
mysql> 存在する従業員から * を選択します (id=200 の部門から ID を選択します);
+----+----------+--------+-------+--------+
| ID | 名前 | 性別 | 年齢 | dep_id |
+----+----------+--------+-------+--------+
| 1 | エゴン | 男性 | 18 | 200 |
| 2 | アレックス | 女性 | 48 | 201 |
| 3 | wupeiqi | 男性 | 38 | 201 |
| 4 | yuanhao | 女性 | 28 | 202 |
| 5 | nvshen | 男性 | 18 | 200 |
| 6 | xiaomage | 女性 | 18 | 204 |
+----+----------+--------+-------+--------+
#department テーブルには dept_id=205 が含まれていますが、False です
mysql> 存在する従業員から * を選択します (id=204 の部門から ID を選択します);
空のセット (0.00 秒)

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリスキル」、「MySQL 共通関数の概要」、「MySQL ログ操作スキル」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアドプロシージャスキル」、および「MySQL データベースロック関連スキルの概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: