Apache Derby – 更改表语句
Apache Derby – 更改表语句
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) );
并且，使用插入语句插入四条记录作为 –
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 且类型为 integer 的新列。
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 命令通过列出列及其详细信息来描述指定的表。如果您描述表员工，您可以观察新添加的列，如下所示 –
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 语句，我们尝试向Phone_No 列添加约束UNIQUE。
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
使用JDBC程序更改表
以下是使用 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
