Access数据库设计与查询分析

玉冰

（云南农业职业技术学院 云南省昆明市 650031）

1 ODBC技术

ODBC技术是开放服务结构中有关数据库的一个组成部分，它提供了一组对数据库访问的标准API（应用程序编程接口）。“ODBC数据源管理器”提供了三种DSN数据源[1]，分别为用户DSN数据源、系统DSN数据源和文件DSN数据源。ODBC的连接（必须先注册—DSN）常用接口技术（表1）。

表1：常用接口类型比较

ODBC连接数据库方式：

（1）Access数据库的连接：

Set con=Server＞CreateObject(“adb.Connection”)

Con.Open”DSN”=”数据源名称”

（2）SQL Server数据库的连接：

Set con=Server.CreatedObject(“adb.Connection”)

Con.connectionstring=”DSN=数据源名称;UID=ka;PWD=ka

Con.open;

2 OLEDB技术

OLEDB[2]是面向不同的数据源的低层应用接口。OLEDB不仅包括 ODBC的结构化查询语言（SQL），还具有面向其他非SQL数据类型的通道。OLEDB定义了一组COM接口，对各种数据库服务进行封装，并允许创建组件，以便实现这些服务。OLEDB 包含了连接 ODBC的底层“连接器”技术，OLEDB最主要三个部分组成：

data providers

Access中的mdb文件，就是Data Providers

data consumers

一个应用程序或网页都是OLEDB数据使用者。

service components

数据服务部件

OLEDB连接数据库步骤：

（1）access数据库连接方法

set conn=Server.CreateObject("adb.Connection")

con.open "Provider=Microsoft.Jet.OLEDB.4.0;datasource="&server.MapPath("dbname（绝对路径）")&";persist security info=false"

（2）SQL Server数据库连接方法

set con=server.CreateObject("adb.Connection")

sql="Provider=SQLOLEDB; datasource=(local); initial catalog=mdbname;

user id=ka;password=ka;"

con.open(sql)

3 ADO[3]组件

ADO 是一个基于com的自动化连接组件。它提供了编程语言和数据访问方式一个中间层。允许开发人员编写访问数据的代码而不用关心数据库是如何实现的，而只用关心到数据库的连接。

整个ADO由两个部分组成：

3.1 NET数据提供程序（.NET Data Provider）

（1）connection对象：在应用程序与数据库之间建立连接关系。

（2）command对象：对数据库进行访问操作。

（3）dataset对象：是虚拟数据源访问。

（4）datareader对象：从数据源读取返回数据。

（5）dataadapter对象：从数据源中检索出数据并填充到DataSet中。

3.2 数据集（DataSet）

支持ADO.NET断开式、分布式数据方案的核心，相当于内存中暂存的数据库。数据库访问流程模型如图1所示，Acces数据库访问流程图如图2所示。

图1：数据库访问流程模型

图2：Acces数据库访问流程图

4 据库操作语句

4.1 代码访问

灵活性较强，可根据需要进行绑定设置。满足数据显示要求多样化，编写代码较多，思路要清晰。

4.2 控件访问

比较方便，实用直观，数据显示方式有局限性。

（1）对读取的数据需要计算和加工后再显示

（2）显示样式和数据内容有关联

（3）用SQL语句难以一次完成的查询

4.3 数据库设计时要遵循的三范式

（1）无重复列，且每列为最小信息单元。表中喜欢颜色这一列有多种选择，是不符合数据库要求的，最好将颜色做成一张表用编号来表示颜色，调用颜色时可以调用编号人名和颜色之间是一对多的关系。

（2）无重复行，行内信息不可依赖于主键。

（3）要求一个数据库的表中不包含已经在其它表中已包含的非主要关键字。

在上面的表中已经有了st_name字段表示姓名，在下面的表中就不能有包含姓名的字段，而要用st_id来表示。

5 通过一个成绩表查询的案例

5.1 SQL语句功能

SQL (Structured Query Language) 数据操纵和数据定义语言，这种语言具有交互性特点，能为用户访问数据库提供极大的便利。

在SQL中，关系模式称为(base table)；存储模式称为(stored file)；子模式称为(view)，我们常用的Access、SQL-server、oracle都是关系型数据库。

5.2 查询语句

SELECT * 数据库

FROM[TABLE] 查询表

WHERE[FIELD]=3 条件语句

PRDER BY[FELD2] 字段排序

DESC 反向排序（无 正向排序）

SELECT *

FROM[table]WHERE[field]=3ORDER BY [field2]DESC

以一个学生年度考试数据查询为例：要查询 小李 2019年度第二学期 数学 72.4

ASP.NET访问数据库方法（表2）。

表2：学生成绩表

如图3所示的表是我们平常常用的一个种表格，但它并不符合数据库的三范式，将它拆分为四张表，通过拆分的表就符合数据库的三范式。在最主要的分数表中，除了分数外都用数字方式表示，它与其他的表之间都通过数字来进行链接。

图3：数据表之间的关系

将四张表拖入到T_student、T_score、 T_testtype、T_subject拖入到查询生成器中，然后切换到【SQL视图】在Selec后面加上*号，就可以查看到全视图形式，每个同学的所有信息我们都可以获取查询到。

5.2.1 简单查询

查询输出2019年度第一学期期末考试，数学大于80分的学生，我们分析需要输出姓名、科目、分数。使输出结果符合用户习惯。

得到的数据关键是Where子句：

WHERE TT_ID=1 AND SBJ_ID=2 AND SC_SCORE＞80;

5.2.2 交叉查询

查询出每个学生的多门成绩，就要应用交叉查询方式要查询出信息我们要进行两部操作：

（1）通过结构库关系中获取信息。

分别修改表T_student的获取字段为ST_name，T_subject的获取字段为SBJ_NAME。

（2）从表中获取下面信息（表3）。

表3：交叉查询结果

打开Access，建立好表，在【查询向导】中选择【交叉表查询向导】

（1）行标签选择学生姓名SC_STID字段

（2）列标签选择科目SC_SBJID字段

（3）交叉区域选择SC_SCORE字段

选中T_SCORE表，打开设计视图，将其余三张表拖入，在字段选项中分别选择学生姓名ST_NAME、考试科目SBJ_NAME、分值SC_SCORE。这样在一张表中就可以查询出多个学生的各门成绩，达到了在一张表中显示多个信息。