SQLServer基础语法实例应用（二）

网络整理 - 08-18

　　二、实例应用

　　1、说明：复制表

　　法一：select * into b from a where 1<>1(仅用于SQlServer)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 --> 测试数据：[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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 --> 测试数据：[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、说明：拷贝表(拷贝数据,源表名：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 --> 测试数据：[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 行受影响) */ 3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 --> 测试数据：[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 行受影响) */

　　4、说明：子查询(表名1：a 表名2：b)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 --> 测试数据：[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、说明：显示品名、数量和最后入库时间

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 --> 测试数据：[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不包括

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 --> 测试数据：[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 的使用方法

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 --> 测试数据：[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 行受影响) */

　　8、说明：前3条记录

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 --> 测试数据：[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 行受影响) */ 9、说明：随机取出3条数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 --> 测试数据：[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 行受影响) */ 10、说明：列出数据库里所有的表名

1 2 3 select name from sysobjects where type='U' // U代表用户 11、说明：列出表里的所有的列名

1 2 3 4 5 6 7 8 9 10 11 12 13 14 select name from syscolumns where id=object_id('a') /* name ---------------------------- ID 品名入库数量入库时间 (4 行受影响) */ 12、说明：初始化表a

　　TRUNCATE TABLE a

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 --> 测试数据：[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 行受影响) */ 13、说明：选择从2到4的记录

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 --> 测试数据：[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 行受影响) */