--在Ms Sql中用alert table修改表基本信息、表结构等 use master go if (exists (select * from sysdatabases where name = 'userDB')) drop database userDB go create database userDB go use userDB go if (exists (select * from sysobjects where name = 'userInfo')) drop table userInfo go create table userInfo ( uNo int, uBrithday varchar(50) ) go if (exists (select * from sysobjects where name = 'student')) drop table student go create table student ( sNumber int primary key ) go --向user表新插入列名为uName alter table userInfo add uName varchar(10); go select * from userInfo go --从user表删除列名为uName alter table userInfo drop column uName; go select * from userInfo go --修改已存在的列项的类型 alter table userInfo alter column uBrithday datetime; go select * from userInfo go --添加唯一约束 alter table userInfo add uAge varchar(20) null constraint age_unique unique; go select * from userInfo go select * from information_schema.columns where table_name = 'userInfo' go --增加主键,要记住pk_uNo即主键名 alter table userInfo add constraint pk_uNo primary key(uNo); go select * from userInfo go select * from information_schema.columns where table_name = 'userInfo' go --删除主键,这里用到了pk_uNo.若忘了,就用下面第二种方法 alter table userInfo drop constraint pk_uNo; go select * from userInfo go select * from information_schema.columns where table_name = 'userInfo' go /*另一种删除主键 *当你不知道(或忘了)创建主键时主键名(约束名), *就像上面的pk_sno(constarint后面的), *使用下面的删除主键的方法. declare @pk_name varchar(100) select @pk_name = name from sysobjects where xtype='pk' and parent_obj = object_id('userInfo') exec('alter table userInfo drop '+@pk_name) */ --添加check约束 alter table userInfo add age int go alter table userInfo with nocheck add constraint age_check check (age > 1) go alter table userInfo add uNumber int go --多个约束一起创建 alter table userInfo add /*添加id主键、自增*/ id int identity constraint id primary key, /* 添加外键约束 */ uNumber int null constraint uNumber references student(sNumber), /*默认约束*/ createDate decimal(3,3) constraint createDate default 2010-6-1 go exec sp_help userInfo go select * from userInfo;