二、实例应用
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 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 行受影响)
*/
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径)
--> 测试数据:[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 行受影响)
*/
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 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条数据
--> 测试数据:[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、说明:列出数据库里所有的表名
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 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的记录
--> 测试数据:[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 行受影响)
*/