mysql> drop database yiibai_tutorials1;

Query OK, 0 rows affected (0.01 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| yiibai             |

+--------------------+

6 rows in set (0.00 sec)

到目前为止，我们只是从一个表读取数据。这是相对简单的，但在大多数现实中的MySQL使用，需要从多个表中，在单个查询获得数据。

可以在单个SQL查询中使用多个表。连接MySQL中的行在两个或多个表到一个表。

可以使用Join在SELECT，UPDATE和DELETE语句加入MySQL表。我们将看到LEFT JOIN的例子, 这与简单的MySQL JOIN有所不同。

假设我们有两个表 tcount_tbl 和 tutorials_tbl，在数据库：test ，完整列表如下：

示例

试试下面的例子：

root@host# mysql -u root -p password;

Enter password:

mysql> use test;

Database changed

mysql> create table tcount_tbl(

    -> tutorial_author varchar(24) not null,

    -> tutorial_count int(10) not null default 0 );

Query OK, 0 rows affected

mysql> INSERT INTO tcount_tbl (tutorial_author, tutorial_count) VALUES('mahran', 10);

mysql> INSERT INTO tcount_tbl (tutorial_author, tutorial_count) VALUES('mahnaz', 0);

mysql> INSERT INTO tcount_tbl (tutorial_author, tutorial_count) VALUES('Jen',    0);

mysql> INSERT INTO tcount_tbl (tutorial_author, tutorial_count) VALUES('Gill', 20);

mysql> INSERT INTO tcount_tbl (tutorial_author, tutorial_count) VALUES('Paul', 10);

mysql> INSERT INTO tcount_tbl (tutorial_author, tutorial_count) VALUES('yiibai', 10);

mysql> SELECT * FROM tcount_tbl;

+-----------------+----------------+

| tutorial_author | tutorial_count |

+-----------------+----------------+

| mahran          |             10 |

| mahnaz          |              0 |

| Jen             |              0 |

| Gill            |             20 |

| Paul            |             10 |

| yiibai          |             10 |

+-----------------+----------------+

6 rows in set (0.01 sec)

mysql> SELECT * from tutorials_tbl;

+-------------+----------------+-----------------+-----------------+

| tutorial_id | tutorial_title | tutorial_author | submission_date |

+-------------+----------------+-----------------+-----------------+

|           1 | Learn PHP      | Paul            | 2015-07-17      |

|           2 | Learn MySQL    | Saya            | 2015-07-17      |

|           3 | Learning JAVA  | yiibai          | 2007-05-06      |

+-------------+----------------+-----------------+-----------------+

3 rows in set (0.00 sec)

mysql>

现在，我们可以写一个SQL查询来连接这两个表。此查询将从表tutorials_tbl和tcount_tbl 选择所有作者的教程数量。

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count

    -> FROM tutorials_tbl a, tcount_tbl b

    -> WHERE a.tutorial_author = b.tutorial_author;

+-------------+-----------------+----------------+

| tutorial_id | tutorial_author | tutorial_count |

+-------------+-----------------+----------------+

|           1 | Paul            |             10 |

|           3 | Yiibai          |             10 |

+-------------+-----------------+----------------+

2 rows in set (0.01 sec)

mysql>

18、MySQL NULL值

我们已经看到SQL SELECT命令和WHERE子句一起使用，来从MySQL表中提取数据，但是，当我们试图给出一个条件，比较字段或列值设置为NULL，它确不能正常工作。

为了处理这种情况，MySQL提供了三大运算符

• IS NULL: 如果列的值为NULL，运算结果返回 true

• IS NOT NULL: 如果列的值不为NULL，运算结果返回 true

• <=>: 运算符比较值，(不同于=运算符)即使两个空值它返回 true

涉及NULL的条件是特殊的。不能使用= NULL或！= NULL来匹配查找列的NULL值。这样的比较总是失败，因为它是不可能告诉它们是否是true。 甚至 NULL = NULL 也是失败的。

要查找列的值是或不是NULL，使用IS NULL或IS NOT NULL。

在命令提示符，使用NULL值：

假设在 test 数据库中的表 tcount_tbl 它包含两个列 tutorial_author 和 tutorial_count， 其中 tutorial_count 的值为NULL表明其值未知：

示例

试试下面的例子：

root@host# mysql -u root -p password;

Enter password:

mysql> use test;

Database changed

mysql> drop table tcount_tbl;

Query OK, 0 rows affected (0.05 sec)

mysql> create table tcount_tbl

    -> (

    -> tutorial_author varchar(40) NOT NULL,

    -> tutorial_count  INT

    -> );

Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO tcount_tbl

    -> (tutorial_author, tutorial_count) values ('mahran', 20);

mysql> INSERT INTO tcount_tbl

    -> (tutorial_author, tutorial_count) values ('mahnaz', NULL);

mysql> INSERT INTO tcount_tbl

    -> (tutorial_author, tutorial_count) values ('Jen', NULL);

mysql> INSERT INTO tcount_tbl

    -> (tutorial_author, tutorial_count) values ('Gill', 20);

mysql> SELECT * from tcount_tbl;

+-----------------+----------------+

| tutorial_author | tutorial_count |

+-----------------+----------------+

| mahran          |             20 |

| mahnaz          |           NULL |

| Jen             |           NULL |

| Gill            |             20 |

+-----------------+----------------+

4 rows in set (0.00 sec)

mysql>

可以看到，= 及 != 不能与 NULL值不能正常工作(匹配)如下：

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;

Empty set (0.00 sec)

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL;

Empty set (0.01 sec)

要查找记录中，其中 tutorial_count 列的值是或不是NULL，查询应该这样写：

mysql> SELECT * FROM tcount_tbl

    -> WHERE tutorial_count IS NULL;

+-----------------+----------------+

| tutorial_author | tutorial_count |

+-----------------+----------------+

| mahnaz          |           NULL |

| Jen             |           NULL |

+-----------------+----------------+

2 rows in set (0.00 sec)

mysql> SELECT * from tcount_tbl

    -> WHERE tutorial_count IS NOT NULL;

+-----------------+----------------+

| tutorial_author | tutorial_count |

+-----------------+----------------+

| mahran          |             20 |

| Gill            |             20 |

+-----------------+----------------+

2 rows in set (0.00 sec)

19、MySQL正则表达式

在前面我们已经看到了MySQL的模式匹配：LIKE ...%. MySQL支持另一种类型的模式匹配的操作基于正则表达式和正则表达式运算符。如果知道PHP或Perl，那么它是非常简单的，因为这匹配非常相似于脚本正则表达式。

以下是模式的表格，其可以连同REGEXP运算符使用。

示例

现在根据上面的表格，可以在不同的设备类型用SQL查询来满足要求。在这里，列出一些的理解。考虑有一个表称为 person_tbl，它是有一个 name 字段：

查询查找所有以 “st” 开头的名字：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查询找到所有以 'ok' 结尾的名字

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查询查找其中包含 'mar' 所有的名字

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查询查找以元音 'ok' 结尾的所有名称

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

20、MySQL事务

事务是数据库处理操作，其中执行就好像它是一个单一的一组有序的工作单元。换言之，事务将永远不会是完全的，除非在组内每个单独的操作是成功的。如果事务中的任何操作失败，整个事务将失败。

实际上，许多SQL查询组成到一个组，将执行所有这些一起作为事务的一部分。

事务性质

事务具有以下四个标准属性，通常由首字母缩写ACID简称：

• 原子性: 确保了工作单位中的所有操作都成功完成; 否则，事务被中止，在失败时会被回滚到事务操作以前的状态。

• 一致性：可确保数据库在正确的更改状态在一个成功提交事务。

• 隔离: 使事务相互独立地操作。

• 持久性: 确保了提交事务的结果或系统故障情况下仍然存在作用。

在MySQL中，事务以BEGIN WORK语句开始开始工作，并使用COMMIT或ROLLBACK语句结束。SQL命令在开始和结束语句之间构成大量事务。

提交和回滚

这两个关键字Commit和Rollback主要用于MySQL的事务。

• 当一个成功的事务完成后，COMMIT命令发出的变化对所有涉及的表将生效。

• 如果发生故障，ROLLBACK命令发出后，事务中引用的每个表将恢复到事务开始之前的状态。

可以通过设置AUTOCOMMIT这个会话变量控制事务的行为。如果AUTOCOMMIT设置为1(默认值)，那么每个SQL语句(在事务或不在事务)被认为是一个完整的事务并提交，在默认情况下是在当它完成时。当AUTOCOMMIT设置为0，通过发出SET AUTOCOMMIT=0命令, 随后的一系列语句就像一个事务，但并没有任何活动被提交直到一个明确的发出 COMMIT 语句。

可以通过使用PHP 的 mysql_query()函数执行这些SQL命令。

关于事务通用示例

此事件序列是独立于所使用的编程语言; 逻辑路径可以是任何编程语言，使用它在创建应用程序中创建。

可以通过使用 mysql_query()函数执行这些PHP SQL命令

• 开始事务是通过发出SQL命令BEGIN WORK

• 类似发出的一个或多个SQL命令 SELECT, INSERT, UPDATE 或 DELETE.

• 根据要求，检查是否有任何错误

• 如果有错误，发出ROLLBACK命令，否则执行COMMIT命令。

在MySQL的事务安全表类型

不能直接使用事务，可以使用但它们没有安全保障。如果打算使用事务在MySQL编程，那么需要使用一个特殊的方式来创建表。 有许多类型的表其支持事务，但目前最流行的一种是：InnoDB.

支持InnoDB表需要特定的编译参数，在源代码编译MySQL时。如果MySQL版本不支持InnoDB，得要求互联网服务提供商建立一个版本的MySQL的InnoDB表类型的支持，或 下载并安装MySQL-Max二进制分发的Windows版本，或者Linux/UNIX开发环境中工作的表类型。

如果你的MySQL安装支持InnoDB表，简直就是一个TYPE= InnoDB的定义添加到表创建语句后面。例如，下面的代码创建一个名为 tcount_tbl 的 InnoDB 类型表：

root@host# mysql -u root -p password;

Enter password:

mysql> use test;

Database changed

mysql> create table tcount_tbl

    -> (

    -> tutorial_author varchar(40) NOT NULL,

    -> tutorial_count  INT

    -> ) TYPE=InnoDB;

Query OK, 0 rows affected (0.05 sec)

请检查下面的链接了解更多有关: InnoDB

可以使用其它类型的表 GEMINI 或 BDB, 但它取决于安装MySQL时，是否支持这两种类型。

21、MySQL Alter命令

当想改变表名，MySQL的ALTER命令非常有用, 在添加或删除任何表字段到现有列在表中。

让我们开始创建一个表为 testalter_tbl。

root@host# mysql -u root -p password;

Enter password:

mysql> use test;

Database changed

mysql> create table testalter_tbl

    -> (

    -> i INT,

    -> c CHAR(1)

    -> );

Query OK, 0 rows affected (0.05 sec)

mysql> SHOW COLUMNS FROM testalter_tbl;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| i     | int(11) | YES  |     | NULL    |       |

| c     | char(1) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)

删除，添加或重新定义列

假设想要从上面MySQL的表中删除一个现有列，那么使用ALTER命令以及DROP子句如下：

mysql> ALTER TABLE testalter_tbl  DROP i;

如果在表中列是唯一的一个，那么DROP将无法正常工作。

要添加列，使用ADD并指定列定义。下面的语句将存储到 i 列：testalter_tbl:

mysql> ALTER TABLE testalter_tbl ADD i INT;

发出这条语句后，testalter将包含当第一次创建表时的两列, 但不会有完全一样的结构。这是因为新的列被添加到表中时，默认排在最后一列。因此，即使 i 列原来是 mytbl 的第一列，但现在它是最后一列。

mysql> SHOW COLUMNS FROM testalter_tbl;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| c     | char(1) | YES  |     | NULL    |       |

| i     | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)

若要指示列在表中的特定位置，要么使用FIRST把它放在第一列或AFTER col_name 来指定新的列应放置col_name之后。 试试下面的ALTER TABLE语句，使用 SHOW COLUMNS 看看有什么影响：

ALTER TABLE testalter_tbl DROP i;

ALTER TABLE testalter_tbl ADD i INT FIRST;

ALTER TABLE testalter_tbl DROP i;

ALTER TABLE testalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 只能在ADD子句中使用。这意味着，如果你想在表中重新排位现有列，首先必须删除(DROP )它，然后在新的位置添加(ADD)它。

更改列定义或名称

要改变列的定义，使用MODIFY 或CHANGE 子句以及ALTER命令。 例如, 要改变字段 c 从 CHAR(1) 修改为 CHAR(10), 那么可以这样做：

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

CHANGE语法可能有点不同。CHANGE关键字后的名称是要修改的列，然后指定新的定义，其中包括新的名称。试试下面的例子：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

如果现在使用CHANGE转换i列从BIGINT为INT，但不改变列名，该语句执行预期：

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE影响Null和缺省值属性

当使用 MODIFY 或 CHANGE修改列，还可以指定该列是否能为NULL值以及它的默认值。事实上，如果你不这样做，MySQL也会自动分配这些属性值。

下面是一个例子，这里 NOT NULL列将使用100作为默认值。

mysql> ALTER TABLE testalter_tbl

    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果不使用上面的命令，那么MySQL将填补使用NULL 来填充所有列值。

更改列的默认值

可以使用ALTER命令更改任何列的默认值。尝试下面的例子。

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

mysql> SHOW COLUMNS FROM testalter_tbl;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| c     | char(1) | YES  |     | NULL    |       |

| i     | int(11) | YES  |     | 1000    |       |

+-------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)

从任何一列删除默认的约束，可以使用ALTER命令以及DROP子句。

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

mysql> SHOW COLUMNS FROM testalter_tbl;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| c     | char(1) | YES  |     | NULL    |       |

| i     | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)

更改表类型

可以通过使用ALTER命令以及TYPE子句修改表的类型。试试下面的例子，将 testalter_tbl 的类型更改为MyISAM 表类型。

若想要知道一个表的当前类型，那么可使用 SHOW TABLE STATUS 语句。

mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;

mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G

*************************** 1. row ****************

           Name: testalter_tbl

           Type: MyISAM

     Row_format: Fixed

           Rows: 0

 Avg_row_length: 0

    Data_length: 0

Max_data_length: 25769803775

   Index_length: 1024

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2015-06-03 18:35:36

    Update_time: 2015-06-03 18:35:36

     Check_time: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)

重命名表

要重命名表，使用ALTER TABLE语句的RENAME选项。试试下面的例子是用来重命名testalter_tbl为alter_tbl。

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

可以使用ALTER命令来创建并在MySQL的文件删除索引。我们将在下一章看到此功能。

22、MySQL索引

数据库索引是一种数据结构，目的是提高表的操作速度。可以使用一个或多个列，提供快速随机查找和访问记录的高效排序来创建索引。

要创建的索引，应当认为哪列将用于使SQL查询，创建对这些列的一个或多个索引。

实际上，索引也是表，其中保存主键或索引字段的指针并指向每个记录到实际的表的类型。

用户无法看到索引，它们只是用来加速查询，并将被用于数据库搜索引擎在查找记录时提高速度。

INSERT和UPDATE语句需要更多的时间来创建索引，作为在SELECT语句快速在这些表上操作。其原因是，在执行插入或更新数据时，数据库需要将插入或更新索引值也更新。

简单和唯一索引

可以在表上创建唯一值索引。唯一索引意味着两行不能有相同的索引值。下面是在表上创建索引的语法

CREATE UNIQUE INDEX index_name

ON table_name ( column1, column2,...);

可以使用一个或多个列来创建索引。例如，我们可以使用tutorial_author 来创建一个 tutorials_tbl 索引。

CREATE UNIQUE INDEX AUTHOR_INDEX

ON tutorials_tbl (tutorial_author)

可以在表上创建一个简单的索引。创建简单的索引只是省略UNIQUE关键字。简单的索引可以在表中重复的值。

如果想索引的列的值按降序排列，可以列名之后添加保留字DESC。

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX

ON tutorials_tbl (tutorial_author DESC)

使用ALTER命令来添加和删除索引

有四种类型的索引可以添加到一个表：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 此语句添加一个主键(PRIMARY KEY)，这意味着索引值必须是唯一的，而不能为空。

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 此语句创建一个索引的量的值必须是唯一的(除了不能使用NULL值，其它的可以出现多次)。

• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 这增加普通的索引，其中的任何值的出现多次。

• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): 这将创建一个用于文本搜索目的一种特殊的FULLTEXT索引。

下面是一个添加索引到现有表的例子。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

您可以通过使用ALTER命令以及DROP子句来删除索引。试试下面的例子，用来删除上面创建的索引。

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

使用ALTER命令来添加和删除PRIMARY KEY

也可以用同样的方法添加主键。但要在列确保主键正常使用，需要指定使用 NOT NULL。

下面是一个例子添加主键在现有的表。列需要添加 NOT NULL 属性，然后再添加为一个主键。

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;

mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

可以使用ALTER命令删除主键如下：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

要删除索引的不是主键，必须指定索引名。

显示索引信息

可以使用SHOW INDEX命令，列出所有与表相关的索引。 垂直格式输出(由\G指定)，这是经常有用的语句，以避免长线概括输出：

试试下面的例子：

mysql> SHOW INDEX FROM table_name\G

........

23、MySQL临时表

临时表可能在某些情况下是非常有用的，以保持临时数据。 临时表的最重要的事情是，当前客户端会话结束时，它们将会被删除。

临时表是在MySQL版本3.23中增加的。如果使用MySQL 3.23之前的旧版本，是不能使用临时表的，但可以使用堆表。

如前所述，临时表将只持续在会话存在时。如果在运行一个PHP脚本代码，临时表会自动在脚本执行完毕时删除。如果是通过MySQL客户端程序连接到MySQL数据库服务器, 那么临时表会一直存在，直到关闭客户端或手动销毁表。

示例

下面是一个例子，显示临时表的使用。

mysql> CREATE TEMPORARY TABLE SalesSummary (

    -> product_name VARCHAR(50) NOT NULL

    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00

    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00

    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0

);

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary

    -> (product_name, total_sales, avg_unit_price, total_units_sold)

    -> VALUES

    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;

+--------------+-------------+----------------+------------------+

| product_name | total_sales | avg_unit_price | total_units_sold |

+--------------+-------------+----------------+------------------+

| cucumber     |      100.25 |          90.00 |                2 |

+--------------+-------------+----------------+------------------+

1 row in set (0.00 sec)

当发出SHOW TABLES命令，临时表不会被列在表的列表中。现在，如果注销MySQL会话，然后发出SELECT命令，那么会发现在数据库中没有可用的数据。即使是临时表也不存在了。

删除临时表

默认情况下，当数据库连接被终止，所有的临时表被MySQL删除。尽管如此，如果想在结束会话前删除它们，那么可通过发出DROP TABLE命令。

以下是删除一个临时表的例子：

mysql> CREATE TEMPORARY TABLE SalesSummary (

    -> product_name VARCHAR(50) NOT NULL

    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00

    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00

    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0

);

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary

    -> (product_name, total_sales, avg_unit_price, total_units_sold)

    -> VALUES

    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;

+--------------+-------------+----------------+------------------+

| product_name | total_sales | avg_unit_price | total_units_sold |

+--------------+-------------+----------------+------------------+

| cucumber     |      100.25 |          90.00 |                2 |

+--------------+-------------+----------------+------------------+

1 row in set (0.00 sec)

mysql> DROP TABLE SalesSummary;

mysql>  SELECT * FROM SalesSummary;

ERROR 1146: Table 'test.SalesSummary' doesn't exist

24、MySQL复制表

可能有一种情况，当需要一个表精确的副本，那么CREATE TABLE... SELECT可能达不到目的，因为副本必须包含相同的索引，默认值等等。

可以通过以下步骤处理这种情况：

• 使用 SHOW CREATE TABLE以获得CREATE TABLE语句用于指定源表的结构，索引和所有其它的。

• 修改语句用来更改表名为克隆表并执行该语句。通过这种方式，将有确切的克隆表。

• 或者，如果需要复制以及表的内容，再发出一个INSERT INTO... SELECT语句。

示例

试试下面的例子来创建表tutorials_tbl的一个克隆表。

步骤1:

获取有关表的完整结构。

mysql> SHOW CREATE TABLE tutorials_tbl \G;

*************************** 1. row ***************************

       Table: tutorials_tbl

Create Table: CREATE TABLE `tutorials_tbl` (

  `tutorial_id` int(11) NOT NULL auto_increment,

  `tutorial_title` varchar(100) NOT NULL default '',

  `tutorial_author` varchar(40) NOT NULL default '',

  `submission_date` date default NULL,

  PRIMARY KEY  (`tutorial_id`),

  UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)

) TYPE=MyISAM

1 row in set (0.00 sec)

ERROR:

No query specified

步骤2:

重命名该表，并创建另一个表。

mysql> CREATE TABLE `clone_tbl` (

  -> `tutorial_id` int(11) NOT NULL auto_increment,

  -> `tutorial_title` varchar(100) NOT NULL default '',

  -> `tutorial_author` varchar(40) NOT NULL default '',

  -> `submission_date` date default NULL,

  -> PRIMARY KEY  (`tutorial_id`),

  -> UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)

-> ) TYPE=MyISAM;

Query OK, 0 rows affected (1.80 sec)

步骤3:

执行步骤2后，将在数据库中创建一张克隆表。如果想从旧表复制数据到新表，那么可以通过使用INSERT INTO... SELECT语句来做到这一点。

mysql> INSERT INTO clone_tbl (tutorial_id,

    ->                        tutorial_title,

    ->                        tutorial_author,

    ->                        submission_date)

    -> SELECT tutorial_id,tutorial_title,

    ->        tutorial_author,submission_date,

    -> FROM tutorials_tbl;

Query OK, 3 rows affected (0.07 sec)

Records: 3  Duplicates: 0  Warnings: 0

最后，这是想要确切复制的一张表。

25、MySQL数据库信息

有三个信息，经常要从MySQL获取。

• 有关查询结果的信息: 这包括任何SELECT，UPDATE或DELETE语句所影响的记录数量。

• 有关表和数据库的信息: 这包括关于表和数据库的结构的信息。

• 关于MySQL服务器的信息: 这包括数据库服务器的当前状态，版本号等。

在mysql的提示符下，很容易得到这些信息，但如果使用Perl或PHP的API，需要显式调用各种API来获取这些信息。 下面的部分将说明如何获取这些信息。

获取通过查询影响的行数量

PERL 示例

在DBI脚本，受影响的行数是通过do( )或execute( )返回，这取决于如何执行查询：

# Method 1

# execute $query using do( )

my $count = $dbh->do ($query);

# report 0 rows if an error occurred

printf "%d rows were affected\n", (defined ($count) ? $count : 0);

# Method 2

# execute query using prepare( ) plus execute( )

my $sth = $dbh->prepare ($query);

my $count = $sth->execute ( );

printf "%d rows were affected\n", (defined ($count) ? $count : 0);

PHP 示例

在PHP中，调用mysql_affected_rows()函数，以找出查询多少行改变：

$result_id = mysql_query ($query, $conn_id);

# report 0 rows if the query failed

$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);

print ("$count rows were affected\n");

列出表和数据库

这很容易列出数据库服务器中的所有可用的数据库和表。但如果结果可能为null，那么可能没有足够的权限。

除了下面提到的方法，还可以用SHOW TABLES或SHOW DATABASES来查询获得表或数据库列表，无论是在 PHP 或 Perl 中。

PERL 示例

# Get all the tables available in current database.

my @tables = $dbh->tables ( );

foreach $table (@tables ){

   print "Table Name $table\n";

}

PHP 示例

<?php

$con = mysql_connect("localhost", "user", "password");

if (!$con)

{

  die('Could not connect: ' . mysql_error());

}

$db_list = mysql_list_dbs($con);

while ($db = mysql_fetch_object($db_list))

{

  echo $db->Database . "<br />";

}

mysql_close($con);

?>

获取服务器元数据

有哪些可以在mysql提示符下执行，或使用任何如PHP脚本来获取各种有关数据库服务器的重要信息。

26、MySQL序列的使用

序列是一组整数如1，2，3，...为了在需要时生成的。数据库中序列是常用的，因为很多应用都需要在表的每行中，包含一个唯一的值，并且序列提供了一种简单的方法来生成它们。本章将介绍如何在MySQL中使用序列。

使用AUTO_INCREMENT列

在MySQL中最简单使用序列的方式是定义一个列AUTO_INCREMENT，然后其余事情由MySQL来打理。

示例

尝试下面的例子。在创建表之后，它会插入几行此表中，但在这里不给出记录ID，因为它由MySQL自动增加。

mysql> CREATE TABLE insect

    -> (

    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    -> PRIMARY KEY (id),

    -> name VARCHAR(30) NOT NULL, # type of insect

    -> date DATE NOT NULL, # date collected

    -> origin VARCHAR(30) NOT NULL # where collected

);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO insect (id,name,date,origin) VALUES

    -> (NULL,'housefly','2003-09-19','kitchen'),

    -> (NULL,'millipede','2004-09-11','driveway'),

    -> (NULL,'grasshopper','2015-02-10','front yard');

Query OK, 3 rows affected (0.02 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM insect ORDER BY id;

+----+-------------+------------+------------+

| id | name        | date       | origin     |

+----+-------------+------------+------------+

|  1 | housefly    | 2003-09-19 | kitchen    |

|  2 | millipede   | 2004-09-11 | driveway   |

|  3 | grasshopper | 2015-02-10 | front yard |

+----+-------------+------------+------------+

3 rows in set (0.00 sec)

获取AUTO_INCREMENT值

LAST_INSERT_ID( )是一个SQL函数，这样可以了解如何发出SQL语句，从任何客户端中使用它。 以其他方式，Perl 和 PHP 脚本提供了独有的函数来获取最后一条记录的自动递增值。

27、MySQL重复处理

表或结果集有时含有重复记录。有时，它是允许的，但有时它被要求停止使用重复记录。有时，需要识别重复记录并从表中删除它们。本章将介绍如何防止在一个表中，以及如何删除已有的重复记录。

防止在一个表发生重复记录

可以使用适当表字段的PRIMARY KEY 或 UNIQUE 来防止重复记录。让我们来看看下面的例子：下表中没有这样的索引或主键，所以这里允许 first_name 和last_name 记录重复。

CREATE TABLE person_tbl

(

    first_name CHAR(20),

    last_name CHAR(20),

    sex CHAR(10)

);

CREATE TABLE person_tbl

(

   first_name CHAR(20) NOT NULL,

   last_name CHAR(20) NOT NULL,

   sex CHAR(10),

   PRIMARY KEY (last_name, first_name)

);

如果插入一条与现有记录重复到表，在列或定义索引列，表中一个唯一索引的存在通常会导致错误的发生。

应该使用 INSERT IGNORE 而不是INSERT。如果记录与现有现有不重复时，MySQL将其正常插入。如果记录是一个重复的，则 IGNORE 关键字告诉MySQL丢弃它而不会产生错误。

下面的例子不会有错误，也不会插入重复的记录。

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)

    -> VALUES( 'Jay', 'Thomas');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)

    -> VALUES( 'Jay', 'Thomas');

Query OK, 0 rows affected (0.00 sec)

使用REPLACE而不是INSERT。如果记录是新的，它插入就像使用 INSERT。如果它是重复的，新的记录将取代旧的记录：

mysql> REPLACE INTO person_tbl (last_name, first_name)

    -> VALUES( 'Ajay', 'Kumar');

Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)

    -> VALUES( 'Ajay', 'Kumar');

Query OK, 2 rows affected (0.00 sec)

INSERT IGNORE和REPLACE应根据实现的重复处理行为来选择。INSERT忽略保持第一套重复记录，并丢弃剩下的。REPLACE保持最后一组重复的和擦除任何较早的记录。

另一种方法是强制唯一性是增加唯一(UNIQUE)索引，而不是一个主键(PRIMARY KEY)。

CREATE TABLE person_tbl

(

   first_name CHAR(20) NOT NULL,

   last_name CHAR(20) NOT NULL,

   sex CHAR(10)

   UNIQUE (last_name, first_name)

);

统计和标识重复

以下是查询以统计first_name和last_name 在表中的重复记录数。

mysql> SELECT COUNT(*) as repetitions, last_name, first_name

    -> FROM person_tbl

    -> GROUP BY last_name, first_name

    -> HAVING repetitions > 1;

这个查询将返回person_tbl表中的所有重复记录的列表.在一般情况下，识别的集合值重复，执行以下步骤：

• 确定哪些列包含可重复值

• 列出这些列中的列选择列表，使用COUNT(*)

• 列出的列也可以使用 GROUP BY 子句

• 添加一个HAVING子句，通过分组计算出唯一值数大于1的记录重复

从查询结果消除重记录

可以使用SELECT语句以及DISTINCT一起在一个表中找出可用唯一记录。

mysql> SELECT DISTINCT last_name, first_name

    -> FROM person_tbl

    -> ORDER BY last_name;

替代DISTINCT方法是添加GROUP BY子句列名称到选择的列。这有删除重复并选择在指定的列值的唯一组合的效果：

mysql> SELECT last_name, first_name

    -> FROM person_tbl

    -> GROUP BY (last_name, first_name);

使用表的更换删除重复

如果一个表中重复的记录，并要删除该表中的所有重复的记录，那么可以参考下面的程序：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex

    ->                  FROM person_tbl;

    ->                  GROUP BY (last_name, first_name);

mysql> DROP TABLE person_tbl;

mysql> ALTER TABLE tmp RENAME TO person_tbl;

从表中删除重复记录的一个简单的方法就添加索引(INDEX) 或 主键(PRIMAY KEY)到该表。即使该表已经提供，可以使用此技术来删除重复的记录。

mysql> ALTER IGNORE TABLE person_tbl

    -> ADD PRIMARY KEY (last_name, first_name);

28、MySQL和SQL注入

如果通过网页需要用户输入一些数据信息，并将其插入到MySQL数据库，这是一个引入SQL注入安全问题的机会。这一节将学习如何防止这种情况的发生，并帮助保护脚本和MySQL语句。

通常注入是在当要求用户输入时，类似他们的姓名，只是一个名字，他们给出，会在不知不觉中包含MySQL的语句会在数据库运行。

永远不要信任用户提供的数据，这个过程只有在数据验证后，作为一项规则，这是通过模式匹配进行。在下面的例子中，用户名被限制在字母+数字+字符加下划线，并在8-20个字符之间的长度 - 可以根据需要修改这些规则。

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))

{

   $result = mysql_query("SELECT * FROM users

                          WHERE username=$matches[0]");

}

 else

{

   echo "username not accepted";

}

为了说明问题，考虑这个片段：

// supposed input

$name = "Qadir'; DELETE FROM users;";

mysql_query("SELECT * FROM users WHERE name='{$name}'");

该函数调用从表中检索用户记录，其中名称列匹配由用户指定的名称。 在正常情况下，$name将只包含字母数字字符，或可能是空格，如字符串ilia。 但在这里，通过附加一个全新的查询到$name，在调用数据库变成灾难：注入DELETE查询删除所有的用户记录。

幸运的是，如果使用MySQL，mysql_query()函数不允许查询堆叠或一个函数调用执行多个查询。如果尝试堆叠查询，调用失败。

然而，其他PHP数据库扩展，如SQLite和PostgreSQL，它们会乐意地进行堆查询，执行一个字符串提供的查询，并创建一个严重的安全问题。

防止SQL注入

可以在脚本语言，如 Perl和PHP巧妙地处理所有转义字符。MySQL扩展为PHP提供mysql_real_escape_string()函数来转义输入的特殊字符。

if (get_magic_quotes_gpc())

{

  $name = stripslashes($name);

}

$name = mysql_real_escape_string($name);

mysql_query("SELECT * FROM users WHERE name='{$name}'");

LIKE的困境

为了解决LIKE困境，自定义的转义机制必须把用户提供%和_字符到常量。使用addcslashes()函数，它可以让指定的字符范围转义。

$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");

// $sub == \%something\_

mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

29、MySQL数据库导出(备份方法)

导出表数据到一个文本文件的最简单方法是使用SELECT... INTO OUTFILE语句直接将导出查询结果导出到服务器主机上的文件。

使用导出数据SELECT... INTO OUTFILE语句

语句的语法结合了常规的SELECT INTO与OUTFILE filename 末尾。默认的输出格式与LOAD DATA是一样的，所以下面的语句导出 tutorials_tbl 表到C:\tutorials.txt 并使用制表符分隔，换行结尾的文件：

mysql> SELECT * FROM tutorials_tbl

    -> INTO OUTFILE 'C:\tutorials.txt';

可以利用选项来说明如何引号和分隔列，更改记录输出格式。 使用CRLF为结束行导出tutorial_tbl 为CSV格式表格，使用以下语句：

mysql> SELECT * FROM passwd INTO OUTFILE 'C:\tutorials.txt'

    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'

    -> LINES TERMINATED BY '\r\n';

SELECT... INTO OUTFILE具有以下属性：

• 输出的文件是直接由MySQL服务器创建的，因此，文件名应指明想要的文件名，它会被写到服务器主机上。还有就是语句类似于没有LOCAL版本的LOAD DATA的本地版本。

• 必须有MySQL的FILE权限来执行SELECT ... INTO语句。

• 输出文件必须还不存在。 这防止MySQL弄错文件很重要。

• 应该有服务器主机或某种方式来检索该主机上登录帐户的文件。否则，SELECT ... INTO OUTFILE可能没有任何值给出。

• 在UNIX下，文件创建所有人都是可读的，由MySQL服务器所拥有。这意味着，虽然能够读取该文件，可能无法将其删除。

导出表作为原始数据

mysqldump程序用于复制或备份表和数据库。它可以写入表输出作为一个原始数据文件，或为一组重新创建表中的INSERT语句的记录。

转储一个表作为一个数据文件，必须指定一个--tab 选项指定目录，让MySQL服务器写入文件。

例如，从数据库test中的tutorials_tbl表转储到一个文件在C:\tmp目录，可使用这样的命令：

$ mysqldump -u root -p --no-create-info \

            --tab=c:\tmp TEST tutorials_tbl

password ******

以SQL格式导出表内容或定义

以SQL格式的表导出到一个文件，使用这样的命令：

$ mysqldump -u root -p test tutorials_tbl > dump.txt

password ******

这将创建一个具有以下内容折文件，如下：

-- MySQL dump 8.53

--

-- Host: localhost    Database: test

---------------------------------------------------------

-- Server version       5.5.58

--

-- Table structure for table `tutorials_tbl`

--

CREATE TABLE tutorials_tbl (

  tutorial_id int(11) NOT NULL auto_increment,

  tutorial_title varchar(100) NOT NULL default '',

  tutorial_author varchar(40) NOT NULL default '',

  submission_date date default NULL,

  PRIMARY KEY  (tutorial_id),

  UNIQUE KEY AUTHOR_INDEX (tutorial_author)

) TYPE=MyISAM;

--

-- Dumping data for table `tutorials_tbl`

--

INSERT INTO tutorials_tbl

       VALUES (1,'Learn PHP','John Poul','2012-01-04');

INSERT INTO tutorials_tbl

       VALUES (2,'Learn MySQL','Abdul S','2015-05-14');

INSERT INTO tutorials_tbl

       VALUES (3,'JAVA Tutorial','Sanjay','2014-05-10');

要转储多个表，所有数据库名称参数后跟它们的名字。要转储整个数据库，不需要在数据库之后命名(附加)任何表：

$ mysqldump -u root -p test > database_dump.txt

password ******

要备份所有可用的数据库在主机上，使用以下命令：

$ mysqldump -u root -p --all-databases > database_dump.txt

password ******

--all-databases选项可在MySQL 3.23.12之后的版本使用。

该方法可用于实现数据库的备份策略。

复制表或数据库到另一台主机

如果想从一个MySQL服务器复制表或数据库到另一台，使用mysqldump以及数据库名和表名。

在源主机上运行下面的命令。将转储完整的数据库到文件dump.txt：

$ mysqldump -u root -p database_name table_name > dump.txt

password *****

可以复制完整的数据库，而无需使用特定的表名，如上所述。

现在ftp dump.txt文件在另一台主机上，并使用下面的命令。运行此命令之前，请确保已创建数据库名称在目标服务器上。

$ mysql -u root -p database_name < dump.txt

password *****

另一种方式来实现这一点，无需使用一个中间文件是来发送，mysqldump输出直接通过网络到远程MySQL服务器。如果可以从源数据库所在的主机那里连接两个服务器，使用此命令(请确保两个服务器可以访问)：

$ mysqldump -u root -p database_name \

       | mysql -h other-host.com database_name

命令mysqldump的一半连接到本地服务器，并转储输出写入管道。另一半MySQL连接到other-host.com远程MySQL服务器。它读取管道输入并发送每条语句到other-host.com服务器。

30、MySQL数据库导入(恢复数据方法)

MySQL中有两种简单的方法可从以前备份的文件数据加载(恢复)到MySQL数据库。

使用LOAD DATA导入数据

MySQL提供了一个大容量数据加载的LOAD DATA语句。下面是一个读取文件C:\dump.txt 并将其加载到当前数据库表mytbl的例子声明：

mysql> LOAD DATA LOCAL INFILE 'C:\dump.txt' INTO TABLE mytbl;

• 如果LOCAL关键词不存在，MySQL查找使用绝对路径在服务器主机上的数据文件，完全指定文件的位置，从文件系统的根开始。 MySQL读取从给定的位置的文件。

• 默认情况下，LOAD DATA假设数据文件包含一个行由制表符分隔范围内被换行(新行)分割行和数据值。

• 要明确指定一个文件格式，使用FIELDS子句来描述一行内字段的特征，LINES子句指定的行结束序列。下面的LOAD DATA语句指定的数据文件包含由冒号和行，是由回车和新行字符结束分隔其值：

mysql> LOAD DATA LOCAL INFILE 'C:\dump.txt' INTO TABLE mytbl

  -> FIELDS TERMINATED BY ':'

  -> LINES TERMINATED BY '\r\n';

• LOAD DATA假定数据文件中的列具有相同的顺序在表中的列。如果不是这样，可以指定一个列表来指示哪些表列数据文件列应该被装入。 假设表中的列A，B和C，但在数据文件连续列对应于列B，C，和A。可以像这样加载文件：

mysql> LOAD DATA LOCAL INFILE 'dump.txt'

    -> INTO TABLE mytbl (b, c, a);

使用mysqlimport导入数据

MySQL还包括一个实用程序名：mysqlimport，它充当围绕LOAD DATA包装器 这样就可以直接从命令行输入加载文件。

从 dump.txt 加载数据到表mytbl，使用下面的命令在命令行提示符。

$ mysqlimport -u root -p --local database_name dump.txt

password *****

如果使用mysqlimport，命令行选项提供的格式说明。对应于上述两个LOAD DATA 语句的 mysqlimport 命令如下：

$ mysqlimport -u root -p --local --fields-terminated-by=":" \

   --lines-terminated-by="\r\n"  database_name dump.txt

password *****

在 mysqlimport 中指定的选项顺序并不重要，但它们都应该在数据库名的前面。

mysqlimport语句使用--columns选项来指定列的顺序：

$ mysqlimport -u root -p --local --columns=b,c,a \

    database_name dump.txt

password *****

处理引号和特殊字符

除了TERMINATED，FIELDS子句可以指定其他格式选项。默认情况下，LOAD DATA假设值是加引号的，并解释反斜线(\)作为特殊字符转义字符。要明确注明引用字符值， 使用ENCLOSED BY; MySQL将剥离字符的数据值末端在输入处理期间。要更改默认的转义字符，可使用ESCAPED BY。

当指定ENCLOSED BY，表明引号字符应该从数据值被剥离，有可能通过加一次或通过转义字符，确实包含引号字符在数据值前。例如，如果引号和转义字符是" 和 \，输入 "a""b\"c" 将被解释为：a"b"c

对于 mysqlimport，相应的命令行选项引号和转义值是通过 --fields-enclosed-by 和 --fields-escaped-by来指定。

31、MySQL实用函数

这里是所有重要的 MySQL 函数的列表。每个函数都用合适的例子来说明如下。

• MySQL Group By 子句 - MySQL的GROUP BY语句以及SQL聚合函数，用于类似SUM提供某些数据库表的列来分组结果数据集

• MySQL IN 子句 - 这是一个子句，它可以用来连同任何MySQL查询语句以指定条件

• MySQL BETWEEN 子句 - 这是一个子句，它可以用来与任何MySQL查询来指定条件

• MySQL UNION关键字 - 使用UNION操作多个结果集组合成一个结果集

• MySQL COUNT函数 - MySQL的COUNT聚合函数用于计算一个数据库表中的行数

• MySQL MAX 函数 - MySQL的MAX聚合函数允许我们选择某些列的最高(最大)值

• MySQL MIN函数 - MySQL的MIN聚合函数允许我们选择某些列的最低(最小)值

• MySQL AVG函数 - MySQL的AVG聚合函数是用来对某些表的列求它的平均值

• MySQL SUM函数 - MySQL的SUM聚合函数允许选择某列的总和

• MySQL SQRT函数 - 这是用来生成给定数的平方根

• MySQL RAND函数 - 使用MySQL命令产生一个随机数

• MySQL CONCAT函数 - 这是用来连接MySQL命令中的任何字符串

• MySQL DATE 和 Time 时间日期函数 - MySQL日期和时间相关的函数完整列表

• MySQL数字函数 - 在MySQL中操作数字的MySQL函数完整列表

• MySQL字符串函数 - 在MySQL中的字符串操作的MySQL函数的完整列表