DDL
建立資料表
create table < tablename>(
< column> < datatype> [column constraint],
[other constraint]
)
datatype常用的有以下
varchar 變動欄位,依資料量多少會給不同的空間
char 固定欄位
column constraint常用的有以下
default 0 若無資料預設為0
not null 資料不可為空值
primary key 設定主鍵
reference table(column) 設定外來鍵
check(條件) 設定輸入資料的欄位限制
other constraint常用的有以下
primary key(< column>) 設定主鍵
foreign key(column) references table(column) 設定外來鍵
check(條件) 設定輸入資料的欄位限制
ex:
column1和column2不可同時為null
check(column1 is not null or column2 is not null)
ex:
column1資料要大於0
check(column1 > 0)
設定主鍵
有以下2種寫法
< column> < datetype> primary key
常用,寫在column constraint區域
指定該欄位為primay key,缺點是只可以設定一個欄位
[option] primary key(< column>)
寫在other constraint區域
宣告primary key是那些欄位,可以設定多個欄位
option包括
constraint < name> 指定constraint名稱,也就是key編號的名稱
ex:
建立資料表department(dname,dnumber,mgr_ssn,mgr_start_date),語法大致如下
create table department
(
dname varchar(20),
dnumber char(1) primary key not null,
mgr_ssn char(8) ,
mgr_start_date varchar(20)
)
…..
定義資料表限制
在主鍵資料變動時,外來鍵的處理
新增資料時可能違反的限制有
實體限制
定義域限制
參考完整性限制
使用者定義
解決方式
Restrict:拒絕新增
刪除資料時可能違反的限制有
參考完整性限制
解決方式
Restrict:拒絕刪除
Delete Cascade:連鎖性刪除
Set to Null Value: 將參考到被刪除紀錄之外來鍵值設成空值
更新資料時可能違反的限制有
實體限制(只有主鍵或外來鍵才有可能違反該項目)
定義域限制
參考完整性限制(只有主鍵或外來鍵才有可能違反該項目)
使用者定義
解決方式
Restrict:拒絕更新
Update Cascade:連鎖性更新
Set to Null Value:將參考不到父資料表的外來鍵值設為空值
…….
設定外來鍵
有以下2種寫法
< column> < datetype> references table(column) [option]
寫在column constraint區域
foreign key(column) references table(column) [option]
寫在other constraint區域
option包括
on < delete|update> cascade 用在forigen key可產生連鎖刪除或連鎖更新
當另一個table的pk異動時，也會影響本table的fk值
當fk同時為pk，在另一邊table的pk異動時會連鎖影響本table資料
當僅做fk，在另一邊table的pk異動時會連鎖影響本table資料
on < delete|update> no action 用在forigen key則不動作
當另一個table的pk異動時，本table的fk不會受影響
當fk同時為pk，在另一邊table的pk異動時會本table會發生錯誤
僅做fk，在另一邊table的pk異動時不會影響本table
ps:
外來鍵不建議與table同時建立
建議使用alter語法在table建完後在修改該欄位為外來鍵
………………………………………………………………………………………………………….
修改資料表
ALTER TABLE < tablename> < action>
常用action有以下
修改欄位屬性為foreign key
alter table < tablename> add [constraint < name>] foreign key(< column>) references < table(column)> [option]
修改欄位data type
alter table < tablename> alter column < name> < datatype>
移除foreign key屬性
alter table < tablename> drop constraint < name>
ps:
constraint name會記錄在sysobjects
sysobjects資料表的xtype欄位,若為F表示外來鍵
ex:select * from sysobjects where xtype=’F’
…………………………………………………………………………………………………………
刪除資料表
DROP TABLE < tablename>
ps:
通常刪除的順序會和建立的順序相反
若被其他table參考時,會無法刪除
若兩table互相參考對方,需先移除foreign key屬性
…………………………………………………………………………………………………………
建立VIEW
CREATE VIEW < viewname> AS < select sql>
建立trigger
CREATE TRIGGER < name> ON [order details] FOR < insert,update,delete> AS < sql>