SQL基础语句大全，增删改查提升（二）

1.复制表

 (只复制结构,源表名：a 新表名：b) (Access可用)
select * into b from a where 1<>1（仅用于SQlServer）

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.外连接

外连接查询(表名1：a 表名2：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    

6.视图查询

在线视图查询(表名1：a )   
select * from (SELECT a,b,c FROM a) T where t.a > 1;  

7.“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

8.“in”的用法

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

9.表关联处理

两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists 
    (
        select * from table2 where table1.field1=table2.field1
    )   

10.多表联查

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 .....