《SQL课件七数据查询》由会员分享，可在线阅读，更多相关《SQL课件七数据查询（75页珍藏版）》请在金锄头文库上搜索。
1、SQLSQL课件课件-七、数据查询七、数据查询目标目标单表查询 按列查询、查询经过计算的值、消除取值重复的行、条件查询、对查询结果排序、使用集函数、对查询结果分组连接查询 等值与非等值连接查询、自身连接、复合条件连接嵌套查询 带有IN谓词的子句查询、带有比较运算符的子查询集合查询本章重点: 数据查询-select本章难点: 1. 连接查询 2. 嵌套查询重点和难点重点和难点查询的一般格式为：查询的一般格式为：select all|distinct目标列表达式目标列表达式1,目标列表达式目标列表达式2 from表名或视图名表名或视图名1,表名或视图名表名或视图名2where条件表达式条件表达式g
2、roup by列名列名1havingorder byasc|descSelect子句指定要显示的列子句指定要显示的列,from子句列出查询的对象表子句列出查询的对象表SQLSQL数据查询基本结构数据查询基本结构基本结构基本结构:select A1 , A2 , , Anfromr1 , r2 , , rmwhereP A1 , A2 , , An( p(r1 r2 rm)单表查询单表查询一按列查询查询指定列例例1：查询所有学生的学号和姓名：查询所有学生的学号和姓名select学号学号,姓名姓名from学生表学生表例例2：查询所有学生的姓名，学号和专业：查询所有学生的姓名，学号和专业select
3、姓名姓名,学号学号,专业专业from学生表学生表查询全部列查询全部列例例3：查询全部学生的所有信息：查询全部学生的所有信息select*from学生表学生表select学号学号,姓名姓名,性别性别,年龄年龄,专业专业from学生表学生表“*”-表示表示“所有的属性所有的属性”目标列可以为列名，目标列可以为列名，*，算术表达式，聚集函数，算术表达式，聚集函数例：查询全部学生的姓名和出生年份例：查询全部学生的姓名和出生年份select姓名姓名,2000-年龄年龄from学生表学生表select姓名姓名,2000-年龄年龄YearofBirthfrom学生表学生表查询经过计算的值查询经过计算的值se
4、lect姓名姓名,2000-年龄年龄YearofBirth,lower(专业专业)from学生表学生表select姓名姓名name,2000-年龄年龄YearofBirth,lower(专业专业)departmentfrom学生表学生表假如假如SC表有表表有表1中的数据中的数据例例1：select学号学号from学生课程表学生课程表-语句执行的结果为表语句执行的结果为表2。例例2：selectdistinct学号学号from学生课程表学生课程表-语句执行的结果为表语句执行的结果为表3。表1表2表3缺省为保留重复元组，也可用关键字缺省为保留重复元组，也可用关键字allall显式指显式指明。若要去
5、掉重复元组，可用关键字明。若要去掉重复元组，可用关键字distinctdistinct消除取值重复的行消除取值重复的行条件查询条件查询常用的查询条件常用的查询条件比较条件查询比较条件查询例例1：查询查询ceacceac系全体学生的名单系全体学生的名单. .select姓名姓名from学生表学生表where专业专业=ceac例例2：查询所有年龄在查询所有年龄在20岁以下学生姓名及其年龄岁以下学生姓名及其年龄.select姓名姓名,年龄年龄from学生表学生表where年龄年龄20例例3：查考试成绩有不及格的学生的学号查考试成绩有不及格的学生的学号. .selectdistinct学号学号from
6、学生课程表学生课程表where成绩成绩60Between.and:Notbetween.and:判断表达式的值是否在某范围内判断表达式的值是否在某范围内.例例1:查询年龄在查询年龄在20至至23岁之间的学生的姓名、系别、和年龄岁之间的学生的姓名、系别、和年龄. .select姓名姓名,系别系别,年龄年龄from学生表学生表where年龄年龄 between20and23确定范围的条件查询确定范围的条件查询例例2：查询年龄不在查询年龄不在20至至23岁之间的学生姓名、系别和年龄岁之间的学生姓名、系别和年龄. .select姓名姓名,系别系别,年龄年龄from学生表学生表where年龄年龄 not
7、 between20and23确定集合的条件查询确定集合的条件查询谓词：谓词：IN,NOTIN判断表达式的值是否在子查询的结果中判断表达式的值是否在子查询的结果中.例例1：查询专业是：查询专业是ceac,wz或者或者wj学生的姓名学生的姓名.select姓名姓名from学生表学生表where专业专业 in (ceac,wz,wj)例例2：求专业不是：求专业不是ceac,wz和和wj的同学的姓名和性别的同学的姓名和性别.select姓名姓名from学生表学生表where专业专业 not in (ceac,wz,wj)字符匹配的条件查询字符匹配的条件查询谓词like的一般语法格式为：not lik
8、e 匹配串 escape 换码字符-其含义是查找指定的属性列值与其含义是查找指定的属性列值与相相匹配的元组。匹配的元组。-可以是一个完整的字符串，也可以可以是一个完整的字符串，也可以含有通配符含有通配符%和和_。匹配规则匹配规则 “%” -匹配零个或多个字符 “” -匹配任意单个字符escape -定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待. -如escape，定义作为转义字符，则可用%去匹配%，用去匹配_.思考：用什么去匹配？例例：查找姓刘的学生的姓名：查找姓刘的学生的姓名,年龄年龄,性别性别 select姓名姓名,年龄年龄,性别性别 from学生表学生表 where 姓
9、名姓名like刘刘%例例1：查询姓周的并且姓名为三个字的同学的学号和姓名：查询姓周的并且姓名为三个字的同学的学号和姓名select姓名姓名from学生表学生表where姓名姓名like周周_换码字符换码字符( (转义符转义符) )的使用的使用例例1.查询课程名以查询课程名以”ch_”开头的所有课程的信息开头的所有课程的信息.select*from课程表课程表where课程名课程名likech_%escapeescape：定定义义转转义义字字符符，用用它它来来去去掉掉特特殊殊字字符符的的特特定定含含义义，使使其其被被作作为为普普通字符看待通字符看待.在此例中，这时在此例中，这时后面的字符后面的字
10、符_就就不再具有通配符的含义不再具有通配符的含义例例2.查查询询课课程程名名以以”sql_”开开头头，且且倒倒数数第第3个个字字符符为为v的的课课程程和和它它的的详详细情况。细情况。select*from课程表课程表where课程名课程名likesql_%v_escape涉及空值的条件查询涉及空值的条件查询例例1：查缺少成绩的学生的学号和相应的课程号查缺少成绩的学生的学号和相应的课程号. .select学号学号,课程号课程号from学生选课表学生选课表where成绩成绩isnull例例2：查所有有成绩的记录的学生学号和课程号查所有有成绩的记录的学生学号和课程号select学号学号,课程号课程号
11、from学生选课表学生选课表where成绩成绩isnotnull 注意事项注意事项: :除除isnotnull之外，空值不满足任何查找条件之外，空值不满足任何查找条件. .如果如果null参与算术运算，则该算术表达式的值为参与算术运算，则该算术表达式的值为null.如果如果null参与聚集运算，则除参与聚集运算，则除count(*)之外其它聚集函数都忽略之外其它聚集函数都忽略null. . 如果如果null参与比较运算，则结果可视为参与比较运算，则结果可视为false。在。在SQL-92中可看成中可看成unknown.多重条件查询多重条件查询例例1：查找是查找是ceac专业并且年龄在专业并且年
12、龄在22岁以下的学生的姓名岁以下的学生的姓名. .select姓名姓名 from学生表学生表where专业专业=ceacand年龄年龄1例例3：查询是查询是ceac专业专业,并且并且选修了选修了1门以上课程的学生的学号门以上课程的学生的学号. .select学号学号from学生选课表学生选课表where学号学号in(select学号学号from学生表学生表where专业专业=ceac)groupby学号学号havingcount(课程号课程号)1ComputeComputecompute ：生成合计作为附加的汇总列出现在结果集的最后，当与 BY 一起使用时，COMPUTE 子句在结果集内生成控
13、制中断和小计 。 注意： 1、 由于包含 COMPUTE 的语句生成表并且这些表的汇总结果不存储在数据库中，因此在 SELECT INTO 语句中不能使用 COMPUTE。2、当与BY一起使用时，必须指定排序ComputeCompute实例实例selecttype,price,advancefromtitlescomputesum(price),avg(advance)selecttype,price,advancefromtitlesorderby tpyecomputesum(price),avg(advance)bytype 连接查询连接查询比较连接查询比较连接查询自身连接自身连接外连接
14、外连接复合条件连接复合条件连接 比较连接查询比较连接查询连接查询中用来连接两个表的条件称为连接条件或或连接谓词，其一般格式为：表名表名1.列名列名1比较运算符比较运算符表名表名2.列名列名2 其中比较运算符主要有：=、= 、 80例例2：求学生的学号：求学生的学号,姓名姓名,课程号课程号,课程名课程名,成绩成绩.select学生表学生表.学号学号,姓名姓名,课程表课程表.课程号课程号,课程名课程名,成绩成绩from学生表学生表,学生选课表学生选课表,课程表课程表where学生表学生表.学号学号=学生选课表学生选课表.学号学号and学生选课表学生选课表.课程号课程号=课程表课程表.课程号课程号嵌
15、套查询嵌套查询在在SQL语言中，一个语言中，一个select-from-where语句称为一个语句称为一个查询块。将一个查询块嵌套在另一个查询块的。将一个查询块嵌套在另一个查询块的where子句子句或或having短语的条件中的查询称为嵌套查询。短语的条件中的查询称为嵌套查询。例例:求选修了求选修了02号课程的学生的姓名号课程的学生的姓名.select姓名姓名from学生表学生表where学号学号in（select学号学号from学生选课表学生选课表where课程号课程号=02）带有带有ININ谓词的子查询谓词的子查询例：查询选修了查询选修了math课程的同学的学号和姓名课程的同学的学号和姓名
16、.select 学号学号,姓名姓名 from 学生表学生表 where 学号学号 in (select 学号学号 from 学生选课表学生选课表where 课程号课程号in (select 课程号课程号 from 学生选课表学生选课表 where 课程名课程名=math)用连接查询实现上例用连接查询实现上例:select学生表学生表.学号学号,姓名姓名from学生表学生表,学生选课表学生选课表,课程表课程表where学生表学生表.学号学号=学生选课表学生选课表.学号学号and学生选课表学生选课表.课程号课程号=课程表课程表.课程号课程号and课程名课程名=english带有比较运算符的子查询带
17、有比较运算符的子查询例：列出和刘风在一个专业的学生的学号例：列出和刘风在一个专业的学生的学号,姓名姓名,专业专业select 学号学号,姓名姓名,专业专业 from 学生表学生表where专业专业=(select 专业专业from 学生表学生表 where姓名姓名=刘风刘风)集合查询集合查询并操作union的使用:例例1:查询查询ceac专业的学生及年龄不大于专业的学生及年龄不大于24岁的学生的信息岁的学生的信息.select*from学生表学生表where专业专业=信息信息unionselect*from学生表学生表where年龄年龄=24例例2：查询选修了课程：查询选修了课程1或者选修了课
18、程或者选修了课程2的学生的学生.select学号学号from学生选课表学生选课表where课程号课程号=01unionselect学号学号from课程表课程表where课程号课程号=02例例3:查询查询ceac专业的学生与年龄不大于专业的学生与年龄不大于23岁的学生的交集岁的学生的交集.select*from学生表学生表where专业专业=ceacand年龄年龄23T-SQL语句的综合应用语句的综合应用学员信息表和成绩表学员信息表和成绩表应到人数：应到人数：5人人实到人数实到人数4人，人，缺考缺考1人人T-SQL语句的综合应用语句的综合应用如何实现？如何实现？本次考试的缺考情况本次考试的缺考情
19、况比较笔试平均分和机试平比较笔试平均分和机试平均分，较低者进行循环提均分，较低者进行循环提分，但提分后最高分不能分，但提分后最高分不能超过超过97分分。加分后重新。加分后重新统计通过情况统计通过情况统计通过率统计通过率T-SQL语句的综合应用语句的综合应用1.提示提示:使用子查询统计缺考情况：使用子查询统计缺考情况：应到人数：应到人数：SELECTcount(*)FROMstuInfo实到人数：实到人数：SELECTcount(*)FROMstuMarks2.提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成绩、提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成
20、绩、是否通过是否通过1)提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接（提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接（LEFTJOIN）；）；SELECTstuNameFROMstuInfoLEFTJOINstuMarks2)要求新加一列要求新加一列“是否通过（是否通过（isPass）”，可采用，可采用CASEEND。为了便于后续的通。为了便于后续的通过率统计，通过则为过率统计，通过则为1，没通过为，没通过为0SELECTisPass=CASEWHENwrittenExam=60THEN1ELSE0END3)要求保存提取（查询）的结果，可以使用我们曾学习过的要求保存提
21、取（查询）的结果，可以使用我们曾学习过的SELECTINTOnewTable语句，生成新表并保存数据语句，生成新表并保存数据T-SQL语句的综合应用语句的综合应用3.比较笔试平均分和机试平均分，对较低者进行循环提分，但提分后最高分不能超比较笔试平均分和机试平均分，对较低者进行循环提分，但提分后最高分不能超过过97分：分：1)使用使用IF语句语句判断笔试还是机试偏低，决定对笔试还是机试提分；判断笔试还是机试偏低，决定对笔试还是机试提分；2)使用使用WHILE循环循环给每个学员加分，缺考的除外，当最高分超过给每个学员加分，缺考的除外，当最高分超过97分时退出循环；分时退出循环；3)因为给每位学员的
22、笔试或机试提分了，有的学员可能提分后刚好通过了，所以需因为给每位学员的笔试或机试提分了，有的学员可能提分后刚好通过了，所以需要更新要更新isPass（是否通过）列。（是否通过）列。UPDATEnewTableSETisPass=CASEWHENwrittenExam=60andlabExam=60THEN1ELSE0ENDT-SQL语句的综合应用语句的综合应用4.提分后，统计学员的成绩和通过情况：提分后，统计学员的成绩和通过情况：1）使用别名实现中文字段名，即）使用别名实现中文字段名，即SELECT姓名姓名=stuName,学号学号=stuNo2）如果某个学员的成绩为）如果某个学员的成绩为NU
23、LL(空空)，则替换为，则替换为”缺考缺考”，否则原样显示；，否则原样显示；3）isPass列中的列中的1替换为是，替换为是，0替换为否；替换为否；SELECT,机试成绩机试成绩=CASEWHENlabExamISNULLTHEN缺考缺考ELSEconvert(varchar(5),labExam)END,是否通过是否通过=CASEWHENisPass=1THEN是是ELSE否否ENDT-SQL语句的综合应用语句的综合应用5.提分后统计学员的通过率情况：提分后统计学员的通过率情况：1）通过人数：因为通过用）通过人数：因为通过用1表示，没通过用表示，没通过用0表示，所以表示，所以isPass列的
24、累加和即是列的累加和即是通过人数；通过人数；2）通过率：同理，）通过率：同理，isPass列的平均值列的平均值*100即是通过率；即是通过率；T-SQL参考语句参考语句/*-本次考试的原始数据本次考试的原始数据-*/-SELECT*FROMstuInfo-SELECT*FROMstuMarks/*-统计考试缺考情况统计考试缺考情况-*/SELECT应到人数应到人数=(SELECTcount(*)FROMstuInfo),-应到人数为子查应到人数为子查询表达式的别名询表达式的别名实到人数实到人数=(SELECTcount(*)FROMstuMarks),缺考人数缺考人数=(SELECTcount
25、(*)FROMstuInfo)-(SELECTcount(*)FROMstuMarks)T-SQL参考语句参考语句/*-统计考试通过情况，并将结果存放在新表统计考试通过情况，并将结果存放在新表newTable中中-*/IFEXISTS(SELECT*FROMsysobjectsWHEREname=newTable)DROPTABLEnewTableSELECTstuName,stuInfo.stuNo,writtenExam,labExam,isPass=CASEWHENwrittenExam=60andlabExam=60THEN1ELSE0ENDINTOnewTableFROMstuInf
26、oLEFTJOINstuMarksONstuInfo.stuNo=stuMarks.stuNo-SELECT*FROMnewTable-查看统计结果，可用于调试查看统计结果，可用于调试T-SQL参考语句参考语句/*-酌情加分：比较笔试和机试平均分，决定加哪门酌情加分：比较笔试和机试平均分，决定加哪门-*/DECLAREavgWrittennumeric(4,1)DECLAREavgLabnumeric(4,1)SELECTavgWritten=AVG(writtenExam)FROMnewTableWHEREwrittenExamISNOTNULLSELECTavgLab=AVG(labExa
27、m)FROMnewTableWHERElabExamISNOTNULLIFavgWritten=97BREAKENDELSE略略-循环给笔试加分，最高分不能超过循环给笔试加分，最高分不能超过97分分T-SQL参考语句参考语句-因为提分，所以需要更新因为提分，所以需要更新isPass（是否通过）列的数据（是否通过）列的数据UPDATEnewTableSETisPass=CASEWHENwrittenExam=60andlabExam=60THEN1ELSE0END-SELECT*FROMnewTable-可用于调试可用于调试/*-显示考试最终通过情况显示考试最终通过情况-*/SELECT姓名姓名
28、=stuName,学号学号=stuNo,笔试成绩笔试成绩=CASEWHENwrittenExamISNULLTHEN缺考缺考ELSEconvert(varchar(5),writtenExam)END,机试成绩机试成绩=CASEWHENlabExamISNULLTHEN缺考缺考ELSEconvert(varchar(5),labExam)END,是否通过是否通过=CASEWHENisPass=1THEN是是ELSE否否ENDFROMnewTableT-SQL参考语句参考语句/*-显示通过率及通过人数显示通过率及通过人数-*/SELECT总人数总人数=count(*),通过人数通过人数=SUM(
29、isPass),通过率通过率=(convert(varchar(5),AVG(isPass*100)+%)FROMnewTable总结总结总结我们曾学习过的查询，合并多个表中的数据的方法有总结我们曾学习过的查询，合并多个表中的数据的方法有三种：三种：联合联合(Union)合并多个数据表中的行合并多个数据表中的行子查询将一个查询包含到另一个查询中子查询将一个查询包含到另一个查询中联接合并多个数据表中的列联接合并多个数据表中的列通过在子查询中使用通过在子查询中使用EXISTS子句，可以对子查询中的行子句，可以对子查询中的行是否存在进行检查是否存在进行检查IN子查询后面可跟随返回多条记录的子查询，用
30、于检测某子查询后面可跟随返回多条记录的子查询，用于检测某列的值是否在某个范围列的值是否在某个范围作业作业有一个学生管理系统，该系统中的有一个学生管理系统，该系统中的stms数据库由两个数据文件数据库由两个数据文件stms_data1，stms_data2(stms_data1属于属于primary文件组而文件组而stms_data2属于属于user2组）和一个事务日志文件组成，该库中包含三个组）和一个事务日志文件组成，该库中包含三个表且各个表的基本情况如下：表且各个表的基本情况如下：学生表学生表(学号学号,姓名姓名,性别性别,年龄年龄,专业专业),主码是学号主码是学号.课程表课程表(课程号课程
31、号,课程名课程名,先修课程号先修课程号,学分学分)，主码是课程号，主码是课程号.学生选课表学生选课表(学号学号,课程号课程号,成绩成绩),主码是主码是(学号学号,课程号课程号).1.查询选修查询选修1号课程的全部学生的姓名，年龄，出生年份（假定当前为号课程的全部学生的姓名，年龄，出生年份（假定当前为2006年）年）.2.以以name,birthday为标题，显示学生表中所有学生的姓名，出生年份（假定当前为为标题，显示学生表中所有学生的姓名，出生年份（假定当前为2006年）年）,并按姓名降序排列并按姓名降序排列.3.显示选修显示选修1号课程学生的最高分，最低分，平均分，号课程学生的最高分，最低分
32、，平均分，总分及人数总分及人数(写一个语句写一个语句)4.显示选课数目大于显示选课数目大于3的学生的学号及选课的学生的学号及选课.5.至少选修了至少选修了3，4号课的学生姓名号课的学生姓名.6.查询和王芳在一个系的学生姓名和年龄查询和王芳在一个系的学生姓名和年龄.7.查询没选查询没选1号课的学生的姓名号课的学生的姓名.8.由于学员的总学分提高了，而课程还是那么多，现要求把课程的学分在由于学员的总学分提高了，而课程还是那么多，现要求把课程的学分在5分以下加分以下加4，5-8之间之间的课程加的课程加3，8-12之间的课程加之间的课程加2,大于大于12分的课程加分的课程加1，且要求以后可能还要修改，请设计具，且要求以后可能还要修改，请设计具体语句，选择好形式。体语句，选择好形式。结束结束
