程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SQLServer基礎語法實例應用（二）

SQLServer基礎語法實例應用（二）

編輯：更多數據庫知識

　　二、實例應用

　　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 行受影響) */