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'),
('Learnfk', 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命令通过列出列及其详细信息来描述指定的表，如果您描述，则表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);
其中 constraint 可以是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约束添加到一列，它就不能在两行中具有相同的值，即，每个员工的pady number应该是唯一的。
如果您尝试添加两个具有相同pady number的列，则会出现如下所示的异常。
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约束，因此您可以添加具有相同pady number的列。
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 {
//注册驱动
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//创建连接
String URL="jdbc:derby:sampleDB;create=true";
Connection conn=DriverManager.getConnection(URL);
//获取Statement对象
Statement stmt=conn.createStatement();
//创建表结构,插入一些测试数据
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(" ");
//执行SQL语句
String insertQuery="INSERT INTO Employees("
+ "Name, Salary, Location) VALUES "
+ "('Amit', 30000, 'Hyderabad'), "
+ "('Kalyan', 40000, 'Vishakhapatnam'), "
+ "('Renuka', 50000, 'Delhi'), "
+ "('Learnfk', 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(" ");
//增加表字段与约束
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: Learnfk 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: Learnfk 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
参考链接
https://www.learnfk.com/derby/apache-derby-alter-table-statement.html
标签：Salary,Name,No,Employees,无涯,Alert,Location,Derby,NULL From： https://blog.51cto.com/u_14033984/7799772