二、实例应用
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.[品名]
/*
品名入库数量最后入库时间
------ ----------------------------------
方便面602013-01-0500:00:00.000
方便面502013-01-0500:00:00.000
方便面502013-01-0500:00:00.000
矿泉水1002013-01-0400:00:00.000
矿泉水802013-01-0400: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矿泉水1002013-01-02 00:00:00.000
2方便面602013-01-03 00:00:00.000
3方便面502013-01-03 00:00:00.000
(3 行受影响)
*/
select * from a where [入库时间] not between '2013-01-02' and '2013-01-03'
/*
ID品名入库数量入库时间
----------- ------ ----------------------------------
4矿泉水802013-01-04 00:00:00.000
5方便面502013-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矿泉水1002013-01-02 00:00:00.000
2方便面602013-01-03 00:00:00.000
3方便面502013-01-03 00:00:00.000
(3 行受影响)
*/
select * from a where [入库时间] not in( '2013-01-02', '2013-01-03')
/*
ID品名入库数量入库时间
----------- ------ ----------------------------------
4矿泉水802013-01-04 00:00:00.000
5方便面502013-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矿泉水1002013-01-02 00:00:00.000
2方便面602013-01-03 00:00:00.000
3方便面502013-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 aorder by newid()
/*
ID品名入库数量入库时间
----------- ------ ----------------------------------
5方便面502013-01-05 00:00:00.000
1矿泉水1002013-01-02 00:00:00.000
4矿泉水802013-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矿泉水1002013-01-02 00:00:00.000
2方便面602013-01-03 00:00:00.000
3方便面502013-01-03 00:00:00.000
4矿泉水802013-01-04 00:00:00.000
5方便面502013-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矿泉水802013-01-04 00:00:00.000
3方便面502013-01-03 00:00:00.000
2方便面602013-01-03 00:00:00.000
(3 行受影响)
*/
