MS SQL常用语句总结

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

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

declare @str varchar(8000)

set @str=''  

select @[email protected]+

'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 @[email protected] + 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 [email protected];

update Land set DefLandID= '1_' + @landfid where ProgramCode='test_0' and [email protected];

set @[email protected]+1

end

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