H2 Database 简明教程

H2 Database - Select

选择命令用于从一个或多个表中获取记录数据。如果我们设计了一个选择查询，它将以 result sets 称为结果表的形式返回数据。

Select command is used to fetch record data from a table or multiple tables. If we design a select query, then it returns data in the form of result table called result sets.

Syntax

SELECT 语句的基本语法如下所示：

The basic syntax of SELECT statement is as follows −

SELECT [ TOP term ] [ DISTINCT | ALL ] selectExpression [,...]
FROM tableExpression [,...] [ WHERE expression ]
[ GROUP BY expression [,...] ] [ HAVING expression ]
[ { UNION [ ALL ] | MINUS | EXCEPT | INTERSECT } select ] [ ORDER BY order [,...] ]
[ [ LIMIT expression ] [ OFFSET expression ] [ SAMPLE_SIZE rowCountInt ] ]
[ FOR UPDATE ]

为了获取所有可用字段，请使用以下语法：

To fetch all the available fields, use the following syntax.

SELECT * FROM table_name;

Example

考虑 CUSTOMER 表，其中有以下记录：

Consider the CUSTOMER table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

要获取 CUSTOMER 表中已给出的数据，执行以下查询。

To get the customer table along with the given data, execute the following queries.

CREATE TABLE CUSTOMER (id number, name varchar(20), age number, address varchar(20),
salary number);

INSERT into CUSTOMER values (1, 'Ramesh', 32, 'Ahmedabad', 2000);
INSERT into CUSTOMER values (2, 'Khilan', 25, 'Delhi', 1500);
INSERT into CUSTOMER values (3, 'kaushik', 23, 'Kota', 2000);
INSERT into CUSTOMER values (4, 'Chaitali', 25, 'Mumbai', 6500);
INSERT into CUSTOMER values (5, 'Hardik', 27, 'Bhopal', 8500);
INSERT into CUSTOMER values (6, 'Komal', 22, 'MP', 4500);
INSERT into CUSTOMER values (7, 'Muffy', 24, 'Indore', 10000);

以下命令是一个示例，它将获取 CUSTOMER 表中客户的 ID、名称和工资字段。

The following command is an example, which would fetch ID, Name and Salary fields of the customers available in the CUSTOMER table.

SELECT ID, NAME, SALARY FROM CUSTOMERS;

上述命令会产生以下结果。

The above command produces the following result.

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  1 | Ramesh   |  2000.00 |
|  2 | Khilan   |  1500.00 |
|  3 | kaushik  |  2000.00 |
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

使用以下查询来获取 CUSTOMERS 表的所有字段。

Use the following query to fetch all the fields of CUSTOMERS table.

SQL> SELECT * FROM CUSTOMERS;

上述查询会产生以下结果：

The above query produces the following result −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+