第二章 关系模型与SQL

主流数据库技术与实践第二章关系模型与SQL

2.1关系数据库的结构2.2关系代数运算2.3SQL2.1关系数据库的结构基本结构表：唯一的表名（关系）列：属性，列名唯一。每个属性有一组可能的取值（域）。行：记录，元组。是一个实体（各属性有具体的值）。null：是所有可能域的成员，表明值未知或不存在。数据库模式数据库模式（schema）：数据库的逻辑设计（表结构的集合），相当与程序设计语言中变量的定义。

instructor(ID,name,dept_name,salary)数据库实例(instance)：给定时刻数据库中数据的一个快照（表记录的集合），相当与程序设计语言中变量的值。码（key，键）属性值唯一标识元组：没有两个元组（行/记录）的所有属性值相同超码（superkey）：一个或多个属性的集合，可以唯一标识元组。它的任意超集也是超码。候选码：任意真子集都不是超码的最小超码。主码：设计者选中的用来区分元组的候选码。外码、参照关系、被参照关系、模式图

图中表account是参照关系,其中的branch_name是外码,表branch是被参照关系。模式图可于表示多个有相互关系的表的数据库模式。教师表(instructor)、系部表(department)每个系有课程列表(course)，包括课程号(course_id)、课程名(title)、系名(dept_name)和学分(credits),可能还有先修要求(prerequisites)。学生列表(student)包括表识(ID)、姓名(name)、主修的系(dept_name)和已修的学分(tot_cred)。教室列表(classroom)包括楼名(building)、房间号(room_number)和容量(capacity)。开课列表(section)包括课程号(course_id)、开课号(sec_id)、年(year)、学期(semester)、楼名(building)、房间号(room_number)和时间段号(time_slot_id)。系部有教学任务列表(teaches)，说明每位教师的授课情况。学生课程注册表(takes)，说明每位学生在哪些课程的哪次开课中注册了。大学案例关系表大学案例关系表(模式图)大学案例关系表(表数据1)course:课程表section:开课表大学案例关系表(表数据2)teaches:教学任务表2.2关系代数运算选择运算(行选择)例1:显示物理系(Physics)的所有教师dept_name=“Physics”

(instructor)SQL语句:select*frominstructorwheredept_name=‘Physics’例2:显示物理系(Physics)中工资大于90000的所有教师dept_name=“Physics”

^salary>90000

(instructor)SQL语句:select*fromloanwheredept_name=‘Physics‘andsalar>90000IDnamedept_namesalary22222EinsteinPhysics9500033456GoldPhysics87000IDnamedept_namesalary22222EinsteinPhysics95000投影运算(列选择)例:显示所有系部的教师号、姓名和工资ID,name,salary(instructor)SQL语句:selectID,name,salaryfrominstructor关系运算的组合关系运算的结果还是一个关系,可以对其进行其他运算。关系代数表达式：多个关系代数运算组合成的运算式。例:显示物理系(Physics)的所有教师的姓名name(

dept_name=“Physics”

(instructor))SQL语句:selectnamefrominstructorwheredept_name=‘Physics‘并运算(联合,结合)rs：把两个关系的元组合并在一起（去除重复元组）。条件：两关系相容，即要同元（属性数相同），并且对应属性的域相同。例:显示2009年秋季或2010年春季（或两者都有）的所有课程号的集合。

course_id

(

semester=“Fall”Λyear=2009(section))

course_id

(

semester=“Spring”Λyear=2010(section))SQL语句:（selectcourse_idfromsectionwheresemester=‘Fall‘andyear=2009）union（selectcourse_idfromsectionwheresemester=‘Spring‘

andyear=2010）集合差运算r－s：得到在关系ｒ中但不在关系ｓ中的元组。条件：两关系相容。例:显示2009年秋季开设但2010年春季没有开设的所有课程号的集合course_id

(

semester=“Fall”Λyear=2009(section))−

course_id

(

semester=“Spring”Λyear=2010(section))SQL语句:（selectcourse_idfromsectionwheresemester=‘Fall‘

andyear=2009）except（selectcourse_idfromsectionwheresemester=‘Spring‘

andyear=2010）笛卡儿积运算r×s：得到关系ｒ与关系ｓ中所有元组的交叉组合。结果中可能重复的列名要加表名前缀。例1:显示教师表与教学任务表的组合列表。instructor×techesSQL语句:selecti.ID,name,dept_name,salary,t.ID,course_id,sec_id,semester,yearfrominstructorasi，

teches

ast

结果见P126图6-8。不同教师的任务信息组合在一起没有实际意义。例2:查找物理系教师所教授的课程列表，显示姓名和课程号。instructor.ID,course_id

(dept_name=“Physics”

(instructor.ID=teaches.ID

(instructorxteaches)))SQL语句:selectinstructor.ID,teches.course_idfrominstructor，techeswhereinstructor.ID=teches.IDanddept_name=‘Physics

‘

另一种方法：instructor.ID,course_id

(instructor.ID=teaches.ID

(dept_name=“Physics”(instructor)xteaches))更名运算

x(E)：将关系代数表达式E的结果命名为x（又称别名）。可以对表名或结果列名赋予别名。

：将多元表达式E的结果命名为x，各个属性分别命名为A1，A2，…，An。作用：避免表名或列名重复。可以对同一个表多次引用。例:显示教师信息表中的最高工资。

步骤1：从教师信息表中找出所有非最大工资的集合；

instructor.salary(

instructor.salary<d,salary(instructorxd

(instructor)))步骤2：求教师信息表与所有非最大工资集合的集合差。salary(instructor)–

instructor.salary(

instructor.salary<d,salary

(instructorxd

(instructor)))SQL语句:（selectinstructor.salaryfrominstructor）except（selectinstructor.salaryfrominstructor,instructorasdwhereinstructor.salary<d.salary）步骤1结果步骤2结果集合交运算rs

：得到关系ｒ与关系ｓ中所有共同的元组集合。等同于r

s=r–(r–s)条件：两关系相容。例:显示2009年秋季和2010年春季都开设的所有课程号的集合course_id

(

semester=“Fall”Λyear=2009(section))

course_id

(

semester=“Spring”Λyear=2010(section))SQL语句:(selectcourse_idfromsectionwheresemester=‘Fall‘

andyear=2009)

intersect

(selectcourse_idfromsectionwheresemester=‘Spring‘

andyear=2010)自然连接运算rs

：二元运算，先得到ｒ与ｓ的笛卡儿积，然后基于共同属性上的相等性选择，最后去除重复属性。形式化定义：r=(A,B,C,D)，s=(E,B,D)，则结果集合=(A,B,C,D,E)rs=r.A,r.B,r.C,r.D,s.E

(r.B=s.Br.D=s.D

(rxs))例:显示所有计算机系教师名及所教课程的列表。

name,title(

dept_name=“Comp.Sci.”(instructor

teaches

course))SQL:

selectname,titlefrom(courseinnerjoin

(instructorinnerjointeaches

oninstructor.ID=teaches.ID

asitb(name,dept_name,course_id)

oncourse.course_id

=itb.course_id

ascitb(name,dept_name,

title))

wheredept_name=‘Comp.Sci‘;自然连接运算instructor

teaches的结果自然连接运算

name,title(

dept_name=“Comp.Sci.”(instructor

teaches

course))的结果外连接是连接运算的扩展，当一个关系中连接数据缺失时可以在结果集合中用空值（null）填补。左外连接（leftoutjoin）（rs）：将左侧关系中在右侧关系没有匹配的所有元组加入自然联结结果中，用空值填补来自右侧关系的所有属性。右外连接（rightoutjoin）（rs）：将右侧关系中在左侧关系没有匹配的所有元组加入自然联结结果中，用空值填补来自左侧关系的所有属性。全外连接（fulloutjoin）（rs）：将两侧关系中在对方关系没有匹配的所有元组加入自然联结结果中，用空值填补来自对方关系的所有属性。例1:自然连接instructorteaches

外连接例2:左连接

instructorteaches

例3:右连接

teaches

instructor聚集函数

含义：输入一个集合，返回单一值。sum：返回集合中各元素值的和。avg(average)：返回集合中各元素值的平均值。count：返回集合中元素的个数。min：返回集合中的最小值。max：返回集合中的最大值。如果需要去除重复，需要加说明，如：count-distinct形式化定义：Gi：分组属性名Fi：聚集函数Ai：聚集函数所计算的属性名例:dept_nameg

average(salary)(instructor)SQL:selectdept_name,avg(salary)frominstructor

groupbydept_name2.3SQL背景诞生:SQL读做Sequel,1970年代由IBM开发，随关系数据库的创建而出现。标准：ANSI和ISO发布了多个SQL标准，有SQL-86、SQL-89、SQL-92、SQL:1999和SQL:2003。组成：数据定义语言DDL:定义关系模式，删除关系和修改关系模式。交互式数据操纵语言DML：查询，插入，删除和修改元组。完整性：定义数据完整性约束条件（违背条件的更新将被禁止）。视图定义：定义视图。事务控制：定义事务的开始和结束。嵌入式SQL和动态SQL：嵌入到通用编程语言中操纵数据库。授权：设定对关系和视图的访问权限。标准与产品实现的不同：目前不同的关系数据库系统产品对SQL语言标准的支持程度不同。多数产品提供标准里所没有的部分功能。功能相同的SQL语句的语法也不完全相同，要以产品手册为准。数据定义基本类型char(n)：固定长度为n的字符串。全称character。varchar(n)：最大长度为n的可变长度字符串。全称(n)：整数类型（机器相关整数的有限子集)。全称integer。smallint：小整数类型（机器相关整数域类型的子集)。numeric(p,d)：定点数，为小数d位的p位十进制数字。real，doubleprecision：浮点数与双精度浮点数（精度与机器相关）。float(n)：精度至少为n位的浮点数。SQL内建数据类型date：日历日期,包括年（四位）、月、日。time，time(p)：时间,p为秒后小数位(默认为0)。也可同时存储时区。timestamp，timestamp(p)：date和time的组合，p为秒后小数位(默认为6)。指定withtimezone时也可同时存储时区。例：date‘2001-04-25’time‘09:30:00’timestamp‘2001-04-2510:29:01.45’数据定义SQL内建数据类型类型转换casteast:将字符串e转换为类型t的时间变量。cast‘2001-04-25’asdate时间提取extract(fieldfromd)：从时间变量d中提取域field的值。域field可以取year、month、day、hour、minute或second中的任意一种。时区可以用timezone_hour和timezone_minute提取。其他时间函数current_date返回当前日期,current_time返回带时区的当前时间,localtime返回不带时区的本地时间,current_timestamp返回带时区的当前日期时间,localtimestamp返回不带时区的当前日期时间。间隔类型interval：代表两个日期时间数据的差。如日期X-日期Y的值为X与Y间的间隔天数（整型）。用户自定义类型：用户可以根据业务需要定义特殊的数据类型。

例:createtypeDollarsasnumeric(12,2)finalSQL还提供droptype和altertype子句来删除和修改以前定义的用户类型。大对象类型：用于存储图像视频等大容量数据。clob用于字符数据，blob用于二进制数据。

例:book_reviewclob(10KB)imageblob(10MB)movieblob(2GB)关系的基本操作建立关系createtabler(A1D1,A2D2,…,AnDn,<完整性约束1>,<完整性约束2>,…,<完整性约束n>)。Ai：属性名，Di：域类型。完整性约束：用户根据需要建立的规则，更新数据时系统检测约束防止破坏数据的一致性。域约束

primarykey：主键约束，非空且唯一（非重复）

notnull：非空约束

unique：唯一约束（非重复）check：条件约束参照完整性约束:外码参照约束（子集依赖）

foreignkey(属性名)

references<参照关系名> ondelete<cascade/setnull> onupdate<cascade/setnull>关系的基本操作建立关系例：大学数据表createtable

department

(

dept_namevarchar(20),

buildingvarchar(15),

budget

numeric(8,2))primarykey(dept_name));createtable

course

(

course_idvarchar(7),titlevarchar(50),dept_namevarchar(20),credits

numeric(2,0),primarykey(course_id),foreignkey(dept_name)referencesdepartment));createtable

instructor(

ID

char(5),

namevarchar(20)notnull,

dept_namevarchar(20),

salary

numeric(8,2),primarykey(ID),

foreignkey(dept_name)referencesdepartment));

createtable

section

(

course_idvarchar(7),

sec_id

varchar(8),

semester

varchar(20)notnull,

year

numeric(4,0),

buildingvarchar(15),

room_numbervarchar(7),

time_slot_idvarchar(4),primarykey(course_id,sec_id,

semester,year

),

foreignkey(course_id)referencescourse));

createtable

teaches

(

ID

char(5),

course_idvarchar(7),sec_id

varchar(8),

semestervarchar(20)notnull,

year

numeric(4,0),

primarykey(course_id,sec_id,semester,year

),

foreignkey(course_id,sec_id,semester,year)references

section

)

foreignkey(ID)referencesinstructor

);

createtable

student(

ID

varchar(5),

name

varchar(20)notnull,

dept_name

varchar(20),

tot_cred

numeric(3,0),

primarykey(ID),

foreignkey(dept_name)referencesdepartment));createtable

takes(

ID

varchar(5),

course_id

varchar(8),

sec_id

varchar(8),

semester

varchar(6),

year

numeric(4,0),

grade

varchar(2),

primarykey(ID,course_id,sec_id,semester,year),

foreignkey(ID)references

student,

foreignkey(course_id,sec_id,semester,year)

referencessection);关系的基本操作建立关系参照完整性:当删除或修改被参照元组而破坏参照完整性时，操作被拒绝事务回滚。例:当试图删除department表中dept_name为Comp.Sci的行时,因instructor中有dept_name为Comp.Sci的行存在，操作被拒绝事务回滚。级联删除

createtable

instructor……foreignkey(dept_name)referencesdepartment

ondeletecascade

onupdatesetnull,……)当删除department表中branch_name为Comp.Sci的行时,

instructor中dept_name为Comp.Sci的行同时被删除。而当被参照数据被修改时，参照数据被设为空（null）。关系的基本操作断言断言就是将复杂的约束条件(谓词)单独定义存储，在建立关系时可以直接使用。 createassertion<断言名>check<谓词>例:每个学生的学分数值必须等于该学生成功修完各课程的学分总和。目前大多数数据库系统不支持断言，对check的支持也有限。断言可以用触发器代替，多数系统支持触发器。删除关系：droptabler修改关系：altertableraddAD在关系r中增加域为D的属性A

altertablerdropA从关系r中去除属性A创建索引createindexstudentID_indexonstudent(ID);主键一般不需要建索引（数据按主键顺序存放），对主键以外的列建索引可以加快数据检索的速度。B+树是最常用的索引结构。SQL查询基本查询selectA1,A2,...,Anfrom

r1,r2,...,rmwherePselect子句select*frominstructor；

得到所有属性selectname,salary*1.1frominstructor;得到部分属性,可有算术表达式

selectdistinctdept_namefrominstructor;去除重复的元组（行）

where子句

selectname,salaryfrominstructorwheresalarybetween

50000and100000;可用notbetweenselectname,salaryfrominstructor

wheredept_name=

‘Comp.Sci.'

andsalary>70000;可以有比较运算符和逻辑运算符（and、or、not）SQL查询基本查询

from子句（多关系查询）例：查询所有教师名以及所在系部名和楼名。selectname,instructor.dept_name,building

frominstructor,department

where

instructor.dept_name=department.dept_name

可以用<表名.属性名>的方式引用指定特定表的属性，以区分同名属性。SQL查询更名运算与元组变量selectID,name,salary/12asmonthly_salaryas在此是运算符不能省

frominstructor；查询比计算机系某一个教师工资高的所有教师。selectdistinctT.name

frominstructorasT,instructorasSas在此是定义变量可以省

whereT.salary>S.salaryandS.dept_name=‘Comp.Sci.’SQL查询字符串运算用单引号表示字符串。串中的单引号可用双引号。‘It’sright’->‘It”sright’模式匹配运算符like

用百分号(%)匹配任意子串,下划线(_)匹配任意一个字符。

例：‘Perr%’匹配所有以“Perr”开头的字符串。

‘%idge%’匹配包含“idge”的任何字符串，如‘Perridge’、

‘RockRidge’、‘Ridgeway’等。selectdept_namefromdepartment

where

buildinglike'%Walson%’,

用escape定义转义字符表示字符串中的百分号或下划线。like‘ab\%cd%’

escape‘\‘匹配所有以“ab%cd”开头的字符串其他字符串函数

串联、提取子串、计算长度、大小写转换等。不同产品的字符串函数集差别较大。SQL查询排序子句orderbyA1desc，A2asc，A3

升序的asc可省略select*frominstructor

orderbysalary

desc，nameasc;集合运算union（集合并）、intersect（集合交）、except（集合差）

自动去除重复。用unionall可以保留重复。聚集函数

共5个sum：返回集合中各元素值的和。avg：返回集合中各元素值的平均值。count：返回集合中元素的个数。min：返回集合中的最小值。max：返回集合中的最大值。SQL查询聚集函数having子句

指定分组后的选择条件(必须与groupby同时用)例:查找显示平均工资大于42000美元的系部名和平均工资。selectdept_name,avg(salary)frominstructorgroupbydept_namehavingavg(salary)>42000;

条件只对分组内的行测试SQL查询聚集函数having子句

where与having同时存在时,先找出满足where条件的所有行，然后分组，再对各分组测试having条件，得到满足条件的分组并输出。例:找出2009年讲授的有2名以上学生选修的课程，显示学生总学分的平均值。select

course_id,semester,year,sec_id,avg(tot_cred)

fromtasknaturaljoinstudentwhereyear=2009

groupbycourse_id,semester,year,sec_id

havingcount(ID)>=2;

SQL查询course_idsemesteryearsec_idavg(tot_cred)CS-101Fall20091SQL：P48，表数据：P63-64作业：将SQL结果填入此表SQL查询空值null

表示值为空

selectIDfrominstructor

where

salary

isnull也可以用isnotnullunknown表示未知(即不是null

也不是isnull)嵌套子查询连接词in:测试元组是否是集合中的成员。notin是否不是集合成员。例:显示2009年秋季和2010年春季都开设的所有课程号的集合。

selectdistinctcourse_idfromsectionwheresemester=’Fall’andyear=2009and

course_idin(selectcourse_id

fromsection

wheresemester=’Spring’andyear=2010);也可以从常量中选择selectdistinctname

fromsalary

wherenamenotin(‘Mozart’,’Einstein’);嵌套子查询集合的比较any:集合中的任一个。早期SQL中使用，现在等同于some。some:集合中的某一个。all:集合中的全部。例1:查询比生物系某一教师工资高的所有教师。selectnamefrominstructorwheresalary>some(selectsalaryfrominstructorwheredept_name=’Biology’);例2:查询平均工资最高的系部。selectdept_amefrominstructorgroupbydept_amehavingavg(salary)>=all

(selectavg(salary)frominstructorgroupbydept_ame);

嵌套子查询测试是否为空关系(记录存在测试)exists:存在测试例:显示2009年秋季和2010年春季都开设的所有课程号的集合。selectcourse_id

fromsectionasS

wheresemester=’Fall’andyear=2009and

exists(select*

fromsectionasT

wheresemester=’Spring’andyear=2010

andS.course_id=T.course_id);notexists:不存在测试例:找出选修了生物系所有课程的学生。selectdistinctS.ID,S.namefromstudentasSwherenotexists((selectcourse_id当生物系所有课程被全部减掉时fromcoursewheredept_name=’Biology’)生物系开设的课程集

except减去(selectT.course_idfromtakesasTwhereS.ID=T.ID));学生S.ID所修的所有课程集嵌套子查询测试是否存在重复元组unique:没有重复测试(唯一)例:找出所有在2009年最多开设一次的课程。selectT.course_id

fromcourseasT

whereunique(selectR.course_id

fromsectionasR

whereT.course_id=R.course_id

andR.year=2009);

notunique:有重复测试(不唯一)例:找出所有在2009年最少开设二次的课程。selectT.course_id

fromcourseasT

wherenotunique(selectR.course_id

fromsectionasR

whereT.course_id=R.course_id

andR.year=2009);

复杂查询from子句中子查询（派生关系）将from中子查询产生的集合命名为临时的关系例1:查找显示平均工资大于42000美元的系部名和平均工资。不用having。

selectdept_name,avg_salary

from(selectdept_name,avg(salary)asavg_salary

frominstructor

groupbydept_name)

whereavg_salary>42000;例2:找出工资总额最大的系部的工资总额。selectmax(tot_salary)

from(selectdept_name,sum(salary)

from

instructor

groupby

dept_name)

asdept_total(dept_name,tot_salary);

复杂查询with子句定义临时关系（视图）的子查询。例1:找出预算额最大的系部。withmax_budget(value)as

(selectmax(budget)

fromdepartment)

selectbudget

fromdepartment,max_budget

wheredepartment.budget=max_budget.value;部分数据库系统不支持with子句。

例3:找出工资总额大于所有系部平均工资总额的系部。withdept_total(dept_name,value)as各系部工资总额(selectdept_name,sum(salary)

frominstructorgroupbydept_name),dept_total_avg(value)as第二个with,平均工资总额(selectavg(value)fromdept_total)selectdept_name大于平均工资总额的系部fromdept_total,dept_total_avgwheredept_total.value>=dept_total_avg.value;视图视图：在实际关系基础上产生的对用户可见的虚关系。主要作为特定查询数据的临时表结构（执行时检索，不存储）。视图定义createviewvas<查询表达式>。例:建立物理系2009年秋季所有课程及上课地点的视图。createviewphysics_fall_2009

as

selectcourse.course_id,sec_id,building,room_number

fromcourse,section

wherecourse.course_id=section.course_id

andcourse.dept_name=’Physics’

andsection.semester=’Fall’

andsection.year=’2009’;视图使用和普通表一样。例：查询物理系2009年秋季在Walson楼里上课的所有课程。

selectcourse_id

fromphysics_fall_2009

wherebuilding=‘Walson’;视图物化视图一旦视图引用的关系的数据发出变化，视图的数据也应该随之更新。一般情况下数据库系统只存储视图定义，在被引用时用定义替换。特定数据库系统允许视图关系被存储，称为物化视图。当实际关系发生变化时，数据库系统负责进行物化视图的及时更新，称为视图维护。视图更新视图是关系的映射，更新会使原关系中的数据出现问题，所称一般情况下不允许更新。如果满足下列条件的情况下可以更新：form字句中只有一个关系；select

子句中只包含属性名，没有表达式、聚集或distinct声明；select

子句中的属性都可以取空值，即属性没有notnull约束，也不是主码的一部分；查询中不包含groupby或having子句。

数据库的修改插入

按关系定义时的属性顺序插入

insertintocourse

values(’CS-437’,’DatabaseSystems’,’Comp.Sci.’,4);insertintostudent

values(’3003’,’Green’,’Finance’,null);

不违反约束时可以有空值按指定的属性顺序插入insertintocourse(course_id,title,dept_name,credits)

values(’CS-437’,’DatabaseSystems’,’Comp.Sci.’,4);从其他表检索数据插入insertintostudent

selectID,name,dept_name,0

from

instructor删除

deletefrominstructor;

删除全部数据

deletefrominstructor

wheredept_name=’Finance’;

删除满足条件的数据

deletefrominstructor

wheredept_namein(selectdept_name

fromdepartment

wherebuilding=’Watson’);数据库的修改更新例：年度工资增长，10万元以上的教师涨3%，10万元及以下的教师涨5%。

updateinstructor

setsalary=salary*1.03

wheresalary>100000;

updateinstructor

setsalary=salary*1.05

wheresalary<=100000;顺序很关键，顺序不同结果不同。

updateinstructor

setsalary=case

whensalary<=100000thensalary*1.05

elsesalary*1.03

end事务

事务：一段查询和（或）更新语句（一条或多条SQL语句）的整体。commitwork：提交当前事务。rollbackwork：回滚当前事务。连接关系连接类型与条件

开设课程表course

预备课程表prereq

course中”CS-315”在prereq中没有对应的数据，

prereq中”CS-347”在course中没有对应的数据。连接类型内连接innerjoin左外连接leftouterjoin右外连接rightouterjoin全外连接fullouterjoin连接类型自然连接(以同名属性相等为条件)natural条件连接on<谓词>using(A1,A2,…,An)连接关系

例1:on条件内连接courseinnerjoinprereq

oncourse.course_id=prereq.course_id

例2:on条件左外连接

courseleftouterjoin

prereq

oncourse.course_id=prereq.course_id

例3:on条件右外连接

courserightouterjoin

prereq

oncourse.course_id=prereq.course_id连接关系

例4:自然连接coursenaturaljoinprereq注意与

innerjoin

on的不同

例5:自然左外连接

coursenaturalleftouterjoin

prereq

例6:自然右外连接

coursenaturalrightouterjoin

prereq连接关系

例7:自然全外连接coursenaturalfullouterjoin