Apache Derby 简明教程
Apache Derby - Insert Data
插入查询插入数据: new records ,到表中。
The insert query inserts data: new records, into the table.
Syntax
以下是 INSERT 语句的基本语法 −
Following is the basic syntax of the INSERT statement −
ij>INSERT INTO table_name VALUES (column_name1, column_name2, ...);
其中 column1、column2 是要插入的行中的列值。
where column1, column2 are the column values in the row that is to be inserted.
Example
以下 SQL INSERT 语句在 Student 表中插入一个新行,其中它插入 id, age, first name 和 last name 列中的值。
The following SQL INSERT statement inserts a new row in the Student table, where it inserts values in the columns id, age, first name and, last name.
SQL> INSERT INTO Student VALUES (101, 20, 'Zara', 'Ali');
Syntax 2
或者,您可以通过提及列名来插入两个特定的列,如下所示 −
Or, you can insert two specific columns by mentioning the column names, as given below −
ij>INSERT INTO table_name VALUES (column_name1, column_name2, ...) VALUES
(value1, value2, ...);
Note − Apache Derby 会自动计算生成列的值。例如,不需要传递本教程前面创建的 student 表中 id 列的值。如果您的表生成了列,请使用 syntax2 。
Note − Apache Derby automatically calculates values for generated columns. For example, there is no need to pass values for the id column in the student table created earlier in this tutorial. In case your table has generated columns, use syntax2.
Example
ij> INSERT INTO Student(Age, First_Name, Last_Name) VALUES (21, 'Sucharitha' , 'Tyagi');
1 row inserted/updated/deleted
此外,您还可以使用以下语句插入两行 −
And, you can also insert two rows using one statement as follows −
ij>INSERT INTO Student(Age, First_Name, Last_Name) VALUES
(20, 'Amit', 'Bhattacharya'), (22, 'Rahul', 'Desai');
2 rows inserted/updated/deleted
可以使用 SELECT 命令验证表的内容(我们将在本教程的后面讨论此命令)。
You can verify the contents of the table using the SELECT command (we will discuss this command later in this tutorial).
Syntax 3
您可以在插入语句中使用另一个查询,如 -
You can use another query in the insert statement as −
INSERT INTO table_Name Query
Example
假设,我们在数据库中有一个名为 First_Year 的表,其列与 Student 表中的列类似,如下所示 −
Suppose, we have a table named First_Year in the database as shown below with similar columns as in Student table −
ID |AGE |FIRST_NAME |LAST_NAME
-----------------------------------------------------------------
1 |20 |Raju |Pendyala
2 |21 |Bhargav |Prayaga
3 |22 |Deepthi |Yerramilli
您可以使用上述语法插入值到 Student 表:
You can insert values in this table to the student table using the above syntax as −
ij> INSERT INTO Student (Age, First_Name, Last_Name)
SELECT Age, First_Name, Last_Name FROM First_Year;
> 3 rows inserted/updated/deleted
执行完所有上述插入语句后,Student 表将如下所示:
After executing all the above insert statements, the Student table will be as follows −
ID |AGE |FIRST_NAME |LAST_NAME
-------------------------------------------------------------
1 |21 |Sucharitha |Tyagi
2 |20 |Amit |Bhattacharya
3 |22 |Rahul |Desai
4 |20 |Raju |Pendyala
5 |21 |Bhargav |Prayaga
6 |22 |Deepthi |Yerramilli
Insert Data using JDBC program
此部分将介绍如何在 Apache Derby 数据库中使用 JDBC 应用程序插入数据到表中。
This section teaches you how to insert data in to 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"
遵循以下步骤将数据插入到 Apache Derby 中的表中:
Follow the steps given below to insert data into a table in Apache Derby −
Step 1: Register the driver
要与数据库通信,首先,您需要注册驱动。类的 forName() 方法 Class 接受表示类名称的 String 值,并将其加载到内存中,从而自动注册该名称。使用此方法注册驱动。
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 or, CallableStatement 对象以向数据库发送 SQL 语句。您可以分别使用 createStatement(), prepareStatement() 和 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 any of these objects using the appropriate method.
Step 4: Execute the query
创建语句后,您需要执行它。 Statement 类提供各种方法来执行查询,例如 execute() 方法,用于执行返回多个结果集的语句。
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.
executeUpdate() 方法执行 INSERT、UPDATE 和 DELETE 之类的查询。 executeQuery() 方法对返回数据等的结果进行处理。使用这两种方法中的任一种,并执行先前创建的语句。
The executeUpdate() method executes queries like INSERT, UPDATE, DELETE. The executeQuery() method to results that returns data etc. Use either of these methods and execute the statement created previously.
Example
以下 JDBC 示例演示了如何使用 JDBC 程序将数据插入 Apache Derby 中的表。在此处,我们使用嵌入式驱动程序连接到名为 sampleDB(如果不存在则创建)的数据库。
Following JDBC example demonstrates how to insert data into 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.SQLException;
import java.sql.Statement;
public class InsertData {
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
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))";
//Executing the query
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')";
stmt.execute(query);
System.out.println("Values inserted");
}
}