3-1-2:資料表的建立、修改與刪除

建立資料表

Create table 資料表名稱
(
 欄位名稱1 資料型態,
 欄位名稱2 資料型態,
 ...
)

常用的欄位資料型態

功能語法
整數int(size)
字元char(size)
可變長度字元varchar(size)
日期date(YYYY-MM-DD)

Create table 的欄位限制設定

名稱說明
primary key主鍵
not null欄位不可為空值
unique唯一值
foreign key外來鍵
check確保資料值滿足特定的條件
default預設值

範例

欄位意義資料型態是否為空值備註
ssid學號char(4)Not null主鍵
sname姓名varchar(20)Not nullF(女)/M(男)
sex性別char(1)Not null
tel電話char(10)

【解】

create table student
(
 ssid char(4),
 sname varchar(20) not null,
 sex char(1) not null,
 tel char(10),
 primary key(ssid),
 check(sex='F' or sex='M')
);

【上機實作】

完整性

實體完整性:新增與修改的限制

使用主鍵(primary key)設定實體完整性,則此欄位值具唯一性(unique)且不可為空值(not null)。

參考完整性:資料刪除與更新的限制

使用外來鍵(foreign key)設定參考完整性。外來鍵所對應的參考主鍵必須存在,否則應為空值,表示未參考任何實體值。當被參考的父表資料刪除時,子表關聯資料的處理方式如下:

  1. No Action:沒有動作,又稱為限制作法(Restricted),被參考的父表資料尚存在外來鍵資料參考時,不允許父表資料被更新或刪除。
  2. Cascades:連帶作法,連帶更新或刪除子表關聯資料。
  3. 設定為空值(Null)
  4. 設定為預設值(default)

值域的完整性:新增與修改的限制

使用check 設定來檢查欄位值是否符合條件,例如成績(score)範圍為0~100分,如果超出範圍,則無法新增,設定方法如下:
check (score >= 0 and score <= 100)

自我練習

練習1

根據下表寫出建立「訂單資料表Orders」的SQL指令

欄位意義資料型態空值備註
Oid訂單序號intPK (主鍵)
Cid客戶代號char(3)FK (外來鍵,參考Customer資料表之Cid)
Odate訂單日期date
Ddate送貨日期date

參考解答

Create table Orders
(
 Oid int not null,
 Cid char(3) not null,
 Odate date not null,
 Ddate date,
 primary key(Oid),
 foreign key(Cid) references Customer(cid)
)

練習2

建立下圖的選課資料庫,包含學生資料表student、老師資料表teacher、課程資料表course、學生選課資料表stu_course。

參考解答

# 建立資料庫
Create Database testDB
# 建立資料表
use testDB
GO
create table student
(
  ssid char(8),
  sname char(4) not null,
  tel char(12),
  address varchar(50),
  depart int not null,
  primary key(ssid),
  unique(tel),
  check(tel is not null or address is not null)
)
create table teacher
(
  tid char(4) primary key,
  tname char(20) not null,
  research varchar(20)
)
GO
create table course
(
  cid char(5),
  cname char (20) not null,
  credite int default 3,
  obligatory char(1),
  tid char(4),
  primary key(cid),
  foreign key(tid) references teacher(tid)
)
GO
create table stu_course
(
  ssid char(8),
  cid char(5),
  score int default 0,
  date datetime default(getdate()),
  primary key(ssid,cid),
  foreign key(ssid) references student(ssid)
  on update cascade
  on delete cascade,
  foreign key(cid) references course(cid),
  check(score>=0 and score<=100)
)

更改資料表

Alter table資料表名稱
[Alter] COLUMN [欄位] [資料型態[NULL| NOT NULL]
   [RESTRICT|CASCADE]
[ADD|DROP] [限制|屬性]
[ADD] [欄位] {資料型態|定義域} [NULL| NOT NULL]
[預設值][整合限制]

範例1

資料表student 之tel欄位資料型態修改為12字元。

【解】

Alter table student
Alter Column tel char(12)

範例2

於資料表student中新增地址欄位address,資料型態最長50個字元、且不可為空值。

【解】

Alter table student
Add address varchar(50) not null

範例3

刪除資料表student之tel欄位。

觀看教學影片

【解】

Alter table student
Drop Column tel

刪除資料表

DROP table 資料表名稱

範例

刪除student資料表。

【解】

Drop table student