データベースとSQL言語を分析します。(パート 5 - 接続と結合) - 「Java プロジェクトの A から Z まで」

Java プロジェクトの作成に関するシリーズの記事 (他の資料へのリンクは最後にあります)。その目標は主要なテクノロジーを分析することであり、その結果として電報ボットを作成することができます。

• データベース内の関係の種類
  • 1対多
  • 一対一（一対一）
  • 多対多
• 接続 (結合)
  • 内部結合
  • 左結合
  • 右結合
  • 結合の保護
• 宿題

ソフトウェアの将来のシニアおよびシニアの皆さん、こんにちは。前のパート (宿題の確認) で述べたように、今日は新しい資料があります。特に熱心な人のために、すでにすべてを知っている人も、知らないけどグーグルで検索したい人も練習してスキルをテストできるように、興味深い宿題を用意しました。今日は接続と結合の種類について説明します。

データベース内の関係の種類

リレーションシップとは何かを理解するには、外部キーが何であるかを覚えておく必要があります。忘れてしまった方は、シリーズの始まりへようこそ。

1対多

国と都市の例を思い出してみましょう。都市には国がなければならないことは明らかです。国と都市を結び付けるにはどうすればよいでしょうか? 各都市に、その都市が属する国の固有の識別子 (ID) を付加する必要があります。これはすでに完了しています。これは、接続のタイプの 1 つである1 対多と呼ばれます(英語版の 1 対多についても知っておくと良いでしょう)。言い換えれば、「複数の都市が 1 つの国に属することができる」と言えます。これが、1 対多の関係であることを覚えておく必要があります。ここまでは明らかですよね？そうでない場合は、インターネットからの最初の写真を次に示します。顧客とその注文が存在することがわかります。1 人の顧客が複数の注文を行うことができるのは当然です。1 対多があります:) または別の例:出版社、著者、書籍の 3 つのテーブルがあります。破産したくない、成功したいと考えている出版社は必ず複数の著者を抱えています。そう思いませんか? さらに、各著者が複数の本を持っている可能性もありますが、それについても疑いの余地はありません。そしてこれは、繰り返しになりますが、一人の著者と多くの本、一人の出版社と多くの著者のつながりを意味します。さらに多くの例を挙げることができます。最初の認識の難しさは、抽象的に考えること、つまりテーブルとその相互作用を外側から見ることを学ぶことだけにあるかもしれません。

一対一（一対一）

これは、1 対多の通信の特殊なケースと言えます。あるテーブルの 1 つのレコードが、別のテーブルの 1 つのレコードのみに関連付けられている状況。人生にはどのような例があるでしょうか? 一夫多妻制を除けば、夫と妻の間には1対1の関係があると言えます。一夫多妻制が認められていると言っても、各妻が持つことができる夫は一人だけです。親についても同じことが言えます。各人は、生物学上の父親と生物学上の母親を 1 人だけ持つことができます。明示的な 1 対 1 の関係。これを書いているときに、ある考えが浮かびました。すでに 1 対 1 の関係があるのに、なぜ 1 対 1 の関係を異なるテーブルの 2 つのレコードに分割するのでしょうか。私自身が答えを見つけました。これらのレコードは、他の方法で他のレコードにリンクされる場合もあります。私は何を話しているのでしょうか？1 対 1 のつながりのもう 1 つの例は、国と大統領の間です。大統領に関するすべてのデータを「国」テーブルに書き込むことは可能ですか? はい、できます。SQL は何も言いません。しかし、大統領も人間であると考えると...そして彼には妻 (別の 1 対 1 の関係) や子供たち (別の 1 対多の関係) もいる可能性があります。国と大統領の妻や子供たちを結びつけるために必要だ…。クレイジーだと思いますよね？:D この接続には他にも多くの例があります。さらに、このような状況では、1 対多のリレーションシップとは異なり、両方のテーブルに外部キーを追加できます。

多対多

すでに名前に基づいて、私たちが話す内容を推測することができます。人生において、そして私たちが自分の人生をプログラムする中で、上記のタイプのつながりだけでは必要なものを説明するのに十分ではない状況がよくあります。出版社、本、著者についてはすでに話しました。ここには非常に多くの接続があります...各出版物には複数の著者が存在する可能性があり、1 対多の接続になります。同時に、各著者は複数の出版社を持っている可能性があります（たとえば、作家が 1 か所で出版され、お金のことで喧嘩し、別の出版社に行った場合など）。これもまた 1 対多の関係です。または、各著者が複数の本を持つことができますが、各本に複数の著者が所属することもできます。繰り返しますが、著者と本、本と著者の間には 1 対多の関係があります。この例から、より形式的な結論を引き出すことができます。 SQL で以前の接続タイプを設定する方法は明らかでした。多数あるレコードにその ID を渡すだけですよね。ある国は、その ID を外部キーとして多くの都市に与えます。多対多の関係をどうするか? この方法は適切ではありません。2 つのテーブルを接続する別のテーブルを追加する必要があります。たとえば、MySQL に移動し、新しいデータベース manytomany を作成し、名前とその ID のみを含む 2 つのテーブル author と book を作成します 。多くのことを使用します。CREATE TABLE author( id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE book( id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (id) ); 次に、author テーブルと book テーブルからの 2 つの外部キーを持つ 3 番目のテーブルを作成しましょう。このリンクは一意になります。つまり、同じキーを持つレコードを 2 回追加することはできません。 CREATE TABLE authors_x_books ( book_id INT NOT NULL, author_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (author_id) REFERENCES author (id ), UNIQUE (book_id, author_id) ); ここでは、個別にコメントする必要があるいくつかの新機能を使用しました。

• NOT NULL は、フィールドが常に入力される必要があることを意味します。入力しない場合は、SQL がそのように指示します。
• UNIQUE は、1 つまたは複数のフィールドがテーブル内で一意である必要があることを示します。一意の識別子に加えて、レコードごとにさらに 1 つのフィールドが一意である必要があることがよくあります。そして、UNIQUEはまさにこの問題を担当しています。

私の実践から言えば、古いシステムから新しいシステムに移行するとき、開発者として、古いシステムを操作できるように古いシステムの ID を保存し、独自の ID を作成する必要があります。なぜ古いものを使用せずに独自のものを作成するのでしょうか? ID は十分に一意ではない可能性があります。あるいは、ID を作成するこのアプローチはもはや関連性がなく、制限されている可能性があります。この目的のために、古い ID 名もテーブル内で一意にしました。これを確認するには、データを追加する必要があります。本と著者を追加します: NSERT INTO book (name) VALUES ("book1"); INSERT INTO 著者 (名前) VALUES ("author1"); 以前の記事から、ID 1 と 1 を持つことはすでにわかっています。したがって、すぐに 3 番目のテーブルにレコードを追加できます。 INSERT INTO authors_x_books VALUES (1,1); そして、最後のコマンドをもう一度繰り返すまではすべて問題ありません。つまり、同じ ID をもう一度書き留めます。結果は当然のことですが、エラーになります。重複があります。エントリは記録されません。これは、多対多の接続がどのように作成されるかです...これはすべて非常にクールで興味深いものですが、論理的な疑問が生じます。この情報をどのように取得するかということです。異なるテーブルのデータを組み合わせて 1 つの答えを得るにはどうすればよいでしょうか? これについては次のパートで説明します))

接続 (結合)

前のパートでは、結合とは何か、そしてそれをどこで使用するかをすぐに理解できるように準備しました。なぜなら、理解すればすぐに、すべてがすぐに非常に単純になり、結合に関するすべての記事が赤ちゃんの目にも明らかになるだろうと私は深く確信しているからです :D 大まかに、そして一般的に、結合はいくつかのテーブルから次の方法で結果を取得します。 JOIN (英語の join からの結合) の。以上です...) そして結合するには、テーブルを結合するフィールドを指定する必要があります。悪魔は描かれているほど怖くないですよね?) 次に、結合の種類とその使用方法について説明します。結合には多くの種類があるため、すべてを考慮するわけではありません。本当に必要なものだけを。だからこそ、Cross や Natural のような珍しい結合には興味がありません。すっかり忘れていましたが、もう 1 つ覚えておく必要があります。テーブルとフィールドには別名、つまり仮名を付けることができます。これらは結合に便利に使用されます。たとえば、次のように実行できます。 SELECT * FROM table1; クエリで table1 を頻繁に使用する場合は、次のようにエイリアスを付けることができます 。 SELECT* FROM table1 as t1; あるいは、次のように書くのも簡単です。 SELECT * FROM table1 t1; その後、クエリの後の方で、このテーブルのエイリアスとしてt1 を使用できるようになります。

内部結合

最も一般的で単純な結合。これは、2 つのテーブルとそれを結合できるフィールドがある場合、2 つのテーブルにリレーションシップが存在するすべてのレコードが選択されることを示しています。なんだか言いにくかったです。例を見てみましょう。都市データベースに 1 つのレコードを追加してみましょう。都市に 1 つのエントリ、国に 1 つのエントリ: $ INSERT INTO country VALUES(5, "Uzbekistan", 34036800); そして $ INSERT INTO city (名前, 人口) VALUES("トビリシ", 1171100); テーブルに都市のない国と、テーブルの国に関連付けられていない都市を追加しました。したがって、INNER JOIN は、2 つのテーブルにある接続のすべてのレコードを発行します。2 つのテーブル table1 と table2 を結合する場合の一般的な構文は次のようになります。 SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; 2 つのテーブル内で関係があるすべてのレコードが返されます。私たちの場合、都市とともに国の情報を受け取りたい場合は、次のようになります。 $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id; ここでは、名前は同じですが、最初に都市の分野があり、次に国の分野が来ていることがよくわかります。しかし、上で追加した 2 つのエントリはそこにはありません。それがまさに INNER JOIN の仕組みだからです。

左結合

隣接するテーブルにメイン テーブルのレコードがないためにメイン テーブルのフィールドが失われることに満足できない場合がよくあります。これが LEFT JOIN の目的です。前のリクエストで INNER の代わりに LEFT を指定した場合、レスポンスに別の都市を追加します - トビリシ: $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; トビリシに関する新しいエントリがあり、その国に関連するものはすべてnullです。このような使い方がよくあります。

右結合

ここでは、すべてのフィールドが接続の左側ではなく右側で選択されるという点で LEFT JOIN とは異なります。つまり、都市ではなく、すべての国が取得されます。 $ SELECT * FROM city ci RIGHT JOIN country co ON ci.country_id = co.id; この場合、トビリシは存在しないが、ウズベキスタンは存在することは明らかである。そんな感じ…））

結合の保護

ここで、後輩が結合の本質を理解していることを納得させるために面接前に詰め込む典型的な図を示したいと思います。ここではすべてがセットの形式で示されており、各円はテーブルです。そして塗りつぶされているところがSELECTで表示される部分になります。見てみよう：

• INNER JOIN はセット、つまり 2 つのテーブル (A と B) に接続されているレコードの共通部分にすぎません。
• LEFT JOIN は、テーブル A のすべてのレコードです。これには、A と交差 (接続) があるテーブル B のすべてのレコードが含まれます。
• RIGHT JOIN は LEFT JOIN の逆です。テーブル B 内のすべてのレコードと、リレーションシップのあるテーブル A のレコードです。

すべての後、この写真は明確になるはずです））

宿題

今回のタスクは非常に興味深いものになるため、問題を解決できた人は全員、安心して SQL 側で作業を始める準備ができています。タスクは細かくまとめられておらず、中学生向けに書かれているので、あなたにとって簡単で退屈なものではありません :) タスクを自分で行うのに 1 週​​間与えます。その後、詳細な分析を含む別の記事を公開します私があなたに与えたタスクの解決策を説明します。

実際のタスク:

1. SQL スクリプトを作成して、次のフィールドを持つ「Student」テーブルを作成します: id (主キー)、name、last_name、e_mail (一意)。
2. SQL スクリプトを作成して、次のフィールドを持つ「Book」テーブルを作成します: ID、タイトル (ID + タイトル = 主キー)。「Student」と「Book」を、「Student」と 1 対多の「Book」の関係でリンクします。
3. SQL スクリプトを作成して、次のフィールドを持つ「Teacher」テーブルを作成します: id (主キー)、name、last_name、e_mail (一意)、subject。
4. 「生徒」と「教師」を「生徒」対多の「教師」関係でリンクします。
5. 「Sid oro v」、「V oro novsky」など、姓に「oro」が含まれる「Student」を選択します。
6. 「Student」テーブルからすべての姓 (「last_name」) とその繰り返しの数を選択します。データベース内に同名の名前が存在することを考慮してください。数量で降順に並べ替えます。次のようになります。

   苗字	量
   ペトロフ	15
   イワノフ	12
   シドロフ	3

7. 「学生」から最も頻繁に使用される名前の上位 3 つを選択します。数量で降順に並べ替えます。次のようになります。

   名前	量
   アレクサンダー	27
   セルゲイ	10
   ピーター	7

8. 「本」とそれに関連付けられた「教師」の数が最も多い「生徒」を選択し、数量の降順に並べ替えます。次のようになります。

   教師の姓	学生の姓	本の数量
   ペトロフ	シドロフ	7
   イワノフ	スミス	5
   ペトロフ	カンカヴァ	2>

9. すべての「生徒」の中から「本」の数が最も多い「先生」を選択します。数量で降順に並べ替えます。次のようになります。

   教師の姓	本の数量
   ペトロフ	9
   イワノフ	5

10. すべての「生徒」の「ブック」の数が 7 ～ 11 である「教師」を選択します。数量で降順に並べ替えます。次のようになります。

    教師の姓	本の数量
    ペトロフ	十一
    シドロフ	9
    イワノフ	7

11. すべての「教師」と「生徒」のすべての「姓」と「名前」をフィールド「タイプ」(生徒または教師) とともに出力します。「last_name」でアルファベット順に並べ替えます。次のようになります。

    苗字	タイプ
    イワノフ	学生
    カンカヴァ	教師
    スミス	学生
    シドロフ	教師
    ペトロフ	教師

12. 既存の「Student」テーブルに「rate」列を追加します。これには、学生が現在参加しているコース (1 ～ 6 の数値) が保存されます。
13. このアイテムは必須ではありませんが、あるとプラスになります。すべての「書籍」を調べて、すべての「タイトル」をカンマで区切って出力する関数を作成します。

結論

データベースに関するシリーズが少し長くなってしまいました。同意する。しかし、私たちは長い道のりを歩んできたので、その結果、この問題についての知識を得ることができました。読んでいただきありがとうございます。先に進んでプロジェクトをフォローしたい人は、GitHubでアカウントを作成し、私のアカウントにサブスクライブする必要があることを思い出してください:) さらに詳しく - Maven と Docker について話しましょう。読んでくれた皆さん、ありがとう。もう一度繰り返します:歩く者が道をマスターします ;)

シリーズのすべてのマテリアルのリストは、この記事の冒頭にあります。