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;
