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

1、.4.1 基本查询基本查询4.2 嵌套查询嵌套查询4.3 连接查询连接查询 在在“学生学籍数据库学生学籍数据库”中创建了以下三个关系模式：中创建了以下三个关系模式：学生信息表（学号，姓名，学生信息表（学号，姓名，性别，年龄，系别）性别，年龄，系别）课程信息表课程信息表(课程号，课程名，课程号，课程名，学分学分)选修信息表（学号，课程号，成绩）选修信息表（学号，课程号，成绩）SELECT SELECT FROM FROM WHERE WHERE GROUP BY GROUP BY HAVING HAVING ORDER BY ORDER BY vSELECT语句说明 SELECT语句中必须有：S

2、ELECT子句、FROM子句其余子句可选：WHERE子句、GROUP BY子句、HAVING子句、ORDER BY子句SELECTSELECT语句说明语句说明 (1)SELECT(1)SELECT ，称为，称为SELECTSELECT子句。用于指定子句。用于指定整个查询结果表中包含的列。假定已经执行完整个查询结果表中包含的列。假定已经执行完FROMFROM、WHEREWHERE、GROUPBYGROUPBY、HAVINGHAVING子句，子句，SELECTSELECT子子句指定的目标列组成表就为整个查询的结果表。句指定的目标列组成表就为整个查询的结果表。(2)FROM(2)FROM ，称为，称

3、为FROMFROM子句。是整个查询子句。是整个查询语句的数据来源，通常称为数据源表。语句的数据来源，通常称为数据源表。单表查询单表查询 单表查询指的是在单表查询指的是在一个源表一个源表中查找所需的数据。中查找所需的数据。下面首先说明下面首先说明单表查询单表查询SELECTSELECT语句中的语句中的各个子句各个子句1.SELECT子句（1）查询表中若干列 在在SELECTSELECT子句的子句的 中指定整个查询结果中指定整个查询结果表中出现的若干个列名，各列名之间用逗号分隔。表中出现的若干个列名，各列名之间用逗号分隔。【例3.3.1】查询全体学生的学号与姓名 SELECT 学号学号,姓名姓名

4、FROM Students（2）查询表中所有的列 可以用*来代替表的所有列。【例3.3.2】查询全部课程的基本信息Select *from 表名表名Select *from 课程信息表课程信息表（2 2）选择表中所有列）选择表中所有列 可以用可以用*来代替表的所有列。来代替表的所有列。【例例】查询全体学生的学号、姓名、性别、查询全体学生的学号、姓名、性别、年龄、所在系。年龄、所在系。SELECT SELECT*FROM FROM 学生信息表学生信息表例例:查询全体学生的姓名及其出生年份。查询全体学生的姓名及其出生年份。SELECT 姓名，2011-年龄FROM 学生信息表学生信息表 输出结果：

5、（4）设置列的别名 所谓别名，就是给另一个名字，主要是为了方便阅读。所谓别名，就是给另一个名字，主要是为了方便阅读。设置列别名的方法有：设置列别名的方法有：原列名 ASAS 列别名【例例】查询全体学生的学号、姓名，并为原来的中文列名设查询全体学生的学号、姓名，并为原来的中文列名设置英文别名：置英文别名：Select 学号学号 as sno,姓名姓名 as sname,年龄年龄 as sage from 学生信息表学生信息表例例:查询全体学生的姓名及其出生年份。查询全体学生的姓名及其出生年份。输出结果：SELECT 姓名姓名,2010-年龄年龄 as 出生年份出生年份FROM 学生信息表学生信息

6、表（4）使用DISTINCT消除结果表中完全重复的行【例】显示所有选课学生的学号，并去掉重复行。解：解：SELECT DISTINCT SELECT DISTINCT 学号学号 FROM FROM 选修信息表选修信息表 与DISTINCT相反的是ALL，ALL表示保留结果表中的重复行。默认情况下是ALL，表示保留重复行。1、查询全体课程的课程号、课程名，并为其设置别名CNO,CNAME。3、查询选修了课程的学生的学号。（去掉结果中重复的行）WHERE 中常用的运算符：比较运算符和逻辑运算符。中常用的运算符：比较运算符和逻辑运算符。比较运算符比较运算符用于比较两个数值之间的大小是否相等。用于比较

7、两个数值之间的大小是否相等。常用的比较运算符有：常用的比较运算符有：=、=、=、!=!=或或、!、!80801、查询“系别”不是“英语”的所有学生的基本信息。2、查询所有年龄在20岁以上的学生姓名、系别及其年龄。基本格式：列名基本格式：列名 BETWEEN BETWEEN 下限值下限值 AND AND 上限值上限值 等价于：列名等价于：列名=下限值下限值 ANDAND列名列名=80 AND =80 AND 成绩成绩=90 =90（3）基于IN的查询 ININ用于测试一个列值是否与常量表中的任何一个值相等。用于测试一个列值是否与常量表中的任何一个值相等。ININ条件表示格式为：列名条件表示格式为

8、：列名 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 系别系别

9、 =计算机计算机 OR OR 系别系别 =艺术艺术（4）基于LIKE的查询LIKELIKE用于测试一个字符串是否与给定的模式匹配。用于测试一个字符串是否与给定的模式匹配。所谓模式是一种特殊的字符串，其中可以包含普通字符所谓模式是一种特殊的字符串，其中可以包含普通字符 ，也可以包含特殊意义的字符，通常叫通配符。，也可以包含特殊意义的字符，通常叫通配符。LIKELIKE运算符的一般形式为：运算符的一般形式为：列名 LIKE 模式串中可包含如下四种通配符：模式串中可包含如下四种通配符：（1 1）_ _：匹配任意一个字符。如：匹配任意一个字符。如 _u_u_表示第二个字符为表示第二个字符为u u，第一

10、、第三个字符为任意字符的字符串。第一、第三个字符为任意字符的字符串。（2 2）%：匹配多个字符。如：匹配多个字符。如S%S%表示以表示以S S开头的字符串。开头的字符串。（3 3）：匹配：匹配 中的任意一个字符，如中的任意一个字符，如SDJ SDJ 。（4 4）：不匹配：不匹配 中的任意一个字符，如中的任意一个字符，如SDJ SDJ。可以用LIKE来实现模糊查询【例】查找姓名的第二个字符是查找姓名的第二个字符是晓晓并且只有三个字符的学生的并且只有三个字符的学生的学号、姓名。学号、姓名。解：解：SELECT SELECT 学号学号,姓名姓名 FROM FROM 学生信息表学生信息表 WHERE

11、WHERE 姓名姓名 LIKE _ LIKE _晓晓_【例】查找姓名以李开头的所有学生的学号、姓名。解：解：SELECT SELECT 学号学号,姓名姓名 FROM FROM 学生信息表学生信息表 WHERE WHERE 姓名姓名 LIKE LIKE 李李%【例】查找姓名以李、张或王开头的所有学生的学号、姓名。解：解：SELECT SELECT 学号学号,姓名姓名 FROM FROM 学生信息表学生信息表 WHERE WHERE 姓名姓名 LIKE LIKE 李张王李张王%（5）基于NULL空值的查询 空值是尚未确定或不确定的值。判断某列值是否为空值是尚未确定或不确定的值。判断某列值是否为NU

12、LLNULL值只值只能使用专门判断空值的子句，语句格式如下：能使用专门判断空值的子句，语句格式如下：判断列值为空的语句格式为：列名判断列值为空的语句格式为：列名 IS NULL IS NULL 判断列值不为空的语句格式为：列名判断列值不为空的语句格式为：列名 IS NOT NULLIS NOT NULL【例】查询无考试成绩的学生的学号和相应的课程号。解：解：SELECT SELECT 学号学号,课程号课程号 FROM FROM 选修信息表选修信息表 WHERE WHERE 成绩成绩 IS NULL IS NULL 不等价于：不等价于：SELECTSELECT学号学号,课程号课程号 FROM F

13、ROM 选修信息表选修信息表 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课程的学生学号、

14、成绩。解：解：SELECT SELECT 学号学号,成绩成绩 FROM FROM 选修信息表选修信息表 WHERE WHERE 课程号课程号=C1 OR=C1 OR 课程号课程号=C2=C21、查询既不是艺术系、数学系，也不是旅游系的学生的 姓名和 性别。2、查询所有不姓张学生的姓名、学号、和性别。3、查询年龄在1719岁（包括17岁和19岁）之间的学生的姓名、系别和年龄。4、查询选修1号或2号课程的学生的学号、课程号、成绩。使用使用ORDER BY子句子句 可以按一个或多个属性列排序可以按一个或多个属性列排序 升序：升序：ASC；降序：降序：DESC；默认值为升序默认值为升序注：DESC 是

15、descend 降序意思;ASC 是ascend 升序的意思 查询选修了查询选修了3号课程的学生的学号及其成号课程的学生的学号及其成绩，查询结果按分数降序排列。绩，查询结果按分数降序排列。SELECT 学号学号,成绩成绩 FROM 选修信息表选修信息表 WHERE 课程号课程号=3 ORDER BY 成绩成绩 DESC查询全体学生情况，查询结果按所在系查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年的系号升序排列，同一系中的学生按年龄降序排列。龄降序排列。SELECT *FROM 学生信息表学生信息表 ORDER BY 系别系别,年龄年龄 DESC查询选修信息表的基本信息查

16、询选修信息表的基本信息,结果按学结果按学号升序排列号升序排列,同一学号按课程号降序排列同一学号按课程号降序排列.查询学生信息表的学号、姓名、年龄查询学生信息表的学号、姓名、年龄，结果按年龄升序排列，同一年龄学生，结果按年龄升序排列，同一年龄学生按学号升序排列。按学号升序排列。函数函数表达式表达式MAX（列名）（列名）求列中的最大值求列中的最大值MIN（列名）（列名）求列中的最小值求列中的最小值AVG（列名）（列名）求一列数据的平均值求一列数据的平均值SUM（列名）（列名）求一列数据的和求一列数据的和COUNT（*）统计查询的行数统计查询的行数常用函数常用函数【例例】求选修了求选修了1号号课程的

17、学生的平均成绩。课程的学生的平均成绩。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 学生信

18、息表学生信息表等价于：等价于：SELECT COUNT(SELECT COUNT(学号学号)AS )AS 学生的总人数学生的总人数 FROM FROM 学生信息表学生信息表【例例】查询选修了课程的学生人数。查询选修了课程的学生人数。SELECT COUNT(DISTINCT 学号学号)FROM 选修信息表选修信息表注：注：用用DISTINCT以避免重复计算学生人数以避免重复计算学生人数 有时我们需要把有时我们需要把FROMFROM、WHEREWHERE子句产生的表按某种原则分子句产生的表按某种原则分成若干组，然后再对每个组进行统计，成若干组，然后再对每个组进行统计，一组形成一行一组形成一行，最

19、最后把所有这些行组成一个表后把所有这些行组成一个表GROUP BYGROUP BY子句在子句在WHEREWHERE子句后边。子句后边。一般形式为：一般形式为：GROUP BY ，n 【例例】求选修每门课程的学生人数。求选修每门课程的学生人数。解：解：SELECT SELECT 课程号课程号,COUNT(,COUNT(学号学号)AS)AS 选修人数选修人数 FROM FROM 选修信息表选修信息表 GROUP BY GROUP BY 课程号课程号 注意：1 1、selectselect 字句中如果有字句中如果有统计函数统计函数，则不允许出现其他的列名，则不允许出现其他的列名，除非是，除非是gro

20、up by group by 字句的字句的列名列名总之：包含总之：包含GROUP BYGROUP BY子句的查询语句中，子句的查询语句中，SELECTSELECT子句子句的列名的列名，要么是，要么是统计函数统计函数，要么是包含在，要么是包含在GROUP BYGROUP BY子句中的子句中的列名列名下列语句是错误的：Select 学号，avg（成绩）from 选修信息表SELECT 学号,课程号,COUNT(学号)AS 选修人数 FROM 选修信息表 GROUP BY 课程号HAVINGHAVING子句指定子句指定GROUP BYGROUP BY生成的组表的选择条件。生成的组表的选择条件。它的一

21、般形式为：它的一般形式为：HAVING HAVING HAVINGHAVING子句必须与子句必须与GROUP BYGROUP BY子句一起使用。子句一起使用。WHERE 子句子句用来用来筛选筛选 FROM 子句中指定的操作所子句中指定的操作所产生的产生的行行。GROUP BY 子句子句用来用来分组分组 WHERE 子句的输出。子句的输出。HAVING 子句子句用来指定用来指定选择组选择组的条件，满足条件的的条件，满足条件的组组被选。被选。【例】查询选修课程大于等于选修课程大于等于2门课门课的学生的学号、平均成绩、选修的门数。解：解：SELECT 学号学号,AVG(成绩成绩)AS 平均成绩平均成

22、绩,COUNT(*)AS 选修门数选修门数 FROM 选修信息表选修信息表 GROUP BY 学号学号 HAVING COUNT(*)=2【例】查询学生的学号、平均成绩、选修的门数。解：解：SELECT 学号学号,AVG(成绩成绩)AS 平均成绩平均成绩,COUNT(*)AS 选修门数选修门数 FROM 选修信息表选修信息表 GROUP BY 学号学号 注意：注意：where子句作用于子句作用于基本表基本表，从中选出满，从中选出满足条件的行，足条件的行，having字句作用于字句作用于组组，从中，从中选出满足条件的组选出满足条件的组查询有查询有3门以上课程门以上课程是是90分以上分以上的的学生

23、的学号及（学生的学号及（90分以上的）分以上的）课程数课程数 SELECT 学号学号,COUNT(课程号课程号)FROM 选修信息表选修信息表 WHERE 成绩成绩=90 GROUP BY 学号学号 HAVING COUNT(课程号课程号)=3 列出选修人数小于列出选修人数小于5的课程号和实际选的课程号和实际选修人数修人数平均成绩大于平均成绩大于80的课程号和实际平均成的课程号和实际平均成绩绩查询学生信息表的基本信息查询学生信息表的基本信息,结果按年结果按年龄降序排列龄降序排列,同一年龄按学号升序排列同一年龄按学号升序排列.统计每位学生的选课情况，列出学号及统计每位学生的选课情况，列出学号及其选课的门数其选课的门数查询选修了最高分大于查询选修了最高分大于87的学生的学号的学生的学号及其最高分及其最高分