常见创建表语法
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
