Oracle基础知识大全.pdf

《Oracle基础知识大全.pdf》由会员分享，可在线阅读，更多相关《Oracle基础知识大全.pdf（65页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、课程目标:1.了解数据库系统在项目开发中的地位2.了解目前主流数据库系统3.熟练掌握查询命令4.熟练掌握数据操作方式5.熟练使用数据库内置函数6.掌握pl/sql程序设计语法结构7.掌握视图8.掌握存储过程9.掌握用户自定义函数10.掌握触发器了解：1.数据库组件：1.1 实施应用集群1.2 Oracle OLAP1.3 数据挖掘1.4 应用程序接口1.5 网络组件1.6 SQL Plus1.7 企业管理器OEM2.几个版本间的差异：2.1 增加了服务器参数文件SPFILE2.2 使用了 OMF免去了手工管理数据库文件的繁琐2.3 支持多种数据库块大小2.4 会话可以自动调整PGA的大小2.5

2、 引入了列表分区课程内容：1.操作数据之前的准备工作：1.1.要保证有一个数据库：1.2.要保证创建一个表空间create tablespace hbjrdatafile E:dbshbjr.dbf size 100M;1.3.要创建一个用户，用户默认的表空间就是你创建的表空间create user hbjridentified by orcldefault tablespace hbjr;授权限：grant dba to hbjr;1.4.用户/密码登录：2.表名/列字段名起名规范：2.1 使用常规字母和数字2.2 长度不要超过302.3 表名/列名不能重复2.4 不能使用oracle的保留

3、字3.数据类型3.1 char:定长，存储单位-字节（bite）3.2 varchar2:变长，存储单位-字节3.3 nchar:定长，存储单位-字符3.4 nvarchar2:变长，存储单位-字符3.5 date:日期类型，7 个字节，存储着-世纪，年，月，日，时，分，秒3.6 timestamp:日期类型，1 1 个字节，存储着-世纪，年，月，日，时，分，秒，毫秒3.7 number:数据类型（数值类型）3.8 clob:大字段类型,适合存储纯文本信息（例如：*.txt文件）3.9 blob:大字段类型，适合存储二进制文件(例如：图3.10 定义数据类型的意义：3.10.1 能够更有效，更

4、直观的描述外部数据信息3.10.2 从程序运行角度来考虑，定义了一种数据类型，实际就是定义了一种存储格式和在此格式上的运算方式，也就是数据结构+算法，通过这种预先定义的机制，计算机可以加快程序运行的速度。4.创建表/修改表珊(J除表(DDL)4.1 创建表：4.1.1 语句格式：create table tableName(colName dataT ype,colName dataT ype)；4.1.2 规范：4.1.2.1 列字段至少要有一个4.1.2.2 列字段名称后必须跟着数据类型4.1.2.3 如果有两个及两个以上的字段，那么字段之间用逗号(,)分隔4.1.2.4 列名称不允许重复

5、(在表中不得重复)4.1.2.5 表名称不允许重复(在数据库中不得重复)4.1.3示例：create table test(tid char(10),tname varchar2(20)；4.2 删除表：4 2 1语句格式：drop table tableName;4.2.2示例：drop table test;4.3 更改表4.3.1添 加一个列：格式：alter table tableName add columnNamedataT ype;示彳列：alter table student add addrvarchar2(100);4.3.2 删除一个列：格 式：alter table t

6、ableName drop columncolumnName;示例：alter table student drop column addr;4.3.3 添加多个列：格式：alter table tableName add(columnNamel dataT ype,columnName2 dataT ype,colunmName3 dataT ype)；示例：alter table student add(addr varchar2(100),age int,dept varchar2(50);4.3.4 删除多个列：格式：alter table tableName drop(columnN

7、ame 1 ,columnName2)；示例：alter table student drop(addr,age);4.3.5更 改一个列(更改列的定义-数据类型)格 式：alter table tableName modifycolumnName newDataT ype;示 例：alter table student modify deptvarchar2(100);4.3.6 更改多个列：格式：alter table tableName modify(columnName 1 newDataT ype,columnName2 newDataT ype,colunmName3 newDat

8、aT ype)；示例：alter table student modify(stu_no char(20),stu_name varchar2(20),dept varchar2(60);4.3.7更改列名称：格式：alter table tableName rename columnoldColumnName to newColumnName;示例：alter table student rename column deptto sDepartment;注释：alter,drop,create 属于 DDL 语言5.增加/删除/修改：5.1 往表里插入一条数据：5.1.1 插入若干列的值：格

9、 式：insert intotableName(columnName l,columnName2)values(valuel,value2);5.1.2 插入全部列的值：格式：insert into tableNamevalues(有多少个列字段此处就赋值多少个）;5.2 从表里删除数据格式：delete from tableName where 一 条件或者：tauncate table table_name 5.3 修改表记录：格式：update tableName set columnName=newValuewhere 一 条 件6.查询：6.1 查询全部数据格式：select*fro

10、m tableName-查询全部学生的信息-示例：select*from student;6.2 查找特定的列（若干列）格式：select columnName from tableName;-查询所有学生的姓名-示例：select stu_name from student;6.3 查找符合条件的部分数据用where表示过滤的条件格式：select*from tableName where 条件-查询所有机电系的学生信息-示例：select*from student where sdepartment=jdx;6.4 列别名格式 1 ：columnName alias格式 2：columnN

11、ame as alias格式 3：columnName as alias示例：select stu_no 学号,stu_name 姓名,sde叩rtment系别from studnet;6.5 distinct:消除重复行格式：select distinct columnName from tableName;-查询院里都设置了那些系别-示例：select disctinct sdepartment from studnet;6.6 查询常量：格式：select finalData from tableName;示例：select stu_name,河套大学，from student;6.7

12、使用表达式（算术表达式/关系表达式/逻辑表达式/连接运算符/模糊查询/范围查询）6.7.1 关系表达式：=,=,=,!=-查询所有分数=60的学生的信息：-示例：select*from student where grade=60;6.7.2 范围运算符：between and,in-查询所有分数在60-80这个范围的学生的信息-select*from student where grade between 60 and 80;-查询所有机电系，师范系，艺体系的学生的信息-select*from student where sdepartmentin(jdx,sfx,ytx);6.7.3 模糊

13、查询：like一般和like搭配使用的符号：%,_-查询所有姓zhao的同学的信息-示例:select*from student where stu_name likezhao%;-查询所有姓zhao的并且名字只有一个字的同学的信息-示例：select*from studnet where stu_name likezhao；6.7.4 逻辑表达式：and,or,not-查询所有机电系或者医学院系的学生的信 息 一示 例：select*from student wheresdepartment=jdx or sdepartment=yxy;-查询所有机电系的并且成绩=60的学生的信息一示 例：

14、select*from student wheresdepartment=j dx and grade=60;-查询所有不是艺体系的学生的信息一示 例：select*from student where not(sdepartment=ytx);6.7.5连接运算符：II将若干个字段或者字符串拼接而成一个列-将学生的姓名和成绩拼接然后显示一示例：select stu_name II 的成绩是：II grade成绩信息from studentwhere stu_no=5555;67 6 null:是未分配的，或者未知的，或者不可用的6.7.6.1 如何插入null值：一一将学生的姓名和系别分别置

15、空示 例：insert into studentvalues(123,null,null,0);6.7.6.2 如何查询null值:-查询所有姓名为空的学生的信息示 例:select*from student wherestu_name is null;6.7.6.3 如何修改null值：-将所有姓名为空的学生重命名为 彳 寺 定 一示例：update student set stu_name=待定where stu_name is null;6.7.6.4 如何删除null值：-将所有姓名未知的学生记录删除 示 例：delete from student wherestu_name is n

16、ull;6.7.6.5 null参与运算的结果：a.参与数学运算：结果为nullb.参与关系运算：忽略c.参与逻辑运算：A N DT R U EF A L S EN U L LT R U ET R U EF A L S EN U L LF A L S EF A L S EF A L S EF A L S EN U L LN U L LF A L S EN U L LORT R U EF A L S ENULLT R U ET R U ET R U ET R U EF A L S ET R U EF A L S EN U L LN U L LT R U EN U L LN U L Ld.参与连接

17、运算：为null的字段其值处理为空的字符串e.参与排序，其值最大6.8 order:排序-asc:升序，默认的，desc-降序6.8.1 order b y总是在w here条件之后出现-将所有学生的信息按照分数降序排列-示例：select*from student order by grade desc;6.8.2当 order b y 中出现两个或者两个以上的字段排序是先是按照第一个出现的字段整体排序然后在第一个字段值相同的情况下再按照第二个字段进行排序然后在第一个和第二个字段都相同的情况下再按照第三个字段进行排序.依次类推-查询所有的学生信息，按照分数降序，编号升序的顺序进行排列-示例：

18、select*from student order by grade desc,stu_no asc;6.9 系统函数：6.9.1 系统函数无一例外都是提供某种功能的，例如:nvl6.9.2 几乎总是出现在字段名的前面6.9.3 分类：a.字符函数b.数值函数c.日期函数d.转换函 数 to_char,to_date,to_numbere.通用函数f.分组函数f.l max(),min(),avg(),count(),sum()f2.decode()6.9.4操作日期函数：1.插入系统当前时间：示例：insert into emp(empno,ename,hiredate)values(801

19、0,zhuaoshuai,sysdate)；2.插入指定的时间(按oracle数据库默认格式插入)示例：insert into emp(empno,ename,hiredate)values(8O2O,panzhiming,01-2 月-H，)；3.插入指定格式的日期：示例：insert into emp(empno,ename,hiredate)values(8030,tudou,to_date(2011/01/01,yyyy/mm/dd);i n s e rt i n t o testl(t2)v a l u e s(to_date(2013-10-1013:13:13 ,y y y y-

20、m m-d d h h 2 4:m i:s s );6.10 group 子句：6.10.1 功能：对数据进行分组-查询各个岗位的职员人数-示例：select job,count(job)from emp groupby(job);=4的所有部门-一示例：select deptno,count(deptno)from empgroup by(deptno)having count(deptno)=4;6.12连接查询：6.12.1 内连接-边表的数据得匹配上，任何一方如果和对方表没有匹配的数据，则会被过滤掉a.等值连接：-查询在 纽约 工作的员工信息：select e.*from emp e,

21、dept dwhere e.deptno=d.deptno and d.loc=NEWYORK-查看员工员工信息及其工作地selectempno,enamez job,sal,comm,e.deptno,dname,locfrom emp e,dept dwhere e.deptno=d.deptno;b.非等值连接：-查询工资级别是3 的员工的信息：select e.*from emp e,salgrade swhere e.sal=s.losal and e.sal sg.losal and e.sal=4 and m=6;6.16 层次查询：select level 层次，empno 编

22、号，ename 姓名，mgr 经理编号from empstart with mgr is nullconnect by mgr=prior empno;select(lpad(,4*(level-l),)II empno II II ename)员工管理层次from empstart with mgr is nullconnect by mgr=prior empno;6.17 top-n:rownum使用时注意select*from empwhere rownum 3order by sal;select*from(select*from emp order by sal)twhere ro

23、wnum=0)and(ggradestart with 10nomaxvaluenocyclenocache8.2.2 使用：和查询表的操作是一样的示例：select seq_stu_sid.nextval from dual;8.2.3 删除：drop sequence seq_stu_sid;8.3 自定义函数：创建，调用，删除8.3.1 用途：就是提供某种功能的8.3.2 创建：示例：create or replace function salaryFun(enoemp.empno%type)return varchar2asbegin代码；end;8.3.3 使用：和系统函数的用法类似

24、示例：select salaryFun(7369)from dual;8.3.4 删除：drop function salaryFun;8.3.5 示例：-创建一个函数create or replace function salaryFun(eno emp.empno%type)return varchar2-声明返回值类型asv_salary number;-声明变量beginselect(nvl(sal,0)+(nvl(comm,0)into v_salary fromemp where empno=eno;return(eno II 的总收入是：II v_salary);end sala

25、ry Fun;-使用自定义函数select salaryFun(7499)from dual;8.4 存储过程：创建，调用，删除8.4.1 用途：提供某种功能的，往往是一次性执行若干条DML语句的8.4.2 创建：create or replace procedure stuinsertasbegin代码段；end;8.4.3 使用：有两种方法可以调用存储过程8.4.4 删除：drop procedure stuinsert;8.4.5 示 例1：无参存储过程 创建一个存储过程，一次性插入100条数 据-1.创建表一create table student(sid char(10),sname

26、 varchar2(20);-2.往student表里一次性插入100条数据 一create or replace procedure stulnsertasbeginfor v_temp in 1.500 loopinsert into student values(to_char(v_temp),dfdf);end loop;end;-3.调用存储过程-3.1 第一种调用方法-beginstuinsert;end;3.2 第二种调用方法：命 令 execute stuinsert;8.4.6传入参数：-根据员工编号修改其工资信息-create or replace procedure ch

27、ange_salary2(v_empno innumber,v_new_sal in number)asbeginupdate empset sal=v_new_salwhere empno=v_empno;commit;end;8.4.7传出参数：-根据员工编号获取其工资信息一create or replace procedure getSalaryByNo(v_empno innumber,v_sal out number)asbeginselect sal into v_salfrom empwhere empno=v_empno;end;declarev_sal number;begi

28、ngetSalaryByNo(7499,v_sal);dbms_output.putine(员工工资：II v_sal);end;-根据员工编号获取其姓名信息一create or replace procedure getNameByNo(v_empno innumber,v_ename out varchar2)asbeginselect ename into v_enamefrom empwhere empno=v_empno;end;declarev_ename emp.ename%type;begingetNameByNo(7900,v_ename);dbms_output.putin

29、e(员工姓名：II v_ename);end;8.4.8传入、传出参数：-根据员工编号获取其工资信息create or replace procedure getSalaryByNo2(v_numin out number)asbeginselect sal into v_numfrom empwhere empno=v_num;end;declarev_num number;beginv_num:=7876;getSalaryByNo2(v_num);dbms_output.put_line(v_num II，员工的工资：II v_num);end;8.4.9优点：a.大大增强了 sql语言

30、的功能和灵活性b.可保证数据的安全性和完整性c.改善sql语句的性能，执行速度快d.可以降低网络的通信量e.使提现企业规则的运算程序放入数据库服务器中以便集中控制8.4.10存储过程与自定义函数的区别：a.参数模式：前者可以使in,out,后者只能是inb.return:前者不包含，后者必须包含c.执行方式：前者可以用execute语句执行，后者不可以8.5触发器：创建，删除8.5.1 用途：监控某种事件的发生，一旦事件发生了系统会自动调用8.5.2 创建：create or replace trigger stuinsert_triggerbefore I afterinsertldelet

31、elupdate on student（在操作之前/之后触发）（触发代码的操作类型）（操作的表）begin被触发的操作代码段;end;8.5.3调 用：当 监 控 的 操 作 一 旦 发 生 则 触 发 器 就 被系统自动调用8.5.4 册 1 J除:drop trigger stuinsert_trigger;8.5.5 示 例：创 建 一 个 触 发 器 create or replace trigger stulnsert_triggerafter insert on studentbegindbms_output.put_line(,您 在 学 生 表 上 进 行 了 插入操作!)；-

32、触 发 触 发 器 的 insert into student valuesf 121,dfdsf);8 5 5.1语 句 级 别 触 发 器：一 条 语 句 触 发 一 次示 例：-如 果 在 非 工 作 日 的 非 工 作 时 间 插 入 数据则操作不成功一create or replace trigger secure_deptbefore insert on deptbeginif(to_char(sysdate,DY)in(星期六星期H)or(to_char(sysdate,HH24:MI)notbetween 08:00,and18:00,)thenraise_applicatio

33、n_error(-20001,you caninsert into the table of dept only in work time!);end if;end;-向dept表插入数据测试触发器是否工作：insert into deptvalues(2345,SOFT,HZ);8.5.5.2行级触发器：一行就触发一次示例：如果工作岗位不是MANAGER或者ANALYST,则工资不能超过5000 create or replace trigger restrict_salbefore insert or update of sal on empfor each rowbeginif(not(

34、:new.job in(MANAGER,ANALYST)and:new.sal 5000thenraise_application_error(-20002,，普通员工不能赚到这么多薪水)；end if;end;-向emp表插入或者修改数据测试insert into emp(empno,ename,job,sal)values(7941,Eniyah,CLERK,3000);insert into emp(empno,ename,job,sal)values(7942,Phennma,CLERK,5500);update empset sal=5001where empno=7941;8.6

35、同义词：类似于对象的别名，可以代替对象的原名称进行使用8.6.1 创建:create synonym synName for object;-当前用户可用或者：同义词；同义字create public synonym synName for object;所有用户可用8.6.2 示例:create synonym tl for testl;为 t e s t l 表创建同义词后，则 可 以 把t l 当成t e s t l 表使用8.6.3 用途：a.可以方便的访问其他对象b.可以缩短对象名字的长度c.在分布式数据库中，它可以使用户操作远程对象像操作本地对象一样方便8.6.4 删除:drop

36、synonym tl;8.7 包：8.7.1 用途可以将多个功能或用途相近的程序单元(存储过程或者函数等)组合到一起形成一个逻辑上的集合8.7.2 创建create or replace package tplasfunction salaryFun(eno emp.empno%type)return varchar2;procedure change_salary2(v_empno in number,v_new_sal in number);procedure getSalaryByNo(v_empno in number,v_sal out number);end tpl;/create

37、 or replace package body tplas根据员工编号获取员工的工资信息:function salaryFun(eno emp.empno%type)return varchar2asv_salary number;beginselect(nvl(sal,0)+(nvl(comm,0)intov_salary from emp where empno=eno;return(eno|*pAxUEOEeEQ*|v_salary);end salaryFun;根据员工编号修改其工资信息:procedure change_salary2(v_empno in number,v_new

38、_sal in number)asbeginupdate empset sal=v_new_salwhere empno=v_empno;commit;end change_salary2;根据员工编号获取员工的工资信息:procedure getSalaryByNo(v_empno in number,v_sal out number)asbeginselect sal into v_salfrom empwhere empno=v_empno;end getsalarybyno;end tpl;/8.7.3使用一调用包中的程序单元:select tpl.salaryFun(7369)fro

39、m dual;select empno,ename,job,sal,tpl.salaryFun(empno)from emp;begintpl.change_salary2(7369,3200);end;declarev_sal number;begingetSalaryB yN o(7499z v _ s a l);dbms_output.p u t_ lin e (员工的工资是:|v _ s a l);end;8.7.4删除drop package t p l;8.8索引:8.8.1 用途：提升查询效率9.PL/SQL开发介绍9.1 PL/SQL存在的意义：它是对标准SQL语言的扩展，增加

40、了编程语言的特点，以实现复杂的功能或计算9.2 语法结构a.程序块署名的声明b.变量、常量 的 声 明-declarec.程序体 一 begin end;d.异常处理-exception9.3 数据类型9.3.1 数据库中的数据类型它都可以使用9.3.2%type-映射某张表的某个字段的类型%rowtype 映射某张表的某行记录的类型9.4 流程控制：9.3.1 分支：if(condition)thenelsif(condition)thenelsif(condition)thenelseend if;9.3.2 循环：a.loopexit when 退出条件;end loop;b.f o r

41、 变 量 i n 最小值.最 大 值 loopend loop;c.while 条件 loopend loop;d.标签9.5 异常处理9.5.1 异常种类：a.no_data_found:b.too_many_ rows:c.value_error:d.dup_val_on_index:e.cursor_already_open:f.invalid_cursor:g.zero_divide:h.others:9.5.2 用户自定义异常：9.6 总结PL/SQL程序由两部分组成：变量、常量的声明/定义方法体标准的SQL语句流程控制语句异常9.7示例：1.第一个PL/SQL程序，不可少的组成部分

42、一begindbms_output.put_line(dfsdfsdfsdfds);end;PL/SQL的一个基本结构-declare-PL/SQL中变量的定义一 所处位置：begin之 前,declare之 后 一v_templ number;v_temp2 char(10);begin-方法体，即代码段所在的地方一v_templ:=80;dbms_output.put_line(v_temp 1);end;2.declare部分的补充 一declare-常量的定义及赋值-v_temp constant char(5)default aaa;v_templ constant char(5):

43、=bbbb;v_temp2 constant char(5)not null default aaa;v_temp3 constant char(5)not null:=bbbb;-常 量 必 须 赋 初 值-v_templO constant char(5);-变量的定义-v_temp4 char(5);v_temp5 char(5):=ccc;v_temp6 char(5)default ddd;v_temp7 char(5)not null:=eee;v_temp8 char(5)not null default fff;v_temp9 emp.empno%type;v_templO e

44、mp.empno%type:=7369;-声 明 为 not n u ll的变量必须赋初值-v_temp9 char(5)not null;begindbms_output.put_line(constant value is:II v_temp8);end;begin一 代码段一 方法体中可以存在标准的SQL语句一insert into student values(111,dfdsfds);-5.捕获异常的地方，在代码段之后，end之前一exceptionwhen dup_val_on_index thendbms_output.put_line(,反唯一约束，您输入的数据不合法！)；wh

45、en others thenrollback;end;4.PL/SQL代码段中使用流程控制语句-declarev_condition char(5);beginv_condition:=lower;如果sid=300,那么就将其设置为：300if v_condition=upper thenupdate student set sid=300 where sid=to_number(30();else 如果sidvlOO,则将其设置为：100update student set sid=100 where sid 0 loopdbms_output.put_line(v_temp：II v_t

46、emp);v_temp:=v_temp-1;end loop;end;declarev_flag boolean:=true;begin-putine()函数不能输出boolean型的数据值:dbms_output.putine(标志：II v_flag);end;begin-可以执行DDL及DCL语句：execute immediate create table test(ta varchar2(10),tb number(3),；end;begin-可以执行DDL及DCL语句：execute immediate drop table test;end;declarev_sal emp.sa

47、l%type;-用户自定义异常sal_over_limit exception;beginselect salinto v_salfrom empwhere empno=7369;if(v_sal 3000)thenraise sal_over_limit;end if;dbms_output.putine(后续操作。,);exceptionwhen sal_over_limit thendbms_output.putineC工资超标，取消后续操作);end;10.游标10.1 本质：是一个指针10.2 如何使用:示例：第一个例子一declarecursor my_curisselect*fr

48、om emp;v_emp emp%rowtype;beginf j开 游 标 open my_cur;从游标里获取数据fetch my_cur into v_emp;dbms_output.put_line(1 no:1 v_emp.empno|1,姓名：|v_emp.ename);fetch my_cur into v_emp;dbms_output.put_line(1 no:|v_emp.empno|1,姓名：1|v_emp.ename);关 闭 游 标 close my_cur;end;-第二个 例 子 declarecursor my_curisselect*from emp;beg

49、infor v_temp in my_cur loopdbm s_outpu t.p u t_ lin e(1 id:|v_temp.empno|姓名：1|v_temp.ename);end loop;end;11.事务:11.1 什么是事务：若干条sql语句要么全部执行成功，要么全部执行失败11.2 事务控制语句：commit,rollback,savepoint11.3 事务的特点：原子性，一致性，隔离性，持久性a.原子性：b.一致性:c.隔离性:默认隔离级别：一个事务对数据进行修改时，其它事务可以查询但不可以修改d.持久性：事务提交后，数据最新结果保存到外部存储器上，一般指硬盘-各自可以

50、查询-select,但是都只能看到 别人已提交的内容-各自可以增、册!1、改数据，对别人没有影响-但是如果双方同时操作(删/改)同一条数据，则死锁12.数据库设计：12.1 现实世界的数据玲抽象成概念数据模型(CDM)3转换成物理数据模型(PDM)12.2 规范化：a.第一范式：属性不可再分割；有唯一标识b.第二范式：标识符属性值可以唯一地确定非标识符属性值c.第三范式：非标识符属性必须依赖于标识符确定,不 能 依 赖 于 其 他 非 标 识 符 属性确定12.3 实践：书上示例13.安全管理：13.1 用户13.2 权限13.3 角色14.oracle体系结构：14.1 逻辑存储结构：a.表