2021年Oracle复习题总结

1、精编word文档 下载可编辑oracle复习题总结绪论1、oracle，甲骨文，1977年开始研发，总部位于美国加州红木滩市，创始人为larryellison(埃里森)、bobminer、edoates2、oracle服务器实例数据库；oracle实例内存后台进程；第四章表空间的创建与删除例1建立名称为data_ts1的数据表空间，大小为50m，区间统一为128kb大小。sqlconnsystem/systempwdorclsqldroptablespacedata_ts1includingconntents;sqlcreatetablespacedata_ts12tempfile%oracl

2、e_home%databasedata_tsdbfsize50mreuse3uniformsize128k;例2建立名称为temp_ts1的临时表空间，使用temp_tsdbf文件存放临时数据。sqlconnsystem/systempwdorclsqldroptablespacetemp_ts1includingconntents;sqlcreatetemporarytablespacetemp_ts12tempfile%oracle_home%databasetemp_tsdbfsize20mreuse3uniformsize128k;例3创建10号部门经理用户emp_mgr10，指定该用

3、户的数据表空间为data_ts1，临时表空间为temp_ts1。授权该用户可以查看scott用户下雇员表中的记录。sqlconnsystem/systempwdorclsqldropuseremp_mgr10cascade;sqlcreateuseremp_mgr10identifiedbyemp_mgr10pwd2defaulttablespacedata_ts1temporarytablespacetemp_ts1;sqlgrantconnecttoemp_mgr10;sqlconnscott/tigerorcl4tempfile%oracle_home%databasetemp_tsdb

4、fsize20mreuse5uniformsize128k;例4创建和应用撤销表空间。sqlconnsystem/systempwdorclsqldroptablespaceundo_ts1includingconntents;sqlcreateundotablespaceundo_ts12datafile%oracle_home%database纀例5创建大文件表空间，并指定为scott用户的默认数据表空间。sqlconnsystem/systempwdorclsqldroptablespacebigfile_ts1includingconntents;sqlcreatebigfiletab

5、lespacebigfile_ts12datafile%oracle_home%databasebigfile_tsdbfsize50mreuse;sqlalteruserscottdefaulttablespacebigfile_ts1;需要注意的是，大文件表空间的段空间管理不能为手工(manual)，只能为自动(auto)。例6调整数据表空间data_ts1的大小。sqlconnsystem/systempwdorcl-为表空间data_ts1增加一数据文件，大小为1m。sqlaltertablespacedata_ts12adddatafile%oracle_home%databased

6、ata_tsdbfsize1m;-重置该数据文件大小为2m。sqlaltertablespace2datafile%oracle_home%databasedata_tsdbfsize2m;-修改该数据库，允许该文件自动扩展，每次扩展1m，文件最大扩展到10m。sqlalterdatabasedatafile%oracle_home%databasedata_tsdbf2autoextendonnext1mmaxsize10m;例7删除temp_ts1表空间。sqlconnsystem/systempwdorclsqldroptablespacetemp_ts12includingconten

7、tsanddatafiles3cascadeconstraints;例8回退段创建与删除。sqlconn3next5ksystem/systempwdorcl4optimal5000ksqldroprollbacksegment5minextents15undo_ts1_rbs1;6maxextentssqlcreaterollbacksegment100);undo_ts1_rbs1;2tablespaceundo_ts1storage(initial5k第5章常用方案对象类型表table索引index视图view序列sequence用户user约束constraint表空间tablespa

8、ce回退段rollbacksegment同义词synonym数据库链接databaselink聚簇cluster分区partition函数/过程/包function/procedure/package触发器trigger类型type约束的类型notnull非空约束check检查约束，用于限制该列的取值范围unique唯一性约束，指定某列值不能重复primarykey主键约束foreignkey外键约束，也叫参照完整性约束ref定义列对象的参照关系references参照完整性约束例1创建采用系统默认存储参数值的关系表。exa_05_0sql例2为scott的雇员表emp创建一个备份表emp_b

9、ak。sqlconnscott/tigeremp_orclsqldorptableemp_bak;sqlcreatetableemp_baksqlasselect*fromemp;sqldescemp_baksqlselect*fromemp_bak;例3创建表stu2，并指定它的存储参数。exa_05_0sql例4创建学生相关表，在列中定义约束。exa_05_0sql例5创建学生相关表，在列之外定义约束。exa_05_0sql例6创建学生相关表，在列之外定义约束。exa_05_0sql例1某电子商务估计每月产生近千万条订单记录。该采用年yyyy+月mm+当月流水号nnnnnnnn的形式标识和

10、区分每一份订单。当有用户提交一份订单时，系统自动生成该订单的编号并告知用户这个唯一的订单号。创建一序列，用于生成每月的订单流水号。(老师要求例子)connscott/tigerorcldropsequenceorder_seq;createsequenceorder_seqincrementby1startwith10000000cache500nocycle;colsequence_nameformata13selectsequence_name,min_value,max_value,increment_by,last_numberfromuser_sequences;selectorde

11、r_seq.currvalfromdual;selectorder_seq.nextvalfromdual;selectorder_seq.currvalfromdual;selectorder_seq.nextvalfromdual;selectorder_seq.currvalfromdual;-函数案例案例返回工人年工资。createorreplacefunctionxxc_fun1(newnamevarchar2)returnnumberisyearsalnumber(10,2);beginselectsal*12+nvl(comm,0)*12intoyearsalfromempwhe

12、reename=newname;returnyearsal;end;例编写函数按yyyy-mm-ddhh24:mi:ss格式以字符串形式返回当前系统时间。connectscott/tigerorclcreateorreplacefunctioncurrent_timereturnvarchar2asbeginreturnto_char(sysdate,yyyy-mm-ddhh24)|:|to_char(sysdate,mi)|:|to_char(sysdate,ss);end;/selectcurrent_timefromdual;例21查询指定编号雇员的名字、工资和佣金。存储过程connsc

13、ott/tigerorclcreateorreplaceprocedurequery_emp(p_noinemp.empno%type,p_nameoutemp.ename%type,p_saloutemp.sal%type,p_m%type)isbeginselectename,sal,commintop_name,p_sal,p_commfromempwhereempno=p_no;endquery_emp;/variableg_namevarchar2(25)variableg_salnumbervariableg_commnumberexecutequery

14、_emp(7369,:g_name,:g_sal,:g_comm)printg_name例39创建触发器1在scott的emp表上建立语句前触发器emp_permit_changes。createorreplacetriggerscott.emp_hellobeforedeleteorinsertorupdateonscott.empbeginraise_application_error(-201*1,howareyou!);end;2修改触发器使emp_hello触发器不能触发altertriggerscott.emp_hellodisable;3删除触发器droptriggerscott

15、.emp_hello;end;/updateempsetsal=100whereempno=7369;例37为雇员表emp创建一触发器，确保插入记录的工资列sal不小于0，同时新记录的能高于已有记录最高工资的2倍。connscott/tigerorclcreateorreplacetriggercheck_sal_empbeforeinsertorupdateonempreferencingoldasoldnewasnewforeachrowbeginif:new.sal2*wage_packageg_salthenraise_application_error(-20502,雇员工资超过现有

16、最高工资2倍);endif;end;/insertintoemp(empno,ename,hiredate,job,sal,deptno)values(300,jordan,sysdate,it_prog,-3,10);insertintoemp(empno,ename,hiredate,job,sal,deptno)values(300,tracyzhou,sysdate,acc.offi.,16000,10);oracle实例有两种类单进程实例和多进程实例。多进程系统中，进程分为两类用户进程和oracle进程。sal列值不oracle进程又分为两类服务器进程(serverprocess)和

17、后台进程（backgroundprocess）。oracle数据库的索引模式(1)b-树索引(2)b-树簇索引(3)散列簇索引(4)全局和本地索引(5)反序索引(6)位图索引(7)基于函数的索引(8)域索引创建视图1、生成一个部门号是10的视图createviewd10empasselectempno,ename,salfromempwheredeptno=10;2、删除视图dropviewd10emp;3、创建索引createindexi_enameonemp(ename);createuniqueindexi_empnoonemp(empno);索引应用如果查询语句如下则没有用到索引i_e

18、nameselectename,job,salfromemp;如果查询语句如下则用到索引i_enameselct*fromempwhereename=jones;4、删除索引dropindexi_ename;5、创建一个用户createusermyselfidentifiedbymy;6、修改用户口令alterusermyselfidentifiedbyme；7、对象权限授权把dept的select对象权限授给myself用户grantselectondepttomyself;把emp的select权限授给所有用户grantselectonemptopublic;8、收回对象权限从myself

19、收回所有dept的对象权限revokeallondeptfrommyself；收回所有用户对emp的select权限revokeselectonempfrompublic;9、删除用户dropusermyself；实验三数据插入（insert）、修改update和删除delete1用insert在基本表customer中插入数据sqlinsertintocustomervalues(nicholson,ca,69899);2在表state中插入指定的字段sqlinsertintostate(state_name,state_cd)2values(massachusetttes,ma);1row

20、created.3修改数据把state表中newyork改为florida，ny改为fdupdatestatesetstate_name=florida,state_cd=fdwherestate_name=newyorkandstate_cd=ny;4删除数据从state表删除state_name为florida和state_cd为fd的记录deletefromstatewherestate_name=floridaandstate_cd=fd;删除表全部数据deletefromhzx.sc2、对s、c、sc表进行操作s(s#，sname，age，sex)对应的中文为(学号，姓名，年龄，性别

21、)sc(s#，c#，grade)对应的中文为学习（学号，课程号，成绩）c(c#，cname，teacher)对应的中文为课程（课程号，课程名，任课教师）1）、把c2课程的非空成绩提高10%。updatestu.scsetgrade=grade*1wherec#=c2andc#isnotnull;2）、在sc表中删除课程名为physics的成绩的元组。deletefromstu.scwherec#=physisc;3）、在s和sc表中删除学号为s8的所有数据。deletefromstu.scwheres#=s8;deletefromstu.swheres#=s8;实验四索引、视图1建立男学生的视

22、图，属性包括学号、姓名、选修课程和成绩。createviewmanasselects.s#,sname,cname,gradefromc,s,scwheresex=mands.s#=sc.s#andc.c#=sc.c#;2在男学生视图中查询平均成绩大于80分的学生学号和姓名。selectdistincts#,snamefrommanwheresnamein(selectsnamefrommangroupbysnamehavingavg(grade)80);3撤消生成的视图。sqldropviewman;4创建一个新用户newuser。执行语句createusernewuseridentifie

23、dbymy;5使用grant语句，把对基本表s、c、sc的使用权限授给newuser用户。grantallonstonewuser;grantallonctonewuser;grantallonsctonewuser;6使用revoke语句从newuser手中收回基本表s、c、sc的使用权。revokeallonsfromnewuser;revokealloncfromnewuser;revokeallonscromnewuser;7删除用户newuser。执行语句dropusernewuser;8对基本表s按照s#生成一个索引。执行语句createindexssons(s#);9对基本表c按

24、照c#生成一个索引。执行语句createindexcconc(c#);10.删除基本表c建立的索引。执行语句dropindexss;扩展阅读oracle复习总结一、储存模式是一种包含了诸如段。视图。过程。函数程序包。触发器。用户自定义的对象集合类型序列同义词和数据连接对象的逻辑结构。用户角色，对象权限系统权限select，insert，update，delete可以使用的权限是execute二sql语言创建表createtable表名约束（1）非空约束notnull（2）唯一约束unique(3)主键约束primarykey最多只能有一个主键约束主键约束可以使用一列组成，不能有重复的不能为空（

25、4）外键约束foreignkey外键是指引用一个表中的某个列或某几个列，或本表中另一个列或者几个列被引用的列应该是主键列或者唯一性约束列。约束关键词constraint名称foreignkeyreferences引用表名引用表主键（5）检查约束checkconstraint名称check表达式（6）缺省约束default修改表（1）增加列altertable表名add新列名数据类型（2）更新列altertable表名modify列名数据类型（3）删除列altertable表名dropcolumn要删除的列名（4）删除表droptable表名dml检索所有列select*fromempselec

26、tename,jobfromemp;selectename,to_char(hiredate,yyyy-mm-dd)fromempselectdistinctdeptno,jobfromemp取消重复行selectename,sal*12frommep处理nullselectename,sal,comm.,(sal+comm)fromemp连接字符串selectename|isa|jobas”employdetail”fromemp使用简单where子句selectename,salfromempwheresal201*selectjob,salfromempwhereename=scott;

27、selectjob,salfromempwherelower(ename)=scott;where子句和betweenandselectename,sal,hiredate,jobfromempwheresalbetween1000and201*where子句中使用like操作符selectename,salfromempwhereenamelikes%;orderby子句1,升序排列selectename,salfromempwheredeptno=30orderbysal;降序排列selectename,sal,comm.fromempwheredeptno=30orderbysaldes

28、c使用多列排序selectename,sal,commmfromempwheredeptno=30orderbysalasc,comm.desc数据分组gropby1,分组函数selectmax(sal),min(sal)fromemp2,取消重复值selectcount(distinctdeptno)asdistinct_deptfrommepgroupbyhaving子句selectcolumn,group_functionfrom表名whereconditiongroupbygroupby_expressionhavinggroup_condition使用groupby进行单列分组sel

29、ectdeptno,avg(sal),mac(sal)fromempgroupbydeptno使用groupby进行多列分组selectdeptno,job,avg(sal),max(sal)fromempgroupbydeptno,job;使用having子句限制分组显示selectdeptno,avg(sal),max(sal)fromempgroupbydeptnohavingavg(sal)ename=smith)成对比较selectename,sal,comm.,deptnofromempwhere(sal,nvl(comm.,-1)in(selectsal,nvl(comm.,-1

30、)fromempwheredeptno=30)合并查询union并集走掉结果集中的重复行unionall两个结果记的并集不会取消重复行intersect两个结果集的交集minus两个结果集的差集insert（1）插入单行数insertintotablecolumn1coulmn2valuesvalues1values2不使用列插入单行数据insertintodeptvalues(50,trainboston)使用列插入单行数据insertintoemp(emp,ename,jobhiredate)values(1244,john,clerk,01-3-86)使用子查询插入数据insertint

31、oemployee(empno,ename,sal,deptno)selectempno,enmae,sal,deptnofromempwheredeptno=20使用firstselectfirstwhendeptno=10thenintodept10whendeptno=20thenintodept20elseintootherselect*fromemp555update3事务锁事务用于确保数据库的一致性主要由insertupdatedeleteselect。forupdate提交事务commit回退事务rollback三数据库对象1，同义词createsynonym名字forobjec

32、t_name2序列创建序列createsequence名字startwith名字incrementby数字（初始值）maxvalue数字cycle和nocycle使用序列selects_test.nextvalfromdualselects_test.currvalformdual使用序列填充主键creaetetablestudent(idintegerconstraints_testprimarykey,namevarchar2(20);insertintostudent(id,name)values(s_test.nextval,fendou)视图视点集简化操作定制数据合并分割数据安全性创建并使用视图createorreplaceviewview_name