lcsf数据库整理sql语句

2011年7月21日  数据库整理所用sql语句

select * from user_tab_comments 
-- 查询本用户的表,视图等

select 'select * from   ' || table_name || ' ;          truncate table  ' || table_name from user_tab_comments;

select * from   D_BANKTYPE ;                 delete from   D_BANKTYPE  a where  a.issystem = 0;
select * from   D_BRANCH ;                   delete from   D_BRANCH  a where  a.issystem = 0;
select * from   D_COMPARE ;         
select * from   D_DEPSTAGE ;                 drop table  D_DEPSTAGE;
select * from   D_DICCONTENT ;        
select * from   D_DICTYPE ;                  truncate table  D_DICTYPE;
select * from   D_DIFFICULTYLEVEL ;          delete from  D_DIFFICULTYLEVEL a where  a.issystem = 0;
select * from   D_ESTIMATEITEM ;             truncate table  D_ESTIMATEITEM ;
select * from   D_EXAMDATETYPE ;             truncate table  D_EXAMDATETYPE ;
select * from   D_EXAMTYPE ;                 truncate table  D_EXAMTYPE ;
select * from   D_FRAME ;                    truncate table  D_FRAME;
select * from   D_GRADE ;                    truncate table  D_GRADE;
select * from   D_HONORLEVEL ;               truncate table  D_HONORLEVEL ;
select * from   D_IMAGE ;                    truncate table  D_IMAGE ;
select * from   D_INFORMATIONTYPE ;          truncate table  D_INFORMATIONTYPE;
select * from   D_MASTERLEVEL ;              delete from D_MASTERLEVEL a where  a.issystem = 0;
select * from   D_PAPERTEMPLATE ;            truncate table  D_PAPERTEMPLATE ;
select * from   D_PAPERTYPE ;                delete from  D_PAPERTYPE a where  a.issystem = 0;
select * from   D_PROVINCE ;                 truncate table  D_PROVINCE;
select * from   D_QUESTIONTYPE ;         
select * from   D_RECORDTYPE ;               truncate table  D_RECORDTYPE;
select * from   D_ROLE ;                     truncate table  D_ROLE;
select * from   D_STAGE ;                    delete from  D_STAGE a where  a.issystem = 0;
select * from   D_SUBJECT ;                  delete from D_SUBJECT a where  a.issystem = 0;
select * from   D_SYSFUNCTION ;              drop table  D_SYSFUNCTION;
select * from   D_SYSMENU ;                  drop table  D_SYSMENU;
select * from   D_SYSMODULE ;                drop table  D_SYSMODULE;
select * from   D_VERSION ;                  delete from  D_VERSION a where  a.issystem = 0;
select * from   D_ZONECODE ;                 truncate table  D_ZONECODE;
select * from   R_ANSWER ;                   truncate table  R_ANSWER;
select * from   R_ANSWERSET ;                truncate table  R_ANSWERSET ;
select * from   R_ASSESS ;                   truncate table  R_ASSESS ;
select * from   R_AXTCATALOG ;               truncate table  R_AXTCATALOG;
select * from   R_AXTCOMMENT ;               truncate table  R_AXTCOMMENT;
select * from   R_AXTSECTION ;               truncate table  R_AXTSECTION ;
select * from   R_BANKCHECKSET ;             truncate table  R_BANKCHECKSET ;
select * from   R_CATALOGKPOINT ;            truncate table  R_CATALOGKPOINT;
select * from   R_CLASSWORK ;                truncate table  R_CLASSWORK ;
select * from   R_COMMENT ;                  truncate table  R_COMMENT;
select * from   R_CONCERNSUBJECT ;           truncate table  R_CONCERNSUBJECT ;
select * from   R_CONNECT ;                  truncate table  R_CONNECT;
select * from   R_DAILYEXAMSCORE ;           truncate table  R_DAILYEXAMSCORE ;
select * from   R_DAILYFINALSCORE ;          truncate table  R_DAILYFINALSCORE ;
select * from   R_DAILYSCORE ;               truncate table  R_DAILYSCORE ;
select * from   R_DEPARTMENTINFO ;           delete from   R_DEPARTMENTINFO a where a.depid != '00000001';
select * from   R_DEPTIMAGE ;                truncate table  R_DEPTIMAGE ;
select * from   R_DEPTSTAGE ;                delete from   R_DEPTSTAGE  a where a.depid != '00000001';
select * from   R_DEPTSTYLE ;                truncate table  R_DEPTSTYLE ;
select * from   R_EDUINFOR ;                 truncate table  R_EDUINFOR ;
select * from   R_ESTIMATETEMPLATE ;         truncate table  R_ESTIMATETEMPLATE ;
select * from   R_ESTIMATETEMPLATEITEM ;     truncate table  R_ESTIMATETEMPLATEITEM ;
select * from   R_EVALUATEARITHMETIC ;       truncate table  R_EVALUATEARITHMETIC ;
select * from   R_EVALUATEFREQUENCY ;        truncate table  R_EVALUATEFREQUENCY ;
select * from   R_EVALUATEITEMSCORE ;        truncate table  R_EVALUATEITEMSCORE;
select * from   R_EVALUATESET ;              truncate table  R_EVALUATESET ;
select * from   R_EVALUATESTATE ;            truncate table  R_EVALUATESTATE ;
select * from   R_EXAMKPOINT ;               truncate table  R_EXAMKPOINT;
select * from   R_EXAMPAPER ;                truncate table  R_EXAMPAPER;
select * from   R_EXAMQUESTION ;             truncate table  R_EXAMQUESTION;
select * from   R_FALLIBLEQUESTION ;         truncate table  R_FALLIBLEQUESTION ;
select * from   R_FOCUSNEWS ;                truncate table  R_FOCUSNEWS;
select * from   R_FRIENDLINK ;               truncate table  R_FRIENDLINK
select * from   R_GRADECLASS ;               truncate table  R_GRADECLASS;
select * from   R_GROWUPFILE ;               truncate table  R_GROWUPFILE;
select * from   R_GROWUPINFO ;               truncate table  R_GROWUPINFO ;
select * from   R_HOMEWORK ;                 truncate table  R_HOMEWORK ;
select * from   R_IMAGE ;                    truncate table  R_IMAGE ;
select * from   R_INFORMATION ;              truncate table  R_INFORMATION ;
select * from   R_INFORMATIONTYPE ;          truncate table  R_INFORMATIONTYPE;
select * from   R_KNOWLEDGEPOINT ;           truncate table  R_KNOWLEDGEPOINT;
select * from   R_LINEWORK ;                 truncate table  R_LINEWORK;
select * from   R_MANAGEAREA ;               truncate table  R_MANAGEAREA;
select * from   R_MESSAGE ;                  truncate table  R_MESSAGE;
select * from   R_MODULETYPE ;               truncate table  R_MODULETYPE;
select * from   R_NOTICE ;                   truncate table  R_NOTICE ;
select * from   R_NOTICECONTENT ;            truncate table  R_NOTICECONTENT ;
select * from   R_NOTICESEND ;               truncate table  R_NOTICESEND;
select * from   R_NOTICETEMPLATE ;           truncate table  R_NOTICETEMPLATE;
select * from   R_NOTICETEMPLATEITEM ;       truncate table  R_NOTICETEMPLATEITEM;
select * from   R_OPERROLE ;                 truncate table  R_OPERROLE
select * from   R_PAPERADDRESS ;             truncate table  R_PAPERADDRESS;
select * from   R_POSTS ;                    truncate table  R_POSTS ;
select * from   R_QUESTIONLIST ;             truncate table  R_QUESTIONLIST ;
select * from   R_RECIEVEMESSAGE ;           truncate table  R_RECIEVEMESSAGE;
select * from   R_REPLIES ;                  truncate table  R_REPLIES;
select * from   R_ROLERIGHT ;                truncate table  R_ROLERIGHT;
select * from   R_SCOREARITHMETIC ;        
select * from   R_SDZNCATALOG ;              truncate table  R_SDZNCATALOG;
select * from   R_SDZNSECTION ;              truncate table  R_SDZNSECTION;
select * from   R_SELFTHINK ;                truncate table  R_SELFTHINK;
select * from   R_SENDMESSAGE ;              truncate table  R_SENDMESSAGE;
select * from   R_STAGESUBJECT ;             truncate table  R_STAGESUBJECT;
select * from   R_STUDENTANSWER ;            truncate table  R_STUDENTANSWER ;
select * from   R_STUDENTANSWERSET ;         truncate table  R_STUDENTANSWERSET;
select * from   R_STUDENTGROWUPRECORD ;      truncate table  R_STUDENTGROWUPRECORD;
select * from   R_STUDENTSUBJECTSCORE ;      truncate table  R_STUDENTSUBJECTSCORE;
select * from   R_STUDENTTESTPAPER ;         truncate table  R_STUDENTTESTPAPER;
select * from   R_STUDENTTOTALSCORE ;        truncate table  R_STUDENTTOTALSCORE;
select * from   R_STUDENTWORK ;              truncate table  R_STUDENTWORK;
select * from   R_STUDYNOTES ;               truncate table  R_STUDYNOTES;
select * from   R_STUDYRESOURCE ;            truncate table  R_STUDYRESOURCE;
select * from   R_STYLE ;                 
select * from   R_SUBJECTQUESTIONTYPE ;        
select * from   R_SYSOPER ;                  delete from  R_SYSOPER a where a.opername !='adminpub';
select * from   R_TEACHERAFFIX ;             truncate table  R_TEACHERAFFIX;
select * from   R_TEACHERCOURSE ;            truncate table  R_TEACHERCOURSE;
select * from   R_TEACHERRESOURCE ;          truncate table  R_TEACHERRESOURCE;
select * from   R_TEACHERSUBJECT ;           truncate table  R_TEACHERSUBJECT;
select * from   R_TEMPLATEITEM ;             truncate table  R_TEMPLATEITEM;
select * from   R_USERCLASS ;                truncate table  R_USERCLASS;
select * from   S_DEPARTMENT ;               truncate table  S_DEPARTMENT;
select * from   S_DICCONTENT ;               truncate table  S_DICCONTENT;
select * from   S_DICTYPE ;                  truncate table  S_DICTYPE;
select * from   S_LOGINLOG ;                 truncate table  S_LOGINLOG ;
select * from   S_OPERROLE ;                 delete from   S_OPERROLE a where a.operno != '000000001'
select * from   S_ROLEMODULE ;        
select * from   S_SYSFUNCTION ;         
select * from   S_SYSMENU ;         
select * from   S_SYSMODULE ;       
select * from   S_SYSROLE ;         
select * from   VIEW_S_OPERROLE ;       

 

预置数据有：

学段             d_stage： isSystem=1

科目             d_subject：  isSystem=1

学段学科对应 r_StageSubject

版本              D_VERSION: isSystem=1

题库类型        D_BANKTYPE : isSystem=1

学科分类类型  D_BRANCH ： isSystem=1

难易程度        D_DIFFICULTYLEVEL: isSystem=1

掌握程度        D_MASTERLEVEL： isSystem=1

试卷类型        D_PAPERTYPE : isSystem=1

试题类型        D_QUESTIONTYPE

学科试题类型 R_SUBJECTQUESTIONTYPE

成绩等级算法 R_SCOREARITHMETIC

 

对照表            D_COMPARE

角色模块表     S_ROLEMODULE

功能表           S_SYSFUNCTION

菜单表           S_SYSMENU

系统模块表     S_SYSMODULE

角色表           S_SYSROLE

样式字典表     R_STYLE

试图              VIEW_S_OPERROLE

题库审核设置 r_bankcheckset  保留教育局的审核设置信息 depid ='00000001'

用户角色表     S_OPERROLE   保留超级管理员的权限  operno != '000000001'

用户表           R_SYSOPER 保留超级管理员用户 opername !='adminpub';

单位表           R_DEPARTMENTINFO  保留教育局数据  depid != '00000001';

单位学段表     R_DEPTSTAGE  保留教育局的对应关系  depid != '00000001';

单位类型        D_DICCONTENT

 

系统表的数据转存sql如下：
-- truncate table  lcsfbak.S_SYSMODULE ; 
--truncate table lcsfbak.S_SYSFUNCTION ;
-- truncate table lcsfbak.S_SYSMENU ;
truncate table  lcsfbak.S_ROLEMODULE ;  

-- select * from    lcsfbak.S_SYSMODULE ;            
-- select * from    lcsfbak.S_SYSFUNCTION ;         
-- select * from    lcsfbak.S_SYSMENU ;         
select * from    lcsfbak.S_ROLEMODULE ;   
select count(1) from    lcsfbak.S_ROLEMODULE ;   

-- select * from    lc2.S_SYSMODULE ;            
-- select * from    lc2.S_SYSFUNCTION ;         
-- select * from    lc2.S_SYSMENU ;         
select * from    lc2.S_ROLEMODULE ;   
select count(1) from    lc2.S_ROLEMODULE ; 

 --insert into  lcsfbak.S_SYSMODULE (moduleno,modulename,remark,position)
 -- select ls.moduleno,ls.modulename,ls.remark,ls.position  from lc2.S_SYSMODULE ls;

 --insert into lcsfbak.S_SYSFUNCTION (moduleno,funcno,funcname,remark)
 -- select ls.moduleno,ls.funcno,ls.funcname,ls.remark    from lc2.S_SYSFUNCTION  ls ;
 
 --insert into lcsfbak.S_SYSMENU(menuno,menuname,menulink,isshow,sort,remark,menuwidth,menuico,menuclass)
 -- select  ls.menuno,ls.menuname,ls.menulink,ls.isshow,ls.sort,ls.remark,ls.menuwidth,ls.menuico,ls.menuclass  from  lc2.S_SYSMENU ls;
 
insert into lcsfbak.S_ROLEMODULE(roleid,moduleno,funcno,remark)
select ls.roleid,ls.moduleno,ls.funcno,ls.remark   from lc2.S_ROLEMODULE  ls;

 

 

、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、

------删除系统表-------------------------
truncate table  lcsfbak.S_SYSMODULE ; 
truncate table lcsfbak.S_SYSFUNCTION ;
truncate table lcsfbak.S_SYSMENU ;
truncate table lcsfbak.S_ROLEMODULE;
 
------查询系统表-------------------------
select * from    lcsfbak.S_SYSMODULE ;            
select * from    lcsfbak.S_SYSFUNCTION ;         
select * from    lcsfbak.S_SYSMENU ;         
select count(1) from    lcsfbak.S_ROLEMODULE ;   

------查询测试数据库---------------------
select * from    lc2.S_SYSMODULE ;            
select * from    lc2.S_SYSFUNCTION ;         
select * from    lc2.S_SYSMENU ;         
select * from    lc2.S_ROLEMODULE ;   
select count(1) from    lc2.S_ROLEMODULE ; 

------系统表数据的移行--------------------
insert into  lcsfbak.S_SYSMODULE (moduleno,modulename,remark,position)
 select ls.moduleno,ls.modulename,ls.remark,ls.position  from lc2.S_SYSMODULE ls;

insert into lcsfbak.S_SYSFUNCTION (moduleno,funcno,funcname,remark)
 select ls.moduleno,ls.funcno,ls.funcname,ls.remark    from lc2.S_SYSFUNCTION  ls ;
 
insert into lcsfbak.S_SYSMENU(menuno,menuname,menulink,isshow,sort,remark,menuwidth,menuico,menuclass)
select  ls.menuno,ls.menuname,ls.menulink,ls.isshow,ls.sort,ls.remark,ls.menuwidth,ls.menuico,ls.menuclass  from  lc2.S_SYSMENU ls;
 
 
insert into lcsfbak.S_ROLEMODULE(roleid,moduleno,funcno,remark)
select ls.roleid,ls.moduleno,ls.funcno,ls.remark   from lc2.S_ROLEMODULE  ls;

 

-----------------------------------爱学堂资源的移行---------------------------------------------------------------------------

insert into lcsfbak.r_axtcatalog(catalogid,catalogname,stagecode,subjectcode,versioncode,gradecode,state,remark)
select catalogid,catalogname,stagecode,subjectcode,versioncode,gradecode,state,remark from lc2.r_axtcatalog;

 

 

insert into lcsfbak.r_axtsection(sectionid,sectionname,catalogid,grade,upnode,lastflag,orderdisplay,remark)
select sectionid,sectionname,catalogid,grade,upnode,lastflag,orderdisplay,remark from lc2.r_axtsection

 

 

insert into lcsfbak.r_studyresource(resourceid,resourcepy,resourcename,displayindex,sectionid,respath)
select resourceid,resourcepy,resourcename,displayindex,sectionid,respath from lc2.r_studyresource

 

 

insert into lcsfbak.d_grade(gradecode,gradename,issystem,remark)
select gradecode,gradename,issystem,remark from lc2.d_grade;