単一のテーブルからのデータのクエリ

このドキュメントでは、SQL とさまざまなプログラミング言語を使用して、データベース内の 1 つのテーブルからデータをクエリする方法について説明します。

あなたが始める前に

次のコンテンツでは、 書店のアプリケーションを例として、TiDB の単一のテーブルからデータをクエリする方法を示します。

データのクエリを実行する前に、次の手順を完了していることを確認してください。

  1. TiDB クラスターを構築します ( TiDB CloudまたはTiUPを使用することをお勧めします)。
  1. Bookshop アプリケーションのテーブル スキーマとサンプル データをインポートする .
  2. TiDB に接続する .

簡単なクエリを実行する

Bookshop アプリケーションのデータベースでは、 authorsテーブルに著者の基本情報が格納されます。 SELECT ... FROM ...ステートメントを使用して、データベースからデータを照会できます。

  • SQL
  • Java

MySQL クライアントで次の SQL ステートメントを実行します。

SELECT id, name FROM authors;

出力は次のとおりです。

+------------+--------------------------+ | id | name | +------------+--------------------------+ | 6357 | Adelle Bosco | | 345397 | Chanelle Koepp | | 807584 | Clementina Ryan | | 839921 | Gage Huel | | 850070 | Ray Armstrong | | 850362 | Ford Waelchi | | 881210 | Jayme Gutkowski | | 1165261 | Allison Kuvalis | | 1282036 | Adela Funk | ... | 4294957408 | Lyla Nitzsche | +------------+--------------------------+ 20000 rows in set (0.05 sec)

Java では、作成者の基本情報を格納するために、クラスAuthorを宣言できます。データベースのデータ型値の範囲に従って、適切な Java データ型を選択する必要があります。例えば:

  • タイプIntの変数を使用して、タイプintのデータを格納します。
  • タイプLongの変数を使用して、タイプbigintのデータを格納します。
  • タイプShortの変数を使用して、タイプtinyintのデータを格納します。
  • タイプStringの変数を使用して、タイプvarcharのデータを格納します。
public class Author { private Long id; private String name; private Short gender; private Short birthYear; private Short deathYear; public Author() {} // Skip the getters and setters. }
public class AuthorDAO { // Omit initialization of instance variables. public List<Author> getAuthors() throws SQLException { List<Author> authors = new ArrayList<>(); try (Connection conn = ds.getConnection()) { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT id, name FROM authors"); while (rs.next()) { Author author = new Author(); author.setId(rs.getLong("id")); author.setName(rs.getString("name")); authors.add(author); } } return authors; } }
  • JDBC ドライバーを使用して TiDB に接続するの後、 conn.createStatus()Statementオブジェクトを作成できます。
  • 次にstmt.executeQuery("query_sql")を呼び出して、TiDB へのデータベース クエリ要求を開始します。
  • クエリ結果はResultSetのオブジェクトに格納されます。 ResultSetをトラバースすることで、返された結果をAuthorオブジェクトにマップできます。

結果のフィルタリング

クエリ結果をフィルタリングするには、 WHEREステートメントを使用できます。

たとえば、次のコマンドは、すべての著者の中で 1998 年生まれの著者を照会します。

  • SQL
  • Java

WHEREステートメントにフィルター条件を追加します。

SELECT * FROM authors WHERE birth_year = 1998;

Java では、同じ SQL を使用して、動的パラメーターを使用したデータ クエリ要求を処理できます。

これは、パラメーターを SQL ステートメントに連結することによって実行できます。ただし、この方法は、アプリケーションのセキュリティにSQL インジェクションのリスクをもたらす可能性があります。

このようなクエリを処理するには、通常のステートメントの代わりに作成済みステートメントを使用します。

public List<Author> getAuthorsByBirthYear(Short birthYear) throws SQLException { List<Author> authors = new ArrayList<>(); try (Connection conn = ds.getConnection()) { PreparedStatement stmt = conn.prepareStatement(""" SELECT * FROM authors WHERE birth_year = ?; """); stmt.setShort(1, birthYear); ResultSet rs = stmt.executeQuery(); while (rs.next()) { Author author = new Author(); author.setId(rs.getLong("id")); author.setName(rs.getString("name")); authors.add(author); } } return authors; }

結果の並べ替え

クエリ結果を並べ替えるには、 ORDER BYステートメントを使用できます。

たとえば、次の SQL ステートメントは、 birth_year列に従ってauthorsテーブルを降順 ( DESC ) に並べ替えることで、最年少の著者のリストを取得します。

  • SQL
  • Java
SELECT id, name, birth_year FROM authors ORDER BY birth_year DESC;
public List<Author> getAuthorsSortByBirthYear() throws SQLException { List<Author> authors = new ArrayList<>(); try (Connection conn = ds.getConnection()) { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(""" SELECT id, name, birth_year FROM authors ORDER BY birth_year DESC; """); while (rs.next()) { Author author = new Author(); author.setId(rs.getLong("id")); author.setName(rs.getString("name")); author.setBirthYear(rs.getShort("birth_year")); authors.add(author); } } return authors; }

結果は次のとおりです。

+-----------+------------------------+------------+ | id | name | birth_year | +-----------+------------------------+------------+ | 83420726 | Terrance Dach | 2000 | | 57938667 | Margarita Christiansen | 2000 | | 77441404 | Otto Dibbert | 2000 | | 61338414 | Danial Cormier | 2000 | | 49680887 | Alivia Lemke | 2000 | | 45460101 | Itzel Cummings | 2000 | | 38009380 | Percy Hodkiewicz | 2000 | | 12943560 | Hulda Hackett | 2000 | | 1294029 | Stanford Herman | 2000 | | 111453184 | Jeffrey Brekke | 2000 | ... 300000 rows in set (0.23 sec)

クエリ結果の数を制限する

クエリ結果の数を制限するには、 LIMITステートメントを使用できます。

  • SQL
  • Java
SELECT id, name, birth_year FROM authors ORDER BY birth_year DESC LIMIT 10;
public List<Author> getAuthorsWithLimit(Integer limit) throws SQLException { List<Author> authors = new ArrayList<>(); try (Connection conn = ds.getConnection()) { PreparedStatement stmt = conn.prepareStatement(""" SELECT id, name, birth_year FROM authors ORDER BY birth_year DESC LIMIT ?; """); stmt.setInt(1, limit); ResultSet rs = stmt.executeQuery(); while (rs.next()) { Author author = new Author(); author.setId(rs.getLong("id")); author.setName(rs.getString("name")); author.setBirthYear(rs.getShort("birth_year")); authors.add(author); } } return authors; }

結果は次のとおりです。

+-----------+------------------------+------------+ | id | name | birth_year | +-----------+------------------------+------------+ | 83420726 | Terrance Dach | 2000 | | 57938667 | Margarita Christiansen | 2000 | | 77441404 | Otto Dibbert | 2000 | | 61338414 | Danial Cormier | 2000 | | 49680887 | Alivia Lemke | 2000 | | 45460101 | Itzel Cummings | 2000 | | 38009380 | Percy Hodkiewicz | 2000 | | 12943560 | Hulda Hackett | 2000 | | 1294029 | Stanford Herman | 2000 | | 111453184 | Jeffrey Brekke | 2000 | +-----------+------------------------+------------+ 10 rows in set (0.11 sec)

LIMITステートメントを使用すると、この例ではクエリ時間が0.23 secから0.11 secに大幅に短縮されます。詳細については、 TopN と制限を参照してください。

集計クエリ

全体的なデータ状況をよりよく理解するために、 GROUP BYステートメントを使用してクエリ結果を集計できます。

たとえば、より多くの著者が生まれた年を知りたい場合は、 authorsのテーブルをbirth_year列でグループ化し、各年をカウントできます。

  • SQL
  • Java
SELECT birth_year, COUNT (DISTINCT id) AS author_count FROM authors GROUP BY birth_year ORDER BY author_count DESC;
public class AuthorCount { private Short birthYear; private Integer authorCount; public AuthorCount() {} // Skip the getters and setters. } public List<AuthorCount> getAuthorCountsByBirthYear() throws SQLException { List<AuthorCount> authorCounts = new ArrayList<>(); try (Connection conn = ds.getConnection()) { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(""" SELECT birth_year, COUNT(DISTINCT id) AS author_count FROM authors GROUP BY birth_year ORDER BY author_count DESC; """); while (rs.next()) { AuthorCount authorCount = new AuthorCount(); authorCount.setBirthYear(rs.getShort("birth_year")); authorCount.setAuthorCount(rs.getInt("author_count")); authorCounts.add(authorCount); } } return authorCount; }

結果は次のとおりです。

+------------+--------------+ | birth_year | author_count | +------------+--------------+ | 1932 | 317 | | 1947 | 290 | | 1939 | 282 | | 1935 | 289 | | 1968 | 291 | | 1962 | 261 | | 1961 | 283 | | 1986 | 289 | | 1994 | 280 | ... | 1972 | 306 | +------------+--------------+ 71 rows in set (0.00 sec)

COUNT関数に加えて、TiDB は他の集計関数もサポートしています。詳細については、 集計 (GROUP BY) 関数を参照してください。