Linux MySQL --- 视图与索引

视图（View）

1）视图是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

2）数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。

3）视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。这样做既使应用简单化，也保证了系统的安全。

* 视图的优点：

视图与表在本质上虽然不相同，但视图经过定义以后，结构形式和表一样，可以进行查询、修改、更新和删除等操作。同时视图具有优点：

1) 定制用户数据，聚焦特定的数据

在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。

例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。

2) 简化数据操作

在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。

3) 提高数据的安全性

视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

4) 共享所需数据

通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。

5) 更改数据格式

通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。

6) 重用 SQL 语句

视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

* 视图与其它数据表的区别：

1）视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。

2）存储在数据库中的查询操作 SQL语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。

3）视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。

4）视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。

5）视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。

6）视图的建立和删除只影响视图本身，不影响对应的基本表。

* 区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

MySQL [mysql_chuid]> DESC students;

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

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

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

| id          | int(5)      | NO   | PRI | 0       |       |

| name        | char(10)    | YES  | UNI | NULL    |       |

| sex         | char(6)     | YES  |     | man     |       |

| weight      | int(5)      | YES  |     | 130     |       |

| height      | int(5)      | YES  |     | 171     |       |

| course_name | varchar(15) | YES  |     | NULL    |       |

| course_ID   | char(10)    | YES  |     | NULL    |       |

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

7 rows in set (0.00 sec)

* 使用视图的时候，注意以下几点：

* 1）创建视图需要足够的访问权限。

* 2）创建视图的数目没有限制。

* 3）视图可以嵌套，即从其他视图中检索数据的查询来创建视图。

* 4）视图不能索引，也不能有关联的触发器、默认值或规则。

* 5）视图可以和表一起使用。

* 6）视图不包含数据，所以每次使用视图时，都必须执行查询中所需的任何一个检索操作。如果用多个连接和过滤条件创建了复杂的视图或嵌套了视图，可能会发现系统运行性能下降得十分严重。因此，在部署大量视图应用时，应该进行系统测试。

创建视图

可以使用 CREATE VIEW 语句来创建视图。

语法格式：CREATE VIEW <视图名> AS <SELECT语句>

* 语法说明：

* <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

* <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

* 对于创建视图中的 SELECT 语句的限制：

* 用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。

* SELECT 语句不能引用系统或用户变量。

* SELECT 语句不能包含 FROM 子句中的子查询。

* SELECT 语句不能引用预处理语句参数。

1）视图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图。可使用CHECK TABLE语句检查视图定义是否存在这类问题。

2）视图定义中允许使用ORDER BY语句，但是若从特定视图进行选择，而该视图使用了自己的ORDER BY语句，则视图定义中的ORDER BY将被忽略。

3）视图定义中不能引用TEMPORARY表（临时表），不能创建TEMPORARY视图。

* WITH CHECK OPTION的意思是修改视图时，检查插入的数据是否符合WHERE设置的条件。

创建基于单表的视图

# 在students表上创建一个名为view_students的视图：

MySQL [mysql_chuid]> CREATE VIEW view_students AS SELECT * FROM students;

Query OK, 0 rows affected (0.11 sec)

MySQL [mysql_chuid]> SELECT * FROM view_students;

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

| id | name       | sex   | weight | height | course_name | course_ID |

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

|  1 | chd        | man   |    130 |    171 | python      | 1         |

|  2 | wang       | woman |    105 |    165 | java        | 2         |

|  3 | li         | man   |    132 |    169 | linux       | 5         |

|  4 | liu        | woman |    132 |    169 | NULL        | 6         |

|  5 | anivd      | man   |    130 |    171 | python      | 2         |

|  6 | luo        | woman |    118 |    162 | linux       | 8         |

|  7 | zhang      | woman |     98 |    162 | java/linux  | 9         |

|  8 | chen       | man   |    130 |    170 | python      | 7         |

|  9 | chenhuid   | woman |    102 |    168 | python      | 3         |

| 10 | chenhuahua | woman |     96 |    158 | python      | 6         |

| 12 | chh        | man   |    132 |    171 | python      | 8         |

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

11 rows in set (0.17 sec)

默认情况下，创建的视图和基本表的字段是一样的，也可以通过指定视图字段的名称来创建视图。

创建基于多表的视图

MySQL中也可以在两个以上的表中创建视图，使用CREATE VIEW语句创建。

# 在students表上创建一个名为v_students的视图：

MySQL [mysql_chuid]> CREATE VIEW v_students(v_id,v_name,v_sex,v_weight,v_height,v_cou_name,v_cou_ID) AS SELECT id,name,sex,weight,height,course_name,course_ID FROM students;

Query OK, 0 rows affected (0.02 sec)

MySQL [mysql_chuid]> SELECT * FROM v_students;

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

| v_id | v_name     | v_sex | v_weight | v_height | v_cou_name | v_cou_ID |

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

|    1 | chd        | man   |      130 |      171 | python     | 1        |

|    2 | wang       | woman |      105 |      165 | java       | 2        |

|    3 | li         | man   |      132 |      169 | linux      | 5        |

|    4 | liu        | woman |      132 |      169 | NULL       | 6        |

|    5 | anivd      | man   |      130 |      171 | python     | 2        |

|    6 | luo        | woman |      118 |      162 | linux      | 8        |

|    7 | zhang      | woman |       98 |      162 | java/linux | 9        |

|    8 | chen       | man   |      130 |      170 | python     | 7        |

|    9 | chenhuid   | woman |      102 |      168 | python     | 3        |

|   10 | chenhuahua | woman |       96 |      158 | python     | 6        |

|   12 | chh        | man   |      132 |      171 | python     | 8        |

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

11 rows in set (0.05 sec)

view_students和v_students两个视图中的字段名称不同，但是数据却相同，因此在使用视图时，可能用户不需要了解基本表的结构，更接触不到实际表中的数据，从而保证了数据库的安全。

查询视图

DESCRIBE 可以用来查看视图。

语法格式：DESCRIBE 视图名；

视图用于查询主要应用：

使用视图重新格式化检索出的数据。

使用视图简化复杂的表连接。

使用视图过滤数据。

MySQL [mysql_chuid]> DESCRIBE v_students;

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

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

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

| v_id       | int(5)      | NO   |     | 0       |       |

| v_name     | char(10)    | YES  |     | NULL    |       |

| v_sex      | char(6)     | YES  |     | man     |       |

| v_weight   | int(5)      | YES  |     | 130     |       |

| v_height   | int(5)      | YES  |     | 171     |       |

| v_cou_name | varchar(15) | YES  |     | NULL    |       |

| v_cou_ID   | char(10)    | YES  |     | NULL    |       |

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

7 rows in set (0.02 sec)

* DESCRIBE一般情况下可以简写成DESC，输入这个命令的执行结果和输入DESCRIBE是一样的。

查看视图的字段信息

MySQL [mysql_chuid]> CREATE TABLE student(id int(12) PRIMARY KEY,name varchar(20),sex char(5),age int(5),score decimal(5,2),subject varchar(25),teacher varchar(20));     #  创建学生信息表student

Query OK, 0 rows affected (0.02 sec)

MySQL [mysql_chuid]> CREATE VIEW v_student AS SELECT id,name,score FROM student;

Query OK, 0 rows affected (0.00 sec)      # 创建查询信息表的视图

MySQL [mysql_chuid]> DESC v_student;   # 查看视图

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

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

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

| id    | int(12)      | NO   |     | NULL    |       |

| name  | varchar(20)  | YES  |     | NULL    |       |

| score | decimal(5,2) | YES  |     | NULL    |       |

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

3 rows in set (0.00 sec)

MySQL [mysql_chuid]> SHOW CREATE VIEW v_student \G    # 查看视图的详细信息

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

                View: v_student

         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_student` AS select `student`.`id` AS `id`,`student`.`name` AS `name`,`student`.`score` AS `score` from `student`

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.00 sec)

修改视图

可以使用ALTER VIEW语句来对已有的视图进行修改。

语法格式：ALTER VIEW <视图名> AS <SELECT语句>

* 语法说明：

<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

<SELECT 语句>：指定创建视图的SELECT语句，可用于查询多个基础表或源视图。

对于ALTER VIEW语句的使用，需要用户具有针对视图的CREATE VIEW和DROP权限，以及由SELECT语句选择的每一列上的某些权限。

修改视图结构，除了可以通过ALTER VIEW外，也可以使用DROP VIEW语句先删除视图，再使用CREATE VIEW语句来实现。

视图是一个虚拟表，实际的数据来自于基本表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据。

* 对视图的修改就是对基本表的修改，因此在修改时，要满足基本表的数据定义。

某些视图是可更新的。也就是说，可以使用UPDATE、DELETE或INSERT等语句更新基本表的内容。对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系。

* 还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的：

* 1）聚合函数SUM()、MIN()、MAX()、COUNT()等。

* 2）DISTINCT关键字。

* 3）GROUP BY子句。

* 4）HAVING子句。

* 5）UNION 或 UNION ALL运算符。

* 6）位于选择列表中的子查询。

* 7）FROM子句中的不可更新视图或包含多个表。

* 8）WHERE子句中的子查询，引用FROM子句中的表。

* 9）ALGORITHM选项为TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。

MySQL [mysql_chuid]> ALTER VIEW v_student AS SELECT id,name,age,sex,score FROM student;   # 修改v_student视图

Query OK, 0 rows affected (0.00 sec)

MySQL [mysql_chuid]> DESC v_student;

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

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

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

| id    | int(12)      | NO   |     | NULL    |       |

| name  | varchar(20)  | YES  |     | NULL    |       |

| age   | int(5)       | YES  |     | NULL    |       |

| sex   | char(5)      | YES  |     | NULL    |       |

| score | decimal(5,2) | YES  |     | NULL    |       |

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

5 rows in set (0.00 sec)

MySQL [mysql_chuid]> insert into v_student(id,name,age,sex,score)values(2,'chuid',26,'man',100);    # 插入视图

Query OK, 1 row affected (0.03 sec)

MySQL [mysql_chuid]> SELECT * FROM v_student;  # 查看视图

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

| id | name  | age  | sex  | score  |

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

|  1 | chd   |   27 | man  |  98.00 |

|  2 | chuid |   26 | man  | 100.00 |

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

2 rows in set (0.00 sec)

MySQL [mysql_chuid]> UPDATE v_student SET age=28 WHERE id=2;    # 更新v_student视图

Query OK, 1 row affected (0.08 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MySQL [mysql_chuid]> SELECT * FROM v_student;

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

| id | name  | age  | sex  | score  |

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

|  1 | chd   |   27 | man  |  98.00 |

|  2 | chuid |   28 | man  | 100.00 |

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

2 rows in set (0.00 sec)

* 修改视图名称可以先将视图删除，然后按照相同的定义语句进行视图的创建，并命名为新的视图名称。

删除视图

可以使用DROP VIEW语句来删除视图。（删除视图时，只能删除视图的定义，不会删除数据。）

语法格式：DROP VIEW <视图名1> [ , <视图名2> …]

其中：<视图名>指定要删除的视图名。DROP VIEW语句可以一次删除多个视图，但是必须在每个视图上拥有DROP权限。

# 删除v_student视图

MySQL [mysql_chuid]> DROP VIEW IF EXISTS v_student;

Query OK, 0 rows affected (0.00 sec)

MySQL [mysql_chuid]> SHOW CREATE VIEW v_student;

ERROR 1146 (42S02): Table 'mysql_chuid.v_student' doesn't exist

MySQL [mysql_chuid]>

 

索引

# 在关系数据库中，如果有成千上万条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

# 索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。

# 通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

# 索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

# 一张表可以创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新、删除记录时，需要同时修改索引。因此，索引越多，插入、更新和删除记录的速度就越慢。

# 对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

为什么要使用索引

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。

索引是MySQL中十分重要的数据库对象，是数据库性能调优技术的基础，常用于实现数据的快速检索。

在MySQL中，通常有以下两种方式访问数据库表的行数据：

1) 顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。

顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。

例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

2) 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。

使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

简而言之，不使用索引，MySQL就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

索引的优缺点

索引有其明显的优势，也有其不可避免的缺点。

* 索引的优点：

1）通过创建唯一索引可以保证数据库表中每一行数据的唯一性。

2）可以给所有的 MySQL 列类型设置索引。

3）可以大大加快数据的查询速度，这是使用索引最主要的原因。

4）在实现数据的参考完整性方面可以加速表与表之间的连接。

5）在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

* 增加索引的缺点：

创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

使用索引时，需要综合考虑索引的优点和缺点。

索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

创建索引

MySQL提供了三种创建索引的方法：

（1）使用CREATE INDEX语句

# 可以使用专门用于创建索引的CREATE INDEX语句在一个已有的表上创建索引，但该语句不能创建主键。

语法格式：CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

* 语法说明：

* <索引名>:指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。

* <表名>：指定要创建索引的表名。

* <列名>：指定要创建索引的列名。通常可以考虑将查询语句中在JOIN子句和WHERE子句里经常出现的列作为索引列。

* <长度>：可选项。指定使用列前的length个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占用的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度最大上限255个字节，如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。

* [ ASC | DESC]：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。

（2）使用CREATE TABLE语句

# 索引也可以在创建表（CREATE TABLE）的同时创建。在CREATE TABLE语句中添加以下语句。

语法格式：CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)

# 在CREATE TABLE语句中添加此语句，表示在创建新表的同事创建该表的主键。

语法格式：KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)

# 在CREATE TABLE语句中添加此语句，表示在创建新表的同时创建该表的索引。

语法格式：UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

# 在CREATE TABLE语句中添加此语句，表示在创建新表的同时创建该表的唯一性索引。

语法格式：FOREIGN KEY <索引名> <列名>

# 在CREATE TABLE语句中添加此语句，表示在创建新表的同时创建该表的外键。

（3）使用ALTER TABLE语句

# CREATE INDEX语句可以在一个已有的表上创建索引，ALTER TABLE语句也可以在一个已有的表上创建索引。在使用ALTER TABLE语句修改表的同时，可以向已有的表添加索引。具体的做法是在ALTER TABLE语句中添加以下语法成分的某一项或几项。

语法格式：ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

# 在ALTER TABLE语句中添加此语法成分，表示在修改表的同时为该表添加索引。

语法格式：ADD PRIMARY KEY [<索引类型>] (<列名>,…)

# 在ALTER TABLE语句中添加此语法成分，表示在修改表的同时为该表添加主键。

语法格式：ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

# 在ALTER TABLE语句中添加此语法成分，表示在修改表的同时为该表添加唯一性索引。

语法格式：ADD FOREIGN KEY [<索引名>] (<列名>,…)

# 在ALTER TABLE语句中添加此语法成分，表示在修改表的同时为该表添加外键。

创建普通索引

创建普通索引时，通常使用INDEX关键字

# 创建表stu_students，在该表的height字段创建普通索引

MySQL [mysql_chuid]> create table stu_students(id int NOT NULL,name char(10),stu_ID int DEFAULT NULL,age int DEFAULT NULL,height int DEFAULT NULL,INDEX(height));

Query OK, 0 rows affected (0.29 sec)

MySQL [mysql_chuid]> show create table stu_students\G

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

       Table: stu_students

Create Table: CREATE TABLE `stu_students` (

  `id` int(11) NOT NULL,

  `name` char(10) DEFAULT NULL,

  `stu_ID` int(11) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `height` int(11) DEFAULT NULL,

  KEY `height` (`height`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.03 sec)

# 如果要经常根据height列进行查询，就可以对height列创建索引：

MySQL [mysql_chuid]> select * from students;

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

| id | name_list | sex   | weight | height |

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

|  1 | chd       | man   |    130 |    171 |

|  2 | wang      | woman |    105 |    165 |

|  5 | anivd     | man   |    130 |    171 |

|  7 | zhang     | woman |     98 |    162 |

|  8 | chen      | man   |    130 |    170 |

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

5 rows in set (0.00 sec)

MySQL [mysql_chuid]> alter table students add index idx_height (height);

Query OK, 0 rows affected (0.10 sec)

Records: 0  Duplicates: 0  Warnings: 0

# 使用 add index idx_height (height)就创建了一个名为idx_height使用列height的索引

# 索引名称是任意的，索引如果有多列，可以在括号里依次写上：

MySQL [mysql_chuid]> alter table students add index idx_sex_height (sex, height);

Query OK, 0 rows affected (0.46 sec)

Records: 0  Duplicates: 0  Warnings: 0

创建唯一索引

创建唯一索引，通常使用UNIQUE关键字

# 创建表stu_student，在该表的height字段上使用UNIQUE关键字创建唯一索引

MySQL [mysql_chuid]> create table stu_student(id int NOT NULL,name char(10),stu_ID int DEFAULT NULL,age int DEFAULT NULL,height int DEFAULT NULL,UNIQUE INDEX(height));

Query OK, 0 rows affected (0.10 sec)

MySQL [mysql_chuid]> show create table stu_student\G

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

       Table: stu_student

Create Table: CREATE TABLE `stu_student` (

  `id` int(11) NOT NULL,

  `name` char(10) DEFAULT NULL,

  `stu_ID` int(11) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `height` int(11) DEFAULT NULL,

  UNIQUE KEY `height` (`height`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

# 在设计关系数据表的时候，看上去唯一的列，例如手机号码、邮箱等，因为他们具有业务含义，因此不宜作为主键。但是这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个手机号。这个时候，就可以给该列添加一个唯一索引。

MySQL [mysql_chuid]> select * from students;

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

| id | name_list | sex   | weight | height |

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

|  1 | chd       | man   |    130 |    171 |

|  2 | wang      | woman |    105 |    165 |

|  5 | anivd     | man   |    130 |    171 |

|  7 | zhang     | woman |     98 |    162 |

|  8 | chen      | man   |    130 |    170 |

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

5 rows in set (0.00 sec)

# 假设students表的name_list不能重复： （通过UNIQUE关键字就可以添加一个唯一索引）

MySQL [mysql_chuid]> alter table students add unique index uni_name_list (name_list);

Query OK, 0 rows affected, 1 warning (0.12 sec)

Records: 0  Duplicates: 0  Warnings: 1

# 只对某一列添加一个唯一约束而不创建唯一索引： （这种情况下name_list列没有索引，但仍然具有唯一性保证。）

MySQL [mysql_chuid]> alter table students add constraint uni_name_list unique (name_list);

Query OK, 0 rows affected, 1 warning (0.07 sec)

Records: 0  Duplicates: 0  Warnings: 1

# 无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别。当我们在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率，如果没有索引，查询也能正常执行，只是速度会变慢。因此，索引可以在使用数据库的过程中逐步优化。

* 小结：

* 1.通过对数据表创建索引，可以提高查询速度。

* 2.通过创建唯一索引，可以保证某一列的值具有唯一性。

* 3.数据库索引对于用户和应用程序来说都是透明的。

查看索引

使用SHOW INDEX语句查看表中创建的索引。

查看索引的语法格式：SHOW INDEX FROM <表名> [ FROM <数据库名>]

* 语法说明：

1）<表名>：指定需要查看索引的数据表名。

2）<数据库名>：指定需要查看索引的数据表所在的数据库，可省略。

比如，SHOW INDEX FROM student FROM test; 语句表示查看test数据库中student数据表的索引。

MySQL [mysql_chuid]> SHOW INDEX FROM stu_students\G   # 查看stu_students表的索引信息

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

        Table: stu_students

   Non_unique: 1

     Key_name: height

 Seq_in_index: 1

  Column_name: height

    Collation: A

  Cardinality: 0

     Sub_part: NULL

       Packed: NULL

         Null: YES

   Index_type: BTREE

      Comment:

Index_comment:

1 row in set (0.02 sec)

索引中主要参数说明

参数	说明
Table	表示创建索引的数据表名，这里是stu_students数据表。
Non_unique	表示该索引是否是唯一索引。若不是唯一索引，则该列的值为1；若是唯一索引，则该列的值为0。
Key_name	表示索引的名称。
Seq_in_index	表示该列在索引中的位置，如果索引是单列的，则该列的值为1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。
Column_name	表示定义索引的列字段。
Collation	表示列以何种顺序存储在索引中。在MySQL中，升序显示值“A”（升序），若显示为NULL，则表示无分类。
Cardinality	索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
Sub_part	表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为NULL。
Packed	指示关键字如何被压缩。若没有被压缩，值为NULL。
Null	用于显示索引列中是否包含NULL。若列含有NULL，该列的值为YES。若没有，则该列的值为NO。
Index_type	显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
Comment	显示评注。

修改和删除索引

删除索引是指将表中已经存在的索引删除掉。不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。对于这样的索引应该将其删除。

在MySQL中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。

当不再需要索引时，可以使用DROP INDEX语句或ALTER TABLE语句来对索引进行删除。

语法格式：DROP INDEX <索引名> ON <表名>

* 使用DROP INDEX语句语法说明：

1）<索引名>：要删除的索引名。

2）<表名>：指定该索引所在的表名。

* 使用ALTER TABLE 语句

根据ALTER TABLE语句的语法可知，该语句也可以用于删除索引。具体使用方法是将ALTER TABLE语句的语法中部分指定为以下子句中的某一项。

DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。

DROP INDEX index_name：表示删除名称为index_name的索引。

DROP FOREIGN KEY FK_symbol：表示删除外键。

* 如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

MySQL [mysql_chuid]> DROP INDEX height ON stu_student;   # 删除表stu_student中的索引

Query OK, 0 rows affected (0.16 sec)

Records: 0  Duplicates: 0  Warnings: 0

MySQL [mysql_chuid]> SHOW INDEX FROM stu_student\G

Empty set (0.00 sec)

MySQL [mysql_chuid]> show create table stu_student\G

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

       Table: stu_student

Create Table: CREATE TABLE `stu_student` (

  `id` int(11) NOT NULL,

  `name` char(10) DEFAULT NULL,

  `stu_ID` int(11) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `height` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

# 删除表stu_students中名称为height的唯一索引

MySQL [mysql_chuid]> ALTER TABLE stu_students DROP INDEX height;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

MySQL [mysql_chuid]> SHOW INDEX FROM stu_students\G

Empty set (0.00 sec)

MySQL [mysql_chuid]> show create table stu_student\G

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

       Table: stu_student

Create Table: CREATE TABLE `stu_student` (

  `id` int(11) NOT NULL,

  `name` char(10) DEFAULT NULL,

  `stu_ID` int(11) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `height` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)