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
- 执行以上步骤后，你将完整的复制表，包括表的结构以及表的数据。
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
