1.基础的增删改

A.增：alter table [表名] add [字段名] 字段属性 default 缺省值 default 是可选参数

B.删：alter table [表名] drop 字段名

C.改：alter table [表名] alter [字段名] 字段属性

注：自增列不能直接修改，必须将原有ID列删除，然后重新添加一列具有identity属性的ID字段。比如你要修改的字段名为ID：

alter table 表名 drop column ID

alter table 表名 add ID int identity(1,1)
2.修改表：

A.重命名表：EXEC sp_rename ‘oldname’,‘newname’

B.修改列属性：alter table 学生信息 alter column 姓名 varchar(20) not null

C.添加列：alter table 学生信息 add 家庭住址 nvarchar(20) null

D.删除列：alter table 学生信息 drop column 家庭住址

E.修改列名：exec sp_rename ‘表名.[字段原名]’,‘字段新名’,‘column’
3.复制表：

A. 复制整张表：select * into new_table from old_table

B. 复制表结构：select * into new_table from old_table where 1=2

C. 复制表内容：insert into new_tab select * from old_table
4.再展示如何将添加的列获取另一列的值

A.update 表2 set （要插入的列名）= select 表1.某一列 from 表1 left jion 表2 on 表1和表2的关联 where …

B.update 表1 set 表1.列=表2.列 from 表2 where 表2.id=表1.id

C.update _a set 列=_b.列 from 表1 _a join 表2 _b on _a.id=_b.id
如果将一个表中的一列复制到另一列中，可以这样

update 表1 set 表1.列2=表1.列2 from 表1

小编参考的资料路径

笑笑小白

NET未来之路

SQL语句–在表中添加列、删除列
在表中添加列：
alter table 表名 add 字段名 数据类型
eg：
alter table Student add PhoneNumber char(11)

在表中删除列：
alter table 表名 drop column 字段名
eg：
alter table Student drop column PhoneNumber

Dear Users,
How to add a new column between the existing columns using T-SQL Statements in SQL Server 2005.
For Example in my existing table i have the columns Id,Name,Age
I need to insert Password column between Name and Age column using transact SQL statement.
解决方案You don''t need to, you can always do a select in the order you want:
select ID, name, password, age from tablename
Mehdi Gholam is answer is good, but you need some explonation.
You can do it and you can insert column between existing coulmns, but it needs to re-create table. By default new column is inserted as a last column in the column collection. As Mehdi Gholam wrote, you can always select data (columns) in order you want.
More at: http://msdn.microsoft.com/en-us/library/ms190273(v=sql.90).aspx[^]
If you still want to insert new column between existing columns, i recommend you to do these steps:
1) create new table NewTable with desired columns
2) copy data from ExistingTable into NewTable with default [Password].
3) remove ExistingTable
In the beginning ...
--CREATE SCHEMA [cpqa]
CREATE TABLE[cpqaAnswers].[cpqa].[tblSFThree] (
[intId][int],
[strName][nvarchar](81),
[intAge][int]
)
SELECT * FROM  [cpqaAnswers].[cpqa].[tblSFThree]
No data, just a table ...
DECLARE @ph [nvarchar](20)
SET @ph = ''nomanshallpass''
SELECT [intId], [strName], @ph As [strPass], [intAge] FROM  [cpqaAnswers].[cpqa].[tblSFThree]
While there''s no table field called [strPass], it''s possible, in TSQL, to name such a thing on-the-fly as a variable using a DECLARE statement.
CREATE TABLE [cpqaAnswers].[cpqa].[tblSFFour] (
[intId][int],
[strName][nvarchar](81),
[strPass][nvarchar](20),
[intAge][int]
)
SELECT [intId], [strName], [strPass], [intAge] FROM  [cpqaAnswers].[cpqa].[tblSFFour]
But to create another field, some more typing is required. Now there''s a formal placeholder
SELECT * FROM [cpqaAnswers].[cpqa].[tblSFFour]
When I say there''s just a table, I mean "no NULLs are allowed" so think of NULL as everything and therefore nothing.

DECLARE @i int

set @i=0

WHILE @i<10

BEGIN

alter table test add lie+@i int

set @i=@i+1

END

就可以添加lie1,lie2,lie3,lie4……

@i是控制循环的次数，也可以理解为要添加的列数


************

补充：

************

1、建一个表，保存你要追加的字段名和字段类型

如：create table t_tmp(id int IDENTITY(1,1) not null,col_name varchar(100),col_type varchar(100));


2、把你想要动态追加的字段和类型全写入表中

如：

insert into t_tmp(col_name,col_type) values('t1','int');

insert into t_tmp(col_name,col_type) values('t2','date');

insert into t_tmp(col_name,col_type) values('t3','varchar(100)');

insert into t_tmp(col_name,col_type) values('t4','char(100)');


3、生成动态建表语句，并创建表

declare @str varchar(2000)

set @str='create table ttt('

select @str=@str + col_name+' '+col_type+',' from t_tmp

set @str=substring(@str,1,len(@str)-1)+')'

select @str

exec(@str)
------------------------------------------------------------------------------------------------------------
declare @min varchar(4),@max varchar(4), @sqlstr varchar(8000)

set @sqlstr = 'select FItemID'

select @min = min(years), @max = max(years) from #Happen3

while @min <= @max

begin

set @sqlstr = @sqlstr+', 

sum(case when years = '+@min +' then(case when Months=1 then (FAmount / FQty)end)end) as
Y'+@min +'P1Price,

sum(case when years = '+@min +' then(case when Months=2 then (FAmount / FQty)end)end) as
Y'+@min +'P2Price,

sum(case when years = '+@min +' then(case when Months=3 then (FAmount / FQty)end)end) as
Y'+@min +'P3Price,

sum(case when years = '+@min +' then(case when Months=4 then (FAmount / FQty)end)end) as
Y'+@min +'P4Price,

sum(case when years = '+@min +' then(case when Months=5 then (FAmount / FQty)end)end) as
Y'+@min +'P5Price,

sum(case when years = '+@min +' then(case when Months=6 then (FAmount / FQty)end)end) as
Y'+@min +'P6Price,

sum(case when years = '+@min +' then(case when Months=7 then (FAmount / FQty)end)end) as
Y'+@min +'P7Price,

sum(case when years = '+@min +' then(case when Months=8 then (FAmount / FQty)end)end) as
Y'+@min +'P8Price,

sum(case when years = '+@min +' then(case when Months=9 then (FAmount / FQty)end)end) as
Y'+@min +'P9Price,

sum(case when years = '+@min +' then(case when Months=10 then (FAmount / FQty)end)end) as
Y'+@min +'P10Price,

sum(case when years = '+@min +' then(case when Months=11 then (FAmount / FQty)end)end) as
Y'+@min +'P11Price,

sum(case when years = '+@min +' then(case when Months=12 then (FAmount / FQty)end)end) as
Y'+@min +'P12Price'

set @min = @min+1

end

set @sqlstr = @sqlstr +' into ##Happen4 from #Happen3 where Not(FQty=0) group by FItemID'

exec(@sqlstr)