Apache Derby 简明教程
Apache Derby - Having Clause
HAVING 子句允许你指定条件，以便按条件筛选在结果中显示哪些组结果。
The HAVING Clause enables you to specify conditions that filter which group results appear in the results.
WHERE 子句对所选列设置条件，而 HAVING 子句对 GROUP BY 子句创建的组设置条件。
The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.
Syntax
以下是 HAVING 子句的语法−
Following is the syntax of the HAVING clause −
ij> SELECT column1, column2 . . . from table_name GROUP BY column having
condition;Example
假设我们在数据库中有一个名为 Employees 的表，其中包含以下 13 条记录 −
Suppose, we have a table named Employees in the database with the 13 records as shown below −
ID |NAME |SALARY |LOCATION
------------------------------------------------------------------
1 |Amit |30000 |Hyderabad
2 |Rahul |39000 |Lucknow
3 |Kalyan |40000 |Vishakhapatnam
4 |Renuka |50000 |Hyderabad
5 |Archana |15000 |Vishakhapatnam
6 |Krishna |40000 |Hyderabad
7 |Trupthi |45000 |Vishakhapatnam
8 |Raghav |12000 |Lucknow
9 |Radha |50000 |Delhi
10 |Anirudh |15000 |Mumbai
11 |Tara |45000 |Kochin
12 |Sucharita |44000 |Kochin
13 |Rizwan |20000 |Lucknow以下查询显示位置中员工的最大工资，其中至少有 3 名员工 −
The following query displays maximum salaries of the employees in the location which have at least 3 employees −
ij> SELECT Location, MAX(Salary) from Employees GROUP BY Location having
count(Location)>=3;这将生成以下输出 −
This generates the following output −
LOCATION |2
------------------------------------------------------------
Hyderabad |50000
Lucknow |39000
Vishakhapatnam |45000
3 rows selectedSorting Data using JDBC program
本部分将指导你如何使用 JDBC 应用程序在 Apache Derby 数据库中使用 Having 子句。
This section teaches you how to use Having a clause in the Apache Derby database using JDBC application.
如果您想使用网络客户端请求 Derby 网络服务器，请确保服务器正在运行。网络客户端驱动程序的类名为 org.apache.derby.jdbc.ClientDriver，URL 为 jdbc:derby://localhost:1527/ DATABASE_NAME;*create=true;user=*USER_NAME;*passw ord=*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;*passw ord=*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() 方法接受一个表示类名称的字符串值，并将其加载到内存中，这会自动将其注册。使用此方法注册驱动程序。
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, and 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 or, 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 示例演示如何使用 Group By 子句并使用 JDBC 程序对 Apache Derby 中的表执行 CURD 操作。此处，我们使用嵌入式驱动程序连接到一个名为 sampleDB 的数据库（如果不存在，则创建该数据库）。
Following JDBC example demonstrates how to use Group By clause and perform CURD operations on 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.Statement;
import java.sql.ResultSet;
public class HavingClauseExample {
public static void main(String args[]) throws Exception {
//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
stmt.execute("CREATE TABLE EmployeesData( "
+ "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
+ "Name VARCHAR(255), "
+ "Salary INT NOT NULL, "
+ "Location VARCHAR(255), "
+ "PRIMARY KEY (Id))");
stmt.execute("INSERT INTO EmployeesData(Name, Salary, Location) "
+ "VALUES ('Amit', 30000, 'Hyderabad'), "
+ "('Rahul', 39000, 'Lucknow'), "
+ "('Renuka', 50000, 'Hyderabad'), "
+ "('Archana', 15000, 'Vishakhapatnam'), "
+ "('Kalyan', 40000, 'Hyderabad'), "
+ "('Trupthi', 45000, 'Vishakhapatnam'), "
+ "('Raghav', 12000, 'Lucknow'), "
+ "('Suchatra', 33000, 'Vishakhapatnam'), "
+ "('Rizwan', 20000, 'Lucknow')");
//Executing the query
String query = "SELECT Location, MAX(Salary) "
+ "from EmployeesData GROUP BY Location having "
+ "count(Location)>=3";
ResultSet rs = stmt.executeQuery(query);
while(rs.next()) {
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(" ");
}
}
}