法一：select * into b from a where 1<>1（仅用于SQlServer）
--> 测试数据：[a] if object_id(‘[a]‘) is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL (5 行受影响) */ --只复制表结构 select * into b from a where 1<>1 select * from b /* ID ----------- (0 行受影响) */
法二：select top 0 * into b from a
--> 测试数据：[a] if object_id(‘[a]‘) is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL (5 行受影响) */ --只复制表结构 select top 0 * into b from a select * from b /* ID ----------- (0 行受影响) */
2、说明：拷贝表(拷贝数据,源表名：
--> 测试数据：[a] if object_id(‘[a]‘) is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL (5 行受影响) */ --复制表数据 create table [b]([ID] int) insert into b(id) select id from a select * from b /* ID ----------- 1 1 2 3 NULL (5 行受影响) */
--> 测试数据：[a] if object_id(‘[a]‘) is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL (5 行受影响) */ --复制表数据 create table [b]([ID] int) insert into b(id) select id from cc_jz.dbo.a select * from b /* ID ----------- 1 1 2 3 NULL (5 行受影响) */
--> 测试数据：[a] if object_id(‘[a]‘) is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL (5 行受影响) */ --> 测试数据：[b] if object_id(‘[b]‘) is not null drop table [b] go create table [b]([ID] int) insert [b] select 1 union all select 2 union all select 2 union all select 4 union all select null select * from b /* (5 行受影响) ID ----------- 1 2 2 4 NULL (5 行受影响) */ select * from a where id in (select id from b ) /* ID ----------- 1 1 2 3 (4 行受影响) */
5、说明：显示品名、数量和最后入库时间
--> 测试数据：[a] if object_id(‘[a]‘) is not null drop table [a] go create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime) insert [a] select 1,‘矿泉水‘,100,‘2013-01-02‘ union all select 2,‘方便面‘,60,‘2013-01-03‘ union all select 3,‘方便面‘,50,‘2013-01-03‘ union all select 4,‘矿泉水‘,80,‘2013-01-04‘ union all select 5,‘方便面‘,50,‘2013-01-05‘ select a.[品名],a.[入库数量],b.[最后入库时间] from [test] a , (select [品名],max([入库时间]) as ‘最后入库时间‘ from [test] group by [品名]) b where a.[品名]=b.[品名] /* 品名 入库数量 最后入库时间 ------ ----------- ----------------------- 方便面 60 2013-01-05 00:00:00.000 方便面 50 2013-01-05 00:00:00.000 方便面 50 2013-01-05 00:00:00.000 矿泉水 100 2013-01-04 00:00:00.000 矿泉水 80 2013-01-04 00:00:00.000 (5 行受影响) */
6、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
--> 测试数据：[a] if object_id(‘[a]‘) is not null drop table [a] go create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime) insert [a] select 1,‘矿泉水‘,100,‘2013-01-02‘ union all select 2,‘方便面‘,60,‘2013-01-03‘ union all select 3,‘方便面‘,50,‘2013-01-03‘ union all select 4,‘矿泉水‘,80,‘2013-01-04‘ union all select 5,‘方便面‘,50,‘2013-01-05‘ select * from a where [入库时间] between ‘2013-01-02‘ and ‘2013-01-03‘ /* ID 品名 入库数量 入库时间 ----------- ------ ----------- ----------------------- 1 矿泉水 100 2013-01-02 00:00:00.000 2 方便面 60 2013-01-03 00:00:00.000 3 方便面 50 2013-01-03 00:00:00.000 (3 行受影响) */ select * from a where [入库时间] not between ‘2013-01-02‘ and ‘2013-01-03‘ /* ID 品名 入库数量 入库时间 ----------- ------ ----------- ----------------------- 4 矿泉水 80 2013-01-04 00:00:00.000 5 方便面 50 2013-01-05 00:00:00.000 (2 行受影响) */
7、说明：in 的使用方法
--> 测试数据：[a] if object_id(‘[a]‘) is not null drop table [a] go create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime) insert [a] select 1,‘矿泉水‘,100,‘2013-01-02‘ union all select 2,‘方便面‘,60,‘2013-01-03‘ union all select 3,‘方便面‘,50,‘2013-01-03‘ union all select 4,‘矿泉水‘,80,‘2013-01-04‘ union all select 5,‘方便面‘,50,‘2013-01-05‘ select * from a where [入库时间] in( ‘2013-01-02‘, ‘2013-01-03‘) /* ID 品名 入库数量 入库时间 ----------- ------ ----------- ----------------------- 1 矿泉水 100 2013-01-02 00:00:00.000 2 方便面 60 2013-01-03 00:00:00.000 3 方便面 50 2013-01-03 00:00:00.000 (3 行受影响) */ select * from a where [入库时间] not in( ‘2013-01-02‘, ‘2013-01-03‘) /* ID 品名 入库数量 入库时间 ----------- ------ ----------- ----------------------- 4 矿泉水 80 2013-01-04 00:00:00.000 5 方便面 50 2013-01-05 00:00:00.000 (2 行受影响) */
--> 测试数据：[a] if object_id(‘[a]‘) is not null drop table [a] go create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime) insert [a] select 1,‘矿泉水‘,100,‘2013-01-02‘ union all select 2,‘方便面‘,60,‘2013-01-03‘ union all select 3,‘方便面‘,50,‘2013-01-03‘ union all select 4,‘矿泉水‘,80,‘2013-01-04‘ union all select 5,‘方便面‘,50,‘2013-01-05‘ select top(3) * from a /* ID 品名 入库数量 入库时间 ----------- ------ ----------- ----------------------- 1 矿泉水 100 2013-01-02 00:00:00.000 2 方便面 60 2013-01-03 00:00:00.000 3 方便面 50 2013-01-03 00:00:00.000 (3 行受影响) */
--> 测试数据：[a] if object_id(‘[a]‘) is not null drop table [a] go create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime) insert [a] select 1,‘矿泉水‘,100,‘2013-01-02‘ union all select 2,‘方便面‘,60,‘2013-01-03‘ union all select 3,‘方便面‘,50,‘2013-01-03‘ union all select 4,‘矿泉水‘,80,‘2013-01-04‘ union all select 5,‘方便面‘,50,‘2013-01-05‘ select top(3) * from a order by newid() /* ID 品名 入库数量 入库时间 ----------- ------ ----------- ----------------------- 5 方便面 50 2013-01-05 00:00:00.000 1 矿泉水 100 2013-01-02 00:00:00.000 4 矿泉水 80 2013-01-04 00:00:00.000 (3 行受影响) */
select name from sysobjects where type=‘U‘ // U代表用户
select name from syscolumns where id=object_id(‘a‘) /* name ---------------------------- ID 品名 入库数量 入库时间 (4 行受影响) */
TRUNCATE TABLE a
--> 测试数据：[a] if object_id(‘[a]‘) is not null drop table [a] go create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime) insert [a] select 1,‘矿泉水‘,100,‘2013-01-02‘ union all select 2,‘方便面‘,60,‘2013-01-03‘ union all select 3,‘方便面‘,50,‘2013-01-03‘ union all select 4,‘矿泉水‘,80,‘2013-01-04‘ union all select 5,‘方便面‘,50,‘2013-01-05‘ select * from a TRUNCATE TABLE a select * from a /* (5 行受影响) ID 品名 入库数量 入库时间 ----------- ------ ----------- ----------------------- 1 矿泉水 100 2013-01-02 00:00:00.000 2 方便面 60 2013-01-03 00:00:00.000 3 方便面 50 2013-01-03 00:00:00.000 4 矿泉水 80 2013-01-04 00:00:00.000 5 方便面 50 2013-01-05 00:00:00.000 (5 行受影响) ID 品名 入库数量 入库时间 ----------- ------ ----------- ----------------------- (0 行受影响) */
--> 测试数据：[a] if object_id(‘[a]‘) is not null drop table [a] go create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime) insert [a] select 1,‘矿泉水‘,100,‘2013-01-02‘ union all select 2,‘方便面‘,60,‘2013-01-03‘ union all select 3,‘方便面‘,50,‘2013-01-03‘ union all select 4,‘矿泉水‘,80,‘2013-01-04‘ union all select 5,‘方便面‘,50,‘2013-01-05‘ select top 3 * from (select top 4 * from a order by id asc) b order by id desc /* ID 品名 入库数量 入库时间 ----------- ------ ----------- ----------------------- 4 矿泉水 80 2013-01-04 00:00:00.000 3 方便面 50 2013-01-03 00:00:00.000 2 方便面 60 2013-01-03 00:00:00.000 (3 行受影响) */
SQL Server基础语法实例应用（二）,布布扣,bubuko.com
原文：http://blog.csdn.net/yole_grise/article/details/21002209