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

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

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

文法:

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

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

2. 集計クエリ

2.1 はじめに

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

2.2 集計関数

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

補充:

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

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

id名前中国語数学英語
1唐三蔵67.0 98.0 56.0
2孫悟空87.5 78.0 77.0
3豚武能88.0 98.5 90.0
4曹孟徳82.0 84.0 67.0
5劉玄徳55.5 85.0 45.0
6孫権70.0 73.0 78.5
7宋公明ヌルヌルヌル

2.3 group by句

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

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

id名前役割給料
1張三発達10000
2李思発達11000
3王武テスト9000
4趙 劉テスト12000
5天斉販売7000
6魔王ボス50000

2.4 持つ

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

知らせ:

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

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

3. 共同クエリ

3.1 はじめに

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

デカルト積の考え方:

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

学生テーブルA:

学生証名前クラスID
1張三2001
2李思2001
3王武2002

クラステーブルB:

クラスIDクラス名
2001シニア2(1)
2002シニア2(2)

新しいテーブル C:

学生証名前クラスIDクラスIDクラス名
1張三2001 2001シニア2(1)
1張三2001 2002シニア2(2)
2李思2001 2001シニア2(1)
2李思2001 2002シニア2(2)
3王武2002 2001シニア2(1)
3王武2002 2002シニア2(2)

補充:

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

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

新しいテーブルD:

学生証名前クラスIDクラスIDクラス名
1張三2001 2001シニア2(1)
2李思2001 2001シニア2(1)
3王武2002 2001シニア2(2)

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

知らせ:

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

以下の例はすべて、以下の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

テーブル:

id名前
1張三
2李思
3王武

表B:

学生IDスコア
1 90
2 80
4 70

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

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

id名前学生IDスコア
1張三1 90
2李思2 80
3王武ヌルヌル

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

id名前学生IDスコア
1張三1 90
2李思2 80
ヌルヌル4 70

補充:

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

文法:

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

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

3.4 自己結合

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

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

学生IDコースIDスコア
1 1 70
1 2 90
1 3 80

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

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

学生IDコースIDスコア学生IDコースIDスコア
1 1 70 1 1 70
1 2 90 1 2 90
1 3 80 1 3 80

この時点で、元のテーブルに対して直積を実行すると、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のコースの情報を表示する

以下もご興味があるかもしれません:
  • MySQL データベース ターミナル - 一般的な操作コマンド コード
  • Python MySQL データベースの基本操作とプロジェクト例
  • MySQLデータベースのデータテーブルに関する詳細な基本操作
  • MySQL データベースの操作とデータ型
  • MySQL学習データベース操作DML初心者向け詳細解説
  • 初心者向けのMySQLデータベースとテーブルDDLの作成と操作の学習
  • MySQL データベース データ テーブル操作

<<:  React Hooks に基づく小さな状態管理の詳細な説明

>>:  Nginx と Lua を使用した JWT 検証の概要

推薦する

単一のMySQLテーブルを復元する手順

休憩中に、眠気を完全に吹き飛ばす電話がかかってきました。「開発者が更新 SQL を書くときに whe...

CSS でハニカム/六角形アトラスを実装するためのサンプルコード

理由は分かりませんが、UIではハニカム効果(手を広げたような効果)のデザインが好まれます。 1. 六...

Vue+Vantはトップ検索バーを実装します

この記事では、参考までに、Vue+Vant のトップ検索バーを実装するための具体的なコードを紹介しま...

Vue ルーターにパラメータを渡すときにページを更新するとパラメータが失われる問題に対処する方法

目次概要方法1: params経由でパラメータを渡す方法2: クエリを通じてパラメータを渡す方法3:...

Docker Alpine イメージのタイムゾーン問題に対する完璧な解決策

最近、Docker を使用して Java アプリケーションをデプロイしていたときに、タイムゾーンが間...

Vue/React シングルページ アプリケーションをリフレッシュなしで復元するソリューション

目次導入なぜわざわざ?落とし穴のあるコミュニティソリューション(Vue を例に挙げる)現時点では良い...

jQueryのチェーンプログラミングスタイルの詳細な例

チェーンプログラミングの実装原理jQuery を使用すると、開発者は常にドット構文を使用して独自のメ...

SSL を実装するために nginx を設定する方法の例

環境説明サーバーシステム: Ubuntu 18.04 64ビットnginx: 1.14この記事では主...

CUDA10.0 のインストールと Ubuntu での問題

Tensorflow バージョンと Cuda および CUDNN の対応: https://tens...

HTML webpackプラグインの使用に関する簡単な分析

html-webpack-pluginプラグインを使用してページを開始すると、htmlページをメモリ...

HTML テーブルタグチュートリアル (11): 水平方向の配置属性 ALIGN

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

Linux での stat 関数と stat コマンドの使用法の詳細な説明

stat 関数と stat コマンドLinux ファイル内の [inode = インデックス ノード...

Rx レスポンシブプログラミングについての簡単な説明

目次1. 観察可能2. 高階関数3. エクスプレスボックスモデル3.1. エクスプレスボックスモデル...

vscodeを使用してReact Native開発環境を構築する方法を教えます

質問コードにはプロンプトがありません: RN 開発に不慣れな、フロントエンド以外の学生の多くは、「ど...

after疑似要素を使用して中空の三角矢印とXアイコンを実装する例

フロントエンドのデザイン案では、「X」や「>」の形をした閉じるボタンや、他の 3 方向の白抜き...