第七章: 建立資料表

章節概覽 7.1 利用管理工具建立資料表 7.2 設定條件約束維持資料的一致性 7.3 修改與刪除資料表 7.4 使用暫存資料表 7.5 資料表的特殊屬性

7.1 利用管理工具建立資料表 7.1.1 利用EM建立資料表

7.1.2 利用Query Analyzer建立資料表 範例7-2 USE Samples CREATE TABLE Employees ( empid char(3) not null, empname varchar(10) not null, department char(1) null, salary int, salary_commer int )

利用Query Analyzer查詢資料表

7.2 設定條件約束維持資料的一致性 7.2.1 資料一致性 預設值 CHECK條件約束 主索引鍵條件約束 唯一鍵值條件約束 外來主索引鍵條件約束 1. 資料型別 2. 條件約束

7.2.2 設定預設值 使用者可能忘了輸入或沒有輸入值時 使用者根本不需要輸入值 其他．．．

設定預設值注意事項 一個欄位只能有一個default設定 不是每個欄位的資料型別都可以設定 如：Identity , rowversion 不可設定default 預設值可以是任何值，甚至是系統函數 如：GETDAY ( )

預設值範例 預設薪水為20000元 ALTER TABLE Employees ADD CONSTRAINT default_salary 範例7-3 預設薪水為20000元 ALTER TABLE Employees ADD CONSTRAINT default_salary DEFAULT 20000 FOR Salary

7.2.3 設定CHECK條件約束 限定使用者的輸入值 一個欄位可以設定多個CHECK條件約束 可使用系統函數 可與同資料表的欄位比較

CHECK條件約束範例(1/2) 檢查年齡在20到65歲之間 ALTER TABLE Employees1 ADD 範例7-6 檢查年齡在20到65歲之間 ALTER TABLE Employees1 ADD CONSTRAINT check_age check (age >= 20 and age <=65)

CHECK條件約束範例(2/2) 限制每一個位元所輸入的資料 ALTER TABLE Employees1 ADD 範例7-9 限制每一個位元所輸入的資料 ALTER TABLE Employees1 ADD CONSTRAINT check_empid check (empid like '[A-Z][0-9][0-9]')

7.2.4 設定Primary Key條件約束 一個資料表只能有一個 PK 條件約束 確保列的資料不重覆 欄位定義不能有NULL SQL Server會自動加上Unique Index

Primary Key條件約束範例 設定學號為primary key ALTER TABLE Student ADD 範例7-12 設定學號為primary key ALTER TABLE Student ADD CONSTRAINT pk_studentid primary key(studentid)

設定Unique Key條件約束 一個資料表只能多個 確保列的資料不重覆 欄位定義可以有NULL(PK 不可是NULL) SQL Server會自動加上Unique Index 在資料庫定義中，為候選主索引鍵

Unique Key條件約束範例 設定身份證字號為unique key ALTER TABLE Student ADD 範例7-13 設定身份證字號為unique key ALTER TABLE Student ADD CONSTRAINT uk_public unique(publicid)

7.2.5 設定Foreign Key條件約束 避免資料表與資料表間的不一致性 是別的欄位的主索引鍵 設定後，彼此有關連的資料無法刪除

Foreign Key條件約束設定範例 設定學生與老師的關係 ALTER TABLE 學生 ADD 範例7-14 設定學生與老師的關係 ALTER TABLE 學生 ADD CONSTRAINT fk_teacherid foreign key (導師編號) references 導師(導師編號)

刪除Detail資料表，無法刪除Master 資料表 設定串聯更新與串聯刪除 將Mater資料表刪除時順便刪除Detail資料表 刪除Detail資料表，無法刪除Master 資料表 簡化了刪除資料的動作

串聯更新與刪除設定範例 設定學生與老師的關係是串聯更新與刪除 ALTER TABLE 學生 ADD 範例7-15 設定學生與老師的關係是串聯更新與刪除 ALTER TABLE 學生 ADD CONSTRAINT fk_teacherid foreign key (導師編號) references 導師(導師編號) ON UPDATE CASCADE ON DELETE CASCADE

修改與刪除資料表欄位 修改資料表欄位 ALTER TABLE 學生 ADD 姓別 CHAR(1) NULL 刪除資料表欄位 範例7-16 修改資料表欄位 ALTER TABLE 學生 ADD 姓別 CHAR(1) NULL 刪除資料表欄位 DROP TABLE 學生

7.4 使用暫存資料表 不是真正的資料表，是存在tempdb中 CREATE TABLE #TABLE1 (Orderid int, 範例7-18 不是真正的資料表，是存在tempdb中 CREATE TABLE #TABLE1 (Orderid int, Customerid char(10))

7.5 資料表的特殊屬性 7.5.1 Identity欄位 自動編號，可用來自動產生數字 建立方式 範例7-19 CREATE TABLE Employees (employeeid int IDENTITY(1,2) NOT NULL, employeename varchar(10)) INSERT方式，IDENTITY欄位空白不用輸入 INSERT Employees values ('楊先民') INSERT Employees values ('張智凱') 範例7-20

IDENTITY語法 IDENTITY [ ( seed , increment ) ] Seed：是載入到資料表的第一個資料列所使用的值 您必須同時指定初始值及遞增值，或都不指定。如 果不指定，預設為 (1,1)。

7.5.2 公式欄位 利用公式算出另一個欄位的值 怕使用者輸入錯資料 IDENTITY欄位空白不用輸入 範例7-22 利用公式算出另一個欄位的值 怕使用者輸入錯資料 IDENTITY欄位空白不用輸入 CREATE TABLE Author ( 作者身份證字號 char(10), 作者姓名 varchar(10), 作者版稅 money, 作者預扣稅 as 作者版稅 /10 )

學習成果回顧 資料表是實際儲存資料的地方 建立一個資料表時，需要指定欄位名稱、資料型 別，以及是否允許NULL值 每個資料表都可以依資料表的特性設定條件約束 資料表可以設定自動編號與公式欄位 可以利用INSERT指令新增資料、UPDATE修改資 料、DELETE刪除資料