SQL 常用操作~~

项目介绍： 从FTP 下载压缩文件，解压到相应目录，然后从该目录parser xml 到DB

常用SQL 操作： 

1. select instrument id, count(distinct cusip)  from table where name like '% corp%'  group by company_idhaving count(distinct cusip)>=2order by count(distinct cusip) desc

2 .select * from table1 inner join table 2 on table1.isin = table2.isin

3. insert into table1(cloumn1, cloumn2) select cloumn1,cloumn2 from table2 where isin in ()

4. drop table1  / truncate table 

5.update table1

set cloumn1 = value

where ....

6. delete from table 

where ....

go 

/* create table with primary key*/
USE ExtFeedData
go
If EXISTS (select * from sys.objects WHERE OBJECT_ID = OBJECT_ID(N'dbo.TEST_data')and type in(N'U'))
DROP TABLE dbo.TEST_data
GO
CREATE TABLE dbo.TEST_data
(
number INT not NULL,
Bdate DATE not NULL,
category CHAR(200) NULL,
score INT NULL,
/*primary key (number), 单个 primary key 约束*/
CONSTRAINT pk_TEST_data PRIMARY KEY (number,Bdate),/*多个 primary key 约束*/
)
/* 撤销primary key */
alter table dbo.TEST_data
drop constraint pk_TEST_data 


insert into dbo.TEST_data values (1,'2017-02-16','English',29)
insert into dbo.TEST_data values (2,'2017-02-16','English',30)
insert into dbo.TEST_data values (2,'2017-02-16','English',35)
insert into dbo.TEST_data values (1,'2017-02-16','Chinese',56)
insert into dbo.TEST_data values (2,'2017-02-16','Chinese',67)
insert into dbo.TEST_data values (2,'2017-02-16','Chinese',68)


select * from dbo.TEST_data where score>(select AVG(score)from dbo.TEST_data)


/* 备份复制*/
select * into new_table_name
from old_table

/*分类并统计*/

select   sec_type,count(distinct(instrument_id))  from [XXX].dbo.XXX_XXX group by sec_type

/*JOIN*/

gSELECT a.StrategyId
     ,b.CompanyId
     ,b.Name
     ,b.Status
FROM  CurrentData.dbo.XXX AS a
INNERJOIN  CurrentData.dbo.YYY  AS b
ON a.ProviderCompanyId= b.CompanyId