查询时生成序号
select (select count([xlh].[aa]) as autonum from xlh where (((xlh.aa)from xlh as xlh_alias inner join xlh on xlh_alias.aa=xlh.aa
order by xlh.aa;
多表sql查询
select test.aa as 第一个字段, test1.bb as 第二个字段, test1.cc
from test, test1
where test.aa=test1.aa;
多表sql查询1
select a.aa, b.bb, b.cc, b.cc*100 as 合计
from test as a, test1 as b
where a.aa=b.aa;
多表sql查询排序
select a.aa, b.bb, b.cc as 第三个字段
from test as a, test1 as b
where a.aa=b.aa
order by b.cc;
查询例子
select a.dhhm
from xl11a as a, xl919 as b
where a.dhhm=b.dhhm and aa1;
日期时间分隔符是#而不是引号
select * from tab1 where [date]>#2002-1-1#;
两个表关联修改多个字段
update chhl as a, jbsj as b set a.fzr = b.fzr, a.gh = b.gh
where a.dhhm=b.dhhm;
update chhl set (fzr,gh)=
(select b.fzr, b.gh
from chhl as a, jbsj as b
where a.dhhm=b.dhhm);
如果tab2可以不是一个表,而是一个查询
update tab1 a,(select id,name from tab2) b
set a.name = b.name
where a.id = b.id;
update tab1 a,tab2 b
set a.name = b.name
where a.id = b.id;
访问多个不同的access数据库-在sql中使用in子句,外部数据库不能带密码
select a.*,b.* from tab1 a,tab2 b in 'db2.mdb' where a.id=b.id;
将一个表的某个字段置空
update chhl as a set a.fzr = null, a.gh = null;
删除两个表中字段一样的记录
delete from xl11 where dhhm in(select a.dhhm
from xl11 as a, xl919 as b
where a.dhhm=b.dhhm;);
完成后access中字段name索引属性显示为--有(无重复)
create unique index iname on tab1 (name);
下面的语句删除刚才建立的两个索引
drop index idate on tab1;
drop index iname on tab1;