データベースとSQL言語を分析します。(パート 6 - 最終課題の確認) - 「Java プロジェクトの A から Z まで」

Java プロジェクトの作成に関するシリーズの記事 (他の資料へのリンクは最後にあります)。その目標は主要なテクノロジーを分析することであり、その結果として電報ボットを作成することができます。この部分には、データベース上の最終タスクの分析が含まれます。親愛なる読者の皆さん、こんにちは。今日はデータベース上の前回の記事のタスクを分析します。中級レベルの面接を対象としているので興味深いです。つまり、このタスクの後、すでに面接に行くことができ、リレーショナル データベースに関連する内容の少なくとも一部に合格することができます。私はこの記事がどれほど必要であるかを知っているので、有益で興味深いものにするために私の経験のすべてを注ぎます。記事の途中で眠らなければ、それは私の目標を達成したことを意味します。タスク全体を繰り返すつもりはありません。各タスクを完了する前に引用し、斜体の下線を引きます。この記事を読んだ人は全員、データベース内のすべてのクエリを実行して同じ結果が得られると思います。これはビジネスに最大の利益をもたらします。そして、私たちの難しい仕事で誰かを助けたと思うと、少し幸せになります）

演習 1

SQL スクリプトを作成して、次のフィールドを持つ「Student」テーブルを作成します: id (主キー)、name、last_name、e_mail (一意)。 これはすでに実行済みなので、問題はないはずです。スクリプトでは、主キーと、主キーとは異なる一意のフィールドを指定する必要があります。まず、このタスク用に新しいデータベースを作成しましょう。 $ CREATE DATABASE Final_task; そして、このデータベースを使用しましょう。 $ USE Final_task; 環境がセットアップされ、ジョブを実行する準備ができたら、次のスクリプトを作成できます。 $ CREATE TABLE Student ( id INT AUTO_INCREMENT, name VARCHAR(40), last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY( id)、UNIQUE (電子メール) ); これまでのところ、私たちがすでに経験してきたことに比べれば、新しいことは何もありません。コメントは不要ですので、次に進みましょう。

タスク2-3

SQL スクリプトを作成して、次のフィールドを持つ「Book」テーブルを作成します: ID、タイトル (ID + タイトル = 主キー)。「Student」と「Book」を、「Student」と 1 対多の「Book」の関係でリンクします。 2 つのタスクを 1 つに結合して、より速く便利にしましょう。別の外部キーを追加する方法については、以前の記事ですでに説明しました。さらに言えば、私たちは何を介してどのようにつながりを築くのかを思い出す必要があります。前の記事が役に立ちます。スクリプトは次のとおりです。 $ CREATE TABLE book ( id INT, title VARCHAR(100), Student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) REFERENCES Student (id) ) ); この簡単な方法で、テーブルPRIMARY KEY (id, title)に複合キーを追加しました。これで、キーは正確にペアになります。これは、テーブル内に同一の ID フィールド値が複数存在する可能性があることを意味します。タイトルも全く同じです。

タスク 4

SQL スクリプトを作成して、次のフィールドを持つ「Teacher」テーブルを作成します: id (主キー)、name、last_name、e_mail (一意)、subject。 引き続きクエリ用にデータベースを準備し、Teacher テーブルを作成します。 $ CREATE TABLE Teacher( id INT AUTO_INCREMENT, name VARCHAR(30), last_name VARCHAR(30), email VARCHAR(100), subject VARCHAR(40), PRIMARY KEY ( id)、UNIQUE (電子メール) ); ここまでは難しくないですよね？3つのタスクはすでに終了しています！

タスク5

「生徒」と「教師」を「生徒」対多の「教師」関係でリンクします。 これでさらに面白くなりました！これについては前回お話ししたばかりです。これを達成するには何が必要かを思い出させてください。生徒と教師のペアを格納する中間テーブルを作成する必要があります。その助けを借りて、多対多の関係を作成することが可能になります。したがって、テーブルstudents_x_techersを作成しましょう。命名方法はオープンであり、Student_Teacher のようにすることもできます。 $ CREATE TABLE students_x_Teachers ( Student_id INT NOT NULL, Teacher_id INT NOT NULL, PRIMARY KEY (student_id, Teacher_id), FOREIGN KEY (student_id) REFERENCES Student(id), FOREIGN KEY (Teacher_id) REFERENCES Teacher(id) ); ご覧のとおり、すべてが明確かつ一貫して行われています。2 つの外部キー、student_id と Teacher_id の複合キーがあります。なぜ外部キーも必要なのでしょうか? 記録されているペアについて、学生テーブルと教師テーブルにレコードが存在することを確認するためです。

タスク6

姓に「oro」が含まれる「学生」を選択します (例: 「Sidorov」、「Voronovsky」)。 興味深く視覚的にするために、最初に数人の生徒を追加して、一部の生徒がこのリクエストに適しており、一部の生徒は適さないようにすることをお勧めします。したがって、リクエストの結果として含める べき人を書き留めてみましょう。 $ INSERT INTO Student (name, last_name, email) VALUES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO 学生 (名前、姓、電子メール) VALUES ('ニコライ', 'ボロノフスキー', 'nikolay.voronovsky@gmail.com'); $ INSERT INTO Student (name, last_name, email) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com') ; $ INSERT INTO 学生 (名前, 姓, メールアドレス) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); 結果を確認して、students テーブルのデータのリストを見てみましょう 。 合計4 つのレコードがあり、そのうち 2 つは適合するはずですが、2 つは適合しないはずです。リクエスト用のすべてのデータを準備したら、タスク自体のリクエストを作成できます。 $ SELECT * FROM Student WHERE last_name LIKE '%oro%'; その結果、イワンとニコライがリストに載った。

タスク 7

次のタスクは次のとおりです。 「Student」テーブルからすべての姓 (「last_name」) とその繰り返しの数を選択します。データベース内に同名の名前が存在することを考慮してください。数量で降順に並べ替えます。次のようになります。

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

わかりやすくするために、さらにデータを追加する必要があります。さっそく、血縁関係を知らないペトロフ家、イワノフ家、シドロフ家を追加してみましょう ;) 私は電子メール アドレスを発明するつもりはありません。新しいエントリから除外するだけです。次のコマンドを 12 回実行してみましょう。 $ INSERT INTO Student (name, last_name) VALUES ('Ivan', 'Ivanov'); 15 個のペトロフを追加しましょう。 $ INSERT INTO Student (name, last_name) VALUES ('Petr', 'Petrov'); そして 2 つの Sidorov (すでに 1 つあります))): $ INSERT INTO Student (name, last_name) VALUES ('Sidor', 'Sidorov'); これでデータの準備が完了しました。このようなデータを取得するには、グループ化を行う必要があります。グループ化を行うには、Group By 演算子を使用する必要があり、これを last_name フィールドによって行う必要があります。また、繰り返しの数が数量として指定されていることにも注意してください。ここでは、SQL でエイリアスを作成する方法も覚えておく必要があります。 $ SELECT last_name, COUNT(*) as amount FROM Student GROUP BY last_name ORDER BY COUNT(*) DESC ; それで私はペトロフ家でそれをやりすぎました - それは16であることが判明しました））

タスク8

条件： 「学生」から最も多く使われた名前の上位 3 つを選択します。数量で降順に並べ替えます。次のようになります。

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

ああ、この目的のために、私たちはすでにイヴァンス、ピータース、シドルスを持っています。したがって、それらを追加する必要はありません。私たちはすでに並べ替えの方法を知っています。今日話さなかった唯一のことは、特定の数のレコードを選択する方法です。これは、データベースの問題に対する以前の解決策ですでに現れています。読んでない人は読んでみてください。残りについては、本題に進みます。 $ SELECT name, COUNT(*) as amount FROM Student GROUP BY name ORDER BY COUNT(*) DESC LIMIT 3; クエリからわかるように、SELECT クエリ内の演算子の順序がわかっていれば、そのようなクエリを実行する際に問題はありません。そして、この任務はまだ私たちにかかっています。そして、以前に提示した知識は、この問題を解決するのに十分です。

タスク9

タスク条件: 「書籍」とそれに関連付けられた「教師」の数が最も多い「生徒」を選択します。数量で降順に並べ替えます。次のようになります。

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

ということは、このタスクは前のタスクよりも明らかに難しいですよね? 不思議ではありません。結合のような匂いがします...そして複数です) まず、何をすべきかを理解する必要があります... Book の量にはグループ化が必要であることは明らかです。でも何？なぜそれらをグループ化する必要があるのでしょうか? クエリには 3 つのテーブル、グループ化、並べ替えが含まれます。ブックが存在しないレコードが表示されないことから判断すると、INNER JOIN を取る必要があることがわかります。LEFT JOIN も問題ないようにリクエストします。そして、いくつかのオプションがあります。最初に行うことは、3 つのテーブルを 1 つのレコードに結合することです。次に、生徒ごとにグループ化し、そこに教師の名前を追加します。何を選ぶでしょうか？先生、生徒の名前、本の数。リクエストのデータを追加しましょう。

• 3人の教師。
• 10冊の本。
• 2 人の生徒と 3 人の教師を結びつけます。

３人の先生

$ INSERT INTO 先生(姓) VALUES ('マトヴィエンコ'); $ INSERT INTO 先生(姓) VALUES ('シェフチェンコ'); $ INSERT INTO 先生(姓) VALUES ('ヴァシレンコ');

10冊

1年生と2年生のIDを取ります。それらに本を添付します。AUTO_INCREMENT が設定されていないため、毎回新しい ID を書き込まないようにするには、次の操作を行う必要があります。 $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; 次に、最初の生徒の書籍を追加します。 $ INSERT INTO book (title,student_id) VALUES('book1', 1); $ INSERT INTO 本 (タイトル, 学生 ID) VALUES('book2', 1); $ INSERT INTO 本 (タイトル, 学生 ID) VALUES('book3', 1); $ INSERT INTO 本 (タイトル, 学生 ID) VALUES('book4', 1); $ INSERT INTO 本 (タイトル, 学生 ID) VALUES('book5', 1); $ INSERT INTO 本 (タイトル, 学生 ID) VALUES('book6', 1); そして 2 人目の生徒の本: $ INSERT INTO book (title,student_id) VALUES('book7', 2); $ INSERT INTO 本 (タイトル, 学生 ID) VALUES('book8', 2); $ INSERT INTO 本 (タイトル, 学生 ID) VALUES('book9', 2); $ INSERT INTO 本 (タイトル, 学生 ID) VALUES('book10', 2);

教師と生徒のつながり

これを行うには、students_x_Teachers をテーブルに追加します。 $ INSERT INTO students_x_Teachers VALUES (1,1); $INSERT INTO students_x_Teachers VALUES(1,2); $INSERT INTO students_x_Teachers VALUES(2,3);

リクエストを実装してみましょう

最初の段階を行います - 3 つのテーブルを 1 つのレコードにリンクします。 $ SELECT * FROM Teacher tch INNER JOIN students_x_Teachers st_x_tch ON tch.id = st_x_tch.Teacher_id INNER JOIN Student st ON st_x_tch.student_id = st.id INNER JOIN book b ON st .id = b.student_id; もちろん、まだレコードはありませんが、3 つのテーブルが正常に接続されていることがわかります。次に、書籍のグループ化、並べ替え、および必要なフィールドを追加します。 $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) as Books FROM Student st INNER JOIN book b ON st.id = b.student_id INNER JOIN students_x_Teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN Teacher tch ON tch.id = st_x_tch.Teacher_id GROUP BY st.id ORDER BY Books DESC; しかし、SQL でエラーが発生し、次のような答えが返されます: エラー コード: 1055。SELECT リストの式 #1 は GROUP BY 句になく、GROUP BY の列に機能的に依存していない非集約列 'final_task.tch.last_name' を含んでいます。 教師と生徒の間には多対多の関係があるため、これらの要素を使用することは機能しません。それは本当です。生徒 1 人に対して 1 人の教師だけを配置することはできません。それでは、別の道に行きましょう。View SQL と呼ばれるものを使用してみましょう。このアイデアは、必要なグループ化がすでに行われた新しいテーブルである別のビューを作成します。そして、この表に必要な教師の名前を追加します。ただし、複数の教師が存在する可能性があるという事実を考慮し、エントリは繰り返し行われます。ビューを作成します。 $ CREATE VIEW StudentBooks as SELECT st.last_name,st.id,COUNT(*) as Books FROM Student st INNER JOIN book b ON st.id=b.student_id GROUP BY st.id ORDER BY Books DESC; 次に、このビューを、student last_name、student_id、books count の 3 つのフィールドを持つ単純なテーブルとして操作します。生徒の ID に基づいて、2 つの結合を通じて教師を追加することもできます: $ SELECT tch.last_name as 'Teacher'、sbw.last_name 'Student'、sbw.books as 'Books' from Studentbook sbw INNER JOIN students_x_Teachers stch ON sbw。 id = stch.student_id 内部結合教師 tch ON tch.id = stch.Teacher_id; そして結果は次のようになります。これはリクエストですよね?) 予想どおり、id=1 の学生は 6 冊の本と 2 人の教師を持ち、id=2 の学生は 4 冊の本と 1 人の教師を持っています。

タスク 10

条件: すべての「生徒」の中で最も多くの「本」を持っている「教師」を選択します。数量で降順に並べ替えます。次のようになります。

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

ここでは、前のタスクで作成した既製のリクエストを使用できます。それについて何を変える必要があるでしょうか? このデータはすでにあるので、別のグループを追加し、出力データから生徒の名前を削除するだけです。まず、結果をより興味深いものにするために、教師にもう 1 人の生徒を追加しましょう。これを行うには、次のように記述します。 $ INSERT INTO students_x_Teachers VALUES (2, 1); そしてクエリ自体: $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from Studentbook sbw INNER JOIN students_x_Teachers stch ON sbw.id = stch.student_id INNER JOIN Teacher tch ON tch.id = stch .Teacher_id GROUP BY tch.id; その結果、次のことがわかります。ワシレンコ先生は 10 冊の本を持っており、シェフチェンコ先生は 6 冊の本を持っています...)

タスク 11

条件: すべての「生徒」の「本」の数が 7 から 11 の間である「教師」を選択します。数量で降順に並べ替えます。次のようになります。

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

ここで HAVING を使用します。私たちは彼のことについて話しました。リクエストは以前とまったく同じですが、本の数が一定の範囲内であるという条件を追加する必要があるだけです。そして、前の記事で述べたように、グループ化中や集計関数でフィルタリングを行う必要がある場合は、 HAVING : $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from を使用する必要があります。Studentbook sbw INNER JOIN students_x_Teachers stch ON sbw.id = stch.student_id INNER JOIN Teacher tch ON tch.id = stch.Teacher_id GROUP BY tch.id HAVING SUM(sbw.books) > 6 AND SUM(sbw.books) < 12; 追加した部分を強調表示させていただきました。そして実際、予想される結果:このラウンドを通過したのはヴァシレンコだけでした))

タスク 12

条件: フィールド「type」(学生または教師) を持つすべての「教師」と「学生」のすべての「姓」と「名前」を出力します。「last_name」でアルファベット順に並べ替えます。次のようになります。

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

つまり、2 つの出力を結合する必要があり、これがまさに UNION の目的です。つまり、生徒と教師からレコードを取得して、一緒に出力します。 $ SELECT last_name, 'Teacher' as type from Teacher UNION ALL select last_name, 'student' as type from Student ORDER BY last_name; そして、教師と生徒の両方が存在します。すべてが単純であるように見えますが、これはすでに結果を確認しているときです。したがって、2 つのことを推測する必要があります。

タスク 13

条件: 既存の「Student」テーブルに「rate」列を追加します。これには、学生が現在参加しているコース (1 ～ 6 の数値) が保存されます。 ALTER TABLE Student ADD CONSTRAINT check_rate CHECK (rate > 0 AND rate < 7); ここでは、ALTER TABLE と CHECK を使用してフィールドを追加し、このフィールドの制限を 1 ～ 6 に設定しています。

タスク 14

状態： このアイテムは必須ではありませんが、プラスになります。すべての「書籍」を調べて、すべての「タイトル」をカンマで区切って出力する関数を作成します。 ここでは、リクエストの結果として書籍のすべてのタイトルを含む行を返すだけです。ここでもグーグルで検索する必要がありました。そのような関数 - GROUP_CONCATがあり、これを使用すると非常に簡単に実行できます。 $ SELECT GROUP_CONCAT(title) from book; 以上です...)) 14 個のタスクすべての準備が整いました。

結論

うーん...簡単ではありませんでした。興味深かった。この任務にはそれだけの価値があったと私は確信しています。これらのタスクを実行している間、これまで知られていなかった多くのことを検討しました。

• SQLビュー
• グループコンキャット
• 連合

等々。私がやったことを読んで再現する力を持った皆さんに感謝します。リクエストをより良くする方法を知っている人はいません - コメントに書いてください。必ず読みます)

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