SQLデータ操作（操作を学ぶ学生や初心者のためのSQL入門チュートリアル）

オペレーションを学ぶ学生と初心者のためのSQL入門チュートリアル

序文
導入
1 予備知識
1.1 データベースとデータテーブル
1.2 最も基本的なSQLクエリ構文
2. さらに一歩進んで
2.1 IN操作
2.2 関係演算子: AND と OR
2.3 ソート: ORDER BY
3 より高度なトピック
3.1 集計関数: COUNT、SUM、AVG
3.2 クエリ結果の重複排除: DISTINCT構文
3.3 クエリデータのグループ化: GROUP BY構文
3.4 集計関数の良きパートナー: HAVING 構文
4 少し範囲を超えたトピック
4.1 フィールドタイプ
4.2 索引
4.3 JOIN構文ファミリー
4.4 ネストされたSELECT構文
5. 質問に答える: 簡単な復習

序文

タイトルが「運用を学ぶ学生と初心者のための SQL 入門チュートリアル」なのはなぜですか?

これはもともと、私と良好な関係にあるオペレーションのクラスメートのためにカスタマイズされた SQL チュートリアルでした。 Ele.me では、本社の運用スタッフがトラブルシューティング、オンラインの問題の追跡、運用上の意思決定を行う際に、運用管理システムを通じて情報を照会し、データアナリストが提供する分析データに頼るだけでなく、SQL を記述してデータベース管理コンソールからより詳細でリアルタイムのビジネスデータを直接取得し、そのデータに基づいてタイムリーな分析を実行して、より迅速に運用計画を提供する必要があることがよくあります。このような状況において、SQL はオペレーションを学ぶ学生にとってますます重要なスキルになってきています。インターネット上には多くの SQL チュートリアル (例: w3school) があり、また、オペレーションのクラスメートの机の上に置いてあった SQL の紙の本も読んだことがあります。これらのチュートリアルは非常に優れていますが、一般的には、短いデモを交えて大量の構文を紹介することに重点を置いています。この参考書は経験豊富な人にとっては素晴らしいものですが、初心者やまだ始めていない人にとっては少し難しいです。運用担当者の使用シナリオを振り返ると、ほとんどの場合、既存の条件に基づいていくつかの簡単なクエリを実行しています。場合によっては、クエリ結果の集計、グループ化、並べ替え、または 2 つまたは 3 つのデータテーブルを同時にクエリするなど、比較的複雑なクエリが実行されることがあります。また、テーブルの作成、インデックスの作成、テーブルフィールドの変更、フィールド値の変更などの操作は、基本的に運用担当者の日常業務では遭遇しません。

上記の理由に基づいて、私はこの良き友人がより高い ROI で SQL を始められるように支援することを本来の目的としてこのチュートリアルを作成しました。このチュートリアルを書く際に考慮すべき点は次のとおりです。

データベースとデータテーブルの基本概念と最も単純な SQL 構文から始めて、必要な準備を行ってください。以降の各章の難易度は徐々に増加します。
クエリ構文（より正確には、最も一般的に使用されるクエリ構文）のみが紹介され、その使用方法とルーチンが明確に説明されています。テーブルの作成やテーブル構造の変更など、日常業務ではまだ使用されていない操作は含まれず、学習のリズムを妨げる圧倒的な構文を回避します。
散りばめられた小テストと小レビューを通じて、知識ポイントの理解度を適時にテストし、学習した知識ポイントを復習します。
理解を助けるためにいくつかのビジネスシナリオデモを組み合わせます。
章の最後には簡単なレビューが用意されており、復習として、また習得していない知識ポイントをテストするために使用できます。

チュートリアルを読んだすべての学生は、独自のデータベースサービスを構築し (MySQL をインストールすることをお勧めします)、チュートリアルのデモでさらに演習を行うことをお勧めします。レビュー内の例、クイズ、SQL ステートメントなど、自分で実行することもできます。これは、構文に慣れるのに役立つ良い方法でもあります。もちろん、独自のデータベースを構築するのは難しい場合があります。このチュートリアルを書いているときに、私は自分の VPS に MySQL (MariaDB) をインストールし、友人が使用できるように接続スクリプト (MySQL への接続プロセスを非表示にする) を提供しましたが、このアプローチはすべての人に適しているわけではありません。具体的なインストール方法と使用方法はこのチュートリアルの範囲外ですので、オペレーションガールの方はおなじみの R&D クラスメイトに助けを求めてください。

データ作成スクリプト - このスクリプトを使用してデモデータをMySQLにインポートします
VPS 上の MySQL に接続するためのスクリプト - 基本的な原理は、SSH トンネルを確立し、トンネル内のリモート MySQL と通信することですが、実際には、ローカルで MySQL サービスを構築する方が便利です。参考用です。

読みやすくするために、ここ sql_tutorial から pandoc+latex でエクスポートされた PDF をダウンロードできます。

このオープンデーイベントでは、Gunicorn に関連するトピックも共有する予定ですので、どなたでもご参加いただけます。共有されたコンテンツの概要は次のとおりです。

サキュレント

導入

実際、SQL はそれほど難しくありません。 Sql は、リレーショナルデータベースと対話するのに役立つ構文のセットです。主に以下の 4 種類の操作をサポートします。

DQL: 実際にはデータクエリ操作です。特定の構文を使用して、データベース内の必要なデータ結果を照会し、表示します。
DML: データフィールドの更新、テーブルへの新しいデータの挿入などのデータ変更操作。
DDL: 新しいテーブルの作成や新しいインデックスの作成などのデータ定義操作 (インデックスとは何でしょうか? これについては後で説明します)。
DCL: 特定の人に特定のテーブルを照会する権限を与えるなどのデータ承認操作。

怖いですね。しかし実際には、DML、DDL、DCL の 3 種類の操作は日常の業務ではほとんど使用されません。実際によく使用されるのは、最初のタイプであるデータクエリ操作 (DQL) です。 Sql の基本的なクエリ構文は比較的単純ですが、何が難しいのでしょうか?難しいのは、基本的な文法を学んだ後、それを実際のケースにどのように適用すればよいかわからないということだと思います。以下の内容では、現実に非常に近いクラウドソーシング運用事例をいくつか取り上げ、最も基本的な SQL クエリ構文を一つずつ説明し、それを具体的なシナリオにどのように適用するかを分析します。

1 予備知識

さて、ここまで自慢してきましたが、まだいくつか基本的なことを紹介する必要がありますが、これはチュートリアル全体の中で最も退屈な部分であり、後でもっと面白くなることをお約束します。

1.1 データベースとデータテーブル

これら 2 つの概念とそれらの関係をより簡単に理解するために、次のような例え話をすることができます。

データテーブル: テーブルです。よく使用する行と列のある Excel テーブルを想像してください。各行はデータの一部であり、各列はこのデータの特定のフィールドに対応します。もちろん、このテーブルにもテーブル名があります。データテーブルでも同じことが言えますが、フィールド名とテーブル名は Excel テーブルほどわかりやすくありません。たとえば、Excel テーブル内の列のフィールド名は rider id と呼ばれ、データテーブル内の対応する名前は rider_id である場合があります。 Excel テーブル名はライダー基本情報テーブルと呼ばれ、データテーブル内の対応するテーブル名は tb_rider になる場合があります。
データベース: 関連するテーブルのグループが集中管理される場所です。フォルダとして考えることができます。通常は、「クラウドソーシングビジネス」というフォルダーを作成し、クラウドソーシング業務に関連するすべての Excel スプレッドシートをこのフォルダーに配置します。データベースについても同様です。たとえば、crowd というデータベースがあり、そこに tb_rider や tb_order など、ライダーや運送状に関連する複数のデータテーブルが格納されているとします。

したがって、「リレーショナルデータベース」という概念は非常に恐ろしいものですが、原理は実際には非常に単純です。列間には一定のつながりがあり、それらを統合すると意味のあるデータが形成されます。これらのデータをまとめるとテーブルが形成され、関連するテーブルのグループをまとめて管理するとデータベースが作成されます。

1.2 最も基本的なSQLクエリ構文

最も基本的な SQL クエリ構文は、実際には 1 つだけです。

 SELECT 列名 (またはすべての列を示す *) FROM テーブル名 WHERE フィルタ条件;

この構文を FROM、WHERE、SELECT の順序で理解してみましょう。

FROM テーブル名: 名前が示すように、テーブル名で指定されたテーブルからのものです。
WHERE フィルター条件: 「フィルター条件が満たされた場合」を意味します。
SELECT 列名: これらのレコードを選択し、指定された列名を表示することを意味します。

直列に並べると、FROM 以降に指定したデータテーブルから WHERE 以降に指定した条件に合うデータを抽出し、SELECT 以降に指定したフィールドの列を表示するという意味になります。簡単じゃないですか？しかし、少し抽象的であるように思えます。それでは、具体的で非常に単純な例をいくつか見てみましょう。生徒の数学の期末試験の成績表があるとします。データテーブルは次のようになり、tb_stu_math_score と呼ばれます。

id (自動増分主キー) name (生徒名) number (生徒番号) grade (学年) class (クラス) score (得点) 1 コナン 010201121002 愛 010202121003 光彦 01020312984 あゆみ 01020412955 源太 0102051259

次のクエリを理解してみましょう。

[1] tb_stu_math_scoreから名前を選択 WHERE score>=95;

tb_stu_math_score テーブルから、スコアが 95 を超える生徒の名前を選択します。結果は明らかです:

|名前 | | ---- | |コナン| |あい| |光彦| |あゆみ|

[2] tb_stu_math_scoreからname、numberを選択します。score<60;

tb_stu_math_score テーブルから、スコアが 60 未満の生徒の名前を選択します。結果は次のとおりです。

名前番号Yuantai010205

[3] tb_stu_math_scoreから*を選択 WHERE score=100;

tb_stu_math_score テーブルからスコアが 100 の学生の情報をすべて選択します (SELECT の後の * 記号はすべてのフィールドを示すことに注意してください)。結果は次のとおりです。

ID名番号学年クラススコア1Conan010201121002Ai01020212100

クイズ

次の SQL クエリステートメントを見てください。それらの意味とクエリ結果が何であるか知っていますか?

 1. tb_stu_math_score から名前、学年、クラス、スコアを選択します。WHERE number = "010201";
2. SELECT * FROM tb_stu_math_score WHERE name = "小艾";
3. tb_stu_math_score から id、score を選択します。WHERE number = "010202";

2. さらに一歩進んで

ここでは、SQL クエリの最も基本的な構文について学習しましたが、ほとんどすべての SQL クエリはこのようになっているので、この最も基本的な構文構造を理解しておけば、後で学習するのがはるかに簡単になります。次に、いくつかの例を通してこの基本的な構文を拡張し、より高度な SQL クエリ操作を説明します。まず、サンプルのデータテーブルがどのようになるかを見てみましょう。

tb_rider というライダーデータテーブルと tb_order という運送状データテーブルがあるとします。これら 2 つのテーブルは次のようになります。

[1] ライダーデータテーブル: tb_rider

フィールドの意味:

id: 自動インクリメント主キー。これも怖い響きの名前ですが、実際の意味は単純です。 「自動増分」とは、このデータテーブルに新しいレコードが作成されるたびに、データベースが新しいレコードの ID 値として、前の ID 値に固定のステップサイズ (デフォルトは +1) を自動的に追加することを意味します。いわゆる「主キー」は、データテーブル内のレコードを一意に識別できるフィールドです。各レコードのIDは異なるため、それが主キーとなります。このフィールドは、データの一意性を単純に識別するために使用することも、何らかのビジネス上の意味を持つこともできます。たとえば、ここでの ID はライダーのアカウント ID でもあります。
name: ライダーの名前;
real_name_certify_state: 実名認証ステータス: 1-認証中、2-認証成功、3-認証失敗。
レベル：ライダーレベル、3-金メダル、2-銀メダル、1-銅メダル、0-普通。
level_city: レベル都市;
is_deleted: このデータが有効かどうか。ほとんどの生産ライン関連のデータテーブルには、このようなマーキングフィールドがあります。 0-削除されていない（有効）、1-削除された（無効）。
created_at: このデータが作成された時刻。これは、すべての生産ラインデータテーブルの必須フィールドでもあります。
updated_at: このデータが最後に更新された時刻。このフィールドは、すべての生産ラインデータテーブルでも必須です。

[2] 運送状データテーブル: tb_order

フィールドの意味:

id: 自動インクリメント主キー。まあ、ここでの ID は単なる主キーであり、他にビジネス上の意味はありません。
order_id: 運送状番号、ビジネスレベルでの運送状の一意の識別子。
order_state: 運送状の現在のステータス。 10-注文待ち、20-店舗到着待ち、80-集荷待ち、40-配達済み。
rider_id: 注文を受け取ったライダーの ID。取得されていない運送状の場合、このフィールドのデフォルト値は 0 です。
Rider_name: 注文を受け取ったライダーの名前。取得されていない注文の場合、このフィールドのデフォルト値は空文字になります。
grabbed_time: 注文が取得された時刻。取得されていない運送状の場合、このフィールドはデフォルトで「1970-01-01 00:00:00」に設定されます (これは特別な時間です。興味がある場合は、キーワード「timestamp」で検索できます)。
created_at: このデータが作成された時刻。これは、すべての生産ラインデータテーブルの必須フィールドでもあります。
updated_at: このデータが最後に更新された時刻。このフィールドは、すべての生産ラインデータテーブルでも必須です。

ちょっとしたレビュー

次の SQL ステートメントと返されるデータ結果の意味を理解してみてください。

 1. tb_rider から name、real_name_certify_state を選択し、level = 3 を指定します。
2. tb_order から * を選択し、rider_id = 1 を指定します。
3. tb_order から rider_id、rider_name、order_id、grabbed_time を選択する
 ここで、order_state = 40;

2.1 IN操作

シナリオ:ライダーのグループは、上海で金メダリストになるはずだったが、メダルのレベルが普通だった、または、展示では金メダリストだったが、上海での金メダル活動を楽しめなかったというオフラインフィードバックを受け取りました。 id=(2, 4, 7) のライダーはすでにわかっており、そのレベルの更新ステータスを確認したいと考えています。

この時点で、前に紹介したように、次のように 1 つずつクエリを実行することを選択できます。

 1. tb_rider から name、real_name_certify_state、level、level_city を選択し、id=2 を指定します。
2. tb_rider から name、real_name_certify_state、level、level_city を選択し、id=4 を指定します。
3. tb_rider から name、real_name_certify_state、level、level_city を選択し、id=7 を指定します。

確かにこれで目的は達成できますが、ライダーが 2 人か 3 人しかいない場合、かろうじて機能する程度です。ライダーが数十人いる場合は、この方法でチェックするのは困難になります。このとき、IN 構文を使用できます。

 tb_rider から name、real_name_certify_state、level、level_city を選択し、id を IN(2、4、7) とします。

とても簡単ですよね？しかし、簡単に理解してみましょう。 WHERE id IN(2,4,7)は、idフィールドの値が2、4、7であるレコードをフィルタリングすることを意味します。このSQL文を実行すると、次の結果が得られます。

名前実名証明書州レベルレベル都市バナー239トール101コールソン239

したがって、ライダーのトールは実名認証に合格しなかったため、金メダルを授与されないことは間違いないことがわかります。バナー選手とコールソン選手はともに金メダリストですが、彼らのレベル都市は福州なので、上海金メダル活動を楽しむことはできません。

ライダーのIDはわからないが、ライダーの名前しかわからない場合はどうなりますか? IN クエリを使用することもできますが、今回はフィルタリング条件が名前になり、値の範囲は「Banner」、「Thor」、「Coulson」になります。ちょうどこんな感じです。

 tb_rider から名前、実名証明書の状態、レベル、レベル都市を選択します。
WHERE name IN("Banner", "Thor", "Coulson");

それで、以下の結果が得られました。

名前実名証明書州レベルレベル都市バナー239トール101コールソン239コールソン102

おっと！コールソンは二人いるよ！

実際のアプリケーションでは、この点に特に注意する必要があります。

2.2 関係演算子: AND と OR

最もよく使用される 2 つの関係演算子は AND と OR であり、複数のフィルター条件を接続するために使用されます。名前が示すように、 AND は「そして」を意味し、 AND の前後のフィルタリング条件が同時に満たされることを意味します。 OR は「または」を意味し、OR の前後のフィルタリング条件のいずれかが満たされていることを意味します。ちょっと抽象的ですよね？具体的な例を見てみましょう。

シナリオ: 2017-02-01 (2017-02-01 を含む) から 2017-06-01 (2017-06-01 を除く) の間に登録されたライダーのすべての情報を表示するとします。

登録時間は、データ内のライダー情報の作成時間（created_at）に対応します。つまり、作成日時が 2017-02-01 から 2017-06-01 の間である tb_rider テーブル内のデータを照会することになります。では、このような SQL はどのように記述すればよいのでしょうか?このとき、AND``` を使用できます。

 tb_rider から * を選択 WHERE created_at >= "2017-02-01 00:00:00"
かつ created_at < "2017-06-01 00:00:00";

もう一度宣伝しましょう。現在のシナリオは次のようになるとします: 2017-02-01 より前 (今日を含む) または 2017-06-01 より後 (今日を含む) に登録されたライダーのすべての情報を表示したいとします。このSQLをどのように記述すればよいでしょうか?これはor関係なので、OR を使用する必要があります。

 tb_rider から * を選択 WHERE created_at <= "2017-02-01 00:00:00"
または created_at >= "2017-06-01 00:00:00";

もちろん、AND や OR などの関係演算子は、2 つのフィルター条件を接続できるだけでなく、複数の AND や OR を使用して複数の異なるフィルター条件を接続することもできます。例えば、2017-02-01（2017-02-01 を含む）から 2017-06-01（2017-06-01 を除く）の間に登録し、現在ゴールドレベルにあるライダーの情報をすべて確認したい場合は、次のように記述します。

 tb_riderから*を選択
created_at >= "2017-02-01 00:00:00" の場合
かつ created_at < "2017-06-01 00:00:00"
AND レベル = 3;

2.3 ソート: ORDER BY

上記で学んだ知識のポイントを復習してみましょう。次のようなシナリオがあります。

このSQLをどのように記述しますか?まず 3...1...2...3 について考えて、それがあなたが考えていた通りかどうかを確認してください。

 tb_order から order_id、created_at を選択
ここで、rider_id = 1
かつ grabbed_time >= "2017-12-30 00:00:00"
かつ grabbed_time < "2017-12-31 00:00:00"
かつ order_state = 40;

正しく書けなかったとしても心配しないでください。分析してみましょう。

ライダー Stark のライダー ID は 1 なので、最初のフィルター条件は rider_id=1; です。
2017 年 12 月 30 日に取得された出荷注文 ID を確認したいため、2 番目のフィルタリング条件は grabbed_time フィールドに対応する取得時間であると判断します。 2017-12-30 は実際には 2017-12-30 00:00:00 (含む) から 2017-12-31 00:00:00 (含まない) までの期間を指します。
ついに条件が整いました。 order_state フィールドは運送状の状態を識別するため、フィルタリング条件は order_state=40 です。

このステートメントを実行すると、次の結果が得られます。

注文ID作成日時3000002017123000012017-12-30 12:34:173000002017123000022017-12-30 12:34:183000002017123000052017-12-30 16:01:03

少し欠陥があります。運送状を作成時刻の逆順に並べ替え、最近作成された運送状を先頭に表示したいと思います。この場合、ORDER BY 構文を使用できます。

 tb_order から order_id、created_at を選択
ここで、rider_id = 1
かつ grabbed_time >= "2017-12-30 00:00:00"
かつ grabbed_time < "2017-12-31 00:00:00"
かつ order_state = 40
ORDER BY created_at DESC;

もう一度理解しましょう。DESC は「減少」を意味し、それに対応するのは ASC の増加です。 ORDER BY created_at DESC は、クエリ結果 (ORDER) を created_at フィールド値 (BY) の降順 (DESC) で並べ替えることを意味します。したがって、次の結果が得られます。

注文ID作成日時3000002017123000052017-12-30 16:01:033000002017123000022017-12-30 12:34:183000002017123000012017-12-30 12:34:17

より複雑なシナリオを見てみましょう。2017 年 12 月 30 日と 2017 年 12 月 31 日のすべての運送状のすべての情報を照会し、ライダー ID で昇順、次に運送状のステータスで降順で並べ替えるとします。少し考えさせてください。

このとき、SQLは次のようになります。

 tb_orderから*を選択
created_at >= "2017-12-30 00:00:00" の場合
かつ created_at < "2018-01-01 00:00:00"
ORDER BY rider_id ASC, order_state DESC;

これまでの各知識ポイントを理解していれば、 「ライダーIDの昇順で並び替えて表示し、次に運送状ステータスの降順で並び替えて表示する」という点だけが混乱するはずです。実際には、1 つのフィールドを並べ替えるだけでなく、複数のフィールドを並べ替えの基準として使用することもできます。また、異なるフィールドの並べ替えルール (増加/減少) は異なる場合があります。ただし、ソートが優先されます。例えば、ここではrider_idフィールドの値が同じで順序が判別できない場合にのみ、同じrider_idを持つデータをorder_stateフィールドの値に従ってソートすることになります。たとえば、rider_id=2 および order_state=80 のデータは、rider_id=1 および order_state=40 のデータより上位にランク付けすることはできません。

この SQL ステートメントを実行すると、次の結果が得られます。

この部分は比較的難しいですが、より多くの例を比較することで理解できます。

3 より高度なトピック

このセクションに入るということは、基本的に前の内容を習得したことを意味します。日常業務では、上記の知識ポイントを使用して、シナリオの約 30% を解決できます (もちろん、練習を重ねることで完璧になるというプロセスがあります)。このセクションでは、さらに高度で、もちろんより難しい SQL スキルをいくつか紹介し続けます。この部分の学習を終えてこれらのスキルを習得すると、データの確認を必要とするほとんどのシナリオに対処できることがわかります。この章の内容はかなり難しいので、複雑な表に直面した場合、重要なポイントに集中するのはさらに難しくなります。したがって、テーブル構造を簡素化し、必要なフィールドのみを保持します。新しい tb_order テーブルは次のとおりです。

新しい列が追加されました:
merchant_customer_distance: 配送距離（販売者からユーザーまでの直線距離）（キロメートル単位）。

3.1 集計関数: COUNT、SUM、AVG

集計関数の名前に怖がらないでください。それは単純にデータの処理として理解することができます。まず、これらの集計関数の基本的な定義を見てみましょう。

COUNT: クエリ結果セット内の特定の列をカウントします。
SUM: クエリ結果のフィールドを合計します。
AVG: 平均を意味し、クエリ結果のフィールドの平均値を計算します。

具体的な例をいくつか見てみましょう。

[1] シナリオ: 2017年12月30日のライダーStarkの完了した注文（ステータス40）の合計数を照会する

このSQLは次のように記述できます。

 tb_order から COUNT(id) を選択し、rider_id = 1 を指定します。
かつ order_state = 40 かつ created_at >= "2017-12-30 00:00:00"
かつ created_at < "2017-12-31 00:00:00";

この時点で、WHERE...AND...AND... 部分の意味をよく理解しているはずなので、この部分については詳しく説明しません (自信を持ってください。まずは自分で理解するようにしてください)。

COUNT(id)の意味に注目してみましょう。実際のところ、これは非常に簡単で、id 列を数えるだけです。この SQL をまとめて見ると、ライダー ID が 1 (rider_id=1)、注文ステータスが完了 (order_state=40)、作成時間が 2017 年 12 月 30 日以降 (created_at>="2017-12-30 00:00:00)、作成時間が 2017 年 12 月 31 日未満 (created_at<"2017-12-31 00:00:00) である tb_order テーブル (FROM tb_order) のデータをフィルター (WHERE) し、返された結果セットを ID 列に従ってカウントすることを意味します。

tb_order テーブルを見ると、ライダー Stark が 2017-12-30 に配達し、ステータスが完了となっている運送状は、300000201712300001、300000201712300002、および 300000201712300005 であることがわかります。対応する自動増分 ID は id=[1, 2, 5] であるため、id 列をカウントした結果は 3 になります。したがって、取得するクエリ結果は次のようになります。

| COUNT(id) | | --------- | | 3 |

特定の列を数えるのではなく、特定の条件を満たす行の合計数を確認したい場合があります。この場合、COUNT(*) 構文を使用できます。たとえば、上記の SQL は次のように記述することもできます。

 tb_order から COUNT(*) を選択し、rider_id = 1 を指定します。
かつ order_state = 40 かつ created_at >= "2017-12-30 00:00:00"
かつ created_at < "2017-12-31 00:00:00";

返される結果には 3 行あるため、次の表のような結果が得られます。

|カウント(*) | | -------- | | 3 |

COUNT(column) と COUNT(*) は完全に同等のように見えますか?これは特定のシナリオでは当てはまりますが、ここでは COUNT の 2 つの小さな特殊性を追加する必要があります。

COUNT は重複を自動的に削除しません。
COUNT: クエリ結果で、カウントに使用される列の値が「空」の場合、このレコードはカウントされません。

ちょっとめまいがしますよね？心配しないでください。2つの例を見てみましょう。以下に示すような、非常に単純なテーブルが 2 つあるとします。

サンプルテーブル1: tb_sample_1

idname1スターク2スターク3コールソン4ナターシャ5スターク

サンプルテーブル2: tb_sample_2

idname1スターク2スターク3\4\5ナターシャ6コールソン

次の SQL ステートメントの実行結果はどうなるでしょうか?

 1. tb_sample_1からCOUNT(id)を選択します。
2. tb_sample_1からCOUNT(*)を選択します。
3. tb_sample_1からCOUNT(名前)を選択します。
4. tb_sample_2からCOUNT(名前)を選択します。

一つずつ分析してみましょう。

 1. tb_sample_1からCOUNT(id)を選択します。

この SQL ステートメントでは分析する内容はあまりありません。tb_sample_1 テーブルの id フィールドの値の範囲は id=[1, 2, 3, 4, 5] で、合計 5 つの値があり、次の結果が得られます。

| COUNT(id) | | --------- | | 5 |

 2. tb_sample_1からCOUNT(*)を選択します。

COUNT(*) はクエリ結果の行の合計数を計算することを意味するため、この SQL ステートメントで分析する内容はあまりありません。 tb_sample_1 には 5 行のデータがあるため、結果は次のようになります。

|カウント(*) | | -------- | | 5 |

 3. tb_sample_1からCOUNT(名前)を選択します。

この SQL ステートメントでは、名前列をカウントします。 tb_sample_1 テーブルには、Stark が 3 人、Coulson が 1 人、Natasha が 1 人含まれています。 COUNT は重複を自動的に削除しないため、結果は次の表に示すように 5=3(Stark)+1(Coulson)+1(Natasha) になります。

| COUNT(名前) | | ----------- | | 5 |

 4. tb_sample_2からCOUNT(名前)を選択します。

この SQL ステートメントでは、名前列を引き続きカウントします。 tb_sample_2テーブルには、スターク2人、コールソン1人、ナターシャ1人、そして2人が含まれています。 COUNTは重複を削除しないので、両方のStarkがカウントされますが、COUNTは「null」の値を持つ結果をカウントしないので、2つの無視されます。したがって、最終結果は次の表に示すように、4=2(スターク)+1(コールソン)+1(ナターシャ)となります。

| COUNT(名前) | | ----------- | | 4 |

[2] シナリオ: ライダーStarkの累計配達距離を照会する

まず、累積配達走行距離、つまりライダーが完了したすべての配達注文の配達距離（販売者からユーザーまでの直線距離）の合計を定義しましょう。

ここでのキーワードは合計なので、SUM 集計関数を使用する必要があります。フィールドを合計するということは、返された結果セット内のフィールドの値を合計することを意味します。このシナリオの SQL の記述方法を見てみましょう。

 SUM(merchant_customer_distance) を tb_order から選択します
ここで、rider_id = 1 かつ order_state = 40;

この声明を分析してみましょう。 FROM tb_order WHERE rider_id=1AND order_state=40 は簡単に理解できます。これは、ライダー ID が 1 で配送ステータスが 40 のレコードを tb_order テーブルから除外することを意味します。ここで、SUM(
merchant_customer_distance) は、前の条件でフィルタリングされたデータ結果を意味します。
merchant_customer_distance列の値が合計されます。ライダーIDと配送状況でフィルタリングされたレコードはid=(1, 2, 5)であり、対応する
merchant_customer_distanceの値は、merchantcustomerdistance=(2.5, 1.8, 1.2)であり、合計は5.5=2.5+1.8+1.2となり、次の表のようになります。

| SUM(販売者顧客距離) | | --------------------------------- | | 5.5 |

[3] シナリオ: ライダーStarkの平均配達距離を問い合わせる

同様に、まず平均配達距離を定義しましょう。平均配達距離とは、配達員による完了したすべての注文の配達距離の合計（販売者からユーザーまでの直線距離）を、完了した注文の総数で割ったものです。

SUM の経験と以前の「プレビュー」に基づいて、今回は AVG 集計関数を使用することは想像に難くありません。フィールドの平均化とは、結果セット内のフィールドの値を合計し、それを結果行の合計数で割ることを意味します。 AVG は自動的に「除算」アクションを完了するため、SQL は前のシーンの SUM とまったく同じになります。

 tb_orderからAVG(merchant_customer_distance)を選択します
ここで、rider_id = 1 かつ order_state = 40;

ライダーIDと配送状況でフィルタリングされたレコードはid=(1, 2, 5)であり、対応する
merchant_customer_distanceの値は、merchantcustomerdistance=(2.5, 1.8, 1.2)であり、平均結果は次の表に示すように、1.83=(2.5+1.8+1.2)/3となります。

| AVG(販売者顧客距離) | | --------------------------------- | | 1.83 |

セクション3.1の最後に書かれています:

これらのシーンを研究した後、あなたはどう感じるでしょうか?これらの集約機能自体は非常にシンプルだと思いますか、それとも一度にこれほど多くの知識ポイントを浸透させるのは少し難しいと思いますか?実際、集計関数には複雑な側面があります。上で見てきたケースは、比較的単純な使用方法です。でも心配しないでください。一方、運用作業で遭遇するシナリオのほとんどは、これらの例よりも複雑ではありません。一方、クエリロジックが複雑になるほど、クエリ結果を「予測」することが難しくなるため、これらの集計関数を複雑に使用することは推奨されません。 SQL は「ロジック」を表現するのに適した言語ではありません。データの再処理にロジックが大量にある場合は、アナリストに依頼するか、ロジックの表現に適した他のプログラミング言語を学習することを検討する必要があります。

2 番目に言いたいのは、自分にもっと自信を持ち、もっと忍耐強くなること。 SQL は Python や Java などの汎用プログラミング言語とは異なり、構文に加えて体系的なプログラミング概念と設計哲学も組み込まれていますが、それでも初めて使い始めるときは少し難しく感じるかもしれません。しかし、例題を何回か理解し、自分で書いていくうちに、特に実際の業務で実際のシナリオに遭遇したときに、それをどのようにSQLに変換するかを考え、さらに練習し、さらに復習と分析をしていくうちに、いつの間にかマスターできるようになります。練習すれば完璧になると信じてください。

次のセクション 3.2 と 3.3 では、さらに難しい 2 つの実用的な SQL 構文と、それらを集計関数と組み合わせて使用する方法について引き続き紹介します。

3.2 クエリ結果の重複排除: DISTINCT構文

名前が示すように、DISTINCT 構文は列から重複する値を削除するために使用されます。まず、セクション 3.1 の COUNT の例を確認しましょう。

この例では、テーブル tb_sample_1 を使用します。この表はとてもシンプルです。もう一度投稿させてください。

idname1スターク2スターク3コールソン4ナターシャ5スターク

同様に、確認したい SQL ステートメントも非常に単純です。

 tb_sample_1からCOUNT(名前)を選択します。

この SQL 行は以前にすでに分析済みです。名前列を数え、Stark が 3 人、Coulson が 1 人、Natasha が 1 人いることがわかりました。最終結果は次の表のようになります。

| COUNT(名前) | | ----------- | | 5 |

しかし、場合によっては、同じ名前を繰り返しカウントせず、同一の名前が複数ある場合に 1 回だけカウントしたいことがあります。この時点で、個別の構文を使用できます。

 tb_sample_1からカウント（個別の名前）を選択します。

以前のSQLと比較して、個別のキーワードを追加するだけです。実際、それについてあまり複雑に考える必要はありません。カウント（別個の名前）は、重複排除の後に名前をカウントします。 TB_Sample_1には3つのスタークがありますが、3つのスタークが繰り返されます。個別の構文を使用した後、それらは一度のみ計算されます。 1つのクールソンと1つのナターシャもあるため、結果は次の表に示されています。

| count（個別の名前）| | ---------------------- | | 3 |

異なる構文は単独で使用できます。この場合、列の重複した値を削除することを意味します。ただし、上記の例のようにカウント集計関数と一緒に使用することがより一般的であるため、重複排除後の結果をカウントできます。

3.3クエリデータのグループ：構文別グループ

以前には、TB_ORDERテーブルに基づいて多くのSQL構文の知識を説明しました。このテーブルの外観を思い出しましょう。

過去を確認して新しいことを学びましょう！いくつかの質問から始めて、以前に学んだSQLの知識を確認しましょう。

質問1：次のシナリオに対応するSQLステートメントを作成してみてください

2017-12-30に作成されたWaybillsの総数を照会し、完了した状態と2キロメートル以上の配送距離を照会します。
2017-12-30に作成されたすべてのWaybillの平均配送距離を照会し、そのステータスが完了しました。
2017-12-30に配送タスクを完了したライダーの総数（少なくとも1つの配達）を照会します。

質問2を確認してください：次のSQLステートメントの意味を理解し、クエリの結果を書き留めてください

1。TB_ORDERからcount（order_id）を選択します。ここで、order_state = 40
 およびmerchant_customer_distance> = 2.0およびcreated_at> = "2017-12-30 00:00:00"
 およびcreated_at <"2017-12-31 00:00:00";
2。TB_ORDERからAVG（merchant_customer_distance）を選択します
 およびcreated_at> = "2017-12-30 00:00:00"およびcreated_at <"2017-12-31 00:00:00";
3。tb_orderからcount（個別のrider_id）を選択しますwhere order_state = 40
 およびcreated_at> = "2017-12-30 00:00:00"およびcreated_at <"2017-12-31 00:00:00";

質問2をレビューするための答えがレビューの質問1であることに気付くのに十分賢いですか？まだ発見していない場合は、大丈夫です。戻って数回分析するだけで、練習が完璧になることは絶対に真実です。ただし、これらの例をレビューすることは、レビューだけではありません。シナリオ1と2を拡張し、新しい知識ポイントを説明しましょう。次の2つのシナリオを検討してください。

2017-12-30の各ライダーの完了した注文の総数を照会します。
2017年12月30日の各ライダーの平均配送距離を照会します。

まず、ここでシナリオ1を分析しましょう。「2017-12-30当日」の条件は、created_at> = '2017-12-30 00：00：00'and_at <' 2017-12-31 00:00:00 'に変換することは難しくありません。 Waybillの「総量」を計算する必要があるため、Order_idでカウント操作を実行することを考えることは難しくありません。それでは、各ライダーをどのようにグループ化しますか？これは、グループが登場するときです。

 tb_orderからcount（order_id）を選択します
and created_at> = "2017-12-30 00:00:00"およびcreated_at <"2017-12-31 00:00:00"
rider_idによるグループ;

ここでの実行順序は、最初にWHERE条件に従ってフィルタリングし、次にライダーID（グループ）ごとにグループ化し、最終的にWayBill番号ごとに各グループをカウントすることに注意してください。したがって、結果を次の表に入力できます。

| count（order_id）| | -------------------- | | 3 | | 1 | | 1 | | 1 |

何かが間違っているようですか？結果に対応するライダーが見えません！心配しないでください。SQLをわずかに変更して、ライダーIDとライダー名の2つの列を表示できます。

 rider_id、rider_name、count（order_id）を選択します
TB_ORDERからOrder_State = 40から
およびcreated_at> = "2017-12-30 00:00:00"
およびcreated_at <"2017-12-31 00:00:00"
rider_idによるグループ;

以下の表に結果が得られます。

rider_idrider_namecount（order_id）1stark32banner15natasha13rogers1

これはもっと明確ではありませんか？

シナリオ2をもう一度分析しましょう。前の例では、「2017-12-30」と「完全な注文」の2つの条件は、すでに便利で簡単にできることです。「平均配送距離」の問題は大きくなく、AVGに変換できます（
merchant_customer_distance）。それでは、各ライダーにどのようにグループ化しますか？または構文によるグループを介して。私たちのSQLはこのように成長します。

 rider_id、rider_name、avg（merchant_customer_distance）を選択します
TB_ORDERからOrder_State = 40から
およびcreated_at> = "2017-12-30 00:00:00"
およびcreated_at <"2017-12-31 00:00:00"
rider_idによるグループ;

結果は、下の表に記載されています。

rider_idrider_nameavg（merchant_customer_distance）1stark1.832banner1.85natasha2.73rogers0.5

ここで実行命令について言及する必要があります。まず、条件付きフィルタリングが実行され、次にフィルタリングされたデータ結果がライダーIDに従ってグループ化され、最後に各グループのデータが実行されます。
merchant_customer_distance列の平均値。

3.4アグリゲート機能の優れたパートナー：構文を持つ

構文を持つことの意味は、どこに似ています。使用するとき、通常、フィルタリング条件を持つという構文構造に従います。構文が類似しており、使用方法が非常に似ている場所の意味は、なぜ薄い空気から構文を出している複数を考え出す必要があるのですか？その理由は、集計関数です。構文を集計関数で使用できないが、総関数の計算結果にフィルター条件として依存する必要がある場合があります。セクション3.3のシーン2の例を見てみましょう。

シナリオ2：2017-12-30に注文を完了した各参加ライダーの平均配送距離を確認します。

分析を通じて、そのようなSQLを取得します。

 rider_id、rider_name、avg（merchant_customer_distance）を選択します
TB_ORDERからOrder_State = 40から
およびcreated_at> = "2017-12-30 00:00:00"
およびcreated_at <"2017-12-31 00:00:00"
rider_idによるグループ;

シナリオ2に基づいて拡張しましょう。

拡張シナリオ2：2017-12-30に注文を完了した各参加ライダーの平均配送距離を照会し、平均配送距離が1.5kmを超えるデータを除外します。

このようなSQLの結果が得られます。

 rider_id、rider_name、avg（merchant_customer_distance）を選択します
TB_ORDERからOrder_State = 40から
およびcreated_at> = "2017-12-30 00:00:00"
およびcreated_at <"2017-12-31 00:00:00"
Rider_idによるグループ
AVG（merchant_customer_distance）> 1.5;

比較の後に、変化が最後にAVGを持っていることが多いということを知ることは難しくありません（
merchant_customer_distance）> 1.5句。分析してみましょう。 Select ... from ...ここから...使用法は前のものから変更されていません。 rider_id by rider_idグループは、rider_idに従って選択の結果をグループ化します。グループ化が完了した後、AVG（
merchant_customer_distance）> 1.5グループごとのステートメント
merchant_customer_distanceフィールド値は平均を取り、結果を返す結果として平均1.5を超える結果をフィルタリングします。

このSQLを実行すると、結果が得られます。

rider_idrider_nameavg（merchant_customer_distance）1stark1.832banner1.85natasha2.7

ライダー（ライダーID = 3）のロジャースは、「1.5kmを超える平均配送距離」のステートメントを持つことで指定されたフィルタリング条件を満たしていないため、クエリの結果にはありません。

4範囲を超えた何か

4.1フィールドタイプ

現時点では、単語の種類は馴染みがないように聞こえるかもしれませんが、実際、コンピューターサイエンスの分野では、タイプは非常に基本的で広く存在する概念であり、ほとんどすべてのプログラミング言語に独自のタイプシステムがあります。

[1]なぜタイプの概念を定義するのですか？

タイプの概念に関して、私は「未熟」の理解を持っています。プログラミング言語は、人間と機械とのやり取りのためのツールです。人間にはデータの論理的理解があります。 2903を見ると、これは整数であると考えます。1031.2903を見ると、これは小数点であると思います。マシンがデータを処理またはアクセスすると、バイナリ操作を実行するか、ビットに応じて読み取りおよび書き込みを実行します。人間がコンピューターをバイナリに直接入力することは困難であり、もちろん、コンピューターが結果をバイナリ形式に直接出力することを受け入れることはできません。ある日、コンピューターの電卓を使用して1+1 = 2を計算したい場合は、タイプがありません。マシンがバイナリを処理する方法を理解する必要がある場合は、入力する必要がある場合があります。
00000000000000000000000000000000000000000000000001+
0000000000000000000000000000000000000000001、結果もバイナリです
000000000000000000000000000000000000010、これがどれほど疲れるか。タイプを使用すると、はるかに簡単です。データのタイプを定義し、タイプ条約に従って、コンピューターはこの1をバイナリに変換する方法を知っています（16ビット、32ビット、または64ビットのバイナリに変換する必要があります。このバイナリデータを操作する場合は、整数または浮動小数点数などと見なす必要があります）、および結果を再び変換する方法を知っている場合、
000000000000000000000000000000000010は整数2に変換されています。

プログラミング言語のタイプは、実際には、データを理解および操作するために人間と機械によって合意された一連のルールです。

要するに、マシンの目には、どの操作がデータで実行されていても、0と1で構成されるものの文字列が見えます。 「バイトストリーム」または「バイナリストリーム」と呼ばれるこの種のものと呼ぶ特別な用語があります。

一緒に別の例を見てみましょう。このようなバイナリストリームに対処したいとします。
00000010011101101101111、このバイナリストリームは多くのことを表すことができます。その意味を明確にするには、そのタイプを明確にする必要があります。たとえば、次の2つの異なるタイプでは、このストリームで表されるコンテンツは完全に異なります。

このバイナリストリームを32ビット整数と見なす場合、整数10322903を表します。
このバイナリストリームを2つの16ビット整数と見なしている場合（最初の16ビット0000000010011101は整数を表し、最後の16ビット1000001111010111は整数を表します）、それぞれ2つの整数157と33751を表します。

私はあなたが現在10322903であることを知っています。なぜ32ビットの整数に変換されますか？なぜ2つの16ビット整数変換を157および33751と考えているのですか？まだ多くの疑問があります。しかし、バイナリと小数の変換方法については、ここでは拡張しません。興味があり、知りたい場合は、この方法を個別に伝えることができます。上記について話す最も重要なことは、「タイプ」の概念を定義することは、人間のコンピューターの相互作用のプロセスで基本的にメカニズムを提供し、無差別のバイナリストリームに特定のセマンティクスを与えることを理解してくれることを願っています。

それとも抽象的すぎますよね？それは問題ではありません、あなたに別の栗を与えましょう。

準備知識の章でTB_STU_MATH_SCOREテーブルを使用しました。あなたがそれを元に戻すために一生懸命働かないようにするために、このテーブルのコンテンツをもう一度投稿しましょう。

ID（自己アドレスプライマリキー）名（学生名）番号（学生番号）グレード（グレード）クラス（クラス）スコア（スコア）1コナン010201121002 Xiao AI 010202121003 Mitsuhiko 01020312984 Stepi 01020412955 Yuan Tai 0105125999999989

また、以下と同様のSQLステートメントも書きました。

 tb_stu_math_scoreからスコアを選択します= 1;

このSQLステートメントは非常に簡単です。これで、データスコアの最初の行の値を返すことがわかり、結果は次のとおりです。

|スコア| | ----- | | 100 |

この結果を取得するプロセス全体を分析して、タイプがどのように機能するかを理解するのに役立ちましょう。

このSQLステートメントは実行され、対応する行はプライマリキーIDに従って見つかり、次にこの行とスコア列の値が取得されます。
ただし、コンピューターのストレージはバイナリに基づいているため、得られたスコア値は0000000000000000000000000000100100に似たバイナリストリームです。
この時点で、TB_STU_MATH_SCOREテーブルの定義によれば、スコア列は整数として定義されるため、バイナリストリームは整数数に変換され、部分変換の後、000000000000000000000000000000000000000000000000000000000001100100に対応する整数値は100です。

実際、それは逆に非常に似ています。たとえば、このテーブルにデータを記述すると、記述されたスコア列の値は100です。ストレージはバイナリに基づいているため、テーブルの定義に従って、スコア列のタイプは整数であるため、値100は対応するバイナリストリームに整数として変換されます。
0000000000000000000000000000001100100およびライブラリに書き込まれます。

[2] SQLの主なデータ型は何ですか？

しばしばSQLにさらされるデータ型には、主にいくつかのカテゴリが含まれます。

1整数

tinyint：非常に小さな整数を表すために使用されます。たとえば、これらの2つのフィールドはレコードが有効かどうかを示しているため、is_deletedやis_validなどのフィールドのフィールドタイプとしてよく使用されます。
Smallint：200、404、401などの整数値など、より大きな整数を表すことができます。
INT：一般的に使用される整数は、10322などの比較的大きな整数を表すために使用できます（実際、INTが表すことができる整数の範囲はこれよりもはるかに大きい）。
Bigint：非常に大きな整数を表すために使用されます。たとえば、ほとんどのテーブルは、このタイプを使用して、10322903のような非常に大きな整数を表します（実際、Bigintが表すことができる整数の範囲はこれよりもはるかに大きいです）。

2フローティングポイントタイプ

小数：緯度や経度など、非常に正確な小数を表すことができます。

3文字列タイプ

Char：固定長文字列。
VARCHAR：可変長文字列。

ここでは、固定された長さと変数の長さは、このパートのコンテンツが実際にこのチュートリアルの範囲を超えているため、ここではあまり違いを説明しません。一般に、Varcharは実際のアプリケーションでより使用されます。それらはすべて、「u、huohuo！」に会うのと同じような一連のキャラクターを表しています。もちろん、それは中国語にいることもできます。

4つの日付タイプ

日付：日付のみが含まれ、現在の日付「2018-01-23」などの時間が含まれない日付を表します。
DateTime：現在の日付「2018-01-23 03:01:43」など、日付と時刻の両方の部分を含む日付を表します。

ここでは、SQLにいくつかの一般的なフィールドタイプを簡単に導入しているだけで、その原則、違い、およびその他のデータ型を詳細に説明していません。私たちは、これらの「ハイエンド」コンテンツを学び、最初にこれらのタイプの意味を理解することを急いでいません。

[3]テーブル内の各列のタイプが何であるかをどのようにして知っていますか？

最初の方法は、DESCテーブル名コマンドを使用することです。たとえば、前述のtb_riderテーブルの各列フィールドタイプを調べたい場合は、コマンドdesc tb_riderを実行して次の結果を得ることができます。

ここの最初の列はフィールド名を表し、2番目の列タイプは対応するフィールドのフィールドタイプを表すことに注意してください。たとえば、IDフィールドはINTタイプです。

2番目の方法は、ショーCreate Table TB_RiderなどのShow Create Table Nameコマンドを使用して、次の結果を得ることです。

テーブルを作成します `tb_rider`（
 `id` int（11）null auto_incrementではありません、
 `name` varchar（32）Not Null default ''コメント 'name'、
 `real_name_certify_state` int（11）not null default '0'コメント 'ID証明書ステータス'、
 `is_deleted` tinyint（4）null default '0'コメント 'このユーザーが存在するかどうか。 0：存在しません、1：存在しません '、
 `created_at` DateTime Not Null default current_timestampコメント 'Create Time'、
 `updated_at` null default current_timestamp on update current_timestampコメント「更新時間」、
 `level'tinyint（4）not null default '0'コメント 'ライダーレベル：0通常1ブロンズ2シルバー3ゴールド'、
 `level_city` varchar（32）null default ''コメント '配達レベル'、
 主キー（ `id`）、
 キー `ix_created_at`（` created_at`）、
 key `ix_updated_at`（` updated_at`）
）Engine = Innodb auto_increment = 9デフォルトcharset = utf8コメント= '配信情報';

私たちは

`name` varchar（32）not null default ''コメント 'name'

ここで声明を説明しましょう。

名前はフィールド名（列名）です。
Varcharは、フィールドタイプが文字列であることを意味します。
nullは、このフィールドが空になることができないことを意味します。空は、このフィールドに値が指定されていないことを意味します（空の文字列に相当しないことに注意してください）。
「デフォルト」とは、このフィールドの値が指定されていない場合、空の文字列がデフォルト値として使用されることを意味します。
コメント「名前」はこのフィールドに関するメモであり、このフィールドのビジネスの意味を示し、表示にのみ使用されます。

4.2インデックス

インデックス作成は間違いなく、リレーショナルデータベースで最も重要で困難なトピックです。経験豊富なR＆Dの学生でさえ、しばしばインデックスの穴に足を踏み入れます。ただし、クエリをよりよくサービスするためだけにインデックスを紹介します。いくつかの複雑な概念や根本的な原則への関与を避けるために最善を尽くします。

[1]インデックスとは？

では、インデックスとは正確には何ですか？データベースを非常に厚い本として理解できます（100,000ページがあるとします）。本の内容はデータベースのデータであり、インデックスは本のディレクトリです。この本を読んだことがないとし、この瞬間に本第7章、セクション2を読みたいとします。ディレクトリがない場合は、本全体を閲覧して、読みたいことを見つける必要がある場合があります。ただし、ディレクトリがある場合は、ディレクトリをチェックして、最初に対応するページ番号を見つけるだけで、次にそのページに移動して表示したいものを確認する必要があります。インデックスは似ています。最初に、ターゲットデータの場所を見つけてインデックスを照会し、特定の場所からデータのコンテンツを読み取ります。

インデックスを設計する方法は、データベーステーブルを設計する際に考慮すべき重要なポイントの1つです。インデックスは通常、テーブル内の列または複数の列で構成されています。列がインデックスとして設定されると、テーブルにデータが書き込まれるたびに、列がインデックスに更新されます。実際、インデックス作成は、ディスクI/O、Bツリー、オプティマイザーなどの多くの技術的概念を含む、技術レベルで比較的複雑です。ただし、今のところこれらの詳細については説明しません。

[2]なぜインデックス作成が重要であり、その使用は何ですか？

インデックス作成が重要である主な理由は、クエリの速度を大幅に改善できることです。上記の本の例を挙げてください。この本のページが十分に大きい場合（2,000万ページがあると仮定）、ディレクトリがなく、特定の章の内容を確認する場合、それはほとんどファンタジーです。データベースにも同じことが言えます。テーブル内に数列、数十行、または数百の行しかない場合、実際にはインデックスがあるかどうかに大きな違いはありません。ただし、テーブルに多くのデータがある場合（クラウドソーシングウェイビルテーブル、200万枚以上の行）、インデックスがない場合、ターゲットデータを見つけるために、クエリ速度は非常に遅くなります。

[3]インデックスの使用方法は？

インデックスを使用するには非常に簡単です。WHERE条件のクエリ条件としてインデックス列を使用する必要があります。例を挙げてみましょう。

まだこのTB_ORDERテーブルです。このデータテーブルのORDER_IDがインデックス列であると仮定すると、順調_IDをクエリ条件として使用する場合、次のSQLなどのインデックスを使用します。

 select * from tb_order where order_id = 300000201712310007;

もちろん、クエリ条件としてorder_idを使用する同様のSQLは、インデックスを使用して、次の2つのSQLステートメントの意味を理解しているかどうかを確認します。

 1。TB_ORDERから *を選択します
 ここで（300000201712310007、300000201712310006）
 and Order_State = 40;
2。TB_ORDERからOrder_Id、Order_Stateを選択します
 Order_id> = 300000201712300001
 and Order_id <= 300000201712300006
 and Order_State = 40;

したがって、テーブル内の複数の列がインデックスであり、クエリSQLで、これらのインデックス列は、どの列がインデックスとして使用される場所のクエリ条件として使用されますか？ TB_ORDERテーブルのORDER_IDとRider_ID列の両方がインデックス列であると仮定して、次のSQLステートメントはインデックスとして使用しますか？

 tb_orderから *を選択します
Order_id> = 300000201712310001
and Order_id <= 300000201712310007
およびrider_id> 0;

答えは不明です。使用するインデックス、またはインデックスを使用するかどうかは、複数のインデックスの長所と短所、およびインデックスの使用と使用の長所と短所を分析し、クエリを実行する最適な方法を選択するオプティマイザーによって基本的に決定されます。トピックのこの部分は複雑すぎるので、ここでは拡張しません。オプティマイザーはありますが、クエリのために、明示的なインデックスフィールドをクエリ条件として使用できる場合は、可能な限りインデックスフィールドを使用する必要があります。

[4]インデックスのタイプ、テーブル内のどの列がインデックス列であるかを判断する方法は？

DESCテーブル名を覚えており、テーブルテーブル名の作成の構文を作成します。フィールドタイプセクションで言及されていますか？前のセクションでは、TB_RIDERテーブルでこれら2つの構文を使用しました。このセクションでは、TB_ORDERテーブルを見てみましょう。

最初はDESC TB_ORDERです。次の結果が得られます。

以前にタイプアイテムに焦点を当てていたので、ここで重要なアイテムに焦点を当てましょう。一部の列に対応するキーは空であることがわかります。つまり、この列（またはこのフィールド）はインデックス列（またはインデックスフィールド）ではありません。ただし、列IDの対応するキー、Order_id、created_at、およびupdated_atはすべて値です。つまり、これらの列はインデックス列です。しかし、これらの列の値は異なりますが、なぜこれはなぜですか？これは、インデックス内の異なるタイプに分割されます。1つずつ説明しましょう。

PRI：プライマリの略語であり、この列をプライマリキーとしてマークします。テーブル内のデータの各行のインデックスを一意に識別するために使用される最初にプライマリキーの概念を導入しました。
Uni：名前が示すように、唯一の意味であるユニークの略語です。 UNIキーに設定された列は、重複した値を許可しません。この列にまったく同じ値の2行のデータをテーブルに挿入しようとすると、エラーが発生します。あなたは間違いなく混乱すると思います。それで、UNIキーとPRIキーの違いは何ですか？まず、これら2つのタイプのインデックス間で実装に違いがあります（この時点で深く入りません。データベースの下部にインデックスの実装が含まれます）。第二に、PRIキーはデータベースレベルのセマンティクスに関するものであり、データの一意性のみを記述しますが、UNIキーはビジネスレベルのセマンティクスに関するものです。たとえば、ここではOrder_idフィールドがあります。これは、ビジネスにまったく同じウェイビル数を持つ2つのウェイビルができないため、Order_id列をUNIキーに設定する必要があります。
MUL：複数の略語であり、この列が通常のインデックスに設定されていることを示しています。それが倍数と呼ばれる理由は、この時点でこの列がインデックスのみとして使用される可能性があるため、またはこの列とMULとしてマークされた他の列がインデックスを形成することです（複数の列で構成されるこのインデックスは複合インデックスと呼ばれます）。

現在、私たちはまだSQLとデータベースの知識を学習する初期段階にあります（はい、SQLに加えて、いくつかのデータベース原則も密かに導入しました）ので、書面の違いをお知らせください。これらはすべてインデックスであり、合理的に使用される限り、SQLクエリの効率を高速化するのに役立ちます。

テーブル内のインデックス列を識別するもう1つの方法は、ショーの作成テーブルTB_ORDERなどのショーCREATEテーブル名コマンドを使用することです。次の結果が得られます。

テーブル「tb_order」（
 `id` bigint（20）null auto_incrementコメント '外部プロビジョニング、内部使用」、
 `order_id` bigint（20）not null default '' ''コメント 'Waybillのトラッキング番号（外の世界に提供できます）」、
 `rider_id` int（11）null default '0'コメント '配信ID'、
 `rider_name` varchar（100）null default ''コメント '配信者名'、
 `order_state`tinyint（4）null default '0'コメント '配信ステータス'、
 `is_deleted` tinyint（4）null default '0'ではない、
 `grabbed_time`タイムスタンプnull default current_timestampコメント「グラブ注文時間」、
 `merchant_customer_distance` decimal（10,2）not null default '0.00'コメント「ショップから顧客までの散歩距離」、
 `created_at` DateTime Null default current_timestamp、
 `updated_at` null default current_timestamp on update current_timestamp、
 主キー（ `id`）、
 一意のキー `uk_order_id`（` order_id`）、
 キー `ix_created_at`（` created_at`）、
 key `ix_updated_at`（` updated_at`）
）Engine = innodb auto_increment = 14デフォルトcharset = utf8コメント= '配信順序';

最後の数行で主要なキー、ユニークなキー、キーを見たことがありますか？それらは、DESC TB_ORDER結果のPRI、UNI、およびMULに対応し、それぞれプライマリキーインデックス、一意のインデックス、および通常インデックスを識別します。各行の括弧内のフィールドは、対応するインデックス列を表します。

4.3グラマーファミリーに参加します

結合構文を明確に説明するためにいくつかの方法を試しました（結合構文は確かに少し複雑です）、私は自分自身を満たすことができません。最終的に、私は例から始めることにしました。まず新しいテーブルを見てみましょう。テーブルの作成ステートメントは次のとおりです。

テーブルの作成 `tb_grab_order_limit`（
 `id` bigint（20）null auto_incrementコメント '自己排出プライマリキー」、
 `rider_id` bigint（20）not null default 0コメント「ライダーID」、
 `order_grab_limit` int（11）not null default '0'コメント '注文の受け入れの上限'、
 `is_deleted` tinyint not null default0コメント 'レコードが削除されているかどうか'
 `created_at` DateTime Not Null default current_timestampコメント 'Create Time'、
 `updated_at` null null default current_timestamp on update current_timestampコメント「更新時間」、
 主キー（ `id`）、
 キー `ix_rider_id`（` rider_id`）、
 キー `ix_created_at`（` created_at`）、
 key `ix_updated_at`（` updated_at`）
）エンジン= innodbデフォルトcharset = utf8コメント= "ライダーオーダーキャップテーブルのカスタマイズ";

リトル・ウェンクシ

上記の表作成ステートメントを参照して、次の質問に答えようとしてください。

このテーブルの名前は何ですか？
Order_grab_limitフィールドの意味は何ですか？
このテーブルの主要なキーインデックスは何ですか？一意のインデックスはいくつあり、通常のインデックスはいくつありますか？

それは正しい！これは、カスタムライダーオーダーキャップテーブルです。特定のライダー（rider_id）に対応する順序制限（Order_grab_limit）について説明します。表のデータは次のとおりです。

idrider_idorder_grab_limitis_deletedcreated_atupdated_at11111018-02-25 17：22：032018-02-25 17：22：0322902018-02-25 17：22：212018-02-25 17：22：2134902018-02-25 17：22：22-25 17：22：3125 17：3125 17：22：2125 17：22-25 17： 2018-02-25 17：22：392018-02-25 17：22：39510802018-02-25 17：22：462018-02-25 17:22:46

以前に繰り返し使用されたTB_Riderテーブルを確認しましょう。

（ついに基礎の敷設を終えました！）

[1]左結合から始めます

これらの2つのテーブルに基づいて、シナリオを想像してください。TB_Riderテーブルのすべてのライダーに対応するカスタム注文の上限を照会したいとします。 SQLをどのように記述する必要がありますか？

アイデア1：最初にTB_RIDERテーブルのすべてのライダーIDを見つけてから、これらのライダーIDをクエリ条件として使用して、TB_GRAB_ORDER_LIMITテーブルからのカスタムオーダーアパート上限の対応するレコードをクエリ条件として照会します。

 tb_riderからidを選択します。

そして

tb_grab_order_limitからrider_id、order_grab_limitを選択します
ここで、rider_id in（1、2、3、4、5、6、7、8）;

アイデア1は明らかに悪い考えです。ただし、アイデア1は、このクエリの問題を解決するための基本的なポイントを説明しています。

最終的に必要なデータは、2つのテーブルTB_RIDERとTB_GRAB_ORDER_LIMITを組み合わせて取得します。
これらの2つのデータテーブルを関連付ける条件は、ライダーIDです。
クエリ要件は次のため、TB_RIDERテーブルのすべてのライダーであるため、TB_RIDERテーブルのライダーIDはクエリリファレンスセットとして使用する必要があります。
TB_RIDERテーブルのすべてのライダーがカスタムオーダーの受け入れ制限を持っているわけではありません。アイデア1のクエリスキームの欠点の1つは、クエリの結果に基づいて論理的な変換を行う必要があることです。これは、どのライダーにカスタム注文の受け入れ制限がないかを見つける必要があることです（ライダーは返品結果ではありません）。

アイデア2：これらのキーポイントに基づいて、左結合構文を使用でき、以下は対応するSQLステートメントです。

 tb_rider.id、tb_grab_order_limit.order_grab_limitを選択します
tb_riderから左からtb_grab_order_limitに参加します
on tb_rider.id = tb_grab_order_limit.rider_id;

ここで、最初に結合構文の基本構造を紹介します：表1（内側/左/右/フル）結合表2on表1 =表1 =列2。列2。結合する必要がある2つのデータテーブルの前後に接続され、オンキーワードの後に関連する条件が続きます。合計には4つのタイプの結合があります。つまり、内側の結合、左結合、右結合、完全な結合があります。例として左の結合を使用します。表1の四方のreftに表2on表1に結合します。列1 =表2。列2の意味は、表1の列1の値を横断することです。表2の列2の値がそれに等しい場合、対応するレコードが表示されます。表2。列2と表1。列1が等しくない場合、ディスプレイはヌルになります。

アイデア2の例では、tb_riderはtb_rider.id = on tb_grab_order_limitに左に左に参加します。
tb_grab_order_limit.rider_idの意味は、tb_riderテーブルのid列の値をトラバースすることです（tb_riderテーブルのIDフィールドのビジネス意味はライダーIDです）。それが存在しない場合、それはヌルです。

また、Selectステートメントのコンテンツは以前に使用したものと非常に似ていることもわかりますが、わずかに異なります。どちらもテーブル名と列名で書かれています。実際、これは主にフィールドが属するテーブルを示しています。これは、TB_RIDERテーブルやTB_GRAB_ORDER_LIMITテーブルなど、JOINの2つのデータテーブルに同じ名前の列が存在する可能性があるため、両方ともIDフィールドを持っているため、意味は完全に異なるため、書くのは明確です。

最終的なアイデア2の結果は次のとおりです。

idorder_grab_limit1112949677 \ 8 \ 5 \ 3 \

ライダーID =（7、8、5、3）を持ついくつかのライダーは、カスタム注文の受け入れ制限を構成していないことがわかりますが、結合のままになっているため、クエリの結果に表示されます。ただし、注文の受け入れ制限の記録がないため、Order_grab_limitの結果はnullです。

テーブル名と列名の書き込みを振り返ってみましょう。 Idea 2のSQLが次のように変更された場合、リターン結果はどうなりますか？

 tb_grab_order_limit.rider_id、tb_grab_order_limit.order_grab_limitを選択します
tb_riderから左からtb_grab_order_limitに参加します
on tb_rider.id = tb_grab_order_limit.rider_id;

分析してみましょう。 We know that the return result set of LEFT JOIN is determined by the data table connected to the left, so the result set still contains 8 records, but the rider id=(7, 8, 5, 3) This rider does not have a corresponding upper limit configuration for order reception, so when we show these riders'
tb_grab_order_limit.rider_id列的值的时候，类似于
tb_grab_order_limit.order_grab_limit，也是null。因此结果是下面这样。

rider_idorder_grab_limit111294967\\\\\\\\

如果你还是不太明白，然我们在SELECT的时候，加上tb_rider.id，或许有助于理解。

 SELECT tb_rider.id, tb_grab_order_limit.rider_id, tb_grab_order_limit.order_grab_limit
FROM tb_rider LEFT JOIN tb_grab_order_limit
ON tb_rider.id = tb_grab_order_limit.rider_id;

消す。

idrider_idorder_grab_limit11112294496677\\8\\5\\3\\

[2] LEFT JOIN的姊妹篇：RIGHT JOIN

前面我们知道LEFT JOIN是以连接的左侧表作为查询的结果集的依据，RIGHT JOIN则是以连接的右侧表作为依据。让我们考虑另一个场景：假设想要查询所有设置了自定义接单上限的骑手姓名。应该如何写这个Sql呢？

先在聪明的大脑里思考几分钟。此时你需要类比LEFT JOIN，需要理解上一段内容讲述的LEFT JOIN知识点，可能需要回到上一段再看一看示例Sql语句以及对应的结果。没关系，一开始学习的时候慢慢来。

これが答えです。

 SELECT tb_grab_order_limit.rider_id, tb_rider.name
FROM tb_rider RIGHT JOIN tb_grab_order_limit
ON tb_rider.id = tb_grab_order_limit.rider_id;

对应的查询结果则是。

rider_idname1Stark2Banner4Thor6Barton10\

如果这个结果和你脑海中思考的结果不一样，不要着急，让我们再来解释一下。RIGHT JOIN是以连接的右侧表为依据，而tb_grab_order_limit中的骑手id=(1, 2, 4, 6, 10)，其中骑手id为10的骑手在tb_rider表中是没有的，所以name为null。

クイズ

尝试下将上面的这条Sql语句改写成LEFT JOIN吧(要求得到相同的查询结果)？

[3] 一丝不苟的INNER JOIN

之所以叫“一丝不苟”的INNER JOIN，是因为INNER JOIN是非常严格的关联查询，换句话说，必须是根据JOIN条件两张表中存在匹配记录的才作为结果集返回。让我们回顾下[1]中LEFT JOIN的Sql。

 SELECT tb_rider.id, tb_grab_order_limit.order_grab_limit
FROM tb_rider LEFT JOIN tb_grab_order_limit
ON tb_rider.id = tb_grab_order_limit.rider_id;

它的返回结果是。

idorder_grab_limit1112949677\8\5\3\

如果我们将LEFT JOIN改为INNER JOIN呐？修改后的Sql像这样。

 SELECT tb_rider.id, tb_grab_order_limit.order_grab_limit
FROM tb_rider INNER JOIN tb_grab_order_limit
ON tb_rider.id = tb_grab_order_limit.rider_id;

这时返回的查询结果变成了。

idorder_grab_limit111294967

这是因为INNER JOIN会遍历连接一侧的表，根据ON后的连接条件，和连接另一侧的表进行比较，只有两张表中存在匹配的记录才会作为结果集返回。例如这里，它会遍历tb_rider表中id字段的值，并且去tb_grab_order_limit表中寻找rider_id与之匹配的记录，如果找到则作为结果返回。

クイズ

猜测一下下面的这条Sql语句的返回结果是什么？

 SELECT tb_rider.id, tb_grab_order_limit.order_grab_limit
FROM tb_grab_order_limit INNER JOIN tb_rider
ON tb_grab_order_limit.rider_id = tb_rider.id;

提示：这里交换了一下INNER JOIN连接的两张表的位置，根据INNER JOIN的特性，查询结果会有影响嘛？

[4] 心大的FULL JOIN

FULL JOIN其实并不在乎匹配与否，而是将连接的两张表中所有的行都返回，如果有匹配的则返回匹配的结果，如果没有匹配则哪张表中缺失则对应的将当前这条记录标记为null。看一个例子就明白啦！

 SELECT tb_rider.id, tb_rider.name, tb_grab_order_limit.rider_id, tb_grab_order_limit.order_grab_limit
FROM tb_rider FULL JOIN tb_grab_order_limit ON tb_rider.id = tb_grab_order_limit.rider_id;

这条Sql语句的查询结果是这样的。

idnamerider_idorder_grab_limit1Stark1112Banner294Thor496Barton673Rogers\\5Natasha\\7Coulson\\8Coulson\\\\1010

可以看到tb_rider表中骑手id=(3, 5, 7, 8)的骑手在tb_grab_order_limit表中没有匹配的记录，而tb_grab_order_limit表中骑手id=(10)的骑手在tb_rider表中没有匹配记录，但是它们都作为结果集返回了。只不过缺失tb_grab_order_limit记录的， rider_id和order_grab_limit字段值为null，而缺失tb_rider记录的， id和name字段的值为null。

事实上，绝大多数情况下，FULL JOIN都不会被用到。而且在一些数据库管理系统中，例如MySql(我们的线上环境主要使用的就是MySql)，是不支持FULL JOIN语法的。对于上面的查询语句，需要使用一些技巧通过LEFT JOIN、RIGHT JOIN以及UNION(这篇教程中我们不讨论UNION语法哦)语法的组合来实现同样效果的查询。

 SELECT tb_rider.id, tb_rider.name, tb_grab_order_limit.rider_id, tb_grab_order_limit.order_grab_limit
FROM tb_rider LEFT JOIN tb_grab_order_limit ON tb_rider.id = tb_grab_order_limit.rider_id
連合
SELECT tb_rider.id, tb_rider.name, tb_grab_order_limit.rider_id, tb_grab_order_limit.rider_id
FROM tb_rider RIGHT JOIN tb_grab_order_limit ON tb_rider.id = tb_grab_order_limit.rider_id
WHERE tb_rider.id IS null;

这已经超出了这篇教程的讨论范围啦！如果想要挑战一下自己，以下是一些提示。

UNION连接两条SELECT语句，作用是将两个SELECT语句的查询结果取交集；
第2条SELECT语句中的WHERE tb_rider.id ISnull 是为了对存在匹配的数据记录去重(否则UNION之后会有重复的结果)；
WHERE语句是在RIGHT JOIN之后，UNION之前执行的；

试着在这两条提示下理解一下这条Sql语句，如果能够弄明白这条语句是如何等价于FULL JOIN的，那么说明你对JOIN家族的语法已经基本掌握啦。如果暂时还不能弄得非常明白也没关系，多看一看例子，多写一写实践一下，慢慢就会明白啦。

話題外

从上面的讲解我们了解到JOIN的四种用法，总结一下。

INNER JOIN关键字在两张表中都有匹配的值的时候返回匹配的行；
LEFT JOIN关键字从左表返回所有的行，即使在右表中没有匹配的行；
RIGHT JOIN关键字从右表返回所有的行，即使在左表中没有匹配的行；
FULL JOIN关键字从左表和右表那里返回所有行，即使右表的行在左表中没有匹配或者左表的行在右表中没有匹配，这些行也会返回；

不过这些都是刻板的文字总结，让我们换个视角总结一下这集中JOIN语法。

离散数学中在讨论集合论的时候介绍过“韦恩图”的概念，它清楚的描述了数据集合之间的关系。而JOIN的这4种操作也正好对应了4种集合运算，下面的这张图(Figure 1)很清楚的描述了这种关系。

4.4 嵌套的SELECT语法

再来看一下讲述LEFT JOIN的开始，我们提到的那个例子：查询tb_rider表中所有骑手对应的自定义接单上限。当时我们首先提出了思路1，是分为2个步骤的。

 SELECT id FROM tb_rider;

そして

SELECT rider_id, order_grab_limit FROM tb_grab_order_limit
WHERE rider_id IN (1, 2, 3, 4, 5, 6, 7, 8);

我们说这个思路不好，这是显然的，因为在现实场景中往往数据集合都很大(例如这里的rider_id在现实中可能是成百上千甚至成千上万个)，思路本身没有问题但无法操作执行。所以在4.3节我们选择通过JOIN语法来实现同样的查询。那是不是思路1就真的只能是个纸上谈兵的思路了呢？当然不是啦！我们还可以使用嵌套的SELECT语句，就像这样。

 SELECT rider_id, order_grab_limit FROM tb_grab_order_limit
WHERE rider_id IN (SELECT id FROM tb_rider);

这个写法非常好理解， WHERE rider_id IN(SELECT id FROM tb_rider)首先执行括号中的语句SELECT id FROM tb_rider，然后执行IN筛选，就是我们的思路1描述的那样。于是得到下面的结果。

rider_idorder_grab_limit111294967

复习题

回想一下上面的结果和以下哪条Sql语句的执行结果是一致的呢？为什么是一致的，为什么和其他的不一致？

 1. SELECT tb_rider.id, tb_grab_order_limit.order_grab_limit
 FROM tb_rider LEFT JOIN tb_grab_order_limit
 ON tb_rider.id = tb_grab_order_limit.rider_id;
2. SELECT tb_grab_order_limit.rider_id, tb_rider.name
 FROM tb_rider RIGHT JOIN tb_grab_order_limit
 ON tb_rider.id = tb_grab_order_limit.rider_id;
3. SELECT tb_rider.id, tb_grab_order_limit.order_grab_limit
 FROM tb_rider INNER JOIN tb_grab_order_limit
 ON tb_rider.id = tb_grab_order_limit.rider_id;
4. SELECT tb_rider.id, tb_grab_order_limit.order_grab_limit
 FROM tb_rider FULL JOIN tb_grab_order_limit
 ON tb_rider.id = tb_grab_order_limit.rider_id;

クイズ

思考一下以下这个场景，看看能否写出它对应的Sql语句？

场景：筛选出所有通过实名认证( real_name_certify_state=2)的金牌( level=3)骑手( tb_rider表)，在2017-12-30当天( created_at>=xxx AND created_at

想一想有几种写法呢？

5 闯关答题：快速复习

前面的几个段落我们学习了Sql查询中最常用，而且特别好用的语法知识，让我们简单总结一下。

数据库、数据表的概念；
最基本的Sql查询结构；
IN查询和逻辑操作语法(AND/OR)；
对查询结果进行排序和LIMIT语法；
聚合函数(COUNT/AVG/SUM)和DISTINCT语法；
对查询结果分组(GROUP BY)；
对聚合函数的结果进行筛选的HAVING语法；
字段类型和索引的概念和作用；
JOIN语法的一家子(LEFT JOIN/RIGHT JOIN/INNER JOIN/FULL JOIN)；
嵌套的SELECT语法；

学习了这么多知识点，实在是太腻害了！给自己点赞！

但是(凡事都有个但是)...

想要把这些知识点融会贯通，灵活应用到现实工作中更多变、更复杂的查询场景，仅仅是“学会”是不够的，还需要更多的“练习”和“回味”。

这个部分我设计了一个“闯关答题”项目，通过思考和回答这些闯关题，帮助你更好的掌握上面提到的知识点。

先来看一下答题将要用到的数据表。

[1] 商品数据表： tb_product

idproduct_idnameprice11001iPad Pro 10.5 64G WLAN488821002Macbook Pro 2017 13.3 i5/8G/256GB1388831003iPhone X 64G8388

建表语句：

 CREATE TABLE `tb_product` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
 `product_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商品id',
 `name` varchar(100) NOT NULL DEFAULT '' COMMENT '商品名称',
 `price` int(11) NOT NULL DEFAULT '0' COMMENT '商品价格',
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='商品信息表';

字段含义：

id：自增主键；
product_id：商品id；
name：商品名称；
price：商品单价，单位是元；

[2] 用户数据表： tb_customer

idcustomer_idnamegenderbalance1NO100001火火女188882NO100002拨泼抹女90003NO100003艾桥男79904NO100004水娃女8388

建表语句：

 CREATE TABLE `tb_customer` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
 `customer_id` varchar(100) NOT NULL DEFAULT '' COMMENT '用户id',
 `name` varchar(100) NOT NULL DEFAULT '' COMMENT '用户姓名',
 `gender` varchar(30) NOT NULL DEFAULT '' COMMENT '用户性别',
 `balance` int(11) NOT NULL DEFAULT '0' COMMENT '账户余额',
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_customer_id` (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='用户信息表';

字段含义：

id：自增主键；
customer_id：用户id；
name：用户姓名；
gender：用户的性别；
balance：用户当前的可用账户余额，单位是元；

[3] 订单数据表： tb_order

idorder_idcustomer_idproduct_idquantity1NUM1000301NO100001100112NUM1000302NO100001100223NUM1000303NO100002100224NUM1000304NO100003100215NUM1000305NO10000110031

建表语句：

 CREATE TABLE `tb_order` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
 `order_id` varchar(100) NOT NULL DEFAULT '' COMMENT '订单id',
 `customer_id` varchar(100) NOT NULL DEFAULT '0' COMMENT '用户id',
 `product_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商品id',
 `quantity` int(11) NOT NULL DEFAULT '0' COMMENT '商品价格',
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='订单数据表';

字段含义：

id：自增主键；
order_id：订单号；
customer_id：下单用户id；
product_id：购买的商品id；
quantity：购买的数量；

了解完需要用到表结构，我们就要开始答题啦！

第一关：查询账户余额大于1万元的用户id和姓名？

答え：

 SELECT customer_id, name FROM tb_customer WHERE balance > 10000;

customer_idnameNO100001火火

第二关：查询账户余额小于1万元且性别为女生的用户姓名？

答え：

 SELECT name FROM tb_customer WHERE balance < 10000 AND gender="女";

| name | |--------| | 拨泼抹| | 水娃|

第三关：查询用户id为NO100001和NO100002的用户，所有购买记录的订单号？

Hint：IN

答え：

 SELECT order_id FROM tb_order WHERE customer_id IN ("NO100001", "NO100002");

| order_id | |------------| | NUM1000301 | | NUM1000302 | | NUM1000303 | | NUM1000305 |

第四关：查询用户id为NO100001、NO100002两位用户所有的购买记录(所有字段)，要求按照优先以商品id递增、其次以订单号递减的规则展示数据？

Hint：IN、ORDER BY

答え：

 SELECT * FROM tb_order WHERE customer_id IN ("NO100001", "NO100002")
ORDER BY product_id ASC, order_id DESC;

idorder_idcustomer_idproduct_idquantity1NUM1000301NO100001100113NUM1000303NO100002100222NUM1000302NO100001100225NUM1000305NO10000110031

第五关：查询性别为女生的用户总数？

Hint：COUNT

答え：

 SELECT COUNT(customer_id) FROM tb_customer WHERE gender="女";

| COUNT(customer_id) | |---------------------| | 3 |

第六关：查询NO100001、NO100002、NO100003三位用户各自购买商品的总数(不区分商品类型)，输出购买商品件数大于等于2件的用户id以及他们对应购买的商品总数？

Warning：“购买商品的总数”和上一关“女生用户的总数”，这两个“总数”一样吗？

Hint：IN、SUM、HAVING

答え：

 SELECT customer_id, SUM(quantity) FROM tb_order
WHERE customer_id IN ("NO100001", "NO100002", "NO100003")
GROUP BY customer_id
HAVING SUM(quantity) >= 2;

customer_idSUM(quantity)NO1000014NO1000022

第七关：查询NO100001、NO100002、NO100003三位用户各自购买商品的总数(不区分商品类型)，输出购买总数前两名的用户id以及他们对应购买的商品总数？

Hint：IN、SUM、ORDER BY、LIMIT

答え：

 SELECT customer_id, SUM(quantity) FROM tb_order
WHERE customer_id IN ("NO100001", "NO100002", "NO100003")
GROUP BY customer_id
ORDER BY SUM(quantity) DESC
LIMIT 2;

customer_idSUM(quantity)NO1000014NO1000022

第八关：查询所有用户各自购买商品的总数(不区分商品类型)，输出购买商品件数大于等于2件的用户id以及他们对应购买的商品总数？要求给出至少两种写法。

Warning：注意是“所有用户”，不是所有的用户都购买了商品

Hint：关联查询有哪些方法？

答え：

写法一：嵌套的SELECT

 SELECT customer_id, SUM(quantity) FROM tb_order
WHERE customer_id IN (SELECT customer_id FROM tb_customer)
GROUP BY customer_id
HAVING SUM(quantity) >= 2;

customer_idSUM(quantity)NO1000014NO1000022

写法二：使用LEFT JOIN语法

SELECT tb_customer.customer_id, SUM(tb_order.quantity) FROM tb_customer
LEFT JOIN tb_order ON tb_customer.customer_id = tb_order.customer_id
GROUP BY tb_customer.customer_id
HAVING SUM(tb_order.quantity) >= 2;

customer_idSUM(tb_order.quantity)NO1000014NO1000022

第九关：查询所有用户各自购买商品的总数(不区分商品类型)，输出购买总数前两名的用户id以及他们对应购买的商品总数？要求给出至少两种写法。

Hint：关联查询有哪些方法？

答え：

写法一：嵌套的SELECT

 SELECT customer_id, SUM(quantity) FROM tb_order
WHERE customer_id IN (SELECT customer_id FROM tb_customer)
GROUP BY customer_id
ORDER BY SUM(quantity) DESC
LIMIT 2;

customer_idSUM(quantity)NO1000014NO1000022

写法二：使用LEFT JOIN语法

SELECT tb_customer.customer_id, SUM(tb_order.quantity) FROM tb_customer
LEFT JOIN tb_order ON tb_customer.customer_id = tb_order.customer_id
GROUP BY tb_customer.customer_id
ORDER BY SUM(tb_order.quantity) DESC
LIMIT 2;

customer_idSUM(tb_order.quantity)NO1000014NO1000022

第十关：以下哪几条Sql语句使用到了索引？分别是哪些字段上的索引？是什么类型的索引？

 1. SELECT name FROM tb_customer WHERE customer_id = 1001;
2. SELECT product_id, name FROM tb_product WHERE price > 5000;
3. SELECT order_id, customer_id, product_id FROM tb_order
 WHERE order_id = "NUM1000302" AND customer_id = "NO100001"
 AND product_id = "1002";
4. SELECT order_id FROM tb_order WHERE id > 2;

Hint：索引

答え：

sql序号是否使用到索引索引所在字段索引类型1是customer_idUNIQUE KEY2否--3是order_idUNIQUE KEY4是idPRIMARY KEY

阅读博客还不过瘾？

<<: Kocユーザー運営（超本格的エキスパート運営戦略：KOCの心を掴むには？）

>>: Yifang Data Operation（越境電子商取引企業はYifang Data CDPの支援を受けてどのようにビジネス成長を達成できるか？）