学步园

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