欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

第十六章 數(shù)據(jù)庫系統(tǒng)

16.1 概論

電子商務的興起讓數(shù)據(jù)庫的應用更受到大家的矚目。在信息科學的應用上,數(shù)據(jù)庫可以說是最歷久彌堅的領域。近來,數(shù)據(jù)產(chǎn)生和數(shù)據(jù)收集方面的技術有非??焖俚倪M展。許多商業(yè)產(chǎn)品廣泛使用了條形碼、許多企業(yè)和政府的交易皆已計算機化,這使得計算機成為數(shù)據(jù)收集的主要工具。同時,數(shù)以百萬計的數(shù)據(jù)庫正被使用在企業(yè)管理、政府管理、科學和工程的數(shù)據(jù)管理和許多其它的應用上。

我們可以安裝一套數(shù)據(jù)庫系統(tǒng),并經(jīng)由一個接口自行開發(fā)程序來使用它。數(shù)據(jù)庫的好處有很多,相信對數(shù)據(jù)庫稍有涉入的人都知道,例如數(shù)據(jù)存取快速、不重復、權限控制、數(shù)據(jù)獨立性等等。以寫一個簡單的留言版程序而言,傳統(tǒng)上使用檔案做為留言的記錄,若要刪除一筆數(shù)據(jù),必須對整個檔案一行一行的比對;但數(shù)據(jù)庫只需指定該留言的編號即可。不過,如果把數(shù)據(jù)庫系統(tǒng)局限于留言版也太大才小用了。

我們將介紹在 FreeBSD 上使用數(shù)據(jù)庫,因為目前網(wǎng)頁數(shù)據(jù)庫使用情形十分風行,尤其在網(wǎng)頁開發(fā)上使用 MySQL+PHP 更是絕配,所以我以 MySQL 為主介紹它的用法。為什么選用 MySQL 而不選擇其它的數(shù)據(jù)庫,因為它簡單、免費、功能強大、具有多平臺、多執(zhí)行緒、且參考文件多。你可以到 MySQL 的網(wǎng)站上參觀 http://www.mysql.com。除了 MySQL 外,還有另一套不錯的數(shù)據(jù)庫 Postgre SQL 也不錯,和 MySQL 比較起來,其最大的優(yōu)點就是支持 transaction。

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

在使用數(shù)據(jù)庫之前,我們必須先了解一些簡單而基本的數(shù)據(jù)庫理論?;旧蠑?shù)據(jù)庫的結(jié)構(gòu)有下列幾個特點:

例如我們有一個數(shù)據(jù)庫名稱是NCU,其中有多個數(shù)據(jù)表,其中一個資料表名為 student 內(nèi)容如下:

STUDENT_ID LAST_NAME FIRST_NAME DEPARTMENT
1 Chang Jack MIS
2 Wang Alex BA

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

另一個觀念是關系型數(shù)據(jù)庫。關系型數(shù)據(jù)庫的意義就是每一個資料表間可以存在關系,例如我們在 NCU 的數(shù)據(jù)庫中有另一個數(shù)據(jù)表名為 score,內(nèi)容如下:

SCORE_ID STUDENT_ID CHINESE ENGLISH
1 2 99 90
2 1 89 87

score 數(shù)據(jù)表中存放學生的成績,我們不需在該數(shù)據(jù)表中存放學生的信息,只要在該數(shù)據(jù)表中存放一個字段名為 STUDENT_ID,經(jīng)由這一個唯一的 ID 我們可以去 studnet 的數(shù)據(jù)表中找到學生的數(shù)據(jù)。

有了這些觀念就足以讓我們開發(fā)出許多數(shù)據(jù)庫的程序了。

16.2 SQL 語法介紹

SQL (Structured Query Language) 語法十分簡單,它是關系型數(shù)據(jù)庫的標準語言,雖然在某些不同數(shù)據(jù)庫系統(tǒng)上有些許的差異,但基本上都遵循一定的標準。

我們可以在命令列下進入 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>

下完指令后會要求輸入密碼, 之后你就可以直接在出現(xiàn)的命令列 mysql> 之后輸入 SQL 的語法了。

關于 MySQL 詳細的語法,你可以參考 MySQL 中文參考手冊,該文件可以在 http://www.freebsd.org.hk/html/mysqldoc/ 中找到。該文件中對于 MySQL 每個細節(jié)都有詳細的描述,例如字段的名稱限制、規(guī)則等,我們不會在這里提及。我們只介紹幾個簡單而常用的指令。 我們以建立一個學生數(shù)據(jù)表來說明這些語法。

16.2.1 CREATE

建立數(shù)據(jù)庫:CREATE DATABASE db_name
建立數(shù)據(jù)表:CREATE TABLE tbl_name [(create_definition,...)] [options]

我們先建立一個名為 NCU 的數(shù)據(jù)庫:

mysql> CREATE DATABASE NCU;

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

我們可以使用下列指令 show 來列出系統(tǒng)中已存在的數(shù)據(jù)庫有哪些:

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

接著用 USE 這個指令來使用 NCU 數(shù)據(jù)庫:

mysql> USE NCU;

接著建立一個放置學生數(shù)據(jù)的數(shù)據(jù)表,名為 STUDENT,內(nèi)容為編號(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) );

在上面的指令中,我們定義學生編號為十位數(shù)的整數(shù)(int),內(nèi)定值是 0,不可以是空的 (NOT NULL),數(shù)字自動增加 (AUTO_INCREMENT)。姓名是最長為五十個字節(jié)的字符串(VARCHAR),科系為最長十個字節(jié)的字符串。最后定義主要的 id 是 STUDENT_ID,也就是該數(shù)據(jù)表中的唯一 ID。

我們可以看到在建立數(shù)據(jù)表時,我們會順便劃分各個字段所要儲存的數(shù)據(jù)長度及其格式,常用的字段格式請參考 MySQL 中文參考手冊。

如果要看現(xiàn)在使用的數(shù)據(jù)庫中有哪些數(shù)據(jù)表,一樣可以使用指令 show 來查看:

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

16.2.2 ALTER

建立了數(shù)據(jù)表后,如果發(fā)現(xiàn)數(shù)據(jù)表的字段不符需求,我們不必將數(shù)據(jù)表刪除重建,可以使用 ALTER 指令來修改數(shù)據(jù)表的格式。另如我們要新增一個姓別字段,內(nèi)容只可以是 "男" 或 "女",我們可以使用下面的指令:

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

我們增加了一個字段 SEX,使用 ENUM 的格式,指定內(nèi)容只能為 "男" 或 "女",且默認值是 "女"。

如果我們要將 SEX 字段改名為 DISTINCTION,并將格式改為 VARCHAR:

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

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

如果我們要刪除整個 DISTINCTION 字段及該字段的數(shù)據(jù):

mysql> ALTER TABLE STUDENT DROP DISTINCTION;

16.2.3 DROP

刪除數(shù)據(jù)庫:DROP DATABASE db_name
刪除數(shù)據(jù)表:DROP TABLE table_bame

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

mysql> DROP TABLE STUDENT;

16.2.4 INSERT

使用 INSERT 指令可以讓我們一筆一筆增加數(shù)據(jù)。

STUDENT_ID NAME DEPARTMENT
1 Jack MIS

假設我們的數(shù)據(jù)表中的字段如上表,我們要新增一筆數(shù)據(jù),姓名是 JACK、部門是 MIS:

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

由于我們在指定 STUDENT_ID 的格式時,加了參數(shù) AUTO_INCREMENT,所以我們不需指定值,mysql 會自動幫我們依序指定。

16.2.5 SELECT

我們可以使用 SELECT 來看數(shù)據(jù)表中的數(shù)據(jù),還可以依自己給定的條件來過濾數(shù)據(jù)。

假設我們要看 STUDENT 數(shù)據(jù)表中的所有數(shù)據(jù)的話,可以使用下列指令:

mysql> SELECT * FROM STUDENT;

假設我們只要看 NAME 及 DEPARTMENT 字段的話,我們可以使用下列指令:

mysql> SELECT NAME, DEPARTMENT FROM STUDENT;

假設我們只要看 NAME 字段,而且所屬部門為 MIS 的人:

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

假設我們要看 MIS 部門中的人所有字段,而且輸出結(jié)果時要依 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)

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

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

16.2.6 UPDATE

我們可以使用 UPDATE 指令來更新記錄。例如我們要將所有記錄的部門數(shù)據(jù)為 MIS 者都改成 CSIE,可以使用下列指令:

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

16.2.7 DELETE

DELETE 指令可以讓我們刪除一筆或多筆數(shù)據(jù)。例如我們要刪除 STUDENT 數(shù)據(jù)表中姓名為 JACK 的記錄:

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

如果我們要刪除姓名為 JACK 且部門為 MIS 的數(shù)據(jù):

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

16.2.8 制成 script 檔

我們可以將要執(zhí)行的指定制成檔案,以利管理。例如我們寫了一個程序,需要先在數(shù)據(jù)庫中建立一些數(shù)據(jù),我們可以將對數(shù)據(jù)庫的規(guī)劃做成一個檔案來管理。這樣可以使用們要安裝程序時更快速方便。

假設我們要建立一個數(shù)據(jù)庫 NCU,該數(shù)據(jù)庫中有一個數(shù)據(jù)表 STUDENT,數(shù)據(jù)表中要先建有以下記錄:

STUDENT_ID NAME DEPARTMENT
1 Jack MIS
2 Mary CSIE

我們先建立一個文件名為 ncu.sql,內(nèi)容如下:

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');

接著使用下列指令來快速建立數(shù)據(jù)庫:

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

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

如果我們在數(shù)據(jù)庫中早就有一個數(shù)據(jù)庫名為 NCU,而我們要新增上述數(shù)據(jù)表及記錄,我們只要將原本 ncu.sql 的內(nèi)容最前面二行刪除,改成下列內(nèi)容:

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 數(shù)據(jù)庫中建立數(shù)據(jù)表:

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

在網(wǎng)絡上有許多 PHP 程序可以下載,下載后要安裝數(shù)據(jù)庫時,大多是以這種方式來使用。

16.3 MySQL 管理

16.3.1 維護密碼安全

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

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

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

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

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

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

[client]
password=your_passowrd

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

# chmod 600 ~/.my.cnf

16.3.2 備份數(shù)據(jù)庫

數(shù)據(jù)庫的數(shù)據(jù)要定時備份,這樣才不會在失手時或系統(tǒng)有問題時產(chǎn)生困擾。在 MySQL 中提供一個備份程序 msqyldump。

假設我們有一個數(shù)據(jù)庫名為 WWW,我們可以使用下列指令來備份整個數(shù)據(jù)庫:

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

這樣就可以把數(shù)據(jù)庫的數(shù)據(jù)存在 www.sql 這個檔案中了。日后要回復時只要使用下列指定就可以把資料存回:

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

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

我們可以利用 crontab 這個指令來定時備份數(shù)據(jù)庫。我們先建立一個 shell script 檔,名為 backupsql.sh,內(nèi)容如下:

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

接著將該檔權限改成只有擁有人可以讀、寫、執(zhí)行,其它人都不行:

# chmod 700 backupsql.sh

然后建立 ~/.my.cnf 檔案內(nèi)容如下:

[client]
password=your_passowrd

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

# chmod 600 ~/.my.cnf

接著要讓它能定時執(zhí)行,命令列打 crontab -e 來進入文字編輯,加入下列內(nèi)容:

#每天 3:05 備份網(wǎng)頁數(shù)據(jù)庫
5	3	*	*	*	/root/backupsql.sh

16.3.3 使用者管理

MySQL 對于使用者的權限控制存放在名為 mysql 數(shù)據(jù)庫中的 user 數(shù)據(jù)表內(nèi)。user 數(shù)據(jù)表內(nèi)有下列幾個字段:

字段名稱 權限 說明
Host   使用者來源主機。
User   使用者名稱。
Password   密碼。
Select_priv select 對 table 做 select 動作。
Insert_priv insert 對 table 做 insert 動作。
Update_priv update 對 table 做 update 動作。
Delete_priv delete 對 table 做 delete 動作。
Index_priv index 對 table 做 index 動作。
Alter_priv alter 對 table 做 alter 動作。
Create_priv create 對 table 、indexs 或 database 做 create 動作。
Drop_priv drop 對 table 或 database 做 drop 動作。
Grant_priv grant 對 table 或 database 做 grant 動作。
References_priv references 對 table 或 database 做 references 動作。
Reload_priv reload 系統(tǒng)管理,權限擁有者可以執(zhí)行 reload, refresh, flush-privileges, flush-hosts, flush-logs, flush-tables。
Shutdown_priv shutdown 系統(tǒng)管理,權限擁有者可以執(zhí)行 shutdown。
Process_priv process 系統(tǒng)管理,權限擁有者可以執(zhí)行 processlist, kill。
File_priv file 對系統(tǒng)上的檔案有存取權限。

我們在新增一個 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 就是要予許聯(lián)機的主機,如果是本機則輸入 localhost;而 user 是使用者名稱;密碼是該使用者的密碼,要使用 password() 函數(shù)來將它加密;接下來的一堆 'Y' 就是對上表中的權限是否要開放,如果不開放則填 'N'。

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

用法:

GRANT 權限 ON 數(shù)據(jù)庫(或表) 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 數(shù)據(jù)庫中所有數(shù)據(jù)表執(zhí)行 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

執(zhí)行了 make install 之后,您將看到一個提示訊息,要求你先行備份原本的數(shù)據(jù)庫。如果您是第一次安裝 PostgreSQL,可以直接略過。

安裝完成后,我們就可以開始做數(shù)據(jù)庫的初始化了。我們使用下列指令來初始化數(shù)據(jù)庫:

# su -l pgsql -c initdb

這個指令的意思是以使用者 pgsql 的身份執(zhí)行 initdb 并設定預設的編碼方式為 SQL_ASCII。PostgreSQL 安裝時會自動建立一個使用者及群組 pgsql,這是 PostgreSQL 預設最高使用者的賬號,您可以使用 vipw 來修改該使用者的數(shù)據(jù)。由于 pgsql 預設使用的 shell 是 sh,筆者習慣使用 tcsh,所以我將該使用者的數(shù)據(jù)修改如下:

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

初始化數(shù)據(jù)庫后還有一些后續(xù)的設定。一開始 PostgreSQL 只允許讓 pgsql 這個使用者經(jīng)由本機聯(lián)機存取數(shù)據(jù)庫,如果您希望其它使用者可以經(jīng)由其它機器聯(lián)機,您必須先修改 ~pgsql/data/postgresql.conf 這個檔案。找出 tcpip_socket 的部份,并修改如下:

tcpip_socket = true

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

接著我們要設定從別的機器聯(lián)機所使用的認證方式,請編輯 ~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

這里的設定除了第一行是批注外,第二、三行表示信任來自本機的聯(lián)機,只要使用者存在于數(shù)據(jù)庫中就不需要密碼,這二行預設就存在于 pg_hba.conf 中。最后一行表示網(wǎng)域 192.168.0.1~192.168.0.255 的聯(lián)機都要使用 md5 驗證密碼。

在我們新增其它使用者之前,必須先啟動 PostgreSQL ,以下為啟動數(shù)據(jù)庫服務的指令:

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

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

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

接著我們就可以增加一個可以使用遠程聯(lián)機的使用者:

# 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 在指令的應用上有所不同,它將許多管理數(shù)據(jù)庫的指令獨立成一個個的執(zhí)行文件,其中有些指令是使用 psql 為基礎所寫成的 scripts。例如新增、刪除數(shù)據(jù)庫或使用者等指令,都可以直接在命令列執(zhí)行。以下為常用的指令列表:

指令 用途
createdb 建立一個新的數(shù)據(jù)庫。
dropdb 刪除數(shù)據(jù)庫。
createuser 建立數(shù)據(jù)庫使用者。
dropuser 刪除數(shù)據(jù)庫使用者。
pg_dump 備份一個數(shù)據(jù)庫。
pg_dumpall 備份所有數(shù)據(jù)庫。
psql 交互式的 SQL 指令工具。

16.5.1 建立及刪除使用者

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

# su -l pgsql

我們使用 su 加上參數(shù) -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 也具有存取數(shù)據(jù)庫的權限了。但因為 PostgreSQL 內(nèi)定沒有密碼的使用者不可以使用遠程登入,如果您希望所新增的使用者可以使用遠程登入,您必須在 createuser 指令加上參數(shù) -P,請注意 P 是大寫喔。如果你要從數(shù)據(jù)庫中刪除一個使用者賬號,只要使用 dropuser 這個指令即可:

% dropuser root

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

16.5.2 建立及刪除數(shù)據(jù)庫

在使用數(shù)據(jù)庫之前,我們必須先建立一個數(shù)據(jù)庫。假設我們要建立的數(shù)據(jù)庫名稱為 MYDB,您可以使用下列指令加以建立:

% createdb MYDB

在 UNIX 的世界中,大小寫是有分別的,在 PostgreSQL 中也是一樣。因此,不論是在建立數(shù)據(jù)庫,數(shù)據(jù)表或其字段時,都要注意大小寫。建議您除了數(shù)據(jù)庫名稱外,最好全部使用小寫。

同樣的,如果你要刪除一個數(shù)據(jù)庫,只要使用 dropdb 這個指令:

% dropdb MYDB

16.5.3 交互式 SQL 指令

PostgreSQL 的 client 端指令中,功能最強大的莫過于 psql 這個指令了。psql 可以除了讓我們進入交互式的 SQL statement 環(huán)境外,也可以加上一些參數(shù)變成一個直接響應的指令。例如,我們想要查看目前有哪些數(shù)據(jù)庫:

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

加上 -l 這個參數(shù)后,就可以列出所有數(shù)據(jù)庫名稱了。除了您所建立的數(shù)據(jù)庫外,還有二個 templateX 的數(shù)據(jù)庫,該數(shù)據(jù)庫存放著 PostgreSQL 的設定,不可以刪除。接下來讓我們選定一個數(shù)據(jù)庫以進入交互式的窗口:

% 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 來存取數(shù)據(jù)庫,例如 create、drop、delete、update、insert、alter 等。建議您進一步至 PostgreSQL 網(wǎng)站參考其使用手冊,PostgreSQL 官方網(wǎng)站是 http://www.postgresql.org 。您也可以在下列網(wǎng)址中找到中文的使用手冊:http://www.freebsd.org.hk/html/pgsqldoc-7.0c/postgres.htm。

在交互式的接口中,您可以使用 \h 及 \? 來查詢可以使用的指令。其中 \h 為查詢 SQL statement ,而 \? 則是常詢 PostgreSQL 特有的反斜線指令,我們最常用的反斜線指令有 \q 離開交互式接口,及 \d 列出該數(shù)據(jù)庫的所有數(shù)據(jù)表。

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

16.5.4 數(shù)據(jù)庫備份及回復

定期備份數(shù)據(jù)庫是十分重要的一件事,我們一定要養(yǎng)成備份的習慣。在 PostgreSQL 中,備份十分容易,假設我們要備份的數(shù)據(jù)庫是 MYDB,您可以使用下列指令:

% pg_dump MYDB > MYDB.sql

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

% pg_dump MYDB | gzip > MYDB.sql.gz

我們一般從數(shù)據(jù)庫 dump 出來的數(shù)據(jù)都是文字文件,所以使用 gzip壓縮可以得到很高的壓縮比。假設不壓縮所備份出來的檔案有五十 MB,使用 gzip壓縮后大約只剩六百多 KB。因此,我習慣都會加上 gzip壓縮。

pg_dump 這個指令只能用來備份單一的數(shù)據(jù)庫,如果您要將所有的數(shù)據(jù)庫中都備份起來,您可以使用 pg_dumpall 來備份:

% pg_dumpall |gzip > ALLDB.sql.gz

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

% createdb NEWDB

接著再使用下列指令來將數(shù)據(jù)回存:

% cat MYDB.sql | psql NEWDB

如果您備份出來的數(shù)據(jù)有經(jīng)過壓縮,則需改以下列指令回存:

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

或是

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

如果您要回存的檔案是經(jīng)由 pg_dumpall 所備份出來的數(shù)據(jù),則必須使用 pgsql 這個使用者來執(zhí)行下列指令:

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

16.6 PostgreSQL 圖形化管理工具介紹

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

15.6.1 pgAdmin

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

接著我們打開 pgAdmin,按了左上角的圖示后即出現(xiàn)聯(lián)機設定的窗體。請輸入您數(shù)據(jù)庫服務器的位置及賬號密碼,如圖 16-1 所示:

圖 16-1

請注意,您必須先將 PostgreSQL 的 TCP/IP 聯(lián)機打開,而且在 pg_hba.conf 中必須允許使用 pgAdmin 的這臺主機登入。您可以參考 16.4 中的說明來設定 pg_hba.conf。輸入聯(lián)機數(shù)據(jù)后,即可開始使用。以建立一個新的數(shù)據(jù)庫為例,我們先在左邊的窗口中,對著數(shù)據(jù)庫按右鍵,再選取 [新物件]->[新建數(shù)據(jù)庫] 如圖 16-2 所示:

圖 16-2

接著我們可以輸入數(shù)據(jù)庫名稱、編碼方式等,如圖 16-3:

圖 16-3

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

16.6.2 ODBC

許多人習慣在 MS Windows 上做程序開發(fā),透過 ODBC 存取數(shù)據(jù)庫。PostgreSQL 也有提供 ODBC 驅(qū)動程序,你可以自 http://odbc.postgresql.org 下載,也可以在本書光盤二 wintools 目錄中取得 psqlodbc-07_02_0005.zip。解壓縮并安裝后,您就可以開始做 ODBC 設定。

如果您使用的是 Windows NT、Windows 2000、或 Windows XP,請執(zhí)行 [控制臺] -> [系統(tǒng)管理工具] -> [資料來源]。如果你使用的是 Windows 98、Windows ME,請執(zhí)行 [控制臺] -> [資料來源]。

圖 16-4

我們接著點選 [檔案數(shù)據(jù)來源] -> [新增],然后會出現(xiàn)以下畫面:

圖 16-5

我們選擇 PostgreSQL 為數(shù)據(jù)來源后,將出現(xiàn)圖 16-6 的畫面:

圖 16-6

我們?yōu)樗∫粋€名字并決定儲存地點后,按下一步即完成。完成后會出現(xiàn)圖 16-7 的畫面讓您輸入認證的數(shù)據(jù),全部輸入后即完成 PostgreSQL ODBC 的安裝。

圖 16-7