五、MySQL索引和鍵

標籤：mysql索引和鍵

MySQL索引和鍵　  
 (不同的索引有不同功能 ，不同的約束方式，不同的使用規則)
優點:對一張表來說，索引就像一本書的目錄，能夠加快查詢速度
缺點:佔用實體儲存體空間 （索引資訊儲存在表對應的檔案裡）
     會降低插入、更新表記錄的速度（insert   delete   update）
1.索引的類型
普通索引：index        
唯一索引：unique
全文索引：fulltext  

2.各個索引的說明
(1).index 普通索引
一個表中可以有多個INDEX欄位
把經常做查詢條件的欄位設定為INDEX欄位   
INDEX欄位的KEY標誌是MUL
對應的欄位值允許有重複
範例1：
建表時設定索引欄位。（預設索引字與欄位名相同）
create   table   tea(
name  char(10) not null,
age  int(3) not null,
index(name)
);
範例2：
把已有表裡的欄位設定為index欄位。
create   index  索引名  on  表名(欄位名);
create  index   name  on  stu_info(name);
create  index   name  on  stu_info(name,sex);
刪除指定表的索引欄位
drop  index  索引名  on  表名;
drop  index  name  on  stu_info;
註：index使用BTREE  演算法   （二叉樹演算法）
查看錶的索引資訊:show  index  from stu_info;

(2)unique 唯一索引   
欄位值不允許有重複，UNIQUE欄位的值允許為NULL，【當將其修改為不允許為NULL，則此欄位限制與主鍵相同】
一個表中可以有多個UNIQUE欄位
UNIQUE欄位的KEY標誌是UNI
範例：
create  table  tea23(
id int(3),
name char(3),
unique(id)
);

create unique index  stu_id   on   stu_info(stu_id);
3.鍵
主    鍵：primary  key
外    鍵：foreign   key
(1). primary   key   
一個表中只能有一個PRIMARY欄位
欄位值不允許有重複,不允許為null
主鍵欄位的KEY標誌是PRI
如果有多個欄位都作為PRIMARY KEY，稱為複合主鍵，必須在建表時一起建立
通常與 AUTO_INCREMENT 連用 (欄位的值自動成長 +)
把能唯一定位一條記錄的欄位設定為主鍵欄位
範例：
create table baitao(lf int(3),primary key(lf));
alter table  stu_info add  primary key(id)
alter   table  表名   drop    primary  key;

(2).foreign    key   * 外      鍵  
欄位類型要一致
表的儲存引擎必須是innodb
被參考欄位必須是索引類型中的一種。

FOREIGN  KEY(欄位名)   References  表B(欄位名)  ON  UPDATE CASCADE
    ON  DELETE  CASCADE

範例:
create   table  yg_info(yg_id  int(3) primary key  auto_increment,name  varchar(15) not null,index(name))engine=innodb;
create  table  gz_tab2(
id  int(3) primary  key   auto_increment,
name   varchar(15) not null,
gz_id   int(3)   not  null,
gz  float(7,2),
FOREIGN  KEY(gz_id)    References  yg_info(yg_id)
on update cascade   on  delete  cascade
)engine=innodb;
insert into  yg_info(name)values("jim");
insert into  yg_info(name)values("tom");
insert  into  gz_tab(name,gz_id,gz)values("jim",1,20000);
insert  into  gz_tab(name,gz_id,gz)values("tom",2,20000);


update   yg_info  set  yg_id=20  where name="tom";
delete from yg_info where yg_id=20;

(3).primary key 複合主鍵
(主鍵欄位的值 不可以同時相同 )
                   
ip             ser_name     port     status
1.1.1.1        vsftpd        21        deny
1.1.1.1        sshd          22        allow
2.2.2.2        sshd          22        allow
1.1.1.10        sshd         22        deny

create  table   sertab(
ip    varchar(15),
ser_name  varchar(15),
port   int(3),
status  enum("deny","allow")  default "deny",
primary key(ip,port)
);

本文出自 “劉福” 部落格，請務必保留此出處http://liufu1103.blog.51cto.com/9120722/1656829

五、MySQL索引和鍵