mysql 索引mul_mysql索引

索引的优点缺点

优点:加快查询记录的速度

缺点：占用物理存储空间，会减慢写速度(update delete  insert)

mySQL索引支持的类型

index 普通索引

unique 唯一索引

primary  key 主键

foreign  key 外键

fulltext  全文索引

索引的使用

index 普通索引

primary  key 主键

foreign  key 外键

查看表中是否有索引字段：

desc   表名;

查看索引详细信息：

show index from 表名\G;  (\G：每行独立显示)

普通索引 index 使用规则

1.一个表中可以有多个INDEX字段

2.字段的值允许有重复，切可以赋NULL值

3.经常把做查询条件的字段设置为INDEX字段

4.INDEX字段的KEY标志是MUL

例题：

mysql> create table t26(

-> name char(10),

-> age int(2),

-> class char(7)default "nsd1724",

-> homedir varchar(20),

-> index(name),

-> index(gamedbclass)

-> );

mysql>inser tinto t26 values("bob",21,"nsd1702","shanghai");

mysql> insert into t26 values("bob",21,"nsd1703","beijing");

mysql> insert into t26 values("lucy",21,NULL,"beijing");

mysql> show index from t26\G;

在已有表里创建

create  index    索引名  on   表名(字段名)；

create index  aaa on  t16(name);

索引名可以与字段名同名，

删除索引

drop  index    索引名  on   表名;

drop  index   aaa on  t16;

primary  key 主键 使用规则

一个表中只能有一个primary  key字段

对应的字段值不允许有重复，且不允许赋NULL值

如果有多个字段都作为PRIMARY KEY，称为复合主键，必须一起创建。

通常与 AUTO_INCREMENT 连用(自增长：做自加1++的运算 的值给)

经常把表中能够唯一标识记录的字段设置为主键字段[记录编号字段]

主键字段的KEY标志是PRI

方法一：mysql> create table t27(

-> stu_id char(7),

-> name char(10),

-> age int(2),

-> primary key(stu_id)

-> );

方法二：mysql> create table t28(

-> stu_id char(7) primary key,

-> name char(10),

-> age int(2),

-> );

在已有表创建主键

alter  table   表名  add  primary key(字段名)；

alter  table   t16  add   primary key(name);

删除主键

alter  table   表名 drop  primary key;

alter  table   t16  drop   primary key;

复合主键 (做主健的值不能同时相同)

多个字段必须同时做主键，多个字段值不同时相同重复就可以。

例题：

PRI         PRI

cip         por      access

clientip    serport     status

1.1.1.1       21          deny

1.1.1.1       21          allow

2.1.1.1       80          deny

mysql> create  table t29(

-> cip varchar(15),

-> port smallint(2),

-> access enum("allow","deny"),

-> primary key(cip,port)

-> );

mysql>insert into t29 values("1.1.1.1",21,"deny");

mysql>insert into t29 values("1.1.1.1",23,"allow");

mysql>insert into t29 values("2.1.1.1",23,"deny");

mysql> select * from t29;

在已有表里创建复合主键

alter table  t24 add primary key(字段名列表);

默认从0开始加1  的结果赋给第一个字段，自己赋值不能赋给已用的值，从最大值自动加1去赋值。

primary key  与 auto_increment  一起使用

自动增长 ++

数值类型

主键

例题：

id  name age   class   maill

1   jim   21  nsd1704   jim@qq.com

2   tom   29  nsd1704   tom@qq.com

mysql> create table t221(

-> id int(2) zerofill primary key

-> auto_increment,

-> name char(5),

-> age int(2),

-> class char(7),

-> mail varchar(30)

-> );

mysql>insert into t221(name,age,class,mail) values("jim",21,"nad1704","jim@qq.com");

mysql>insert into t221(name,age,class,mail) values("tom",29,"nad1702","tom@qq.com");

mysql>insert into t221(name,age,class,mail) values("xixi",20,"nsd1704","xixi@qq.cim");

Null #空可以写进去是因为他可以自动增长排序。

mysql> select * from t221;

unique 唯一索引使用规则 (主健)

一个表中可以有多个UNIQUE字段

对应的字段值不允许有重复

UNIQUE字段的KEY标志是UNI

UNIQUE字段的值允许为NULL，当将其修改为不允许为NULL，则此字段限制与主键相同.(但是他并不是主键,删除不能用主键的删除方法。)

例题：

姓名     护照      驾驶证

NULL   NULL

jim       xxxx     cccc

bob     xxxx       cccc

建表是创建unique

mysql> create table t222(

-> name char(10),

-> sf_id char(18),

-> js_id char(8),

-> unique(sf_id),

-> unique(js_id)

-> );

mysql> insert into t222 values("bob","123456789","66666");

mysql> insert into t222 values("tom","4567891323","45679");

mysql> insert into t222 values("hshs","","");

mysql> insert into t222 values("hshs",null,null);

在已有表里创建unique

create  unique index  索引名  on 表名(字段名);

例题：

create  unique index  sh_id  on t27(sh_id)

删除unique索引

drop   index   索引名 on  表名;

外键foreign key 使用规则

1 表的存储引擎必须是innodb

2 字段的类型必须匹配

3 被参考字段必须是索引(key)的一种(通常是primary key主健)

外键的功能：给当前表的字段赋值时，只能在另外一张表的中字段值的范围里做选择。

例题：必须先要创建财务表。

cwb财务表

cwb_id     name     pay

1        tom      2W

2        jerry    2W

3        unll       2W

mysql> create table cwb(

-> cwb_id int(2) primary key

-> auto_increment,

-> name char(10),

-> pay float(7,2)

-> )engine=innodb;

班级表

stu_id    name

1        tom

2        jerry

3         unll

mysql> create table bjb(

-> bjb_id int(2),

-> name char(10),

-> foreign key(bjb_id) references cwb (cwb_id) on update cascade on delete cascade

-> )engine=innodb;

foreign key(bjb_id)     #为班级的id创建外健。

references cwb (cwb_id)  # 参考的是财务表的id。

on update cascade        #于财务表的信息同步更新。为了保持信息一致

on delete cascade        #于财务表的信息同步删除。

mysql>insert into  cwb(name,pay) values("bob",20000),("tom",20000),("lucy",20000);

mysql> select * from cwb;

mysql> insert bjb values(3,"lucy");

mysql> insert bjb values(1,"bob");

mysql> insert bjb values(2,"tom");

mysql> insert bjb values(null,"tom");

#null是没有值，相当于没有写，没有限制不允许写空值。

只要在财务表的范围里都可以写入，无论重复不重复。

同步更新：

mysql> update cwb set cwb_id=8 where cwb_id=2;

#把原来财表里的id=2 修改成id=8.验证同步。

mysql> select * from bjb;

同步删除一条表记录：

mysql> delete from cwb where cwb_id=8;

Delete  from cwd; #如果不加条件删除全部班级里的内容也会

删除外键、查看外键名：

查看格式： show create table 表名;(创建外键的表名)

删除格式：

alter table 表名 drop foreign key 约束名;

(创建外键的表名)

例题：

mysql> show create table bjb;

mysql> alter table bjb drop foreign key bjb_ibfk_1;

mysql> show create table bjb;

在已有表里添加外键

格式：alter table (表名) add foreign key(列名)    references cwb(列名) on update cascade  on delete cascade;

备注：创建外键的表名，创建外健的列名，参考的列名。

例题：

mysql> alter table bjb

-> add

-> foreign key(bjb_id)references cwb(cwb_id)

-> on update cascade on delete cascade;

mysql> show create table bjb;

解释： foreign key(bjb_id)references cwb(cwb_id)

#指定给哪列作外键，参考的对象是谁。