Oracle使用教程参考模板

1、第一章 简介SQL Plus 行编辑命令及功能APPENDtext 将文本text的内容附加在当前行的末尾CHRNGE/old/new 将旧文本old替换新文本new的内容CHANGE/text 删除当前行中text指定的内容CLEARBUFFER 删除SQL缓冲区中的所有命令DEL 删除当前行DEL n 删除n指定的行DEL m n 删除由m行到n行之间的所有命令INPUT 在当前行后插入任意数量的命令行INPUT text 在当前行后插入一行text指定的命令行LIST 列出SQL缓冲区所有的行LIST n SQL指定的第n行第二章 数据库和表的创建Oracle是一种关系数据库管理系统（R

2、DBMS）。关系数据库是按照二维表结构方式组织的数据集合，每个表体现了集合理论中定义的数学概念关系。数据库的内部结构有：1、表空间（system表空间sysaux表空间undo表空间users表空间temporary表空间）；2、表；3、约束条件（主键默认约束条件检查约束条件唯一性约束条件外键约束条件）；4、分区；5、索引；6、用户；7、方案；8、同义词；9、权限及角色；10、段、盘区和数据块。SYSTEM表空间自动生成，并且必须要有。undo表空间是存储撤销信息的表空间。表空间可以分为几个段，段划分为几个数据区间段是表空间的下一级逻辑存储单位，一个段只能存储同一种模式对象。根据段中存储逻辑对

3、象不同可以分为：数据段、索引段、临时段、回退段。数据库数据表空间数据段盘区数据块。数据库外部结构：1、数据文件（一个表空间可以有多个数据文件，而一个数据文件只能属于一个表空间）。2、重做日志文件（当数据库中的数据遭破坏时可以用这些日志来恢复数据库）3、控制文件包括：（1）数据库名称与建立时间； （2）数据文件与重置日志文件名称及其所在位置。 （3）日志记录序列码（log sequence number）数据库实例也称作服务器，数据库拥有自己的系统全局区和相关数据文件的oracle服务器进程集，它是存储和控制数据库的软件机制。数据类型：char 固定长度字符域，最大2000B；yarchar2

4、可变字符域，最大长度4000字符；date 日期；number 可变长度数值列，允许0，正负。创建表的实质是定义表结构及约束条件等属性。1 / 10第三章 数据库的操作1插入记录；INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES(061101,王林，计算机，男，TO_DATE(19891120 .YYYYMMDD),50);2建立表test：CREATE TABLE test（xm char（20）NOT NULL，Zy varchar(30) DEFAULT(计算机), Nj number NOT NULL);3修改表记录UPDATE XSSET BZ

5、=三好学生 WHERE XH=061110；索引是一种供服务器在表中快速查找一个行的数据库结构。作用：1快速存取数据；2即可以改善数据库性能，又可以保证列的值的唯一性；3实现表与表之间的参照完整性；4在使用order by、group by子句进行数据检索时，利用索引可以减少排序和分组时间。创建索引需要保存，保持Row ID和每一行索引值结合起来。1为kc表的课程名列创建索引Create index kc_name_idxOn xs (xm)Tablespacce “indx”;2为xs表的姓名列创建索引，指定索引的物理和存储特征值，数据库中的行以升序保存Create index xs_xm_

6、idxOn xs (xm)Tablespace “indx”Initrans 2Maxtrans 255Nosort;3修改索引kc_name_idxAlter index admin.xs_name_indexInitrans 2Maxtrans 128;4重命名索引kc_name_idxAlter index kc_name_idxRename to kc_idx;5删除索引kc_name_idxDrop index kc_name_idx;同义词是可以指向的对象有表、视图、过程、函数、包和序列的别名；最大的好处是：安全方便；使用时不必指出实际访问对象所属的模式及名称；简化用户操作；对数据

7、库有一定的安全保护作用。数据完整性的分类：域完整性；（又称为列完整性，指定一个数据集对某一个列是否有效和确定是否允许空值）实体完整性（行完整性，要求每个表中的每一行有一个唯一的标识符，这个标识符就是主关键字）；参照完整性（保证主表中的数据与从表（被参照表）中数据的一致性）。第四章 数据库的查询与视图1、查询XSH数据库CP表中产品名含有“冰箱”的产品情况Select *From cpWhere cpmc like %冰箱%；2、查询xscj数据库xs表中姓“王”且单名的学生情况Select *From xsWhere xm like 王_;3、查询xsh数据库cp表中库存量为“200”、“30

8、0”和“500”的情况Select *From cpWhere kcl in（200，300，500）；或者select *From cpWhere kcl=200 or kcl=300 or kcl=500；4、在xscj数据库中有描述课程情况的kc表和描述学生成绩表的xs_kc表。查找选修了课程号为101的课程的学生的情况；Select *From xsWhere xh in（select xh from xs_kc where kch=101）；5、查询未选修离散数学的学生的情况Select XH,XM,ZYM,ZXFFROM XSWHERE XH NOT IN(SELECT XHFRO

9、M XS_KCWHERE KCH IN(SELECT KCHFROM KCWHERE KCM= 离散数学）；6、查找比所有计算机系学生年龄都大的学生Select *From xsWhere cssj=ANY(SELECT CJFROM XS_KCWHERE KCH= 101);8、查找选修206号课程的学生姓名Select xmFrom xsWhere exists（select *From xs_kcWhere xh=xs.xh and kch=206）；9、查找选修了全部课程的学生的姓名Select xmFROM XSWHERE NOT EXISTS(SELECT *FROM KCWHER

10、E NOT EXISTS(SELECT *FROM XS_KCWHERE XH=XS.XH AND KCH=KC.KCH);10、查找xscj数据库每个学生的情况以及选修的课程情况Select xs.*,XS_KC.*FROM XS,XS_KCWHERE XS.XH=XS_KC.XH;11、自然连接查询SELECT XS.*,XS_KC.KC.KCH,XS_KC.CJFROM XS,XS_KCWHERE XS.XH=XS_KC.XH;Where和having的不同：1位置不一样；2控制范围不一样；3同时存在先做where；Where必须跟在from后面同from子句指定的基本表或视图中找出满足

11、条件的记录；Having子句是在GROUP by 子句进行分组后对分组数据进一步的筛选；1、查找xscj数据库中平均成绩在85分以上的学生的学号和平均成绩SELECT XH AS 学号,AVG(CJ) AS 平均成绩FROM XS_KCGROUP BY XHHAVING AVG(CJ)=85;2、查找选修课程超过两门且成绩都在80分以上的学生的学号SELSCT XH AS 学号FROM XS_KCWHERE CJ=80GROUP BY XHHAVING GOUP(*)2;3、查找通讯工程学院专业平均成绩在85分以上的学生的学号和平均成绩SELECT XH AS学号,AVG(CJ) AS 平均成

12、绩FROM XS_KCWHERE XH IN(SELECT XHFROM XSWHERE ZYM= 通讯工程）GROUP BY XHHAVING AVG(CJ)=85;视图的作用：1安全性（可以限制用户访问表的权限）；2方便性（利用图来封装复杂语句）；3一致性（用户查询视图可以到统一的格式）；通过视图也能够修改基本表创建CS_KC视图，包括计算机专业个学生的学号、其选修的课程号及成绩。Create OR REPLACE VIEW CS_KCASSELECT XS.XH,KCH,CJFROM XS,XS_KCWHERE XS.XH=XS_CK.XH AND ZYM= 计算机WITH CHECK

13、OPTION;创建计算机学生的平均成绩视图CS_KC_AVG,包括学号（num）和平均成绩（score_avg）。CREATE OR REPLACE VIEW CS_KC_AVG(num,score_avg)ASSELECT XH,AVG(CJ)FROM XS_KCGROUP BU XH;查询视图查找计算机专业的学生学号和选秀的课程号SELECT XH,KCHFROM CS_KC更新视图CREATE OR REPLACE VIEW CS_XSJASSELECT *FROM XSWHERE ZYM= 计算机；INSERT INTO CS_XSIVALUES(001115, 刘明,计算机，男，TO

14、_DATE(19890809, YYYYMMDD),50, 三好学生)第五章 PLSQL语言介绍SELECT查询数据库INSERT在数据库中插入一行数据UPDATE更新数据库中的数据DELETE 从表中删除数据GEANT将权限和角色授予用户或角色REVOKE从用户或角色收回权限DECLARExm varchar2(8):= 张琼;zym varchar2(10):= 计算机;zxf number(2):=45;BEGINUPDATE XSSET zxf=zxfWHERE xm=xm;IF SQL%NOTFOUND THENINSERT INTO XS(XH,XM,ZYM,ZXF)VALUES(

15、xs_sequence,NEXTVAL,xm,zym,zxf);END IF;END;判断选修了离散数学的总人数是否大于100，超过100人则输出“选修了离散数学的人数大与100”否则输出“选修了离散数学人数小于100”DECLAREv_num number(3);BEGINSELECT COUNT(DISTINCT XH)INTO v_numFROM XS_KC,KCWHERE XS_KC,KCM=KC.KCM AND KC.KCM= 离散数学；IF v_num100 THENdbms_output,put_line(选修了离散数学的人数大于100)；ELSEdbms_output,put_

16、line(选修了离散数学的人数小于100)；END IF;END;用while-loop-end循环做10的阶层DECLAREn NUMBER:=1;count1 NUMBER:=2;BEGINWHILE count1=10LOOPn:=n*count1;count1:=count1+1;END LOOP;dbms_output,put_line(to_char(n);END;创建一个计算学生总成绩的函数，要求以学号字段来进行统计，并且当无相关学号时扑捉异常；使用这个函数统计学号为10050402101学生的总成绩。CREATE OR REPLAE FUNCTION SUN_CJ(in_xh

17、IN CHAR)RETURN NUMBERASOUT_CJ NUMBER;BEGINSELECT SUN(CJ) INTO OUT_CJFROM XS_KCWHERE XH=IN_XHGROUP BY XH；EXCEPTIONWHEN NOT_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE(not_data_found)RETURN(OUT_CJ);END SUN_CJ;DECLAREONE_CJ NUMBERBEGINONE_CJ:=SUN_CJ(20050402101);Dbms_output.put_linr(to_char(one_cj);End;声明游标DEC

18、LARE CURSOR XS_CURISSELECT XH,XM,CJFROM XSWHERE ZYM= 计算机；打开游标：OPEN XS_CUR读取数据：FETCH XS_CUR INTO XH,XM,CJ;关闭游标：CLOSE XS_CUR;第六章 存储过程和触发器存储过程有返回值，但PRO不能返回值。统计表xs中男女同学的人数，存储过程使用了一个输入参数和一个输出参数CREATE OR REPLACE PROCEDURE count_num( sex IN char, numOUT number )ASBEGINIF sex= 男；THENSELECT COUNT(XB) INTO nu

19、mFROM XSWHERE XB= 男；ELSESELECT COUNT(XB) INTO numFROM XSWHERE XB= 女；END IF;END count_num;DECLAREMan_num NUMBER;BEGINCount_num(男，man_num);END;用触发器在数据库xscj的xs表执行插入、更新、和删除3种操作后给出相应提示CREATE TRIGGER cue_xsAFTER INSERT OR UPDATE OR DELETE ON XS FOR EACH ROWDECLAREInfor char(10);BEGINIF INSERTING THENInfor

20、:=插入；ELSIF UPDATING THENInfor:=更新；ELSEInfor:=删除；END IF;INSERT INTO SQL_INFO VALUES(infor)；END cue_xs;第七章 系统安全管理Oracle中常见的数据对象有：数据库对象有很多，比如，表，索引，视图，触发器，序列，过程，函数和包等数据对象，是性质相同的数据元素的集合。数据库方案，是一种解决方案，有特定应用场景的解决方案。用户要连接到数据库必须具有create session权限。概要文件是用于限制由用户使用的系统和数据库资源，并管理口令限制。第八章 数据库备份和恢复日志文件至少有3个，还要有归档日志文

21、件。备份一个数据库有三种标准方式：导出备份，脱机备份，和联机备份。物理备份和逻辑备份的区别：物理备份是在操作系统层面上复制物理文件，逻辑备份是在数据库中用export导出的二进制文件；物理备份主要是为了防止操作系统级的数据库组件丢失，逻辑备份主要用于实例之间移动指定数据或在不同的系统结构、操作系统版本或oracle版本之间移动数据。导入模式包括:用户模式、表模式和全局模式（又称数据库模式），与导出完全相同。联机备份又可成为热备份，或archivelog备份，联机备份要求数据库运行在archivelog方式（归档方式）下。用户想要连接到数据库必须要有一种权限称为：Create sessionWi

22、th check option :指出在视图上所进行的修改都要符合限定所规定的限制条件，这样确保数据修改后仍可以通过视图看到修改的数据。With admin option:将对其他用户授权的能力传递给被授予者。创建触发器，当用户对test表执行DML语句时，将相关信息记录到日志表创建测试表CREATE TABLE test(t_id NUMBER(4),t_name VARCHAR2(20),t_age NUMBER(2),t_sex CHAR);创建记录测试表CREATE TABLE test_log(l_user VARCHAR2(15),l_type VARCHAR2(15),l_date VARCHAR2(30); 创建触发器CREATE OR REPLACE TRIGGER test_triggerAFTER DELETE OR INSERT OR UPD