数据库开发技术-第2章关系数据库系统理论基础.ppt

第2章,关系数据库系统理论基础,内容提要,2.1关系模型概述,2.2关系数据结构及形式化定义,2.3 关系的完整性,2.4 关系代数,2.5关系数据库标准语言SQL,2.6 关系规范化理论,2.1关系模型概述,关系模型是建立在数学概念上的，与层次模型、网状模型相比，关系模型是一种最重要的数据模型。它主要由关系数据结构、关系操作集合、关系完整性约束三部分组成。实际上，关系模型可以理解为用二维表格结构来表示实体及实体之间联系的模型，表格的列表示关系的属性，表格的行表示关系中的元组。 关系模型允许定义三类完整性约束：实体完整性、参照完整性和用户定义的完整性。,2.2关系数据结构及形式化定义,在关系模型中，数据是以二维表的形式存在的，这是一种非形式化的定义。但关系理论是以集合代数理论为基础的，因此这里使用集合代数的形式给出关系的数学定义。 （1）域（Domain） （2）笛卡尔积（Cartesian Product） （3）关系（Relation）,域,域是一组具有相同数据类型的值的集合，域中数据的个数称为域的基数。 D1=王丽丽，张亭，李中 D1是姓名的集合，基数为3 D2=英语系，中文系 D2是系别的集合，基数是2,笛卡尔积,两个域D1、D2的笛卡尔积定义为： 多个域的笛卡尔积为： 笛卡尔积的基数是构成该积所有域的基数累乘积。,关 系,笛卡尔积 的子集称作在域D1,D2，,Dn上的关系，记作： R(D1,D2,Dn) 关系的相关概念 候选键（Candidate Key） 主键（Primary Key） 主属性（Primary Attribute） 外键（Foreign Key）,基本关系（基本表或基表）：实际存在的表，它是实际存储数据的逻辑表示。 查询表：查询结果对应的表。 视图表：有基本表或其他视图导出的表，是虚表，不对应实际存储的数据。,关系的三种类型,2.2.2 关系的性质,关系是一种规范化了的二维表中行的集合。为了使相应的数据操作简化，在关系模型中对关系进行了限制。 因此关系具有以下六条性质： （1）列是同质的，即每一列中的分量是同一类型的数据，来自同一个域。 （2）关系中的任意两个元组不能相同。 （3）关系中不同的列来自相同的域，每一列中有不同的属性名。 （4）关系中列的顺序可以任意互换，不会改变关系的意义。,（5）行的次序和列的次序一样，也可以任意交换。 （6）关系中每一个分量都必须是不可分的数据项，元组分量具有原子性。,2.2.3关系模式,关系模式指出了关系由哪些属性组成。关系模式是静态的、稳定的，而关系是动态的、不断变化的，它是关系模式在某一时刻的状态和内容。关系模式是型，关系是值。 一组关系模式的集合构成了关系数据库模式。,2.3 关系的完整性,关系模型的完整性规则是指对关系的某种约束条件。为了维护数据库中数据与现实世界的一致性，对关系数据库的插入、删除和修改操作必须有一定的约束条件，这就是关系的完整性。关系的完整性有三类：实体完整性、参照完整性和用户定义的完整性。,实体完整性规则是指若属性A是基本关系R的主属性，则属性A不能取空值。 如果主码是由若干个属性的集合构成，则要求构成主码的每一个属性的值都不能取空值。,2.3.1 实体完整性,2.3.2 参照完整性,如果关系R2的外码X与关系R1的主码相对应， 则外码X的每个值必须在关系Rl中主码的值中找到，或者为空值。 实体完整性是为了保证关系中主键属性值的正确性，而参照完整性是为了保证关系之间能够进行正确的联系。两个关系能否进行正确的联系，外键起着很重要的作用。,参照完整性规则,数据库模式导航图 一个含有主码和外码依赖的数据库模式可以通过模式导航图来表示。,2.3.3 用户定义完整性,用户定义的完整性就是用户按照实际的数据库应用系统运行环境要求，针对某一具体关系数据库的约束条件。例如某个属性“成绩”的取值范围必须在0100之间。用户定义完整性反映某一具体应用所涉及的数据必须满足的语义要求。,2.4 关系代数,关系代数是一种抽象的查询语言，用对关系的运算来表达查询。每个运算都以一个或者多个关系作为他的运算对象，并生成另外一个关系作为该运算的查询结果。 关系代数的基本有两类：一类是传统的集合运算，另一类是专门的关系运算。其运算符包括四类：集合运算符（、-、）、专门的关系运算符（、）、算术比较符（、）和逻辑运算符（、）。,2.4.1 传统的集合运算,传统的集合运算包括并、交、差和笛卡尔积。 (1)并 关系R和关系S的所有元组合并，再删去重复的元组，组成一个新关系，称为R和S相并，记为 RS。 (2)差 关系R和关系S的差是由属于R而不属于S的所有元组组成的集合，即关系R中删去与S关系中相同的元组，组成一个新关系，记为RS 。 (3)关系的交 关系R和关系S的交是由既属于R又属于S的元组组成的集合，即在两个关系R与S中取相同的元组，组成一个新关系，记为 RS 。,（4）笛卡尔积 两个分别为n目和m目的关系r和s的笛卡尔积是一个n+m目元组的集合。 元组的前n列是关系r的一个元组，后m列是关系s的一个元组 若关系r有kr个元组，关系s有ks个元组，则关系r和s的笛卡尔积有krks个元组。记作 ： rs = trtstrrtss ,传统的集合运算,Class关系 ClassNo ClassName instiute grade ClassNum AC070 会计学08(3)班 会计学院 2008 46 CS0701 计算机07(1)班 信息学院 2007 48 ISO802 信息系统08(2)班 信息学院 2008 43,Course关系 CourseNo CourseName creditHour courseHour priorCourse AC001 基础会计 48 3 null CN028 大学语文 48 3 null CS012 操作系统 80 5 null CS015 数据库系统 64 4 CS012,2.4.2 专门的关系运算,专门的关系运算包括：选择、投影、连接、除。 下面介绍关系运算的定义： （1）选择（Selection） （2）投影（projection） （3）连接（join） （4）除,专门的关系运算,(1)选择 从关系中找出满足给定条件的所有元组称为选择。其中的条件是以逻辑表达式给出的。 选择运算记为F（R），其中R为一个关系，F为布尔函数，该函数中可以包含算术比较符（，）和逻辑运算符（ ， ）,专门的关系运算,选择举例 例如，在数据库ScoreDB中，查找2007级的所有班级情况grade=2007(Class),ClassNo ClassName instiute grade ClassNum AC0703 会计学08(3)班 会计学院 2007 46 CS0701 计算机07(1)班 信息学院 2007 48,例如，在数据库ScoreDB中，查找所有1992年及以后出生 的女学生情况 ，year(birthday)=1992sex=女(Student),StudentNo StudentName sex birthday native nation classNo 0703010 李宏冰 女 1992-03-09 太原 蒙古族 AC0703,（2）投影 从关系中挑选若干属性组成新的关系称为投影。这是从列的角度进行的运算，相当于对关系进行垂直分解。 如果新关系中包含重复元组，则要删除重复元组。 投影运算记为X（R），其中R为一个关系，X为一组属性名或属性序号组，属性序号是对应属性在关系中的顺序编号。,专门的关系运算,专门的关系运算,投影举例 例如，在数据库ScoreDB中，查找所有学生的姓名和民族 studentName, nation(Student),例如，在数据库ScoreDB中，查找所有“蒙古族”学生的 姓名和籍贯,StudentName nation 李小勇 汉族 王红 汉族 王宏冰 蒙古族 刘方晨 傣族 王红敏 蒙古族,StudentName native 王宏冰 太原 王红敏 上海,studentName, native(nation=蒙古族(Student),（3）连接 连接是将两个关系的属性名拼接成一个更宽的关系，生成的新关系中包含满足连接条件的元组。运算过程是通过连接条件来控制的。 连接 连接操作是从关系R和S的笛卡尔积中选取属性值满足某一 操作的元组，记为 这里i和j分别是关系R和S中第i和第j个属性的序号。 如果是等号“，该连接操作称为“等值连接”。,专门的关系运算,自然连接 自然连接是除去重复属性的等值连接，它是连接运算的一个特例，是最常用的连接运算。 自然连接记为 其中R和S是两个关系，并且具有一个或多个同名属性。,专门的关系运算-连接,F 连接 F 连接操作是从关系R和S的笛卡尔积中选取属性值满足某一公式F的元组，记为 这里 F 是形 F1 F2 Fn的公式， 每个FP是形为 i j 的式子， 而 i 和 j 应分别为 R和 S的第 i、第 j个分量的序号。,专门的关系运算-连接,关系代数,专门的关系运算 例如，在数据库ScoreDB中，查找所有2008级的“蒙古族”学生的姓名,StudentName 王红敏,分析： nation=蒙古族(Student)可以找到所有蒙古族学生的情况，但关系Student中没有年级的信息，因此必须将关系Student与关系Class关联起来。, 根据模式导航图可知，关系Student与关系Class可通过外码classNo关联起来，这种外码引用关系可通过自然连接表示 Student Class =Student.classNo=Class.classNo(StudentClass), 最后的查询可表达为： studentName(nation=蒙古族(Student) grade=2008(Class),关系代数查询综合举例,给定一个查询需求，构造其关系代数表达式的步骤 明确该查询涉及到哪些属性； 明确该查询涉及到哪些关系； 根据数据库模式导航图，通过多对一联系(或一对多联系)把所有涉及的关系连接起来，每一个多对一联系(或一对多联系)都可以表示为外码属性的自然连接。,关系代数查询综合举例,ScoreDB数据库中，查找“蒙古族”学生所修各门课程的情况，要求输出学生姓名、课程名和成绩。 分析： 该查询共涉及4个属性，分别是民族nation、姓名studentName、课程名courseName和成绩score，其中，nation属性用于选择条件notion=蒙古族。 共涉及3个关系，分别是学生关系Student、课程关系Course和成绩关系Score。 成绩关系Score分别通过外码studentNo和courseNo与学生关系Student和课程关系Course建立多对一的联系 。,studentName, courseName, score(nation=蒙古族(Student Score) Course),关系代数查询综合举例,ScoreDB数据库中，查找2007级的“南昌”籍同学修读 了哪些课程，要求输出学生姓名、课程名。 分析： 该查询共涉及4个属性，分别是年级grade、籍贯native、姓名studentName和课程名courseName，其中年级grade和籍贯native用于选择条件。 共涉及3个关系，分别是班级关系Class、学生关系Student和课程关系Course。 学生关系Student与班级关系Class之间是多对一联系 ；学生关系Student和课程关系Course之间是多对多联系，并借助关系Score才能建立。因此，该查询还需涉及Score关系。,studentName, courseName(grade=2007Class native=南昌Student) Score) Course),关系代数查询综合举例,例如，SCDB数据库中，查找“吴文君”老师教过的2008级 学生的姓名。 分析： 该查询共涉及3个属性，分别是职工名teacherName、年级grade和学生姓名studentName ，职工名teacherName和年级grade都是用于选择条件。 共涉及3个关系，分别是教师关系Teacher、班级关系Class和学生关系Student。 学生关系Student与班级关系Class之间是多对一联系 ；学生关系Student和教师关系Teacher之间是多对多联系，这种多对多联系不是直接通过一个联系关系就能建立 。,studentName(grade=2008Class Student) SC) CourseClass) teacherName=吴文君Teacher),关系代数查询综合举例,例如，SCDB数据库中，查找“吴文君”老师在“操作系统” 课程中教过的“信息学院”学生的姓名。 分析： 该查询共涉及4个属性，分别是职工名teacherName、课程名courseName、学院名称instituteName和学生姓名studentName，职工名teacherName、课程名courseName和学院名称instituteName都是用于选择条件。 共4个关系，分别是教师关系Teacher、课程关系Course、学院关系Institute和学生关系Student。 学生关系Student与班级关系Class之间是多对一联系。班级关系Class又与学院关系Institute之间是多对一联系 。学生关系Student和教师关系Teacher之间是多对多联系，这种多对多联系不是直接通过一个联系关系就能建立。,studentName( ( (instituteName=信息学院Institute Class) Student) SC) (CourseClass courseName=操作系统Course) teacherName=吴文君Teacher),2.5关系数据库标准语言SQL,SQL(Structure Query Language)全称是结构化查询语言,是1974年IBM圣约瑟实验室的Boyce和Chamberlin为关系数据库管理系统System-R设计的一种查询语言，当时称为SEQUEL语言（Structured English Query Language），后简称为SQL。,2.5.1 SQL语言基本知识,（1）SQL的特点 综合统一 高度非过程化 面向集合的操作方式 以同一种语法结构提供两种使用方式，一种是自含式语言，以独立交互式使用，另一种是嵌入式语言，主要嵌入到其他高级语言中使用。 不是一个应用程序开发语言，只提供对数据库的操作能力，不能完成屏幕控制、菜单管理等功能。 书写简单、易学易用。,（2）SQL语言的组成 数据定义语言（Data Definition Language，DDL） 数据操纵语言（Data Manipulation Language，DML） 数据控制语言（Data Control Language，DCL）,2.5.2 数据定义命令,SQL语言的数据定义命令用于定义表(CREATE TABLE)、定义视图(CREATE VIEW)和定义索引(CREATE INDEX)等。 1.定义基本表 2. 修改基本表 3.删除基本表 4. 建立索引 5. 删除索引,定义基本表,CREATE TABLE ( 列级完整性约束条件 , 列级完整性约束条件 , ) 列级完整性约束条件 NOT NULL PRIMARY KEY UNIQUE DEFAULT FOREIGN KEY REFERENCES CHECK,数据类型,int或integer :整数，4个字节 decimal或numeric:数字数据类型， 格式：decimal(数据长度，小数位数) float 或 real: 浮点数 datatime: 日期时间型，如2008-5-6 char: 固定长度字符类型，格式：char(n), n介于18000. 字符型数据要用或”括起来。,创建表,【例】在“test1”数据库中创建“学生表”。 CREATE TABLE 学生表 ( 学号 varchar(12) PRIMARY KEY, 姓名 nchar(20) NOT NULL, 性别 nchar(1) check(sex in(男,女) , 出生日期 datetime, 入学日期 datetime， 院系名称 nchar(20) , 民族 nchar(20) default 汉族 ),外键约束,CREATE TABLE authors ( authorid int PRIMARY KEY, authorname char (20), address char (30) ) CREATE TABLE book ( bid int NOT NULL PRIMARY KEY, bname char (8) NOT NULL, authorid int FOREIGN KEY REFERENCES authors(authorid) ),CREATE TABLE Student ( - 定义学生表Student studentNo char(7) NOT NULL -学号 studentName varchar(20) NOT NULL , -姓名 sex nchar(1) , -性别 birthday datetime , -出生日期 native varchar(20) , -籍贯 nation varchar(30) default 汉族 ,-民族 classNo char(6) , -所属班级 CONSTRAINT StudentPK PRIMARY KEY (studentNo), CONSTRAINT StudentFK FOREIGN KEY (classNo) REFERENCES Class(classNo), CONSTRAINT StudentCK CHECK (sex in(男,女) ),表级约束,列之间的约束,create table tab2 ( id int primary key, fld2 int, fld3 int, constraint t2_ck check(fld2fld3) ),2.5.2 数据定义命令,SQL语言的数据定义命令用于定义表(CREATE TABLE)、定义视图(CREATE VIEW)和定义索引(CREATE INDEX)等。 1.定义基本表 2. 修改基本表 3.删除基本表 4. 建立索引 5. 删除索引,修改表结构,通过ALTER TABLE命令来修改表的结构，如扩充列等。 修改基本表操作的语法为（为要修改的表名）： 增加列（新增一列的值为空值）： ALTER TABLE ADD 删除列 ALTER TABLE DROP COLUMN 修改列的数据类型： ALTER TABLE ALTER COLUMN 增加约束： ALTER TABLE ADD CONSTRAINT 删除约束： ALTER TABLE DROP ,修改表结构举例,例： ALTER TABLE student ADD snation char (10) ALTER TABLE TempTable ALTER COLUMN xname char(10),2.5.2 数据定义命令,SQL语言的数据定义命令用于定义表(CREATE TABLE)、定义视图(CREATE VIEW)和定义索引(CREATE INDEX)等。 1.定义基本表 2. 修改基本表 3.删除基本表 4. 建立索引 5. 删除索引,删除表,DROP TABLE table_name 例： DROP TABLE student,2.5.2 数据定义命令,SQL语言的数据定义命令用于定义表(CREATE TABLE)、定义视图(CREATE VIEW)和定义索引(CREATE INDEX)等。 1.定义基本表 2. 修改基本表 3.删除基本表 4. 建立索引 5. 删除索引,什么是索引,汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等 我们可以根据拼音或偏旁部首，快速查找某个字词,Indexes Use Key Values to Locate Data （根据索引键查找定位数据行）,Index Pages（索引页）,什么是索引,什么是索引,SQL Server中的数据也是按页存放 索引：是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据 。 索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。 索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。,索引类型,唯一索引：唯一索引不允许两行具有相同的索引值 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空 聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个 非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个,如何创建索引,CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name ON table_name (column_name),UNIQUE表示唯一索引，可选 CLUSTERED、NONCLUSTERED表示聚集索引还是 非聚集索引，可选,使用T-SQL语句创建索引的语法：,唯一索引,聚集索引或非聚集索引,CREATE NONCLUSTERED INDEX IX_writtenExam ON stuMarks(writtenExam),在stuMarks表的writtenExam列创建索引：,建立索引的原则,什么情况不适合建索引 表记录太少 经常插入、删除、修改的表 对于查询很少涉及的列或数据值较少的列 什么情况适合建索引 对于按范围查询的列 经常参与查询的列 主键和外键,2.5.2 数据定义命令,SQL语言的数据定义命令用于定义表(CREATE TABLE)、定义视图(CREATE VIEW)和定义索引(CREATE INDEX)等。 1.定义基本表 2. 修改基本表 3.删除基本表 4. 建立索引 5. 删除索引,删除索引,DROP INDEX 例： DROP INDEX IX_writtenExam,2.5.3 数据查询语言,数据库查询是数据库的核心操作。SQL提供了功能强大的SELECT语句，通过查询到做可以得到所需要的信息。 （1）单表查询 （2）连接查询 （3）嵌套查询 （4）集合查询,SELECT 语句的格式,SELECT ALL|DISTINCT , FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC SQL基本结构包括3个子句： SELECT子句 对应投影运算，指定查询结果中所需要的属性或表达式 FROM子句 给出查询所涉及的表，表可以是基本表或视图 WHERE子句 对应选择运算，指定查询结果元组所需要满足的选择条件 SELECT和FROM是必须的，其他是可选的,DISTINCT参数-消除重复元组,需要消除重复元组，使用DISTINCT参数 例 查询所有学院的名称。 SELECT institute FROM Class 上述查询不消除重复元组 消除重复元组，查询结果如图3-12所示 SELECT DISTINCT institute FROM Class,目标列表达式,可以是列名 也可以一个运算表达式 也可以用SQL提供的表达式 COUNT(*) 统计记录条数 COUNT(列名) 统计列值的个数 SUM(列名) 计算数值列的总和 AVG(列名) 计算数值列的平均值 MAX(列名) 计算数值列的最大值 MIN(列名) 计算数值列的最小值,单表查询,选择表中的若干列 查询全部列 查询经过计算的列 选择表中的若干元组 对查询结果排序 使用集函数,查询全部列,可使用两种方法： 将所有的列在SELECT子句中列出(可以改变列的显示顺序)； 使用*符号，*表示所有属性，按照表定义时的顺序显示所有属性 例 查询班级表的全部信息。 SELECT classNo, className, classNum, grade, institute FROM Class 或 SELECT * FROM Class,查询经过计算的列,有一个书店将图书信息存在book表内： book(bno,bname,press,price) 现在书店进行促销活动，所有图书85折出售，请给出图书的所有信息以及打折后的价格 SELECT *, price*0.85 FROM book,给属性列取别名,可为属性列取一个便于理解的列名，如用中文来显示列名 为属性列取别名特别适合经过计算的列 例 查询所有班级的所属学院、班级编号和班级名称，要求用中文显示列名 SELECT institute 所属学院, classNo 班级编号, className 班级名称 FROM Class 该查询可使用AS关键字取别名： SELECT institute AS 所属学院, classNo AS 班级 编号, className AS 班级名称 FROM Class,选择表中的若干元组,WHERE子句可实现关系代数中的选择运算 WHERE常用的查询条件有： 比较大小：、=、(或!=) 确定范围：BETWEEN.AND 确定集合：IN、NOT IN 字符匹配 LIKE 、 NOT LIKE 空值查询：IS NULL、IS NOT NULL 逻辑运算：AND、OR、NOT,（1）比较大小,使用比较运算符、=、(或!=) 例 查询2007级的班级编号、班级名称和所属学院。 SELECT classNo, className, institute FROM Class WHERE grade=2007 例 查询score表中课程号为c001 成绩不及格的记录 SELECT * FROM score WHERE courseno=c001 AND degree 60,（2）确定范围,BETWEEN.AND用于查询属性值在某一个范围内的元组 NOT BETWEEN.AND用于查询属性值不在某一个范围内的元组 BETWEEN后是属性的下限值，AND后是属性的上限值 例 在选课Score表中查询成绩在8090分之间的同学学号、课程号和相应成绩 SELECT studentNo, courseNo, score FROM Score WHERE score BETWEEN 80 AND 90 该查询也可以使用逻辑运算AND实现,（3）确定集合,IN用于查询属性值在某个集合内的元组 NOT IN用于查询属性值不在某个集合内的元组 IN后面是集合，可以是具体的集合，也可以是查询出来的元组集合。 例 在选课Score表中查询选修了“001”、“005”或“003”课程的同学学号、课程号和相应成绩。 SELECT studentNo, courseNo, score FROM Score WHERE courseNo IN (001, 005, 003) 该查询也可以使用逻辑运算OR实现,（4）字符匹配,匹配查询用谓词LIKE实现，LIKE 匹配串可以是完整的字符串，也可以是通配符 % 代表任意长度的字符串 _ 代表任意单个字符 SELECT * FROM student WHERE sno LIKE 200909_ _ SELECT * FROM book WHERE bno LIKE %数据库%,字符匹配-转义字符,如果用户要查询的字符串本身就含有%或_，就需要用到转义字符 查询C_Language课程的课程号，课程名和学分 SELECT cno, cname, ccredit FROM course WHERE cname LIKE C_Language ESCAPLE ,(5) 涉及空值的查询,在score（选课表）中将目前没有成绩的记录选出来 SELECT * FROM score WHERE score IS NULL 注意：IS 不能用等号（=）代替,（6）多重条件查询-逻辑运算符,SQL提供AND、OR和NOT逻辑运算符分别实现逻辑与、逻辑或和逻辑非运算 例 在选课Score表中查询选修了“001”、“005”或“003”课程的同学学号、课程号和相应成绩 SELECT studentNo, courseNo, score FROM Score WHERE courseNo=001 OR courseNo=005 OR courseNo=003,对查询结果排序,使用ORDER BY 子句实现排序运算，其语法为： ORDER BY ASC | DESC , ASC | DESC, . 其中： , , .可以是列名、函数或表达式 缺省按升序(ASC)排序 按降序排序，必须指明DESC选项 该运算含义是： 在查询结果中首先按的值进行排序 在值相等的情况下再按值排序 依此类推,排序查询举例,例 在学生Student表中查询“女”学生的学号、姓名、所属班级编号和出生日期，并按班级编号的升序、出生日期的降序排序输出。 SELECT studentNo, studentName, classNo, birthday FROM Student WHERE sex=女 ORDER BY classNo, birthday DESC,使用集函数,COUNT(DISTINCT|ALL *) 统计记录条数 COUNT(DISTINCT|ALL ) 统计列值的个数 SUM(DISTINCT|ALL ) 计算数值列的总和 AVG(DISTINCT|ALL ) 计算数值列的平均值 MAX(DISTINCT|ALL ) 计算数值列的最大值 MIN(DISTINCT|ALL ) 计算数值列的最小值 DISTINCT: 表示在计算时取消指定列中的重复值 ALL: 默认值，表示不取消重复值,使用集函数举例(1),查询选课表（score）中有多少学生选了课 查询选课表（score）中课程号为c023的课程平均分,使用集函数举例(1),查询选课表（score）中有多少学生选了课 SELECT COUNT(DISTINCT sno) FROM score 查询选课表（score）中课程号为c023的课程平均分 SELECT AVG(score) FROM score WHERE cno=c023,使用集函数举例(2),查询选课表（score）中有多少学生选了课程号为c023的课程 查询选课表（score）中课程号为c023的课程已经给出分数的人数,使用集函数举例(2),查询选课表（score）中有多少学生选了课程号为c023的课程 SELECT COUNT(*) FROM score WHERE cno=c023 查询选课表（score）中课程号为c023的课程已经给出分数的人数 SELECT COUNT(score) FROM score WHERE cno=c023,对查询结果进行分组-group by,GROUP BY短语格式如下： GROUP BY GroupColumn,GroupColumn HAVING FilterCondition GROUP BY子句将查询结果按某一列或多列值进行分组，值相等的为 一组。 查询结果分组的目的是为了细化集函数作用的对象。如果未对查询结果分组，则集函数作用于整个查询结果；若进行了分组，集函数将作用于每一个组，即每一组都有一个函数值。,GROUP BY 和 HAVING 子句,使用GROUP BY子句可按一列或多列分组，还可以用HAVING进一步限定分组的条件。 GROUP BY子句一般跟在WHERE子句之后，没有WHERE子句时，跟在FROM子句之后；HAVING子句必须跟在GROUP BY 之后，不能单独使用。在查询中是先用WHERE子句限定元组，然后进行分组，最后再用HAVING子句剔除不需要的分组。,分组统计举例,例：求每个学生选课的考试成绩平均分。 说明：在此查询中，选按学号属性进行分组，然后再计算每个学号的平均成绩。 例：在选课表中求每个选课门数为4门的学生的总分和平均分。,分组统计举例,例：求每个学生选课的考试成绩平均分。 SELECT 学号, AVG(成绩) FROM 选课 GROUP BY 学号 说明：在此查询中，选按学号属性进行分组，然后再计算每个学号的平均成绩。 例：在选课表中求每个选课门数为4门的学生的总分和平均分。 SELECT 学号,SUM(成绩) AS 总分,AVG(成绩) AS 平均分 FROM 选课 GROUP BY 学号 HAVING COUNT(*)=4,分组统计举例,求平均成绩在80分以上的各课程的课程号与平均成绩。 SELECT 课程号,AVG(成绩) FROM 选课 GROUP BY 课程号 HAVING AVG(成绩)80,分组查询限制,分组列必须是FROM子句指定的表中的实际列名，不能按照计算列分组 出现在分组查询SELECT 子句中每项对于每个分组只能有一个值，因此，选项只能为下列情形之一 常数 集函数，每个分组生成单个值 分组列，每个分组具有相同的值 select score.no, name, avg(degree) from score, student where student.no=score.no group by score.no 思考：上述查询正确吗？,分组统计查询练习,查询每个同学的学号、选课门数、平均分和最高分 查询获得的总学分(注：只有成绩合格才能获得该课程的学分)大于或等于28的同学的学号、姓名和总学分。,多表连接查询,联接是关系的基本操作之一，联接查询基于多个关系的查询。 连接运算是关系数据库中使用最广泛的一种运算，包括等值连接、自然连接、非等值连接、自表连接和外连接等 例：查询学生的选课信息，包括姓名，学号及成绩。 分析：本例的查询结果包括两个表“学生”与“选课”的属性，适用于联接查询。 SELECT 姓名,选课.学号,成绩 FROM 学生,选课 WHERE 学生.学号=选课.学号,因为两个表中均有“学号”字段，所以必须指明所属表。 学生.学号=选课.学号：连接条件,多表连接查询,可为参与连接的表取别名(称为元组变量)，在相同的属性名前加上表的别名。 将Student表取别名为a，Class表取别名为b，班级编号分别用a.classNo和b.classNo表示。本例可以改写为： SELECT studentNo, studentName, native, b.classNo, className FROM Student AS a, Class AS b WHERE a.classNo=b.classNo AND institute=会计学院 或者 SELECT studentNo, studentName, native, b.classNo, className FROM Student a, Class b WHERE a.classNo=b.classNo AND institute=会计学院 对于不同的属性名，可以不在属性名前加上表名(别名)。,非等值连接,【例】 查询score 表中的信息，包括学号，课程号，成绩和成绩等级（A、B、C、D、E）。 步骤1： 新建一个表grade,并插入5行数据: CREATE TALBE grade ( low int, upp int, rank char(1) ) INSERT INTO grade values (90,100,A) INSERT INTO grade values (80,89,B) INSERT INTO grade values (70,79,C) INSERT INTO grade values (60,69,D) INSERT INTO grade values (0,59,E),非等值连接,步骤2：建立查询 SELECT no, cno, degree, rank FROM score, grade WHERE degree BETWEEN low AND upp,自连接,多表查询时，除了不同表之间的连接，有时还会遇到同一张表自身作连接，称为自连接 SELECT c1.city,c1.no, FROM student c1, student c2 WHERE c1.ClassNo=c2.ClassNo and c2.StudentNo=101 此SQL语句查询与学号为101的同学同一个班的学生信息 注意：自连接容易出现多余数据，应在查询语句中加上适当的限制条件,自连接练习,查询每门课的先行课程的课程号和学分,连接JOIN,在一般的连接中，只有满足连接条件的元组才被检索出来，对于没有满足连接条件的元组是不作为结果被检索出来的。 SELECT s1.no, , o, s2.degree FROM student s1 JOIN score s2 ON s1.no=s2.no 上述查询的结果不会出现没有选课的学生的信息,左外连接,SELECT s1.no, , o, s2.degree FROM student s1 LEFT OUTER JOIN score s2 ON s1.no=s2.no,student 为左表，score为右表，连接结果中包含左关系中的所有元组，对于左关系中没有连接上的元组，其右关系中的相应属性用空值替代 此连接查询的结果将显示student表中所有同学的记录以及已经选了课的同学的选课信息。,右外连接,右外连接 连接结果中包含右关系中的所有元组，对于右关系中没有连接上的元组，其左关系中的相应属性用空值替代 例 使用右外连接查询每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出 SELECT className, institute, studentNo, studentName FROM Student a RIGHT OUTER JOIN Class b ON a.classNo=b.classNo ORDER BY className,全外连接,全外连接 连接结果中包含左、右关系中的所有元组 对左关系中没有连接上的元组，其右关系中的相应属性用空值替代 对右关系中没有连接上的元组，其左关系中的相应属性用空值替代 例 使用全外连接查询每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。 SELECT className, institute, studentNo, studentName FROM Class a FULL OUTER JOIN Student b ON a.classNo=b.classNo ORDER BY className,内连接,内连接使用INNER JOIN关键字 USE school SELECT ame, teacher.tname FROM course INNER JOIN teacher ON ( course.tno=teacher.tno),嵌套查询,一个SELECT FROM WHERE语句称为一个查询块。 一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。 上层的查询块称为外层查询（父查询），下层的查询块称为内层查询（子查询）。 嵌套查询的求解方法是由里向外进行处理。 子查询的结果用于建立父查询的查找条件。 子查询中不能有ORDER BY 子句。,带有比较运算的子查询,SELECT c1.city,c1.no, FROM student c1, student c2 where c1.city=c2.city and c2.no=101,SELECT city FROM student WHERE no=101 ; SELECT * FROM student WHERE city=?,问题提出: 如何查找和101同学相同城市的学生,采用自连接方式,通过两次 查询,带有比较运算的子查询,SELECT no, name, city FROM student WHERE city = ( select city from student where no=101),此类子查询通常返回单个值,带有IN谓词的子查询,例：查询女生学生选课信息，包括学号，课程号，成绩。 SELECT * FROM 选课 WHERE 学号 IN (SELECT 学号 FROM 学生 WHERE 性别=女),IN 后面的子查询通常返回多个值,带有ANY、ALL谓词的子查询,ANY、ALL为量词，ANY在进行比较运算时只要子查询中有一行能使结果为真，则结果为真；ALL则要求子查询中的所有行都使结果为真时，结果才为真。 SELECT * FROM 课程 WHERE 学分=ALL (SELECT 学分 FROM 课程) SELECT * FROM 课程 WHERE NOT 学分ANY (SELECT 学分 FROM 课程),EXIST一般在WHERE子句中，其后紧跟一个子查询，