数据库原理与应用快速入门 课件 第4章 查询数据

数据库原理与应用快速入门4.1了解SQL4.2实现单表查询4.3实现连接查询第4章

查询数据4.4实现嵌套查询4.5实现集合查询4.6查询综合应用4.1了解SQL4.1了解SQLSQL(StructuredQueryLanguage)称为结构化查询语言，它是专门应用于关系数据库中，实现对数据各种操作的语言。尽管名为查询语言，实际上SQL的功能包括查询、操纵、定义和控制四个方面。4.1了解SQL表4.1SQL的动词SQL功动词数据查询SELECT数据操纵INSERT，UPDATE，DELETE数据定义CREATE，DROP，ALTER数据控制GRANT，REVOKE4.1了解SQLSQL可以独立完成数据库生命周期中的全部活动，包括定义关系模式、输入数据、创建数据库、査询、更新、维护、重构数据库、数据库安全性控制等一系列操作，为数据库应用系统开发提供了良好的环境。在数据库系统投入运行后，SQL还可以根据需要随时修改模式，且不影响数据库的运行，从而使系统具有良好的可扩展性。4.1了解SQL使用SQL进行数据操作时，用户只需提出“要做什么”，而不必指明“怎么去做”。因此，用户无须了解数据存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。这样既大大减轻了用户的负担，也有利于提高数据的独立性。由于设计巧妙、语言简洁、接近英语口语的特点，使得SQL容易学习，也容易使用。关系数据库系统使用SQL语言作为共同的数据存取语言和标准接口，经久不衰。4.1了解SQL数据查询是数据库中核心且执行频繁的操作。SQL语言使用SELECT动词完成数据查询，其一般格式为：SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>，]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]谢谢！数据库原理与应用快速入门4.1了解SQL4.2实现单表查询4.3实现连接查询第4章

查询数据4.4实现嵌套查询4.5实现集合查询4.6查询综合应用4.2.1查询表中的列4.2.2消除结果中的重复行4.2实现单表查询4.2.3查询满足条件的记录4.2.4对查询结果排序4.2.5使用LIMIT限制查询结果的条数4.2.6使用聚合函数4.2.1查询表中的列4.2.2消除结果中的重复行4.2实现单表查询4.2.3查询满足条件的记录4.2.4对查询结果排序4.2.5使用LIMIT限制查询结果的条数4.2.6使用聚合函数4.2.7对查询结果分组4.2.1查询表中的列SELECT子句中的目标列表达式，可以是列名的列表，也可以是算术表达式或者函数。4.2.1查询表中的列【例4-1】查询全体学生的姓名、学号、班级。4.2.1查询表中的列【例4-2】查询全体学生的详细信息。4.2.1查询表中的列【例4-3】查询sc表中学生的学号以及每名学生加5分后的成绩。4.2.1查询表中的列【例4-4】查询每个学生的学号、姓名、班级、和出生的年份。4.2.2消除结果中的重复行【例4-5】查询选修了课程的学生的学号，要求相同的学号在查询结果中只显示一次。【注意】如果没有在SELECT子句中指定DISTINCT短语，则缺省为ALL,即保留结果表中取值重复的行。4.2.3查询满足条件的记录以上两节中，查询结果将表中的全部数据行按查询需求显示出来，然而在实际应用中，很多查询只需要将表中的部分数据行显示，这就是带有条件的查询。查询满足条件的记录通过WHERE子句来实现。4.2.3查询满足条件的记录表4.2常用的查询条件查询条件谓词比较=，>，<，>=，<=，!=，<>，!>，!<确定范围BETWEEN…AND…，NOTBETWEEN…AND…确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件AND，OR4.2.3查询满足条件的记录（1）比较大小读者需注意，部分比较运算符和我们在数学中常用的运算符表示方式不同，如>=（大于或等于），<=（小于或等于），!=（不等于），<>（不等于），!>（不大于），!<（不小于）等。4.2.3查询满足条件的记录（1）比较大小【例4-6】在sc表中查询选修成绩及格的学生记录。4.2.3查询满足条件的记录（1）比较大小【例4-7】查询students表中班级不是“软件技术”班的学生的学号和姓名。4.2.3查询满足条件的记录（1）比较大小【注意】在带有条件的查询中，表示查询条件的值时，有两类数据需要用单引号括起来。一类是字符串型数据，如char、varchar、enum等。上例中，因班级列的数据类型是字符串，所以列的值“软件技术”用单引号括起来了。另一类是日期和时间型数据，如datetime、date等。如表示出生年月日在1992年1月2日，表达式应该写作：出生年月日='1992-1-2'。4.2.3查询满足条件的记录（2）确定范围谓词BETWEEN…AND…和NOTBETWEEN…AND…用于查询表中某列的值在（或不在）指定范围内的记录，其中BETWEEN后是范围下限（即低值），AND后是范围的上限（即高值）。4.2.3查询满足条件的记录（2）确定范围【例4-8】在sc表中查询课程成绩为优秀的学生的学号、课程号和成绩（成绩在90—100分之间的为优秀）。4.2.3查询满足条件的记录（3）确定集合如果查询条件不是一个连续的范围，而是几个固定的值的集合，则需要使用谓词IN和NOTIN来表示。4.2.3查询满足条件的记录（3）确定集合【例4-9】在courses表中查询“操作系统”、“软件工程”和“数据结构”这三门课的详细信息。4.2.3查询满足条件的记录（4）字符匹配有些情况下，我们可能不清楚查询的具体条件是什么，例如，要查询姓“王”的学生的信息，但不清楚学生的名字叫什么。要查询“数据库”的课程信息，但不知道课程的全称是什么。此类查询需要用字符匹配来实现。字符匹配类似于Windows操作系统中的模糊查找。4.2.3查询满足条件的记录（4）字符匹配【例4-10】在学生表中查询姓“王”的学生的学号和姓名。4.2.3查询满足条件的记录（4）字符匹配为实现查询需求，WHERE子句中使用了谓词like加匹配串的格式。字符匹配的一般格式为：

[NOT]LIKE'匹配串'其含义是查找指定列的值与匹配串相匹配的记录。4.2.3查询满足条件的记录（4）字符匹配各部分的参数解释如下：匹配串：固定字符串或含通配符的字符串。当为固定字符串时，可以用=运算符取代LIKE，用!=或<>运算符取代NOTLIKE。通配符“%”(百分号)代表任意长度（长度可以为0）的字符串。例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addpb，ab等都满足该匹配串。通配符“_”(下横线)代表任意单个字符，例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串。4.2.3查询满足条件的记录（4）字符匹配【例4-11】查询姓“欧阳”且全名为四个汉字的学生的名字。4.2.3查询满足条件的记录（4）字符匹配【例4-12】查询名字中第二个字为“阳”字的学生姓名和学号。4.2.3查询满足条件的记录（4）字符匹配【例4-13】在students表中查询不姓“王”的所有学生的学号和姓名。4.2.3查询满足条件的记录（5）涉及空值的查询在实际应用中，如果我们需要查询某些列为空值的记录，应该如何表示呢？4.2.3查询满足条件的记录（5）涉及空值的查询【例4-14】因考试旷考，sc表中有学生某些课程的成绩为空值。查询成绩为空值的学生的学号和课程号。4.2.3查询满足条件的记录（5）涉及空值的查询【例4-14】因考试旷考，sc表中有学生某些课程的成绩为空值。查询成绩为空值的学生的学号和课程号。若将查询修改为“查询成绩不为空值的学生的学号和课程号”，则查询语句更改为：4.2.3查询满足条件的记录（6）多重条件查询如果查询的条件多于一个，我们称之为多重条件查询。多重条件查询中使用逻辑运算符AND和OR来联接多个查询条件。需要注意的是，AND的优先级高于OR，但用户可以用括号改变优先级。4.2.3查询满足条件的记录（6）多重条件查询【例4-15】查询学生表中计算机应用班的男生信息。4.2.3查询满足条件的记录（6）多重条件查询【例4-16】查询courses表中学时大于40或者学分大于3分的课程信息。4.2.3查询满足条件的记录（6）多重条件查询【例4-17】在students表中查询女生的学号和姓名，要求班级为计算机应用或者计算机网络班。4.2.4对查询结果排序所谓对查询结果排序，指的是根据排序规则，将查询结果按照指定的顺序（升序或者降序）排列显示。排序使用ORDERBY子句来实现。4.2.4对查询结果排序【例4-18】查询选修了001号课程的学生的学号和成绩，查询结果按成绩升序排列。4.2.4对查询结果排序【例4-19】查询students表中的学生信息，查询结果先按姓名升序排列，对于同名同姓的学生，再按出生年月日降序排列。4.2.5使用LIMIT限制查询结果的条数如果只需要返回查询结果的一部分，如前几行记录或指定的几行记录，可以使用LIMIT关键字来实现。LIMIT关键字的语法格式如下：

LIMIT[位置偏移量,]行数其中，第一个参数“位置偏移量”为可选参数，指明查询结果从哪一行开始显示。如果不指定位置偏移量，则会从查询结果的第一行开始显示。请注意，查询结果的第一条记录位置偏移量为0，第二条记录位置偏移量为1，以此类推。第二个参数行数表示要显示的记录行数。4.2.5使用LIMIT限制查询结果的条数【例4-20】查询students表的前五条记录。4.2.5使用LIMIT限制查询结果的条数【例4-21】查询students表中从第三条记录开始的三名学生的信息。4.2.6使用聚合函数为了方便用户对数据进行汇总和统计，SQL中提供了一系列聚合函数。本节介绍常用聚合函数（见表4.3所示）的使用方法。表4.3常用的聚合函数函数名作用COUNT()统计表中记录的总条数或表中某列包含数值的个数SUM()计算一列值的总和(此列数据类型必须是数值型)AVG()计算一列值的平均值(此列数据类型必须是数值型)MAX()求一列中的最大值MIN()求一列中的最小值4.2.6使用聚合函数（1）COUNT()函数COUNT()函数用于统计表中记录的总条数或表中某列包含数值的个数，其使用格式有三种：COUNT(*)：统计表中记录的总条数。COUNT(列名)：统计表中某列包含数值的个数，忽略空值。COUNT(DISTINCT列名)：统计表中某列包含数值的个数，忽略空值和重复值。4.2.6使用聚合函数（1）COUNT()函数【例4-22】查询学生的总人数。4.2.6使用聚合函数（1）COUNT()函数【例4-23】查询选修了课程的学生人数。4.2.6使用聚合函数（2）SUM()函数SUM()函数用于计算表中一列数据，其数值的总和。因为需要进行求和运算，此列的数据类型必须是数值型。4.2.6使用聚合函数（2）SUM()函数【例4-24】查询学号为“210101001”的学生所选修课程的总成绩（即选修成绩的总和）。4.2.6使用聚合函数（3）AVG()、MAX()和MIN()函数AVG()函数用于计算表中一列数据，其数值的平均数。因为需要进行求平均数运算，此列的数据类型必须是数值型。MAX()函数用于计算表中一列数据中的最大值。MIN()函数用于计算表中一列数据中的最小值。MAX()函数和MIN()函数的参数不一定是数值型数据，因为其他类型的数据也可以比较大小。4.2.6使用聚合函数（3）AVG()、MAX()和MIN()函数【例4-25】查询选修“001”号课程的学生的平均成绩，最高分和最低分。4.2.7对查询结果分组GROUPBY子句用于将查询结果按表中某一列或者多列的值分组，值相等的分为一组。对查询结果分组的目的是为了细化聚合函数的作用对象。其语法格式如下：SELECT<列名>,[聚合函数]FROM<表名或视图名>GROUPBY<列名>[HAVING<条件表达式>]4.2.7对查询结果分组【例4-26】统计students表中男女生各有多少人。4.2.7对查询结果分组【例4-27】查询选修了3门以上课程（包含3门）学生的学号和选修的课程数。4.2.7对查询结果分组【例4-28】查询男生超过10人的班级和人数。谢谢！数据库原理与应用快速入门4.1了解SQL4.2实现单表查询4.3实现连接查询第4章

查询数据4.4实现嵌套查询4.5实现集合查询4.6查询综合应用4.3.1理解连接查询的原理4.3.2实现内连接查询4.2实现连接查询4.2.3实现外连接查询4.3.1理解连接查询的原理为实现连接查询，读者需要首先理解连接查询的实现原理。为了方便表述，我们对student数据库中表的结构进行了简化，并填充了少量样例数据。简化后的students表、courses表、sc表分别如表4.4、表4.5、表4.6所示。表4.4studentsstuIdstuNamesex210101001李勇男210101002刘晨女

表4.5coursescorIdcorNamecredit001数据库4002数学4003英语3.5表4.6scstuIdcorIdscore21010100100185210101001002924.3.1理解连接查询的原理现要求查询学生选修课程的情况，在查询结果中显示students表的全部字段和sc表的全部字段。表4.7连接查询结果一stuIdstuNamesexstuIdcorIdscore210101001李勇男21010100100185210101001李勇男21010100100292

4.3.1理解连接查询的原理现要求查询学生选修课程的情况，在查询结果中显示students表的全部字段和sc表的全部字段。

4.3.1理解连接查询的原理现要求查询学生选修课程的情况，在查询结果中显示students表的全部字段和sc表的全部字段。查询处理过程是：将students表中的记录和sc表中的记录进行连接，两表学号相同的记录连接在一起并存入结果表，学号不同的记录在结果中去掉。

4.3.1理解连接查询的原理在连接查询中，这种查询称为内连接查询。内连接查询将各表中用于连接字段取值相等的行进行连接并存入结果表，相同字段取值不等的行在结果中去掉。

4.3.1理解连接查询的原理如果要查询学生选修课程的情况，查询结果要求显示students表、courses表和sc表三表的全部字段，如表4.8所示，又该如何处理呢？

表4.8连接查询结果二stuIdstuNamesexcorIdcorNamecreditstuIdcorIdscore210101001李勇男001数据库421010100100185210101001李勇男002数学4210101001002924.3.1理解连接查询的原理

4.3.1理解连接查询的原理如果想了解全部学生选修课程的情况，查询结果实现既显示选修了课程的学生情况，又显示没有选修课程的学生情况。对于没有选修课程的学生，其选修部分为空，查询结果如表4.9所示。

表4.9连接查询结果三stuIdstuNamesexstuIdcorIdscore210101001李勇男21010100100185210101001李勇男21010100100292210101002刘晨女

4.3.1理解连接查询的原理对于这种情况，内连接查询显然不能满足要求，需要使用外连接查询来解决。外连接分为左向外连接（简称左连接）、右向外连接（简称右连接）和完整外连接三种。本书介绍左连接和右连接查询两种情况。左连接和右连接的实现原理相同。连接查询：1、内连接

主外键2、外连接

：（1）左连接

（2）右连接

（3）完整外连接

4.3.1理解连接查询的原理左连接查询将两表相同字段取值相等的记录进行连接并存入结果表的同时，还将连接时左表未能连接的所有记录也存入结果表，这部分记录在结果表中右表字段的部分取空值。右连接则与左连接相反，将两表相同字段取值相等的记录连接并存入结果表的同时，还将连接时右表未能连接的所有记录也存入结果表，这部分记录在结果表中左表字段的部分取空值。

4.3.1理解连接查询的原理

4.3.1理解连接查询的原理

4.3.2

实现内连接查询内连接查询语句的基本格式为：SELECT<目标列表达式>[，<目标列表达式>，]…FROM表1INNERJOIN表2ON表1.列名1=表2.列名2[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]

4.3.2

实现内连接查询【例4-29】查询选修了001号课程且成绩及格的学生的学号和姓名。

4.3.2

实现内连接查询【例4-28】查询选修了课程的学生的情况，要求结果显示students表和sc表的全部字段。

4.3.2

实现内连接查询【例4-30】查询选修了“数据库”的学生的学号、姓名和成绩。

4.3.2

实现内连接查询【例4-31】查询“软件技术”或“计算机应用”班选修了“数据库”的学生的学号、姓名和成绩。

4.3.2

实现内连接查询【例4-32】查询每门课学生成绩的最高分、最低分和平均分，要求在结果中显示课程号和课程名。

4.3.2

实现内连接查询【例4-33】查询选修了课程“数据库”并且成绩优秀（85分以上为优秀）的学生人数。

4.3.3

实现外连接查询左连接查询语句的基本格式为：SELECT<目标列表达式>[，<目标列表达式>，]…FROM表1LEFTJOIN表2ON表1.列名1=表2.列名2[WHERE<条件表达式>]

4.3.3

实现外连接查询右连接查询语句的基本格式为：SELECT<目标列表达式>[，<目标列表达式>，]…FROM表1RIGHTJOIN表2ON表1.列名1=表2.列名2[WHERE<条件表达式>]

4.3.3

实现外连接查询【例4-35】查询没有选修任何课程的学生的学号、姓名和班级信息。

谢谢！数据库原理与应用快速入门4.1了解SQL4.2实现单表查询4.3实现连接查询第4章

查询数据4.4实现嵌套查询4.5实现集合查询4.6查询综合应用4.4.1带IN谓词的子查询4.4.2带比较运算符的子查询4.4实现嵌套查询4.4.3带ANY/ALL谓词的子查询4.4.4带EXISTS谓词的子查询4.4

实现嵌套查询在SQL中，有一类特殊格式的查询，即将一个Select-From-Where语句（称为一个查询块）嵌套在另一个查询的Where子句或Having短语的条件中，这类查询称为嵌套查询。SELECTstuNameFROMstudentsWHEREstuIdIN(SELECTstuId

FROMsc

WHEREcorId='001');

4.4

实现嵌套查询我们将上层查询块称为父查询，下层查询块称为子查询。嵌套查询的执行方法是由里向外执行，即每个子查询在上一级查询处理之前执行，子查询的结果用于建立父查询的查找条件。SQL语言允许多层嵌套，即子查询块还可以嵌套其他子查询块。需要注意的是，子查询的SELECT语句中不能使用ORDERBY子句，ORDERBY子句只能对最终查询结果排序。嵌套查询采用多个简单查询块构成复杂的查询，查询代码结构清晰，易于学习和使用。其中，父、子查询的连接谓词包括IN谓词、比较运算符、ANY/ALL、EXISTS等

4.4.1带IN谓词的子查询在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最常使用的谓词。

4.4.1带IN谓词的子查询【例4-36】查询选修了“数据结构”课的学生的学号和成绩。

4.4.1带IN谓词的子查询【例4-37】查询与刘晨同一个班的学生的信息。

4.4.2带比较运算符的子查询带比较运算符的子查询指父、子查询之间的连接谓词是=，>，<，>=，<=，!=，<>等比较运算符。

4.4.2带比较运算符的子查询【例4-38】查询students表中出生日期比刘晨晚的学生的信息。

4.4.3带ANY/ALL谓词的子查询带比较运算符的子查询常常和ANY、ALL谓词一起来使用。

表4.10带ANY/ALL谓词的子查询中常用的表达方式谓词说明>Any大于子查询结果中的某个值>All大于子查询结果中的所有值<Any小于子查询结果中的某个值<All小于子查询结果中的所有值>=Any大于等于子查询结果中的某个值>=All大于等于子查询结果中的所有值<=Any小于等于子查询结果中的某个值<=All小于等于子查询结果中的所有值=Any等于子查询结果中的某个值=All等于子查询结果中的所有值（通常没有实际意义）!=Any不等于子查询结果中的某有值!=All不等于子查询结果中的任何一个值4.4.3带ANY/ALL谓词的子查询【例4-39】查询sc表中比科目001的某些成绩高的记录。

4.4.3带ANY/ALL谓词的子查询【例4-39】查询sc表中比科目001的某些成绩高的记录。本例也可以用聚合函数来实现。SELECT*FROMscWHEREscore>(SELECTMIN(score)FROMscWHEREcorId='001')ANDcorId<>'001';

4.4.3带ANY/ALL谓词的子查询【例4-40】查询sc表中比科目001所有成绩都高的记录。

4.4.3带ANY/ALL谓词的子查询【例4-40】查询sc表中比科目001所有成绩都高的记录。本例同样可以用聚合函数实现。代码如下：SELECT*FROMscWHEREscore>(SELECTMAX(score)FROMscWHEREcorId='001')

ANDcorId<>'001';

4.4.3带ANY/ALL谓词的子查询

表4.11ANY/ALL谓词与聚合函数及IN谓词的等价转换关系

=<>!=<<=>>=ANYIN--<MAX<=MAX>MIN>=MINALL--NOTIN<MIN<=MIN>MAX>=MAX4.4.4带EXISTS谓词的子查询有些查询，当查询条件提到“全部”或“如果…就”等字眼时，我们用上面所学的查询难以实现或实现起来较麻烦，而使用带有EXISTS谓词的嵌套查询会更方便。EXISTS代表“存在”的意思。带有EXISTS谓词的子查询不返回任何数据，它产生逻辑真值“TRUE”或逻辑假值“FALSE”。

4.4.4带EXISTS谓词的子查询【例4-41】查询没有选修课程的学生记录。

谢谢！数据库原理与应用快速入门4.1了解SQL4.2实现单表查询4.3实现连接查询第4章

查询数据4.4实现嵌套查询4.5实现集合查询4.6查询综合应用4.5实现集合查询4.5

实现集合查询集合查询的应用并不算广泛，但有些情况下使用集合查询能很好地解决问题。所谓的集合，就是把多个SELECT语句的查询结果进行组合。集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。

4.5

实现集合查询【例4-42】查询计算机应用班的学生及出生日期在2003-9-1之前的学生。

4.5

实现集合查询【例4-43】查询软件技术班或者选修了002课程的学生。

4.5

实现集合查询【例4-44】查询计算机应用班的学生与出生日期在2003-9-1之后的学生的交集。

4.5

实现集合查询【例4-45】查询计算机应用班的学生与性别为男的学生的差集。

谢谢！数据库原理与应用快速入门4.1了解SQL4.2实现单表查询4.3实现连接查询第4章

查询数据4.4实现嵌套查询4.5实现集合查询4.6查询综合应用4.6查询综合应用4.6查询综合应用我们在处理一些较为复杂的查询时，可能无法通过一条查询语句解决所有问题。此时，往往需要将复杂的查询分解成几个较为简单的查询，通过这些查询依次、连续地执行，得出结果。前序查询的执行结果，要作为后续的查询的数据来源。那么，如何将前序查询的结果暂时保存下来呢？使用临时表是一种解决问题的办法。

4.6查询综合应用在MySQL中，临时表是一类特殊的表，它在当前用户下创建，当前会话内有效（一次会话可理解为用户从请求连接数据库，执行操作到退出的过程）。当前会话结束后，临时表自动被数据库系统删除。临时表在我们需要保存一些临时数据时是非常有用的。

4.6查询综合应用我们将前序查询的结果称为中间结果，这些中间结果只作为后续查询的数据来源，并不需要永久地保留，所以，可以将中间结果保存在临时表中，为后续查询所用。在MySQL中创建临时表的语法格式如下：CREATETEMPORARY