数据库语言SQL——数据查询课件.ppt

第第4 4章章 数据查询数据查询4.1 基本查询基本查询4.2 嵌套查询嵌套查询4.3 连接查询连接查询.在在“学生学籍数据库学生学籍数据库”中创建了以下三个关系模式：中创建了以下三个关系模式：学生信息表（学号，姓名，学生信息表（学号，姓名，性别，年龄，系别）性别，年龄，系别）课程信息表课程信息表(课程号，课程名，课程号，课程名，学分学分)选修信息表（学号，课程号，成绩）选修信息表（学号，课程号，成绩）SQLSQL数据查询语句是数据查询语句是SELECTSELECT语句语句语法：语法：SELECT SELECT FROM FROM WHERE WHERE GROUP BY GROUP BY HAVING HAVING ORDER BY ORDER BY vSELECT语句说明 SELECT语句中必须有：SELECT子句、FROM子句其余子句可选：WHERE子句、GROUP BY子句、HAVING子句、ORDER BY子句SELECTSELECT语句说明语句说明 (1)SELECT(1)SELECT ，称为，称为SELECTSELECT子句。用于指定子句。用于指定整个查询结果表中包含的列。假定已经执行完整个查询结果表中包含的列。假定已经执行完FROMFROM、WHEREWHERE、GROUPBYGROUPBY、HAVINGHAVING子句，子句，SELECTSELECT子子句指定的目标列组成表就为整个查询的结果表。句指定的目标列组成表就为整个查询的结果表。(2)FROM(2)FROM ，称为，称为FROMFROM子句。是整个查询子句。是整个查询语句的数据来源，通常称为数据源表。语句的数据来源，通常称为数据源表。单表查询单表查询 单表查询指的是在单表查询指的是在一个源表一个源表中查找所需的数据。中查找所需的数据。下面首先说明下面首先说明单表查询单表查询SELECTSELECT语句中的语句中的各个子句各个子句3.3.2 3.3.2 3.3.2 3.3.2 单表查询单表查询单表查询单表查询1.SELECT子句 （1）查询表中若干列 在在SELECTSELECT子句的子句的 中指定整个查询结果中指定整个查询结果表中出现的若干个列名，各列名之间用逗号分隔。表中出现的若干个列名，各列名之间用逗号分隔。【例3.3.1】查询全体学生的学号与姓名SELECT 学号学号,姓名姓名 FROM Students（2）查询表中所有的列 可以用*来代替表的所有列。【例3.3.2】查询全部课程的基本信息Select *from 表名表名Select *from 课程信息表课程信息表（2 2）选择表中所有列）选择表中所有列 可以用可以用*来代替表的所有列。来代替表的所有列。【例例】查查询询全全体体学学生生的的学学号号、姓姓名名、性性别别、年龄、所在系。年龄、所在系。SELECT*FROM SELECT*FROM 学生信息表学生信息表 （3 3）查询经过计算的值查询经过计算的值例例:查询全体学生的姓名及其出生年份。查询全体学生的姓名及其出生年份。SELECT姓名，2011-年龄FROM学生信息表学生信息表输出结果：（4）设置列的别名 所所谓谓别别名名，就就是是给给另另一一个个名名字字，主主要要是是为为了了方方便便阅阅读读。设置列别名的方法有：设置列别名的方法有：原列名 ASAS 列别名【例例】查询全体学生的学号、姓名，并为原来的中文列名设查询全体学生的学号、姓名，并为原来的中文列名设置英文别名：置英文别名：Select 学号学号 as sno,姓名姓名 as sname,年龄年龄 as sage from 学生信息表学生信息表例例:查询全体学生的姓名及其出生年份。查询全体学生的姓名及其出生年份。输出结果：SELECT 姓名姓名,2010-年龄年龄 as 出生年份出生年份FROM 学生信息表学生信息表（4）使用DISTINCT消除结果表中完全重复的行【例】显示所有选课学生的学号，并去掉重复行。解：解：SELECT DISTINCT SELECT DISTINCT 学号学号 FROM FROM 选修信息表选修信息表 与DISTINCT相反的是ALL，ALL表示保留结果表中的重复行。默认情况下是ALL，表示保留重复行。练习练习1 1：1、查询全体课程的课程号、课程名，并为其设置别名CNO,CNAME。3、查询选修了课程的学生的学号。（去掉结果中重复的行）3.WHERE3.WHERE3.WHERE3.WHERE子句子句子句子句 WHERE 中常用的运算符：比较运算符和逻辑运算符。中常用的运算符：比较运算符和逻辑运算符。比较运算符比较运算符用于比较两个数值之间的大小是否相等。用于比较两个数值之间的大小是否相等。常用的比较运算符有：常用的比较运算符有：=、=、=、!=!=或或、!、!8080练习练习2 2：1、查询“系别”不是“英语”的所有学生的基本信息。2、查询所有年龄在20岁以上的学生姓名、系别及其年龄。（2）基于BETWEENAND的查询基本格式：列名基本格式：列名 BETWEEN BETWEEN 下限值下限值 AND AND 上限值上限值 等价于：列名等价于：列名=下限值下限值 ANDAND列名列名=80 AND =80 AND 成绩成绩=90 =90（3）基于IN的查询 ININ用于测试一个列值是否与常量表中的任何一个值相等。用于测试一个列值是否与常量表中的任何一个值相等。ININ条件表示格式为：列名条件表示格式为：列名 IN(IN(常量常量1,1,常量常量2,2,常量常量n)n)当列值与当列值与ININ中的任一常量值相等时，则条件为中的任一常量值相等时，则条件为TRUETRUE，否则，否则为为FALSEFALSE。【例】查询数学系、计算机系、艺术系学生的学号、姓名。解：解：SELECT SELECT 姓名姓名,学号学号 FROM FROM 学生信息表学生信息表 WHERE WHERE 系系别别 IN IN(数数学学,计计算算机机,艺艺术术)等价于：SELECT SELECT 学号学号,姓名姓名 FROM FROM 学生信息表学生信息表 WHERE WHERE 系别系别 =数学数学 OR OR 系别系别 =计算机计算机 OR OR 系别系别 =艺术艺术（4）基于LIKE的查询LIKELIKE用于测试一个字符串是否与给定的模式匹配。用于测试一个字符串是否与给定的模式匹配。所谓模式是一种特殊的字符串，其中可以包含普通字符所谓模式是一种特殊的字符串，其中可以包含普通字符 ，也可以包含特殊意义的字符，通常叫通配符。，也可以包含特殊意义的字符，通常叫通配符。LIKELIKE运算符的一般形式为：运算符的一般形式为：列名 LIKE 模式串中可包含如下四种通配符：模式串中可包含如下四种通配符：（1 1）_ _：匹配任意一个字符。如：匹配任意一个字符。如 _u_u_表示第二个字符为表示第二个字符为u u，第一、第三个字符为任意字符的字符串。，第一、第三个字符为任意字符的字符串。（2 2）%：匹配多个字符。如：匹配多个字符。如S%S%表示以表示以S S开头的字符串。开头的字符串。（3 3）：匹配：匹配 中的任意一个字符，如中的任意一个字符，如SDJ SDJ 。（4 4）：不匹配：不匹配 中的任意一个字符，如中的任意一个字符，如SDJ SDJ。可以用LIKE来实现模糊查询【例】查查找找姓姓名名的的第第二二个个字字符符是是晓晓并并且且只只有有三三个个字字符符的的学学生生的的学号、姓名。学号、姓名。解：解：SELECT SELECT 学号学号,姓名姓名 FROM FROM 学生信息表学生信息表 WHERE WHERE 姓名姓名 LIKE _ LIKE _晓晓_【例】查找姓名以李开头的所有学生的学号、姓名。解：解：SELECT SELECT 学号学号,姓名姓名 FROM FROM 学生信息表学生信息表 WHERE WHERE 姓名姓名 LIKE LIKE 李李%【例】查找姓名以李、张或王开头的所有学生的学号、姓名。解：解：SELECT SELECT 学号学号,姓名姓名 FROM FROM 学生信息表学生信息表 WHERE WHERE 姓名姓名 LIKE LIKE 李张王李张王%（5）基于NULL空值的查询 空值是尚未确定或不确定的值。判断某列值是否为空值是尚未确定或不确定的值。判断某列值是否为NULLNULL值只值只能使用专门判断空值的子句，语句格式如下：能使用专门判断空值的子句，语句格式如下：判断列值为空的语句格式为：列名判断列值为空的语句格式为：列名 IS NULL IS NULL 判断列值不为空的语句格式为：列名判断列值不为空的语句格式为：列名 IS NOT NULLIS NOT NULL【例】查询无考试成绩的学生的学号和相应的课程号。解：解：SELECT SELECT 学号学号,课程号课程号 FROM FROM 选修信息表选修信息表 WHERE WHERE 成绩成绩 IS NULL IS NULL 不等价于：不等价于：SELECTSELECT学号学号,课程号课程号 FROM FROM 选修信息表选修信息表 WHERE WHERE 成绩成绩=0=0（6）基于多个条件的查询 可以使用可以使用ANDAND、OROR逻辑谓词来连接多个条件，构成一个逻辑谓词来连接多个条件，构成一个复杂的查询条件。复杂的查询条件。使用格式为：使用格式为：AND AND AND2 AND n 或或 OR OR OR2 OR n【例】查询计算机系年龄在18岁以上的学生学号、姓名 解：解：SELECT SELECT 学号学号,姓名姓名 FROM FROM 学生信息表学生信息表 WHERE WHERE 系别系别=计算机计算机 AND AND 年龄年龄1818【例】求选修了C1课程或C2课程的学生学号、成绩。解：解：SELECT SELECT 学号学号,成绩成绩 FROM FROM 选修信息表选修信息表 WHERE WHERE 课程号课程号=C1 OR=C1 OR 课程号课程号=C2=C2练习练习3 3：1、查询既不是艺术系、数学系，也不是旅游系的学生的姓名和性别。2、查询所有不姓张学生的姓名、学号、和性别。3、查询年龄在1719岁（包括17岁和19岁）之间的学生的姓名、系别和年龄。4、查询选修1号或2号课程的学生的学号、课程号、成绩。对查询结果排序对查询结果排序 使用使用ORDER BY子句子句 可以按一个或多个属性列排序可以按一个或多个属性列排序 升序：升序：ASC；降序：降序：DESC；默认值为升序默认值为升序注：DESC 是descend 降序意思;ASC 是ascend 升序的意思 对查询结果排序（续）对查询结果排序（续）按一个属性列按一个属性列排序排序查查询询选选修修了了3号号课课程程的的学学生生的的学学号号及及其其成成绩，查询结果按分数降序排列。绩，查询结果按分数降序排列。SELECT 学号学号,成绩成绩 FROM 选修信息表选修信息表 WHERE 课程号课程号=3 ORDER BY 成绩成绩 DESC对查询结果排序（续）对查询结果排序（续）按多个属性列按多个属性列排序排序查查询询全全体体学学生生情情况况，查查询询结结果果按按所所在在系系的的系系号号升升序序排排列列，同同一一系系中中的的学学生生按按年年龄降序排列。龄降序排列。SELECT *FROM 学生信息表学生信息表 ORDER BY 系别系别,年龄年龄 DESC练习练习4 4查询选修信息表的基本信息查询选修信息表的基本信息,结果按学结果按学号升序排列号升序排列,同一学号按课程号降序排列同一学号按课程号降序排列.查询学生信息表的学号、姓名、年龄，查询学生信息表的学号、姓名、年龄，结果按年龄升序排列，同一年龄学生按结果按年龄升序排列，同一年龄学生按学号升序排列。学号升序排列。（7）使用统计函数的查询 统计函数统计函数统计函数统计函数也称为也称为也称为也称为集合函数集合函数集合函数集合函数或或或或聚集函数聚集函数聚集函数聚集函数，其作用是对一组，其作用是对一组，其作用是对一组，其作用是对一组值进行计算并返回一个值。值进行计算并返回一个值。值进行计算并返回一个值。值进行计算并返回一个值。函数函数表达式表达式MAX（列名）（列名）求列中的最大值求列中的最大值MIN（列名）（列名）求列中的最小值求列中的最小值AVG（列名）（列名）求一列数据的平均值求一列数据的平均值SUM（列名）（列名）求一列数据的和求一列数据的和COUNT（*）统计查询的行数统计查询的行数常用函数常用函数【例例】求选修了求选修了1号号课程的学生的平均成绩。课程的学生的平均成绩。SELECT AVG(SELECT AVG(成绩成绩)AS)AS 平均成绩平均成绩 FROM FROM 选修信息表选修信息表 WHERE WHERE 课程号课程号=1=1【例例】选修了选修了1号号课程的学生的最高分课程的学生的最高分 SELECT MAX(SELECT MAX(成绩成绩)AS )AS 最高分最高分 FROM FROM 选修信息表选修信息表 WHERE WHERE 课程号课程号=1=1【例例】求学生的总人数求学生的总人数 SELECT COUNT(SELECT COUNT(*)AS )AS 学生的总人数学生的总人数 FROM FROM 学生信息表学生信息表等价于：等价于：SELECT COUNT(SELECT COUNT(学号学号)AS )AS 学生的总人数学生的总人数 FROM FROM 学生信息表学生信息表【例例】查询选修了课程的学生人数。查询选修了课程的学生人数。SELECT COUNT(DISTINCT 学号学号)FROM 选修信息表选修信息表注：注：用用DISTINCT以避免重复计算学生人数以避免重复计算学生人数4.GROUP BY 4.GROUP BY 4.GROUP BY 4.GROUP BY 子句子句子句子句 有时我们需要把有时我们需要把FROMFROM、WHEREWHERE子句产生的表按某种原则分子句产生的表按某种原则分成成若若干干组组，然然后后再再对对每每个个组组进进行行统统计计，一一组组形形成成一一行行，最最后把所有这些行组成一个表后把所有这些行组成一个表GROUP BYGROUP BY子句在子句在WHEREWHERE子句后边。子句后边。一般形式为：一般形式为：GROUP BY ，n 【例例】求选修每门课程的学生人数。求选修每门课程的学生人数。解：解：SELECT SELECT 课程号课程号,COUNT(,COUNT(学号学号)AS)AS 选修人数选修人数 FROM FROM 选修信息表选修信息表 GROUP BY GROUP BY 课程号课程号 注意：1 1、selectselect 字字句句中中如如果果有有统统计计函函数数，则则不不允允许许出出现现其其他他的的列列名名，除非是除非是group by group by 字句的字句的列名列名总总之之：包包含含GROUP GROUP BYBY子子句句的的查查询询语语句句中中，SELECTSELECT子子句句的的列列名名，要么是要么是统计函数统计函数，要么是包含在，要么是包含在GROUP BYGROUP BY子句中的子句中的列名列名下列语句是错误的：Select学号，avg（成绩）from选修信息表SELECT 学号,课程号,COUNT(学号)AS 选修人数 FROM 选修信息表 GROUP BY 课程号5.HAVING5.HAVING5.HAVING5.HAVING子句子句子句子句HAVINGHAVING子句指定子句指定GROUP BYGROUP BY生成的组表的选择条件。生成的组表的选择条件。它的一般形式为：它的一般形式为：HAVING HAVING HAVINGHAVING子句必须与子句必须与GROUP BYGROUP BY子句一起使用。子句一起使用。WHERE 子句子句用来用来筛选筛选 FROM 子句中指定的操作所子句中指定的操作所产生的产生的行行。GROUP BY 子句子句用来用来分组分组 WHERE 子句的输出。子句的输出。HAVING 子句子句用来指定用来指定选择组选择组的条件，满足条件的的条件，满足条件的组组被选。被选。【例】查询选修课程大于等于选修课程大于等于2门课门课的学生的学号、平均成绩、选修的门数。解：解：SELECT 学号学号,AVG(成绩成绩)AS 平均成绩平均成绩,COUNT(*)AS 选修门数选修门数 FROM 选修信息表选修信息表 GROUP BY 学号学号 HAVING COUNT(*)=2【例】查询学生的学号、平均成绩、选修的门数。解：解：SELECT 学号学号,AVG(成绩成绩)AS 平均成绩平均成绩,COUNT(*)AS 选修门数选修门数 FROM 选修信息表选修信息表 GROUP BY 学号学号 注意：注意：where子句作用于子句作用于基本表基本表，从中选出满，从中选出满足条件的行，足条件的行，having字句作用于字句作用于组组，从中，从中选出满足条件的组选出满足条件的组例题例题查询有查询有3门以上课程门以上课程是是90分以上分以上的的学生的学号及（学生的学号及（90分以上的）分以上的）课程数课程数 SELECT 学号学号,COUNT(课程号课程号)FROM 选修信息表选修信息表 WHERE 成绩成绩=90 GROUP BY 学号学号 HAVING COUNT(课程号课程号)=3练习练习5 5：1.列出选修人数小于列出选修人数小于5的课程号和实际选的课程号和实际选修人数修人数2.平均成绩大于平均成绩大于80的课程号和实际平均成的课程号和实际平均成绩绩综合练习综合练习1.查询学生信息表的基本信息查询学生信息表的基本信息,结果按年结果按年龄降序排列龄降序排列,同一年龄按学号升序排列同一年龄按学号升序排列.2.统计每位学生的选课情况，列出学号及统计每位学生的选课情况，列出学号及其选课的门数其选课的门数3.查询选修了最高分大于查询选修了最高分大于87的学生的学号的学生的学号及其最高分及其最高分