SQL数据操作功能.ppt

第5章SQL语言数据操作功能,5.1数据查询功能5.2数据更改功能5.3视图,5.1数据查询功能,5.1.1查询语句的基本结构5.1.2简单查询5.1.3多表连接查询5.1.4使用TOP限制结果集5.1.5子查询,5.1.1查询语句的基本结构SELECTALL|DISTINCT,-需要哪些列FROM（或视图）-来自哪些表WHERE-根据什么条件GROUPBYHAVING内部函数表达式ORDERBYASC|DESC；语句含义：根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。,如果有ORDER子句，则结果表要根据指定的列名2按升序或降序排序；如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中作用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。,5.1.2简单查询,1.选择表中若干列（1）查询表中用户感兴趣的部分属性列。例1：查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent例2：查询全体学生的姓名、学号和所在系。SELECTSname，Sno，SdeptFROMStudent,（2）查询全部列例3查询全体学生的记录SELECTSno，Sname，Ssex,Sage,SdeptFROMStudent等价于：SELECT*FROMStudent,（3）查询经过计算的列例4查询全体学生的姓名及其出生年份。SELECTSname，2010-SageFROMStudent结果为如下形式：Sname2010-Sage-S11984S21983S31985S41985例5含字符串常量的列：查询全体学生的姓名和出生年份，并在出生年份列前加一列，此列的每行数据均为“出生年份”常量值。SELECTSname，出生年份,2010-SageFROMStudent,（4）改变列标题语法：列名|表达式AS列标题或：列标题列名|表达式例：SELECTSname姓名,2008-Sage年份FROMStudent,（5）消除取值相同的记录例6在修课表中查询有哪些学生修了课程，要求列出学生的学号。SELECTSnoFROMSC结果中有重复的行。用DISTINCT关键字可以去掉结果中的重复行。DISTINCT关键字放在SELECT词的后边、目标列名序列的前边。SELECTDISTINCTSnoFROMSC,2.查询满足条件的元组,（1）比较大小,例7查询计算机系全体学生的姓名。SELECTSnameFROMStudentWHERESdept=计算机系例8查询年龄在20岁以下的学生的姓名及年龄。SELECTSname,SageFROMStudentWHERESage20例9查询考试成绩有不及格的学生的学号SELECTDISTINCTSnoFROMSCWHEREGrade=20ANDSage=4,说明,WHERE子句用来筛选FROM子句中指定的数据源所产生的行数据。GROUPBY子句用来对经WHERE子句筛选后的结果数据进行分组。HAVING子句用来对分组后的结果数据再进行筛选。对于可在分组操作之前应用的搜索条件，在WHERE子句中指定它们更有效，这样可减少参与分组的数据行。应当在HAVING子句中指定的搜索条件应该是那些必须在执行分组操作之后应用的搜索条件。建议将所有行搜索条件放在WHERE子句中而不是HAVING子句中。,例如，查询计算机系和信息管理系的学生人数：SELECTSdept,COUNT(*)FROMStudentGROUPBYSdeptHAVINGSdeptin(计算机系,信息管理系)或：SELECTsdept,COUNT(*)FROMStudentWHERESdeptin(计算机系,信息管理系)GROUPBYSdept第二种写法比第一种写法效率要高，因为参与分组的数据会比较少。,5.1.3多表连接查询,若一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询，包括内连接、外连接和交叉连接等。连接查询中用于连接两个表的条件称为连接条件或连接谓词。一般格式为：=,必须是可比的,1.内连接,SQL-92内连接语法如下：SELECTFROM表名INNERJOIN被连接表ON连接条件执行过程如下：首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2，重复该过程，直到表1中的全部元组都处理完为止。,例39查询每个学生及其选课的详细信息。SELECT*FROMStudentINNERJOINSCONStudent.Sno=SC.Sno结果中有重复的列。例40去掉例39中的重复列。SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.Sno,例41查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。SELECTSname,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept=计算机系可为表提供别名，其格式如下：AS例如，使用别名时例41可写为如下形式：SELECTSname,Cno,GradeFROMStudentSJOINSCONS.Sno=SC.SnoWHERESdept=计算机系,例42查询“信息管理系”选修了“计算机文化学”课程的学生的成绩，要求列出学生姓名、课程名和成绩。SELECTSname,Cname,GradeFROMStudentsJOINSCONs.Sno=SC.SnoJOINCoursecONc.Cno=SC.CnoWHERESdept=信息管理系ANDCname=计算机文化学,例43查询所有选修了VB课程的学生的修课情况，要求列出学生姓名和所在的系。SELECTSname,SdeptFROMStudentSJOINSCONS.Sno=SC.SnoJOINCourseCONC.Cno=SC.cnoWHERECname=VB,例44有分组的多表连接查询。查询每个系的学生的考试平均成绩。SELECTSdept,AVG(grade)asAverageGradeFROMstudentSJOINSCONS.Sno=SC.SnoGROUPBYSdept,例45有分组和行过滤的多表连接查询。查询计算机系每门课程的选课人数、平均成绩、最高成绩和最低成绩。SELECTCno,COUNT(*)ASTotal,AVG(Grade)asAvgGrade,MAX(Grade)asMaxGrade,MIN(Grade)asMinGradeFROMStudentSJOINSCONS.Sno=SC.SnoWHERESdept=计算机系GROUPBYCno,2、自连接,是一种特殊的内连接。相互连接的表物理上为同一张表。必须为两个表取别名，使之在逻辑上成为两个表。例46查询与刘晨在同一个系学习的学生的姓名和所在系。SELECTS2.Sname,S2.SdeptFROMStudentS1JOINStudentS2ONS1.Sdept=S2.SdeptWHERES1.Sname=刘晨ANDS2.Sname!=刘晨,3、外连接,在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，但是有时我们想以S表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况信息，其选课信息为空值即可，这时就需要使用外连接（OuterJoin）。外连接的运算符通常为*。有的关系数据库中也用+。,ANSI方式的外连接的语法格式为：FROM表1LEFT|RIGHTOUTERJOIN表2ONtheta方式的外连接的语法格式为：左外连接：FROM表1,表2WHERE表1.列名(+)表2.列名右外连接：FROM表1,表2WHERE表1.列名表2.列名(+),例47查询学生的选课情况，包括选修了课程的学生和没有选修课程的学生。SELECTStudent.Sno,Sname,Cno,GradeFROMStudentLEFTOUTERJOINSCONStudent.Sno=SC.Sno例48查询哪些课程没有人选，列出课程名。SELECTCnameFROMCourseCLEFTJOINSCONC.Cno=SC.CnoWHERESC.CnoISNULL,5.1.4使用TOP限制结果集,在进行查询时有时只希望列出结果集中的前几个结果，而不是全部结果。例如，竞赛时可能只取成绩最高的前三名.可以使用TOP谓词限制输出的结果。格式如下:TOPnpercentWITHTIESn为非负整数。TOPn：表示取查询结果的前n行；TOPnpercnet：表示取查询结果前n%行；WITHTIES：表示包括并列的结果。,例49查询年龄最大的三个学生的姓名、年龄及所在的系。SELECTTOP3Sname,Sage,SdeptFROMStudentORDERBYSageDESC若包括年龄并列第3名的学生，则：SELECTTOP3WITHTIESSname,Sage,SdeptFROMStudentORDERBYSageDESC例50查询VB课程考试成绩前三名的学生的姓名和成绩。SELECTTOP3WITHTIESSname,GradeFROMStudentSJOINSConS.Sno=SC.SnoJOINCourseCONC.Cno=SC.CnoWHERECname=VBORDERBYGradeDESC,4.1.5子查询,一个SELECT语句称为一个查询块。子查询是一个SELECT查询，它嵌套在SELECT、INSERT、UPDATE、DELETE语句的WHERE或HAVING子句内，或其它子查询中。子查询的SELECT查询使用圆括号括起来。子查询语句可以出现在任何能够使用表达式的地方，通常情况下，子查询语句用在外层查询的WHERE子句或HAVING子句中。,1.使用子查询进行基于集合的测试,使用子查询进行基于集合的测试的语句的一般格式为：列名NOTIN(子查询),例51.查询与刘晨在同一个系的学生。SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨)ANDSname!=刘晨,例52.查询成绩为大于90分的学生的学号、姓名。SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHEREGrade90),例53.查询选修了“VB”课程的学生的学号、姓名。SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname=VB),例54查询选修了VB课程的学生的选课门数和平均成绩。SELECTSno学号,COUNT(*)选课门数,AVG(Grade)平均成绩FROMSCWHERESnoIN(SELECTSnoFROMSCJOINCourseCONC.Cno=SC.CnoWHERECname=VB)GROUPBYSno,2使用子查询进行比较测试,使用子查询进行比较测试时，通过比较运算符（=、(SELECTAVG(Sage)FROMStudentWHERESdept=计算机系),3.使用子查询进行存在性测试,通常用EXISTS谓词，其形式如下：WHERENOTEXISTS（子查询）带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值和逻辑假值。EXISTS：当子查询中有满足条件的数据时，返回真值，否则返回假值。NOTEXISTS：当子查询中有满足条件的数据时，返回假值；否则返回真值。,例57查询选修了C002课程的学生姓名。SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=C002),注意,注1：处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外层结果数决定。注2:由于EXISTS的子查询只能返回真或假值，因此在这里给出列名无意义。所以在有EXISTS的子查询中，其目标列表达式通常都用*。,例57的处理过程,1.找外层表Student表的第一行，根据其Sno值处理内层查询2.由外层的值与内层的结果比较，由此决定外层条件的真、假3.顺序处理外层表Student表中的第2、3、行。,例58查询没有选修C001课程的学生姓名和所在系。用多表连接实现（数据有误）SELECTDISTINCTSname,SdeptFROMStudentSJOINSCONS.Sno=SC.SnoWHERECno!=C001,例58示例（续）,用嵌套子查询实现在子查询中否定（数据有误）SELECTSname,SdeptFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno!=C001)在外层查询中否定SELECTSname,SdeptFROMStudentWHERESnoNOTIN(SELECTSnoFROMSCWHERECno=C001),例58示例（续）,用相关子查询实现在子查询中否定（数据有误）SELECTSname,SdeptFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno!=C001)在外层查询中否定SELECTSname,SdeptFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=C001),例59查询计算机系没有选修VB课程的学生的姓名和性别。SELECTsname,ssexFROMStudentWHEREsnoNOTIN(SELECTsnoFROMSCJOINCourseONSC.cno=CoWHEREcname=VB)ANDsdept=计算机系,5.2数据更改功能,5.2.1插入数据5.2.2更新数据5.2.3删除数据,5.2.1插入数据,插入单行记录的INSERT语句的格式为：INSERTINTO()VALUES（值表）功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序或列名表顺序赋给对应列名。,注意,值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。如果后边没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）。,例1将一个新生插入到Student表中，学号：0621105，姓名：陈冬，性别：男，年龄18岁，信息管理系学生。INSERTINTOStudentVALUES(0621105,陈冬,男,信息管理系,18)例2在SC表中插入一条新记录，学号为“0621105”，选修的课程号为“C001”，成绩暂缺。INSERTINTOSC(Sno,Cno)VALUES(0621105,C001),5.2.2更新数据,用UPDATE语句实现。格式：UPDATESET,nWHERE需要修改数据的表的名称。SET子句指定要修改的列，表达式指定要修改后的新值。WHERE子句用于指定只修改表中满足条件的记录的相应列值。,无条件更新,例1.将所有学生的年龄加1。UPDATEStudentSETSage=Sage+1,有条件更新,1.基于本表条件的更新例2.将学号为“0611104”学生的年龄改为18岁。UPDATEStudentSETSage=18WHERESno=0611104,2.基于其他表条件的更新,例3：将计算机系全体学生的成绩加5分。（1）用子查询实现UPDATESCSETGrade=Grade+5WHERESnoIN(SELECTSnoFROMStudentWHERESdept=计算机系)（2）用多表连接实现UPDATESCSETGrade=Grade+5FROMSCJOINStudentONSC.Sno=Student.SnoWHERESdept=计算机系,基于其他表条件的更新（续）,例4将学分最低的课程的学分加2分。UPDATECourseSETCcredit=Ccredit+2WHERECcredit=(SELECTMIN(Ccredit)FROMCourse),4.2.3删除数据,用DELETE语句实现。格式：DELETEFROMWHERE说明了要删除哪个表中的数据。WHERE子句说明只删除表中满足条件的记录。,无条件删除,例1.删除所有学生的选课记录。DELETEFROMSC,有条件删除,（1）基于本表条件的删除。例2删除所有不及格学生的修课记录。DELETEFROMSCWHEREGrade60,基于其他表条件的删除,例3删除计算机系不及格学生的修课记录。（1）用子查询实现DELETEFROMSCWHEREGrade60ANDSnoIN(SELECTSnoFROMStudentWHERESdept=计算机系)（2）用多表连接实现DELETEFROMSCFROMSCJOINStudentONSC.Sno=Student.SnoWHERESdept=计算机系ANDGrade=60,V_IS_S1,通过视图查询数据（续）,例9查询信息管理系学生的学号、姓名、所选课程的