MySQL接続クエリの原理と応用

MySQL接続クエリの原理と応用

概要

MySQL の最も強力な機能の 1 つは、データ取得を実行しながらテーブルを結合できることです。ほとんどの単一テーブル データ クエリでは、ニーズを満たすことができません。現時点では、1 つ以上のテーブルを接続し、いくつかの条件を使用して必要なデータをフィルター処理する必要があります。

MySQL の結合クエリを理解する前に、まずデカルト積の原理を理解しましょう。

データ準備

前のセクションのテーブル データ (classes テーブルと students テーブルを含む) を引き続き使用します。

mysql> クラスから * を選択します。
+---------+------------+
| クラスID | クラス名 |
+---------+------------+
| 1 | 1年生、9年生|
| 2 | 9 年生、2 クラス |
| 3 | 3年生、3クラス |
| 4 | 3年生~4年生 |
+---------+------------+
4行セット

mysql> 学生から * を選択します。
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 1 | ブランド | 97.5 | 1 |
| 2 | ヘレン | 96.5 | 1 |
| 3 | リン | 96 | 1 |
| 4 | ソル | 97 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72.5 | 3 |
| 19 | ララ | 51 | 0 |
+-----------+-------------+--------+---------+
9行セット

デカルト積

デカルト積: デカルト積とも呼ばれ、2 つのセット A と B を想定し、デカルト積はセット A の要素とセット B の要素間のあらゆる関連付けによって生成されるすべての可能な結果を​​表します。

たとえば、A に m 個の要素があり、B に n 個の要素がある場合、A と B の直積は m*n の結果を生成します。これは、2 つのセットの要素を任意の組み合わせでループすることと同じです。

SQL におけるカルテシアン積の実装はクロス結合です。すべての結合方法では、まず一時的な直積テーブルが生成されます。直積はリレーショナル代数の概念であり、2 つのテーブル内の各データ行の任意の組み合わせを表します。

したがって、上記の表は 4 (クラス表) * 9 (生徒表) = 36 のデータになります。

デカルト積構文形式:

 tname1、tname2、... から cname1、cname2、... を選択します。
 または
 tname1 から cname を選択し、tname2 を結合します [tname を結合...];

凡例には次のことが示されています:

上記 2 つのテーブルの実際の実行結果は次のとおりです。

mysql> クラス a、学生 b から * を選択し、 a.classid、b.studentid で順序付けします。
+---------+------------+------------+-----------+---------+---------+
| クラス ID | クラス名 | 学生 ID | 学生名 | スコア | クラス ID |
+---------+------------+------------+-----------+---------+---------+
| 1 | クラス 1、グレード 9 | 1 | ブランド | 97.5 | 1 |
| 1 | クラス 1、グレード 9 | 2 | ヘレン | 96.5 | 1 |
| 1 | クラス 1、グレード 9 | 3 | lyn | 96 | 1 |
| 1 | クラス 1、グレード 9 | 4 | sol | 97 | 1 |
| 1 | 1年生、9年生 | 7 | b1 | 81 | 2 |
| 1 | 1年生、9年生 | 8 | b2 | 82 | 2 |
| 1 | クラス 1、グレード 9 | 13 | C1 | 71 | 3 |
| 1 | クラス 1、グレード 9 | 14 | C2 | 72.5 | 3 |
| 1 | 1年生、9年生 | 19 | lala | 51 | 0 |
| 2 | クラス 2、グレード 9 | 1 | ブランド | 97.5 | 1 |
| 2 | クラス 2、グレード 9 | 2 | ヘレン | 96.5 | 1 |
| 2 | クラス 2、グレード 9 | 3 | lyn | 96 | 1 |
| 2 | クラス 2、グレード 9 | 4 | sol | 97 | 1 |
| 2 | 2年生、9年生 | 7 | b1 | 81 | 2 |
| 2 | クラス 2、グレード 9 | 8 | b2 | 82 | 2 |
| 2 | クラス 2、グレード 9 | 13 | C1 | 71 | 3 |
| 2 | クラス 2、グレード 9 | 14 | C2 | 72.5 | 3 |
| 2 | 2年生、9年生 | 19 | lala | 51 | 0 |
| 3 | 3年生、9年生 | 1 | ブランド | 97.5 | 1 |
| 3 | クラス 3、グレード 9 | 2 | ヘレン | 96.5 | 1 |
| 3 | 3年生、9年生 | 3 | lyn | 96 | 1 |
| 3 | 3年生、9年生 | 4 | sol | 97 | 1 |
| 3 | 3年生、9年生 | 7 | b1 | 81 | 2 |
| 3 | 3年生、9年生 | 8 | b2 | 82 | 2 |
| 3 | 3年生、9年生 | 13 | C1 | 71 | 3 |
| 3 | クラス 3、グレード 9 | 14 | C2 | 72.5 | 3 |
| 3 | 3年生、9年生 | 19 | lala | 51 | 0 |
| 4 | クラス 3-4 | 1 | ブランド | 97.5 | 1 |
| 4 | クラス 3-4 | 2 | ヘレン | 96.5 | 1 |
| 4 | クラス 3-4 | 3 | lyn | 96 | 1 |
| 4 | 3-4年生 | 4 | sol | 97 | 1 |
| 4 | 3-4年生 | 7 | b1 | 81 | 2 |
| 4 | 3-4年生 | 8 | b2 | 82 | 2 |
| 4 | クラス 3-4 | 13 | C1 | 71 | 3 |
| 4 | クラス 3-4 | 14 | C2 | 72.5 | 3 |
| 4 | 3-4年生 | 19 | ララ | 51 | 0 |
+---------+------------+------------+-----------+---------+---------+
36行セット

この種のデータは、私たちが望むものではありません。実際のアプリケーションでは、テーブルを結合するときに制限を追加して、本当に必要なデータをフィルタリングする必要があります。

主な接続クエリは、内部接続、左 (外部) 接続、右 (外部) 接続です。 1 つずつ見ていきましょう。

内部結合クエリ内部結合

構文形式:

 結合条件で tname1 から cname を選択し、 tname2 を内部結合します。
 または、結合条件で tname1 から cname を選択し、tname2 を結合します。
 または、tname1、tname2からcnameを選択します[結合条件];

注: デカルト積に基づいて結合条件が追加され、2 つのテーブルが結合され、結合条件を満たすレコードが返されます。つまり、2 つのテーブルの交差部分 (網掛け部分) が返されます。この結合条件が追加されない場合、結果は上記の直積になります。

mysql> クラス a から a.classname、b.studentname、b.score を選択し、 students b を a.classid = b.classid で内部結合します。
+-----------+-------------+--------+
| クラス名 | 生徒名 | スコア |
+-----------+-------------+--------+
| クラス 1、グレード 9 | ブランド | 97.5 |
| クラス 1、グレード 9 | ヘレン | 96.5 |
| クラス 1、グレード 9 | lyn | 96 |
| クラス 1、グレード 9 | sol | 97 |
| クラス 2、グレード 9 | b1 | 81 |
| クラス 2、グレード 9 | b2 | 82 |
| 3年生、9年生 | C1 | 71 |
| クラス 3、グレード 9 | C2 | 72.5 |
+-----------+-------------+--------+
8行セット

上記のデータから、3年生と4年生のクラスのクラスIDは4であり、関連付けられた生徒がいないため除外されていることがわかります。また、lalaのクラスIDは0であり、特定のクラスに関連付けることができないため、これも除外されています。両方のテーブルのデータ交差のみが取得されます。

mysql> クラス a、学生 b から a.classname、b.studentname、b.score を選択します。ここで、a.classid = b.classid かつ a.classid=1 です。
+-----------+-------------+--------+
| クラス名 | 生徒名 | スコア |
+-----------+-------------+--------+
| クラス 1、グレード 9 | ブランド | 97.5 |
| クラス 1、グレード 9 | ヘレン | 96.5 |
| クラス 1、グレード 9 | lyn | 96 |
| クラス 1、グレード 9 | sol | 97 |
+-----------+-------------+--------+
4行セット

クラス 1 の生徒の成績情報を検索するには、上記の 3 番目の構文形式を使用します。この方法は簡潔かつ効率的であり、Where 条件は接続クエリの結果の後にすぐにフィルター処理されます。

左結合クエリ 左結合

左結合オン / 左外部結合オン、構文形式:

結合条件で tname1 から cname を選択し、tname2 を左結合します。

注: left join は left outer join の略語で、正式名称は left outer join であり、外部結合の一種です。 左 (外部) 結合の場合、左側のテーブル (クラス) のすべてのレコードが表示され、右側のテーブル (生徒) には検索条件を満たすレコードのみが表示されます。右テーブルに関連付けることができないコンテンツはすべて null です。

mysql> クラス a から a.classname、b.studentname、b.score を選択し、左側の students b を a.classid = b.classid で結合します。
+-----------+-------------+--------+
| クラス名 | 生徒名 | スコア |
+-----------+-------------+--------+
| クラス 1、グレード 9 | ブランド | 97.5 |
| クラス 1、グレード 9 | ヘレン | 96.5 |
| クラス 1、グレード 9 | lyn | 96 |
| クラス 1、グレード 9 | sol | 97 |
| クラス 2、グレード 9 | b1 | 81 |
| クラス 2、グレード 9 | b2 | 82 |
| 3年生、9年生 | C1 | 71 |
| クラス 3、グレード 9 | C2 | 72.5 |
| クラス 3-4 | NULL | NULL |
+-----------+-------------+--------+
9行セット

上記の結果から、3 年生と 4 年生には対応する生徒が見つからないため、最後の 2 つのフィールドは null でマークされていることがわかります。

右結合クエリ

右結合オン / 右外部結合オン、構文形式:

結合条件で tname1 から cname を選択し、tname2 を右結合します。

注: 右結合は右外部結合の略語であり、正式名称は右外部結合で、外部結合の一種です。左 (外部) 結合とは対照的に、右 (外部) 結合では、検索条件を満たす左のテーブル (クラス) のレコードのみが表示され、右のテーブル (生徒) のすべてのレコードが表示されます。左の表のレコードが不足している箇所はすべて NULL です。

mysql> クラス a から a.classname、b.studentname、b.score を選択し、右側の students b を a.classid = b.classid で結合します。
+-----------+-------------+--------+
| クラス名 | 生徒名 | スコア |
+-----------+-------------+--------+
| クラス 1、グレード 9 | ブランド | 97.5 |
| クラス 1、グレード 9 | ヘレン | 96.5 |
| クラス 1、グレード 9 | lyn | 96 |
| クラス 1、グレード 9 | sol | 97 |
| クラス 2、グレード 9 | b1 | 81 |
| クラス 2、グレード 9 | b2 | 82 |
| 3年生、9年生 | C1 | 71 |
| クラス 3、グレード 9 | C2 | 72.5 |
| NULL | ララ | 51 |
+-----------+-------------+--------+
9行セット

上記の結果から、Lala はクラスを見つけることができないため、クラス名フィールドは null であることがわかります。

結合クエリ + 集計関数

結合クエリを使用する場合、データを要約するために集計関数がよく使用されます。たとえば、上記のデータに基づいて、各クラスの生徒数、平均スコア、クラスの合計スコアを照会できます。

mysql> a.classname を 'クラス名'、count(b.studentid) を '生徒総数'、sum(b.score) を '合計スコア'、avg(b.score) を '平均スコア' として選択します。
クラス A から学生 B を a.classid = b.classid で内部結合します。
a.classid、a.classname でグループ化します。
+----------+--------+---------+-----------+
| クラス名| 生徒総数| 合計点数| 平均点|
+----------+--------+---------+-----------+
| クラス 1、グレード 9 | 4 | 387.00 | 96.750000 |
| 2年生、9年生 | 2 | 163.00 | 81.500000 |
| 3年生、9年生 | 2 | 143.50 | 71.750000 |
+----------+--------+---------+-----------+
3行セット

ここでテーブルをクエリすると、クラス (classid、classname) がグループ化され、各クラスの生徒数、平均スコア、合計スコアが出力されます。

接続クエリの追加フィルター条件

接続クエリを使用した後、データはフィルタリングされる可能性が高いため、接続クエリの後に where 条件を追加できます。たとえば、上記の結果に基づいて、1 つのクラスの学生情報のみを取得できます。

mysql> クラス a から a.classname、b.studentname、b.score を選択し、 students b を a.classid = b.classid で内部結合し、 a.classid=1 にします。
+-----------+-------------+--------+
| クラス名 | 生徒名 | スコア |
+-----------+-------------+--------+
| クラス 1、グレード 9 | ブランド | 97.5 |
| クラス 1、グレード 9 | ヘレン | 96.5 |
| クラス 1、グレード 9 | lyn | 96 |
| クラス 1、グレード 9 | sol | 97 |
+-----------+-------------+--------+
4行セット

上記のように、1 つのクラスの生徒のみが出力されます。同様に、制限制約、並べ替えによる順序付け、その他の操作を追加できます。

要約する

1. 結合クエリには結合条件が含まれている必要があります。含まれていない場合は、直交積データになります。結合条件が間違っていると、間違ったデータが返されます。

2. SQL 仕様では、INNER JOIN 構文を第一の選択肢として使用することが推奨されています。ただし、複数の接続方法自体には明らかなパフォーマンスの違いはありません。パフォーマンスの違いは、主にデータ構造、接続条件、インデックスの使用状況など、複数の条件の組み合わせによって決まります。

上記のデータ シナリオなど、実際のビジネス シナリオに基づいて決定する必要があります。クラスと学生を返す必要がある場合は、内部結合を使用します。すべてのクラスを出力する必要がある場合は、左結合を使用します。すべての学生を出力する必要がある場合は、右結合を使用します。

3. パフォーマンスの考慮事項: MySQL は実行時に関連条件に従って接続されたテーブルを処理します。この処理はリソースを大量に消費する可能性があります。接続されるテーブルが多いほど、パフォーマンスの低下は深刻になります。そのため、不要な接続や表示する必要がないフィールドを分析して削除する必要があります。

以前、私のプロジェクト チームが古いビジネス コードを最適化していたとき、ビジネスが変化するにつれて、一部のデータは表示する必要がなくなり、対応する接続​​も不要になったことがわかりました。それを削除した後、パフォーマンスが大幅に向上しました。

上記は、MySQL 接続クエリの原理と応用の詳細な内容です。MySQL 接続クエリの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQL の結合クエリとサブクエリの問題
  • MySQL マルチテーブル結合クエリの詳細な説明
  • どのような種類の MYSQL 接続クエリを知っていますか?
  • MySQL 結合クエリ構文と例
  • MySQLの結合クエリ、ユニオンクエリ、サブクエリの原理と使用例の詳細な説明
  • Mysql 自己結合クエリ例の詳細な説明
  • MySQL マルチテーブル結合クエリ例の説明
  • MySQL接続クエリの詳細な説明

<<:  カルーセル例の JavaScript 実装

>>:  3分でUbuntu 16.04を初期化し、Java、Maven、Docker環境をデプロイする

推薦する

JavaScript を使用してページ要素のオフセットを取得/計算する方法

質問コントロールをクリックすると、コントロールの下にフローティング レイヤーが表示されます。通常の方...

MySQL IDは1から増加し始め、不連続IDの問題を素早く解決します

mysql idは1から始まり、不連続なidの問題を解決するために自動的に増加します。強迫性障害の私...

Java は Excel から MySQL にデータをインポートします

実際の業務では、Excel からデータベースにデータをインポートする必要がある場合があります。データ...

CSS3 で実装された価格表

結果: 実装コードhtml <div id="価格表" class=&qu...

MySQL 5.7.17 でパスワードを忘れた場合の対処方法

1. my.iniファイルにskip-grant-tablesを追加し、MySQLサーバーを再起動し...

HTML ウェブページ作成のための 8 つの強力なテクニック

<br />作業を簡単に完了できる Web ページ作成ツールは数多くありますが、HTML...

MySQL の日付フォーマットと複雑な日付範囲クエリ

目次序文クエリの使用シナリオ例時間間隔クエリクエリ日付と今日の時間の比較データ一般的なサイクルタイム...

MySQLデータベースとOracleデータベース間のバックアップをインポートする

OracleデータベースからエクスポートされたデータをMySqlデータベースにインポートします。 1...

Linuxカーネルで中国語の文字を出力する方法

次のように、Windows/MacOS からログインした Linux の SSH ターミナルで簡単に...

Node.js http モジュールの使用

目次序文ウェブHTTP サーバーファイルサーバー練習する序文Node.js 開発の目的は、JavaS...

LinuxでRPMを使用してmysql5.7.17をインストールする

LinuxでのMySQL5.7 rpmのインストール方法を参考までに記録します。具体的な内容は以下の...

Mac ノードの削除と再インストールのケーススタディ

Macノードの削除と再インストール消去 ノード -v sudo npm アンインストール npm -...

Vue3 親子コンポーネントパラメータ転送における sync 修飾子の使用法の詳細な説明

目次一方向データフローの説明Vue2.x の使用法親コンポーネントに変更を通知するイベントのフォーム...

HTML テーブル マークアップ チュートリアル (42): テーブル ヘッダーの水平方向の配置属性 ALIGN

水平方向では、テーブル ヘッダーの配置を左、中央、右に設定できます。基本的な構文<TH ALI...

ウェブサイト構築経験概要

<br />注目を集めるウェブサイトを作成するには、どのような原則に従うべきでしょうか?...