数据库第四章

数据库系统原理1陈岭浙江大学计算机学院关系模型2SQL查询的基本结构集合运算空值聚集函数嵌套子查询数据库的修改SQL语言（2）SQL查询的基本结构3SQL查询的基本结构由3个子句构成：select，from，whereSELECTA1,A2,...,AnFROMr1,r2,...,rmWHEREP上述查询语句等价于关系代数表达式：―A1,A2,...,An(P(r1xr2x...xrm))查询的输入是在from子句中列出的关系，在这些关系上进行where和select子句中指定的运算，然后产生一个关系作为结果select子句4例，找出所有教师的名字selectnamefrominstructor;表示成关系代数表达式为：name(instructor)注意：SQL不允许在属性名称中使用字符‘-’，例如，使用dept_name代替dept-nameSQL不区分字母的大小写。因此，你可以使用大写字母或小写字母命名表、属性等select子句5SQL允许在关系以及SQL表达式结果中出现重复的元组若要强行去除重复，可在select后加入关键词distinct例，查询instructor关系中的所有系名，并去除重复selectdistinctdept_namefrominstructor;SQL也允许我们使用关键词all来显式指明不去除重复(SQL默认就是all)例，selectalldept_namefrominstructorselect子句6星号“*”在select子句中，可以用来表示“所有的属性”例，select*frominstructor;select子句还可带含有+、-、*、/运算符的算术表达式，运算对象可以是常数或元组的属性例，selectID,name,salary*1.05frominstructor;where子句7where子句允许我们只选出那些在from子句的结果关系中满足特定谓词的元组例，找出所有在ComputerScience系并且工资超过70000美元的教师的姓名selectnamefrominstructorwheredept_name=‘Comp.Sci.’andsalary70000;上述SQL查询语句，对应的关系代数表达式为：name(dept_name=‘Comp.Sci.’^salary70000(instructor))where子句8SQL允许在where子句中使用逻辑连词and，or和not，也可以使用between指定范围查询。逻辑连词的运算对象可以是包含比较运算符、=、、=、=和的表达式例，找出工资在90000美元和100000美元之间的教师的姓名selectnamefrominstructorwheresalary=100000andsalary=90000;或者：selectnamefrominstructorwheresalarybetween90000and100000;from子句9from子句是一个查询求值中需要访问的关系列表，通过from子句定义了一个在该子句中所列出关系上的笛卡尔积例，找出关系instructor和teaches的笛卡尔积select*frominstructor，teaches;from子句10例，找出ComputerScience系的教师名和课程标识selectname,course_idfrominstructor,teacheswhereinstructor.ID=teaches.IDandinstructor.dept_name=‘Comp.Sci.’;这个前缀是必要的instructor(ID,name,dept_name,salary)teaches(ID,course_id,sec_id,semester,year)更名运算11SQL提供可为关系和属性重新命名的机制，即使用as子句：old-nameasnew-nameas子句既可以出现在select子句中，也可以出现在from子句中例，考虑刚刚的查询,将属性name重命名为instructor_nameselectnameasinstructor_name,course_idfrominstructor,teacheswhereinstructor.ID=teaches.IDandinstructor.dept_name=‘Comp.Sci.’;更名运算12使用更名运算，对关系重命名例，找出所有教师，以及他们所讲授课程的标识selectT.name,S.course_idfrominstructorasT,teachesasSwhereT.ID=S.ID;例，找出所有教师名，他们的工资至少比Biology系某一个教师的工资要高selectdistinctT.namefrominstructorasT,instructorasSwhereT.salaryS.salaryandS.dept_name=‘Biology’;为了引用简洁为了区分字符串运算13对字符串进行的最通常的操作是使用操作符like的模式匹配，使用两个特殊的字符来描述模式：百分号（%）：匹配任意子串下划线（_）：匹配任意一个字符例，找出所在建筑名称中包含子串‘Watson’的所有系名selectdept_namefromdepartmentwherebuildinglike‘%Watson%’;字符串运算14为使模式中能够包含特殊字符（即%和_），SQL允许定义转义字符。我们在like比较运算中使用escape关键词来定义转义字符例，使用反斜线（\）作为转义字符―like‘ab\%cd%’escape‘\’匹配所有以“ab%cd”开头的字符串―like‘ab\\cd%’escape‘\’匹配所有以“ab\cd”开头的字符串SQL还允许在字符串上有多种函数，例如串联(“||”)、提取子串、计算字符串长度、大小写转换（用upper（s）将字符串s转换为大写或用lower（s）将字符串s转换为小写）、去掉字符串后面的空格（使用trim（s））等等排列元组的显示次序15SQL为用户提供了一些对关系中元组显示次序的控制。orderby子句就可以让查询结果中元组按排列顺序显示例，按字母顺序列出在Physics系的所有教师selectnamefrominstructorwheredept_name=‘Physics’orderbyname;排列元组的显示次序16orderby子句默认使用升序。要说明排序顺序，我们可以用desc表示降序，或者用asc表示升序例，按salary的降序列出整个instructor关系，如果有几位教师的工资相同，就将他们按姓名升序排列select*frominstructororderbysalarydesc,nameasc;重复17在关系模型的形式化数学定义中，关系是一个集合。因此，重复的元组不会出现在关系中。但在实践中，包含重复元组的关系是有用的可以用关系运算符多重集版本（Multisetversions）来定义SQL查询的复本定义，在此定义几个关系代数运算符的多重集版本，已知多重集关系r1和r2(r1):如果在r1中有元组t1的c1个复本，而且t1满足选择，那么有c1个t1的复本在(r1)中A(r):对于r1中t1的每个复本，在A(r1)中都有一个A(t1)的复本与其对应，其中A(t1)表示单个元组t1的投影r1xr2:如果有c1个t1的复本在r1中且有c2个t2的复本在r2中，那么有c1*c2个t1·t2元组的复本在r1xr2中重复18例，假设多重集关系r1(A,B)和r2(C)如下所示：r1={(1,a)(2,a)}r2={(2),(3),(3)}那么，B(r1)={(a),(a)},则B(r1)xr2为：{(a,2),(a,2),(a,3),(a,3),(a,3),(a,3)}SQL中的select子句也支持关系代数运算符的多重集版本：、A、x，形如selectA1,,A2,...,Anfromr1,r2,...,rmwhereP;的SQL查询等价于关系代数表达式（多重集版本）：A1,A2,...,An(P(r1xr2x...xrm))集合运算19SQL作用在关系上的union、intersect和except运算对应于数学集合论中的和运算union、intersect和except运算与select子句不同，它们会自动去除重复如果想保留所有重复，必须用unionall、intersectall和exceptall假设一个元组在关系r中重复出现了m次，在关系s中重复出现了n次，那么这个元组将会重复出现：在runionalls中，重复出现m+n次在rintersectalls中，重复出现min（m，n）次在rexceptalls中，重复出现max（0，m-n）次当mn当mn集合运算20例1，找出在2009年秋季开课，或者在2010年春季开课或两个学习都开课的所有课程(selectcourse_idfromsectionwheresemester=‘Fall’andyear=2009)union(selectcourse_idfromsectionwheresemester=‘Spring’andyear=2010);section(course_id,sec_id,semester,year,building,room_number,time_slot_id)集合运算21例2，找出在2009年秋季和2010年春季同时开课所有课程(selectcourse_idfromsectionwheresemester=‘Fall’andyear=2009)intersect(selectcourse_idfromsectionwheresemester=‘Spring’andyear=2010);集合运算22例3，找出在2009年秋季开课，但不在2010年春季开课的所有课程(selectcourse_idfromsectionwheresemester=‘Fall’andyear=2009)except(selectcourse_idfromsectionwheresemester=‘Spring’andyear=2010);集合运算23在Oracle中，支持union，unionALL，intersect和Minus；但不支持IntersectALL和MinusALL在SQLServer2000中，只支持union和unionALL聚集函数24聚集函数是以值的一个集合（集或多重集）为输入，返回单个值的函数。SQL提供了五个固有聚集函数：平均值：avg最小值：min最大值：max总和：sum计数：count其中，sum和avg的输入必须是数字集，但其他运算符还可作用在非数字数据类型的集合上，如字符串聚集函数25除了上述的五个基本聚集函数外，还有分组聚集（groupby）。groupby子句中给出的一个或多个属性是用来构造分组的，在groupby子句中的所有属性上取值相同的元组将被分在一个组中having子句类似于where子句，但其是对分组限定条件，而不是对元组限定条件。having子句中的谓词在形成分组后才起作用，因此可以使用聚集函数聚集函数26例1，找出ComputerScience系教师的平均工资selectavg(salary)asavg_salaryfrominstructorwheredept_name=‘Comp.Sci.’;上述SQL查询等价于关系代数表达式：gavg(salary)(dept_name=‘Comp.Sci’(instructor)avg_salary77333聚集函数27例2，找出每个系的平均工资selectdept_nameavg(