第十九章 資料庫系統
現在許多的商業應用、網頁程式都必須使用資料庫來存放資料。例如，網站上的留言版、討論區、購物網站等，全部都是使用資料庫來存放重要的資料。本章中，我們將介紹二種常見的資料庫：MySQL 及 PostgreSQL。
本章包含了資料庫的安裝、設定、語法、及各種操作方式的介紹，讀完本章後，您將可以了解下列主題：
這些資訊將有助您建立一個功能強大的網站，並對於日後安裝資料庫相關應用時更加得心應手。
19.1 概論
電子商務的興起讓資料庫的應用更受到大家的矚目。在資訊科學的應用上，資料庫可以說是最歷久彌堅的領域。近來，資料產生和資料收集方面的技術有非常快速的進展。許多商業產品廣泛使用了條碼、許多企業和政府的交易皆已電腦化，這使得電腦成為資料收集的主要工具。同時，數以百萬計的資料庫正被使用在企業管理、政府管理、科學和工程的資料管理和許多其它的應用上。
我們可以安裝一套資料庫系統，並經由一個介面自行開發程式來使用它。資料庫的好處有很多，相信對資料庫稍有涉入的人都知道，例如資料存取快速、不重覆、權限控制、資料獨立性等等。以寫一個簡單的留言版程式而言，傳統上使用檔案做為留言的記錄，若要刪除一筆資料，必須對整個檔案一行一行的比對；但資料庫只需指定該留言的編號即可。不過，如果把資料庫系統 侷限於留言版也太大才小用了。
我們將介紹在 FreeBSD 上使用資料庫，因為目前網頁資料庫使用情形十分風行，尤其在網頁開發上使用 MySQL 或 PostgreSQL 資料庫加 PHP 更是絕配。目前 Open Source 的資料庫中，最常用、最有名的就是 PostgreSQL 及 MySQL，因此，本章將先介紹這二個資料庫系統的安裝及使用 。
至於 PostgreSQL 及 MySQL 這二個資料庫系統有什麼差別？到底應該選哪一個呢？在 MySQL 3.x 以前，如果您問我這個問題，我一定說 PostgreSQL，因為 MySQL 3.x 不支援 transaction。所謂的 transaction 就是將一連串的 SQL 指令做為一個執行單位，當其中一個指令失敗，在同一個 transaction 所執行過的命令都取消。Transaction 對於程式開發的應用十分重要，例如，當我們要新增處理一筆訂單時，我們會先將訂單輸入資料庫中，再將金額輸入應收帳款中。如果我們在新增應收帳款時出現錯誤，而訂單卻已輸入資料庫，是不正確的做法。有了支援 transaction 的資料庫，我們可以將訂單的輸入和應收帳款的輸入做為一個執行單位，如果其中一個執行失敗，則資料庫會自動取消先前先做的動作，如此一來便可以確保資料的正確性。由於 transaction 十分重要，因此在做為商業上的應用時，我會選擇 PostgreSQL。
不過 MySQL 4.x 後已經加入 transaction 的支援，只是功能還是沒有 PostgreSQL 那麼完整。那麼除此之外，這二個資料庫的差別是什麼？PostgreSQL 是一個功能強大的資料庫系統，它的威力不下於商業用的資料庫。而 MySQL 的特性是對於簡單 SQL 指令處理快速，大部份的日常簡單操作，MySQL 的速度會比 PostgreSQL 快一點。以下我們列出它們在功能上的主要差異：
| 功能 | MySQL 3.x | MySQL 4.1.x | PostgreSQL |
| 版權宣告 | GPL | GPL | BSD |
| Sub-Selects | No | Yes | Yes |
| Views | No | No | Yes |
| Foreign Key relationships | No | Yes | Yes |
| Foreign Key constraints | No | No | Yes |
| Triggers | No | No | Yes |
| Indexing on non trivial types | No | No | Yes |
| Sequences | Some | Some | Yes |
| Transactions | No | Yes | Yes |
| OO (Inheritance of tables) | No | No | Yes |
| Async Notifications | No | No | Yes |
| Constraints | No | No | Yes |
| SELECT INTO | No | Yes | Yes |
| Stored Procedures | No | No | Yes |
| Row level locking | Yes | Yes | Yes |
| Table level locking | Yes | Yes | Yes |
| Multi version Concurrency Control | No | No | Yes |
我們可以看到 PostgreSQL 功能真的強大很多，如果您必須設計功能強大又複雜的系統，PostgreSQL 是不二之選。而 MySQL 4.x 以後的重要功能都很齊全，速度也很快，比較適合簡單的網頁應用，所以目前大部份網頁應用軟體使用 MySQL 比較多。不過呢，筆者認為 PostgreSQL 及 MySQL 都使用 Transaction 時，二者的效能表現差不多，就網頁應用而言，我的首選還是 PostgreSQL。
19.2 安裝 MySQL
MySQL 和 FreeBSD 一樣也有多種版本同時開發，例如 3.x、4.0.x、4.1.x、5.x 等，其中 5.0.x 是目前較穩定的版本。
您可以自行到 http://www.mysql.com 取得最新版的 MySQL Source Package 來安裝，不過安裝上比較麻煩。所以我們使用 ports 來安裝 MySQL：
# cd /usr/ports/databases/mysql50-server # make WITH_CHARSET=big5 WITH_XCHARSET=all install clean
我們指定 MySQL 預設的字集為 Big5，並另外支援所有的字集。接下來請修改 /etc/rc.conf 並加入下列這一行，以讓開機時啟動 MySQL：
mysql_enable="yes" |
接著您就可以使用下列指令啟動 MySQL 了：
# /usr/local/etc/rc.d/mysql-server start
啟動 MySQL 後，我們就可以使用下列指令進入 MySQL 互動式命令列了：
# /usr/local/bin/mysql mysql
若安裝成功，你將看到以下畫面：
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 5.0.33 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> |
MySQL 剛安裝完成時，並未設定 root 的密碼，因此我們接著要設定 root 的密碼並即時更新設定：
mysql> UPDATE user SET password=password('你的密碼') where user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
|
以上指令及 MySQL 更詳細的設定說明，我們會在下一小節中加以說明。最後請以 exit; 來離開 MySQL。
如果您有其他使用者要加入也可以加入，最好不要讓使有人都有對所有資料庫有全部的權限。 使用命令列的方式新增使用者有點麻煩，我們等一下再使用其它圖形化介面進入新增使用者。
建議您以後使用 MySQL 的圖形化介面管理工具「MySQL Administrator」及「MySQL Query Browser」來管理 MySQL，我們會再後續章節中說明如何使用這些管理工具。
19.3 SQL 語法介紹
在使用資料庫之前，我們必須先了解一些簡單而基本的資料庫理論。如果您對於資料庫 SQL 語法已經很熟悉了，您可以跳過這一個小節。
基本上資料庫的結構有下列幾個特點：
例如我們有一個資料庫名稱是NCU，其中有多個資料表，其中一個資料表名為 student 內容如下：
| STUDENT_ID | LAST_NAME | FIRST_NAME | DEPARTMENT |
| 1 | Chang | Jack | MIS |
| 2 | Wang | Alex | BA |
在資料表中有許多欄位 (column)，每個欄位都有一個名稱，也就是第一列 (row) 中的 STUDENT_ID、LAST_NAME、FIRST_NAME、DEPARTMENT。接著我們將資料存入，每一筆記錄我們都可以看成一列 (row)，每一個記錄都有一個「唯一的 ID (編號)」。唯一的 ID 十分重要，它是我們在存取資料庫時的依據。在新增資料時，以 MySQL 而言，我們可以自行指定 ID 或是由系統自行取得。
另一個觀念是關聯式資料庫。關聯式資料庫的意義就是每一個資料表間可以存在關係，例如我們在 NCU 的資料庫中有另一個資料表名為 score，內容如下：
| SCORE_ID | STUDENT_ID | CHINESE | ENGLISH |
| 1 | 2 | 99 | 90 |
| 2 | 1 | 89 | 87 |
score 資料表中存放學生的成績，我們不需在該資料表中存放學生的資訊，只要在該資料表中存放一個欄位名為 STUDENT_ID，經由這一個唯一的 ID 我們可以去 student 的資料表中找到學生的資料。
有了這些觀念就足以讓我們開發出許多資料庫的程式了。
SQL (Structured Query Language) 語法十分簡單，它是關聯式資料庫的標準語言，雖然在某些不同資料庫系統上有些許的差異，但基本上都遵循一定的標準。
我們可以在命令列下進入 MySQL 來練習 SQL 的語法：
# /usr/local/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.1.13 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 每個細節都有詳細的描述，例如欄位的名稱限制、規則等，我們不會在這裡提及。我們只介紹幾個簡單而常用的指令。 我們以建立一個學生資料表來說明這些語法。
19.3.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) 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) |
19.3.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; |
19.3.3 DROP
刪除資料庫：DROP DATABASE db_name
刪除資料表：DROP TABLE table_bame
我們可以使用 DROP 指令來刪除不要的資料。例如我們要刪除 STUDENT 這一個資料表的話，可以使用下列指令：
mysql> DROP TABLE STUDENT; |
19.3.4 INSERT
使用 INSERT 指令可以讓我們一筆一筆增加資料。
| STUDENT_ID | NAME | DEPARTMENT |
| 1 | Jack | MIS |
假設我們的資料表中的欄位如上表，我們要新增一筆資料，姓名是 JACK、部門是 MIS：
mysql> INSERT INTO STUDENT (NAME, DEPARTMENT)
VALUES ('JACK', 'MIS');
|
由於我們在指定 STUDENT_ID 的格式時，加了參數 AUTO_INCREMENT，所以我們不需指定值，mysql 會自動幫我們依序指定。
19.3.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 指定就夠我們做一般的應用了。
19.3.6 UPDATE
我們可以使用 UPDATE 指令來更新記錄。例如我們要將所有記錄的部門資料為 MIS 者都改成 CSIE，可以使用下列指令：
mysql> UPDATE STUDENT SET DEPARTMENT='CSIE' WHERE DEPARTMENT='MIS'; |
19.3.7 DELETE
DELETE 指令可以讓我們刪除一筆或多筆資料。例如我們要刪除 STUDENT 資料表中姓名為 JACK 的記錄：
mysql> DELETE FROM STUDENT WHRE NAME='JACK'; |
如果我們要刪除姓名為 JACK 且部門為 MIS 的資料：
mysql> DELETE FROM STUDENT WHERE NAME='JACK' AND DEPARTMENT='MIS'; |
19.3.8 製成 script 檔
我們可以將要執行的指定製成檔案，以利管理。例如我們寫了一個程式，需要先在資料庫中建立一些資料，我們可以將對資料庫的規劃做成一個檔案來管理。這樣可以使用們要安裝程式時更快速方便。
假設我們要建立一個資料庫 NCU，該資料庫中有一個資料表 STUDENT，資料表中要先建有以下記錄：
| STUDENT_ID | NAME | DEPARTMENT |
| 1 | Jack | MIS |
| 2 | Mary | CSIE |
我們先建立一個檔案名為 ncu.sql，內容如下：
CREATE DATABASE NCU;
USE NCU;
CREATE TABLE STUDENT (
STUDENT_ID int(10) 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/bin/mysql -u root -p < ncu.sql
輸入使用者 root 的密碼後就完成建立了。
如果我們在資料庫中早就有一個資料庫名為 NCU，而我們要新增上述資料表及記錄，我們只要將原本 ncu.sql 的內容最前面二行刪除，改成下列內容：
CREATE TABLE STUDENT (
STUDENT_ID int(10) 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/bin/mysql -u root -p NCU <ncu.sql
在網路上有許多 PHP 程式可以下載，下載後要安裝資料庫時，大多是以這種方式來使用。
19.4 MySQL 管理
19.4.1 維護密碼安全
當我們要使用 MySQL 時，必須輸入密碼。輸入密碼的方式有很多種，第一種也是最不安全的一個方式是直接在命令列打指令時就輸入：
# /usr/local/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
19.4.2 備份資料庫
資料庫的資料要定時備份，這樣才不會在失手時或系統有問題時產生困擾。在 MySQL 中提供一個備份程式 mysqldump。
假設我們有一個資料庫名為 WWW，我們可以使用下列指令來備份整個資料庫：
# /usr/local/bin/mysqldump -u root -p WWW > www.sql
這樣就可以把資料庫的資料存在 www.sql 這個檔案中了。日後要回復時只要使用下列指定就可以把資料存回：
# /usr/local/bin/mysql -u root -p WWW < www.sql
我們要注意的是備份出來的檔案應該要放在不同的電腦中，而且要注意權限的控制。由於該檔是文字檔，任何人都可以讀，所以要特別注意。
我們可以利用 crontab 這個指令來定時備份資料庫。我們先建立一個 shell script 檔，名為 backupsql.sh，內容如下：
/usr/local/bin/mysqldump -uroot WWW>/home/www.sql chmod 600 /home/www.sql |
接著將該檔權限改成只有擁有人可以讀、寫、執行，其他人都不行：
# chmod 700 backupsql.sh
為了要讓執行 backupsql.sh 時可以不必輸入密碼，我們必須先將密碼存在 ~/.my.cnf ，請先建立 ~/.my.cnf 檔案內容如下：
[client] password=your_passowrd |
接著要把 .my.cnf 的權限改成只有檔案擁有者才可以讀寫：
# chmod 600 ~/.my.cnf
接著要讓它能定時執行，命令列打 crontab -e 來進入文字編輯，加入下列內容：
#每天 3:05 備份網頁資料庫 5 3 * * * /root/backupsql.sh |
19.4.3 使用者管理
如果您要新增可以連線到 MySQL 的使用者，可以在 MySQL 互動式介面串使用 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) |
19.4.4 如何更改使用者密碼
我們可以使用下列指令來更改自己的密碼：
# /usr/local/bin/mysqladmin -u root -p password newpwd
上面指令中的使用者是 localhost 的 root ，新的密碼是 newpwd。在輸入指令後，會先詢問你舊的密碼。
我們也可以使用具有管理使用者權限的 mysql 使用者登入 MySQL 後，使用 UPDATE 指令來更改密碼：
# /usr/local/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.1.13
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> UPDATE user set password=password('新密碼')
where user='使用者' and host='主機';
|
19.5 MySQL 圖形化管理工具介紹
MySQL 有許多圖形化的管理工具，我們在此介紹二個官方的工具「MySQL Administrator」及「MySQL Query Browser」。MySQL Administrator 是用來管理 MySQL Server 用的，您可以查看目前系統狀態、新增使用者等。而 MySQL Query Browser 可以用來查看資料庫內容。
我們可以在一台 Windows 的機器上使用圖形化的管理工具，或者是在本機的 XWindow 中執行也可以。如果要從另一台電腦連線到 MySQL，則在使用這些工具連到 MySQL 之前，您必須先新增具有權限從遠方連線到 MySQL 的使用者。假設我們要新增一個使用者 root，它可以從 192.168.0.2 這台機器連線到 MySQL，而密碼為 mypasswd，則可以使用下列指令：
mysql> GRANT ALL on *.* to root@192.168.0.2 IDENTIFIED BY 'mypasswd'; Query OK, 1 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) |
19.5.1 MySQL Administrator
請先到 MySQL 網站下載 MySQL Administrator，並執行安裝，安裝後請執行 MySQL Administrator。執行後，您會看到下列畫面：
圖 19-1
請在 Server Host 欄位中輸入 MySQL Server 的 IP，並輸入使用者名稱及密碼。登入後的第一頁，您可以看到目前 MySQL 的狀態。如果您的 Server 和 MySQL Administrator 是在同一台機器上，則可以對 MySQL 進行更多的控制，例如停用 MySQL 等。
MySQL Administrator 的管理介面使用上很容易，您可以自已點點看有什麼功能，這裡我們只介紹如何使用它來新增使用者。首先，請在右邊選單中選取「User Administration」，接著在下圖標示 2 的地方按滑鼠右鍵，然後選擇「Add new User」。
圖 19-2
然後您就可以在 MySQL User 欄位中輸入使用者名稱，並輸入密碼。輸入完後請按「Apply changes」以套用設定。
接下來我們可以再進一步設定使用者 john 設定權限。假設我們希望設使用者 john 只可以對資料庫 www 進行 SELECT、INSERT、UPDATE、及 DELETE 的指令，則可以點選「Schema Privileges」標籤，並選取好權限後，按下圖 3 的按鈕以新增權限。最後請點選「Apply changes」以套用設定。
圖 19-3
最後，我們要設定使用者可以從什麼地方連線到 MySQL Server，請對著該使用者按右鍵，並選擇「Add Host From Which The User Can Connect」，接著輸入 IP 即可。
圖 19-4
如果您要刪除使用者，只要對著使用者按右鍵，並選取「Delete User」即可。
19.5.2 MySQL Query Browser
MySQL Query Browser 一樣可以從 MySQL 官方網站下載。下載並安裝後，就可以執行 MySQL Query Browser 了。執行 Query Browser 後，會出現一個要求登入的視窗，同樣的，您必須輸入帳號、密碼及所要連線的主機。
如果您已經安裝了 MySQL Administrator，您可以直接從 MySQL Administrator 中執行 Query Browser，而且不必再輸入帳號密碼：
圖 19-5
在 MySQL Query Browser 中，我們可以建立刪除資料庫、資料表、查詢資料庫中的資料。如果您要建立一個新的資料庫，請對著下圖中 1 的位置按滑鼠右鍵，再點選「Create New Schema」，最後輸入資料庫名稱即可。
圖 19-6
建立了資料庫後，我們可以再建資料表。請對著我們剛建立的資料庫按滑鼠右鍵，再點選「Create New Table」即出現下列視窗：
圖 19-7
請在「Table Name」中輸入表格名稱，並在上圖 2 的部份輸入每一個欄位的類別，最後按「Apply Changes」即可。建立資料庫後，您就可以在 Query Browser 中輸入、檢視資料庫中的資料。只要對著剛才建立的表格點二下，並點選「Execute」即可查看資料庫中的資料。如果您要新增一筆資料，請點選下圖中 3 的位置，並選擇「Edit」即可進行編輯。
圖 19-8
MySQL 圖形化介面的使用相當直覺，您只要多試幾次就可以明白各種使用方法了。
|
19.6 PostgreSQL 安裝設定
另一個好用的資料庫為 PostgreSQL，這是筆者偏好的資料庫，比起 MySQL，它的設定更簡單、功能更強大。
我們同樣使用 port 來安裝 PosgreSQL：
# cd /usr/ports/databases/postgresql82-server # make install clean
執行了 make install 之後，會出現一個進階設定的視窗，我們使用預設的設定即可。接著，您將看到一個提示訊息，要求你先行備份原本的資料庫。如果您是第一次安裝 PostgreSQL，可以直接略過。
安裝完成後，我們就可以開始做資料庫的初始化了。
首先，請先在 /etc/rc.conf 中加入下列這一行以允許系統使用 PostgreSQL：
postgresql_enable="YES" |
我們使用下列指令來初始化資料庫：
# /usr/local/etc/rc.d/postgresql initdb
上述指令是 FreeBSD 6.2 中所包好的初始化 PosrgreSQL 資料庫的指令，如果您使用的是 FreeBSD 6.2 以前的版本，而且沒有更新 port tree 的話，請使用下列指令：
# su -l pgsql -c initdb
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 這個檔案。找出 listen_addresses 的部份，並修改如下：
listen_addresses = '*' |
listen_addresses 是表示您所要允許連線的 IP 位址，我們填入 * 表示允許任何連線。如果您安裝的 PostgreSQL 是 7.x 的版本，您要修改的是這下列一行：
tcpip_socket = true |
postgresql.conf 這個檔案記錄著 PostgreSQL 的其本設定，其中使用 "#" 為首的是註解。 其內容包括可以設定所要使用的連接埠、最大連線數量等，不過我們通常沒有必要修改它。
接著我們要設定從別的機器連線所使用的認證方式，請編輯 ~pgsql/data/pg_hba.conf，在文件最下方加入下列設定：
# "local" is for Unix domain socket connections only local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 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/postgresql start
如果您沒有在 rc.conf 中加入啟動 PostgreSQL 的設定，則上述指令並不會啟動 PostgreSQL。
因為 010.pgsql.sh 這支 script 放在 /usr/local/etc/rc.d ，所以在一開機時，系統就會自動執行它來啟動 PostgreSQL，如果您要停止 PostgreSQL，只要執行下列指令：
# /usr/local/etc/rc.d/postgresql stop
接著我們就可以增加一個可以使用遠端連線的使用者：
# su -l pgsql % createuser -P Enter name of user to add: alex Enter password for user "alex": Enter it again: Shall the new role be a superuser? (y/n) y CREATE USER
如此一來我們就可以使用 alex 這個使用者從遠端登入了。
19.7 PostgreSQL 管理指令
PostgreSQL 和 MySQL 在指令的應用上有所不同，它將許多管理資料庫的指令獨立成一個個的執行檔，其中有些指令是使用 psql 為基礎所寫成的 scripts。例如新增、刪除資料庫或使用者等指令，都可以直接在命令列執行。以下為常用的指令列表：
| 指令 | 用途 |
| createdb | 建立一個新的資料庫。 |
| dropdb | 刪除資料庫。 |
| createuser | 建立資料庫使用者。 |
| dropuser | 刪除資料庫使用者。 |
| pg_dump | 備份一個資料庫。 |
| pg_dumpall | 備份所有資料庫。 |
| psql | 互動式的 SQL 指令工具。 |
19.7.1 建立及刪除使用者
因為 PostgreSQL 安裝完畢時只有一個使用者 pgsql，如果您要使用其它使用者登入，您必須先以 pgsql 這個使用者來新增其它使用者帳號。首先，我們先將身份切換成 pgsql：
# su -l pgsql
我們使用 su 加上參數 -l 表示模擬使用者真正 login 的情形，也就是會將工作目錄切換到 /usr/local/pgsql，並載入該目錄中的 .cshrc 等檔案。接著我們就可以使用下列指令來建立一個新的使用者了，假設我們要為 root 建立一個帳號：
% createuser root Shall the new role be a superuser? (y/n) y CREATE USER
如此一來，root 也具有存取資料庫的權限了。但因為 PostgreSQL 內定沒有密碼的使用者不可以使用遠端登入，如果您希望所新增的使用者可以使用遠端登入，您必須在 createuser 指令加上參數 -P 以輸入密碼，請注意 P 是大寫喔。如果你要從資料庫中刪除一個使用者帳號，只要使用 dropuser 這個指令即可：
% dropuser root
值得注意的是，您無法使用刪除你正在使用中的帳號，例如以 root 身份來刪除 root 是不被允許的。
如果您要修改某個使用者的密碼，可以使用下列指令：
# su -l pgsql % psql template1 Welcome to psql 8.0.3, 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 template1=# ALTER USER pgsql WITH PASSWORD 'mypass'; ALTER USER template1=# quit
我們先切換身份為 psql 以確保具有權限修改使用者密碼，接著我們使用 psql 進入預設的資料庫，並使用 ALTER USER 來修改使用者 pgsql 的密碼，將密碼設為 mypass。最後使用 quit 離開資料庫。
19.7.2 建立及刪除資料庫
在使用資料庫之前，我們必須先建立一個資料庫。假設我們要建立的資料庫名稱為 MYDB，您可以使用下列指令加以建立：
% createdb MYDB
在 UNIX 的世界中，大小寫是有分別的，在 PostgreSQL 中也是一樣。因此，不論是在建立資料庫，資料表或其欄位時，都要注意大小寫。建議您除了資料庫名稱外，最好全部使用小寫。
同樣的，如果你要刪除一個資料庫，只要使用 dropdb 這個指令：
% dropdb MYDB
19.7.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=#
在這裡，我們可以使用 19.3 所列出的一些標準的 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 列出該資料庫的所有資料表。
如果您覺得這種命令列的介面不好使用，我們在下一節將會介紹如何在 MS Windows 使用圖形化介面的管理工具。
19.7.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
19.8 PostgreSQL 圖形化管理工具介紹
許多人可能不太習慣使用命令列來管理資料庫，還好 PostgreSQL 提供了許多圖形介面的管理工具。您可以在 MS Windows 執行的 pgAdmin。由於這些圖形介面操作上比較容易，只要您多試幾次，就可以熟悉它們的使用，因此，我們不會深入介紹每個功能的用法。
對於初學者而言，使用 pgAdmin 會比在命令列中輸入來得容易。您可以自 http://www.pgadmin.org/ 下載最新版本的 pgAdmin。
安裝完成後，我們打開 pgAdmin，按了左上角的圖示後即出現連線設定的表單。請輸入您資料庫伺服器的位置及帳號密碼，如圖 19-9 所示：
圖 19-9
請注意，您必須先將 PostgreSQL 的 TCP/IP 連線打開，而且在 pg_hba.conf 中必須允許使用 pgAdmin 的這台主機登入。您可以參考 19.6 中的說明來設定 pg_hba.conf。輸入連線資料後，即可開始使用。以建立一個新的資料庫為例，我們先在左邊的視窗中，對著資料庫按右鍵，再選取 [新物件]->[新建資料庫] 如圖 19-10 所示：
圖 19-10
接著我們可以輸入資料庫名稱、編碼方式等，如圖 19-11：
圖 19-11
我們輸入了資料庫名稱為 test，並指定使用 UNICODE 的編碼方式。接下來您可以看到在管理視窗中多了一個資料庫「test」，我們可以在此資料庫中再建立資料表。請先點選資料庫「test」，再選擇「模式」->「public」->「資料表」，並對著資料表按右鍵選擇「新建資料表」。
圖 19-12
接下來，您就可以輸入資料表名稱，再選取「資料行」以設定本資料表的欄位資料。
圖 19-13
在 pgAdmin 的主畫面中，最上方有幾個功能鍵，可以讓我們手動輸入 SQL 指令或是查看資料表內容，建議您可以每一個功能都試試看，以熟悉 pgAdmin 的使用介面。基本上 pgAdmin 十分容易上手，而且也功能十分齊全。
|
