Apache Derby 简明教程
Apache Derby - Where Clause
WHERE 子句用于 SELECT、DELETE 或 UPDATE 语句，以指定需要对其执行操作的行。通常，此子句后面跟着返回布尔值的条件或表达式，Select、delete 或 update 操作仅对满足给定条件的行执行。
The WHERE clause is used in the SELECT, DELETE or, UPDATE statements to specify the rows on which the operation needs to be carried out. Usually, this clause is followed by a condition or expression which returns a Boolean value, the Select, delete or, update operations are performed only on the rows which satisfy the given condition.
ij> SELECT * from table_name WHERE condition;
or,
ij> DELETE from table_name WHERE condition;
or,
ij> UPDATE table_name SET column_name = value WHERE condition;WHERE 子句可以使用比较运算符，如 =、!=、<、>、⇐ 和 >=，以及 BETWEEN 和 LIKE 运算符。
The WHERE clause can use the comparison operators such as =,!=, <, >, ⇐, and >=, as well as the BETWEEN and LIKE operators.
Example
假设我们在数据库中有一个名为 Employees 的表，其中有 7 条记录，如下所示：
Let us assume we have a table named Employees in the database with 7 records as shown below −
ID |NAME |SALARY |LOCATION
-----------------------------------------------------------------------------
1 |Amit |30000 |Hyderabad
2 |Kalyan |40000 |Vishakhapatnam
3 |Renuka |50000 |Delhi
4 |Archana |15000 |Mumbai
5 |Trupthi |45000 |Kochin
6 |Suchatra|33000 |Pune
7 |Rahul |39000 |Lucknow以下 SQL DELETE 语句获取工资超过 35000 的员工记录：
The following SQL DELETE statement fetches the records of the employees whose salary is more than 35000 −
ij> SELECT * FROM Employees WHERE Salary>35000;这将产生以下输出 −
This will produce the following output −
ID |NAME |SALARY |LOCATION
---------------------------------------------------
2 |Kalyan |40000 |Vishakhapatnam
3 |Renuka |50000 |Delhi
5 |Trupthi|45000 |Kochin
7 |Rahul |39000 |Lucknow
4 rows selected同样，您还可以使用此子句删除和更新记录。
Similarly, you can also delete and update records using this clause.
以下示例更新工资低于 30000 的员工的位置。
Following example updates the location of those whose salary is less than 30000.
ij> UPDATE Employees SET Location = 'Vijayawada' WHERE Salary<35000;
3 rows inserted/updated/deleted如果您验证表的内容，可以看到更新后的表如下所示：
If you verify the contents of the table, you can see the updated table as shown below −
ij> SELECT * FROM Employees;
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
7 rows selectedWhere clause JDBC example
本节将教您如何使用 WHERE 子句并在 JDBC 应用程序中对 Apache Derby 数据库中的表执行 CURD 操作。
This section teaches you how to use WHERE clause and perform CURD operations on a table in Apache Derby database using JDBC application.
如果你想使用网络客户端访问 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".
按照以下步骤使用 WHERE 子句并在 Apache Derby 中对表执行 CURD 操作：
Follow the steps given below to use WHERE clause and perform CURD operations on 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 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 results that returns data. Use either of these methods and execute the statement created previously.
Example
以下 JDBC 示例演示了如何使用 JDBC 程序在 Apache Derby 中使用 WHERE 子句并在表上执行 CURD 操作。在此，我们使用嵌入式驱动程序连接到名为 sampleDB 的数据库（如果不存在则创建）。
Following JDBC example demonstrates how to use WHERE 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 WhereClauseExample {
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
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 * FROM Employees WHERE Salary>35000";
ResultSet rs = stmt.executeQuery(query);
while(rs.next()) {
System.out.println("Id: "+rs.getString("Id"));
System.out.println("Name: "+rs.getString("Name"));
System.out.println("Salary: "+rs.getString("Salary"));
System.out.println("Location: "+rs.getString("Location"));
System.out.println(" ");
}
}
}