《SQL课件--七、数据查询.ppt》由会员分享，可在线阅读，更多相关《SQL课件--七、数据查询.ppt（75页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、目标目标单表查询 按列查询、查询经过计算的值、消除取值重复的行、条件查询、对查询结果排序、使用集函数、对查询结果分组连接查询 等值与非等值连接查询、自身连接、复合条件连接嵌套查询 带有IN谓词的子句查询、带有比较运算符的子查询集合查询本章重点: 数据查询-select本章难点: 1. 连接查询 2. 嵌套查询重点和难点重点和难点查询的一般格式为：查询的一般格式为：select all | distinct 目标列表达式目标列表达式1 ,目标列表达式目标列表达式2 from 表名或视图名表名或视图名1 , 表名或视图名表名或视图名2 where 条件表达式条件表达式 group by 列名列名1
2、 having order by asc|descSelect子句指定要显示的列子句指定要显示的列,from子句列出查询的对象表子句列出查询的对象表SQLSQL数据查询基本结构数据查询基本结构基本结构基本结构:select A1 , A2 , , An from r1 , r2 , , rm where P A1 , A2 , , An( p(r1 r2 rm)单表查询单表查询一按列查询查询指定列 例例1：查询所有学生的学号和姓名：查询所有学生的学号和姓名select 学号学号,姓名姓名 from 学生表学生表 例例2：查询所有学生的姓名，学号和专业：查询所有学生的姓名，学号和专业select
3、 姓名姓名,学号学号,专业专业 from 学生表学生表查询全部列查询全部列例例3：查询全部学生的所有信息：查询全部学生的所有信息select * from 学生表学生表 select 学号学号 ,姓名姓名,性别性别,年龄年龄, 专业专业 from 学生表学生表“*” -表示表示“所有的属性所有的属性”目标列可以为列名，目标列可以为列名，* ，算术表达式，聚集函数，算术表达式，聚集函数例：查询全部学生的姓名和出生年份例：查询全部学生的姓名和出生年份 select 姓名姓名,2000-年龄年龄 from 学生表学生表 select 姓名姓名, 2000-年龄年龄 Year of Birth fro
4、m 学生表学生表 查询经过计算的值查询经过计算的值select 姓名姓名, 2000-年龄年龄 Year of Birth,lower(专业专业) from 学生表学生表select 姓名姓名 name, 2000-年龄年龄 Year of Birth, lower(专业专业) department from 学生表学生表假如假如SC表有表表有表1中的数据中的数据 例例1：select 学号学号 from 学生课程表学生课程表 -语句执行的结果为表语句执行的结果为表2。 例例2：select distinct 学号学号 from学生课程表学生课程表 -语句执行的结果为表语句执行的结果为表3。S
5、noCnoGrade 950011929500128595002298Sno 950019500195002Sno 9500195002表1表2表3消除取值重复的行消除取值重复的行条件查询条件查询常用的查询条件常用的查询条件查询条件查询条件谓词谓词比较确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空 值IS NULL，IS NOT NULL多重条件AND，OR比较条件查询比较条件查询例例1：查询查询ceacceac系全体学生的名单系全体学生的名单. . select 姓名姓名 from 学生表学生表 where 专业专
6、业=ceac例例2：查询所有年龄在查询所有年龄在20岁以下学生姓名及其年龄岁以下学生姓名及其年龄. select 姓名姓名,年龄年龄 from 学生表学生表 where 年龄年龄20例例3：查考试成绩有不及格的学生的学号查考试成绩有不及格的学生的学号. . select distinct 学号学号 from 学生课程表学生课程表 where 成绩成绩60 Between .and:Not between .and:判断表达式的值是否在某范围内判断表达式的值是否在某范围内.例例1: 查询年龄在查询年龄在20至至23岁之间的学生的姓名、系别、和年岁之间的学生的姓名、系别、和年龄龄. . selec
7、t 姓名姓名,系别系别,年龄年龄 from 学生表学生表 where 年龄年龄 between 20 and 23确定范围的条件查询确定范围的条件查询例例2：查询年龄不在查询年龄不在20至至23岁之间的学生姓名、系别和年龄岁之间的学生姓名、系别和年龄. . select 姓名姓名,系别系别,年龄年龄 from 学生表学生表 where 年龄年龄 not between 20 and 23确定集合的条件查询确定集合的条件查询谓词：谓词：IN , NOT IN 判断表达式的值是否在子查询的结果中判断表达式的值是否在子查询的结果中.例例1：查询专业是：查询专业是ceac, wz或者或者wj学生的姓名
8、学生的姓名. select 姓名姓名 from 学生表学生表 where 专业专业 in (ceac,wz,wj)例例2：求专业不是：求专业不是ceac, wz和和wj的同学的姓名和性别的同学的姓名和性别. select 姓名姓名 from 学生表学生表 where 专业专业 not in (ceac,wz,wj)字符匹配的条件查询字符匹配的条件查询谓词like的一般语法格式为：not like 匹配串 escape 换码字符 -其含义是查找指定的属性列值与其含义是查找指定的属性列值与相相匹配的元组。匹配的元组。 -可以是一个完整的字符串，也可以可以是一个完整的字符串，也可以含有通配符含有通配
9、符%和和_。 匹配规则匹配规则 “%” -匹配零个或多个字符 “” -匹配任意单个字符 escape -定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待. -如escape ，定义作为转义字符，则可用%去匹配%，用去匹配_.思考：用什么去匹配？例例：查找姓刘的学生的姓名：查找姓刘的学生的姓名,年龄年龄,性别性别 select 姓名姓名,年龄年龄,性别性别 from 学生表学生表 where 姓名姓名 like 刘刘%例例1：查询姓周的并且姓名为三个字的同学的学号和姓名：查询姓周的并且姓名为三个字的同学的学号和姓名 select 姓名姓名 from 学生表学生表 where 姓名姓
10、名 like 周周_换码字符换码字符( (转义符转义符) )的使用的使用例例1.查询课程名以查询课程名以”ch_”开头的所有课程的信息开头的所有课程的信息. select * from 课程表课程表 where 课程名课程名 like ch_% escape escape：定义转义字符，用它来去掉特殊字符的特定含义，使其被作为普定义转义字符，用它来去掉特殊字符的特定含义，使其被作为普通字符看待通字符看待.在此例中，这时在此例中，这时后面的字符后面的字符_就就不再具有通配符的含义不再具有通配符的含义例例2.查询课程名以查询课程名以”sql_”开头，且倒数第开头，且倒数第3个字符为个字符为v的课程
11、和它的详的课程和它的详细情况。细情况。 select * from 课程表课程表 where 课程名课程名 like sql_%v_escape 涉及空值的条件查询涉及空值的条件查询例例1：查缺少成绩的学生的学号和相应的课程号查缺少成绩的学生的学号和相应的课程号. . select 学号学号,课程号课程号 from 学生选课表学生选课表 where 成绩成绩 is null例例2：查所有有成绩的记录的学生学号和课程号查所有有成绩的记录的学生学号和课程号 select 学号学号,课程号课程号 from 学生选课表学生选课表 where 成绩成绩 is not null 注意事项注意事项: :除除
12、is not null之外，空值不满足任何查找条件之外，空值不满足任何查找条件. .如果如果null参与算术运算，则该算术表达式的值为参与算术运算，则该算术表达式的值为null.如果如果null参与聚集运算，则除参与聚集运算，则除count(*)之外其它聚集函数都忽略之外其它聚集函数都忽略null. . 如果如果null参与比较运算，则结果可视为参与比较运算，则结果可视为false。在。在SQL-92中可看成中可看成unknown.多重条件查询多重条件查询例例1：查找是查找是ceac专业并且年龄在专业并且年龄在22岁以下的学生的姓名岁以下的学生的姓名. . select 姓名姓名 from 学
13、生表学生表 where 专业专业 =ceac and 年龄年龄1例例3：查询是查询是ceac专业专业,并且并且选修了选修了1门以上课程的学生的学号门以上课程的学生的学号. . select 学号学号 from 学生选课表学生选课表 where 学号学号 in (select 学号学号 from 学生表学生表 where 专业专业=ceac) group by 学号学号 having count(课程号课程号) 1ComputeCompute compute ：生成合计作为附加的汇总列出现在结果集的最后，当与 BY 一起使用时，COMPUTE 子句在结果集内生成控制中断和小计 。 注意： 1、
14、由于包含 COMPUTE 的语句生成表并且这些表的汇总结果不存储在数据库中，因此在 SELECT INTO 语句中不能使用 COMPUTE 。 2、当与BY一起使用时，必须指定排序ComputeCompute实例实例select type,price,advance from titles compute sum(price),avg(advance)select type,price,advance from titles order by tpye compute sum(price),avg(advance) by type 连接查询连接查询比较连接查询比较连接查询自身连接自身连接外连接
15、外连接复合条件连接复合条件连接 比较连接查询比较连接查询连接查询中用来连接两个表的条件称为连接条件或或连接谓词，其一般格式为： 表名表名1.列名列名1 比较运算符比较运算符 表名表名2. 列名列名2 其中比较运算符主要有：=、= 、 80 例例2：求学生的学号：求学生的学号, 姓名姓名, 课程号课程号,课程名课程名, 成绩成绩. select 学生表学生表.学号学号, 姓名姓名, 课程表课程表.课程号课程号,课程名课程名, 成绩成绩 from 学生表学生表, 学生选课表学生选课表, 课程表课程表 where 学生表学生表.学号学号 =学生选课表学生选课表.学号学号 and 学生选课表学生选课表
16、.课程号课程号 =课程表课程表.课程号课程号 嵌套查询嵌套查询 在在SQL语言中，一个语言中，一个select-from-where语句称为一个语句称为一个查询块。将一个查询块嵌套在另一个查询块的。将一个查询块嵌套在另一个查询块的where子句子句或或having短语的条件中的查询称为嵌套查询。短语的条件中的查询称为嵌套查询。例例:求选修了求选修了02号课程的学生的姓名号课程的学生的姓名. select 姓名姓名 from 学生表学生表 where 学号学号 in （select 学号学号 from 学生选课表学生选课表 where 课程号课程号=02）带有带有ININ谓词的子查询谓词的子查询
17、例：查询选修了查询选修了math课程的同学的学号和姓名课程的同学的学号和姓名.select 学号学号,姓名姓名 from 学生表学生表 where 学号学号 in (select 学号学号 from 学生选课表学生选课表 where 课程号课程号in (select 课程号课程号 from 学生选课表学生选课表 where 课程名课程名=math)用连接查询实现上例用连接查询实现上例: select 学生表学生表.学号学号,姓名姓名 from 学生表学生表,学生选课表学生选课表,课程表课程表 where 学生表学生表.学号学号 =学生选课表学生选课表.学号学号 and 学生选课表学生选课表.课
18、程号课程号 =课程表课程表.课程号课程号 and 课程名课程名=english带有比较运算符的子查询带有比较运算符的子查询例：列出和刘风在一个专业的学生的学号例：列出和刘风在一个专业的学生的学号, 姓名姓名, 专业专业 select 学号学号, 姓名姓名, 专业专业 from 学生表学生表 where 专业专业 = (select 专业专业 from 学生表学生表 where 姓名姓名=刘风刘风) 集合查询集合查询并操作union的使用:例例1:查询查询ceac专业的学生及年龄不大于专业的学生及年龄不大于24岁的学生的信息岁的学生的信息. select * from 学生表学生表 where
19、专业专业=信息信息 union select * from 学生表学生表 where 年龄年龄=24例例2：查询选修了课程：查询选修了课程1或者选修了课程或者选修了课程2的学生的学生. select 学号学号 from 学生选课表学生选课表 where 课程号课程号=01 union select 学号学号 from 课程表课程表 where 课程号课程号=02 例例3:查询查询ceac专业的学生与年龄不大于专业的学生与年龄不大于23岁的学生的交集岁的学生的交集. select * from 学生表学生表 where 专业专业=ceac and 年龄年龄23 T-SQL语句的综合应用语句的综合
20、应用学员信息表和成绩表学员信息表和成绩表 应到人数：应到人数：5人人实到人数实到人数4人，人，缺考缺考1人人T-SQL语句的综合应用语句的综合应用如何实现？如何实现？本次考试的缺考情况本次考试的缺考情况 比较笔试平均分和机试平比较笔试平均分和机试平均分，较低者进行循环提均分，较低者进行循环提分，但提分后最高分不能分，但提分后最高分不能超过超过97分分 。加分后重新。加分后重新统计通过情况统计通过情况统计通过率统计通过率 T-SQL语句的综合应用语句的综合应用1.提示提示: 使用子查询统计缺考情况：使用子查询统计缺考情况：应到人数：应到人数：SELECT count(*) FROM stuInf
21、o实到人数：实到人数：SELECT count(*) FROM stuMarks2.提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成绩、提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成绩、是否通过是否通过1)提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接（提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接（ LEFT JOIN ）；）； SELECT stuNameFROM stuInfo LEFT JOIN stuMarks 2)要求新加一列要求新加一列“是否通过（是否通过（isPass）”，可采用，可采用CASE END。为了便于后续
22、的。为了便于后续的通过率统计，通过则为通过率统计，通过则为1，没通过为，没通过为0 SELECT isPass=CASE WHEN writtenExam=60 THEN 1 ELSE 0 END 3)要求保存提取（查询）的结果，可以使用我们曾学习过的要求保存提取（查询）的结果，可以使用我们曾学习过的SELECT INTO newTable语句，生成新表并保存数据语句，生成新表并保存数据 T-SQL语句的综合应用语句的综合应用3.比较笔试平均分和机试平均分，对较低者进行循环提分，但提分后最高分不能超比较笔试平均分和机试平均分，对较低者进行循环提分，但提分后最高分不能超过过97分：分：1) 使用
23、使用IF语句语句判断笔试还是机试偏低，决定对笔试还是机试提分；判断笔试还是机试偏低，决定对笔试还是机试提分；2) 使用使用WHILE循环循环给每个学员加分，缺考的除外，当最高分超过给每个学员加分，缺考的除外，当最高分超过97分时退出循环；分时退出循环；3)因为给每位学员的笔试或机试提分了，有的学员可能提分后刚好通过了，所以需因为给每位学员的笔试或机试提分了，有的学员可能提分后刚好通过了，所以需要更新要更新isPass（是否通过）列。（是否通过）列。 UPDATE newTable SET isPass=CASE WHEN writtenExam=60 and labExam=60 THEN 1
24、 ELSE 0 ENDT-SQL语句的综合应用语句的综合应用4.提分后，统计学员的成绩和通过情况：提分后，统计学员的成绩和通过情况：1）使用别名实现中文字段名，即）使用别名实现中文字段名，即SELECT 姓名姓名=stuName,学号学号=stuNo2）如果某个学员的成绩为）如果某个学员的成绩为NULL(空空)，则替换为，则替换为”缺考缺考”，否则原样显示；，否则原样显示；3）isPass列中的列中的1替换为是，替换为是，0替换为否；替换为否； SELECT ,机试成绩机试成绩=CASE WHEN labExam IS NULL THEN 缺考缺考 ELSE convert(varchar(5
25、),labExam) END ,是否通过是否通过=CASE WHEN isPass=1 THEN 是是 ELSE 否否 ENDT-SQL语句的综合应用语句的综合应用5.提分后统计学员的通过率情况：提分后统计学员的通过率情况：1）通过人数：因为通过用）通过人数：因为通过用1表示，没通过用表示，没通过用0表示，所以表示，所以isPass列的累加和即列的累加和即是通过人数；是通过人数；2）通过率：同理，）通过率：同理，isPass列的平均值列的平均值*100即是通过率；即是通过率；T-SQL参考语句参考语句/*-本次考试的原始数据本次考试的原始数据-*/-SELECT * FROM stuInfo-
26、SELECT * FROM stuMarks/*-统计考试缺考情况统计考试缺考情况-*/SELECT 应到人数应到人数=(SELECT count(*) FROM stuInfo) , -应到人数为子查应到人数为子查询表达式的别名询表达式的别名 实到人数实到人数=(SELECT count(*) FROM stuMarks) , 缺考人数缺考人数=(SELECT count(*) FROM stuInfo)-(SELECT count(*) FROM stuMarks) T-SQL参考语句参考语句/*-统计考试通过情况，并将结果存放在新表统计考试通过情况，并将结果存放在新表newTable中中
27、-*/IF EXISTS(SELECT * FROM sysobjects WHERE name=newTable) DROP TABLE newTableSELECT stuName,stuInfo.stuNo,writtenExam ,labExam , isPass=CASE WHEN writtenExam=60 and labExam=60 THEN 1 ELSE 0 END INTO newTable FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo -SELECT * FROM newTable -查看
28、统计结果，可用于调试查看统计结果，可用于调试 T-SQL参考语句参考语句/*-酌情加分：比较笔试和机试平均分，决定加哪门酌情加分：比较笔试和机试平均分，决定加哪门-*/DECLARE avgWritten numeric(4,1)DECLARE avgLab numeric(4,1) SELECT avgWritten=AVG(writtenExam) FROM newTable WHERE writtenExam IS NOT NULLSELECT avgLab=AVG(labExam)FROM newTable WHERE labExam IS NOT NULLIF avgWritten=
29、97 BREAK ENDELSE 略略 -循环给笔试加分，最高分不能超过循环给笔试加分，最高分不能超过97分分T-SQL参考语句参考语句 -因为提分，所以需要更新因为提分，所以需要更新isPass（是否通过）列的数据（是否通过）列的数据UPDATE newTable SET isPass=CASE WHEN writtenExam=60 and labExam=60 THEN 1 ELSE 0 END-SELECT * FROM newTable -可用于调试可用于调试 /*-显示考试最终通过情况显示考试最终通过情况-*/SELECT 姓名姓名=stuName,学号学号=stuNo ,笔试成绩
30、笔试成绩=CASE WHEN writtenExam IS NULL THEN 缺考缺考 ELSE convert(varchar(5),writtenExam) END ,机试成绩机试成绩=CASE WHEN labExam IS NULL THEN 缺考缺考 ELSE convert(varchar(5),labExam) END ,是否通过是否通过=CASE WHEN isPass=1 THEN 是是 ELSE 否否 END FROM newTable T-SQL参考语句参考语句 /*-显示通过率及通过人数显示通过率及通过人数-*/ SELECT 总人数总人数=count(*) ,通过人
31、数通过人数=SUM(isPass), 通过率通过率=(convert(varchar(5),AVG(isPass*100)+%) FROM newTable 总结总结 总结我们曾学习过的查询，合并多个表中的数据的方法有总结我们曾学习过的查询，合并多个表中的数据的方法有三种：三种：联合联合(Union)合并多个数据表中的行合并多个数据表中的行子查询将一个查询包含到另一个查询中子查询将一个查询包含到另一个查询中联接合并多个数据表中的列联接合并多个数据表中的列通过在子查询中使用通过在子查询中使用EXISTS子句，可以对子查询中的行子句，可以对子查询中的行是否存在进行检查是否存在进行检查IN子查询后面
32、可跟随返回多条记录的子查询，用于检测某子查询后面可跟随返回多条记录的子查询，用于检测某列的值是否在某个范围列的值是否在某个范围 作业作业 有一个学生管理系统，该系统中的有一个学生管理系统，该系统中的stms数据库由两个数据文件数据库由两个数据文件stms_data1，stms_data2(stms_data1属于属于primary文件组而文件组而stms_data2属于属于user2组）和一个事务日志文件组成，该库中包含三个组）和一个事务日志文件组成，该库中包含三个表且各个表的基本情况如下：表且各个表的基本情况如下：学生表学生表 (学号学号,姓名姓名,性别性别,年龄年龄,专业专业), 主码是学
33、号主码是学号.课程表课程表 (课程号课程号,课程名课程名,先修课程号先修课程号,学分学分) ，主码是课程号，主码是课程号.学生选课表学生选课表 (学号学号,课程号课程号,成绩成绩), 主码是主码是(学号学号,课程号课程号). 1.查询选修查询选修1号课程的全部学生的姓名，年龄，出生年份（假定当前为号课程的全部学生的姓名，年龄，出生年份（假定当前为2006年）年）.2.以以name,birthday 为标题，显示学生表中所有学生的姓名，出生年份（假定当前为为标题，显示学生表中所有学生的姓名，出生年份（假定当前为2006年）年）,并按姓名降序排列并按姓名降序排列.3.显示选修显示选修1号课程学生的
34、最高分，最低分，平均分，号课程学生的最高分，最低分，平均分， 总分及人数总分及人数 (写一个语句写一个语句)4.显示选课数目大于显示选课数目大于3的学生的学号及选课的学生的学号及选课.5.至少选修了至少选修了3，4号课的学生姓名号课的学生姓名.6.查询和王芳在一个系的学生姓名和年龄查询和王芳在一个系的学生姓名和年龄.7.查询没选查询没选1号课的学生的姓名号课的学生的姓名.8.由于学员的总学分提高了，而课程还是那么多，现要求把课程的学分在由于学员的总学分提高了，而课程还是那么多，现要求把课程的学分在5分以下加分以下加4，5-8之间之间的课程加的课程加3，8-12之间的课程加之间的课程加2,大于大于12分的课程加分的课程加 1，且要求以后可能还要修改，请设计具，且要求以后可能还要修改，请设计具体语句，选择好形式。体语句，选择好形式。