《《数据操作语句》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《数据操作语句》PPT课件.ppt（112页珍藏版）》请在金锄头文库上搜索。
1、第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 查询语句基本格式SELECTSELECT -需要哪些列需要哪些列需要哪些列需要哪些列FROMFROM -来自于哪些表来自于哪些表来自于哪些表来自于哪些表 WHEREWHERE -根据什么条件根据什么条件根据什么条件根据什么条件 GROUPBYGROUPBY HAVINGHAVING ORDERBYORDERBY 1. 1. 查询
2、指定的列查询指定的列查询表中用户感兴趣的部分属性列。查询表中用户感兴趣的部分属性列。例例1：查询全体学生的学号与姓名。：查询全体学生的学号与姓名。SELECTSno,SnameFROMStudent一、选择表中若干列一、选择表中若干列 5.1.2 单表查询例例2.2.查询全体学生的姓名、学号、所在系查询全体学生的姓名、学号、所在系 SELECTSname,Sno,SdeptFROMStudent2. 2. 查询全部列查询全部列 例例3查询全体学生的记录查询全体学生的记录SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent等价于：等价于：SELECT*FROMSt
3、udent3. 3. 查询经过计算的列查询经过计算的列例例5.查询全体学生的姓名及其出生年份。查询全体学生的姓名及其出生年份。SELECTSname,2010-SageFROMStudent常量列常量列 例例5查询全体学生的姓名和出生年份，并在出生年份列查询全体学生的姓名和出生年份，并在出生年份列前加入一个列，此列的每行数据均为前加入一个列，此列的每行数据均为“出生年份出生年份”常量值。常量值。SELECTSname,出生年份出生年份:,2010-SageFROMStudent改变列标题改变列标题 用户还可以通过指定别名来改变查询结果的列标题用户还可以通过指定别名来改变查询结果的列标题例：例：
4、SELECTSname姓名姓名,YearofBirth出生年份出生年份,2010-Sage年份，年份，FROMStudent出生年份出生年份年份年份姓名姓名二、选择表中若干元组二、选择表中若干元组 1.1.消除取值相同的行消除取值相同的行 例例6查询选修了课程的学生的学号查询选修了课程的学生的学号SELECT Sno FROM SC 有重复行！有重复行！二、选择表中若干元组二、选择表中若干元组 用用DISTINCT去掉结果集中的重复行去掉结果集中的重复行SELECTDISTINCTSnoFROMSC2. 2. 查询满足条件的元组查询满足条件的元组（使用（使用wherewhere子句）子句） 查
5、询条件查询条件谓谓 词词比较运算符比较运算符=, , =, , =, （或（或!=）NOT+比较运算符比较运算符 确定范围确定范围BETWEENAND, NOT BETWEENAND确定集合确定集合IN, NOT IN字符匹配字符匹配LIKE, NOT LIKE 空值空值IS NULL, IS NOT NULL逻辑谓词逻辑谓词AND, OR（1）比较大小例例7查询计算机系全体学生的姓名。查询计算机系全体学生的姓名。SELECTSnameFROMStudentWHERESdept=计算机系计算机系例例8查询年龄在查询年龄在20岁以下的学生的姓名及年龄。岁以下的学生的姓名及年龄。SELECTSna
6、me,SageFROMStudentWHERESage20例例9查询考试成绩有不及格的学生的学号查询考试成绩有不及格的学生的学号SELECTDISTINCTSnoFROMSCWHEREGrade60（2）确定范围用用BETWEENAND和和NOTBETWEENAND是逻辑运算符，可以用来查找属性值在或不在指定范围内的是逻辑运算符，可以用来查找属性值在或不在指定范围内的元组，其中元组，其中BETWEEN后边指定范围的下限，后边指定范围的下限，AND后边指定后边指定范围的上限。范围的上限。BETWEENAND的格式为：的格式为：列名列名|表达式表达式NOTBETWEEN下限值下限值AND上限值上限
7、值如果列或表达式的值在如果列或表达式的值在不在不在下限值和上限值范围内，则结果下限值和上限值范围内，则结果为为True，表明此记录符合查询条件。，表明此记录符合查询条件。示例例例10查询年龄在查询年龄在2023岁之间的学生的姓名、所在系岁之间的学生的姓名、所在系和年龄。和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23例例11查询年龄不在查询年龄不在2023之间的学生姓名、所在系和年之间的学生姓名、所在系和年龄。龄。SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20A
8、ND23关于日期类型查询关于日期类型查询例例12查询查询2009年年6月份出版的全部图书的详细信息。月份出版的全部图书的详细信息。SELECT*FROM图书表图书表WHERE出版日期出版日期BETWEEN2009/6/1AND2009/6/30书号书号书名书名价格价格出版日期出版日期T001Java程序设计程序设计26.02009-6-1T002数据结构数据结构32.02009-6-15T003操作系统基础操作系统基础36.52009-7-1T004计算机体系结构计算机体系结构29.52009-6-1T005数据库原理数据库原理30.02009-7-1T006汇编语言汇编语言34.02009-
9、8-15T007编译原理编译原理38.02009-8-1T008计算机网络计算机网络35.02008-3-15T009高等数学高等数学22.02008-3-1T010有机化学有机化学19.52008-5-1注意：日期类型的注意：日期类型的常量要用单引号括常量要用单引号括起来，而且年、月、起来，而且年、月、日之间通常用分隔日之间通常用分隔符隔开，常用的分符隔开，常用的分隔符有隔符有“/ /”和和“- -”（3）确定集合使用使用ININ运算符。运算符。用来查找属性值属于指定集合的元组。用来查找属性值属于指定集合的元组。格式为：格式为： 列名列名 NOT IN ( NOT IN (常量常量1, 1,
10、 常量常量2, 2, 常量常量n)n)当列中的值与当列中的值与ININ中的某个常量值相等时，则结果为中的某个常量值相等时，则结果为TrueTrue，表明此记录为符合查询条件的记录；表明此记录为符合查询条件的记录；NOT INNOT IN：当列中的值与某个常量值相同时，则结果为：当列中的值与某个常量值相同时，则结果为FalseFalse，表明此记录为不符合查询条件的记录，表明此记录为不符合查询条件的记录示例例例13查询信息系、数学系和计算机系学生的姓名和性别。查询信息系、数学系和计算机系学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptIN(信息系信息系
11、,数学系数学系,计算机系计算机系)例例14查询信息系和计算机系之外的其他系的学生姓名、查询信息系和计算机系之外的其他系的学生姓名、性别和所在系。性别和所在系。SELECTSname,SsexFROMStudentWHERESdeptNOTIN(信息系信息系,计算机系计算机系)（4）字符串匹配使用使用LIKELIKE运算符运算符一般形式为：一般形式为： 列名列名 NOT LIKE NOT LIKE 匹配串中可包含如下四种通配符：匹配串中可包含如下四种通配符：_ _：匹配任意一个字符；：匹配任意一个字符；% %：匹配：匹配0 0个或多个字符；个或多个字符； ：匹配：匹配 中的任意一个字符；对于连续
12、字母的匹中的任意一个字符；对于连续字母的匹配，例如匹配配，例如匹配abcdabcd，可简写为，可简写为a-da-d， ：不匹配：不匹配 中的任意一个字符。中的任意一个字符。 示例例例15.查询姓查询姓张张的学生的详细信息。的学生的详细信息。SELECT*FROMStudentWHERESnameLIKE张张%例例16查询学生表中姓查询学生表中姓张张、李李和和刘刘的学生的情况。的学生的情况。SELECT*FROMStudentWHERESnameLIKE张李刘张李刘%例例17查询名字中第查询名字中第2个字为个字为小小或或大大的学生的姓名和的学生的姓名和学号。学号。SELECTSname,SnoF
13、ROMStudentWHERESnameLIKE_小大小大%示例（续）例例18查询所有不姓查询所有不姓“王王”也不姓也不姓“张张”的学生姓名的学生姓名SELECTSnameFROMStudentWHERESnameNOTLIKE王张王张%或者：或者：SELECTSnameFROMStudentWHERESnameLIKE王张王张%或者：或者：SELECTSnameFROMStudentWHERESnameNOTLIKE王王%ANDSnameNOTLIKE张张%示例（续）例例19查询姓查询姓“王王”且名字是且名字是2个个字的学生姓名。字的学生姓名。SELECTSnameFROMStudentWH
14、ERESnameLIKE王王_示例（续）例例20查询姓王且名字是查询姓王且名字是3个字的学生姓名个字的学生姓名SELECTSnameFROMStudentWHERESnameLIKE王王_ 注意：尾随空格的处理。注意：尾随空格的处理。 SELECT Sname FROM Student SELECT Sname FROM Student WHERE rtrim(Sname) LIKE WHERE rtrim(Sname) LIKE 王王_ _ _例例21.在在Student表中查询学号的最后一位不是表中查询学号的最后一位不是2、3、5的的学生信息。学生信息。SELECT*FROMStudent
15、WHERESnoLIKE%235转义字符如果要查找的字符串正好含有通配符，比如下划线或百分号，如果要查找的字符串正好含有通配符，比如下划线或百分号，就需要使用一个特殊子句来告诉数据库管理系统这里的下划就需要使用一个特殊子句来告诉数据库管理系统这里的下划线或百分号是一个普通的字符，而不是一个通配符，线或百分号是一个普通的字符，而不是一个通配符，这个特殊的子句就是这个特殊的子句就是ESCAPE。ESCAPE的语法格式为：的语法格式为：ESCAPE转义字符转义字符“转义字符转义字符”是任何一个有效的字符。是任何一个有效的字符。在匹配串中包含转义字符，表明位于该字符后面的字符是普在匹配串中包含转义字符
16、，表明位于该字符后面的字符是普通字符，而不是通配符。通字符，而不是通配符。示例例如，查找例如，查找field1字段中包含字符串字段中包含字符串“30%”的记的记录：录：WHEREfield1LIKE%30!%ESCAPE!查找查找field1字段中包含下划线（字段中包含下划线（_）的记录：）的记录：WHEREfield1LIKE%!_%ESCAPE!（5）涉及空值的查询空值（空值（NULL）在数据库中表示不确定的值。）在数据库中表示不确定的值。例如，学生选修课程后还没有考试时，这些学生有选课记例如，学生选修课程后还没有考试时，这些学生有选课记录，但没有考试成绩，因此考试成绩为空值。录，但没有考
17、试成绩，因此考试成绩为空值。判断某个值是否为判断某个值是否为NULL值，不能使用普通的比较运算符。值，不能使用普通的比较运算符。判断取值为空的语句格式为：判断取值为空的语句格式为：列名列名ISNULL判断取值不为空的语句格式为：判断取值不为空的语句格式为：列名列名ISNOTNULL示例例例22查询没有考试成绩的学生的学号和相应的课程号。查询没有考试成绩的学生的学号和相应的课程号。SELECTSno,CnoFROMSCWHEREGradeISNULL例例23查询所有有考试成绩的学生的学号和课程号。查询所有有考试成绩的学生的学号和课程号。SELECTSno,CnoFROMSCWHEREGradeI
18、SNOTNULL（6）多重条件查询在在WHERE子句中可以使用逻辑运算符子句中可以使用逻辑运算符AND和和OR来组成来组成多条件查询。多条件查询。用用AND连接的条件表示必须全部满足所有的条件的结连接的条件表示必须全部满足所有的条件的结果才为果才为True；用用OR连接的条件表示只要满足其中一个条件结果即为连接的条件表示只要满足其中一个条件结果即为True。例例24查询计算机系年龄在查询计算机系年龄在20岁以下的学生姓名。岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept=计算机系计算机系ANDSage=20或：或：SELECTSname,Sdept,SageF
19、ROMStudentWHERESdeptIN(计算机系计算机系,信息系信息系)ANDSage=20可对查询结果进行排序。可对查询结果进行排序。排序子句为：排序子句为：ORDERBYASC|DESC,说明：按说明：按进行升序（进行升序（ASC）或降序）或降序（DESC）排序。）排序。3. 对查询结果进行排序 示例例例2626将学生按年龄的升序排序。将学生按年龄的升序排序。SELECT*FROMStudentORDERBYSage例例2727查询选修了查询选修了c02c02号课程的学生的学号及其成绩，查号课程的学生的学号及其成绩，查询结果按成绩降序排列。询结果按成绩降序排列。SELECTSno,G
20、radeFROMSCWHERECno=c02ORDERBYGradeDESC例例28.28.查询全体学生的信息，查询结果按所在系的系名升查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。序排列，同一系的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept,SageDESCSQL提供的聚合函数有：提供的聚合函数有：COUNT（*）：统计表中元组个数；：统计表中元组个数；COUNT（DISTINCT）：统计本列列值个数；：统计本列列值个数；SUM（DISTINCT）：计算列值总和；：计算列值总和；AVG（DISTINCT）：计算列值平均值；
21、：计算列值平均值；MAX（DISTINCT）：求列值最大值；：求列值最大值；MIN（DISTINCT）：求列值最小值。：求列值最小值。上述函数中除上述函数中除COUNT（*）外，其他函数在计算过程中均）外，其他函数在计算过程中均忽略忽略NULL值。值。4. 使用聚合函数汇总数据 示例例例29统计学生总人数。统计学生总人数。SELECTCOUNT(*)FROMStudent例例30统计选修了课程的学生的人数。统计选修了课程的学生的人数。SELECTCOUNT(DISTINCTSno)FROMSC例例31统计统计9512101号学生的考试总成绩之和。号学生的考试总成绩之和。SELECTSUM(Gr
22、ade)FROMSCWHERESno=9512101示例（续）例例32计算计算C01号课程学生的考试平均成绩。号课程学生的考试平均成绩。SELECTAVG(Grade)FROMSCWHERECno=C01例例33查询查询C01号课程的考试最高分和最低分。号课程的考试最高分和最低分。SELECTMAX(Grade),MIN(Grade)FROMSCWHERECno=C01注意：计算函数不能出现在注意：计算函数不能出现在WHERE子句中子句中示例（续）例例34查询查询“9512101”学生的选课门数、已考试课程门学生的选课门数、已考试课程门数以及考试最高分、最低分和平均分。数以及考试最高分、最低分
23、和平均分。SELECTCOUNT(*)AS选课门数选课门数,COUNT(Grade)AS考试门数考试门数,MAX(Grade)AS最高分最高分,MIN(Grade)AS最低分最低分,AVG(Grade)AS平均分平均分FROMSCWHERESno=9512101作用：可以控制计算的级别：对全表还是对一组。作用：可以控制计算的级别：对全表还是对一组。目的：细化计算函数的作用对象。目的：细化计算函数的作用对象。分组语句的一般形式：分组语句的一般形式：GROUPBY,nHAVING5. 对查询结果进行分组统计 （1）使用GROUP BY例例3535统计每门课程的选课人数，列出课程号和人数。统计每门课
24、程的选课人数，列出课程号和人数。SELECTCnoas课程号课程号,COUNT(Sno)as选课人数选课人数FROMSCGROUPBYCno对查询结果按对查询结果按CnoCno的值分组，所有具有相同的值分组，所有具有相同CnoCno值的元组值的元组为一组，然后再对每一组使用为一组，然后再对每一组使用COUNTCOUNT计算，求得每组的学计算，求得每组的学生人数。生人数。SnoCnoGrade951201C0180951201C0278951202C0190952103C0288952103C0185952103C0391952103C0474课程号课程号选课人数选课人数C013C022C031
25、C041SnoCnoGrade951201C0180951202C0190952103C0185951201C0278952103C0288952103C0391952103C0474例例3636查询每个学生的选课门数和平均成绩。查询每个学生的选课门数和平均成绩。SELECTSnoas学号学号,COUNT(*)as选课门数选课门数,AVG(Grade)as平均成绩平均成绩FROMSCGROUPBYSno注意注意GROUPBY子句中的分组依据列必须是表中存在子句中的分组依据列必须是表中存在的列名，不能使用的列名，不能使用AS子句指派的结果集列别名。子句指派的结果集列别名。例如，例例如，例36中不
26、能将中不能将GROUBY子句写成：子句写成：GROUPBY学号。学号。带有带有GROUPBY子句的子句的SELECT语句的查询列表语句的查询列表中只能出现中只能出现分组依据列分组依据列或或聚合函数聚合函数，因为分组后，因为分组后每个组只返回一行结果。每个组只返回一行结果。示例例例37统计每个系的学生人数和平均年龄。统计每个系的学生人数和平均年龄。SELECTSdept,COUNT(*)AS学生人数学生人数,AVG(Sage)AS平均年龄平均年龄FROMStudentGROUPBYSdept示例例例38.带带WHERE子句的分组。统计每个系的女生人子句的分组。统计每个系的女生人数。数。SELEC
27、TSdept,Count(*)女生人数女生人数FROMStudentWHERESsex=女女GROUPBYSdept示例例例39.按多列分组。统计每个系的男生人数和女生按多列分组。统计每个系的男生人数和女生人数，以及男生的最大年龄和女生的最大年龄。人数，以及男生的最大年龄和女生的最大年龄。结果按系名的升序排序。结果按系名的升序排序。SELECTSdept,Ssex,Count(*)人数人数,Max(Sage)最大年龄最大年龄FROMStudentGROUPBYSdept,SsexORDERBYSdept（2）使用HAVINGHAVING用于对分组自身进行限制，它有点象用于对分组自身进行限制，它
28、有点象WHERE子句，但它用于组而不是对单个记录。子句，但它用于组而不是对单个记录。例例37查询修了查询修了3门以上课程的学生的学号门以上课程的学生的学号SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3示例例例40.查询选修了查询选修了3门以上课程的学生的学号和选课门以上课程的学生的学号和选课门数。门数。SELECTSno,Count(*)选课门数选课门数FROMSCGROUPBYSnoHAVINGCOUNT(*)3处理过程：先执行处理过程：先执行GROUP BYGROUP BY子句对子句对SCSC表数据按表数据按SnoSno进行分组，然后再用统计函数进行分组，
29、然后再用统计函数COUNTCOUNT分别对每一组分别对每一组进行统计，最后筛选出统计结果满足大于进行统计，最后筛选出统计结果满足大于3 3的组。的组。处理过程图示处理过程图示示例例例41查询修课门数等于或大于查询修课门数等于或大于4的学生的平均成绩和选的学生的平均成绩和选课门数。课门数。SELECTSno,AVG(Grade)平均成绩平均成绩,COUNT(*)修课门数修课门数FROMSCGROUPBYSnoHAVINGCOUNT(*)=4几个子句比较WHERE子句用来筛选子句用来筛选FROM子句中指定的数据源子句中指定的数据源所产生的行数据。所产生的行数据。GROUPBY子句用来对经子句用来对
30、经WHERE子句筛选后的子句筛选后的结果数据进行分组。结果数据进行分组。HAVING子句用来对分组后的结果数据再进行筛子句用来对分组后的结果数据再进行筛选。选。示例例例42.查询计算机系和信息管理系的学生人数。查询计算机系和信息管理系的学生人数。方法方法1：SELECTSdept,COUNT(*)FROMStudentGROUPBYSdeptHAVINGSdeptIN(计算机系计算机系,信息管理系信息管理系)方法方法2：SELECTsdept,COUNT(*)FROMStudentWHERESdeptIN(计算机系计算机系,信息管理系信息管理系)GROUPBYSdept第二种写法比第一种写法执
31、行效率高。第二种写法比第一种写法执行效率高。示例例例43.查询每个系年龄小于等于查询每个系年龄小于等于20岁的学生人数。岁的学生人数。SELECTSdept,COUNT(*)FROMStudentWHERESage=20GROUPBYSdept5.1.3 多表连接查询 若一个查询同时涉及两个或两个以上的表，则称若一个查询同时涉及两个或两个以上的表，则称之为连接查询。之为连接查询。连接查询是关系数据库中最主要的查询连接查询是关系数据库中最主要的查询连接查询包括内连接、外连接和交叉连接等。连接查询包括内连接、外连接和交叉连接等。连接基础知识连接查询中用于连接两个表的条件称为连接条件或连接查询中用于
32、连接两个表的条件称为连接条件或连接谓词。连接谓词。一般格式为：一般格式为：必须语义相同必须语义相同内连接SQL-92内连接语法如下：内连接语法如下：SELECTFROM表表1INNERJOIN表表2ON连接条件连接条件执行连接操作的过程：执行连接操作的过程：首先取表首先取表1 1中的第中的第1 1个元组，然后从头开始扫描表个元组，然后从头开始扫描表2 2，逐一，逐一查找满足连接条件的元组，查找满足连接条件的元组，找到后就将表找到后就将表1 1中的第中的第1 1个元组与该元组拼接起来，形成结个元组与该元组拼接起来，形成结果表中的一个元组。果表中的一个元组。表表2 2全部查找完毕后，再取表全部查找
33、完毕后，再取表1 1中的第中的第2 2个元组，然后再从个元组，然后再从头开始扫描表头开始扫描表2 2， 重复这个过程，直到表重复这个过程，直到表1 1中的全部元组都处理完毕为止。中的全部元组都处理完毕为止。 示例例例44查询每个学生及其选课的详细信息。查询每个学生及其选课的详细信息。SELECT*FROMStudentINNERJOINSCONStudent.Sno=SC.Sno改进例44SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.Sno示例例例46.查询计算机系学生的修课
34、情况，要求列出学查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。生的名字、所修课的课程号和成绩。SELECTSname,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept=计算机系计算机系表别名可以为表提供别名，其格式如下：可以为表提供别名，其格式如下：FROMAS为表指定别名可以简化表的书写。为表指定别名可以简化表的书写。当为表指定了别名时，在查询语句中的其他地方，当为表指定了别名时，在查询语句中的其他地方，所有用到表名的地方都要使用别名，而不能再使所有用到表名的地方都要使用别名，而不能再使用原表名。用原表名
35、。示例例例47.查询信息系修了查询信息系修了“计算机文化学计算机文化学”课程的学课程的学生信息，要求列出学生姓名、课程名和成绩。生信息，要求列出学生姓名、课程名和成绩。SELECTSname,Cname,GradeFROMStudentsJOINSCONs.Sno=SC.SnoJOINCoursecONc.Cno=SC.CnoWHERESdept=信息系信息系ANDCname=计算机文化学计算机文化学示例例例48查询所有修了查询所有修了VB课程的学生的修课情况，要求列课程的学生的修课情况，要求列出学生姓名和所在系。出学生姓名和所在系。SELECTSname,SdeptFROMStudentSJ
36、OINSCONS.Sno=SC.SnoJOINCourseCONC.Cno=SC.cnoWHERECname=VB示例例例49有分组的多表连接查询。统计每个系的学生考试平有分组的多表连接查询。统计每个系的学生考试平均成绩。均成绩。SELECTSdept,AVG(grade)asAverageGradeFROMstudentSJOINSCONS.Sno=SC.SnoGROUPBYSdept示例例例50有分组和行选择条件的多表连接查询。统计计算机有分组和行选择条件的多表连接查询。统计计算机系每门课程的选课人数、平均成绩、最高成绩和最低成绩。系每门课程的选课人数、平均成绩、最高成绩和最低成绩。SEL
37、ECTCno,COUNT(*)ASTotal,AVG(Grade)asAvgGrade,MAX(Grade)asMaxGrade,MIN(Grade)asMinGradeFROMStudentSJOINSCONS.Sno=SC.SnoWHERESdept=计算机系计算机系GROUPBYCno2.自连接为特殊的内连接为特殊的内连接相互连接的表物理上为同一张表。相互连接的表物理上为同一张表。必须为两个表取别名，使之在逻辑上成为两个表。必须为两个表取别名，使之在逻辑上成为两个表。FROM表表1AST1-在内存中生成在内存中生成“T1”表表JOIN表表1AST2-在内存中生成在内存中生成“T2”表表示
38、例例例51.查询与刘晨在同一个系学习的学生的姓名和所在的查询与刘晨在同一个系学习的学生的姓名和所在的系。系。SELECTS2.Sname,S2.SdeptFROMStudentS1JOINStudentS2ONS1.Sdept=S2.SdeptWHERES1.Sname=刘晨刘晨ANDS2.Sname!=刘晨刘晨示例例例52查询与查询与“操作系统操作系统”学分相同的课程的课程名和学学分相同的课程的课程名和学分。分。SELECTC1.Cname,C1.CreditFROMCourseC1JOINCourseC2ONC1.Credit=C2.CreditWHEREC2.Cname=操作系统操作系统
39、3.外连接l只限制一张表中的数据必须满足连接条件，而另一张表中数据可只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。以不满足连接条件。lANSI方式的外连接的语法格式为：方式的外连接的语法格式为：FROM表表1LEFT|RIGHTOUTERJOIN表表2ONl外连接分为左外连接和右外连接外连接分为左外连接和右外连接l左外连接的含义是限制表左外连接的含义是限制表2中的数据必须满足连接条件中的数据必须满足连接条件l右外连接的含义是限制表右外连接的含义是限制表1中的数据必须满足连接条件中的数据必须满足连接条件示例例例53.查询学生的修课情况，包括修了课程的学生和没有查询学生
40、的修课情况，包括修了课程的学生和没有修课的学生。修课的学生。SELECTStudent.Sno,Sname,Cno,GradeFROMStudentLEFTOUTERJOINSCONStudent.Sno=SC.Sno示例例例54.查询哪些课程没有人选，列出其课程名。查询哪些课程没有人选，列出其课程名。SELECTCnameFROMCourseCLEFTJOINSCONC.Cno=SC.CnoWHERESC.CnoISNULL示例例例55.查询计算机系没有选课的学生，列查询计算机系没有选课的学生，列出学生姓名和性别。出学生姓名和性别。SELECTSname,Sdept,Cno,gradeFRO
41、MStudentSLEFTJOINSCONS.Sno=SC.SnoWHERESdept=计算机系计算机系ANDSC.SnoISNULL示例例例56.统计计算机系每个学生的选课门数，包括没有选课统计计算机系每个学生的选课门数，包括没有选课的学生，结果按选课门数递减排序。的学生，结果按选课门数递减排序。SELECTS.SnoAS学号学号,COUNT(SC.Cno)AS选课门数选课门数FROMStudentSLEFTJOINSCONS.Sno=SC.SnoWHERESdept=计算机系计算机系GROUPBYS.SnoORDERBYCOUNT(SC.Cno)DESC5.1.4 使用TOP限制结果集行数
42、可以使用可以使用TOP谓词来限制输出的结果行数。谓词来限制输出的结果行数。TOPnpercentWITHTIESTOPn：取查询结果的前：取查询结果的前n行数据。行数据。TOPnpercnet：取查询结果的前：取查询结果的前n%行。行。WITHTIES：包括并列的结果。：包括并列的结果。TOP谓词写在谓词写在SELECT单词的后边单词的后边*73示例例例57查询年龄最大的三个学生的姓名、查询年龄最大的三个学生的姓名、年龄及所在的系。年龄及所在的系。SELECTTOP3Sname,Sage,SdeptFROMStudentORDERBYSageDESC*74示例查询年龄最大的三个学生的姓名、年龄
43、查询年龄最大的三个学生的姓名、年龄及所在的系。及所在的系。包括并列的情况。包括并列的情况。SELECTTOP3WITHTIESSname,Sage,SdeptFROMStudentORDERBYSageDESC*75示例例例58查询查询VB考试成绩最高的前三名的学生的姓名、考试成绩最高的前三名的学生的姓名、所在系和所在系和VB考试成绩。考试成绩。SELECTTOP3WITHTIESSname,Sdept,GradeFROMStudentSJOINSConS.Sno=SC.SnoJOINCourseCONC.Cno=SC.CnoWHERECname=VBORDERBYGradeDESC*76示例
44、例例59查询选课人数最少的两门课程（不包括没有人选查询选课人数最少的两门课程（不包括没有人选的课程），列出课程号和选课人数。的课程），列出课程号和选课人数。SELECTTOP2WITHTIESCno,COUNT(*)选课人数选课人数FROMSCGROUPBYCnoORDERBYCOUNT(Cno)ASC*775.1.5 子查询 在在SQL语言中，一个语言中，一个SELECTFROMWHERE语句称为一个查询块。语句称为一个查询块。子查询是一个子查询是一个SELECT查询，它嵌套在查询，它嵌套在SELECT、INSERT、UPDATE、DELETE语句语句的的WHERE或或HAVING子句内，或
45、其它子查询中子句内，或其它子查询中子查询的子查询的SELECT查询总是使用圆括号括起来。查询总是使用圆括号括起来。1. 基于集合的测试使用子查询进行基于集合的测试使用子查询进行基于集合的测试的语句的一般格式为：的语句的一般格式为：列名列名NOTIN(子查询子查询)注意注意使用子查询进行基于集合的测试时，由该子查询使用子查询进行基于集合的测试时，由该子查询返回的结果集中的列的个数、数据类型以及语义返回的结果集中的列的个数、数据类型以及语义必须与表达式中的列的个数、数据类型以及语义必须与表达式中的列的个数、数据类型以及语义相同。当子查询返回结果之后，外层查询将用这相同。当子查询返回结果之后，外层查
46、询将用这个结果作为筛选条件。个结果作为筛选条件。示例例例60.查询与刘晨在同一个系的学生。查询与刘晨在同一个系的学生。SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨刘晨)ANDSname!=刘晨刘晨示例例例61.查询成绩为大于查询成绩为大于90分的学生的学号、姓名。分的学生的学号、姓名。SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHEREGrade90)示例例例62查询计算机系选了查询计算机系选了“c02”课程的学生，列
47、出姓名和课程的学生，列出姓名和性别。性别。SELECTSname,SsexFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno=c02)ANDSdept=计算机系计算机系也可以用多表连接实现：也可以用多表连接实现：SELECTSname,SsexFROMStudentSJOINSCONS.Sno=SC.SnoWHERESdept=计算机系计算机系ANDCno=c02示例例例63.查询选修了查询选修了“VB”课程的学生的学号和姓名。课程的学生的学号和姓名。（1）在）在Course表中，找出表中，找出“VB”课程名对应的课程号；课程名对应的课程号；（2）根据得
48、到的）根据得到的“VB”课程号，在课程号，在SC表中找出选了该课程表中找出选了该课程号的学生的学号；号的学生的学号；（3）根据得到的学号，在）根据得到的学号，在Student表中找出对应的学生的学表中找出对应的学生的学号和姓名。号和姓名。SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname=VB)例例64.在选修了在选修了VB的这些学生中，统计他们的选课门数和的这些学生中，统计他们的选课门数和平均成绩。平均成绩。SELECTSno学号学号,COUNT(*)选课门数
49、选课门数,AVG(Grade)平均成绩平均成绩FROMSCWHERESnoIN(-选选VB的学生的学生SELECTSnoFROMSCJOINCourseCONC.Cno=SC.CnoWHERECname=VB)GROUPBYSno2.进行比较测试 带比较运算符的子查询指父查询与子查询之间用比带比较运算符的子查询指父查询与子查询之间用比较运算符连接，较运算符连接，当用户能确切知道内层查询返回的是单值时当用户能确切知道内层查询返回的是单值时，可用，可用、=、=、运算符。运算符。示例例例65.查询选了查询选了“c04”号课程且成绩高于此课程的平均成绩号课程且成绩高于此课程的平均成绩的学生的学号和成绩
50、。的学生的学号和成绩。首先计算首先计算“c04”号课程的平均成绩：号课程的平均成绩：SELECTAVG(Grade)fromSCWHERECno=c04-79然后，查找然后，查找“c04”号课程所有的考试成绩中，高于号课程所有的考试成绩中，高于79的学的学生：生：SELECTSno,GradeFROMSCWHERECno=c04ANDGrade79将两个查询语句合起来即为满足我们要求的查询语句：将两个查询语句合起来即为满足我们要求的查询语句：SELECTSno,GradeFROMSCWHERECno=c04ANDGrade(SELECTAVG(Grade)FROMSCWHERECno=c04)
51、不相关子查询不相关子查询用子查询进行基于集合测试和比较测试时，都是用子查询进行基于集合测试和比较测试时，都是先执行子查询，然后再在子查询的结果基础之上先执行子查询，然后再在子查询的结果基础之上执行外层查询。执行外层查询。子查询都只执行一次，子查询的查询条件不依赖子查询都只执行一次，子查询的查询条件不依赖于外层查询，于外层查询，我们将这样的子查询称为我们将这样的子查询称为不相关子查询不相关子查询或或嵌套子嵌套子查询查询（nestedsubquery）示例例例66.查询计算机系年龄最大的学生的姓名和年龄。查询计算机系年龄最大的学生的姓名和年龄。SELECTSname,SageFROMStudent
52、WHERESdept=计算机系计算机系ANDSage=(SELECTMAX(Sage)FROMStudentWHERESdept=计算机系计算机系)示例例例67.查询考试平均成绩高于全体学生的总平均成绩的学查询考试平均成绩高于全体学生的总平均成绩的学生的学号和平均成绩。生的学号和平均成绩。SELECTSno,AVG(Grade)平均成绩平均成绩FROMSCGROUPBYSnoHAVINGAVG(Grade)(SELECTAVG(Grade)FROMSC)示例例例68.查询查询VB考试成绩高于考试成绩高于VB平均成绩的学生的姓名、平均成绩的学生的姓名、所在系和所在系和VB成绩。成绩。SELECT
53、Sname,Sdept,GradeFROMStudentSJOINSCONS.Sno=SC.SnoJOINCourseCONC.Cno=SC.CnoWHERECname=VBANDGrade(SELECTAVG(Grade)FROMSCJOINCourseCONC.Cno=SC.CnoWHERECname=VB)3. 存在性测试 通常使用通常使用EXISTS谓词，其形式为：谓词，其形式为：WHERENOTEXISTS（子查询）（子查询）带带EXISTS谓词的子查询不返回查询的数据，只产谓词的子查询不返回查询的数据，只产生逻辑真值（有数据）和假值（没有数据）。生逻辑真值（有数据）和假值（没有数据
54、）。例例69.查询选修了查询选修了c01课程的学生姓名。课程的学生姓名。SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=c01)注意处理过程为：先外后内；由外层的值决定内层的处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外层结果数决定。结果；内层执行次数由外层结果数决定。由于由于EXISTS的子查询只能返回真或假值，因此在的子查询只能返回真或假值，因此在这里给出列名无意义。所以在有这里给出列名无意义。所以在有EXISTS的子查询的子查询中，其目标列表达式通常都用中，其目标列表达式
55、通常都用*。上句的处理过程1.找找外外层层表表Student表表的的第第一一行行，根根据据其其Sno值处理内层查询值处理内层查询2.由由外外层层的的值值与与内内层层的的结结果果比比较较，由由此此决决定定外层条件的真、假外层条件的真、假3.顺序处理外层表顺序处理外层表Student表中的第表中的第2、3、行。行。示例例例71.查询没有选修查询没有选修c01号课程的学生姓名和所在系。号课程的学生姓名和所在系。SELECTSname,SdeptFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=c01)或：或：SELEC
56、TSname,SdeptFROMStudentWHERESnoNOTIN(SELECTSnoFROMSCWHERECno=c01)注意：不注意：不能用连接能用连接查询和在查询和在子查询中子查询中否定的形否定的形式实现。式实现。示例例例71查询计算机系没有选修查询计算机系没有选修“VB”课程的学生的姓名课程的学生的姓名和性别。和性别。SELECTSname,SsexFROMStudentWHERESnoNOTIN(-查询选了查询选了VB的学生的学生SELECTSnoFROMSCJOINCourseONSC.Cno=Course.CnoWHERECname=VB)ANDSdept=计算机系计算机系
57、5.2 数据更改功能 5.2.1插入数据插入数据5.2.2更新数据更新数据5.2.3删除数据删除数据5.2.1 插入数据 插入单行记录的插入单行记录的INSERT语句的格式为：语句的格式为：INSERTINTO()VALUES（值表）（值表）功能：新增一个符合表结构的数据行，将值表数功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序据按表中列定义顺序或列名表顺序或列名表顺序赋给对应列名。赋给对应列名。注意值列表中的值与列名表中的列按位置顺序对应，值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。它们的数据类型必须一致。如果如果后边没有指明列名，则新插入记录后边没有指明
58、列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）。每一个列均有值（可以为空）。示例例例72将新生记录（将新生记录（9521104，陈冬，男，陈冬，男，18岁，岁，信息系）插入到信息系）插入到Student表中。表中。INSERTINTOStudentVALUES(9521104,陈冬陈冬,男男,18, 信息系信息系)例例73在在SC表中插入一新记录，学号为表中插入一新记录，学号为“9521104”，选的课程号为，选的课程号为“c01”，成绩暂缺。，成绩暂缺。INSERTINTOSC(Sno,Cno)VALUES(952
59、1104,c01)实际插入的值为：实际插入的值为：(9521105,c01，NULL)5.2.2 更新数据 用用UPDATE语句实现。语句实现。格式：格式：UPDATESET,nWHERE1.无条件更新例例74.将所有学生的年龄加将所有学生的年龄加1。UPDATEStudentSETSage=Sage+12.有条件更新（1）基于本表条件的更新）基于本表条件的更新例例75.将将9512101学生的年龄改为学生的年龄改为21岁。岁。UPDATEStudentSETSage=21WHERESno=9512101（2）基于其他表条件的更新例例76：将计算机系全体学生的成绩加：将计算机系全体学生的成绩加
60、5分。分。（1）用子查询实现）用子查询实现UPDATESCSETGrade=Grade+5WHERESnoIN(SELECTSnoFROMStudentWHERESdept=计算机系计算机系)（2）用多表连接实现）用多表连接实现UPDATESCSETGrade=Grade+5FROMSCJOINStudentONSC.Sno=Student.SnoWHERESdept=计算机系计算机系示例例例77.将学分最低的课程的学分加将学分最低的课程的学分加2分。分。UPDATECourseSETCredit=Credit+2WHERECredit=(SELECTMIN(Credit)FROMCourse
61、)示例示例例例78.数学系学生的数学系学生的VB考试成绩增加考试成绩增加10分。分。用子查询实现。用子查询实现。UPDATESCSETGrade=Grade+10WHERECnoIN(SELECTCnoFROMCourseWHERECname=VB)ANDSnoIN(SELECTSnoFROMStudentWHERESdept=数学系数学系)用多表连接实现。用多表连接实现。UPDATESCSETGrade=Grade+10FROMSCJOINCourseCONC.Cno=SC.CnoJOINStudentSONS.Sno=SC.SnoWHERECname=VBANDSdept=数学系数学系5.
62、2.3 删除数据 用用DELETE语句实现语句实现格式：格式：DELETEFROMWHERE1. 无条件删除例例79.删除所有学生的选课记删除所有学生的选课记录。录。DELETEFROMSC2. 有条件删除 （1）基于本表条件的删除。）基于本表条件的删除。例例80删除所有不及格学生的修删除所有不及格学生的修课记录。课记录。DELETEFROMSCWHEREGrade60（2）基于其他表条件的删除例例81删除计算机系不及格学生的修课记录。删除计算机系不及格学生的修课记录。（1）用子查询实现）用子查询实现DELETEFROMSCWHEREGrade60ANDSnoIN(SELECTSnoFROMS
63、tudentWHERESdept= 计算机系计算机系)（2）用多表连接实现）用多表连接实现DELETEFROMSCFROMSCJOINStudentONSC.Sno=Student.SnoWHERESdept=计算机系计算机系 ANDGrade60示例例例81.删除信息系考试成绩不及格学生的不及格课程的选删除信息系考试成绩不及格学生的不及格课程的选课记录。课记录。用多表连接形式实现。用多表连接形式实现。DELETEFROMSCFROMStudentSJOINSCONS.Sno=SC.snoWHERESdept=信息系信息系ANDGrade60用子查询形式实现。用子查询形式实现。DELETEFROMSCWHERESnoIN(SELECTSnoFROMStudentWHERESdept=信息系信息系)ANDGrade60
