ch2 关系数据库基础

Click to edit Master text styles,Second level,Third level,*,Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Click to edit Master title style,第2章 关系数据库基础,国家,“,十三五,”,重点出版规划项目,上海高校优秀教材奖获得者主编,数据库原理及应用与实践,主编 贾铁军,谷伟,副主编 俞小怡 王冠 万程,编者 戴春妮 王坚,上海市高校精品课程,上海高校优质在线课程,基于,SQL Server2016,（第,3,版 立体化,.,新形态）,目 录,2.1,关系模型概述,1,2.2,关系模型的完整性,2,2.3,常用的关系运算,3,* 2.4,关系演算与查询优化,4,2.5,常量变量函数和表达式,5,2.6,实验二,常量变量函数及表达式应用,6,2.7,本章小结,7,第2章,关系数据库基础,教学目标, 掌握关系模型及关系数据库的,基本概念, 掌握关系模型的,完整性规则及用法, 熟练掌握常用的,关系运算,及其,应用, 了解,关系演算,和,查询优化,基本过程,掌握,常量变量函数,及,表达式应用,重点,重点,重点,2.1.1 关系模型有关概念,1970,年，,IBM,研究员,E.F.Codd,博士在刊物,Communication of the ACM,上,发表了,一篇论文中,提出了,关系模型的概念,，,奠定了关系模型的理论基础,，这篇论文被普遍认为是数据库系统历史上,具有划时代意义的里程碑,。关系模型有严格的数学基础，抽象级别比较高，而且简单清晰，便于理解和使用。,1974,年,ACM,牵头组织了一次研讨会，会上开展了一场分别以,Codd,和,Bachman,为首的支持和反对关系数据库两派之间的辩论。这次著名的辩论推动了关系数据库的发展，使其最终成为现代数据库产品的主流。,案例,2-1,复习,2.1,关系模型概述,1. 关系模型的常用概念,关系模型,是,用二维表结构形式表示实体及实体之间联系的数据模型。,（,1,）,属性、记录、分量和关系,.,在关系模型中,二维表的,列,称为,属性,(,也称,字,段,),各属性有独立的名称，属性值对应于各行的,字段值,。,二维表的,行,称为,记录,(,也称,元组,);,一条记录的一个属性值被称为一个,分量,(,也称为,数据项,);,二维表记录的集合称为,关系,每个关系有一个,关系名,。,（,2,）,域,.,是一组相同数据类型的属性值的集合,.,在关系中,用域表示,属性的取值范围.,属性A的域,用,DOM（A）,表示,域中所包含的值的个数,(,行数,),称为域的,基数,(用m表示).每个属性对应一个域,不同的属性可对应同一域。,已知下述三个域，确定其基数：,D1(,姓名,)=,周明,杨涛,张晓东,李立,王军,m=5,D2(,年龄,)=43, 38, 41, 39, 36 ,D2基数,m=5,D3(,性别,)=,男，女,，,m=2,2.1.1 关系模型有关概念,案例,2-2,属性的个数,称为,元数,即列数,属性(字段),元组(记录),2.1.1 关系模型有关概念,（,3,）,关系模式。,关系模式,（,Relation Schema,）是对,关系的,描述方式,表示为,: R(U,D,dom,F),如,客户,(,U,A,B,购物,),其中,R,为,关系名,U,为组成该关系的,属性名集合,D,为属性组,U,中属性所来自的,域,;,dom,为属性向域的,映像集合,;,F,为属性间数据的,依赖关系集合,，,类似函数,F(x,y,z),。,注册日期,属性组,注册顺序,数据表,关联,客户,(,客户,ID,客户名称,注册日期,联系人,ID,类型,),2.1.1 关系模型有关概念,（,4,）,关系模型,。是关系模式的集合，是用键表示实体和实体间联系的,数据模型,。,（,5,）,键,（,Key,）,也称为,主,键,或,码,在关系中,用于,标识,记录,(,元组,),的,属性或属性组,。在实际应用中主要有,3,种键,。,*,1,）,超键,。,超键（,Super Key,）,关系中,能唯一标识,记录,(,元组,),的属性或属性集称为关系模式的超键。,*,2,）,候选键,。,候选键（,Candidate Key,）,指,不含有多余属性,的超键称为候选键。,2.1.1 关系模型有关概念,(,学号, 课程号,课程名,),(商品编号,商品名称),(,学号,),(,商品编号,),3,）,外键,。,外键,（,FK,，,Foreign Key,）或,外码,指若在模式,R,中包含有另一个关系,S,的主键所对应的属性或属性组,K,则称,K,为,R,的外键,.,外键是一个关系中的属性或属性组，但,不是本关系的主键，而是,另一关系的主键,，则称该属性或属性组是该关系的,外键,，也被称为,外关键字,。,2.1.1 关系模型有关概念,2.1.2 关系的类型和性质,关系,是一个元数为,K（K,=,1）,的,元组集合,。,关系,有,3种类型,:,基本表(,基本关系,常称,表,),、,查询表和视图表.,（1）,基本表,实际存在的表,存储数据的逻辑表示。,（2）,查询表,是查询结果对应的表,(,关联的表,),。,（3）,视图表,由表/其他视图表导出的表,虚表。,不对应实际存储数据。,基本关系,具有,六条性质,：,（1）同一,属性,(,列,),的,数据,是,同质的,(,Homogeneous,),即每列值为,同类,数据,且来自,同域,.(,同列同类同域,）。,（2）关系中,每个,属性值,都,不可分解,.,即各分量都是,不可分,的数据项。,（3）,任意两列不能相同,，同一,关系,的,属性（列）名,不能重复,，即不同列可出自同一个域，称其中的每列为一个属性。,（4）,任意两行元组(记录),不能完全相同,。,（5）,列,的,顺序,无所谓,即列的次序可任意交换。,（6）,行,的,顺序,无所谓,即行的次序可任意交换。,2.1.3,E-R,图转换为关系模型的方法,1,实体及联系转换关系,的规则,（,1,）,实体转换关系,(二维表),规则：将,每个实体,转换成一个关系模式时，,实体的属性,就是,关系的属性,。,（,2,）,二元联系的转换,规则：, 若,实体间联系是,1:1,，则可以在两个实体任选一个，,其关键字和联系的属性放入另一个实体对应的关系模式中，,关键字,为,后者的关键字,。, 若,实体间联系是,1:n,，,方法一,是,1,端实体的关键字放入,n,端实体的关系模式中，联系有属性也一并放入,n,端，关键字是,n,端的关键字；,方法二,是独立构建一个关系模式，属性为两端实体的关键字加上联系的属性，而,关键字为,n,端实体的关键字,。, 若,实体间联系是,m:n,，新构建一个关系模式，其属性为两端实体关键字加上联系的属性，而,关键字为两端实体关键字的组合,。,若,一个联系涉及三个或三个以上实体,，则新构建一个关系模式，其,属性是各实体的关键字。,E-R,模型,实体,关系模式,客观事物,(,现实世界,),信息世界,机械世界,2.1.4 ER图转换为关系模型的方法,2,转换方法应用,【案例,2-3,辅导员和,年级,是一对一的联系，如图,2-3,所示。一端实体的关键字放入另一端，其,关系模式,如下：,辅导员,（,辅导员,I,D,，教师名，性别，年龄，职称）,PK:,辅导员,I,D,年级,（,年级,号,，所在系，,年级,名，,辅导员,I,D,，接管时间）,PK,：,年级,号,FK,：辅导员教师,对应的关系模式,：,案例,2-3,年级,年级,辅导员ID,教师名,性别,年龄,职称,年级号,年级,辅导员,I,D,辅导员,I,D,或,总经理,管理,企业,方法一,：将,1,端实体的关键字和联系的属性与,n,端关系模式合并，关键字为,n,端的属性。,案例,2-4,】,辅导员和年级是一对多的联系,如图所示,其关系模式如下,:,辅导员,（,辅导员,I,D,，教师名，性别，年龄，职称）,PK:,教师号,年级,（,年级号,所在系,年级名,辅导员,I,D,接管时间）,PK,：年级号,FK,：辅导员教师号,。,2.1.4 ER图转换为关系模型的方法,案例,2-4,年级,年级,年级,辅导员,I,D,辅导员,I,D,方法二,：联系可独立转换成一个关系模式，其属性包括两端实体的关键字，以及联系的属性。,【,案例,2-5】,辅导员与年级联系是一对多,如图所示,.,其关系模式如下：,辅导员,（,教师号,，教师名，性别，年龄，职称）,PK:,教师号,年级,（,年级号,，所在系，年级名）,PK,：年级号,FK,：辅导员,ID,管理,（,辅导员,ID,，年级号,，接管时间）,PK,：辅导员,ID,，年级号,FK,：辅导员,ID,或年级号,2.1.4 ER图转换为关系模型的方法,案例,2-5,年级,年级,年级,年级,辅导员,I,D,辅导员,I,D,对于实体之间多对多联系的情况,各,实体,直接可转换为关系模式,联系,则独立转换成一个关系模式,其属性包括两端实体的关键和联系的属性。,案例,2-6,】,两端实体的关键字和联系的属性新构建一个模式，属性为两端的关键字和联系的属性，如图所示，其关系模式如下：,学生,（,学号,，姓名，性别，年龄，籍贯）,PK:,学号,课程,（,课号,，课名，课程性质）,PK,：课号,选课,（,学号，课号,，成绩）,2.1.4 ER图转换为关系模型的方法,案例,2-6,（1）什么是关系模式？形式化表示是什么？,（2）关系应该具有哪些性质？,（3）ER模型如何向关系模型转换？,讨论思考,讨论思考,2.2关系模型的完整性,(1),实体,完整性,规则,：,主键中的,主属性,不可取空值。一个基本关系通常对应现实世界的一个实体集。例如学生关系对应于学生的集合。实体完整性如图2-7所示。,图,2-7,实体完整性示意图,正确、有效、一致,2.2关系模型的完整性,(2),参照完整性规则,(Referential Integrity),要求,:若属性组,F,是关系模式,R,1,的,主键,同时,F,也是关系模式,R,2,的,外键,则在,R,2,的关系中,F,的取值,只允许两种可能,:,空值,或等于,R,1,关系中某个,主键值,.,R,1,称为,被参照关系模式,R,2,称为,参照关系模式,。,关系模型可以表示,为：,Customer(,CID,CName,RID,CPassword,CRegistrationDate,CType,CStatus,CAccountBalance),PK,(主键):CID；,FK,(外键):,RID,Relation(,RID, RName, RIndentityNo, RDuty, RAddress,RContactinfo),PK,：,RID,注意,：,在实际应用中，,外键,不一定与对应的主键同名,.在关系模式中,外键,可用,下划曲线,标出,。,联系人,客户,F,参照完整性,就是定义外键和主键之间的,引用规则,。关系,R,中每个元组在,F,上的值必须为空,(F,的每个属性值均为,空值,);,或等于,S,中某个元组的,主键,。,2.2关系模型的完整性,(3),用户定义的完整性,是针对某一具体关系数据库的约束条件,反映出某一具体应用所涉及的数据,必须满足的语义要求,。,2.2关系模型的完整性,（,1,）关系模型中有哪三类完整性约束？,（,2,）关系为什么应该满足实体完整性规则和参照完整性规则？,（,3,）举例说明用户定义的完整性规则。,讨论思考,图,2-8,用户定义完整性示意图,2.3 常用的关系运算,2.3.1 关系运算种类及运算符,关系代数,的,运算,对象,及,结果,都是,关系,。,1.关系运算的种类,关系代数的运算,分为,两类：,传统集合运算,和,专门关系运算。,（1）,传统的集合运算,将,元组,(记录,),作为集合中,元素,进行运算,其运算是,从关系的,“,水平,”,方向,即行的角度进行的。包括,并,、,差,、,交,和,笛卡尔积,等运算。,（2）,专门的关系运算,针对关系数据库的应用而专门设计的。涉及,关系的,行运算,和关系的,列运算,。它包括,选取,、,投影连接,和,除,等运算。,2.3.1关系运算种类及运算符,2.关系运算的运算符,关系代数,所使用的,运算符,有4类:,（1）,集合运算符,：(并),（差）,（交）,（广义笛卡儿积）。,（2）专门的,关系运算符,:(,选择）,（投影）, （连接,）,（,除,),（3）,比较,(算数),运算符,:（大于）,（大于等于），（小于），,（小于等于），（等于），（不等于）,（4）,逻辑运算符,：,（非），（与），（或）。,2.3.2 常用传统的关系运算,传统的集合运算,是,二目运算,包括,并、差、交、广义笛卡尔积,四种运算,.,1）并（Union）,关系,R,和关系,S,的,并,由属于,R,或属于,S,的元组组成,即,R,和,S,的所有元组合并,删除重复元组,，组成一个新的关系，其结果仍为,n,目关系,。记作：,RS = t | tR tS ,其中，,t,是元组变量（关系表的行）,对于关系数据库，,记录的,插入,可通过,并运算,实现。,并异去余,R,S,:,2.3.2传统的关系运算,关系,R,关系,S,【案例,2-7,】,已知关系,R,和关系,S,如表所示,求关系,R,和,S,的并集。,案例,2-7,%,注意,:,并、差和交运算,R,和,S,的属性一定要相同,否则无法进行运算。,2.3.2传统的关系运算,2,),差（Difference）,关系,R,和,关系,S,的,差,由属于,R,而不属于,S,的所有元组组成,即,R,中,删除,与,S,中,相同的元组,，组成一个新的关系，其结果仍为,n,目,关系。,记作,：,R,S,= ,t,|,t,R,t,S, t是元组变量,【案例,2-8,】,有关系,R,和关系,S,如表所示,求关系,R,和关系,S,的差。,关系,R,关系,S,R,S：,案例,2-8,前同后去,2.3.2传统的关系运算,3,),交（ Intersection）,关系,R,和,关系,S,交,由既属于,R,又属于,S,的元组组成，运算结果是,R,和,S,中,相同的元组,组成一个新的关系，仍为,n,目关系,。,记作,:,R,S,=,t,|,t,R,t,S, 关系的交,也可用差表示,即,R,S,=,R,（,R,S,）,。,案例,2-9,】,有关系,R,和关系,S,如所示，求关系,R,和关系,S,的交。,关系,R,关系,S,R,S,:,案例,2-9,同存共有,2.3.2传统的关系运算,4,),. 广义笛卡尔积,两个分别为,n,目和,m,目的关系,R,和,S,的,广义笛卡尔积,是一个（,m+n,）,列的,元组,的,集合,，元组的前,n,列是关系,R,的一个元组，后,m,列是关系,S,的一个元组。若,R,有,k1,个元组，,S,有,k2,个元组，则关系,R,和关系,S,的广义笛卡尔有,k1k2,个元组，,记作,：,R,S,t,t,=,t,r,Rt,s,S,案例,2-10,】,有关系,R,和关系,S,如所示,求关系,R,和,S,的广义笛,卡,尔积,。,关系,R,关系,S,R,S,:,案例,2-10,前抄后列,2.3.3专门的关系运算,专门的关系运算,包括4个：,选择、投影、连接、除,等。,1. 选择运算,选择运算,是在表中选择,符合给定条件,的元组，即对二维表进行,水平分割,，记为,F,（,R,）。其中，,为选择运算符，,F,表示选择条件，是一个条件（逻辑）表达式。,F,的形式是由算术,比较运算符,（，,，,，,）和,逻辑运算符,（,，,，,）连接起来的逻辑表达式，结果为逻辑值,“真”和“假”,。选择运算的,形式定义,如下：,F,（R）= t | t R ,F(t)=true, ， X,Y,:,如,价格 38,F,(R),表示,从R中,挑,(筛)选,满足条件F的,元组,所构成,的,关系,。,(,比较运算符,),看列选行,案例,2,-,11,】,设有一个如表所示的,商品,关系。,查询,上海生产的商品信息。,选择运算,产地,=,上海,(,商品,),或,8=,上海,(,商品,),结果,如表,2-9,：,2.3.3专门的关系运算,案例,2-11,案例,2-12,】,在表中,查询,价格小于,80,元的,商品信息,。,选择运算,价格,80,(,商品,),或,380,(,商品,),结果：,2.3.3专门的关系运算,案例,2-12,2.3.3专门的关系运算,2. 投影运算,投影运算,是在一个关系中,选取,某些,列,并,重新安排,列,的顺序,再,删去,重复元组,即对二维表,进行,垂直分割,.,记作,:,A,(R).其中, 为,投影运算符,，A为R中的,属性列,。,投影运算的,形式定义,如下：,A,（R）= tA | tR ,注意,：,投影运算,是对关系进行,“,垂直分割,”,，即对元组列方向的,“,筛选,”,。投影后不仅取消了原关系中的某些列，而且还可能取消某些元组，因为取消了某些属性列后，就可能,出现重复行,，应取消这些完全相同的行,。,看,R,定列,例,2-13,】,已知,商品,关系如表所示，,查询,商品的,产地,。,投影运算,产地,(,商品,),或,8,(,商品,),的,结果,如表。,2.3.3专门的关系运算,案例,2-13,已知,商品,关系如表所示,查询,商品的,名称,和,价格,。,投影运算,商品名称,价格,(,商品,),或,2,3,(,商品,),的结果,如表所示。,2.3.3专门的关系运算,案例,2-14,2.3.3专门的关系运算,3. 连接运算,连接,运算,是从两个关系的,笛卡尔积,中，,选择,两个关系的,属性,满足,一定条件的,元组,。,记作,：,其中,i和j,分别是,关系,R和S中的第i个、第j个属性.是,比较运算符,.,r是,关系R的元数,.,该式表示,连接运算,是在关系R和S的,笛卡儿积中,挑选,第i个,分量,和第(rj),个,分量,满足,运算的,元组,。,如果,为等号“=”，那么这个,连接操作,称为,等值连接,。,自然连接,是一种特殊的,等值连接,.,要求两个关系中,进行比较,的,分量必须是相同,的属性组,并在结果中把重复属性列,去掉,.,积行去余,【,案例,2-15,】,设关系,R,和关系,S,如表所示,求,R S,R S,和,R S,。,（,a,）关系,R,（,b,）关系,S,广义笛卡尔积,结果,2.3.3专门的关系运算,案例,2-15,【,案例,2-15,】,设关系,R,和关系,S,如表所示,求,R S,，,R S,和,R S,。,(1),(2),(3),2.3.3专门的关系运算,案例,2-15,2.3.3专门的关系运算,4. 除运算,对于,给定,关系R(X,Y,)和S(,Y,Z)，其中X，Y，Z,为,属性组。R中的Y与S中的Y,可以有,不同的属性名,，但,必须出自,相同的,域集,。R与S的,除运算,得到,一个,新的关系,P(X)，P是 R中,满足,下列条件的元组在 X属性列上的,投影,：元组在X上分量值x的,像集,Y,x,包含,S在Y上,投影的集合,。记作：,其中Y,x,为,x,在R中的,像集, x = t,r,X。,除操作,是,同时,从行和列角度进行,运算,。,分,投影,分组,对应,除运算过程,:,关系,的,除运算操作是从,行,和,列,同时,进行的。,具体计算过程,为,：,（,1,）将,被除关系,的,属性,分为,像集属性,和,结果属性,两部分，与除关系,相同的,属性属于像集属性，,不相同的,属性属于结果属性。,（,2,）在,除关系中,，在与被除关系相同的属性（,像集属性,）上,投影,，得到除,目标数据集,。,（,3,）将,被除关系,分组,，将结果属性值,相同的元组,分为一组,。,（,4,）观察,每个组,若其像集属性值中,包括,除目标数据集，则,对应,的结果属性值应属于该除法运算结果集,.,并,差去,与,原被除关系,相同的分组,.,注意,：,除法应用,.,当问题中出现,至少,、,全部,、,所有,等类似的集合,包含,的概念时，可能会用到,除法,；用到除法时，关键的问题是构造,除关系,和,被除关系,。,2.3.3专门的关系运算,分,投影,分组,对应,*【,法,2】,投影差选,:,T =,1,2, ,r-s,(R); W=(T,X,S)-,R,;,V= ,1,2, ,r-s,(W); RS=T-V.,设,选课,和,课程,关系，求,学生,选课,课程,数据,表,。,2.3.3专门的关系运算,案例,2-16,选课,R,课程,S,像集属性,结果属性,目标数据集（,投影,）,-S,(BX15120, BE16227,BG16245,BG16238),R,分组,对应,(BX15120, BE16227),分投影,*,学生选课,课程数据,的,具体,计算过程,如下：,（,1,）将关系学生,选课,的学生学号,属性（列）投影,分为,BX15120, BE16227, BG16245, BG16238,。,（,2,）学号属性,（列）,投影,BX15120,对课程代码的,像集,为, P2637, A3103, P2637,，,BE16227,的,像集,为, P2637, A3103, P2637 ,，,BG16245,的,像集,为, P2637, A3103,，,BG16238,的,像集,为, P2637,。,（,3,）在课程数据中，在属性课程代码上进行,投影,为, P2637, A3103, P2637,。,（,4,）显然，课程数据中课程代码的,投影,P2637, A3103, P2637,包含在,学生选课的学号,BX15120, BE16227,的,像集中,。,（,5,）,学生选课,课程数据,结果,BX15120, BE16227,。,2.3.3专门的关系运算,*另方法,2.3.3专门的关系运算,【,案例,2-11,】,设关系,订购,和,零件,数据如表2-15和表2-16所示，求,订,购零件。,表2-15 订购关系,工程号,零件号,数量,a1,b1,58,a2,b1,43,a3,b4,678,a1,b2,65,a4,b6,65,a2,b2,43,a1,b2,58,表2-16零件关系,零件号,零件名,颜色,b1,螺母,红色,b2,螺钉,蓝色,表2-17 定购零件结果,工程号,数量,a1,58,a2,43,（b1,b2）,（a1,58）,（a2,43）,（,a3,678）（a1,65）（a4,65）,像集属性,上海市高校精品课程,上海市教育高地项目,除目标数据集,像集属性,结果属性,补案例,分组,对应,分,投影,下面举几个关系代数综合运算应用的,案例,。,设有,商品销售数据库有三个关系（表），,包括商品关系、售货员关系和售货关系。三个关系的关系模式如下：,会员,(,会员,ID,姓名,性别,所在地区,家庭住址,手机号码,入会时间,会员等级,),商品,（商品,ID,，商品名称，价格，品牌，型号，颜色，生产商，产地）,订货,（商品,ID,会员,ID,姓名,商品名称,总价,提交时间,是否付款,运送方式,重量,运费）,2.3.3专门的关系运算,讨论思考,（,1,）做交、并、差运算的两个关系必须满足什么条件？,（,2,）查询什么什么结果的运算中用除运算？,（,3,）举例说明自然连接与等值连接的区别和联系？,讨论思考,*2.4.1 关系演算概述,1.元组关系演算,在,元组关系演算,（Tuple Relational Calculus）中，元组关系演算表达式的,一般形式,为：,其中， 为元组变量，表示一个元数固定的元组，是以元组变量为基础的公式。该表达式的含义是使为真的元组的集合。,原子公式（Atoms）有,三种形式,：,（1） 。,（2） 。,（3） 或者 。,。,*2.4,关系演算与查询优化,关系演算公式,（Formula）的,递归定义,如下：,2.4.1 关系演算概述,。,关系代数,表达式转换到元组演算,表达式,。,2.4.1 关系演算概述,2.域关系演算,域关系演算,（Domain Relational Calculus）与元组关系演算相似，元组关系演算中表达式使用的是元组变量，元组变量的变化范围是一个关系，域关系演算表达式中以属性列为变量，即域变量，域变量的变化范围是某个属性的值域。,域关系演算的原子公式有,两种形式,：,（1）,（2）,域关系演算表达式的,一般形式,是：,。,2.4.1 关系演算概述,1,、关系代数表达式的优化问题,在关系代数运算中，,笛卡尔积,和,连接运算,是,最费时间,的,，,通过,查询从,“亘立科技”订货的商品的商品,ID,和价格，其中订货关系中有,500,个元组，商品关系中有,200,个元组。,商品,（,商品,ID,，商品名称，价格，品牌，型号，颜色，生产商，产地）。,订货,（,商品,ID,，会员,ID,，姓名，商品名称，总价，提交时间，是否付款，运送方式，重量，运费）,。,E1=,商品,ID,，数量,（,生产商,=,亘立科技,商品,.,商品,ID=,订货,.,商品,ID,（订货,X,商品）,求,E1,的值，先做订货和商品关系的笛卡尔积，具有,200 X 500=10000,个元组。,E2=,商品,ID,，数量,（,商品,.,商品,ID=,订货,.,商品,ID,（订货,X,生产商,=,亘立科技,（商品）,求,E2,的值，“亘立科技”信息只存在就,50,，因此，笛卡尔积的结果是,50 X 500,个元组。,求,E3,的值，由于“亘立科技”在订货关系中有,100,个元组，等值连接后，具有结果是,100 X 50,个元组。,2.4.1 关系演算概述,2,. 关系代数等价变换规则,关系代数,是各种数据库查询语言的基础，各种查询语言都能够,转换成,关系代数表达式。所以,关系代数表达式,的,优化,是查询优化的基本方法。两个关系代数表达式,等价,是指用同样的关系实例,代替,两个表达式中相应的关系时所,得到,的结果是一致的。两个关系表达式E1和E2,等价,时，可,表示,为：E1E2。,等价变换规则,指出,两种不同形式的表达式是等价,的，可以利用第二种形式的表达式代替第一种，或者用第一种形式的表达式代替第二种。这是因为这两种表达式在任何有效的数据库中将产生相同的结果。,*2.4.2查询优化常用规则及算法,常用的,等价变换规则,有：,（,1,）笛卡尔积和连接的,等价交换律,设E1和E2是两个关系代数表达式，F是连接运算的条件，则：,（,2,）笛卡尔积和连接的,结合律,设E1、E2和E3是三个关系代数表达式，F1和F2是两个连接运算的限制条件，F1只涉及到E1和E2的属性，F2只涉及到E2和E3的属性，则：,*2.4.2查询优化常用规则及算法,（,3,）投影的,串联,设E是一个关系代数表达式，L1，L2，Ln是属性名，则：,（,4,）选择的,串联,设E是一个关系代数表达式，F1和F2是两个选择条件，则：,（,5,）选择和投影的,交换,设E为一个关系代数表达式，选择条件F只涉及L中的属性，则：,*2.4.2查询优化常用规则及算法,（,6,）选择对笛卡尔积的,分配,律,设E1和E2 是两个关系代数表达式，若条件F只涉及E1的属性，则：,（,7,）选择对并的,分配律,设E1和E2有相同的属性名，或者E1和E2表达的关系的属性有对应性，则：,（,8,）选择对差的,分配律,设E1和E2有相同的属性名，或者E1和E2表达的关系的属性有对应性，则：,*2.4.2查询优化常用规则及算法,（,9,）投影对并的,分配,律,设E1和E2有相同的属性名，或者E1和E2表达的关系的属性有对应性，则：,（,10,）投影对笛卡尔积的,分配律,设E1和E2是两个关系代数表达式，L1是E1的属性集，L2是E2的属性集，则：,*2.4.2查询优化常用规则及算法,3.,关系表达式的,优化算法,输入：一个关系代数表达式的语法树。,输出：计算表达式的一个优化序列。,方法,：,（1）利用等价变换规则4把形如 变换为,（2）对每一个选择，利用等价变换4-8尽可能把它移到叶端。,（3）对每一个投影利用等价变换规则3,5,10中的一般形式尽可能把它移向树的叶端。,（4）利用等价变换规则3-5把选择和投影的串接合并成单个选择、单个投影或一个选择后跟一个投影。使多个选择或投影能同时执行，或在一次扫描中全部完成。,（5）把上述得到的语法树的内节点分组。每一个二元运算和它所有的直接祖先为一组。如果其后代直到叶子全是一元运算，则也将它们并入该组，但当二元运算是笛卡尔积，而且后面不是与它组成等值连接的选择时，则不能把选择与这个二元运算组成同一组.而是把这些一元运算单独分为一组.,*2.4.2查询优化常用规则及算法,讨论思考,：,（1）什么是关系演算？在关系演算公式中，各种运算符的优先级次序是什么？,（2）域关系演算和元组关系演算有什么区别和联系？,（3）为什么要进行查询优化？什么是等价变换规则？,（4）举例说明关系表达式的优化过程。,*2.4.2查询优化常用规则及算法,2.5,常量变量函数和表达式,2.5.1,常规标识符及使用规则,在,SQL,语言中，,标识符,（,Identifer,）是指,用于,标识操作对象名称等的,字符串,。在,SQL Server,中,所有的数据库对象都可以有,标识符,，如服务器、数据库、表、视图、索引、约束等。一般操作对象都需要标识符，如创建表时必须指定表名。,1.,常规标识符规则,常规标识符,(Regular identifer),也称,规则标识符,包括,五项,：,（,1,）标识符,组成,是字母、数字、下划线、,(,专用,),、,#,和,$,符号，其中,字母,可以是大小写英文,也可以是来自其他语言的字符。,（,2,）标识符的,首字符,不允许是数字或,$,符号。,（,3,）标识符不允许使用,SQL,的,保留字,，如命令名、函数名等。,（,4,）标识符内,不允许,有,空格和特殊字符,，如？、,%,、,&,、*等。,（,5,）标识符,长度,不超过,128,字节。,由,字符、符号或数字,组成,的,字符串,2.5.1,常规标识符及使用规则,2.,界定标识符,界定标识符,也叫,分隔标识符,(,定界符,),是使用, ,、 ,或,”,等起到分隔作用的符号限定的,标识符,。,带分隔符的标识符,用于下列情况：,（,1,）在,对象名称,或,其,组成部分中使用,保留字,时。,保留的关键字,不应用作对象名称。从,SQL Server,早期版本升级的数据库可能包含这样的标识符：这些标识符包含在,早期版本中,不是保留字,而在,SQL Server,的,当前版本,是保留字的字词。在可以,改变对象名称之前,，可以,使用,带分隔符的标识符引用对象,。,（,2,）,使用,未列为,限定标识符,的字符时。,SQL Server,允许,在,带分隔符的标识符中,使用当前代码页中的,任何字符,。但是，不加选择地在对象名称中使用特殊字符会使,SQL,语句和脚本难以阅读和维护,（,如，可创建名为,Employee,的表，其中右方括号是名称的一部分,）,。若要执行此操作，必须使用两个方括号以避免因使用右方括号而出现问题，如下所示：,CREATE TABLE ,Employee,(EmployeeID int IDENTITY (1,1) NOT NULL,FirstName varchar(30),LastName varchar(30) ),2.5.1,常规标识符及使用规则,2.5,常量变量函数和表达式,2.5.2,常量和变量的表示及用法,1.,常量的表示及用法,常量,是指在程序运行过程中其值保持不变的量。常量是表示一个特定数据值的符号，也称为,字面量、文字值,或,标量值,。常量的,格式,取决于它所表示的值的,数据类型,。,如,This is a book.,、,August 8, 2018,、,29157,和,29157,等都是常量,.,对于,非数值型常量,需要,使用,单引号,.,常量类型,数据类型,说,明,字符串常量,Char,varChar,Text,存放固定长度的字符数据，,n,的取值范围是,1,8000,存放可变长度的字符数据，,n,的取值范围是,1,8000,存放最大长度为,2,31,-1,的字符数据,数值,常量,Int,smallint,bigint,tinyint,decimal,（,p,s,）,mumeric(p,s),float,（,n,）,real,（,n,）,占,4,字节，取值范围为,-2 147 483 648,2 147 483 647,占,2,字节，取值范围为,-3 2768,3 2767,占,8,字节，取值范围为,-2,63,2,63,-1,占,1,字节，取值范围为,0,255,长度可变，固定精度，取值范围为,-10,38,-1,10,38,-1,长度可变，固定精度，取值范围为,-10,38,-1,10,38,-1,长度可变，近似小数，取值范围为,-1.79E+308,1.79E+308,长度可变，近似小数，取值范围为,-1.79E+308,1.79E+308,日期时,间,常,量,datetime,date,time,占,8,字节,日期取值,1753,年,1,月,1,日,9999,年,12,月,31,日,精确,到,0.03,秒,占,3,字节，日期取值为,0001,年,1,月,1,日,9999,年,12,月,31,日,占,3,5,字节，时间取值为,00:00:00:0000000,23:59:59:9999999,货币常量,money,占,8,字节，,取值范围为,-2,63,2,63,-1,，精确到,4,个小数位,二进制,型,binary,varBinary image,存放定长二进制数据，,n,的取值范围为,1,8000,存放变长二进制数据，,n,的取值范围为,1,8000,存放最大长度为,2,31,-1,的二进制数据,位类型,bit,占,1,字节，存放逻辑值，只能去,0,、,1,、,null,2.5.2,常量和变量,常量的数据类型,2.,变量,变量,是指在程序运行过程中其值可以,发生改变,的,量,。,（,1,）局部变量,局部变量,由,用户,定义,是,作用域,局限在,一定范围内的变量,.,作用域,(“,定义域”,),：若局部变量在一个批处理、存储过程中,被声明或定义,，则其作用域就在批处理、存储过程内。,局部变量,声明,(,定义,),局部变量声明,(,定义,),语句,的,语法格式,：,DECLARE,变量名,数据类型, n,说明,：,局部变量,必须由,开头,，另外变量名的形式要符合,SQL Server,标示符的规则。,局部变量,使用的数据类型,:,系统的数据类型和用户自定义数据类型,.,变量不能是,text,、,ntext,或,image,数据类型。,局部变量,必须,先声明,（定义）,然后,在,SQL,语句中,使用,默认初值,NULL,。,2.5.2,常量和变量,局部变量,赋值,局部变量,赋值,语句,的,语法格式,：,格式,1,：,SET ,变量名,=,表达式,格式,2,:,SELECT ,变量名,=,表达式,/,SELECT,变量名,=,输出值,FROM,表,where,或,SELECT ,变量,1=,表达式,1,变量,2=,表达式,2,变量,n=,表达式,n,说明,：,变量名,是除,cursor, text, ntext, image,外的任何类型；,表达式,是任何有效的,SQL Server,表达式。,“,格式,2,”,可以为,多个变量赋值,其中,SELECT ,变量名,=,表达式,.,用于,将单个“表达式”值,返回到,变量中,若表达式为,列名,则返回多个,.,(1),若,SELECT,语句,返回多个值,则将返回的,最后一个值,赋给,变量,.,(2),若,SELECT,语句,没有,返回值,变量,保留,当前值,;,若表达式是,不返回,值,的子查询，则变量为,NULL,。,上海市高校精品课程,上海优秀教材奖主编,光标,2.5.2,常量和变量,【,案例,2-7,】SELECT,命令赋值，执行程序。,USE educ,GO,DECLARE,var1 varchar(8),- -,声明局部变量,SELECT var1=,学生姓名,- -,为局部变量赋初始值,SELECT var1=Sname,- -,查询结果赋值给变量,FROM student,WHERE SID=bj10001,SELECT var1,as,学生姓名,- -,显示局部变量结果,执行结果,：,学生姓名,如,输入,张伟箭,上海市高校精品课程,上海优秀教材奖主编,SID,Sname,.,student,2.5.2,常量和变量,补充,【,案例,2-8,】SELECT,命令赋值,多个返回值中取最后一个,.,USE educ,go,DECLARE var1 varchar(8),SELECT,var1=,读者姓名,SELECT,var1=Sname,- -,查询结果赋值,返回整个列全部值,但最后一个给变量,FROM student,SELECT var1 AS ,读者姓名,-,显示局部变量的结果,执行结果,：,可输入不同的读者,(,如张晓东,),上海市高校精品课程,上海优秀教材奖主编,新字段名,2.5.2,常量和变量,补充,【,案例,2-9,】SET,命令赋值,USE educ,go,DECLARE no varchar(10),SET,no=Bj10001,- -,变量赋值,SELECT,SID,Sname,FROM student,WHERE SID=no,执行结果,：,上海市高校精品课程,上海优秀教材奖主编,2.5.2,常量和变量,补充,（,2,）全局变量,系统全局变量,是,SQL Server,系统,定义,(,提供并赋值,),的变量,.,常用于,跟踪,服务器范围和特定会话期间的信息,不能被用户显式地定义和赋,值,.,即,用户不能建,/,改全局变量,也不能用,SET,语句改变全局变量的值。,格式,：,变量名,记录,SQL Server,服务器活动状态的一组数据,系统提供,33,个全局变量,.,常用的全局变量,如表,2-9,所示。,全局变量,说明,全局变量,说明,error,上条,SQL,语句报告的错误号,nestlevel,当前存储过程,/,触发器的嵌套级别,rowcount,上一条,SQL,语句处理的行数,servername,本地服务器的名称,identity,最后插入的标识值,spid,当前用户进程的会话,id,max_connections,可创建并链接的最大数目,cpu_busy,系统自上次启动后的工作时间,language,当前使用语言的名称,servicename,该计算机上的,SQL,服务的名称,transcont,当前连接打开的事务数,version,SQL Server,的版本信息,上海市高校精品课程,上海优秀教材奖主编,由系统,定义和维护,表,2-9,常用的全局常量,2.5.2,常量和变量,注意,:,全局变量,以,开头,由系统,定义和维护,用户,只能,显示和读取,不能,修改,;,局部变量,由,开头,由用户定义和赋值,.,例如，显示,SQL Server,的版本。,select version,select servername,-,本地服务器名,上海市高校精品课程,上海优秀教材奖主编,2.5.2,常量和变量,2.5,常量变量函数和表达式,函数种类,主 要 功 能,函数种类,主 要 功 能,聚合函数,将多个数值合并为一个数值，如计算合计值,行集函数,可返回一可用于代替,SQL,语句中表引用的对象,配置函数,返回当前配置选项配置的信息,安全函数,返回有关用户和角色的信息,加密函数,支持加密、解密、数字签名和数字签名验证等操作,字符串函数,可以对字符数据执行替换、截断、合并等操作,游标函数,返回有关游标状态的信息,元数据函数,用于返回数据库和数据库对象的属性信息,排名函数,可以返回分区中的每一行的排名值,系统统计函数,返回有关,SQL Server,系统性能统计的信息,数学函数,执行对数、指数、三角函数、平方根等数学运算,文本和图像函数,用于执行更改,TEXT,和,IMAGE,值的操作,系统函数,对系统级的各种选项和对象进行操作或报告,日期时间函数,可以执行与日期、时间数据相关的操作,2.5.3,常用函数及其用法,函数,是指具有可以完成某种,特定功能的程序,，并,返回处理结果,的,一组,SQL,语句,，其处理结果称为,“,返回值,”,，处理过程称为,“,函数体,”,。,SQL Server,同其他程序设计语言类似，,提供,了丰富的,内置函数,，也允许用户,自定义函数,。利用这些函数可方便地实现各种,运算,和,操作,。,表,2,-,23,常用内置函数种类和功能,2.5.3,常用函数及其用法,1.,聚合函数,聚合函数,也称为,统计函数,，所有聚合函数均为,确定性函数,，只要使用一组特定,输入值,（数值型）,调用,聚合函数,，该函数总是,返回,同类型的值,。,函数名称,功 能 描 述,AVG,返回组中各值的平均值，若为空将被忽略,CHECKSUM,用于生成哈希索引,返回按表某行或组表达式计算出的校验和值,CHECKSUM_AGG,返回组中各值的校验和，若为空将被忽略,COUNT,返回组中项值的数量，若为空也将计数,COUNT_BIG,返回组中项值的数量。与,COUNT,函数唯一差别是其返回值。,COUNT_BIG,总返回,bigint,型值。,COUNT,始终返回,int,型值,GROUPING,当行由,CUBE/ROLLUP,运算符添加时,该函数将导致附加列的输出,1;,当行不由这两种运算符添加时,将导致附加列的输出,0,MAX,返回组中值列表的最大值,MIN,返回组中值列表的最小值,SUM,返回组中各值的总和,STDEV,返回指定表达式中所有值的标准偏差,STDEVP,返回指定表达式中所有值的总体标准偏差,VAR,返回指定表达式中所有值的方差,VARP,返回指定表达式中所有值的总体方差,2,数学函数,数学函数,用于对数字表达式进行数学,运算,并,返回,运算结果,。,SQL Server,提供了,20,多个用于处理整数与浮点值的数学函数。,2.5.3,常用函数及其用法,函数,说,明,ABS,返回数值表达式的,绝对值,EXP,返回指定表达式以,e,为底的,指数,-,e,x,CEILING,返回大于或等于数值表达式的最小整数,FLOOR,返回小于或等于数值表达式的最大整,数,LN,返回数值表达式的自然对数,LOG,返回数值表达式以,10,为底的对,数,POWER,返回对数值表达式进行,幂运算,的结果,ROUND,返回,舍入,到指定长度,/,精度的数值表达式,SIGN,返回数值表达式的正号,(+),、负号,(-),或零,(0),SQUARE,返回数值表达式的,平方,SQRT,返回数值表达式的,平方根,2.5.3,常用函数及其用法,3,字符函数,字符函数,也称为,字符串函数,用于,计算、格式化和处理,字符串参数,或将对象,转换为,字符串,.,常见的字符函数,如表,2-13,所示,.,字符函数,说,明,ASCII,ASCII,函数,返回,字符表达式中最左侧的,字符,的,ASCII,代码值,CHAR,ASCII,代码转换函数，,返回,指定,ASCII,代码,的,字符,LEFT,左子串函数,返回字符串中从左边开始指定个数的,字符,LEN,字符串函数,返回指定表达式的,字符,(,非字节,),数,不含尾部空格,LOWER,小写字母函数,将大写字符,转换为,小写,字符后返回字符表达式,LTRIM,删除,前导空格,字符串,返回删除前导空格后的字符表达式,REPLACE,(e1,e2,e3),替换函数,用第,3,个表达式,替换,第,1,个表达式中,出现的,所有第,2,个指定字符串表达式的,匹配项,REPLICATE,复制函数，以指定的次数,重复,字符表达式,，如*,-,RIGHT,右子串函数，返回字符串中从右边开始指定个数的字符,RTRIM,删除,尾随空格,函数,删除所有尾随空格后返回一个字符串,SPACE,空格函数，返回由重复的空格组成的字符串,STR,数字,向,字符,转换,函数，返回由数字数据转换来的字符数据,SUBSTRING,子串函数,返回,4,种表达式,(,字符,二进制,文本和图像,),的一部分,UPPER,大写