超级会员免费看
SQL 数据处理与 DAO 对象详解
复杂 SQL 语句的挑战
在处理 SQL 语句时，有些复杂的语句可能需要花费大量时间去理解和消化。例如下面这个语句：
" WHERE (W3.WidgetID = W2.WidgetID)" & _
" And (W3.ModuleID = W2.ModuleID)" & _
" And (W3.PartID = W2.PartID)" & _
" Group BY W3.WidgetID, W3.ModuleID, W3.PartID)" & _
" AND (W2.WidgetID = W1.WidgetID) And (W2.ModuleID = W1.ModuleID)" & _
" GROUP BY W2.WidgetID, W2.ModuleID)"
在实际应用中，使用这样复杂的语句时需要谨慎考虑。
数据从垂直排列转换为水平排列
假设有两个表，分别记录个人统计信息。
| EmpID | Name |
| — | — |
| 1 | Beethoven |
| 2 | Chopin |
| 3 | Mozart |
| 4 | Schubert |
| 5 | Brahms |
| 6 | Liszt |
| EmpID | StatType | Value |
|---|---|---|
| 1 | Age | 45 |
| 1 | Height | 63 |
| 1 | Weight | 150 |
| 2 | Age | 46 |
| 2 | Height | 67 |
| 3 | Age | 35 |
| 3 | Weight | 135 |
| 4 | Age | 44 |
| 5 | Height | 76 |
我们希望将数据以水平方式展示，如下表：
| EmpID | Name | Age | Height | Weight |
| — | — | — | — | — |
| 1 | Beethoven | 45 | 63 | 150 |
| 2 | Chopin | 46 | 67 | |
| 3 | Mozart | 35 | | 135 |
| 4 | Schubert | 44 | | |
| 5 | Brahms | | 76 | |
| 6 | Liszt | | | |
解决方案
可以使用以下 SQL 语句实现：
SELECT DISTINCT Composers.EmpID, Name,
(SELECT Value FROM ComposerData As T2 WHERE
(T2.StatType='Age') And (T2.EmpID=Composers.EmpID)) As Age,
(SELECT Value FROM ComposerData As T2 WHERE
(T2.StatType='Height') And (T2.EmpID=Composers.EmpID)) As Height,
(SELECT Value FROM ComposerData As T2 WHERE
(T2.StatType='Weight') And (T2.EmpID=Composers.EmpID)) As Weight
FROM (Composers INNER JOIN ComposerData
ON Composers.EmpID=ComposerData.EmpID)
在这个语句中，主
SELECT
子句里包含多个
SELECT
子查询语句。例如：
(SELECT Value FROM ComposerData As T2 WHERE
(T2.StatType='Age') And (T2.EmpID=Composers.EmpID)) As Age,
该子句用于选择主
SELECT
子句所选人员的年龄。随着统计数据数量的增加，这个 SQL 语句会变得更加复杂。
也可以使用 DAO 提供的替代解决方案，示例代码如下：
Private Sub VerticalToHorizontal2()
Dim db As Database
Set db = CurrentDb
Dim rsEmp As Recordset
Dim rsData As Recordset
Dim rsHor As Recordset
Set rsEmp = db.OpenRecordset("Composers")
Set rsHor = db.OpenRecordset("ComposersOutput")
Do While Not rsEmp.EOF
Set rsData = db.OpenRecordset( _
"SELECT * FROM ComposerData WHERE EmpID = " & rsEmp!EmpID)
rsHor.AddNew
rsHor!EmpID = rsEmp!EmpID
rsHor!Name = rsEmp!Name
Do While Not rsData.EOF
rsHor.Fields(rsData!StatType).Value = rsData!Value
rsData.MoveNext
Loop
rsHor.Update
rsEmp.MoveNext
Loop
rsEmp.Close
rsData.Close
rsHor.Close
End Sub
匹配问题
有两个表，一个记录程序员及其语言技能，另一个记录不同工作的语言要求。
| Name | Language |
| — | — |
| Blaise Pascal | VB |
| Blaise Pascal | C++ |
| Blaise Pascal | Access |
| Blaise Pascal | Excel |
| Gauss | VB |
| Gauss | Access |
| Gauss | Delphi |
| Gauss | SQL Server |
| Smith | C++ |
| Von Neuman | VB |
| Von Neuman | C++ |
| Wordsworth | Delphi |
| Wordsworth | C++ |
| Wordsworth | Word |
| JobID | Language |
|---|---|
| 1 | VB |
| 1 | Access |
| 2 | C++ |
| 3 | C++ |
| 3 | SQL Server |
| 4 | Delphi |
| 5 | VB |
| 5 | Pascal |
我们要展示工作列表以及相应的合格程序员。
解决方案
可以使用以下 SQL 语句：
SELECT ProgrammingJobs.JobID, Programmers.Name
FROM Programmers INNER JOIN ProgrammingJobs
ON Programmers.Language = ProgrammingJobs.Language
GROUP BY ProgrammingJobs.JobID, Programmers.Name
HAVING Count(Programmers.Language)=
(SELECT Count([Language]) FROM ProgrammingJobs AS PJ
WHERE PJ.JobID=ProgrammingJobs.JobID)
操作步骤如下：
1. 首先，通过
Language
字段对两个表进行
INNER JOIN
操作，为每个工作/程序员对创建一组记录。
2. 然后，按工作/程序员对分组记录。
3. 最后，使用
HAVING
子句比较这些记录的数量与该工作所需语言的数量。
结果如下表：
| JobID | Name |
| — | — |
| 1 | Blaise Pascal |
| 1 | Gauss |
| 2 | Blaise Pascal |
| 2 | Smith |
| 2 | Von Neuman |
| 2 | Wordsworth |
| 4 | Gauss |
| 4 | Wordsworth |
集合相等问题
有一个表记录了五个集合及其成员。
| Set | Member |
| — | — |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |
| 4 | 4 |
| 5 | 1 |
| 5 | 2 |
| 5 | 8 |
我们要找出哪些集合是相等的。
解决方案
可以使用以下 SQL 语句：
SELECT Equality.Set, E2.Set
FROM Equality INNER JOIN Equality AS E2 ON
(Equality.Member = E2.Member) And (Equality.Set < E2.Set)
GROUP BY Equality.Set, E2.Set
HAVING
((SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=Equality.Set) =
(SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=E2.Set))
AND
(Count(Equality.Set) =
(SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=E2.Set))
操作步骤如下：
1. 通过
(Equality.Member = E2.Member) And (Equality.Set < E2.Set)
条件对表进行
INNER JOIN
操作，避免返回重复的集合对。
2. 按集合对分组记录。
3. 使用
HAVING
子句判断两个集合是否相等，即集合的大小和交集的大小都相同。
DAO 3.0/3.5 集合、属性和方法
Microsoft Access 97 中的对象浏览器可用于探索 DAO 对象层次结构，但有时硬拷贝参考也很有用。以下是 DAO 中的一些类及其描述：
| Class name | Version | Description |
| — | — | — |
| Connection | 3.5 | 一个开放的 ODBCDirect 连接 |
| Connections | 3.5 | 一个 Connection 对象的集合 |
| Container | 3.0/3.5 | 用于存储预定义对象类型的信息 |
| Containers | 3.0/3.5 | 一个 Container 对象的集合 |
| Database | 3.0/3.5 | 一个开放的数据库 |
| Databases | 3.0/3.5 | 一个 Database 对象的集合 |
| DBEngine | 3.0/3.5 | Jet 数据库引擎 |
| Document | 3.0/3.5 | 关于保存的预定义对象的信息 |
| Documents | 3.0/3.5 | 一个 Document 对象的集合 |
| Error | 3.0/3.5 | 关于 DAO 对象发生的任何错误的信息 |
| Errors | 3.0/3.5 | 一个 Error 对象的集合 |
| Field | 3.0/3.5 | 表、查询、索引、关系或记录集的一部分列 |
| Fields | 3.0/3.5 | 一个 Field 对象的集合 |
| Group | 3.0/3.5 | 用户账户组 |
| Groups | 3.0/3.5 | 一个 Group 对象的集合 |
| Index | 3.0/3.5 | 用于对值进行排序并提供对记录集的有效访问的对象 |
| Indexes | 3.0/3.5 | 一个 Index 对象的集合 |
| Parameter | 3.0/3.5 | 参数查询的参数 |
| Parameters | 3.0/3.5 | 一个 Parameter 对象的集合 |
| Properties | 3.0/3.5 | 一个 Property 对象的集合 |
| Property | 3.0/3.5 | 内置或用户定义的属性 |
| QueryDef | 3.0/3.5 | 保存的查询定义 |
| QueryDefs | 3.0/3.5 | 一个 Querydef 对象的集合 |
| Recordset | 3.0/3.5 | 表中记录或查询结果的表示 |
| Recordsets | 3.0/3.5 | 一个 Recordset 对象的集合 |
| Relation | 3.0/3.5 | 表和查询中字段之间的关系 |
| Relations | 3.0/3.5 | 一个 Relation 对象的集合 |
| TableDef | 3.0/3.5 | 保存的表定义 |
| TableDefs | 3.0/3.5 | 一个 Tabledef 对象的集合 |
| User | 3.0/3.5 | 用户账户 |
| Users | 3.0/3.5 | 一个 User 对象的集合 |
| Workspace | 3.0/3.5 | Jet 数据库引擎的会话 |
| Workspaces | 3.0/3.5 | 一个 Workspace 对象的集合 |
这些类各自有其对应的属性、方法和集合，后续将详细介绍。
SQL 数据处理与 DAO 对象详解
集合对象
每个集合对象都支持一个方法和一个属性：
| 分类 | 名称 | 类型 | 版本 | 描述 |
| — | — | — | — | — |
| 方法 | Refresh | Sub | 3.0/3.5 | 更新集合以反映最近的更改 |
| 属性 | Count | Integer | 3.0/3.5 | 集合中对象的数量（只读） |
对于成员可以动态添加和删除的 DynaCollection 对象，还有另外两个方法：
| 方法 | 参数 | 返回值 | 版本 | 描述 |
| — | — | — | — | — |
| Append | Object As Object | Sub | 3.0/3.5 | 将对象追加到集合中 |
| Delete | Name As String | Sub | 3.0/3.5 | 从集合中删除对象 |
Connection 对象（仅 DAO 3.5）
集合
| 属性 | 类型 | 版本 | 描述 |
|---|---|---|---|
| Database | Database | 3.5 | 返回此 Connection 对象的 Database 引用 |
| QueryDefs | QueryDefs | 3.5 | QueryDef 对象的集合 |
| Recordsets | RecordSets | 3.5 | 此连接中打开的 Recordset 对象的集合 |
方法
| 方法 | 参数 | 返回值 | 版本 | 描述 |
|---|---|---|---|---|
| Cancel | Sub | 3.5 | 取消异步 Execute 或 OpenRecordset 方法的执行 | |
| Close | Sub | 3.5 | 关闭 Connection 对象及其包含的所有内容 | |
| CreateQueryDef | [Name], [SQLText] | QueryDef | 3.5 | 创建新的 QueryDef 对象 |
| Execute | Query As String, [Options] | Sub | 3.5 | 执行 SQL 语句 |
| OpenRecordSet | Name As String, [Type], [Options], [LockEdit] | Recordset | 3.5 | 创建新的 Recordset 对象 |
属性
| 属性 | 类型 | 版本 | 描述 |
|---|---|---|---|
| Connect | String | 3.5 | 从 OpenDatabase 方法的 Connect 参数保存的信息 |
| Name | String | 3.5 | Connection 对象的名称 |
| QueryTimeout | Integer | 3.5 | 执行 ODBC 查询时超时前的秒数 |
| RecordsAffected | Long | 3.5 | 最后一次 Execute 方法影响的记录数 |
| StillExecuting | Boolean | 3.5 | 指示异步方法调用是否仍在执行 |
| Transactions | Boolean | 3.5 | 指示 DAO 对象是否支持事务 |
| Updatable | Boolean | 3.5 | 指示连接是否允许更新数据 |
Container 对象
集合
| 属性 | 类型 | 版本 | 描述 |
|---|---|---|---|
| Documents | Documents | 3.0/3.5 | 容器中 Document 对象的集合 |
属性
| 属性 | 类型 | 版本 | 描述 |
|---|---|---|---|
| AllPermissions | Long | 3.0/3.5 | 适用于当前用户名的所有权限 |
| Inherit | Boolean | 3.0/3.5 | 指示新 Document 对象是否继承默认权限属性 |
| Name | String | 3.0/3.5 | 此对象的名称 |
| Owner | String | 3.0/3.5 | 设置或返回对象的所有者 |
| Permissions | Long | 3.0/3.5 | 设置或返回由 UserName 属性指示的用户或组访问对象时的权限 |
| UserName | String | 3.0/3.5 | Permissions 属性适用的用户或组 |
Database 对象
集合
| 属性 | 类型 | 版本 | 描述 |
|---|---|---|---|
| Connection | Connection | 3.5 | 开放的 ODBCDirect 连接 |
| Containers | Containers | 3.0/3.5 | Database 对象中 Container 对象的集合 |
| QueryDefs | QueryDefs | 3.0/3.5 | Database 对象中 QueryDef 对象的集合 |
| Recordsets | Recordsets | 3.0/3.5 | Database 对象中打开的 Recordset 对象的集合 |
| Relations | Relations | 3.0/3.5 | Database 对象中 Relation 对象的集合 |
| TableDefs | TableDefs | 3.0/3.5 | Database 对象中 TableDef 对象的集合 |
方法
| 方法 | 参数 | 返回值 | 版本 | 描述 |
|---|---|---|---|---|
| Close | Sub | 3.0/3.5 | 关闭 Database 对象及其包含的所有内容 | |
| CreateProperty | [Name], [Type], [Value], [DDL] | Property | 3.0/3.5 | 创建新的用户定义 Property 对象 |
| CreateQueryDef | [Name], [SQLText] | QueryDef | 3.0/3.5 | 创建新的 QueryDef 对象 |
| CreateRelation | [Name], [Table], [ForeignTable], [Attributes] | Relation | 3.0/3.5 | 创建新的 Relation 对象 |
| CreateTableDef | [Name], [Attributes], [SourceTableName], [Connect] | TableDef | 3.0/3.5 | 创建新的 TableDef 对象 |
| Execute | Query As String, [Options] | Sub | 3.0/3.5 | 执行查询 |
| MakeReplica | PathName As String, Description As String, [Options] | Sub | 3.0/3.5 | 根据当前可复制数据库创建新的副本 |
| NewPassword | bstrOld As String, bstrNew As String | Sub | 3.0/3.5 | 更改现有数据库的密码 |
| OpenRecordset | Name As String, [Type], [Options] | Recordset | 3.0/3.5 | 创建新的 Recordset 对象 |
| PopulatePartial | DbPathName As String | Sub | 3.5 | 同步部分副本 |
| Synchronize | DbPathName As String, [ExchangeType] | Sub | 3.0/3.5 | 同步数据库对象 |
属性
| 属性 | 类型 | 版本 | 描述 |
|---|---|---|---|
| CollatingOrder | Long | 3.0/3.5 | 定义用于排序和比较的顺序 |
| Connect | String | 从 OpenDatabase 方法的 Connect 参数保存的信息 | |
| DesignMasterID | String | 3.0/3.5 | 副本设计主服务器的唯一标识符 |
| Name | String | 3.0/3.5 | 此 Database 对象的名称 |
| QueryTimeout | Integer | 3.0/3.5 | 执行 ODBC 查询时超时前的秒数 |
| RecordsAffected | Long | 3.0/3.5 | 最后一次 Execute 方法影响的记录数 |
| ReplicaID | String | 3.0/3.5 | 副本的唯一标识符 |
| Transactions | Boolean | 3.0/3.5 | 指示 Database 对象是否支持事务 |
| Updatable | Boolean | 3.0/3.5 | 指示 Database 对象是否可以修改 |
| Version | String | 3.0/3.5 | Database 对象格式的版本号 |
DBEngine 对象
集合
| 属性 | 类型 | 版本 | 描述 |
|---|---|---|---|
| Errors | Errors | 3.0/3.5 | 最近失败的 DAO 操作的错误集合 |
| Properties | Properties | 3.0/3.5 | Property 对象的集合 |
| Workspaces | Workspaces | 3.0/3.5 | 开放的 Workspace 对象的集合 |
方法
| 方法 | 参数 | 返回值 | 版本 | 描述 |
|---|---|---|---|---|
| BeginTrans | Sub | 3.0/3.5 | 开始新的事务 | |
| CommitTrans | Sub | 3.0 | 结束事务并保存任何更改 | |
| CommitTrans | [Option as Long] | Sub | 3.5 | 结束事务并保存任何更改 |
| CompactDatabase | SrcName As String, DstName As String, [DstConnect], [Options], [SrcConnect] | Sub | 3.0 | 压缩关闭的数据库 |
| CompactDatabase | SrcName As String, DstName As String, [DstLocale], [Options], [SrcLocale] | Sub | 3.5 | 压缩关闭的数据库 |
| CreateDatabase | Name As String, Connect As String, [Option] | Database | 3.0 | 创建新的数据库 |
| CreateDatabase | Name As String, Locale As String, [Option] | Database | 3.5 | 创建新的 .mdb 数据库 |
| CreateWorkspace | Name As String, UserName As String, Password As String | Workspace | 3.0 | 创建新的 Workspace 对象 |
| CreateWorkspace | Name As String, UserName As String, Password As String, [UseType] | Workspace | 3.5 | 创建新的 Workspace 对象 |
| Idle | [Action] | Sub | 3.0/3.5 | 完成挂起的引擎任务，如移除锁 |
| OpenConnection | Name As String, [Options], [ReadOnly], [Connect] | Connection | 3.5 | 打开与数据库的连接 |
| OpenDatabase | Name As String, [Exclusive], [ReadOnly], [Connect] | Database | 3.0 | 打开指定的数据库 |
| OpenDatabase | Name As String, [Options], [ReadOnly], [Connect] | Database | 3.5 | 打开指定的数据库 |
| RegisterDatabase | Dsn As String, Driver As String, Silent As Boolean, Attributes As String | Sub | 3.0/3.5 | 输入 ODBC 数据源的连接信息 |
| RepairDatabase | Name As String | Sub | 3.0/3.5 | 修复损坏的数据库 |
| Rollback | Sub | 3.0/3.5 | 回滚自上次 BeginTrans 以来的任何更改 | |
| SetOption | Option As Long, Value | Sub | 3.5 | 覆盖 Jet 注册表设置 |
属性
| 属性 | 类型 | 版本 | 描述 |
|---|---|---|---|
| DefaultPassword | String | 3.0/3.5 | 创建 Workspace 对象时如果没有密码则使用的默认密码 |
| DefaultType | Long | 3.5 | 设置默认的 Workspace 类型 |
| DefaultUser | String | 3.0/3.5 | 创建 Workspace 对象时如果没有用户名则使用的默认用户名 |
| IniPath | String | 3.0/3.5 | 初始化文件的路径和文件名（Jet 3.0）或包含 Jet 引擎设置的完整注册表路径（Jet 3.5） |
| LoginTimeout | Integer | 3.0/3.5 | 登录 ODBC 数据库时允许的秒数 |
| SystemDB | String | 3.0/3.5 | 系统数据库的路径 |
| Version | String | 3.0/3.5 | Jet 数据库引擎的版本号 |
Document 对象
方法
| 方法 | 参数 | 返回值 | 版本 | 描述 |
|---|---|---|---|---|
| CreateProperty | [Name], [Type], [Value], [DDL] | Property | 3.0/3.5 | 创建新的用户定义 Property 对象 |
属性
| 属性 | 类型 | 版本 | 描述 |
|---|---|---|---|
| AllPermissions | Long | 3.0/3.5 | 适用于当前用户名的所有权限 |
| Container | String | 3.0/3.5 | 此 Document 对象所属的 Container 对象的名称 |
| DateCreated | Variant | 3.0/3.5 | Document 对象创建的日期和时间 |
| LastUpdated | Variant | 3.0/3.5 | Document 对象最近更改的日期和时间 |
| Name | String | 3.0/3.5 | 此 Document 对象的名称 |
| Owner | String | 3.0/3.5 | 对象的所有者 |
| Permissions | Long | 3.0/3.5 | 访问 Document 对象的用户或组的权限 |
| UserName | String | 3.0/3.5 | Permissions 属性适用的用户或组 |
Error 对象
| 属性 | 类型 | 版本 | 描述 |
|---|---|---|---|
| Description | String | 3.0/3.5 | 错误的描述 |
| HelpContext | Long | 3.0/3.5 | 描述错误的主题的帮助上下文 ID |
| HelpFile | String | 3.0/3.5 | 描述错误的帮助文件的路径 |
| Number | Long | 3.0/3.5 | 最近错误的错误代码 |
| Source | String | 3.0/3.5 | 生成错误的对象类的名称 |
Field 对象
集合
| 属性 | 类型 | 版本 | 描述 |
|---|---|---|---|
| Properties | Properties | 3.0/3.5 | Property 对象的集合 |
方法
| 方法 | 参数 | 返回值 | 版本 | 描述 |
|---|---|---|---|---|
| AppendChunk | Val | Sub | 3.0/3.5 | 将长二进制数据写入字段 |
| CreateProperty | [Name], [Type], [Value], [DDL] | Property | 3.0/3.5 | 创建新的用户定义 Property 对象 |
| FieldSize | Long | 3.0 | 返回 FieldSize 字段 | |
| GetChunk | Offset As Long, Bytes As Long | Byte | 3.0/3.5 | 从字段读取二进制数据 |
属性
| 属性 | 类型 | 版本 | 描述 |
|---|---|---|---|
| AllowZeroLength | Boolean | 3.0/3.5 | 指示零长度字符串是否对此字段有效 |
| Attributes | Long | 3.0/3.5 | 指示此 Field 对象特征的值 |
| CollatingOrder | Long | 3.0/3.5 | 用于排序和比较的语言 |
| DataUpdatable | Boolean | 3.0/3.5 | 指示字段中的数据是否可更新 |
| DefaultValue | String | 3.0/3.5 | 新记录此字段的默认值 |
| FieldSize | Long | 3.5 | 备注字段或长二进制字段的大小 |
| ForeignName | String | 3.0/3.5 | 外部字段的名称 |
| Name | String | 3.0/3.5 | 此 Field 对象的名称 |
| OrdinalPosition | Integer | 3.0/3.5 | 此字段对象的相对位置 |
| OriginalValue | Variant | 3.5 | 批量更新开始时数据库服务器中存储的值 |
| Required | Boolean | 3.0/3.5 | 指示字段是否需要非空值 |
| Size | Long | 3.0/3.5 | 字段的最大大小 |
| SourceField | String | 3.0/3.5 | Field 对象数据的原始来源的名称 |
| SourceTable | String | 3.0/3.5 | 原始来源表的名称 |
| Type | Integer | 3.0/3.5 | 字段的数据类型 |
| ValidateOnSet | Boolean | 3.0/3.5 | 确定验证是立即发生（True 值）还是延迟到更新时（False 值） |
| ValidationRule | String | 3.0/3.5 | 成功更新必须计算为 True 的表达式 |
| ValidationText | String | 3.0/3.5 | 如果使用 ValidationRule 验证失败时显示的消息 |
| Value | Variant | 3.0/3.5 | Field 对象的数据 |
| VisibleValue | Variant | 3.5 | 当前存储在数据库服务器中的数据 |
通过以上内容，我们详细了解了 SQL 数据处理中的各种问题及解决方案，以及 DAO 3.0/3.5 中各个对象的集合、属性和方法，这些知识对于数据库开发和管理具有重要的指导意义。
扫一扫
1570
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
