1、第5章 数据查询,本章教学概要,5.1 SELECT简单查询5.2 SELECT高级查询5.3 使用SQL Server Management Studio创建查询,5.1 SELECT简单查询,5.1.1 SELECT查询语句格式 5.1.2 数据表中列的筛选 5.1.3 数据表中记录的筛选 5.1.4 查询结果的排序 5.1.5 用查询结果生成新表 5.1.6 联合查询 5.1.7 实训项目9 简单查询,5.1.1 SELECT查询语句格式,SELECT语句的作用是让服务器从数据库中按用户的要求检索数据，并将结果以表格的形式返回给客户。它是SQL语言的核心。完整的SELECT语句比较复杂，
2、主要的子句语法如下： SELECT INTO FROM ,n WHERE GROUP BY ,n HAVING ORDER BY ,n,5.1.2 数据表中列的筛选,选择表中的列组成结果集合，其语法格式如下： SELECT ALL | DISTINCT TOP expression PERCENT WITH TIES := * | table_name | view_name | table_alias .* | column_name | expression AS column_alias | column_alias = expression ,.n ,5.1.2 数据表中列的筛选,1.
3、选择一个表中指定的列 使用SELECT语句选择一个表中的某些列时各列名之间要以逗号进行分割。语法格式为： SELECT column_name,column_name. FROM table_name WHERE search_condition【例5-1】：查询JXGL数据库的XS表中各个同学的XH、XM和XB,5.1.2 数据表中列的筛选,2.选择一个表中所有列 当在SELECT语句指定的位置上使用*号时，表示选表的所有的列。【例5-2】：查询JXGL数据库的XS表中的所有列3.在查询结果中显示字符串 在一些查询中，经常需要在检索结果中增加一些字符串来辅助说明相关查询结果，如要求查询结果为
4、： 计算机应用基础 课程代码为：CS001 SELECT 子句中，将要增加的字符串用单引号括起来，然后和列的名字写在一起 ，中间用分隔符逗号隔开。【例5-3】：从KC表中要查询出所有课程的名称和类别，并在类别前面显示字符串“类别为：”。,5.1.2 数据表中列的筛选,4.修改查询结果中的列标题 在显示结果时，可以指定以别名来代替原来的字段名称，总共有3种方法：采用“字段名称AS 别名”的格式。采用“字段名称 别名”的格式。采用“别名=字段名称”的格式，其中别名a用单引号括起来。【例5-4】：查询xs表中XH和XM字段，并在标题栏中显示“学号”和“姓名”字样，而不是显示XH和XM。5.替换查询结
5、果中的数据 在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据，例如学生学习某门课程成绩在查询结果中我们希望（优、良、及格和不及格）的方式显示。要替换查询结果中的数据，要使用CASE表达式。,5.1.2 数据表中列的筛选,6.查询经过计算的值 在查询的过程中，不仅可以直接以列的原始值作为结果，而且还可以以对列值经过计算所得的值作为查询结果输出，即SELECT语句可以用表达式作为结果。语法格式： SELECT expression,expression from table_name【例5-5】：在XS表中，每个学生都有一个CSRQ（出生日期），现要查询出每个同学的姓名和
6、年龄。7.消除结果集中的重复行 如果查询只选择出表中的部分列时，就可能会出现重复的行。例如，从JXGL数据库的XS表中只选择出XB和bjh，就会出现多行重复的情况。可以使用DISTINCT关键字来消除结果集中的重复行，其格式为： SELECT DISTINCT column_name,column_name from table_name【例5-6】：对XS表只选择XB和BJH,消除结果中的重复行,5.1.3 数据表中记录的筛选,数据表中记录的筛选，是通过在SELECT语句中使用*查询所有列以及使用WHERE子句指定选择的条件来实现的 1.限制结果集的返回行数 使用Top选项限制查询结果集返回
7、的行数。格式为：Select top npercent from table_name 其中：n是一个正整数，表示返回查询结果集的前n行；若带percent关键字，则表示返回结果集的前n%行。【例5-7】：从XS表中查询所有学生的相关信息，并分别返回结果集的前10条和前10%条记录,5.1.3 数据表中记录的筛选,2.查询满足条件的行 是通过WHERE子句构成相应的查询条件来实现的。在这一部分中将详细讨论WHERE子句中查询条件的构成，WHERE子句同样适用于上节所介绍的列的筛选。WHERE子句必须紧跟在FROM子句之后。其基本的语法格式为： WHERE 其中,search_condition
8、为查询条件，通常指定为逻辑表达式，结果集将返回表达式为真的数据行。可以用来构成查询条件的常用运算符及关键字如表5.1所示：,5.1.3 数据表中记录的筛选,2.查询满足条件的行 是通过WHERE子句构成相应的查询条件来实现的。 WHERE子句必须紧跟在FROM子句之后。其基本的语法格式为： WHERE 其中,search_condition为查询条件，通常指定为逻辑表达式。可以用来构成查询条件的常用运算符及关键字如下所示：比较： =、=、 范围：BETWEEN（在某个范围内） 、NOT BETWEEN（不在某个范围内） 列表：IN（在某个列表中）、NOT IN（不在某个列表中） 字符串匹配：L
9、IKE（和指定字符串匹配）、NOT LIKE（和指定字符串不匹配） 空值判断：IS NULL（为空）、 IS NOT NULL（不为空） 组合条件：AND（与）、 OR（或） 取反：NOT,5.1.3 数据表中记录的筛选,【例5-8】：在CJ（成绩）表中查询出ZP(课程成绩总评)不及格的同学的相关信息【例5-9】：从JXGL数据库KC表中查询出KCH包含“计算机”一词的课程情况 【例5-10】：在CJ表查询出课程成绩ZP在80分到90之间学生成绩情况 【例5-11】：在JXGL数据库的KC表中查询出KCH为：“CS001”、“ CS002”和“CS003”的课程信息【例5-12】：在JXGL数
10、据库的XS表中查询出BJH尚不确定的学生的相关信息。 【例5-13】：在JXGL数据库的XS表中查询出BJH为2班的女生的相关信息,5.1.4 查询结果的排序,在SELECT语句中，可以使用ORDER BY 子句对结果进行排序。该子句的格式为： ORDER BYorder_by_expressionASC|DESC,.n【例5-14】：在JXGL数据库的XS表中将班级编号（BJH）为4班的学生按学号由小到大排序 【例5-15】：在JXGL数据库的XS表中查询出BJH为4的同学的XH和XM(要求以中文别名“学号”和“姓名”显示)并以学号由小到大来排序 说明：除了可以使用列名和别名，也可以用一个表
11、示列在选择表中所处位置的正整数来做为order_by_expression，但是前提条件是必须知道排序的列在选择表所处的位置。【例5-16】：如果已知在XS表中CSRQ字段位于第4列，现在要将BJH为4的学生按出生日先后排序,5.1.5 用查询结果生成新表,SELECT INTO 语句用于创建一个新表，并用 SELECT 语句的结果集填充该表。SELECT INTO 可将几个表或视图中的数据组合成一个新表，新表的结构由选择列表中表达式的属性定义。【例5-17】：在JXGL数据库的XS表中查询出所有女生的XH、XM和XB,并将查询结果插入到新表STUDENT中语句如下： SELECT XH,XM
12、,XB INTO STUDENT FROM XS WHERE XB=0,5.1.6 联合查询,使用UNION子句可以将两个或更多查询的结果合并为单个结果集，该结果集包含联合查询中的所有查询的全部行。其格式为： | ( ) UNION ALL ) UNION ALL | ( ) .n 其中：query specification和query expression都是查询语句。使用UNION子句组合两个查询的结果集有两个基本规则：所有查询中的列数和列的顺序必须相同。对应列的数据类型必须兼容关键字ALL表示合并结果集中包括所有的行，不去除重复行。不使用ALL则在合并结果中去除重复行。【例5-18】：
13、查询出1989年以后出生的学生和1969年以后出生的老师的编号和姓名。,5.1.7实训项目9 简单查询,1.实训目的: 1）熟悉SELECT语句的基本语法，理解并掌握语句中各子句的含义和用法。 2）掌握使用SELECT语句编写简单的SQL查询，学会使用SELECT语句解决实际中的查询问题。2.实训内容: 在数据库JXGL上，完成下列操作：实验练习1：在查询编辑器中，查询出平时成绩PSCJ高于85但实践成绩SJCJ低于60的同学的学号和课程号。实验练习2：在查询编辑器中，分别输出BJ表中所有记录和前8条记录。实验练习3：在查询编辑器中，查询出类别为专业基础课或专业课的课程的课程号和课程名（要求：
14、使用IN关键字）。 实验练习4：在查询编辑器中，使用CASE语句输出每个学生的学号与总评分，要求总评分低于60分时显示不及格；60到70 之间则显示一般；70到80之间则显示还可以；80到90之间则显示良好；90分以上则显示优秀 （要求：使用汉字的别名）。,5.1.7实训项目9 简单查询,实验练习5：在查询编辑器中，从CJ表中输出学生的学号和总分，其中总分是由平时成绩*0.3+实践成绩*0.3+期末成绩*0.4计算确定，并且列名分别用学号和总分输出。实验练习6：在查询编辑器中，查询出姓张、李或王的学生的学号和姓名。实验练习7：在查询编辑器中，查询出出生日期为空值的教师的信息。实验练习8：在查询
15、编辑器中，从CJ表中查询出学习CS001课程的学生的XH和ZP，并按ZP降序排列。实验练习9：在查询编辑器中，查询出1989年以后出生的女生的XH和XM，并将查询结果插入新表STU。实验练习10： 查询出”网络091” 班的学生名单和学习”计算机网络基础”课程的学生的名单。（使用UNION子句）3.实训思考: 1）若需要从课程表中查询出课程号以A到F之间的一个字母打头的课程的课程号和课程名，对应的SQL语句应如何编写？ 2）如果想在查询结果中列出总评成绩比60分多多少，则SQL语句如何编写？,5.2 SELECT高级查询,5.2.1 数据汇总 5.2.2 联接查询 5.2.3 子查询 5.2.
16、4 实训项目10 高级查询,5.2.1 数据汇总,对表数据进行查询时，经常要对结果进行计算或汇总，例如在学生成绩表中查询某位同学说学各门课程的总分、统计某门课程各分数段的人数等。1.聚合函数聚合函数用于计算表中的数据，返回单个计算结果，常用来增强检索功能。T-SQL中常用的聚合函数如下： SUM 和AVG函数 MAX和MIN函数 COUNT函数,5.2.1 数据汇总,【例5-19】：求选修CS001课程的学生的平均成绩 【例5-20】：求选修CS001课程的学生的最高分和最低分【例5-21】：求XS表中学生的总人数,5.2.1 数据汇总,2. GROUP BY子句 GROUP BY子句用于对表
17、或视图的数据按字段分组。如果 SELECT 子句 中包含聚合函数，则 GROUP BY 将计算每组的汇总值。指定 GROUP BY 时，选择列表中任意非聚合表达式内的所有列都应包含在 GROUP BY 列表中，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。 【例5-22】：从XS表中查询出各班级的学生的人数 【例5-23】：在JXGL数据库中查询出每个同学的学号、姓名所学各门课程的总分,5.2.1 数据汇总,3. HAVING子句 在使用GROUP BY 子句和聚合对数据进行分组后，还可以使用HAVING子句指定组或聚合函数所应满足的搜索条件，即对分组数据进行附加筛选。 注意：当
18、 HAVING 与 GROUP BY ALL一起使用时，HAVING 子句优于 ALL。另外在 HAVING 子句中不能使用 text、image 和 ntext 数据 类型。【例5-24】：在JXGL数据库XS表中查询出班级男生人数大于等于2人的班级编号和男生人数。,5.2.1 数据汇总,4. COMPUTE 和COMPUTE BY子句 在SQL Server 2005提供COMPUTE 和COMPUTE BY子句是为了向后兼容。其语法格式为： COMPUTE AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM ( expr
19、ession ) ,.n BY expression ,.n 【例5-25】：使用COMPUTE子句汇总出专业基础课的总学时和总学分【例5-26】：使用COMPUTE BY 子句汇总出各类课程的总学时和总学分,5.2.2 联接查询,在查询的过程中很多时候需要从有关联的多个表中来进行查询，例如要查询出学习“计算机应用基础”课程的学生的学号姓名和成绩，查询的过程中要用到XS、KC和CJ表，这类查询称之为联接查询。联接查询主要包括内联接(INNER JOIN)、外联接(OUTER JOIN)和交叉联接(CROSS JOIN)等。1.内联接查询 内联接是一种最常用的联接类型。内联接查询实际上是一种任意
20、条件的查询 。格式一：在FROM子句中定义联接条件 SELECT 列的列表 FROM 表数据源 INNER JOIN 表数据源 ON 联接条件 WHERE 搜索条件,5.2.2 联接查询,格式二：在WHERE子句中定义联接条件 SELECT 列的列表 FROM 表数据源，表数据源 WHERE 搜索条件AND联接条件 另外内联接根据比较方式还可以分为：1）等值联接：在联接条件中使用等于号(=)运算符比较被联接列的列值，其查询结果中列出被联接表中的所有列，包括其中的重复列。2）不等联接：在联接条件使用除等于运算符以外的其它比较运算符比较被联接的列的列值。这些运算符包括、=、!。3）自然联接：在联接
21、条件中使用等于(=)运算符比较被联接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除联接表中的重复列。,5.2.2 联接查询,【例5-27】：查询出软件081班所有学生的相关信息及BJMC 说明： 从上例中可以看到在FROM和WHERE 子句中都可以指定内联接的联接条件，但是在FROM子句指定联接条件将有助于将联接条件和其他搜索条件分开，指定联接时建议使用在FROM子句指定的方法。 在内联接中，所有引用的列都必须明确。如上例中的BJH在两表中是重复的列，在引用的过程就必须要通过表名来限定。 上例中联接就是自然联接。,5.2.2 联接查询,2.外联接查询 外联接是只限制一张表中的数
22、据必须满足联接条件，而另一张表中的数据可以不满足联接条件的联接方式。外联接总共有3种：1）左外联接(LEFT OUNTER JOIN)：结果集合中除了包括满足联接条件的行外，还包括左表的所有行。2）右外联接(RIGHT OUTER JOIN)：结果集合中除了包括满足联接条件的行外，还包括右表的所有行。3）全外联接(FULL OUTER JOIN)：结果集合中除了包括满足联接条件的行外，还包括两个表的所有行。其中OUTER关键字均可省略。,5.2.2 联接查询,外联接查询的联接条件只能在FROM子句中指定具、体格式为： SELECT 列的列表 FROM 表数据源OUTER JOIN 表数据源 O
23、N 联接条件 WHERE 搜索条件【例5-28】：查询出每个学生的选课情况（包括已选课的学生和未选课的学生）,5.2.2 联接查询,3.交叉联接查询 交叉联接即笛卡尔乘积，结果表是由第一个表的每一行与第二个表的每一行拼接后形成的表。结果集合中记录的条数为两个表行数之乘积。 交叉联接查询的联接条件也只能在FROM子句中指定,具体格式为： SELECT 列的列表 FROM 表数据源CROSS JOIN 表数据源 【例5-29】：查询出所有学生的可能的选课情况。,5.2.3 子查询,子查询是一个 SELECT 语句，它嵌套在一个SELECT、INSERT、UPDATE 或 DELETE 语句中或嵌套
24、在另一子查询中。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。 在执行包含子查询的的语句时，系统先执行子查询，产生一个结果表，再执行外部查询。1.IN子查询 IN子查询用于进行一个给定值是否在子查询结果集中的判断【例5-30】：查询出选修了CS001课程的学生的学号和姓名,5.2.3 子查询,子查询是一个 SELECT 语句，它嵌套在一个SELECT、INSERT、UPDATE 或 DELETE 语句中或嵌套在另一子查询中。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。 在执行包含子查询的的语句时，系统先执行子查询，产生一个结果表，再
25、执行外部查询。1.IN子查询 IN子查询用于进行一个给定值是否在子查询结果集中的判断【例5-30】：查询出选修了CS001课程的学生的学号和姓名,5.2.3 子查询,2.比较子查询 比较子查询是由一个比较运算符（=、 =、! 或 =）引入的。与使用 IN 引入的子查询一样，由未修改的比较运算符（即后面不接 ANY 或 ALL 的比较运算符）引入的子查询必须返回单个值而不是值列表。 要使用由未修改的比较运算符引入的子查询，必须对数据和问题的本质非常熟悉，以了解该子查询实际是否只返回一个值。【例5-31】：查询出“C程序设计”课程成绩高于该门课平均成绩的学生姓名。【例5-32】：查询出比2班所有同
26、学年龄都大的学生的相关情况,5.2.3 子查询,3. EXISTS子查询使用 EXISTS 关键字引入一个子查询时，就相当于进行一次存在测试。外部查询的 WHERE 子句测试子查询返回的行是否存在。子查询实际上不产生任何数据，它只返回 TRUE 或 FALSE 值。【例5-33】：查询出所学课程成绩都及格的学生名单 注意：使用 EXISTS 引入的子查询在以下几方面与其他子查询略有不同： EXISTS 关键字前面没有列名、常量或其他表达式。由 EXISTS 引入的子查询的选择列表通常几乎都是由星号 (*) 组成。由于只是测试是否存在符合子查询中指定条件的行，所以不必列出列名。,5.2.4实训项
27、目10 高级查询,1.实训目的: 1）掌握GROUP BY 子句的使用； 2）熟悉COMPUTE和COMPUTE BY子句的使用； 3）掌握联接查询的方法； 4）掌握子查询的方法。2.实训内容: 在数据库JXGL上，完成下列操作： 实验练习1：查询出学习“计算机应用基础”课的学生的学号、姓名和成绩。 实验练习2：查询出每个同学的平均成绩（要求分别使用GROUP BY 和COMPUTE BY子句，并比较两者的区别）。 实验练习3：查询出比宋华同学年长的学生的学号和姓名。 实验练习4：查询出没有课程成绩记录的学生的学号和姓名。 实验练习5：查询出比所有男生都年幼的女生的学号和姓名3.实训思考:若需
28、要查询出至少有两门课成绩不及格的学生的学号和姓名，试思考可以有几种方法？,5.3使用SQL Server Management Studio创建查询,在SQL Server 2005中查询除了可以使用前面介绍的T-SQL语句来实现以外，还可以使用SQL Server Management Studio管理平台来创建查询，下面就以一个实例来介绍如何使用SQL Server Management Studio管理平台来创建相关查询。【例5-34】：使用Server Management Studio管理平台在JXGL数据库中查询每个学生的学号、姓名和出生日期。操作步骤如下：1）打开SQL Serv
29、er Management Studio管理平台2）展开服务器组展开服务器展开数据库展开JXGL数据库3）点击“表”，右击“XS”表，在系统弹出的快捷菜单上，分别点击“打开表”命令单击“查询设计器”菜单中“窗格”下的各选项或“查询设计器”工具栏上对应的按钮“”，弹出查询窗口，在窗口中设置如图5.35所示。4）查询字段及条件设置完成后，点击工具栏上的“”按钮，执行查询。,本章教学小结,本章介绍了数据查询的各种方法，从查询的复杂程度把查询分为简单查询和高级查询。简单查询包括SELECT查询语句的格式、数据表中列和记录的筛选、对查询结果进行排序、用查询的结果生成新表以及联合查询等。它们是对SELECT语句基本功能的应用。高级查询包括数据汇总、联接查询和子查询。它们是对SELECT语句功能的扩展。,
