第十九章 数据库系统
现在许多的商业应用、网页程序都必须使用数据库来存放数据。例如，网站上的留言版、讨论区、购物网站等，全部都是使用数据库来存放重要的数据。本章中，我们将介绍二种常见的数据库：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 等，其中 4.1.x 是目前较稳定的版本。
您可以自行到 http://www.mysql.com 取得最新版的 MySQL Source Package 来安装，不过安装上比较麻烦。所以我们使用 ports 来安装 MySQL：
# cd /usr/ports/databases/mysql41-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.sh 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: 4.1.13 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) 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) |
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) 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/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/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/postgresql80-server # make install clean
执行了 make install 之后，会出现一个进阶设定的窗口，我们使用预设的设定即可。接着，您将看到一个提示讯息，要求你先行备份原本的数据库。如果您是第一次安装 PostgreSQL，可以直接略过。
安装完成后，我们就可以开始做数据库的初始化了。我们使用下列指令来初始化数据库：
# su -l pgsql -c initdb
这个指令的意思是以使用者 pgsql 的身份执行 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，请修改 /etc/rc.conf 并加入下列这一行：
postgresql_enable="YES" |
在我们新增其它使用者之前，必须先启动 PostgreSQL ，以下为启动数据库服务的指令：
# /usr/local/etc/rc.d/010.pgsql.sh start
如果您没有在 rc.conf 中加入启动 PostgreSQL 的设定，则上述指令并不会启动 PostgreSQL。
因为 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 这个使用者从远程登入了。
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 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 是不被允许的。
如果您要修改某个使用者的密码，可以使用下列指令：
# 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 十分容易上手，而且也功能十分齐全。
|
