SQL Server笔记

01

数据库一般用三层架构

sdata（源数据，临时表层），pdata（物理数据，经过一定的处理），pmart（市场库，可以用的数据）

建表

create table username 
(
name varchar(100)
,age int
,address varchar(100)
,money float
)

插入数据

insert into username
(
name  # 一定要写列名，避免出错，列名和列值一一对应
,age
,address
,money
)
values
(
'alex'
,18
,'北京001'
，200000
)

简单查询

select *   from username --  * 范指，全部列，查询所有数据
select * into #testuser from username -- #testuser是指建立临时表，会话级别，只在程序运行时存在，没有主键
select * from #testuser
select distinct * from username   -- 去重使用distinct
select top 100 * from username -- 生产库上查询必须使用top，避免数据过多造成严重后果

sql server 建表低层工具bcp

本地 bcp sdata.dbo.Shipped_Order_20180306  in D:\培训\shipment.txt -c  -S"YANHEPING-PC\SQLEXPRESS"  -T

服务器 bcp $SdataTable in $DATA_FILE -F 2 -b 3500 -N -r -f ${jobPath}/${TableName}.fmt -S \"$MSSQL_DbServer\" -U \"$MSSQL_DbUser\" -P \"$MSSQL_DbPasswd\

-F从第二行开始， -b一次插入数量，.fmt是格式，-S 服务器，-U 用户，-P 密码

ultraEdit编辑工具ctr+R替换，ctr+f查找，列块模式快速将xlsx数据表头做成表的字段，若出现乱码问题，数据源使用utf-8编码，字段类型使用nvarchar，区别https://www.cnblogs.com/gdouzz/p/7094522.html

02

强制类型转换 cast（a as decimal(18, 4)）--- int，float，bigint

函数 count(1), min, max, avg

left jon

select 
 a.total_now
,b.total_last
,(a.total_now-b.total_last)*1.0/b.total_last*100.0
,CAST((a.total_now-b.total_last)*1.0/b.total_last*100.0   as decimal(18,2)) -- 除数要检查是为0
from 
 (
 select COUNT(1) total_now from SDATA.DBO.open_order_report20180307
  ) a 
left join
 (
  select COUNT(1) total_last  from  SDATA.DBO.open_order_report20170306
  ) b 
 on  1=1 -- 一定可以连接上

03

group by 分组查询，count，avg，min，max，sum，只有当select后面没有字段时可以不使用，其他必须使用select后面非函数的字段

convert，日期格式转换

getdate()，获取当前日期

order by，默认asc升序，降序desc

同比，同期的比，（今年的值-去年的值）*1.0/去年的值*100.0；环比，近期的比，（本月的值-上月的值）*1.0/上月的值*100.0

case when

建新表，可以insert into table1 select... from，也可以select ... into table1 from

 
 delete from pdata.dbo.open_order_report_ops where date_stamp= CONVERT(varchar(12) , getdate(), 111 ) -- 日期转换
 
 insert into   pdata.dbo.open_order_report_ops -- 建新表
 select  
   CONVERT(varchar(12) , getdate(), 111 )   date_stamp -- 别名
  ,product_number
  ,source_order_line_flow_status
  ,COUNT(1) all_count  
  ,MAX(cast( ordered_qty as  bigint) ) max_number
  ,sum(cast( ordered_qty as  bigint) ) sum_number 
  ,MIN(cast( ordered_qty as  bigint) ) min_number
  ,AVG(cast( ordered_qty as  bigint) ) avg_number
  from SDATA.DBO.open_order_report20180307
  group by product_number,source_order_line_flow_status
order by Product_Number asc

select 
 a.date_stamp
,a.product_number
,a.source_order_line_flow_status
,a.all_count now_count
,b.all_count last_year_count
,case when b.all_count<>0   -- 条件满足时和不满足时，相当于if
        then (a.all_count -b.all_count )*1.0/b.all_count*100.0
     else  0 
end  year_over_year
into pmart.dbo.rpt_open_order_report -- 插入到新表
from 
(
 select 
  * 
 from pdata.dbo.open_order_report_ops
 where date_stamp='2018/03/08'
 ) a 
left join 
( select 
  * 
 from pdata.dbo.open_order_report_ops
 where date_stamp='2017/03/08'
 )b 
on  A.product_number =b.product_number
and a.source_order_line_flow_status =b.source_order_line_flow_status

04

group by 求累计

having，先分组，再筛选，必须使用group by

提取字符串函数，substring(字段，1，3)，从第1个开始，提取3个

select  -- 求用户每月总金额
 username
,MONTH
,SUM(salary) money-- 用户总金额
from sdata.dbo.t_access_times
group by 
username
,MONTH
order by 
username asc
,MONTH


select -- 求用户截止当月累计报销，自己与自己连接，满足a.month>=b.month，即每一条a.month都会匹配到多个比它小的数据，然后group by a.month
 a.username
,a.month
,SUM(b.money) -- 截止当月报销的钱
from (select
username
,MONTH
,SUM(salary) money-- 用户总金额
from sdata.dbo.t_access_times
group by 
username
,MONTH
)a
left join (
select
username
,MONTH
,SUM(salary) money -- 用户总金额
from sdata.dbo.t_access_times
group by 
username
,MONTH
)b
on a.username = b.username 
and a.month>=b.month
group by
 a.username
,a.month
order by
a.username

select username,MONTH,COUNT(1) count from   sdata.dbo.t_access_times
group  by username,MONTH
having COUNT(1)>3  -- having

06

加载算法，1全量覆盖（有风险），2追量更新（最安全），3拉链（一段时间客观存在的状态）

-- 全量覆盖
truncate table aaa 
delete from aaa

insert into aaa 
select * from sdata._xxxxx

-- 追量更新
-先有条件的删除
delete from	pdata.dbo.customer
where EXISTS(
   SELECT 1 FROM sdata.dbo.customer$DATE_TODAY B
     WHERE pdata.dbo.customer.Name 	  = b.Name 	
       and pdata.dbo.customer.type 	= b.type 	
    );

-再插入
insert into pdata.dbo.customer （）select from sdata.dbo.customer$DATE_TODAY 

07

追量更新的优化算法，先删除有改变的记录，再使用新表与旧表左连接，然后插入旧表字段为空的数据（即旧表中没有的数据）

windows定时计划

delete from	WW_PDATA.dbo.T_Koala_IQS_MMI --先删哪些有变化的数据
where EXISTS(
   SELECT 1 FROM WW_SDATA.dbo.T_Koala_IQS_MMI$DATE_TODAY B
                            WHERE WW_PDATA.dbo.T_Koala_IQS_MMI.MACH_TYPE	= b.MACH_TYPE	
   and WW_PDATA.dbo.T_Koala_IQS_MMI.MACH_MDL	= b.MACH_MDL	
   and WW_PDATA.dbo.T_Koala_IQS_MMI.CTY_CODE	= b.CTY_CODE	
   and WW_PDATA.dbo.T_Koala_IQS_MMI.YYYYMM		= b.YYYYMM	
   and (
         WW_PDATA.dbo.T_Koala_IQS_MMI.DATE_WAR_MOS_LEFT         <>  b.DATE_WAR_MOS_LEFT
      or WW_PDATA.dbo.T_Koala_IQS_MMI.DATE_WAR_MMI	            <>  b.DATE_WAR_MMI	                    
      )	
);

INSERT	INTO WW_PDATA.dbo.T_Koala_IQS_MMI -- 再插入新的并且在旧表中不存在的数据
  (
   MACH_TYPE	
	)
 SELECT
	 a.MACH_TYPE	
	FROM WW_SDATA.dbo.T_Koala_IQS_MMI$DATE_TODAY a 
  Left join WW_PDATA.dbo.T_Koala_IQS_MMI b 
  on  a.MACH_TYPE	= b.MACH_TYPE	
  and a.MACH_MDL	= b.MACH_MDL		
  where b.MACH_TYPE is null   -- 左连接旧表中没有的记录

08

row_number over(partition by 字段 order by 字段) as rows，对每个分组的字段添加序号，不加partition对所有添加序号

对于每天都要处理的数据，在处理后的表中添加数据update_time字段，知道每条数据是哪个日期的源数据更新的

select -- 取分组中分数最大的一条记录
a.* 
from (
select ROW_NUMBER() over(partition by name order by score desc)as rows
,* from  sdata.dbo.t
)a
where a.rows = 1