创建表、插入数据、更新数据

常见创建表语法

create table [表名]
(
[自动编号字段] int IDENTITY (1,1) PRIMARY KEY ,
[字段1] nVarChar(50) default \'默认值\' null ,
[字段2] ntext null ,
[字段3] datetime,
[字段4] money null ,
[字段5] int default 0,
[字段6] Decimal (12,4) default 0,
[字段7] image null ,
)

根据当前表创建新表

新表不存在

select CLU,MCE,[CM Rev],公式1
into [TS].[dbo].[LM_MCBASE_test]
from [TS].[dbo].[LM_MCBASE];

select * 
into [TS].[dbo].[LM_MCBASE_test]
from [TS].[dbo].[LM_MCBASE];

新表存在

select [col1,col2,col3...] 
into new_table 
from old_table where 1=1   

插入数据

insert into Student_back(S_StuNo,S_Name,S_Sex,S_Height)
select S_StuNo,S_Name,S_Sex,S_Height  
from Student
where S_Sex='s'

更新数据

1.不同表的修改

update a 
set a.[公司法人] = b.a_1,a.[合同签署人] = b.a_2
from [Online].[dbo].[T_Feedback_eCommerce] a
    inner join [Test].[dbo].[acct_temp_3_yuxin] b on a.Acct_No = b.acct

2.表的自连接更新

update v1
set RecommendName=v1.NickName
from Member_Info v1  
    inner join Member_Info v2 on v1.MemberId=v2.RecommendId

3.关联表先汇总，然后更新字段

update t1
set InterestFreeze-=Amount,InterestThaw+=Amount
from Member_Info t1
    inner join (
        select MemberID,sum(Amount) as Amount 
        from Member_Interest
        where Type=1 and DaySum=10
        group by MemberID
        )  t2
on t1.MemberId=t2.MemberID