数据库操作2

 
1.备份数据至新表进行操作
  select * into Mark_Level from m_Level 
  select * into Mark_ReadingOption from m_ArticleOption


  select * into YUBIN_MaterialLevel from MaterialLevel
  select * into YUBIN_ReadingMaterial from ReadingMaterial
  select * into YUBIN_ReadingQuestion from ReadingQuestion

2.truncate table Mark_ReadingOption


3.向Mark_Level 中添加MaterialLevel字段
update Mark_Level set MaterialLevel=(LevelNo+8)


4.向YUBIN_ReadingMaterial表中加入 LevelId 字段
 
查询 select a.materialLevel,a.LevelId,b.MaterialLevel,b.Id from YUBIN_ReadingMaterial as a join 
  Mark_Level as b on a.materialLevel = b.MaterialLevel


更新 update a set a.LevelId=b.Id from YUBIN_ReadingMaterial as a join 
  Mark_Level as b on a.materialLevel = b.MaterialLevel  


5.向YUBIN_ReadingQuestion表中加入ArticleId字段


查询 select * from YUBIN_ReadingQuestion as a join YUBIN_ReadingMaterial as b on a.materialCode=b.materialCode 


更新 update a set a.ArticleId=b.Id from YUBIN_ReadingQuestion as a 
join YUBIN_ReadingMaterial as b on a.materialCode=b.materialCode 




6.向Mark_ReadingOption表中加入Answer字段，导入选项数据
  insert into Mark_ReadingOption(QuestionId,Body,Sn,Answer) select Id,optionA,'A',answer from YUBIN_ReadingQuestion 
  insert into Mark_ReadingOption(QuestionId,Body,Sn,Answer) select Id,optionB,'B',answer from YUBIN_ReadingQuestion 
  insert into Mark_ReadingOption(QuestionId,Body,Sn,Answer) select Id,optionC,'C',answer from YUBIN_ReadingQuestion 
  insert into Mark_ReadingOption(QuestionId,Body,Sn,Answer) select Id,optionD,'D',answer from YUBIN_ReadingQuestion 


 删除内容为空的选项 delete from Mark_ReadingOption where body is null


7.填入答案  
update Mark_ReadingOption set IsAnswer=1 where Sn=Answer
删除 Answer 字段


8.整理4张数据表，改为以a开头的对应表，备份为b表。


9.修改a表相应的字段。


10.数据检查


select a.Name,b.title,b.body from a_Level as a join a_Article as b on b.LevelId=a.Id
where b.title='A Clever Bird'


select a.Name,b.title,b.body,c.body from a_Level as a join a_Article as b on b.LevelId=a.Id 
join a_ArticleQuestion as c on c.ArticleId=b.Id
where b.title='A Clever Bird'


select a.Name,b.title,b.body,c.body,d.body,d.IsAnswer from a_Level as a join a_Article as b on b.LevelId=a.Id
join a_ArticleQuestion as c on c.ArticleId=b.Id
join a_ArticleOption as d on d.QuestionId=c.Id
where b.title='A Clever Bird' order by c.body