MySQL データベースの集計クエリと結合クエリ操作

1. クエリ結果を挿入する

文法：

挿入するテーブルに挿入します [(列 1、...、列 n)] クエリするテーブルから {* | (列 1、...、列 n)} を選択します

上記のステートメントは、クエリ対象のテーブルのいくつかの列を、新しいテーブルのいくつかの対応する列に挿入できます。

2. 集計クエリ

2.1 はじめに

集計クエリ:データ テーブル内のフィールドのデータに対して部分的または完全な統計クエリを実行する方法 (つまり、行ディメンションでマージするクエリ) を指します。たとえば、すべての書籍の平均価格や書籍の総数など、このような場合には集計クエリ方式が使用されます。

2.2 集計関数

集計クエリでは、SQL が提供する「ライブラリ関数」と同等の次の一般的な集計関数を使用できます。

補充:

• 特定の列の行数を照会する場合、行の値が null の場合、照会結果にはこの行はカウントされません。
• データを合計する場合、データ型は数値である必要があります。文字列と日付は合計できません。
• 構文エラーはないが、実行時エラーが発生した場合は、警告メッセージが表示されます。警告メッセージは、show warnings SQL ステートメントを使用して表示できます。

次に、次のデータを含むexam_resultというテーブルを例として使用します。

2.3 group by句

前の集計関数を使用すると、実際にはテーブル内のすべての行が結合されます。ただし、 group by使用してグループ集計を実行することもできます (group by の後に指定した列名を追加すると、その列に同じ値を持つ列がグループ化されます)

次に、empという名前のテーブルに次のデータが含まれている例を示します。

2.4 持つ

group by句を使用してグループ化した後、条件によってグループ化された結果をフィルタリングする必要がある場合、 where句は使用できません。代わりに、having 句を使用します。

知らせ：

• where文はグループ化の前にフィルタリングするために使用されます
• グループ化後にフィルタリングするにはhavingステートメントを使用します。
• where句とhaving句は同時に使用できます。

例 1: 給与が 10,000 を超える職種を検索する

3. 共同クエリ

3.1 はじめに

ユニオンクエリ: 複数の類似した選択クエリの結果セットを組み合わせることができます。 つまり、マルチテーブルクエリを実行するには、デカルト積を使用するのが基本となる。

デカルト積の考え方:

デカルト積を使用するアイデアは、実際には 2 つのテーブルの結果を並べ替えて結合することです。次に、デカルト積の考え方を使用して、2 つのテーブル A と B から新しいテーブル C を取得します。

学生テーブルA:

クラステーブルB:

新しいテーブル C:

補充:

• デカルト積の結果は依然として表である
• この表の列数は、2 つの表の列数の合計です。
• この表の行数は、2 つの表の行数の積です。

新しく取得した C テーブルを通じて、2 つのテーブル A と B をリンクできます。上記の例では、リンクはクラス ID です。この時点では、2 つのテーブルはリンクされていますが、新しいテーブルのすべてのデータが適切であるわけではありません。たとえば、行 2 の情報は実際には正しくありません。そのため、2 つのテーブルをリンクした後、テーブル A とテーブル B のクラス ID が同じであるなど、いくつかの制限を追加する必要があります。この時点で、より合理的なデータを持つテーブル D を取得できます。

新しいテーブルD:

この時点で、複数テーブルクエリを実行できます。

知らせ：

結合クエリではデカルト積が使用されるため、新しいテーブル内の行数はすべてのテーブルの結合の積になります。そのため、結合クエリ結果のデータは非常に大きくなる可能性があるため、注意して使用してください。

以下の例はすべて、以下のSQL文で作成されたテーブルを介して操作および学習されます。以下の内容で操作したい場合は、直接コピーして使用することができます。

クラスが存在する場合はテーブルを削除します。
学生が存在する場合はテーブルを削除します。
もちろん、存在する場合はテーブルを削除します。
スコアが存在する場合はテーブルを削除します。

テーブルクラスを作成します (id int primary key auto_increment、name varchar(20)、`desc` varchar(100));

テーブルstudentを作成します（id int primary key auto_increment、sn varchar(20)、name varchar(20)、qq_mail varchar(20)、
        クラスID int);

テーブルcourseを作成します(id int primary key auto_increment, name varchar(20));

テーブル score(scoredecimal(3, 1), student_id int, course_id int) を作成します。

クラス(名前、`desc`)の値に挿入する 
（「コンピュータサイエンス学科 2019 クラス 1」、「コンピュータの原理、C 言語と Java 言語、データ構造とアルゴリズムを学習」）、
（「中国語学科 2019年3年生」、「伝統的な中国文学を学んだ」）、
（「オートメーション 2019 クラス 5」、「機械オートメーションを学習」）

学生(sn、名前、qq_mail、classes_id)の値に挿入します
('09982','黒旋風 李逵','[email protected]',1),
('00835','菩提祖師',null,1),
('00391','ホワイト 素',null,1),
('00031','徐仙','[email protected]',1),
('00054','卒業したくない',null,1),
('51234','よく話してください','[email protected]',2),
('83223','教えて',null,2),
('09527','外国人が中国語を学ぶ','[email protected]',2);

コース(名前)の値に挿入
('Java'),('伝統的な中国文化'),('コンピュータ原理'),('中国語'),('高度な数学'),('英語');

スコア(スコア、学生ID、コースID)の値を挿入する
-- 黒旋風李逵 (70.5, 1, 1), (98.5, 1, 3), (33, 1, 5), (98, 1, 6),
-- 菩提祖師 (60, 2, 1)、(59.5, 2, 5)、
-- 白素珍 (33, 3, 1), (68, 3, 3), (99, 3, 5),
-- 徐仙 (67, 4, 1), (23, 4, 3), (56, 4, 5), (72, 4, 6),
-- 卒業したくない（81、5、1）、（37、5、5）、
-- 丁寧に話す (56, 6, 2), (43, 6, 4), (79, 6, 6),
 -  教えて
(80, 7, 2),(92, 7, 6);

3.2 内部結合

文法：

-- 方法 1:
結合条件となる表 1 [表 1 の別名]、表 2 [表 2 の別名] から表示される列名を選択します。

-- 方法2: [内部]結合を使用する
テーブル 1 [テーブル 1 の別名] [内部] から表示された列名を選択し、結合条件でテーブル 2 [テーブル 2 の別名] を結合します。

補充:

• マルチテーブルクエリを使用する場合、複数のテーブルがあるため、テーブル内の列は次のように使用されます: テーブル名.列名
• テーブル名エイリアスメソッドを使用してテーブルにエイリアスを付与することができます。
• [inner] join on メソッドを使用します。inner を省略すると、デフォルトで内部結合が実行されます。

例1: 各コースにおけるXu Xianの成績を照会する

3.3 外部結合

外部結合:左外部結合と右外部結合に分かれます。ユニオン クエリを使用する場合、左外部結合を使用すると左側のテーブルが完全に表示され、右外部結合を使用すると右側のテーブルが完全に表示されます。

外部結合は、どちらもデカルト積を使用するという点で内部結合に似ています。内部結合は、2 つのテーブル内の各データを 1 対 1 で対応させるものですが、なぜ 1 対 1 の対応にならないのでしょうか。例えば、次の2つの表AとB

テーブル:

表B:

デカルト積の後に新しいテーブルを作成すると、テーブル A の ID 3 のレコードにはテーブル B に対応するデータがなく、テーブル B の student_id 4 のレコードにはテーブル A に対応するデータがないことがわかりました。したがって、これらの 2 つのテーブルは内部結合方式を使用してクエリすることはできず、外部結合を使用する必要があります。

左結合方式を使用する場合、新しいテーブル C は次のようになります。

適切な結合方法を使用すると、新しいテーブル D は次のようになります。

補充:

• 2 つのテーブルのデータが 1 対 1 で対応できる場合、外部結合と内部結合の使用は同等です。
• 内部結合、左外部結合、右外部結合に加えて、完全外部結合もありますが、MySQL は完全外部結合操作をサポートしていません。

文法：

-- 左結合、テーブル 1 が完全に表示されます。テーブル 1 [テーブル 1 の別名] [左] から表示される列名を選択し、結合条件でテーブル 2 [テーブル 2 の別名] を結合します。

-- 右結合、テーブル 2 が完全に表示されます テーブル 1 [テーブル 1 の別名] [右] から表示される列名を選択し、結合条件でテーブル 2 [テーブル 2 の別名] を結合します。

3.4 自己結合

自己結合:クエリのために同じテーブルをそれ自体に結合することを指します。自己結合を使用すると、操作のために実際に「行を列に変換」することができます。

自己結合によって行を列に変換して操作できるのはなぜですか?テーブルAがあると仮定する

元のテーブルで、 student_idが 1 で、コース 2 のスコアがコース 3 よりも高い学生の情報を検索する場合、行を比較する必要がありますが、この操作は単一のテーブルでは実行できません。

直積を自身に対して実行すると、新しいテーブルBが得られる。

この時点で、元のテーブルに対して直積を実行すると、2 つの同一のテーブルが作成され、行間で操作を実行できることがわかります。

例: Javaのスコアがコンピュータ原理のスコアより高い学生を照会する

3.5 サブクエリ

サブクエリ:他のSQL文に埋め込まれたselect文を指します。ネストされたクエリとも呼ばれます。

分類：

• 単一行サブクエリ:単一行のレコードを返すサブクエリ
• 複数行サブクエリ:複数の行を返すサブクエリ (in または exists を使用)

補充:

• 複数行のクエリ処理を実行するにはin使用します。サブクエリを使用する場合は、最初にサブクエリを実行し、クエリ結果をメモリに保存してから、外部クエリを実行してメモリ内の結果に従ってフィルタリングします。
• 複数行のクエリ処理を実行するには、 existsを使用します。まず外側のループを実行して多くのレコードを取得し、次に各レコード行をサブクエリに持ち込み、条件を満たすレコードを保持します (exists はサブクエリの結果が空のセットであるかどうかを検出します)。

要約すれば：

inライティング方式に基づいており、サブクエリの結果セットが比較的小さい（大きなメモリでは保持できない）状況に適しています。
exists書き込み方式に基づいているため、速度は遅く、サブクエリの結果セットが比較的大きく、外部クエリの結果の数が比較的少ない状況に適しています。

例 1: 卒業したくないクラスメートのクラスメートを照会する(まず卒業したくないクラスメートのクラスを知っておく必要があり、次にクラス別に生徒をフィルタリングします)

3.6 マージクエリ

クエリのマージ: セット演算子unionまたはunion allを使用して、複数の選択の実行結果をマージします。マージクエリを使用する場合、前のクエリと次のクエリの結果セットのフィールドは一致している必要があります。

補充:

• union演算子は結果セット内のデータの重複を排除しませんが、 union all重複を排除します。
• set 演算子の機能は、実際には or 演算子の機能と似ていますが、異なるテーブルをクエリする場合は or は使用できません。

例: IDが3未満またはJavaのコースの情報を表示する