MySQL学习记录（七）-索引、临时表、复制表、元数据

索引

用途：

极大提升MySQL的检索速度，类似于目录页

分类：
单列索引：一个索引只含单个列，一个表可以有多个单列索引，但不属于组合索引
组合索引：一个索引包含多个列
区分方法：看索引包含几个列

缺点：

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

普通索引

创建索引：

语法：

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; 

唯一索引

区别：
索引列值必须唯一，但允许有空值，如果是组合索引，则索引的组合必须唯一。
创建索引：

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
#区别：unique

修改表结构：

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表时指定：

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);  

采用ALTER指令添加和删除索引

四种添加索引的方式：

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

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 
#这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 
#添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
#该语句指定了索引为 FULLTEXT ，用于全文索引。

使用 ALTER 命令添加和删除主键

添加主键：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。

删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
#删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

mysql> SHOW INDEX FROM table_name; \G
#可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

临时表

用途：
用于保存临时的数据
特性：
再关闭客户端程序时会自动销毁，也可以采用手动销毁
举例：

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
);#临时表：TEMPORARY TABLE

仅在本次MySQL会话中可以查询到此临时表，下次重新进入MySQL会发现该表已自动销毁。

我们也可以手动删除临时表：
删除临时表
采用DROP TABLE

DROP TABLE 表名;

复制表

用途：
用于完全复制MySQL的数据表，包括表的结构、索引、默认值等
步骤：

1. 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
2. 复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
3. 如果你想复制表的内容，你就可以使用INSERT INTO … SELECT 语句来实现。

代码：

show create table nowcoder_tbl \G;

获得：

Create Table: CREATE TABLE `nowcoder_tbl` (
  `nowcoder_id` int(11) NOT NULL AUTO_INCREMENT,
  `nowcoder_title` varchar(100) NOT NULL,
  `nowcoder_author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL,
  PRIMARY KEY (`nowcoder_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

于是我们执行：以得到复制表

mysql> create table clone_tbl (
    -> nowcoder_id int(11) not null auto_increment,
    -> nowcoder_title varchar(100) not null default '',
    -> nowcoder_author varchar(40) not null default '',
    -> submission_date date default null,
    -> primary key (nowcoder_id)
    -> ) engine=innodb;

拷贝数据：

mysql> insert into clone_tbl
    -> (nowcoder_id, nowcoder_title, nowcoder_author, submission_date)
    -> select
    -> nowcoder_id, nowcoder_title, nowcoder_author, submission_date
    -> from nowcoder_tbl;
    #这里用了一个select来获取原表中的所有数据

元数据

是什么？：
一些基础信息，如下：

• 查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
• 数据库和数据表的信息： 包含了数据库及数据表的结构信息。
• MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。
  如何获得？：

用途	语法
服务器版本信息	SELECT VERSION() \ select version()
当前数据库名	SELECT DATABASE() \ select database
当前用户名	SELECT USER() \ select user()
服务器状态	SHOW STATUS \ show status
服务器配置变量	SHOW VARIABLES \ show variables

总结

本文介绍了MySQL索引的类型、添加、修改、删除的基本方法，介绍了临时表、复制表的概念和创建修改方法，并记录了查询MySQL元数据的语法，便于复习和回顾。