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
文件批处理:将本文件夹下面的所有文件的名称列入到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
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