snowman的部落格

 

使用SQL Server Management Studio管理工具建立資料庫

初始大小：SQL Server提供『自動成長』功能，當資料量太多，導致檔案放不下時，SQL Server會自動擴大檔案的大小。　　但是成長後容易造成資料在磁碟中不連續存放，亦會降低資料庫的效率，故建議先估算資料庫所需容量，在初始大小欄位中直接設定適當大小。

 

• CREATE TABLE（透過敍述建立資料表）

1. 設定資料表名稱

Database_name：資料表所在的資料庫名稱．若省略，則為目前連接的資料庫．

Schema：資料表所屬的結構描述．若省略，則系統會自動以建立資料表者在目前資料庫中的預設結構描述，做為資料表的結構描述．

Table_name：新建資料表名稱．

Ex. CREATE TABLE 客戶（省略database_name & schema）

2. 定義欄位屬性

Column_name data_type：定義欄位名稱以及資料型別（包含長度）．

COLLATE collation_name：設定欄位要套用的定序名稱，若省略，則會使用資料庫的預設值．只有字串類別（char、varchar、text、nchar、nvarchar、ntext）的欄位可以設定這項屬性．

DEFAULT constant_expression：設定欄位的預設值或繫結屬性．

IDENTITY(seed,increment)：設定識別屬性，讓欄位值自動編號．Seed為自動編號的起始值，若省略，則預設為1；increment為自動編號的遞增值，若省略，亦預設為1．（若IDENTITY）參數後加上NOT FOR REPLICATION，則表示若該欄位由複寫方式輸入資料時，不會自動編號．

ROWGUIDCOL：由SQL Server自動為欄位產生全域唯一的欄位值，只有uniqueidentifier型別的欄位可設定這項屬性．

<column_constraint>：針對單一欄位設定條件約束．

Ex.

（客戶編號　int IDENTITY,

　客戶名稱　varchar(30),

　優惠折扣　numeric(3,2) DEFAULT 0.8）

3. 設定欄位的條件約束

利用CONSTRAINT constraint_name參數可自行為條件約束取名，若省略，則SQL Server亦會自動為設定的條件約束取名．）

Ex.（訂單編號　int CONSTRAINT PK_訂單編號 PRIMARY KEY）

 

• NULL、Not Null條件

Ex.（書籍名稱　varchar(40) NOT NULL, 價格 smallmoney NULL）

• Primary Key、UnIQUE條件

Ex.（客戶編號　int NOT NULL PRIMARY KEY,　聯絡人 char(10) NULL UNIQUE）

【Null, not Null, Primary Key 或　UNIQUE不需要設定條件約束名稱】

• Foreign Key條件

Ex.

CREATE TABLE 訂單01

（　訂單編號　int PRIMARY KEY,

　　下單日期　datetime,

　　客戶編號　int CONSTRAINT FK_訂單與客戶01

FOREIGN KEY

REFERENCES 客戶（客戶編號）　）　←設定參照的資料表及欄位名稱

ON DELECT {CASCADE|NO ACTION}：設定ON DELETE CASCADE，表示串聯刪除相關記錄．若省略這項屬性，預設為ON DELETE NO ACTION，亦即若FOREIGN KEY參照到的記錄被刪除，則會出現錯誤訊息，拒絕執行．

ON UPDATE{CASCADE|NO ACTION}：設定ON UPDATE CASCADE，表示要串聯更新相關欄位．若省略這項屬性，預設為ON UPDATE NO ACTION，亦即若FOREIGN KEY參照到的欄位被修改，則會出現錯誤訊息，拒絕執行．

NOT FOR REPLICATION：若加上這項參數，則在複寫程式將資料輸入資料表時，不執行FOREIGN KEY條件約束．

• CHECK條件約束

Ex.

（價格　smallmoney CONSTRAINT CK_價格範圍

CHECK (價格>1 and 價格<3000) ）

 

Ex.

Create table 客戶02

(客戶編號　int Identity primary key,

身分證字號 char(10) not null unique,

年齡 int check (年齡>0) DEFAULT 25,

地址 varchar(50),

電話 char(12),

雜誌編號 int REFERENCES 雜誌種類（雜誌編號）,

訂戶編號 int not null,

Foreign Key (雜誌編號,訂戶編號) REFERENCES 雜誌訂戶 (雜誌編號，訂戶編號)，

Check (地址 is not null or 電話 is not null) )

• 建立計算欄位

計算欄位（Computed column）是一種虛擬欄位，此欄位的值是由同一資料表中的其他欄位所計算出來，其欄位內實際上並沒有存放資料．

Ex.

（編號 int IDENTITY,

單價 numeric(5,1),

數量 int,

總價 AS 單價*數量 ）

• ALTER TABLE（透過敍述修改資料表）

Column_name：指定欲修改屬性的欄位名稱．

New_data_type(precision[,scale])：為欄位指定新的資料型別，若是decimal、numeric型別，則還需要指定精確度(precision)和小數點位數(scale)．

Ex. （原客戶A的聯絡人欄位屬性為 char(20),not null→ varchar(30), null）

ALTER TABLE 客戶A

ALTER COLUMN 聯絡人　varchar(30) null

Ex. （客戶A資料表新增一個類別編號欄位，且有預設值，並需參照到客戶類別資料表的類別編號欄位）

ALTER TABLE 客戶A

Add

類別編號　int

DEFAULT 1 WITH VALUES

CONSTARAINT FK_類別編號

FOREIGN KEY REFERENCES 客戶類別（類別編號）

 

Ex. （將訂購項目A資料表設定Primary key條件約束）

ALTER TABLE 訂購項目A

With check Add

CONSTRAINT PK_訂購項目A

Primary key (訂單編號，項目編號)

• 透過敍述刪除條件約束／欄位

Ex. （刪除 PK_訂購項目A的Primary key條件）

ALTER TABLE 訂購項目A

Drop constraint PK_訂購項目A

 

Ex. （刪除 PK_訂購項目A的二個欄位）

ALTER TABLE 訂購項目A

Drop column 訂單編號,項目編號

• 透過敍述啟動／關閉條件約束

Ex. （暫停條件約束）

ALTER TABLE 客戶A

NoCheck constraint FK_類別編號

 

Ex. （啟動條件約束）

ALTER TABLE 客戶A

Check constraint FK_類別編號

• DROP TABLE透過敍述刪除資料表

Ex. Drop TABLE table_name （注意有關聯的設定時，則需先解除關聯設定）

• 透過敍述建立暫存資料表

1. 暫存資料表會存放在tempdb資料庫中

2. 使用完暫存資料表並離線後，暫存資料表會自動被刪除

Ex. （建立區暫存資料表）

CREATE TABLE #訂單（編號 int,　數量 int）

 

Ex. （建立全域暫存資料表）

CREATE TABLE ##客戶（編號 int, 姓名 char(10) )