只要在检索和显示记录时有特定顺序的要求,SQL SELECT命令就会从HSQLDB表中获取数据.在这种情况下,我们可以使用 ORDER BY 子句.
语法
以下是SELECT命令的语法与ORDER BY子句一起对HSQLDB中的数据进行排序.
SELECT field1, field2,...fieldN table_name1, table_name2...ORDER BY field1, [field2...] [ASC [DESC]]
您可以在列出该字段的任何字段上对返回的结果进行排序.
您可以在多个字段上对结果进行排序.
您可以使用关键字ASC或DESC以升序或降序获取结果.默认情况下,它按升序排列.
您可以通过常规方式使用WHERE ... LIKE子句来设置条件.
示例
让我们考虑一个获取和排序 tutorials_tbl 表格通过按升序排序作者姓名.以下是查询相同的内容.
SELECT id, title, author from tutorials_tbl ORDER BY author ASC;
执行上述查询后,您将收到以下输出.
+------+----------------+-----------------+| 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程序
这是用于获取和排序 tutorials_tbl 记录的JDBC程序表格通过按升序排序作者姓名.将以下程序保存到 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); } }}
您可以使用以下命令启动数据库.
\>cd C:\hsqldb-2.3.4\hsqldbhsqldb>java -classpath lib/hsqldb.jar org.hsqldb.server.Server --database.0file:hsqldb/demodb --dbname.0 testdb
使用以下命令编译并执行上述程序.
\>javac OrderBy.java\>java OrderBy
执行上述命令后,您将收到以下输出.
102 | Learn MySQL | Abdul S104 | Learn JDB | Ajith kumar103 | Learn Excell | Bavya Kanna100 | Learn PHP | John Poul105 | Learn Junit | Sathya Murthi101 | C and Data Structures | Yaswanth