常用SQL语句

--主键
ALTER TABLE [GBTerminal].[TerminalDec] DROP CONSTRAINT [PK__Terminal__C451DB3124C84789];
ALTER TABLE [GBTerminal].[TerminalDec] ADD  CONSTRAINT [PK_TerminalDec] PRIMARY KEY CLUSTERED
(
     [Tid] ASC
)
GO

--建唯一索引 注意sp_helpconstraint不会显示索引，要用sp_helpindex
DROP INDEX [IX_TerminalDec_useid] ON [GBTerminal].[TerminalDec]
CREATE UNIQUE NONCLUSTERED INDEX [IX_TerminalDec_useid] ON [GBTerminal].[TerminalDec]
(
     [AreaCode] ASC,
     [useid] ASC
)
drop index IX_user on meter
CREATE UNIQUE NONCLUSTERED INDEX IX_F10_1 ON GBTerminal.F10     (tid,txdz)
    
--外键    
ALTER TABLE GBTerminal.F27 WITH CHECK ADD CONSTRAINT [FK_F10] FOREIGN KEY(tid,cldh)
REFERENCES GBTerminal.f10(tid,cldh)
ON UPDATE CASCADE ON DELETE no action

--check检查
alter table GBTerminal.F11
add constraint CK_mcsx check(mcsx<4)

--默认值
ALTER TABLE GBTerminal.F10 ADD CONSTRAINT
DF_F10_txmm DEFAULT ('1') FOR txmm

--更改架构
alter schema GBTerminal transfer dbo.f27

--链接服务器
EXEC master.dbo.sp_addlinkedserver @server = N'58.18.173.3', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'58.18.173.3',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'

--select * from [58.18.173.3].ch51.dbo.meter where rtu_id=11
go
--更改sa密码（已知sa现密码）用sa登录，旧密码写null。可以更改任何用户的密码。
exec sp_password Null,'newpassword,'sa'
go
--给视图加扩展属性
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'电表视图' ,
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_meter'
go

--给表字段加扩展属性
EXEC sys.sp_addextendedproperty
@level0type=N'SCHEMA', @level0name=N'GBTerminal',
@level1type=N'TABLE',@level1name=N'f10',
@level2type=N'COLUMN',@level2name=N'Stamp',
@name=N'MS_Description',@value=N'总分表类型';

--case语句
declare @x numeric(6,2);
declare @y numeric(6,2);
set @x=35;
set @y=case when @x=0 then 0 else 20/@x end;
select @y as result;

--用exists做判断 
if exists(select m_id from meter where M_Id=22222)
    select 'yes'
else
    select 'no'

--修改列
alter table daylinelose alter column tran_id int not null
alter table daylinelose alter column powerdate smalldatetime not null

--查看一个数据表的触发器
sp_depends 'DelMeterTrigger'
sp_helptrigger 'meter'
--查看数据中所有的触发器
select * from sys.objects where type='tr'

--分组与having
select rtu_id,COUNT(*) from meter where rtu_id>9000 group by rtu_id having COUNT(*)>900