第5章数据库的查询和视图 数据查询是数据库系统应用的主要内容,保存数据就是为了使用,使用数据首先要查 找到需要的数据。在T-SQL 中使用SELECT 语句实现数据查询。用户通过SELECT 语句可以从数据库中搜寻用户所需要的数据,也可以进行数据的统计汇总并返回给用户。 SELECT 语句是数据库操作中使用频率最高的语句,是SQL 的“灵魂”。 通过学习本章,读者应掌握以下内容: .掌握各种查询方法的语法格式和使用,包括单表条件查询、单表多条件查询、多表 多条件查询、嵌套查询,并能对查询结果进行排序、分组和汇总操作; .掌握视图的建立、修改、使用和删除操作,并能通过视图查询数据、修改数据、更新 数据和删除数据。 本章将以jxgl数据库为例,在“学生信息”“课程”“成绩”“系部”等表的基础上介绍有 关数据查询的技术,包括基本查询和高级查询。在进行查询之前,各表中应当已输入相应 记录,各表中的记录分别如图5.5所示。 1~图5. 图5.“学生信息”表记录 1 图5.“课程”表记录 2 图5.“成绩”表记录 3 图5.“系部”表记录 4 104 图5.“教师信息”表记录 5 105 5.1 简单SELECT语句 简单查询是按照一定的条件在单表上查询数据,还包括汇总查询以及查询结果的排 序与保存。 5.1.1 SELECT语句概述 查询是指对已经存在于数据库中的数据按特定的组合、条件或次序进行检索,从数据 库中获取数据和操作数据的过程。查询功能是数据库最基本也是最重要的功能。 查询是SQL中最主要、最核心的部分。查询语言用来对已经存在于数据库的数据按 照特定组合、条件表达式或者一定的次序进行检索。 数据查询命令是SQL最常用的命令,由于查询要求不同而有各种变化,因此查询命 令也是最复杂的命令。其基本格式是由SELECT 子句、FROM 子句和WHERE子句组 成的SQL查询语句。 5.1.2 完整的SELECT语句的基本语法格式 SELECT语句的完整语法格式如下: SELECT [ALL|DISTINCT][TOP n [PERCENT]]select_list [INTO new_table_name] FROM <table_name/view_name> [WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression[ASC|DESC]][,...n] 对其中各参数的说明如下。 (1)ALL:表示输出所有记录,包括重复记录。 (2)DISTINCT:表示去掉重复的记录。 (3)TOPn:查询结果只显示表中的前n 条记录,TOPn PERCENT关键字表示查 询结果只显示前面n%条记录。 (4)select_list:所要查询的选项的集合,多个选项之间用逗号分开。 (5)INTOnew_table_name:用于指定使用结果集创建一个新表,new_table_name 是新表名。 (6)FROM <table_name/view_name>:结果集数据来源于哪些表或视图,FROM 子句还可包含连接的定义。 (7)WHEREsearch_condition:一个条件筛选,只有符合条件的行才向结果集提供 数据,不符合条件的行中的数据不会被使用。 简单SELECT 语句 106 (8)GROUPBYgroup_by_expression:根据group_by_expression列中的值将查询 结果进行分组。 (9)HAVINGsearch_condition:应用于结果集的附加筛选。从逻辑上讲,HAVING 子句从中间结果集对行进行筛选,这些中间结果集是用SELECT 语句中的FROM、 WHERE或GROUPBY子句创建的。HAVING 子句通常与GROUPBY子句一起使用。 (10)ORDERBYorder_expression[ASC|DESC]:定义结果集中行的排列顺序。 order_expression指定组成排序列表的结果集的列。ASC指定行按升序排序,DESC指 定行按降序排序。 SELECT语句可以完成以下工作。 . 投影:用来选择表中的列。 . 选择:用来选择表中的行。 . 连接:将两个关系拼接成一个关系。 SELECT语句的功能为从FROM 列出的数据源表中找出满足WHERE检索条件的 记录,按SELECT子句的字段列表输出查询结果表,在查询结果表中可进行分组与排序。 说明:在SELECT语句中,SELECT子句和FROM子句是不可缺少的,其余的是可选的。 5.1.3 基本的SELECT语句 SELECT语句的基本形式如下: SELECT [ALL|DISTINCT][TOP n [PERCENT]]select_list FROM <table_name/view_name> [WHERE search_condition] 1.查询表中的若干列 在很多情况下,用户只对表中的一部分属性感兴趣,这时可以在SELECT 子句的 <字段列表>中指定要查询的属性。 【例5.1】 在“学生信息”表中查询学生的学号及姓名。 USE jxgl GO SELECT stu_id,stu_name FROM 学生信息 GO 查询结果如图5.6所示。 2.查询表中的全部列 将表中的所有属性都选出来有两种方法:一种方法是在SELECT 命令后面列出所 有列名;另一种方法是如果列的显示顺序与其在基表中的顺序相同,也可以简单地将<字 107 段列表>简写为“*”。 【例5.2】 查询“课程”表的所有信息。 USE jxgl GO SELECT* FROM 课程 GO 查询结果如图5.7所示。 图5.6 查询“学生信息”指定列的结果 图5.7 查询“课程”表的全部列 3.设置字段别名 T-SQL提供了在SELECT语句中操作别名的方法。用户可以根据实际需要对查询 数据的列标题进行修改,或者为没有标题的列加上临时标题。其语法格式如下: 列表达式[AS]别名 或 别名=列表达式 【例5.3】 查询jxgl数据库的“课程”表,列出表中的所有记录,每个记录的名称依次 为课程号、课程名、课程学分和学时数。 USE jxgl GO SELECT course _id AS 课程号, course _name AS 课程名, course _credit AS 课程学分, 108 course_hour AS 学时数 FROM 课程 GO 或 SELECT 课程号= course_id,课程名= course_name,课程学分= course_credit,学时数= course_hour FROM 课程 GO 查询结果如图5.8所示。 图5.8 显示字段别名 4.查询经过计算的值 SELECT子句中的<select_list>不仅可以是表中的属性列,也可以是表达式,包括 字符串常量、函数等,其语法格式如下: 计算字段名=表达式 【例5.4】 查询“学生信息”表中所有学生的学号、姓名及年龄。 在本例的查询操作中应该使用“学生信息”表中的stu_birth字段值计算得到,这里需 要用到两个函数,一个是取得当前系统日期的函数GETDATE(),另一个是计算两个日 期型量之差的函数DATEDIFF(),本例计算当前日期与学生出生时间之间年份的差值, 通过这种方式得到学生的年龄。 USE jxgl GO SELECT stu_id AS 学号,stu_name AS 姓名,年龄=DATEDIFF(YY,stu_birth,GETDATE()) FROM 学生信息 109 GO 查询结果如图5.9所示。 图5.9 显示经过计算的年龄字段 5.返回全部记录 如果要返回全部记录可在SELECT后使用ALL,ALL是默认设置,因此也可以省略。 【例5.5】 查询“学生信息”表中所有学生的系别代码。 USE jxgl GO SELECT dept_id FROM 学生信息 GO 部分查询结果如图5.10所示。 6.过滤重复记录 在例5.5的执行结果集中显示了重复行。如果让重复行只显示一次,需在SELECT 子句中用过滤重复记录(DISTINCT)指定在结果集中只能显示唯一行。 【例5.6】 查询“学生信息”表中的学生所在系别有哪些(重复专业只显示一次)。 USE jxgl GO 110 SELECT DISTINCT dept_id FROM 学生信息 GO 查询结果如图5.11所示。 图5.10 显示所有学生的系别代码 图5.11 过滤重复记录后的显示结果 注意:在使用DISTINCT关键字后,如果表中有多个为NULL的数据,服务器会把 这些数据视为相同。 7.仅返回前面若干记录 其语法格式如下: SELECT [TOP n |TOP n PERCENT]select_list FROM table_name 其中,TOPn 表示返回最前面的n 行,n 表示返回的行数; TOPn PERCENT表示返回最前面的n%行。 【例5.7】 查询“学生信息”表中的前5条记录。 USE jxgl GO SELECT TOP 5* FROM 学生信息 GO 【例5.8】 查询“学生信息”表中前面的10%行记录。 111 USE jxgl GO SELECT TOP 10 PERCENT* FROM 学生信息 GO 运行例5.7将返回5条记录,运行例5.8会返回“学生信息”表最前面10%条记录,即 前两条记录。 注意:TOP子句不能和DISTINCT关键字同时使用。 5.1.4 INTO 子句 使用INTO子句允许用户自定义一个新表,并且把SELECT子句的数据插入新表中。 在使用INTO 子句插入数据时,应注意以下4点。 . 新表不能存在,否则会产生错误信息。 . 新表中的列和行是基于查询结果集的。 . 使用该子句必须在目的数据库中具有CREATETABLE权限。 . 如果新表名称的开头为“#”,则生成的是临时表。 注意:使用INTO 子句,通过在WHERE子句中设置FALSE条件,可以创建一个和 源表结构相同的空表。 【例5.9】 创建一个和“学生信息”表结构相同的xs_new表。 USE jxgl GO SELECT*INTO xs_new FROM 学生信息 WHERE 6>8 GO 设置WHERE6>8这样一个明显为逻辑否的条件的目的是只保留“学生信息”表的 结构,而不返回任何记录。 【例5.10】 查询所有女生的信息并将结果保存在名为“女生表”的数据表中。 USE jxgl GO SELECT*INTO 女生表 FROM 学生信息 WHERE stu_sex='女' GO 【例5.11】 查询所有男生的信息并将结果存入临时表中。 USE jxgl GO SELECT*INTO #男生表 112 FROM 学生信息 WHERE stu_sex='男' GO 查看临时表的内容可用下面的语句: SELECT*FROM #男生表 查询结果如图5.12所示。 图5.12 查询临时表的结果 5.1.5 WHERE子句 WHERE子句获取FROM 子句返回的值(在虚拟表中),并且应用WHERE子句中定义 的搜索条件。WHERE子句相当于从FROM 子句返回结果的筛选器,每行都要根据搜索条 件进行评估,评估为真的那些行作为查询结果的一部分返回,评估为未知或假的那些行不出 现在结果中。条件查询就是关系运算的选择运算,就是对数据源进行水平分割。 使用WHERE子句可以限制查询的记录范围。在使用时,WHERE子句必须紧跟在 FROM 子句后面。WHERE子句中的条件是一个逻辑表达式,其中可以包含的运算符如 表5.1所示。 表5.1 查询条件中常用的运算符 运 算 符用 途 =、<、>、<>、!>、!<、>=、<=、!= 比较大小 AND、OR、NOT 设置多重条件 BETWEEN…AND… 确定范围