SQL Server常见对表的操作

☻给数据库表添加字段

 

ALTER TABLE dbo.TrainApply ADD IsPassFirstCheck INT NOT NULL DEFAULT 0 --默认o:未处理，1审核通过，2审核未通过

ALTER TABLE dbo.TrainApply ADD FirstCheckUserID NVARCHAR(50)

ALTER TABLE dbo.TrainApply ADD FirstCheckTime DATETIME

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻删除表中某些字段

 

ALTER TABLE dbo.TrainApply DROP COLUMN FirstCheckUserID

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

☻创建表

use StuDB

go

if exists(select * from sysobjects where name='stuMarks')

drop table stuMarks

create table stuMarks

(

ExamNo      int     identity(1,1) primary key,

stuNo       char(6) not null,

writtenExam int     not null,

LabExam     int     not null

)

Go

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//修改表字段的类型

ALTER TABLE tablename ALTER COLUMN colname newDataType

比如：ALTER TABLE mytable ALTER COLUMN mycol1 INT DEFAULT 0

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//修改表字段长度

Alter Table zxchem_Suggest Alter Column Yj_Finish_Date Varchar(10) Not Null --修改字段长度

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//修改表的字段名

--test_tab 表的 val字段， 修改为 val2 字段。

--不过简单的写，就是

EXECUTE sp_rename N'dbo.test_tab.val', N'val2', 'COLUMN'