程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> 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 行受影響) */

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved