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 検証の概要

推薦する

Vue でコミュニケーションを実装する 8 つの方法

目次1. コンポーネント通信1. Props 親コンポーネント ---> 子コンポーネント通信...

ElementUI のネストされたテーブルに基づいて複数選択を実装するためのサンプル コード

序文:私は友人のプロジェクトのバグを修正するのを手伝ったのでこれを書きました。この関数を書くのは初め...

MySQLテーブルパーティショニングプログラムを変更する方法

MySQLテーブルパーティショニングプログラムを変更する方法1. サブテーブルの実装の原則は次のとお...

ES6のシンボルデータ型について詳しく説明します

目次シンボルデータタイプシンボルが表示される理由シンボルの特徴シンボルの応用rbオブジェクトにupメ...

canvas.toDataURL image/png エラー処理方法の推奨

問題の背景:再生中のビデオのスクリーンショットを撮る必要があります。ビデオはビデオタグを使用して再生...

Vue はデータの変更をどのように追跡しますか?

目次背景例誤解 - コールスタックを表示するためにウォッチでブレークポイントを設定する正しいアプロー...

JavaScript タイマー原理の詳細な説明

目次1. setTimeout() タイマー2. setTimeout() タイマーを停止する3. ...

MySQLのさまざまなロックに関する詳細な理解

目次ロックの概要ロックの分類データベース操作の粒度データ操作の種類MySQL ロックさまざまなストレ...

回転灯効果を実現するWeChatアプレットの例

序文日常の開発では、テキストの水平スクロール効果(一般にカルーセルと呼ばれる)によく遭遇します。これ...

JavaScript イベント委任 (プロキシ) の使用例の詳細

目次導入例: イベントの委任記述方法1: イベント委譲書き方2: 各子要素がイベントをバインドする例...

vue.config.js パッケージ最適化構成

Baiduの情報は多様すぎて目が回ります。心配しないでください。私はあなたのためにそれを体験しました...

PhpStormがVirtualBoxに接続できない問題を解決する

問題の説明: phpstorm の SFTP ホストを 192.168.122.1 に設定すると、接...

Vue イベントの $event パラメータ = イベント値の場合

テンプレート <el-table :data="データリスト"> &...

CSS 画面サイズ適応実装例

CSS 画面サイズの適応を実現するには、まず CSS3 @media メディア クエリを導入する必要...