Access数据库杂记

最近在做一个项目，数据库需要用到Access，平时习惯了MYSQL、SQL server数据库，用起Access数据库还是有一些不习惯，特别是SQL系列数据库都能支持存储过程、自定义函数、视图、触发器等功能，而Access就不支持这些功能，而自带函数也极有限。

下面小小总结一下在项目过程中遇到的几个问题

1、在查询中需要用到类似Substring函数的功能，Access不支持Substring函数。但可以用Mid函数代替这个功能。

Mid函数原型：Mid(列名，起始位置，长度)， 起始位置从1索引，这点与Substring一致。

 如： 

用一个表(tArea)来存储一个区域多级层次，

广东省 ID=001

广州市 ID=001001

天河区 ID=001001001

--表设计的采用ID实现多级子父类关系
现在如果知道一个区域的代码（ID=001001001）要查询它的父类名称,那么

SELECT Name FROM tArea WHERE ID=Mid('001001001',1,6)

删除父级的时候，子级所有节点都要删除（假如现在删除广东省所有数据ID=001）

DELETE FROM tArea  WHERE Mid(ID,1,3)='001'

2、case... when...  then... else...

在SQL系列数据库中，可以用这个表达式在查询语句中实现一些特殊功能。

如，一个bool类型字段(IsSend)，数据库存放1或0，现在要求在查询结果中实现用“是”或“否”显示。

在MSSQL中 ： SELECT (case IsSend when IsSend=1 then '是' else '否') AS IsSend FROM t

在Access中： SELECT IIF(IsSend=1,'是','否') AS IsSend FROM t

SQL Server中的case when，在Access中用iif()。

IIf 函数
         
根据表达式的值，来返回两部分中的其中一个。

语法

IIf(expr, truepart, falsepart)

IIf 函数的语法含有下面这些命名参数：

参数部分    描述
expr        必要参数。用来判断真伪的表达式。
truepart   必要参数。如果 expr 为 True，则返回这部分的值或表达式。
falsepart  必要参数。如果 expr 为 False，则返回这部分的值或表达式。

说明

由于 IIf 会计算 truepart 和 falsepart，虽然它只返回其中的一个。因此要注意到这个副作用。例如，如果 falsepart 产生一个被零除错误，那么程序就会发生错误，即使 expr 为 True。

IIf 函数示例
本示例使用 IIf 函数来判断 CheckIt 过程之 TestMe 参数的值，如果参数值大于 1000 则传回“Large”；否则传回“小图标”。

Function CheckIt (TestMe As Integer)
       CheckIt = IIf(TestMe > 1000, "Large ", "Small ")
End Function

3、SQL系列数据库Case或者Convert函数在Access中不支持

 用一个字段(takeDate)来存储日期时间，现在需要在查询语句中格式化显示时间。

 在Access数据库中可以用format函数格式化。

Format函数原型

Format[$] ( expr [ , fmt ] )

format 返回变体型

format$ 强制返回为文本

 

Format(Date,"YYYY年MM月DD日")
'M 个位月只显示一位，
'MM 显示两位月，
'MMM显示英文月简称,
'MMMM显示英文全名

General Date

Format$(Now,"General Date") 返回值 2006-5-25 14:56:15

Long Date 操作系统定义的长日期
Format$(Now,"Long Date") 返回值 2006年5月25日

Medium Date 中日期（yy/mmm/dd）
Format$(Now,"Medium Date") 返回值 06-5月-25

Short Date 操作系统定义的短日期
Format$(Now,"Short Date") 返回值 2006-5-25

Long Time 操作系统定义的长时间
Format$(Now,"Long Time") 返回值 15:06:36

Medium Time 带AM/PM的12小时制，不带秒
Format$(Now,"Medium Time") 返回值 03:08 PM

Short Time 24时制的时间，不带秒
Format$(Now,"Short Time") 返回值 15:08

自定义格式参数
: 用来标识时间字符的间隔
Format$(Time(),"hh:nn") 返回值 15:25

/ 用来标识日期字符的间隔
Format$(now,"yyyy/mm/dd") 返回值 2006-05-25

c 格式化为国标的日期和时间
Format$(Now,"c") 返回值 2006-5-25 14:56:15

y 一年中的第几天
Format$(Now,"y") 返回值 145

d 一个月中的第几天（1-366）
Format$(Now,"d") 返回值 25

dd 当小于10时前面带0的天数（01-31）
Format$("2006-1-7","dd") 返回值 07

ddd 周几
Format$(Now,"ddd") 返回值 周四

dddd 星期几
Format$(Now,"dddd") 返回值 星期四

ddddd 显示标准日期
Format$(Now,"ddddd") 返回值 2006-05-25

dddddd 长日期
Format$(Now,"dddddd") 返回值 2006年5月25日

w 一个星期中的第几天
Format$(Now,"w") 返回值 5

ww 一年中的第几周
Format$(Now,"ww") 返回值 21

m 月数(注：当用于时间时，也可以表时为分钟)
Format$(Now,"m") 返回值 5

Format$(Now,"h:m") 返回值 16:11

mm 当小于10时前面带0的月数(注：当用于时间时，也可以表时为带0的分钟)
Format$(Now,"m") 返回值 05
Format$(Now,"hh:mm") 返回值 16:09

mmm 月份
Format$(Now,"mmm") 返回值 五月

q 一年中的第几季(1-4)
Format$(Now,"q") 返回值 2

yy 两位数的年份(00-99)
Format$(Now,"yy") 返回值 06

yyyy 四位数的年份(0100-9999)
Format$(Now,"yyyy") 返回值 2006

h 一天中的第N小时(0-23)
Format$(Now,"h") 返回值 16

hh 当小于10时带0的小时数(00-23)
Format$("7:30:28","hh") 返回值 07

n 一小时的分钟数(0-59)
Format$("7:30:28","n") 返回值 30

nn 当小于10时带0的分钟数(00-59)
Format$("7:3:28","n") 返回值 03

s 一分钟中的秒数(0-59)
Format$("7:30:8","s") 返回值 8

ss 当小于10时带0的分钟数(00-59)
Format$("7:3:8","ss") 返回值 08

ttttt 标准时间，小时数当小于10时不带0，与h:mm:ss相同
Format$("7:3:28","ttttt") 返回值 7:03:28

AM/PM 显示当前为AM或为PM
Format$(Now,"AM/PM") 返回值 PM

A/P 显示当前为A或为P
Format$(Now,"A/P") 返回值 P

AMPM 对0至2359的数值进行判断是AM还是PM，可以看作是同等于对00:00至23:59的数字进行判断，如1000可以看作是10:00。
Format$(1000,"AMPM") 返回值 AM

联合格式化
m/d/yy Format$(Now,"m/d/yy") 返回值 5-25-06
d-mmm-yy Format$(Now,"d-mmm-yy") 返回值 25-5月-06
d-mmmm Format$(Now,"d-mmmm") 返回值 25-五月
mmmm-yy Format$(Now,"mmmm-yy") 返回值 五月-06
hh:mm AM/PM Format$(Now,"hh:mm AM/PM") 返回值 04:50 PM
h:mm:ss a/p Format$(Now,"h:mm:ss a/p") 返回值 4:51:38 p
h:mm Format$(Now,"h:mm") 返回值 16:51
h:mm:ss Format$(Now,"h:mm:ss") 返回值 16:51:38
m/d/yy h:mm Format$(Now,"m/d/yy h:mm") 返回值 5-25-06 16:54

 

4、多表查询问题

条件：Access数据库的三个表，a表、b表、c表，三个表结构不同，其中都有字段ID，a表为主表，其中的ID不一定b表、c表中都存在，建立此三个表的联合查询。
根据SQL语法，通过连接运算符可以实现多个表查询。

连接可以在Select 语句的FROM子句或Where子句中建立，在FROM子句中指出连接时有助于将连接操作与Where子句中的搜索条件区分开来。　　
SQL-92标准所定义的FROM子句的连接语法格式为：

    FROM join_table join_type join_table [ON (join_condition)]

其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。
　　
join_type 指出连接类型，

可分为三种：内连接、外连接和交叉连接。

内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。

根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。

外连接分为：

左外连接(LEFT OUTER JOIN或LEFT JOIN)、

右外连接(RIGHT OUTER JOIN或RIGHT JOIN)

全外连接(FULL OUTER JOIN或FULL JOIN)

三种。

与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

前提：假设已存在外部数据库，并且已经打开包含a表、b表、c表三表的Mdb数据库。

根据SQL语法规则，写出如下代码：
1). 内查询（查询三个表中均存在的ID记录，任何一个表中不存在的id将均被过滤掉）

select * from a表 inner join b表 on a表.id=b表.id inner join c表 on a表.id=c表.id where ……   --where 条件暂时省略

2). 外查询（left join ,列出左表中的全部ID，不管b、c表中有无该ID）

select * from a表 left join b表 on a表.id=b表.id left join c表 on a表.id=c表.id  where ……   ---where 条件暂时省略

可是运行结果均为查询失败，如果仅仅两个表，查询则成功，三个或三个以上的表就会失败。

原因：Access对SQL的支持有些不同，将上述代码改为如下代码，问题解决！

select * from (a表 inner join b表 on a表.id=b表.id) inner join c表 on a表.id=c表.id  where ……  ---where 条件暂时省略

select * from (a表 left join b表 on a表.id=b表.id) left join c表 on a表.id=c表.id   where ……  --where 条件暂时省略

如果有四张表，可以写为：

select * from ((a表 inner join b表 on a表.id=b表.id) inner join c表 on a表.id=c表.id) inner join d表 on a表.id=d表.id  where …… ---where 条件暂时省略