MySQLの基本を素早く学ぶ

SQLを理解する

SQL は、データとやり取りするために使用する最も長い方法の 1 つです。機能別に分けると、次の 4 つの部分に分けられます。

DDL、データ定義言語。データベースオブジェクト、データテーブル、およびデータ列を定義します。つまり、データベースとテーブル構造を追加、削除、変更します。
DML、データ操作言語。データテーブルを追加、削除、変更します。
DCL、データ制御言語。アクセス権とセキュリティレベルを定義します。
DQL、データクエリ言語。データを照会するために使用されます。

SQL を記述する際、多くの SQL が同じ大文字または小文字で記述されていないことに気付くことがあります。これは SQL の実行結果には影響しませんが、統一された記述基準を維持することが効率を向上させる鍵となります。通常は、次の原則に従います。

テーブル名、テーブル別名、フィールド名、フィールド別名などは小文字にする必要があります。
SQL の予約語、関数名、バインド変数などは大文字で記述されます。
データテーブルでは、フィールド名にはアンダースコアが付けられます。

現在トップの DBMS は次のとおりです。

リレーショナルデータベース: リレーショナルモデルに基づいて構築されたデータベース。テーブルを作成する場合、テーブル間の関係は通常、ER 図で表されます。
キーバリューデータベース: キーと値の形式でデータを格納します。検索速度が速いのが利点ですが、リレーショナルデータベースのように WHERE などのフィルタリング条件を使用できないのが欠点です。一般的なシナリオは、コンテンツキャッシュとして使用することです。
ドキュメントベースのデータベースでは、保存時に情報を処理するための基本単位としてドキュメントを使用します。
検索エンジン: 全文検索用に設計されています。中心となる原則は「逆インデックス」です。
列ベースのデータベース: MySQL などの行ベースのデータベースと比較して、列ベースのデータベースはデータを列に格納します。列は同じデータ型であるため、圧縮率が高く、システムの I/O を削減できます。分散ファイルシステムに適していますが、機能は比較的制限されています。
グラフデータベースは、グラフデータ構造を使用してエンティティ間の関係を保存します。たとえば、ソーシャルネットワーク内の人々の関係は、ノードとエッジで構成されるデータモデルを使用して実装されます。

SELECTを理解する

SELECT は通常、SQL を学習するときに最初に触れるキーワードです。ここでは基本的な内容については説明しませんが、一般的な仕様をいくつか示します。

エイリアス

SELECT name AS n FROM 学生

定数をクエリし、固定定数列を追加します。

'学生情報' を student_info、name として選択し、学生から取得します。

重複行を削除する

学生から異なる年齢を選択

DISTINCT は、後続のすべての列から重複を削除することに注意してください。次の例では、年齢と名前の組み合わせが削除されます。

DISTINCT 年齢、名前 FROM 学生を選択

データを並べ替えます。ASC は昇順、DESC は降順を表します。

たとえば、最初に名前で並べ替え、名前が同じ場合は年齢で並べ替えます。

SELECT DISTINCT age FROM student ORDERY BY name,age DESC

返品回数を制限する

SELECT DISTINCT age FROM student ORDERY BY name DESC LIMIT 5

SELECT実行順序

SELECT の実行順序を理解することによってのみ、より効率的な SQL を記述できます。

SELECT 順序には 2 つの原則があります。

キーワードの順序を逆にすることはできません。

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

SELECT は次の順序で実行されます。

FROM > WHERE > GROUP BY > HAVING > 選択されたフィールド > DISTINCT > ORDER BY > LIMIT

SELECT DISTINCT 学生ID、名前、count(*) を数値 #シーケンス 5 として選択します。
FROM student JOIN class ON student.class_id = class.class_id #シーケンス 1
WHERE age > 18 #シーケンス 2
GROUP BY student.class_id #シーケンス 3
num > 2 の場合 #シーケンス 4
ORDER BY num DESC #シーケンス 6
LIMIT 2 #シーケンス 7

このプロセスを 1 つずつ分析する前に、上記の各ステップで仮想テーブルが生成され、この仮想テーブルが次のステップで入力として使用されることを知っておく必要がありますが、このプロセスは私たちには見えません。

FROM ステートメントから開始して、学生テーブルとクラステーブルに対して CROSS JOIN カテシアン積演算を実行し、仮想テーブル vt 1-1 を取得します。
ONスクリーニングを通じて、vt1-1に基づいてフィルタリングが実行され、テーブルvt1-2が得られます。
外部行を追加します。左結合、右結合、完全結合が使用される場合、外部行が関与し、vt1-2 に基づいて外部行が追加され、vt1-3 が得られます。
テーブルが 2 つ以上ある場合は、上記の手順が繰り返されます。
vt1 の最終テーブルデータを取得した後、WHERE の後のフィルタリングステージが実行され、テーブル vt2 が取得されます。
次に、GROUP ステージに進み、グループ化して vt3 を取得します。
次に、HAVING ステージで、グループ化されたデータがフィルタリングされ、vt4 が取得されます。
次に、SELECT ステージに入り、必要なフィールドを抽出して vt5-1 を取得します。次に、DISTINCT ステージに進み、重複する行をフィルターして vt5-2 を取得します。
次に、指定されたフィールドを並べ替え、ORDER BY フェーズを入力して vt6 を取得します。
最後に、LIMIT ステージで、最終結果である vt7 に対応する指定された行が取り出されます。

sum() などの関数計算が含まれる場合、集計関数の計算は GROUP BY の後、HAVING の前に実行されます。
age * 10 などの式の計算は、HAVING フェーズの後、SELECT フェーズの前に計算されます。

ここから、SQL 効率を向上させる最初の方法をまとめることができます。

SELECT を使用する場合は、SELECT * の代わりに明示的な列を指定します。これにより、ネットワーク転送の量が削減されます。

WHERE によるフィルタリング

WHERE フィルタリングを使用する場合、比較演算子、論理演算子、ワイルドカードの 3 つの一般的な方法があります。

比較演算子については、よく使用される演算子を次の表に示します。

論理演算子の場合、複数の比較演算子を接続して複数条件スクリーニングを実行できます。よく使用される演算子は次のとおりです。

AND と OR が同時に出現した場合、 AND の方が優先順位が高く、最初に実行されることに注意してください。 () 括弧が存在する場合、括弧の優先順位が最も高くなります。

ワイルドカードフィルタリングの使用:

例えば、(%) は 0 個以上の文字を表し、(_) は 1 文字のみを表します。

関数

プログラミング言語で定義された関数と同様に、SQL でも、合計、平均、長さなど、簡単に使用できる関数がいくつか定義されています。

一般的な関数は主に次の 4 つのカテゴリに分類され、分類の原則は列を定義するときのデータ型に基づいています。

算術関数:

文字列関数

文字列を使用して日付を比較する場合は、比較に DATE 関数を使用する必要があることに注意してください。

日付関数

変換機能:

CAST 関数は、データ型を変換するときに四捨五入しません。元の値が小数の場合、整数に変換するとエラーが報告されます。

変換時に、DECIMAL(a,b) 関数を使用して小数の精度を指定できます。たとえば、DECIMAL(8,2) は精度が 8 桁であることを意味します。つまり、小数と整数を足した値は最大 8 桁になります。小数点以下の最大桁数は 2 です。

次にSELECT CAST(123.123 AS DECIMAL(8,2))で変換します。

集計関数

通常、集計関数はテーブルデータを要約するために使用され、入力はデータのセット、出力は単一の値になります。

よく使用される集計関数は次の 5 つです。

COUNT 関数には特別な注意が必要です。詳細については、こちらの記事を参照してください。

グループ化の方法

結果をカウントする場合、多くの場合、GROUP BY ステートメントに対応する特定の条件に従ってデータをグループ化する必要があります。

たとえば、各クラスの生徒数を数えます。

SELECT class_id, COUNT(*) as student_count FROM student \
クラスIDごとにグループ化します。

GROUP BY の後に複数の列名を指定してグループ化することもできます (例: クラスと性別)。

SELECT class_id, sex, COUNT(*) as student_count FROM \
学生 GROUP BY class_id, sex;

HAVINGフィルタリングとWHEREフィルタリングの違い

WHERE と同様に、グループ化されたデータをフィルタリングできます。違いは、WHERE は行に適用され、HAVING はグループに適用されることです。

さらに、WHERE でサポートされている操作は HAVING でもサポートされています。

たとえば、2 人以上のクラスをフィルタリングできます。

SELECT class_id, COUNT(*) as student_count FROM student \ 
GROUP BY クラスID \ 
学生数が20を超えている;

サブクエリ

より複雑な状況では、結果を取得した後、その結果を入力として使用して別の結果セットを取得するなど、ネストされたクエリが実行されることがよくあります。

SQL では、クエリは相関サブクエリと非相関サブクエリに分けられます。

次のテーブル構造を想定します。

-- ----------------------------
`student` が存在する場合はテーブルを削除します。
テーブル「学生」を作成（
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL デフォルト ''
 `age` int(3) NOT NULL,
 `sex` varchar(10) NOT NULL デフォルト '',
 `class_id` int(11) NOT NULL COMMENT 'クラスID',
 主キー (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 デフォルト CHARSET=utf8;

-- ----------------------------
-- 学生の記録
-- ----------------------------
`student` に VALUES ('1', '胡一', 13, '男', '1') を挿入します。
`student` に VALUES ('3', '王阿', 11, '女', '1') を挿入します。
`student` に VALUES ('5', '王淇', 12, '男', '1') を挿入します。
INSERT INTO `student` VALUES ('7', '刘伟', 11, '女', '1');
INSERT INTO `student` VALUES ('7', '王识', 11, '女', '2');

-- ----------------------------
`student_activities` が存在する場合はテーブルを削除します。
テーブル「student_activities」を作成します（
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL デフォルト ''
 `stu_id` int(11) NOT NULL COMMENT 'クラスID',
 主キー (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 デフォルト CHARSET=utf8;
`student_activities` に VALUES ('1', 'Museum', 1) を挿入します。
`student_activities` に VALUES ('3, '春の遠足', 3) を挿入します。

非相関サブクエリ

サブクエリは、データテーブルからデータ結果を照会します。このデータ結果が 1 回だけ実行される場合、このデータ結果は次に実行されるメインクエリの条件として使用されます。

ここでは、Hu Yi と同じクラスの生徒の名前を照会します。

SELECT name FROM student WHERE class_id = \
(SELECT class_id FROM student WHERE name='胡一')

ここでは、まず Hu Yi のクラスを検索します。これは 1 つのクエリのみです。次に、クラスに基づいて生徒を検索します。これは相関のないサブクエリです。

相関サブクエリ

サブクエリを複数回実行する必要がある場合は、外部クエリから開始し、クエリごとにサブクエリを渡し、結果を外部クエリにフィードバックするループが使用されます。

たとえば、各クラスの平均年齢よりも年上の生徒の名前を照会してみましょう。

SELECT name FROM student as s1 WHERE age > 
	(SELECT AVG(age) FROM student as s2 where s1.class_id = s2.class_id)

ここでは、各生徒のクラス情報をもとに、該当クラスの平均年齢を調べて判断します。サブクエリが実行されるたびに、外部クエリに基づいて計算する必要があります。このようなサブクエリは相関サブクエリです。

EXISTS サブクエリ

相関サブクエリでは、EXISTS と一緒に使用されることが多いです。条件が満たされているかどうかを判断するために使用されます。条件が満たされている場合は True、満たされていない場合は False になります。

たとえば、学校の活動に参加した生徒の名前を照会します。

SELECT NAME FROM student as s where \
	EXISTS(SELECT stu_id FROM student_activities as sa where sa.stu_id=s.id)

同様に、NOT EXISTS は存在しないことを意味します。条件を満たす場合は FALSE となり、満たさない場合は True となります。

たとえば、学校の活動に参加していない生徒の名前を照会します。

SELECT NAME FROM student as s where \
	存在しません(SELECT stu_id FROM student_activities as sa where sa.stu_id=s.id)

比較サブクエリを設定する

サブクエリでセット演算子を使用して結果を比較できます。

IN を使用して、学校活動に参加した生徒の名前を照会してみましょう。

SELECT name FROM student WHERE id IN (SELECT stu_id FROM student_activities)

EXISTSとINの違い

EXISTS と IN はどちらも同じ機能を実現できますが、それらの違いは何でしょうか?

ここで、テーブル A とテーブル B があり、A と B の両方にフィールド cc があり、cc に対して b+ インデックスが作成され、テーブル A には n 個のレコードがあり、テーブル B には m 個のインデックスがあるとします。

抽象モードは次のとおりです。

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

EXISTS の場合、最初に外部テーブルが 1 つずつループされます。外部テーブルの各結果が取得された後、サブクエリの内部テーブルに取り込まれ、値が存在するかどうかが判断されます。

疑似コードは次のようになります。

Aのiの場合
Bのjの場合
j.cc == i.ccの場合:
結果を返す

まず、外観 A を見てみましょう。すべての行をトラバースする必要があるため、n 回かかります。内部テーブル B をクエリする場合、インデックスの使用により、クエリ効率は m ではなく log(m) B + ツリーの高さになります。

したがって、全体の効率は n * log(m) となります。

したがって、テーブル A の項目数がテーブル B の項目数よりも大幅に少ない場合は、EXISTS クエリを使用することをお勧めします。

IN をもう一度見てみると、まず内部テーブル B がクエリされ、次に外部テーブル A が判断に使用されます。疑似コードは次のとおりです。

Bのiの場合
Aのjの場合
j.cc == i.ccの場合:
結果を返す

まず内部テーブルのすべてのデータを検索する必要があるため、必要な回数は m です。外部テーブル A を見ると、cc インデックスが使用されているため、n は log(n)、つまり m * log(n) に簡略化できます。

したがって、テーブル A のデータがテーブル B のデータよりも大幅に大きい場合は、IN クエリを使用することをお勧めします。

要約すると、IN と EXISTS では、小さなテーブルを使用して大きなテーブルを駆動するという原則が採用されています。

ここでは、NOT EXISTS と NOT IN の違いについて詳しく説明します。

cc が含まれない A から * を選択 (cc が B から選択) 

存在しない場合に A から * を選択 (B.cc=A.cc の場合、B から cc を選択)

NOT EXITS の場合、EXISTS と同様に、cc のインデックスを内部テーブルに使用できます。テーブル A がテーブル B より小さい場合に適用されます。

しかし、NOT INの場合はINとは異なります。ccはインデックスで設定されるため、cc IN (1, 2, 3)はWHERE cc=1 OR cc=2 OR cc=3に変換でき、ccインデックスは正常に使用できます。しかし、NOT INの場合はcc!=1 OR cc!=2 OR cc!=3に変換されます。このとき、不等式クエリなのでインデックスは使用できず、テーブル全体をスキャンします。

つまり、インデックスが設定されている場合、NOT EXISTS は NOT IN よりも効率的です。

しかし、インデックスがない場合、IN と OR は異なります。

1. 異なる操作
1. in: in は、親クエリテーブルと子クエリテーブル間のハッシュ接続です。
2. or: or は親クエリテーブルをループし、ループが繰り返されるたびに子クエリテーブルを再度クエリします。
2. さまざまなアプリケーションシナリオ
1. in: in は、サブクエリテーブルに親クエリテーブルよりも多くのデータがある場合に適しています。
2. or: or は、サブクエリテーブル内のデータが親クエリテーブル内のデータよりも少ない場合に適しています。
3. 異なる効率
1. in: インデックスがない場合、in 以降のデータ量が増加しても、in の実行効率は大幅に低下しません。
2. または: インデックスがない場合、またはの後のデータ量が増加するにつれて、またはの実行効率が大幅に低下します。