MySQL专题9之MySQL索引、MySQL临时表、MySQL复制表

ab168

　　1、MySQL索引
　　-  MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
　　-  打个比方，如果合理的设计并使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
　　-  索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
　　-  创建索引时，你需要确保该索引是应用在SQL查询语句的条件（一般作为WHERE子句的条件）。
　　-  实际上，索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录。
　　-  上面都在说使用索引的好处，但是过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
　　-  建立索引会占用磁盘空间的索引文件。
　　a、普通索引
　　-  创建索引：这是最基本的索引，它没有任何限制。它有一下几种创建方式。
CREATE INDEX indexName ON mytable(username(length));　　-  如果是CHAR、VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length。
　　-  修改表结构（添加索引）：
ALTER table tableName ADD INDEX indexName(columnName)　　-  创建表的时候直接指定：
CREATE TABLE mytable(　　

　　
ID INT NOT NULL,
　　

　　
username VARCHAR(16) NOT NULL,
　　

　　
INDEX [indexName] (username(length))
　　

　　
);
　　-  删除索引的语法：
DROP INDEX [indexName] ON mytable;　　b、唯一索引
　　-  他与前面的普通索引类似，不同的是：索引列的值必须是唯一的，但是允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式。
　　-  创建索引：
CREATE UNIQUE INDEX indexName ON mytable(username(length))　　-  修改表结构：
ALTER table mytable ADD UNIQUE [indexName] (username(length))　　-  创建表的时候直接指定：
CREATE TABLE mytable(　　
ID INT NOT NULL,
　　
username VARCHAR(16) NOT NULL,
　　
UNIQUE [indexName] (username(length))
　　
);
　　c、使用ALTER命令添加和删除索引
　　-  有四种方式添加数据表的索引：
　　-  ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
　　-  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;　　d、使用ALTER命令添加和删除主键
　　-  主键只能作用于一个列中，添加主键索引时，你需要确保该主键默认不为空（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;　　-  删除主键只需要指定PRIMARY KEY，但是在删除索引时，你必须要知道索引名。
　　e、显示索引信息
　　-  你可以使用SHOW INDEX命令来列出表中的相关的索引信息。可以通过添加\G来格式化输出信息。如：
mysql> SHOW INDEX FROM table_name; \G　　2、MySQL临时表
　　-  MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接中可见，当关闭连接时，MySQL会自动删除表并释放所有空间。
　　-  临时表在MySQL3.23版本中添加，如果你的MySQL版本低于3.23版本就无法使用MySQL的临时表。不过现在一般很少见再使用这么低版本的MySQL数据库服务。
　　-  MySQL临时表只在当前连接中可见，如果你使用PHP脚本来创建MySQL临时表，那么每当PHP脚本执行完成之后，该临时表就会自动销毁。
　　-  如果你使用其他MySQL客户端程序连接MySQL数据库来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。
　　-  以下实例展示了如何是引用MySQL临时表的简单实例。以下的SQL代码可以适用于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命令显示数据表列表时，你将无法看到SalesSummary表。
　　-  如果你退出当前MySQL会话，再使用SELECT命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时，该临时表已经被销毁了。
　　-  删除MySQL临时表：默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你可以在当前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 'RUNOOB.SalesSummary' doesn't exist
　　3、MySQL复制表
　　-  如果我们需要完全的复制MySQL的数据表，包括表的结构、索引、默认值等，如果仅仅使用CREATE TABLE...SELECT命令，是无法实现的。
　　-  本节将向大家介绍如何使用完整的复制MySQL数据表，步骤如下：
　　a、使用SHOW CREATE TABLE命令获取创建数据表（CREATE TABLE）语句，该语句包含了元数据表的结构，索引等。
　　b、复制一下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令将完成的复制数据表结构。
　　c、如果你想复制表的内容，你就可以使用INSERT INTO ...SELECT语句来实现。
　　-  实例：尝试复制表runnoob_tbl。
　　-  步骤一：获取数据表的完整结构：
mysql> SHOW CREATE TABLE runoob_tbl \G;　　
*************************** 1. row ***************************
　　
       Table: runoob_tbl
　　
Create Table: CREATE TABLE `runoob_tbl` (
　　
  `runoob_id` int(11) NOT NULL auto_increment,
　　
  `runoob_title` varchar(100) NOT NULL default '',
　　
  `runoob_author` varchar(40) NOT NULL default '',
　　
  `submission_date` date default NULL,
　　
  PRIMARY KEY  (`runoob_id`),
　　
  UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
　　
) ENGINE=InnoDB
　　
1 row in set (0.00 sec)
　　

　　
ERROR:
　　
No query specified
　　-  修改SQL语句的数据表名，并执行SQL语句：
mysql> CREATE TABLE `clone_tbl` (　　
  -> `runoob_id` int(11) NOT NULL auto_increment,
　　
  -> `runoob_title` varchar(100) NOT NULL default '',
　　
  -> `runoob_author` varchar(40) NOT NULL default '',
　　
  -> `submission_date` date default NULL,
　　
  -> PRIMARY KEY  (`runoob_id`),
　　
  -> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
　　
-> ) ENGINE=InnoDB;
　　
Query OK, 0 rows affected (1.80 sec)
　　-  步骤三：执行完第二步骤后，你将在数据库中创建新的克隆表clone_tbl。如果你想拷贝数据表的数据你可以使用INSERT INTO ...SELECT语句来实现：
mysql> INSERT INTO clone_tbl (runoob_id,　　
    ->                        runoob_title,
　　
    ->                        runoob_author,
　　
    ->                        submission_date)
　　
    -> SELECT runoob_id,runoob_title,
　　
    ->        runoob_author,submission_date
　　
    -> FROM runoob_tbl;
　　
Query OK, 3 rows affected (0.07 sec)
　　
Records: 3  Duplicates: 0  Warnings: 0
　　-  执行以上步骤后，你将完整的复制表，包括表的结构以及表的数据。