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);
</span><span class="com">//获取Statement对象</span><span class="pln">
</span><span class="typ">Statement</span><span class="pln"> stmt</span><span class="pun">=</span><span class="pln">conn</span><span class="pun">.</span><span class="pln">createStatement</span><span class="pun">();</span><span class="pln">
</span><span class="com">//创建表结构,插入一些测试数据</span><span class="pln">
</span><span class="typ">String</span><span class="pln"> createQuery</span><span class="pun">=</span><span class="str">"CREATE TABLE Employees( "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"Name VARCHAR(255), "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"Salary INT NOT NULL, "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"Location VARCHAR(255), "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"PRIMARY KEY (Id))"</span><span class="pun">;</span><span class="pln">
stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="pln">createQuery</span><span class="pun">);</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Table created"</span><span class="pun">);</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">" "</span><span class="pun">);</span><span class="pln">
</span><span class="com">//执行SQL语句</span><span class="pln">
</span><span class="typ">String</span><span class="pln"> insertQuery</span><span class="pun">=</span><span class="str">"INSERT INTO Employees("</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"Name, Salary, Location) VALUES "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"(Amit, 30000, Hyderabad), "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"(Kalyan, 40000, Vishakhapatnam), "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"(Renuka, 50000, Delhi), "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"(Learnfk, 15000, Mumbai), "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"(Trupti, 45000, Kochin)"</span><span class="pun">;</span><span class="pln">
stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="pln">insertQuery</span><span class="pun">);</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Values inserted"</span><span class="pun">);</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">" "</span><span class="pun">);</span><span class="pln">
</span><span class="com">//Executing the query</span><span class="pln">
</span><span class="typ">String</span><span class="pln"> selectQuery</span><span class="pun">=</span><span class="str">"SELECT * FROM Employees"</span><span class="pun">;</span><span class="pln">
</span><span class="typ">ResultSet</span><span class="pln"> rs</span><span class="pun">=</span><span class="pln">stmt</span><span class="pun">.</span><span class="pln">executeQuery</span><span class="pun">(</span><span class="pln">selectQuery</span><span class="pun">);</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Contents of the table after inserting the table"</span><span class="pun">);</span><span class="pln">
</span><span class="kwd">while</span><span class="pun">(</span><span class="pln">rs</span><span class="pun">.</span><span class="kwd">next</span><span class="pun">())</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Id: "</span><span class="pun">+</span><span class="pln">rs</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Id"</span><span class="pun">));</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Name: "</span><span class="pun">+</span><span class="pln">rs</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Name"</span><span class="pun">));</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Salary: "</span><span class="pun">+</span><span class="pln">rs</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Salary"</span><span class="pun">));</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Location: "</span><span class="pun">+</span><span class="pln">rs</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Location"</span><span class="pun">));</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">" "</span><span class="pun">);</span><span class="pln">
</span><span class="com">//增加表字段与约束</span><span class="pln">
stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="str">"ALTER TABLE Employees ADD COLUMN Age INT"</span><span class="pun">);</span><span class="pln">
stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="str">"ALTER TABLE Employees ADD COLUMN Phone_No BigINT"</span><span class="pun">);</span><span class="pln">
stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="str">"ALTER TABLE Employees "</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">"ADD CONSTRAINT New_Constraint UNIQUE(Phone_No)"</span><span class="pun">);</span><span class="pln">
stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="str">"INSERT INTO Employees "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"(Name, Salary, Location, Age, Phone_No) "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"VALUES (Amit, 30000, Hyderabad, 30, 9848022338)"</span><span class="pun">);</span><span class="pln">
</span><span class="typ">ResultSet</span><span class="pln"> alterResult</span><span class="pun">=</span><span class="pln">stmt</span><span class="pun">.</span><span class="pln">executeQuery</span><span class="pun">(</span><span class="str">"Select * from Employees"</span><span class="pun">);</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Contents of the table after altering "</span><span class="pln">
</span><span class="pun">+</span><span class="pln"> </span><span class="str">"the table and inserting values to it: "</span><span class="pun">);</span><span class="pln">
</span><span class="kwd">while</span><span class="pun">(</span><span class="pln">alterResult</span><span class="pun">.</span><span class="kwd">next</span><span class="pun">())</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Id: "</span><span class="pun">+</span><span class="pln">alterResult</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Id"</span><span class="pun">));</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Name: "</span><span class="pun">+</span><span class="pln">alterResult</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Name"</span><span class="pun">));</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Salary: "</span><span class="pun">+</span><span class="pln">alterResult</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Salary"</span><span class="pun">));</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Location: "</span><span class="pun">+</span><span class="pln">alterResult</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Location"</span><span class="pun">));</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Age: "</span><span class="pun">+</span><span class="pln">alterResult</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Age"</span><span class="pun">));</span><span class="pln">
</span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Phone_No: "</span><span class="pun">+</span><span class="pln">alterResult</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="str">"Phone_No"</span><span class="pun">));</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
}
}
执行上述程序时，将生成以下输出-
Table createdValues 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
