SQL 如何在单个 ALTER TABLE 中添加和删除列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15989527/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you ADD and DROP columns in a single ALTER TABLE
提问by fatsokol
I tried the following but I got a syntax error
我尝试了以下操作，但出现语法错误
ALTER TABLE Grades (
DROP COLUMN (Student_FamilyName, Student_Name),
ADD Student_id INT );
Is it possible to perform a DROPand an ADDin the same ALTER TABLEstatement?
是否可以在同一个语句中执行 aDROP和 an ？ADDALTER TABLE
回答by Conrad Frix
If you look at the ALTER TABLE SYTAX
如果你看一下ALTER TABLE SYTAX
you'll see this
你会看到这个
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{
ALTER COLUMN column_name
{
[ type_schema_name. ] type_name [ ( { precision [ , scale ]
| max | xml_schema_collection } ) ]
[ COLLATE collation_name ]
[ NULL | NOT NULL ] [ SPARSE ]
| {ADD | DROP }
{ ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }
}
| [ WITH { CHECK | NOCHECK } ]
| ADD
{
<column_definition>
| <computed_column_definition>
| <table_constraint>
| <column_set_definition>
} [ ,...n ]
| DROP
{
[ CONSTRAINT ]
{
constraint_name
[ WITH
( <drop_clustered_constraint_option> [ ,...n ] )
]
} [ ,...n ]
| COLUMN
{
column_name
} [ ,...n ]
} [ ,...n ]
This can be reduced to
这可以减少到
ALTER TABLE { ALTER COLUMN column_name | ADD | DROP }
ALTER TABLE { ALTER COLUMN column_name | ADD | DROP }
According to Transact-SQL Syntax Conventions (Transact-SQL)the | (vertical bar)
据Transact-SQL语法约定（的Transact-SQL）的| （竖条）
Separates syntax items enclosed in brackets or braces. You can use only one of the items.
分隔括在方括号或大括号中的语法项。您只能使用其中一项。
So you can't Alter, Drop or Add in a single statement. You also have the parens and comma that won't work. So you'll need
因此，您不能在单个语句中更改、删除或添加。您还有无法使用的括号和逗号。所以你需要
ALTER TABLE Grades DROP COLUMN (Student_FamilyName, Student_Name);
ALTER TABLE Grades ADD Student_id INT;
If you need them to be an atomic action you just need to wrap in transaction
如果你需要它们是一个原子动作，你只需要包装在事务中
回答by Klesun
In case your database is MySQL, you can do it this way:
如果你的数据库是 MySQL，你可以这样做：
ALTER TABLE Grades
DROP COLUMN Student_FamilyName,
DROP COLUMN Student_Name,
ADD Student_id INT;
Works in MySQL 5.5.5
适用于MySQL 5.5.5
