IT工程師數位筆記本

Alter TABLE [dbo].[CustomerBackupConfig] Add [Stamp] [timestamp] NULL
GO

Alter Table tblStates Add [TaxRate] money not null default 0,
[Transit] int not null default 0
GO

Alter Table [dbo].[tblOrder] Drop Column [CookieID]
GO

Alter Table [dbo].[tblOrder] Alter Column [CookieID] int not null
GO

EXEC sp_rename '[dbo].[Table_1].[filedName_Old]', 'filedName_New', 'COLUMN';

sp_rename [ @objname = ] 'object_name' ,[ @newname = ] 'new_name',分類信息; [ , [ @objtype =] 'object_type' ]
如：EXEC sp_rename  'newname','PartStock'

Drop Table tblTaxRate
GO

Alter TABLE [dbo].[OrderPackageOption] Drop CONSTRAINT [FK_OrderPackageOption_OrderPackage]


ALTER TABLE [dbo].[OrderPackageOption] ADD
CONSTRAINT [FK_OrderPackageOption_OrderPackage] FOREIGN KEY
(
[OrderPackageID]
) REFERENCES [dbo].[OrderPackage] (
[OrderPackageID]
)
GO

ALTER TABLE [dbo].[Account] ADD
CONSTRAINT [FK_Account_State] FOREIGN KEY
(
[StateID]
) REFERENCES [dbo].[State] (
[StateID]
),
CONSTRAINT [FK_Account_TimeZones] FOREIGN KEY
(
[TimeZoneID]
) REFERENCES [dbo].[TimeZones] (
[timeid]
)
GO

Alter table [dbo].[Account] NoCheck Constraint [FK_Account_TimeZones]
GO

ALTER TABLE [dbo].[Account] With NoCheck
ADD CONSTRAINT [FK_Account_AccountType] 
FOREIGN KEY
(
[TypeID]
) REFERENCES [dbo].[AccountType] (
[TypeID]
)
GO

set identity_insert [dbo].[PSTNUsageNotes] on
--在這里執行對PSTNUsageNotes表的批量插入操作
set identity_insert [dbo].[PSTNUsageNotes] off

ALTER TABLE [Usage] NOCHECK CONSTRAINT [FK_USAGE_REFERENCE_PSTN1]
ALTER TABLE [Usage] NOCHECK CONSTRAINT [FK_USAGE_REFERENCE_PSTN2]
-- 在這里執行有可能破壞外鍵約束的操作，如強行刪除數據等
ALTER TABLE [Usage] CHECK CONSTRAINT [FK_USAGE_REFERENCE_PSTN1]
ALTER TABLE [Usage] CHECK CONSTRAINT [FK_USAGE_REFERENCE_PSTN2]

exec sp_rename 'dbo.LocationIncidentalServiceRelationship.UsageAllowedWithouReservation','UsageAllowedWithoutReservation','column'

select name from Jaguar.dbo.sysobjects where xtype='P' and name not in
(select name from JaguarTest.dbo.sysobjects where xtype='P' )

select OBJECT_NAME(id) AS name,[name] as content,xtype from sysobjects
where [name] like '%IncidentalService%' And (xtype='U' or xtype='P')

sp_help 'object_name';   
如：EXEC sp_help 'PartStock';

select name from sysobjects where type='U'

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 = '你要判斷的字段名'

select name from syscolumns where id=object_id('TableName')

if exists(select * from syscolumns where id=object_id('[dbo].[CFFund]') and name='CFInvestmentMaxAmount') 
begin
    alter table CFFund alter column CFInvestmentMaxAmount decimal(18,4) NULL;
end

即：

if exists (select * from syscolumns where id=object_id('PartStock') and name='PartVelocity')  
    print 'PartVelocity exists'
else 
    print 'PartVelocity not exists'

--判斷要添加列的表中是否有主鍵
if exists(select 1 from sysobjects where parent_obj=object_id('tb') and xtype='PK')
begin
    print '表中已經有主鍵,列只能做為普通列添加'
    --添加int類型的列,默認值為0
    alter table tb add 列名 int default 0  
end
else
begin
    print '表中無主鍵,添加主鍵列'
    --添加int類型的列,默認值為0
    alter table tb add 列名 int primary key default 0  
end

select 
    type,
    sum(case vender when A then pcs else 0 end),
    sum(case vender when C then pcs else 0 end),
    sum(case vender when B then pcs else 0 end) 
from tablename group by type

TRUNCATE TABLE tableName

select * from table1 where times between time1 and time2
select * from table1 where times not between time1 and time2

delete from table1 where not exists (
    select * from table2 where table1.field1=table2.field1
)

法一：select * into b from a where 1<>1
法二：select top 0 * into b from a

insert into b(a, b, c) select d,e,f from b;

insert into b(a, b, c) select d,e,f from b in ‘具體數據庫’ where 條件

例子：..from b in "&Server.MapPath(".")&"\data.mdb" &" where..

CREATE DATABASE database name

drop database dbname

--- 創建備份數據的 device
USE master
EXEC sp_addumpdevice disk, testBack, c:\mssql7backup\MyNwind_1.dat
--- 開始 備份
BACKUP DATABASE pubs TO testBack

create table tabname(col1 type1 [not null] [primarykey],col2 type2 [not null],..)

create table tab_new like tab_old (使用舊表創建新表)

create table tab_new as select col1,col2… from tab_old definition only

drop table tabname

Alter table tabname add column col type

Alter table tabname add primary key(col)

Alter table tabname drop primary key(col)

create [unique] index idxname on tabname(col….)

drop index idxname

create view viewname as select statement

drop view viewname

總數：select count * as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

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 n

select * from 日程安排 where datediff(minute,f開始時間,getdate())>5

(select a from tableA ) except (select a from tableB) except (select a from tableC)

select top 10 * from tablename order by newid()

SET IDENTITY_INSERT ON INSERT INTO (xxxx) values(xxxx) SET IDENTITY_INSERT OFF