Apache Derby 简明教程

Apache Derby - Order By Clause

ORDER BY 子句用于按其使用关键词的顺序排列结果集的内容，ASC 代表升序，DESC 代表降序。如果您不提及这些内容中的任何内容，那么内容将按升序排列（默认设置）。

The ORDER BY clause is used to arrange the contents of the result set in the order it uses the keywords, ASC representing ascending order and DESC representing descending order. If you do not mention either of these, the contents will be arranged in ascending order by default.

Syntax

以下为 ORDER BY 子句的语法 −

Following is the syntax of the ORDER BY clause −

SELECT * FROM table_name ORDER BY column_name ASC|DESC.

Example

假设我们在数据库中有一个名为 Employees 的表，其中包含以下记录 −

Suppose we have a table named Employees in the database with the following records −

ID |NAME    |SALARY |LOCATION
------------------------------------------------------------------------------
1  |Amit    |30000  |Vijayawada
2  |Kalyan  |40000  |Vishakhapatnam
3  |Renuka  |50000  |Delhi
4  |Archana |15000  |Vijayawada
5  |Trupthi |45000  |Kochin
6  |Suchatra|33000  |Vijayawada
7  |Rahul   |39000  |Lucknow

以下查询按 Employee 名称将表中的内容按升序排列。

The following query arranges the contents of the table in ascending order based on the name of the Employee.

ij> SELECT * FROM Employees ORDER BY Name;

这会产生以下输出 −

This will generate the following output −

ID |NAME    |SALARY |LOCATION
---------------------------------------------------------------
1  |Amit    |30000  |Hyderabad
4  |Archana |15000  |Mumbai
2  |Kalyan  |40000  |Vishakhapatnam
7  |Rahul   |39000  |Lucknow
3  |Renuka  |50000  |Delhi
6  |Suchatra|33000  |Pune
5  |Trupthi |45000  |Kochin
7 rows selected

同样，以下查询按 Employee 的薪水将表中的内容按降序排列 −

Similarly, following query arranges the contents of the table in descending order based on the salary of the Employee −

ij> SELECT * FROM Employees ORDER BY Salary DESC;

这会产生以下输出 −

This will generate the following output −

ID |NAME     |SALARY |LOCATION
---------------------------------------------------------------
3  |Renuka   |50000  |Delhi
5  |Trupthi  |45000  |Kochin
2  |Kalyan   |40000  |Vishakhapatnam
7  |Rahul    |39000  |Lucknow
6  |Suchatra |33000  |Pune
1  |Amit     |30000  |Hyderabad
4  |Archana  |15000  |Mumbai
7 rows selected

Sorting Data using JDBC program

本节将为您讲解如何在 Derby 中使用 JDBC 对表的内容进行排序。您可以使用 ORDER BY 子句和关键词 ASC（表示升序）和 DSC（表示降序）按顺序排列记录。

This section teaches you how to sort the contents of a table in Derby using JDBC. You can arrange records in order using the ORDER BY clause and the keywords ASC (denoting ascending order), and DSC (denoting descending order).

如果您想使用网络客户端请求 Derby 网络服务器，请确保服务器已启动且正在运行。网络客户端驱动程序的类名为 org.apache.derby.jdbc.ClientDriver，URL 为 jdbc:derby://localhost:1527/ DATABASE_NAME;*create=true;user=*USER_NAME; password= PASSWORD "。

If you want to request the Derby network server using network client, make sure that the server is up and running. The class name for the Network client driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;*create=true;user=*USER_NAME; password=PASSWORD".

按照以下步骤在 Apache Derby 中对表的记录进行排序 −

Follow the steps given below to sort the records of a table in Apache Derby −

Step 1: Register the driver

要与数据库通信，首先需要注册驱动程序。类 Class 的 forName() 方法接受表示类名称的 String 值，将其加载到内存中，这会自动注册它。使用此方法注册驱动程序。

To communicate with the database, first of all, you need to register the driver. The forName() method of the class Class accepts a String value representing a class name loads it in to the memory, which automatically registers it. Register the driver using this method.

Step 2: Get the connection

通常，我们与数据库进行通信时执行的第一步就是与数据库连接。类 Connection 表示与数据库服务器之间的物理连接。你可以通过调用类 DriverManager 的方法 getConnection() 创建连接对象。使用此方法来创建连接。

In general, the first step we do to communicate to the database is to connect with it. The Connection class represents the physical connection with a database server. You can create a connection object by invoking the getConnection() method of the DriverManager class. Create a connection using this method.

Step 3: Create a statement object

您需要创建一个 Statement 或 PreparedStatement 或 CallableStatement 对象才能向数据库发送 SQL 语句。您可以分别使用 createStatement(), prepareStatement() and, prepareCall() 方法创建这些对象。使用相应的方法创建其中一个对象。

You need to create a Statement or PreparedStatement or, CallableStatement objects to send SQL statements to the database. You can create these using the methods createStatement(), prepareStatement() and, prepareCall() respectively. Create either of these objects using the appropriate method.

Step 4: Execute the query

创建该语句后，你需要执行它。类 Statement 提供了多个方法来执行查询，如方法 execute() 以执行返回多个结果集的语句。方法 executeUpdate() 执行诸如 INSERT、UPDATE、DELETE 的查询。方法 executeQuery() 返回数据。使用其中一种方法并执行之前创建的语句。

After creating a statement, you need to execute it. The Statement class provides various methods to execute a query like the execute() method to execute a statement that returns more than one result set. The executeUpdate() method executes queries like INSERT, UPDATE, DELETE. The executeQuery() method returns data. Use either of these methods and execute the statement created previously.

Example

以下 JDBC 示例演示了如何使用 JDBC 程序对 Apache Derby 中表的记录进行排序。在此处，我们使用嵌入式驱动程序连接到名为 sampleDB（如果不存在则创建）的数据库。

Following JDBC example demonstrates how to sort the records of a table in Apache Derby using JDBC program. Here, we are connecting to a database named sampleDB (will create if it does not exist) using the embedded driver.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SortData {
   public static void main(String args[]) throws SQLException, ClassNotFoundException {
      //Registering the driver
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

      //Getting the Connection object
      String URL = "jdbc:derby:SampleDB;create=true";
      Connection conn = DriverManager.getConnection(URL);

      //Creating the Statement object
      Statement stmt = conn.createStatement();

      //Creating a table and populating it
      String query = "CREATE TABLE Employees("
         + "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
         + "Name VARCHAR(255), Salary INT NOT NULL, "
         + "Location VARCHAR(255), "
         + "PRIMARY KEY (Id))";
      String query = "INSERT INTO Employees("
         + "Name, Salary, Location) VALUES "
         + "('Amit', 30000, 'Hyderabad'), "
         + "('Kalyan', 40000, 'Vishakhapatnam'), "
         + "('Renuka', 50000, 'Delhi'), "
         + "('Archana', 15000, 'Mumbai'), "
         + "('Trupthi', 45000, 'Kochin'), "
         + "('Suchatra', 33000, 'Pune'), "
         + "('Rahul', 39000, 'Lucknow'), "
         + "('Trupti', 45000, 'Kochin')";
      //Executing the query
      String query = "SELECT Location, SUM(Salary) " + "from Employees GROUP BY Location";
      ResultSet rs = stmt.executeQuery(query);
      while(rs.next()) {
         System.out.println("Salary: "+rs.getString(1));
         System.out.println("Location: "+rs.getString(2));
         System.out.println(" ");
      }
   }
}

Output

执行上述程序时，你将得到以下输出：

On executing the above program, you will get the following output −

Salary: Chennai
Location: 43000

Salary: Delhi
Location: 50000

Salary: Hyderabad
Location: 30000

Salary: Kochin
Location: 45000

Salary: Lucknow
Location: 39000

Salary: Mumbai
Location: 15000

Salary: Pune
Location: 33000