用SQL语句表与字段的基本操作数据库备份等.docVIP

用SQL语句添加删除修改字段 1.增加字段???? alter table docdsp??? add dspcode char(200)2.删除字段???? ALTER TABLE table_NAME DROP COLUMN column_NAME3.修改字段类型???? ALTER TABLE table_name??? ALTER COLUMN column_name new_data_type4.sp_rename 改名???? 更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。??? 语法??? sp_rename [ @objname = ] object_name ,??????? [ @newname = ] new_name??????? [ , [ @objtype = ] object_type ]??????? 如：EXEC sp_rename??? newname,PartStock5.sp_help 显示表的一些基本情况???? sp_help object_name???? 如：EXEC sp_help??? PartStock6.判断某一表PartStock中字段PartVelocity是否存在if???? exists???? (select???? *???? from???? syscolumns???? where???? id=object_id(PartStock)???? and???? name=PartVelocity)??print???? PartVelocity exists else print PartVelocity not exists另法：判断表的存在性：??? select count(*) from sysobjects where type=U and name=你的表名判断字段的存在性：??? select count(*) from syscolumns??? where id???? = (select id from sysobjects where type=U and name=你的表名)??? and???? name = 你要判断的字段名??一个小例子--假设要处理的表名为: tb--判断要添加列的表中是否有主键if exists(select 1 from sysobjects where parent_obj=object_id(tb) and xtype=PK)beginprint 表中已经有主键,列只能做为普通列添加--添加int类型的列,默认值为0alter table tb add 列名 int default 0??endelsebeginprint 表中无主键,添加主键列--添加int类型的列,默认值为0alter table tb add 列名 int primary key default 0??end 7.随机读取若干条记录????? Access语法：SELECT top 10 * From 表名 ORDER BY Rnd(id)????? Sql server:select top n * from 表名 order by newid()????? mysql select * From 表名 Order By rand() Limit n8.说明：日程安排提前五分钟提醒????? SQL: select * from 日程安排 where datediff(minute,f开始时间,getdate())5 9.前10条记录????? select top 10 * form table1 where 范围 10.包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表????? (select a from tableA ) except (select a from tableB) except (select a from tableC) 11.说明：随机取出10条数据????? select top 10 * from tablename order by newid() 12.列出数据库里所有的表名????? select name from sysobjects where type=U 13.列出表里的所有的字段名????? select name from syscolumns where id=object_id(TableName)14.说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现