MySQL数据库(五):索引

一.什么是索引
相当于一本数的目录

二.使用索引的好处和坏处
好处:加快查找速度
坏处:占用磁盘空间
三.查看索引命令
命令格式:

show index from  表名;

四.索引的算法

Index_type: BTREE (二叉树)
Column_name: User
Key_name: PRIMARY

五.索引的种类

1. index普通索引(务必掌握)
1.1 使用场景:通常表表中最为查询条件的字段设置为索引字段(性别 班级 名字)

1.2 使用规则
-字段值可以重复
-一个表中可以有多个Index字段
-字段的值可以为null

1.3 普通索引在表中的标识:MUL

1.4 设置普通索引的命令格式

添加index索引

㈠把表中已有字段设置为index字段

mysql> create index 索引名 on 表名(字段名);
mysql> create index 索引名 on 表名(字段名1,字段名2);

例子:
把t25表中的newname字段设置为index索引,索引名叫newname

mysql> create index  newname on t25(newname);

㈡建表时,创建index字段

create table  t26(
name  varchar(15),
age  int(2),
sex  enum("boy","girl"),
index(name),
index(sex)
);

删除index索引

删除字段的Index属性
格式:

drop  index  索引名  on  表名;
drop  index  newname  on  t25;

2.unique唯一索引

1.1使用规则
-字段的值不允许重复,
-字段值可以为null;
-一个表中可以有多个unique字段
-当把字段设置为不允许为null时,约束方式和主键一样。
1.2 唯一索引在表中的标识: UNI

1.3 设置唯一索引的命令格式
㈠建表时,创建unique字段

create table  t27(
tel   char(11),
mail  varchar(50) not null,
name  varchar(15),
age  int(2),
sex  enum("boy","girl"),
unique(tel),
unique(mail)
);

㈡把表中已有字段设置为unique字段

mysql> create unique index 索引名 on t27(字段名);
mysql> create unique index  mail on t27(mail);

1.4 删除字段的unique属性

drop  index  索引名  on  表名;
drop  index  newname  on  t25;

3.primary 主键(务必账务)
1.1使用规则
-字段值不允许重复且不允许字段值为空
-且一个表中只能有一个primary字段
-通常和auto_increment 属性一起使用,让字段的值自动增长,每插入一条新记录时,字段的值就自动+1

1.2主键在表中的标识:PRI

1.3使用场景:通常把表中能够唯一定位到一条记录的字段设置为主键字段。比如记录的编号字段。

1.4 主键设置的命令格式
㈠建表时,创建primary key字段
方法1:

create  table   t28(
id  int(2),
name varchar(15),
age  int(2),
primary key(id)
);

方法2:

create  table   t29(
id  int(2) primary key,
name varchar(15),
age  int(2)
);

㈡把表中已有字段设置为primary key字段

mysql> alter table t22 add primary key(stu_id);

㈢添加新字段时,直接设置字段是主键且自动增长。

alter table  t30  add  id  int(2) primary key  auto_increment first;

例子:
给表 stuinfo 在所有字段的上方添加 id 字段 用来储存表中记录的编号 要求 字段的值不允许重复且不可以插入null值
并让id字段的值自动增长

mysql> alter table  t30 modify id int(2) not null auto_increment ;

创建表时 让字段的值自动增长

create   table   t29(
id int(2) primary key auto_increment,
stu_id  char(4),
name  varchar(15),
age  tinyint(2) unsigned not null,
index(name),
unique(stu_id)
);

1.5删除字段的primary key 属性

mysql> alter table t22 drop primary key;

4.复合主键(表中的多个字段同时做主键字段,必须再建表时创建)
-复合主键字段的值不同时重复就可以
例子:

create  table t31(
clientip  varchar(15),   
sername   varchar(20),
port      varchar(5),
stuatus   enum("allow","deny") not null default "deny",
primary key(clientip,port)
);

FOREIGN KEY(表A的字段名)
References 表B(字段名)
ON UPDATE CASCADE

ON DELETE CASCADE

5.foreign key 外键(务必掌握)
1.1 说明:让当前表中外键字段的值,只能在指定的另一个表中的某个字段值的范围内选择。

1.2外键使用规则?
-表的存储引擎必须是innodb
-字段的类型要匹配
-被参考字段必须是key中的一种(通常设置为primary key)

1.3 查看存储引擎

show  create table 表;

1.4指定表的存储集

mysql> create  table  t33(
    -> 姓名 varchar(30)
    -> )DEFAULT CHARSET=utf8;

1.5指定表的存储引擎

create table t33(
name varchar(30)
)engine=innodb DEFAULT CHARSET=utf8;

1.6 修改表的存储引擎(一般不要修改)

alter table t32 engine=innodb;

1.7 设置外键字段(例子)

create   table  yg_info(
yg_id int(2) primary key  auto_increment,
name  varchar(10)
);
create  table  gz_tab(
gz_id  int(2) not null,
gz  float(7,2),
foreign key(gz_id) references  yg_info(yg_id)  on update cascade  on delete cascade
);
insert into  yg_info(name)values("jim");
insert into gz_tab values (1,20000);
update  yg_info set yg_id=8 where yg_id=2;