Access 2016数据库教程课件

第5章SQL查询第5章SQL查询【学习目标】掌握SELECT语句的格式。掌握使用SELECT语句创建单表查询和多表查询。掌握使用SQL实现数据定义、数据操作和特定查询。结构化查询语言（StructureQueryLanguage，SQL）是操作关系数据库的标准语言，SQL具有使用方便、功能强大的特点，因此应用广泛。SQL查询是使用SQL语句创建的查询。查询对象本质上是一条SQL语言编写的命令。SELECT语句SELECT语句是对关系数据库的表作选择查询的一个命令，可以返回指定的数据表中的全部或部分满足条件的记录。第5章SQL查询设计视图SQL视图5.1SQL视图在“SQL视图”中，用户可以直接输入SQL语句或编辑已有的SQL语句，保存后同样可以得到一个查询对象。SELECT[ALL|DISTINCT|TOPN]*|<字段名称>[AS别名][,<字段名称>[AS别名],…]FROM<表名>|<查询名>[WHERE<条件表达式>][GROUPBY<字段名称>[,<字段名称>,…][HAVING<条件表达式>]][ORDERBY<字段名称>[ASC|DESC][,<字段名称>[ASC|DESC],…]]功能：从FROM子句指定的表中返回一个满足WHERE子句指定条件的记录集，该记录集中只包含SELECT语句中指定的字段。5.2.1SELECT语句1．SELECT语句的一般格式命令说明：（1）ALL：查询结果是满足条件的全部记录，默认值是ALL。（2）字段名称：字段名称之间使用“,”分隔。

字段可以来自单个表，也可以来自多个表。

多个表的字段格式为：表名.字段名称（3）FROM：指定查询的数据源。可以是单表，也可是多表。（4）WHERE：指定查询的条件。（5）GROUPBY：用于对查询结果进行分组。（6）ORDERBY：用于对查询结果进行排序。ASC表示升序，是默认值DESC表示降序5.2.1SELECT语句Select语句的执行顺序：From

Where

GroupBy

Having

OrderBy

Select5.2.1SELECT语句SQL语句的所有子句既可以写在同一行中，也可以分成多行书写。命令中的大写字母与小写字母含义相同。【例5-1】查询学生表中所有学生的学号、姓名和出生日期。Select学号，姓名，出生日期From学生表SQL命令中的字母大写与小写含义相同。5.2.1SELECT语句2．使用SELECT语句创建简单查询【例5-2】查询“课程表”中全部记录。Select*From课程表SelectDistinct班级From学生表消除查询结果中重复的记录【例5-3】查询“学生表”中所包含的班级名称。5.2.1SELECT语句【例5-4】查询学生的学号、姓名和年龄Select学号,姓名,Year(date())-year(出生日期)As年龄From学生表查询的列可以是计算表达式。可以使用As指定显示结果列标题名称。5.2.1SELECT语句【例5-5】查询学生表中入学总分前3名学生的学号、姓名、院系代码和入学总分。SelectTop3

学号,姓名,院系代码,入学总分From学生表OrderBy入学总分DescASC：升序，是默认值DESC：降序5.2.1SELECT语句【例5-6】查询学生表中所有女生的学号、姓名和性别。Select学号，姓名，性别From学生表

Where性别=“女"指定查询条件5.2.1SELECT语句Select学号,姓名,性别,入学总分

From学生表

WhereLeft(姓名,1)=“李”And入学总分>=600【例5-7】查询学生表中入学总分在600（含）以上的姓李的学生的学号、姓名、性别和入学总分。5.2.1SELECT语句在实际应用中，往往不仅要求将表中的记录查询出来，还需要在原有数据的基础上，通过计算来输出统计结果。函数名功能Sum()对数字型字段求和Avg()对数字型字段求均值Count()统计表中记录个数Max()求最大值Min()求最小值First()返回分组记录中的第一条记录中的字段值Last()返回分组记录中的最后一条记录中的字段值5.2.2数据分组和聚合函数【例5-8】统计学生表中不同性别的学生人数。Select性别,Count(学号)As人数From学生表Groupby性别使用GroupBy子句后Select子句中只能有GroupBy中指定的列，其他列必须使用聚合函数。GroupBy班级GroupBy院系代码5.2.2数据分组和聚合函数使用GroupBy子句可以对表中记录进行分组，然后使用聚合函数进行分组统计。5.2.2数据分组和聚合函数使用“GroupBy”子句后，“Select”子句中除了“GroupBy”中指定的字段，其他字段必须使用聚合函数，形式如下。SelectA,聚合函数,

,聚合函数From表名GroupByA错误！【例5-9】在学生表中按照院系代码统计不同性别的学生人数。Select性别,政治面貌,Count(学号)As人数From学生表Groupby性别,政治面貌GroupBy子句先按照“性别”字段进行分组，具有相同性别的记录被分配在同一个组中；然后分别在男组和女组内按照“政治面貌”再次分组。Select前两个字段与GroupBy指定的两个分组字段是一致的，第三个使用聚合函数Count()。5.2.2数据分组和聚合函数Select院系代码,性别,Count(学号)As人数From学生表GroupBy院系代码【例5-10】在学生表中统计男女生入学总分的最高分、最低分和平均值（保留2位小数）Select性别,Max(入学总分)As最高分,Min(入学总分)As最低分,Round(Avg(入学总分),2)As平均值From学生表GroupBy性别四舍五入函数5.2.2数据分组和聚合函数如果查询结果的字段来自多张不同的表，则需要通过连接运算将多张表进行连接。连接运算主要分成：内连接外连接5.2.3多表连接查询5.2.3多表连接查询1．内连接内连接是应用最广泛的连接运算，结果只包含两张表中连接字段值相同的记录行，是等值连接。使用“InnerJoin”就可以将两张表内连接在一起。所以使用SQL创建查询前，不需要先建立表间的关联关系。表名.字段名称对于多张表中共有的字段，该字段名称前必须加表名，中间用“.”间隔，格式为“表名.字段名称”。对于非共有的字段，可以直接写字段名称。【例5-12】按院系名称统计学生表中男学生的入学平均分，并按照平均分降序显示。Select院系名称,Avg(入学总分)As平均分From院系代码表InnerJoin学生表On院系代码表.院系代码=学生表.院系代码Where性别="男"GroupBy院系名称OrderByavg(入学总分)DESC由于“院系代码”是学生表和院系代码表共有的字段，所以必须加上表名;其他字段均不同名,可以省略表名按照语句的执行顺序，先执行InnerJoin内连接操作，将“院系代码表”和“学生表”按院系代码字段进行等值连接5.2.3多表连接查询5.2.3多表连接查询2．外连接外连接是从一个表中选择全部的记录，从另一个表中选择与连接字段匹配的记录行。外连接的方式有两种：左连接右连接Select院系名称,学号,姓名From院系代码表

LeftJoin学生表On院系代码表.院系代码=学生表.院系代码左外连接中显示出了所有的院系名称，包含没有学生的“可再生能源学院”，与其对应的学生表中的“学号”“姓名”字段均为空。5.2.3多表连接查询Select课程编号,成绩,学生表.学号,姓名From选课成绩表

RightJoin学生表On选课成绩表.学号=学生表.学号右外连接表示包含“学生表”的全部记录，以及“选课成绩表”中具有相同“学号”的记录。使用该方法可以查询出没有选课的学生。可以看出，有“张虎”等5位学生没有选课。【例5-13】查询每门课程的平均分（保留1位小数）、最高分和最低分。Select课程名称,Round(Avg(成绩),1)As平均分,Max(成绩)As最高分,Min(成绩)As最低分From课程表InnerJoin选课成绩表On课程表.课程编号=选课成绩表.课程编号GroupBy课程名称涉及到“选课成绩表”和“课程表”，先执行InnerJoin内连接操作将两张表进行等值连接然后使用GroupBy子句按照课程名称进行分组再计算每个组内包含成绩的平均值、最大值和最小值。5.2.3SELECT多表连接查询【例5-14】查询每个学生的学号、姓名和平均成绩（保留2位小数），查询结果按平均成绩降序排序。Select学生表.学号,First(学生表.姓名)As姓名,Round(Avg(成绩),2)As平均成绩From学生表InnerJoin选课成绩表On学生表.学号=选课成绩表.学号GroupBy学生表.学号OrderByRound(Avg(成绩),2)DescSelect中的字段第一个与GroupBy子句的分组字段相同，其后必须使用聚合函数。“GroupBy学生表.学号”指定按照“学号”分组，相同“学号”中的“姓名”是一致的，所以使用聚合函数First(学生表.姓名)求出第一个姓名显示（或Last(学生表.姓名)）5.2.3SELECT多表连接查询【例5-15】查询学生选修课程成绩，要求显示学号、姓名、课程名称和成绩。Select学生表.学号,姓名,课程名称,成绩From课程表InnerJoin(学生表InnerJoin选课成绩表On学生表.学号=选课成绩表.学号)On课程表.课程编号=选课成绩表.课程编号涉及到“学生表”“课程表”和“选课成绩表”3张表1、先执行：学生表InnerJoin选课成绩表On学生表.学号=选课成绩表.学号，产生中间结果2、再执行：InnerJoin(中间结果)On课程表.课程编号=选课成绩表.课程编号，完成3张表内连接5.2.3SELECT多表连接查询每位学生的每门课程都会产生一条结果数据，导致查询结果数据较多【例5-16】查询通过了四门课程的学生，要求显示学号、姓名和通过课程数。5.2.3SELECT多表连接查询Select学生表.学号,First(学生表.姓名)As姓名,Count(成绩)As通过课程数From学生表InnerJoin选课成绩表On学生表.学号=选课成绩表.学号Where成绩>=60GroupBy学生表.学号HavingCount(成绩)>=4;

注意“Having”子句与“Where”子句的不同之处如下。（1）“Where”子句在“GroupBy”分组之前起作用，“Having”子句在“GroupBy”分组之后起作用。（2）“Where”子句作用于表，从表中选择满足条件的记录；“Having”子句作用于“GroupBy”分组，从分组中选择满足条件的组。筛选出及格的记录“Having”子句必须和“GroupBy”配合使用，用于指定约束条件。数据定义查询可以创建表删除表修改表5.3SQL数据定义创建表CREATETABLECREATETABLE<表名>(<字段名称1><数据类型>,<字段名称2><数据类型>[,…])【例5-17】创建一个名为“学生家庭情况”表。字段名称数据类型大小说

明学号短文本(Text)10主键(PrimaryKey)家庭住址短文本(Text)50

家庭年收入数字(Numeric)

5.3SQL数据定义CreateTable家庭情况表(学号Text(10),家庭住址Text(50),家庭年收入Numeric,PrimaryKey(学号))“学生家庭情况”表修改表ALTERTABLEALTERTABLE<表名>[ADDCOLUMN<新字段名称><数据类型>][DROP<字段名称>][ALTERCOLUMN<字段名称><数据类型>]5.3SQL数据定义【例5-18】为“家庭情况表”添加一个新字段，字段名称为“父亲工作单位”，短文本型，字段大小为40。AlterTable家庭情况表Add父亲工作单位Text(40)5.3SQL数据定义【例5-19】删除“学生家庭情况表”。DropTable学生家庭情况表表一旦被删除，其结构和记录都被删除，并且不可恢复删除表DROPTABLE

DROPTABLE<表名>5.3SQL数据定义1.插入记录INSERTINTO<表名>[(字段名称1[,字段名称2[,…]])]VALUES(值1[,值2[,….]])VALUES：指定表中新插入字段的具体值。其中各常量的数据类型及个数必须与对应字段的数据类型和个数一致。5.4SQL数据操作InsertInto课程表(课程编号,课程名称,开课状态)Values("10600201","大学计算机",True)【例5-20】在选课成绩表中添加一条记录。5.4SQL数据操作语法格式：UPDATE<表名>SET<字段名称1=值1[,字段名称2=值2,…]>WHERE<条件表达式>2.更新记录对指定表中满足<条件表达式>的记录进行修改。如果没有WHERE子句，则对指定表的全部记录进行修改。5.4SQL数据操作【例5-21】将“学生表副本”中“学号”字段4～6位是“104”的记录对应位修改为“999”，其他位保持不变。Update学生表副本Set学号=left(学号,3)+"999"+right(学号,4)Wheremid(学号,4,3)="104"UPDATE语句Left(学号,3)取前3位Right(学号,4)取最后4位3.删除语句语法格式：DELETEFROM<表名>WHERE<条件表达式>删除指定表中满足<条件表达式>的所有记录如果没有WHERE子句，则删除指定表的所有记录5.4SQL数据操作【例5-22】删除“女学生表”中全部的男生的记录。DeleteFrom女学生表

Where性别="男"5.4SQL数据操作命令格式：SELECT<字段列表>FROM<表名1>[，<表名2>]…[WHERE<条件表达式1>]UNIONSELECT<字段列表>FROM<表名A>[，<表名B>]…[WHERE<条件表达式2>]1．联合查询联合查询将两个以上的表或查询中的字段合并到一个集合中查看。使用联合查询可以合并多个表中的数据。5.5SQL特定查询命令说明（1）“UNION”运算符可以将前后两个SELECT语句的查询结果进行合并，生成一个数据集

（2）联合查询中的两个SELECT语句必须具有相同的字段列数，各列具有相同的数据类型。Select姓名,课程名称,成绩From不及格学生信息UnionSelect姓名,课程名称,成绩From例5-15查询Where成绩>=90OrderBy成绩Desc【例5-23】要求显示“不及格学生信息”表以及“例5-15查询”中所有成绩90（含）分以上的学生的姓名、课程名称和成绩，并按成绩的降序排列。5.5SQL特定查询Select姓名,课程名称,成绩From不及格学生信息UnionSelect姓名,课程名称,成绩From成绩查询Where成绩>=90OrderBy成绩Desc因为查询名称中有符号“-”，执行后系统会提示错误，所以先将“例5-15查询”复制后粘贴为名为“成绩查询”，再执行联合查询2．子查询子查询由另一个选择查询或操作查询的SELECT语句组成。在子查询中还可创建子查询，称为嵌套子查询。在SELECT语句中使用的子查询，是指嵌套于SELECT语句的WHERE子句中的SELECT语句。5.5SQL特定查询【例5-24】查询“学生表”中入学总分高于平均值的学生的学号、姓名和入学总分。Select学号,姓名,入学总分From学生表Where入学总分>=(SelectAvg(入学总分)From学生表)先执行5.5SQL特定查询子查询结果只有一条记录时，可用“=”、“>”、“<”、“<=”、“>=”等比较运算符子查询结果有多条记录时，用In运算符5.6课堂案例：学生成绩管理数据库的SQL查询1.单表SQL查询可以完成单表的条件查询，也可以实现统计计算功能的查询。【课堂案例5-1】查询学生表中“英语2001”“机械2001”“财务2001”这三个班学生的学号、姓名和班级。Select学号,姓名,班级From学生表Where班级In("英语2001","机械2001","财务2001")5.6课堂案例：学生成绩管理数据库的SQL查询【课堂案例5-2】查询学生表中“英语2001”“机械2001”“财务2001”这三个班的学生人数。Select班级,Count(学号)As人数From学生表Where班级In("英语2001","机械2001","财务2001")GroupBy班级5.6课堂案例：学生成绩管理数据库的SQL查询【课堂案例5-3】查询课程表中全部课程的课程编号、课程名称、学分和学时（假设1学分对应16学时），并按照学时的降序排列。Select课程编号,课程名称,学分,学分*16As学时From课程表OrderBy4Desc;课程表的设计为了满足3NF的要求，只保留了学分，学时可以通过表达式计算出来。因为计算表达式“学分*16As学时”位于第4列，所以可以使用“OrderBy4Desc”来表示按照计算出的学时的降序排列。但是不能写成“OrderBy学时Desc”，因为OrderBy子句先于Select执行。【课堂案例5-4】查询选课成绩在90分以上的学生学号、姓名、课程名称和成绩，并按成绩降序排列。Having子句必须和GroupBy配合使用，用于指定约束条件。5.6课堂案例：学