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

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

• 宿題の確認
  • 主キーの追加
  • モルドバを追加
  • 都市テーブルを作成する
  • 都市に外部キーを追加する
  • ボーナス部分。テスト
• SELECT ステートメント
  • WHEREパラメータ
  • ORDER BY パラメータ
  • GROUP BY パラメータ
• 宿題
• 結論

こんにちは、皆さん、データベース、SQL、その他について話を続けましょう。今日の教材には、理論部分と実践部分が含まれます。前回、すべてをセットアップする方法、データベースとテーブルを作成してそこからデータを取得する方法について説明したことを思い出してください。リモート センシングで何かがうまくいくかどうかを確認するときが来ました。私の意見では、半分は前の記事に基づいてのみ実行できたはずです。アプリケーションを適切に組み立てて、多かれ少なかれすべてを美しくするには、データベースについて話す必要があり、データベースについて話すには、多くの時間を費やす必要があることがわかりました。

宿題の確認

タスクを無事に完了した全員に多大な敬意を表します。これは、これが必要なのは自分だけであり、役立つだけであることを理解していることを意味します。私の仕事を怠ってきた人のために、次の条件を思い出させてください。

1. ID フィールドの主キー (PRIMARY KEY) を国テーブル スキーマに追加する必要があります。
2. 国テーブルに別の国 (モルドバ) を追加します。
3. 前の記事のスキームに従って、説明されているすべてのフィールドを含むテーブル city を作成します。フィールド名は次のようになります: id、name、country_id、population。
4. city テーブルに主キーを追加します。
5. city テーブルに外部キーを追加します。

まず、前の記事の最初の部分を使用して、データベース ターミナルに移動しましょう。

主キーの追加

主キー (PRIMARY KEY) は 2 つの方法で追加できます。テーブルの作成時にすぐに追加する方法と、作成後に ALTER TABLE を使用して追加する方法です。

テーブル作成時の主キー

すでにテーブルを作成しており、それを削除しないとこのデータベース内でこのアプローチを示すことができないため、すべてを実行する一時的なテスト データベースを作成するだけです。次のコマンドを入力してみましょう。

• 新しいデータベースを作成します。

  $CREATE DATABASE テスト;

• 主キーを追加してテーブルを作成します。

  $ CREATE TABLE country(id INT, name VARCHAR(30), PRIMARY KEY (id));

一般に、複雑なことは何もありません。変数を宣言した後、次の部分PRIMARY KEY (id)が追加され、主キーとなるフィールドの名前が括弧内に渡されます。そして、テーブル スキーマがどのように変更されたかを見てみましょう 。 $ DESC country; ご覧のとおり、値PRI がID エントリのKeyフィールドに表示されています。

テーブル作成後の主キー

前に述べたように、テーブル作成後の最初のキーはALTER TABLE を使用して割り当てることができます。この例を都市データベースで実行します。

• テスト データベースからデータベースに移動してみましょう。

  $USE 都市。

• データベースに確実に存在することを確認してみましょう (そこには別のフィールド、つまり人口があるはずです)。これを行うには、次のように書きます。

  $ DESC 人口;

すべて正しいです、テーブルは私たちのものです。次のように書いてみましょう。

$ ALTER TABLE country ADD PRIMARY KEY (id);

• 次のコマンドですぐに確認してください。

  $DESC 国;

写真からわかるように、すべてが正しく、PRI値は正確にあるべき位置にあります。ちなみに、私たちはテストデータベースを使用しました。次に、それを削除する必要があります。なぜサーバーを乱雑にする必要があるのでしょうか? これを行うには、よく知られたコマンド $ DROP DATABASE test;を使用します。

モルドバを追加

まず、何を記録するかを決める必要があります。次の ID は 4 になります。名前はモルドバで、人口は 3550900 です。したがって、すでにわかっている INSERT INTO コマンドを実行します。 $ INSERT INTO country VALUES (4, 'Moldova', 3550900); そして、この値がデータベースに正確に存在するかどうかを確認します。 $ SELECT * FROM country WHERE id = 4; データ要求では、どのフィールドを検索するかをすぐに決定したため、必要なレコードが 1 つだけ得られました。

都市テーブルを作成する

データベースに関する最初の記事の図を使用して、テーブルに関する必要な情報を取得します。これには次のフィールドが含まれます。

• id - 一意の識別子。
• 名前 — 都市名。
• country_id — 国の外部キー。
• 人口 — 都市の人口。

毎回固有のIDを書くのはちょっとストレスですよね。これについては MySQL当局に任せたいと思います。そして、そのような方法があります - AUTO INCREMENT。これをデジタル フィールドに追加する必要があります。値を明示的に渡さない場合、MySQL 自体が以前の ID と比較して ID を 1 つ増やします。したがって、テーブルの作成は次のようになります。 $ CREATE TABLE city ( id INT AUTO_INCREMENT, name VARCHAR(30), country_id INT, Population INT, PRIMARY KEY (id)); テーブル図を見て、すべてが正しく行われたかどうかを確認してみましょう 。テーブル図からわかるように、id フィールドに新しい説明 - auto_increment が追加されました。それで私たちはすべてを正しく行いました。完全に構成されたテーブルのデータを確認してみましょう。これを行うには、タスクの最後の部分である外部キーを実行します。

都市に外部キーを追加する

外部キーの場合は、次のコマンドがあります。 $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); そして、テーブル スキーマの何が問題なのかをすぐに確認しましょう。1 時間の間に変更はありましたか? $DESC 都市;

ボーナス部分。テスト

タスクに追加するのを忘れていました - 最初の部分のスクリーンショットにあったデータを入力してください。忘れてしまったので今度は自分でやります。興味がある方は、私なしでご自身でやっていただければ、私たちが確認します ;) ハリコフ、キエフ、ミンスク、オデッサ、ヴォロネジがありましたが、キシナウも追加します。ただし、今回は ID を送信せず、スキップします。 $ INSERT INTO city (name, country_id, Population) VALUES ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' 、3、2545500)、(「オデッサ」、1、1017699)、(「ヴォロネジ」、2、1058261)、(「キシニョフ」、4、695400); ご覧のとおり、1 つの INSERT INTO コマンドを使用して複数のエントリを同時に作成できます。便利なことです、覚えておいてください) そしてすぐにテーブルの内容を見てみましょう: $ SELECT * FROM city; AUTO_INCREMENT - 希望どおりに機能しました。ID ファイルは提出していませんが、すべて記入されています。外部キーは依存するものです。正しく動作するかどうかを確認するには、外部テーブルに存在しない外部キーを書き込んでみてください。id = 5 がカザフスタンであると決定したとしましょう。しかし実際には、それは国の表には載っていません。そして、データベースが正しく動作することを確認するには、都市を追加します - Astana: $ INSERT INTO city (name, country_id, Population) VALUES ('Astana', 5, 1136156); そして、当然のことながらエラーが発生します。外部キーにより、データベースにない都市に国を割り当てようとしないようになりました。宿題のこの部分は完了したと見なすことができます - 新しい宿題に進みます:)

SELECT ステートメント

まあ、もう何もかもがそれほど怖くないように思えますよね？Java 開発者にとって、データベースの知識は必須であることをもう一度指摘しておきます。データベースがなければどこにも行けません。はい、すでにアプリケーションの作成を開始したいと思っています。同意します。しかし、それは必要なことなのです。ということで、このまま続けていきます。SELECT ステートメントを使用して、データベースからデータを取得します。つまり、これは典型的な DML 操作です (もう内容を忘れてしまいましたか?...))) 前に記事をもう一度読んでください)。リレーショナル データベースの利点は何ですか? データの集約と取得のための優れた機能を備えています。これは SELECT ステートメントが使用される目的です。それについては何も複雑なことはできないように思えますよね? しかし、理解すべきことはまだたくさんあることがわかりました) 私たちが基礎を築くことができる基本を理解することが重要です。SELECT ステートメントを使用した最も単純なクエリは、1 つのテーブルからすべてのデータを選択することです。SELECT クエリで演算子がどのような順序で実行されるべきかについての Wiki の説明がとても気に入ったので、厚かましくもここにコピーします。

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

ここで、最初に GROUP BY 演算子を配置し、次に WHERE 演算子を配置することはできないことがわかります。後でどこから来たのか不明な間違いに対して憤慨しないように、これを覚えておく必要があります。 $SELECT * 都市から; しかし、すべてのデータをかき出すことは、私たちにとって明らかに楽しいことではありません。これは、顕微鏡で釘を打ちたい場合にもまったく同じです[1]、[2]。データベースは、Java コードよりもはるかに高速にフィルタリング、並べ替え、および集計操作を実行するため、この問題はデータベースに任せた方がよいでしょう。したがって、タスクを複雑にすることで、新しい機能が開かれます。

WHEREパラメータ

選択内容をフィルタするには、WHERE という単語が使用されます。これは次のように解釈されます: SELECT * FROM tablename (テーブル tablename からすべてのフィールドを選択します) WHERE talbe_row = 1 (レコード内の table_row フィールドは 1 に等しい)。クエリ内のキーワードの順序が重要であることに注意することが重要です。WHERE a =1 FROM table_name SELECT * は記述できません。ロシア語ではこれは問題ありませんし、それほど悪くないと思われる人もいるかもしれませんが、SQL ではこれは受け入れられません。次のクエリを作成します。 $ SELECT * FROM city WHERE country_id = 1; そして私たちはウクライナの都市を選びました。悪くないですよね？ウクライナ語だけでなくベラルーシ語も必要な場合はどうすればよいでしょうか? この目的のために、フィールドが取得できる値のコレクションをリストできます。 $SELECT * FROM city WHERE country_id IN(1, 3); そして、すでに 2 か国の都市がこれに応えています。フィルタリングする条件が複数ある場合はどうすればよいでしょうか? 人口が 200 万人を超える都市が必要だとしますか? これを行うには、単語ORおよびANDを使用します。 $ SELECT * FROM city WHERE country_id IN (1, 3) AND Population > 2000000; それは素晴らしいことですが、正規表現を使用して名前を検索するという条件をもう 1 つ追加する必要がある場合はどうすればよいでしょうか (正規表現についてはここでは説明しません。これを4 つのパートで「簡単に」実行した人をここに示します)。たとえば、都市の綴りは覚えていますが、完全には覚えていません...これを行うには、フィルター式にLIKEキーワードを追加します。 $ SELECT * FROM city WHERE country_id IN (1, 3) AND Population > 2000000 OR 「%hark%」のような名前を付けます。こうして私たちはハリコフも手に入れました。結果として、私たちの探索は非常にうまくいったと言えます。ID順ではなく人口順に並べ替えたいのですが、どうすればよいでしょうか？はい、とてもシンプルです...

ORDER BY パラメータ

ORDER BY を使用すると、受信したレコードを特定のフィールドで並べ替えることができます。数値と文字列の両方をソートします。前のクエリを拡張して、人口で並べ替え、ORDER BY 人口を追加してみましょう。ご覧のとおり、並べ替えは自然な順序、つまり昇順で行われました。逆を望む場合はどうなるでしょうか? これを行うには、単語 DESC を追加する必要があります。 $ SELECT * FROM city WHERE country_id IN (1, 3) AND 人口 > 2000000 OR name LIKE “%hark%” ORDER BY 人口 DESC; 現在、仕分けは人口削減に基づいています。そして、データベースはこれを非常に迅速に実行します。Collections.sortは比較できません。次に、行ごと、名前ごとに逆順に並べ替えてみましょう。 $ SELECT * FROM city WHERE country_id IN (1, 3) AND 人口 > 2000000 OR name LIKE “%hark%” ORDER BY name DESC;

GROUP BY パラメータ

特定のフィールドごとにレコードをグループ化するために使用されます。これは通常、集計関数を使用するために必要です...集計関数とは何ですか?)) 複数のフィールドが異なるレコードで同じである場合、いくつかのフィールドでグループ化することは理にかなっています。この例を使用して、これが何を意味するかを見てみましょう。都市に外部キー、つまり国 ID があるとします。したがって、同じ国の都市では ID は同じになります。したがって、レコードを取得してそれらによってグループ化できます。 $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id; ただし、関数を集約しないと、見た目が少し精彩に欠けることは認めざるを得ません。したがって、最も一般的な関数をいくつか見てみましょう。

• COUNT - レコードの数。グループ化せずに使用でき、COUNT(*)として使用されます。何らかのフィールドでグループ化する場合 - COUNT(groupped_field);
• MAX - 特定のフィールドの最大値を見つけます。
• MIN - 特定のフィールドの最小値を見つけます。
• SUM - 特定のフィールドの合計を見つけます。
• AVG - 平均値を見つけます。

一般に、これらの関数はグループ化せずに使用でき、その場合のみ 1 つのフィールドのみが表示されます。私たちの都市の人口に対してそれらを試してみましょう: $ SELECT COUNT(*) FROM city; 彼らが求めたものは、彼らが手に入れたものでした。レコード数だけです。場合によってはこれが役立つこともあります。たとえば、特定の著者の記事数を調べる必要がある場合です。データベースからそれらをかき集めて数える必要はありません。単純に COUNT() を使用できます。 $ 都市から AVG(人口) を選択します。$ 都市から MIN(人口) を選択します。 ここでグループ化が有効になります。たとえば、タスクはその国で最小の都市を取得することです。すでにその方法を知っていますか? $ SELECT country_id as Country, MIN(population) FROM city WHERE GROUP BY country_id; これまでのところ、国の ID 番号しか表示されていませんが、それは問題ではありません。次回はすべてを実行します。そして、すでに結果があり、私たちが望んでいたもの、つまり ID = 1 を持つ国内最小の都市が得られました。残りの機能は同じです。グループ化と集計を使用する場合、* を使用してすべてのフィールドをかき出すことは機能しないことに注意することが重要です。考えてみてください ;)

宿題

前回の記事の結果から、宿題が行われているのは明らかなので、続けてみましょう)) はい、宿題をした皆さんは引き続きコメントに「+」を付けていきます。宿題のテーマがあなたにとって興味深いものであることが私にとって重要なので、今後も宿題を続けていきたいと思います。はい、あなたのコメントは定期的に読んでいます。もちろん、返信する頻度は低くなります。もっと難しい SQL の問題を出してほしいと頼まれているのを見ました。結合を学習するまでは興味深い問題は存在しないため、さらなる資料として必要な問題が存在します。

タスク:

HAVING演算子を理解して、この例のテーブルに対するサンプル クエリを作成します。より明確にするためにいくつかのフィールドまたは複数の値を追加する必要がある場合は、それらを追加します。希望する人がいたら、コメントに解決策の例を書いてください。そうすれば、時間があれば私もそれを確認することができます。
1. MySQL Workbench をインストールして、UI を通じてデータベースを操作します。コンソールからの作業についてはすでに十分な練習ができていると思います。データベースに接続します。他のものを使用してデータベースを操作する場合は、このタスクをスキップしてください。ここでは今後も MySQL Workbench のみを使用します。
2. データを使用して受信するリクエストを書き込みます。
   1. 最小/最も人口の多い国。
   2. その国の平均住民数。
   3. 名前が「a」で終わる国の平均住民数。
   4. 人口が400万人を超える国の数。
   5. 住民数の多い順に国を並べ替えます。
   6. 国を名前で自然な順序で並べ替えます。

結論

今日は前回のレッスンの宿題について詳しく話し合いました。さらに、これはやらなかった人にとっても、やった人にとっても重要だと思います。前者にとっては答えを見つける機会であり、後者にとってはそれを自分の結果と比較する機会です。 GitHub アカウントを購読して、プロジェクトの変更に関する最新情報を入手してください。コードベース全体をそこで保守します。すべてはこの組織内で行われます。 次に、SELECT ステートメントについて説明しました。彼は私たちにとって最も重要な人です。すべてのデータ要求はそれを通じて行われるため、私たちはそれを理解する必要があります。最も重要なことは、パラメーターが追加される順序 (WHERE、ORDER BY、GROUP BY など) を覚えておくことです。はい、可能なことすべてを話したわけではありませんが、自分自身にそのような目標を設定したわけではありません。はい、あなたはすでにアプリケーションを書きたいと思っていると思います。お待ちください。必要なのはこれだけです。プロジェクトとあなたの専門的な成長の両方のために。待っている間、Git がすでに慣れていることを確認してください。よく知られているツールなので、デフォルトで使用します。読んでくれた皆さん、ありがとう。次の記事では、データベース接続と結合について説明します。そこに素晴らしいタスクがあります))

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