sql server 的一些操作修改（会一直更新补充）

1快速修改一列的所有内容 

update 列表 set 列名 = 值

2删除表内所有内容

delete from 表名

3 将一个表复制到另一个表（不同数据库迁移）

示例代码：

SELECT  [stock_code]
      ,[stock_date]
      ,[factor_name]
      ,[factor_value]
      ,[frequency]
      ,[update_time]
      ,[flag]
      ,[stamp]
	  into [stk_per_attribution].[dbo].[t_stock_factor]
  FROM [chinatimes_fof_bak].[dbo].[t_stock_factor]

4 处理百万级别的数据迁移，将csv数据导入数据库或者其他

bulk insert [dbo].[t_stock_factor_test]
from 'F:\\因子数据库\\roa_ttm.csv'
with(
    FIELDTERMINATOR=',',
    ROWTERMINATOR='\n' ,
    FIRSTROW=2,
		BATCHSIZE = 100000
)

 5创建表

CREATE TABLE [dbo].[t_stock_factor_test] (
[id] char(6) NOT NULL ,
[stock_code] char(6) NOT NULL ,
[stock_date] char(8) NOT NULL ,
[factor_name] varchar(200) NOT NULL ,
[factor_value] FLOAT NULL
[stamp] datetime NOT NULL 
)

 6搜索唯一不重复的内容

select distinct 列名 from 表名

7更改数据表列名

exec sp_rename '[表名].[列名]','[表名].[新列名]'

8  将两个不相同的数据表导进去数据

insert into A表数据库名.[dbo].A(a,b,c)(select a,b,c from B表数据库名.[dbo].B)

insert into [stk_per_attribution].[dbo].[t_stock_factor_spare](stock_code,stock_date,factor_name,factor_value,frequency,update_time,flag,stamp)(select stock_code,stock_date,factor_name,factor_value,frequency,update_time,flag,stamp from [stk_per_attribution].[dbo].[t_stock_factor_spare1])


insert into [perf_attr_stock].[dbo].[t_stock_factor](stock_code,stock_date,factor_name,factor_value,frequency,update_time,flag,stamp)(select stock_code,stock_date,factor_name,factor_value,frequency,update_time,flag,stamp from [perf_attr_stock].[dbo].[t_stock_factor_test])

9更改数据表列的类型 

alter table 表名 alter column 列名 数据类型

ALTER TABLE 表 ALTER COLUMN 列名 类型

10追加一列

ALTER TABLE table_name ADD column_name datatype

11csv,Excel和sql server默认编码问题

对于数字类型，excel和csv的编码都是数值型；但对于字符串和汉字类型的单元格，excel的默认编码为unicode，csv的默认编码为str。sql server默认的为utf-8

12用来检验两列数据是否相等

select * from t_stock_ETF where substitution_amount_for_purchase not in(select redemption_substitution_amount from t_stock_ETF)

13替换sqlserver某列数据中的某个字符，比如“2019-03-27”变成“20190327”

update t_stock_constituent_in_index set  date = replace(date,'-','')

14删除某列

alter table 表名 drop column 列名

15在指定列替换内容

update 表名 set 字段名=replace(字段名,' 替换的内容','要替换的内容')

16多字段删除重复项

1加递增主键ID
alter table table_name add column id int(11) PRIMARY KEY AUTO_INCREMENT;


alter table table_name add id int identity(1,1)

2删除重复项
DELETE from table_name where id in ( SELECT id from (Select min(ID) as id From table_name  Group By index_code,index_name,stock_code,date(能够剔除的重复字段) Having Count(*)>1) as yu)
3删除ID
ALTER table table_name DROP column id;

17多字段查找重复数据

SELECT * from t_stock_day where  EXISTS (SELECT stock_code,stock_date From t_stock_day Group By stock_code,stock_date Having Count(*)>1)

18查询长度

SELECT stock_code from t_stock_info where len(stock_code)<长度

 19sql server 在数据库操作字符长度不足六位左边补零

update t_stock_info set stock_code = right(cast('000000'+rtrim(stock_code) as varchar(20)),6) from t_stock_info where len(stock_code)<6

20sql server varchar 转换浮点数查询

SELECT factor_name,factor_value from t_stock_factor where  Convert(numeric(18,18),factor_value)<1 
SELECT factor_name,factor_value from t_stock_factor where CAST(factor_value AS DECIMAL(18,18))<1 

21查询字符串小数点后位数

select factor_value,factor_name from t_stock_factor where (len(factor_value)-charindex('.',factor_value))>8 and stock_date>'20190101' ORDER BY len(factor_value)

22  根据数据库某一列和另一个数据表来填充一列的异常数据做法

SELECT  [Programme_progress]
      ,[ex_dividend_date]
    ,[ex_dividend_date1] = ( SELECT  top 1  cast([trade_date] as date) 
               FROM [perf_attr_stock].[dbo].[trade_calendar]
               where cast([trade_date] as date) > cast(a.[Programme_progress]  as date)
               order by cast([trade_date]  as date)  ) 
      ,[Equity_registration_date]
       
  FROM [perf_attr_stock].[dbo].[t_stock_dividends] a 
  where [ex_dividend_date] = '--'
  and [Equity_registration_date] = '实施'
  and [Programme_progress] <> '--'

23查看数据表重复个数（单列）

select 列名,count(*) from  表名  group by 列名 having count(*)>1

24设置自动增长主键

[id] int identity(1,1) primary key,

25设置默认值字段设置主键

[id] int not null default 0 primary key,

26追加主键

alter table t_stock_index_day add constraint PK_t_stock_index_day_date primary key(stock_date)


alter table t_stock_index_day add constraint PK_t_stock_index_day_code primary key(stock_code)

27 删除表中的列

ALTER TABLE table_name 
DROP COLUMN column_name

28 RANK()函数

RANK()函数是一个Window函数，它为结果集的分区中的每一行分配一个排名，用法如下

RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

示例： 

29  删除主键及其约束

ALTER TABLE 表名DROP CONSTRAINT 约束名