Oracle复习题总结

Oracle复习题总结

绪论：

1、Oracle，甲骨文，1977年开始研发，总部位于美国加州红木滩市，创始人为LarryEllison(埃里森)、BobMiner、EdOates

2、Oracle服务器＝实例＋数据库；Oracle实例＝内存＋后台进程；

第四章：表空间的创建与删除

例4.1建立名称为data_ts1的数据表空间，大小为50M，区间统一为128KB大小。

SQL>connsystem/systempwd@orcl

SQL>droptablespacedata_ts1includingconntents;SQL>createtablespacedata_ts1

2tempfile‘%oracle_home%\\database\\data_ts1.dbf’SIZE50MREUSE3uniformsize128K;

例4.2建立名称为temp_ts1的临时表空间，使用temp_ts1.dbf文件存放临时数据。

SQL>connsystem/systempwd@orcl

SQL>droptablespacetemp_ts1includingconntents;SQL>createtemporarytablespacetemp_ts1

2tempfile‘%oracle_home%\\database\\temp_ts1.dbf’size20Mreuse3uniformsize128k;

例4.3创建10号部门经理用户EMP_MGR10，指定该用户的数据表空间为data_ts1，临时表空间为

temp_ts1。授权该用户可以查看SCOTT用户下雇员表中的记录。SQL>connsystem/systempwd@orcl

SQL>dropuseremp_mgr10cascade;

SQL>createuseremp_mgr10identifiedbyemp_mgr10pwd

2defaulttablespacedata_ts1temporarytablespacetemp_ts1;SQL>grantconnecttoemp_mgr10;SQL>connscott/tiger@orcl

4tempfile‘%oracle_home%\\database\\temp_ts1.dbf’size20Mreuse5uniformsize128k;

例4.4创建和应用撤销表空间。

SQL>connsystem/systempwd@orcl

SQL>droptablespaceundo_ts1includingconntents;SQL>createundotablespaceundo_ts1

2datafile‘%oracle_home%\\database\\undo_ts1.dbf’size50Mreuse;例4.5创建大文件表空间，并指定为SCOTT用户的默认数据表空间。

SQL>connsystem/systempwd@orcl

SQL>droptablespacebigfile_ts1includingconntents;SQL>createbigfiletablespacebigfile_ts1

2datafile‘%oracle_home%\\database\\bigfile_ts1.dbf’size50Mreuse;SQL>alteruserscottdefaulttablespacebigfile_ts1;

需要注意的是，大文件表空间的段空间管理不能为手工(MANUAL)，只能为自动(AUTO)。例4.6调整数据表空间data_ts1的大小。SQL>connsystem/systempwd@orcl

--为表空间data_ts1增加一数据文件，大小为1M。SQL>altertablespacedata_ts1

2adddatafile‘%oracle_home%\\database\\data_ts2.dbf’size1M;--重置该数据文件大小为2M。SQL>altertablespace

2datafile‘%oracle_home%\\database\\data_ts2.dbf’size2M;

--修改该数据库，允许该文件自动扩展，每次扩展1M，文件最大扩展到10M。SQL>alterdatabasedatafile‘%oracle_home%\\database\\data_ts2.dbf’

2autoextendonnext1Mmaxsize10M;例4.7删除temp_ts1表空间。

SQL>connsystem/systempwd@orclSQL>droptablespacetemp_ts1

2includingcontentsanddatafiles3cascadeconstraints;例4.8回退段创建与删除。SQL>conn3next5Ksystem/systempwd@orcl4optimal5000KSQL>droprollbacksegment5minextents15undo_ts1_rbs1;6maxextentsSQL>createrollbacksegment100);undo_ts1_rbs1;2tablespaceundo_ts1storage(initial5K

第5章：常用方案对象类型：

表：table索引：index视图：view序列：sequence用户：user约束：constraint表空间：tablespace回退段：rollbacksegment同义词：synonym数据库链接：databaselink聚簇：cluster分区：partition函数/过程/包：function/procedure/package触发器：trigger类型：type约束的类型：

NOTNULL：非空约束

check：检查约束，用于限制该列的取值范围Unique：唯一性约束，指定某列值不能重复primarykey：主键约束

Foreignkey：外键约束，也叫参照完整性约束Ref：定义列对象的参照关系References：参照完整性约束

例5.1创建采用系统默认存储参数值的关系表。EXA_05_01.SQL例5.2为SCOTT的雇员表emp创建一个备份表emp_bak。

SQL>connscott/tiger@emp_orclSQL>dorptableemp_bak;SQL>createtableemp_bakSQL>asselect*fromemp;SQL>descemp_bak

SQL>select*fromemp_bak;

例5.3创建表stu2，并指定它的存储参数。EXA_05_03.SQL例5.4创建学生相关表，在列中定义约束。EXA_05_04.SQL例5.5创建学生相关表，在列之外定义约束。EXA_05_05.SQL例5.6创建学生相关表，在列之外定义约束。EXA_05_06.SQL

例7.1某电子商务网站估计每月产生近千万条订单记录。该网站采用年YYYY+月MM+当月流水号nnnnnnnn的形式标识和区分每一份订单。当有用户提交一份订单时，系统自动生成该订单的编号并告

知用户这个唯一的订单号。创建一序列，用于生成每月的订单流水号。(老师要求例子)connscott/tiger@orcl

dropsequenceorder_seq;createsequenceorder_seqincrementby1

startwith10000000cache500nocycle;

colsequence_nameformata13

selectsequence_name,min_value,max_value,increment_by,last_numberfromuser_sequences;

selectorder_seq.currvalfromdual;selectorder_seq.nextvalfromdual;selectorder_seq.currvalfromdual;selectorder_seq.nextvalfromdual;selectorder_seq.currvalfromdual;

--函数案例：案例1.返回工人年工资。

createorreplacefunctionxxc_fun1(newnamevarchar2)returnnumberisyearSalnumber(10,2);begin

selectsal*12+nvl(comm,0)*12intoyearSalfromempwhereename=newname;returnyearSal;end;

例9.编写函数按YYYY-MM-DDHH24:MI:SS格式以字符串形式返回当前系统时间。connectscott/tiger@orcl

createorreplacefunctioncurrent_timereturnvarchar2asbegin

returnto_char(sysdate,"yyyy-mm-ddhh24")||":"

||to_char(sysdate,"mi")||":"||to_char(sysdate,"ss");end;/

selectcurrent_timefromdual;

例9.21查询指定编号雇员的名字、工资和佣金。存储过程connscott/tiger@orcl

createorreplaceprocedurequery_emp(p_noinemp.empno%type,p_nameoutemp.ename%type,p_saloutemp.sal%type,

p_commoutemp.comm%type)isbegin

selectename,sal,comm

intop_name,p_sal,p_comm

fromemp

whereempno=p_no;endquery_emp;/

variableg_namevarchar2(25)variableg_salnumbervariableg_commnumber

executequery_emp(7369,:g_name,:g_sal,:g_comm)

printg_name

例9.39创建触发器：

1．在scott的emp表上建立语句前触发器emp_permit_changes。Createorreplacetriggerscott.emp_helloBeforeDeleteorinsertorupdateOnscott.empBegin

raise_application_error(-201*1,"howareyou!");End;2．修改触发器

使emp_hello触发器不能触发：altertriggerscott.emp_hellodisable;3．删除触发器

droptriggerscott.emp_hello;end;/

updateempsetsal=100whereempno=7369;

例9.37为雇员表emp创建一触发器，确保插入记录的工资列sal不小于0，同时新记录的能高于已有记录最高工资的2倍。connscott/tiger@orcl

createorreplacetriggercheck_sal_empbeforeinsertorupdateonempreferencingoldasoldnewasnewforeachrowbegin

if:new.sal<0then

raise_application_error(-20501,"雇员工资不能为负数");endif;

if:new.sal>2*wage_package3.g_salthen

raise_application_error(-20502,"雇员工资超过现有最高工资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)和后台进程（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_ename：selectename,job,salfromemp;如果查询语句如下则用到索引i_ename：selct*fromempwhereename=‘jones’;4、删除索引：dropindexi_ename;

5、创建一个用户：createusermyselfidentifiedbymy;6、修改用户口令：alterusermyselfidentifiedbyme；

7、对象权限授权：把dept的select对象权限授给myself用户：

grantselectondepttomyself;

把emp的select权限授给所有用户：grantselectonemptopublic;8、收回对象权限：

从myself收回所有dept的对象权限：revokeallondeptfrommyself；收回所有用户对emp的select权限：revokeselectonempfrompublic;9、删除用户：dropusermyself；

实验三：数据插入（insert）、修改update和删除delete1．用Insert在基本表customer中插入数据

SQL>insertintocustomervalues(‘nicholson’,’ca’,6989.99);2．在表STATE中插入指定的字段

SQL>insertintostate(state_name,state_cd)2values(‘massachusetttes’,’MA’);1rowcreated.3．修改数据

把state表中newyork改为florida，ny改为fd：

updatestatesetstate_name=‘florida’,state_cd=‘fd’wherestate_name=‘newyork’andstate_cd=‘ny’;4．删除数据

从state表删除state_name为Florida和state_cd为fd的记录：deletefromstatewherestate_name=‘florida’andstate_cd=‘fd’;

删除表全部数据：deletefromhzx.sc

2、对s、c、sc表进行操作：

s(s#，sname，age，sex)对应的中文为：(学号，姓名，年龄，性别)]sc(s#，c#，grade)对应的中文为：[学习（学号，课程号，成绩）]c(c#，cname，teacher)对应的中文为：[课程（课程号，课程名，任课教师）]1）、把c2课程的非空成绩提高10%。

updatestu.scsetgrade=grade*1.1wherec#="c2"andc#isnotnull;2）、在sc表中删除课程名为physics的成绩的元组。

deletefromstu.scwherec#="physisc";3）、在s和sc表中删除学号为s8的所有数据。

deletefromstu.scwheres#="s8";deletefromstu.swheres#="s8";

实验四：索引、视图

1．建立男学生的视图，属性包括学号、姓名、选修课程和成绩。

createviewmanasselects.s#,sname,cname,gradefromc,s,scwheresex="m"ands.s#=sc.s#andc.c#=sc.c#;

2．在男学生视图中查询平均成绩大于80分的学生学号和姓名。selectdistincts#,snamefrommanwheresnamein(

selectsnamefrommangroupbysnamehavingavg(grade)>80);3．撤消生成的视图。SQL>dropviewman;

4．创建一个新用户newuser。执行语句：createusernewuseridentifiedbymy;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按照C#生成一个索引。执行语句：CREATEINDEXccONC(C#);10.删除基本表C建立的索引。执行语句：DROPINDEXss;

扩展阅读：oracle复习总结

一、

1.储存模式是一种包含了诸如段。视图。过程。函数程序包。触发器。用户自定义的对象集合类型序列同义词和数据连接对象的逻辑结构。2.用户角色，对象权限系统权限

Select，insert，update，delete可以使用的权限是execute二．SQL语言

1.创建表createtable表名2.约束

（1）非空约束notnull（2）唯一约束unique

(3)主键约束primarykey最多只能有一个主键约束主键约束可以使用一列组成，不能有重复的不能为空

（4）外键约束foreignkey

外键是指引用一个表中的某个列或某几个列，或本表中另一个列或者几个列被引用的列应该是主键列或者唯一性约束列。

约束关键词constraint名称foreignkeyreferences引用表名引用表主键（5）检查约束checkconstraint名称check表达式（6）缺省约束default3.修改表（1）增加列

Altertable表名add新列名数据类型（2）更新列

Altertable表名modify列名数据类型（3）删除列

Altertable表名dropcolumn要删除的列名（4）删除表Droptable表名2.DML

1.检索所有列Select*fromemp

Selectename,jobfromemp;

Selectename,to_char(hiredate,yyyy-mm-dd)fromempSelectdistinctdeptno,jobfromemp取消重复行Selectename,sal*12frommep处理null

Selectename,sal,comm.,(sal+comm)fromemp连接字符串

Selectename||isa||jobas”employdetail”fromemp使用简单where子句

Selectename,salfromempwheresal>201*

Selectjob,salfromempwhereename=SCOTT;

Selectjob,salfromempwherelower(ename)=scott;

Where子句和betweenand

Selectename,sal,hiredate,jobfromempwheresalbetween1000and201*Where子句中使用like操作符

Selectename,salfromempwhereenamelikeS%;Orderby子句

1,升序排列

Selectename,salfromempwheredeptno=30orderbysal;2.降序排列

Selectename,sal,comm.Fromempwheredeptno=30orderbysaldesc3.使用多列排序

Selectename,sal,commmfromempwheredeptno=30orderbysalasc,comm.Desc4.数据分组Gropby1,分组函数

Selectmax(sal),min(sal)fromemp2,取消重复值

Selectcount(distinctdeptno)asdistinct_deptfrommep3.groupbyhaving子句

Selectcolumn,group_functionfrom表名whereconditiongroupbygroupby_expressionhavinggroup_condition

使用groupby进行单列分组selectdeptno,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)fromempwheredeptno=30)4.合并查询

Union并集走掉结果集中的重复行unionall两个结果记的并集不会取消重复行intersect两个结果集的交集minus两个结果集的差集Insert

（1）插入单行数insertintotablecolumn1coulmn2valuesvalues1values2

不使用列插入单行数据

Insertintodeptvalues(50,TRAINBOSTON)使用列插入单行数据

Insertintoemp(emp,ename,jobhiredate)values(1244,john,clerk,01-3-86)使用子查询插入数据

Insertintoemployee(empno,ename,sal,deptno)selectempno,enmae,sal,deptnofromempwheredeptno=20使用first

Selectfirstwhendeptno=10thenintodept10whendeptno=20thenintodept20elseintootherselect*fromemp555update2.3事务锁

事务用于确保数据库的一致性主要由insertupdatedeleteselect。。。。forupdate提交事务commit回退事务rollback三．数据库对象1，同义词

Createsynonym名字forobject_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)3.视图

1.视点集2.简化操作3.定制数据4.合并分割数据5.安全性4.创建并使用视图

Createorreplaceviewview_nameassubqueryconstranintconstraint_name

Createviewemp_viewasselectempno,ename,deptnofromempwheredeptno=30Insertintoemp_viewvalues(201*,fendou,30)创建具有checkoption约束的视图Creaeteorreplaceemp_viewasselectempno,ename,deptnofromempwheredeptno=30wiehcheckoptionconstraintemp_view_ck5.索引

索引加快数据的一种有效方式

Creaeteuniqueindexindex_nameontable_namecolumn_name(column_name…)Tablespacetablespace_name

Createindexi_emp_indexonemp(deptno)

Createendexi_emp_ednoonemp(empno,deptno)修改索引

Alterindexi_emp_ednorenametoi_emp_noDropindexi_emp_no;临时表

Createglobaltemporary表名列类型Oncommitpreserverows会话中断时Commit时

Createglobaltemporarytabletemp_test2(tempIdint)

Oncommitdeleterows;四．PLSQL编程简介1，块结构Declare

名称类型值Begin

执行异常处理部分End

已知矩形面积和高求宽度DeclareV_widthint;V_heithtint:=2;V_areaint:=6;Begin

SetthewidtheaualtotheareadividedbytheheightV_width:=v_area/v_height;

Dbms_output.put_line(“v_width=”||v_width);Exception

Whenzero_dividethen

Dbms_output.put_line(divisonbyzero);End

2,变量和类型

Intvarchar2number条件逻辑

Ifthenelseelseifendif循环

While循环for循环简单循环Loop

StatementsEndloop

While循环

友情提示：本文中关于《Oracle复习题总结》给出的范例仅供您参考拓展思维使用，Oracle复习题总结：该篇文章建议您自主创作。

来源：网络整理 免责声明：本文仅限学习分享，如产生版权问题，请联系我们及时删除。

《Oracle复习题总结》由互联网用户整理提供,转载分享请保留原作者信息,谢谢!
链接地址：http://www.bsmz.net/gongwen/671834.html

• 上一篇：大二的学习生活总结
• 下一篇：大一到大二的总结及学习计划