1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)
法一: select * into b from a where 1 <> 1
法二:
select
top
0
*
into
b
from
a
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
insert into b ( a , b , c ) select d , e , f from b ;
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert
into
b
(
a
,
b
,
c
)
select
d
,
e
,
f
from
b
in
‘
具
体
数
据
库
’
where
条
件
例
子
:..
from b in
'"&Server.MapPath(".")&"\data.mdb" &"'
where..
4、说明:子查询(表名1:a 表名2:b)
select
a
,
b
,
c
from
a
where
a
IN
(
select
d
from
b
)
或
者
:
select
a
,
b
,
c
from
a
where
a
IN
(
1
,
2
,
3
)
5、说明:显示文章、提交人和最后回复时间
select
a
.
title
,
a
.
username
,
b
.
adddate
from
table
a
,
(
select
max
(
adddate
)
adddate
from
table
where
table
.
title
=
a
.
title
)
b
select
a
.
a
,
a
.
b
,
a
.
c
,
b
.
c
,
b
.
d
,
b
.
f
from
a
LEFT
OUT
JOIN
b
ON
a
.
a
=
b
.
c
7、说明:在线视图查询(表名1:a )
select
*
from
(
Select
a
,
b
,
c
FROM
a
)
T
where
t
.
a
>
1
;
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括
select
*
from
table1
where
time between time1
and
time2
select
a
,
b
,
c
,
from
table1
where
a
not
between
数
值
1
and
数
值
2
9、说明:in 的使用方法
select
*
from
table1
where
a
[
not
]
in
(
‘
值
1
’,’
值
2
’,’
值
4
’,’
值
6
’
)
10、说明:两张关联表,删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1 . field1 = table2 . field1 )
10、说明:两张关联表,删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1 . field1 = table2 . field1 )
11、说明:四表联查问题:
select
*
from
a
left
inner
join
b
on
a
.
a
=
b
.
b
right
inner
join
c
on
a
.
a
=
c
.
c
inner
join
d
on
a
.
a
=
d
.
d
where
.....
12、说明:日程安排提前五分钟提醒
sql
:
select
*
from
日
程
安
排
where
datediff
(
'minute'
,
f
开
始
时
间
,
getdate
(
))
>
5