数据查询教学课件市公开课一等奖百校联赛特等奖课件.pptx

3.4 数据查询 一、概述二、单表查询三、连接查询四、嵌套查询五、集合查询六、小结 An Introduction to Database System第1页一、概述 语句格式SELECT ALL|DISTINCT ，FROM，WHERE GROUP BY HAVING ORDER BY ASC|DESC ；An Introduction to Database System第2页语句格式说明：SELECT子句：指定要显示属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件 GROUP BY子句：对查询结果按指定列值分组，该属性列值相等元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出只有满足指定条件组ORDER BY子句：对查询结果表按指定列值升序或降序排序 An Introduction to Database System第3页3.4 查 询 一、概述二、单表查询三、连接查询四、嵌套查询五、集合查询六、小结 An Introduction to Database System第4页二、单表查询 1.选择表中若干列2.选择表中若干元组3.对查询结果排序4.使用集函数5.对查询结果分组 查询仅包括一个表最简单查询操作An Introduction to Database System第5页1、查询指定列例1 查询全体学生学号与姓名。例2 查询全体学生姓名、学号、所在系。SELECTSno，SnameFROMStudent；SELECTSname，Sno，SdeptFROMStudent；An Introduction to Database System第6页2、查询全部列SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent；例3 查询全体学生详细统计。SELECT*FROMStudent；或An Introduction to Database System第7页3、查询经过计算值 SELECT子句为表示式算术表示式字符串常量函数列别名等 An Introduction to Database System第8页查询经过计算值例4 查询全体学生姓名及其出生年份。SELECTSname，-SageFROMStudent；输出结果：Sname -Sage -李勇 1986 刘晨 1987 王名 1988 张立 1987An Introduction to Database System第9页查询经过计算值例5 查询全体学生姓名、出生年份和所在系，要求用小写字母表示全部系名。SELECTSname,YearofBirth:,-Sage，LOWER(Sdept)FROMStudent；An Introduction to Database System第10页例题（续）输出结果：Sname Year of Birth:-Sage LOWER(Sdept)-李勇 Year of Birth:1986 cs 刘晨 Year of Birth:1987 is 王名 Year of Birth:1988 ma 张立 Year of Birth:1987 is An Introduction to Database System第11页例5.1 使用列别名改变查询结果列标题输出结果：NAME BIRTH BIRTHDAY DEPARTMENT-李勇 Year of Birth:1986 cs 刘晨 Year of Birth:1987 is 王名 Year of Birth:1988 ma 张立 Year of Birth:1987 isSELECTSnameNAME,YearofBirth:BIRTH,-SageBIRTHDAY,LOWER(Sdept)DEPARTMENTFROMStudent;An Introduction to Database System第12页二、选择表中若干元组 消除取值重复行查询满足条件元组 An Introduction to Database System第13页1.消除取值重复行在SELECT子句中使用DISTINCT短语假设SC表中有以下数据 Sno Cno Grade -15121 1 92 15121 2 85 15121 3 88 15122 2 90 15122 3 80An Introduction to Database System第14页ALL 与 DISTINCT 例6 查询选修了课程学生学号。(1)SELECT Sno FROM SC;或(默认 ALL)SELECT ALL Sno FROM SC;结果：Sno -95001 95001 95001 95002 95002 An Introduction to Database System第15页例题（续）(2)SELECT DISTINCT Sno FROM SC;结果：Sno -95001 95002 注意 DISTINCT短语作用范围是全部目标列An Introduction to Database System第16页2.查询满足条件元组WHERE子句惯用查询条件子句惯用查询条件(1)比较大小(2)确定范围(3)确定集合(4)字符串匹配(5)包括空值查询(6)多重条件查询An Introduction to Database System第17页(1)比较大小在WHERE子句中使用比较运算符=，=，=，!=或，!，!，逻辑运算符NOT +比较运算符例8 查询全部年纪在20岁以下学生姓名及其年纪。SELECT Sname，Sage FROM Student WHERE Sage=20；An Introduction to Database System第18页(2)确定范围使用谓词 BETWEEN AND NOT BETWEEN AND 例10 查询年纪在2023岁（包含20岁和23岁）之间学生姓名、系别和年纪。SELECT Sname，Sdept，SageFROM StudentWHERE Sage BETWEEN 20 AND 23；An Introduction to Database System第19页例题（续）例11 查询年纪不在2023岁之间学生姓名、系别和年纪。SELECT Sname，Sdept，SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23；An Introduction to Database System第20页(3)确定集合使用谓词 IN（值表）,NOT IN（值表）（值表）：用逗号分隔一组取值例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生姓名和性别。SELECT Sname，SsexFROM StudentWHERE Sdept IN(IS，MA，CS);An Introduction to Database System第21页(3)确定集合例13查询既不是信息系、数学系，也不是计算机科学系学生姓名和性别。SELECT Sname，SsexFROM Student WHERE Sdept NOT IN(IS，MA，CS);An Introduction to Database System第22页(4)字符串匹配 NOT LIKE ESCAPE ：指定匹配模板 匹配模板：固定字符串或含通配符字符串 当匹配模板为固定字符串时，能够用=运算符取代 LIKE 谓词 用!=或 运算符取代 NOT LIKE 谓词查找指定属性列值与相匹配元组An Introduction to Database System第23页通配符%(百分号)代表任意长度（长度可认为0）字符串例：a%b表示以a开头，以b结尾任意长度字符串。如acb，addgb，ab 等都满足该匹配串_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾长度为3任意字符串。如acb，afb等都满足该匹配串An Introduction to Database System第24页ESCAPE 短语：当用户要查询字符串本身就含有%或 _ 时，要使用ESCAPE 短语对通配符进行转义。An Introduction to Database System第25页例题1)匹配模板为固定字符串 例14 查询学号为95001学生详细情况。SELECT*FROM Student WHERE Sno LIKE 95001；等价于：SELECT *FROM Student WHERE Sno=95001；An Introduction to Database System第26页例题（续）2)匹配模板为含通配符字符串例15 查询全部姓刘学生姓名、学号和性别。SELECT Sname，Sno，SsexFROM StudentWHERE Sname LIKE 刘%；An Introduction to Database System第27页例题（续）匹配模板为含通配符字符串（续）例16 查询姓欧阳且全名为三个汉字学生姓名。SELECT Sname FROM Student WHERE Sname LIKE 欧阳_ _；An Introduction to Database System第28页例题（续）匹配模板为含通配符字符串（续）例17 查询名字中第2个字为阳字学生姓名和学号。SELECT Sname，Sno FROM Student WHERE Sname LIKE _ _阳%；An Introduction to Database System第29页例题（续）匹配模板为含通配符字符串（续）例18 查询全部不姓刘学生姓名。SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE 刘%；An Introduction to Database System第30页例题（续）3)使用换码字符将通配符转义为普通字符 例19 查询DB_Design课程课程号和学分。SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE An Introduction to Database System第31页例题（续）使用换码字符将通配符转义为普通字符(续)例20 查询以DB_开头，且倒数第3个字符为 i课程详细情况。SELECT *FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ；An Introduction to Database System第32页(5)包括空值查询 使用谓词 IS NULL 或 IS NOT NULL“IS NULL”不能用“=NULL”代替例21 一些学生选修课程后没有参加考试，所以有选课统计，但没有考试成绩。查询缺乏成绩学生学号和对应课程号。SELECT Sno，Cno FROM SC WHERE Grade IS NULL；An Introduction to Database System第33页例题(续)例22 查询全部有成绩学生学号和课程号。SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；An Introduction to Database System第34页(6)多重条件查询用逻辑运算符AND和 OR来联结多个查询条件 AND优先级高于OR 能够用括号改变优先级An Introduction to Database System第35页例题例23 查询计算机系年纪在20岁以下学生姓名。SELECT Sname FROM Student WHERE Sdept=CS AND Sage=20 AND Sage=23；An Introduction to Database System第38页三、对查询结果排序 使用ORDER BY子句 能够按一个或多个属性列排序 升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值元组最终显示DESC：排序列为空值元组最先显示 An Introduction to Database System第39页对查询结果排序（续）例24 查询选修了3号课程学生学号及其成绩，查询结果按分数降序排列。SELECT Sno，Grade FROM SC WHERE Cno=3 ORDER BY Grade DESC；An Introduction to Database System第40页查询结果 Sno Grade -95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55An Introduction to Database System第41页对查询结果排序（续）例25 查询全体学生情况，查询结果按所在系系号升序排列，同一系中学生按年纪降序排列。SELECT *FROM StudentORDER BY Sdept，Sage DESC；An Introduction to Database System第42页四、使用集函数 5类主要集函数计数COUNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）计算总和SUM（DISTINCT|ALL）计算平均值AVG（DISTINCT|ALL）An Introduction to Database System第43页使用集函数（续）求最大值MAX（DISTINCT|ALL）求最小值MIN（DISTINCT|ALL）DISTINCT短语：在计算时要取消指定列中重复值ALL短语：不取消重复值ALL为缺省值An Introduction to Database System第44页使用集函数（续）例26 查询学生总人数。SELECT COUNT(*)FROM Student；例27 查询选修了课程学生人数。SELECT COUNT(DISTINCT Sno)FROM SC；注：用DISTINCT以防止重复计算学生人数An Introduction to Database System第45页使用集函数（续）例28 计算1号课程学生平均成绩。SELECT AVG(Grade)FROM SC WHERE Cno=1；例29 查询选修1号课程学生最高分数。SELECT MAX(Grade)FROM SC WHER Cno=1；An Introduction to Database System第46页五、对查询结果分组 使用GROUP BY子句分组 细化集函数作用对象 未对查询结果分组，集函数将作用于整个查询结果 对查询结果分组后，集函数将分别作用于每个组 An Introduction to Database System第47页使用GROUP BY子句分组例30 求各个课程号及对应选课人数。SELECT Cno，COUNT(Sno)FROM SC GROUP BY Cno；结果 Cno COUNT(Sno)1 22 2 34 3 44 4 33 5 48An Introduction to Database System第48页对查询结果分组（续）GROUP BY子句作用对象是查询中间结果表分组方法：按指定一列或多列值分组，值相等为一组使用GROUP BY子句后，SELECT子句列名列表中只能出现分组属性和集函数 An Introduction to Database System第49页使用HAVING短语筛选最终输出结果例31 查询选修了3门以上课程学生学号。SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)3；An Introduction to Database System第50页例题例32 查询有3门以上课程是90分以上 学生学号及（90分以上）课程数 SELECT Sno,COUNT(*)FROM SC WHERE Grade90 GROUP BY Sno HAVING COUNT(*)3;An Introduction to Database System第51页使用HAVING短语筛选最终输出结果只有满足HAVING短语指定条件组才输出HAVING短语与WHERE子句区分：作用对象不一样WHERE子句作用于基表或视图，从中选择满足条件元组。HAVING短语作用于组，从中选择满足条件组。An Introduction to Database System第52页 下课了。休息一会儿。休息一会儿。An Introduction to Database System第53页