Oracle教程(三)

Oracle 教程 -1- Oracle 教程 2009.5 Oracle 教程 -2- 目录 引言................................................................................................................................................... 3 第一章 数据库基础 ......................................................................................................................... 6 第二章 Oracle 入门 ......................................................................................................................... 6 第三章 查询基础 ........................................................................................................................... 11 第四章 单行函数 ........................................................................................................................... 14 第五章 分组函数 ........................................................................................................................... 19 第六章 多表查询 ........................................................................................................................... 21 第七章 子查询 ............................................................................................................................... 24 第八章 高级查询 ........................................................................................................................... 36 第九章 数据字典 ........................................................................................... 错误！未定义书签。 第十章 Oracle 数据类型 ............................................................................... 错误！未定义书签。 第十一章 Oracle 体系结构(DBA) ................................................................ 错误！未定义书签。 第十二章 DDL(改变表结构) ........................................................................ 错误！未定义书签。 第十三章 DML(改变数据结构） ................................................................. 错误！未定义书签。 第十四章 约束 ............................................................................................... 错误！未定义书签。 第十五章 视图 ............................................................................................... 错误！未定义书签。 第十六章 索引 ............................................................................................... 错误！未定义书签。 第十七章 序列、同义词 ............................................................................... 错误！未定义书签。 第十八章 PL SQL .......................................................................................... 错误！未定义书签。 第十九章 游标、函数 ................................................................................... 错误！未定义书签。 第二十章 存储过程 ....................................................................................... 错误！未定义书签。 第二十一章 触发器 ....................................................................................... 错误！未定义书签。 第二十二章 事务（数据库系统概论） ....................................................... 错误！未定义书签。 第二十三章 用户管理 ................................................................................... 错误！未定义书签。 第二十四章 备份 恢复 SQLLoader ............................................................ 错误！未定义书签。 第二十五章 数据库 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 范式 ....................................................................... 错误！未定义书签。 第二十六章 数据库设计工具 ....................................................................... 错误！未定义书签。 第二十七章 对象关系数据库系统 ............................................................... 错误！未定义书签。 第二十八章 其他数据库 ............................................................................... 错误！未定义书签。 Oracle 教程 -3- 引言 SUN 2008 初 10 亿美元收购 MySQL Oracle 2009 年 4 月 74 亿美元收购 SUN Sun 与 Oracle 合并的未来 1，如果云计算对企业来说变得越来越重要，那么数据将是云计算的核心。而讲到数据，也就意味着 数据库。就如塔克商学院数字策略中心主任 M. Eric Johnson 所说的那样，Sun 已经清楚展现了一 个真实的云计算环境。就那些将云计算停留在理论阶段的对手来说，收购 Sun 对 Oracle 来说将获 得竞争优势。 2，Johnson 主任还指出，Sun 在很多需求旺盛的方面“保持领先地位”。Sun 的技术可以帮助数 据库提高性能，尤其是对大型数据库。Oracle 和 Sun 的结合之后，对任何一个信息官来说都具有吸 引力。 3，Oracle 软件和 Java 的紧密结合，使得 Oracle 的软件能跨平台使用。就如 IDC 研究副总裁 Jean Bozman 指出那样，真正云计算的第一步是“将服务器和存储单元垂直化”，可以适用于不同的 硬件平台。凭借 Java，Oracle 可以拓展跨平台的控制力，而这正是企业 IT 部门想要的。实际上， Oracle 的 Oracle Fusion Middleware 正是基于 Java。 4，有意思的是，Oracle Fusion Middleware 扩大了对服务器的需求，其中原因正是由于它是基 于 Java。而现在，Oracle 通过购得 Sun 服务器资源，可以进一步降低价格，从而进一步提高 Oracle Fusion Middleware 的竞争力。 5，由于 Oracle 大部分收入来自数据库软件许可，因此有必要的话，可以用便宜的服务器硬件 作为促销的手段，这样对那些硬件服务器竞争对手来说是不小的打击。 6，Sun 已经售出 160 万到 200 万台服务器，还有更多的运行 Sun Solaris 操作系统的其它公司 的服务器。这对 Oracle 捆绑销售自家产品也很有帮助。 7，Oracle 收购 Sun，当然也包括开源数据库 MySQL。对于那些预算不多但又需要 Oracle 产 品的用户，Oracle 可以用 MySQL 来满足这些用户的需求。这可以使 Oracle 进入中小企业业务，更 有机会在这方面超过 SAP。此外，还提供了和微软 SQLServer 的竞争产品，而不需要降低 Oracle 自有的产品的定位。 Oracle 教程 -4- Oracle 和 Sun 的合作可能会影响 Oracle 和一些合作伙伴的关系，比如 Oracle 和惠普的关系可 能就会出现问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 。不过，不像 IBM，事实上，每个硬件公司都绕不开 Oracle，因为它是数据库的主 力军。他们离不开 Oracle，因此，他们也只能选择忍气吞声。 而且，与 Sun 和 IBM 联合不同，Oracle 和 Sun 不存在硬件的冲突，那些 Sun 的服务器用户不 必担心合并会导致放弃自己在使用的服务器产品线。 总而言之，Oracle 以相对较低的价格收购 Sun 是明智之举，将会使得合并后的 Oracle 成为各 大公司数据中心的枢纽。 云计算 李开复（现任 Google 全球副总裁、中国区总裁）打了一个很形象的比喻：钱庄。最早人们只是把 钱放在枕头底下，后来有了钱庄，很安全，不过兑现起来比较麻烦。现在发展到银行可以到任何一 个网点取钱，甚至通过 ATM，或者国外的渠道。就像用电不需要家家装备发电机，直接从电力公司 购买一样。“云计算”带来的就是这样一种变革——由谷歌、IBM 这样的专业网络公司来搭建计算机 存储、运算中心，用户通过一根网线借助浏览器就可以很方便的访问，把“云”做为资料存储以及应 用服务的中心。 GOOGLE （一）原理: 云计算(Cloud Computing)是分布式处理(Distributed Computing)、并行处理(Parallel Comput ing)和网格计算(Grid Computing)的发展，或者说是这些计算机科学概念的商业实现。 云计算的基本原理是，通过使计算分布在大量的分布式计算机上，而非本地计算机或远程服务 器中，企业数据中心的运行将更与互联网相似。这使得企业能够将资源切换到需要的应用上，根据 需求访问计算机和存储系统。 这可是一种革命性的举措，打个比方，这就好比是从古老的单台发电机模式转向了电厂集中供 电的模式。它意味着计算能力也可以作为一种商品进行流通，就像煤气、水电一样，取用方便，费 用低廉。最大的不同在于，它是通过互联网进行传输的。 云计算的蓝图已经呼之欲出：在未来，只需要一台笔记本或者一个手机，就可以通过网络服务 来实现我们需要的一切，甚至包括超级计算这样的任务。从这个角度而言，最终用户才是云计算的 真正拥有者。 云计算的应用包含这样的一种思想，把力量联合起来，给其中的每一个成员使用。 Oracle 教程 -5- （二）云计算有哪些好处？ 1、安全，云计算提供了最可靠、最安全的数据存储中心，用户不用再担心数据丢失、病毒入侵 等麻烦。 2、方便，它对用户端的设备要求最低，使用起来很方便。 3、数据共享，它可以轻松实现不同设备间的数据与应用共享。 4、无限可能，它为我们使用网络提供了几乎无限多的可能。 （三）云计算最有利于中小企业？ 云计算技术将使得中小企业的成本大大降低。如果说“云”给大型企业的 IT 部门带来了实惠，那 么对于中小型企业而言，它可算得上是上天的恩赐了。过去，小公司人力资源不足，IT 预算吃紧， 那种动辄数百万美元的 IT 设备所带来的生产力对它们而言真是如梦一般遥远，而如今，“云”为它们 送来了大企业级的技术，并且先期成本极低，升级也很方便。 这一新兴趋势的重要性毋庸置疑，不过，它还仅仅是一系列变革的起步阶段而已。云计算不但 抹平了企业规模所导致的优劣差距，而且极有可能让优劣之势易主。简单地说，当今世上最强大最 具革新意义的技术已不再为大型企业所独有。“云”让每个普通人都能以极低的成本接触到顶尖的 IT 技术。 （四）“云”时代 目前，PC 依然是我们日常工作生活中的核心工具——我们用 PC 处理文档、存储资料，通过电 子邮件或 U 盘与他人分享信息。如果 PC 硬盘坏了，我们会因为资料丢失而束手无策。 而在“云计算”时代，“云”会替我们做存储和计算的工作。“云”就是计算机群，每一群包括了几十 万台、甚至上百万台计算机。“云”的好处还在于，其中的计算机可以随时更新，保证“云”长生不老。 Google 就有好几个这样的“云”，其他 IT 巨头，如微软、雅虎、亚马逊（Amazon）也有或正在建设 这样的“云”。 届时，我们只需要一台能上网的电脑，不需关心存储或计算发生在哪朵“云”上，但一旦有需要， 我们可以在任何地点用任何设备，如电脑、手机等，快速地计算和找到这些资料。我们再也不用担 心资料丢失。 Oracle 教程 -6- 第一章 数据库基础 1.1、数据库基本概念 数据库（Database, DB） 数据库管理系统（Database Management System, DBMS） 数据库管理员（ Database Administrator, DBA） 数据库系统（ Database System, DBS ） 1.2、关系型数据库 Q:目前都有哪些主流的关系型数据库 A:Oracle Oralce、IBM DB2、MS SQL /Server、SyBase SyBase、IBM Informix、MySQL、 Access Q:XML,TXT 可以做为数据库吗？ 1.3、E-R 模型（Entry-Relation） E-R 模型三要素：实体、关系、属性 实体间联系（1:1）（1：n）(n:m) Q:学生与课程什么关系？ 第二章 Oracle 入门 2.1、Oracle 概述 甲骨文，四大创始人 Oracle 教程 -7- Larry Ellison 一生最大的目标，“财富榜超过 Bill Gate” Bruce Scott 已离开 Oralce,创建了一套新的数据库 PointBase 2.2、Oracle 特点 全球化、跨平台的数据库 支持多用户、高性能的事务处理 强大的安全性控制和完整性控制 支持分布式数据库和分布处理 2.3、Oracle 版本 Oracle8i: I internet 表示 Oracle 公司要开始正式进入互联网 Oracle9i:与 Oracle8i 相关，性能方面更佳， 管理更人性化 Oracle10g: g(grid)网格技术 Oracle11g: g(grid)网格技术 Q: 何为网格技术？ 2009 年 1 月 淘宝网决定采用 Oracle 网格计算(Grid Computing)架构来,采用 Oracle 数据库和 Oracle 真正 应用集群来重新打造并强化其基础架构和数据仓库环境 2.4、安装 Oracle 数据库 注意： 1、 安装的时候，一定要关掉防火墙。否则可能造成安装不成功 Oracle 教程 -8- 2、 全局数据库名 SID,类似于 MYSQL 中常用的 localhost. 3、 字符集一定要选择正确。一旦选错，除非更改成该字符集的父类。否则只能重装 4、 安装完主要的用户为： a) 普通用户： Scott/tiger(练习常用) b) 普通管理员：System/system c) 超级管理员：Sys/sys 5、安装完后的服务配置 (运行中输入：services.msc) 开启该服务，会占用 tomcat 的 8080 端口。 查看端口号：tasklist|findstr "8080" 如果装完 Oracle 后，又改了机器名可能会导致 Listener 服务无法启动，解决方式： 修改 C:\oracle\ora92\network\admin\ listener.ora 下的 HOST 2.5、卸载 Oracle 数据库 Oracle 教程 -9- 2.6、Oracle 目录（Admin,oracle92,oradata） Q:这三个文件夹各存放什么文件？ 2.7、Oracle 客户端工具 2.7.1、SQLPLUS 2.7.2、SQLPLUSW 2.7.3、Sql Plus WorkSheet 2.7.4、iSqlplus（HTTP） http://localhost:7778/isqlplus 2.7.5、PL /SQL Developer 7.14 以上版本，带有自动提示功能 2.7.6、Object Browser 国外用的比较多（日本） Oracle 教程 -10- 2.8、Scott 案例下表分析 2.8.1、雇员表：EMP 2.8.2、部门表：Dept 2.8.3、工资等级表：Salgrade Oracle 教程 -11- 2.8.4、奖金表：Bonus 第三章 查询基础 3.1、入门语句 普通用户连接： Conn scott/tiger 超级管理员连接： Conn “sys/sys as sysdba” Disconnect; 断开连接 Save c:\1.txt 把 SQL 存到文件 Ed c:\1.txt 编辑 SQL 语句 @ c:\1.txt 运行 SQL 语句 Desc emp; 描述 Emp 结构 Select * from tab; 查看该用户下的所有对象 Show user; 显示当前用户 如果在 sys 用户下： 查询 Select * from emp; 会报错，原因：emp 是属于 scott，所以此时必 须使用：select * from scott.emp; / 运行上一条语句 3.2、SQL 简介 SQL 全名是结构化查询语言（Structured Query Language），是用于数据库中的 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 数据查 询语言，IBM 公司最早使用在其开发的数据库系统中。1986 年 10 月，美国 ANSI 对 SQL 进行 规范 编程规范下载gsp规范下载钢格栅规范下载警徽规范下载建设厅规范下载 后，以此作为关系式数据库管理系统的标准语言 (ANSI X3. 135-1986)，1987 年 得到国际标准组织的支持下成为国际标准。不过各种通行的数据库系统在其实践过程中都 对 SQL 规范作了某些编改和扩充。所以，实际上不同数据库系统之间的 SQL 语言不能完 全相互通用 DML 语句（数据操作语言）Insert、Update、 Delete、Merge DDL 语句（数据定义语言）Create、Alter、 Drop、Truncate DCL 语句（数据控制语言）Grant、Revoke 事务控制语句 Commit 、Rollback、Savepoint Oracle 教程 -12- 3.3、简单的 Select 语句 3.4、使用算术表达式 + - / * 3.5、连接运算符 || 3.6、使用字段别名 as 3.7、空值 is null 3.8、去除重复行 distinct 3.9、查询结果排序 order by asc(desc) 3.10、比较运算符 > < (!= or <>) between and 3.11、in 操作 not in 3.12、模糊查询 like % 表示零或多个字符 _ 表示一个字符 对于特殊符号可使用 ESCAPE 标识符来查找 select * from emp where ename like '%*_%' escape '*' 上面的 escape 表示*后面的那个符号不当成特殊字符处理，就是查找普通的_符号 3.13、逻辑运算符 or and not 3.14、练习 选择在部门 30 中员工的所有信息 Select * from emp where deptno=30; Oracle 教程 -13- 列出职位为（Manager）的员工的编号，姓名 Select empno,ename from emp where job = „Manager‟; 找出奖金高于工资的员工 Select * from emp where comm>sal; 找出每个员工奖金和工资的总和 Select sal+comm,ename from emp; 找出部门 10 中的经理和部门 20 中的普通员工 Select * from emp where (deptno=10 and job=‟MANAGER‟) or (deptno=20 and job=‟CLERK‟); 找出部门 10 中既不是经理也不是普通员工，而且工资大于等于 2000 的员工 Select * from emp where deptno=10 and job not in(„MANAGER‟,‟CLERK) ‟ and sal>=2000; 找出有奖金的员工的不同工作 Select distinct job from emp where comm is not null and comm>0 找出没有奖金或者奖金低于 500 的员工 Select * from emp where comm<500 or comm is null; 显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 select ename from emp order by hiredate ; Oracle 教程 -14- 第四章 单行函数 4.1、字符函数 Upper SELECT Upper ('abcde') FROM dual ; SELECT * FROM emp WHERE ename=UPPER('smith') ; Lower SELECT lower('ABCDE') FROM dual ; Initcap Select initcap(ename) from emp; Concat Select concat(„a‟,‟b‟) from dual; Select „a‟ || „b‟ from dual; Substr Select substr(„abcde‟,length(„abcde‟)-2) from dual; Select substr(„abcde‟,-3,3) from dual; Length Select length(ename) from emp; Replace Select replace(ename,‟a‟,‟A‟) from emp; Oracle 教程 -15- Instr Select instr('Hello World','or') from dual; 8 indexof Lpad lpad('Smith',10,'*') 左侧填充 lpad() *****Smith Rpad rpad('Smith',10,'*') 右侧填充 rpad() Smith***** Trim trim(' Mr Smith ') 过滤首尾空格 trim() Mr Smith 4.2、数值函数 Round select round(412,-2) from dual; select round(412.313,2) from dual; Mod Trunc select trunc(412.13,-2) from dual; 4.3、日期函数 Months_between() select months_between(sysdate,hiredate) from emp; Add_months() select add_months(sysdate,1) from dual; Oracle 教程 -16- Next_day() select next_day(sysdate,'星期一') from dual; Last_day select last_day(sysdate) from dual; 4.4、转换函数 To_char select to_char(sysdate,'yyyy') from dual; select to_char(sysdate,'fmyyyy-mm-dd') from dual; select to_char(sal,'L999,999,999') from emp; select to_char(sysdate,’D’) from dual;//返回星期 To_number select to_number('13')+to_number('14') from dual; To_date Select to_date(„20090210‟,‟yyyyMMdd‟) from dual; 4.5、通用函数 NVL()函数 select nvl(comm,0) from emp; NULLIF()函数 如果表达式 exp1 与 exp2 的值相等则返回 null，否则 返回 exp1 的值 NVL2()函数 select empno, ename, sal, comm, nvl2(comm, sal+comm, sal) total from emp; Oracle 教程 -17- COALESCE()函数 依次考察各参数表达式，遇到非 null 值即停止并返 回该值。 select empno, ename, sal, comm, coalesce(sal+comm, sal, 0)总收入 from emp; CASE 表达式 select empno, ename, sal, case deptno when 10 then '财务部' when 20 then '研发部' when 30 then '销售部' else '未知部门' end 部门 from emp; DECODE()函数 和 case 表达式类似，decode()函数也用于实现多路分支结构 select empno, ename, sal, decode(deptno, 10, '财务部', 20, '研发部', 30, '销售部', '未知部门') 部门 from emp; 单行函数嵌套 select empno, lpad(initcap(trim(ename)),10,' ') name, job, sal from emp; 4.6、练习 找出每个月倒数第三天受雇的员工（如：2009-5-29） select * from emp where last_day(hiredate)-2=hiredate; Oracle 教程 -18- 找出 25 年前雇的员工 select * from emp where hiredate<=add_months(sysdate,-25*12); 所有员工名字前加上 Dear ,并且名字首字母大写 select 'Dear ' || initcap(ename) from emp; 找出姓名为 5 个字母的员工 select * from emp where length(ename)=5; 找出姓名中不带 R 这个字母的员工 select * from emp where ename not like '%R%'; 显示所有员工的姓名的第一个字 select substr(ename,0,1) from emp; 显示所有员工，按名字降序排列，若相同，则按工资排序 假设一个月为 30 天，找出所有员工的日薪，不计小数 找到 2 月份受雇的员工 select * from emp where to_char(hiredate,'fmmm')='2'; 列出员工加入公司的天数(四舍五入） 分别用 case 和 decode 函数列出员工所在的部门，deptno=10 显示'部门 10', deptno=20 显示'部门 20' deptno=30 显示'部门 30' deptno=40 显示'部门 40' 否则为'其他部门' Oracle 教程 -19- 第五章 分组函数 5.1、COUNT 如果数据库表的没有数据，count(*)返回的不是 null，而是 0 5.2、Avg，max，min，sum 5.3、分组函数与空值 分组函数省略列中的空值 select avg(comm) from emp; select sum(comm) from emp; 可使用 NVL()函数强制分组函数处理空值 select avg(nvl(comm, 0)) from emp; 5.4、GROUP BY 子句 出现在 SELECT 列表中的字段或者出现在 order by 后面的字段，如果不是包含在分组函数 中，那么该字段必须同时在 GROUP BY 子句中出现。 包含在 GROUP BY 子句中的字段则不必须出现在 SELECT 列表中。 可使用 where 字句限定查询条件 可使用 Order by 子句指定排序方式 如果没有 GROUP BY 子句，SELECT 列表中不允许出现字 段（单行函数）与分组函数混用的情况。 select empno, sal from emp; //合法 select avg(sal) from emp; //合法 select empno, initcap(ename), avg(sal) from emp; //非法 不允许在 WHERE 子句中使用分组函数。 select deptno, avg(sal) from emp where avg(sal) > 2000; group by deptno; 5.5、HAVING 子句 select deptno, job, avg(sal) from emp Oracle 教程 -20- where hiredate >= to_date('1981-05-01','yyyy-mm-dd') group by deptno,job having avg(sal) > 1200 order by deptno,job; 5.6、分组函数嵌套 select max(avg(sal)) from emp group by deptno; 5.7、练习 分组统计各部门下工资>500 的员工的平均工资、 Select avg(sal) from emp where sal>500 group by deptno ; 统计各部门下平均工资大于 500 的部门 select deptno,avg(sal) from emp group by deptno having avg(sal)>500 ; 算出部门 30 中得到最多奖金的员工奖金 Select max(comm) from emp where deptno = 30 ; 算出部门 30 中得到最多奖金的员工姓名 select ename from emp where comm = (select max(comm) from emp where deptno=30); 算出每个职位的员工数和最低工资 Select job,min(sal),count(*) from emp group by job; Oracle 教程 -21- 算出每个部门,，每个职位的平均工资和平均奖金(平均值包括没有奖金)，如果平均奖金大于 300，显示“奖 金不错”，如果平均奖金 100 到 300，显示“奖金一般”，如果平均奖金小于 100，显示“基本没有奖金”， 按部门编号降序，平均工资降序排列 Select avg(sal),avg(nvl(comm.,0)) case when avg(nvl(comm.,0))>300 then „奖金不错‟ when avg(nvl(comm.,0))<100 and avg(nvl(comm,0))>300 then „奖金不错‟ end 奖金状况 from emp group by job order by job desc,avg(sal) desc; 列出员工表中每个部门的员工数，和部门 no Select count(*),deptno from emp group by deptno; 得到工资大于自己部门平均工资的员工信息 select * from emp e1,(select deptno,avg(sal) as avgsal from emp group by deptno) e2 where e1.deptno=e2.deptno and e1.sal > e2.avgsal; 分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资 select deptno,job,avg(nvl(comm,0)),sum(sal) from emp group by deptno,job; 第六章 多表查询 6.1、笛卡尔集(Cross Join) Select * from emp,dept; 6.2、等值连接(Equijoin)(Natural join..on) select empno, ename, sal, emp.deptno, dname from emp, dept where emp.deptno = dept.deptno; 6.3、非等值连接(Non-Equijoin) select ename,empno,grade from emp,salgrade where sal between losal and hisal; Oracle 教程 -22- 6.4、自连接(Self join) select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.mgr = m.empno; select e.empno,e.ename,m.empno,m.ename from emp e,emp m where m.mgr = e.empno; 6.5、左外联接（Left Outer Join ） select s.sid,s.sname,s1.sid,s1.sname from student s,student1 s1 where s.sid=s1.sid(+); select empno,ename,dname from emp left outer join dept on emp.deptno = dept.deptno; 6.6、右外联接（Right Outer Join） select s.sid,s.sname,s1.sid,s1.sname from student s,student1 s1 where s.sid(+)=s1.sid; select empno,ename,dname from emp right outer join dept on emp.deptno = dept.deptno; 6.7、满外联接（Full Outer Join） select empno,ename,dname from emp full outer join dept on emp.deptno = dept.deptno; 1 2 2 4 3 6 4 5 内连接 2 2 4 4 Oracle 教程