基于数据库模式导航的select多表查询教学法

摘要:查询语句select是SQL的教学重点。基于关系自然联接运算的工作原理,本文提出借助关系数据库模式图中表之间的关联,来“导航”编写select查询语句方法,借助导航提示功能,在待求问题和select语句间构成映射,通过已知条件沿数据表间的关联来求未知,最终得到正确的select语句代码。本教学法在实践中取得良好效果。

关键词:SQL查询;教学法;数据库模式;select语句;自然联接

中图分类号:G642 文献标识码:A

SQL是关系数据库的标准查询语言,查询语句select是SQL的教学重点,它具有功能强,结构复杂等特点。国内多数大学已经将SQL语句纳入财经、管理类的数据库教学大纲中。江西财经大学公共基础课程“数据库应用”中,SQL查询是教学的重要内容。通过多年教学摸索,我们总结出基于数据库模式导航的select多表查询教学法。这里“导航”的含义是通过已知来求解未知,导航中方向的识别是借助数据库模式图。从教学反馈来看,学生能够理解关系数据库多表查询的真正含义,教学效果良好。

1数据库模式的核心概念

1.1关系数据库

(1) 关系:一个关系就是一张二维表,通常将一个没有重复行、重复列的二维表看成一个关系,每个关系都有一个关系名。和关系密不可分的两个概念是描述关系行的元组(也称为记录)和描述关系列的属性(也称为字段)。只有属性和元组确定后,才能够唯一确定一个关系(也称为表)。后面我们将混用关系和表这两个术语。

(2) 主关键字:一个关系中能唯一区分、确定不同元组的属性或属性组合,称为该关系的主关键字或主键PK (Primary Key)。主关键字对应的属性称为主属性。单个属性组成的关键字称为单关键字,多个属性组合的关键字称为组合关键字。需要强调的是,关键字的属性值不能取“空值”。所谓空值就是“不知道”或“不确定”的值,因空值无法唯一区分和确定元组,它不能是组成关键字的属性。

(3) 外部关键字:一个关系中某个属性或属性组合不是这个关系的主关键字,但却是另一个关系的主关键字,称此属性或属性组合为这个关系的外部关键字或外键FK (Foreign Key)。两个关系之间的联系是通过外部关键字实现的。

主关键字和外部关键字的作用是实现数据库中多个关系间的完整性约束。

1.2关系完整性约束

关系完整性约束是为保证数据库中多个关系的数据的正确性和相容性,对数据库提出的某种约束条件或规则。关系完整性通常包括:实体完整性、参照完整性、域完整性和用户定义完整性,其中实体完整性和参照完整性,是数据库必须满足的完整性约束条件。实际上,数据库关系完整性约束实际上是定义数据必须满足的基本要求,当数据违反数据库关系完整性约束时,数据库将拒绝违反关系完整性的数据的插入或更新,通过关系完整性可以保证数据库中没有垃圾数据。

或者说通过定义关系的完整性约束,使得数据库有了一定的行为能力。当用户提交那些违背数据库关系完整性约束的数据时,数据库将拒绝用户提交的操作,这样保证数据库中的数据是真实有效的。

(1) 实体完整性:实体完整性是指一个关系中不能存在两个完全相同的记录。实体完整性是通过关系的主关键字(PK)来实现的。

(2) 参照完整性:关系数据库中通常都包含多个存在相互联系的关系(表),关系与关系之间的联系是通过公共属性来实现的。所谓公共属性(联接属性),它是一个关系R(称为被参照关系或目标关系,常被称为一表)的主关键字,同时又是另一关系K(称为参照关系,常被称为多表)的外部关键字。所谓参照完整性是指参照关系K中外部关键字的取值必须与被参照关系R中某元组主关键字的值相同,否则违反了参照完整性约束。

可以看到,主关键字实现了对单个关系的不含重复元组的约束,外部关键字实现了具有公共属性的两个关系间的约束。

1.3关系运算

关系运算中,输入是一个或多个关系,输出是经过关系运算后得到的一个新关系。常见关系运算包括:选择、投影、自然联接。由于选择和投影运算只要求一个输入关系,故称为一元关系运算符,而自然联接运算要求二个输入关系,故称为二元关系运算符。

(1)选择运算:给定一个关系,从中筛选出满足某种条件的记录(或元组)的过程称为选择。(2)投影运算:给定一个关系,从中只检索期望得到的字段(或属性)的过程称为投影。(3)自然联接运算:不同于选择、投影运算仅需一个输入关系,二元自然联接运算有前提条件,既参与自然联接运算的两个输入关系间必须有一个公共的属性(称为联接属性),在一个关系(称为一表)中它是主键,而在另一个关系(称为多表)中它是外键。自然联接运算的结果:在属性上是两个参与运算关系的属性叠加(部分系统会剔除重复的联接属性);在元组上是在多表元组的基础上,扩展与一表联接属性取值相同的对应一表元组数据值,既先取一条多表元组,再以该元组的联接属性取值为条件,搜索一表中的元组,当一表的联接属性与已知条件相同时,将该一表的元组拼接到给定多表元组上,形成一条新关系中的元组。如此反复对多表元组进行相同操作,最后得到新的关系表。可以认为是一表被融合到了多表。自然联接操作是关系数据库多表查询的理论基础。

1.4数据库模式图实例

数据库模式图是从全局的角度来描述多个关系之间的实体完整性和参照完整性。关系之间的参照完整性约束连线构成了关系间的导航图。

图1所示的是一个教学管理数据库,它涉及4个关系,这些关系之间的完整性约束由施加在这些关系间的连线表示。各表中小钥匙表示该表的主关键字。图中不同关系间的连线表示关系间的外键约束。图1中,记号“+”表示主键中的壹,记号 表示外键中的多。两表之间的连线表示两表之间的参照完整性约束,既这两个表之间可以进行自然联接运算。而没有连线的两个表之间,则不能够直接进行自然联接运算。例如,“学生”关系中,“学号”是主关键字,则“学号”唯一确定“学生”关系中的一条记录。实际情况是一个班级的“学生表”中可能存在姓名相同的人,由于他们是两个不同的人,因此用“姓名”作为主关键字是不行的,但“学号”能唯一标识他们。

如图1所示,“成绩表”作为参照关系,“学生表”作为被参照关系,“学号”是这两个关系联接属性,则这两个关系进行自然联接运算的结果是形成一个新的关系。具体解释为:新关系中,元组为“多”表的元组,即成绩表的元组,属性为两个关系属性叠加。新关系中,一条元组的“一”表的属性取值是“一”表的联接属性值等于“多”表的联接属性值。新关系可以认为是“一”表被合并到了多表。

2基于数据库模式导航的SQL查询教学法

2.1单表和两表自然联接的select语句编写

对单表的运算包括投影和选择。讲解重点是select条件构造,既关系运算符、逻辑运算符、集合运算in、字符匹配运算符like和between…and…等。

两个表的自然联接运算必须有参照完整性约束,这两个表才能够进行自然联接操作。

select字段列表;

from 两个数据表;

where 条件;

group by 条件;

having条件;

order by 条件

注意,一定要求规范select语句格式。分行使得select语句易于理解,缩格表示被缩格的子句是上面语句的子部分。

两个表的自然联接运算重点是where条件中如何反映两个表的联接属性。这里方法是一表的联接属性必须等于多表的联接属性,一般表述为:

where 一表. 联接属性 = 多表.联接属性;

例如,学生表和成绩表的自然联接运算,对应的select语句为:

select *;

from 学生表,成绩表;

where学生表 . 学号 =成绩表. 学号

2.2多表查询的select编写方法

多表查询可以认为是两表自然联接例子的扩展,首先是其中两个表实现自然联接运算形成一个新的表,然后这个新表再和其它表格进行自然联接运算,又形成一个新的表,如此反复最终形成一个表。其一般形式为:

select字段列表;

from 多个数据表;

where 条件;

group by 条件;

having条件;

order by 条件

由于两个具有主、外键约束的表之间自然联接的结果是一个新表,其字段为这两个表字段的叠加,记录为多表的记录。当给的查询涉及多个数据表时,通过给定的已知,沿数据表间的关联来求未知,这就是基于数据库模式的导航select编写方法。一个更为简单的方法是在数据模式的基础上使用填字格方式,具体为:

select字段列表:要求输出的字段出现在“字段列表”。

from 数据表列表:将所有具备输出字段的数据表加入到“数据表列表”,如果这样选择的多个数据表在给定的数据模式中不存在关联,则必须将关联这些数据表的中间数据表也加入到“数据表列表”,虽然它们不需要输出字段值。

where 条件:使用“表名.主键 =表名.外键”实现二表间的关联,多表间的联接使用“表名.主键 =表名.外键 and 表名.主键 =表名.外键”方式,最后再“and已知条件”。

下面,我们通过一个例子来说明求解过程。

案例:求计算机科学技术2003-01班的所有成绩单。

分析:“计算机科学技术2003-01班”对应为“班级表”中的“专业名称”,这是已知条件。待求为“学生表”的“姓名”;“课程”表的“课程号、课程名”;“成绩表”的“成绩”。由此得到对应的select语句为:

select 姓名,课程表.课程号,课程名,成绩;

from 学生表,成绩表,课程表,班级表;

where 学生表.学号 = 成绩表.学号;

and 课程表.课程号 = 成绩表.课程号;

and 班级表.班级号 = 学生表.班级号;

and 专业名称 = "计算机科学技术2003-01班"

3结语

数据库模式图就是数据库管理员预先在数据库中定义的行为模式。本文引入“数据库导航”概念,让学生自己学会利用数据库模式图,通过已知来求未知。将select含多表的查询转换为两两表间的自然联接运算,进而,变成两个表的自然联接运算,最终,转换成为一个表的投影和选择操作。这样,学生能够理解并编写正确的SQL查询语句。

参考文献:

[1] 黎升洪,杨波,沈波. Visual FoxPro面向对象程序设计教程[M]. 2版. 北京:科学出版社,2007.

[2] 王珊,萨师煊. 数据库系统概论[M]. 4版. 北京:高等教育出版社,2006.

Didactics of Multi-Tables Select Sentence in SQL Based on the Navigation of Database Scheme

LI Sheng-hong

(School of Information & Technology, Jiangxi University of Finance & Economics, Nanchang 330013, China)

Abstract: The didactics of select sentence in SQL is a keystone. A new didactics is proposed for the didactics based on the relationships, “navigation”, among the tables of a database scheme according to the principle of natural joint in relationship algebra. The mapping between a problem and a select sentence can be constructed by means of the navigation between tables, where a solution can be solved from a given condition. The effect of the didactics is remarkable.

Key words: SQL query; didactics; database scheme; select sentence in SQL; natural joint