资源简介

(共41张PPT)
第5章 数据查询
目录
◎本章目标
■熟练应用：SELECT语句进行简单查询
■掌握：SELECT语句进行统计查询的方法
■掌握：子查询
■能够：应用SELECT语句对多表进行连接、联合和嵌套查询
◎本章内容
■5.1 SELECT语句
■5.2 简单SELECT语句
■5.3 SELECT语句的统
计功能
■5.4 多表连接查询
■5.5 合并结果集
■5.6 子查询
5.1 SELECT语句
SELECT [ALL|DISTINCT] [TOP n]列表达式
[INTO 新表名]
FROM 表名与视图名列表
[WHERE 逻辑表达式]
[GROUP BY 列名列表]
[HAVING 逻辑表达式 ]
[ORDER BY 列名[ASC|DESC]]
指定查询结果要输出的列
指定要查询的表或视图
指定查询条件
对查询结果集进行分组
对查询结果集进行排序
5.2 简单SELECT语句
5.2.1 基本的SELECT语句
SELECT 选取的列
FROM 表与视图的列表
WHERE 查询条件
■选取的列：
特定列：列名1,列名2…
所有列：*
指定返回列的名称
（1）列名 AS 别名
（2）列名 别名
（3）别名 = 列名
派生列：表达式
消除重复行：
DISTINCT 列名列表
限制返回行的数量：
TOP n [PERCENT] 列名列表
5.2.1 基本的SELECT语句
【例5-1】查询学生表，输出所有学生的详细信息。
SELECT * FROM 学生
【例5-2】查询学生表，输出所有学生的学号和姓名。
SELECT 学号 AS 学生学号，姓名 学生姓名，
查询日期=GetDate() FROM 学生
【例5-4】查询学生表，输出所有学生的学号、姓名、以及查询日期和时间，并分别使用“学生学号”，“学生姓名”，“查询日期”作为别名。
【例5-3】查询学生表，输出所有学生的学号、姓名、以及查询日期和时间，在“查询日期和时间”列前输出常量“查询日期和时间”。
SELECT 学号,姓名 FROM 学生
SELECT 学号,姓名,'查询日期和时间',GetDate() FROM 学生
5.2.1 基本的SELECT语句
【例5-5】查询学生表，输出学生所在的班级，每个班级只输出一次。
SELECT DISTINCT 班级
FROM 学生
【例5-6】查询学生表，输出表中前三名学生的详细信息。
SELECT TOP 3 * FROM 学生
【例5-7】查询学生表，输出表中前30%学生的详细信息。
SELECT TOP 30 PERCENT * FROM 学生
5.2.2 使用INTO子句
使用INTO子句可以把查询结果插入到一个新的表中
SELECT 列表达式
INTO 新表名
FROM 表名与视图名列表
WHERE 查询条件
使用INTO子句，应注意以下几点：
（1）新表是数据库中没有存在的表。
（2）新表中行和列是由查询结果集决定的。
（3）使用该子句时，用户必须有在数据库中建立表的权限。
（4）如果新表的表名以“#”开头，则生成的是一个临时表。
【例5-8】查询学生表，将所有学生的学号和姓名插入到“学生1”表中。
SELECT 学号，姓名 INTO 学生1 FROM 学生
5.2.3 使用WHERE子句
WHERE子句确定查询条件，用来从表与视图中筛选出满足条件的数据行 。
SELECT 列表达式
FROM 表
WHERE 条件表达式
条件表达式中用到的运算符：
5.2.3 使用WHERE子句
1.比较搜索条件
【例5-9】查询学生表，输出“网络技术101”班学生的详细信息。
SELECT * FROM 学生 WHERE 班级='网络技术101'
2.范围搜索条件
【例5-10】查询学生表，输出1992年出生的学生的详细信息。
SELECT * FROM 学生
WHERE 出生日期 BETWEEN '1992-01-01' AND '1992-12-31'
5.2.3 使用WHERE子句
3.列表搜索条件
【例5-11】查询学生表，输出学号为“10101001”、“10102001”、“11101001”的学生的详细信息。
SELECT * FROM 学生
WHERE 学号 IN('10101001','10102001','11101001')
5.2.3 使用WHERE子句
4.模式匹配搜索条件
LIKE运算符用于将指定的列的值与模式字符串进行匹配运算，其语法格式如下:
<列名> [NOT] LIKE <‘模式字符串’> [ESCAPE ‘<换码字符>’]
其中：
◇模式字符串：可以是一般的字符串，也可以是包含有通配符的字符串。通配符的种类如图所示。
◇ ESCAPE：表示将模式字符串中<换码字符>后的通配符进行转义，使其成为普通字符。
【例5-12】查询学生表，输出姓“张”的学生的详细信息。
SELECT * FROM 学生 WHERE 姓名 LIKE '张%'
5.2.3 使用WHERE子句
5．空值判断搜索条件
T-SQL使用IS [NOT] NULL运算符判断指定列的值是否为空值。对于空值判断，不能使用比较运算符或模式匹配运算符。
【例5-13】查询选课表，输出没有成绩的学生的学号和课程号。
SELECT 学号,课程号,成绩 FROM 选课 WHERE 成绩 IS NULL
5.2.3 使用WHERE子句
6．包含逻辑运算符搜索条件
AND：连接两个条件。如果两个条件表达式都成立，那么组合起来的条件成立。
OR：连接两个条件。如果两个条件表达式中任何一个成立，那么组合起来的条件成立。
NOT：连接一个条件表达式，对给定条件的结果取反。
【例5-14】查询学生表，输出姓“王”且是“电子商务111”班的学生的详细信息。
SELECT * FROM 学生
WHERE 姓名 LIKE '王%' AND 班级='电子商务'
【例5-15】查询学生表，输出不是1992年出生的学生的详细信息。
SELECT * FROM 学生 WHERE NOT(YEAR(出生日期)=1992)
5.2.4 使用ORDER BY子句
1.可以使用ORDER BY子句对结果集中的数据行按指定列的值重新排列顺序。可以规定按升序排列（使用参数ASC）或按降序排列（使用参数DESC），默认参数为ASC。
2.可以在ORDER BY子句中指定多个列，查询结果首先按第1列进行排序，第1列值相同的那些数据行，再按照第2列排序。
3.ORDER BY子句要写在WHERE子句后面。
【例5-16】查询选课表，输出修选了课程号为1的学生的学号、课程号和成绩，并将查询结果按成绩从高到底进行排序。
SELECT * FROM 选课 WHERE 课程号=1 ORDER BY 成绩 DESC
5.3 SELECT语句的统计功能
5.3.1 使用集合函数进行数据统计
集合函数用于对查询结果集中的指定列进行统计，并输出统计值。
ALL为默认选项，指计算所有的值；DISTINCT则去掉重复值。
5.3.1 使用集合函数进行数据统计
【例5-17】查询学生表，统计学生总人数。
【例5-18】查询选课表，统计选修了课程号为3的学生人数、总成绩、平均成绩、最高成绩和最低成绩
SELECT COUNT(*) AS 学生人数，SUM(成绩) AS 总成绩,
AVG(成绩) AS 平均成绩,MAX(成绩) AS最高成绩,
MIN(成绩) AS 最低成绩
FROM 选课
WHERE 课程号=3
SELECT COUNT(*) AS 学生总数 FROM 学生
5.3.2 使用GROUP BY子句
GROUP BY子句用于对查询结果集按指定列的值进行分组，列值相同的放在一组。集合函数和GROUP BY子句配合使用，将对查询结果集进行分组统计。
注意：
使用GROUP BY子句进行分组统计时，SELECT子句中的列表达式中所包含的列只能是如下两种情况：
◇列名应用了集合函数。
◇未应用集合函数的列必须包含在GROUP BY子句中。
5.3.2 使用GROUP BY子句
【例5-21】查询选课表，统计输出每门课程的所选学生人数及最高分。
【例5-20】查询选课表，统计输出每个学生所选课程数目及平均分。
SELECT 课程号，COUNT(学号) AS 选课人数，
MAX(成绩) AS 最高分 FROM 选课 GROUP BY 课程号
SELECT 学号，COUNT(课程号) AS 选课数目，AVG(成绩) AS 平均分 FROM 选课 GROUP BY 学号
【例5-19】查询学生表，分别统计男女生人数。
SELECT COUNT(*) AS 男女生人数 FROM 学生
GROUP BY 性别
5.3.2 使用GROUP BY子句
GROUP BY子句常和HAVING子句配合使用。HAVING子句用于对分组按条件进行筛选。HAVING子句只能出现在GROUP BY子句后。
格式：GROUP BY 列名 HAVING 条件表达式
注意：
当一个语句中同时出现了WHERE子句，GROUP BY子句和HAVING子句，SQL的执行顺序如下：
（1）执行WHERE子句，从数据表中选取满足条件的数据行。
（2）由GROUP BY子句对选取的行进行分组。
（3）执行聚合函数。
（4）执行HAVING子句，选取满足条件的分组。
5.3.2 使用GROUP BY子句
【例5-22】查询选课表中至少有4名学号前四位为1010的学生选修的课程的平均分数。
【例5-23】查询选课表中每门课成绩都在70～90之间的学生的学号。
SELECT 学号 FROM 选课
GROUP BY 学号
HAVING MIN(成绩)>70 AND MAX(成绩)<90
SELECT 课程号，AVG(成绩) AS 平均分
FROM 选课 WHERE 学号 LIKE '1010%'
GROUP BY 课程号 HAVING COUNT(*)>=4
5.3.3 使用COMPUTE BY子句
用于同时显示明细数据和统计结果。
语法格式：
COMPUTE 集合函数 [BY 列名]
使用COMPUTE BY子句注意：
◇集合函数中不能使用DISTINCT关键字。
◇COMPUTE子句中指定的属性列必须存在于SELECT子句的列表达式中。
◇COMPUTE…BY子句必须与ORDER BY子句一起使用，并且BY关键字后面指定的列必须与ORDER BY子句中指定的列相同，或为其子集，且列的顺序也必须一致。
5.3.3 使用COMPUTE BY子句
【例5-24】查询选课表，输出学号，课程号，成绩的明细并统计平均成绩，最高分，最低分。
【例5-25】查询选课表，按课程号分组，输出各组的学号，课程号，成绩的明细并统计每门课的上课人数和平均成绩。
SELECT * FROM 选课
COMPUTE AVG(成绩),MAX(成绩),MIN(成绩)
SELECT * FROM 选课
ORDER BY 课程号
COMPUTE COUNT(学号),AVG(成绩) BY 课程号
5.4 多表连接查询
多表连接查询语法格式：
SELECT [表名.]目标列表达式 [AS 别名]，…
FROM 左表名 [AS 别名] 连接类型 右表名 [AS 别名]
ON 连接条件
其中连接类型以及运算符有以下几种：
CROSS JOIN:交叉连接
INNER JOIN或JOIN:内连接
LEFT JOIN或LEFT OUTER JOIN:左外连接
RIGHT JOIN或RIGHT OUTER JOIN:右外连接
FULL JOIN 或FULL OUTER JOIN:完全连接
5.4 多表连接查询（各种连接示意）
内连接
内连接3种类型：
◇等值连接：在连接条件中使用等号（=）比较运算符来比较连接列的列值。结果集中有两个表的所有列，包括重复列。在等值连接中，连接条件通常采用“表1.主键=表2.外键”的形式。
◇非等值连接：在连接条件中使用了除等号之外的比较运算符（>、<、>=、<=、!=）来比较连接列的列值。
◇自然连接：与等值连接相同，都是在连接条件中使用等号（=）比较运算符，但结果集中不包括重复列。
内连接语法格式二：
SELECT 列名列表 FROM 表名1,表名2
WHERE 表名1.列名=表名2.列名
内连接语法格式一：
SELECT 列名列表 FROM 表名1 [INNER] JOIN 表名2
ON 表名1.列名=表名2.列名
内连接例子
【例5-27】查询学生选课数据库，输出考试不及格学生的学号、姓名、课程号以及成绩。
【例5-28】查询学生选课数据库，输出考试不及格学生的学号、姓名、课程名以及成绩。
答案一：
SELECT A.学号,姓名,课程号,成绩
FROM 学生 AS A INNER JOIN 选课 AS B
ON A.学号=B.学号 WHERE 成绩<60
答案一：
SELECT A.学号 AS 不及格学生学号,姓名,课程名,成绩
FROM 学生 AS A INNER JOIN 选课 AS B
ON A.学号=B.学号 INNER JOIN 课程 AS C
ON B.课程号=C.课程号 WHERE 成绩<60
答案二：
SELECT A.学号,姓名,课程号,成绩
FROM 学生 AS A ，选课 AS B
WHERE A.学号=B.学号 AND 成绩<60
答案二：
SELECT A.学号 AS 不及格学生学号,姓名,课程名,成绩
FROM 学生 AS A，选课 AS B，课程 AS C
WHERE A.学号=B.学号 AND B.课程号=C.课程号
AND 成绩<60
外连接例子
【例5-29】查询学生选课数据库，输出所有教师所教授的课程信息，没有教授课程的教师也要列出。
SELECT A.教师号，教师名，课程名，学时，学分
FROM 教师 AS A LEFT JOIN 课程 AS B
ON A.教师号=B.授课教师
5.4.4 自连接
自连接就是一个表的两个副本之间的内连接。表名在FROM子句中出现两次，为了区别，必须对表指定不同的别名，语句中使用的列名前也要加上表的别名进行限定。
【例5-30】对学生表进行查询，查询和学号“11101002”在同一个班级的学生的学号和姓名。
SELECT B.学号,B.姓名
FROM 学生 AS A INNER JOIN 学生 AS B
ON A.班级=B.班级
WHERE A.学号='11101002' AND B.学号<>'11101002'
5.5 合并结果集
是指将多个SELECT语句查询的结果集使用运算符UNION进行合并，组合成一个结果集。
使用UNION时，需要注意：
◇所有SELECT语句中的列数必须相同。
◇所有SELECT语句中按顺序对应列的数据类型必须兼容。
◇合并后的结果集中的列名是第一个SELECT语句中各列的列名。如果需要为返回列指定列名，则必须在第一个SELECT语句中指定。
◇使用UNION运算符合并结果集时，每一个SELECT语句本身不能包括ORDER BY子句或COMPUTE子句，只能在最后使用一个ORDER BY子句或COMPUTE子句对整个结果集进行排序或汇总，且必须使用第一个SELECT语句中的列名。
合并结果集例子
【例5-31】对学生选课数据库进行查询，输出所有学生和教师的编号和姓名。
SELECT 学号 AS 编号，姓名 FROM 学生
UNION
SELECT 工号 AS 编号，姓名 FROM 教师
5.6 子查询
子查询是一个SELECT语句嵌套在另一个SELECT语句的WHERE子句中的查询。包含子查询的SELECT语句称为父查询或外部查询。子查询可以多层嵌套，执行时由内向外，即每一个子查询在其上一级父查询处理之前先处理，其查询结果回送给父查询。
使用子查询时，应注意以下几点：
◇子查询的SELECT语句总是使用圆括号括起来。
◇子查询不能包含COMPUTE子句；如果子查询的SELECT语句中使用了TOP关键字，则子查询必须包含ORDER BY子句。
◇子查询的返回值为单个值时，子查询可以应用到任何表达式中。
子查询有：比较子查询、IN子查询、批量比较子查询，EXISTS子查询。
5.6.1 比较子查询
父查询与子查询之间用比较运算符进行连接。在这种类型子查询中，子查询返回的值最多只能有一个。
【例5-32】查询学生选课数据库，输出选修了“音乐欣赏”这门课的所有学生的学号和成绩。
SELECT 学号，成绩 FROM 选课
WHERE 课程号=(SELECT 课程号 FROM 课程
WHERE 课程名='音乐欣赏')
【例5-33】查询学生选课数据库，输出年龄最大的学生的姓名。
SELECT 姓名 FROM 学生
WHERE 出生日期=(SELECT MIN(出生日期) FROM 学生)
子查询的嵌套
【例5-34】查询学生选课数据库，输出“音乐欣赏”这门课不及格的学生的姓名。
SELECT 姓名 FROM 学生
WHERE 学号=(SELECT 学号 FROM 选课
WHERE 成绩<60 AND 课程号=(SELECT 课程号
FROM 课程 WHERE 课程名=‘音乐欣赏’))
在上例中，如果音乐欣赏这门课不及格的学生有多个，该怎么办？
答案：使用IN子查询
5.6.2 IN子查询
◇IN后面的子查询可以返回多条记录
◇常用IN替换等于（＝）的比较子查询
【例5-35】查询学生选课数据库，输出考试不及格的学生的姓名
修改上例，查询没有考试不及格的学生的姓名
SELECT 姓名 FROM 学生
WHERE 学号 NOT IN(SELECT 学号 FROM 选课
WHERE 成绩<60)
SELECT 姓名 FROM 学生
WHERE 学号 IN(SELECT 学号 FROM 选课 WHERE 成绩<60)
5.6.3 批量比较子查询
是指子查询的结果不止一个，父查询和子查询之间又需要用比较运算符进行连接。
1．使用ANY谓词
会使用指定的比较运算符将一个表达式的值或列值与子查询返回值中的每一个进行比较，只要有一次比较的结果为TRUE，则整个表达式的值为TRUE，否则为FALSE。
【例5-36】查询学生选课数据库，输出需要补考的学生姓名。
SELECT 姓名 FROM 学生
WHERE 学号=ANY(SELECT 学号 FROM 选课 WHERE 成绩<60)
5.6.3 批量比较子查询
2．使用ALL谓词
会使用指定的比较运算符将一个表达式的值或列值与子查询返回值中的每一个进行比较，只有所有比较的结果为TRUE，整个表达式的值为TRUE，否则为FALSE。
【例5-37】查询学生选课数据库，输出不需要补考的学生的姓名。
SELECT 姓名 FROM 学生
WHERE 学号<>ALL(SELECT 学号 FROM 选课 WHERE 成绩<60)
5.6.4 EXISTS子查询
是指在子查询前面加上EXISTS运算符或NOT EXISTS运算符。EXISTS运算符和后面的子查询构成EXISTS表达式。如果子查询查找到有满足条件的数据行，那么EXISTS表达式返回值为TRUE，否则为FALSE。
【例5-38】查看学生选课数据库中有没有一个“教师”表，如果有，删除这个表。
USE 学生选课
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='教师‘)
DROP TABLE 教师
GO
5.6.4 在INSERT、UPDATE、DELETE中使用子查询
1.在INSERT语句中使用子查询
使用INSERT…SELECT语句可以将SELECT语句查询的结果添加到表中，一次可以添加多行。语法格式如下：
INSERT 表1[列名列表1]
SELECT 列名列表2 FROM 表2 [WHERE 条件表达式]
【例5-40】建立一个“电子商务学生”表，表里有学号、姓名、出生年月，把在“学生”表中查询到的电子商务专业的学生的信息添加到本表中。
注意：使用本语句时，表1已经存在，且列名列表1中的列数、列的顺序、列的数据类型和列名列表2中对应的要一样或兼容。
INSERT 电子商务学生(学号,姓名,出生年月)
SELECT 学号,姓名,出生年月 FROM 学生
WHERE 班级 LIKE '电子商务%'
5.6.4 在INSERT、UPDATE、DELETE中使用子查询
2.在UPDATE语句中使用子查询
使用UPDATE语句时，可以在WHERE子句中使用子查询。
【例5-41】修改“课程”表，把上课老师的职称为“教授”的课程的学时减少6个。
UPDATE 课程 SET 学时=学时-6
WHERE 授课教师 IN(SELECT 工号 FROM 教师
WHERE 职称='教授')
5.6.4 在INSERT、UPDATE、DELETE中使用子查询
3.在DELETE语句是使用子查询
使用DELETE语句时，可以在WHERE子句中使用子查询。
【例5-42】将“选课”表中王斌的选课信息删除。
DELETE FROM 选课
WHERE 学号=(SELECT 学号 FROM 学生
WHERE 姓名='王斌')
本章结束,谢谢！

展开更多......

收起↑