ch6关系数据库管理系统的数据操纵及相关SQL语句

2020/2/25南京大学计算机科学与技术系艾文凯1第六章关系数据库管理系统的数据操纵及相关SQL语句2020/2/25南京大学计算机科学与技术系艾文凯26.1RDBMS数据操纵功能介绍1．查询功能查询一般应具有如下的功能：（1）单表的查询功能根据指定的列及行条件可查询到表中的最小粒度。（2）多表查询功能通过表间关联由指定某表的已知条件查到另一些表的元组或属性。（3）单表自关联查询通过单表内某些列的关联作单表的嵌套查询。（4）查询的组合查询可将若干个查询组合成一个查询。2020/2/25南京大学计算机科学与技术系艾文凯3RDBMS数据操纵功能2．增、删、改功能（1）定位增加操作——定位为：表。删除操作——定位为：表，元组。修改操作——定位为：表，元组。（2）操作增加操作——给出所增加的元组以及实施删除操作——无需给出数据，仅实施该操作。修改操作——给出需修改的数据要求，并实施2020/2/25南京大学计算机科学与技术系艾文凯4RDBMS数据操纵功能3．其它功能（1）赋值功能。在数据操纵过程中所产生的一些中间结果以及需作永久保留的结果，必须以新的关系形式存储于数据库内，因此对这些新关系须予以命名并赋值，经赋值后的关系今后在数据库中即可供用户使用。（2）计算功能。在数据操纵中还需一些计算功能：简单的四则运算、统计功能、分类功能（3）输入/出功能。关系数据库管理系统一般提供标准的数据录入与输出功能。2020/2/25南京大学计算机科学与技术系艾文凯5对查询的基本要求在关系代数中可以用下式表示：∏r1,r2,…rmσF(R1R2…Rn)在这个公式中有三种基本参数，它们是：查询的目标属性：r1,r2,…rm；查询所涉及的关系：R1，R2…，Rn；查询的逻辑条件：F。6.2SQL的操纵功能2020/2/25南京大学计算机科学与技术系艾文凯6这三种参数可以用SQL中的基本语句的三个子句（SELECT，FROM及WHERE）表示.其中SELECT子句给出查询的目标属性，FROM子句给出查询所涉及的关系，而WHERE子句则给出查询的逻辑条件，它们可以用下面形式表示：SELECT（列名）[,（列名）]FROM（基表名）[,（基表名）]WHERE（逻辑条件）2020/2/25南京大学计算机科学与技术系艾文凯7SQL数据查询功能映像语句SELECT*|colname{,colname...}FROMtablename{,tablename...}[WHEREsearch_condition][GROUPBYcolname{,colname...}[HAVINGgroup_condition]][ORDERBYcolname[ASC|DESC]{,colname[ASC|DESC]...}];目标子句：范围子句：条件子句：分组子句：分组查询子句：排序输出子句：2020/2/25南京大学计算机科学与技术系艾文凯8FROMtablename{,tablename...}SQL数据操纵功能映像语句各子句的执行顺序[WHEREsearch_condition][GROUPBYcolname{,colname...}][GROUPBYcolname{,colname...}[HAVINGgroup_condition]]SELECT*|colname{,colname...}[ORDERBYcolname[ASC|DESC]{,colname[ASC|DESC]...}];2020/2/25南京大学计算机科学与技术系艾文凯96.2.1SQL的查询功能映像语句的组成1)目标子句：SELECT*|colname{,colname...}–定义结果关系所需要的属性目标子句的构造方式–给出结果属性的属性名可以通过‘表名.属性名’的方式来表明是哪一张表中的属性结果属性的重命名»column_expressionAScolname–可用‘*’来代替表中的所有属性–可使用保留字‘distinct’(‘unique’)来消除结果关系中的重复元组2020/2/25南京大学计算机科学与技术系艾文凯106.2.1SQL的查询功能映像语句的组成(cont.)2)范围子句：FROMtablename{,tablename...}–指定操作对象（被访问的关系）可以在FROM子句中对一个关系重新命名(即定义一个别名(alias))table_namealias_name–主要用于关系自身的联接运算‘SELECT子句’和‘FROM子句’是一条映像语句中必不可少的两个组成部分。2020/2/25南京大学计算机科学与技术系艾文凯116.2.1SQL的查询功能映像语句的组成(cont.)3)条件子句：WHEREsearch_condition–是映像语句中的可选成分，用于定义查询条件（即结果关系中的元组必须满足的条件）–包括‘单个关系中的元组选择条件’以及‘关系与关系之间的联接条件’都需要在WHERE子句中通过一定的逻辑表达式显式地表示出来。在FROM子句中给出的关系只是表明此次查询需要访问这些关系，它们之间是通过笛卡儿乘积运算进行合并的。如果需要执行它们之间的‘-联接’或‘自然联接’运算，则需要在WHERE子句中显式地给出它们的联接条件。2020/2/25南京大学计算机科学与技术系艾文凯126.2.1SQL的查询功能映像语句的组成(cont.)WHERE子句中的查询条件除了可以使用常见的一些算术比较运算符之外，在SQL中还引入了一些其它的比较谓词，以扩充WHERE子句的表达能力。WHERE子句中的谓词–TheINPredicateexpr[NOT]IN(subquery)–TheQuantifiedComparisonPredicateexprSOME|ANY|ALL(subquery)2020/2/25南京大学计算机科学与技术系艾文凯136.2.1SQL的查询功能映像语句的组成(cont.)WHERE子句中的谓词(cont.)–TheEXISTSPredicate[NOT]EXISTS(subquery)–TheBETWEENPredicateexpr[NOT]BETWEENexpr1ANDexpr2–TheISNULLPredicatecolumnIS[NOT]NULL2020/2/25南京大学计算机科学与技术系艾文凯146.2.1SQL的查询功能映像语句的组成(cont.)WHERE子句中的谓词(cont.)–TheLIKEPredicatecolumn[NOT]LIKEval1[ESCAPEval2]模版(pattern)：val1»underscore(_):anysinglecharacter»percent(%):anysequenceofzeroormorecharacters»others:representthemselves转义指示字符：val2»紧跟在转义指示字符val2之后的‘_’或‘%’不再是通配符，而是其自身。2020/2/25南京大学计算机科学与技术系艾文凯156.2.1SQL的查询功能SQL查询功能的例子S（S＃，Sn，Sa，Sd）C（C＃，Cn，P＃）SC（S＃，C＃，G）2020/2/25南京大学计算机科学与技术系艾文凯166.2.1SQL的查询功能1单个表上的查询例1：查询S的所有情况【answer1】SELECTS＃,Sn,Sa,SdFROMS;【answer2】SELECT*FROMS;例2：查询所有年龄大于20岁的学生的姓名与学号SELECTSn,S#FROMSWHERESa20;例3：查询所有选修了课程的学生学号SELECTDISTINCTS#FROMSC;2020/2/25南京大学计算机科学与技术系艾文凯176.2.1SQL的查询功能例4：查询年龄在18至21岁（包括18与21岁）的学生姓名与年龄例5：查询年龄不在18至21岁的学生姓名与年龄SELECTSn,SaFROMSWHERESaBETWEEN18AND21;SELECTSn,SaFROMSWHERESaNOTBETWEEN18AND21;2020/2/25南京大学计算机科学与技术系艾文凯186.2.1SQL的查询功能例6：查询姓名以A打头的学生姓名与系别SELECTSn,SdFROMSWHERESnLIKE‘A％’;例7：查询姓名以A打头，且第三个字符为P的学生姓名与系别SELECTSn,SdASDepartmentFROMSWHERESnLIKE‘A_P％’;2020/2/25南京大学计算机科学与技术系艾文凯196.2.1SQL的查询功能例8：查询无课程分数的学号与课程号SELECTS#,C#FROMSCWHEREGISNULL;2020/2/25南京大学计算机科学与技术系艾文凯206.2.1SQL的查询功能2多表联接查询例9：查询修读课程号为C1的所有学生的姓名SELECTS.SnFROMS,SCWHERESC.S#=S.S#ANDSC.C#=‘C1’;例10：查询修读课程名为DATABASE的所有学生的姓名SELECTS.SnFROMS,SC,CWHERES.S#=SC.S#ANDSC.C#=C.C#ANDC.Cn=‘DATABASE’;2020/2/25南京大学计算机科学与技术系艾文凯216.2.1SQL的查询功能例13：查询至少修读S5所修读的一门课的学生学号首先从选课（SC）关系中查到S5所修过课程的课程号，然后再用这些课程号到选课（SC）关系中查出有哪些学生修过其中的课程。–因此，这是一个表与表自身进行联接的查询，同一个表名（SC）需要在FROM子句中出现两次。–为了区分开它们，必须至少对其中的一个（SC）进行换名。SELECTSC1.S#FROMSCSC1，SCSC2WHERESC1.C#=SC2.C#ANDSC2.S#=‘S5’；2020/2/25南京大学计算机科学与技术系艾文凯22表的连接内连接内连接只从叉积中返回满足连接条件的行。如果某一行在一个表中存在，但不在另一张表中，那么结果集中将不包括这一行。内连接可以通过在FROM子句中用naturalJOIN操作符来实现。2020/2/25南京大学计算机科学与技术系艾文凯23外连接外连接返回的是内连接操作生成的行，以及内连接操作无法返回的行。外连接共有三类：左外连接包括内连接和在左表中但内连接不会返回的那些行。这类连接在FROM子句中使用LEFTOUTERJOIN（或LEFTJOIN）操作符。右外连接包括内连接和在右表中但内连接不会返回的那些行。这类连接在FROM子句中使用RIGHTOUTERJOIN（或RIGHTJOIN）操作符。全外连接包括内连接和在左表和右表中但内连接不会返回的那些行。这类连接在FROM子句中使用OUTERJOIN操作符。在ORACLE中，外连接可以通过在WHERE子句中：（+）的使用来使用，例如：表A与表B的左连接A.FIELD1（+）=B.FIELD1，右连接A.FIELD1=B.FIELD1（+）。2020/2/25南京大学计算机科学与技术系艾文凯24C11C12A11B12C13C21C22A21C22D23表：T1表：T22020/2/25南京大学计算机科学与技术系艾文凯25C11C12C21C22A11A21C13C22SELECT*FROMtab1naturalJOINtab2ONc11=c212020/2/25南京大学计算机科学与技术系艾文凯26SELECT*FROMtab1RIGHTOUTERJOINtab2ONc11=c21C11C12C21C22A11A21C13C22------D232020/2/25南京大学计算机科学与技术系艾文凯27C11C12C21C22A11A21B12------C13C22SELECT*FROMtab1LEFTOUTERJOINtab2ONc11=c212020/2/25南京大学计算机科学与技术系艾文凯28C11C12C21C22A11