Apache Derby 简明教程

Apache Derby - Alter Table Statement

ALTER TABLE 语句允许你更改现有表。使用它可以执行以下操作 −

The ALTER TABLE statement, allows you to alter an existing table. Using this you can do the following −

  1. Add a column, add a constraint

  2. Drop a column, drop a constraint

  3. Change the row level locking of a table

让我们假设我们已创建一个名为 Employees 的表，如下所示 −

Let us assume we have created a table named Employees as shown below −

ij> CREATE TABLE Employees (
   Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   Name VARCHAR(255),
   Salary INT NOT NULL,
   Location VARCHAR(255),
   PRIMARY KEY (Id)
);

而且，使用 insert 语句插入四条记录，如下 −

And, inserted four records using the insert statement as −

ij> INSERT INTO Employees (Name, Salary, Location) VALUES
   ('Amit', 30000, 'Hyderabad'),
   ('Kalyan', 40000, 'Vishakhapatnam'),
   ('Renuka', 50000, 'Delhi'),
   ('Archana', 15000, 'Mumbai');

Adding a column to a Table

以下是使用 ALTER 语句将列添加到表中的语法。

Following is the syntax to add a column to a table using ALTER statement.

ALTER TABLE table_name ADD COLUMN column_name column_type;

Example

使用 ALTER 语句，我们尝试使用类型 integer 添加一个名为 Age 的新列。

Using ALTER statement, we are trying to add a new column named Age with the type integer.

ALTER TABLE Employees ADD COLUMN Age INT;
0 rows inserted/updated/deleted

使用 Gemini 将此文本翻译成中文:

Add another column named Phone_No with the type integer.

ALTER TABLE Employees ADD COLUMN Phone_No BIGINT;
0 rows inserted/updated/deleted

在类型为整型的列中添加另一个名为 Phone_No 的列。

The DESCRIBE command describes specified table by listing the columns and their details, if the table exists. If you DESCRIBE, the table Employees you can observe the newly added columns as shown below −

ij> DESCRIBE Employees;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID          |INTEGER  |0   |10   |10   |AUTOINCRE&|NULL      |NO
NAME        |VARCHAR  |NULL|NULL |255  |NULL      |510       |YES
SALARY      |INTEGER  |0   |10   |10   |NULL      |NULL      |NO
LOCATION    |VARCHAR  |NULL|NULL |255  |NULL      |510       |YES
AGE         |INTEGER  |0   |10   |10   |NULL      |NULL      |YES
PHONE_NO    |INTEGER  |0   |10   |10   |NULL      |NULL      |YES
6 rows selected

Adding a constraint to a table

DESCRIBE 命令通过列出列とその詳細信息来描述指定表格（如果表存在）。如果您对 Employees 表格执行 DESCRIBE，您可以观察到新添加的列，如下所示：

Following is the syntax to add a constraint to a column of a table using ALTER statement.

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint (column_name);

以下是使用 ALTER 语句向表格列添加约束的语法：

Where constraint can be NOT NULL, NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK.

Example

其中 constraint 可以是 NOT NULL、NULL、PRIMARY KEY、UNIQUE、FOREIGN KEY、CHECK。

Using ALTER statement, we are trying to add constraint UNIQUE to the Phone_No column.

ij> ALTER TABLE Employees ADD CONSTRAINT New_Constraint UNIQUE(Phone_No);
0 rows inserted/updated/deleted

使用 ALTER 语句，我们尝试向 Phone_No 列添加约束 UNIQUE 。

Once, you add a UNIQUE constraint to a column, it cannot have the same values for two rows, i.e., phone number should be unique for each employee.

一旦您向列添加 UNIQUE 约束，则两个行不能具有相同的值，即每个员工的电话号码应唯一。

If you try to add two columns with a same phone number, you will get an exception as shown below.

ij> INSERT INTO Employees (Name, Salary, Location, Age, Phone_No) VALUES
('Amit', 30000, 'Hyderabad', 30, 9848022338);
1 row inserted/updated/deleted
ij> INSERT INTO Employees (Name, Salary, Location, Age, Phone_No) VALUES
('Sumit', 35000, 'Chennai', 25, 9848022338);
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by
'NEW_CONSTRAINT' defined on 'EMPLOYEES'.

Dropping a constraint from a table

如果您尝试添加两个具有相同电话号码的列，则会收到异常，如下所示：

Following is the syntax to drop a constraint of a column −

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example

以下是删除列约束的语法：

The following query deletes the constraint name New_Constraint on the column Phone_No created above.

ij> ALTER TABLE Employees DROP CONSTRAINT New_Constraint;
0 rows inserted/updated/deleted

以下查询在上面创建的列 Phone_No 中删除了约束名称 New_Constraint。

Since we have removed the UNIQUE constraint on the column Phone_No, you can add columns with the same phone number.

ij> INSERT INTO Employees (Name, Salary, Location, Age, Phone_No) VALUES
('Sumit', 35000, 'Chennai', 25, 9848022338);
1 row inserted/updated/deleted

由于我们已删除列 Phone_No 上的 UNIQUE 约束，因此您可以添加具有相同电话号码的列。

You can verify the contents of the table ij> select * from Employees as follows −

ID |NAME  |SALARY |LOCATION |AGE |PHONE_NO
-------------------------------------------------------------------------
1  |Amit  |30000  |Hyderabad|30  |9848022338
2  |Sumit |35000  |Chennai  |25  |9848022338
2 rows selected

Dropping a column from a table

您可以通过以下方式验证表格的内容：ij> select * from Employees

Following is the syntax to drop a column of a column.

ALTER TABLE table_name DROP COLUMN column_name;

Example

以下是删除列的语法的列。

Following query deletes the column named age of the employee −

ij> ALTER TABLE Employees DROP COLUMN Age;
0 rows inserted/updated/deleted

以下查询删除名为 age of the employee 的列：

If you describe the table, you can see only 4 columns.

ij> DESCRIBE Employees;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF |CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID          |INTEGER  |0   |10  |10    |AUTOINCRE& |NULL      |NO
NAME        |VARCHAR  |NULL|NULL|255   |NULL       |510       |YES
SALARY      |INTEGER  |0   |10  |10    |NULL       |NULL      |NO
LOCATION    |VARCHAR  |NULL|NULL|255   |NULL       |510       |YES
PHONE_NO    |BIGINT   |0   |10  |19    |NULL       |NULL      |YES

Altering table using JDBC program

如果您描述表格，您只能看到 4 列。

Following is the JDBC program to alter a table using the ALTER query −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class AlterTableExample {
   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();

      //Executing the query
      String createQuery = "CREATE TABLE Employees( "
         + "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
         + "Name VARCHAR(255), "
         + "Salary INT NOT NULL, "
         + "Location VARCHAR(255), "
         + "PRIMARY KEY (Id))";

      stmt.execute(createQuery);
      System.out.println("Table created");
      System.out.println(" ");

      //Executing the query
      String insertQuery = "INSERT INTO Employees("
         + "Name, Salary, Location) VALUES "
         + "('Amit', 30000, 'Hyderabad'), "
         + "('Kalyan', 40000, 'Vishakhapatnam'), "
         + "('Renuka', 50000, 'Delhi'), "
         + "('Archana', 15000, 'Mumbai'), "
         + "('Trupti', 45000, 'Kochin')";

      stmt.execute(insertQuery);
      System.out.println("Values inserted");
      System.out.println(" ");

      //Executing the query
      String selectQuery = "SELECT * FROM Employees";
      ResultSet rs = stmt.executeQuery(selectQuery);
      System.out.println("Contents of the table after inserting the table");
      while(rs.next()) {
         System.out.println("Id: "+rs.getString("Id"));
         System.out.println("Name: "+rs.getString("Name"));
         System.out.println("Salary: "+rs.getString("Salary"));
         System.out.println("Location: "+rs.getString("Location"));
      }
      System.out.println(" ");

      //Altering the table
      stmt.execute("ALTER TABLE Employees ADD COLUMN Age INT");
      stmt.execute("ALTER TABLE Employees ADD COLUMN Phone_No BigINT");
      stmt.execute("ALTER TABLE Employees " + "ADD CONSTRAINT New_Constraint UNIQUE(Phone_No)");

      stmt.execute("INSERT INTO Employees "
         + "(Name, Salary, Location, Age, Phone_No) "
         + "VALUES ('Amit', 30000, 'Hyderabad', 30, 9848022338)");
      ResultSet alterResult = stmt.executeQuery("Select * from Employees");
      System.out.println("Contents of the table after altering "
         + "the table and inserting values to it: ");
      while(alterResult.next()) {
         System.out.println("Id: "+alterResult.getString("Id"));
         System.out.println("Name: "+alterResult.getString("Name"));
         System.out.println("Salary: "+alterResult.getString("Salary"));
         System.out.println("Location: "+alterResult.getString("Location"));
         System.out.println("Age: "+alterResult.getString("Age"));
         System.out.println("Phone_No: "+alterResult.getString("Phone_No"));
      }
   }
}

Output

以下是使用 ALTER 查询来更改表格的 JDBC 程序：

On executing the above program, the following output will be generated −

Table created

Values inserted

Contents of the table after inserting the table
Id: 1
Name: Amit
Salary: 30000
Location: Hyderabad
Id: 2
Name: Kalyan
Salary: 40000
Location: Vishakhapatnam
Id: 3
Name: Renuka
Salary: 50000
Location: Delhi
Id: 4
Name: Archana
Salary: 15000
Location: Mumbai
Id: 5
Name: Trupti
Salary: 45000
Location: Kochin

Contents of the table after altering the table and inserting values to it:
Id: 1
Name: Amit
Salary: 30000
Location: Hyderabad
Age: null
Phone_No: null
Id: 2
Name: Kalyan
Salary: 40000
Location: Vishakhapatnam
Age: null
Phone_No: null
Id: 3
Name: Renuka
Salary: 50000
Location: Delhi
Age: null
Phone_No: null
Id: 4
Name: Archana
Salary: 15000
Location: Mumbai
Age: null
Phone_No: null
Id: 5
Name: Trupti
Salary: 45000
Location: Kochin
Age: null
Phone_No: null
Id: 6
Name: Amit
Salary: 30000
Location: Hyderabad
Age: 30
Phone_No: 9848022338