单表查询

单表查询

在这个章节当中，将开始介绍如何使用 SQL 来对数据库中的数据进行查询。

开始之前

下面将围绕 Bookshop 这个应用程序来对 TiDB 的数据查询部分展开介绍。

在阅读本章节之前，你需要做以下准备工作：

简单的查询

在 Bookshop 应用程序的数据库当中，authors 表存放了作家们的基础信息，可以通过 SELECT ... FROM ... 语句将数据从数据库当中调取出去。

SQL
Java

在 MySQL Client 等客户端输入并执行如下 SQL 语句：

1. SELECT id, name FROM authors;

输出结果如下：

1. +------------+--------------------------+
2. | id         | name                     |
3. +------------+--------------------------+
4. |       6357 | Adelle Bosco             |
5. |     345397 | Chanelle Koepp           |
6. |     807584 | Clementina Ryan          |
7. |     839921 | Gage Huel                |
8. |     850070 | Ray Armstrong            |
9. |     850362 | Ford Waelchi             |
10. |     881210 | Jayme Gutkowski          |
11. |    1165261 | Allison Kuvalis          |
12. |    1282036 | Adela Funk               |
13. ...
14. | 4294957408 | Lyla Nitzsche            |
15. +------------+--------------------------+
16. 20000 rows in set (0.05 sec)

在 Java 语言当中，可以通过声明一个 Author 类来定义如何存放作者的基础信息，根据数据的类型和取值范围从 Java 语言当中选择合适的数据类型来存放对应的数据，例如：

使用 Int 类型变量存放 int 类型的数据。
使用 Long 类型变量存放 bigint 类型的数据。
使用 Short 类型变量存放 tinyint 类型的数据。
使用 String 类型变量存放 varchar 类型的数据。
…

1. public class Author {
2. private Long id;
3. private String name;
4. private Short gender;
5. private Short birthYear;
6. private Short deathYear;

8. public Author() {}

10. // Skip the getters and setters.
11. }

1. public class AuthorDAO {

3. // Omit initialization of instance variables.

5. public List<Author> getAuthors() throws SQLException {
6. List<Author> authors = new ArrayList<>();

8. try (Connection conn = ds.getConnection()) {
9. Statement stmt = conn.createStatement();
10. ResultSet rs = stmt.executeQuery("SELECT id, name FROM authors");
11. while (rs.next()) {
12. Author author = new Author();
13. author.setId(rs.getLong("id"));
14. author.setName(rs.getString("name"));
15. authors.add(author);
16. }
17. }
18. return authors;
19. }
20. }

在获得数据库连接之后，你可以通过 conn.createStatement() 语句创建一个 Statement 实例对象。
然后调用 stmt.executeQuery("query_sql") 方法向 TiDB 发起一个数据库查询请求。
数据库返回的查询结果将会存放到 ResultSet 当中，通过遍历 ResultSet 对象可以将返回结果映射到此前准备的 Author 类对象当中。

对结果进行筛选

查询得到的结果非常多，但是并不都是你想要的？可以通过 WHERE 语句对查询的结果进行过滤，从而找到想要查询的部分。

例如，想要查找众多作家当中找出在 1998 年出生的作家：

SQL
Java

在 SQL 中，可以使用 WHERE 子句添加筛选的条件：

1. SELECT * FROM authors WHERE birth_year = 1998;

对于 Java 程序而言，可以通过同一个 SQL 来处理带有动态参数的数据查询请求。

将参数拼接到 SQL 语句当中也许是一种方法，但是这可能不是一个好的主意，因为这会给应用程序带来潜在的 SQL 注入风险。

在处理这类查询时，应该使用 PreparedStatement 来替代普通的 Statement。

1. public List<Author> getAuthorsByBirthYear(Short birthYear) throws SQLException {
2. List<Author> authors = new ArrayList<>();
3. try (Connection conn = ds.getConnection()) {
4. PreparedStatement stmt = conn.prepareStatement("""
5. SELECT * FROM authors WHERE birth_year = ?;
6. """);
7. stmt.setShort(1, birthYear);
8. ResultSet rs = stmt.executeQuery();
9. while (rs.next()) {
10. Author author = new Author();
11. author.setId(rs.getLong("id"));
12. author.setName(rs.getString("name"));
13. authors.add(author);
14. }
15. }
16. return authors;
17. }

对结果进行排序

使用 ORDER BY 语句可以让查询结果按照期望的方式进行排序。

例如，可以通过下面的 SQL 语句对 authors 表的数据按照 birth_year 列进行降序 (DESC) 排序，从而得到最年轻的作家列表。

SQL
Java

1. SELECT id, name, birth_year
2. FROM authors
3. ORDER BY birth_year DESC;

1. public List<Author> getAuthorsSortByBirthYear() throws SQLException {
2. List<Author> authors = new ArrayList<>();
3. try (Connection conn = ds.getConnection()) {
4. Statement stmt = conn.createStatement();
5. ResultSet rs = stmt.executeQuery("""
6. SELECT id, name, birth_year
7. FROM authors
8. ORDER BY birth_year DESC;
9. """);

11. while (rs.next()) {
12. Author author = new Author();
13. author.setId(rs.getLong("id"));
14. author.setName(rs.getString("name"));
15. author.setBirthYear(rs.getShort("birth_year"));
16. authors.add(author);
17. }
18. }
19. return authors;
20. }

查询结果如下：

1. +-----------+------------------------+------------+
2. | id        | name                   | birth_year |
3. +-----------+------------------------+------------+
4. | 83420726  | Terrance Dach          | 2000       |
5. | 57938667  | Margarita Christiansen | 2000       |
6. | 77441404  | Otto Dibbert           | 2000       |
7. | 61338414  | Danial Cormier         | 2000       |
8. | 49680887  | Alivia Lemke           | 2000       |
9. | 45460101  | Itzel Cummings         | 2000       |
10. | 38009380  | Percy Hodkiewicz       | 2000       |
11. | 12943560  | Hulda Hackett          | 2000       |
12. | 1294029   | Stanford Herman        | 2000       |
13. | 111453184 | Jeffrey Brekke         | 2000       |
14. ...
15. 300000 rows in set (0.23 sec)

限制查询结果数量

如果希望 TiDB 只返回部分结果，可以使用 LIMIT 语句限制查询结果返回的记录数。

SQL
Java

1. SELECT id, name, birth_year
2. FROM authors
3. ORDER BY birth_year DESC
4. LIMIT 10;

1. public List<Author> getAuthorsWithLimit(Integer limit) throws SQLException {
2. List<Author> authors = new ArrayList<>();
3. try (Connection conn = ds.getConnection()) {
4. PreparedStatement stmt = conn.prepareStatement("""
5. SELECT id, name, birth_year
6. FROM authors
7. ORDER BY birth_year DESC
8. LIMIT ?;
9. """);
10. stmt.setInt(1, limit);
11. ResultSet rs = stmt.executeQuery();
12. while (rs.next()) {
13. Author author = new Author();
14. author.setId(rs.getLong("id"));
15. author.setName(rs.getString("name"));
16. author.setBirthYear(rs.getShort("birth_year"));
17. authors.add(author);
18. }
19. }
20. return authors;
21. }

查询结果如下：

1. +-----------+------------------------+------------+
2. | id        | name                   | birth_year |
3. +-----------+------------------------+------------+
4. | 83420726  | Terrance Dach          | 2000       |
5. | 57938667  | Margarita Christiansen | 2000       |
6. | 77441404  | Otto Dibbert           | 2000       |
7. | 61338414  | Danial Cormier         | 2000       |
8. | 49680887  | Alivia Lemke           | 2000       |
9. | 45460101  | Itzel Cummings         | 2000       |
10. | 38009380  | Percy Hodkiewicz       | 2000       |
11. | 12943560  | Hulda Hackett          | 2000       |
12. | 1294029   | Stanford Herman        | 2000       |
13. | 111453184 | Jeffrey Brekke         | 2000       |
14. +-----------+------------------------+------------+
15. 10 rows in set (0.11 sec)

通过观察查询结果你会发现，在使用 LIMIT 语句之后，查询的时间明显缩短，这是 TiDB 对 LIMIT 子句进行优化后的结果，你可以通过 TopN 和 Limit 下推章节了解更多细节。

聚合查询

如果你想要关注数据整体的情况，而不是部分数据，你可以通过使用 GROUP BY 语句配合聚合函数，构建一个聚合查询来帮助你对数据的整体情况有一个更好的了解。

比如说，你希望知道哪些年出生的作家比较多，你可以将作家基本信息按照 birth_year 列进行分组，然后分别统计在当年出生的作家数量：

SQL
Java

1. SELECT birth_year, COUNT(DISTINCT id) AS author_count
2. FROM authors
3. GROUP BY birth_year
4. ORDER BY author_count DESC;

1. public class AuthorCount {
2. private Short birthYear;
3. private Integer authorCount;

5. public AuthorCount() {}

7. // Skip the getters and setters.
8. }

10. public List<AuthorCount> getAuthorCountsByBirthYear() throws SQLException {
11. List<AuthorCount> authorCounts = new ArrayList<>();
12. try (Connection conn = ds.getConnection()) {
13. Statement stmt = conn.createStatement();
14. ResultSet rs = stmt.executeQuery("""
15. SELECT birth_year, COUNT(DISTINCT id) AS author_count
16. FROM authors
17. GROUP BY birth_year
18. ORDER BY author_count DESC;
19. """);

21. while (rs.next()) {
22. AuthorCount authorCount = new AuthorCount();
23. authorCount.setBirthYear(rs.getShort("birth_year"));
24. authorCount.setAuthorCount(rs.getInt("author_count"));
25. authorCounts.add(authorCount);
26. }
27. }
28. return authorCount;
29. }

查询结果如下：

1. +------------+--------------+
2. | birth_year | author_count |
3. +------------+--------------+
4. |       1932 |          317 |
5. |       1947 |          290 |
6. |       1939 |          282 |
7. |       1935 |          289 |
8. |       1968 |          291 |
9. |       1962 |          261 |
10. |       1961 |          283 |
11. |       1986 |          289 |
12. |       1994 |          280 |
13. ...
14. |       1972 |          306 |
15. +------------+--------------+
16. 71 rows in set (0.00 sec)

除了 COUNT 函数外，TiDB 还支持了其他聚合函数。详情请参考 GROUP BY 聚合函数。

单表读取

单表查询

开始之前

简单的查询

对结果进行筛选

对结果进行排序

限制查询结果数量

聚合查询