Hsqldb 简明教程

HSQLDB - Sorting Results

SQL SELECT命令从HSQLDB表中获取数据，只要检索和显示记录时有遵循特定顺序的要求。在这种情况下，我们可以使用 ORDER BY 子句。

The SQL SELECT command fetches data from the HSQLDB table whenever there is a requirement that follows a particular order while retrieving and displaying records. In that case, we can use the ORDER BY clause.

Syntax

以下是SELECT命令的语法，以及ORDER BY子句，用于对HSQLDB中的数据进行排序。

Here is the syntax of the SELECT command along with ORDER BY clause to sort data from HSQLDB.

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

  1. You can sort the returned result on any field provided that field is being listed out.

  2. You can sort the result on more than one field.

  3. You can use the keyword ASC or DESC to get the result in an ascending or descending order. By default, it’s in an ascending order.

  4. You can use the WHERE…​LIKE clause in a usual way to put a condition.

Example

让我们考虑一个例子，通过按升序排列作者姓名来获取并排序 tutorials_tbl 表中的记录。以下是相同的查询。

Let us consider an example that fetches and sorts the records of tutorials_tbl table by ordering the author name in an ascending order. Following is the query for the same.

SELECT id, title, author from tutorials_tbl ORDER BY author ASC;

在执行上述查询后，你将收到以下输出。

After execution of the above query, you will receive the following output.

+------+----------------+-----------------+
| id   |     title      |     author      |
+------+----------------+-----------------+
| 102  |  Learn MySQL   |     Abdul S     |
| 104  |  Learn JDB     |    Ajith kumar  |
| 103  |  Learn Excell  |    Bavya kanna  |
| 100  |  Learn PHP     |    John Poul    |
| 105  |  Learn Junit   |   Sathya Murthi |
| 101  |  Learn C       |    Yaswanth     |
+------+----------------+-----------------+

HSQLDB – JDBC Program

以下是通过按升序排列作者姓名来获取并排序 tutorials_tbl 表中的记录的JDBC程序。将以下程序保存到 OrderBy.java 。

Here is the JDBC program that fetches and sorts the records of tutorials_tbl table by ordering the author name in an ascending order. Save the following program into OrderBy.java.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class OrderBy {

   public static void main(String[] args) {
      Connection con = null;
      Statement stmt = null;
      ResultSet result = null;

      try {
         Class.forName("org.hsqldb.jdbc.JDBCDriver");
         con = DriverManager.getConnection(
            "jdbc:hsqldb:hsql://localhost/testdb", "SA", "");
         stmt = con.createStatement();
         result = stmt.executeQuery(
            "SELECT id, title, author from tutorials_tbl
            ORDER BY author ASC");

         while(result.next()){
            System.out.println(result.getInt("id")+" |
            "+result.getString("title")+" |
            "+result.getString("author"));
         }
      } catch (Exception e) {
         e.printStackTrace(System.out);
      }
   }
}

您可以使用以下命令启动数据库。

You can start the database using the following command.

\>cd C:\hsqldb-2.3.4\hsqldb
hsqldb>java -classpath lib/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 testdb

使用以下命令编译并执行以上程序。

Compile and execute the above program using the following command.

\>javac OrderBy.java
\>java OrderBy

执行上述命令后，您将收到以下输出。

After execution of the above command, you will receive the following output.

102 | Learn MySQL           | Abdul S
104 | Learn JDB             | Ajith kumar
103 | Learn Excell          | Bavya Kanna
100 | Learn PHP             | John Poul
105 | Learn Junit           | Sathya Murthi
101 | C and Data Structures | Yaswanth