单表查询
在这个章节当中,将开始介绍如何使用 SQL 来对数据库中的数据进行查询。
开始之前
下面将围绕 Bookshop 这个应用程序来对 TiDB 的数据查询部分展开介绍。
在阅读本章节之前,你需要做以下准备工作:
- 构建 TiDB 集群(推荐使用 TiDB Cloud 或 TiUP)。
- 导入 Bookshop 应用程序的表结构和示例数据。
- 连接到 TiDB。
简单的查询
在 Bookshop 应用程序的数据库当中,authors
表存放了作家们的基础信息,可以通过 SELECT ... FROM ...
语句将数据从数据库当中调取出去。
- SQL
- Java
在 MySQL Client 等客户端输入并执行如下 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;
}
}
- 在获得数据库连接之后,你可以通过
conn.createStatement()
语句创建一个Statement
实例对象。 - 然后调用
stmt.executeQuery("query_sql")
方法向 TiDB 发起一个数据库查询请求。 - 数据库返回的查询结果将会存放到
ResultSet
当中,通过遍历ResultSet
对象可以将返回结果映射到此前准备的Author
类对象当中。
对结果进行筛选
查询得到的结果非常多,但是并不都是你想要的?可以通过 WHERE
语句对查询的结果进行过滤,从而找到想要查询的部分。
例如,想要查找众多作家当中找出在 1998 年出生的作家:
- SQL
- Java
在 SQL 中,可以使用 WHERE
子句添加筛选的条件:
SELECT * FROM authors WHERE birth_year = 1998;
对于 Java 程序而言,可以通过同一个 SQL 来处理带有动态参数的数据查询请求。
将参数拼接到 SQL 语句当中也许是一种方法,但是这可能不是一个好的主意,因为这会给应用程序带来潜在的 SQL 注入风险。
在处理这类查询时,应该使用 PreparedStatement 来替代普通的 Statement。
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 语句对 authors
表的数据按照 birth_year
列进行降序 (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)
限制查询结果数量
如果希望 TiDB 只返回部分结果,可以使用 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
语句之后,查询的时间明显缩短,这是 TiDB 对 LIMIT
子句进行优化后的结果,你可以通过 TopN 和 Limit 下推章节了解更多细节。
聚合查询
如果你想要关注数据整体的情况,而不是部分数据,你可以通过使用 GROUP BY
语句配合聚合函数,构建一个聚合查询来帮助你对数据的整体情况有一个更好的了解。
比如说,你希望知道哪些年出生的作家比较多,你可以将作家基本信息按照 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 聚合函数。