数据库操作1

1.清空一张数据表
truncate table word(慎用) 


2.找出所有带有单引号的单词（单引号用两个单引号转义）
select * from Word where soundMarkUs like '%''%' 


3.调用replace函数将双引号替换成单引号
update Word  set soundMarkUs=REPLACE(soundMarkUs,'"','''') where soundMarkUs like'%"%'
  
4.备份一张数据表
select * into Course_bak from Course


5.显示一张表中去重之后的数据
select distinct spelling from Word


6.查询一张表中重复的数据
select * from Word where spelling in (select spelling from Word group by spelling having count(1) >= 2)


7.通过某个字段按字母顺序排序：ASC-升序(A-Z)；DESC-降序(Z-A)
select * from Word order by spelling asc
  
8.把字段数据类型由varchar改为nvarchar可以解决一些乱码的问题


9.查找存在于一张表而 不存在于另外一张表的数据
  select spelling from temp where spelling not in(select spelling from temp1) 


10.给vocCode字段设置一个UID

  update WordBak set vocCode=NEWID() where Id<=300

11.给temp1表中的spelling字段按顺序加一个编号放入temp2中
select row_number() over(order by spelling)as num,spelling  into temp2 from temp1

文件批处理：将本文件夹下面的所有文件的名称列入到ZZZ.TXT文件中
DIR *.* /S/B> ZZZ.TXT




//select * from Course where bookName='零基础入门拼读'
//select * from CourseUnit where moduleCode='8A108CB7-42AD-314F-0142-AD33A0A70001'
//select * from UnitWord where UnitId in(select Id from CourseUnit where moduleCode='8A108CB7-42AD-314F-0142-AD33A0A70001')
//select * from Word where Id in(select WordId from UnitWord where UnitId in(select Id from CourseUnit where moduleCode='8A108CB7-42AD-314F-0142-AD33A0A70001'))


//按单元获取一本书的数据
  select Unit,spelling,meaning,soundMarkUs from (  
  select Unit,UnitId,WordId,vocCode from (select * from CourseUnit where moduleCode='8A108CB7-4C56-483D-014C-77F8010A58B3') as cu 
  join UnitWord as uw on cu.Id=uw.UnitId ) as cuw 
  join Word on cuw.vocCode=Word.vocCode


//按单元获取多本书的数据
  select a.bookName,b.OrderNum,d.spelling,meaning,soundMarkUs from Course as a 
  join CourseUnit as b on b.moduleCode=a.moduleCode
  join UnitWord as c on c.UnitId= b.Id
  join Word as d on d.Id=c.WordId
  where a.bookName like '%大数据%' order by bookName


删除一个单词
 select * from UnitWord where WordId=230689
 delete from Word where Id=230689
 delete from UnitWord where WordId=230689


superteacher 13528815950@2017


==========================正式表=============================
1.往Course表中插入一本书并生成GUID
update Course set moduleCode=NEWID() where Id=99999999


2.往CourseUnit表中插入各个单元
  
  Declare @i int
  set @i=1
  while @i<=6
  begin
  insert into CourseUnit(moduleCode,Unit,OrderNum) values('376F07F8-C28D-','Unit'+CONVERT(char(1),@i),@i)
  set @i = @i+1
  end


3.将课本的moduleCode内容放入到belongTo字段中，往Word表中导入各个单元的单词并生成vocCode
 语句：update Word set vocCode=NEWID() where belongTo='55405494-38BA-4EF5-B97A'
(课本字段spelling,meaning,remark,belongTo)


4.循环往UnitWord表中插入Unit和Word的一个单元的单词关联
  
  Declare @i int
  set @i=1
  while @i<=6
  begin
  insert into UnitWord (UnitId,WordId,vocCode,GroupNum)
  select c.ID,w.Id,vocCode,0 from CourseUnit c ,Word w where c.moduleCode='376F07F8' and c.OrderNum=@i
  and c.OrderNum = w.remark  and w.belongTo='376F07F8'
  set @i = @i+1
  end




=====================================================


从本数据库中查找已经有的单词，把它的meaning更新到这本书中


update Word set meaning = nt.meaning
from Word w
join (
select wo.spelling,wo.meaning,wo.soundMarkUs,ROW_NUMBER() over(partition by spelling order by Id) rn from Word wo
where wo.soundMarkUs is not null and spelling is not null
) nt on nt.spelling = w.spelling and nt.rn = 1
join UnitWord uw on uw.vocCode = w.vocCode
join CourseUnit cu on cu.Id = uw.UnitId
join Course c on c.moduleCode = cu.moduleCode
where c.bookName = '托福提高词汇2'


===================分音节==================


将moduleCode、vocCode、soundMarkUs、meaning 全部粘贴到分音节excel表中，导入


=======================阅读赢======================
1.删除错误数据
  delete from ReadingQuestion where materialCode='88568453'
  delete from ReadingMaterial where materialCode='88568453'


====================================================
select a1.Id,a1.Title,a1.Body,a1.imageUrl,a1.Description,a1.DocumentResource,b2.Body,c3.Body ,c3.IsAnswer
from m_Article as a1 
join m_ArticleQuestion as b2 on b2.ArticleId = a1.Id 
join m_ArticleOption as c3 on c3.QuestionId = b2.Id
where a1.Id > 5 and b2.Id > 5