只要在检索和显示记录时有特定顺序的要求,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 | +------+----------------+-----------------+
这是用于获取和排序 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\hsqldb hsqldb>java -classpath lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:hsqldb/demodb --dbname.0 testdb
使用以下命令编译并执行上述程序.
\>javac OrderBy.java \>java OrderBy
执行上述命令后,您将收到以下输出.
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