第十六章 數(shù)據(jù)庫(kù)系統(tǒng)
16.1 概論
電子商務(wù)的興起讓數(shù)據(jù)庫(kù)的應(yīng)用更受到大家的矚目。在信息科學(xué)的應(yīng)用上，數(shù)據(jù)庫(kù)可以說(shuō)是最歷久彌堅(jiān)的領(lǐng)域。近來(lái)，數(shù)據(jù)產(chǎn)生和數(shù)據(jù)收集方面的技術(shù)有非常快速的進(jìn)展。許多商業(yè)產(chǎn)品廣泛使用了條形碼、許多企業(yè)和政府的交易皆已計(jì)算機(jī)化，這使得計(jì)算機(jī)成為數(shù)據(jù)收集的主要工具。同時(shí)，數(shù)以百萬(wàn)計(jì)的數(shù)據(jù)庫(kù)正被使用在企業(yè)管理、政府管理、科學(xué)和工程的數(shù)據(jù)管理和許多其它的應(yīng)用上。
我們可以安裝一套數(shù)據(jù)庫(kù)系統(tǒng)，并經(jīng)由一個(gè)接口自行開(kāi)發(fā)程序來(lái)使用它。數(shù)據(jù)庫(kù)的好處有很多，相信對(duì)數(shù)據(jù)庫(kù)稍有涉入的人都知道，例如數(shù)據(jù)存取快速、不重復(fù)、權(quán)限控制、數(shù)據(jù)獨(dú)立性等等。以寫一個(gè)簡(jiǎn)單的留言版程序而言，傳統(tǒng)上使用檔案做為留言的記錄，若要?jiǎng)h除一筆數(shù)據(jù)，必須對(duì)整個(gè)檔案一行一行的比對(duì)；但數(shù)據(jù)庫(kù)只需指定該留言的編號(hào)即可。不過(guò)，如果把數(shù)據(jù)庫(kù)系統(tǒng)局限于留言版也太大才小用了。
我們將介紹在 FreeBSD 上使用數(shù)據(jù)庫(kù)，因?yàn)槟壳熬W(wǎng)頁(yè)數(shù)據(jù)庫(kù)使用情形十分風(fēng)行，尤其在網(wǎng)頁(yè)開(kāi)發(fā)上使用 MySQL+PHP 更是絕配，所以我以 MySQL 為主介紹它的用法。為什么選用 MySQL 而不選擇其它的數(shù)據(jù)庫(kù)，因?yàn)樗?jiǎn)單、免費(fèi)、功能強(qiáng)大、具有多平臺(tái)、多執(zhí)行緒、且參考文件多。你可以到 MySQL 的網(wǎng)站上參觀 http://www.mysql.com。除了 MySQL 外，還有另一套不錯(cuò)的數(shù)據(jù)庫(kù) Postgre SQL 也不錯(cuò)，和 MySQL 比較起來(lái)，其最大的優(yōu)點(diǎn)就是支持 transaction。
所謂的 transaction 就是將一連串的 SQL 指令做為一個(gè)執(zhí)行單位，當(dāng)其中一個(gè)指令失敗，在同一個(gè) transaction 所執(zhí)行過(guò)的命令都取消。Transaction 對(duì)于程序開(kāi)發(fā)的應(yīng)用十分重要，例如，當(dāng)我們要新增處理一筆訂單時(shí)，我們會(huì)先將訂單輸入數(shù)據(jù)庫(kù)中，再將金額輸入應(yīng)收帳款中。如果我們?cè)谛略鰬?yīng)收帳款時(shí)出現(xiàn)錯(cuò)誤，而訂單卻已輸入數(shù)據(jù)庫(kù)，是不正確的做法。有了支援 transaction 的數(shù)據(jù)庫(kù)，我們可以將訂單的輸入和應(yīng)收帳款的輸入做為一個(gè)執(zhí)行單位，如果其中一個(gè)執(zhí)行失敗，則取消先前先做的動(dòng)作，如此一來(lái)便可以確保數(shù)據(jù)的正確性。由于 transaction 十分重要，因此在做為商業(yè)上的應(yīng)用時(shí)，我會(huì)選擇 PostgreSQL。所以除了 MySQL 外，我們也會(huì)介紹如何在 FreeBSD 上安裝及使用 PostgreSQL。在這一章中，我們不再介紹如何安裝 MySQL，您可以參考第十章網(wǎng)頁(yè)服務(wù)器中關(guān)于安裝 MySQL 的說(shuō)明。
在使用數(shù)據(jù)庫(kù)之前，我們必須先了解一些簡(jiǎn)單而基本的數(shù)據(jù)庫(kù)理論?；旧蠑?shù)據(jù)庫(kù)的結(jié)構(gòu)有下列幾個(gè)特點(diǎn)：
例如我們有一個(gè)數(shù)據(jù)庫(kù)名稱是NCU，其中有多個(gè)數(shù)據(jù)表，其中一個(gè)資料表名為 student 內(nèi)容如下：
| STUDENT_ID | LAST_NAME | FIRST_NAME | DEPARTMENT |
| 1 | Chang | Jack | MIS |
| 2 | Wang | Alex | BA |
在數(shù)據(jù)表中有許多字段 (column)，每個(gè)字段都有一個(gè)名稱，也就是第一列 (row) 中的 STUDENT_ID、LAST_NAME、FIRST_NAME、DEPARTMENT。接著我們將數(shù)據(jù)存入，每一筆記錄我們都可以看成一列 (row)，每一個(gè)記錄都有一個(gè)「唯一的 ID (編號(hào))」。唯一的 ID 十分重要，它是我們?cè)诖嫒?shù)據(jù)庫(kù)時(shí)的依據(jù)。在新增資料時(shí)，以 MySQL 而言，我們可以自行指定 ID 或是由系統(tǒng)自行取得。
另一個(gè)觀念是關(guān)系型數(shù)據(jù)庫(kù)。關(guān)系型數(shù)據(jù)庫(kù)的意義就是每一個(gè)資料表間可以存在關(guān)系，例如我們?cè)?NCU 的數(shù)據(jù)庫(kù)中有另一個(gè)數(shù)據(jù)表名為 score，內(nèi)容如下：
| SCORE_ID | STUDENT_ID | CHINESE | ENGLISH |
| 1 | 2 | 99 | 90 |
| 2 | 1 | 89 | 87 |
score 數(shù)據(jù)表中存放學(xué)生的成績(jī)，我們不需在該數(shù)據(jù)表中存放學(xué)生的信息，只要在該數(shù)據(jù)表中存放一個(gè)字段名為 STUDENT_ID，經(jīng)由這一個(gè)唯一的 ID 我們可以去 studnet 的數(shù)據(jù)表中找到學(xué)生的數(shù)據(jù)。
有了這些觀念就足以讓我們開(kāi)發(fā)出許多數(shù)據(jù)庫(kù)的程序了。
16.2 SQL 語(yǔ)法介紹
SQL (Structured Query Language) 語(yǔ)法十分簡(jiǎn)單，它是關(guān)系型數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言，雖然在某些不同數(shù)據(jù)庫(kù)系統(tǒng)上有些許的差異，但基本上都遵循一定的標(biāo)準(zhǔn)。
我們可以在命令列下進(jìn)入 MySQL 來(lái)練習(xí) SQL 的語(yǔ)法：
# /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> |
下完指令后會(huì)要求輸入密碼， 之后你就可以直接在出現(xiàn)的命令列 mysql> 之后輸入 SQL 的語(yǔ)法了。
關(guān)于 MySQL 詳細(xì)的語(yǔ)法，你可以參考 MySQL 中文參考手冊(cè)，該文件可以在 http://www.freebsd.org.hk/html/mysqldoc/ 中找到。該文件中對(duì)于 MySQL 每個(gè)細(xì)節(jié)都有詳細(xì)的描述，例如字段的名稱限制、規(guī)則等，我們不會(huì)在這里提及。我們只介紹幾個(gè)簡(jiǎn)單而常用的指令。 我們以建立一個(gè)學(xué)生數(shù)據(jù)表來(lái)說(shuō)明這些語(yǔ)法。
16.2.1 CREATE
建立數(shù)據(jù)庫(kù)：CREATE DATABASE db_name
建立數(shù)據(jù)表：CREATE TABLE tbl_name [(create_definition,...)] [options]
我們先建立一個(gè)名為 NCU 的數(shù)據(jù)庫(kù)：
mysql> CREATE DATABASE NCU; |
請(qǐng)注意，每一個(gè)指令皆以 ";" 為結(jié)尾，如果沒(méi)有 ";" 就算換行也是代表同一條指令的延續(xù)。
我們可以使用下列指令 show 來(lái)列出系統(tǒng)中已存在的數(shù)據(jù)庫(kù)有哪些：
mysql> show databases; +-----------+ | Database | +-----------+ | mysql | | test | | NCU | +-----------+ 3 rows in set (0.01 sec) |
接著用 USE 這個(gè)指令來(lái)使用 NCU 數(shù)據(jù)庫(kù)：
mysql> USE NCU; |
接著建立一個(gè)放置學(xué)生數(shù)據(jù)的數(shù)據(jù)表，名為 STUDENT，內(nèi)容為編號(hào)(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) ); |
在上面的指令中，我們定義學(xué)生編號(hào)為十位數(shù)的整數(shù)(int)，內(nèi)定值是 0，不可以是空的 (NOT NULL)，數(shù)字自動(dòng)增加 (AUTO_INCREMENT)。姓名是最長(zhǎng)為五十個(gè)字節(jié)的字符串(VARCHAR)，科系為最長(zhǎng)十個(gè)字節(jié)的字符串。最后定義主要的 id 是 STUDENT_ID，也就是該數(shù)據(jù)表中的唯一 ID。
我們可以看到在建立數(shù)據(jù)表時(shí)，我們會(huì)順便劃分各個(gè)字段所要儲(chǔ)存的數(shù)據(jù)長(zhǎng)度及其格式，常用的字段格式請(qǐng)參考 MySQL 中文參考手冊(cè)。
如果要看現(xiàn)在使用的數(shù)據(jù)庫(kù)中有哪些數(shù)據(jù)表，一樣可以使用指令 show 來(lái)查看：
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 指令來(lái)修改數(shù)據(jù)表的格式。另如我們要新增一個(gè)姓別字段，內(nèi)容只可以是 "男" 或 "女"，我們可以使用下面的指令：
mysql> ALTER TABLE STUDENT
ADD SEX ENUM('男','女') DEFAULT '女';
|
我們?cè)黾恿艘粋€(gè)字段 SEX，使用 ENUM 的格式，指定內(nèi)容只能為 "男" 或 "女"，且默認(rèn)值是 "女"。
如果我們要將 SEX 字段改名為 DISTINCTION，并將格式改為 VARCHAR：
mysql> ALTER TABLE STUDENT CHANGE SEX DISTINCTION VARCHAR(4); |
如果我們只是要將 SEX 字段格式改為 VARCHAR，但不更改名稱，只要將上面的指令中 DISTINCTION 改成 SEX 即可。
如果我們要?jiǎng)h除整個(gè) DISTINCTION 字段及該字段的數(shù)據(jù)：
mysql> ALTER TABLE STUDENT DROP DISTINCTION; |
16.2.3 DROP
刪除數(shù)據(jù)庫(kù)：DROP DATABASE db_name
刪除數(shù)據(jù)表：DROP TABLE table_bame
我們可以使用 DROP 指令來(lái)刪除不要的資料。例如我們要?jiǎng)h除 STUDENT 這一個(gè)資料表的話，可以使用下列指令：
mysql> DROP TABLE STUDENT; |
16.2.4 INSERT
使用 INSERT 指令可以讓我們一筆一筆增加數(shù)據(jù)。
| STUDENT_ID | NAME | DEPARTMENT |
| 1 | Jack | MIS |
假設(shè)我們的數(shù)據(jù)表中的字段如上表，我們要新增一筆數(shù)據(jù)，姓名是 JACK、部門是 MIS：
mysql> INSERT INTO STUDENT (NAME, DEPARTMENT)
VALUES ('JACK', 'MIS');
|
由于我們?cè)谥付?STUDENT_ID 的格式時(shí)，加了參數(shù) AUTO_INCREMENT，所以我們不需指定值，mysql 會(huì)自動(dòng)幫我們依序指定。
16.2.5 SELECT
我們可以使用 SELECT 來(lái)看數(shù)據(jù)表中的數(shù)據(jù)，還可以依自己給定的條件來(lái)過(guò)濾數(shù)據(jù)。
假設(shè)我們要看 STUDENT 數(shù)據(jù)表中的所有數(shù)據(jù)的話，可以使用下列指令：
mysql> SELECT * FROM STUDENT; |
假設(shè)我們只要看 NAME 及 DEPARTMENT 字段的話，我們可以使用下列指令：
mysql> SELECT NAME, DEPARTMENT FROM STUDENT; |
假設(shè)我們只要看 NAME 字段，而且所屬部門為 MIS 的人：
mysql> SELECT NAME FROM STUDENT WHERE DEPARTMENT='MIS'; |
假設(shè)我們要看 MIS 部門中的人所有字段，而且輸出結(jié)果時(shí)要依 STUDENT_ID 來(lái)排序：
mysql> SELECT * FROM STUDENT WHERE DEPARTMENT='MIS' ORDER BY STUDENT_ID DESC; |
最后的 DESC 表示遞減 (descending)，由大到小排序。也可以使用 ASC 來(lái)表示遞增 (ascending)。
除了這些之外，在 MySQL 中還有一些可以使用的函式，例如我們可以使用 count() 這個(gè)函式來(lái)計(jì)算出有多少筆記錄：
mysql> SELECT count(*) FROM STUDENT WHERE DEPARTMENT='MIS'; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) |
上述結(jié)果表示部門為 MIS 的記錄有五筆。
會(huì)了這些基本的 INSERT 指定就夠我們做一般的應(yīng)用了。
16.2.6 UPDATE
我們可以使用 UPDATE 指令來(lái)更新記錄。例如我們要將所有記錄的部門數(shù)據(jù)為 MIS 者都改成 CSIE，可以使用下列指令：
mysql> UPDATE STUDENT SET DEPARTMENT='CSIE' WHERE DEPARTMENT='MIS'; |
16.2.7 DELETE
DELETE 指令可以讓我們刪除一筆或多筆數(shù)據(jù)。例如我們要?jiǎng)h除 STUDENT 數(shù)據(jù)表中姓名為 JACK 的記錄：
mysql> DELETE FROM STUDENT WHRE NAME='JACK'; |
如果我們要?jiǎng)h除姓名為 JACK 且部門為 MIS 的數(shù)據(jù)：
mysql> DELETE FROM STUDENT WHERE NAME='JACK' AND DEPARTMENT='MIS'; |
16.2.8 制成 script 檔
我們可以將要執(zhí)行的指定制成檔案，以利管理。例如我們寫了一個(gè)程序，需要先在數(shù)據(jù)庫(kù)中建立一些數(shù)據(jù)，我們可以將對(duì)數(shù)據(jù)庫(kù)的規(guī)劃做成一個(gè)檔案來(lái)管理。這樣可以使用們要安裝程序時(shí)更快速方便。
假設(shè)我們要建立一個(gè)數(shù)據(jù)庫(kù) NCU，該數(shù)據(jù)庫(kù)中有一個(gè)數(shù)據(jù)表 STUDENT，數(shù)據(jù)表中要先建有以下記錄：
| STUDENT_ID | NAME | DEPARTMENT |
| 1 | Jack | MIS |
| 2 | Mary | CSIE |
我們先建立一個(gè)文件名為 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');
|
接著使用下列指令來(lái)快速建立數(shù)據(jù)庫(kù)：
# /usr/local/mysql/bin/mysql -u root -p < ncu.sql
輸入使用者 root 的密碼后就完成建立了。
如果我們?cè)跀?shù)據(jù)庫(kù)中早就有一個(gè)數(shù)據(jù)庫(kù)名為 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');
|
之后再以下列指令來(lái)在 NCU 數(shù)據(jù)庫(kù)中建立數(shù)據(jù)表：
# /usr/local/mysql/bin/mysql -u root -p NCU <ncu.sql
在網(wǎng)絡(luò)上有許多 PHP 程序可以下載，下載后要安裝數(shù)據(jù)庫(kù)時(shí)，大多是以這種方式來(lái)使用。
16.3 MySQL 管理
16.3.1 維護(hù)密碼安全
當(dāng)我們要使用 MySQL 時(shí)，必須輸入密碼。輸入密碼的方式有很多種，第一種也是最不安全的一個(gè)方式是直接在命令列打指令時(shí)就輸入：
# /usr/local/mysql/bin/mysql -u root -pmypwd
上面這種方法會(huì)讓別的使用者使用 ps 指令就可以看到你在執(zhí)行的指定及密碼。因此絕對(duì)不要使用這種方法，請(qǐng)改用下列方式輸入：
# /usr/local/myqsl/bin/mysql -u root -p
接著會(huì)要求你輸入密碼時(shí)再輸入即可。
另一個(gè)方式是在你的家目錄下建立一個(gè)存放密碼的檔案，文件名為 .my.cnf，當(dāng) mysql 需要使用密碼時(shí)會(huì)自動(dòng)去讀取。該檔的內(nèi)容如下：
[client] password=your_passowrd |
接著要把 .my.cnf 的權(quán)限改成只有檔案擁有者才可以讀寫：
# chmod 600 ~/.my.cnf
16.3.2 備份數(shù)據(jù)庫(kù)
數(shù)據(jù)庫(kù)的數(shù)據(jù)要定時(shí)備份，這樣才不會(huì)在失手時(shí)或系統(tǒng)有問(wèn)題時(shí)產(chǎn)生困擾。在 MySQL 中提供一個(gè)備份程序 msqyldump。
假設(shè)我們有一個(gè)數(shù)據(jù)庫(kù)名為 WWW，我們可以使用下列指令來(lái)備份整個(gè)數(shù)據(jù)庫(kù)：
# /usr/local/mysql/bin/mysqldump -u root -p WWW > www.sql
這樣就可以把數(shù)據(jù)庫(kù)的數(shù)據(jù)存在 www.sql 這個(gè)檔案中了。日后要回復(fù)時(shí)只要使用下列指定就可以把資料存回：
# /usr/local/mysql/bin/mysql -u root -p WWW < www.sql
我們要注意的是備份出來(lái)的檔案應(yīng)該要放在不同的計(jì)算機(jī)中，而且要注意權(quán)限的控制。由于該文件是文字文件，任何人都可以讀，所以要特別注意。
我們可以利用 crontab 這個(gè)指令來(lái)定時(shí)備份數(shù)據(jù)庫(kù)。我們先建立一個(gè) shell script 檔，名為 backupsql.sh，內(nèi)容如下：
/usr/local/mysql/bin/mysqldump -uroot WWW>/home/www.sql chmod 600 /home/www.sql |
接著將該檔權(quán)限改成只有擁有人可以讀、寫、執(zhí)行，其它人都不行：
# chmod 700 backupsql.sh
然后建立 ~/.my.cnf 檔案內(nèi)容如下：
[client] password=your_passowrd |
接著要把 .my.cnf 的權(quán)限改成只有檔案擁有者才可以讀寫：
# chmod 600 ~/.my.cnf
接著要讓它能定時(shí)執(zhí)行，命令列打 crontab -e 來(lái)進(jìn)入文字編輯，加入下列內(nèi)容：
#每天 3:05 備份網(wǎng)頁(yè)數(shù)據(jù)庫(kù) 5 3 * * * /root/backupsql.sh |
16.3.3 使用者管理
MySQL 對(duì)于使用者的權(quán)限控制存放在名為 mysql 數(shù)據(jù)庫(kù)中的 user 數(shù)據(jù)表內(nèi)。user 數(shù)據(jù)表內(nèi)有下列幾個(gè)字段：
| 字段名稱 | 權(quán)限 | 說(shuō)明 |
| Host | 使用者來(lái)源主機(jī)。 | |
| User | 使用者名稱。 | |
| Password | 密碼。 | |
| Select_priv | select | 對(duì) table 做 select 動(dòng)作。 |
| Insert_priv | insert | 對(duì) table 做 insert 動(dòng)作。 |
| Update_priv | update | 對(duì) table 做 update 動(dòng)作。 |
| Delete_priv | delete | 對(duì) table 做 delete 動(dòng)作。 |
| Index_priv | index | 對(duì) table 做 index 動(dòng)作。 |
| Alter_priv | alter | 對(duì) table 做 alter 動(dòng)作。 |
| Create_priv | create | 對(duì) table 、indexs 或 database 做 create 動(dòng)作。 |
| Drop_priv | drop | 對(duì) table 或 database 做 drop 動(dòng)作。 |
| Grant_priv | grant | 對(duì) table 或 database 做 grant 動(dòng)作。 |
| References_priv | references | 對(duì) table 或 database 做 references 動(dòng)作。 |
| Reload_priv | reload | 系統(tǒng)管理，權(quán)限擁有者可以執(zhí)行 reload, refresh, flush-privileges, flush-hosts, flush-logs, flush-tables。 |
| Shutdown_priv | shutdown | 系統(tǒng)管理，權(quán)限擁有者可以執(zhí)行 shutdown。 |
| Process_priv | process | 系統(tǒng)管理，權(quán)限擁有者可以執(zhí)行 processlist, kill。 |
| File_priv | file | 對(duì)系統(tǒng)上的檔案有存取權(quán)限。 |
我們?cè)谛略鲆粋€(gè) MySQL 使用者時(shí)，有二種方式。比較差的方式是使用 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)機(jī)的主機(jī)，如果是本機(jī)則輸入 localhost；而 user 是使用者名稱；密碼是該使用者的密碼，要使用 password() 函數(shù)來(lái)將它加密；接下來(lái)的一堆 'Y' 就是對(duì)上表中的權(quán)限是否要開(kāi)放，如果不開(kāi)放則填 'N'。
第二種方式是使用 GRANT 指令來(lái)新增使用者，GRANT 在設(shè)定使用者權(quán)限時(shí)，如果使用者存在則更新其權(quán)限，如果不存在則新增該使用者。
用法：
GRANT 權(quán)限 ON 數(shù)據(jù)庫(kù)(或表) TO user@host IDENTIFIED BY '密碼';
范例一：
新增一個(gè)本機(jī)的使用者 admin，并開(kāi)放所有權(quán)限，密碼為 mypwd：
mysql> GRANT ALL PRIVILEGES ON *.* TO admin@localhost IDENTIFIED BY 'mypwd'; Query OK, 0 row affected (0.00 sec) |
范例二：
新增一個(gè)來(lái)自 www.mydomain.com 的使用者 www，并設(shè)定只能對(duì) www 數(shù)據(jù)庫(kù)中所有數(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) |
如果要?jiǎng)h除使用者上述新增的使用者 www，可以使用下列指令：
mysql> DELETE FROM user WHERE user='www' and host='www.mydomain.com'; Query OK, 1 rows affected (0.01 sec) |
在新增或刪除使用者后，離開(kāi) MySQL 之前都必須指行下列指令來(lái)讓它生效：
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) |
16.3.4 如何更改使用者密碼
我們可以使用下列指令來(lái)更改自己的密碼：
# /usr/local/mysql/bin/mysqladmin -u root -p password newpwd
上面指令中的使用者是 localhost 的 root ，新的密碼是 newpwd。在輸入指令后，會(huì)先詢問(wèn)你舊的密碼。
我們也可以使用具有管理使用者權(quán)限的 mysql 使用者登入 MySQL 后，使用 UPDATE 指令來(lái)更改密碼：
# /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='主機(jī)';
|
16.4 PostgreSQL 安裝設(shè)定
您可以選擇使用 ports 或 package 來(lái)安裝 PostgreSQL，我們以 ports 安裝為例：
# cd /usr/ports/databases/postgresql7 # make install
執(zhí)行了 make install 之后，您將看到一個(gè)提示訊息，要求你先行備份原本的數(shù)據(jù)庫(kù)。如果您是第一次安裝 PostgreSQL，可以直接略過(guò)。
安裝完成后，我們就可以開(kāi)始做數(shù)據(jù)庫(kù)的初始化了。我們使用下列指令來(lái)初始化數(shù)據(jù)庫(kù)：
# su -l pgsql -c initdb
這個(gè)指令的意思是以使用者 pgsql 的身份執(zhí)行 initdb 并設(shè)定預(yù)設(shè)的編碼方式為 SQL_ASCII。PostgreSQL 安裝時(shí)會(huì)自動(dòng)建立一個(gè)使用者及群組 pgsql，這是 PostgreSQL 預(yù)設(shè)最高使用者的賬號(hào)，您可以使用 vipw 來(lái)修改該使用者的數(shù)據(jù)。由于 pgsql 預(yù)設(shè)使用的 shell 是 sh，筆者習(xí)慣使用 tcsh，所以我將該使用者的數(shù)據(jù)修改如下：
pgsql:*:70:70::0:0:PostgreSQL Daemon:/usr/local/pgsql:/bin/tcsh |
初始化數(shù)據(jù)庫(kù)后還有一些后續(xù)的設(shè)定。一開(kāi)始 PostgreSQL 只允許讓 pgsql 這個(gè)使用者經(jīng)由本機(jī)聯(lián)機(jī)存取數(shù)據(jù)庫(kù)，如果您希望其它使用者可以經(jīng)由其它機(jī)器聯(lián)機(jī)，您必須先修改 ~pgsql/data/postgresql.conf 這個(gè)檔案。找出 tcpip_socket 的部份，并修改如下：
tcpip_socket = true |
postgresql.conf 這個(gè)檔案記錄著 PostgreSQL 的其本設(shè)定，其中使用 "#" 為首的是批注。
接著我們要設(shè)定從別的機(jī)器聯(lián)機(jī)所使用的認(rèn)證方式，請(qǐng)編輯 ~pgsql/data/pg_hba.conf，在文件最下方加入下列設(shè)定：
# 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 |
這里的設(shè)定除了第一行是批注外，第二、三行表示信任來(lái)自本機(jī)的聯(lián)機(jī)，只要使用者存在于數(shù)據(jù)庫(kù)中就不需要密碼，這二行預(yù)設(shè)就存在于 pg_hba.conf 中。最后一行表示網(wǎng)域 192.168.0.1~192.168.0.255 的聯(lián)機(jī)都要使用 md5 驗(yàn)證密碼。
在我們新增其它使用者之前，必須先啟動(dòng) PostgreSQL ，以下為啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)的指令：
# /usr/local/etc/rc.d/010.pgsql.sh start
因?yàn)?010.pgsql.sh 這支 script 放在 /usr/local/etc/rc.d ，所以在一開(kāi)機(jī)時(shí)，系統(tǒng)就會(huì)自動(dòng)執(zhí)行它來(lái)啟動(dòng) PostgreSQL，如果您要停止 PostgreSQL，只要執(zhí)行下列指令：
# /usr/local/etc/rc.d/010.pgsql.sh stop
接著我們就可以增加一個(gè)可以使用遠(yuǎn)程聯(lián)機(jī)的使用者：
# 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
如此一來(lái)我們就可以使用 alex 這個(gè)使用者從遠(yuǎn)程登入了。
16.5 PostgreSQL 管理指令
PostgreSQL 和 MySQL 在指令的應(yīng)用上有所不同，它將許多管理數(shù)據(jù)庫(kù)的指令獨(dú)立成一個(gè)個(gè)的執(zhí)行文件，其中有些指令是使用 psql 為基礎(chǔ)所寫成的 scripts。例如新增、刪除數(shù)據(jù)庫(kù)或使用者等指令，都可以直接在命令列執(zhí)行。以下為常用的指令列表：
| 指令 | 用途 |
| createdb | 建立一個(gè)新的數(shù)據(jù)庫(kù)。 |
| dropdb | 刪除數(shù)據(jù)庫(kù)。 |
| createuser | 建立數(shù)據(jù)庫(kù)使用者。 |
| dropuser | 刪除數(shù)據(jù)庫(kù)使用者。 |
| pg_dump | 備份一個(gè)數(shù)據(jù)庫(kù)。 |
| pg_dumpall | 備份所有數(shù)據(jù)庫(kù)。 |
| psql | 交互式的 SQL 指令工具。 |
16.5.1 建立及刪除使用者
因?yàn)?PostgreSQL 安裝完畢時(shí)只有一個(gè)使用者 pgsql，如果您要使用其它使用者登入，您必須先以 pgsql 這個(gè)使用者來(lái)新增其它使用者賬號(hào)。首先，我們先將身份切換成 pgsql：
# su -l pgsql
我們使用 su 加上參數(shù) -l 表示模擬使用者真正 login 的情形，也就是會(huì)將工作目錄切換到 /usr/local/pgsql，并加載該目錄中的 .cshrc 等檔案。接著我們就可以使用下列指令來(lái)建立一個(gè)新的使用者了，假設(shè)我們要為 root 建立一個(gè)賬號(hào)：
% 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
如此一來(lái)，root 也具有存取數(shù)據(jù)庫(kù)的權(quán)限了。但因?yàn)?PostgreSQL 內(nèi)定沒(méi)有密碼的使用者不可以使用遠(yuǎn)程登入，如果您希望所新增的使用者可以使用遠(yuǎn)程登入，您必須在 createuser 指令加上參數(shù) -P，請(qǐng)注意 P 是大寫喔。如果你要從數(shù)據(jù)庫(kù)中刪除一個(gè)使用者賬號(hào)，只要使用 dropuser 這個(gè)指令即可：
% dropuser root
值得注意的是，您無(wú)法使用刪除你正在使用中的賬號(hào)，例如以 root 身份來(lái)刪除 root 是不被允許的。
16.5.2 建立及刪除數(shù)據(jù)庫(kù)
在使用數(shù)據(jù)庫(kù)之前，我們必須先建立一個(gè)數(shù)據(jù)庫(kù)。假設(shè)我們要建立的數(shù)據(jù)庫(kù)名稱為 MYDB，您可以使用下列指令加以建立：
% createdb MYDB
在 UNIX 的世界中，大小寫是有分別的，在 PostgreSQL 中也是一樣。因此，不論是在建立數(shù)據(jù)庫(kù)，數(shù)據(jù)表或其字段時(shí)，都要注意大小寫。建議您除了數(shù)據(jù)庫(kù)名稱外，最好全部使用小寫。
同樣的，如果你要?jiǎng)h除一個(gè)數(shù)據(jù)庫(kù)，只要使用 dropdb 這個(gè)指令：
% dropdb MYDB
16.5.3 交互式 SQL 指令
PostgreSQL 的 client 端指令中，功能最強(qiáng)大的莫過(guò)于 psql 這個(gè)指令了。psql 可以除了讓我們進(jìn)入交互式的 SQL statement 環(huán)境外，也可以加上一些參數(shù)變成一個(gè)直接響應(yīng)的指令。例如，我們想要查看目前有哪些數(shù)據(jù)庫(kù)：
% psql -l List of databases Name | Owner | Encoding -----------+-------+----------- MYDB | root | SQL_ASCII template0 | pgsql | SQL_ASCII template1 | pgsql | SQL_ASCII (3 rows)
加上 -l 這個(gè)參數(shù)后，就可以列出所有數(shù)據(jù)庫(kù)名稱了。除了您所建立的數(shù)據(jù)庫(kù)外，還有二個(gè) templateX 的數(shù)據(jù)庫(kù)，該數(shù)據(jù)庫(kù)存放著 PostgreSQL 的設(shè)定，不可以刪除。接下來(lái)讓我們選定一個(gè)數(shù)據(jù)庫(kù)以進(jìn)入交互式的窗口：
% 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 所列出的一些標(biāo)準(zhǔn)的 SQL statement 來(lái)存取數(shù)據(jù)庫(kù)，例如 create、drop、delete、update、insert、alter 等。建議您進(jìn)一步至 PostgreSQL 網(wǎng)站參考其使用手冊(cè)，PostgreSQL 官方網(wǎng)站是 http://www.postgresql.org 。您也可以在下列網(wǎng)址中找到中文的使用手冊(cè)：http://www.freebsd.org.hk/html/pgsqldoc-7.0c/postgres.htm。
在交互式的接口中，您可以使用 \h 及 \? 來(lái)查詢可以使用的指令。其中 \h 為查詢 SQL statement ，而 \? 則是常詢 PostgreSQL 特有的反斜線指令，我們最常用的反斜線指令有 \q 離開(kāi)交互式接口，及 \d 列出該數(shù)據(jù)庫(kù)的所有數(shù)據(jù)表。
如果您覺(jué)得這種命令列的接口不好使用，我們?cè)谙乱还?jié)將會(huì)介紹如何在 FreeBSD 及 MS Windows 使用圖形化接口的管理工具。
16.5.4 數(shù)據(jù)庫(kù)備份及回復(fù)
定期備份數(shù)據(jù)庫(kù)是十分重要的一件事，我們一定要養(yǎng)成備份的習(xí)慣。在 PostgreSQL 中，備份十分容易，假設(shè)我們要備份的數(shù)據(jù)庫(kù)是 MYDB，您可以使用下列指令：
% pg_dump MYDB > MYDB.sql
如此一來(lái)，你就可以把 MYDB 這個(gè)數(shù)據(jù)庫(kù) dump 出來(lái)了。然而，數(shù)據(jù)庫(kù)的數(shù)據(jù)往往十分龐大，動(dòng)輒數(shù)十 MB 至數(shù)百 MB，為了節(jié)省空間，您可以在備份時(shí)順便壓縮數(shù)據(jù)庫(kù)。以上述指令而言，我們只要將輸出導(dǎo)向到 gzip 即可進(jìn)行同步壓縮：
% pg_dump MYDB | gzip > MYDB.sql.gz
我們一般從數(shù)據(jù)庫(kù) dump 出來(lái)的數(shù)據(jù)都是文字文件，所以使用 gzip壓縮可以得到很高的壓縮比。假設(shè)不壓縮所備份出來(lái)的檔案有五十 MB，使用 gzip壓縮后大約只剩六百多 KB。因此，我習(xí)慣都會(huì)加上 gzip壓縮。
pg_dump 這個(gè)指令只能用來(lái)備份單一的數(shù)據(jù)庫(kù)，如果您要將所有的數(shù)據(jù)庫(kù)中都備份起來(lái)，您可以使用 pg_dumpall 來(lái)備份：
% pg_dumpall |gzip > ALLDB.sql.gz
有了備份，自然也要回存。由于我們使用 pg_dump 所備份出來(lái)的數(shù)據(jù)庫(kù)實(shí)際上是將一堆數(shù)據(jù)以 SQL statement 的方式存起來(lái)，如果您將該備份的檔案以文書編輯器打開(kāi)，您可以看到它其實(shí)是先存放數(shù)據(jù)庫(kù)中所有數(shù)據(jù)表的信息，再將存放數(shù)據(jù)。所以我們只要將這些指令導(dǎo)向到 psql 來(lái)執(zhí)行即可。首先，請(qǐng)先建立要回存的數(shù)據(jù)庫(kù)名稱，假設(shè)我們要將 MYDB 所備份出來(lái)的數(shù)據(jù)存放在 NEWDB 這個(gè)數(shù)據(jù)庫(kù)中，我們要先建立一個(gè)名為 NEWDB 的數(shù)據(jù)庫(kù)：
% createdb NEWDB
接著再使用下列指令來(lái)將數(shù)據(jù)回存：
% cat MYDB.sql | psql NEWDB
如果您備份出來(lái)的數(shù)據(jù)有經(jīng)過(guò)壓縮，則需改以下列指令回存：
% gunzip -c MYDB.sql.gz | psql NEWDB
或是
% cat MYDB.sql.gz | gunzip | psql NEWDB
如果您要回存的檔案是經(jīng)由 pg_dumpall 所備份出來(lái)的數(shù)據(jù)，則必須使用 pgsql 這個(gè)使用者來(lái)執(zhí)行下列指令：
% gunzip -c ALLDB.sql.gz | psql -e template1
16.6 PostgreSQL 圖形化管理工具介紹
許多人可能不太習(xí)慣使用命令列來(lái)管理數(shù)據(jù)庫(kù)，還好 PostgreSQL 提供了許多圖形接口的管理工具。您可以在 MS Windows 執(zhí)行的 pgAdmin，另外也有 ODBC 接口可供使用。由于這些圖形接口操作上比較容易，只要您多試幾次，就可以熟悉它們的使用，因此，我們不會(huì)深入介紹每個(gè)功能的用法。
15.6.1 pgAdmin
如果您習(xí)慣使用 MS Windows，您可以使用 pgAdmin 在 MS Windows 上管理數(shù)據(jù)庫(kù)。對(duì)于初學(xué)者而言，使用 pgAdmin 會(huì)比在命令列中輸入來(lái)得容易。您可以自 http://pgadmin.postgresql.org 下載最新版本的 pgAdmin，或者您也可以在本書光盤二 /wintools 目錄中找到 pgadmin3-1_0_2.zip。您只要將它解壓縮，即可安裝。
接著我們打開(kāi) pgAdmin，按了左上角的圖示后即出現(xiàn)聯(lián)機(jī)設(shè)定的窗體。請(qǐng)輸入您數(shù)據(jù)庫(kù)服務(wù)器的位置及賬號(hào)密碼，如圖 16-1 所示：
圖 16-1
請(qǐng)注意，您必須先將 PostgreSQL 的 TCP/IP 聯(lián)機(jī)打開(kāi)，而且在 pg_hba.conf 中必須允許使用 pgAdmin 的這臺(tái)主機(jī)登入。您可以參考 16.4 中的說(shuō)明來(lái)設(shè)定 pg_hba.conf。輸入聯(lián)機(jī)數(shù)據(jù)后，即可開(kāi)始使用。以建立一個(gè)新的數(shù)據(jù)庫(kù)為例，我們先在左邊的窗口中，對(duì)著數(shù)據(jù)庫(kù)按右鍵，再選取 [新物件]->[新建數(shù)據(jù)庫(kù)] 如圖 16-2 所示：
圖 16-2
接著我們可以輸入數(shù)據(jù)庫(kù)名稱、編碼方式等，如圖 16-3：
圖 16-3
其它建立資料表等操作也都大同小異，您可以每一個(gè)功能都試試看。
16.6.2 ODBC
許多人習(xí)慣在 MS Windows 上做程序開(kāi)發(fā)，透過(guò) ODBC 存取數(shù)據(jù)庫(kù)。PostgreSQL 也有提供 ODBC 驅(qū)動(dòng)程序，你可以自 http://odbc.postgresql.org 下載，也可以在本書光盤二 wintools 目錄中取得 psqlodbc-07_02_0005.zip。解壓縮并安裝后，您就可以開(kāi)始做 ODBC 設(shè)定。
如果您使用的是 Windows NT、Windows 2000、或 Windows XP，請(qǐng)執(zhí)行 [控制臺(tái)] -> [系統(tǒng)管理工具] -> [資料來(lái)源]。如果你使用的是 Windows 98、Windows ME，請(qǐng)執(zhí)行 [控制臺(tái)] -> [資料來(lái)源]。
圖 16-4
我們接著點(diǎn)選 [檔案數(shù)據(jù)來(lái)源] -> [新增]，然后會(huì)出現(xiàn)以下畫面：
圖 16-5
我們選擇 PostgreSQL 為數(shù)據(jù)來(lái)源后，將出現(xiàn)圖 16-6 的畫面：
圖 16-6
我們?yōu)樗∫粋€(gè)名字并決定儲(chǔ)存地點(diǎn)后，按下一步即完成。完成后會(huì)出現(xiàn)圖 16-7 的畫面讓您輸入認(rèn)證的數(shù)據(jù)，全部輸入后即完成 PostgreSQL ODBC 的安裝。
圖 16-7
