Hsqldb 简明教程
HSQLDB - Introduction
HyperSQL 数据库 (HSQLDB) 是一款现代关系数据库管理器，完全符合 SQL:2011 标准和 JDBC 4 规范。它支持所有核心特性和 RDBMS。HSQLDB 用于开发、测试和部署数据库应用程序。
HyperSQL Database (HSQLDB) is a modern relational database manager that conforms closely to the SQL:2011 standard and JDBC 4 specifications. It supports all core features and RDBMS. HSQLDB is used for development, testing, and deployment of database applications.
HSQLDB 的主要和独特特性是标准合规性。它可以在用户应用程序流程中、应用程序服务器中或作为独立的服务器流程提供数据库访问。
The main and unique feature of HSQLDB is Standard Compliance. It can provide database access within the user’s application process, within an application server, or as a separate server process.
Features of HSQLDB
-
HSQLDB uses in-memory structure for fast operations against DB server. It uses disk persistence as per user flexibility, with a reliable crash recovery.
-
HSQLDB is also suitable for business intelligence, ETL, and other applications that process large data sets.
-
HSQLDB has a wide range of enterprise deployment options, such as XA transactions, connection pooling data sources, and remote authentication.
-
HSQLDB is written in the Java programming language and runs in a Java Virtual Machine (JVM). It supports the JDBC interface for database access.
Components of HSQLDB
HSQLDB jar 包中有三个不同的组件。
There are three different components in HSQLDB jar package.
-
HyperSQL RDBMS Engine (HSQLDB)
-
HyperSQL JDBC Driver
-
Database Manager (GUI database access tool, with Swing and AWT versions)
HyperSQL RDBMS 和 JDBC 驱动程序提供核心功能。数据库管理器是通用数据库访问工具，可与具有 JDBC 驱动程序的任何数据库引擎配合使用。
HyperSQL RDBMS and JDBC Driver provide the core functionality. Database Managers are general-purpose database access tools that can be used with any database engine having a JDBC driver.
一个名为 SqlTool 的附加 jar，即 sqltool.jar，它是一个命令行数据库访问工具。这是一个通用命令。行数据库访问工具，也可以与其他数据库引擎配合使用。
An additional jar called sqltool.jar, contains Sql Tool, which is a command line database access tool. This is a general purpose command. Line database access tool that can be used with other database engines as well.
HSQlDB - Installation
HSQLDB 是用纯 Java 实现的关系数据库管理系统。您可以使用 JDBC 轻松地将此数据库嵌入应用程序中。或者，您可以单独使用该操作。
HSQLDB is a relational database management system implemented in pure Java. You can easily embed this database to your application using JDBC. Or you can use the operations separately.
Prerequisites
按照 HSQLDB 的先决软件安装指南进行操作。
Follow the prerequisite software installations for HSQLDB.
Verify Java Installation
由于 HSQLDB 是使用纯 Java 实现的关系型数据库管理系统，因此您必须在安装 HSQLDB 之前安装 JDK（Java Development Kit）软件。如果您已经在系统中安装了 JDK，请尝试以下命令来验证 Java 版本。
Since HSQLDB is a relational database management system implemented in pure Java, you must install JDK (Java Development Kit) software before installing HSQLDB. If you already have JDK installation in your system, then try the following command to verify the Java version.
java –version如果 JDK 已成功安装在您的系统中，您将收到以下输出。
If JDK is successfully installed in your system, you will get the following output.
java version "1.8.0_91"
Java(TM) SE Runtime Environment (build 1.8.0_91-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.91-b14, mixed mode)如果您尚未在系统中安装 JDK，请访问以下链接 Install JDK.
If you don’t have JDK installed in your system, then visit the following link to Install JDK.
HSQLDB Installation
以下是安装 HSQLDB 的步骤。
Following are the steps to install HSQLDB.
Step 1 − Download HSQLDB bundle
Step 1 − Download HSQLDB bundle
从以下链接下载 HSQLDB 数据库的最新版本 https://sourceforge.net/projects/hsqldb/files/. ,一旦您单击该链接，您将看到以下屏幕截图。
Download the latest version of HSQLDB database from the following link https://sourceforge.net/projects/hsqldb/files/. Once you click the link, you will get the following screenshot.
单击 HSQLDB，下载将立即开始。最后，您将获得名为 hsqldb-2.3.4.zip 的 zip 文件。
Click HSQLDB and the download will start immediately. Finally, you will get the zip file named hsqldb-2.3.4.zip.
Step 2 − Extract the HSQLDB zip file
Step 2 − Extract the HSQLDB zip file
解压缩 zip 文件并将其放入 C:\ 目录。解压缩后，您将获得如下屏幕截图所示的文件结构。
Extract the zip file and place it into the C:\ directory. After extraction, you will get a file structure as shown in the following screenshot.
Step 3 − Create a default database
Step 3 − Create a default database
HSQLDB 没有默认数据库，因此，您需要为 HSQLDB 创建一个数据库。让我们创建一个名为 server.properties 的属性文件，该文件定义了一个名为 demodb 的新数据库。查看以下数据库服务器属性。
There is no default database for HSQLDB, therefore, you need to create a database for HSQLDB. Let us create a properties file named server.properties which defines a new database named demodb. Take a look at the following database server properties.
server.database.0 = file:hsqldb/demodb
server.dbname.0 = testdb将此 server.properties 文件放入 HSQLDB 主目录 C:\hsqldb- 2.3.4\hsqldb\ 中。
Place this server.properties file into HSQLDB home directory that is C:\hsqldb- 2.3.4\hsqldb\.
现在在命令提示符中执行以下命令。
Now execute the following command on command prompt.
\>cd C:\hsqldb-2.3.4\hsqldb
hsqldb>java -classpath lib/hsqldb.jar org.hsqldb.server.Server执行上述命令后，您将收到服务器状态，如下面的屏幕截图所示。
After execution of the above command, you will receive the server status as shown in the following screenshot.
稍后，您会在 HSQLDB 主目录中找到 hsqldb 目录的以下文件夹结构，即 C:\hsqldb-2.3.4\hsqldb 。这些文件是 HSQLDB 数据库服务器创建的 temp 文件、lck 文件、日志文件、属性文件和 demodb 数据库的脚本文件。
Later, you will get to find the following folder structure of the hsqldb directory in the HSQLDB home directory that is C:\hsqldb-2.3.4\hsqldb. Those files are temp file, lck file, log file, properties file, and script file of demodb database created by HSQLDB database server.
Step 4 − Start the database server
Step 4 − Start the database server
创建数据库后，您必须使用以下命令启动数据库。
Once you are done creating a database, you have to start the database by 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执行上述命令后，您将获得以下状态。
After execution of the above command, you get the following status.
现在，您可以打开 runManagerSwing.bat 从 C:\hsqldb-2.3.4\hsqldb\bin 位置的数据库主页。此 bat 文件将打开 HSQLDB 数据库的 GUI 文件。在此之前，它将通过一个对话框询问您数据库设置。请看以下截图。在此对话框中输入设置名称，URL 如上所示，然后单击确定。
Now, you can open the database home screen that is runManagerSwing.bat from C:\hsqldb-2.3.4\hsqldb\bin location. This bat file will open the GUI file for HSQLDB database. Before that it will ask you for database settings through a dialog box. Take a look at the following screenshot. In this dialog box, enter the Setting Name, URL as shown above and click Ok.
您将获得如下截图所示的 HSQLDB 数据库的 GUI 界面。
You will get the GUI screen of HSQLDB database as shown in the following screenshot.
HSQlDB - Connect
在安装章节中，我们讨论了如何手动连接数据库。在此章节中，我们将讨论如何以编程方式连接数据库（使用 Java 编程）。
In the installation chapter, we discussed how to connect the database manually. In this chapter, we will discuss how to connect the database programmatically (using Java programming).
看看以下程序，它将启动服务器并在 Java 应用程序和数据库之间建立连接。
Take a look at the following program, which will start the server and create a connection between the Java application and the database.
Example
import java.sql.Connection;
import java.sql.DriverManager;
public class ConnectDatabase {
public static void main(String[] args) {
Connection con = null;
try {
//Registering the HSQLDB JDBC driver
Class.forName("org.hsqldb.jdbc.JDBCDriver");
//Creating the connection with HSQLDB
con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/testdb", "SA", "");
if (con!= null){
System.out.println("Connection created successfully");
}else{
System.out.println("Problem with creating connection");
}
} catch (Exception e) {
e.printStackTrace(System.out);
}
}
}将此代码保存到 ConnectDatabase.java 文件中。您需要使用以下命令启动数据库。
Save this code into ConnectDatabase.java file. You will have to 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您可以使用以下命令编译并执行代码。
You can use the following command to compile and execute the code.
\>javac ConnectDatabase.java
\>java ConnectDatabase在执行上述命令之后，您将收到以下输出−
After execution of the above command, you will receive the following output −
Connection created successfullyHSQLDB - Data Types
此章节解释了 HSQLDB 的不同数据类型。HSQLDB 服务器提供了六类数据类型。
This chapter explains the different datatypes of HSQLDB. HSQLDB server offers six categories of data types.
Exact Numeric Data Types
Data Type |
From |
To |
bigint |
-9,223,372,036,854,775,808 |
9,223,372,036,854,775,807 |
int |
-2,147,483,648 |
2,147,483,647 |
smallint |
-32,768 |
32,767 |
tinyint |
0 |
255 |
bit |
0 |
1 |
decimal |
-10^38 +1 |
10^38 -1 |
numeric |
-10^38 +1 |
10^38 -1 |
money |
-922,337,203,685,477.5808 |
+922,337,203,685,477.5807 |
smallmoney |
-214,748.3648 |
+214,748.3647 |
Approximate Numeric Data Types
Data Type |
From |
To |
float |
-1.79E + 308 |
1.79E + 308 |
real |
-3.40E + 38 |
3.40E + 38 |
Date and Time Data Types
Data Type |
From |
To |
datetime |
Jan 1, 1753 |
Dec 31, 9999 |
smalldatetime |
Jan 1, 1900 |
Jun 6, 2079 |
date |
Stores a date like June 30, 1991 |
time |
Note - 此处，datetime 的精度为 3.33 毫秒，而 small datetime 的精度为 1 分钟。
Note − Here, datetime has 3.33 milliseconds accuracy whereas small datetime has 1- minute accuracy.
Character Strings Data Types
Data Type |
Description |
char |
Maximum length of 8,000 characters (Fixed length non-Unicode characters) |
varchar |
Maximum of 8,000 characters (Variable-length non-Unicode data) |
varchar(max) |
Maximum length of 231 characters, variable-length non-Unicode data (SQL Server 2005 only) |
text |
Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters |
Unicode Character Strings Data Types
Data Type |
Description |
nchar |
Maximum length of 4,000 characters (Fixed length Unicode) |
nvarchar |
Maximum length of 4,000 characters (Variable length Unicode) |
nvarchar(max) |
Maximum length of 231 characters (SQL Server 2005 only),(Variable length Unicode) |
ntext |
Maximum length of 1,073,741,823 characters (Variable length Unicode) |
Binary Data Types
Data Type |
Description |
binary |
Maximum length of 8,000 bytes (Fixed length binary data) |
varbinary |
Maximum length of 8,000 bytes (Variable length binary data) |
varbinary(max) |
Maximum length of 231 bytes (SQL Server 2005 only), (Variable length Binary data) |
image |
Maximum length of 2,147,483,647 bytes (Variable length Binary Data) |
Misc Data Types
Data Type |
Description |
sql_variant |
Stores values of various SQL Server-supported data types, except text, ntext, and timestamp |
timestamp |
Stores a database-wide unique number that gets updated every time a row gets updated |
uniqueidentifier |
Stores a globally unique identifier (GUID) |
xml |
Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only) |
cursor |
Reference to a cursor object |
table |
Stores a result set for later processing |
HSQLDB - Create Table
创建表的基本强制性要求是表名、字段名和这些字段的数据类型。此外，您还可以向表提供键约束。
The basic mandatory requirements to create a table are table name, field names, and the data types to those fields. Optionally, you can also provide the key constraints to the table.
Syntax
看一下以下语法。
Take a look at the following syntax.
CREATE TABLE table_name (column_name column_type);Example
我们创建一个名为 tutorials_tbl 的表，其字段名称为 id、title、author 和 submission_date。看一下以下查询。
Let us create a table named tutorials_tbl with the field-names such as id, title, author, and submission_date. Take a look at the following query.
CREATE TABLE tutorials_tbl (
id INT NOT NULL,
title VARCHAR(50) NOT NULL,
author VARCHAR(20) NOT NULL,
submission_date DATE,
PRIMARY KEY (id)
);执行以上查询后，您将收到以下输出 −
After execution of the above query, you will receive the following output −
(0) rows effectedHSQLDB – JDBC Program
以下是用于在 HSQLDB 数据库中创建一个名为 tutorials_tbl 的表的 JDBC 程序。将程序保存到 CreateTable.java 文件中。
Following is the JDBC program used to create a table named tutorials_tbl into the HSQLDB database. Save the program into CreateTable.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class CreateTable {
public static void main(String[] args) {
Connection con = null;
Statement stmt = null;
int result = 0;
try {
Class.forName("org.hsqldb.jdbc.JDBCDriver");
con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/testdb", "SA", "");
stmt = con.createStatement();
result = stmt.executeUpdate("CREATE TABLE tutorials_tbl (
id INT NOT NULL, title VARCHAR(50) NOT NULL,
author VARCHAR(20) NOT NULL, submission_date DATE,
PRIMARY KEY (id));
");
} catch (Exception e) {
e.printStackTrace(System.out);
}
System.out.println("Table created successfully");
}
}您可以使用以下命令启动数据库。
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 program using the following command.
\>javac CreateTable.java
\>java CreateTable在执行上述命令之后，您将收到以下输出−
After execution of the above command, you will receive the following output −
Table created successfullyHSQLDB - Drop Table
删除一个现有的 HSQLDB 表非常容易。但是，在删除任何现有的表时，您需要非常小心，因为删除表后任何丢失的数据都无法恢复。
It is very easy to drop an existing HSQLDB table. However, you need to be very careful while deleting any existing table as any data lost will not be recovered after deleting a table.
Syntax
以下是用来删除 HSQLDB 表的通用 SQL 语法。
Following is a generic SQL syntax to drop a HSQLDB table.
DROP TABLE table_name;Example
我们考虑一个示例，从 HSQLDB 服务器删除名为 employee 的表。以下是用来删除名为 employee 的表的查询。
Let us consider an example to drop a table named employee from the HSQLDB server. Following is the query to drop a table named employee.
DROP TABLE employee;执行以上查询后，您将收到以下输出 −
After execution of the above query, you will receive the following output −
(0) rows effectedHSQLDB – JDBC Program
以下是用于从 HSQLDB 服务器删除名为 employee 的表的 JDBC 程序。
Following is the JDBC program used to drop the table employee from the HSQLDB server.
将以下代码另存为 DropTable.java 文件。
Save the following code into DropTable.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class DropTable {
public static void main(String[] args) {
Connection con = null;
Statement stmt = null;
int result = 0;
try {
Class.forName("org.hsqldb.jdbc.JDBCDriver");
con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/testdb", "SA", "");
stmt = con.createStatement();
result = stmt.executeUpdate("DROP TABLE employee");
}catch (Exception e) {
e.printStackTrace(System.out);
}
System.out.println("Table dropped successfully");
}
}您可以使用以下命令启动数据库。
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 program using the following command.
\>javac DropTable.java
\>java DropTable在执行上述命令之后，您将收到以下输出−
After execution of the above command, you will receive the following output −
Table dropped successfullyHSQLDB - Insert Query
可以使用 INSERT INTO 命令在 HSQLDB 中执行插入查询语句。你必须按照表中列字段的顺序提供用户定义的数据。
You can achieve Insert query statement in HSQLDB by using the INSERT INTO command. You have to provide the user-defined data following the column field order from the table.
Syntax
以下是 INSERT 查询的一般语法。
Following is the generic syntax to INSERT a query.
INSERT INTO table_name (field1, field2,...fieldN)
VALUES (value1, value2,...valueN );若要将字符串类型数据插入表中，你必须使用双引号或单引号在插入查询语句中提供字符串值。
To insert a string type data into a table, you will have to use double or single quotes to provide string value into the insert query statement.
Example
我们考虑一个示例，其中将记录插入一个名为 tutorials_tbl 的表中，其中值 id = 100、title = Learn PHP、Author = John Poul，并且提交日期是当前日期。
Let us consider an example that inserts a record into a table named tutorials_tbl with the values id = 100, title = Learn PHP, Author = John Poul, and the submission date is current date.
以下是给定示例的查询。
Following is the query for the given example.
INSERT INTO tutorials_tbl VALUES (100,'Learn PHP', 'John Poul', NOW());执行以上查询后，您将收到以下输出 −
After execution of the above query, you will receive the following output −
1 row effectedHSQLDB – JDBC Program
以下是使用给定值（id =100、title = Learn PHP、Author = John Poul 且提交日期为当前日期）将记录插入表中的 JDBC 程序。查看给定程序。将代码另存为 InserQuery.java 文件。
Here is the JDBC program to insert the record into the table with the given values, id =100, title = Learn PHP, Author = John Poul, and the submission date is current date. Take a look at the given program. Save the code into the InserQuery.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class InsertQuery {
public static void main(String[] args) {
Connection con = null;
Statement stmt = null;
int result = 0;
try {
Class.forName("org.hsqldb.jdbc.JDBCDriver");
con = DriverManager.getConnection(
"jdbc:hsqldb:hsql://localhost/testdb", "SA", "");
stmt = con.createStatement();
result = stmt.executeUpdate("INSERT INTO tutorials_tbl
VALUES (100,'Learn PHP', 'John Poul', NOW())");
con.commit();
}catch (Exception e) {
e.printStackTrace(System.out);
}
System.out.println(result+" rows effected");
System.out.println("Rows inserted successfully");
}
}您可以使用以下命令启动数据库。
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 program using the following command.
\>javac InsertQuery.java
\>java InsertQuery在执行上述命令之后，您将收到以下输出−
After execution of the above command, you will receive the following output −
1 rows effected
Rows inserted successfully尝试使用 INSERT INTO 命令将以下记录插入到 tutorials_tbl 表中。
Try to insert the following records into the tutorials_tbl table by using the INSERT INTO command.
Id |
Title |
Author |
Submission Date |
101 |
Learn C |
Yaswanth |
Now() |
102 |
Learn MySQL |
Abdul S |
Now() |
103 |
Learn Excell |
Bavya kanna |
Now() |
104 |
Learn JDB |
Ajith kumar |
Now() |
105 |
Learn Junit |
Sathya Murthi |
Now() |
HSQLDB - Select Query
SELECT 命令用于从 HSQLDB 数据库中获取记录数据。在此处，你需要在 Select 语句中提及所需字段列表。
The SELECT command is used to fetch the record data from HSQLDB database. Here, you need to mention the required fields list in the Select statement.
Syntax
以下是 Select 查询的一般语法。
Here is the generic syntax for Select query.
SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]-
You can fetch one or more fields in a single SELECT command.
-
You can specify star (*) in place of fields. In this case, SELECT will return all the fields.
-
You can specify any condition using WHERE clause.
-
You can specify an offset using OFFSET from where SELECT will start returning records. By default, offset is zero.
-
You can limit the number of returns using LIMIT attribute.
Example
以下是一个示例，其中获取 tutorials_tbl 表中所有记录的 id、title 和 author 字段。我们可以使用 SELECT 语句来实现该操作。以下是示例的查询。
Here is an example that fetches id, title, and author fields of all records from tutorials_tbl table. We can achieve this by using the SELECT statement. Following is the query for the example.
SELECT id, title, author FROM tutorials_tbl在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+------+----------------+-----------------+
| id | title | author |
+------+----------------+-----------------+
| 100 | Learn PHP | John Poul |
| 101 | Learn C | Yaswanth |
| 102 | Learn MySQL | Abdul S |
| 103 | Learn Excell | Bavya kanna |
| 104 | Learn JDB | Ajith kumar |
| 105 | Learn Junit | Sathya Murthi |
+------+----------------+-----------------+HSQLDB – JDBC Program
以下是一个 JDBC 程序，其中将获取 tutorials_tbl 表中所有记录的 id、title 和 author 字段。将以下代码另存为 SelectQuery.java 文件。
Here is the JDBC program that will fetch id, title, and author fields of all records from tutorials_tbl table. Save the following code into the SelectQuery.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SelectQuery {
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 id, title, author FROM tutorials_tbl");
while(result.next()){
System.out.println(result.getInt("id")+" | "+
result.getString("title")+" | "+
result.getString("author"));
}
} 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 SelectQuery.java
\>java SelectQuery在执行上述命令之后，您将收到以下输出−
After execution of the above command, you will receive the following output −
100 | Learn PHP | John Poul
101 | Learn C | Yaswanth
102 | Learn MySQL | Abdul S
103 | Learn Excell | Bavya Kanna
104 | Learn JDB | Ajith kumar
105 | Learn Junit | Sathya MurthiHSQLDB - Where Clause
通常，我们使用 SELECT 命令从 HSQLDB 表中获取数据。我们可以使用 WHERE 条件子句来过滤结果数据。使用 WHERE，我们可以指定选择条件以从表中选择所需的记录。
Generally, we use SELECT command to fetch data from HSQLDB table. We can use WHERE conditional clause to filter the resultant data. Using WHERE we can specify the selection criteria to select the required records from a table.
Syntax
以下是 SELECT 命令 WHERE 子句的语法，用于从 HSQLDB 表中获取数据。
Following is the syntax of SELECT command WHERE clause to fetch data from HSQLDB table.
SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....-
You can use one or more tables separated by comma to include various conditions using a WHERE clause, but WHERE clause is an optional part of SELECT command.
-
You can specify any condition using WHERE clause.
-
You can specify more than one conditions using AND or OR operators.
-
A WHERE clause can also be used along with DELETE or UPDATE SQL command to specify a condition.
我们可以使用条件来过滤记录数据。我们正在条件 WHERE 子句中使用不同的运算符。此处的运算符列表可与 WHERE 子句一起使用。
We can filter the record data by using conditions. We are using different operators in conditional WHERE clause. Here is the list of operators, which can be used with WHERE clause.
Operator |
Description |
Example |
= |
Checks if the values of two operands are equal or not, if yes then the condition becomes true. |
(A = B) is not true |
!= |
Checks if the values of two operands are equal or not, if values are not equal then the condition becomes true. |
(A != B) is true |
> |
Checks if the value of the left operand is greater than the value of the right operand, if yes then the condition becomes true. |
(A > B) is not true |
< |
Checks if the value of the left operand is less than the value of the right operand, if yes then the condition becomes true. |
(A < B) is true |
>= |
Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes then the condition becomes true. |
(A >= B) is not true |
⇐ |
Checks if the value of the left operand is less than or equal to the value of the right operand, if yes then the condition becomes true. |
(A ⇐ B) is true |
Example
这里有一个示例，该示例检索有关 ID、标题以及标题为“Learn C”一书的作者的详细信息。这可以通过在 SELECT 命令中使用 WHERE 子句来实现。以下是查询。
Here is an example that retrieves the details such as id, title, and the author of the book titled "Learn C". It is possible by using WHERE clause in the SELECT command. Following is the query for the same.
SELECT id, title, author FROM tutorials_tbl WHERE title = 'Learn C';在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+------+----------------+-----------------+
| id | title | author |
+------+----------------+-----------------+
| 101 | Learn C | Yaswanth |
+------+----------------+-----------------+HSQLDB – JDBC Program
以下 JDBC 程序从表 tutorials_tblhaving 标题 Learn C 中检索记录数据。将以下代码保存到 WhereClause.java 中。
Here is the JDBC program that retrieves the record data from the table tutorials_tblhaving the title Learn C. Save the following code into WhereClause.java.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class WhereClause {
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 id, title, author FROM tutorials_tbl
WHERE title = 'Learn C'");
while(result.next()){
System.out.println(result.getInt("id")+" |
"+result.getString("title")+" |
"+result.getString("author"));
}
} 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 WhereClause.java
\>java WhereClause执行上述命令后，您将收到以下输出。
After execution of the above command, you will receive the following output.
101 | Learn C | YaswanthHSQLDB - Update Query
每当您要修改表中的值时，都可以使用 UPDATE 命令。这将修改任何 HSQLDB 表中的任何字段值。
Whenever you want to modify the values of a table, you can use the UPDATE command. This will modify any field value from any HSQLDB table.
Syntax
以下是 UPDATE 命令的通用语法。
Here is the generic syntax for UPDATE command.
UPDATE table_name SET field1 = new-value1, field2 = new-value2 [WHERE Clause]-
You can update one or more field altogether.
-
You can specify any condition using WHERE clause.
-
You can update values in a single table at a time.
Example
让我们考虑一个示例，该示例将教程的标题从“学习 C”更新为“C 和数据结构”，其 ID 为“101”。以下是更新的查询。
Let us consider an example that updates the title of the tutorial from "Learn C" to "C and Data Structures" having an id "101". Following is the query for the update.
UPDATE tutorials_tbl SET title = 'C and Data Structures' WHERE id = 101;在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
(1) Rows effectedHSQLDB – JDBC Program
以下是 JDBC 程序，该程序将教程标题从 Learn C 更新为 C and Data Structures ，其 ID 为 101 。将以下程序保存到 UpdateQuery.java 文件中。
Here is the JDBC program that will update a tutorial title from Learn C to C and Data Structures having an id 101. Save the following program into the UpdateQuery.java file.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class UpdateQuery {
public static void main(String[] args) {
Connection con = null;
Statement stmt = null;
int result = 0;
try {
Class.forName("org.hsqldb.jdbc.JDBCDriver");
con = DriverManager.getConnection(
"jdbc:hsqldb:hsql://localhost/testdb", "SA", "");
stmt = con.createStatement();
result = stmt.executeUpdate(
"UPDATE tutorials_tbl SET title = 'C and Data Structures' WHERE id = 101");
} catch (Exception e) {
e.printStackTrace(System.out);
}
System.out.println(result+" Rows effected");
}
}您可以使用以下命令启动数据库。
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 program using the following command.
\>javac UpdateQuery.java
\>java UpdateQuery在执行上述命令之后，您将收到以下输出−
After execution of the above command, you will receive the following output −
1 Rows effectedHSQLDB - Delete Clause
每当您希望从任何 HSQLDB 表中删除记录时，都可以使用 DELETE FROM 命令。
Whenever you want to delete a record from any HSQLDB table, you can use the DELETE FROM command.
Syntax
以下是 DELETE 命令用于从 HSQLDB 表中删除数据的通用语法。
Here is the generic syntax for DELETE command to delete data from a HSQLDB table.
DELETE FROM table_name [WHERE Clause]-
If WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
-
You can specify any condition using WHERE clause.
-
You can delete records in a single table at a time.
Example
让我们考虑一个示例，该示例从名为 tutorials_tbl 的表中删除记录数据，其 ID 为 105 。以下是实现给定示例的查询。
Let us consider an example that deletes the record data from the table named tutorials_tbl having id 105. Following is the query that implements the given example.
DELETE FROM tutorials_tbl WHERE id = 105;执行以上查询后，您将收到以下输出 −
After execution of the above query, you will receive the following output −
(1) rows effectedHSQLDB – JDBC Program
以下是实现给定示例的 JDBC 程序。将以下程序保存为 DeleteQuery.java 。
Here is the JDBC program that implements the given example. Save the following program into DeleteQuery.java.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class DeleteQuery {
public static void main(String[] args) {
Connection con = null;
Statement stmt = null;
int result = 0;
try {
Class.forName("org.hsqldb.jdbc.JDBCDriver");
con = DriverManager.getConnection(
"jdbc:hsqldb:hsql://localhost/testdb", "SA", "");
stmt = con.createStatement();
result = stmt.executeUpdate(
"DELETE FROM tutorials_tbl WHERE id=105");
} catch (Exception e) {
e.printStackTrace(System.out);
}
System.out.println(result+" Rows effected");
}
}您可以使用以下命令启动数据库。
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 program using the following command.
\>javac DeleteQuery.java
\>java DeleteQuery在执行上述命令之后，您将收到以下输出−
After execution of the above command, you will receive the following output −
1 Rows effectedHSQLDB - 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-20HSQLDB - Sorting Results
SQL SELECT命令从HSQLDB表中获取数据，只要检索和显示记录时有遵循特定顺序的要求。在这种情况下，我们可以使用 ORDER BY 子句。
The SQL SELECT command fetches data from the HSQLDB table whenever there is a requirement that follows a particular order while retrieving and displaying records. In that case, we can use the ORDER BY clause.
Syntax
以下是SELECT命令的语法，以及ORDER BY子句，用于对HSQLDB中的数据进行排序。
Here is the syntax of the SELECT command along with ORDER BY clause to sort data from HSQLDB.
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]-
You can sort the returned result on any field provided that field is being listed out.
-
You can sort the result on more than one field.
-
You can use the keyword ASC or DESC to get the result in an ascending or descending order. By default, it’s in an ascending order.
-
You can use the WHERE…LIKE clause in a usual way to put a condition.
Example
让我们考虑一个例子，通过按升序排列作者姓名来获取并排序 tutorials_tbl 表中的记录。以下是相同的查询。
Let us consider an example that fetches and sorts the records of tutorials_tbl table by ordering the author name in an ascending order. Following is the query for the same.
SELECT id, title, author from tutorials_tbl ORDER BY author ASC;在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+------+----------------+-----------------+
| id | title | author |
+------+----------------+-----------------+
| 102 | Learn MySQL | Abdul S |
| 104 | Learn JDB | Ajith kumar |
| 103 | Learn Excell | Bavya kanna |
| 100 | Learn PHP | John Poul |
| 105 | Learn Junit | Sathya Murthi |
| 101 | Learn C | Yaswanth |
+------+----------------+-----------------+HSQLDB – JDBC Program
以下是通过按升序排列作者姓名来获取并排序 tutorials_tbl 表中的记录的JDBC程序。将以下程序保存到 OrderBy.java 。
Here is the JDBC program that fetches and sorts the records of tutorials_tbl table by ordering the author name in an ascending order. Save the following program into OrderBy.java.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class OrderBy {
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 id, title, author from tutorials_tbl
ORDER BY author ASC");
while(result.next()){
System.out.println(result.getInt("id")+" |
"+result.getString("title")+" |
"+result.getString("author"));
}
} 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 program using the following command.
\>javac OrderBy.java
\>java OrderBy执行上述命令后，您将收到以下输出。
After execution of the above command, you will receive the following output.
102 | Learn MySQL | Abdul S
104 | Learn JDB | Ajith kumar
103 | Learn Excell | Bavya Kanna
100 | Learn PHP | John Poul
105 | Learn Junit | Sathya Murthi
101 | C and Data Structures | YaswanthHSQLDB - Joins
每当需要使用单个查询从多个表中检索数据时，您可以使用RDBMS中的JOINS。可以在单个SQL查询中使用多个表。在HSQLDB中连接操作是指将两个或更多个表合并成一个表。
Whenever there is a requirement to retrieve data from multiple tables using a single query, you can use JOINS from RDBMS. You can use multiple tables in your single SQL query. The act of joining in HSQLDB refers to smashing two or more tables into a single table.
考虑以下Customers和Orders表。
Consider the following Customers and Orders tables.
Customer:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Orders:
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+现在，让我们尝试检索客户数据和相应客户下的订单金额。这意味着我们正在从customers表和orders表中检索记录数据。我们可以通过使用HSQLDB中的JOINS概念来实现此目的。以下是相同的JOIN查询。
Now, let us try to retrieve the data of the customers and the order amount that the respective customer placed. This means we are retrieving the record data from both customers and orders table. We can achieve this by using the JOINS concept in HSQLDB. Following is the JOIN query for the same.
SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS, ORDERS WHERE CUSTOMERS.ID =
ORDERS.CUSTOMER_ID;在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+----+----------+-----+--------+
| ID | NAME | AGE | AMOUNT |
+----+----------+-----+--------+
| 3 | kaushik | 23 | 3000 |
| 3 | kaushik | 23 | 1500 |
| 2 | Khilan | 25 | 1560 |
| 4 | Chaitali | 25 | 2060 |
+----+----------+-----+--------+JOIN Types
HSQLDB中提供不同类型的连接。
There are different types of joins available in HSQLDB.
-
INNER JOIN − Returns the rows when there is a match in both tables.
-
LEFT JOIN − Returns all rows from the left table, even if there are no matches in the right table.
-
RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the left table.
-
FULL JOIN − Returns the rows when there is a match in one of the tables.
-
SELF JOIN − Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
Inner Join
最常用且最重要的连接是 INNER JOIN。它也被称为 EQUIJOIN（等值连接）。
The most frequently used and important of the joins is the INNER JOIN. It is also referred to as an EQUIJOIN.
INNER JOIN 基于连接谓词通过组合两张表（table1 和 table2）中的列值创建新的结果表。查询会比较 table1 中的每行与 table2 中的每行，以找到满足连接谓词的所有行对。当满足连接谓词时，将每个匹配行对 A 和 B 的列值组合成一行结果。
The INNER JOIN creates a new result table by combining the column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows, which satisfy the join-predicate. When the join-predicate is satisfied, the column values for each matched pair of rows A and B are combined into a result row.
Syntax
INNER JOIN 的基本语法如下。
The basic syntax of INNER JOIN is as follows.
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;Example
考虑以下两张表，一张称为 CUSTOMERS 表，另一张称为 ORDERS 表，如下所示：
Consider the following two tables, one titled as CUSTOMERS table and another titled as ORDERS table as follows −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------++-----+---------------------+-------------+--------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+现在，让我们使用 INNER JOIN 查询连接这两张表，如下所示：
Now, let us join these two tables using INNER JOIN query as follows −
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+----+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+---------------------+
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+Left Join
HSQLDB LEFT JOIN 会返回左表中的所有行，即使右表中没有匹配项。这意味着如果 ON 子句与右表中的记录匹配 0（零）条，连接仍会在结果中返回一行，但右表中的每列都是 NULL。
The HSQLDB LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table, the join will still return a row in the result, but with NULL in each column from the right table.
这意味着左连接会返回左表中的所有值，加上右表中的匹配值或在没有匹配连接谓词的情况下返回 NULL。
This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.
Syntax
LEFT JOIN 的基本语法如下：
The basic syntax of LEFT JOIN is as follows −
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;这里给定的条件可以是根据您的要求给出的任何表达式。
Here the given condition could be any given expression based on your requirement.
Example
考虑以下两张表，一张称为 CUSTOMERS 表，另一张称为 ORDERS 表，如下所示：
Consider the following two tables, one titled as CUSTOMERS table and another titled as ORDERS table as follows −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------++-----+---------------------+-------------+--------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+现在，让我们使用 LEFT JOIN 查询连接这两张表，如下所示：
Now, let us join these two tables using the LEFT JOIN query as follows −
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;执行以上查询后，您将收到以下输出 −
After execution of the above query, you will receive the following output −
+----+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
+----+----------+--------+---------------------+Right Join
HSQLDB RIGHT JOIN 会返回右表中的所有行，即使左表中没有匹配项。这意味着如果 ON 子句与左表中的记录匹配 0（零）条，连接仍会在结果中返回一行，但左表中的每列都是 NULL。
The HSQLDB RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in the left table, the join will still return a row in the result, but with NULL in each column from the left table.
这意味着右连接会返回右表中的所有值，加上左表中的匹配值或在没有匹配连接谓词的情况下返回 NULL。
This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.
Syntax
RIGHT JOIN 的基本语法如下：
The basic syntax of RIGHT JOIN is as follows −
SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;Example
考虑以下两张表，一张称为 CUSTOMERS 表，另一张称为 ORDERS 表，如下所示：
Consider the following two tables, one titled as CUSTOMERS table and another titled as ORDERS table as follows −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------++-----+---------------------+-------------+--------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+现在，让我们使用 RIGHT JOIN 查询连接这两张表，如下所示：
Now, let us join these two tables using the RIGHT JOIN query as follows −
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;执行以上查询后，您将收到以下结果。
After execution of the above query, you will receive the following result.
+------+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+---------------------+
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+Full Join
HSQLDB FULL JOIN 将左外连接和右外连接的结果组合在一起。
The HSQLDB FULL JOIN combines the results of both left and right outer joins.
连接的表将包含两张表中的所有记录，并在两边填入缺少匹配项的 NULL。
The joined table will contain all records from both tables, and fill in NULLs for the missing matches on either side.
Syntax
FULL JOIN 的基本语法如下：
The basic syntax of FULL JOIN is as follows −
SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;这里给定的条件可以是根据您的要求给出的任何表达式。
Here the given condition could be any given expression based on your requirement.
Example
考虑以下两张表，一张称为 CUSTOMERS 表，另一张称为 ORDERS 表，如下所示：
Consider the following two tables, one titled as CUSTOMERS table and another titled as ORDERS table as follows −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------++-----+---------------------+-------------+--------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+现在，让我们使用 FULL JOIN 查询连接这两张表，如下所示：
Now, let us join these two tables using the FULL JOIN query as follows −
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;执行以上查询后，您将收到以下结果。
After execution of the above query, you will receive the following result.
+------+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+Self Join
SQL 自联接用于将一个表连接到它自身，就像该表是两个表一样，在 SQL 语句中临时重命名至少一个表。
The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
Syntax
SELF JOIN 的基本语法如下 −
The basic syntax of SELF JOIN is as follows −
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;这里，WHERE 子句可以是基于您要求的任何给定表达式。
Here, the WHERE clause could be any given expression based on your requirement.
Example
考虑以下两张表，一张称为 CUSTOMERS 表，另一张称为 ORDERS 表，如下所示：
Consider the following two tables, one titled as CUSTOMERS table and another titled as ORDERS table as follows −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+现在，我们使用 SELF JOIN 查询如下连接此表 −
Now, let us join this table using the SELF JOIN query as follows −
SELECT a.ID, b.NAME, a.SALARY FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY > b.SALARY;执行以上查询后，您将收到以下输出 −
After execution of the above query, you will receive the following output −
+----+----------+---------+
| ID | NAME | SALARY |
+----+----------+---------+
| 2 | Ramesh | 1500.00 |
| 2 | kaushik | 1500.00 |
| 1 | Chaitali | 2000.00 |
| 2 | Chaitali | 1500.00 |
| 3 | Chaitali | 2000.00 |
| 6 | Chaitali | 4500.00 |
| 1 | Hardik | 2000.00 |
| 2 | Hardik | 1500.00 |
| 3 | Hardik | 2000.00 |
| 4 | Hardik | 6500.00 |
| 6 | Hardik | 4500.00 |
| 1 | Komal | 2000.00 |
| 2 | Komal | 1500.00 |
| 3 | Komal | 2000.00 |
| 1 | Muffy | 2000.00 |
| 2 | Muffy | 1500.00 |
| 3 | Muffy | 2000.00 |
| 4 | Muffy | 6500.00 |
| 5 | Muffy | 8500.00 |
| 6 | Muffy | 4500.00 |
+----+----------+---------+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 | 6HSQLDB - Regular Expressions
HSQLDB 支持一些特殊符号，用于基于正则表达式和 REGEXP 运算符进行模式匹配操作。
HSQLDB supports some special symbols for pattern matching operation based on regular expressions and the REGEXP operator.
以下是模式表，可与 REGEXP 运算符一起使用。
Following is the table of pattern, which can be used along with REGEXP operator.
Pattern |
What the Pattern Matches |
^ |
Beginning of the string |
$ |
End of the string |
. |
Any single character |
[…] |
Any character listed between the square brackets |
[^…] |
Any character not listed between the square brackets |
p1 |
p2 |
p3 |
Alternation; matches any of the patterns p1, p2, or p3 |
* |
Zero or more instances of the preceding element |
+ |
One or more instances of the preceding element |
{n} |
n instances of the preceding element |
{m,n} |
m through n instances of the preceding element |
Example
让我们尝试不同的示例查询来满足我们的要求。查看以下给定的查询。
Let us try different example queries to meet our requirements. Take a look at the following given queries.
试用此查询以查找所有名称以“^A”开头的作者。
Try this Query to find all the authors whose name starts with '^A'.
SELECT author FROM tcount_tbl WHERE REGEXP_MATCHES(author,'^A.*');在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+-----------------+
| author |
+-----------------+
| Abdul S |
| Ajith kumar |
+-----------------+试用此查询以查找所有名称以“ul$”结尾的作者。
Try this Query to find all the authors whose name ends with 'ul$'.
SELECT author FROM tcount_tbl WHERE REGEXP_MATCHES(author,'.*ul$');在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+-----------------+
| author |
+-----------------+
| John Poul |
+-----------------+试用此查询以查找所有名称包含“th”的作者。
Try this Query to find all the authors whose name contains 'th'.
SELECT author FROM tcount_tbl WHERE REGEXP_MATCHES(author,'.*th.*');在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+-----------------+
| author |
+-----------------+
| Ajith kumar |
| Abdul S |
+-----------------+试用此查询以查找所有名称以元音（a、e、i、o、u）开头的作者。
Try this query to find all the authors whose name starts with vowel (a, e, i, o, u).
SELECT author FROM tcount_tbl WHERE REGEXP_MATCHES(author,'^[AEIOU].*');在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+-----------------+
| author |
+-----------------+
| Abdul S |
| Ajith kumar |
+-----------------+HSQLDB - Transactions
Transaction 是数据库操作的顺序组，它被执行并认为是一个单一的工作单元。换句话说，当所有操作都成功执行时，整个事务才会完成。如果事务中的任何操作失败，那么整个事务将会失败。
A Transaction is a sequential group of database manipulation operations, which is performed and considered as one single work unit. In other words, when all the operations are successfully executed, only then the entire transaction will be complete. If any operation within the transaction fails, then the entire transaction will fail.
Properties of Transactions
基本上，事务支持 4 个标准属性。它们可以称为 ACID 属性。
Basically, transaction supports 4 standard properties. They can be referred to as ACID properties.
Atomicity − 事务中的所有操作都成功执行，否则事务将在失败时中止，并且先前的操作回滚到它们先前的状态。
Atomicity − All the operations in the transactions are executed successfully, otherwise the transaction gets aborted at the point of failure and the previous operations are rolled back to their previous position.
Consistency − 成功提交事务后，数据库将适当地更改状态。
Consistency − The database properly changes states upon a successfully committed transaction.
Isolation − 它使事务能够独立操作，彼此透明。
Isolation − It enables the transaction to operate independently on and transparent to each other.
Durability − 在系统故障的情况下，已提交事务的结果或影响会持久存在。
Durability − The result or effect of a committed transaction persists in case of a system failure.
Commit, Rollback, and Savepoint
这些关键字主要用于 HSQLDB 事务。
These keywords are mainly used for HSQLDB transactions.
Commit − 始终应通过执行 COMMIT 命令来完成成功的的事务。
Commit− Always the successful transaction should be completed by executing the COMMIT command.
Rollback − 如果事务中出现故障，则应执行 ROLLBACK 命令，以便将事务中引用的所有表返回到其先前状态。
Rollback − If a failure occurs in the transaction, then the ROLLBACK command should be executed to return every table referenced in the transaction to its previous state.
Savepoint - 在交易组内创建一个回滚点。
Savepoint − Creates a point within the group of transactions in which to rollback.
Example
下面这个示例将解释交易的概念以及提交、回滚和保存点。让我们考虑一下表 Customers 以及列 id、name、age、address 和 salary。
The following example explains the transactions concept along with commit, rollback, and Savepoint. Let us consider the table Customers with the columns id, name, age, address, and salary.
Id |
Name |
Age |
Address |
Salary |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Karun |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitanya |
25 |
Mumbai |
6500.00 |
5 |
Harish |
27 |
Bhopal |
8500.00 |
6 |
Kamesh |
22 |
MP |
1500.00 |
7 |
Murali |
24 |
Indore |
10000.00 |
使用下列命令创建与上述数据相类似的客户表。
Use the following commands to create the customer table along the lines of the above data.
CREATE TABLE Customer (id INT NOT NULL, name VARCHAR(100) NOT NULL, age INT NOT
NULL, address VARCHAR(20), Salary INT, PRIMARY KEY (id));
Insert into Customer values (1, "Ramesh", 32, "Ahmedabad", 2000);
Insert into Customer values (2, "Karun", 25, "Delhi", 1500);
Insert into Customer values (3, "Kaushik", 23, "Kota", 2000);
Insert into Customer values (4, "Chaitanya", 25, "Mumbai", 6500);
Insert into Customer values (5, "Harish", 27, "Bhopal", 8500);
Insert into Customer values (6, "Kamesh", 22, "MP", 1500);
Insert into Customer values (7, "Murali", 24, "Indore", 10000);Example for COMMIT
下面这个查询会删除表中 age = 25 的行，并且使用 COMMIT 命令将这些改动应用至数据库。
The following query deletes rows from the table having age = 25 and uses the COMMIT command to apply those changes in the database.
DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
2 rows effected在上述命令成功执行后，通过执行下面给出的命令检查客户表的记录。
After successful execution of the above command, check the records of the customer table by executing the below given command.
Select * from Customer;在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000 |
| 3 | kaushik | 23 | Kota | 2000 |
| 5 | Harish | 27 | Bhopal | 8500 |
| 6 | Kamesh | 22 | MP | 4500 |
| 7 | Murali | 24 | Indore | 10000 |
+----+----------+-----+-----------+----------+Example for Rollback
让我们考虑同样的 Customer 表作为输入。
Let us consider the same Customer table as input.
Id |
Name |
Age |
Address |
Salary |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Karun |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitanya |
25 |
Mumbai |
6500.00 |
5 |
Harish |
27 |
Bhopal |
8500.00 |
6 |
Kamesh |
22 |
MP |
1500.00 |
7 |
Murali |
24 |
Indore |
10000.00 |
这里有一个示例查询可以解释 Rollback 功能，具体是通过删除表中 age = 25 的记录然后 ROLLBACK 数据库中的改动。
Here is the example query that explains about Rollback functionality by deleting records from the table having age = 25 and then ROLLBACK the changes in the database.
DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;在上述两个查询成功执行后，你可以使用下面的命令查看 Customer 表中的记录数据。
After successful execution of the above two queries, you can view the record data in the Customer table using the following command.
Select * from Customer;执行上述命令后，您将收到以下输出。
After execution of the above command, you will receive the following output.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000 |
| 2 | Karun | 25 | Delhi | 1500 |
| 3 | Kaushik | 23 | Kota | 2000 |
| 4 | Chaitanya| 25 | Mumbai | 6500 |
| 5 | Harish | 27 | Bhopal | 8500 |
| 6 | Kamesh | 22 | MP | 4500 |
| 7 | Murali | 24 | Indore | 10000 |
+----+----------+-----+-----------+----------+删除查询会删除年龄为 25 的客户的记录数据。Rollback 命令会回滚 Customer 表上的那些改动。
The delete query deletes the record data of customers whose age = 25. The Rollback command, rolls back those changes on the Customer table.
Example for Savepoint
保存点是事务中的一个点，你可以在此点回滚事务到某个点，而不回滚整个事务。
Savepoint is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.
让我们考虑同样的 Customer 表作为输入。
Let us consider the same Customer table as input.
Id |
Name |
Age |
Address |
Salary |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Karun |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitanya |
25 |
Mumbai |
6500.00 |
5 |
Harish |
27 |
Bhopal |
8500.00 |
6 |
Kamesh |
22 |
MP |
1500.00 |
7 |
Murali |
24 |
Indore |
10000.00 |
让我们在本例中考虑一下，你计划删除 Customers 表中的三条不同的记录。你希望在每次删除之前创建一个保存点，以便你可以随时回滚到任意保存点，以将相应的数据返回到其原始状态。
Let us consider in this example, you plan to delete the three different records from the Customers table. You want to create a Savepoint before each delete, so that you can roll back to any Savepoint at any time to return the appropriate data to its original state.
这里有一系列操作。
Here is the series of operations.
SAVEPOINT SP1;
DELETE FROM CUSTOMERS WHERE ID = 1;
SAVEPOINT SP2;
DELETE FROM CUSTOMERS WHERE ID = 2;
SAVEPOINT SP3;
DELETE FROM CUSTOMERS WHERE ID = 3;现在，你已经创建了三个保存点并删除了三条记录。在这个情况下，如果你想要回滚 Id 为 2 和 3 的记录，那么使用下面的 Rollback 命令。
Now, you have created three Savepoint and deleted three records. In this situation, if you want to roll back the records having Id 2 and 3 then use the following Rollback command.
ROLLBACK TO SP2;注意，由于你回滚到了 SP2，因此只发生了第一次删除。使用下面的查询显示所有客户记录。
Notice that only the first deletion took place since you rolled back to SP2. Use the following query to display all the records of the customers.
Select * from Customer;在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 2 | Karun | 25 | Delhi | 1500 |
| 3 | Kaushik | 23 | Kota | 2000 |
| 4 | Chaitanya| 25 | Mumbai | 6500 |
| 5 | Harish | 27 | Bhopal | 8500 |
| 6 | Kamesh | 22 | MP | 4500 |
| 7 | Murali | 24 | Indore | 10000 |
+----+----------+-----+-----------+----------+HsqlDB - Alter Command
无论何时需要更改表或字段的名称、更改字段的顺序、更改字段的数据类型或任何表结构，你都可以使用 ALTER 命令。
Whenever there is a need to change the name of a table or a field, change the order of fields, change the datatype of fields, or any table structure, you can achieve the same using the ALTER command.
Example
让我们考虑一个解释 ALTER 命令使用不同场景的示例。
Let us consider an example that explains the ALTER command using different scenarios.
使用下面的查询创建名为 testalter_tbl 的表以及字段 id 和 name 。
Use the following query to create a table named testalter_tbl with the fields’ id and name.
//below given query is to create a table testalter_tbl table.
create table testalter_tbl(id INT, name VARCHAR(10));
//below given query is to verify the table structure testalter_tbl.
Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM | TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
| PUBLIC |TESTALTER_TBL| ID | 4 | INTEGER | 4 |
| PUBLIC |TESTALTER_TBL| NAME | 12 | VARCHAR | 10 |
+------------+-------------+------------+-----------+-----------+------------+Dropping or Adding a Column
无论何时想要从 HSQLDB 表中 DROP 现有列时，你都可以将 DROP 子句和 ALTER 命令一起使用。
Whenever you want to DROP an existing column from the HSQLDB table, then you can use the DROP clause along with the ALTER command.
使用以下查询从表 testalter_tbl 中删除一列 ( name )。
Use the following query to drop a column (name) from the table testalter_tbl.
ALTER TABLE testalter_tbl DROP name;在成功执行以上查询后，你可以使用以下命令了解名称字段是否从表 testalter_tbl 中删除。
After successful execution of the above query, you can get to know if the name field is dropped from the table testalter_tbl using the following command.
Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';执行上述命令后，您将收到以下输出。
After execution of the above command, you will receive the following output.
+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM | TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
| PUBLIC |TESTALTER_TBL| ID | 4 | INTEGER | 4 |
+------------+-------------+------------+-----------+-----------+------------+每当你想要向 HSQLDB 表中添加任何列时，你可以在 ALTER 命令中使用 ADD 子句。
Whenever you want to add any column into the HSQLDB table, you can use the ADD clause along with the ALTER command.
使用以下查询，向表 testalter_tbl 中添加一个名为 NAME 的列。
Use the following query to add a column named NAME to the table testalter_tbl.
ALTER TABLE testalter_tbl ADD name VARCHAR(10);在成功执行以上查询后，你可以使用以下命令了解名称字段是否已添加到表 testalter_tbl 。
After successful execution of the above query, you can get to know if the name field is added to the table testalter_tbl using the following command.
Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';在执行上述查询后，你将收到以下输出。
After execution of the above query, you will receive the following output.
+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM | TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
| PUBLIC |TESTALTER_TBL| ID | 4 | INTEGER | 4 |
| PUBLIC |TESTALTER_TBL| NAME | 12 | VARCHAR | 10 |
+------------+-------------+------------+-----------+-----------+------------+Changing a Column Definition or Name
每当需要更改列定义时，请在 ALTER 命令中使用 MODIFY 或 CHANGE 子句。
Whenever there is a requirement of changing the column definition, use the MODIFY or CHANGE clause along with the ALTER command.
让我们考虑一个示例，来说明如何使用 CHANGE 子句。表 testalter_tbl 包含两个字段：id 和 name，分别具有数据类型 int 和 varchar。现在让我们尝试将 id 的数据类型从 INT 更改为 BIGINT。以下是进行更改的查询。
Let us consider an example that will explain how to use the CHANGE clause. The table testalter_tbl contains two fields - id and name - having datatypes int and varchar respectively. Now let us try to change the datatype of id from INT to BIGINT. Following is the query to make the change.
ALTER TABLE testalter_tbl CHANGE id id BIGINT;在成功执行以上查询后，可以使用以下命令验证表结构。
After successful execution of the above query, the table structure can be verified using the following command.
Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';执行上述命令后，您将收到以下输出。
After execution of the above command, you will receive the following output.
+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM | TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
| PUBLIC |TESTALTER_TBL| ID | 4 | BIGINT | 4 |
| PUBLIC |TESTALTER_TBL| NAME | 12 | VARCHAR | 10 |
+------------+-------------+------------+-----------+-----------+------------+现在让我们尝试将表 testalter_tbl 中的列 NAME 的大小从 10 增加到 20。以下是使用 ALTER 命令和 MODIFY 子句实现此操作的查询。
Now let us try to increase the size of a column NAME from 10 to 20 in the testalter_tbl table. Following is the query to achieve this using the MODIFY clause along with the ALTER command.
ALTER TABLE testalter_tbl MODIFY name VARCHAR(20);在成功执行以上查询后，可以使用以下命令验证表结构。
After successful execution of the above query, the table structure can be verified using the following command.
Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';执行上述命令后，您将收到以下输出。
After execution of the above command, you will receive the following output.
+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM | TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
| PUBLIC |TESTALTER_TBL| ID | 4 | BIGINT | 4 |
| PUBLIC |TESTALTER_TBL| NAME | 12 | VARCHAR | 20 |
+------------+-------------+------------+-----------+-----------+------------+HSQLDB - Indexes
database index 是一个数据结构，它提高了表中操作的速度。可以使用一列或多列创建索引，为快速随机查找和高效排序对记录的访问提供基础。
A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to the records.
在创建索引时，应考虑哪些列将用于创建 SQL 查询，并在这些列上创建一列或多列索引。
While creating an index, it should be considered what are the columns which will be used to make SQL queries, and create one or more indexes on those columns.
实际上，索引也是一种表，它保留主密钥或索引字段以及指向实际表中每个记录的指针。
Practically, indexes are also type of tables, which keep the primary key or the index field and a pointer to each record into the actual table.
用户看不到索引。它们仅用于加速查询，并将由数据库搜索引擎使用来快速查找记录。
The users cannot see the indexes. They are just used to speed up queries and will be used by the Database Search Engine to quickly locate records.
对具有索引的表，INSERT 和 UPDATE 语句需要更多时间，而 SELECT 语句在这些表上运行得更快。原因是在插入或更新时，数据库也需要插入或更新索引值。
The INSERT and UPDATE statements take more time on tables having indexes, whereas SELECT statements run faster on those tables. The reason being while inserting or updating, the database needs to insert or update the index values as well.
Simple & Unique Index
您可以在表上创建唯一索引。 unique index 表示两行不能具有相同的索引值。以下是创建表索引的语法。
You can create a unique index on a table. A unique index means that two rows cannot have the same index value. Following is the syntax to create an Index on a table.
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2,...);可以使用一个或多个列创建索引。例如，使用 tutorial_author 在 tutorials_tbl 上创建索引。
You can use one or more columns to create an index. For example, create an index on tutorials_tbl using tutorial_author.
CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)可以在表上创建简单索引。从要创建简单索引的查询中省略该 UNIQUE 关键字。 simple index 允许表中存在重复值。
You can create a simple index on a table. Just omit the UNIQUE keyword from the query to create a simple index. A simple index allows duplicate values in a table.
如果您想按降序对某列中的值进行索引，您可以在列名后添加保留字 DESC。
If you want to index the values in a column in a descending order, you can add the reserved word DESC after the column name.
CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author DESC)ALTER Command to Add & Drop INDEX
有四类用于向表中添加索引的语句 −
There are four types of statements for adding indexes to a table −
-
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) − This statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL.
-
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) − This statement creates an index for which the values must be unique (with the exception of NULL values, which may appear multiple times).
-
ALTER TABLE tbl_name ADD INDEX index_name (column_list) − This adds an ordinary index in which any value may appear more than once.
-
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) − This creates a special FULLTEXT index that is used for text-searching purposes.
以下是添加到现有表的索引的查询。
Following is the query to add index in an existing table.
ALTER TABLE testalter_tbl ADD INDEX (c);你可以使用 DROP 子句和 ALTER 命令来删除任何索引。以下是删除上述创建的索引的查询。
You can drop any INDEX by using the DROP clause along with the ALTER command. Following is the query to drop the above-created index.
ALTER TABLE testalter_tbl DROP INDEX (c);Displaying INDEX Information
你可以使用 SHOW INDEX 命令列出与表关联的所有索引。此语句经常会使用垂直格式输出（通过 \G 指定），以避免长行换行。
You can use the SHOW INDEX command to list out all the indexes associated with a table. Vertical-format output (specified by \G) often is useful with this statement, to avoid long line wraparound.
以下是显示表索引信息的通用语法。
Following is the generic syntax to display the index information about a table.
SHOW INDEX FROM table_name\G