Hsqldb 简明教程

HSQLDB - Null Values

SQL NULL 是用于表示缺失值的一个术语。表中的 NULL 值是字段中看似空白的值。每当我们尝试给出一个条件（将字段或列值与 NULL 进行比较）时，它并不能正常工作。

SQL NULL is a term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. Whenever we try to give a condition, which compare the field or column value to NULL, it does not work properly.

我们可以通过使用以下三点来处理 NULL 值。

We can handle the NULL values by using the three things.

IS NULL − The operator returns true if the column value is NULL.
IS NOT NULL − The operator returns true if the column value is NOT NULL.
<⇒ − The operator compares values, which (unlike the = operator) is true even for two NULL values.

要查找 NULL 或 NOT NULL 的列，分别使用 IS NULL 或 IS NOT NULL。

To look for columns that are NULL or NOT NULL, use IS NULL or IS NOT NULL respectively.

Example

让我们考虑一个示例，其中有一个表 tcount_tbl ，它包含两列，作者和教程计数。我们可以向 tutorial_count 提供 NULL 值，表示作者甚至没有发布一个教程。因此，该相应作者的 tutorial_count 值为 NULL。

Let us consider an example where there is a table tcount_tbl that contains two columns, author and tutorial_count. We can provide NULL values to the tutorial_count indicates that the author did not publish even one tutorial. Therefore, the tutorial_count value for that respective author is NULL.

执行以下查询。

Execute the following queries.

create table tcount_tbl(author varchar(40) NOT NULL, tutorial_count INT);
INSERT INTO tcount_tbl values ('Abdul S', 20);
INSERT INTO tcount_tbl values ('Ajith kumar', 5);
INSERT INTO tcount_tbl values ('Jen', NULL);
INSERT INTO tcount_tbl values ('Bavya kanna', 8);
INSERT INTO tcount_tbl values ('mahran', NULL);
INSERT INTO tcount_tbl values ('John Poul', 10);
INSERT INTO tcount_tbl values ('Sathya Murthi', 6);

使用以下命令显示 tcount_tbl 表中的所有记录。

Use the following command to display all the records from the tcount_tbl table.

select * from tcount_tbl;

执行上述命令后，您将收到以下输出。

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

+-----------------+----------------+
|     author      | tutorial_count |
+-----------------+----------------+
|      Abdul S    |      20        |
|    Ajith kumar  |      5         |
|        Jen      |     NULL       |
|    Bavya kanna  |      8         |
|       mahran    |     NULL       |
|     John Poul   |      10        |
|   Sathya Murthi |      6         |
+-----------------+----------------+

要查找 tutorial_count 列为 NULL 的记录，以下是要执行的查询。

To find the records where the tutorial_count column IS NULL, following is the query.

SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL;

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

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

+-----------------+----------------+
|     author      | tutorial_count |
+-----------------+----------------+
|       Jen       |     NULL       |
|      mahran     |     NULL       |
+-----------------+----------------+

要查找 tutorial_count 列为 NOT NULL 的记录，以下是要执行的查询。

To find the records where the tutorial_count column IS NOT NULL, following is the query.

SELECT * FROM tcount_tbl WHERE tutorial_count IS NOT NULL;

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

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

+-----------------+----------------+
|      author     | tutorial_count |
+-----------------+----------------+
|      Abdul S    |      20        |
|     Ajith kumar |       5        |
|     Bavya kanna |       8        |
|     John Poul   |      10        |
|   Sathya Murthi |       6        |
+-----------------+----------------+

HSQLDB – JDBC Program

以下是一个 JDBC 程序，它从 tcount_tbl 表中分别检索 tutorial_count 为 NULL 和 tutorial_count 为 NOT NULL 的记录。将以下程序保存到 NullValues.java 。

Here is the JDBC program that retrieves the records separately from the table tcount_tbl where the tutorial_ count is NULL and the tutorial_count is NOT NULL. Save the following program into NullValues.java.

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

public class NullValues {
   public static void main(String[] args) {
      Connection con = null;
      Statement stmt_is_null = null;
      Statement stmt_is_not_null = null;
      ResultSet result = null;
      try {
         Class.forName("org.hsqldb.jdbc.JDBCDriver");
         con = DriverManager.getConnection(
            "jdbc:hsqldb:hsql://localhost/testdb", "SA", "");
         stmt_is_null = con.createStatement();
         stmt_is_not_null = con.createStatement();
         result = stmt_is_null.executeQuery(
            "SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL;");
         System.out.println("Records where the tutorial_count is NULL");

         while(result.next()){
            System.out.println(result.getString("author")+" |
            "+result.getInt("tutorial_count"));
         }
         result = stmt_is_not_null.executeQuery(
            "SELECT * FROM tcount_tbl WHERE tutorial_count IS NOT NULL;");
         System.out.println("Records where the tutorial_count is NOT NULL");

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

使用以下命令编译并执行以上程序。

Compile and execute the above program using the following command.

\>javac NullValues.java
\>Java NullValues

执行上述命令后，您将收到以下输出。

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

Records where the tutorial_count is NULL
Jen         | 0
mahran      | 0

Records where the tutorial_count is NOT NULL
Abdul S        | 20
Ajith kumar    | 5
Bavya kanna    | 8
John Poul      | 10
Sathya Murthi  | 6