最新更新 热门文章
当前位置：首页>免费文档>文库资料>综合文库>第11章 数据库基础

第11章 数据库基础

更新时间：2024-05-21 11:57:01 阅读量： 综合文库 文档下载

说明：文章内容仅供预览，部分内容可能不全。下载后的文档，内容与下面显示的完全一致。下载之前请确认下面内容是否您想要的，是否完整无缺。

第11章 数据库应用基础

【学习目标】

了解数据库系统的作用。 掌握数据库系统的基本构成。 了解数据库系统开发的基本过程。 了解E-R图及其作用。

了解ORACLE数据库管理系统的基本结构和基本工具。 掌握关系数据库系统的基本概念。

掌握利用SQL语言建立基本表、索引和视图。

掌握利用SQL语言对基本表的操作和对视图的应用。

【知识架构】

? ? ? ? ? ? ? ?

现实世界的应用需求 概念模型 E-R图 硬件 数据库 数据库系统 软件 关系数据模型 基本表 完整性约束 数据库管理系统ORACLE 关系数据操作 SQL 用户

76

11.1 数据库基本知识

11.1.1 数据库系统应用

我们已经进入了一个信息时代，从我们个人的生活、学习、工作到企业的运营、社会的发展无不打上了信息的烙印。

我们外出买票可以使用交通卡、可以网上订票、可以电话订票; 在学校就餐、购物、洗衣可以使用校园卡；买东西可以到淘宝、当当进行网上购物；选课、借书、信息检索、投资股票等也无一不是通过信息系统来完成。

企业从最早的工资系统到财务系统、办公自动化系统，再到目前的ERP（企业资源规划）系统、CRM（客户关系管理）系统、SCM（供应链管理）系统、BI（商务智能）系统，信息化已经涵盖了企业经营管理的各个角落。

银行领域在信息化发展进程中一直处于领先地位，目前，商业银行的信息化系统总体上可以分为核心业务系统和外围系统两部分。银行核心业务系统就是面向银行各类业务的交易处理系统，通过交易处理，驱动会计核算和支付清算，最终达到集成化处理后台业务的目标。各个银行核心业务系统包含的具体业务并不完全一致，但大体上一般包括存款系统、贷款系统、国际业务系统、支付清算系统、资金交易系统和衍生业务系统等。外围系统又可以分为三部分：信息管理类系统、渠道业务系统和其他系统。信息管理类系统与决策、管理和业务相关，以提高效率和规避风险为目的，主要包括办公自动化系统、信贷审批系统、银行财务系统（非客户）、档案系统等；渠道业务系统是商业银行面向市场和客户的窗口，也是对外服务的渠道，包括柜面服务、电话银行、手机银行、网上银行、企业银行和自助服务终端（ATM和POS）、银联交易、区域性通存通兑和同城跨行通存通兑等。其他系统主要包括现代化支付系统、同城交换系统、第三方存管清算系统等。

信息系统在各个行业得到了极大地普及，在我们的生活中什么地方还没有信息化？这恐怕越来越难以回答。

信息系统是支持数据的输入、存储、访问、处理、结果展现的人机交互式计算机系统。目前，数据库技术是信息系统的基础，信息系统也就是数据库系统。

11.1.2 数据库系统概述

在数据库系统中，数据有哪些类型？存放在哪里？如何存储？ 由谁管理？以什么方式对数据进行存取？这些问题都是数据库系统需要解决的基本问题。

1．数据、信息与数据处理

数据是人们用于描述事物特征的物理符号，它包括三个方面：数据形式、数据内容和数据语义。数据形式即数据的“类型”，包括数字、文字、图形、图像、声音等多种类型；数据内容即数据的“值”；数据的语义即数据的“含义”，是数据所反映的客观事实，没有语义，数据也就失去了意义，只能成为“垃圾”。例如：如果没有明确以下字符形式的数据（王伟，男，金融）表示的含义，则它就不能称为真正的数据，因为不知道它所描述的客观事实。相同形式的数据可以表达不同的含义，例如上述数据可以表示“王伟是一名金

77

融专业的男学生”，也可以表示“王伟是一名金融专业的男教师”等等。

信息是一种被加工为特定形式的数据，这种数据形式对接收者来说是有意义的，而且对当前和将来的决策具有明显的或实际的价值。可以说数据是信息的具体表现形式，信息则是数据的语义及其有意义的体现。数据和信息是不可分割的，在一些不是很严格的场合，对信息和数据没有严格的区分，有时甚至当作同义词来使用，如：信息处理与数据处理、信息采集与数据采集等等。

数据处理是对各种数据进行收集、整理、存储、加工（例如：编码、计算、排序、筛选、分类、汇总、检索）和传播的一系列活动的总合。数据处理技术经历了人工处理阶段、文件系统处理阶段，于20世纪60年代末以以下三件大事为标志开始进入数据库处理阶段。

1968年美国IBM公司推出第一个基于层次模型的商用数据库管理系统IMS（Information Management System）。

1969年美国数据系统语言协会CODASYL（Conference On Data System Language）下属的数据库任务组DBTG（Data Base Task Group）发布了DBTG报告，基于网状模型确定并建立了数据库系统的许多概念、方法和技术。

1970年美国IBM公司San Jose研究室的研究员E.F.Codd发表论文，首次提出了数据库系统的关系模型，奠定了关系数据库的理论基础。

我们现在实际使用的数据库系统都是基于关系模型的数据库系统。四十多年来，数据库技术得到了快速发展，目前，面向对象的数据库系统、分布式数据库系统、并行数据库系统、多媒体数据库系统、数据仓库与数据挖掘已经成为数据库技术的重要研究领域。

2．数据库与数据库管理系统

数据库简称DB，是存放数据的仓库。只是这个仓库存在计算机的存储设备上。数据库中往往存储大量的数据，满足整个组织各种用户的需要。为了高效的存取这些数据，正如人们在现实生活中对物品分门别类的进行组织存放，数据库中的数据往往按一定的格式来存放，具有较小的冗余度。严格的讲，数据库是长期储存在计算机内、有组织、可共享的大量数据的集合。数据库中的数据按一定的数据模型组织、描述和储存。

数据库管理系统（DataBase Management System）简称DBMS，是专门负责对数据库中的数据进行统一管理和控制的系统软件。数据库管理系统将应用程序和数据库中数据结构的变化进行适当的隔离，只要用户的数据需求没有改变，数据库中数据结构的任何变化都不会影响到应用程序。数据库管理系统提供相应的语言和机制实现对数据库的集中管理和控制，是数据库名副其实的管家。

3．数据库系统

数据库系统是使用数据库的计算机系统，一般由计算机硬件设备、数据库、软件、数据库用户几部分组成。数据库系统的构成如图11-1所示。

（1）硬件设备和数据库

数据库和所有的软件都存放在计算机的存储设备上，并且需要在运行效率上满足多个用户的需要，因此数据库系统对硬件资源的要求较高：包括要求计算机具有足够大的内存，用于存放操作系统、DBMS的核心模块、数据缓冲区及应用程序；有足够大和安全性好的磁盘或磁盘阵列等存储设备存放数据及数据备份和软件；有运算能力强大的中央处理器和通信能力强大的I/O通道。

78

（2）软件

数据库系统的软件主要包括：操作系统作为基础软件支持DBMS运行；数据库管理系统完成数据库的管理和控制；高级语言及其编译系统用于开发应用程序；应用程序满足特定业务需求。其中，数据库管理系统是数据库系统的核心。

（3）数据库用户

数据库用户通常包括以下几类人员：

数据库管理员DBA：负责整个数据库的规划和设计，并利用数据库管理系统对数据库进行全面管理、维护和控制；

系统分析员：主要负责应用系统的分析设计，配合DBA，并指导应用程序员的工作； 应用程序员：按照系统分析员的设计要求，利用某种编程语言和开发工具编写和调试应用程序；

终端用户：通过使用应用程序完成特定的业务。

图11-1 数据库系统构成

4．数据模型

在现实世界有许多模型，这些模型都是对现实世界中某种对象特征的模拟和抽象，比如飞机模型、汽车模型。数据模型也是一种模型，是对数据建模，而数据又是对客观世界的描述，因此，我们可以说数据模型是对我们关注的某个特定现实世界的模拟。例如银行存款数据模型就是对现实世界中某银行的存款相关业务进行的抽象模拟，描述这些业务涉及的数据、数据之间的联系、数据的组织结构、存取方式等。

建立一个飞机模型、汽车模型我们往往需要先绘制图纸，再准备或购买材料根据图纸制作模型。数据模型所要模拟的客观世界更加复杂，因此建立数据模型也需要一个绘制图纸的中间过程，即数据模型有两个层次：第一个层次是概念模型，从用户的角度认识并抽象现实世界中的人、物、活动、概念等，并用相应的术语和工具进行描述；第二个层次是结构数据模型（通常简称数据模型），从计算机系统的角度对数据建模。

（1）概念模型

概念模型是对特定现实世界进行分析、抽象、归纳的结果。在概念模型中主要使用实体和联系两大类术语来描述现实世界，并使用E-R图来表示。

①实体（Entity）

79

实体是现实世界中客观存在并可相互区别的“物体”或“事件”，例如，每个在银行存款的客户，每个存款账户、每笔存取款或转账的明细记录都是一个实体。不同的实体可以通过某些方面的不同特征（即属性）相互区别。

②属性（Attribute）

实体所具有的某一方面的特征称为属性，实体往往通过一组属性来描述。例如：银行存款客户实体可能具有身份证号、姓名、性别、出生日期、职业、家庭地址等属性。

③域（Domain）

每个属性都有一个合理的取值范围，这个取值范围就称为域，即域是具有一组相同数据类型的值的集合。例如：性别属性的域为（“男”，“女”）或代表男女的（“M”,“F”）等。

④候选码(Candidate Key)和主码（Primary Key）

描述实体的属性有多个，但它们的重要性不同。候选码、主码是对属性重要性的一种描述。

取值能够唯一标识一个实体的最简属性组称为实体的候选码。候选码既具有标识一个实体的唯一性，也具有表达形式上的最简性（或最小性）。例如：（身份证号）是银行存款客户实体的候选码，如果假定同一性别的客户不会重名，则（姓名，性别）也是银行存款客户实体的候选码。

一个实体可能存在多个候选码，根据实际需要，选定其中一个候选码作为唯一标识一个实体的主码。

⑤实体集（Entity Set）

具有相同属性的实体构成的集合称为实体集。例如，全体银行存款客户构成一个实体集。

⑥实体型（Entity Type）

对实体集中所有实体共同特征的描述称为实体型，用实体名及其属性名集合来表示。实体型实际上就是实体集的类型，是对实体集的描述。例如银行存款客户实体型表示为：银行存款客户（身份证号，姓名，性别，出生日期，职业，家庭地址）。

⑦联系

在现实世界中，事物与事物之间以及事物内部都是有联系的，而这些联系反映在信息世界中即表现为实体集之间的联系和实体集内部的联系，最普遍的是两个实体集之间的联系，这种联系从两个实体集元素的数量对应关系方面可以分为一对一、一对多、多对多三种。

如果实体集A中的一个实体至多同实体集B中的一个实体相联系，实体集B中的一个实体也至多同实体集A中的一个实体相联系，则称二者之间存在一对一联系，记为1：1。

如果实体集A中的一个实体同实体集B中的多个实体相联系，而实体集B中的一个实体至多同实体集A中的一个实体相联系，则称二者之间存在一对多联系，记为1：m。

如果实体集A中的一个实体同实体集B中的多个实体相联系，实体集B中的一个实体也同实体集A中的多个实体相联系，则称二者之间存在多对多联系，记为m：n。

另外，从实体集之间的逻辑关系方面，多个实体集之间还可能具有构成和父子关系。

80

⑧E- R（实体-联系）图

E-R图是常用的一种概念模型表示工具，它通过特定的图形符号来表示实体和联系相关的术语。一般用矩形表示实体型，用椭圆形表示属性，用菱形表示联系，在属性名下加下划线表示主码中的属性，用线段连接上述各部分。例如，银行存款业务的概念模型可以表示为图11-2所示的E-R图。

（2）数据模型

数据模型从计算机角度精确地描述了系统的静态特性、动态特性和完整性约束条件，是数据库系统的核心和基础。数据模型通常具有以下三个要素。

图11-2 银行存取款业务概念模型E-R图

①数据结构

数据结构用于描述数据库的组成对象以及对象之间的联系，是数据模型最重要的方面。在数据库系统中，往往根据数据结构的类型来命名数据模型。例如：基于表结构的关系数据模型、基于树结构的层次模型和基于图结构的网状模型。我们目前的数据库系统都支持关系数据模型。

②数据操作

数据操作描述对数据库中的对象允许执行的操作，主要有检索和更新（包括插入、删除、修改）两大类。数据模型必须定义这些操作的确切含义、操作符号、操作规则（如优先级）以及实现操作的语言。

③数据的约束条件

数据的约束条件描述数据及其联系应满足的制约规则，数据模型应该提供定义完整性约束条件的机制。

5.关系数据模型

关系数据模型结构简单，操作方便，而且具有严格的数学理论基础，是目前数据库系统普遍支持的一种数据模型。

（1）关系数据模型的数据结构

关系模型的数据结构就是我们平时常用的二维表，专业术语称为关系。基本概念如图11-3所示。

81

图11-3 关系基本概念

简单地讲，二维表由表名、行和列构成，与此相对应，关系中涉及以下术语： ①元组(Tuple)和基数(Cardinality) 二维表中的行称为元组。概念模型的一个实体转化为关系模型的一个元祖。二维表的行数（或元组的个数）称为关系的基数。

②属性(Attribute)和目(Degree)

二维表的列称为属性，每个属性有一个唯一的属性名，各属性不能重名。关系包含的属性的个数称为关系的目或度。

③域（Domain）

属性的取值范围称为域，即一组具有相同数据类型的值的集合。每个属性对应一个域，不同的属性可以具有相同的域。

④分量（Item）

属性在每个元组中对应的取值称为元组的一个分量。如果一张二维表的每个分量都是不可分的数据项，即只有一个取值，则这张二维表就是一个关系。

⑤侯选码（Candidate Key）和主码（Primary Key） 取值能够唯一代表一个元组的最小属性组称为侯选码，根据实际需要，从侯选码中任选一个做为主码。侯选码和主码同概念模型中的响应概念一致。

⑥关系模式

现实世界中的任何实体都有它所属的类型，对实体的称呼实际上就包含了类型的名称，例如一张桌子、一个学生、一门课程。同样的，二维表或关系也有它所属的类型，在关系模型中，对二维表或关系类型的描述就称为关系模式（即关系模式描述了关系的类型）。

表的类型包括那些要素呢？总体上可以分为两大类，一类是表的结构（即空表），包括表名（关系名）、栏目名（属性名）；另一类是填表说明，说明表中数据应满足的约束条件，包括每列应填写的数据含义和范围（域）、不同列之间取值的约束关系（数据依赖关系）。因此，关系模式可以归纳为包含五个元素的五元组，表示为：R（U，D，DOM，F）

其中，R为关系名，U为属性组，D为属性的取值范围（域），DOM为属性与域的对应关系（说明哪个属性的取值范围是哪个域），F为关系应满足的约束条件。

由于D和DOM根据实际环境很容易确定和理解，所以也可以将关系模式表示为三元组：

82

R（U，F），又因为F的表达比较专业，一般用于关系模式的设计领域，因此一般情况下，也可以简单的将关系模式表示为二元组R（U），即 关系名（属性1，属性2，?，属性n），例如图11—3所示二维表的关系模式为：

存款客户（身份证号，姓名，性别、职业、城市

一个关系实际上就是关系模式在某一时刻的状态或内容，是关系模式的一个取值。 按照一定的规则可以将E-R图表示的概念模型转化为关系模型，基本转换规则如下： ? 实体型转换为一个关系

? 一个一对多联系可以选择与多方实体型转化后的关系合并（在关系中加入1方实

体型的主码）

? 一个多对多联系转换为一个独立的关系

图11-2所示的E-R图可转化为如图11-4所示的关系模型，其中deposit的主码为（account_no,oper_date）。 （2）关系数据模型的数据操作

前面我们已经提到“关系数据模型应用数学方法来处理数据库中的数据”，关系操作和数学操作有紧密关系。在关系数据模型中，所有的关系操作都是数学的集合操作，即操作的对象和结果都是集合，关系看作元组的集合，而不是属性的集合（集合要求元素必须是同类型的，关系的元组肯定类型相同，而属性的类型则不一定）。

关系操作要通过关系操作语言来实现，关系操作语言是非过程化的语言，只需要描述操作要求，而不用描述操作的过程。关系操作语言大体可以分为关系代数（用对关系的代数运算来表达关系操作的要求）、关系演算（用关系谓词来表达关系操作的要求）和SQL（Structured Query Language，实际应用的标准关系操作语言）三大类。

关系模型中常用的关系操作包括查询和更新（具体包括增、删、改操作）两类，其中查询的表达能力尤其重要，关系查询分为传统的集合运算（具体包括并、交、差运算）和专门的关系运算（具体包括选择、投影、连接、除）。

图11-4 银行存取款关系模型

在银行存取款案例中，如果我们要查询所有位于“成都”的客户，则需要在customer表中选择满足cust_city=”成都”的所有行，这种从表中选择部分行构成查询结果的操作称为选择；

如果我们要查询所有客户的身份证号、姓名和电话，则需要在customer表中选择所

83

有行的customer_id、cust_name、cust_phone三列，这种从表中选择所有行的部分列构成查询结果的操作称为投影；

图11-5 选择者、投影与连接操作

如果我们要查询所有账户及其上发生的所有存取款历史信息，则需要将account和deposit表中的信息从水平方向上进行对接形成一个大表，大表中的属性包括两个表的所有属性，大表中的行这样得到：对account表中的每一行，逐行检查deposit表中的每一行，如何他们的账号account_no相同，则从水平方向上连成一行放入查询结果集合。这种将两张表按照某种条件（例如账号相同）从水平方向上拼接成一张大表的操作称为连接，上述查询的连接条件表示为account.account_no=deposit.account_no，这种连接条件中使用“=”比较符的连接称为等值连接；实际上，“=”两边进行比较的往往是相同的属性，这样在等值连接的结果中就会出现重复列，为此，在等值连接中引入了一种称为自然连接（Natural join）的特殊连接操作：将两张表中的同名属性相等作为连接条件，并将等值连接结果中的重复列只保留一份。

选择、投影、连接操作示意图参见图11-5,等值连接和自然连接操作对象和结果实例参见图11-6。

图11-6 等值连接与自然连接

（3）关系数据模型的完整性约束

关系模型的完整性约束是为了保证关系中数据的正确性、完整性和相容性，对关系及其操作做出的一系列约束。其中，最重要也最常用的是以下三类完整性约束：实体完整性约束、参照完整性约束和用户自定义的完整性约束。

①实体完整性约束（Entity Integrity Rule）

84

一个基本关系通常对应现实世界的一个实体集，一个元组对应一个实体。在构成关系的多个属性中，用侯选码的取值作为元组唯一性的标识；我们还知道包含在任何一个侯选码中的属性称为主属性。因此，元组的唯一性最终通过主属性的取值表现出来，这就要求主属性的取值必须是一个具有唯一性的具体的值，而不能是一个“不知道”或“无意义”或“不确定”的值（即空值），这就是实体完整性约束的内容，可以简单的表达为：关系的主属性非空。

②参照完整性约束(Reference Integrity Rule)

在银行存款案例中，我们有客户表customer、存款账户表account、存取款明细表deposit。账户表中customer_id栏是否可以出现一个在客户表customer_id栏中不存在的值？存取款明细表的account_no栏是否可以出现一个在存款账户表的account_no栏中不存在的值？答案显然是否定的，账户表中customer_id栏的填写必须参照客户表customer_id栏的取值，存取款明细表的account_no栏的填写必须参照存款账户表的account_no栏中的值。这中表间的参照关系在关系模型中用外码来表示。

如果关系R的一组属性K不是关系R的侯选码，但需要参照某一关系S的一个侯选码，则称该属性组K是关系R的外码（注意：R和S可以是同一个关系）。

上述客户表customer的主码为customer_id，存款账户表account的主码为account_no、存取款明细表deposit的主码为（account_no，oper_date）。在存款账户表account中，customer_id 就是外码，在存取款明细表deposit中，account_no就是外码。

外码用来体现关系之间的引用关系，参照完整性约束就是对外码取值的一种约束。简单的讲，参照完整性约束的内容就是：外码的取值要么为空值，要么取它所参照的侯选码的一个值。

在存取款明细表deposit中，account_no是外码，但同时也是主属性，它只能取参照的account表中的account_no的值。

③用户自定义的完整性约束

用户定义的完整性约束就是针对某一具体应用的约束条件，反映某一具体应用所涉及的客观事实。例如：在上述隐含存取款案例中，客户性别只能取值为F（表示女）和M（表示男）、存款余额和发生额必须大于0，等等。

在对关系进行更新操作（即插入、删除和修改操作）时，数据库管理系统DBMS会检查数据是否满足上述三类完整性约束条件。

11.2 ORACLE数据库简介

ORACLE数据库系统是美国ORACLE（甲骨文）公司提供的数据库产品，在目前世界数据库产品市场上占有最大份额。本节简要介绍ORACLE数据库产品及安装注意事项、体系结构、常用工具。

11.2.1 ORACLE数据库概述

1．ORACLE数据库产品发展历史

85

ORACLE公司自1977年6月发布第一个关系数据库产品以来，对其数据库产品不断优化扩展，逐步提高产品的稳定性、可用性、扩展性和易用性，加入对多媒体数据、分布式处理、面向对象、集群、网格计算、云计算、XML等技术的支持。ORACLE数据库产品可在100多种硬件平台上运行（所括微机、工作站、小型机、中型机和大型机），支持多种操作系统（例如WINDOWS、LINUX、SOLARIS等）。30多年来，ORACLE数据库产品版本不断提高，由最初的ORACLE1.0，发展到目前的11g。从1992年推出ORACLE7以来，ORACLE8、ORACLE8i、ORACLE9i、ORACLE10g、ORACLE11g版本在全球各个领域都得到广泛应用。目前流行的ORACLE10g数据库产品根据支持的CPU数量、并发用户数、是否支持集群和复杂企业级应用等特性又可分为个人版、标准版、标准版1、和企业版，另外还有专门支持移动数据库应用的轻型版。本章以后内容将基于学习目的使用WINDOWS操作系统下的ORACLE10G企业版。

2．ORACLE10G数据库安装说明

ORACLE10G通过一个图形用户界面的Oracle Universal Installer (OUI) 安装软件进行数据库的安装和卸载，安装方法分为基本安装和高级安装两种，一般在安装过程中根据提示进行选择和操作，具体安装过程参见练习册相应内容。为保证安装过程的顺利完成和安装后的数据库可用，现对以下问题进行说明：

（1）在安装前，首先下载适用于本机操作系统的ORACLE10G安装软件（http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html）并解压，注意，解压后的安装文件所在目录不要包含中文字符(例如不能将安装文件放在桌面上) 。

（2）了解两个重要的ORACLE目录设置: ORACLE_BASE（ORACLE基目录）用来存放oracle数据库服务器的所有文件（包括数据库及备份和DBMS软件）, ORACLE_HOME（ORACLE主目录）存放DBMS相关软件。ORACLE_BASE是ORACLE_HOME的上一级目录。

（3）ORACLE10G是基于网络的软件，安装时要求主机必须具有有效的IP地址，如果不能获取有效IP，可以安装windows本地回环虚拟网卡，并设置一个固定IP。

（4）安装过程中需要设置数据库名和实例名，要求实例名不大于8个字符或数值。 （6）安装过程中设置的管理员口令不能仅包含数字，否则安装后可能不能正常使用数据库。

（7）数据库安装完毕后，相关服务启动方式为“自动”，为提高主机系统的运行效率，建议将ORACLE相关服务设置为“手动”，在需要时再手工启动服务。

11.2.2 ORACLE数据库体系结构

一个安装好的ORACLE服务器（或称主机）包括两部分：ORACLE实例和数据库，参见图11-7。

1. ORACLE实例

ORACLE实例是ORACLE数据库管理系统的基本服务，包括一个内存结构SGA（System Global Area）和一组进程。

86

SGA是为一个ORACLE实例分配的一组共享内存缓冲区，保存着Oracle系统与所有数据库用户的共享信息，是ORACLE实例的主要部分。它又可以分为以下几个部分：

? 共享池：存放数据字典和最近使用过的SQL/PLSQL语句的解析；

? 数据库高速缓存：用于缓存需写入磁盘数据文件或从数据文件中读取的数据； ? 日志高速缓存：用于缓存需写入Redo日志文件的数据。

? 大池：用于rman等大型操作和shared server构件的高速缓存数据。 ? java池：高速缓存最近使用的java对象和应用程序代码。 ? 流池：用于ORACLE的高级队列选件。

ORACLE实例的进程包括前台的服务器进程和后台服务进程。服务器进程处理用户进程的各种请求：解释、编译和执行用户进程发出的SQL语句，并将SQL语句执行结果返回给客户端应用程序。

服务器进程又分为专用服务器进程和共享服务器进程两类，每个专用服务器进程只能服务于一个指定的用户进程，而在共享服务器配置下，每个共享服务器进程则能服务于多个用户进程。

ORACLE常用的后台进程主要包括数据库写入进程DBWn (Database Writer)、日志写入进程LGWR (Log Writer)、系统监视进程SMON (System Monitor)、进程监视进程PMON (Process Monitor)、检查点进程CKPT (Checkpoint)、日志存档进程ARCn(Archiver)。

2. ORACLE数据库

ORACLE数据库存放在磁盘上，用户通过ORACLE 实例访问数据库，一个Oracle 实例只能打开一个数据库。

ORACLE数据库从计算机和用户角度分别具有相应的物理结构和逻辑结构。

图11-7 ORACLE体系结构

87

（1）ORACLE数据库物理结构

ORACLE数据库物理结构主要包括数据文件、控制文件、重做日志文件。 ? 数据文件

一个ORACLE数据库包含若干数据文件，数据文件存储数据库数据，包括表、索引等等。一个数据文件只允许分配给一个数据库，数据文件可设置为自动扩展。

? 重做日志文件

重做日志文件记录了数据库中数据的变化。一般一个数据库都会有两到三组重做日志文件。

? 控制文件

控制文件是一个小的二进制文件，记录物理数据库的当前状态，控制文件中包括ORACLE数据库名、数据文件名及位置、重做日志文件名及位置、数据库的建立时间等等。一般一个数据库有若干个控制文件镜像。数据库在打开的时候会读取控制文件中的信息，当数据库的物理结构发生改变时，比如增加一个数据文件、一组重做日志等等，ORACLE实例都会自动修改控制文件。

另外，ORACLE数据库还包括归档日志文件（重做日志文件的自动备份）、初始化参数文件（包含数据库启动时的配置信息）和口令文件（规定数据库特权用户的口令和用户数）。

（2）ORACLE数据库逻辑结构

ORACLE数据库逻辑结构是从用户角度所看到的数据库结果，主要包括表空间和模式对象。

? 表空间

表空间是ORACLE数据库的最大逻辑划分，一个数据库包括多个表空间，在oracle10g中，在创建数据库的时候会自动创建SYSTEM、USERS、SYSAUX等多个表空间。数据库管理员也可以根据需要创建其它表空间。每个用户都有对应的隐含表空间。

? 模式对象

模式对象指用户存放在数据库中的对象，例如基本表、视图、同义词等等。模式对象存放在相应的表空间中。

表空间中的对象通过段、区、块结构实现磁盘空间的分配和数据的读写，从而与ORACLE物理结构建立联系和转换。

11.2.3 ORACLE数据库常用工具SQL*PLUS

SQL*PLUS是ORACLE最常用的交互式管理和维护工具，它随ORACLE数据库或客户端软件一起安装。通过SQL*PLUS工具，用户可以完成以下重要功能：

? 管理ORACLE数据库，如启动数据库、关闭数据库等。

? 执行sql命令或ORACLE的pl/sql语句完成对数据库对象（例如表）的定义和访

问，进行应用程序的开发、测试，输出查询结果； ? 开发和执行sql脚本文件；

88

? 完成用户管理及权限维护等安全性控制。

在ORACLE10G中，SQL*PLUS工具有三种用户界面：命令行用户界面（对应的工具是sqlplus.exe）、WINDOWS图形用户界面（对应的工具是sqlplusw.exe）、基于WEB的浏览器用户界面（对应的工具称为isqlplus，需要在服务器端启动专门的isqlplus服务，在客户端浏览器中输入HTTP://服务器ip：端口号/isqlplus）。目前比较流行的是后两种，以下介绍基于这两种界面。

1. 启动SQL*PLUS

（1）在WINDOWS开始菜单中选择“运行”，输入sqlplusw；或者在WINDOWS开始菜单中选择“程序”-“Oracle-OraDb10g_home1”-“Application Developmen”-“SQL plus”,出现如图11-8所示的左侧登录界面。

图11-8 SQL*PLUS登录界面

（2）在浏览器地址栏中输入：HTTP://服务器ip或主机名：端口号/isqlplus，端口号默认为5560。例如：

HTTP://192.168.210.228:5560/isqlplus

如果本机就是数据库服务器，也可以输入：HTTP://localhost:5560/isqlplus ，出现如图11-8所示的右侧登录界面。

图11-8所示的左侧登录界面中的“主机字符串”和右侧登录界面中的“连接标识符”在需要使用网络连接方式（需要进行专门的网络配置）连接数据库时使用。

在isqlplus中，如果超过一定时间没有操作，ORACLE服务器会自动断开连接，客户要继续使用isqlplus则需要重新登录。

2. 常用SQL*PLUS命令

在SQL*PLUS中除了可以执行下一节介绍的SQL命令外，还可以使用一些专有命令。下面介绍最常用的几个命令。

（1）show

Show命令可以查看SQL*PLUS的环境变量及ORACLE的初始化参数。例如： Show all 可以显示所有环境变量的值； Show user 可以显示登录的当前用户名；

Show parameters 可以显示ORACLE的初始化参数当前值。 （2）desc（或describe）

89

Desc命令用于查看表的结构。例如： Desc s 可以查看表s中各列的定义。 （3）spool

Spool命令用于将以后的操作结果输出到文件（默认文件类型为.lst，但用户也可以指定）或默认打印机（文件名为out）。例如：

Spool d:\\result.txt 将以后的命令操作结果输出到文件d:\\result.txt

Spool off 命令则完成对上述文件的写入操作，同时取消spool命令的输出设置。 （4）start或@

start或@命令用于执行脚本文件（文件类型为.sql）。例如：

@d:\\branch_db.sql 批量执行d:\\branch_db.sql文件中的所有SQL命令。 （5）conn（或connect）

conn（或connect）命令用于登录数据库或切换用户。例如：

conn student/student 以用户名student，口令student登录ORACLE数据库。

11.2.4 ORACLE数据字典

数据字典是数据库的重要组成部分，它类似于一个商品仓库的物品清单，说明存放在仓库中的物品及存放位置、所属用户、使用说明等等，仓库管理员根据它完成日常业务。因此，数据库中的数据字典存放有关数据库的安全性（例如数据库的合法用户及其操作权限、账户状态等），数据库逻辑结构（例如有哪些表、每个表中包括哪些属性，表应该满足哪些完整性约束）、物理结构（数据库在磁盘上分几个文件存储，文件名是什么，存在什么位置等）以及逻辑结构和物理结构的对应关系（例如一个表存放在磁盘的哪个文件里）。

数据字典由DBMS自动维护，DBMS依赖数据字典管理、操作和维护数据库。对用户来说，数据字典是一组只读表，通过数据字典可以了解数据库的结构和状态等相关信息。

ORACLE的数据字典包括以下两类： ? 静态数据字典

静态数据字典包括三种视图：

DBA_开头的视图：包含数据库拥有的所有对象和权限的信息。

ALL_开头的视图：包含用户当前可以访问的全部对象和权限的信息。 USER_开头的视图：包含当前用户拥有的所有对象的信息。

另外，tab视图包含当前用户拥有的基本表、视图等对象的基本信息。 静态数据字典由sys用户创建，存放在system表空间中。 ? 动态性能视图

动态性能视图在数据库运行时存在于内存中，记录活动的数据库的信息（包括参数设置、数据库状态等）；由Sys用户所有，视图以v$开头。

90

11.3 关系数据库标准语言SQL

SQL（Structured Query Language）结构化查询语言是一个通用的、功能极强的关系数据库标准操作语言。

11.3.1 SQL概述

1. SQL 语言及标准

SQL语言是1974年由Boyce和Chamberlin提出的，最早用于IBM的San Jose研究室，该研究室在20世纪70年代后期研制的著名的关系数据库管理系统原型系统System R中实现了SQL语言。由于SQL功能丰富，语言简捷，倍受用户及计算机界欢迎，被众多计算机公司和软件公司所采用。经各公司的不断修改、扩充和完善，SQL语言最终发展成为关系数据库的标准语言。

1986年至今，美国国家标准协会（ANSI）和国际标准化组织（ISO）制订了一系列的SQL标准，它们是SQL-86、SQL-89、SQL-92、SQL-99和SQL-2003，使SQL标准的内容也越来越丰富、完善，以适应不断变化的技术需求。

为了使SQL完成更复杂的任务，各主要数据库厂商对标准SQL进行了一定的扩充和修改，增加了部分非标准SQL语句和函数。ORACLE公司支持的扩充后的SQL语言称为PL/SQL。

SQL语言主要有以下几个方面特点。

? SQL集数据定义、数据操纵、数据控制几大功能于一体。 ? SQL高度非过程化。用户只要提出“做什么”，而无需了解“怎么做”。 ? SQL采用集合操作方式，即操作对象、操作结果都是元组的集合。

? 以同一种语法结构提供两种使用方式（联机交互方式即用户直接输入SQL命令

完成对数据库的各种操作，嵌入式方式即将SQL语言嵌入到高级语言（例如C，C++，Java等）程序中使用）。 ? 语言简洁，易学易用

2. SQL主要命令动词

SQL虽功能强大，集数据定义、数据操纵、数据控制几大功能于一体，但其命令并不太多，用9个动词即完成数据库的核心功能。参见表11-1。

表11-1 SQL主要命令动词

SQL功能 数据定义 数据查询 数据操纵 数据控制

91

动词 CREATE，DROP，ALTER SELECT INSERT，UPDATE，DELETE CRANT，REVOKE 3. SQL语言使用注意事项

? SQL语言是大小写无关的。SQL命令中的命令动词、关键字、表名、列名既可

以大写，也可以小写。但SQL命令中涉及的字符数据是大小写敏感的。 ? 每个SQL语句用半角分号“；”结束，语句中使用的标点符号都是英文半角符号。 ? 在SQL语句中字符数据用半角单引号“ ‘ ”括起。

? 各数据库厂商对标准SQL进行了一定的扩充和修改，增加了部分非标准SQL语

句和函数。例如，ORACLE中的DESC命令，TO_CHAR()、TO_DATE()函数等。

11.3.2 SQL数据定义功能

SQL定义的主要对象包括基本表、索引和视图三类，其中后两者都是基于基本表的，因此通常不提供修改功能，视图的建立基于查询操作，请参见11.3.4。SQL主要定义语句参见表11-2。

表11-2 SQL主要数据定义语句

操作方式 操作对象 创建 基本表 视图 索引 删除 修改 CREATE TABLE DROP TABLE ALTER TABLE CREATE VIEW DROP VIEW CREATE INDEX DROP INDEX

1. 定义基本表

定义基本表包括定义表的名称、栏目（即属性）和完整性约束。具体语法如下： CREATE TABLE <表名>（

<列名> <数据类型> [ <列级完整性约束条件> ] [，<列名> <数据类型>[ <列级完整性约束条件>] ]

?

[，<表级完整性约束条件> ]

）；

（1）ORACLE常用数据类型

数据类型规定了表中每个栏目的大的取值范围，定义数据类型时往往同时定义取值的长度。不同的DBMS支持的数据类型不完全相同，对相同数据类型的表示也不完全一致。ORACLE中常用的主要数据类型及其表达如表11-3所示。

表11-3 ORACLE主要数据类型

数据类型 字符型

CHAR(n) 含义 长度为n的定长字符串,n最大2000 92

VARCHAR2(n) 数值型 NUMBER (p，d) 最大长度为n的变长字符串,n最大4000。 P表示最大有效数位（不包括符号、小数点） d表示小数位数 日期型 大对象型 DATE CLOB BLOB BFILE ORACLE固定按7字节长度保存，包括年月日时分秒 文本大对象，例如大的带格式的文本文档 二进制大对象，例如图片 二进制大文件，例如视频，文件保存在数据库外，数据库中只保存文件名及指向存储位置的指针 CHAR(n)与VARCHAR2(n)的区别：CHAR(n)类型的属性不论取多长的字符，实际占用空间都为n字节，长度不足n时后面以空格填充；VARCHAR(n)类型的属性实际占用空间以该属性实际取值长度为标准。

在ORACLE中，日期型数据往往借助于to_date()函数来表达，例如2012年05月01日可以表示为to_date(?2012-05-01?,?yyyy-mm-dd?)，即将字符数据?2012-05-01?转换为日期数据。其中yyyy表示四位年份，mm表示两位月份，dd表示两位日。另外，SYSDATE可以表示系统当前日期。

ORACLE中也支持SQL标准的INT、FLOAT、REAL、VARCHAR(n)等类型，但会将它们自动转化为相应的NUMBER和VARCHAR2(n)类型。

（2）ORACLE常用完整性约束

ORACLE中支持的完整性约束内容如下： ? 实体完整性约束：PRIMARY KEY

? 参照完整性约束：FOREIGN KEY(外码) REFERENCES 被引用表名（主码）

如果被引用表中修改了主码的值或删除了一行，则可能破坏当前表的参照完整性约束，例如：账户ACCOUNT表的CUSTOMER_ID列引用了客户表CUSTOMER表的主码。现在一个客户“510101199002013445”应经开立了一个存款账户“A00001”，当在CUSTOMER表中删除了该客户的信息或者把客户身份证号修改为一个新值时，都会破坏ACCOUNT表的参照完整性约束。对这类问题，可以指定三种处理方式：

NO ACTION: 不允许上述对CUSTOMER表的修改或删除操作； CASCADE（级联）：允许对CUSTOMER表进行修改或删除操作，但同时对ACCOUNT表中对应的行进行相同的操作；

SET NULL: 允许对CUSTOMER表进行修改或删除操作，但同时对ACCOUNT表中对应的行的CUSTOMER_ID设为空值。

ORACLE中银行为NO ACTION处理方式，可以指定在对被引用表进行删除操作时进行级联操作，形式为ON DELETER CASCADE ? 用户定义的完整性约束：

取值非空：NOT NULL 取值唯一：UNIQUE

取值满足一定的条件：CHECK(条件) ，例如客户性别属性CUST_GENDER只

93

能取值为“F”或“M”在SQL中表示为：check(cust_gender=?F? or cust_gender=?M?)

上述约束如果只涉及一个属性，则既可以作为列级完整性约束，也可以作为表级完整性约束，但参照完整性约束一般都放在表级。当PRIMARY KEY、NOT NULL、UNIQUE作为表级完整性约束时，必须在其后指定具体的属性名。

如果一个约束中涉及多个属性，则该约束必须放在表级。例如，账号ACCOUNT_NO和发生日期OPER_DATE构成存取明细表DEPOSIT的主码，该约束必须放在DEPOSIT的表级，形式为：PRIMARY KEY（ACCOUNT_NO，OPER_DATE）。

为方便对约束的维护，在定义约束时可以为每个约束定义一个名字，形式为： CONSTRAINT <约束名> <约束内容>

例如上述DEPOSIT表的主码约束可以表示为:

CONSTRAINT deposist_pk PRIMARY KEY（ACCOUNT_NO，OPER_DATE） （3）定义列的隐含值

指定一列的隐含值形式为：DEFAULT <隐含值>

在向CUSTOMER表中添加数据时，如果没有指定CUST_GENDER列的值，我们想让DBMS自动填入“M”（即“男”），则在定义CUST_GENDER列时可以指定隐含值，形式为：DEFAULT ?M?。指定隐含值一般放在列级约束之前。

【例11-1】 前述银行存款案例的三个表的定义如下： CREATE TABLE customer (

cust_id CHAR(18) constraint cust_pk primary key,

cust_name VARCHAR2(20) constraint cust_ntnull not null, cust_city VARCHAR2 (30) , cust_phone VARCHAR2(13),

cust_gender CHAR(1) constraint cust_fm check(cust_gender=?M? or cust_gender=?F?),

cust_job varCHAR2(30) );

CREATE TABLE account ( CUSTOMER_ID CHAR(18) ,

ACCOUNT_NO CHAR(8) constraint acct_pk primary key , BALANCE NUMBER(14, 2) default 0, BUILD_DATE DATE,

acct_type NUMBER (2,0) default 0 ,

STATUS CHAR(1) DEFAULT 0 constraint acct_status check (status in ('0','1','2')), staff_id VARCHAR2(12),

constraint acct_fk_cus foreign key(customer_id) references customer(customer_id) on delete cascade);

CREATE TABLE deposit ( ACCOUNT_NO CHAR(8),

94

amount NUMBER(11, 2) default 0, oper_DATE DATE,

oper_type CHAR (1) default 'c', staff_id VARCHAR 2(12),

constraint deposit_fk_acc foreign key(account_no) references account); （4）查看表的结构

在ORACLE的SQL*PLUS工具中，使用desc可以查看已经定义的表的结构。

例如：查看上述新建的CUSTOMER、ACCOUNT和DEPOSIT表的结构，可以用以下命令：

Desc customer Desc account Desc deposit

2、修改基本表

在ORACLE中，修改基本表的语句格式如下： 增加属性或约束：

ALTER TABLE 表名 ADD 列定义；

ALTER TABLE 表名 ADD CONSTRAINT <表级完整性约束>； 删除属性或约束：

ALTER TABLE 表名 DROP COLUMN 列名；

ALTER TABLE 表名 DROP CONSTRAINT <完整性约束>； 修改属性：

ALTER TABLE 表名 modify 列名 新的数据类型和长度

【例11-2】将deposit表的amount列的最大有效数位由11改为14。 ALTER TABLE deposit MODIFY amount NUMBER(14,2)；

在customer表中增加一列cust_street记录客户所在的街道，为ACCOUNT表增加一列branch_id 记录客户的开户分行编码。

ALTER TABLE customer ADD cust_street VARCHAR2(20); ALTER TABLE account ADD branch_id CHAR(4);

3．建立索引

索引是对基本表中一个或多个列的值进行排序，并建立其与表中相应行的对应关系，它是一个单独的数据库对象。建立索引的目的是为了加快查询速度。

ORACLE中创建索引的一般格式为： CREATE [UNIQUE] INDEX <索引名>

ON <表名> (<列名>[<次序>][,<列名>[<次序>]]...);

索引可以建在基本表的一列或多列上，各个列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，包括ASC（升序）和DESC（降序）两种，默认值为ASC。UNIQUE 表明该索引的每一个索引值只对应唯一的数据记录。

95

【例11-3】 对表customer 根据职业cust_job建立升序索引 idx_job CREATE INDEX idx_job ON customer(cust_job); 索引建立后，DBMS根据查询需要选择使用。 4．删除基本表和索引

当确定基本表或索引不再使用时，可以通过以下DROP命令删除。 DROP TABLE <表名> ; DROP INDEX <索引名>;

对存在引用（或称参照）关系的表的删除需要注意删除的顺序，例如account表引用了customer表，deposit表引用了account表，在删除时，应先删除deposit，在删account，最后删除customer表。

11.3.3 SQL数据查询功能

数据查询是数据库的核心操作。虽然SQL语言的数据查询只有一个SELECT命令动词，但却具有灵活的使用方法和丰富的功能。

1．SELECT语句基本格式

SELECT <目标列表达式>[，…] FROM <表名> [，…] [ WHERE <条件表达式> ]

[ GROUP BY <列名> [ HAVING <组条件表达式> ] ] [ ORDER BY <列名> [ ASC|DESC ] ]；

SELECT语句的结果也是一张表，通常称为查询表。SELECT语句的六个子句分别描述了查询的不同方面的特征，其中，SELECT 子句和FROM子句是必须的，其它子句根据查询的具体需要选择使用，但各子句的顺序不能颠倒。上述每个子句的含义如下：

SELECT子句：指定查询结果表中包含的属性或表达式，多个属性或表达式之间以逗号隔开；

FROM子句：指定查询对象表，多个对象之间以逗号隔开, 可以为表指定别名，形式为 FROM <表名> <别名> ；

WHERE子句：指定对象表中需要查询的行满足的条件

GROUP BY子句：分组统计。对表中满足查询条件的行按指定列分组，值相等的行为一个组。通常会在每组中使用集函数（即完成统计功能的函数）。

HAVING短语：在group by分组统计结果中筛选出满足指定条件的组 ORDER BY子句：对查询结果按指定列的指定顺序排序，该子句只能放在select语句的最后。

96

2．单表查询

（1）查询部分属性

【例11-4】查询每个存款客户的身份证号和姓名。 select cust_id,cust_name from customer;

（2）查询所有属性

为简化表达，SQL中用“*”表示表中所有属性。 【例11-5】查询所有存款客户的基本信息。 SELECT *

FROM Customer;

（3）取消结果表中的重复记录

SQL中通过在SELECT 子句中使用distinct关键字取消查询结果表中的重复记录。 【例11-6】查询存款客户分布在哪些职业。 SELECT DISTINCT cust_job FROM customer;

（4）查询经过计算的表达式与属性重命名 查询结果表中不仅包括查询对象中的属性，也可以是经过计算的表达式结果，但表达式结果所在的列没有具体有意义的属性名，此时可以为该列在结果表中重新命名，也称别名，格式为： <表达式> <别名> 或者 <表达式> as <别名>

【例11-7】查询每个客户的出生日期。

在customer表中并没有客户出生日期列，但在其身份证号中包含出生日期，在ORACLE中可以借助取子串函数substr(字符数据，子串起始位置，子串长度)来从身份证号中提取出生日期。

Select customer_id,cust_name,substr(customer_id,7,8) cust_birth From customer;

该例中，结果表的第三列的属性名为cust_birth，内容为客户的出生日期。 （5）带条件的单表查询

若要在数据表中找出满足某些条件的行时，则需使用WHERE来指定查询条件。查询条件的表达如表11-4所示。

表11-4 where 查询条件

运算符 =、>、<、>=、<=、!=、<> BETWEEN ...AND… IN、NOT IN LIKE、NOT LIKE IS NULL、IS NOT NULL

含 义 比较大小 是否在范围内 是否在集合内 字符模糊匹配 是否为空值 balance>100000 balance between 20000 and 100000 相当于：balance>=20000 and balance<=100000 cust_job in ('教师','工程师') cust_job like '%师' cust_name is not null 97

举 例 NOT、AND、OR 多重条件 balance>20000 and balance<100000

【例11-8】查询所有来自“成都”的客户信息 select *

from customer

where cust_city='成都‘;

【例11-9】查询所有来自“成都”的职业为“教师”的客户信息 select *

from customer

where cust_city='成都' and cust_job='教师';

【例11-10】查询余额大于100000或小于20000的帐户信息 select * from account

where balance<20000 or balance>1000 【例11-11】查询职业为“教师”、“工人”、“工程师”的客户信息。 select *

from customer

where cust_job in ('教师','工程师','工人')；

【例11-12】查询没有填写联系电话的客户的身份证号和姓名。 select customer_id,cust_name from customer

where cust_phone is null；

【例11-13】查询2012年新开的所有账户的信息。

账户表中开户日期build_date为日期型，不能直接和2012进行比较，在ORACLE中可以使用to_char(build_date,?yyyy?)先将开户日期的年份转化为字符数据，再和2012进行比较。

SELECT * FROM account

WHERE to_char(build_date,?yyyy?)=?2012?;

【例11-14】查询位于“成都”市“青羊大道”的客户的基本信息。

该例中涉及customer表的cust_street列，但条件不是精确相等，而是在cust_street列中只要包含“青羊大道”即可，这是一种字符数据的模糊查询。在SQL中使用以下形式表达模糊查询：

字符型列名 like ?模板?

其中，?模板?中会用到两个通配符%和_（下划线）： %：表示任意长度的字符串。

_：表示任意一个字符，在ORACLE中，_表示一个英文字符或一个汉字，根据列中该通配位置的具体类型自动确定。

Select *

98

From customer

Where cust_city='成都' and cust_street like '%青羊大道%'; （6）统计查询

在实际应用中，往往不仅要求将表中的记录查询出来，还需要在原有数据的基础上进行统计计算。SQL提供了许多统计函数，常用的统计函数如表11-5所示。在这些函数中，如果指定了DISTINCT，在计算时取消指定列中的重复值。

表11-5 SQL统计函数 函数名称 AVG([DISTINCT] <数值型列名>) SUM([DISTINCT] <数值型列名>) COUNT([DISTINCT] <列名>) COUNT（*） MAX([DISTINCT] <列名>) MIN([DISTINCT] <列名>) 功 能 按列计算平均值 按列计算值的总和 按列统计值的个数 统计行数 求一列中的最大值 求一列中的最小值 在聚合函数中遇到空值时，除COUNT(*)外，都跳过空值，仅处理非空值。须特别注意的是，在WHERE子句中是不能用聚合函数作为条件表达式的。

【例11-15】统计所有帐户中的最高余额和最低余额 select max(balance) 最高余额 ,min(balance) 最低余额 from account;

【例11-16】统计位于“成都”市的客户数。 select count(*) from customer

where cust_city='成都';

【例11-17】统计在银行开户的客户数。 select count(distinct customer_id) from account；

思考：这里的DISTINCT为什么不能省略？

在上述几个例子中，都是将表中所有满足条件的行（或者所有行）作为一个组处理，在一个组上进行指定的统计。如果需要将表中所有满足条件的行（或者所有行）分成若干组，对每个组分别进行统计，则要使用分组统计，用GROUP BY子句。

分组统计的关键是确定分组属性、汇总属性, 统计函数。

【例11-18】一个客户可以开立多个存款账户，统计每个客户开立的存款帐户数。 该例中，分组属性为客户身份证号，汇总属性为账号, 统计函数为统计个数。 select customer_id,count(account_no) 开户数 from account

group by customer_id;

【例11-19】统计每个客户的总存款额。 select customer_id,sum(balance) 余额合计

99

from account

group by customer_id;

如果需要从分组统计结果中再选择满足条件的组，则要用到HAVING 子句。 【例11-20】统计总存款额大于100000的客户的身份证号及其总存款额。 select customer_id,sum(balance) from account

group by customer_id

having sum(balance)>100000;

注意：在带有GROUP BY子句的查询语句中，SELECT子句中只能出现：分组属性、统计函数、常量。换句话说，SELECT子句中出现的属性必须包含在分组属性中；HAVING子句总是在GROUP BY子句之后，不可以单独使用。

（7）对查询结果排序

当用户需要对查询结果排序时，可用ORDER BY子句对查询结果按一个或多个查询列的升序（ASC）或降序（DESC）排列，默认值为升序。排在后面的属性指定的顺序只有在排在前面的属性的值相同时才起作用。

【例11-21】查询所有客户基本信息，结果按cust_city升序排序，cust_city相同的按照cust_job降序排序。

select *

from customer

order by cust_city, cust_job desc;

3．连接查询

当一个查询同时涉及两个及以上表时，一般使用连接查询。连接查询主要包括内连接和外连接两种。

（1）内连接

内连接的表达方式有以下两种方式（以两个表连接为例）： 方式一：

SELECT <查询目标列> From <表1>,<表2> where <连接条件> 方式二：

SELECT <查询目标列>

FORM <表1> join <表2> on <连接条件> 或者使用自然连接： SELECT <查询目标列>

FORM <表1> natural join <表2>

在方式一和方式二的非自然连接表达中，需要注意：对于两个表都有的同名字段，必须用表名或别名加以限制。使用形式为：<表名>.<属性名>

在方式二的自然连接表达中，由于自然连接结果中取消了重复属性，因此不能使用<

100

表名>.<属性名>的形式。

【例11-22】查询每个账户的账号、客户姓名和账户余额。 select cust_name,account_no,balance from customer c,account a

where c.customer_id=a.customer_id; 或者：

select cust_name,account_no,balance

from customer c join account a on c.customer_id=a.customer_id; 或者：

select cust_name,account_no,balance from customer natural join account; （2）外连接

在ORACLE中，外连接也分为左外联、右外联和全外联。表达形式为： SELECT <查询目标列>

FORM <表1> left|right|full outer join <表2> on <连接条件>

【例11-23】查询每个客户的姓名、身份证号及其开立的定期存款账号及账户余额。 select cust_name, c.customer_id, account_no,balance

from customer c left outer join account a on c.customer_id=a.customer_id and acct_type>0;

思考：acct_type>0是否可以放在where子句中？

4．嵌套查询和集合查询

一个SELECT查询的结果是一张表，表有可以看做元组的集合。因此，在SELECT查询的六个子句中，FROM、WHERE、HAVING三个子句中可以嵌入另一不带ORDER BY子句的查询语句，这就形成了嵌套查询。

两个集合之间可以进行并、交、差运算，因此两个SELECT查询之间也可以进行并、交、差运算，这就形成了集合查询。

嵌套查询和集合查询的具体内容请参见数据库相关书籍。

13.3.4 SQL的数据更新功能

SQL的数据更新功能包括数据插入、修改和删除。 1.插入数据

如果要向表中插入一个具体的行，可以使用以下格式的SQL命令：

INSERT INTO <表名>[(<属性1>[,<属性2>，?])] VALUES(<值1>[,<值2>，?])； INSERT语句中属性的排列顺序可以任意指定，但当指定属性名时，VALUES子句值的排列顺序必须和指定属性名的排列顺序一致，个数相等，数据类型一一对应。

101

如果要将一个查询的结果插入一张表，则可以使用以下格式的SQL命令： INSERT INTO <表名>[(<属性1>[,<属性2>，?])] <查询语句>

查询语句中SELECT子句指定的属性的个数及相应属性的类型和长度，必须与INTO后指定的属性相容。

INTO语句中没有出现的属性名，新记录在这些属性上将取空值。 如果INTO子句没有指定属性名，则插入的新记录的值顺序必须和表定义的属性顺序一致，而且必须在每个属性上都有值。

如果INTO子句指定了属性，则必须包含表中具有主码和非空约束的属性。 【例11-24】向customer表中插入一个新客户信息：

客户身份证号：510106199006035443,姓名:李峰,电话:13512345678，性别：男 INSERT INTO customer(customer_id,cust_name,cust_phone,cust_gender) VALUES(?510106199006035443?,? 李峰?,? 13512345678?,?M?);

2.修改数据

修改数据的SQL命令格式为： UPDATE <表名>

SET <属性名1>=<新值表达式> [,<属性名2>=<新值表达式>…] [ WHERE <条件> ]；

如果不指定WHERE 条件则表示修改表的所有行。

【例11-25】 将身份证号为“510106199006035443”的客户的城市修改为重庆。 UPDATE customer SET cust_city=? 重庆?

WHERE customer_id=? 510106199006035443?; 2.删除数据

使用DELETE语句可删除表中的一个或多个记录，语法格式为： DELETE FROM <表名> [ WHERE <条件> ]；

如果不指定WHERE 条件则表示删除表的所有行。

【例11-26】删除表ACCOUNT中STATUS为“2”的账户信息。 DELETE FROM account WHERE STATUS=?2?;

13.3.4 视图

视图是一个虚表，在数据库中只存放视图的定义，而不存放视图对应的数据。视图也

102

可以理解为命名的查询表。

1．定义视图

定义视图的SQL命令为：

CREATE VIEW <视图名> [ (属性名1，属性名2，…) ]AS