最新更新 热门文章
当前位置：首页>免费文档>文库资料>教育文库>5—单元5 数据查询与维护

5—单元5 数据查询与维护

更新时间：2024-01-20 05:15:01 阅读量： 教育文库 文档下载

说明：文章内容仅供预览，部分内容可能不全。下载后的文档，内容与下面显示的完全一致。下载之前请确认下面内容是否您想要的，是否完整无缺。

单元五 数据查询与维护

数据库管理系统DBMS具有数据定义、数据操纵、数据库维护、数据库管理四项功能。这四项功能也是数据库维护管理人员与设计人员的基本工作职责。前面的单元已经介绍了数据定义、数据库维护、数据库管理功能的具体实现。本单元将围绕学生管理系统数据库中的日常数据查询与维护工作，根据具体的数据操纵项目（项目使用的数据库参见附录A、附录B），讲述实现数据操纵功能的SQL语言。

5.1 数据查询语句（Select）

数据查询语句可完成对关系数据表的集合运算（并、交、差）、关系代数运算（投影、选择、连接），还可对数据表进行排序与统计汇总运算，如求某数据项的和、平均值、最大值、最小值等。因此，数据查询语句具有极强的查询与统计汇总功能，语句一般格式如下。

Select [All | Distinct] <目标表达式1>[，…，<目标表达式n>] From <表 | 视图名>[，<表 | 视图名>]… [Where <条件表达式>]

[Order By <表达式> [Asc | Desc]

[Group By <表达式列表> [Having <条件表达式>] ] 其中：

Select <目标表达式1>[，…，<目标表达式n>] 为投影运算； Where <条件表达式>为集合、选择与连接运算； Order By <表达式> [Asc | Desc] 为排序运算；

Group By <表达式列表> [Having <条件表达式>]为分组统计运算；

由上述语句格式看出，Select语句是SQL语言中最复杂的语句，同时也是最重要的语句。为此，将Select语句分为简单查询、连接查询、排序、分组统计、嵌套查询、相关子查询六节，采用由简单到复杂的方法来介绍。

5.1.1 简单查询

1．从数据表中选择列（投影运算）

实际上，从数据表中选择列就是关系代数中的投影运算，分为查询指定列、全部列与计算表达式列三种情况。

（1）查询指定列

语句格式：Select <列名1>，…，<列名n> From <表名>

- 136 -

【例5-1】在学生档案表tblStudent 中查询全体学生的学号与姓名。 Use Studentsys

Select Stud_Id , Stud_Name From tblStudent （2）查询全部列

语句格式：Select * From <表名>

【例5-2】在学生档案表tblStudent 中查询全体学生的所有字段信息。 Select * From tblStudent （3）计算表达式列

语句格式：Select <目标表达式1>，…，<目标表达式n> From <表名> 【例5-3】在学生档案表tblStudent 中查询全体学生的姓名及其年龄。 Select Stud_Name, Year(GetDate())-Year(Birth) From tblStudent

在目标表达式Year(Getdate())-Year(Birth)中，GetDate为系统当前日期，Birth为出生日期，用Year函数取出当前年份及出生年份，相减后得到学生的年龄。

目标表达式可采用别名表示方式，格式为：<列名> As <别名> 或 <列名> <别名> 在上例中采用别名方式的Select语句为：

Select Stud_Name As '姓名', Year(getdate())-Year(Birth) As '年龄' From tblStudent

或 Select Stud_Name 姓名, Year(getdate())-Year(Birth) 年龄

From tblStudent

说明：如果别名中没有空格符，则别名的单引号可以省略。 （4）消除取值重复的行

为了使投影运算后的列值不出现重复，可在列名前添加关键字。 语句格式：Select Distinct <列名1>，…，<列名n> From <表名> 【例5-4】 在班级编码表tblClass中查询出所有的系部编号。 Select Dpt_ID From tblClass

执行该语句后，将出现多个相同的系部编码。 在列名前增加关键字，语句改为： Select Distinct Dpt_ID From tblClass

执行该语句后，消除取值重复的行，各记录内容不同。 2．从表选择行（选择运算）

从数据表中选择行就是关系代数中的选择运算，选择运算是用Where条件子句实现行记录的选择。

语句格式：Select <列名> From <表名> Where <查询条件>

Where子句中常用的6种查询条件如表5-1所示，下面依次介绍这6种查询条件。

- 137 -

表5-1 常用查询条件

序号 1 2 3 4 5 6 查询条件 关系表达式 范围查询 集合查询 模糊查询 空值判断 逻辑表达式 运算符号 =、>、<、>=、<=、<>、!=、!<、!> Between、Not Between In、Not In Like、Not Like Is Null、Is Not Null And、Or、Not （1）关系表达式

关系表达式是将列名、常量等用关系运算符连接而成的式子。常用关系运算符有： =、>、<、>=、<=、<>、!=、!<、!>。后三种运算符分别表示不等于、不小于和不大于。 说明：对Char、Varchar、Text、Datetime和Smalldatetime类型常量要用单引号括起来。 【例5-5】在系部编码表tblDepart与学生成绩表tblScore中，编写满足下列条件的查询语句。

? 查询计算机系（系部编号为'30'）的专业名称。 Select DptMj_Name From tblMajor Where Dpt_Id = '30' ? 查询考试不及格学生的学号。

Select Distinct Stud_Id From tblScore Where Score <60

这里使用了Distinct短语，当一个学生有多门课程不及格，他的学号也只出现一次。 （2）用Between运算符进行范围查询

查询条件为：<表达式> [Not] Between <下界> And <上界>

查询结果为数据表中满足表达式值在（或不在） <下界> 与 <上界>之间的记录。 【例5-6】在学生档案表tblStudent中，编写满足下列条件的查询语句。 ? 查询年龄在20至28岁之间的学生的学号、姓名和年龄。 Select Stud_Id ,Stud_Name , Year(GetDate())-Year(Birth) As Age From tblStudent

Where Year(GetDate())-Year(Birth) Between 20 And 28

? 查询年龄不在20至28岁之间的学生的学号、姓名和年龄。 Select Stud_Id ,Stud_Name , Year(GetDate())-Year(Birth) As Age From tblStudent

Where Year(GetDate())-Year(Birth) Not Between 20 And 28 （3）用In运算符指定集合查询 查询条件为：<表达式> [Not] In 集合

- 138 -

查询结果为数据表中满足表达式值在集合（或不在集合）中的记录。 【例5-7】在专业编码表tblMajor中，编写满足下列条件的查询语句。

? 查询机械系（编号为'10 '）、电气系（编号为'20'）和计算机系（编号为'30 '）的专业

编号与名称。

Select DptMj_Id , DptMj_Name From tblMajor

Where UpperId In ('10 ','20 ','30 ')

? 查询不是机械系（编号为'10'）、电气系（编号为'20'），也不是计算机系（编号为'30'）

的专业编号与名称。 Select DptMj_Id , DptMj_Name From tblMajor

Where UpperId Not In ('10 ','20 ','30 ') （4）用Like运算符模糊查询

查询条件为：<表达式> Like ' [<通配符>]<字符串>[<通配符>]' 四种通配符所表示含义如表5-2所示。

表5-2 通配符

序号 1 2 3 4 通配符 % _（下划线） [ ] [^] 说明 代表任意多个字符 代表单个字符 代表指定范围内的单个字符 代表不在指定范围内的单个字符 【例5-8】在学生档案表tblStudent中，编写满足下列条件的查询语句。 ? 查询所有姓刘的学生的学号、姓名和性别。 Select Stud_Id , Stud_Name , Stud_Sex From tblStudent

Where Stud_Name Like '刘%'

? 查询姓名中第二个字为“阳”的学生的学号、姓名和性别。 Select Stud_Id , Stud_Name , Stud_Sex From tblStudent

Where Stud_Name Like '_阳%'

? 查询学号第一位不是1的学生的学号、姓名和性别。 Select Stud_Id , Stud_Name , Stud_Sex

- 139 -

From tblStudent

Where Stud_Id Like '[^1]%' 或 Select Stud_Id , Stud_Name , Stud_Sex From tblStudent

Where Stud_Id Not Like '1%' （5）空值（NULL）查询

空值查询条件为：<字段名> Is Null 非空查询条件为：<字段名> Is Not Null

【例5-9】在学生成绩表tblScore中，编写满足下列条件的查询语句。 ? 查询缺少成绩的学生的学号和相应的课程号。 Select Stud_Id , Course_Id From tblScore Where Score Is Null

注意：这里的“Is”不能用等号（“=”） 代替。 ? 查询所有有成绩的学生的学号和课程号。 Select Stud_Id , Course_Id From tblScore

Where Score Is Not Null （6）逻辑表达式

逻辑表达式是将列名、常量等用逻辑运算符连接而成的式子。常用逻辑运算符有： And（逻辑与）、Or（逻辑或）、Not（逻辑非）。 逻辑运算符优先级为Not最先，And其次，最后为Or。

【例5-10】在学生档案表tblStudent中，查询年龄在20至25岁之间的女生学号、姓名和年龄。

Select Stud_Id , Stud_Name , Year(GetDate())-Year(Birth) As Age From tblStudent

Where Year(GetDate())-Year(Birth) Between 20 And 25

And (Stud_Sex='女')

- 140 -

5.1.2 连接查询

由关系代数运算可知，所谓连接就是从两个关系表的笛卡尔乘积中，选择满足一定条件元组记录。本节主要介绍等值连接（内连接）、外连接、自连接与交叉连接。

1．等值连接（内连接）查询

等值连接（内连接）是将两个表中满足等值连接条件的行组合起来，有两种语句格式。 （1）语句格式1（等值连接方式）： Select <列名表> From <表1> , <表2> Where <表1>.<列名1>=<表2>.<列名2> （2）语句格式2（内连接方式）： Select <列名表> From <表1> [Inner]

Join <表2> On <表1>.<列名1>=<表2>.<列名2>

从概念上讲DBMS执行等值连接操作的过程是，先在表1中找到第一个元组，然后从头开始顺序或按索引扫描表2 ，查询满足条件的元组，每找到一个元组，就将表1 中的第一个元组与该元组拼接形成新的元组。表2全部扫描完毕后，再到表1中找到第二个元组，重复上述过程，直到表1的全部元组处理完毕为止。

说明：

（1）通常情况下，表1为子表，列名1为外键，而表2为主表，列名2为主键。 （2）当列名1与列名2的名称不相同时，在Where 与On 子句中的列名前缀“表1.”与“表2.”可以省略。

（3）查询条件可使用关系运算符“>、<、>=、<=、<>”构成非等值连接查询，由于非等值查询使用较少，本书不再介绍。

（4）可对多个数据表进行等值连接查询，在查询条件中用And运算符连接两表间的等值连接表达式。

【例5-11】 在班级编码表TB03中，用两种方式查询班级名称、班级编码与系部名称。 等值连接方式： Use Xsgl

Select TB0302,TB0301,TB0602 From TB03,TB06

- 141 -

Where TB03.TB0309=TB06.TB0601

由于连接字段名不同，所以可省略表名TB03与TB06，简写如下： Select TB0302,TB0301,TB0602 From TB03,TB06 Where TB0309=TB0601 内连接方式：

Select TB0302,TB0301,TB0602 From TB03

Inner Join TB06 On TB03.TB0309=TB06.TB0601

【例5-12】在学生管理数据库Xsgl中，查询计算机系所属班级的班级名称（TB0302）、班级编码（TB0301）、专业名称（TB0402）、在校标志（TC0402）与系部名称（TB0602）。

由于班级名称（TB0302）、班级编码（TB0301）、专业名称（TB0402）、在校标志（TC0402）与系部名称（TB0602）五个字段分别属于班级编码表（TB03）、专业编码表（TB04）、毕业标志编码表（TC04）与系部编码表（TB06）四个数据表，因此，必须用三个等值表达式将四张表连接在一起。由附表中的学生班级编码表A.2可知，与学生班级编码子表中的外键TB0304（专业编码）、TB0308（毕业标志）、TB0309（系部编码）对应的父表为TB04（专业编码表）、TC04（毕业标志编码表）与TB06（系部编码表），对应主键分别为TB0401（专业编码）、TC0401（毕业标志编码）与TB0601（系部编码）。由此可写多表等值连接查询语句如下。

Use Xsgl

Select TB0302,TB0301,TB0402,TC0402,TB0602 From TB03,TB04,TC04,TB06 Where TB0304=TB0401 And

TB0308=TC0401 And TB0309=TB0601 And TB0602='计算机系' 也可用内连接方式写成：

Select TB0302,TB0301,TB0402,TC0402,TB0602 From TB03

Join TB04 On TB0304=TB0401 Join TC04 On TB0308=TC0401

- 142 -

Join TB06 On TB0309=TB0601 Where TB0602='计算机系'

【例5-13】 查询学生档案表XA01中“王”姓男生的所有信息，要求编码字段（如性别XA0106）要用汉字显示。

由附录A中表A.5学生档案表XA01可知，性别（XA0106）、民族（XA0108）、籍贯（XA0109）、政治面貌（XA0110）、班级编码（XA0114）均采用编码，若要用汉字显示就必须使用与之联系的父表中的代码名称字段。因此，在Select子句中使用GC0102、GC0202、GC0302、GC0402与TB0302字段来显示上述代码对应的汉字名称，另用A.*表示学生档案表XA01中的所有字段信息。而在From子句中应列出包含上述字段的代码表GC01、GC02、GC03、GC04与TB03，及学生档案表XA01 As A，其中A为XA01的别名。在Where子句中，6张表共要建立5个等值连接表达式，等值表达式中的所使用的外键与主键见表A.5，最后再加“王”姓“男”生两个条件表达式即可构成满足要求的查询语句。语句有两种表达方式。 （1）等值连接方式：

Select A.*,GC0102,GC0202,GC0302,GC0402,TB0302 From XA01 As A ,GC01,GC02,GC03,GC04 ,TB03 Where XA0106 = GC0101 And

XA0108 = GC0301 And XA0109 = GC0201 And XA0110=GC0401 And XA0114=TB0301 And XA0104 Like '王%' And GC0102='男'

（2）内连接方式：

Select A.*,GC0102,GC0202,GC0302,GC0402,TB0302 From XA01 As A

Join GC01 On XA0106 = GC0101 Join GC03 On XA0108 = GC0301 Join GC02 On XA0109 = GC0201 Join GC04 On XA0110=GC0401 Join TB03 On XA0114=TB0301

- 143 -

Where XA0104 Like '王%' And

GC0102='男' 2．外连接查询

外连接分为左外连接、右外连接和全外连接，左外连接对连接条件中左边的表不加限制，右外连接对连接条件中右边的表不加限制，全外连接是对两个表都不加限制，所有两个表中的行都出现在结果集中。下面依次介绍三种外连接查询的语句格式与使用方法。

（1）左外连接查询语句格式 Select 选择列表 From <表1>

Left [Outer] Join <表2> On <表1>.<列名1>=<表2>.<列名2> （2）右外连接查询语句格式 Select <选择列表> From <表1>

Right [Outer] Join <表2> On <表1>.<列名1>=<表2>.<列名2> （3）全外连接语句格式 Select 选择列表 From <表1>

Full [Outer] Join <表2> On <表1>.<列名1>=<表2>.<列名2>

【例5-14】查询班级编码表TB03中的班级名称、班级编码与系部名称，分别采用左外连接、右外连接与全外连接三种查询方式。

假设：班级表（TB03）中的第一条记录（汽车10021）中的TB0309字段为NULL值；在班级表（TB03）中没有TB0309字段为“00”（即班级所属系部为基础部）的班级存在。

（1）左外连接查询

Select TB0302,TB0301,TB0602 From TB03

Left Join TB06 On TB0309=TB0601

在执行上述语句后，由于左外连接对连接条件中左边的表TB03不加限制，但对右边的表TB06限制，所以会出现没有指定系部的班级信息，如图5-1(a)所示的第一条记录“1000221 汽车10021 NULL”。

（2）右外连接查询

- 144 -

Select TB0302,TB0301,TB0602 From TB03

Right Join TB06 On TB0309=TB0601

由于右外连接对左边的表TB03限制，但对连接条件中右边的表TB06不加限制，所以 “1000221 汽车10021 NULL”记录不显示，但会显示没有班级的系部，如图5-1(b)所示的第一条记录“NULL NULL 基础部”。

(a)左外连接

图5-1 TB03与TB06执行外连接的结果

(b)右外连接

（3）全外连接查询

Select TB0302,TB0301,TB0602 From TB03

Full Join TB06 On TB0309=TB0601

由于全外连接是对两个表TB03与TB06都不加限制，所有两个表中的行都出现在结果集中。执行上述语句后，“1000221 汽车10021 NULL”和“NULL NULL 基础部”都会显示。

通常在数据查询中，全外连接查询使用的概率较小。左外连接和右外连接可以互换，而对于究竟是采取左外连接还是右外连接编写查询语句，则是根据公司的编码规范选择其中的一种进行编写。

【例5-15】在学籍管理系统数据库Studentsys中，根据学生档案表tblStudent与成绩表tblScore查询所有学生选课成绩情况，包含没有选课学生的情况。

当学生在成绩表tblScore中没有选课记录时，用等值连接查询不会显示这些学生的成绩信息。只有用左外连接才能查询学生档案表与成绩表中所有学生的成绩记录，包含没有选课学生的情况。在这些没有选课的记录中，只有学生档案表中的学号、姓名等信息，而成绩表中课程号和成绩只能用NULL表示。这里选择左外连接查询实现。

Use Studentsys

- 145 -

Select tblStudent.Stud_Id , Stud_Name , Course_Id , Score From tblStudent

Left Join tblScore On tblStudent.Stud_Id=tblScore.Stud_Id

执行语句后的查询结果如图5-2所示（图5.2中的前4条记录是已选课学生的信息，后4条记录显示的是没有选课的学生信息）。

图5-2 在成绩表中用左外连接显示所有学生选课信息

3．自连接

连接操作不仅可以在不同的基表上进行，而且在同一张表内也可以进行自身连接。自连接可以看作一张表的两个副本之间进行的连接。在自连接中，必须为表指定两个别名，使之在逻辑上成为两张表。语句格式：

Select <别名1>.<列名1>,…,<别名1>.<列名n>,<别名2>.<列名1>,…,<别名2>.<列名m> From <表1> As <别名1>，<表1> As <别名2> Where <等值连接表达式>

【例5-16】职工档案表JA01的表结构如表5-3所示，表内容如表5-4所示。

表5-3 职工档案表JA01的表结构

序号 1 2 3 4 5 字段名 JA0101 JA0102 JA0103 JA0104 JA0105 含义 职工编码 职工姓名 性别码 部门编码 负责人编码 类型 宽度 小数 主键/外键 VARCHAR 10 P VARCHAR CHAR VARCHAR VARCHAR 10 1 10 10 F F F 父表/主键 GC01/GC0101 TB06/TB0601 JA01/JA0101 表5-4 职工档案表JA01的内容

JA0101(职工编码) JA0102(职工姓名) 3001 刘明强 3002 李明萍 4001 蒋明华 4002 翁明祥 JA0103(性别码) 1 2 2 1 JA0104(部门编码) 30 30 40 40 JA0105(负责人编码) 3001 3001 4001 4001 （1）用自连接方式显示职工负责人姓名，要求如图5-3所示。 Use Xsgl

Select A.JA0101 As '职工编码',A.JA0102 As '职工姓名',

- 146 -

A.JA0103 As '性别码',A.JA0104 As '部门编码',B.JA0102 As '负责人' From JA01 A

Join JA01 B On A.JA0105=B.JA0101

图5-3 职工编码与负责人编码进行自连接

（2）用自连接与内连接方式显示职工的性别、系部与负责人姓名，如图5-4所示。 Use Xsgl

Select A.JA0101 As '职工编码',A.JA0102 As '职工姓名', GC0102 As '性别',TB0602 As '所属部门',B.JA0102 As '负责人' from JA01 A

Join JA01 B On A.JA0105=B.JA0101 Join GC01 On A.JA0103=GC0101 Join TB06 On A.JA0104=TB0601

图5-4 用自连接与内连接方式显示职工的性别、系部与负责人姓名

4．交叉连接

交叉连接也叫非限制连接，它将两个表不加任何约束地组合在一起。在数学上，就是两个表的笛卡尔积。交叉连接后的结果集的行数是两个表的行数的乘积。在实际应用中，这种连接方式极少用到。

语句格式： Select <选择列表>

From <表1> Cross Join <表2>

【例5-17】将职工档案表JA01与系部TB06进行交叉连接。 Use Xsgl Select *

From JA01 Cross Join TB06

- 147 -

语句执行后如图5-5所示，查询结果共有4（JA01的行数）*10（TB06的行数）=40行。

图5-5 JA01与TB06的交叉连接

5.1.3 排序

排序是将数据表中的记录按指定列值或列组值升序或降序排列，语句格式如下。 Select <选择列名> From <表名>

Order By <列名或列号> [Asc | Desc] [，……] 说明：

（1）Asc为升序排序（默认值），Desc为降序排序。 （2）可对多达16个列执行Order By语句。

【例5-18】 对学生档案表XA01按照系部编码(升序)、班级编码XA0114(升序)与学号XA0102（降序）排序，显示系部名称、班级名称、学号与姓名。

Use Xsgl

Select TB0602 系部名称,TB0302 班级名称,XA0102 学号,XA0104 姓名 From XA01

Join TB03 On XA0114=TB0301 Join TB06 On TB0309=TB0601

Order By TB0601 Asc, TB0301 Asc, XA0102 Desc

5.1.4 分组统计查询

用户经常要对数据库中的数据进行统计汇总，为此，Select语句提供了Group By 子句用于分组统计，分组统计语句格式如下。

Select <分组字段>,<统计函数> From <表名> Where <条件表达式>

- 148 -

Group By <分组字段名> Having <条件表达式> Order By <排序字段名> [Asc | Desc] 说明：

（1）Select子句中的统计函数如表5-5所表示，同一个Select子句可包括多个统计函数。

表5-5 统计函数

统计函数 Sum(字段名) Avg(字段名) Min(字段名) Max(字段名) Count(字段名) 或Count(*) 对数值字段求和 对数值字段求平均值 求字段最小值 求字段最大值 统计记录行数 描述 （2）Group By <分组字段名> 表示，Select子句中的统计函数将按分组字段进行统计。若无Group By子句，则统计函数对整个数据表进行统计，即统计结果只有一条记录。

【例5-19】在学生档案表tblStudent与学生成绩表tblScore中完成如下统计任务。 ① 统计学生档案表tblStudent中的学生总人数。 Select Count(*) From tblStudent

语句执行后统计结果只显示一条记录，即学生总人数为：2459

② 在学生成绩表tblScore中，统计选修课程号='1004'的课程的选修平均成绩。 Select Avg(Score) From tblScore Where Course_Id='1004'

语句执行后统计结果只显示一条记录，即数学平均成绩为：72.32 ③ 计算课程号='1004'的课程的选修最高成绩、最低成绩和平均成绩。 Select Max(Score) AS Max, Min(Score) AS Min, Avg(Score) AS Avg From tblScore Where Course_Id='1004'

语句执行后的统计结果是最高分Max=99、最低分Min=0、平均分Avg=72.32。

（3）若对查询结果进行分组，则统计函数将作用于每一个组，即每一组都有一个函数值，显示多条记录。

（4）在Select子句中出现的“分组字段”必须出现在 “Group By”子句后面，且字段名相同。

（5）Having <条件表达式> 子句用于对分组字段进行条件检查，条件表达式中的字段只

- 149 -

能是Select子句中的“分组字段名”或“统计函数名”。

【例5-20】 按下列要求统计学生档案表XA01中各班学生人数。 ① 用班级编码XA0114作为分组字段，查询各班学生人数。 Use Xsgl

Select XA0114,Count(*) From XA01 Group By XA0114

用查询分析器执行语句，对同一班级编码的学生人数进行分组统计，统计结果分为两列，第一列显示班级编码，第二列显示该班学生人数，如图5-6(a)所示。此图只显示班级编号与该班学生人数，由于无班级名称，因此无法直观的知道各班学生人数。

② 通过班级编码表TB03与学生档案表XA01的内连接，用班级名称TB0302作为分组字段，查询各班学生人数。

Use Xsgl

Select TB0302 As '班级名称',Count(*) As '学生人数' From XA01

Join TB03 On TB0301=XA0114 Group By TB0302

用查询分析器执行语句的结果如图5-6(b)所示，此图能显示班级名称与该班学生人数。 ③ 通过班级编码表TB03与学生档案表XA01、系部编码表TB06的内连接，用系部名称TB0602、班级名称TB0302、系部编码TB0601作为分组字段，查询各班学生人数。查询结果按系部编码TB0601升序排序。

Use Xsgl

Select TB0602 As '系部名称',TB0302 As '班级名称',Count(*) As '学生人数' From XA01

Join TB03 On TB0301=XA0114 Join TB06 On TB0309=TB0601 Group By TB0602,TB0302,TB0601 Order By TB0601

用查询分析器执行语句后的结果如图5-6(c)所示，此图能显示系部名称、班级名称与学生人数。

- 150 -

④ 通过班级编码表TB03与学生档案表XA01、系部编码表TB06的内连接，学生档案表XA01与性别编码表GC01的内连接，用系部名称TB0602、班级名称TB0302、系部编码TB0601、性别GC0102作为分组字段，查询各系各班男女生人数。查询结果按系部编码TB0601升序排序。

Use Xsgl

Select TB0602 As '系部名称',TB0302 As '班级名称',GC0102 As '性别', Count(*) As '学生人数' From XA01

Join TB03 On TB0301=XA0114 Join TB06 On TB0309=TB0601 Join GC01 On XA0106=GC0101 Group By TB0602,TB0302,TB0601,GC0102 Order By TB0601

用查询分析器执行语句后的结果如图5-6(d)所示，此图能显示系部名称、班级名称、性别与男女生人数。

⑤ 查询各班男生人数 Use Xsgl

Select TB0602 As '系部名称',TB0302 As '班级名称',

GC0102 As '性别',Count(XA0101) As '学生人数'

From XA01

Join TB03 On TB0301=XA0114 Join TB06 On TB0309=TB0601 Join GC01 On XA0106=GC0101

Group By TB0602,TB0302,TB0601,GC0102 Having GC0102='男' Order By TB0601

- 151 -

(a) 班级编码为分组字段 (c) 系编码、系名与班级名称为分组字段 (b) 班级名称为分组字段 (d) 系编码、系名、班级名称与性别为分组字段 图5-6 分组查询示例

【例5-21】 对学生成绩表XA05进行分组查询。

（1）查询计算机系（系部编码为'30'）各班各门课程考核分数的平均成绩。 Use Xsgl

Select TB0302 As '班级名称',TB0103 As '课程名称', Cast (Avg(Cast(XA0509 as float)) as Int) As '平均成绩' From XA05

Join TB03 On XA0502=TB0301 Join TB01 On XA0506=TB0101 Where TB0309='30' And

XA0509 Between '00' And '99' Group By TB0302,TB0103

- 152 -

Order By TB0302,TB0103

说明：由于学生成绩表XA05的成绩字段XA0509为字符型，记录内容有分数“00”~“99”，等级“优秀”、“良好”、“中等”、“及格”、“不及格”、“旷考”等信息。因此，用Avg（）函数只能统计分数“00”~“99”类型的成绩，所以在查询条件中增加范围查询：

XA0509 Between '00' And '99'

此外，还必须用Cast函数将字符型字段XA0509转换为实型，即： Cast(XA0509 as float)

求平均值后再用Cast函数将实型转换为整型,即： Cast (Avg(Cast(XA0509 as float)) as int)

也可用Round函数取整：Round(Avg(Cast(XA0509 as float),0) 语句执行后结果如图5-7所示。

图5-7 学生平均成绩分组查询

（2）查询计算机系（系部编码为'30'）各班各门课程的学生选修人数、平均分、最高分、最低分。

Use Xsgl

Select TB0302 As '班级名称',TB0103 As '课程名称',Count(XA0501) As '学生人数',

Cast (Avg(Cast(XA0509 as float)) as int) As '平均成绩', Cast (Max(Cast(XA0509 as float)) as int) As '最高分', Cast (Min(Cast(XA0509 as float)) as int) As '最低分' From XA05

Join TB03 On XA0502=TB0301 Join TB01 On XA0506=TB0101 Where TB0309='30' And

XA0509 Between '00' And '99' Group By TB0302,TB0103 Order By TB0302,TB0103 语句执行后结果如图5-8所示。

- 153 -

图5-8 学生人数、平均成绩、最高分、最低分分组统计查询

5.1.5 嵌套子查询

与C++程序设计中循环嵌套、函数嵌套类似，Select语句也能进行嵌套查询，即在Select语句中嵌套Select语句，分为嵌套子查询和相关子查询两种，本节介绍嵌套子查询。嵌套子查询的一般语句格式为：

Select <列名表> From <数据表>

Where <表达式> <运算符> （Select 子查询） 说明：

（1）允许多重嵌套子查询，即在Select 子查询中允许嵌套子查询。嵌套查询由里向外。即先进行最内层子查询，并将查询结果作为其上一级子查询的查询条件，依次类推，最终显示最外层查询结果。

（2）Select子查询不能使用Order By子句，Order By子句只能对最终查询结果排序。 （3）<运算符>有五类，即：关系运算符（>、 <、 =、 >=、<=、!=或<>）、In、Exists、Any、All。现分五类运算符介绍嵌套子查询的使用方法。

1．关系运算符子查询

在使用嵌套子查询时，必须了解嵌套子查询的查询方式与使用条件。

（1）查询方式。父查询字段表达式与子查询结果用关系运算符进行比较，将满足条件的记录添加到父查询结果记录集中。

（2）使用条件。子查询必须返回单字段单值结果。

【例5-22】 查询与学号为“100022101”的同学同班的学生学号与姓名。 该查询可分两步进行：

第一步：用Select 子查询查找出学号为“100022101”同学的班级编码。 Select XA0114 From XA01 Where XA0102='100022101' 语句执行返回单字段单值结果是，班级编码XA0114为：“1000221”

- 154 -

第二步：在父查询Select语句的Where子句中，用XA0114与子查询得到的班级编码(“1000221”)进行等值比较。

Select XA0102,XA0104 From XA01 Where XA0114='1000221'

执行该语句后，可得到满足要求的学生信息。将上述两步合并成一条嵌套子查询语句。 Use Xsgl

Select XA0102,XA0104 From XA01

Where XA0114=(Select XA0114

From XA01

Where XA0102='100022101')

执行该语句后，同样可得到满足要求的学生信息。读者可思考如下问题。 （1）如何显示学生学号、姓名、班级编码与班级名称。

由于学生学号、姓名、班级编码与班级名称分别属于学生档案表XA01与班级编码表TB03，所以必须用内连接与嵌套子查询才能显示学生学号、姓名、班级编码与班级名称。内连接查询语句为：

Use Xsgl

Select TB0301 As '班级编码',TB0302 As '班级名称',

XA0102 As '学号',XA0104 As '姓名'

From XA01

Join TB03 On XA0114=TB0301 Where XA0114=(Select XA0114

From XA01

Where XA0102='100022101')

查询结果如图5-9所示。

（2）如何用自连接查询同班同学的信息。

在From子句中使用两个学生档案表XA01，分别用别名A与B表示。在Where子句中通过A.XA0114=B.XA0114可形成两张学生档案表的自连接，若在B表中加学号查询条件：B.XA0102='100022101'，则可查询到同班同学的信息。语句如下。

Select TB0301 As '班级编码',TB0302 As '班级名称',

A.XA0102 As '学号',A.XA0104 As '姓名'

- 155 -

From XA01 As A,XA01 As B, TB03 Where A.XA0114=TB0301 And

A.XA0114=B.XA0114 And

B.XA0102='100022101' 查询结果如图5-9所示。

图5-9 用嵌套子查询或自连接查询查找同班同学的信息

2．In运算符子查询

（1）查询方式：父查询字段表达式与子查询结果用In进行集合比较，即用In运算符判断父查询的字段表达式值是否属于子查询集合，将属于集合的记录添加到父查询结果记录集中去。

（2）使用条件：子查询必须返回1条或多条单字段记录值。

由于在嵌套查询中，子查询的结果往往是一个集合，所以谓词In是嵌套查询中最经常使用的谓词。

【例5-23】 用In子查询，查询与“周明明”同班同学的信息（学号、姓名与班级名）。 由于姓名为“周明明”的同学可能不惟一，即子查询“周明明”所在班级记录可能有多个，因此该查询要用In子查询来实现。

Use Xsgl

Select TB0302 As '班级名称',XA0102 As '学号',XA0104 As '姓名' From XA01

Join TB03 On XA0114=TB0301 Where XA0114 In ( Select XA0114

From XA01

Where XA0104='周明明')

- 156 -

语句执行后查询结果如图5-10所示，在图中可以看到，在学生档案XA01中到少有两个周明明，分别出现在机制30031班与模具30031班。

图5-10 用In子查询查找周明明同班同学的信息

3．Any与All子查询

当子查询中返回单值时可以使用比较运算符，而当子查询中返回多值时就不能单独使用比较运算符，此时可使用<关系运算符> 或 <关系运算符> 组合运算符。

（1）查询表达式格式

<字段> <关系运算符> （Select 子查询） <字段> <关系运算符> （Select 子查询） 其中，关系运算符为：>、>=、=、<、<=。 （2）查询方式

>Any ：当字段值>子查询结果某一个值时，查询表达式为Ｔrue <关系运算符>Any =Any ：当字段值=子查询结果某一个值时，查询表达式为Ｔrue

>All ：当字段值>子查询结果所有值时，查询表达式为Ｔrue <关系运算符>All =All ：当字段值=子查询结果所有值时，查询表达式为Ｔrue

（3）使用条件：子查询为单字段多值集合。

【例5-24】利用“=Any”子查询，查询与“周明明”同班同学的信息（学号、姓名与班级名）。

Use Xsgl

Select TB0302 As '班级名称',XA0102 As '学号',XA0104 As '姓名'

- 157 -

From XA01

Join TB03 On XA0114=TB0301 Where XA0114 =Any (Select XA0114

From XA01

Where XA0104='周明明')

执行语句的结果如图5-10所示，从语句执行效果来看，“=Any”子查询与In子查询是相同的。由此可见，同一个查询问题可以用不同的语句完成。

5.1.6 相关子查询

1．相关子查询的处理过程

相关子查询与前面的嵌套子查询有一个明显区别，子查询的查询条件依赖于外部父查询的某个属性值。求解相关子查询不能象求解嵌套子查询那样，一次将子查询求解出来，然后求解外部查询。相关子查询的子查询由于与外部查询有关，因此必须反复求值。从概念上讲，相关子查询的一般处理过程如下：

（1）首先取外部查询中的第一个记录，根据它与子查询相关的属性值处理内层查询； （2）若Where子句返回值为真（即子查询结果非空），则取此记录放入结果表； （3）然后再检查外部表的下一个记录；重复这一过程，直至外部表全部检查完毕为止。 【例5-25】假如在成绩表中有如表5-6所示的几条记录，编写下列查询语句对嵌套子查询与相关子查询进行对比分析。

表5-6 学生成绩表tblScore的记录

Stud_Id(学号) 101100101 101100101 101100101 101100102 101100102 101100102 Course_Id(课程名 ) 00001 00002 00003 00001 00002 00003 Score(成绩) 70 80 65 95 63 77 （1）查询考试成绩大于所有同学平均考试成绩的成绩记录。 这道题目属于嵌套子查询，查询语句如下： Use Studentsys

Select Stud_Id , Course_Id , Score From tblScore

Where Score>(Select Avg(Score) From tblScore)

- 158 -

查询过程可简要描述如下：先从子查询中查询出所有学生的平均成绩，将该结果作为父查询的查询条件，再从父查询中查询出大于所有同学平均考试成绩的成绩记录。

（2）查询考试成绩大于该同学平均考试成绩的成绩记录。

两位学生三门课程平均成绩分别是72与78。题目中希望得到的结果是：

Stud_Id 101100101 101100102 Course_Id 00002 00001 Score 80 95 这道题目就是相关子查询，查询语句如下： Use Studentsys

Select Stud_Id , Course_Id , Score From tblScore C1

Where Score>(Select Avg(Score) From tblScore C2

Where C1.Stud_Id=C2.Stud_Id)

查询过程可简要描述如下：先从父查询学生成绩表的第1条记录开始，通过子查询的连接表达式C1.Stud_Id=C2.Stud_Id（将别名为C1与C2两个学生成绩表tblScore作自连接）作内层查询，得到该同学的平均成绩；通过Where子句判断第1条记录是否满足查询条件（成绩大于该同学平均考试成绩），满足条件则显示记录，否则不显示；然后再检查父查询表的下一个记录，重复这一过程，直至父查询表全部检查完毕为止。

通过前面2个例子，可以总结出相关子查询的本质是父查询和子查询有相关性，即在子查询中出现了父查询的字段值。

2．Exists运算符子查询

Exists运算符子查询是相关子查询的一个典型应用。用Exists判断子查询结果是否为空。若为非空，则Exists返回逻辑值True，否则返回False。

【例5-26】 在学生管理数据库Xsgl中，利用Exists子查询，查询与“周明明”同班同学的信息（学号、姓名与班级名）。

Use Xsgl

Select XA0102,XA0104,TB0302 From XA01 As A

Join TB03 On XA0114=TB0301

Where Exists (Select * From XA01 As B

- 159 -

Where A.XA0114=B.XA0114 And B.XA0104='周明明')

说明：

（1）因为Exists子查询只返回真值或假值，给出列名亦无实际意义，所以Exists子查询的列名常用“*”表示。

（2）查询过程可简要描述如下：在子查询中通过连接表达式A.XA0114=B.XA0114，将别名为A与B两个学生档案表XA01作自连接。先从父查询中XA01 A表第1条记录开始，取出班级编码A.XA0114，通过子查询中的自连接表达式A.XA0114=B.XA0114找到子查询中XA01 B表同班学生的记录，并通过条件表达式B.XA0104='周明明' 判断该班同学中是否存'周明明'，若存在则子查询结果非空，Exists判断为真，此时应将父查询中XA01 A表中当前记录添加到与'周明明'同班同学的集合中去。然后再对XA01 A表中第2 条记录作上述处理，直到最后一条记录为止。

（3）Not Exists的返回结果与Exists相反。

5.1.7 数据查询综合实例

【例5-27】按下列要求编写查询语句。

（1）在班级课程表TA19中，查询周学时高于平均周学时数的课程信息（课程编码与周学时数）。

Use Xsgl

Select TA1904,TA1910 From TA19

Where TA1910 >(Select Avg(TA1910) From TA19 )

执行嵌套查询语句时，先执行子查询语句“Select Avg（TA1910） From TA19”，得到单行单列结果（如平均周学时数=4.25），然后将其作为父查询关系表达式右边常量，执行父查询“Select TA1904,TA1910 From TA19 Where TA1910>4.25”。

（2）在班级课程表TA19等表中，查询课时数高于平均课时数的课程信息。课程信息为课程编码、课程名、班级名、课程类别、课程性质、考核标志、起始周次、教学周数、周学时数和课时数，并用汉字显示标题栏。

由于投影字段涉及班级课程表TA19、课程编码表TB01、班级编码表TB03、课程类别表TC01、课程性质表TC02、考核标志表TC03，所以要对6张表通过主、外键进行内连接。课程数可通过教学周数（TA1909）*周学时数（TA1910）得到。因此，查询课时数高于平均课

- 160 -

时数的表达式为：

TA1909*TA1910 >(Select Avg(TA1909*TA1910) From TA19 ) 嵌套子查询语句如下。 Use Xsgl

Select TA1904 As '课程编码',TB0103 As '课程名', TB0302 As '班级名',

TC0102 As '类别',TC0202 As '课程性质', TC0302 As '考核标志', TA1908 As '起始周次',TA1909 As '教学周数', TA1910 As '周学时数',TA1909*TA1910 As '课时数'

From TA19

Join TB01 On TA1904=TB0101 Join TB03 On TA1901=TB0301 Join TC01 On TA1905=TC0101 Join TC02 On TA1906=TC0201 Join TC03 On TA1907=TC0301

Where TA1909*TA1910 >(Select Avg(TA1909*TA1910) From TA19 ) 语句执行后结果如图5-11所示。

图5-11 嵌套查询课时数高于平均课时数的课程信息

【例5-28】在学生成绩表XA05中，用嵌套子查询与连接查询两种方式查询选修“Java程序设计”的学生信息。

（1）嵌套子查询（要求显示学号、姓名与班级编码）

用嵌套子查询方式查询选修“Java程序设计”的学生信息，可按三步进行，第一步从课程编码表TB01中查询出“Java程序设计”的课程编码；第二步从学生成绩表XA05中查询出选修该课程编码的学生学号；第三步用In运算符找出这些学号的学生信息。具体过程如下。

第一步确定“Java程序设计”的课程编码。

Select TB0101 From TB01 Where TB0103='Java程序设计'

- 161 -

结果为：5058

第二步查找选修 “5058”课程的学生学号。

Select XA0501 From XA05 Where XA0506 ='5058' 结果：0000002744、0000002745、---------、0000002754

第三步查找上述学生的信息，由于第②步得到的结果不止一个，所以要用In表示取值范围。

Select XA0102,XA0104，XA0114 From XA01 Where XA0101 In （） 综合三步查询，得到一个三层的嵌套查询语句如下。

Use Xsgl

Select XA0102 As '学生学号',XA0104 As '学生姓名',XA0114 As '班级编码' From XA01

Where XA0101 In (Select XA0501 From XA05

Where XA0506 =(Select TB0101 From TB01

Where TB0103='Java程序设计'))

语句执行结果如图5-12(a)所示。

（2）连接查询（要求显示班级名称、学号、姓名、课程名称） Use Xsgl

Select TB0302 As '班级名称',XA0102 As '学生学号',

XA0104 As '学生姓名',TB0103 As '课程名称'

From XA05

Join TB03 On XA0502=TB0301 Join TB01 On XA0506=TB0101 Join XA01 On XA0501=XA0101 Where TB0103='Java程序设计' 语句执行结果如图5-12(b)所示。

(a) 嵌套子查询

(b) 连接查询

图5-12查询选修“Java程序设计”的学生信息

【例5-29】 用Exists子查询，查询所有选修课程号为“5058”的学生学号与姓名。 在学生档案表XA01中取出第一条记录的学生编码XA0101，用相关子查询，在学生成绩

- 162 -

表XA05中查询选修课程号为“5058”的学生，并用Exists运算符判断查询结果是否非空，若非空则将该学生记录添加到父查询结果集合中去，否则放弃该学生记录。然后在XA01中取出第二条记录的学生编码XA0101，继续上述过程，直到XA01中最后一条记录为止。查询语句如下。

Select XA0102,XA0104 From XA01

Where Exists ( Select *

From XA05

Where XA0501=XA0101 And

XA0506='5058')

【例5-30】在班级编码表TB03中，查询满足下列要求的班级信息（系部编码、班级名称、班级人数）。

（1）查询机械系（代码为“10”）中所有班级信息。

Select TB0309 As '系部编码',TB0302 As '班级名称',TB0306 As '班级人数' From TB03 Where TB0309='10' 查询结果如图5-13所示。

图5-13 查询机械系所有班级信息

（2）用Any嵌套子查询，查询计算机系（代码为“30”）中，班级人数小于机械系(代码为“10”)某一班级人数的班级信息。

Use Xsgl

Select TB0309 As '系部编码',TB0302 As '班级名称',TB0306 As '班级人数' From TB03

Where TB0309='30' And

TB0306

From TB03 Where TB0309='10')

由图5-13可知机械系最大班级人数为48。因此查询结果应该是计算机系中任一班级人数小于48人的班级信息。如图5-14所示。

- 163 -

图5-14 查询计算机系班级人数小于48的班级信息

（3）用All嵌套子查询，查询管理系（代码为“40”）中，班级人数小于机械系(代码为“10”)所有班级人数的班级信息。

Select TB0309 As '系部编码',TB0302 As '班级名称',TB0306 As '班级人数' From TB03

Where TB0309='40' And

TB0306

From TB03 Where TB0309='10' )

由图5-13可知机械系最小班级人数为24人。因此查询结果应该是计算机系中所有班级人数小于24人的班级信息。如图5-15所示。

图5-15 查询管理系班级人数小于24班级信息

5.2 使用视图查询数据

在第1章中曾介绍过数据库系统的三级模式与两级映射的体系结构，三级模式是外模式、模式与内模式。其中外模式是用户与数据库系统的接口，以用户视图方式表示。不同用户可通过不同外模式（视图）访问数据库。事实上，所谓视图就是用户使用Select语句对数据表进行投影、选择与连接运算后得到局部数据表。在SQL语言中视图View可用Create语句创建，并在Select语句中使用。下面分别介绍视图的概念、创建语句与使用方法。

5.2.1 视图的概念

视图是由一个或多个数据基表导出的二维虚表，虚表的列与行来自于基表并由定义视图的查询语句产生。和表一样，视图也有定义的行和列，但是这些列和数据行并不实际地以视图存在于数据库中，而是存储于它所引用的基表当中。视图之所以称为虚表，是因为它并没有作为数据集存储在数据库中。

视图定义存储在数据库中，而用户所看到的数据并没有像表那样又在数据库中重新存储一

- 164 -

份，通过视图所看到的数据只是存放在基表中的数据。修改在视图中看到的数据时，实际上修改的是基表的数据。基表的数据变化自动反映在视图中。对视图的操作和对表的操作一样，可以对其进行查询、修改和删除操作。

5.2.2 建立视图

1．用Create View语句创建视图 （1）语句格式

Create View [数据库.][拥有者.]<视图名> [（列名1，…，列名n）] [With Encryption] As Select 语句 [With Check Option] （2）说明

? With Encryption可选项用于对视图结构进行加密；

? With Check Option保证修改数据在被提交前仍可通过视图看见；

? 视图中不允许使用Group By分组查询、Order By排序、Distinct、Into等关键字； ? 一个视图最多能定义1024列；

? 若基表结构改变（如删除某字段），则视图必须重建； ? 视图中的函数、表达式、常量等必须定义列名才能访问。

【例5-31】创建班级编码表TB03的视图View_TB03，能显示班级编码表TB03所有字段、专业名称、毕业标志、所属系部名称等信息。

Use Xsgl Go

Create View View_TB03 As

Select TB03.*,TB0402,TC0402,TB0602 From TB03

Join TB04 On TB0304 = TB0401 Join TC04 On TB0308 = TC0401 Join TB06 On TB0309=TB0601 2．用企业管理器建立视图

按【例5-31】的要求，用企业管理器建立视图View_TB03。创建的步骤如下： （1）在企业管理器中选择要创建视图的数据库Xsgl，右击“视图”，选择“新建视图”命令，会出现一个如图5-16所示的视图窗体。该对话框共分为表区、列区、SQL Script区和数据结果区4个区,此时4个区都是空白。

（2）右击表区，选择“添加表”命令。则出现对话框要求选择创建视图的基表或视图，

- 165 -

在对话框中选择创建视图的基表TB03、TB04、TB06、TC04 ， 单击“添加”按纽将四张表添加到表区。

（3）在表区的TB03、TB04、TB06、TC04四个表中单击字段左边复选框，将需要显示字段（TB03.*,TB0402,TC0402,TB0602）添加到列区中去。

（4）在列区中的准则框中输入查询条件，并可进行排序类型和排序顺序的设置。 （5）上述工作完成后，相应的SQL脚本将出现在SQL Script区中，如图5-16所示。 （6）单击对话框右上角的“关闭”按钮，出现保存视图的对话框，输入视图名View_TB03，则视图建立完毕。

图5-16 用企业管理器建立视图

注意：当建立视图的数据基表结构发生变化时，必须重新修改创建视图的语句并执行后，才能对变化后数据基表进行查询。常用的做法就是先删除视图（删除视图的语句为：Drop View <视图名>），然后再重新创建视图。

5.2.3 使用视图

视图主要用于数据查询，可以用Select语句查询视图中的数据，语句格式与数据表的Select语句完全相同，只须将From子句中的数据表名换成视图名即可。

【例5-32】 用视图View_TB03查询“计算机系”所有班级的班级名称、入学年份、专业名称、毕业标志、所属系部。

Select TB0302,TB0303,TB0402,TC0402,TB0602 From View_TB03 Where TB0602='计算机系'

【例5-33】 创建学生档案表XA01的视图View_XA01，能显示学生档案表XA01所有字

- 166 -

段，及性别、籍贯、民族、班级名称、系部名称字段信息。使用视图View_XA01查询姓王男生的信息（系部,班级,学号,姓名,性别），使用视图View_XA01分组统计各系各班男女生人数。

（1）创建视图View_XA01 Create View View_XA01 As

Select XA01.*,GC0102,GC0202,GC0302,GC0402,TB0302,TB0601,TB0602 From XA01

Join GC01 On XA0106 = GC0101 Join GC02 On XA0109 = GC0201 Join GC03 On XA0108 = GC0301 Join GC04 On XA0110=GC0401 Join TB03 On XA0114=TB0301 Join TB06 On TB0309=TB0601

（2）使用视图View_XA01查询“王”姓男生的信息 Select TB0602 As '系部' ,TB0302 As '班级',XA0102 As '学号',

XA0104 As '姓名' ,GC0102 As '性别'

From View_XA01 Where GC0102='男' And

XA0104 Like '王%'

（3）使用视图View_XA01分组统计各系各班男女生人数 Select TB0602 As '系部名称',TB0302 As '班级名称',

GC0102 As '性别',Count(*) As '学生人数'

From View_XA01

Group By TB0602,TB0302,GC0102,TB0601 Order By TB0601

显然，原先要用连接查询和子查询实现的复杂查询语句，现在使用事先定义好的视图，Select语句变得简单多了。

【例5-34】 创建学生成绩表XA05的视图View_XA05，能显示学生成绩表XA05所有字段、学生学号、班内序号、姓名、班级名称、系部名称等信息。用视图View_XA05查询选修“C++程序设计”课程的所有学生成绩信息（系名、班名、学号、姓名、课程名、考核标志、成绩），查询各班各门课程的学生人数、平均分、最高分、最低分。

（1）创建视图View_XA05 Use Xsgl Go

Create View View_XA05 As

- 167 -

Select XA05.*,XA0102,XA0103,XA0104,TB0302,TB0602,TB0103,TC0202,TC0302 From XA05

Join XA01 On XA0501 = XA0101 Join TB03 On XA0502 = TB0301 Join TB06 On TB0309=TB0601 Join TB01 On XA0506=TB0101 Join TC02 On XA0507=TC0201 Join TC03 On XA0508=TC0301

（2）使用视图View_XA05查询选修“C++程序设计”课程的所有学生成绩信息 Use Xsgl

Select TB0602,TB0302,XA0102,XA0104,TB0103,TC0302,XA0509 From View_XA05

Where TB0103 = 'C 语言程序设计'

（3）使用视图View_XA05查询计算机系各班各门课程的学生人数、平均分、最高分、最低分，并按班级与课程排序。

Select TB0302 As '班级名称',TB0103 As '课程名称',Count(XA0501) As '学生人数',

Cast (Avg(Cast(XA0509 as float)) as int) As '平均成绩', Cast (Max(Cast(XA0509 as float)) as int) As '最高分', Cast (Min(Cast(XA0509 as float)) as int) As '最低分'

From View_XA05

Where TB0602='计算机系' and XA0509 Between '00' AND '99' Group By TB0302,TB0103 Order By TB0302,TB0103

5.3 数据插入语句

当数据表创建后，就要对数据表进行数据插入操作。可以使用企业管理器与SQL语句两种方法实现数据插入操作。用企业管理器对数据进行更新操作的方法是，在企业管理器的树形目录中右击数据表，在弹出式菜单中选择“打开表 | 返回所有行 ”命令，进入数据表编辑对话框，在此对话框内可进行记录的插入，同时也可进行记录的删除和修改，操作比较简单，读者可以自行练习。这里只介绍用SQL语言进行数据插入的方法（数据删除与修改也仅介绍用语句实现）。

Insert插入语句有两种格式，一种是使用Values关键字直接给字段赋值，另一种是使用Select子句，从其他表或视图中取出数据插入到数据表中去。

- 168 -

5.3.1 使用Values关键字的Insert语句

语句格式如下：

Insert Into <数据表名> [（字段名表）] Values (字段值表)

执行语句时，先在数据表中添加一条新记录，然后将关键字Values后的字段值依次赋给新记录的每个字段。

说明：

（1）插入整条记录的所有字段值时，语句中的“字段名表”可省略。 【例5-35】按下列要求编写插入语句。 ① 在系部编码表TB06中插入汽车系信息。 Insert Into TB06 Values('11','汽车系','QCX','冯明东') ② 在班级编码表TB03中插入班级的信息。

Insert Into TB03 Values('10200232','机械10232','2002','04','3',32,'','1','10') Insert Into TB03 Values('10300132','模具10132','2001','06','3',30,'327','1','10') Insert Into TB03 Values('2001131','制冷20031','2000','11','3',38,'','1','50') Insert Into TB03 Values('2001901','电子20001','2000','25','3',45,'328','1','20') Insert Into TB03 Values('2001902','电子20002','2000','25','3',47,'332','1','20') Insert Into TB03 Values('20400101','制冷20101','2001','20','3',47,'','1','50') 插入语句执行后，可用Select语句查询插入结果，如Select * From TB03。 （2）插入记录的部分字段数据时，必须写明插入字段名。 【例5-36】在学生档案表XA01中插入“戴明波”学生的部分信息。 Insert Into

XA01(XA0101,XA0102,XA0103,XA0104,XA0106,XA0108,XA0109,XA0110,XA0114) Values('02479','980140','51','戴明波','1','01','320100','01','990101')

（3）插入数据时要保证数据完整性，即不能违反实体完整性、参照性完整性、用户自定义完整性，否则会出现插入错误的信息，如图5-17所示。

图5-17 插入数据时的错误

（4）字段值表中的数目、顺序和数据类型必须与字段名表中列的数目、顺序和数据类型相对应。

- 169 -

5.3.2 使用Select子句的Insert语句

语句格式如下：

Insert Into <数据表名> [（字段名表1）] Select 字段名表2 From 表名

说明：字段名表1与字段名表2中的数目、顺序和数据类型必须相对应。

【例5-37】 在学生成绩表XA05初始化程序中，要将学生档案表XA01中指定班级学生编码与班级课程表TA19中该班所上课程编码等信息添加到学生成绩表XA05中去。

学生档案表XA01、班级课程表TA19与学生成绩表XA05的表结构见附录A的表A.5、表A.11、表A.14。在Insert语句中，可用Select子句从学生档案表XA01中，将指定班级的学生编码XA0101、班内序号XA0103插入到学生成绩表XA05的XA0501与XA0503字段中；从班级课程表TA19中，将指定班级的班级编码TA1901、学年TA1902、学期TA1903、课程编码TA1904等字段内容插入到学生成绩表的XA0504、XA0505、XA0506等字段中。假设指定班级编码为'3001231'、学年为'2001-2002'、学期为'1'，则将学生编码与课程信息插入学生成绩表中的Insert语句如下。

Insert Into

XA05 ( XA0501,XA0502,XA0503,XA0504,XA0505,XA0506,XA0507,XA0508) Select XA0101,TA1901,XA0103,TA1902,TA1903,TA1904,TA1907,TA1908 From XA01

Join TA19 On XA0114=TA1901 Where XA0114='3001231' And

TA1902='2004-2005' And TA1903='1'

5.4 数据删除语句

5.4.1 使用Delete语句删除数据

删除语句用于删除数据表中满足条件的记录，语句格式如下。 Delete From <数据表名> Where <条件表达式>

说明：若不加Where子句则删除表中全部数据。 【例5-38】 从系部编码表TB06中删除汽车系记录。 Delete From TB06 Where TB0602='汽车系'

- 170 -

说明：若要删除已被其它表引用的记录，即有主、外键约束的记录时，会出现禁止删除的提示。如图5-18中，从系部表中删除“计算机系”的记录，但由于系部表与班级表之间存在着参照完整性关系，出现了冲突，所以无法删除。

图5-18 删除记录时与外键约束发生冲突

【例5-39】在学生成绩表XA05中，删除指定学年（'2001-2002'）、学期（'1'）与班级编码（'100121'）的学生成绩记录。

Delete From XA05

Where XA0502='3001231' And

XA0504='2004-2005' And XA0505='1'

【例5-40】在学生成绩表XA05中，删除“周明明”学生的所有成绩。 Delete From XA05

Where XA0501 In (Select XA0101

From XA01

Where XA0104='周明明')

5.4.2 使用Truncate语句清除表中全部数据

在SQL Server 2000提供了一种快速删除表中所有行的方法。它比不用WHERE子句的DELECT语句要快，因为DELETE 语句要记录删除的每个改变以用来备份。

语句格式：Truncate Table <数据表名>

【例5-41】 在学生成绩表XA05中清除所有记录。 Truncate Table XA05

说明：用Truncate语句清除数据表内容时，系统不进行日志记录，所以在进行清除数据表内容前，先应对数据库进行备份，防止由于错误操作引起数据的丢失。

5.5 数据修改语句

修改语句用于更新数据表中满足条件记录的字段值，语句格式如下： Update <数据表名 | 视图名>

- 171 -

Set <字段名1>=<表达式值1> [, <字段名2>=<表达式值2>, …<字段名n>=<表达式值n>] Where <条件表达式>

该语句执行时，先在数据表或视图找到满足条件记录，然后将表达式值赋给字段。 【例5-42】从系部编码表TB06中，将“计算机系”改为“信息工程系”。 Update TB06

Set TB0602='信息工程系' Where TB0602='计算机系'

说明：若不加Where 条件，则对表中所有记录进行替换操作。

【例5-43】在学生成绩表XA05中，将指定学年（'2001-2002'）、学期（'1'）、学生编码（'0000000404'）与课程名（'JAVA程序设计'）的学生成绩改为90分。

Update XA05 Set XA0509='90' Where XA0501='0000000404' And

XA0504='2004-2005' And XA0505='1' And

XA0506=(Select TB0101 From TB01 Where TB0103= ' JAVA程序设计')

【例5-44】用学生档案表XA01统计出的学生人数，更新班级编码表TB03中的学生人数字段TB0306。

Update TB03 Set TB0306=(Select Count(XA0101) From XA01 Where XA0114= TB0301)

该语句执行过程为，从班级编码表TB03的第1条记录开始，先取出该记录的班级编码TB0301，然后执行相关子查询语句：

Select Count(XA0101) From XA01 Where XA0114= TB0301

子查询语句从学生档案表XA01中统计出由TB0301指定班级的学生人数，结果返回给父查询，父查询将结果值赋给班级人数TB0306字段。然后父查询再从班级编码表TB03中取第2条记录的班级编码TB0301，按上述步骤依次计算每班学生人数，并赋给TB0306，直到最后1 条记录为止。

本章小结

1．Select查询语句

Select语句可完成对数据表的投影、选择、连接、排序、统计汇总等运算。一般格式为： Select Distinct <目标表达式> （投影运算）

From <表1>

<连接方式> Join <表2> On 表1.列名=表2.列名 … … …

- 172 -

<连接方式> Join <表n> On 表1.列名=表n.列名

Where <条件表达式> （选择运算） Order By <表达式> [Asc | Desc] （排序运算） Group By <分组字段> Having <条件表达式> （分组运算）

在语句中，Select子句为投影运算，子句的目标表达式有4种表达方式，如图5-19(a)所示；From子句中的连接方式有7种表达方式，如图5-19(b)所示；Where子句为选择运算，子句中的条件表达式有6种表达方式，如图5-19(c)所示，在Where 子句中还允许进行嵌套子查询与相关子查询；Oder By子句完成排序运算；Group By子句完成分组统计运算。

列名 As 别名 * 目标表达式 表达式 As 别名 统计函数 As 别名 统计函数为：Sum、Avg、Max、 Min、Count (a)目标表达式的4种表达方式 内连接 ：Inner

左外连接：Left 右外连接：Right 连接方式 全外连接：Full 自连接 ：A.列=B.列 交叉连接：Cross (b)连接方式的7种表达方式 关系查询：>、>=、=、<、<=、<> 逻辑查询：And、Or、 Not 范围查询：Between 下界 and 上界 集合查询：In (集合元素表) 条件表达式 空值查询：Is Null 或 Is Not Null 模糊查询：Like ‘d字符串d’ ，其中通配符d=%、_等 (c)条件表达式的6种表达方式 图5-19目标表达式、连接方式、条件表达式的表达方式

2．嵌套子查询语句

Select <目标表达式>

From <表名> Where <字段表达式> <运算符> (