第5章
数据查询与视图管理




学习要点： 查询是数据库系统中最常用，也是最重要的功能，它为用户快速、方便地使用数据库中的数据提供了一种有效的方法。视图是根据用户的需求而定义的从基本表导出的虚表。要求掌握单表查询，灵活运用单表查询、多表连接查询，掌握分组与排序的使用方法，理解子查询和联合查询的使用规则，掌握视图的创建和管理。


5.1简单查询


5.1简 单 查 询
简单查询是按照一定的条件在单一的表上进行数据查询，还包括查询结果的排序与利用查询结构生成新表。
使用SQL中的SELECT子句来实现对数据库的查询。SELECT语句的作用是让服务器从数据库中按用户要求检索数据，并将结果以表格的形式返回给用户。
5.1.1SELECT语句结构
完整的SELECT语句非常复杂，为了更加清楚地理解SELECT语句，从下面几个成分来描述。数据查询SELECT语句的语法格式如下。



SELECT <子句1>

FROM <子句2>

［WHERE <表达式1>］

［GROUP BY <子句3>］

［HAVING <表达式2>］

［ORDER BY <子句4>］

［LIMIT <子句5>］

［UNION <运算符>］;




说明： 
(1) SELECT子句指定查询结果中需要返回的值。
(2) FROM子句指定从其中检索行的表或视图。
(3) WHERE表达式指定查询的搜索条件。
(4) GROUP BY子句指定查询结果的分组条件。
(5) HAVING表达式指定分组或集合的查询条件。
(6) ORDER BY子句指定查询结果的排序方法。
(7) LIMIT子句可以被用于限制被SELECT语句返回的行数。
(8) UNION操作符将多个SELECT语句查询结果组合为一个结果集，该结果集包含联合查询中的所有查询的全部行。
5.1.2SELECT子语句
SELECT子句的语法格式如下。



SELECT ［ALL|DISTINCT］ <目标表达式>［,<目标表达式>］［,…］

FROM<表或视图名>［,<表或视图名>］［,…］ ［LIMIT n1［,n2］］;





MySQL数据库应用与实践教程(第2版)微课视频版
第
5
章
数据查询与视图管理

说明： 
(1) ALL指定表示结果集的所有行，可以显示重复行，ALL是默认选项。
(2) DISTINCT指定在结果集中显示唯一行，空值被认为相等，用于消除取值重复的行。ALL与DISTINCT不能同时使用。
(3) LIMIT n1表示返回最前面的n1行数据，n1表示返回的行数。
(4) LIMIT n1,n2表示从n1行开始，返回n2行数据。初始行为0(从0行开始)。n1,n2必须是非负的整型常量。
(5) 目标表达式为结果集选择的要查询的特定表中的列，可以是星号(*)、表达式、列表、变量等。其中，星号(*)用于返回表或视图的所有列，列表用“表名.列名”来表示，如student.学号，若只有一个表或多个表中没有相同的列时，表名可以省略。
例5.1在数据库D_sample中查询student表中学生的学号、姓名和性别。SQL语句如下。



use D_sample;

select 学号,姓名,性别 from student;




查询结果如图5.1所示。



图5.1对student表的投影查询


例5.2在数据库D_sample中查询student表中学生的全部信息。SQL语句如下。



select * from student;




查询结果如图5.2所示。


图5.2对student表全部信息查询


例5.3在数据库D_sample中查询student表前6行数据。SQL语句如下。



select * from student limit 6;




查询结果如图5.3所示。




图5.3对student表前6行查询


例5.4在数据库D_sample中查询student表中从第4行开始的6行数据。SQL语句如下。



select * from student limit 3,6;




查询结果如图5.4所示。


图5.4对student表中从第4行开始的6行数据的查询


例5.5在数据库D_sample中查询student表所有学生的民族信息，要求输出的信息不重复。SQL语句如下。



select distinct 民族 from student;




查询结果如图5.5所示。


图5.5对student表不重复数据的查询


5.1.3WHERE子语句
使用SELECT进行查询时，如果用户希望设置查询条件来限制返回的数据行，可以通过在SELECT语句后使用WHERE子句来实现。
WHERE子句的语法格式如下。



WHERE <表达式>;




使用WHERE子句可以限制查询的范围，提高查询的效率。使用时，WHERE子句必须紧跟在FROM子句之后。WHERE子句中的查询条件或限定条件可以是比较运算符、模式匹配、范围说明、是否为空值、逻辑运算符。
1. 比较查询
比较查询条件由两个表达式和比较运算符(如表5.1所示)组成，系统将根据该查询条件的真假来决定某一条记录是否满足该查询条件，只有满足该查询条件的记录才会出现在最终结果集中。
比较查询条件的格式如下。



表达式1比较运算符表达式2






表5.1比较运算符



运算符
描述
表达式
运算符
描述
表达式
=
相等
ｘ=ｙ
<=
小于等于
ｘ<=ｙ
<>
不相等
ｘ<>ｙ
>=
大于等于
ｘ>=ｙ
>
大于
ｘ>ｙ
!=
不等于
x!=y
<
小于
ｘ<ｙ
<=>
相等或都等于空
x<=>y

例5.6在数据库D_sample中查询student表中姓名为李立波的学号、姓名和性别的列信息。SQL语句如下。



select 学号,姓名,性别 from student

where 姓名='李立波';




查询结果如图5.6所示。



图5.6对student表的比较查询结果


2. 模式匹配
模式匹配常用来返回某种匹配格式的所有记录，通常使用LIKE或REGEXP关键字来指定模式匹配条件。
1) LIKE运算符
LIKE运算符使用通配符来表示字符串需要匹配的模式，通配符及其含义见表5.2。


表5.2常用通配符及其含义



通配符
名称
描述
%
百分号
匹配０个或多个任意字符
_
下画线
匹配单个的任意字符

模式匹配条件的格式如下。



表达式［NOT］ LIKE模式表达式




例5.7在数据库D_sample中查询student表中姓张的学生信息。SQL语句如下。



select * from student

where 姓名 like '张%';




查询结果如图5.7所示。


图5.7对student表的LIKE查询


例5.8在数据库D_sample中查询student表中少数民族的学生信息。SQL语句如下。



select * from student

where 民族 not like '汉%';




查询结果如图5.8所示。



图5.8对student表的NOT LIKE查询


2) REGEXP运算符
REGEXP运算符使用通配符来表示字符串需要匹配的模式，通配符及其含义见表5.3。


表5.3常用通配符及其含义



通配符
名称
描述
^
插入号
匹配字符串的开始部分
$
美元
匹配字符串的结束部分
.
句号
匹配字符串(包括回车和新行)
*
乘号
匹配０个或多个任意字符
+
加号
匹配单个或多个任意字符
?
问号
匹配0个或单个任意字符
()
括号
匹配括号里的内容
{n}
大括号
匹配括号前的内容出现n次的序列

模式匹配条件的格式如下。



表达式［NOT］ REGEXP模式表达式




例5.9在数据库D_sample中查询student表中姓张的学生信息。SQL语句如下。



select * from student

where 姓名 regexp '^张';




或者



select * from student

where 姓名 regexp '张+';




查询结果如图5.9所示。



图5.9对student表的REGEXP查询


3. 范围查询
如果需要返回某一字段的值介于两个指定值之间的所有记录，那么可以使用范围查询条件进行检索。范围检索条件主要有以下两种情况。
(1) 使用BETWEEN…AND…语句指定内含范围条件。
要求返回记录某个字段的值在两个指定值范围以内，同时包括这两个指定的值，通常使用BETWEEN…AND…语句来指定内含范围条件。
内含范围条件的格式如下。



表达式 BETWEEN 表达式1 AND 表达式2




例5.10在数据库D_sample中查询sc表中成绩为80~100分的学生的学号和成绩信息。SQL语句如下。



select 学号,成绩 from sc

where 成绩 between 80 and 100;




查询结果如图5.10所示。



图5.10对sc表的范围查询


(2) 使用IN语句指定列表查询条件。
包含列表查询条件的查询将返回所有与列表中的任意一个值匹配的记录，通常使用IN语句指定列表查询条件。对于查询条件表达式中出现多个条件相同的情况，也可以用IN语句来简化。
列表查询条件的格式如下。



表达式IN(表达式［,…］)




例5.11在数据库D_sample中查询course表中开课学期为第1学期和第2学期的课程信息。SQL语句如下。



select * from course

where 开课学期 in('1','2');




查询结果如图5.11所示。



图5.11对course表的范围查询



4. 空值判断查询条件
空值判断查询条件主要用来搜索某一字段为空值的记录，可以使用IS NULL或IS NOT NULL关键字来指定查询条件。
注意： IS NULL不能用“=NULL”代替。
例5.12在数据库D_sample中查询course表中所有课程简介为空的课程信息。SQL语句如下。



select * from course

where 课程简介 is null;




查询结果如图5.12所示。


图5.12对course表的空值判断查询


5. 使用逻辑运算符查询
前面介绍的查询条件还可以通过逻辑运算符组成更为复杂的查询条件，逻辑运算符有4个，分别是NOT、AND、OR和XOR。其中，NOT表示对条件的否定； AND用于连接两个条件，当两个条件都满足时才返回TRUE，否则返回FALSE； OR也用于连接两个条件，只要有一个条件满足时就返回TRUE； XOR同样也用于连接两个条件，只有一个条件满足时才返回TRUE，当两个条件都满足或都不满足时返回FALSE。
说明： 
(1) 4种运算的优先级按从高到低的顺序是NOT、AND、OR和XOR，但可以通过括号改变其优先级关系。
(2) 在MySQL中，逻辑表达式共有３种可能的结果值，分别是1(TRUE)、0(FALSE)和 NULL。
例5.13在数据库D_sample中查询sc表中成绩为80~100分的学号和成绩信息。SQL语句如下。



select 学号,成绩 from sc

where 成绩>=80 and 成绩<=100;




查询结果如图5.13所示。


图5.13对sc表的逻辑运算符查询


5.1.4ORDER BY子语句
当使用SELECT语句查询时，如果希望查询结果能够按照其中的一个或多个字段进行排序，这时可以通过在SELECT语句后跟一个ORDER BY子句来实现。排序有两种方式： 一种是升序，使用ASC关键字来指定； 一种是降序，使用DESC关键字来指定。如果没有指定顺序，系统将默认使用升序。
ORDER BY子句的语法格式如下。



ORDER BY <字段名> ［ASC|DESC］［,…］;




例5.14在数据库D_sample中查询course表中开课学期按照升序排列的课程信息。SQL语句如下。



select * from course

order by 开课学期;




查询结果如图5.14所示。



图5.14对course表的排序


例5.15在数据库D_sample中查询sc表中选修了“07003”课程的学生成绩信息，成绩按降序进行排序。SQL语句如下。



select * from sc

where 课程号='07003'

order by 成绩 desc;




查询结果如图5.15所示。



图5.15对sc表的排序


5.1.5GROUP BY子语句
使用SELECT进行查询时，如果用户希望将数据记录依据设置的条件分成多个组，可以通过在SELECT语句后使用GROUP BY子句来实现。如果SELECT子句<目标表达式>中包含聚合函数，则GROUP BY将计算每组的汇总值。指定GROUP BY时，选择列表中任意非聚合表达式内的所有列都应包含在GROUP BY列表中，或者GROUP BY表达式必须与选择列表的表达式完全匹配。GROUP BY子句可以将查询结果按字段或字段组合在行的方向上进行分组，每组在字段或字段组合上具有相同的聚合值。如果聚合函数没有使用GROUP BY子句，则只为SELECT语句报告一个聚合值。常用的聚合函数见表5.4。


表5.4常用的聚合函数



函数名
功能
sum()
返回一个数值列或计算列的总和
avg()
返回一个数值列或计算列的平均值
min()
返回一个数值列或计算列的最小值
max()
返回一个数值列或计算列的最大值
count()
返回满足SELECT语句中指定条件的记录数
count(*)
返回找到的行数

GROUP BY子句的语法格式如下。



GROUP BY {字段名|表达式}［ASC|DESC］［,…］

［WITH ROLLUP］;




说明： 
(1) 与ORDER BY子句中的ASC或DESC关键字相同。ASC关键字用来指定升序，DESC关键字用来指定降序。
(2) ROLLUP指定在结果集内不仅包含由GROUP BY提供的行，还包含汇总行。汇总行在结果集中显示为NULL，用于表示所有值。按层次结构顺序，从组内的最低级别到最高级别汇总组。组的层次结构取分组时指定使用的顺序。更改列分级的顺序会影响在结果集内生成的行数。
例5.16在数据库D_sample中统计student表中学生的男女人数。SQL语句如下。



select 性别,count(性别) as 人数 from student

group by 性别;




查询结果如图5.16所示。


图5.16对student表的分组查询


例5.17在数据库D_sample中统计student表中每个民族的男女人数、总人数，以及学生总人数。SQL语句如下。



select 民族,性别,count(*) as 人数 from student

group by 民族,性别

with rollup;




查询结果如图5.17所示。



图5.17对student表使用ROLLUP分组的查询


5.1.6HAVING子语句
当完成数据结果的查询和统计后，若希望对查询和计算后的结果进行进一步的筛选，可以通过在SELECT语句后使用GROUP BY子句配合HAVING子句来实现。
HAVING子句的语法格式如下。



HAVING <表达式>;




可以在包含GROUP BY子句的查询中使用WHERE子句。WHERE与HAVING子句的根本区别在于作用对象不同，WHERE子句作用于基本表或视图，从中选择满足条件的记录，HAVING子句作用于组，选择满足条件的组，必须用于GROUP BY子句之后，但GROUP BY子句可以没有HAVING子句。HAVING与WHERE语法类似，但HAVING可以包含聚合函数。
例5.18在数据库D_sample中查询sc表中平均成绩在85分以上的课程号。SQL语句如下。



select 课程号, avg(成绩) as 平均成绩 from sc

group by 课程号

havingavg(成绩)>=85;




查询结果如图5.18所示。



图5.18对sc表的限定查询




课堂实践6


课堂实践6： 简单查询的应用
(1) 在教务管理系统数据库D_eams中，查询学生信息表T_student中前8条记录。SQL语句如下。



use D_eams;

select * from T_student limit 8;




查询结果如图5.19所示。


图5.19对T_student表的前8条记录查询


(2) 查询课程信息表T_course中的课程名称。SQL语句如下。



select distinct 课程名称 from T_course;




查询结果如图5.20所示。



图5.20对T_course表的不重复记录查询


(3) 查询学生信息表T_student中姓“李”的男生的学生信息。SQL语句如下。



select * from T_student

where 姓名 like '李%' and 性别='男';




查询结果如图5.21所示。



图5.21对T_student表的选择查询


(4) 查询学生信息表T_student中年龄为20~25岁的学生信息。SQL语句如下。



select * from T_student

where year(now())-year(出生日期) between 20 and 25;




查询结果如图5.22所示。



图5.22对T_student表的范围查询


(5) 查询成绩表T_sc中考试成绩为85分以下的学生的学号。SQL语句如下。



select distinct 学号 from T_sc

where 成绩<=85;




查询结果如图5.23所示。



图5.23对T_sc表的条件查询


(6) 查询成绩表T_sc中有成绩的学生学号和课程号。SQL语句如下。



select 学号,课程号 from T_sc

where 成绩 is not null;




查询结果如图5.24所示。


图5.24对T_sc表的空值判断查询


(7) 统计成绩表T_sc中选修了课程的学生人数。SQL语句如下。



select count(distinct 学号) as 人数

from T_sc;




查询结果如图5.25所示。


图5.25对T_sc表的统计人数查询


(8) 计算成绩表T_sc中07003号课程的学生平均成绩。SQL语句如下。



select avg(成绩) as 平均成绩 from T_sc

where 课程号='07003';




查询结果如图5.26所示。



图5.26对T_sc表的统计平均值查询


(9) 统计党团员的人数。SQL语句如下。



select 政治面貌,count(政治面貌) as 人数 from T_student

group by 政治面貌;




查询结果如图5.27所示。


图5.27对T_sc表的分组统计查询


(10) 统计党团员的男女人数。SQL语句如下。



select 政治面貌,性别,count(*) as 人数 from T_student

group by 政治面貌,性别;




查询结果如图5.28所示。



图5.28对T_student表的分组统计查询


(11) 查询选修了两门以上课程的学生学号。SQL语句如下。



select 学号 from T_sc

group by 学号

having count(课程号)>=2;




查询结果如图5.29所示。



图5.29对T_sc表的分组统计限定查询


(12) 查询选修了07003号课程的学生学号及其成绩，要求成绩按照由高到低的顺序排列。SQL语句如下。



select 学号,成绩 from T_sc

where 课程号='07003'

order by 成绩 desc;




查询结果如图5.30所示。



图5.30对T_sc表的排序查询




5.2连接查询


5.2连 接 查 询
连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，可以在任何时候增加新的数据类型。为不同实体创建新的表，然后通过连接进行查询。
5.2.1内连接
内连接的连接查询结果集中仅包含满足条件的行，内连接是MySQL默认的连接方式，可以把INNER JOIN简写成JOIN，根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。
内连接命令的语法格式如下。



FROM <表名1> ［别名1］, <表名2> ［别名2］ ［,…］

WHERE <连接条件表达式> ［AND <条件表达式>］;




或者



FROM <表名1> ［别名1］ INNER JOIN <表名2> ［别名2］ ON <连接条件表达式>

［WHERE <条件表达式>］;




其中，第一种命令格式的连接类型在WHERE子句中指定，第二种命令格式的连接类型在FROM子句中指定。
另外，连接条件是指在连接查询中连接两个表的条件。连接条件表达式的一般格式如下。



［<表名1>］<别名1.列名> <比较运算符> ［<表名2>］<别名2.列名>




比较运算符可以使用等号“=”，此时称作等值连接； 也可以使用不等比较运算符，包括>、<、>=、<=、!=、<>，等等，此时为不等值连接。
说明： 
(1) FROM后可跟多个表名，表名与别名之间用空格间隔。
(2) 当连接类型在WHERE子句中指定时，WHERE后一定要有连接条件表达式，即两个表的公共字段相等。
(3) 若不定义别名，表的别名默认为表名，定义别名后使用定义的别名。
(4) 若在输出列或条件表达式中出现两个表的公共字段，则在公共字段名前必须加别名。
例5.19在数据库D_sample中查询每个学生及其选修课的情况。
学生的基本情况存放在student表中，选课情况存放在sc表中，所以查询过程涉及上述两个表。这两个表是通过公共字段学号实现内连接的。SQL语句如下。



use D_sample;

select a.*,b.*

from student a,sc b

where a.学号=b.学号;




或者



select a.*,b.*

from student a inner join sc b on a.学号=b.学号;




查询结果如图5.31所示。



图5.31对student表和sc表的等值连接


若在等值连接中把目标列中的重复字段去掉，则称为自然连接。
例5.20用自然连接完成例5.19的查询。SQL语句如下。



select student.学号,姓名,性别,出生日期,课程号,成绩

from student,sc

where student.学号=sc.学号;




查询结果如图5.32所示。



图5.32对student表和sc表的自然连接


注意： 在学号前的表名不能省略，因为学号是student和sc共有的属性，所以必须加上表名前缀。
例5.21查询所有女生的学号、姓名、课程号及成绩信息。SQL语句如下。



select a.学号,姓名,课程号,成绩

from student a,sc b

where a.学号=b.学号 and 性别='女';




或者



select a.学号,姓名,课程号,成绩

from student a inner join sc b on a.学号=b.学号

where 性别='女';




查询结果如图5.33所示。



图5.33对student表和sc表的内连接


例5.22查询学生的姓名、课程名称和成绩信息。SQL语句如下。



select 姓名,课程名称,成绩

from student a,course b,sc c

where a.学号=c.学号 and b.课程号=c.课程号;




其中，3个表进行两两连接，a.学号=c.学号和b.课程号=c.课程号是两个连接条件。若n个表连接,需要n-1个连接条件。
另一种方法为： 



select a.姓名,b.课程名称,c.成绩

from student a inner join sc c on a.学号=c.学号

 inner join course b on b.课程号=c.课程号;




查询结果如图5.34所示。



图5.34对student表、course表和sc表的内连接


5.2.2外连接
外连接的连接查询结果集中既包含那些满足条件的行，还包含其中某个表的全部行，有3种形式的外连接： 左外连接、右外连接、全外连接。
左外连接是对连接条件中左边的表不加限制，即在结果集中保留连接表达式左表中的非匹配记录； 右外连接是对右边的表不加限制，即在结果集中保留连接表达式右表中的非匹配记录； 全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中。
外连接命令的语法格式如下。



FROM <表名1> LEFT| RIGHT| FULL ［OUTER］JOIN <表名2>

ON <表名1.列1>=<表名2.列2>




例5.23在数据库D_sample中查询所有学生信息及其选修的课程号，如果学生未选修任何课程，也要包括其基本信息。SQL语句如下。



select student.*,课程号

from student left join sc

on student.学号=sc.学号;




执行该查询时，若学生未选修任何课程，则结果表中相应行的课程号字段值为NULL。查询结果如图5.35所示。



图5.35对student表和sc表的左外连接



例5.24查询被选修的成绩信息和所有的课程名称。SQL语句如下。



select sc.*,课程名称

from sc right join course

on course.课程号=sc.课程号;




该查询执行时，若某课程未被选修，则结果表中相应行的学号、课程号和成绩字段值均为NULL。查询结果如图5.36所示。



图5.36对course表和sc表的右外连接


5.2.3交叉连接
交叉连接又称为笛卡儿连接，是指两个表之间作笛卡儿积操作，得到结果集的行数是两个表的行数的乘积。
交叉连接命令的语法格式如下。



FROM <表名1>［别名1］ ,<表名2>［别名2］




需要连接查询的表名在FROM子句中指定，表名之间用英文逗号隔开。
例5.25在数据库D_sample中sc表和course表进行交叉连接。SQL语句如下。



select a.*,b.*

from course a,sc b;




此处为了简化表名，分别给两个表指定了别名。一旦表名指定了别名，在该命令中，都必须用别名代替表名。查询结果如图5.37所示。



图5.37对course表和sc表的交叉连接


5.2.4自连接
连接操作不只是在不同的表之间进行，一张表内还可以进行自身连接操作，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。在自连接中，必须为表指定两个别名，使之在逻辑上成为两张表。
自连接命令的语法格式如下。



FROM <表名1> ［别名1］,<表名1> ［别名2］［,…］

WHERE <连接条件表达式> ［AND <条件表达式>］;




例5.26在数据库D_sample中查询同时选修了07001和07003课程的学生学号。SQL语句如下。



select a.学号

from sc a,sc b

where a.学号=b.学号






and a.课程号='07001'

and b.课程号='07003';




查询结果如图5.38所示。



图5.38对sc表的自连接


例5.27查询选修相同课程的学生学号、课程号和成绩。SQL语句如下。



select distinct a.学号,a.课程号,a.成绩

from sc a,sc b

where a.课程号=b.课程号 and a.学号<>b.学号




查询结果如图5.39所示。



图5.39对sc表的自连接


5.2.5多表连接
在进行内连接时，有时候出于某种特殊需要，可能涉及三张表甚至更多表进行连接。三张表甚至更多表进行连接和两张表连接的方法基本是相同的，先把两张表连接成一个大表，再将其和第三张表进行连接，以此类推。


课堂实践7


课堂实践7： 连接查询的应用
(1) 在教务管理系统数据库D_eams中，查询“宋志强”同学所选课程的成绩。SQL语句如下。



use D_eams;

select 成绩

from T_student a,T_sc b

where a.学号=b.学号 and a.姓名='宋志强';




查询结果如图5.40所示。



图5.40对T_student和T_sc表的连接查询


(2) 查询至少选修一门课程的女学生姓名。SQL语句如下。



select distinct 姓名

from T_student a,T_sc b

where a.学号=b.学号 and 性别='女';




查询结果如图5.41所示。



图5.41对T_student和T_sc表的连接查询


(3) 查询姓宋的学生选修的课程名称。SQL语句如下。



select 课程名称

from T_student a,T_course b,T_sc c

where a.学号=c.学号 and b.课程号=c.课程号 and 姓名 like '宋%';




查询结果如图5.42所示。



图5.42对三个表的连接查询


(4) 查询选修了课程号为07003的课程且成绩在80分以上的学生姓名及成绩。SQL语句如下。



select 姓名,成绩

from T_student a,T_sc b

where a.学号=b.学号 and 课程号='07003' and 成绩>=80;




查询结果如图5.43所示。



图5.43对两个表连接的选择查询


(5) 查询选修了数据库技术基础课程且成绩在80分以上的学生学号、姓名、课程名称及成绩。SQL语句如下。



select a.学号,姓名,课程名称,成绩

from T_student a,T_course b,T_sc c

where a.学号=c.学号 and b.课程号=c.课程号

and 课程名称='数据库技术基础' and 成绩>=80;




查询结果如图5.44所示。



图5.44对三个表连接的选择查询


(6) 查询在第2学期所开课程的课程名称及成绩。SQL语句如下。



select 课程名称,成绩

from T_course a,T_sc b

where a.课程号=b.课程号 and 开课学期='2';




查询结果如图5.45所示。



图5.45对两个表连接的选择查询



(7) 查询选修课程名称为数据库技术基础的学生学号和姓名。SQL语句如下。



select a.学号,姓名

from T_student a,T_course b,T_sc c

where a.学号=c.学号 and b.课程号=c.课程号

and 课程名称='数据库技术基础';




查询结果如图5.46所示。



图5.46对三个表连接的选择查询


(8) 查询课程成绩及格的女同学的学生信息及课程号与成绩。SQL语句如下。



select a.*,b.课程号,成绩

from T_student a,T_sc b

where a.学号=c.学号

and 成绩>=60 and 性别='女';




查询结果如图5.47所示。



图5.47对两个表连接的选择查询


(9) 查询高峰的所有选修课的成绩。SQL语句如下。



select 成绩

from T_student a,T_sc b

where a.学号=c.学号

and 姓名='高峰';




查询结果如图5.48所示。



图5.48对两个表连接的选择查询


(10) 查询选修了课程号为07005的学生的姓名和成绩。SQL语句如下。



select 姓名,成绩

from T_student a,T_sc b

where a.学号=b.学号 and 课程号='07005';




查询结果如图5.49所示。



图5.49对两个表连接的选择查询


(11) 查询选修了程序设计基础课程的学生的姓名和课程成绩，并按成绩降序排列。SQL语句如下。



select 姓名,成绩

from T_student a,T_course b,T_sc c

where a.学号=c.学号 and b.课程号=c.课程号

and 课程名称='程序设计基础'

order by 成绩 desc;




查询结果如图5.50所示。



图5.50对三个表连接的选择查询


(12) 查询选修07003课程的学生的平均年龄。SQL语句如下。



select avg(year(now())-year(出生日期)) as 平均年龄

from T_student a,T_sc b

where a.学号=b.学号 and 课程号='07003';




查询结果如图5.51所示。



图5.51对两个表连接的选择查询




5.3子查询


5.3子查询
子查询指在一个SELECT查询语句的WHERE子句中包含另一个SELCET查询语句，或者将一个SELECT查询语句嵌入在另一个语句中成为其一部分。在外层的SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询。
子查询可描述复杂的查询条件，也称为嵌套查询。嵌套查询一般会涉及两个以上的表，所做的查询有的也可以采用连接查询或者用几个查询语句完成。
在子查询中可以使用IN关键字、EXISTS关键字和比较操作符(ALL与ANY)等来连接表数据信息。
5.3.1IN子查询
IN子查询可以用来确定指定的值是否与子查询或列表中的值相匹配。通过 IN(或 NOT IN)引入的子查询结果是一列值。子查询返回结果之后，外部查询将利用这些结果。
IN子查询的语法格式如下。



<字段名> ［NOT］IN(子查询)




例5.28在数据库D_sample中查询没有选修计算机网络技术基础的学生学号和姓名。SQL语句如下。



use D_sample;

select 学号,姓名 from student where 学号 not in

(select 学号 from sc where 课程号 in

(select 课程号 from course where 课程名称='计算机网络技术基础'));




查询结果如图5.52所示。



图5.52IN子查询


例5.29查询所有成绩大于80分的学生的学号和姓名。SQL语句如下。



select 学号,姓名 from student

where 学号 in

(select 学号 from sc where 成绩>80);




查询结果如图5.53所示。



图5.53IN子查询


5.3.2比较运算符子查询
带有比较运算符的子查询是指主查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用>、<、=、>=、<=、!=或<>等比较运算符。
比较运算符子查询的语法格式如下。



<字段名> <比较运算符> <子查询>




例5.30在数据库D_sample中查询超过平均年龄的学生的信息。SQL语句如下。



select * from student

where year(now())-year(出生日期)>

(select avg(year(now())-year(出生日期)) from student);




查询结果如图5.54所示。



图5.54比较运算符子查询


例5.31查询选修07003号课程，并且分数超过课程平均成绩的学号。SQL语句如下。



select 学号 from sc

where 课程号='07003' and 成绩>=

(select avg(成绩) from sc);




查询结果如图5.55所示。



图5.55比较运算符子查询


5.3.3ANY或ALL子查询
子查询返回单值时，可以用比较运算符，但返回多值时，要用ANY或ALL谓词修饰符。而使用ANY或ALL谓词时，必须同时使用比较运算符。子查询由一个比较运算符引入，后面跟ANY 或ALL的比较运算符，ANY和 ALL 用于一个值与一组值的比较，以“>”为例，ANY表示大于一组值中的任意一个，ALL 表示大于一组值中的每一个。
ANY或ALL与比较运算符一起使用的语义见表5.5。


表5.5ANY和ALL的用法和具体含义



用法
含义
>ANY
大于子查询结果中的某个值
>ALL
大于子查询结果中的所有值
<ANY
小于子查询结果中的某个值
<ALL
小于子查询结果中的所有值
>=ANY
大于等于子查询结果中的某个值
>=ALL
大于等于子查询结果中的所有值
<=ANY
小于等于子查询结果中的某个值
<=ALL
小于等于子查询结果中的所有值
=ANY
等于子查询结果中的某个值
=ALL
等于子查询结果中的所有值(通常没有实际意义)
!=ANY或<>ANY
不等于子查询结果中的某个值
!=ALL或<>ALL
不等于子查询结果中的任何一个值

ANY或ALL子查询的语法格式如下。



<字段名> <比较运算符>［ANY|ALL］ <子查询>




例5.32在数据库D_sample中查询成绩最高的学号和成绩。SQL语句如下。



select 学号,成绩 from sc

where 成绩>=all(select 成绩 from sc);




查询结果如图5.56所示。



图5.56ALL子查询


例5.33查询选修07002课程号的成绩高于07003课程号的成绩的学生的学号。SQL语句如下。



select 学号from sc

where课程号='07002' and 成绩>any

(select 成绩 from sc

 where 课程号='07003');




查询结果如图5.57所示。



图5.57ANY子查询


5.3.4EXISTS子查询
带有EXISTS的子查询不需要返回任何实际数据，而只需要返回一个逻辑真值TRUE或逻辑假值FALSE。也就是说，它的作用是在WHERE子句中测试子查询返回的行是否存在。如果存在则返回真值； 如果不存在则返回假值。
EXISTS子查询的语法格式如下。



<字段名> ［NOT］ EXISTS(子查询)




例5.34在数据库D_sample中查询选修了07003课程的学生姓名。SQL语句如下。



select 姓名 from student

where exists

(select * from sc

where student.学号=sc.学号 and 课程号='07003');




查询结果如图5.58所示。



图5.58EXISTS子查询


例5.35查询没有选修07003课程的学生姓名。SQL语句如下。



select 姓名 from student

where not exists

(select * from sc

where student.学号=sc.学号 and 课程号='07003');




查询结果如图5.59所示。



图5.59NOT EXISTS子查询




课堂实践8


课堂实践8： 子查询的应用
(1) 在教务管理系统数据库D_eams中，查询选修了课程的学生的学号、姓名，并按学号升序排序。SQL语句如下。



use D_eams;

select 学号,姓名 from T_student

where 学号 in






(select 学号 from T_sc)

order by 学号 asc;




查询结果如图5.60所示。



图5.60IN子查询


(2) 查询未选修任何课程的学生的学号、姓名，并按学号升序排序。SQL语句如下。



select 学号,姓名 from T_student

where 学号 not in

(select 学号 from T_sc)

order by 学号 asc;




查询结果如图5.61所示。



图5.61NOT IN子查询


(3) 查询所有选修了07005课程的学生的姓名。SQL语句如下。



select 姓名 from T_student

where exists

(select * from T_sc

where T_student.学号= T_sc.学号 and 课程号='07005');




查询结果如图5.62所示。



图5.62EXISTS子查询


(4) 用NOT EXISTS子查询改写查询未选修任何课程的学生的学号、姓名，并按学号升序排序。SQL语句如下。



select 学号,姓名 from T_student

where not exists

(select 学号 from T_sc where T_student.学号= T_sc.学号)

order by 学号 asc;




查询结果如图5.63所示。



图5.63NOT EXISTS子查询


(5) 查询出生日期大于所有男同学出生日期的女同学的姓名。SQL语句如下。



select 姓名 from T_student

where 出生日期>all

(select 出生日期 from T_student 

where 性别='男');




查询结果如图5.64所示。



图5.64ALL子查询


(6) 查询选修了课程“计算机网络技术基础”的学生的学号和成绩。SQL语句如下。



select 学号,成绩 from T_sc

where 课程号=

(select 课程号 from T_course 

where 课程名称='计算机网络技术基础');




查询结果如图5.65所示。



图5.65比较运算符子查询


(7) 查询成绩比该课程平均成绩高的学生的学号及成绩。SQL语句如下。



select 学号,成绩 from T_sc

where 成绩>=

(select avg(成绩) from T_sc);




查询结果如图5.66所示。



图5.66比较运算符子查询


(8) 查询选修课考试不及格的学生的学号和姓名。SQL语句如下。



select 学号,姓名 from T_student

where 学号 in 

(select 学号 from T_sc

where 成绩<60);




查询结果如图5.67所示。



图5.67IN子查询


(9) 查询年龄比包晓娅大的学生的学号和姓名。SQL语句如下。



select 学号,姓名 from T_student

where 出生日期<

(select 出生日期 from T_student

where 姓名='包晓娅');




查询结果如图5.68所示。



图5.68比较运算符子查询


(10) 查询所有与张文静选修了至少一门相同课程的学号、课程号和成绩。SQL语句如下。



select * from T_sc

where 课程号 in

(select 课程号 from T_sc 

where 学号=

(select 学号 from T_student

where 姓名='张文静'));




查询结果如图5.69所示。



图5.69IN子查询




5.4联合查询


5.4联 合 查 询
对于不同的查询操作会生成不同的查询结果集，但在实际应用中会希望这些查询结果集连接到一起，从而组成符合实际需要的数据，此时就可以使用联合查询。使用联合查询可以将两个或更多的结果集组合到一个结果集中，新结果集则包含所有查询结果集中的全部数据。
SELECT的查询结果是元组的集合，所以可以对SELECT的结果进行集合操作。SQL提供的集合操作主要包括3个： UNION(并操作)、INTERSECT(交操作)、MINUS(差操作)。下面对并操作举一个实例，另外两个集合操作的方法类似。
5.4.1UNION操作符
查询的并操作被称为“并集运算”，又称为“合并查询”，是将两个或两个以上的查询结果合并，形成一个具有综合信息的查询结果。使用 UNION语句可以把两个或两个以上的查询结果集合并为一个结果集。
联合查询的语法格式如下。



SELECT <子句1> UNION ［ALL］ SELECT <子句2>;




说明： ALL关键字为可选项。如果在UNION子句中使用ALL关键字，则返回全部满足匹配的结果； 如果不使用ALL关键字，则返回结果中删除满足匹配的重复行。在进行联合查询时，查询结果的列标题为第一个查询语句的列标题。因此，必须在第一个查询语句中定义列标题。
例5.36在数据库D_sample中查询年龄不大于21岁的学生和女生的信息。SQL语句如下。



use D_sample;

select * from student

where year(now())-year(出生日期)<=21

union

select * from student

where 性别='女';




查询结果如图5.70所示。



图5.70联合查询



说明： 
(1) 两个查询结果表必须是兼容的，即列的数目相同且对应列的数据类型相同。
(2) 组合查询最终结果表中的列名来自第一个SELECT语句。
(3) 如果希望组合查询最终结果表中的行以特定的顺序出现，则可在最后一个SELECT语句之后使用ORDER BY子句来排序。
例5.37在数据库D_sample中查询选修课程07002或者07003的成绩信息。SQL语句如下。



select * from sc

where 课程号='07002'

union

select * from sc

where 课程号='07003';




查询结果如图5.71所示。



图5.71联合查询


5.4.2UNION操作符和JOIN操作符的区别与联系
UNION操作符和JOIN操作符都可以将两个表或多个数据表连接在一起，但是，UNION操作符通常用于连接两个或多个SELECT查询语句，而JOIN操作符则是在一个SELECT查询语句中将两个或多个表连接在一起。
在有些情况下，同一个操作任务，可使用UNION或者JOIN两种不同的查询方法。
例5.38在数据库D_sample中使用UNION或者JOIN查询选修了课程07002或者选修成绩在85分以上的成绩信息。SQL语句如下。



select distinct a.*

from sc a join sc b on a.学号=b.学号

where (a.课程号='07002')

or (a.成绩>=85);




或者



select * from sc

where 课程号='07002'

union






select * from sc

where 成绩>=85;




查询结果如图5.72所示。



图5.72UNION与JOIN不同的查询方法相同的结果




5.5视图管理


5.5视 图 管 理
视图是由一个或多个数据表或视图导出的虚拟表或查询表组成的，是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。 
5.5.1视图概述
视图是从一个或者几个基本表或者视图中导出的虚拟表，是从现有基表中抽取若干子集组成用户的“专用表”，这种构造方式必须使用SQL中的SELECT语句来实现。在定义一个视图时，只是把其定义存放在数据库中，并不直接存储视图对应的数据，直到用户使用视图时才去查找对应的数据。
使用视图具有如下优点。
(1) 简化对数据的操作。视图可以简化用户操作数据的方式。可将经常使用的连接、投影、联合查询和选择查询定义为视图，这样在每次执行相同的查询时，不必重写这些复杂的语句，只要一条简单的查询视图语句即可。视图可向用户隐藏表与表之间复杂的连接操作。
(2) 自定义数据。视图能够让不同用户以不同方式看到不同或相同的数据集，即使不同水平的用户共用同一数据库时也是如此。
(3) 数据集中显示。视图使用户着重于其感兴趣的某些特定数据或所负责的特定任务，可以提高数据操作效率，同时增强了数据的安全性，因为用户只能看到视图中所定义的数据，而不是基本表中的数据。
(4) 导入和导出数据。可以使用视图将数据导入或导出。
(5) 合并分割数据。在某些情况下，由于表中数据量太大，在表的设计过程中可能需要经常对表进行水平分割或垂直分割，然而，这样表结构的变化会对应用程序产生不良的影响。使用视图就可以重新保持原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据。
(6) 安全机制。视图可以作为一种安全机制。通过视图，用户只能查看和修改他们能看到的数据。其他数据库或表既不可见也不可访问。
5.5.2创建视图
在SQL中，使用CREATE VIEW语句创建视图。其语法格式如下。



CREATE ［OR REPLACE］ VIEW <视图名> ［(字段名［,…］)］

AS SELECT语句

［WITH CHECK OPTION］;




说明： 
(1) OR REPLACE允许在同名的视图中，用新语句替换掉旧语句。
(2) SELECT语句定义视图的SELECT命令。
(3) WITH CHECK OPTION强制所有通过视图修改的数据满足SELECT语句中指定的选择条件。
(4) 视图中的SELECT命令不能包含FROM子句中的子查询，不能引用系统变量或局部变量。
(5) 在视图定义中命名的表必须已存在，不能引用TEMPORARY表，不能创建TEMPORARY视图，不能将触发程序与视图关联在一起。
例5.39在数据库D_sample中定义视图查询学生的姓名、课程名称和成绩。SQL语句如下。



use D_sample;

create view v1

as

select 姓名,课程名称,成绩

from student a,course b,sc c

where a.学号=c.学号 and b.课程号=c.课程号;




视图定义后，可以像基本表一样进行查询。例如，若要查询以上定义的视图v1，可以使用如下SQL语句。



select * from v1;




在安装系统和创建数据库之后，只有系统管理员sa和数据库所有者DBO具有创建视图的权限，此后他们可以使用GRANT CREATE VIEW命令将这个权限授予其他用户。此外，视图创建者必须具有在视图查询中包括的每一列的访问权。
5.5.3更新视图
在SQL语句中，使用ALTER VIEW语句修改视图。其语法格式如下。



ALTER VIEW <视图名> ［(字段名［,…］)］

AS SELECT语句

［WITH CHECK OPTION］;




说明： 如果在创建视图时使用了WITH CHECK OPTION选项，则在使用ALTER VIEW命令时，也必须包括这些选项。
例5.40修改例5.39中的视图v1。



alter view v1

as

select 学号,姓名 from student;




5.5.4删除视图
在SQL中，使用DROP VIEW语句删除视图。其语法格式如下。



DROP VIEW {视图名}［,…］;




DROP VIEW语句可以删除多个视图，各视图名之间用逗号分隔。
例5.41删除视图v1。



drop view v1;




说明： 
(1) 删除视图时，将从系统目录中删除视图的定义和有关视图的其他信息，还将删除视图的所有权限。
(2) 使用DROP TABLE删除的表上的任何视图都必须用DROP VIEW语句删除。


课堂实践9


课堂实践9： 教务管理系统中视图管理的应用
(1) 在D_eams数据库中创建视图V_sc查询成绩大于90分的所有学生选修的成绩信息。SQL语句如下。



use D_eams;

create view V_sc

as

select * from T_sc

where 成绩>90;




(2) 创建视图V_course查询选修课程号07005的所有学生的学号和姓名。SQL语句如下。



create view V_course

as

select a.学号,姓名 from T_student a,T_sc b

where a.学号=b.学号 and 课程号='07005';




(3) 创建视图V_student查询学生姓名、课程名称、成绩等信息的视图。SQL语句如下。



create view V_student

as

select 姓名,课程名称,成绩 from T_student a,T_sc b,T_course c

where a.学号=b.学号 and b.课程号=c.课程号;




(4) 修改视图V_sc，查询成绩大于90分且开课学期为第3学期的所有学生选修成绩信息。SQL语句如下。



alter view V_sc

as

select 成绩 from T_sc a,T_course b

where a.课程号=b.课程号 and 成绩>90 and 开课学期='3';




(5) 将视图V_student删除。SQL语句如下。



drop view V_student;




小结
本章详细介绍了简单查询、连接查询、子查询、联合查询和视图的创建与管理。数据查询是数据库系统中最常用，也是最重要的功能，它为用户快速、方便地使用数据库中的数据提供了一种有效的方法。视图是根据用户的需求而定义的从基本表导出的虚表。通过本章的学习，重点掌握数据查询的各种方法(简单查询、连接查询、子查询、联合查询)，同时掌握视图的创建和管理。应注重培养良好的学习习惯： 勤于思考，善于学习，勇于实践，敢于创新。
思考与实践
1. 选择题
(1) 要查询学生信息表中学生姓“张”的学生情况，可用()命令。

A. select * from 学生信息表 where 姓名 like '张%'
B.  select * from 学生信息表 where 姓名 like '张_'
C.  select * from 学生信息表 where 姓名 like '%张%' 
D.  select * from 学生信息表 where 姓名='张'
(2) 使用关键字()可以把查询结果中的重复行屏蔽。
A. DISTINCTB. UNION
C.  ALLD. TOP
(3) WHERE子句的条件表达式中，可以匹配0个到多个字符的通配符是()。
A. _B. ％
C.  +D. $
(4) 模式查找like '_a%',下面哪个结果是可能的？()
A. aissB. eai
C.  hbaD. dda
(5) 在SQL中，SELECT语句的“SELECT DISTINCT”表示查询结果中()。 
A. 属性名都不相同B. 去掉了重复的列 
C.  行都不相同D. 属性值都不相同  
(6) 与where g between 60 and 80语句等价的子句是()。
A. where g>60 and g<80
B.  where g>=60 and g<80
C.  where g>60 and g<=80
D.  where g>=60 and g<=80
(7) 当两个子查询的结果()时，可以执行并、交、差操作。
A. 结构完全不一致B. 结构完全一致
C.  结构部分一致D. 主键一致
(8) MySQL中表查询的命令是()。
A. USEB. SELECTC. UPDATED. DROP 
(9) 表示职称为副教授同时性别为男的表达式为()。
A. 职称='副教授' OR 性别='男'B. 职称='副教授' AND 性别='男'
C.  BETWEEN '副教授' AND '男'D. IN ('副教授','男')
(10) 查询员工工资信息时，结果按工资降序排列，正确的是()。

A. ORDER BY 工资B. ORDER BY 工资 desc
C.  ORDER BY 工资 ascD. ORDER BY 工资 dictinct
(11) 使用SQL创建视图时，不能使用的关键字是()。
A. ORDER BYB. WHERE
C.  COMPUTED. WITH CHECK OPTION
(12) 在SQL中，CREATE VIEW语句用于建立视图。如果要求对视图更新时必须满足于查询中的表达式，应当在该语句中使用()短语。
A. WITH UPDATEB. WITH INSERT
C.  WITH DELETED. WITH CHECK OPTION
(13) 数据库中只存放视图的()。
A. 操作B. 对应的数据
C.  定义D. 限制
(14) SQL的视图是从()中导出的。
A. 基本表B. 视图 
C.  基本表或视图D. 数据库
(15) 以下关于视图的描述，错误的是()。
A. 视图是从一个或几个基本表或视图导出的虚表
B.  视图并不实际存储数据，只在数据字典中保存其逻辑定义
C.  视图里面的任何数据不可以进行修改
D.  SQL中的SELECT语句可以像对基表一样，对视图进行查询
(16) 在视图上不能完成的操作是()。
A. 更新视图B. 查询
C.  在视图上定义新的基本表D. 在视图上定义新视图
(17) 在SQL中，删除一个视图的命令是()。
A. DELETEB. DROPC. CLEARD. REMOVE
2. 填空题
(1) 用SELECT进行模式匹配时，可以使用like或not like匹配符，但要在条件值中使用()或()等通配符来配合查询。并且模式匹配只能针对()类型字段查询。
(2) 检索姓名字段中含有'娟'的表达式为 ： 姓名 like ()。
(3) HAVING子句与WHERE子句很相似，其区别在于： WHERE子句作用的对象是()，HAVING子句作用的对象是()。
(4) 视图是从()中导出的表，数据库中实际存放的是视图的()，而不是()。
(5) 当对视图进行UPDATE、INSERT和DELETE操作时，为了保证被操作的行满足视图定义中子查询语句的谓词条件，应在视图定义语句中使用可选择项()。
(6) 如果在视图中删除或修改一条记录，则相应的()也随着视图更新。
3. 实践题
(1) 创建信息表info(编号，姓名，出生日期，职业)并添加数据。查询信息表info中姓钟且年龄大于80岁的人员信息。
(2) 创建中国城市信息表city(编号，城市名称，分值)并添加数据。查询该表，确定中国十大最美城市的排名。
(3) 创建工匠信息表craftsman(工号，姓名，性别，职务/职称，单位)、行业信息表industry(编号，行业名称，行业描述，岗位)和工匠与行业关联表ci(工号，编号，贡献) ，并为三个表添加数据。根据某位大国工匠的姓名，查询其岗位和所做贡献的信息。
(4) 根据第（3）题中的数据表，查询在制造业工作且岗位为焊工的大国工匠的姓名和所做贡献的信息。
(5) 计算学号为201907003的学生的总分和平均分。
(6) 查询课程号为07003的课程成绩低于60分的学生的学号信息。
(7) 用两种方法查询既没有选修课程号为07002的课程，也没有选修课程号为07004的课程的学生的学号、课程号和成绩信息。
(8) 查询有两门以上的课程成绩在80分以上的学生的姓名信息。