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");

mysql> insert into t221 values(6,"haha",21,"nsd1702","haha@qq.com");

mysql> insert into t221 values(null,"haha",21,"nsd1702","haha@qq.com");

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)

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