Hsqldb 简明教程

HSQLDB - LIKE Clause

RDBMS 结构中有一个 WHERE 子句。您可以在我们希望进行精确匹配的地方使用带等号 (=) 的 WHERE 子句。但可能需要一个要求，我们想要过滤出所有包含“john”的作者姓名的结果。可以使用 SQL LIKE 子句和 WHERE 子句一起处理此问题。

There is a WHERE clause in the RDBMS structure. You can use the WHERE clause with an equal to sign (=) where we want to do an exact match. But there may be a requirement where we want to filter out all the results where the author name should contain "john". This can be handled using the SQL LIKE clause along with the WHERE clause.

如果 SQL LIKE 子句与 % 字符一起使用，那么在命令提示符处列出所有文件或目录时，它将像 UNIX 中的元字符 (*) 一样工作。

If the SQL LIKE clause is used along with % characters, then it will work like a metacharacter (*) in UNIX while listing out all the files or directories at command prompt.

Syntax

以下是 LIKE 子句的通用 SQL 语法。

Following is the generic SQL syntax of the LIKE clause.

SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

You can specify any condition using the WHERE clause.
You can use the LIKE clause along with the WHERE clause.
You can use the LIKE clause in place of the equal to sign.
When the LIKE clause is used along with the % sign, then it will work like a metacharacter search.
You can specify more than one conditions using AND or OR operators.
A WHERE…LIKE clause can be used along with the DELETE or the UPDATE SQL command to specify a condition.

Example

让我们考虑一个例子，其中检索教程数据的列表，其中作者名字以 John 开头。以下是给定例子的HSQLDB查询。

Let us consider an example that retrieves the list of tutorials data where the author name starts with John. Following is the HSQLDB query for the given example.

SELECT * from tutorials_tbl WHERE author LIKE 'John%';

在执行上述查询后，你将收到以下输出。

After execution of the above query, you will receive the following output.

+-----+----------------+-----------+-----------------+
|  id |      title     |   author  | submission_date |
+-----+----------------+-----------+-----------------+
| 100 |    Learn PHP   | John Poul | 2016-06-20      |
+-----+----------------+-----------+-----------------+

HSQLDB – JDBC Program

以下是检索教程数据的列表的JDBC程序，其中作者名字以 John 开头。将代码保存到 LikeClause.java 。

Following is the JDBC program that retrieves the list of tutorials data where the author name starts with John. Save the code into LikeClause.java.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class LikeClause {

   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 * from tutorials_tbl WHERE author LIKE 'John%';");

         while(result.next()){
            System.out.println(result.getInt("id")+" |
               "+result.getString("title")+" |
               "+result.getString("author")+" |
               "+result.getDate("submission_date"));
         }
      } catch (Exception e) {
         e.printStackTrace(System.out);
      }
   }
}

您可以使用以下命令启动数据库。

You can start the database using the following command.

\>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

使用以下命令编译并执行上述代码。

Compile and execute the above code using the following command.

\>javac LikeClause.java
\>java LikeClause

在执行以下命令后，您将收到下面的输出。

After execution of the following command, you will receive the following output.

100 | Learn PHP | John Poul | 2016-06-20