数据库技术与应用.ppt

1、数据库技术与应用,课程负责人 黄崇本,编委会名单,编委会名单,主任：俞瑞钊 副主任：陈庆章 周必水 刘加海 委员 (以姓氏笔画为序) 王 雷 王筱慧 方 程 方锦明 卢菊洪 代绍庆 吕何新 朱 炜 刘向荣 江爱民 江锦祥 孙光第 李天真 李永平 李良财 李明钧 李益明 余根墀 汪志达 沈凤池 沈安衢 张 元 张学辉 张锦祥 张德发 陈月波 陈晓燕 邵应珍 范剑波 欧阳江林 周国民 周建阳 赵小明 胡海影 秦学礼 徐文杰 凌 彦 曹哲新 戚海燕 龚祥国 章剑林 蒋黎红 董方武 鲁俊生 谢 川 谢晓飞 楼 丰 楼程伟 鞠洪尧 秘书长：熊盛新,1.本软件用PowerPoint制作而成，播放亦在其状态

2、下进 行，为了更好地使用该软件,使用者需掌握PowerPoint的基本 操作原理和方法。 .本软件内容丰富、新颖，突破传统教学模式，在多媒体教室使用时，使用者应注意这一变化，改变教学方法，充分发挥其功效。同时也应注意本软件仍是一教学辅助工具，授课时应与讲授相结合使其更好地为教学服务。 .本软件内容完整，自成体系，对于使用者的不同情况，用本软件授课时，可补充所需内容，链入其它媒体信息，或同其它软（如CS ChemDraw等）共同使用，以达到多媒体辅助教学的良好效果。 .多媒体教学信息量大，传递速度快，用本软件授课时，应 熟悉播放程序，注意控制播放速度，要使观看者有充足的思 考时间,注意事项,数据

3、库技术是计算机科学技术中发展最快的应用之一，已经成为计算机信息系统与应用系统的核心技术，它与网络技术构成计算机应用的两个平台。数据库技术与应用课程已成为高职高专院校计算机教学中的主干课程，是计算机应用专业的必修基础课程。 本着高职高专教学突出理论知识的应用和实践能力的培养，基础理论以必需、够用为度，专业教学加强针对性和实用性等原则，将课程的相关内容分为基础篇、应用篇和实训篇。课程中既安排了目前使用非常广泛的桌面数据库管理系统Access，让学生学会使用Access数据库的基本方法，也全面介绍了当前主流大型网络数据库系统SQL Server ，同时还介绍了数据库应用系统开发的基本方法,前 言,第

4、1章 数据库技术概论,第2章 关系数据库,第3章 关系数据库语言SQL,第4章 数据库设计,数据库技术与应用 之 基础篇,第一章 数据库技术概论,1.1 数据与数据管理,1什么是数据库,数据库是长期存储在计算机内有组织、可共享的数据集合。数据库好比是存放数据的仓库，里面存放各式各样的相关数据。 图书馆可视作一个数据库，书架对应表格，书籍对应原始数据，书名或作者名的目录卡片对应索引,2数据与信息,数据是指用符号记录下来的可以区别的信息；信息是关于现实世界中事物的存在方式或运动形态的反映，是人们进行各种活动所需的知识。数据是信息的载体，信息是被人们消化的数据,1.1数据与数据管理,3数据处理,数据

5、处理指将数据转换成信息的过程 ，广义也讲，数据处理包括对数据的收集、存储、传播、检索、分类、加工或计算、打印各类报表或输出各种需要的图形等一系列活动；狭义也讲，数据是指对所输入的数据进行加工整理,4数据管理,在数据处理的一系列活动中，数据收集、存储、传播、检索、分类等操作是基本环节，这些基本环节统称为数据管理。 数据与信息之间的关系可以表示为： 信息 = 数据 + 数据处理,1.2 数据管理技术的发展,数据管理技术发展的三个阶段,计算机数据管理大致经历了三个阶段： 人工管理方式阶段 文件管理方式阶段 数据库 管理方式阶段,1.2.1 人工管理方式阶段,1基本情况,20世纪50年代中期以前，计算

6、机主要用于科学计算。硬件方面，外存储器只有卡片、纸带、磁带，没有像磁盘这样的可以随机访问、直接存取的外部存储设备。软件方面，没有专门管理数据的软件，数据由计算机或处理它的程序自行携带。数据处理方式基本是批处理,1.2.1 人工管理方式阶段,2特点,特点1：数据和程序不具有独立性。一组数据对应一组程序，这就使得程序依赖于数据，如果数据的类型、格式或者数据量、存取方法、输入输出方式等改变了，程序必须做相应的修改。 特点2：数据不能长期保存。由于数据是面向应用程序的，在一个程序中定义的数据，无法被其它程序利用，因此，程序与程序之间存在大量的重复数据。 特点3：系统中没有对数据进行管理的软件。数据管理

7、任务，包括存储结构、存取方法、输入输出方式等完全由程序设计人员 自负其责，这就给应用程序设计人员增加了很大的负担,1.2.2 文件管理方式阶段,1基本情况,20世纪50年代后期至60年代中后期，大量的数据存储、检索和维护成为紧迫的需求。数据处理方式有批处理，也有联机实时处理。程序与数据有了一定的独立性，程序与数据分开存储，有了程序文件和数据文件的区别。数据文件可以长期保存在外存储器上多次存取，如进行查询、修改、插入、删除等操作。数据的存取以记录为基本单位，并进行了多种文件组织形式，如顺序文件、索引文件、随机文件等。在文件系统的支持下，数据的逻辑结构与物理结构之间可以有一定的差别，逻辑结构与物理

8、结构之间的转换由文件系统的趣取方法来实现。数据与程序之间有设备独立性，程序只需用文件名访问数据，不必关心数据的物理位置。这样，程序员可以集中精力在数据处理的算法上，而不必考虑数据存储的具体细节,1.2.2 文件管理方式阶段,2存在问题,问题1：据冗余大。数据冗余是指不必要的重复存储，同一数据项重复出现在多个文件中。 问题2：缺乏数据独立性。文件系统中的数据文件是为某一特定的应用而设计的，数据与程序相互依赖，如果改变数据的逻辑结构或文件的组织方法，必须修改相应的应用程序，反之也是。 问题3：数据无集中管理。数据文件均由相应的应用程序管理和维护，没有统一的管理机制。造成数据文件之间无法进行联系，不

9、能反映现实世界事物之间的联系,1.2.3 数据库管理方式阶段,1基本情况,从20世纪60年代后期开始，需要计算机管理的数据量急剧增长，并且对数据共享的需求日益增强。为了解决数据的独立性问题，实现数据的统一管理，达到数据共享的目的，出现了数据库技术。数据库是通用化的相关数据集合。它不仅包括数据本身，而且包括关于数据之间的联系。为数据库的建立、使用和维护而配置的软件称为数据库管理系统（DBMS）。它是在操作系统支持下运行的。目前较为流行的数据库管理系统包括：Oracle、Informix、SQL server及微机上DBMS（dDBASE、FoxBASE、FoxPro等）。 数据库已成为各类信息系

10、统的核心基础,1.2.3 数据库管理方式阶段,2优点,实现数据共享，减少数据冗余。数据库中存放通用化的综合数据，某一应用通常仅使用总体数据的子集。 数据高度结构化。数据库中的数据是有结构的，它是用某种数据模型表示出来的，这种结构既反映文件内数据之间的联系，也反映文件之间的联系。具有较高的数据独立性。在数据库系统中，DBMS提供映象功能，确保应用程序对数据结构和存取方法有较高的独立性。数据的物理存储结构与用户看到的逻辑结构可以有很大的差别。用户只以简单的逻辑结构来操作数据，无需考虑数据在存储器上的物理位置与结构。有统一的数据控制功能。数据库作为用户与应用程序的共享资源，对数据的存取往往是并发的，

11、即多个用户同时使用同一个数据库。数据库管理系统必须提供并发控制功能、数据的安全性控制功能和数据完整性控制功能,1.3 数据库基本概念,1数据库系统（DBS或DBAS,数据库系统(DBS)也称为数据库应用系统（DBAS）,是指具有管理和控制数据库功能的计算机应用系统。 数据库系统由5部分组成：硬件系统、数据库集合、数据库管理系统及相关软件、数据库管理员（DBA）和用户。 硬件系统是整个数据库系统的基础，数据库集合是若干个设计合理、满足应用需求的数据库。数据管理系统是为数据库的建立、使用和维护而配置的软件，是数据库系统的核心组成部分；相关软件是支持软件，如操作系统等。数据库管理员是全面负责建立、维

12、护和管理数据库系统的人员。用户是最终系统的使用和操作人员,1.3 数据库基本概念,2DBA职责,1.安装和升级数据库服务器（如ORACLE.、MICROSOFT SQL SERVER），以及应用程序工具 2 数据库设计系统存储方案，并制定未来的存储需求计划 3 开发人员设计了一个应用，就需要DBA来创建数据库存储结构4 开发人员设计了一个应用，就需要DBA来创建数据库对象 5 根据开发人员的反馈信息，必要的时候，修改数据库的结构 6 登记数据库的用户，维护数据库的安全性7 保证数据库的使用符合知识产权相关法规8 监控和优化数据库的性能9 控制和监控用户对数据库的存取访问 10 制定数据库备份计

13、划，灾难出现时对数据库信息进行恢复 11维护适当介质上的存档或者备份数据12备份和恢复数据库13. 联系数据库系统的生产厂商，跟踪技术信息 。DBA的认证 ： IBM ORACLE MICROSOFT,1.3 数据库基本概念,3数据库管理系统（DBMS,数据库管理系统（DBMS）作为数据库系统的核心软件，其主要目标是数据成为方便用户使用的资源，易于为各类用户所共享，并增加数据的安全性、完整性和可用性。 DBMS具有下列功能： 数据库的定义功能 提供数据定义语言DDL或操作命令，以便对各级数据模式进行精确的描述,1.3 数据库基本概念,3数据库管理系统（DBMS,数据操纵功能 为了对数据库中的数

14、据进行追加、插入、修改、删除、检索等操作命令，称为数据操作语言DML。可以分为两类：一类是DML可以独立交互式使用，不依赖于任何程序设计语言，称为自含或自主型语言。另一类是宿主型DML，嵌入到宿主语言中使用。如嵌入到C程序设计语言中，在使用高级语言编写的应用程序中，需要调用数据库中的数据时，则要用宿主型DML语句来操纵数据,1.3 数据库基本概念,3数据库管理系统（DBMS,数据库运行控制功能 数据库中的数据是提供给多个用户共享的，用户对数据的存取可能是并发的，即多个用户同时使用一个数据库。DBMS必须提供以下3方面的数据控制功能：并发控制功能，数据的安全性控制。数据的完整性控制。 数据字典

15、数据字典DD中存放着对实际数据库各级模式所做的定义，即对数据库结构的描述。这些数据是数据库系统中有关数据的数据，称之为元数据。DD提供了对数据库数据描述的集中管理手段，对数据库的使用和操作都通过查阅数据字典来进行,1.3 数据库基本概念,4数据模型,数据模型是指数据以及数据之间的联系的描述 数据库管理系统所支持的数据模型有四种：层次模型、网状模型、关系模型、面向对象模型。 层次模型用树形结构来表示各类实体以及实体间的联系。每个结点表示一个记录类型，结点之间的连线表示记录类型间的联系，这种联系只能是父子联系。网状数据模型是一种比层次模型更具普遍性的结构，它去掉了层次模型的两个限制，允许多个结点没

16、有双亲结点，允许结点有多个双亲结点，此外它还允许两个结点之间有多种联系（称之为复合联系）。在用户看来，一个关系模型的逻辑结构是一张二维表，它由行和列组成,1.4 数据库系统的体系结构,1模式的概论,模式是数据库中全体数据的逻辑结构和特征的描述，它仅仅涉及模式的描述，不涉及具体的值,2数据库系统的三级体系结构,用户使用的数据视图叫做外模式，又称子模式，是一种局部的逻辑数据视图，表示用户所理解的实体、实体属性和实体关系。 全局的逻辑数据视图叫概念模式，简称为模式，是数据库管理员所看到的实体、实体属性和实体之间的关系。 数据的物理存储模式叫做内模式，也称物理模式。 子模式是模式的子集，可以从模式推导

17、出来,1.4 数据库系统的体系结构,数据库三级模式结构,1.4 数据库系统的体系结构,3数据库的两级映象,子模式与模式之间的映象：定义了各子模式与模式之间的映射关系。当整个系统要求改模式时，可以改变映射关系而保持子模式不变。这种用户数据独立于全局的逻辑数据的特性即为逻辑数据独立性； 模式与物理模式之间映象：定义了模式与物理模式的映射关系。当为了某种需要改变物理模式时，可以同时改变两者之间的映射而保持模式与子模式不变，这种全局的逻辑数据独立于物理数据的特性即为物理数据独立性,1.5 数据库保护,1安全性保护,防止未被授权者非法存取数据。可采用方法：鉴定用户身份、口令、用密码记载数据、控制用户权限

18、,2完整性保护,指数据正确性和一致性。完整性保护可以通过对数据及数据之间的逻辑关系施加约束条件来实现，约束条件有：值约束、结构约束、动态约束,1.5 数据库保护,3故障恢复保护,备份与恢复,4并发控制保护,多用户共享数据时，而造成数据不一致性。用加锁的方法进行保护,第二章 关系数据库,2.1 关系的数学定义,1二维表与关系,学生信息表,2.1 关系的数学定义,上述二维表（或简称表）表示了学生类型的一些实体，每个二维表又称为关系。 学生信息表是表名，在关系数据库中，表名对应于数据库名（或关系名）。 表头由一些反映实体属性的属性名组成，它对应于数据库结构描述，定义了实体的型，在表上属性名必须是唯一

19、，不允许重名。 表体是由一些行（元组或称记录）组成，它是数据库的内容及数据库操作对象。一个元组对应于文件系统中的一个记录，一个记录含有若干个域用以存储属性值。一个元组对应于一个学生实体。表体中的列反映实体的属性,2.1 关系的数学定义,2关系的数据定义,域（Domain）是值（Value）的集合如：整数、实数、0，1，2，3、真、假、（杭州，0571）、（宁波，0574） 域，若是一个有限集，其元数的个数叫做域的基数。如1，2，3，4，5的基数为5。 给定一组域D1，D2，Dn， 这些域中有些域可以是相同的。D1，D2，Dn的笛卡尔乘积为D1D2Dn= d1，d2，dndiDi，i=1，2，n

20、,其中每一个元素（ d1，d2，dn）叫做一个n元组，或简称元组。 D1D2Dn的子集叫作域D1，D2，Dn上的关系，用R（D1，D2，Dn）表示，这里的R表示关系的名字，n是关系的度（或叫元数,2.1 关系的数学定义,3关系的相关概念,关系是笛卡积的子集，所以关系也是一个二维表，表的每行对应一个元组，表的每列对应一个域。由于域可以相同，为了加以区分，必须对每列起一个名字，称为属性（Attribute）。n目关系必有n个属性。 若关系中的某一属性组的值能唯一地标识一个元组，则称该属性组为候选码（Candidate key）。 若一个关系有多个候选码，则选定其中一个为主码（Primary key

21、）。主码的诸属性称为主属性（Prime attribute）。 不包含在任何侯选码中的属性称为非码属性（Non-key attribute）。 在最简单的情况下， 侯选码只包含一个属性。 在最极端的情况下，关系模式的所有属性组是这个关系模式的侯选码，称为全码 （All-key,2.1 关系的数学定义,4关系的三种类型,基本关系（通常又称为基本表或基表）、查询表和视图表。基本表是实际存在的表，它是实际存储数据的逻辑表示。查询表是查询结果对应的表。视图表是由基本表或其他视图表导出的表，是虚表，不对应实际存储的数据,2.1 关系的数学定义,5关系的性质,在关系数据库中，要求关系中的每一个分量是不可再

22、分的数据项， 关系数据库中的关系应具有以下性质： 列是同质的，即每一列中的分量均是同类型的数据，即均来自同一个域。 不同的列可以出自同一个域，每一列称为一个属性，要给予不同的列不同的属性名。 列的顺序是无所谓的，即列的次序可以变换。 任意两个元组不能完全相同。 行的顺序是无所谓的，即行的次序可以变换。 每一分量必须是不可再分的数据项,2.2 关系数据库,1关系模型,关系模型是由三部分组成：数据结构（即关系）、关系操作、关系完整性。 在关系模型中，无论是实体还是实体之间的联系均由单一的类型结构-关系来表示。 关系模式是指关系的描述，即对关系名、组成关系的各属性名、属性到域的映射、属性间的数据依赖

23、关系等等。关系模式通常简记为R（A1，A2，An） 关系数据库是指数据库结构的描述，它包括关系数据库名，若干属性的定义，以及这些属性上的若干关系模式,2.2 关系数据库,2关系操作,关系模型规定了关系操作的功能和特点，但不对DBMS语言的语法做出具体的规定。 关系数据库语言的主要优点是其高度的非过程化，用户只须知道语句做什么，而不须知道怎么做。 关系操作主要有：并、交、差、选择、投影、连接等，其中选择、投影及连接是最基本的关系操作。这些操作均对关系的内容或表体实施操作的，得到的结果仍为关系。 关系操作的特点是集合操作，即操作对象和结果都是集合,2.2 关系数据库,3关系模型的完整性,关系模型的

24、三类完整性：实体完整性、参照完整性及用户定义的完整性。 实体完整性和参照完整性是任何关系模型都有必须满足的完整性约束条件，应该由关系数据库DBMS自动支持。 用户定义的完整性的支持是由DBMS提供完整性定义设施，可以随DBMS商品软件不同而有所变化,2.2 关系数据库,4实体完整性（Entity Integrity,一个基本关系通常对应现实世界的一个实体集。例如学生关系对应于学生的集合。现实世界中的实体是可区分的，即它们具有某种唯一性标识。相应地，关系模型中以主码作为唯一性标识。主码中的属性即主属性不能取空值。所谓空值就是“不知道”或“无意义”的值。如果主属性取空值，就说明存在某个不可标识的实

25、体，即存在不可区分的实体，这与现实世界的应用环境相矛盾，因此这个实体一定不是一个完整的实体。 规则： 实体完整性规则：若属性A是基本关系R的主属性，则属性A不能取空值,2.2 关系数据库,4参照完整性（Referential integrity,定义 设F是基本关系R的一个或一组属性，但不是关系R的码，如果F与基本关系S的主码Ks相对应，则称F是基本关系R的外码（Foreign key），并称基本关系R为参照关系（Referencing relation），基本关系S为被参照关系（Referenced relation）或目标关系（Target relation）。关系R和S不一定是不同的关系

26、。参照完整性规则就是定义外码与主码之间的引用规则。 规则2.2 参照完整性规则：若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码Ks相对应（基本关系R和S不一定是不同的关系），则对于R中每个元组在F上的值必须为： 或者取空值（F的每个属性值均为空值）； 或者等于S中某个元组的主码值,2.2 关系数据库,5用户定义完整性（User-defined integrity,实体完整性和参照性适用于任何关系数据库系统。除此之外，不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件，用户定义的完整性就是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的

27、语义要求。关系模型应提供定义和检验这类完整性的机制，以便用统一的系统的方法处理它们，而不要由应用程序承担这一功能,2.2 关系数据库,6关系数据语言,关系数据语言可分为三类：数据描述语言DDL，数据操纵语言DML和数据控制语言DCL。 DDL负责数据库的描述，提供一种数据描述机制，用来描述数据库的特征或数据的逻辑结构。 DML负责数据库的操作，提供一种数据处理操作的机制。 DCL负责控制数据库的完整性和安全性，提供一种检验完整性和保证安全的机制,2.2 关系数据库,7DML语言,DML包括数据查询和数据的增、删、改等功能。其中查询的表达方式是DML的主要部分。 关系数据库的DML按照查询方式可

28、以分为两大类：用对关系的集合运算来表示查询方式，称为关系代数；用谓词演算来表达查询方式，称为关系演算； 关系代数和关系演算均是抽象的查询语言，是实际DBMS软件产品中实现的具体查询语言的理论基础。 结构化查询语言SQL是介于关系代数和关系演算之间的一种语言，SQL是集DDL、DML、DCL为一体的标准的关系数据库语言,2.3 关系运算,1传统的集合运算,传统的集合运算是二目运算。设关系R和关系S具有相同的度，且相应的属性值取自同一个域，则它们之间能进行并、交及差运算 并运算：两个关系R与S的并记为RS，它是一个新的关系，由属于R或属于S的元组组成，可形式地定义为： RS=ttRtS其中t是元组

29、变量，表示关系中的元组。 交运算：两个关系R与S的交记为RS，它是由属于R且属于S的元组组成，可形式地定义为： RS=ttRtS 差运算：两个关系R与S的并记为R-S，它是由属于R但不属于S的 元组组成，可形式地定义为： R-S=ttRnot(tS,2.3 关系运算,2专门的关系运算选择,选择运算是从某个给定的关系中筛选出满足限定条件的元组子集，它是一元关系运算。可形式定义为：F (R)=ttRF（t） 其中，F是筛选关系R中元组的限定条件的布尔表达式，它由逻辑运算符、连接各算术表达式组成。 学生信息表 S如下： 例：查询信息系 全体学生： SD信息（S,2.3 关系运算,3专门的关系运算投影

30、,选择运算是从某个关系中选取一个“行”的子集，而投影运算实际上是生成一个关系的“列”的子集，它从给予定的关系中保留指定的属性子集而删去其余属性。设某关系R（X），X是R的属性集，A是X的一个子集，则R在A的投影可表示为：A(R)=tAtR其中，tA表示元组相应A属性中的分量。 例：查询学生的姓名和所在系： SN,SD(S,2.3 关系运算,4专门的关系运算连接,连接也称为连接。它是从两个关系的笛卡尔积中选取属性间满足一定条件的元组，连接运算中有两种最为重要也最为常用的连接，一种是等值连接（equi-join），另一种是自然连接（Natural join）。 为“”的连接运算称为等值连接。它是从

31、关系R与S的笛卡尔积中选取A、B属性值相等的那些元组 自然连接（Natural join）是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且要在结果中把重复的属性去掉。 一般的连接操作是从行的角度进行运算。但自然连接还 需要取消了重复列，所以是同时从行和列的 角度进行运算,2.3 关系运算,5专门的关系运算除,一个m元关系R除以一外n元关系S（其中mn，S非空关系并且R中存在n个属性与S的n个属性定义在相同的域）所得到的结果是一个（m-n）元的新的关系，它表示满足以下条件的元组集合：RS=t(m-n)对任一t(n)S都有t(m-n)。t(n)R其中t(m-n)。t(n

32、) 表示一个（m-n）元的元组和一个n元的元组拼合成一个m元的新元组,2.3 关系运算,6关系代数运算举例,学生关系S同前面的一样，课程关系C（课程号C#，课程名CN，任课教师CT）和学生选课关系SC（学号S#，课程号C#，成绩G） 例1：查询年龄小于20岁的学生 sage20(Student) 例2：检索学习课程C2的学生的学号与成绩 S#，G（C#=C2（SC） 例3：求出学习全部课程的的学生名单 SN（S（S#，C#（SC）C#（C,2.3 关系运算,例4、将新开课程记录（C6,Q,R）插入到关系C中 C（C6,Q,R） 例5、将学号为S3学生的C4课程的成绩改为A （ SC(S3,C4

33、,?)（S3,C4,A） 例6、求同时选修课程C1和C2的学生姓名 C12=C# （C#=C1OR C#=C2（C） SC12= S#，C#（SC）C12 S12=SN（SSC12,第三章 关系数据库语言SQL,3.1 SQL数据定义,1SQL介绍,SQL是英文Structured Query Language的缩写，意思为结构化查询语言。SQL语言的主要功能就是同各种数据库建立联系，进行沟通。按照ANSI（美国国家标准协会）的规定，SQL被作为关系型数据库管理系统的标准语言。SQL语句可以用来执行各种各样的操作，例如更新数据库中的数据，从数据库中提取数据等。目前，绝大多数流行的关系型数据库管

34、理系统，如Oracle, Sybase, Microsoft SQL Server, Access等都采用了SQL语言标准。虽然很多数据库都对SQL语句进行了再开发和扩展，但是包括Select, Insert, Update, Delete, Create, 以及Drop在内的标准的SQL命令仍然可以被用来完成几乎所有的数据库操作 SQL语言的版本包括：SQL-89，SQL-92，SQL3,3.1 SQL数据定义,2SQL动词,数据查询：SELECT 数据定义： CREAT, DROP, ALTER 数据操纵： INSERT, UPDATE, DELETE 数据控制： GRANT , REVO

35、KE,3.1 SQL数据定义,3示例数据库结构,学生课程”数据库中包括三个表： “学生”表Student由学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）五个属性组成，可记为： Student(Sno,Sname,Ssex,Sage,Sdept) 关键字Sno (2) “课程”表Course由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）四个属性组成，可记为： Course(Cno,Cname,Cpno,Ccredit) 关键字 Cno (3) “学生选课”表SC由学号（Sno）、课程号（Cno）、成绩（Grad

36、e）三个属性组成，可记为： SC(Sno,Cno,Grade) 关键字(Sno, Cno,3.1 SQL数据定义,4创建基本表,一般格式如下： CREATE TABLE ( 列级完整性约束条件 ， 列级完整性约束条件.) ,; 例1 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号属性不能为空，并且其值是唯一的。 CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20), Ssex CHAR(1), Sage INT, Sdept CHA

37、R(15,3.1 SQL数据定义,SQL提供的基本数据类型： 数值型：包括 integer、real、float,numeric等字符串型：char(n)、varchar(n)，前者是定长，后者为 变长串 位串型：bit(n),bit varying(n)，同上。 时间型：date、time,3.1 SQL数据定义,5修改基本表,一般格式如下： ALTER TABLE ADD 完整性约束DROP MODIFY ; 其中指定需要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义。 例2 向Student表增加“

38、入学时间”列，其数据类型为日期型 ALTER TABLE Student ADD Scome DATE; 不论基本表中原来是否已有数据，新增加的列一律为空值,3.1 SQL数据定义,例3 将年龄的数据类型改为半字长整数 ALTER TABLE Student MODIFY Sage SMALLINT; 修改原有的列定义有可能会破坏已有数据 例4 删除关于学号必须取唯一值的约束 ALTER TABLE Student DROP UNIQUE(Sno,3.1 SQL数据定义,5删除基本表,一般格式如下： DROP TABLE 例5 删除Student表 DROP TABLEStudent 基本表定

39、义一旦删除，表中的数据、在此表上建立的索引都将自动被删除掉，而建立在此表上的视图虽仍然保留，但已无法引用。因此执行删除操作一定要格外小心,3.1 SQL数据定义,6建立索引,一般格式如下： CREATE UNIQUE CLUSTER INDEX ON (,.); 其中，指定要建索引的基本表的名字。索引可以建在该表的一列或多列上，各列名之间用逗号分隔。每个后面还可以用指定索引值的排列次序，包括ASC（升序）和DESC（降序）两种，缺省值为ASC。 UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。 CLUSTER表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序

40、一致的索引组织,3.1 SQL数据定义,例6 为学生-课程数据库中的Student、Couse、SC三个表建立索引。其中Student表按学号升序建唯一索引，Couse表按课程号升序建唯一索引，Sno、Cno表按学号升序和课程号降序建唯一索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Couse(Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC,3.1 SQL数据定义,7删除索引,一般格式如下： DROP INDEX; 例7 删除

41、Student表的Stusname索引 DROP INDEX Stusname; 索引一经建立，就由系统使用和维护它，不需用户干预。建立索引是为了减少查询操作的时间，但如果数据增删改频繁，系统会花费许多时间来维护索引。这时，可以删除一些不必要的索引。删除索引时，系统会同时从数据字典中删去有关该索引的描述,3.2 SQL数据查询,1查询语句格式,一般格式为： SELECT ALL|DISTINCT,. FROM , . WHERE GROUP BY HAVING ORDER BY ASC|DESC; 整个SELECT语句的含义是，根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中

42、找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中作用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。如果有ORDER子句，则结果表还要按的值的 升序或降序排序,3.2 SQL数据查询,1单表查询,一、选择表中的若干列 例1 查询全体学生的学号与姓名 SELECT Sno,Sname FROM Student; 例2 查询全体学生的姓名、学号、所在系 SELECT Sname, Sno, Sdept FROM

43、 Student; 例3 查询全体学生的详细记录 SELECT * FROM Student,3.2 SQL数据查询,例4 查全体学生的姓名及其出生年份 SELECT Sname, 2004-Sage FROM Student; 例5 查全体学生的姓名、出生年份和所有系，要求用小写字 母表示所有系名 SELECT Sname, Year of Birth:, 1996-Sage, ISLOWER(Sdept) FROM Student,3.2 SQL数据查询,二、选择表中的若干元组 1、消除取值重复的行 例6 ：查所有选修过课的学生的学号 SELECT DISTINCT Sno FROM SC

44、; 2、查询满足条件的元组 查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如下页,3.2 SQL数据查询,常用的查询条件 （1）比较 ：、 = 、 =、 != （2）确定范围 ：BETWEEN AND, NOT BETWEEN AND （3）确定集合 ：IN, NOT IN （4）字符匹配 ：LIKE, NOT LIKE （5）空值 ：IS NULL, IS NOT NULL （6）多重条件 ：AND, OR,3.2 SQL数据查询,1) 比较,例7 ：查计算机系全体学生的名单 SELECT Sname FROM Student WHERE Sdept = CS

45、; 例8 ：查所有年龄在20岁以下的学生姓名及其年龄 SELECT Sname, Sage FROM Student WHERE Sage = 20 例9 ：查考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM Course WHERE Grade 60,3.2 SQL数据查询,2) 确定范围,例10 ：查询年龄在20至23岁之间的学生的姓名、系别、和年龄 SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23 ; 例11 ：查询年龄不在20至23岁之间的学生姓名、系别和年 龄 SELE

46、CT Sname, Sdept， Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23,3.2 SQL数据查询,3) 确定集合,例12 ：查信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别 SELECT Sname, Ssex FROM Student WHERE Sdept IN (IS, MA, CS) ; 例13 ：查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别 SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN (IS, MA, CS,3.2 SQL数据

47、查询,4) 字符匹配,谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下： NOT LIKE ESCAPE 其含义是查找指定的属性列值与相匹配的元组。可以是一个完整的字符串，也可以含有通配符%和_。 %(百分号) 代表任意长度（长度可以为0）的字符串。 _(下横线) 代表任意单个字符。 例14 ：查所有姓刘的学生的姓名、学号和性别 SELECT Sname, Sno, Ssex (not 表示不姓刘的同学) FROM Student WHERE Sname not LIKE 刘,3.2 SQL数据查询,例15 ：查姓“欧阳”且全名为三个汉字的学生的姓名 SELECT Sname FROM

48、 Student WHERE Sname LIKE 欧阳_ _ 例16 ：查名字中第二字为“阳”字的学生的姓名和学号 SELECT Sname, Sno FROM Student WHERE Sname LIKE _ _阳% 如果用户要查询的匹配字符串本身就含有%或_，比如要查名字为 DB_Design的课程的学分，应如何实现呢？这时就要使用 ESCAPE 短语对通配符进行转义了,3.2 SQL数据查询,例17 ：查DB_Design课程的课程号和学分 SELECT Cno, Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE ESCAP

49、E 短语表示为换码字符，这样匹配串中紧跟在后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的”_”字符。 例18：查以”DB_”开头，且倒数第三个字符为i的课程的详细情况 SELECT * FROM Course WHERE Cname LIKE DB_%i_ ESCAPE,3.2 SQL数据查询,5) 涉及空值的查询,例19：某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号 ： SELECT Sno, Cno FROM SC WHERE Grade IS NULL 例20：查所有有成绩的记录的学生学号和

50、课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL,3.2 SQL数据查询,6) 多重条件查询,逻辑运算符AND和OR可用来联结多个查询条件。如果这两个运算符同时出现在同一个WHERE条件子句中，则AND的优先级高于OR，但用户可以用括号改变优先级。 例21 ：查CS系年龄在20岁以下的学生姓名 SELECT Sname FROM Student WHERE Sdept=CS AND Sage20,3.2 SQL数据查询,三、 对查询结果排序,如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果

51、。用户也可以用ORDER BY子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查询结果，其中升序ASC为缺省值。 例22 ：查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列 SELECT Sno, Grade FROM SC WHERE Cno=1 GROUP BY Grade DESC,3.2 SQL数据查询,四、 使用集函数,为了进一步方便用户，增强检索功能，SQL提供了许多集函数，主要包括： COUNT(DISTINCT|ALL *) 统计元组个数 COUNT(DISTINCT|ALL ) 统计一列中值的个数 SUM(DISTINCT|ALL ) 计

52、算一列值的总和（此列必须是数值型） AVG(DISTINCT|ALL ) 计算一列值的平均值（此列必须是数值型） MAX(DISTINCT|ALL ) 求一列值中的最大值 MIN(DISTINCT|ALL ) 求一列值中的最小值,3.2 SQL数据查询,例24 ：查询学生总人数 SELECT COUNT(*) FROM Student 例25 ：查询选修了课程的学生人数 SELECT COUNT(DISTINCT Sno) FROM SC 例26 ：计算1号课程的学生平均成绩 SELECT AVG(Grade) FROM SC WHERE Cno=1 例27 ：查询学习1号课程的学生最高分数

53、SELECT MAX(Grade) FROM SC WHERE Cno=1,3.2 SQL数据查询,五、 对查询结果分组,GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组 。 对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值 例28 ：查询各个课程号与相应的选课人数 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno 该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，

54、然后对每一组作用集函数COUNT以 求得该组的学生人数,3.2 SQL数据查询,如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件 例29 ：查询信息系选修了3门以上课程的学生的学号 SELECT Sno FROM SC WHERE Sdept=IS GROUP BY Sno HAVING COUNT(*)3 WHERE子句与HAVING短语的根本区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组,3.2 SQL数据查询,2多表查询,一、连接查询 （1）、等

55、值与非等值连接查询 例30 ：查询每个学生及其选修课程的情况 SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno 如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student.Sno=SC.Sno,3.2 SQL数据查询,2)、 复合连接,上面连接查询中，WHERE子句中只有一个条件，即用

56、于连接两个表的谓词。WHERE子句中有多个条件的连接操作，称为复合条件连接 ： 例31 ：查询选修2号课程且成绩在90分以上的所有学生 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade90,3.2 SQL数据查询,二、 嵌套查询,1）带有IN谓词的子查询 例 32：查询与“刘晨”在同一个系学习的学生 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN SELECT Sdept FROM Student W

57、HERE Sname=刘晨,3.2 SQL数据查询,2）、带有比较运算符的子查询,带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用、 =、等比较运算符。 例33：查询选修了课程名为信息系统的学生学号和姓名 SELECT Sno, Sname FROM Student WHERE Sno IN （SELECT Sno FROM SC WHERE Cno = （SELECT Cno FROM Course WHERE Cname=信息系统,3.2 SQL数据查询,3）、带有ANY或ALL谓词的子查询,例34：查询其他系中比IS系任一学

58、生年龄小的学生名单 SELECT Sname, Sage FROM Student WHERE Sage IS ORDER BY Sage DESC,3.2 SQL数据查询,4）、带有EXISTS谓词的子查询,EXISTS代表存在量词彐。带有EXISTS谓词的子查询不返回任何实际数据，它只产生逻辑真值“true或逻辑假值“false。 例35、查询所有选修了1号课程的学生姓名 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1) 使用存在量词EXISTS后，若内层查询结

59、果非空，则外层的WHERE子句返回真值，否则返回假值,3.2 SQL数据查询,5）、集合查询,每一个SELECT语句都能获得一个或一组元组。若要把多个SELECT语句的结果合并为一个结果,可用集合操作来完成。集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。 使用UNION将多个查询结果合并起来，形成一个完整的查询结果时，系统会自动去掉重复的元组。需要注意的是，参加UNION操作的各数据项数目必须相同；对应项的数据类型也必须相同。 例36 查询计算机科学系的学生及年龄不大于19岁的学生 SELECT * FROM Student WHERE Sdept=CS UNI

60、ON SELECT * FROM Student WHERE Sage=19,3.3 SQL数据更新,1插入数据,一、插入单个元组 INSERT INTO (,.) VALUES ( ,.) 如果某些属性列在INTO子句中没有出现，则新记录在这些列上将取空值。但必须注意的是，在表定义时说明了NOT NULL的属性列不能取空值。否则会出错如果INTO子句中没有指明任何列名，则新插入的记录必须在每个属性列上均有值 例1 插入一条选课记录(95020，1) INSERT INTO SC(Sno, Cno) VALUES (95020, 1,3.3 SQL数据更新,二插入子查询结果,INSERT IN