数据库原理与应用-课件副本.ppt

数据库系统原理及应用,2,第一章：绪论,主要内容 本章主要介绍数据库的基本概念，数据管理技术的产生和发展；数据模型的概念及组成，概念模型和三种主要的数据库模型；数据库系统结构。,3,第一节：数据库系统概述,一、数据库的基本概念 1、数据(data) 数据是描述事物的符号记录。 2、数据库(database,简称DB) 数据库是指长期存储在计算机内，有组织的、大量的、可 共享的数据集合。 3、数据库管理系统 (Database Management System，简称DBMS) 是用来帮助用户在计算机上建立、使用和管理数据库的软 件系统。,4,第一节：数据库系统概述,其主要功能： 数据定义功能(DDL) 数据操纵功能(DML): DML = Data Manipulation Language，数据操纵语言，命令使用户能够查询数据库以及操作已有数据库中的数据的计算机语言。具体是指是SELECT查询、UPDATE更新、INSERT插入、DELETE删除。 其他管理：向数据库系统提供一组管理和控制程序，保障数据库的安全、通信与其它管理事务。 4、数据库系统(Database system，简称DBS) 数据库系统是指在计算机系统中引入数据库后的系统，一 般由数据库、数据库管理系统(及开发工具)、应用系统、 数据库管理员(DBA)和用户构成。,5,第一节：数据库系统概述,二、数据管理技术的产生和发展 1、人工管理阶段（20世纪50年代中期以前） 数据不保存 数据需由应用程序自己管理 数据不共享 数据不具有独立性,6,第一节：数据库系统概述,2、文件系统阶段(20世纪50年代后期-60年代中期) 数据可以长期保存 由文件系统管理数据 数据共享性差，冗余度大 数据独立性低,7,第一节：数据库系统概述,3、数据库系统阶段(20世纪60年代后期) 数据结构化 数据的共享性高，冗余度低，易扩充 数据独立性高 数据由DBMS统一管理和控制 安全性、完整性、并发控制、数据库恢复,8,第二节：数据模型,一、数据模型 1、概念 数据模型是现实世界数据特征的抽象，通俗地讲就是现实世界的模拟。 2、分类(应用目的) 概念数据模型：按用户的观点对数据和信息建模。 逻辑数据模型：按计算机的观点对数据建模。,现实世界,概念模型,数据模型,认识抽象,转换,9,第二节：数据模型,二、数据模型的组成要素 1、数据结构：是所研究的对象类型的集合。一类是与数据类型、内容、性质有关的对象。另一类是与数据之间联系有关的对象。 2、数据操作：是指对数据库中各种对象的实例允许执行的操作的集合，包括操作及有关的规则。 3、数据的约束条件：完整性规则的集合。 三、概念模型 1、信息世界中的基本概念 (1)实体：客观存在并可相互区别的事物。 (2)属性：实体所具有的某一特性。 (3)码：唯一标识实体的属性集。,10,第二节：数据模型,(4)域：属性的取值范围。 (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图) 表示方法： 实体型：用矩形表示 属性：用椭圆形表示 联系：用菱形表示 例：学生（学号，姓名，性别，出生日期） 班级（班号，所在专业） 课程（课程号，名称，专业） 教师（职工号，姓名，性别，职称）,12,第二节：数据模型,这些实体之间的联系如下： 一门课程可以有若干个教师讲授，而每一个教师只讲授一门课程。 一门课程可以同时有若干个学生选修，一个学生也可以同时选修多门课程。 一个班级中有若干名学生，而每个学生只在一个班级中学习。,13,第二节：数据模型,14,练习,某图书馆有多种图书，其中每一种图书仅由一家出版 社出版，而每一个出版社可以出版多种图书。该馆规 定：每位读者可以借阅多种图书，每种图书也可以被 多人借阅。 请根据以上描述，构造出该图书馆的E-R图。,15,答案,16,第二节：数据模型,四、luoji数据模型 非关系模型(层次、网状)、关系模型、面向对象模型 非关系模型：实体：记录 属性：数据项（字段） 联系：记录之间的联系 非关系模型中数据结构的单位是基本层次联系。 基本层次联系：两个记录以及它们之间的一对多（包括一 对一）的联系。,Ri：双亲节点 Rj：子女节点 Lij：一对多(包括一对一)联系名,17,第二节：数据模型,1、层次数据模型(IMS) (1)层次数据模型的数据结构 层次模型的两个条件： 有且只有一个结点没有双亲结点(根结点)。 根以外的其他节点有且只有一个双亲结点。,18,第二节：数据模型,多对多联系在层次模型中的表示： 冗余节点法、虚拟节点法,冗余节点法：浪费存储空间，有潜在的不一致性。 虚拟节点法：改变节点的存储位置可能引起虚拟节点中指针的修改。,19,第二节：数据模型,(2)层次数据模型的数据操纵与完整性约束 数据操纵：查询、插入、删除和修改 插入：没有双亲节点，不能插入子女节点 删除：删除双亲节点，相应子女节点也被删除。 修改：应修改所有相应的记录，保证数据一致性。 (3)层次模型的优缺点 优点： a.数据模型简单，比较容易使用。 b.提供了良好的完整性支持。,20,第二节：数据模型,c.对于实体间联系是固定的，且预先定义好的应用系统，性能优于关系模型，不次于网状模型。 缺点： a.对于非层次联系表示方法笨拙。 b.对插入和删除的限制比较多。 c.查询子女节点必须通过双亲节点。 d.由于结构严密，层次命令趋于程序化。,21,第二节：数据模型,2、网状数据模型(DBTG) (1)网状模型的数据结构 允许一个以上的结点无双亲。 一个节点可以有多于一个的双亲。 允许两个节点之间有多种联系。,22,第二节：数据模型,(2)网状数据模型的操纵与完整性约束 数据操纵：查询、插入、删除和更新 插入：允许插入尚未确定双亲节点值的子女节点。 删除：允许只删除双亲节点值。 (3)网状数据模型的优缺点 优点 a.更直接描述现实世界 b.具有良好的性能，存取效率效高。 缺点 a.DLL语言极其复杂。 b.数据独立性较差。,23,第二节：数据模型,3、关系数据模型 (1)关系数据模型的数据结构 实体以及实体之间的联系都是用关系来表示的。 关系：二维表（规范化的） 元组：行 属性：列(属性名唯一) 主码：唯一确定一个元组的属性组。 域：属性的取值范围。 分量：元组中的一个属性值。 关系模式：对关系的描述。 关系名(属性名1，属性名2，),24,第二节：数据模型,学生,课程,选修,学生,课程,选修,m,n,25,第二节：数据模型,(2)关系数据模型的操纵与完整性约束 操纵：select、insert、delete、update 实体完整性：主码唯一且不为空。 参照完整性 自定义完整性 (3) 关系数据模型的优缺点 优点 a.数据结构简单、清晰，用户易懂易用。 b.存取路径对用户透明，具有更高的数据独立性。 缺点 查询效率不如非关系数据模型。,26,第二节：数据模型,4、面向对象数据模型 与关系数据模型相比较： （1）数据结构 关系数据模型：关系、元组 面向对象数据模型：类、实例 （2）数据操纵 关系数据模型：关系的运算 面向对象数据模型：方法和消息 （3）完整性约束 关系数据模型：完整性约束方法 面向对象数据模型：完整性约束消息,27,第三节：数据库系统结构,一、数据库系统的模式结构 1、数据库系统模式的概念 是数据库中全体数据的逻辑结构和特征的描述。 2、数据库系统的三级模式结构 模式：也称逻辑模式,是数据库数据在逻辑级上的视图。 外模式：也称子模式,它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述。 内模式：也称存储模式,它是数据物理结构和存储方式的描述,是数据在数据库内部的表示方式。,28,第三节：数据库系统结构,2、数据库的二级映象功能与数据独立性 外模式/模式映象 保证了数据库系统的逻辑独立性。 模式/内模式映象 保证了数据库系统的物理独立性。,29,第三节：数据库系统结构,二、数据库系统的体系结构 1、单用户数据库系统 整个数据库系统（应用程序、DBMS、数据）都装在 一台机器上，由一个用户独占，不能共享使用。 2、主从式结构的数据库系统 一个主机带多个终端的多用户结构。 3、分布式结构的数据库系统 数据库系统中的数据在逻辑上是一个整体，但分布 在计算机网络的不同节点上。 4、客户/服务器结构的数据库系统 集中的服务器结构和分布的服务器结构。,30,练习,1、数据管理技术经历了( )、 ( )和( )阶段。 2、数据模型构成的三个要素为( )、 ( )和( )。 3、按照数据结构的类型命名来分，数据模型分为( )、 ( ) 、 ( )和( )。 4、概念模型的表示方式，最为常用的是P.P.S.Chen于1976年提出的( )。 5、数据库系统的三级模式结构包括( )、 ( )和( )，在这三个级别之间提供了两层映像 ( )和( )。 6、数据库系统中的数据独立性包括( )和( )。,31,作业,某医院病房计算机管理中需要如下实体: 科室：科室名，电话，科室主任名 病房：病房号，床位号,所属科室名 医生：姓名，职称，工作证号，所属科室名 病人：病历号，姓名，性别，诊断，主管医生，病房号 其中，一个科室可以有多个病房、多个医生，一个病房只能属于 一个科室，一个医生只能属于一个科室，但可以负责多个病人的 诊治，一个病人的主管医生只能有一个。试根据要求，用E-R图 画出该系统的概念模型。,32,第2章 关系数据库,主要内容 本章主要介绍关系模型的基本概念。即关系模型 的数据结构、关系操作和关系的完整性。,33,第1节：关系模型概述,一、关系数据结构 二、关系操作 1、操作特点：采用集合操作方式，即操作的对象和结果都是 集合。这种操作方式被称为一次一集合的方式 2、关系操作：选择、投影、连接、除、并、交、差等查询操 作和增、删、改操作。 3、关系数据语言 关系代数 关系演算(元组关系演算、域关系演算) 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(研究生集合)=张三、李四 则笛卡尔积为：D1D2,36,第2节：关系数据结构,二、关系： 1、基本概念 是有意义的笛卡尔积的子集。用R(D1,D2,Dn)表 示。R是关系的名字，n是关系的目或度。(单元关 系，二元关系)。 2、关系的码 超码：若某一个关系R中某属性(组)能唯一将R中的各个元组区分开，则称该属性(组)为R的一个超码。 侯选码：若某一个关系R中某属性(组)能唯一将R中的各个元组区分开，并不含多余的属性，则称该属性(组)为R的一个侯选码。,37,第2节：关系数据结构,主码：从关系R的侯选码中选定一个作为主码。 全码：包含了关系中全部属性的侯选码，称为全码。 外部码：设F是关系R的一个属性(组)，但不是关系R的主码，如果F与关系S的主码KS相对应，则称F为关系R的一个外部码。关系R被称为参照关系。关系S被称为被参照关系或目标关系。 主属性：主码的各属性称为主属性。 非码属性：不包含在任何侯选码中的属性。,38,第2节：关系数据结构,3、关系的性质： 列是同质的。 不同的属性要给予不同的属性名。 列的顺序可以任意交换。 任意两个元组不能完全相同。 行的顺序可以任意交换。 每一个分量都必须是不可分的数据项(规范化)。,39,第2节：关系数据结构,三、关系模式 关系模式就是对关系的描述。其可以表示为： R(U，D，dom，F) R：关系名 U：属性名集合 D：属性的域 Dom ：属性向域的映象集合。 F：属性间数据的依赖关系集合。 关系模式可简记为：R(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,第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与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中选择出若干个属性列组成一个新的关系。投影操作是从列的角度进行的运算。 记作： A(R) A为R中的属性列。 投影操作后，不仅取消了原有关系中的某些列，而且还可能取消某些元组。,51,例如：要查询学生关系在学号、姓名、年龄3个属性上的投影，可以表示为 学号，姓名，年龄（学生）,52,3、连接 连接是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。 记作：R S AB,53,第4节：关系代数,两种最为常用的连接： 等值连接：为“=” 的连接运算称为等值连接。 自然连接：是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。,学生,选修,54,第4节：关系代数,4、除 象集：给定一个关系R(X，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种运算来表达。 例1：查询姓名为“王一”的男生记录。 姓名=“王一” 性别=“男”(学生) 例2：查询王一同学“0001”号课程成绩。 成绩(姓名=“王一”(学生) 课程号=“0001” (选修) ) 例3：查询选修了全部课程的学生学号。 学号，课程号(选修) 课程号(课程),58,练 习,用关系代数语言完成下述查询操作： 查询信息系所有男生 查询所有男生的姓名 查询“数据库”课程的选修学生名单 查询所有学生的学号、姓名，课程名和成绩 查询选修了全部课程的学生的学号 查询所有“数据库”课程不及格的学生姓名和分数 查询“王敏”同学选修了哪些课程,59,练 习,S,C,SC,60,答 案,系=“信 息” 性别=“男”(s) 姓名(性别=“男”(s) 姓名(课程名=“数据库”(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、把投影同其前后的双目运算结合起来。 5、把某些选择同在它前面要执行的笛卡尔积运算结合起来成为 一个连接运算。 6、找出公共表达式。,62,第6节：关系数据库管理系统,一、关系数据库管理系统(RDBMS) 1、关系数据库管理系统 简称为关系系统。是指支持关系模型的系统。 2、关系系统的必备条件 满足关系模型的数据结构 数据库是由表构成的，且系统中只有表这种结构。 支持选择、投影和连接运算，并且不需要定义任何物理存取路径。,63,第6节：关系数据库管理系统,二、关系系统的分类 1、最小关系系统 支持关系数据结构和选择、投影、连接三种操作。 2、关系完备系统 支持关系数据结构和所有的关系代数操作。 3、全关系系统 支持关系模型的所有特征。,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概述,一、什么是SQL SQL(Structured Query Language)结构化查询语言，是关 系数据库的标准语言。 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的特点 集数据定义、操纵、数据控制与一体。 高度非过程化。 操纵对象与结果均为元组的集合（关系）。 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(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,第二节：数据定义,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）索引的用途 提高查询速度、保证数据唯一性、加快表连接速度 （3）创建索引的原则 由DBA创建，由DBMS自动选择和维护。 是否创建取决于表的数据量大小和对查询的要求 对一个基本表，不要建立过多的索引,73,第二节：数据定义,2、建立索引 Create unique cluster index On (，) Unique:唯一索引 Cluster：建立聚蔟索引，表中各行的物理顺序与索引键值的逻辑（索引）顺序相同。 次序：ASC(升序)、DESC(降序)，默认为：ASC 示例1：按姓名的升序建立聚蔟索引 Create cluster index name on student(姓名) 示例2：按入学成绩降序建立唯一索引 Create unique 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 姓名,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 逻辑：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 学分=3 Select 课程名,学分 from course where 学分 between 1 and 3 Select 课程名,学分 from course where 学分 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 null 3、对查询结果进行排序 select order by asc|desc 示例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) 求一列值中的最小值 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 ：作用的对象是组，即表示对分组后的记录进行筛选。,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门以上课程的学生学号 Select 学号 from sc group by 学号 having count(*)3,82,第三节：查询,二、多表查询（连接查询） 1、命令及运算符 Select where . =、=、=、!=、between and 2、连接查询的分类 按连接的运算符分类 等值连接与非等值连接两类 按连接的类型分类 内连接、外连接和交叉连接,83,第三节：查询,3、内连接：只返回与连接条件相匹配的元组。 示例1：查询所有学生的选课情况 Select student.*,sc.* from student,sc where student.学号 =sc.学号 示例2：查询选修“数据库”的学生的学号及成绩 Select x.学号,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、外连接 不但返回与连接条件相匹配的元组，而且还会根据外连接类 型不同返回与连接条件不匹配的元组。 左外连接、右外连接、全外连接 示例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 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,第三节：查询,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 课 程名=“数据库”) 不相关子查询：子查询的查询条件不依赖于父查询。,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 student 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 student 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.课程号) 四、集合查询 集合运算包括并(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 * from 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(学号、课程号、成绩)，试利用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.课程号=1 select 姓名,2002-year(出生日期) as 年龄 from student where 系=信息 order by (2002-year(出生日期) desc select 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.学号 select avg(成绩) from student s,sc where s.系=工管 and s.学号=sc.学号 and sc.课程号=4,95,练 习,统计所有课程的选修人数及平均分 查询王一同学选修课程的总学分是多少 查询同时选修了数据库课程与数据结构课程的学生的学号 查询选修了1号课程而没有选修3号课程的学生学号 查询至少选修了xg00102同学选修的全部课程的学生学号,96,答 案,select c.课程名,count(*),avg(sc.成绩) from sc,course c where sc.课程号=c.课程号 group by c.课程名 select sum(c.学分) from course c,sc,student s where s.学号=sc.学号 and sc.课程号=c.课程号 and s.姓名=王一 select distinct sc.学号 from course c,sc where sc.课程号=c.课程号 and c.课程名=数据库 and sc.学号 in (select sc.学号 from sc,course c where c.课程名=数据结构 and sc.课程号=c.课程号) select distinct 学号 from sc where 课程号=1 and 学号 not in (select 学号 from sc where 课程号=3) select 学号 from student s where 学号xg00102 and not exists(select 课程号 from sc sc1 where 学号=xg00102 and not exists(select * from sc sc2 where s.学号=sc2.学号 and sc1.课程号=sc2.课程号),97,第四节：数据更新,一、插入数据 1、插入单个元组 Insert into (,) Values (,) 示例：新增一门课程，课程号为9，课程名为ERP，先行 课课程号为3，学分为1分。 Insert into course values(“9”,”ERP”,”3”,1) 示例：“xg00102”选修了“4”号课程 Insert into sc (学号,课程号) values (“xg00102”,”4”),98,第四节：数据更新,2、插入子查询结果 Insert into (,) 子查询 示例：将所有信息系的全体学生插入到st表中。（假设st表的结构student表完全一样） Insert into st select * from student where 系=信息 示例：工管系的所有学生均选修了“4”号课程。 Insert into sc (学号,课程号) select s.学号, “4” from student where s.系=“工管“,99,第四节：数据更新,二、修改数据 Update set =,= where 1、修改某个元组的值 示例：将xg00101的4号课程的成绩由70改为80 Update sc set 成绩=80 where 学号=“xg00101” and 课程号=“4” 2、修改多个元组的值 示例：将所有员工的基本工资和奖金项目清空 Update gz set 基本工资=0,奖金=0,100,第四节：数据更新,示例：计算所有员工的实发工资 Update gz set 实发工资=基本工资+奖金 示例：计算所有员工的实发工资总额 Select sum(实发工资) as 实发工资总额 from gz 示例：给所有职称为“总工程师”的人奖金加800元 Update gz set 奖金=奖金+800 where 职称=“总工程师” 3、带子查询的修改语句 将信息系的全体学生的成绩清空 Update sc set 成绩=0 where “信息”=(select 系 from student where student.学号=sc.学号) Update sc set 成绩=0 where 学号 in (select 学号 from student where 系=信息),101,第四节：数据更新,三、删除数据 Delete from where 1、删除某个元组的值 示例：删除学号为xg00201的学生记录 Delete from student where 学号=“xg00201” 2、删除多个元组的值 示例：删除sc表中的所有记录 Delete fr