二、实例应用
1、说明：复制表法一：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 allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/*(5 行受影响)ID-----------1123NULL(5 行受影响)*/--只复制表结构select * into b from a where 1<>1select * 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 allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/*(5 行受影响)ID-----------1123NULL(5 行受影响)*/--只复制表结构select top 0 * into b from aselect * 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 allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/*(5 行受影响)ID-----------1123NULL(5 行受影响)*/--复制表数据create table [b]([ID] int)insert into b(id) select id from aselect * from b/*ID-----------1123NULL(5 行受影响)*/3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径)
--> 测试数据：[a]if object_id('[a]') is not null drop table [a]go create table [a]([ID] int)insert [a]select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/*(5 行受影响)ID-----------1123NULL(5 行受影响)*/--复制表数据create table [b]([ID] int)insert into b(id) select id from cc_jz.dbo.a select * from b/*ID-----------1123NULL(5 行受影响)*/--> 测试数据：[a]if object_id('[a]') is not null drop table [a]go create table [a]([ID] int)insert [a]select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/*(5 行受影响)ID-----------1123NULL(5 行受影响)*/--> 测试数据：[b]if object_id('[b]') is not null drop table [b]go create table [b]([ID] int)insert [b]select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from b/*(5 行受影响)ID-----------1224NULL(5 行受影响)*/select * from a where id in (select id from b )/*ID-----------1123(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 allselect 2,'方便面',60,'2013-01-03' union allselect 3,'方便面',50,'2013-01-03' union allselect 4,'矿泉水',80,'2013-01-04' union allselect 5,'方便面',50,'2013-01-05'select a.[品名],a.[入库数量],b.[最后入库时间] from [test] a ,(select [品名],max([入库时间]) as '最后入库时间' from [test] group by [品名]) bwhere 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 allselect 2,'方便面',60,'2013-01-03' union allselect 3,'方便面',50,'2013-01-03' union allselect 4,'矿泉水',80,'2013-01-04' union allselect 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.0002 方便面 60 2013-01-03 00:00:00.0003 方便面 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.0005 方便面 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 allselect 2,'方便面',60,'2013-01-03' union allselect 3,'方便面',50,'2013-01-03' union allselect 4,'矿泉水',80,'2013-01-04' union allselect 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.0002 方便面 60 2013-01-03 00:00:00.0003 方便面 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.0005 方便面 50 2013-01-05 00:00:00.000(2 行受影响)*/8、说明：前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 allselect 2,'方便面',60,'2013-01-03' union allselect 3,'方便面',50,'2013-01-03' union allselect 4,'矿泉水',80,'2013-01-04' union allselect 5,'方便面',50,'2013-01-05'select top(3) * from a /*ID 品名 入库数量 入库时间----------- ------ ----------- -----------------------1 矿泉水 100 2013-01-02 00:00:00.0002 方便面 60 2013-01-03 00:00:00.0003 方便面 50 2013-01-03 00:00:00.000(3 行受影响)*/9、说明：随机取出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 allselect 2,'方便面',60,'2013-01-03' union allselect 3,'方便面',50,'2013-01-03' union allselect 4,'矿泉水',80,'2013-01-04' union allselect 5,'方便面',50,'2013-01-05'select top(3) * from a order by newid()/*ID 品名 入库数量 入库时间----------- ------ ----------- -----------------------5 方便面 50 2013-01-05 00:00:00.0001 矿泉水 100 2013-01-02 00:00:00.0004 矿泉水 80 2013-01-04 00:00:00.000(3 行受影响)*/10、说明：列出数据库里所有的表名
select name from sysobjects where type='U' // U代表用户
11、说明：列出表里的所有的列名
select name from syscolumns where id=object_id('a')/*name----------------------------ID品名入库数量入库时间(4 行受影响)*/12、说明：初始化表a
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 allselect 2,'方便面',60,'2013-01-03' union allselect 3,'方便面',50,'2013-01-03' union allselect 4,'矿泉水',80,'2013-01-04' union allselect 5,'方便面',50,'2013-01-05'select * from a TRUNCATE TABLE aselect * from a /*(5 行受影响)ID 品名 入库数量 入库时间----------- ------ ----------- -----------------------1 矿泉水 100 2013-01-02 00:00:00.0002 方便面 60 2013-01-03 00:00:00.0003 方便面 50 2013-01-03 00:00:00.0004 矿泉水 80 2013-01-04 00:00:00.0005 方便面 50 2013-01-05 00:00:00.000(5 行受影响)ID 品名 入库数量 入库时间----------- ------ ----------- -----------------------(0 行受影响)*/13、说明：选择从2到4的记录
--> 测试数据：[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 allselect 2,'方便面',60,'2013-01-03' union allselect 3,'方便面',50,'2013-01-03' union allselect 4,'矿泉水',80,'2013-01-04' union allselect 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.0003 方便面 50 2013-01-03 00:00:00.0002 方便面 60 2013-01-03 00:00:00.000(3 行受影响)*/