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

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.フィールド;

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

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)内部結合:両側に基づいて一致する行のみを結合する

# 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; と同等です。

(3)外部リンクの左結合:左のテーブルのすべてのレコードが最初に表示される

# 左のテーブルを標準として、部門のない従業員を含むすべての従業員情報を検索します。# 要点は、内部結合に基づいて、左側の結果を追加し、右側の結果は追加しないことです。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 秒)

(5)完全外部結合:左テーブルと右テーブルの全レコードを表示する(理解)

#外部結合: 内部結合に基づいて、左側にあるが右側にはないものと、右側にあるが左側にはないものの結果を追加します。
#注意: 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: 比較演算子 (=、!=、>、< など) も含めることができます。

(1)inキーワードを使ったサブクエリ

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

(2)比較演算子を使ったサブクエリ

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

アイデア:

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

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 |

(3)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データベース設計に役立つことを願っています。

以下もご興味があるかもしれません:
  • IDEA で MySQL にリンクするとエラー 08001 が発生し、接続に成功した後にテーブルが表示されない問題と解決策
  • MySQLにNavicatをインストールした後、2059が表示され、認証プラグインとローカルリンク仮想マシンdocker、リモートリンクサーバー
  • Python pymysql リンク データベース クエリ結果を Dataframe インスタンスに変換
  • 操作例 MySQL ショートリンク
  • MySql 8.0.11 のインストール プロセスと Navicat とのリンク時に発生する問題の概要
  • MySQLグループリンクの使用に関する詳細な説明
  • MySqlは、外部ネットワーク接続クライアントの低速問題を解決するためにskip-name-resolveを使用します。
  • SSHトンネルを使用してMySQLサーバーに接続する方法
  • MySQL リンクを表示し、異常なリンクを削除する方法

<<:  CentOS8 デプロイメント LNMP 環境で mysql8.0.29 をコンパイルしてインストールする方法の詳細なチュートリアル

>>:  Vue画像拡大鏡コンポーネントのカプセル化と使用方法の詳細な説明

推薦する

Brotli圧縮アルゴリズムを有効にするNginxの実装プロセスの詳細な説明

序文Web アプリケーションでは、トラフィックを節約し、転送データのサイズを縮小し、転送効率を向上さ...

Vue+js はビデオのフェードインとフェードアウト効果を実現します

Vue+jsはビデオのフェードインとフェードアウトを実現します。参考までに、具体的な内容は次のとおり...

CSS3 で虫眼鏡効果を模倣するいくつかの方法の原理の分析

記事のタイトルが「模造虫眼鏡」なのはなぜですか?今日お話ししたいのは、一般的に言われているような、マ...

Linuxのpasswdコマンドの使用

1. コマンドの紹介passwd コマンドは、ユーザー パスワード、アカウント ロック、パスワードの...

実践的な経験を共有するためのコードチェックツールstylelintの紹介

目次序文文章1. stylelintをインストールする2. 設定ファイル3. stylelintを使...

SQL実行ステップの詳細な分析

SQL実行ステップの詳細な分析まず、ステートメントが実行される順序を見てみましょう。 (8)選択する...

xtrabackup による MySQL データベースのバックアップと復元

mysqldump バックアップは、その独自の特性 (テーブルのロック、基本的に挿入スクリプトまたは...

バックエンドの権限に基づいてナビゲーション メニューを動的に生成する Vue-router のサンプル コード

目次js の1. グローバルガードを登録する2. Vuex 状態管理グローバルキャッシュルート3. ...

JSはjQueryのappend関数を実装します

目次コードを見せてください効果をテストする効果追伸別のアプローチコードを見せてください HTMLEl...

Dockerはプロセス操作を管理するためにSupervisorを使用する

Docker コンテナは、起動時に、たとえば ssh または apache デーモン サービスなどの...

CSS3を使用して背景画像の色を変更するさまざまな方法

CSS3 では画像の色を変更できます。これからは複数の絵をデザインする必要がなくなり、いつでも修正で...

XHTML 特殊文字コレクション

注意&#160;ノーブレークスペース = ノーブレークスペース、 iexcl ¡ &...

スネークゲームを作るための Pygame コード

目次使用されるPygame関数スクリーンの作成ヘビの作成ヘビを動かすゲームオーバーの処理食事を増やす...

Linux 型バージョン メモリ ディスク クエリ コマンド紹介

1. まず、Linux システムのバージョン内容について概要を説明します。 1. カーネルバージョン...

JS 配列の重複を排除する 9 つの高度な方法 (実証済みで効果的)

序文一般的な方法はここには記載されていませんが、等しいかどうかを判断するための二重ループや、比較のた...