ALTER TABLE语句允许您更改现有表.使用此功能，您可以执行以下操作;
添加列，添加约束
删除列，删除约束
更改表的行级锁定
我们假设我们创建了一个名为Employees的表，如下所示 :
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语句插入四个记录为 :
ij> INSERT INTO Employees (Name, Salary, Location) VALUES
('Amit', 30000, 'Hyderabad'),
('Kalyan', 40000, 'Vishakhapatnam'),
('Renuka', 50000, 'Delhi'),
('Archana', 15000, 'Mumbai');以下是使用ALTER语句向表中添加列的语法.
ALTER TABLE table_name ADD COLUMN column_name column_type;
使用ALTER语句，我们尝试添加一个名为Age的新列，其类型为整数.
ALTER TABLE Employees ADD COLUMN Age INT; 0 rows inserted/updated/deleted
添加另一个名为Phone_No的列，类型为整数.
ALTER TABLE Employees ADD COLUMN Phone_No BIGINT; 0 rows inserted/updated/deleted
DESCRIBE命令通过列出列及其详细信息(如果表存在)来描述指定的表.如果你DESCRIBE，表Employees你可以观察新添加的列，如下所示 :
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
以下是使用ALTER语句向表的列添加约束的语法.
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint (column_name);
其中约束可以是NOT NULL，NULL，PRIMARY KEY，UNIQUE，FOREIGN KEY，CHECK.
使用ALTER语句，我们尝试将约束 UNIQUE 添加到Phone_No列.
ij> ALTER TABLE Employees ADD CONSTRAINT New_Constraint UNIQUE(Phone_No); 0 rows inserted/updated/deleted
有一次，你向一个列添加一个UNIQUE约束，它不能为两行提供相同的值，即，每个员工的电话号码应该是唯一的.
如果您尝试添加两个具有相同电话号码的列，您将收到如下所示的例外情况.
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'.以下是删除列约束的语法 :
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
以下查询删除上面创建的Phone_No列上的约束名New_Constraint.
ij> ALTER TABLE Employees DROP CONSTRAINT New_Constraint; 0 rows inserted/updated/deleted
由于我们删除了Phone_No列上的UNIQUE约束，您可以添加具有相同电话号码的列.
ij> INSERT INTO Employees (Name, Salary, Location, Age, Phone_No) VALUES
('Sumit', 35000, 'Chennai', 25, 9848022338);
1 row inserted/updated/deleted您可以验证表格的内容ij> select * from Employees，如下 :
ID |NAME |SALARY |LOCATION |AGE |PHONE_NO ------------------------------------------------------------------------- 1 |Amit |30000 |Hyderabad |30 |9848022338 2 |Sumit |35000 |Chennai |25 |9848022338 2 rows selected
以下是删除列列的语法.
ALTER TABLE table_name DROP COLUMN column_name;
以下查询删除名为员工年龄 :
ij> ALTER TABLE Employees DROP COLUMN Age; 0 rows inserted/updated/deleted
如果您描述该表，则只能看到4列。
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
以下是使用ALTER查询更改表的JDBC程序:
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"));
}
}
}执行上述程序时，将生成以下输出:
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
