数据库培训教程PPT教案

1、数据库培训教程数据库培训教程第1页/共80页随着计算机技术、通信技术和网络技术的发展，人类社会已经进入了信息化时代。信息资源已经成为最重要和宝贵的资源之一，确保信息资源的存储，以及其有效性就变得非常重要，而保存信息的核心就是数据库技术。对于数据库技术，当前应用最为广泛的是关系型数据库，而在关系型数据库中，Oracle公司推出的Oracle数据库是其中佼佼者。本书所基于的数据库为Oracle 11g 。第2页/共80页关系数据库的逻辑模型关系数据库的设计规范理解什么是网格技术了解Oracle的应用结构了解Oracle的体系结构第3页/共80页 关系数据库具有坚实的理论基础，这一理论有助于关系数据

2、库的设计和用户对数据库信息需求的有效处理。它涉及的内容有关模式的基本知识、关系数据库的标准语言SQL，以及关系数据理论，在本节中将对这些做简要的介绍。第4页/共80页 数据库系统是指一个计算机存储记录的系统，它需要特定的软件和一系列硬件支持；并且利用数据库系统能够存储大量的数据记录，支持用户进行检索和更新所需的信息。数据库系统通常在企业应用或科学研究中用于对大量数据进行存储和分析，从而为实际应用提供帮助信息。第5页/共80页在关系数据库的设计阶段，需要为它建立逻辑模型。关系数据库的逻辑模型可以通过实体和关系组成的图来表示，这种图表称为“E-R图”，使用E-R图表示的逻辑模型被称为“ER模型”。

3、一个典型的ER模型由如下三部分组成：实体、联系和属性。第6页/共80页 在关系数据库中，为了保证构造的表（关系）既能准确地反应现实世界，又有利于应用和具体的操作，还需要对构造的表进行规范化，常用的规范化方法就是对关系应用不同的设计范式。在关系数据库中，在构造数据库时必须遵循一定的规则，这种规则就是范式。 第7页/共80页 Oracle数据库是Oracle公司出品的十分优秀的DBMS，当前Oracle DBMS以及相关的产品几乎在全世界各个工业领域中都有应用。无论是大型企业中的数据仓库应用，还是中小型的联机事务处理业务，都可以找到成功使用Oracle数据库系统的典范。到目前为止，11g是Orac

4、le数据库的最新版本，它是在10g的基础上对企业级网格计算进行了扩展，提供了众多特性支持企业网格计算。第8页/共80页 超级计算机作为复杂科学计算领域的主宰，以其强大的处理能力著称。但以超级计算机为中心的计算模式存在明显的不足，由于它的造价极高，通常只有一些国家级的部门，如航天、气象等部门才有能力配置。而随着人们日常工作遇到的商业计算越来越复杂，人们越来越需要数据处理能力更强大的计算机。于是，人们开始寻找一种造价低廉而数据处理能力超强的计算模式，最终找到了答案网格计算Grid Computing。第9页/共80页 Oracle 10g/11g中的g代表网格计算，Oracle数据库作为第一个为企

5、业级网格计算而设计的数据库，为管理信息和应用提供了最灵活的、成本最低的方式。例如，通过Oracle网格计算，可以在几个互联的数据库服务器网格上运行不同的应用。当应用需求增加时，数据库管理员能够自动为应用提供更多的服务器支持。网格计算使用最高端的负载管理机制，使得应用能够共享多个服务器上的资源，从而提高数据处理能力，减少对硬件资源的需求，节省企业成本。第10页/共80页 在安装、部署Oracle 11g数据库时，需要根据硬件平台和操作系统的不同采取不同的结构，下面介绍几种常用的应用结构。第11页/共80页 在安装、部署Oracle 11g数据库时，需要根据硬件平台和操作系统的不同采取不同的结构，

6、下面介绍几种常用的应用结构。第12页/共80页 在客户/服务器结构中，数据库服务器的管理和应用分布在两台计算机上，客户机上安装应用程序和连接工具，通过Oracle专用的网络协议SQL *Net建立和服务器的连接，发出数据请求。服务器上运行数据库，通过网络协议接收连接请求，将执行结果回送客户机。第13页/共80页 分布式结构是客户机/服务器结构的一种特殊类型。在这种结构中，分布式数据库系统在逻辑上是整体，但在物理上分布在不同的计算机网络里，通过连接网络连接在一起。网络中的每个节点可以独立处理本地数据库服务器中的数据，执行局部应用，同是也可存取处理多个异地数据库服务器中的数据，执行全局应用。第14

7、页/共80页 内存结构： 系统全局区 (SGA)：数据库缓冲区高速缓存、重做缓冲区和各种池 程序全局区 (PGA) 进程结构： 用户进程和服务器进程 后台进程：SMON、PMON、DBWn、CKPT、LGWR、ARCn 等等 存储结构： 逻辑：数据库、方案、表空间、段、区和 Oracle 块 物理：数据、参数、重做和 OS 块使用的文件第15页/共80页Oracle 服务器： 是一个数据库管理系统，它提供开放、全面、集成的方法进行信息管理 由 Oracle 实例和 Oracle 数据库组成第16页/共80页数据库结构数据库结构 - 内存内存 - 进程进程 - 存储存储 存储结构存储结构内存结构

8、内存结构进程结构进程结构实例 系统全局区系统全局区 (SGA)后台进程后台进程数据库文件第17页/共80页数据库结构数据库结构 内存内存 进程进程 存储存储 Java 池数据库缓冲区高速缓存重做日志缓冲区共享池大型池SGAStreams 池服务器进程 1PGA服务器进程 2PGA后台进程PGA第18页/共80页数据库结构数据库结构 内存内存 进程进程 存储存储 实例SGA后台进程后台进程服务器进程PGA用户进程用户进程 用户进程：在数据库用户请求连接到 Oracle 服务器时启动 服务器进程：可以连接到 Oracle 实例，它在用户建立会话时启动 后台进程：在启动 Oracle 实例时启动第1

9、9页/共80页系统监视器 (SMON)数据库写进程 (DBWn)日志写进程 (LGWR)进程监视器 (PMON) 归档进程 (ARCn)SGAJava 池共享池大型池Streams 池数据库 缓冲区高速缓存重做日志缓冲区检查点 (CKPT)控制文件控制文件数据文件数据文件重做日志重做日志 文件文件归档日志归档日志文件文件第20页/共80页SQL诞生于70年代的IBM某实验室。 SQL是一种非过程语言，它描述了如何对数据进行检索、插入、删除，但它并不说明如何进行这样的操作。SQL最新标准为ANSI-92。尽管该标准要求所有的数据库设计者应遵守这一标准，然而所有的数据库系统、所用的SQL均与ANS

10、I-92存在一定的差异。本文基于ORACLE数据库介绍SQL。本文重点介绍数据库的开发部分而不是管理部分。第21页/共80页了解数据库的常用对象。熟练掌握标准SQL的使用。熟练掌握ORACLE常用标量函数的使用。熟悉SQL的常用优化方法。了解ORACLE的存储过程及触发器的开发。 第22页/共80页表空间与数据文件用户与权限表索引、PK、FK视图序列内部函数自定义函数存储过程触发器第23页/共80页常用数据类型表相关DDLSelectinsertUpdateDelete聚集函数常用标量函数谓词第24页/共80页字符类数字类日期类二进制类第25页/共80页CREATE TABLECREATE T

11、ABLE table_name(column_name1 data_type,column_name2 data_type,.)DROP TABLE DROP TABLE table_name ALTER TABLEALTER TABLE table_name ADD column_name datatypeALTER TABLE table_name DROP column_name ALTER TABLE table_name MODIFY column_name 第26页/共80页SELECT DISTINCTselect_list INTO new_table FROM table_s

12、ource WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC 第27页/共80页INSERT INTO table_nameVALUES (value1, value2,.)INSERT INTO table_name (column1, column2,.)VALUES (value1, value2,.) 第28页/共80页UPDATE table_name SET column_name = new_value,W

13、HERE column_name = some_value第29页/共80页DELETE FROM table_name WHERE column_name = some_value 第30页/共80页INBETWEENLIKEEXISTS第31页/共80页COUNTSUMMAXMINAVGGROUP BYHAVING第32页/共80页数学函数字符类函数SUBSTR、REPLACE、TRIM、LOWER、UPPER日期函数 SYSDATE转换函数TO_CHAR、TO_DATE、TO_NUMBER其他NVL、DECODE、CASE WHEN第33页/共80页子查询表关联外连接第34页/共80页-

14、订单明细create table orderdetail(orderid int not null,productid int not null,quantity int not null);第35页/共80页-产品数据insert into productinfo values(1,ThinkPadR32,IBM,12000);insert into productinfo values(2,ThinkPadR40,IBM,15000);insert into productinfo values(3,Nokia3210,Nokia,1000);insert into productinfo

15、 values(4,Nokia4110,Nokia,2000);-订单数据insert into orderinfo values(1,order1,hoperun,to_date(20070101,yyyymmdd);insert into orderinfo values(2,order2,microsoft,to_date(20060101,yyyymmdd);insert into orderinfo values(3,order3,hoperun,to_date(20070105,yyyymmdd);-订单明细数据insert into orderdetail values(1,1,

16、10);insert into orderdetail values(1,2,5);insert into orderdetail values(1,3,2);insert into orderdetail values(2,1,30);insert into orderdetail values(2,2,50);insert into orderdetail values(2,3,60);insert into orderdetail values(2,4,100);insert into orderdetail values(3,1,2);insert into orderdetail v

17、alues(3,2,3);第36页/共80页（1）查询采购ThinkPadR32产品的订单明细：select * from orderdetail where productid = (select productid from productinfo where productname=ThinkPadR32)第37页/共80页（2）查询采购IBM公司产品的订单明细：select * from orderdetail where productid in (select productid from productinfo where companyname=IBM)第38页/共80页（3）

18、查询hoperun客户采购的ibm公司产品列表：select * from orderdetail where orderid in(select orderid from orderinfo where customer=hoperun) and productid in (select productid from productinfo where companyname=IBM)order by orderid第39页/共80页（4）查询hoperun客户采购的ibm公司价格在13000以上的所有产品的总件数：select sum(quantity) from orderdetail

19、where orderid in(select orderid from orderinfo where customer=hoperun) and productid in (select productid from productinfo where companyname=IBM and price13000)第40页/共80页查询2007年定购Nokia产品的客户：select distinct customer from orderinfo where orderdate=to_date(20070101,yyyymmdd)and orderid in(select orderid

20、 from orderdetail where productid in(select productid from productinfo where companyname=Nokia)第41页/共80页相关子查询和独立子查询的概念。2.3.2（2）中的，查询采购IBM公司产品的订单明细：select * from orderdetail where productid in (select productid from productinfo where companyname=IBM)用exists相关子查询描述为：select * from orderdetail awhere ex

21、ists(select * from productinfo b where ductid=ductid and panyname=IBM)第42页/共80页2.3.3中的，查询2007年定购Nokia产品的客户：select distinct customer from orderinfo where orderdate=to_date(20070101,yyyymmdd)and orderid in(select orderid from orderdetail where productid in(select productid from productinfo w

22、here companyname=Nokia)用exists相关子查询描述为：select distinct customer from orderinfo awhere a.orderdate=to_date(20070101,yyyymmdd)and exists(select * from orderdetail b where a.orderid=b.orderid and exists(select * from productinfo c where ductid=ductid and panyname=Nokia)第43页/共80页笛卡尔叉积等值连接不等值连接

23、第44页/共80页select * from orderinfo,orderdetail第45页/共80页（1）按照订单号、采购产品名称、数量显示订单明细信息：select a.orderid,ductname,a.quantity from orderdetail a,productinfo bwhere ductid=ductid第46页/共80页（2）按照客户名称、订单名称、采购产品名称、数量显示订单明细信息：select c.customer,c.ordername,ductname,a.quantity from orderdetail a,p

24、roductinfo b,orderinfo cwhere ductid=ductid and a.orderid=c.orderid第47页/共80页极少使用，不作介绍。第48页/共80页插入一条新产品数据：insert into productinfo values(5,ThinkPadT41,IBM,20000);查询已订货产品的订货情况select ductname,b.orderid,b.quantityfrom productinfo a,orderdetail bwhere ductid=ductidorder by d

25、uctname,b.orderid查询所有产品的订货情况select ductname,b.orderid,b.quantityfrom productinfo a left join orderdetail bon ductid=ductidorder by ductname,b.orderid第49页/共80页左连接select ductname,b.orderid,b.quantityfrom productinfo a left join orderdetail bon ductid=ductidorder by a

26、.productname,b.orderid右连接select ductname,b.orderid,b.quantityfrom orderdetail b right join productinfo aon ductid=ductidorder by ductname,b.orderid第50页/共80页统计所有产品的订货数量：select ductname,nvl(sum(b.quantity),0)from productinfo a left join orderdetail bon ductid=ductidg

27、roup by ductnameorder by ductname第51页/共80页基于数据库的应用系统性能的优化主要从两个方面来考虑，即DBA级和SQL级。DBA级的优化主要包括：数据库内存及缓冲池的合理配置、数据表空间（数据段）和索引表空间（数据段）的分离、裸设备/文件系统的合理使用以及巨型表的分区等等。本文主要介绍较常用的、可操作性较强的SQL级的优化方法。第52页/共80页全表扫描：当数据库服务为执行某一个SQL语句需要对表中的每一个记录进行检查时就会发生全表扫描。它通常在执行SELECT语句时发生，但有时也会在更新和删除记录时发生。全表扫描通常是因为在WHERE子

28、句中使用了索引中没有的字段。一般情况下，应避免全表扫描。第53页/共80页全表扫描：当数据库服务为执行某一个SQL语句需要对表中的每一个记录进行检查时就会发生全表扫描。它通常在执行SELECT语句时发生，但有时也会在更新和删除记录时发生。全表扫描通常是因为在WHERE子句中使用了索引中没有的字段。一般情况下，应避免全表扫描。第54页/共80页创建索引：避免全表扫描的首要手段就是创建索引。在下列情况下，需要建立索引：最大的限制条件返回表的数据库小于表总数据量的10%。最大的限制条件在SQL语句中是经常使用的。列经常被ORDER BY或GROUP BY子句所引用。在SQL中应尽量避免在索引列上使用

29、函数。如ORDER BY TO_NUMBER(USERID)第55页/共80页版本较老得ORACLE数据库中WHERE子句的布局： 查询优化器对WHERE子句中的条件是从后向前读。 尽量把最大条件的限制条件语句放在最后。第56页/共80页OR、IN、EXISTS：尽量少用OR，而用IN。尽量用EXISTS代替IN，因为IN不使用索引，而EXISTS使用索引进行优化。第57页/共80页存储过程可简单地理解为一个处理复杂业务逻辑的一组SQL代码块。存储过程的优势主要有：存储过程业务逻辑集中，方便维护。存储过程是预编译的，可提高性能。存储过程可大量减少应用程序与数据库服务器的通信，减少网络负荷。在多

30、层架构体系出现之前，很多应用系统采用存储过程来封装业务逻辑。第58页/共80页根据订单号删除订单及其明细信息。CREATE OR REPLACE PROCEDURE del_order(v_order_id in varchar2)asbegin -删除子表 delete from orderdetail where orderid=v_order_id; -删除主表 delete from orderinfo where orderid=v_order_id; commit;end;/执行该存储过程：execute del_order(1);第59页/共80页触发器机制从本质上来说是一种特殊

31、类型的存贮过程，它可以在下列的三种情况之一发生时自动运行。更新插入删除。触发器机制所进行的活动是被默认为事务处理的一部分进行。第60页/共80页对插入或修改userinfo.useraddr字段的null值自动转换成空字符串。CREATE OR REPLACE TRIGGER trg_userinfo BEFORE INSERT OR UPDATE ON userinfo FOR EACH ROWBEGIN IF (:new.useraddr IS NULL) THEN :new.useraddr:=; END IF;END;第61页/共80页本部分内容前提要求熟练掌握了基本的SQL语句。本部

32、分将涉及到PL/SQL语句块结构、变量和类型、错误处理、控制结构、游标等内容。 第62页/共80页为什么使用PL/SQL 一）PL/SQL是用于从各种环境中访问Oracle数据库的一 种编程语言。它与数据库服务器集成在一起，因此，PL/SQL代码可以得到快速有效的处理。二）PL/SQL将SQL语言（4GL）强大性和灵活性与3GL的过程化结构融为一体。三）网络流量减少，应用程序执行得更快。第63页/共80页PL/SQL语句块 PL/SQL程序的基本单元是语句块，所有的语句块包含三个基本的部分：声明部分、执行部分和异常处理部分。只有执行部分是必须的，其他二个部分都是可选的。有二种类型的语句块：匿名

33、的和命名的。 DECLAREBEGINEXCEPTIONEND；第64页/共80页变量和类型在PL/SQL和数据库之间是通过变量传送消息的。变量在语句块的声明部分声明的。变量声明我们通过PL/SQL语句块中的变量与数据库进行通信，来自数据库的信息可以被赋值给变量，而变量中的内容也可以被插入到数据库中。变量在语句块的声明部分被声明。声明变量如果一个变量没有初始化，它会被缺省地被赋值为Null，如果指明是Not Null，那么该变量就必须初始化。如果在变量声明中使用了CONSTANT，那么该变量必须要初始化，在声明过程中也可以用关键字DEFAULT替换 ：=在声明部分，每一行只能声明一个变量。下面

34、的声明是错误的DECLARE v_Firstname ,v_Lastname VARCHAR2(20);第65页/共80页PL/SQL类型PL/SQL类型有：标量类型、复合类型、LOB类型、引用类型、对象类型使用%TYPE在许多情况下，PL/SQL变量用来处理存储在数据库表中的数据。在这种情况下，变量应该拥有与表列相同的类型。DECLAREv_Firstname students.first_name%TYPE;用户定义子类型子类型是基于现有类型的PL/SQL类型。语法：SUBTYPE new_type IS original_type;如：DECLARE SUBTYPE t_loop IS

35、NUMBER(5); v_Count t_loop; 第66页/共80页什么是异常声明异常 预定义异常 用户自定义异常抛出异常处理异常创建错误消息RAISE_APPLICATION_ERROR(error_number,error_messages,keep_errors)第67页/共80页异常传播 可执行部分产生的异常 声明部分出现的异常 异常部分产生的异常第68页/共80页PL/SQL控制结构 IF-THEN-ELSE CASE 带标号的CASE语句 循环语句a）简单循环 LOOPEND LOOPb）WHILE循环 WHILELOOPEND LOOPc）数字式FOR循环 FORLOOPEND LOOP第69页/共80页什么是游标？ 游标用来处理从数据库中检索的多行记录（使用SELECT语句）。利用游标，程序可以