MS SQL常用语句总结

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

统计当前所有数据库和库中表的总个数

declare @str varchar(8000)
set @str=''  
select @str=@str+
'union all select '+quotename(name,'''')+' ,COUNT(*)
from '+name+'.dbo.sysobjects where xtype=''U'''
from (select  name from master.dbo.sysdatabases) a
set @str =' select ''0数据库总数'' as 库名,(select  count(*) from master.dbo.sysdatabases)
 as 表的个数 '+ @str+' order by 库名 '
print @str
exec(@str)

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

同一个表中，插入不重复的数据（表中必须存在记录）

insert into Table1(Field1) select top 1 '3' from Table1 where not exists(select * from Table1 where Field1='3')

如果表中不存在记录时

if not exists (select * from Table1 where Field1=1)
begin
insert into Table1(Field1) values(1)
end

---------------------------------------------------------------------------------------------------------------------------------------
--为表添加外键约束
--要求：外键表中的数据 主键表中是有的 数据是匹配的；不能删除外键表的数据，然后才去建立关系
alter table User_ProgramList 
add constraint FK_UserName_UserBookUserName 
foreign key (UserName) 
references UserBook(UserName);


---------------------------------------------------------------------------------------------------------------------------------------
--设置主键约束
ALTER TABLE Land     --修改表
ADD     
CONSTRAINT PK_DefLandID
PRIMARY KEY CLUSTERED (DefLandID) --将你要设置为主键约束的列


---------------------------------------------------------------------------------------------------------------------------------------
--增加Check约束
--约束列的值只能为0或1
alter table landdata_Field
with check add CONSTRAINT CK_landdata_Field_isShowTable
check (isShowTable in(0,1))


---------------------------------------------------------------------------------------------------------------------------------------
--一次删除所有表SQL语句
declare @tname varchar(8000)
set @tname=''
select @tname=@tname + Name + ',' from sysobjects where xtype='U'
select @tname='drop table ' + left(@tname,len(@tname)-1)
exec(@tname)


---------------------------------------------------------------------------------------------------------------------------------------
--delete使用
delete from Land where ProgramCode='test_0';


---------------------------------------------------------------------------------------------------------------------------------------
--修改表字段名
exec sp_rename 'peoplehouse_Field.MapTabelFieldName','MapTableFieldName','column';


---------------------------------------------------------------------------------------------------------------------------------------
--修改表字段不空
ALTER TABLE house_0 alter column DefID nvarchar(255) Not NULL;


---------------------------------------------------------------------------------------------------------------------------------------
--修改表字段类型
alter table Special_Road alter column Field1 nvarchar(50) not null; --修改字段类型
alter table Module_User_Program add PlateTextNo int not null Default 0; --并设置约束，默认为0


---------------------------------------------------------------------------------------------------------------------------------------
--增加表字段
alter table Land add DefLandID nvarchar(255) not null default 0; 
alter table UserBook add Field1 int null; 


---------------------------------------------------------------------------------------------------------------------------------------
--删除表字段
ALTER TABLE [UserBook] DROP COLUMN [Disable];


---------------------------------------------------------------------------------------------------------------------------------------
--当新表不存在时，复制老表表的记录等到新表中
select * into ImmigrationSystem.dbo.House from MigrateCloud.dbo.House;


---------------------------------------------------------------------------------------------------------------------------------------
--当新表存在时，复制老表中的记录到新表中
insert into ImmigrationSystem.dbo.Estimate(EstimateFilePath,ProgramCode) (select href,region from MigrateCloud.dbo.Estimate); 


---------------------------------------------------------------------------------------------------------------------------------------
--创建表
CREATE TABLE [User_ModifyRecord](
[UserName] [nvarchar](50) NOT NULL,
[ProgramCode] [nvarchar](50) NULL,
[ModifyLog] [nvarchar](max) NULL,
[ModifyTime] [datetime] NULL,
[Remark] [nvarchar](300) NULL
)


---------------------------------------------------------------------------------------------------------------------------------------
--update使用
update Special_ConvertStation set ProgramCode='test_0' where ProgramCode='key1';


---------------------------------------------------------------------------------------------------------------------------------------
--select使用
select * from Land;


---------------------------------------------------------------------------------------------------------------------------------------
--insert使用
insert into people_Field(FieldName,CFieldName,ProgramCode) values ('ProgCode','区域','test_0');
insert into people_Field values ('ProgCode','区域','test_0');


---------------------------------------------------------------------------------------------------------------------------------------
--while循环
declare @i int
declare @landfid nvarchar(255)
declare @deflandid nvarchar(255)
set @i=1001
while @i<1516
begin
select @landfid=LandFID from Land where ProgramCode='test_0' and LandFID=@i;
update Land set DefLandID= '1_' + @landfid where ProgramCode='test_0' and LandFID=@landfid;
set @i=@i+1
end


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