06 第6章 数据查询.pptx

第1页：第6章数据查询

第6章数据查询

第2页：查询：SELECT语句

查询：SELECT语句

• SQL语句中的SELECT语句用于选择希望从数据库返回到应用程序的数据。

第3页：SELECT语句的主要子句

SELECT语句的主要子句

SELECT语句的主要子句：SELECT [ALL|DISTINCT][TOP n|expression [PERCENT] [WITH TIES]][INTO new_table][FROM {} [,…n]][WHERE ][GROUP BY [ALL] group_by_expression[,…n][WITH {CUBE|ROLLUP }][HAVING ]ORDER BY order_expression [ASC|DESC]]COMPUTE {{AVG|COUNT|MAX|MIN|SUM}(expression)}[,…n]BY expression [,…n]]

第4页：说明：SELECT子句用于指定

说明：SELECT子句用于指定

• SELECT子句用于指定所选择的要查询的特定表中的列，它可以是星号*，表达式、列表、变量等。
• TOP n | expression|percent：表示返回最前面的n行(或百分之n)数据，n表示返回的行数或百分数。
• INTO子句用于指定所要生成的新表的名称。
• FROM子句用于指定要查询的表或视图。最多可以指定16个表或视图。
• WHERE子句用来限定查询的范围和条件。
• GROUP BY子句是分组查询子句。
• HAVING子句用于指定分级子句的条件。
• ASC表示升序排列，DESC表示降序排列。
• COMPUTE子句使用集合函数在查询的结果集中生成汇总行。
• COMPUTE BY子句用于增加各列汇总行。

第5页：CUBE与ROLLUP

CUBE与ROLLUP

CUBE

指定在结果集内不仅包含由GROUP BY提供的行，也包含汇总行。GROUP BY汇总行针对每个可能的组和子组组合在结果集内返回。GROUP BY汇总行在结果集中显示为NULL，用于表示所有值。使用BROUPING函数可以确定结果集内的空值是否为GROUP BY汇总值。

ROLLUP

指定在结果集内不仅包含由GROUP BY提供的行，还包含汇总行。按层次结构顺序，从组内的最低级别到最高级别汇总组。组的层次结构取分组时指定使用的顺序。更改列分级的顺序会影响在结果集内生成的行数。

第6页：SELECT语句的执行过程

SELECT语句的执行过程

• 根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。
• 如果有GROUP BY子句，则将查询结果按照<列名>相同的值进行分组。
• 如果GROUP BY子句后有HAVING短语，则只输出满足HAVING条件的元组。

第7页：基本的SELECT语句

基本的SELECT语句

• 投影查询
• 条件查询

第8页：投影查询

投影查询

• 例1，在数据库teachingData的表StuInfo中查询全体学生的SID（学号）、SNAME（姓名）和SEX（性别）,并将查询结果生成一个新表Stu_tbl。Use teachingDataGOSelect SID,SName,sex into stu_tbl from StuInfo
• 例2，查询表Stu_tbl中所有的学生信息Select * from Stu_tbl

第9页：例3：查询前5条记录

例3：查询前5条记录

例3，查询表StuInfo中的前5条记录中的学生姓名SNAME和专业Major，要求在显示列标题的时候将SNAME和Major分别显示为姓名和专业。Select top 5 sname as姓名, major as专业from StuInfo 如果要查询表StuInfo中的前50%条信息则可以用命令：Select top 50 percent * from StuInfo

第10页：例4：查询scoreInfo

例4：查询scoreInfo

例4 从scoreInfo中查询有哪些课程被选择。Select distinct cid from scoreinfo

第11页：条件查询

条件查询

条件通常通过三部分来描述

列名；比较运算符；列名、常数。常用的比较运算符有：LIKE<>,!=<=>=<>=运算符字符匹配不等于小于等于大于等于小于大于等于含义

LIKE运算符的一般格式

<属性名>LIKE<字符串常量>以上格式中属性名必须为字符型，字符串的字符可以包含如下所示的通配符：表示任意单个字符_（下划线）表示任意长度的字符串%与特定范围或特定集中的任意单字符的匹配[ ]与特定范围或特定集之外的任意单字符的匹配[^]

第12页：通配符使用的例子

通配符使用的例子

Where name like ‘smith_’可以找到所有6个字母的、以smith开头的名字Where name like ‘%smith’可以找到以smith结尾的名字Where name like ‘[JT]im’可以找到3个字母的、以im结尾并以J或T开始的名字Where name like ‘m[^i]%’可以找到以m开始，第二个字符不为‘i’的所有人的名字

第13页：例5：查询姓李的老师

例5：查询姓李的老师

例5， 查询所有姓李的老师的TID和TName。SELECT TID, TName FROM TchInfoWHERE TName LIKE '李%'

第14页：例6：表ScoreInfo中查询

例6：表ScoreInfo中查询

• 在表ScoreInfo中查询选修改课程号为‘00000001’的学生的学号和成绩。SELECT SID AS学号,Score AS成绩FROM ScoreInfoWHERE CID='00000001'

第15页：例7：表ScoreInfo中查询

例7：表ScoreInfo中查询

• 在表ScoreInfo中查询课程号CID为‘00000001’，且成绩低于60分的学生的学号SID和成绩Score。SELECT SID AS学号, Score AS成绩FROM ScoreInfoWHERE CID='00000001' AND Score<60

第16页：例8，between…and…

例8，between…and…

例8，between…and… 查询成绩在70分到80分之间的学生的学号SID和成绩Score。SELECT SID, Score FROM ScoreInfoWHERE Score between 70 AND 80 如果要查询成绩除成绩70分到80分以外的学生的学号SID和成绩Score。SELECT SID, Score FROM ScoreInfoWHERE Score not between 70 AND 80

第17页：例9：查询在所有选课的学生中

例9：查询在所有选课的学生中

例9， 查询在所有选课的学生中，既没有选修课程‘00000001’，也没有选修‘00100002’的学生的学号、课程号和成绩。 方法一：SELECT SID,CID,Score FROM ScoreInfoWHERE CID!= '00000001' AND CID!= '00100002' 方法二：SELECT SID,CID,Score FROM ScoreInfoWHERE CID NOT IN ( '00000001','00100002')

第18页：例10----空值查询

例10----空值查询

• 查询所有已经选课但没有考试成绩的学生的学号sid和相应的课程号cidselect sid, cid from scoreinfowhere score is null
• 注意：这里的空值条件为IS NULL,不能写成SCORE=NULL

第19页：例11----查询的排序

例11----查询的排序

例11 查询的排序 在scoreInfo表中查询选修课程‘00100002’的学生的学号和成绩，并按成绩降序排列。Select sid,score from scoreInfoWhere cid='00100002'Order by score desc

第20页：分组查询

分组查询

• 聚合函数和GROUP BY子句
• GROUP BY和WHERE子句、HAVING子句

第21页：聚合函数和GROUP BY子句

聚合函数和GROUP BY子句

• GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的聚合值。

第22页：常用的聚合函数功

常用的聚合函数功

能函数名称求一列中的最小值MIN求一列中的最大值MAX按列计算值总和SUM按列计算值的平均值AVG按列值计个数COUNT返回表中的所用行数COUNT(*)

第23页：例12：求总分与平均分

例12：求总分与平均分

例12， 通过查询表scoreInfo求学号sid为‘00100001’的学生的总分和平均分。Select sum(score) as总分,avg(score) as平均分From scoreInfo where sid= '05000001‘ 可以用GROUP BY子句查询所有学生的总分和平均分。

第24页：例13：求各科最高最低分

例13：求各科最高最低分

• 通过查询表scoreInfo求各门学科的最高分和最低分。

第25页：例14：查询每位学生的学号及其选课

例14：查询每位学生的学号及其选课

• 查询每位学生的学号及其选课的门数。SELECT SID as学号,COUNT(*) AS选课数FROM ScoreInfoGROUP BY SID
• GROUP BY子句按SID的值分组，所有具有相同SID的元组为一组，对每一组使用函数COUNT进行计算，统计出各位学生选课的门数。

第26页：GROUP BY与HAVING

GROUP BY与HAVING

• 可以在包含GROUP BY子句的查询中使用WHERE子句和HAVING子句。WHERE与HAVING子句的根本区别在于作用对象不同，WHERE子句作用于基本表或视图，从中选择满足条件的元组，HAVING子句作用于组，选择满足条件条件的组，必须用于GROUP BY子句之后，但GROUP BY子句可以没有HAVING子句。HAVING与WHERE语法类似，但HAVING可以包含聚合函数。

第27页：例15：求各专业男生人数

例15：求各专业男生人数

例15， 通过查询表StuInfo求各专业中的男生人数.Select major as专业,count(*) as专业_男生数from StuInfoWhere sex='男'group by major

第28页：例16：从表scoreinfo中查询

例16：从表scoreinfo中查询

• 从表scoreinfo中查询选课人数少于5个学生的课程名及学生数。Select cid,count(*)as学生数from ScoreInfogroup by cidhaving count(*)<5注意：having子句要放在group by子句的后面

第29页：排序查询

排序查询

• ASC表示升序排列，DESC表示降序排列。

第30页：例17：多列排序查询

例17：多列排序查询

例17， 查询表ScoreInfo中选修了“00000001”课程的学生的学号SID，成绩Score和学年Schyear，并按学年Schyear升序、按成绩的降序进行排序。use teachingData select SID, Score, Schyear from ScoreInfowhere cid='00000001'order by Schyear, Score DESC

第31页：联结查询

联结查询

联结查询

关系数据库中最主要的查询，数据表之间的联系是通过表的字段值来体现的，这种字段称为联结字段。

联结操作的目的就

通过加在联结字段的条件将多个表联结起来，以便从多个表中查询数据。联结查询是关系数据库中最主要的查询。联结查询的种类主要有：等值与非等值联结查询、自身联结、外联结、复合条件联结等。

第32页：联结条件的一般格式

联结条件的一般格式

• [[<表名1>.]<列名1><比较运算符> [<表名2>.]<列名2>
  • 其中比较运算符主要有：＝、>、<、>=、<=、!=。
• 当联结运算符为“=”时，称为等值联结，使用其它运算符称为非等值联结。
• 联结条件中的各联结字段类型必须是可比的，但不必是相同的。

第33页：例18：查询每个教师的信息及其主讲课

例18：查询每个教师的信息及其主讲课

例18查询每个教师的信息及其主讲课程的信息。分析：教师情况存放在TchInfo表中，教师主讲课程情况存放在ScoreInfo表中，所以本查询实际上涉及TchInfo、ScoreInfo两个表。这两个表之间的联系是通过公共属性TID实现的。

第34页：用自然联结来实现

用自然联结来实现

SELECT TchInfo.TID, Tname,Sex,Birthday,Title,Dept,CID,SID,Score,Schyear,TermFROM TchInfo,ScoreInfoWHERE TchInfo.TID=ScoreInfo.TID

第35页：复合条件联结

复合条件联结

• 例19，查询每门课程成绩在70分以上的所有学生的姓名、课程号、成绩。SELECT StuInfo.Sname, ScoreInfo.CID,ScoreInfo.ScoreFROM StuInfo,ScoreInfoWHERE StuInfo.SID= ScoreInfo.SID AND ScoreInfo.Score>70

第36页：使用：JOIN和ON的的联结查

使用：JOIN和ON的的联结查

使用JOIN和ON的的联结查询在FROM子句中，使用JOIN联结不同的表，使用ON给出两个表之间的联结条件。例如输入如下命令：SELECT StuInfo.Sname, ScoreInfo.CID,ScoreInfo.ScoreFROM StuInfo JOIN ScoreInfo ON StuInfo.SID= ScoreInfo.SIDWHERE ScoreInfo.Score>70

第37页：自身联结

自身联结

• 联结操作不仅可以在两个表之间进行，也可以是一个表与其自己进行联结，称为表的自身联结。这时一般需要为表指定两个别名。

第38页：例20：相同成绩课程查询

例20：相同成绩课程查询

例20，在ScoreInfo表中，查询具有相同成绩的课程信息，给出学号、课程号和成绩。在查询中，我们需要为表ScoreInfo指定两个别名s1和s2。USE teachingdataSELECT DISTINCT S1.SID,s1.CID,S2.scoreFROM ScoreInfo s1 JOIN ScoreInfo s2 ons1.Score=s2.ScoreWHERE s1.CID<>S2.CID

第39页：查询同时选修了课程‘

查询同时选修了课程‘

查询同时选修了课程‘00000001’和课程‘00100002’的学生的学号。select distinct s1.sidfrom scoreinfo s1 join scoreinfo s2 ons1.sid=s2.sidwhere s1.cid='00000001' ands2.cid='00100002'

第40页：外联结

外联结

• 在通常的联结操作中，只有满足联结条件的元组才能作为结果输出。而采用外联结时，返回到结果集中的不仅包含符合联结条件的行，而且还包括在左表（左外联结）、右表（右外联结）或两个联结表（全外联结）中的所有为空值或者不匹配的数据行。外联结分为左外联结、右外联结和全外联结。

第41页：左外联结

左外联结

• 左外联结的语法格式：数据表1 LEFT OUTER JION数据表2 ON联接表达式
• 或：数据表1 LEFT JION数据表2 ON联接表达式
• 使用左外联结进行查询的结果集将包括数据表1中的所有记录，而不仅仅是联结字段所匹配的记录。
• 如果数据表1的某一条记录在数据表2中没有匹配的记录，那么结果集相应记录中的有关数据2的所有字段将为空值。

第42页：例21：左外连接查询

例21：左外连接查询

• 查询学生信息表StuInfo中的所有记录，并将成绩信息表ScoreInfo中学号字段SID可以匹配的记录输出到结果集。

第43页：左外连接查询示例

左外连接查询示例

SELECT *FROM StuInfo LEFT OUTER JOIN ScoreInfo ONStuInfo.SID= ScoreInfo.SID

第44页：右外联结右外联结

右外联结右外联结

• 或：RIGHT JION数据表2 ON联结表达式
• 使用右外联结进行查询的结果集将包括数据表2中的所有记录，而不仅仅是联结字段所匹配的记录。
• 如果数据表2的某一条记录在数据表1中没有匹配的记录，那么结果集相应记录中的有关数据1的所有字段将为空值。

第45页：例22：右外连接查询

例22：右外连接查询

• 查询成绩信息表ScoreInfo中的所有记录，并将学生信息表StuInfo中学号字段SID可以匹配的记录输出到结果集。

第46页：右外连接查询示例

右外连接查询示例

select *from scoreinforight join tchinfo on scoreinfo.tid=tchinfo.tid

第47页：嵌套查询

嵌套查询

嵌套查询

指在一个外层查询中包含有另一个内层查询，其中，外层查询称为主查询，内层查询称为子查询。

嵌套查询

通过在SELECT语句的WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块来实现的，这个查询块就是子查询或嵌套查询，查询块所在的外层查询就是父查询或外部查询。

第48页：例23：查询选修了00000001号

例23：查询选修了00000001号

例23， 查询选修了00000001号课程的学生姓名。SELECT Sname FROM StuInfo‘外层查询/父查询WHERE SID IN（SELECT SID FROM ScoreInfoWHERE CID= '00000001'）‘内层查询/子查询

第49页：子查询的执行过程分析

子查询的执行过程分析

• 下面简单分析一下这个查询执行的过程：首先是服务器执行小括号中的子查询，返回结果是表ScoreInfo中所有选修了00000001号课程的学生的学号SID；然后服务器执行主查询，返回查询的结果。
• 常见的子查询主要有两类：嵌套子查询和相关子查询。嵌套子查询是指子查询的查询条件不依赖于父查询，相关子查询是指子查询的查询条件依赖于父查询。

第50页：嵌套查询

嵌套查询

嵌套查询的语句格式如下：SELECT [ ALL | DISTINCT ][TOP expression [PERCENT] [WITH TIES ]]< select_list >[FROM { | }[WHERE表达式[NOT] IN|EXISTS|比较运算符[ANY|ALL](子查询)]

第51页：例24：查询与王晓红老师职称相同教师

例24：查询与王晓红老师职称相同教师

例24， 查询与王晓红老师职称相同教师的工号TID、姓名TName和职称Title。USE TeachingDataSELECT TID,TName, title FROM TchInfoWHERE Title=(SELECT Title FROM TchInfoWHERE TName='王晓红')and tname!='王晓红'

第52页：例25：查询同时选修了课程‘0000

例25：查询同时选修了课程‘0000

例25， 查询同时选修了课程‘00000001’和课程‘00100002’的学生的姓名和班级。select sname,class from stuinfowhere sid in(select distinct s1.sid from scoreinfo s1join scoreinfo s2 on s1.sid=s2.sidwhere s1.cid='00000001' and s2.cid='00100002')注意等号和in的区别

第53页：例26：查询同时选修了课程‘高等数学’

例26：查询同时选修了课程‘高等数学’

例26，查询同时选修了课程‘高等数学’和课程‘电子商务’的学生的姓名和班级。select sid,snamefrom stuinfo where sid in (select distinct s1.sidfrom scoreinfo s1 join scoreinfo s2 on s1.sid=s2.sidwhere s1.cid=(select cid from courseinfo wherecname='高等数学') and s2.cid=(select cid fromcourseinfo where cname='电子商务'))

第54页：例27：查询没有上任何课程的教师的姓

例27：查询没有上任何课程的教师的姓

例27， 查询没有上任何课程的教师的姓名和所在部门。select sname,deptfrom tchinfowheretid not in (selecttid from scoreinfo)

第55页：小结：投影查询

小结：投影查询

• 投影查询（纵向选择）
• 条件查询（横向选择）
  • Where+条件（字段+like/in/between…and…/比较运算符）
• 排序查询（ORDER BY）
• 分组查询（GROUP BY）注意where子句和having子句的区别
• 联结查询
  • 多个表中数据的复合条件
  • JOIN(内联结、外联结)

第56页：作业：完成第6章所有例题

作业：完成第6章所有例题

• 完成第6章所有例题