第十六章 資料庫系統

16.1 概論

電子商務的興起讓資料庫的應用更受到大家的矚目。在資訊科學的應用上，資料庫可以說是最歷久彌堅的領域。近來，資料產生和資料收集方面的技術有非常快速的進展。許多商業產品廣泛使用了條碼、許多企業和政府的交易皆已電腦化，這使得電腦成為資料收集的主要工具。同時，數以百萬計的資料庫正被使用在企業管理、政府管理、科學和工程的資料管理和許多其它的應用上。

我們可以安裝一套資料庫系統，並經由一個介面自行開發程式來使用它。資料庫的好處有很多，相信對資料庫稍有涉入的人都知道，例如資料存取快速、不重覆、權限控制、資料獨立性等等。以寫一個簡單的留言版程式而言，傳統上使用檔案做為留言的記錄，若要刪除一筆資料，必須對整個檔案一行一行的比對；但資料庫只需指定該留言的編號即可。不過，如果把資料庫系統局限於留言版也太大才小用了。

我們將介紹在 FreeBSD 上使用資料庫，因為目前網頁資料庫使用情形十分風行，尤其在網頁開發上使用 MySQL+PHP 更是絕配，所以我以 MySQL 為主介紹它的用法。為什麼選用 MySQL 而不選擇其他的資料庫，因為它簡單、免費、功能強大、具有多平台、多執行緒、且參考文件多。你可以到 MySQL 的網站上參觀 http://www.mysql.com。除了 MySQL 外，還有另一套不錯的資料庫 Postgre SQL 也不錯，和 MySQL 比較起來，其最大的優點就是支援 transaction。

所謂的 transaction 就是將一連串的 SQL 指令做為一個執行單位，當其中一個指令失敗，在同一個 transaction 所執行過的命令都取消。Transaction 對於程式開發的應用十分重要，例如，當我們要新增處理一筆訂單時，我們會先將訂單輸入資料庫中，再將金額輸入應收帳款中。如果我們在新增應收帳款時出現錯誤，而訂單卻已輸入資料庫，是不正確的做法。有了支援 transaction 的資料庫，我們可以將訂單的輸入和應收帳款的輸入做為一個執行單位，如果其中一個執行失敗，則取消先前先做的動作，如此一來便可以確保資料的正確性。由於 transaction 十分重要，因此在做為商業上的應用時，我會選擇 PostgreSQL。所以除了 MySQL 外，我們也會介紹如何在 FreeBSD 上安裝及使用 PostgreSQL。在這一章中，我們不再介紹如何安裝 MySQL，您可以參考第十章網頁伺服器中關於安裝 MySQL 的說明。

在使用資料庫之前，我們必須先了解一些簡單而基本的資料庫理論。基本上資料庫的結構有下列幾個特點：

• 一個資料庫系統中可以有多個獨立資料庫。
• 資料庫是由許多資料表 (table) 所組成。
• 資料表中包含許多記錄 (record)。
• 每一筆記錄中的欄位數目都一樣。
• 每一個欄位儲存一種分類過的資料。

例如我們有一個資料庫名稱是NCU，其中有多個資料表，其中一個資料表名為 student 內容如下：

在資料表中有許多欄位 (column)，每個欄位都有一個名稱，也就是第一列 (row) 中的 STUDENT_ID、LAST_NAME、FIRST_NAME、DEPARTMENT。接著我們將資料存入，每一筆記錄我們都可以看成一列 (row)，每一個記錄都有一個「唯一的 ID (編號)」。唯一的 ID 十分重要，它是我們在存取資料庫時的依據。在新增資料時，以 MySQL 而言，我們可以自行指定 ID 或是由系統自行取得。

另一個觀念是關聯式資料庫。關聯式資料庫的意義就是每一個資料表間可以存在關係，例如我們在 NCU 的資料庫中有另一個資料表名為 score，內容如下：

score 資料表中存放學生的成績，我們不需在該資料表中存放學生的資訊，只要在該資料表中存放一個欄位名為 STUDENT_ID，經由這一個唯一的 ID 我們可以去 studnet 的資料表中找到學生的資料。

有了這些觀念就足以讓我們開發出許多資料庫的程式了。

16.2 SQL 語法介紹

SQL (Structured Query Language) 語法十分簡單，它是關聯式資料庫的標準語言，雖然在某些不同資料庫系統上有些許的差異，但基本上都遵循一定的標準。

我們可以在命令列下進入 MySQL 來練習 SQL 的語法：

# /usr/local/mysql/bin/mysql -u root -p

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 202 to server version: 4.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

下完指令後會要求輸入密碼， 之後你就可以直接在出現的命令列 mysql> 之後輸入 SQL 的語法了。

關於 MySQL 詳細的語法，你可以參考 MySQL 中文參考手冊，該文件可以在 http://www.freebsd.org.hk/html/mysqldoc/ 中找到。該文件中對於 MySQL 每個細節都有詳細的描述，例如欄位的名稱限制、規則等，我們不會在這裡提及。我們只介紹幾個簡單而常用的指令。 我們以建立一個學生資料表來說明這些語法。

• CREATE
• ALTER
• DROP
• INSERT
• SELECT
• UPDATE
• DELETE
• 製成 script 檔

16.2.1 CREATE

建立資料庫：CREATE DATABASE db_name
建立資料表：CREATE TABLE tbl_name [(create_definition,...)] [options]

我們先建立一個名為 NCU 的資料庫：

mysql> CREATE DATABASE NCU;

請注意，每一個指令皆以 ";" 為結尾，如果沒有 ";" 就算換行也是代表同一條指令的延續。

我們可以使用下列指令 show 來列出系統中已存在的資料庫有哪些：

mysql> show databases;
+-----------+
| Database  |
+-----------+
| mysql     |
| test      |
| NCU       |
+-----------+
3 rows in set (0.01 sec)

接著用 USE 這個指令來使用 NCU 資料庫：

mysql> USE NCU;

接著建立一個放置學生資料的資料表，名為 STUDENT，內容為編號(STUDENT_ID)、姓名 (NAME)、科系 (DEPARTMENT)：

mysql> CREATE TABLE STUDENT (
STUDENT_ID int(10) DEFAULT '0' NOT NULL AUTO_INCREMENT,
NAME varchar(50),
DEPARTMENT varchar(10),
PRIMARY KEY (STUDENT_ID) ); 

在上面的指令中，我們定義學生編號為十位數的整數(int)，內定值是 0，不可以是空的 (NOT NULL)，數字自動增加 (AUTO_INCREMENT)。姓名是最長為五十個字節的字串(VARCHAR)，科系為最長十個字節的字串。最後定義主要的 id 是 STUDENT_ID，也就是該資料表中的唯一 ID。

我們可以看到在建立資料表時，我們會順便劃分各個欄位所要儲存的資料長度及其格式，常用的欄位格式請參考 MySQL 中文參考手冊。

如果要看現在使用的資料庫中有哪些資料表，一樣可以使用指令 show 來查看：

mysql> show tables;
+--------------------+
| Tables_in_NCU      |
+--------------------+
| STUDENT            |
+--------------------+
4 rows in set (0.00 sec)

16.2.2 ALTER

建立了資料表後，如果發現資料表的欄位不符需求，我們不必將資料表刪除重建，可以使用 ALTER 指令來修改資料表的格式。另如我們要新增一個姓別欄位，內容只可以是 "男" 或 "女"，我們可以使用下面的指令：

mysql> ALTER TABLE STUDENT 
ADD SEX ENUM('男','女') DEFAULT '女';

我們增加了一個欄位 SEX，使用 ENUM 的格式，指定內容只能為 "男" 或 "女"，且預設值是 "女"。

如果我們要將 SEX 欄位改名為 DISTINCTION，並將格式改為 VARCHAR：

mysql> ALTER TABLE STUDENT 
CHANGE SEX DISTINCTION VARCHAR(4);

如果我們只是要將 SEX 欄位格式改為 VARCHAR，但不更改名稱，只要將上面的指令中 DISTINCTION 改成 SEX 即可。

如果我們要刪除整個 DISTINCTION 欄位及該欄位的資料：

mysql> ALTER TABLE STUDENT DROP DISTINCTION;

16.2.3 DROP

刪除資料庫：DROP DATABASE db_name
刪除資料表：DROP TABLE table_bame

我們可以使用 DROP 指令來刪除不要的資料。例如我們要刪除 STUDENT 這一個資料表的話，可以使用下列指令：

mysql> DROP TABLE STUDENT;

16.2.4 INSERT

使用 INSERT 指令可以讓我們一筆一筆增加資料。

假設我們的資料表中的欄位如上表，我們要新增一筆資料，姓名是 JACK、部門是 MIS：

mysql> INSERT INTO STUDENT (NAME, DEPARTMENT)
VALUES ('JACK', 'MIS');

由於我們在指定 STUDENT_ID 的格式時，加了參數 AUTO_INCREMENT，所以我們不需指定值，mysql 會自動幫我們依序指定。

16.2.5 SELECT

我們可以使用 SELECT 來看資料表中的資料，還可以依自己給定的條件來過濾資料。

假設我們要看 STUDENT 資料表中的所有資料的話，可以使用下列指令：

mysql> SELECT * FROM STUDENT;

假設我們只要看 NAME 及 DEPARTMENT 欄位的話，我們可以使用下列指令：

mysql> SELECT NAME, DEPARTMENT FROM STUDENT;

假設我們只要看 NAME 欄位，而且所屬部門為 MIS 的人：

mysql> SELECT NAME FROM STUDENT
WHERE DEPARTMENT='MIS';

假設我們要看 MIS 部門中的人所有欄位，而且輸出結果時要依 STUDENT_ID 來排序：

mysql> SELECT * FROM STUDENT
WHERE DEPARTMENT='MIS'
ORDER BY STUDENT_ID DESC;

最後的 DESC 表示遞減 (descending)，由大到小排序。也可以使用 ASC 來表示遞增 (ascending)。

除了這些之外，在 MySQL 中還有一些可以使用的函式，例如我們可以使用 count() 這個函式來計算出有多少筆記錄：

mysql> SELECT count(*) FROM STUDENT WHERE DEPARTMENT='MIS';
+----------+
| count(*) |
+----------+
| 5        |
+----------+
1 row in set (0.00 sec)

上述結果表示部門為 MIS 的記錄有五筆。

會了這些基本的 INSERT 指定就夠我們做一般的應用了。

16.2.6 UPDATE

我們可以使用 UPDATE 指令來更新記錄。例如我們要將所有記錄的部門資料為 MIS 者都改成 CSIE，可以使用下列指令：

mysql> UPDATE STUDENT SET DEPARTMENT='CSIE' 
WHERE DEPARTMENT='MIS';

16.2.7 DELETE

DELETE 指令可以讓我們刪除一筆或多筆資料。例如我們要刪除 STUDENT 資料表中姓名為 JACK 的記錄：

mysql> DELETE FROM STUDENT WHRE NAME='JACK';

如果我們要刪除姓名為 JACK 且部門為 MIS 的資料：

mysql> DELETE FROM STUDENT 
WHERE NAME='JACK' AND DEPARTMENT='MIS';

16.2.8 製成 script 檔

我們可以將要執行的指定製成檔案，以利管理。例如我們寫了一個程式，需要先在資料庫中建立一些資料，我們可以將對資料庫的規劃做成一個檔案來管理。這樣可以使用們要安裝程式時更快速方便。

假設我們要建立一個資料庫 NCU，該資料庫中有一個資料表 STUDENT，資料表中要先建有以下記錄：

我們先建立一個檔案名為 ncu.sql，內容如下：

CREATE DATABASE NCU;
USE NCU;

CREATE TABLE STUDENT (
STUDENT_ID int(10) DEFAULT '0' NOT NULL AUTO_INCREMENT,
NAME varchar(50),
DEPARTMENT varchar(10),
PRIMARY KEY (STUDENT_ID) ); 

INSERT INTO STUDENT (NAME, DEPARTMENT)
VALUES ('JACK', 'MIS');

INSERT INTO STUDENT (NAME, DEPARTMENT)
VALUES ('MARY', 'CSIE');

接著使用下列指令來快速建立資料庫：

# /usr/local/mysql/bin/mysql -u root -p < ncu.sql

輸入使用者 root 的密碼後就完成建立了。

如果我們在資料庫中早就有一個資料庫名為 NCU，而我們要新增上述資料表及記錄，我們只要將原本 ncu.sql 的內容最前面二行刪除，改成下列內容：

CREATE TABLE STUDENT (
STUDENT_ID int(10) DEFAULT '0' NOT NULL AUTO_INCREMENT,
NAME varchar(50),
DEPARTMENT varchar(10),
PRIMARY KEY (STUDENT_ID) ); 

INSERT INTO STUDENT (NAME, DEPARTMENT)
VALUES ('JACK', 'MIS');

INSERT INTO STUDENT (NAME, DEPARTMENT)
VALUES ('MARY', 'CSIE');

之後再以下列指令來在 NCU 資料庫中建立資料表：

# /usr/local/mysql/bin/mysql -u root -p NCU <ncu.sql

在網路上有許多 PHP 程式可以下載，下載後要安裝資料庫時，大多是以這種方式來使用。

16.3 MySQL 管理

16.3.1 維護密碼安全

當我們要使用 MySQL 時，必須輸入密碼。輸入密碼的方式有很多種，第一種也是最不安全的一個方式是直接在命令列打指令時就輸入：

# /usr/local/mysql/bin/mysql -u root -pmypwd

上面這種方法會讓別的使用者使用 ps 指令就可以看到你在執行的指定及密碼。因此絕對不要使用這種方法，請改用下列方式輸入：

# /usr/local/myqsl/bin/mysql -u root -p

接著會要求你輸入密碼時再輸入即可。

另一個方式是在你的家目錄下建立一個存放密碼的檔案，檔名為 .my.cnf，當 mysql 需要使用密碼時會自動去讀取。該檔的內容如下：

[client]
password=your_passowrd

接著要把 .my.cnf 的權限改成只有檔案擁有者才可以讀寫：

# chmod 600 ~/.my.cnf

16.3.2 備份資料庫

資料庫的資料要定時備份，這樣才不會在失手時或系統有問題時產生困擾。在 MySQL 中提供一個備份程式 msqyldump。

假設我們有一個資料庫名為 WWW，我們可以使用下列指令來備份整個資料庫：

# /usr/local/mysql/bin/mysqldump -u root -p WWW > www.sql

這樣就可以把資料庫的資料存在 www.sql 這個檔案中了。日後要回復時只要使用下列指定就可以把資料存回：

# /usr/local/mysql/bin/mysql -u root -p WWW < www.sql

我們要注意的是備份出來的檔案應該要放在不同的電腦中，而且要注意權限的控制。由於該檔是文字檔，任何人都可以讀，所以要特別注意。

我們可以利用 crontab 這個指令來定時備份資料庫。我們先建立一個 shell script 檔，名為 backupsql.sh，內容如下：

/usr/local/mysql/bin/mysqldump -uroot WWW>/home/www.sql
chmod 600 /home/www.sql

接著將該檔權限改成只有擁有人可以讀、寫、執行，其他人都不行：

# chmod 700 backupsql.sh

然後建立 ~/.my.cnf 檔案內容如下：

[client]
password=your_passowrd

接著要把 .my.cnf 的權限改成只有檔案擁有者才可以讀寫：

# chmod 600 ~/.my.cnf

接著要讓它能定時執行，命令列打 crontab -e 來進入文字編輯，加入下列內容：

#每天 3:05 備份網頁資料庫
5	3	*	*	*	/root/backupsql.sh

16.3.3 使用者管理

MySQL 對於使用者的權限控制存放在名為 mysql 資料庫中的 user 資料表內。user 資料表內有下列幾個欄位：

我們在新增一個 MySQL 使用者時，有二種方式。比較差的方式是使用 INSERT 指令：

# /usr/local/mysql/bin/mysql -u root -p mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 202 to server version: 4.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> insert into user values ('host','user', password('密碼'),
'y','y','y','y','y','y','y','y','y','y','y','y','y','y');
Query OK, 0 row affected (0.00 sec)

上面 mysql 指令中的 host 就是要予許連線的主機，如果是本機則輸入 localhost；而 user 是使用者名稱；密碼是該使用者的密碼，要使用 password() 函數來將它加密；接下來的一堆 'Y' 就是對上表中的權限是否要開放，如果不開放則填 'N'。

第二種方式是使用 GRANT 指令來新增使用者，GRANT 在設定使用者權限時，如果使用者存在則更新其權限，如果不存在則新增該使用者。

用法：

GRANT 權限 ON 資料庫(或表) TO user@host IDENTIFIED BY '密碼'; 

範例一：

新增一個本機的使用者 admin，並開放所有權限，密碼為 mypwd：

mysql> GRANT ALL PRIVILEGES ON *.* TO admin@localhost IDENTIFIED BY 'mypwd';
Query OK, 0 row affected (0.00 sec)

範例二：

新增一個來自 www.mydomain.com 的使用者 www，並設定只能對 www 資料庫中所有資料表執行 SELECT, INSERT, UPDATE, DROP, CREATE, DELETE, INDEX，密碼為 mypwd：

mysql> GRANT SELECT, INSERT, UPDATE, DROP, CREATE, DELETE, 
INDEX ON www.* TO www@www.mydomain.com IDENTIFIED BY 'mypwd';
Query OK, 0 row affected (0.00 sec)

如果要刪除使用者上述新增的使用者 www，可以使用下列指令：

mysql> DELETE FROM user WHERE user='www' and host='www.mydomain.com';
Query OK, 1 rows affected (0.01 sec)

在新增或刪除使用者後，離開 MySQL 之前都必須指行下列指令來讓它生效：

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

16.3.4 如何更改使用者密碼

我們可以使用下列指令來更改自己的密碼：

# /usr/local/mysql/bin/mysqladmin -u root -p password newpwd

上面指令中的使用者是 localhost 的 root ，新的密碼是 newpwd。在輸入指令後，會先詢問你舊的密碼。

我們也可以使用具有管理使用者權限的 mysql 使用者登入 MySQL 後，使用 UPDATE 指令來更改密碼：

# /usr/local/mysql/bin/mysql -u root -p mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 202 to server version: 4.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> UPDATE user set password=password('新密碼') 
where user='使用者' and host='主機';

16.4 PostgreSQL 安裝設定

您可以選擇使用 ports 或 package 來安裝 PostgreSQL，我們以 ports 安裝為例：

# cd /usr/ports/databases/postgresql7
# make install

執行了 make install 之後，您將看到一個提示訊息，要求你先行備份原本的資料庫。如果您是第一次安裝 PostgreSQL，可以直接略過。

安裝完成後，我們就可以開始做資料庫的初始化了。我們使用下列指令來初始化資料庫：

# su -l pgsql -c initdb

這個指令的意思是以使用者 pgsql 的身份執行 initdb 並設定預設的編碼方式為 SQL_ASCII。PostgreSQL 安裝時會自動建立一個使用者及群組 pgsql，這是 PostgreSQL 預設最高使用者的帳號，您可以使用 vipw 來修改該使用者的資料。由於 pgsql 預設使用的 shell 是 sh，筆者習慣使用 tcsh，所以我將該使用者的資料修改如下：

pgsql:*:70:70::0:0:PostgreSQL Daemon:/usr/local/pgsql:/bin/tcsh

初始化資料庫後還有一些後續的設定。一開始 PostgreSQL 只允許讓 pgsql 這個使用者經由本機連線存取資料庫，如果您希望其他使用者可以經由其他機器連線，您必須先修改 ~pgsql/data/postgresql.conf 這個檔案。找出 tcpip_socket 的部份，並修改如下：

tcpip_socket = true

postgresql.conf 這個檔案記錄著 PostgreSQL 的其本設定，其中使用 "#" 為首的是註解。

接著我們要設定從別的機器連線所使用的認證方式，請編輯 ~pgsql/data/pg_hba.conf，在文件最下方加入下列設定：

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
local   all         all                                             trust
host    all         all         127.0.0.1         255.255.255.255   trust
host    all         all         192.168.0.1       255.255.255.0     md5

這裡的設定除了第一行是註解外，第二、三行表示信任來自本機的連線，只要使用者存在於資料庫中就不需要密碼，這二行預設就存在於 pg_hba.conf 中。最後一行表示網域 192.168.0.1~192.168.0.255 的連線都要使用 md5 驗証密碼。

在我們新增其他使用者之前，必須先啟動 PostgreSQL ，以下為啟動資料庫服務的指令：

# /usr/local/etc/rc.d/010.pgsql.sh start

因為 010.pgsql.sh 這支 script 放在 /usr/local/etc/rc.d ，所以在一開機時，系統就會自動執行它來啟動 PostgreSQL，如果您要停止 PostgreSQL，只要執行下列指令：

# /usr/local/etc/rc.d/010.pgsql.sh stop

接著我們就可以增加一個可以使用遠端連線的使用者：

# su -l pgsql
% createuser -P
Enter name of user to add: alex
Enter password for user "alex":
Enter it again:
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y
CREATE USER

如此一來我們就可以使用 alex 這個使用者從遠端登入了。

16.5 PostgreSQL 管理指令

PostgreSQL 和 MySQL 在指令的應用上有所不同，它將許多管理資料庫的指令獨立成一個個的執行檔，其中有些指令是使用 psql 為基礎所寫成的 scripts。例如新增、刪除資料庫或使用者等指令，都可以直接在命令列執行。以下為常用的指令列表：

16.5.1 建立及刪除使用者

因為 PostgreSQL 安裝完畢時只有一個使用者 pgsql，如果您要使用其它使用者登入，您必須先以 pgsql 這個使用者來新增其它使用者帳號。首先，我們先將身份切換成 pgsql：

# su -l pgsql

我們使用 su 加上參數 -l 表示模擬使用者真正 login 的情形，也就是會將工作目錄切換到 /usr/local/pgsql，並載入該目錄中的 .cshrc 等檔案。接著我們就可以使用下列指令來建立一個新的使用者了，假設我們要為 root 建立一個帳號：

% createuser root
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y
CREATE USER

如此一來，root 也具有存取資料庫的權限了。但因為 PostgreSQL 內定沒有密碼的使用者不可以使用遠端登入，如果您希望所新增的使用者可以使用遠端登入，您必須在 createuser 指令加上參數 -P，請注意 P 是大寫喔。如果你要從資料庫中刪除一個使用者帳號，只要使用 dropuser 這個指令即可：

% dropuser root

值得注意的是，您無法使用刪除你正在使用中的帳號，例如以 root 身份來刪除 root 是不被允許的。

16.5.2 建立及刪除資料庫

在使用資料庫之前，我們必須先建立一個資料庫。假設我們要建立的資料庫名稱為 MYDB，您可以使用下列指令加以建立：

% createdb MYDB

在 UNIX 的世界中，大小寫是有分別的，在 PostgreSQL 中也是一樣。因此，不論是在建立資料庫，資料表或其欄位時，都要注意大小寫。建議您除了資料庫名稱外，最好全部使用小寫。

同樣的，如果你要刪除一個資料庫，只要使用 dropdb 這個指令：

% dropdb MYDB

16.5.3 互動式 SQL 指令

PostgreSQL 的 client 端指令中，功能最強大的莫過於 psql 這個指令了。psql 可以除了讓我們進入互動式的 SQL statement 環境外，也可以加上一些參數變成一個直接回應的指令。例如，我們想要查看目前有哪些資料庫：

% psql -l
List of databases
Name | Owner | Encoding
-----------+-------+-----------
MYDB | root | SQL_ASCII 
template0 | pgsql | SQL_ASCII
template1 | pgsql | SQL_ASCII
(3 rows)

加上 -l 這個參數後，就可以列出所有資料庫名稱了。除了您所建立的資料庫外，還有二個 templateX 的資料庫，該資料庫存放著 PostgreSQL 的設定，不可以刪除。接下來讓我們選定一個資料庫以進入互動式的視窗：

% psql MYDB
Welcome to psql, the PostgreSQL interactive terminal.
Type:	\copyright for distribution terms
	\h for help with SQL commands
	\? for help on internal slash commands
	\g or terminate with semicolon to execute query
	\q to quit
MYDB=# 

在這裡，我們可以使用 16.2 所列出的一些標準的 SQL statement 來存取資料庫，例如 create、drop、delete、update、insert、alter 等。建議您進一步至 PostgreSQL 網站參考其使用手冊，PostgreSQL 官方網站是 http://www.postgresql.org 。您也可以在下列網址中找到中文的使用手冊：http://www.freebsd.org.hk/html/pgsqldoc-7.0c/postgres.htm。

在互動式的介面中，您可以使用 \h 及 \? 來查詢可以使用的指令。其中 \h 為查詢 SQL statement ，而 \? 則是常詢 PostgreSQL 特有的反斜線指令，我們最常用的反斜線指令有 \q 離開互動式介面，及 \d 列出該資料庫的所有資料表。

如果您覺得這種命令列的介面不好使用，我們在下一節將會介紹如何在 FreeBSD 及 MS Windows 使用圖形化介面的管理工具。

16.5.4 資料庫備份及回復

定期備份資料庫是十分重要的一件事，我們一定要養成備份的習慣。在 PostgreSQL 中，備份十分容易，假設我們要備份的資料庫是 MYDB，您可以使用下列指令：

% pg_dump MYDB > MYDB.sql

如此一來，你就可以把 MYDB 這個資料庫 dump 出來了。然而，資料庫的資料往往十分龐大，動輒數十 MB 至數百 MB，為了節省空間，您可以在備份時順便壓縮資料庫。以上述指令而言，我們只要將輸出導向到 gzip 即可進行同步壓縮：

% pg_dump MYDB | gzip > MYDB.sql.gz

我們一般從資料庫 dump 出來的資料都是文字檔，所以使用 gzip壓縮可以得到很高的壓縮比。假設不壓縮所備份出來的檔案有五十 MB，使用 gzip壓縮後大約只剩六百多 KB。因此，我習慣都會加上 gzip壓縮。

pg_dump 這個指令只能用來備份單一的資料庫，如果您要將所有的資料庫中都備份起來，您可以使用 pg_dumpall 來備份：

% pg_dumpall |gzip > ALLDB.sql.gz

有了備份，自然也要回存。由於我們使用 pg_dump 所備份出來的資料庫實際上是將一堆資料以 SQL statement 的方式存起來，如果您將該備份的檔案以文書編輯器打開，您可以看到它其實是先存放資料庫中所有資料表的資訊，再將存放資料。所以我們只要將這些指令導向到 psql 來執行即可。首先，請先建立要回存的資料庫名稱，假設我們要將 MYDB 所備份出來的資料存放在 NEWDB 這個資料庫中，我們要先建立一個名為 NEWDB 的資料庫：

% createdb NEWDB

接著再使用下列指令來將資料回存：

% cat MYDB.sql | psql NEWDB

如果您備份出來的資料有經過壓縮，則需改以下列指令回存：

% gunzip -c MYDB.sql.gz | psql NEWDB

或是

% cat MYDB.sql.gz | gunzip | psql NEWDB

如果您要回存的檔案是經由 pg_dumpall 所備份出來的資料，則必須使用 pgsql 這個使用者來執行下列指令：

% gunzip -c ALLDB.sql.gz | psql -e template1

16.6 PostgreSQL 圖形化管理工具介紹

許多人可能不太習慣使用命令列來管理資料庫，還好 PostgreSQL 提供了許多圖形介面的管理工具。您可以在 MS Windows 執行的 pgAdmin，另外也有 ODBC 介面可供使用。由於這些圖形介面操作上比較容易，只要您多試幾次，就可以熟悉它們的使用，因此，我們不會深入介紹每個功能的用法。

15.6.1 pgAdmin

如果您習慣使用 MS Windows，您可以使用 pgAdmin 在 MS Windows 上管理資料庫。對於初學者而言，使用 pgAdmin 會比在命令列中輸入來得容易。您可以自 http://pgadmin.postgresql.org 下載最新版本的 pgAdmin，或者您也可以在本書光碟二 /wintools 目錄中找到 pgadmin3-1_0_2.zip。您只要將它解壓縮，即可安裝。

接著我們打開 pgAdmin，按了左上角的圖示後即出現連線設定的表單。請輸入您資料庫伺服器的位置及帳號密碼，如圖 16-1 所示：

圖 16-1

請注意，您必須先將 PostgreSQL 的 TCP/IP 連線打開，而且在 pg_hba.conf 中必須允許使用 pgAdmin 的這台主機登入。您可以參考 16.4 中的說明來設定 pg_hba.conf。輸入連線資料後，即可開始使用。以建立一個新的資料庫為例，我們先在左邊的視窗中，對著資料庫按右鍵，再選取 [新物件]->[新建資料庫] 如圖 16-2 所示：

圖 16-2

接著我們可以輸入資料庫名稱、編碼方式等，如圖 16-3：

圖 16-3

其它建立資料表等操作也都大同小異，您可以每一個功能都試試看。

16.6.2 ODBC

許多人習慣在 MS Windows 上做程式開發，透過 ODBC 存取資料庫。PostgreSQL 也有提供 ODBC 驅動程式，你可以自 http://odbc.postgresql.org 下載，也可以在本書光碟二 wintools 目錄中取得 psqlodbc-07_02_0005.zip。解壓縮並安裝後，您就可以開始做 ODBC 設定。

如果您使用的是 Windows NT、Windows 2000、或 Windows XP，請執行 [控制台] -> [系統管理工具] -> [資料來源]。如果你使用的是 Windows 98、Windows ME，請執行 [控制台] -> [資料來源]。

圖 16-4

我們接著點選 [檔案資料來源] -> [新增]，然後會出現以下畫面：

圖 16-5

我們選擇 PostgreSQL 為資料來源後，將出現圖 16-6 的畫面：

圖 16-6

我們為它取一個名字並決定儲存地點後，按下一步即完成。完成後會出現圖 16-7 的畫面讓您輸入認証的資料，全部輸入後即完成 PostgreSQL ODBC 的安裝。

圖 16-7