Mysql索引示例_mysql索引语法及示例

注：本篇文章是对菜鸟教程中的mysql索引(http://www.runoob.com/mysql/mysql-index.html)的翻译版本；添加了示例，便于理解；

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

普通索引

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length));

注：indexName是索引名称，username是字段名

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

举例：

CREATE TABLE employee(

id INT,

sname VARCHAR(50),

age INT ,

PASSWORD VARCHAR(50)

);

#创建索引

CREATE INDEX index_sname ON employee(sname(20)); #注意添加索引的长度必须小于建表时给字段定义的长度，也可以不给长度，如：CREATE INDEX index_sname ON employee(sname);

#删除索引

DROP INDEX index_sname ON employee;

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

举例：

ALTER TABLE employee ADD INDEX index_sname(sname);#这里可以不指定索引的长度

创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

INDEX [indexName] (username(length))

);

举例：

CREATE TABLE teacher(

id INT NOT NULL,

teachername VARCHAR(16) NOT NULL,

INDEX index_teachername(teachername(5))

);

CREATE TABLE tea(

id INT NOT NULL,

teaname VARCHAR(16) NOT NULL,

INDEX (teaname)

);

删除索引的语法

DROP INDEX [indexName] ON mytable;

举例：

DROP INDEX index_sname ON employee;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

举例：

CREATE TABLE tea(

id INT NOT NULL,

teaname VARCHAR(16) NOT NULL,

INDEX (teaname)

);

CREATE UNIQUE INDEX index_teaname ON tea(teaname(4));

CREATE UNIQUE INDEX index_teaname ON tea(teaname);#也可以不给长度

修改表结构

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

举例：

CREATE TABLE student(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(50) NOT NULL COMMENT "学生姓名",

age VARCHAR(50) NOT NULL COMMENT "学生年龄"

);

ALTER TABLE student ADD UNIQUE index_sname (sname(20));

ALTER TABLE student ADD UNIQUE(sname);##也可以不指定长度

DROP INDEX index_sname ON student;

SHOW INDEX FROM student;

创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

UNIQUE [indexName] (username(length))

);

举例：

CREATE TABLE cat(

id INT ,

catName VARCHAR(20),

UNIQUE INDEX index_catName(catName(5))

);

CREATE TABLE dog(

id INT ,

dogName VARCHAR(20),

UNIQUE INDEX index_dogName(dogName)

);

使用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 TABLE table_name ADD INDEX index_name ( column1, column2, column3 ): 添加多列索引.

以下实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

举例：

CREATE TABLE student(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(50) NOT NULL COMMENT "学生姓名",

age VARCHAR(50) NOT NULL COMMENT "学生年龄"

);

ALTER TABLE student ADD INDEX index_sname(sname);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

举例：

ALTER TABLE student DROP INDEX index_sname;

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

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空(NOT NULL)。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;#先修改为--不为空

mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);#然后添加主键

举例：

CREATE TABLE cat(

id INT ,

catName VARCHAR(20),

UNIQUE INDEX index_catName(catName(5))

);

ALTER TABLE cat MODIFY id INT NOT NULL;

ALTER TABLE cat ADD PRIMARY KEY (id);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

ALTER TABLE cat DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY(一个表只有一个主键)，但在删除索引时，你必须知道索引名。

显示索引信息

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

尝试以下实例:

mysql> SHOW INDEX FROM table_name;

MySQL索引语法+使用场景

MySQL索引语法 建表时添加索引 建表同时建立单索引 CREATE TABLE t_user1(id INT , userName VARCHAR(20), PASSWORD VARCHAR(20) ...

MySQL创建索引语法

1.介绍: 所有mysql索引列类型都可以被索引,对来相关类使用索引可以提高select查询性能,根据mysql索引数,可以是最大索引与最小索引,每种存储引擎对每个表的至少支持16的索引.总索引长度为 ...

MySQL ALTER语法的运用方法 && 操作索引和字段

语法:alter_specification: ADD [COLUMN] create_definition [FIRST | AFTER column_name ] or ADD INDEX [in ...

MySQL 通过多个示例学习索引

最近在准备面试,关于索引这一块,发现很多以前忽略的点,这里好好整理一下 首先为什么要建立索引 一本书,有章.节.段.行这种单位. 如果现在需要找一个内容:第9章>第2节>第3段>第4 ...

MySQL索引与Index Condition Pushdown(employees示例)

实验 先从一个简单的实验开始直观认识ICP的作用. 安装数据库 首先需要安装一个支持ICP的MariaDB或MySQL数据库.我使用的是MariaDB 5.5.34,如果是使用MySQL则需要5.6版 ...

索引-mysql索引创建、查看、删除及使用示例

mysql索引创建.查看.删除及使用示例 1.创建索引: ALTER TABLE用来创建普通索引.UNIQUE索引或PRIMARY KEY索引. ALTER TABLE table_name ADD ...

mysql 索引2

/* 所有MySQL列类型可以被索引.根据存储引擎定义每个表的最大索引数和最大索引长度. 所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节.大多数存储引擎有更高的限制. 索引的存储类型 ...

MySQL索引分析与优化

1.MySQL能够在name的索引中查找“Mike”值,然后直接转到数据文件中相应的行,准确地返回该行的 peopleid(999).在这个过程中,MySQL只需处理一个行就可以返回结果.如果没有“n ...

mysql索引之四(索引使用注意规则：索引失效--存在索引但不使用索引)

但是如果是同样的sql如果在之前能够使用到索引,那么现在使用不到索引,以下几种主要情况: 1. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全 ...

随机推荐

【JBOSS】User not found SA

启动JBOSS 发现报User not found: SA 错误, 找了老半天才找到处理方法,异常日志如下: java.sql.SQLException: User not found: SA at ...

javascript中的内置对象总结

内置对象 标准内置对象 Object Object.create Object.prototype.toString Object.prototype.hasOwnProperty Boolean S ...

SQL Server 存储过程遇到“表 '#TT' 没有标识属性。无法执行 SET 操作”错误

创建临时表,往临时表插入数据的时候报的错误. 一开始提示没有打开主键,后来打开主键就提示上述错误异常. 从网上查找资料没有找到,然后又到群里问各位大牛,一位大牛告诉我是没有设置主键. 我又仔细看看提示 ...

Windows Internals学习笔记(七)Image Loader

参考资料: 1. 2. Fibers 知识点: ● 当一个进程在系统上启动时,内核将创建一个进程对象来代表它,并执行各种内核相关的初始化任务.然而, ...

Android 开发错误信息001

Error:Execution failed for task ':app:dexDebug'. > com.android.ide.common.process.ProcessExceptio ...

Oracle INV - SO line backorder API

--Sales Order Lines to backorder API--===================================--SET serveroutput on size ...

[转载] python+Eclipse+pydev环境搭建

转自:http://www.cnblogs.com/Bonker/p/3584707.html 编辑器:Python 自带的 IDLE 简单快捷, 学习Python或者编写小型软件的时候.非常有用. ...

BC 65 ZYB's Premutation (线段树+二分搜索)

题目简述:有一个全排列,一直每个前缀区间的逆序对数,还原这个排列. fi记录逆序对数,pi记录该位置数值,则k=fi-f(i-1)表示前i-1个数比pi大的数的个数,那么只要在剩余元素求出按大小顺序第 ...

python3.4 data type

#coding=utf-8 #Python 3.4 https://docs.python.org/3.4/library/ #IDE:Eclipse +PyDev Window10 import a ...

JDBC常见面试题

以下我是归纳的JDBC知识点图: 图上的知识点都可以在我其他的文章内找到相应内容. JDBC常见面试题 JDBC操作数据库的步骤 ? JDBC操作数据库的步骤 ? 注册数据库驱动. 建立数据库连接. ...