《PHP和MySQL Web开发》学习笔记（十一）

第9章 创建Web数据库

 

9.1 使用MySQL监视程序
本章中的MySQL例子和每个命令之间都用分号（;）分开，分号将告诉MySQL执行这个命令。如果漏掉了这些分号，MySQL将不会执行这些命令。
漏掉分号的结果是：可能在一个命令中间添加新行。我们使用这样的模式是为了使得本例更容易阅读。因为MySQL提供了一个持续符号，它是一个箭头，表示MySQL期待着更多的输入。每次按回车键时都会出现这些提示符，直到输入分号才没有提示符。
另外还需要注意的一点是SQL语句不区分大小写，但数据库和表的名称则区分大小写。

 

9.2 登录到MySQL
mysql -h hostname -u username -p
-h命令选项用于指定所希望连接的主机，即运行MySQL服务器的机器。如果正在该MySQL服务器所运行的机器上运行该命令，可以忽略该选项和hostname参数。
-u命令选项用于指定连接数据库时使用的用户名称。如果不指定，默认值是登录该操作系统时使用的用户名。
-p命令选项用来告诉服务器要使用一个密码来连接它。
如果以root用户的身份登录并且没有设置root密码，系统是不安全的。
我们不必在本行命令中包含密码，MySQL服务器会向你询问密码的。实际上，没有这样做更好。如果在命令行输入密码，它将以普通文本方式显示在屏幕上，很容易被其他用户发现。

 

9.3 创建数据库和用户
创建数据库
mysql> create database dbname;

 

9.4 设置用户与权限
一个MySQL系统可能有许多用户。为了安全起见，root用户通常只用作管理目的。对于每个需要使用该系统的用户，应该为他们创建一个帐号和密码。
为用户设置密码码不是必需的，但是我们强烈建议为所有创建的用户设定密码。要建立一个Web数据库，最好为每个网站应用程序建立一个用户。

 

9.5 MySQL权限系统的介绍
MySQL最好的特性之一是支持复杂的权限系统。权限是对特定对象执行特定操作的权力，它与特定用户相关。其概念非常类似于文件的权限。

 

9.5.1 最少权限原则
最少权限原则可以用来提高任何计算机系统的安全性。该原则包含如下内容：一个用户（或者一个进程）应该拥有能够执行分配给他的的任务的最低级别的权限。

 

9.5.2 创建用户：GRANT命令
GRANT和REVOKE命令分别用来授予和取消MySQL用户的权限，这些权限分4个级别：全局，数据库，表，列。
GRANT命令用来创建用户并赋予他们权限。

 

9.5.3 权限的类型和级别
MySQL中存在3个基本类型的权限：适用于赋予一般用户的权限、适用于赋予管理员的权限和几个特定的权限。任何用户都可以被赋予这3类权限，但是根据最少权限原则，最好严格限定只将管理员类型的权限赋予管理员。
我们应该只赋予用户访问他们必须使用的数据库和表的权限。而不应该将访问MySQL的权限赋予不是管理员的人。
常规用户的权限直接与特定的SQL命令类型以及用户是否被允许运行它们相关。

 

用户的权限：
SELECT：应用于表，列。允许用户从表中选择行（记录）。
INSERT：应用于表，列。允许用户在表中插入新行。
UPDATE：应用于表，列。允许用户修改现存表里行中的值。
DELETE：应用于表。允许用户删除现存表的行。
INDEX：应该于表。允许用户创建和拖动特定表索引。
ALTER：应用于表。允许用户改变现存表的结构，例如，可添加列、重命名列或表、修改列的数据类型。
CREATE：应用于数据库，表。允许用户创建新数据库或表。如果在GRANT中指定了一个数据库或表，他们只能够创建该数据库或表，即他们必须首先删除（drop）它。
DROP：应用于数据库，表。允许用户拖动（删除）数据库或表。
从系统的安全性方面考虑，适于常规用户的权限大多数是相对无害的。ALTER权限通过重命名表可能会影响权限系统，但是大多数用户需要它。安全性常常是可用性与保险性的折中。遇到ALTER的时候，应当作出自己的选择，但是通常还是会将这个权限授予用户。
还有两种权限，REFERENCES权限和EXECUTE权限，但是如今已经不再使用了，而GRANT权限是以WITH GRANT OPTION选项给出，而不是在权限列表里列出的。

 

管理员权限：
CREATE TEMPORARY TABLES：允许管理员在CREATE TABLE语句中使用TEMPORARY关键字。
FILE：允许将数据从文件读入表，或从表读入文件。
LOCK TABLES：允许使用LOCK TABLES语句。
PROCESS：允许管理员查看属于所有用户的服务器进程。
RELOAD：允许管理员重新载入授权表、清空授权、主机、日志和表格。
REPLICATION CLIENT：允许在复制主机（Master）和从机（Slave）上使用SHOW STATUS。
REPLICATION SLAVE：允许复制从服务器连接到主服务器。
SHOW DATABASES：允许使用SHOW DATABASES语句查看所有的数据库列表。没有这个权限，用户只能看到他们能够看到的数据库。
SHUTDOWN：允许管理员关闭MySQL服务器。
SUPER：允许管理员关闭属于任何用户的线程。
可以将这些权限授予非管理员用户，这样做的时候要非常小心。
FILE权限有些不同，它对普通用户非常有用，因为它可以将数据从文件载入数据库，从而可以节省许多时间，否则，每次将数据输入数据库都需要重新输入，这很浪费时间。然而，文件载入可以用来载入MySQL可识别的任何文件，包括属于其他用户的数据库和潜在的密码文件。授予该权限的时候需要小心，或者自己为用户载入数据。

 

特别的权限：
ALL：授予用户权限和管理员权限的所有权限，也可以将ALL写成ALL PRIVILEGES。
USAGE：不授予权限。这将创建一个用户并允许他登录，但是不允许进行任何操作。通常在以后会授予该用户更多的权限。

 

9.5.4 REVOKE命令
与GRANT相反的命令是REVOKE。它用来从一个用户收回权限。

 

9.5.5 使用GRANT和REVOKE的例子
要创建一个管理员，可以输入如下所示命令：
mysql> grant all on * to fred identified by `mnb123' with grant option;
以上命令授予了用户名为Fred、密码为mnb123的用户使用所有数据库的所有权限，并允许他向其他授予这些权限。
如果不希望用户在系统中存在，可以按如下方式撤销：
mysql> revoke all privileges, grant from fred;
现在，我们可以按如下方式创建一个没有任何权限的常规用户：
mysql> grant usage on books.* to sally identified by `magic123';
在与Sally交谈之后，我们对她需要进行的操作有了进一步了解，因此按如下方式可以授予她适当的权限：
mysql> grant select, insert, update, delete, index, alter, create, drop on books.* to sally;
请注意，要完成这些，并不需要指定Sally的密码。
如果我们认为Sally权限过高，可能会决定按如下方式减少一些权限：
mysql> revoke alter, create, drop on books.* from sally;
后来，当她不再需要使用数据库时，可以按如下方式撤销所有的权限：
mysql> revoke all on books.* from sally;

 

9.6 创建一个Web用户
要通过PHP连接到MySQL，需要为PHP脚本创建一个用户。在大多数情况下，脚本的操作只需要从表中选择（SELECT）、插入（INSERT）、删除（DELETE）和更新（UPDATE）查询。因此，可以按如下方式设定这些权限：
mysql> grant select, insert, delete, update on books.* to bookorama identified by `bookorama123';
很明显，为了安全起见，应该选择一个更好的密码。
可以输入quit命令注销root用户的登录。再以Web用户的身份登录，测试每件事情是否工作正常。如果所运行的GRANT语句已经执行了，但是尝试登录时，又被拒绝了，这通常是因为安装过程中还没有删除匿名帐户。删除匿名帐户后，应该能够以Web用户身份重新登录了。

 

9.7 使用正确的数据库
登录进入后，要做的第一件事是指定要使用的数据库。可以输入如下命令来完成：
mysql> use dbname;
或者，也可以通过在登录的时候指定数据库而避免使用命令。如下所示：
mysql -D dbname -h hostname -u username -p

 

9.8 创建数据库表
可以使用SQL命令CREATE TABLE来创建表。常见形式如下所示：
CREATE TABLE tablename(columns)
可以运行现在的SQL文件，如：
>mysql -h host -u bookorama -D books -p < bookorama.sql

 

9.8.1 理解其他关键字的意思
NOT NULL的意思是表中所有行的此属性必须有一个值。如果没有指定，该列可以为空（NULL）。
AUTO_INCREMENT是一个特殊的MySQL特性，可以在整数列中使用它。它的意思是在表中插入行的时候，如果将该字段设置为空，那么MySQL将自自动产生一个唯一的标识符值。该值比本列中现存的最大值更大。在每个表中只能有一个这样的值。指定AUTO_INCREMENT的列必须是索引的列。
列名称后面的PRIMARY KEY表示该列是表的主键。本列中的输入必须唯一。MySQL将自动索引该列。主键的自动索引功能将管理AUTO_INCREMENT所要求的索引列。
在列的名称后面指定PRIMARY KEY，这只用于单列主键。
整数类型后面的UNSIGNED意思是它只能是0或者一个正数。

 

9.8.2 理解列的类型
我们首先看看第一个表的例子：
create table customers
{   customerid int unsigned not null auto_increment primary key,
    name char(50) not null,
    address char(100) not null,
    city char(30) not null
};
在创建一个表的时候，需要确定列的数据类型。
对于customers表，我们在模式里指定它有4个列。第一列customerid，是主键，我们已经直接将它指定为主键。确定该列的数据类型是一个整数（数据类型int，而且这些ID应该是无符号的（unsigned）。我们还使用了auto_increment工具，这样MySQL就可以为我们管理这些，我们就不需要担心它。
其他列都是字符串类型数据。我们为这些列选择了char类型。同时，还将它们定义为固定长度的字段，该长度是在括号里指定的，例如姓名最多可以有50个字符宽度。
该数据类型将为姓名分配50个字符的存储空间，尽管姓名的长度通常不会长达30个字符。MySQL将用空格填充空余的部分。或者，我们还可以选择使用varchar类型，该数据类型可以根据需要分配存储空间（加一个字节）。这可能会有一些不足——因为，虽然varchar类型数据占用空间较少，但是char类型数据速度更快。
请注意，我们所声明的所有列都是NOT NULL（不为空），这是一个小小的优化措施，可以使得那些需要的地方速度更快。
让我们来看看orders表：
create table orders
{   orderid int unsigned not null auto_increment primary key,
    customerid int unsigned not null,
    amount float(6,2),
    date date not null
};
amout列被指定为浮点类型数据（float）。对于大多数浮点数据类型来说，可以指定显示宽度和小数点的位数。在这个例子中，订单总量将以美元计算，因此我们将允许合理大小的订单总金额（宽6位），小数位数到美分（2位）。
Date（日期）列数据类型为date。
在这个表中，我们将所有列指定为NOT NULL，这是为什么呢？因为当一个订单输入数据库的时候，将在orders表中创建一个记录，将所购物品添加到order-items表中，然后计算出总金额。在创建订单之前，我们可能还无法知道订单的总金额，所以必须允许amount列为NULL。
books表具有一些类似的特性：
create table books
{   isbn char(13) not null primary key,
    author char(50),
    title char(100),
    price float(4,2)
};
在这个例子中，我们不必生成主键，因为可以将ISBN作为主键，而ISBN在其他地方可以生成。我们将其他字段设置为NULL，因为书店可能在知道书本标题（title）、作者（author）或价格（price）之前就已经知道此书的ISBN了。
order_items表显示了如何创建多列主键：
create table order_items
{   orderid int unsigned not null,
    isbn char(13) not null,
    quantity tinyinc unsigned,

 

    primary key (orderid, isbn)
};
该表将图书的数量指定为TINYINT UNSIGNED数据类型，其取值范围为0~255之间的一个整数。
正如前面已经提到的，多列主键需要通过一个特定的主键子句指定。在这里，我们就要使用它。
最后，考虑book_reviews表：
create table book_reviews
{   isbn char(13) not null primary key,
    review text
};
它使用了text数据类型，该类型用于更长的文本。

 

9.8.3 用SHOW和DESCRIBE来查看数据库
输入如下命令，可以查看数据库中的所有表：
mysql> show tables;
也可以使用show命令来查看数据库列表：
mysql> show databases;
如果没有SHOW DATABASES权限，你将只看到权限范围内的数据库。
要查看某个特定表（例如，books表）的详细信息，可以使用DESCRIBE命令：
mysql> describe books;

 

9.8.4 创建索引
MySQL的新用户可能面临的一个常见问题是他们抱怨数据库的性能非常低下，因为他们曾经听说数据库速度很快。这个性能问题通常会在数据库上没有创建任何索引的情况下发生（创建没有主键或索引的表格是可能的）。
要开始创建索引，可以使用自动创建的索引。如果发现需要对一个不是主键的列运行许多查询，我们可能希望在该列上添加索引来改善性能。可以使用CREATE INDEX语句来实现。

 

9.8.5 表格类型的提示
MySQL提供了多种表格类型或存储引擎，其中包括事务安全的类型。目前，数据库中的所有表格都使用默认的存储引擎，MyISAM。

 

9.9 理解MySQL的标识符
在MySQL中，提供了5种类型的标识符——Database（数据库）、Table（表）、Column（列）、index（索引）和Alias（别名）。
MySQL中的数据库将被映射到具有某种文件结构的目录，而表则映射到文件。这可能对赋予它们的名字有直接影响。它也可以影响这些名字的大小写——如果操作系统区分目录与文件的大小写，那么数据库名称和表名称也会区分大小写（例如，在UNIX中），否则不区分（例如在Windows中）。列的名称和别名的名称不区分大小写，但是不能在同一个SQL语句中使用不同的大小写。
对于MySQL 3.23.6版本来说，甚至可以使用所有类型的单词和特殊字符作为标识符，唯一的限制是如果使用这样奇怪的标识符，必须用后引号将其括起来。例如：create database `create database`;
当然，对这些自由需要运用常识。只因为可以调用数据库“create database”，并不意味着应该这么做。同样的原则也适用于其他编程语言——使用有意义的标识符。

 

9.10 选择列的数据类型
MySQL中有3种基本的列数据类型：数字、日期和时间、字符串。而每个类型又包含了许多种类型。
这3种类型需要不同的存储空间。一般来说，选择列数据类型的时候，基本原则是选择可以满足数据的最小类型。
对许多数据类型来说，当创建该类型的时候，可以指定最大的显示长度。在后面的数据类型总结中，显示的就是M。如果该类型是可选的，它就显示在方括号内。M的最大值可以为255。

 

9.10.1 数字类型
数字类型分为整型和浮点型两类。对于浮点数来说，可以指定小数点后数字的位数。本书中即为D。可以指定D的最大值为30或M-2（也就是，最大显示长度减去2——一个小数点和一个此数字的整数部分。）
对于整型数据，也可以将它们指定为无符号型。
对所有数字类型，也可以指定ZEROFIL属性。当显示ZEROFILL字段中的值时，空余部分用前导0来补充。如果将一个字段指定为ZEROFILL，它将自动成为UNSIGNED数据类型。
整数类型有：TINYINT[(M)]，存储空间1字节，非常小的整数；BIT，TINYINT的同义词；BOOL，TINYINT的同义词；SMALLINT[(M)]，存储空间2字节，小型整数；MEDIUMINT[(M)]，存储空间3节字，中型整数；INT[(M)]，存储空间4字节，一般整数；INTEGER[(M)]，INT的同义词；BIGINT[(M)]，存储空间8字节，大型整数。
浮点类型有：FLOAT(精度)，存储空间可变，可用于指定单精度和双精度浮点数；FLOAT[(M,D)]，存储空间4字节，单精度浮嘛数，等同于FLOAT(4)，但是指定显示宽度和小数位数；DOUBLE[(M,D)]，存储空间8字节，双精度浮点数，等同FLOAT(8)，但是指定显示宽度和小数位数；DOUBLE PRECISION[(M,D)]，DOUBLE[(M,D)]的同义词；REAL[(M,D)]，DOUBLE[(M,D)]的同义词；DECIMAL[(M[,D])]，浮点数，以char存储，范围取决于显示宽度M；NUMERIC[(M,D)]，DECIMAL[(M[,D])]的同义词；DEC[(M,D)]，DECIMAL[(M[,D])]的同义词；FIXED[(M,D)]，DECIMAL[(M[,D])]的同义词。

 

9.10.2 日期和时间类型
值得注意的是，如果不手动设置，特定行中的TIMESTAMP列将被设置为最近修改该行的日期和时间。这对于事务记录是很有意义的。
日期和时间数据类型有：DATE，一个日期，以YYYY-MM-DD格式显示；TIME，一个时间，以HH:MM:SS形式显示，注意其范围比想像的宽得多；DATETIME，日期和时间，以YYYY-MM-DD HH:MM:SS格式显示；TIMESTAMP[(M)]，时间标签，在处理报告中有意义，显示格式取决于M的值 ；YEAR[(2|4)]，年份，可以指定2位数字或4位数字的格式。各有不同的范围。

 

9.10.3 字符串类型
字符串类型分为3类。第1类为普通字符串，即小段文本，包括CHAR（固定长度字符）类型和VARCHAR（可变长度字符）类型。可以指定每种类型的宽度。无论数据大小是多少，CHAR类型的列都会用空格来填补空白，但是VARCHAR列宽随数据大小变化。（请注意，获取CHAR类型数据的时候与存储VARCHAR数据的时候，MySQL将过滤多余的空格。）这两种类型都有速度与存储空间的问题。
第2类为TEXT和BLOB类型。这些类型大小可变，它们分别适用于长文本或二进制数据。BLOB全称为binary large objects（大二进制对象）。它支持任何数据，例如，图像或声音数据。
在实际应用中，除了TEXT区分大小写而BLOB不区分之外，TEXT和BLOB列是相同的。因为这些弄类型可以容纳大量的数据，所以在使用它们时需要特别考虑。
第3类包括两种特殊类型，SET和ENUM。SET类型用来指定列中的值必须来自一个特定集合中的指定值。列值可以包含来自该集合的多个值 。在指定的集合中，最大可以有64个元素。
ENUM就是枚举，与SET类型非常类似。但是该类型的列可以只有一个指定集合中的值 或者NULL，在枚举中最大还可以有65535个元素。
常规字符串类型有：[NATIONAL] CHAR(M) [BINARY|ASCII|UNICODE]，取值范围0~255个字符，固定长度为M的字符串，其中M的取值范围为0~255。NATIONAL关键字指定了应该使用的默认字符集。虽然这是MySQL的默认值，但包含它的原因是因为它是ANSI SQL标准的一部分。BINARY关键字指定了数据是区分大小写的（默认值就是区分大小写的）。ASCII关键字指定了在该列中使用latin1字符集。UNICODE关键字指定了使用Ucs字符集；CHAR，CHAR(1)的同义词；[NATIONAL] VARCHAR(M) [BINARY]，取值范围1~255个字符，除了可变长度，其他与上一项相同。
TEXT和BLOB类型有：TINYBLOB，小二进制大对象（BLOB）字段；TINYTEXT，小TEXT字段；BLOB，常规大小BLOB字段；TEXT，常规大小TEXT字段；MEDIUMBLOB，中型大小BLOB字段；MEDIUMTEXT，中型大小TEXT字段；LONGBLOB，长BLOB字段，LONGTEXT，长TEXT字段。
SET和ENUM类型有：ENUM('value1', 'value2', ...)，集合中的最大值为65535，该类型的列只可以容纳所列值之一或者为NULL；SET('value1', 'value2', ...)，集合中的最大值 为64，该类型的列可以容纳一组值或者为NULL。

 

9.11 进一步学习

 

9.12 下一章