《数据操作语句》PPT课件.ppt

第5章数据操作语句,5.1数据查询功能5.2数据更改功能,5.1数据查询功能,5.1.1查询语句的基本结构5.1.2单表查询5.1.3多表连接查询5.1.4使用TOP限制结果集5.1.5子查询,5.1.1查询语句基本格式,SELECT-需要哪些列FROM-来自于哪些表WHERE-根据什么条件GROUPBYHAVINGORDERBY,1.查询指定的列,查询表中用户感兴趣的部分属性列。例1：查询全体学生的学号与姓名。SELECTSno,SnameFROMStudent,一、选择表中若干列,5.1.2单表查询,例2.查询全体学生的姓名、学号、所在系,SELECTSname,Sno,SdeptFROMStudent,2.查询全部列,例3查询全体学生的记录SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent等价于：SELECT*FROMStudent,3.查询经过计算的列,例5.查询全体学生的姓名及其出生年份。SELECTSname,2010-SageFROMStudent,常量列,例5查询全体学生的姓名和出生年份，并在出生年份列前加入一个列，此列的每行数据均为“出生年份”常量值。SELECTSname,出生年份:,2010-SageFROMStudent,改变列标题,用户还可以通过指定别名来改变查询结果的列标题例：SELECTSname姓名,YearofBirth出生年份,2010-Sage年份，FROMStudent,出生年份,年份,姓名,二、选择表中若干元组,1.消除取值相同的行,例6查询选修了课程的学生的学号,SELECTSnoFROMSC,有重复行！,二、选择表中若干元组,用DISTINCT去掉结果集中的重复行,SELECTDISTINCTSnoFROMSC,2.查询满足条件的元组（使用where子句）,（1）比较大小,例7查询计算机系全体学生的姓名。SELECTSnameFROMStudentWHERESdept=计算机系例8查询年龄在20岁以下的学生的姓名及年龄。SELECTSname,SageFROMStudentWHERESage20例9查询考试成绩有不及格的学生的学号SELECTDISTINCTSnoFROMSCWHEREGrade=20,可对查询结果进行排序。排序子句为：ORDERBYASC|DESC,说明：按进行升序（ASC）或降序（DESC）排序。,3.对查询结果进行排序,示例,例26将学生按年龄的升序排序。SELECT*FROMStudentORDERBYSage例27查询选修了c02号课程的学生的学号及其成绩，查询结果按成绩降序排列。SELECTSno,GradeFROMSCWHERECno=c02ORDERBYGradeDESC例28.查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept,SageDESC,SQL提供的聚合函数有：COUNT（*）：统计表中元组个数；COUNT（DISTINCT）：统计本列列值个数；SUM（DISTINCT）：计算列值总和；AVG（DISTINCT）：计算列值平均值；MAX（DISTINCT）：求列值最大值；MIN（DISTINCT）：求列值最小值。上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。,4.使用聚合函数汇总数据,示例,例29统计学生总人数。SELECTCOUNT(*)FROMStudent例30统计选修了课程的学生的人数。SELECTCOUNT(DISTINCTSno)FROMSC例31统计9512101号学生的考试总成绩之和。SELECTSUM(Grade)FROMSCWHERESno=9512101,示例（续）,例32计算C01号课程学生的考试平均成绩。SELECTAVG(Grade)FROMSCWHERECno=C01例33查询C01号课程的考试最高分和最低分。SELECTMAX(Grade),MIN(Grade)FROMSCWHERECno=C01注意：计算函数不能出现在WHERE子句中,示例（续）,例34查询“9512101”学生的选课门数、已考试课程门数以及考试最高分、最低分和平均分。SELECTCOUNT(*)AS选课门数,COUNT(Grade)AS考试门数,MAX(Grade)AS最高分,MIN(Grade)AS最低分,AVG(Grade)AS平均分FROMSCWHERESno=9512101,作用：可以控制计算的级别：对全表还是对一组。目的：细化计算函数的作用对象。分组语句的一般形式：GROUPBY,nHAVING,5.对查询结果进行分组统计,（1）使用GROUPBY,例35统计每门课程的选课人数，列出课程号和人数。SELECTCnoas课程号,COUNT(Sno)as选课人数FROMSCGROUPBYCno对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后再对每一组使用COUNT计算，求得每组的学生人数。,例36查询每个学生的选课门数和平均成绩。SELECTSnoas学号,COUNT(*)as选课门数,AVG(Grade)as平均成绩FROMSCGROUPBYSno,注意,GROUPBY子句中的分组依据列必须是表中存在的列名，不能使用AS子句指派的结果集列别名。例如，例36中不能将GROUBY子句写成：GROUPBY学号。带有GROUPBY子句的SELECT语句的查询列表中只能出现分组依据列或聚合函数，因为分组后每个组只返回一行结果。,示例,例37统计每个系的学生人数和平均年龄。SELECTSdept,COUNT(*)AS学生人数,AVG(Sage)AS平均年龄FROMStudentGROUPBYSdept,示例,例38.带WHERE子句的分组。统计每个系的女生人数。SELECTSdept,Count(*)女生人数FROMStudentWHERESsex=女GROUPBYSdept,示例,例39.按多列分组。统计每个系的男生人数和女生人数，以及男生的最大年龄和女生的最大年龄。结果按系名的升序排序。SELECTSdept,Ssex,Count(*)人数,Max(Sage)最大年龄FROMStudentGROUPBYSdept,SsexORDERBYSdept,（2）使用HAVING,HAVING用于对分组自身进行限制，它有点象WHERE子句，但它用于组而不是对单个记录。例37查询修了3门以上课程的学生的学号SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3,示例,例40.查询选修了3门以上课程的学生的学号和选课门数。SELECTSno,Count(*)选课门数FROMSCGROUPBYSnoHAVINGCOUNT(*)3,处理过程：先执行GROUPBY子句对SC表数据按Sno进行分组，然后再用统计函数COUNT分别对每一组进行统计，最后筛选出统计结果满足大于3的组。,处理过程图示,示例,例41查询修课门数等于或大于4的学生的平均成绩和选课门数。SELECTSno,AVG(Grade)平均成绩,COUNT(*)修课门数FROMSCGROUPBYSnoHAVINGCOUNT(*)=4,几个子句比较,WHERE子句用来筛选FROM子句中指定的数据源所产生的行数据。GROUPBY子句用来对经WHERE子句筛选后的结果数据进行分组。HAVING子句用来对分组后的结果数据再进行筛选。,示例,例42.查询计算机系和信息管理系的学生人数。方法1：SELECTSdept,COUNT(*)FROMStudentGROUPBYSdeptHAVINGSdeptIN(计算机系,信息管理系)方法2：SELECTsdept,COUNT(*)FROMStudentWHERESdeptIN(计算机系,信息管理系)GROUPBYSdept第二种写法比第一种写法执行效率高。,示例,例43.查询每个系年龄小于等于20岁的学生人数。SELECTSdept,COUNT(*)FROMStudentWHERESage90),示例,例62查询计算机系选了“c02”课程的学生，列出姓名和性别。SELECTSname,SsexFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno=c02)ANDSdept=计算机系也可以用多表连接实现：SELECTSname,SsexFROMStudentSJOINSCONS.Sno=SC.SnoWHERESdept=计算机系ANDCno=c02,示例,例63.查询选修了“VB”课程的学生的学号和姓名。（1）在Course表中，找出“VB”课程名对应的课程号；（2）根据得到的“VB”课程号，在SC表中找出选了该课程号的学生的学号；（3）根据得到的学号，在Student表中找出对应的学生的学号和姓名。SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname=VB),例64.在选修了VB的这些学生中，统计他们的选课门数和平均成绩。SELECTSno学号,COUNT(*)选课门数,AVG(Grade)平均成绩FROMSCWHERESnoIN(-选VB的学生SELECTSnoFROMSCJOINCourseCONC.Cno=SC.CnoWHERECname=VB)GROUPBYSno,2.进行比较测试,带比较运算符的子查询指父查询与子查询之间用比较运算符连接，当用户能确切知道内层查询返回的是单值时，可用、=、运算符。,示例,例65.查询选了“c04”号课程且成绩高于此课程的平均成绩的学生的学号和成绩。首先计算“c04”号课程的平均成绩：SELECTAVG(Grade)fromSCWHERECno=c04-79然后，查找“c04”号课程所有的考试成绩中，高于79的学生：SELECTSno,GradeFROMSCWHERECno=c04ANDGrade79将两个查询语句合起来即为满足我们要求的查询语句：SELECTSno,GradeFROMSCWHERECno=c04ANDGrade(SELECTAVG(Grade)FROMSCWHERECno=c04),不相关子查询,用子查询进行基于集合测试和比较测试时，都是先执行子查询，然后再在子查询的结果基础之上执行外层查询。子查询都只执行一次，子查询的查询条件不依赖于外层查询，我们将这样的子查询称为不相关子查询或嵌套子查询（nestedsubquery）,示例,例66.查询计算机系年龄最大的学生的姓名和年龄。SELECTSname,SageFROMStudentWHERESdept=计算机系ANDSage=(SELECTMAX(Sage)FROMStudentWHERESdept=计算机系),示例,例67.查询考试平均成绩高于全体学生的总平均成绩的学生的学号和平均成绩。SELECTSno,AVG(Grade)平均成绩FROMSCGROUPBYSnoHAVINGAVG(Grade)(SELECTAVG(Grade)FROMSC),示例,例68.查询VB考试成绩高于VB平均成绩的学生的姓名、所在系和VB成绩。SELECTSname,Sdept,GradeFROMStudentSJOINSCONS.Sno=SC.SnoJOINCourseCONC.Cno=SC.CnoWHERECname=VBANDGrade(SELECTAVG(Grade)FROMSCJOINCourseCONC.Cno=SC.CnoWHERECname=VB),3.存在性测试,通常使用EXISTS谓词，其形式为：WHERENOTEXISTS（子查询）带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值（有数据）和假值（没有数据）。,例69.查询选修了c01课程的学生姓名。SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=c01),注意,处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外层结果数决定。由于EXISTS的子查询只能返回真或假值，因此在这里给出列名无意义。所以在有EXISTS的子查询中，其目标列表达式通常都用*。,上句的处理过程,1.找外层表Student表的第一行，根据其Sno值处理内层查询2.由外层的值与内层的结果比较，由此决定外层条件的真、假3.顺序处理外层表Student表中的第2、3、行。,示例,例71.查询没有选修c01号课程的学生姓名和所在系。SELECTSname,SdeptFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=c01)或：SELECTSname,SdeptFROMStudentWHERESnoNOTIN(SELECTSnoFROMSCWHERECno=c01),注意：不能用连接查询和在子查询中否定的形式实现。,示例,例71查询计算机系没有选修“VB”课程的学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESnoNOTIN(-查询选了VB的学生SELECTSnoFROMSCJOINCourseONSC.Cno=Course.CnoWHERECname=VB)ANDSdept=计算机系,5.2数据更改功能,5.2.1插入数据5.2.2更新数据5.2.3删除数据,5.2.1插入数据,插入单行记录的INSERT语句的格式为：INSERTINTO()VALUES（值表）功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序或列名表顺序赋给对应列名。,注意,值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。如果后边没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）。,示例,例72将新生记录（9521104，陈冬，男，18岁，信息系）插入到Student表中。INSERTINTOStudentVALUES(9521104,陈冬,男,18,信息系)例73在SC表中插入一新记录，学号为“9521104”，选的课程号为“c01”，成绩暂缺。INSERTINTOSC(Sno,Cno)VALUES(9521104,c01)实际插入的值为：(9521105,c01，NULL),5.2.2更新数据,用UPDATE语句实现。格式：UPDATESET,nWHERE,1.无条件更新,例74.将所有学生的年龄加1。UPDATEStudentSETSage=Sage+1,2.有条件更新,（1）基于本表条件的更新例75.将9512101学生的年龄改为21岁。UPDATEStudentSETSage=21WHERESno=9512101,（2）基于其他表条件的更新,例76：将计算机系全体学生的成绩加5分。（1）用子查询实现UPDATESCSETGrade=Grade+5WHERESnoIN(SELECTSnoFROMStudentWHERESdept=计算机系)（2）用多表连接实现UPDATESCSETGrade=Grade+5FROMSCJOINStudentONSC.Sno=Student.SnoWHERESdept=计算机系,示例,例77.将学分最低的课程的学分加2分。UPDATECourseSETCredit=Credit+2WHERECredit=(SELECTMIN(Credit)FROMCourse),示例,例78.数学系学生的VB考试成绩增加10分。用子查询实现。UPDATESCSETGrade=Grade+10WHERECnoIN(SELECTCnoFROMCourseWHERECname=VB)ANDSnoIN(SELECTSnoFROMStudentWHERESdept=数学系)用多表连接实现。UPDATESCSETGrade=Grade+10FROMSCJOINCourseCONC.Cno=SC.CnoJOINStudentSONS.Sno=SC.SnoWHERECname=VBANDSdept=数学系,5.2.3删除数据,用DELETE语句实现格式：DELETEFROMWHERE,1.无条件删除,例79.删除所有学生的选课记录。DELETEFROMSC,2.有条件删除,（1）基于本表条件的删除。例80删除所有不及格学生的修课记录。DELETEFROMSCWHEREGrade60,（2）基于其他表条件的删除,例81删除计算机系不及格学生的修课记录。（1）用子查询实现DELETEFROMSCWHEREGrade60ANDSnoIN(SELECTSnoFROMStudentWHERESdept=计算机系)（2）用多表连接实现DELETEFROMSCFROMSCJOINStudentONSC.Sno=Student.SnoWHERESdept=计算机系ANDGrade60,示例,例81.删除信息系考试成绩不及格学生的不及格课程的选课记录。用多表连接形式实现。DELETEFROMSCFROMStudentSJOINSCONS.Sno=SC.snoWHERESdept=信息系ANDGrade60用子查询形式实现。DELETEFROMSCWHERESnoIN(SELECTSnoFROMStudentWHERESdept=信息系)ANDGrade60,