目录
索引
索引用于快速找出在某个烈中有一特定值的行。不使用索引,就必须从第一条记录开始读完整个表,知道找出相关的行。数据越多,查找的时间越多。如果表中查询的列有一个索引,那么就能快速到达某个位置去搜寻数据文件,而不必查找所有的数据。例如,数据库有2万条记录,要执行这样一个查询:select * from table where num=1000,那么就要遍历整个表寻找这一行,但如果在num列上创建一个索引,那么MySQL不需要任何扫描,直接在索引里面找到1000,速度将会大大提高。
索引分类:普通索引和唯一索引、单列索引和组合索引、全文索引、空间索引
创建索引
创建表时创建索引
使用create table 创建表时,除了可以定义列的数据类型外,还可以定义主键约束、外键约束或者唯一性约束。在定义约束的同时就相当于在指定列上创建了一个索引。
- 创建普通索引。只需要在创建表时,在后面加上
index(列名)
即可 - 创建唯一索引,主要原因是减少查询索引列操作的执行时间,尤其是对比较庞大的数据表。只需在创建表时在后面加上
unique index uniqidx(列名)
即可 - 创建单列索引,是在单个字段上创建的索引,一个表可以创建多个单列索引,只需要在创建表时,在后面加上
index(列名)
即可 - 创建组合索引。只需要在创建表时,在后面加上
index(列名1,列名2,...)
即可 - 创建全文索引,可用于全文搜索,只有MyISAM存储引擎支持fulltext索引,并且只为char、varchar、text列创建索引。只需要在创建表时,在后面加上
fulltext index(列名)
即可,引擎记得在创建表之后加上engine=MyISAM;
- 创建空间索引,必须在MyISAM类型的表创建,且字段必须非空,只需要在创建表时,在后面加上
spatial index(列名)
即可,引擎记得在创建表之后加上engine=MyISAM;
在已经存在的表中创建索引
使用alter table 语句创建
语法规则:alter table 表名 add [unique|fulltext|spatial] [index] [索引名字] (列名)
使用create index语句创建
语法规则:create [unique|fulltext|spatial] index 索引名 on 表名 (列名)
删除索引
- 使用alter table删除:
alter table 表名 drop Index 索引名
- 使用drop index删除:
drop index 索引名 on 表名
存储过程与函数
存储程序包括存储过程和存储函数。MySQL创建存储过程和函数使用的语句分别是:create procedure 和create function。
创建存储过程和函数
创建存储过程
语法规则:
create procedure 存储过程的名字 (参数) begin 要执行的语句; end;
参数包括 in|out|inout。
下面就是创建了一个查看fruits表的存储过程,每次调用这个存储过程就会执行select语句查看表的内容。括号里没有过程的参数,但仍然需要括号:
注:利用delimiter将结束符号变成//,以避免跟默认的结束符;冲突。再利用delimiter ; 就可以变回了分号。
创建存储函数
语法规则:
create function 存储函数的名字 (参数) returns 返回数据的类型,如char(50) return (要返回值而需执行的语句);
参数包括 in|out|inout。
下面创建了一个存储函数namezip,参数定义为空,返回一个int类型的结果,代码如下:
注:指定参数为in|out|inout只对procedure是合法的,function 总是默认为in参数。returns 子句只能对function指定,对函数而言是强制的。
调用存储过程和函数
调用存储过程
存储过程用call语句进行调用
语法:call 存储过程的名称 (存储过程对应的参数赋值)
调用存储函数
存储函数用select语句进行调用
语法:select 存储函数的名称 (存储函数对应的参数赋值)
查看存储过程和函数
使用show create 语句查看存储过程和函数的定义
show create {function|procedure} 名字
使用show status 语句查看存储过程和函数的状态
show {procedure|function} status like '过程或函数的名称'
information_schema.Routines表中查看存储过程和函数的信息
MySQL中存储过程和函数的信息都在information_schema数据库下的Routines表中。
语法规则:select * from information_schema.Routines where ROUTINE_NEMA=''sp_name
修改存储过程和函数
语法规则:alter procedure|function 名字 [特性]
特性一般包括:
例如:修改存储过程countproc的定义,将读写权限改为modifie sql data,并指明调用者可以执行,代码如下:
删除存储过程和函数
使用drop:drop [procedure|function] 名字