《数据库原理与应用-课件副本.ppt》由会员分享，可在线阅读，更多相关《数据库原理与应用-课件副本.ppt（248页珍藏版）》请在三一办公上搜索。
1、数据库系统原理及应用,2,第一章：绪论,主要内容 本章主要介绍数据库的基本概念，数据管理技术的产生和发展；数据模型的概念及组成，概念模型和三种主要的数据库模型；数据库系统结构。,3,第一节：数据库系统概述,一、数据库的基本概念1、数据(data)数据是描述事物的符号记录。2、数据库(database,简称DB)数据库是指长期存储在计算机内，有组织的、大量的、可共享的数据集合。3、数据库管理系统(Database Management System，简称DBMS)是用来帮助用户在计算机上建立、使用和管理数据库的软件系统。,4,第一节：数据库系统概述,其主要功能：数据定义功能(DDL)数据操纵功能
2、(DML):DML=Data Manipulation Language，数据操纵语言，命令使用户能够查询数据库以及操作已有数据库中的数据的计算机语言。具体是指是SELECT查询、UPDATE更新、INSERT插入、DELETE删除。其他管理：向数据库系统提供一组管理和控制程序，保障数据库的安全、通信与其它管理事务。4、数据库系统(Database system，简称DBS)数据库系统是指在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统(及开发工具)、应用系统、数据库管理员(DBA)和用户构成。,5,第一节：数据库系统概述,二、数据管理技术的产生和发展1、人工管理阶段（20世纪5
3、0年代中期以前）数据不保存数据需由应用程序自己管理数据不共享数据不具有独立性,6,第一节：数据库系统概述,2、文件系统阶段(20世纪50年代后期-60年代中期)数据可以长期保存由文件系统管理数据数据共享性差，冗余度大数据独立性低,7,第一节：数据库系统概述,3、数据库系统阶段(20世纪60年代后期)数据结构化数据的共享性高，冗余度低，易扩充数据独立性高数据由DBMS统一管理和控制 安全性、完整性、并发控制、数据库恢复,8,第二节：数据模型,一、数据模型1、概念数据模型是现实世界数据特征的抽象，通俗地讲就是现实世界的模拟。2、分类(应用目的)概念数据模型：按用户的观点对数据和信息建模。逻辑数据模
4、型：按计算机的观点对数据建模。,现实世界,概念模型,数据模型,认识抽象,转换,9,第二节：数据模型,二、数据模型的组成要素1、数据结构：是所研究的对象类型的集合。一类是与数据类型、内容、性质有关的对象。另一类是与数据之间联系有关的对象。2、数据操作：是指对数据库中各种对象的实例允许执行的操作的集合，包括操作及有关的规则。3、数据的约束条件：完整性规则的集合。三、概念模型1、信息世界中的基本概念(1)实体：客观存在并可相互区别的事物。(2)属性：实体所具有的某一特性。(3)码：唯一标识实体的属性集。,10,第二节：数据模型,(4)域：属性的取值范围。(5)实体型：用实体名及其属性名集合来抽象和刻
5、画同类实体，称为实体型。(6)实体集：同型实体的集合。(7)联系：实体内部及实体间的联系。两个实体型之间的联系可以分为：一对一联系(1:1)：如果对于实体集A中的每一个实体，实体集B中至多有一个实体与之联系，反之亦然。一对多联系(1:n)：如果对于实体集A中的每一个实体，实体集B中有n(n0)个实体与之联系，反之，对于实体集B中的每一个实体，实体集A中至多只有一个实体与之联系。多对多联系(m:n)如果对于实体集A中的每一个实体，实体集B中有n(n0)个实体与之联系，反之，对于实体集B中的每一个实体，实体集A中也有m(m0)个实体与之联系。,11,第二节：数据模型,2、概念模型的表示方法(E-R
6、图)表示方法：实体型：用矩形表示属性：用椭圆形表示联系：用菱形表示例：学生（学号，姓名，性别，出生日期）班级（班号，所在专业）课程（课程号，名称，专业）教师（职工号，姓名，性别，职称）,12,第二节：数据模型,这些实体之间的联系如下：一门课程可以有若干个教师讲授，而每一个教师只讲授一门课程。一门课程可以同时有若干个学生选修，一个学生也可以同时选修多门课程。一个班级中有若干名学生，而每个学生只在一个班级中学习。,13,第二节：数据模型,14,练习,某图书馆有多种图书，其中每一种图书仅由一家出版社出版，而每一个出版社可以出版多种图书。该馆规定：每位读者可以借阅多种图书，每种图书也可以被多人借阅。请
7、根据以上描述，构造出该图书馆的E-R图。,15,答案,16,第二节：数据模型,四、luoji数据模型非关系模型(层次、网状)、关系模型、面向对象模型非关系模型：实体：记录 属性：数据项（字段）联系：记录之间的联系非关系模型中数据结构的单位是基本层次联系。基本层次联系：两个记录以及它们之间的一对多（包括一对一）的联系。,Ri：双亲节点Rj：子女节点Lij：一对多(包括一对一)联系名,17,第二节：数据模型,1、层次数据模型(IMS)(1)层次数据模型的数据结构层次模型的两个条件：有且只有一个结点没有双亲结点(根结点)。根以外的其他节点有且只有一个双亲结点。,18,第二节：数据模型,多对多联系在层
8、次模型中的表示：冗余节点法、虚拟节点法,冗余节点法：浪费存储空间，有潜在的不一致性。虚拟节点法：改变节点的存储位置可能引起虚拟节点中指针的修改。,19,第二节：数据模型,(2)层次数据模型的数据操纵与完整性约束数据操纵：查询、插入、删除和修改插入：没有双亲节点，不能插入子女节点删除：删除双亲节点，相应子女节点也被删除。修改：应修改所有相应的记录，保证数据一致性。(3)层次模型的优缺点优点：a.数据模型简单，比较容易使用。b.提供了良好的完整性支持。,20,第二节：数据模型,c.对于实体间联系是固定的，且预先定义好的应用系统，性能优于关系模型，不次于网状模型。缺点：a.对于非层次联系表示方法笨拙
9、。b.对插入和删除的限制比较多。c.查询子女节点必须通过双亲节点。d.由于结构严密，层次命令趋于程序化。,21,第二节：数据模型,2、网状数据模型(DBTG)(1)网状模型的数据结构允许一个以上的结点无双亲。一个节点可以有多于一个的双亲。允许两个节点之间有多种联系。,22,第二节：数据模型,(2)网状数据模型的操纵与完整性约束数据操纵：查询、插入、删除和更新插入：允许插入尚未确定双亲节点值的子女节点。删除：允许只删除双亲节点值。(3)网状数据模型的优缺点优点a.更直接描述现实世界b.具有良好的性能，存取效率效高。缺点a.DLL语言极其复杂。b.数据独立性较差。,23,第二节：数据模型,3、关系
10、数据模型(1)关系数据模型的数据结构实体以及实体之间的联系都是用关系来表示的。关系：二维表（规范化的）元组：行属性：列(属性名唯一)主码：唯一确定一个元组的属性组。域：属性的取值范围。分量：元组中的一个属性值。关系模式：对关系的描述。关系名(属性名1，属性名2，),24,第二节：数据模型,学生,课程,选修,学生,课程,选修,m,n,25,第二节：数据模型,(2)关系数据模型的操纵与完整性约束操纵：select、insert、delete、update实体完整性：主码唯一且不为空。参照完整性自定义完整性(3)关系数据模型的优缺点优点a.数据结构简单、清晰，用户易懂易用。b.存取路径对用户透明，具
11、有更高的数据独立性。缺点查询效率不如非关系数据模型。,26,第二节：数据模型,4、面向对象数据模型与关系数据模型相比较：（1）数据结构关系数据模型：关系、元组面向对象数据模型：类、实例（2）数据操纵关系数据模型：关系的运算面向对象数据模型：方法和消息（3）完整性约束关系数据模型：完整性约束方法面向对象数据模型：完整性约束消息,27,第三节：数据库系统结构,一、数据库系统的模式结构1、数据库系统模式的概念是数据库中全体数据的逻辑结构和特征的描述。2、数据库系统的三级模式结构模式：也称逻辑模式,是数据库数据在逻辑级上的视图。外模式：也称子模式,它是数据库用户能够看见和使用的局部数据的逻辑结构和特征
12、的描述。内模式：也称存储模式,它是数据物理结构和存储方式的描述,是数据在数据库内部的表示方式。,28,第三节：数据库系统结构,2、数据库的二级映象功能与数据独立性外模式/模式映象 保证了数据库系统的逻辑独立性。模式/内模式映象 保证了数据库系统的物理独立性。,29,第三节：数据库系统结构,二、数据库系统的体系结构1、单用户数据库系统整个数据库系统（应用程序、DBMS、数据）都装在一台机器上，由一个用户独占，不能共享使用。2、主从式结构的数据库系统一个主机带多个终端的多用户结构。3、分布式结构的数据库系统数据库系统中的数据在逻辑上是一个整体，但分布在计算机网络的不同节点上。4、客户/服务器结构的
13、数据库系统集中的服务器结构和分布的服务器结构。,30,练习,1、数据管理技术经历了()、()和()阶段。2、数据模型构成的三个要素为()、()和()。3、按照数据结构的类型命名来分，数据模型分为()、()、()和()。4、概念模型的表示方式，最为常用的是于1976年提出的()。5、数据库系统的三级模式结构包括()、()和()，在这三个级别之间提供了两层映像()和()。6、数据库系统中的数据独立性包括()和()。,31,作业,某医院病房计算机管理中需要如下实体:科室：科室名，电话，科室主任名病房：病房号，床位号,所属科室名医生：姓名，职称，工作证号，所属科室名病人：病历号，姓名，性别，诊断，主管
14、医生，病房号其中，一个科室可以有多个病房、多个医生，一个病房只能属于一个科室，一个医生只能属于一个科室，但可以负责多个病人的诊治，一个病人的主管医生只能有一个。试根据要求，用E-R图画出该系统的概念模型。,32,第2章 关系数据库,主要内容本章主要介绍关系模型的基本概念。即关系模型的数据结构、关系操作和关系的完整性。,33,第1节：关系模型概述,一、关系数据结构二、关系操作1、操作特点：采用集合操作方式，即操作的对象和结果都是 集合。这种操作方式被称为一次一集合的方式2、关系操作：选择、投影、连接、除、并、交、差等查询操 作和增、删、改操作。3、关系数据语言关系代数关系演算(元组关系演算、域关
15、系演算)SQL三、完整性约束 实体完整性、参照完整性和用户自定义完整性。,34,第2节：关系数据结构,一、域和笛卡尔积1、域：是一组具有相同数据类型的值的集合。2、笛卡尔积：给定一组域D1，D2Dn(可以完全不同，也可以部分或全部相同)，D1，D2Dn的笛卡尔积为：D1 D2 Dn=(d1，d2dn)|diDi，i=1,2,n其中每一个元素(d1，d2dn)叫做一个n元组，简称为元组。di叫做一个分量。若Di(i=1,2,n)为有限集，其基数为mi(i=1,2,n)，则D1 D2 Dn的基数为：m=,35,第2节：关系数据结构,例如：D1(导师集合)=王导、孙导 D2(研究生集合)=张三、李四
16、则笛卡尔积为：D1D2,36,第2节：关系数据结构,二、关系：1、基本概念是有意义的笛卡尔积的子集。用R(D1,D2,Dn)表示。R是关系的名字，n是关系的目或度。(单元关系，二元关系)。2、关系的码超码：若某一个关系R中某属性(组)能唯一将R中的各个元组区分开，则称该属性(组)为R的一个超码。侯选码：若某一个关系R中某属性(组)能唯一将R中的各个元组区分开，并不含多余的属性，则称该属性(组)为R的一个侯选码。,37,第2节：关系数据结构,主码：从关系R的侯选码中选定一个作为主码。全码：包含了关系中全部属性的侯选码，称为全码。外部码：设F是关系R的一个属性(组)，但不是关系R的主码，如果F与关
17、系S的主码KS相对应，则称F为关系R的一个外部码。关系R被称为参照关系。关系S被称为被参照关系或目标关系。主属性：主码的各属性称为主属性。非码属性：不包含在任何侯选码中的属性。,38,第2节：关系数据结构,3、关系的性质：列是同质的。不同的属性要给予不同的属性名。列的顺序可以任意交换。任意两个元组不能完全相同。行的顺序可以任意交换。每一个分量都必须是不可分的数据项(规范化)。,39,第2节：关系数据结构,三、关系模式关系模式就是对关系的描述。其可以表示为：R(U，D，dom，F)R：关系名U：属性名集合D：属性的域Dom：属性向域的映象集合。F：属性间数据的依赖关系集合。关系模式可简记为：R(
18、U)或R(A1,A2An),40,第3节：关系的完整性,一、实体完整性 若属性A是基本关系R的主属性，则属性A不能取空值。二、参照完整性1、基本概念外码参照关系被参照关系2、参照完整性若属性(组)F是基本关系R的外码，它与基本关系S的主码KS相对应(基本关系R和S不一定是不同的关系)，则对于R中每个元组在F上的值必须为：,41,第3节：关系的完整性,或者取空值或者等于S中某个元组的主码值例1：学生(学号，姓名，专业号)专业(专业号，专业名)例2：学生(学号，姓名，专业号)课程(课程号，课程名称)选修(学号，课程号，成绩)三、用户自定义完整性反映某一具体应用所涉及的数据必须满足的语义要求。,42
19、,第4节：关系代数,一、关系代数运算的三个要素1、运算对象：关系2、运算符号：集合运算符：、专门的关系运算符：、算术比较符：、逻辑运算符：、3、运算结果：关系,43,第4节：关系代数,二、传统的集合运算1、并：RS由属于R或属于S的元组组成。要求：关系R和S具有相同的目。相应的属性取至同一个域。2、差：R-S(要求同上)由属于R而不属于S的所有元组组成。3、交：RS(要求同上)由既属于R又属于S的元组组成。交运算可用差来表示：RS=R-（R-S）,44,假定有两个关系R与S是关系模式学生的实例,R与S如表2-8、2-9所示。,表2-8 关系R,表2-9 关系S,45,R与S的并集结果,46,R
20、与S的交集,R与S的交集,47,第4节：关系代数,4、广义笛卡尔积：R S两个分别具有n目和m目、k1 和 k2个元组的关系R和S的广义笛卡尔积是一个具有(n+m)列、(k1 k2)个元组的集合。该元组的前n列是关系R的一个元组，后m列是关系S的一个元组。,48,关系R与S笛卡尔积的结果,49,三、专门的关系运算1、选择选择又称为限制。它是在关系R中选择满足给定条件的诸元组。选择是从行的角度进行运算的。记作：F(R)F：表示选择条件，是一个逻辑表达式。例：查询学生关系中年龄为19岁的男生 性别=男AND年龄=19（学生）,50,第4节：关系代数,2、投影投影是从R中选择出若干个属性列组成一个新
21、的关系。投影操作是从列的角度进行的运算。记作：A(R)A为R中的属性列。投影操作后，不仅取消了原有关系中的某些列，而且还可能取消某些元组。,51,例如：要查询学生关系在学号、姓名、年龄3个属性上的投影，可以表示为 学号，姓名，年龄（学生）,52,3、连接连接是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。记作：R S AB,53,第4节：关系代数,两种最为常用的连接：等值连接：为“=”的连接运算称为等值连接。自然连接：是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。,学生,选修,54,第4节：关系代数,4、除象集：给定一个关系R(X
22、，Z)，x为R中属性组X上的一个分量。则x的象集表示为：R中属性组X上值为x的诸元组在Z上分量的集合。,学生 选修,55,第4节：关系代数,3的象集为(5,9),(9,5)4的象集为(6,8),(7,7)5的象集为(8,6),56,第4节：关系代数,除运算：给定关系R(X，Y)和S(Y，Z)，R与S的除运算得到一个新的关系P(X)，P是R中满足下列条件的元组在X属性列上的投影：元组在X上分量值x的象集Yx包含S在Y上的投影的集合。,RS,R,S,57,第4节：关系代数,四、五种基本的关系运算并、差、笛卡尔积、选择和投影为基本的关系运算。其他三种运算：交、连接和除运算均可以用这5种运算来表达。例
23、1：查询姓名为“王一”的男生记录。姓名=“王一”性别=“男”(学生)例2：查询王一同学“0001”号课程成绩。成绩(姓名=“王一”(学生)课程号=“0001”(选修)例3：查询选修了全部课程的学生学号。学号，课程号(选修)课程号(课程),58,练 习,用关系代数语言完成下述查询操作：查询信息系所有男生查询所有男生的姓名查询“数据库”课程的选修学生名单查询所有学生的学号、姓名，课程名和成绩查询选修了全部课程的学生的学号查询所有“数据库”课程不及格的学生姓名和分数查询“王敏”同学选修了哪些课程,59,练 习,S,C,SC,60,答 案,系=“信 息”性别=“男”(s)姓名(性别=“男”(s)姓名(
24、课程名=“数据库”(c)sc s)学号,姓名,课程名,成绩(s sc c)学号,课程号(sc)课程号(c)姓名,成绩(课程名=“数据库”(c)成绩60(sc)s)课程名(姓名=“王敏”(s)sc c),61,第5节：查询优化,例：查询选修了1024号课程的学生姓名1、SNAME(s.学号=sc.学号 sc.课程号=“1024”(sc s)2、SNAME(课程号=“1024”(sc s)3、SNAME(s 课程号=“1024”(sc)上述三种关系代数运算中，查询效率为321查询优化的策略1、选择尽可能先做。2、连接前对关系进行预处理。（索引或排序）3、同一关系的投影和选择运算同时进行。4、把投影
25、同其前后的双目运算结合起来。5、把某些选择同在它前面要执行的笛卡尔积运算结合起来成为 一个连接运算。6、找出公共表达式。,62,第6节：关系数据库管理系统,一、关系数据库管理系统(RDBMS)1、关系数据库管理系统简称为关系系统。是指支持关系模型的系统。2、关系系统的必备条件满足关系模型的数据结构 数据库是由表构成的，且系统中只有表这种结构。支持选择、投影和连接运算，并且不需要定义任何物理存取路径。,63,第6节：关系数据库管理系统,二、关系系统的分类1、最小关系系统支持关系数据结构和选择、投影、连接三种操作。2、关系完备系统支持关系数据结构和所有的关系代数操作。3、全关系系统支持关系模型的所
26、有特征。,64,P28/8答案,SNO(JNO=“J1”(SPJ)SNO(JNO=“J1”PNO=“P1”(SPJ)SNO(JNO=“J1”(SPJ)COLOR=“红”(P)JNO(SPJ)-(JNO(CITY=“天津”(S)SPJ CLOLOR=“红”(P)PNO，JNO(SNO=“S1”(SPJ)PNO(SNO=“S1”(SPJ),65,第3章：关系数据库标准语言,主要内容 本章主要讲述关系数据库标准语言SQL。主要包括数据定义、查询、数据更新、视图、数据控制以及嵌入式SQL的使用。,66,第一节：SQL概述,一、什么是SQLSQL(Structured Query Language)结构
27、化查询语言，是关系数据库的标准语言。1974年由boyce和chamberlin提出。1975-1979年IBM公司研制的System R实现了这种语言。1986年10月ANSI的数据库委员会将其作为关系数据库语言的美国标准(SQL-86)。1987年ISO也通过了这一标准。此后，ANSI不断完善SQL标准，推出了SQL-89、SQL-92、SQL-99标准。二、SQL语言的基本概念外模式：视图模式：基本表内模式：存储文件,67,第一节：SQL概述,三、SQL的组成数据定义：定义表、视图和索引。数据操纵：查询和更新。数据控制：安全性和完整性。嵌入式SQL四、SQL的特点集数据定义、操纵、数据控
28、制与一体。高度非过程化。操纵对象与结果均为元组的集合（关系）。SQL有自含式和嵌入式两种方式。语言简洁、易学易用(9个动词)。,68,第二节：数据定义,一、创建、修改和删除数据库1、创建数据库Create database 2、修改数据库alter database 3、删除数据库drop database 数据库：数据文件和日志文件。,69,第二节：数据定义,二、定义、删除与修改基本表1、定义基本表Create table(列级完整性约束条件,)（1）数据类型数值型：int、smallint、real、float、numberic等字符型：char(n)、varchar(n)位串型：bit(
29、n)、bit varying(n)日期型：date、time,70,第二节：数据定义,（2）完整型约束Primary key约束：主关键字约束Foreign key约束：外键约束Unique key约束Check 约束Not null 或null约束（3）示例：创建student基本表。该表中有学号、性别、出生日期和入学成绩四个字段，将学号设为主码，并要求性别只能输入“男”或“女”两个值。create table student(学号 c(5)primary key,性别 c(2)default 男 check(性别$男女)ERROR 性别输入错误！,出生日期 d,入学成绩 n(5,1),71
30、,第二节：数据定义,2、修改基本表Alter table add 完整性约束 drop modify 示例1：在student表中，增加一个字段“党员否”。alter table student add 党员否 L示例2：将“党员否”字段类型改为C型alter table student alter 党员否 c(2)示例3：将党员否字段删除alter table student drop 党员否,72,第二节：数据定义,3、删除基本表Drop table 三、建立和删除索引1、索引（1）含义 索引是在基本表中列上建立的一种数据库对象，其主要目的是提高数据的检索性能。（2）索引的用途 提高查询速
31、度、保证数据唯一性、加快表连接速度（3）创建索引的原则由DBA创建，由DBMS自动选择和维护。是否创建取决于表的数据量大小和对查询的要求对一个基本表，不要建立过多的索引,73,第二节：数据定义,2、建立索引Create unique cluster index On(，)Unique:唯一索引Cluster：建立聚蔟索引，表中各行的物理顺序与索引键值的逻辑（索引）顺序相同。次序：ASC(升序)、DESC(降序)，默认为：ASC示例1：按姓名的升序建立聚蔟索引Create cluster index name on student(姓名)示例2：按入学成绩降序建立唯一索引Create uniqu
32、e index rxcj on student(入学成绩 desc)示例3：按姓名和性别的升序建立索引Create index xmxb on student(姓名，性别)3、删除索引Drop index,74,第三节：查询,一、单表查询1、选择表中的若干列Select from 示例1：查询全体学生姓名与学号Select 姓名，学号 from student示例2：查询全体学生的全部字段项目Select*from student 示例3：查询全体学生的姓名和年龄Select 姓名，2004-year(出生日期)from student示例4：查询全体学生的姓名和年龄,改变列标题。Select
33、 姓名,2004-year(出生日期)as 年龄 from student,75,第三节：查询,2、选择表中的若干元组（1）消除取值重复的行Select distinctfrom示例1：查询选修了课程的学生学号Select distinct 学号 from sc示例2：观察命令的执行结果Select distinct 学号，课程号 from sc（2）查询满足条件的元组select where,76,第三节：查询,条件表达式中常用运算符号：比较：,=,(,!,!)范围：between and集合：in匹配：like(%:任意个字符，_:单个字符)空值：is null；is not null逻辑
34、：and，or，not示例1：显示所有男生记录Select*from student where 性别=“男”示例2：显示20岁以下的学生姓名及年龄Select 姓名，2007-year(出生日期)as 年龄 from student where 2007-year(出生日期)20,77,第三节：查询,示例3：查询学分在1-3之间的所有课程名及学分Select 课程名,学分 from course where 学分=1 and 学分=3Select 课程名,学分 from course where 学分 between 1 and 3Select 课程名,学分 from course wher
35、e 学分 in(1,2,3)示例4：查找所有姓王的学生的记录Select*from student where 姓名 like 王%示例5：查找姓名中第2个字是“小”的女同学Select*from student where 姓名 like _ _小%and 性别=女,78,第三节：查询,示例6：查询以“DB_”开头的课程Select*from course where 课程名 like“DB_%”escape“”示例7：查询没有成绩的学生的学号和课程号Select 学号,课程号 from sc where 成绩 is null3、对查询结果进行排序select order by asc|de
36、sc示例1：查询全体学生信息，结果按姓名降序排列。Select*from student order by 姓名 desc示例2：查询全体学生信息，结果按系升序排列，同一系内部按姓名升序排列Select*from student order by 系,姓名,79,第三节：查询,4、统计操作在中使用集函数：Count(distinct|all*)统计元组个数Count(distinct|all)统计一列中值的个数sum(distinct|all)求一列值的总和avg(distinct|all)求一列值的平均值max(distinct|all)求一列值中的最大值min(distinct|all)求
37、一列值中的最小值Distinct：表示在计算时要取消指定列中的重复值。All：缺省值，不取消重复值。,80,第三节：查询,示例1：查询信息系的学生人数Select count(*)from student where 系=“信息”示例2：查询选修了课程的学生人数Select count(distinct(学号)as 人数 from sc示例3：查询4号课程的最高分、最低分和平均分。Select max(成绩),min(成绩),avg(成绩)from sc where 课程号=“4”5、对查询的结果进行分组select group by having having：作用的对象是组，即表示对分组后
38、的记录进行筛选。,81,第三节：查询,示例1：统计各个系的学生人数。Select 系,count(*)from student group by 系 示例2：统计每位学生的所有课程的平均成绩Select 学号,avg(成绩)from sc group by 学号示例3：统计每门课程的最高分、最低分Select 课程号,max(成绩),min(成绩)from sc group by 课程号示例4：统计信息系和管理系的学生人数Select 系,count(*)as 学生人数 from student group by 系 having 系=“信息”or 系=“工管”示例5：查询选修了3门以上课程的
39、学生学号Select 学号 from sc group by 学号 having count(*)3,82,第三节：查询,二、多表查询（连接查询）1、命令及运算符Select where.=、=、=、!=、between and2、连接查询的分类按连接的运算符分类等值连接与非等值连接两类按连接的类型分类内连接、外连接和交叉连接,83,第三节：查询,3、内连接：只返回与连接条件相匹配的元组。示例1：查询所有学生的选课情况Select student.*,sc.*from student,sc where student.学号=sc.学号示例2：查询选修“数据库”的学生的学号及成绩Select x
40、.学号,x.成绩 from course c,sc x where c.课程号=x.课程号 and c.课程名=“数据库”示例3：查询“王一”的姓名、课程名及成绩Select s.姓名,c.课程名,x.成绩,from student s,sc x,course c where s.学号=x.学号 and c.课程号=x.课程号 and s.姓名=“王一”,84,第三节：查询,示例4：查询每一门课程的先修课的名称(自连接)Select a.课程号,a.课程名,b.课程名 from course a,course b where a.先行课=b.课程号4、外连接不但返回与连接条件相匹配的元组，而且
41、还会根据外连接类型不同返回与连接条件不匹配的元组。左外连接、右外连接、全外连接示例1：显示所有学生的姓名及选课情况（没有选课的学生 名单也要列出来）Select student.姓名,sc.课程号,sc.成绩 from student,sc where student.学号=sc.学号(*),85,第三节：查询,5、交叉连接（广义笛卡尔积）返回两个表中元组的交叉乘积。6、SQL-92中连接查询的表示方法将连接条件放在From子句的后面，基本格式为：R natural 连接类型 join S on 条件连接类型：Cross：笛卡尔积Left outer join：左外连接Right outer
42、join：右外连接Full outer join：全外连接,86,第三节：查询,三、嵌套查询在一个select查询语句的where子句或having子句中插入另一个查询语句称为嵌套查询。例如：查询选修了2号课程的学生名单。Select s.姓名 from student s,sc x where s.学号=x.学号 and x.课程号=“2”Select 姓名 from student where 学号 in(Select 学号 from sc where 课程号=“2”)上层的查询模块称为外层查询或父查询。下层的查询模块称为内层查询或子查询。子查询中不能使用order by 子句。,87,第
43、三节：查询,1、带有IN谓词的子查询示例1：查询与“王一”同一个系的学生姓名Select 姓名 from student where 系 in(select 系 from student where 姓名=“王一”)Select a.姓名 from student a,student b where a.系=b.系 and b.姓名=“王一”查询“数据库”课程的学生选修名单Select 姓名 from student where 学号 in(select 学号 from sc where 课程号 in(select 课程号 from course where 课程名=“数据库”)不相关子查询：
44、子查询的查询条件不依赖于父查询。,88,第三节：查询,2、带有比较运算符的子查询当确信子查询返回的是单值时，可使用比较运算符。示例1：查询与“王一”同一个系的学生姓名Select 姓名 from student where 系=(select 系 from student where 姓名=“王一”)3、带有some(any)或all谓词的子查询使用some或all时，必须同时使用比较运算符。示例1：查询其他系中比信息系所有学生年龄都小的学生的姓名。(即比信息系中年龄最小的还要小)Select 姓名 from student where 出生日期all(select 出生日期 from stu
45、dent where 系=“信息”)And 系“信息”,89,第三节：查询,Select 姓名 from student where 出生日期(select max(出生日期)from student where 系=“信息”）And 系“信息”带有some、all的子查询往往可以用集函数来代替，而且查询的效率更高。4、带有exists谓词的子查询该类子查询不返回任何数据，只产生逻辑真或假。示例1：查询选修了1号课程的学生姓名Select 姓名 from student where 学号 in(Select 学号 from sc where 课程号=“1”)Select 姓名 from stu
46、dent where exists(select*from sc where 学号=student.学号 and 课程号=“1”),90,第三节：查询,相关子查询：子查询的查询条件依赖于外层父查询的某个属性值。所有的带in、比较运算符、some和all谓词的子查询都可以用带exists的子查询替换。反之却不一定。示例2：查询选修了全部课程的学生姓名Select 姓名 from student where not exists(select*from course where not exists(select*from sc where 学号=student.学号 and 课程号=course
47、.课程号)四、集合查询集合运算包括并(union)、差(intersect)、交(except)其中差和交运算是SQL-92中规定的。,91,第三节：查询,示例1：查询选修了1号课程和3号课程的并集Select*from sc where 课程号=“1”union(Select*from sc where 课程号=“3”)Select*from sc where 课程号=“1”or 课程号=“3”示例2：查询选修了1号课程和3号课程的交集Select*from sc where 课程号=“1”and 学号 in(select 学号 from sc where 课程号=“3”)Select*fr
48、om sc where 课程号=“1”intersect(Select*from sc where 课程号=“3”)示例3：查询选修了1号课程和3号课程的差集Select*from sc where 课程号=“1”and 学号 not in(select 学号 from sc where 课程号=“3”)Select*from sc where 课程号=“1”except(select 学号 from sc where 课程号=“3”),92,练 习,XS数据库中有三个表，表名及结构分别为：Student(学号、姓名、性别、出生日期、系)Course(课程号、课程名、先修课课程号、学分)Sc(
49、学号、课程号、成绩)，试利用sql语句完成下列查询。查询信息系所有姓王的男生名单查询工管系所有选修1号课程的学生名单显示信息系学生姓名及年龄，并按年龄降序排列。查询王一同学所有选修课程的课程名及成绩查询信息系所有选课学生的名单统计王一同学的选课门数统计工管系学生的4号课程的平均成绩,93,答 案,select 姓名 from student where 系=信息 and 性别=“男”and 姓名 like 王%(注意：access里为王*)2.select s.姓名 from student s,sc where s.系=工管 and s.学号=sc.学号 and sc.课程号=1select
50、 姓名,2002-year(出生日期)as 年龄 from student where 系=信息 order by(2002-year(出生日期)descselect c.课程名,sc.成绩 from student s,course c,sc where s.学号=sc.学号 and c.课程号=sc.课程号 and s.姓名=王一select distinct s.姓名 from student s,sc where s.系=信息 and s.学号=sc.学号,94,答 案,select count(*)from student s,sc where s.姓名=王一 and s.学号=sc
