基本表格(BASED TABLE)


4-1 資料庫共通的語言－SQL	4-2 SQL 的歷史	4-3 SQL 的基本語言
4-4 SQL 物件(SQL Object)	4-5 資料型態	4-6 資料型態轉換
4-7 基本表格(BASED TABLE)	4-8 虛擬欄位和虛擬表格	4-9 資料型態轉換函數
4-10 SQL 中常用的函數	4-11 SELECT 指令小結	4-12 視觀表格(VIEW)
4-13 基本表格、視觀表格的拷貝

ORACLE 以表格(Table) 的形式組織資料。SQL 語言透過基本表格來存放、尋找、更新資料。對於基本表格的定義有如下規定：
　

	每個表格(Table) 都有一個名字，通常稱為表格名。
	表格中每一列(Row) 相當於一筆記錄(Record)。
	一個表格(Table) 可以由若干欄位(Column) 組成，表格的每一欄位名稱必須唯一，即表格中各欄位不允許同名，欄位的名字稱之為欄位名。
	同一欄位的資料必須具有相同的資料型態。
	表中的每個欄位值是獨立的、不可分割的基本資料項。
	一個表格能夠包含多達 1000 個欄位。

一個基本表格(Based Table) 實質上由兩部分組成：

一部分是表格名和一組欄位名構成的分類資訊，另一部分是實際存放的資料。在建立基本表格時，只需要定義基本表格的表格名和包含的各欄位名稱及型態，整個基本表格結構就可以確定了。在 SQL 語言中，產生新的基本表格是使用 CREATE TABLE 指令。

在使用 CREATE TABLE 命令建立表格時，定義表格名和欄位名的規則如下：

	表格名、欄位名必須以字母開頭，大小寫均可，其後的字元可以由字母、數字組成，也可以包括“$”、“#”和底線“_”。
	同一個使用者的基本表格名必須唯一，而且不能與 ORACLE 的保留字相同；同一基本表格中的欄位名也須唯一，不允許在同一基本表格中兩個欄位名相同。
	表格名與欄位名是不區分大小寫的。例如：FIRSTNAME，FirstName 和 Firstname 均認為是相同的。
	如果表格名、欄位名用雙引號括起來，則可以不滿足上述規則。如果使用了雙引號，則要區分字母的大小寫，如“FIRSTNAME”與“Firstname”就不再認為是相同的。如果命名時使用了雙引號，則以後對表或欄位進行操作時，也必須使用雙引號。

所有的資料庫實體均要被指定一個名字作識別字，它們的命名與基本表格名、欄位名的命名類似，也要遵循上述原則。

在 ORACLE 系統中，提供一個使用者學習的帳號 SCOTT，其密碼為 TIGER。帳號內有四個樣本表格 EMP、DEPT、SALGRADE 與 BONUS，使用者可以利用此帳號學習 SQL 指令。
　

員工資料表格 EMP

EMPNO	NUMBER(4)	員工代碼(主鍵)
ENAME	VARCHAR2(10)	員工名字
JOB	VARCHAR2(9)	員工職稱
MGR	NUMBER(4)	員工的主管代碼(外來鍵)
SAL	NUMBER(7,2)	員工薪資
COMM	NUMBER(7,2)	員工獎金
HIREDATE	DATE	受雇日期
DEPTNO	NUMBER(2)	員工的部門代碼(外來鍵)

部門資料表格 DEPT

DEPTNO	NUMBER(2)	員工的部門代碼(主鍵)
DNAME	VARCHAR2(14)	部門名稱
LOC	VARCHAR2(13)	部門所在地

薪資等級表格 SALGRADE

GRADE	NUMBER	員工薪資等級
LOSAL	NUMBER	最低薪資
HISAL	NUMBER	最高薪資

員工獎金表格 BONUS (只有表格結構而已，沒有資料)

ENAME	VARCHAR2(10)	員工名字
JOB	VARCHAR2(9)	員工職稱
SAL	NUMBER(7,2)	員工薪資
COMM	NUMBER(7,2)	員工獎金

除了使用 ORACLE 四個樣本表格 EMP、DEPT、SALGRADE 與 BONUS 外，使用者可自已建立基本表格。
　

1. 建立基本表命令－ CREATE TABLE

CREATE TABLE <表格名>
<欄位名1><型態> [NULL | NOT NULL][,
<欄位名2><型態> [NULL | NOT NULL]]

或

CREATE TABLE <新表格名> AS
SELECT <欄位名> , <欄位名> , …
FROM <表格名>
WHERE <條件>

其中 <表格名> 字義了要建立的基本表格(Based Table) 的名稱。一個基本表格(Based Table) 可以包含若干欄位(Column)，但至少要有一個欄位。每個欄位必須有欄位名、資料型態與其寬度。
　

建立父母基本資料表格 parent，父母代碼(parent_id)，父親名字(f_name)，母親名字(m_name)，父親生日(f_date)，母親生日(m_date)，收入(sal)。

除了以上的基本語法外，系統可以對特定欄位加入一些整合限制(Integrity Constraints)。關聯表格內部的整合限制(Integrity Constraints) 可分為兩種：個体整合限制(Entity Integrity Constraints)與參考整合限制(Referential Integrity Constraints)：
　

個体整合限制(Entity Integrity Constraints)：它規範關聯表內部的整合限制條件。其設定語法如下：

	PRIMARY KEY
	一個關聯表 R 中可能有一個以上的候選鍵，此時我們可以由這些候選鍵當中選出最方便、最常被使用、長度最短、定義最清楚的候選鍵來當主鍵。一個關聯表中只有一個主鍵。例如： PRIMARY KEY(Empno)
	[NOT] NULL
	這個功能是定義此欄位是否允許設定欄位可以為虛值(NULL)。例如： DNAME VARCHAR2(10) NOT NULL
	UNIQUE
	這個功能是設定欄位的值在表格中是唯一的，表示欄位的值是不可重覆的。例如： UNIQUE(Name, Age)
	CHECK
	CHECK 是用來做為欄位檢位限制(Check Constraint)，利用此功能可以限制欄位的輸入值為何，並且避免欄位值對入錯誤。輸入的值的檢查：CHECK 檢查的條件。例如： CHECK(SAL>0)

參考整合限制(Referential Integrity Constraints)：它規範關聯表格與關聯表格之間的整合限制條件。

FOREIGN KEY

利用外來鍵(Foreign Key)，透過這種設定，可以使兩個或兩個以上的關聯表資料能互相參考。其設定語法如下：

FOREIGN KEY (外來鍵)
REFERENCES <被關聯的表格> (主鍵或候選鍵)
[ON DELETE <option 選項>]
　

P.K.			F.K.	P.K.
A_PK	…	…	A_FK	B_PK	…	…
A1	…	…	p	p	…	…
A2	…	…	p	q	…	…
A3	…	…	q	…	…	…
…	…	…	…	…	…	…
An	…	…	r	r	…	…

表格 A 表格 B
　

使用[ON DELETE <option 選項>]指令，其中 <option 選項> 可以為 CASCAD 或 SET NULL 其中之一：如果 [ON DELETE <option 選項>] 省略，則 ORACLE 系統則內定為不可刪除。
　

1.ON DELETE CASCADE

P.K.			F.K.	P.K.
A_PK	…	…	A_FK	B_PK	…	…
A3	…	…	q	q	…	…
…	…	…	…	…	…	…
An	…	…	r	r	…	…

表格 A 表格 B

2.ON DELETE SET NULL

P.K.			F.K.	P.K.
A_PK	…	…	A_FK	B_PK	…	…
A1	…	…	*NULL*	q	…	…
A2	…	…	*NULL*	…	…	…
A3	…	…	q	…	…	…
…	…	…	…	…	…	…
An	…	…	r	r	…	…

表格 A 表格 B

對一般個體(Entity) 與弱個体(Weak Entity) 所產生的表格的存在相依性而言，常設定外來鍵 ON DELETE 的 <option 選項> 為 CASCADE 或是直接省略 [ON DELETE <option 選項>]。
　

有時候關聯表格 R1 與 R2 是相同一個表格，也就是說，外來鍵 FK 與候選鍵 CK 是在同一個表格中，此時我們稱這種關聯為自身關聯，我們以 ORACLE 所提供的學習帳號中的樣本表格 EMP 為例說明：

P.K.			F.K.	P.K.
Empno	Ename	Sal	Deptno	Deptno	Dname	Loc
7369	SMITH	800	20	10	ACCOUNTING	NEW YORK
7499	ALLEN	1800	10	20	RESEARCH	DALLAS
…	…	…	…	…	…	…
7566	JONES	2975	-	40	OPERATIONS	BOSTON
R1 (員工基本資料表格 EMP)				R2 (部門基本資料表格 DEPT)

SQL 的編輯與修改方式大概為以下幾種：

	直接下指令時，可直接利用 SQL*PLUS 指令修改。(將於後面章節中介紹)
	利用純文字編輯工具 (如記事本) 編輯好並存檔案後，然後在 SQL*PLUS 中利用“ @目錄\檔案名”方式執行。(這是屬於批次方法執行)
	直接在 SQL*PLUS 中鍵入指令，若有錯誤或需要修改時，直接鍵入“edit”或“ed ”即可進入 ORACLE 內定的文字編輯工具，在WINDOW98 中內定為記事本。修改並存檔後再利用“/”或“RUN”重新執行即可。

2 插入資料－INSERT INTO……

INSERT INTO <表格名>(<欄位名1>,<欄位名2>,……)
VALUES(<值1>,<值2>,……);
或
INSERT INTO <表格名> ( <欄位名> , <欄位名> , … )
SELECT (<欄位名>, <欄位名>, … )
FROM <表格名>
WHERE <條件>;

SQL 的插入指令通常用於資料庫中已有的表格(Table)，若插入的值與表格中欄位的順序一致，且每一個欄位都有個值，則可以省略表格後的欄位名稱。

利用 INSERT INTO 指令將資料插入表格 PARENT 中。
首先可用 desc 指令來查看表格 PARENT 的表格結構：
　

例子中的日期格式(DATE)，利用繁体中文的內定日期格式 (日日-月月-年年)，例如：1964 年 3 月 31 日，須輸入為 31-3月-64，後面的例子利用 TO_DATE 函數，把它轉成指定格式。
　

除了以上每次只能輸入一個資料的方法外，使用者可利用以下參數的方式改善輸入的不方便：
INSERT INTO <表格名> (<欄位名1>,<欄位名2>,……)
VALUES(<參數1>,<參數2>,……)
　

利用參數方式輸入資料到表格 dept 中。

該指令一次執行也只能輸入一筆記錄值，但是我們可以重覆執行 (打 RUN 或“ / ”(斜線)) 來達成輸入多筆記錄值的目的。若輸入的欄位為文字資料型態 (如 CHAR 或 VARCHAR2)，則輸入時必須加上引號。使用者也可以直接在文字資料欄位對應的參數上直接加上引號，那麼在輸入時則可省去引號了。

EMPNO	ENAME	JOB	COMM
3838	SCOTT	SALESMAN	200
3849	FORD	ACCOUNTANT	NULL

更新資料－UPDATE……SET
UPDATE <表格名> SET <欄位名1>=<新值>,
<欄位名2>=<新值>,
...............
<欄位名n>=<新值>
WHERE <被更新記錄的條件>
其中 SET 子句指明更新的欄位與更新的值；WHERE 子句指明要更新的記錄的條件。

更新 EMP 表格中員工代碼(EMPNO) 為 7566 的員工，將他的職位(JOB) 改為 SALESMAN 與部門代碼(DEPTNO) 改為 30。

刪除資料－DELETE FROM
DELETE FROM <表格名>
WHERE <條件>

刪除指令不能只刪除部分欄位中的資料，因此不需要指明欄位名。其中 WHERE 子句為指明刪除記錄的條件。
　

刪除 EMP 表格中所有職位(JOB) 為 SALESMAN 的員工。

聰明的讀者，一定會發現刪除了 0 列，原因是我們在例 3 中將全部的‘SALESMAN’都改成了‘SALES’了，所以沒有任何‘SALESMAN’的記錄會被刪除。

修改基本資料表格結構命令，主要可分為：

	新增欄位定義
	修改欄位定義
	廢除欄位定義
	新增整合限制
	修改整合限制
	廢除整合限制

1. 新增欄位定義－ALTER TABLE……ADD

ALTER TABLE <表格名>
ADD (<欄位名1><資料型態>[NULL|NOT NULL]
[ ,<欄位名2><資料型態>[NULL|NOT NULL]]…);

其中 <表格名> 為新增欄位的表格名稱，ADD 為新增欄位的關鍵字，新增欄位的方式與 CREATE TABLE 指令相同。
　

	在已有資料的表格 PARENT 中新增二個欄位：父親的身份證號碼(f_id) 與母親的身份證號碼(m_id)。

2. 修改欄位的定義－ALTER TABLE……MODIFY

ALTER TABLE<表格名>
MODIFY (<欄位名1>[<資料型態>][NULL|NOT NULL]
[ ,<欄位名1>[<資料型態>[NULL|NOT NULL]]…]);

其中 <表格名> 指出要修改結構的表格名稱，MODIFY 為修改欄位的關鍵字。而修改欄位方式與 CREATE TABLE 指令相同。
　

利用 ALTER TABLE 的 MODIFY 更改表格 HDATE 的 HEMPNO 與 HENAME 欄位的大小。

修改定義時，應該遵循以下原則：
　

	可以在任何時候增加一個字元型(CHAR) 或數值型(NUMBER) 欄位的寬度。
	可以在任何時候增大或減少一個 NUMBER 型欄位的小數位數。
	只有當某欄位的每一列都為空時，才能減少該字元型或數值型欄位的寬度。
	只有當某欄位的每一列記錄都為空時，才能改變該欄位的資料型態。
	只有當某欄位的所有列都不為空值時，才能將該欄位定義為 NOT NULL。

3. 廢除欄位定義

然而在 ORACLE 8i 以後的版本，有提供直接廢除欄位的指令：
ALTER TABLE <表格名>
DROP COLUMN <欄位名>
　

	將表格 DEPT 中的欄位 BRENCH 廢除。

4. 新增整合限制(Integrity Constraint)－ALTER TABLE…ADD

ALTER TABLE <表格名>
ADD (CONSTRAINT <整合限制名><整合限制條件>
[ , CONSTRAINT <整合限制名><整合限制條件>…]);

使用者可利用 ALTER TABLE 的 ADD 指令，在表格中新增整合限制條件。其中 <整合限制名> 是用來作為管理用途，例如：ENABLE (實施整合限制)、DISABLE (取消整合限制) 與 DROP (廢除整合限制) 等指令管理整合限制。
　

	利用 ALTER TABLE 的 ADD 指令在表格 PARENT 中的欄位Parent_id 加入主鍵(Primary Key) 的個体整合限制條件(Entity Integrity Constraint)

5. 修改整合限制

修改整合限制又可分成實施整合限制(Enable)、取消整合限制(Disable)、廢除整合限制等三種。
(1) 實施整合限制 (ENABLE)－ALTER TABLE……ENABLE
ALTER TABLE <表格名>
ENABLE CONSTRAINT <整合限制名>
其中 <整合限制名> 是用來作為管理用途，例如：利用 ENABLE (實施整合限制)、DISABLE (取消整合限制) 與 DROP (廢除整合限制) 等指令管理整合限制。
　

	利用 ALTER TABLE 的 ENABLE 指令實施(Enable) 表格 student 中的參考整合限制 student_parent_fk。

(2) 取消整合限制(DISABLE)－ALTER TABLE……DISABLE
ALTER TABLE <表格名>
DISABLE CONSTRAINT <整合限制名>
　

	利用 ALTER TABLE 的 DISABLE 取消表格 student 中的參考整合限制 student_parent_fk。

(3) 廢除整合限制－ALTER TABLE……DROP
ALTER TABLE <表格名＞
DROP CONSTRAINT <整合限制名>
　

	利用 ALTER TABLE 的 DROP 廢除表格 student 中的參考整合限制 student_parent_fk。

廢除基本表格命令－DROP TABLE
DROP TABLE <表格名> [CASCADE CONSTRAINTS]
當要廢除的基本表格中含有參考整合限制時，也就是被別的表格參考到表格中的候選鍵時，必須加入 CASCADE CONSTRAINTS 才能廢除表格。
　

	利用 DROP 指令廢棄表格 PARENT。假設表格 PARENT 的欄位 PARENT_ID 有被表格 STUDENT 的外來鍵關聯。

	因為表格 PARENT 的欄位 PARENT_ID 有被表格 STUDENT 的外來鍵關聯，所以不能直接刪除表格 PARENT。若是 ORACLE 系統允許使用者廢除表格 PARENT，則表格 STUDENT 的外來鍵會因此找不到關聯記錄值，而發生錯誤。因此 ORACLE 系統在使用者想要廢除有被其他表格的外來鍵關聯的表格時，就會顯示錯誤訊息“ORA-02449: 外部鍵參考表格中的唯一/主鍵”，告訴使用者不能廢除有被其他表格的外來鍵關聯的表格。若是使用者仍想要廢除該表格，則必須如例 2 使用 CASCADE CONSTRAINTS 的選項參數：

	利用 DROP 指令廢棄表格 PARENT。

在建立表格時加入整合性限制。此時建立表格的順序就很重要。被關聯的表格要先建立，例如：parent 表格要比 student表格先建立； parent 表格的資料要比 student 表格資料先輸入。

建立表格 student 的完整例子，並加上整合限制條件將學號(id) 設為主鍵、父母代碼(parent_id)外來鍵與設學生身份證號碼(s_id)為唯一。

本例子中直接將整合限制加到欄位的定義中，並且省略整合限制名稱。其與後面有加整合限制名稱的例子之差別在於是否能修改整合限制 (Enable、Disable 與 Drop 整合限制)。如使用本例子中的方法 (省略整合限制名稱) 是不能直接利用修改整合限制名來修改整合限制。

簡易查詢－SELECT
SELECT <查詢內容>
FROM <表格名>
WHERE <條件>
ORDER BY <排序內容>
　

	查詢 EMP 部門 20 中的所有的員工的資料，如員工代碼(EMPNO)、員工名字(ENAME) 與薪資(SAL)。


	查詢 EMP 表格中員工 SCOTT 的全部資料。

	其中‘*’表示查詢表格中的所有欄位。也可以將查詢的資料做升冪排序(ASC) 或降冪排序(DESC)。只要利用 ORDER BY 指令加上升冪排序參數(ASC) 或降冪排序參數(DESC) 即可。