SQL语言与关系数据理论.ppt

第11章 Web数据库应用,教材：数据库技术及应用 2006年4月,宁可、吴菁、胡海编著,本章要点,关系数据库 结构化查询语言 数据库设计理论,目 录,2.1 关系数据库 2.1.1 关系数据结构 2.1.2 关系操作 2.1.3 关系的完整性 2.2 SQL语言 2.2.1 SQL概述及特点 2.2.2 SQL数据定义 2.2.3 SQL数据查询 2.2.4 SQL数据操纵 2.2.5 数据控制 2.3 关系数据理论 2.3.1 关系模式可能存在的问题 2.3.2 函数依赖及规范化小结,2.1 关系数据库,关系数据库系统是支持关系模型的数据库系统。关系模型由关系数据结构、关系操作集合和关系完整性约束三部分组成。关系数据库是表的集合，数据库中的表，数学家称为关系。关系数据库中还定义了关系操作和完整性约束，关系操作就是对表中的数据的处理、查询，完整性约束就是表中的数据要满足的条件，这些条件由数据库系统自动维护，产生违反这些约束条件的对数据的操作会被拒绝执行。,2.1.1 关系数据结构,在关系模型中，无论是实体还是实体之间的联系均由单一的结构即关系（表）来表示。关系模型是建立在集合代数的基础上的。1 关系的定义 在用户观点下，关系模型中数据的逻辑结构是一张二维表。二维表的每一列称为属性。每个属性有一个取值范围，称为值域。每个属性对应一个值域。下面是二维表的一个实例。,2 关系中的基本名词(1)关键字 能唯一标识一行记录或元组的一个或一组属性。(2)候选关键字（Candidate key）和主关键字（Primary key）能作为关键字的属性可能有很多，这些属性称为候选关键字。为数据管理方便，若一个关系有多个候选关键字时，指定其中一个为主关键字。(3)主属性（Primary Attribute）和非主属性（Non-key Attribute）候选关键字中的属性为主属性。不包含在任何候选关键字中的属性为非主属性。,3 数据库中关系的类型 关系可以有三种类型：基本关系（通常称为基本表或基表）查询表和视图表。(1)基本表 基本表是关系数据库中实际存在的表，它是实际存储数据的逻辑表。(2)查询表 它是从基本表中查询得到的，一般是临时表。(3)视图表 视图表是由基本表或其它视图表导出的表，它是为数据处理方便以及数据安全要求而设计的虚表，它一般是不存储数据的，而只是存储提取数据的条件，它的数据依赖于基本表。,4 基本关系的性质基本关系具有如下六条性质：(1)列是同性质的，即同一列中的分量是同一类型的数据。(2)关系中的列又称为属性，并赋予属性名。不同列的属性名不同。不同列可以取相同的数据类型。(3)列的次序可以任意交换。(4)任意两个元组不能完全相同。(5)行的次序可以任意交换。(6)列具有原子性，即每一列值是不可分的数据项,5 关系模式 关系模式是对关系的描述，可以理解为表的结构定义。关系实际上就是关系模式在某一时刻的状态或内容。也就是说，关系模式是型，关系是它的值。6 关系数据库 在关系模型中，实体以及实体间的联系都是用关系来表示。在一个给定应用中，所有实体及实体之间联系的集合构成一个关系数据库。关系数据库也有型和值之分。,2.1.2 关系操作,关系模型给出了关系操作的能力，但不对具体的RDBMS给出具体的语法要求。关系操作语言灵活方便，表达能力和功能强大。关系模型的数据操作采用集合操作方式，即操作对象和结果都是集合。关系模型中常用的关系操作包括数据查询、数据维护和数据控制三大功能。数据查询指数据检索、统计、排序等功能；数据维护指数据的增加、删除、修改等数据的更新功能；数据控制是为了保证数据的安全性和完整性而采用的数据存储控制及并发控制等功能。,2.1.3 关系的完整性,1 实体完整性 现实世界中的实体是可区分的，即它们具有某种唯一性标识。相应地，关系模型中以主键（主关键字）作为唯一性标识。主键中的属性即主属性不能取空值。所谓空值就是“不知道”或“无意义”的值。2 参照完整性 现实世界中的实体之间往往存在某种联系，在关系模型中实体及实体间的联系都是用关系来描述的。这样就自然存在着关系与关系间的引用。3.用户定义的完整性 用户定义的完整性是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。例如：某个属性的取值不能为空，学生考试的成绩取值范围在0到100之间。,2.2 SQL语言,SQL全称是“Structured Query Language”，即结构化查询语言，它目前是关系数据库的标准语言，它是在1974年由Boyce和Chamberlin提出的。经过不断修改、扩充和完善，SQL语言最终发展成为关系数据库的标准语言。由于它功能丰富、语言简洁、易学易用，所以深受用户和计算机工业界的欢迎，被各计算机公司和软件公司广泛采用。SQL标准的影响超出了数据库领域，SQL成为国际标准后，它在数据库以外的其他领域也得到了重视和采用。在未来一段相当长的时间，SQL将是数据库语言领域中的一个主流语言。,2.2.1 SQL概述及特点,1.SQL概述 SQL是一种结构化查询语言，其功能并不象名称所显示的那样仅仅是查询，而是一个通用的、功能强大的关系数据库语言。SQL从主要功能上可以分为4个部分：数据定义、数据查询、数据操纵和数据控制。它是一个综合的、通用的关系数据库查询语言。它的特点如下：(1)功能一体化(2)高度非过程化(3)以同一种语法结构提供两种使用方式(4)语言简洁，易学易用,2.SQL语言的基本概念 SQL支持三级模式结构，外模式对应于视图和部分基本表，模式对应于基本表，内模式对应于存储文件。基本表是独立存在的表，一个关系就是一个表。基本表是按数据全局逻辑模式建立的。全体基本表构成了数据库的模式。一个SQL模式（Schema）是表和约束的集合。视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图的数据，这些数据仍存放在导出视图的基本表中。因此，视图是一个虚表。,2.2.2 SQL数据定义,SQL的数据定义功能包括定义表、定义视图、定义索引和定义数据库。SQL的数据定义语句见下表。,1.基本表的定义和维护 SQL基本表的定义和维护功能包括表的定义、修改和删除。(1)定义基本表（CREATE TABLE）例2-2 建立一个“学生”表，它由学号、姓名、性别、年龄、所在系五个属性组成，年龄是整型数据，其它为字符型。CREATE TABLE 学生（学号 CHAR（5）,姓名 CHAR（20）,年龄 INT,性别 CHAR（2）,所在系 CHAR（20）,）,(2)修改基本表（ALTER TABLE）例2-6 删除“学生”表中“入学时间”列。ALTER TABLE 学生 DROP COLUMN 入学时间；(3)删除基本表 例2-7 删除学生表 DROP TABLE学生,2.索引的定义和维护 当你阅读一本厚书时，如果你想快速找到特定的内容，你就需要查看目录，找到你所关心的内容在书中的准确位置页码，然后直接翻到这个位置。索引对于表，就相当于书的目录。索引可以提高数据查询速度。可以对频繁查询的表增加索引。只有按有索引的列查询，索引才有作用。,(1)建立索引（CREATE INDEX）例2-8 为学生表建立索引，按学号升序建唯一索引。CREATE UNIQUE INDEX Stusno ON 学生（学号）(2)删除索引（DROP INDEX）例2-9 DROP INDEX 学生.Stusno,2.2.3 SQL数据查询,数据库查询是数据库的核心操作，查询就是从数据库中挑选、提取所需要的数据。SQL提供了SELECT语句进行数据库的查询，该语句使用方式灵活、功能强大。SELECT语句的语法格式为：SELECT ALL|DISTINCT,.FROM,.WHERE GROUP BY HAVING ORDER BY ASC|DESC；,1.简单查询 简单查询是指从一个表中进行查询，也叫单表查询。如选择一个表中的某些列、某些行。(1)查询指定列例2-10 查询全体学生的姓名与学号。学生表中有很多列：学号，姓名，年龄，性别，所在系，而下列语句只选择学号和姓名两列，即查询的结果是一个只包含两列的新关系。SELECT 学号，姓名 FROM 学生；,(2)查询全部列例2-11 查询学生表的全部列：SELECT*FROM 学生 也可以写为：SELECT学号，姓名，年龄，性别，所在系,(3)查询经过计算的值 SELECT子句可以包含表达式，运算对象可以是常量或属性。例2-12 查询5年之后学生的年龄。假如数据库中学生的年龄是今年的年龄。SELECT 学号，姓名，年龄+5 FROM 学生,(4)消除取值重复的行 一个表中两个本来不完全相同的行，选择部分列后，可能完全相同了。例2-13 查询选修了课程的学号。SELECT 学号 FROM 选课 如果要去掉重复行，必须指定短语DISTINCT：SELECT DISTINCT 学号 FROM 选课 取消了重复行，结果就是选了课的学生学号。,(5)查询满足条件的行 WHERE子句通过条件表达式描述表中行的选择条件。DBMS系统根据选择条件扫描表中的每一行，把不符合条件的行筛选掉。WHERE子句常用的查询条件，如下表所示：,比较 例2-14 查询信息系学生的名单 SELECT 姓名 FROM 学生 WHERE 所在系=信息系例2-15 查询所有20岁以下的学生姓名和年龄 SELECT 姓名，年龄 FROM 学生 WHERE 年龄20,例2-16 查询有成绩不及格的学生的学号 SELECT DISTINCT 学号 FROM 选课 WHERE 成绩60 确定范围例2-17 查询年龄在20到23岁（包括20和23）之间的学生姓名、年龄、所在系。SELECT 姓名，年龄，所在系 FROM 学生 WHERE 年龄BETWEEN 20 AND 23,确定集合例2-18 查询数学系或化学系学生的姓名、性别、所在系。SELECT 姓名，性别，所在系 FROM 学生 WHERE 所在系 IN（数学系，化学系）字符串的匹配。例2-19 查询姓刘的学生的姓名、性别、所在系。SELECT 姓名，性别，所在系 FROM 学生 WHERE姓名 LIKE 刘%,涉及空值的查询例2-22 查询选修了课程而没参加考试的学生名单及课程号。SELECT 学号，课程号 FROM 选课 WHERE成绩 IS NULL,多重条件查询 如果查询条件多于两个，则要使用逻辑运算符AND和OR连接多个条件，AND的优先级高于OR，但可使用括号改变运算顺序。例2-23 查询计算机系姓赵的学生。SELECT 姓名 FROM 学生 WHERE所在系=计算机系 AND 姓名 LIKE 赵%,(6)对查询结果排序 如果没有指定查询结果的显示顺序，DBMS通常按其数据录入的先后顺序（物理存储顺序）输出查询结果。用户也可以用ORDER BY子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查询结果，其中升序ASC为缺省值。例2-24 查询选修了3号课程的学生的学号及其成绩，查询结果按成绩的降序排列。SELECT 学号，成绩 FROM 选课 WHERE 课程号3 ORDER BY 成绩 DESC,(7)使用集合函数 为了进一步方便用户，增强检索功能，SQL提供了许多集函数，常用的集函数见下表。,例2-25 查询学生总人数。SELECT COUNT（*）FROM 学生例2-26 查询选修了课程的学生人数。SELECT COUNT（DISTINCT 学号）FROM 选课 学生每选修一门课，在选课中都有一条相应的记录，而一个学生一般都要选修多门课，为避免重复计算学生人数，必须在COUNT函数中用DISTINCT限定词。,(8)对查询结果分组 GROUP BY子句可以将查询结果表的各行按一列或多列的值分组，值相等的为一组。对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值，如上面的例题，否则，集函数将作用于每一个组，即每一组都有一个函数值。,例2-28 查询各个课程号与相应的选课人数。SELECT 课程号，COUNT（学号）FROM 选课 GROUP BY 课程号 该SELECT语句对课程表按课程号的取值进行分组，所有具有相同课程号的行为一组，然后对每一组作用集函数COUNT以求得该组的学生人数。,2.连接查询 一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有用的信息。前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询实际上是关系数据库中最主要的查询，主要包括等值连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询。,例2-31 查询每个学生及其选修课程的情况。学生选课情况存放在选课表中，所以本查询实际上同时涉及学生表和选课表两个表中的数据。这两个表之间的联系是通过两个表都具有的公共属性学号实现的。要查询学生及其选修课程的情况就必须将这两个表中学号相同的元组连接起来。这是一个等值连接。完成本查询的SQL语句为：SELECT 学生.*，选课.*FROM 学生，选课 WHERE 学生.学号选课.学号,3.嵌套查询 在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。在嵌套查询中，上层的查询块又称为外层查询或父查询或主查询，下层查询块又称为内层查询或子查询。SQL语言允许多层嵌套查询。即一个子查询中还可以嵌套其它子查询。嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了SQL的查询能力。,(1)带有IN谓词的子查询 带有IN渭词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。由于在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。例2-32 查询选修了“数学”课的学生姓名。学生名和课程名分别在“学生”和“课程”表中，因此不能通过简单的查询一个表而得到结果。实际上连接学生和课程的是“选课”表，因此这个查询涉及到三个表：学生、课程、选课。查询步骤如下：,在课程表中查询“数学”课的课程号。在选课表中找到选修了上一步得到的课程号的课程的学生学号。在学生表中找到学号包含在上一步所得到的学号集合中的学生姓名。SELECT 学号，姓名 FROM 学生 WHERE 学号 IN（SELECT 学号 FROM 选课 WHERE 课程号 IN（SELECT 课程号 FROM 课程 WHERE 课程=数学）,(2)带有比较运算符的子查询 带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用、2或等比较运算符。,例2-33 查询与“刘刚”同一个系的学生。这个查询分两步：首先在学生表找到“刘刚”所在的系。然后在学生表中查询所在系与上一不的查询结果相等的学生名单。SELECT 姓名FROM 学生WHERE 所在系=（SELECT所在系 FROM 学生 WHERE 姓名=刘刚）,2.2.4 SQL数据操纵,在对数据库进行操作时，除了经常查询数据库外，还要更新数据。SQL中数据更新包括插入数据（INSERT）、修改数据（UPDATE）和删除数据（DELETE）三条语句。插入语句（INSERT INTO）例2-34 将一个新学生记录（学号：95005；姓名：陈俊；性别：男；所在系：计算机；年龄：18岁）插入学生表中。INSERT INTO 学生 VALUES（95020，陈俊，男，18，计算机系）；,2.删除语句（DELETE）例2-35 删除学号为95001的学生记录。DELETE FROM 学生 WHERE 学号=95001；3.修改语句（UPDATE）例2-36 将学号为95001的学生年龄改为22岁。UPDATE 学生 SET 年龄=22 WHERE 学号=95001,2.2.5 SQL数据控制,这里所讨论的数据控制主要是指SQL语言的安全性控制功能，通过对数据库用户的使用权限加以限制而保证数据安全的重要措施。SQL语言的的数据控制语句包括授权（Grant）、收权或删除权限（Revoke）、拒绝访问（Deny）三种，权限的设置对象可以是数据库用户或用户组。,授权 例2-37 将查询成绩的权限授予所有人。GRANT SELECT ON TABLE 成绩 TO PUBLIC2.收权语句 数据库管理员DBA、数据库拥有者DBO或数据库对象的拥有者DBOO可以通过REVOKE语句将其他用户的数据操作权限收回。例2-38 将用户wp修改学生学号的权限收回。REVOKE UPDATE（学号）ON 学生 FROM wp,2.3 关系数据理论,针对一个具体的问题，应该如何构造一个适合于它的数据模式。这是数据库设计问题，确切地讲是数据库逻辑设计问题。关系模型有严格的数学理论基础，基于这种理论上的优势，关系模型可以设计的更加科学，关系数据库中的问题可以更好地解决。关系数据库规范化理论就是数据库逻辑设计工具。关系数据库的规范化理论就是解决如何设计出合理的关系数据库模式，也就是一个数据库中要定义哪些表，每个表有哪些属性，表之间有哪些联系，而尽量地减少数据冗余，消除插入、删除等存在的问题。,2.3.1 关系模式可能存在的问题,例如，要设计一个教学管理数据库，希望在该数据库中保存学生的学号、姓名、性别、年龄、系名、系主任和该学生选修的课程及成绩信息。若据此设计一个关系保存所有的数据，关系模式为：教学（学号，姓名，性别，年龄，系名，系主任，课程名，成绩）这个关系模式存在以下的问题：数据冗余大 插入异常 删除异常。更新异常,由于上述的问题，它是一个不合理的设计。一个合理的设计应该尽量减少以上的问题。只有找到问题存在的原因，才能找到解决办法。那么出现上述问题的原因是什么呢？这是因为这个关系模式中的数据之间的依赖关系所造成的。如果把这个单一的关系模式，分解成如下三个关系模式：学生（学号，姓名，性别，年龄，所在系）；系（系名，系主任）；选课（学号，课程名，成绩）,2.3.2 函数依赖及规范化,为了使数据库模式的设计更合理，人们研究了规范化理论。而函数依赖理论是关系规范化的理论基础。函数依赖反映了同一关系中属性间一一对应的约束。,1.关系模式的简化表示法 一般把关系模式看做一个三元组：R（U，F）其中，R是关系名，U是一组属性，F是属性组U上的一组数据依赖关系的集合。当且仅当U上的一个关系 r 满足 F 时，r称为关系模式R（U，F）的一个关系。,2.函数依赖 函数依赖是数据依赖的一种，函数依赖反映了同一关系中属性间的一一对应约束。它是现实世界属性间相互联系的抽象，是数据内在的性质，是语义的体现。它是数据库规范化理论的基础。函数依赖普遍存在于现实生活中。例如在描述学生的关系中，可能的属性有学号，姓名，所在系等。由于一个学号只对应于一个学生，一个学生只能属于一个系，因此学号确定之后，该学生的姓名和所在系也唯一的确定了。类似于自变量确定了，函数值也唯一确定了。起决定性的属性相当于自变量。函数依赖记为：学号姓名，学号所在系。,现在讨论一个具体的关系模式。例如，要设计一个教学管理数据库，希望该数据库中包括的信息有学生学号、姓名、系名、系主任姓名，以及学生学习的课程和成绩。于是得到一组属性：U=学号，姓名，系名，系主任，课程名，成绩 根据经验得知：一个学号对应一个学生，即一个学号对应一个姓名，但可以有多个学生重名；一个系有若干学生，但一个学生只属于一个系；一个系只有一名（正职）负责人；,一个学生可选修多门课程，每门课程有若干学生选修；学生学习每一门课程有一个成绩；于是得到属性组上的一组函数依赖：F=学号姓名，学号系名，系名系主任，（学号，课程名）成绩,定义：设R（U）是属性集U上的关系模式。X，Y是U的子集。若对于R（U）的任意一个可能的关系r（一个满足约束条件的数据组成的表），r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称X函数确定Y或Y函数依赖于X，记作XY。定义：在R（U）中,，如果XY，YZ，且（Y X），Y X，则称Z对X传递函数依赖。例如，在教学关系模式中，学号系名，系名系主任，所以系主任对学号传递依赖。,3.范式 关系数据库中的关系是要满足一定要求的。满足不同程度要求的为不同的范式。范式是满足一定函数依赖的关系模式的集合。有多种范式：第一范式、第二范式、第三范式、BCN范式等。满足最低要求的叫第一范式，简称1NF。在满足第一范式的条件下，满足进一步条件的为第二范式，简称2NF。其余依此类推。显然各种范式之间存在关系：1NF2NF 3NF BCNF 4NF 5NF 一个低一级的范式模式，通过模式分解可以转换为若干个高一级范式的模式集合，这个过程叫规范化。,(1)第一范式（1NF）定义：如果一个关系模式R的所有属性都是简单属性，即每个属性都是不可再分的基本数据项，则称R属于第一范式，记作 R 1NF。例如，教学模式中的所有属性都是不可再分的简单属性，即：教学（学号，姓名，系名，系主任，课程名，成绩）1NF,(2)第二范式（2NF）定义：若一个关系模式RlNF，且每一个非主属性完全函数依赖于关键字，则R2NF。将教学模式分为以下两个模式：学生_系（学号，姓名，系，系主任）；选课（学号，课程名，成绩）在“学生_系”关系中，“学号”是关键字，其它非主属性都完全依赖于关键字，“选课”关系中，（学号，课程名）是关键字，非主属性也完全依赖于关键字，因此两个关系中非主属都完全函数依赖于关键字，服从2NF，即：学生_系2NF，选课2NF。,(3)第三范式（3NF）定义：若关系模式R是第二范式，且没有一个非主属性传递函数依赖关键字，则R3NF。R3NF，则非主属性对主属性既不部分函数依赖于关键字，也不传递函数依赖于关键字。考察“学生_系”关系，可以看出：学号系名，系名系主任，即“系主任”传递依赖于“学号”。“学号”是关键字，而“系主任”是非主属性，它们之间存在传递函数依赖关系，因此“学生_系”关系 3NF。如果对“学生_系”关系按第三范式的要求分解，分解后的模式如下：学生（学号，姓名，系）系（系，系主任）则分解后的模式均属于3NF。,规范化方法为设计人员判断关系模式的优劣提供了理论依据，可以使数据库的设计工作建立在严格的理论基础上。关系模式的优化步骤如下：按需求分析所得到的实体及其联系，确定数据库中各关系模式中存在的函数依赖集。逐一分析各关系模式，确定关系模式属于第几范式。对关系模式通过模式分解的方法进行改进，使其达到一定的范式。,小结,本章介绍了关系数据库的基本概念、SQL语言、函数依赖理论及范式。1.关系数据库。在关系数据库中关系就是表。关系数据库中的实体及联系均用关系来表示。2.SQL语言。它是关系数据库的结构化查询语言，具有一体化、高度非过程化、语言简洁、易于使用的特点。SQL语言提供了创建数据库、定义关系模式、查询、更新、维护、数据库安全控制等一系列功能。3.关系数据库规范化理论关系数据库规范化理论是数据库逻辑设计工具。关系数据库的规范化理论就是解决如何设计出合理的关系数据库模式。,习题,1.试说明SELECT语句的作用。2.试说明SELECT语句中FROM、WHERE、GROUP、ORDER、HAVING的作用。3.设学生选课数据库包含如下三个关系模式：学生（学号，姓名，性别，年龄，系名）；系（系名，系主任）；选课（学号，课程名，成绩）试用SQL语言实现下列操作：(1)查询年龄大于20岁的男生。(2)查询姓王的学生。,习题,(3)求出学习高等数学课程学生的最高成绩、最低成绩和平均成绩。(4)求出学习数据库原理课程且成绩高于80分的学生人数。(5)查询每个学生的学号及选修课的数量.(6)查询选修三门以上课程的学生学号。(7)查询学习数据库原理课程的学生姓名。(8)把张三的年龄修改为20。(9)删除学号为951001的学生选修的数据库原理课程。,习题,4.解释下列名词：函数依赖、部分函数依赖、传递函数依赖、1NF、2NF、3NF。5.现在要建立关于系、学生、班级信息的数据库，关系模式为：学生（学号，姓名，出生日期，系名，班号，宿舍区）；系（系号，系名，办公室，系人数）；班级（班号，专业名，系名，人数，入学年份）各个属性之间的关系：一个系有若干个专业，每个专业每年只招一个班，每个班有若干学生，一个系的学生住在同一个宿舍区。(1)请写出每个关系模式的函数依赖，分析是否存在部分依赖、传递依赖。(2)找出各个关系的关键字、外部关键字。(3)每个关系是什么范式？如果不是3NF请规范化为3NF。,