[摘 要] 本文以SQL Server 2000为数据库实验平台,基于结构化查询语言SQL这一综合通用的、功能极强的关系数据库语言功能,从应用角度,探析合理利用SQL语言功能快速实现实验室信息管理系统的信息处理,并对如何提高其执行效率给出具体分析与处理方法。此应用过程对处理其他数据库管理系统也具有通用性。
[关键词] SQL语言; 执行效率; 数据库系统; 实验室信息管理系统
doi : 10 . 3969 / j . issn . 1673 - 0194 . 2010 . 14 . 042
[中图分类号]G642; TP311.3 [文献标识码]A [文章编号]1673 - 0194(2010)14- 0109 - 03
1引言
我国高等教育快速发展的今天,利用计算机技术管理数据已成为数据管理的必然趋势。结构化查询语言SQL是英文Structured Query Language的缩写。按照美国国家标准协会(ANSI)的规定,SQL被作为关系型数据库管理系统的标准语言,SQL语言是功能很强的、公认的数据库管理系统的检索工具,SQL语句可以用来执行各种操作。目前,绝大多数流行的关系数据管理系统(DBMS),如Oracle,Sybase,SQL Server,Visual FoxPro等都采用了SQL标准语言。尽管查询数据是SQL最重要的功能之一,但它绝不仅仅是一个查询工具,它还为用户提供了用于控制数据库管理系统的所有功能,包括数据定义、数据操纵、数据控制等功能。使用SQL语句来处理数据,不仅方便了编程,而且注重SQL 语言不同实现方式之间执行效率性能上的差异,还可以提高数据处理的速度。
“实验室信息管理系统”是为适合高等院校实验室需求及管理特点而开发的一套具有综合管理功能的系统软件。而在基于结构化查询语言SQL语言对实验室信息管理系统的信息处理应用中,对SQL语言的索引、临时表、谓词嵌套、SQL 语言开发应用系统技术等功能,不仅在执行速度和效率问题上进行探析,而且在对其各功能实现的分析研究中,可以很方便地实现对数据库的浏览、修改、查询和报表输出,以减轻管理人员的基础工作量,从而更好地完成实验教学任务,提高实验室教学管理效率。同时对处理其他数据库管理系统也具有实用性。
2实验信息管理系统
“实验室信息管理系统”开发中,数据库采用SQL Server 2000,设计上采用了Java,ASP.NET,VB.NET多种软件完成,各模块的划分与设计基于 B/S 和 C/ S体系结构实现。
2.1 实验室管理系统
根据系统设计的目标, 实验室管理系统所具备的主要功能有:学生可以查阅与自己相关的实践课程信息,教师与系统管理员可以实现对学生与教师基本信息、实验室课程教学计划、学生实验成绩信息的录入修改删除查询、数据库的管理操作,并可以实现按照用户自定义条件进行查询统计生成报表。本文讨论的问题涉及以上一部分功能。
2.2 系统的数据库
在实际管理中,用到许多由SQL Server 2000 建立的实验室信息管理系统的数据库中的数据表,以下是本文研究中所涉及的数据表,内容如下:
(1) 实验教师科研信息表(kyxx):(tch_ID)、(xueke)、 (kytm),(cbs)、(fbrq)、 (kyfz)。
(2) 教师基本信息表(tch_info):(tch_ID)、(tch_name)、(zhicheng_ID)、(tch_age)。
(3)学生实验成绩数据表(stu_achieve):(stu_ID)、(stu_su)、(stu_ax)、(stu_at)、(stu_bx), (stu_as)。
(4) 实验课程表(course_info):(lab_ID)、(tch_name)、(class_name)、(time_name)、(tutorshipID)。
(5) 实验室基本信息表(lab_info):(lab_ID)、(lab_name)、(lab_computornum)、(lab_introducton)。
(6) 学生基本信息表(stu_info):(stu_ID)、(stu_name)、(class_ID)。
3SQL语言信息处理中的执行效率
以下探讨SQL语言在SQL Server 2000中如何提高执行效率,快速实现实验室信息管理信息处理的具体应用。
3.1 用视图整理多个实验系统数据表
在实验教学应用中往往需要查询的数据,分别放在实验室管理系统的多个数据库表中,将实际需要的属于同一类数据的表做成视图。视图是由基表或其他视图导出的虚表,当使用建立好的一个视图时,就如调用一个自由表。利用视图限制对特定行或列的访问, 可以增强系统的安全性, 利用视图连接相关的表, 可以简化程序员的工作,提高编译效率。
如从教师基本信息表(tch_info)、实验教师科研信息表(kyxx)中查询教师科研的信息。
CREATEviewtch_view as
SELECT kyxx.tch_id,tch_name, zhicheng_id, xueke, kytm,cbs,fbrq,kyfz
From tch_info , kyxx
Where kyxx.tch_id = tch_info.tch_id
在实验室这个大的信息管理系统中,从视图中取出数据往往要读所有的表, 实际应用中是采用将查询结合起来综合考虑,适当建立视图的方法。
3.2 用联合(UNION)查询实验课内容
在实际应用中,用OR 子句可以分解成多个查询, 但OR是低效率的子句。为提高效率可通过UNION 连接多个查询。OR与UNION,两者的速度都与使用索引有关,如果查询需要用到联合索引,用UNION all 执行的效率更高。
如对于学生实验成绩数据表(stu_achieve), 查询平时成绩或考勤成绩不合格的学生的学号。
(1) SELECTstu_ID
FROM stu_achieve
WHERE stu_ax<’60’
ORstu_at <’60’
(2) SELECTstu_ID
FROM stu_achieve
WHERE stu_ax<’60’
UNIONSELECTstu_ID
FROM stu_achieve WHERE stu_at <’60’
在实验室管理系统的应用中,以上所采用的OR子句与UNION子句中,通常使用联合(UNION)可以加速执行效率。但如要在stu_info这个大表上执行这种查询, 在请求下会建立大量数据的临时表以保留每一步的查询结果, 如果这个临时表很大, 就有可能占满所有的临时存储空间,会影响执行效率。
3.3 查询实验成绩使用聚集函数与相关子查询
实验室管理系统用到聚集函数与相关子查询,内部函数SQL 标准中只使用 COUNT 、SUM 、AVG 、MAX 、MIN 函数,称之为聚集函数( Set Function ),当一个子查询涉及一个来自外部查询的列时,称为相关子查询(Correlated Subquery)。使用聚集函数中避免相关子查询,由于一个列的标签同时在主查询和where子句中的查询中出现,很可能当主查询中的列值改变之后,子查询必须重新查询一次。实际应用中,在子查询中过滤掉尽可能多的行,同时尽量避免使用子查询处理实验室管理系统中的数据。
如查询各实验课程的平均成绩,按课程号分组且只选择考勤成绩合格的学生成绩。
SELECT stu_su as 科目,avg(stu_bx) as 平均成绩
FROM stu_achieve
GROUP BY stu_su having stu_at >’60’
其中HAVING 子句用于在计算出聚集之后对行的查询进行控制。
3.4 合理使用索引查询实验室排课信息
在SQL 的查询语句中,使用索引这一重要的数据结构,目的就是为了提高查询效率。在对经常进行连接,但是没有指定为外键的列上建立索引,而对不经常连接的字段则由优化器自动生成索引。在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。不可简单使用index,进行耗空间的全文索引也不可取。对CHAR 类型查询时,查询耗时和字段值总长度成正比,所以不能用CHAR 类型。而对VARCHAR类型. 对于字段的值很长的要建立全文索引。
如对课程进行查询, 对课程表的 lab_ID字段建立了index索引。返回某一实验室的排课信息。
SELECTlab_name,tch_name,class_name,time_
name,tutorshipID
FROMcourse_info,lab_info
WHERElab_ID = ’2’
在SQL语句的子查询的条件where子句中使用了表course_info的索引字段lab_ID,where 子句,它不仅指出查询的条件,同时它的编写方法、编写次序和编写方案直接关系到整个查询的效率。因此对表course_info的扫描只在与索引有关的范围而无须对整个表扫描, 执行效率很高。
3.5 使用临时表查询全院学生的成绩信息
SQL语句查询为了减少对公共数据的访问,提高系统的运行效率,在系统中只对公共数据进行一次查询,将查询结果保存到系统临时表中,当系统中其他模块需要该数据时,直接从临时表提取,查询结束清除变量和关闭临时表即可。 在实验室管理关系系统中建立临时存放公共数据的临时表student1,将从课程表中查询的数据放入student1 中的SQL 语句如下:
insert into student1
(lab_ID,tch_name,class_name,time_name, tutorshipID)
SELECTlab_ID,tch_name,class_name,time_name,tutorshipID
FROMcourse_info
如在全院学生信息这一很大的事务表中, 包含了百万以上行, 而要查询其中的几百条数据, 且执行多次,这时可创建一个临时表, 并在临时表的一个子集建立必要的索引,这样能加速查询,提高执行效率。它有助于避免多重排序操作,还能简化优化器的工作,临时表创建后不会反映主表的修改,但防止频繁修改主表数据以免丢失数据。
例如: (1) 查询全院学生的实验成绩信息。
SELECT stu_ID,stu_name,stu_xu,stu_ax,stu_at,stu_bx
FROM stu_achieve,stu_info
WHERE stu_achieve.stu_ID = stu_info.stu_ID
ORDER BY stu_achieve.stu_ID
(2) 将2008级学生的成绩找出来放在一个临时表achieve_temp中,并按学生的学号进行排序:
insert into achieve_temp(stu_ID,stu_name,stu_xu,stu_ax,stu_at,stu_bx)
select stu_ID,stu_name,stu_xu,stu_ax,stu_at,stu_bx
FROM stu_achieve,stu_info
WHERE stu_achieve.stu_ID = stu_info.stu_ID
And stu_achieve.stu_ID >=’802000000’
And stu_achieve.stu_ID <=’802999999’
ORDER BY stu_achieve.stu_ID
(3) 在临时表中查询:
SELECT * FROM achieve_temp
WHERE stu_ID = ’802080134’
临时表中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘I/O,查询工作量可以得到大幅减少,提高执行效率。
3.6 使用谓词嵌套查询
使用谓词嵌套查询使得查询求解层次分明,有结构化程序之特征,如果要用子查询,那就用EXISTS替代IN、用NOT EXISTS替代NOT IN。因为EXISTS引入的子查询只是测试是否存在符合子查询中指定条件的行,效率较高。NOT IN都是最低效的。因为它对子查询中的全表执行。
例如:查询实验课中“程序设计基础”不及格的学生的学号,姓名。
SELECT stu_ID,stu_name
FROMstu_info
WHERE stu_ID in
SELECTstu_ID
FROMstu_achieve
WHERE stu_bx <’60’and stu_su = ’程序设计基础’
使用谓词EXISTS的嵌套查询。
SELECT stu_ID,stu_name
FROMstu_info
WHERE EXISTS(
SELECT*
FROMstu_achieve
WHERE stu_bx <’60’and stu_su =’程序设计基础’)
3.7 SQL 编程中存储过程的使用
使用SQL 编程来访问和管理数据库中数据的方式主要有:嵌入式SQL,PL/SQL,ODBC,JDBC 以及OLEDB编程等方式。现仅对经常使用的嵌入式SQL 和PL/SQL 来做分析。PL/SQL 是编写数据库存储过程的一种过程语言,存储过程允许预定义通用数据库操作,只需要调用存储过程就可以启用这些操作,这种方式可有效地提高执行效率并减少可能出现错误的机会。开发存储过程,可以提高软件的可维护性和系统的运行速度。
例如:定义一个存储过程用于向学生基本信息表(stu_info)中插入新记录。
CREATE proc insert_stuinfo(@stuid int,@stu_name varchar(15),@classid int)
AS
BEGIN
INSERT into stu_info(stu_id,stu_name,class_id)values(@stuid,@stu_name,@classid)
END
四、结束语
实验室教学管理是学院教学工作的重要组成部分, 通过以上分析,用SQL 语言来完成对实验室管理系统信息数据库的信息处理,是可以采用多种SQL 语句的编写策略来提高查询对象的执行效率,因此在SQL 语言功能实现的同时,探讨SQL 语言不同实现方式之间的执行效率的性能上的差异, 要根据数据库应用系统的实际情况给出适当的调整来满足系统的运行要求,同时还要采取适当的数据策略, 尽量减少查询范围,减少计算机系统需要计算的数据,编写优化的SQL 语句,既可以提高效率, 又可以提高信息的正确度,随着大型数据库的使用, SQL 语言将不仅用于实验室信息管理, 而且会被广泛地应用于各种信息处理领域。
主要参考文献
[1] [美]詹姆斯·R·格罗夫(James R Groff),等. SQL完全手册[M]. 第2版,王丽敏,等译. 北京:电子工业出版社,2006.
[2] 王寅永,李降宇,等. SQL Server深入详解[M]. 北京:电子工业出版社,2008.
[3] 韩景田, 陈小义. 完善实验室管理的方法探究[J]. 中国科技信息, 2005(10).
[4] 罗运模,王珊. SQL Server数据库系统基础[M]. 北京:电子工业出版社,2005.
[5] 卢镇波,李青. 编程实例入门与提高[M].北京:电子工业出版社,2005.