實訓(xùn)案例名稱：索引的基本語法一、任務(wù)介紹索引是用來提高數(shù)據(jù)庫查詢效率的一種方式。建立有效、合適的索引可以極大地提高查詢效率，是數(shù)據(jù)庫高性能優(yōu)化操作必做的一件事。索引的基本語法包括創(chuàng)建索引、查看索引和刪除索引。二、實現(xiàn)步驟1.創(chuàng)建索引（1）創(chuàng)建表的時候創(chuàng)建索引。CREATETABLE表名(屬性名數(shù)據(jù)類型[完整性約束條件],屬性名數(shù)據(jù)類型[完整性約束條件],…屬性名數(shù)據(jù)類型[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[別名](屬性名1[(長度)][ASC|DESC]));示例如下。mysql>CREATETABLEstudent(idintprimarykey,namevarchar(255),sexvarchar(10),index(name));（2）使用CREATEINDEX創(chuàng)建索引。CREATEINDEXindex_nameONtable_name(column_list)CREATEUNIQUEINDEXindex_nameONtable_name(column_list)示例如下。mysql>CREATEINDEXidx_sexONstudent(sex);（3）使用ALTERTABLE創(chuàng)建索引。#普通索引ALTERTABLEtable_nameADDINDEXindex_name(column_list)#唯一索引ALTERTABLEtable_nameADDUNIQUE(column_list)#主鍵索引ALTERTABLEtable_nameADDPRIMARYKEY(column_list)示例如下。mysql>ALTERTABLEstudentADDINDEXidx_name(name);2.查看索引SHOWINDEXFROMtable_name;或者SHOWKEYSFROMtable_name;執(zhí)行該語句后，會返回一個結(jié)果集，包含表的所有索引信息，包括索引名稱、索引類型、索引字段等。每一行代表一個索引。#查看學(xué)生表student的索引信息mysql>SHOWINDEXFROMstudent;+---------+------+---------------+-------+------------+--------+---------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|

Cardinality|+---------+------+---------------+-------+------------+--------+---------+|student|0

|PRIMARY

|1

|id

|A

|0

||student|1

|idx_name

|1

|name

|A

|0

||student|1

|idx_sex

|1

|sex

|A

|0

||student|1

|id_name_index

|1

|id

|A

|0

|+---------+------+---------------+-------+------------+--------+---------+#查看學(xué)生表student的索引信息mysql>SHOWKEYSFROMstudent;+---------+------+---------------+-------+------------+--------+---------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|

Cardinality|+---------+------+---------------+-------+------------+--------+---------+|student|0

|PRIMARY

|1

|id

|A

|0

||student|1

|idx_name

|1

|name

|A

|0

||student|1

|idx_sex

|1

|sex

|A

|0

||student|1

|id_name_index

|1

|id

|A

|0

|+---------+------+---------------+-------+------------+--------+---------+3.刪除索引DROPINDEXindex_nameONtalbe_nameALTERTABLEtable_nameDROPINDEXindex_nameALTERTABLEtable_nameDROPPRIMARYKEY示例如下。mysql>DROPINDEXidx_sexONstudent;mysql>ALTERTABLEstudentDROPINDEXname;mysql>ALTERTABLEstudentDROPPRIMARYKEY;#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id |name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)#在姓名字段上添加索引mysql>ALTERTABLEuserADDINDEXidx_name(name);QueryOK,0rowsaffected(0.43sec)Records:0Duplicates:0Warnings:0#在性別字段上添加索引mysql>ALTERTABLEuserADDINDEXidx_sex(sex);QueryOK,0rowsaffected(0.34sec)Records:0Duplicates:0Warnings:0#查詢用戶表user的索引mysql>SHOWINDEXFROMuser;+-----+----------+---------+------------+-----------+---------+----------+|+-----+----------+---------+------------+-----------+---------+----------+|user| 0|PRIMARY|1|id|A|9||user| 1|idx_name|1|name|A|9||user| 1|idx_sex|1|sex|A|2|+-----+----------+---------+------------+-----------+---------+----------+3rowsinset(0.00sec)#查詢用戶表user的索引mysql>SHOWKEYSFROMuser;+-----+----------+---------+------------+-----------+---------+----------+|+-----+----------+---------+------------+-----------+---------+----------+|user| 0|PRIMARY|1|id|A|9||user| 1|idx_name|1|name|A|9||user| 1|idx_sex|1|sex|A|2|+-----+----------+---------+------------+-----------+---------+----------+3rowsinset(0.00sec)#刪除姓名字段上的索引mysql>DROPINDEXidx_nameONuser;QueryOK,0rowsaffected(0.18sec)Records:0Duplicates:0Warnings:0#刪除性別字段上的索引mysql>DROPINDEXidx_sexONuser;QueryOK,0rowsaffected(0.21sec)Records:0Duplicates:0Warnings:0#查詢用戶表user的索引mysql>SHOWKEYSFROMuser;+------+----------+---------+------------+-----------+---------+----------+|+