sql server常用

1、替换所有字符串

update TB_EB_GOODS(表名) set [img_path(字段)]=Replace(Cast([img_path(字段)] as nvarchar(4000)),'xcx.ylslw.vip(要替换的)','bspf.fxsoft88.com(替换后的)');

2、复制表结构和数据

select * into (自定义新表) from (要复制的表);

3、修改表结构

ALTER TABLE (表) ALTER COLUMN (修改的列) VARCHAR(30) not null(不允许为null);

4、添加表字段(表中没有这个字段,就添加,有就不会执行)

IF COL_LENGTH('(表)', '(字段)') IS  NULL Alter table 表 add 地段 int default 0;

5、添加表(没有表就添加)

if OBJECT_ID(N'm_pricebuy1(表名)',N'U') is null
CREATE TABLE [dbo].[m_pricebuy1(表名)] (
[ID] varchar(255) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Name] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[BeginTime] datetime NULL,
[Useflag] int NULL,
CONSTRAINT [PK__m_pricebuy1(表名)] PRIMARY KEY CLUSTERED ([ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
);

 6、给已有的字段添加默认值(0)

IF NOT EXISTS (
select * from sysobjects where parent_obj in(
select id from sysobjects where name='m_vip(表名)') and name = 'DF_m_vip_frequency(约束名)')
ALTER TABLE m_vip add constraint DF_m_vip_frequency(约束名) DEFAULT 0 for frequency(字段)

 7、表中没有这条数据就添加

if exists( select  1  from 表  where  code = 13) begin update  表  set  updatedtime= GETDATE()  where  code = code end else begin insert  表 (id,code,name,no,useflag) VALUES (13,13,'ki16','jingdong',1) end;

  if not exists(select 1 from m_menu where url = 'coupons') begin insert m_menu (name,createtime, creator, parentid, url, isleaf, indexno) VALUES ('券包', getdate(), 'sys', '21', 'coupons', '0', '7') end;

 8、修改表字段的长度

ALTER TABLE m_department ALTER COLUMN Longitude VARCHAR(30);

 9、按照给的顺序排序

  select goodsid, retailsales, extid from goods where 1=1  and goodsid in('003ZW','00404')  order by charindex(ltrim(goodsid),'003ZW,00404')

 10、分布函数(应用场景，例如：订单同个用户有多条记录，取每个用户的最近一条)

select * from (
select *,rank() over(partition by vipid（分区字段） order by createtime(排序) desc) as dd from m_order(表)
) d where dd = '1'

 11、添加唯一索引

CREATE UNIQUE INDEX index_name(索引名) ON Customer(表) (MobilePhone(字段))

12、删除唯一索引

drop index index_name(索引名) on Customer(表名);