mysql基础（二）

12.正则表达式：

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

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

模式什么样的模式匹配^开始的一个字符串$结束的一个字符串.任意单个字符[...]方括号中列出的任何字符[^...]任何字符方括号之间不会被列出p1|p2|p3交替;匹配的任何模式 p1, p2, 或 p3*前一个元素的零个或多个实例+前面元素的一或多个实例{n}前一个元素的n个实例{m,n}前一个元素的 m 到 n 个实例

示例

现在根据上面的表格，可以在不同的设备类型用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$';

13.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 changedmysql> 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时，是否支持这两种类型。

14.Alter命令：

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

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

root@host# mysql -u root -p password;Enter password:mysql> use test;Database changedmysql> 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: 0Max_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的文件删除索引。我们将在下一章看到此功能。

15.索引：

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

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

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

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

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

简单和唯一索引

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

CREATE UNIQUE INDEX index_nameON table_name ( column1, column2,...);

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

CREATE UNIQUE INDEX AUTHOR_INDEXON tutorials_tbl (tutorial_author)

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

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

mysql> CREATE UNIQUE INDEX AUTHOR_INDEXON 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........

16.临时表：

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

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

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

示例

下面是一个例子，显示临时表的使用。同样的代码可以在PHP脚本mysql_query()函数中使用。

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

17.复制表：

可能有一种情况，当需要一个表精确的副本，那么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_tblCreate 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=MyISAM1 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

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

18.数据库信息：

有三个信息，经常要从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 occurredprintf "%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脚本来获取各种有关数据库服务器的重要信息。

命令描述SELECT VERSION( )服务器版本字符串SELECT DATABASE( )当前数据库名称(如果没有，则为空)SELECT USER( )当前用户名SHOW STATUS服务器状态指示SHOW VARIABLES服务器配置变量

19.序列的使用：

序列是一组整数如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: 0mysql> 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 脚本提供了独有的函数来获取最后一条记录的自动递增值。

PERL 示例

使用mysql_insertid属性来获得由查询生成的AUTO_INCREMENT值。此属性是通过一个数据库句柄或语句句柄访问，这取决于如何发出查询。 下面的例子通过数据库句柄引用它：

$dbh->do ("INSERT INTO insect (name,date,origin)VALUES('moth','2001-09-14','windowsill')");my $seq = $dbh->{mysql_insertid};

PHP 示例

查询发出产生AUTO_INCREMENT值后，检索值是通过调用mysql_insert_id( )：

mysql_query ("INSERT INTO insect (name,date,origin)VALUES('moth','2001-09-14','windowsill')", $conn_id);$seq = mysql_insert_id ($conn_id);

重新编号序列

可能会出现一种情况，当从表中删除多个记录，并希望所有的记录都重新排序。这可以通过使用一个简单的技巧来完成，但这样做要非常小心，如果表是有关联其他表。

如果确定重新排序一个AUTO_INCREMENT列是无效的，做到这一点的方法是，从表中删除列，然后重新添加该列。下面的例子演示了如何使用这种技术在 insect 表来重新编号ID值：

mysql> ALTER TABLE insect DROP id;mysql> ALTER TABLE insect    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,    -> ADD PRIMARY KEY (id);

开始一个顺序在一个特定的值

默认情况下，MySQL的顺序将从1开始，但可以在创建表时指定其他数字也是可以的。以下的例子，MySQL序列将从100开始。

mysql> CREATE TABLE insect    -> (    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,    -> PRIMARY KEY (id),    -> name VARCHAR(30) NOT NULL, # type of insect    -> date DATE NOT NULL, # date collected    -> origin VARCHAR(30) NOT NULL # where collected);

或者，可以创建表，然后设定的初始序列值使用 ALTER TABLE。

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

20.重复处理：

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

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

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

CREATE TABLE person_tbl(    first_name CHAR(20),    last_name CHAR(20),    sex CHAR(10));

为了防止表中被创建的多个记录具有相同的姓氏和名字的值，添加一个主键(PRIMARY KEY)到它的定义。 当要做这一点，也必须声明索引列是NOT NULL，因为PRIMARY KEY不允许NULL值：

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

21.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}%'");

22.数据库导出（备份方法）：

导出表数据到一个文本文件的最简单方法是使用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_tblpassword ******

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

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

$ mysqldump -u root -p test tutorials_tbl > dump.txtpassword ******

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

---

-- 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.txtpassword ******

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

$ mysqldump -u root -p --all-databases > database_dump.txtpassword ******

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

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

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

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

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

$ mysqldump -u root -p database_name table_name > dump.txtpassword *****

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

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

$ mysql -u root -p database_name < dump.txtpassword *****

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

$ mysqldump -u root -p database_name \       | mysql -h other-host.com database_name

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