成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

Mysql索引概述（用于個(gè)人學(xué)習(xí)與回顧）

MySQL索引概述

專(zhuān)注于為中小企業(yè)提供網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)瑪多免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了上1000家企業(yè)的穩(wěn)健成長(zhǎng)，幫助中小企業(yè)通過(guò)網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

索引介紹

  • 索引是對(duì)記錄集的多個(gè)字段進(jìn)行排序的方法。

  • 類(lèi)似于書(shū)的目錄

  • 索引類(lèi)型包括：Btree,B+tree,hash

索引優(yōu)缺點(diǎn)

  • ?索引優(yōu)點(diǎn)

    -通過(guò)創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫(kù)中每一行數(shù)據(jù)的唯一性

    -可以加快數(shù)據(jù)的檢索速度

  • 索引缺點(diǎn)

    -當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加，刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，降低數(shù)據(jù)的維護(hù)速度

    -索引需要占物理空間



MySQL鍵值類(lèi)型

鍵值類(lèi)型

  • INDEX：普通索引

  • UNIQUE: 唯一索引

  • FULLTEXT: 全文索引

  • PRIMARY KEY: 主鍵

  • POREIGN KEY: 外鍵

INDEX普通索引

  • 使用說(shuō)明

    -一個(gè)表中可以有多個(gè)INDEX字段

    -字段的值允許有重復(fù)，且可以賦予NULL值

    -經(jīng)常把做查詢(xún)條件的字段設(shè)置為INDEX字段

    -INDEX字段的key標(biāo)志位MUL

  • 建表指定索引字段

    -index(字段1),index(字段2)...

mysql>?create?table?test1(
????->?id?char(6)?not?null,
????->?name?varchar(4)?not?null,
????->?age?int(3)?not?null,
????->?gender?enum('male','female'),
????->?index(id),index(name)
????->?);
Query?OK,?0?rows?affected?(0.06?sec)
mysql>?desc?test1;
+--------+-----------------------+------+-----+---------+-------+
|?Field??|?Type??????????????????|?Null?|?Key?|?Default?|?Extra?|
+--------+-----------------------+------+-----+---------+-------+
|?id?????|?char(6)???????????????|?NO???|?MUL?|?NULL????|???????|
|?name???|?varchar(4)????????????|?NO???|?MUL?|?NULL????|???????|
|?age????|?int(3)????????????????|?NO???|?????|?NULL????|???????|
|?gender?|?enum('male','female')?|?YES??|?????|?NULL????|???????|
+--------+-----------------------+------+-----+---------+-------+
4?rows?in?set?(0.00?sec)

  • 在已有的表中設(shè)置index字段

    -create index 索引名 on表名（字段名）；

mysql>?create?index?age?on?test1(age);
Query?OK,?0?rows?affected?(0.02?sec)
Records:?0??Duplicates:?0??Warnings:?0
mysql>?desc?test1;
+--------+-----------------------+------+-----+---------+-------+
|?Field??|?Type??????????????????|?Null?|?Key?|?Default?|?Extra?|
+--------+-----------------------+------+-----+---------+-------+
|?id?????|?char(6)???????????????|?NO???|?MUL?|?NULL????|???????|
|?name???|?varchar(4)????????????|?NO???|?????|?NULL????|???????|
|?age????|?int(3)????????????????|?NO???|?MUL?|?NULL????|???????|
|?gender?|?enum('male','female')?|?YES??|?????|?NULL????|???????|
+--------+-----------------------+------+-----+---------+-------+
4?rows?in?set?(0.00?sec)

  • 刪除指定表的索引字段

    -drop index 索引名 on 表名；

mysql>?drop?index?name?on?test1;
Query?OK,?0?rows?affected?(0.34?sec)
Records:?0??Duplicates:?0??Warnings:?0
mysql>?desc?test1;
+--------+-----------------------+------+-----+---------+-------+
|?Field??|?Type??????????????????|?Null?|?Key?|?Default?|?Extra?|
+--------+-----------------------+------+-----+---------+-------+
|?id?????|?char(6)???????????????|?NO???|?MUL?|?NULL????|???????|
|?name???|?varchar(4)????????????|?NO???|?????|?NULL????|???????|
|?age????|?int(3)????????????????|?NO???|?????|?NULL????|???????|
|?gender?|?enum('male','female')?|?YES??|?????|?NULL????|???????|
+--------+-----------------------+------+-----+---------+-------+
4?rows?in?set?(0.00?sec)

  • 查看表的索引信息

    -show index from 表名；

mysql>?show?index?from?test1\G;
***************************?1.?row?***************************
????????Table:?test1
???Non_unique:?1
?????Key_name:?id
?Seq_in_index:?1
??Column_name:?id
????Collation:?A
??Cardinality:?0
?????Sub_part:?NULL
???????Packed:?NULL
?????????Null:?
???Index_type:?BTREE
??????Comment:?
Index_comment:?
***************************?2.?row?***************************
????????Table:?test1
???Non_unique:?1
?????Key_name:?age
?Seq_in_index:?1
??Column_name:?age
????Collation:?A
??Cardinality:?0
?????Sub_part:?NULL
???????Packed:?NULL
?????????Null:?
???Index_type:?BTREE
??????Comment:?
Index_comment:?
2?rows?in?set?(0.00?sec)

primary key主鍵

  • 基本概念

    -一個(gè)表中只能有一個(gè)primary key 字段

    -對(duì)應(yīng)的字段值不允許有重復(fù)，且不允許賦NULL值

    -如果有多個(gè)字段都作為primary key，稱(chēng)為復(fù)合主鍵，必須一起創(chuàng)建

    -主鍵字段的KEY標(biāo)志是PRI

    -通常與AUTO_INCREMENT 連用

    -經(jīng)常把表中能夠唯一標(biāo)識(shí)記錄的字段設(shè)置為主鍵字段【記錄編號(hào)字段】

  • 建表的時(shí)候指定主鍵字段

    -primary key(字段名)

mysql>?create?table?test2(
????->?id?int(3)?auto_increment,
????->?name?varchar(4)?not?null,
????->?age?int(2)?not?null,
????->?primary?key(id)
????->?);
Query?OK,?0?rows?affected?(0.05?sec)
mysql>?desc?test2;
+-------+------------+------+-----+---------+----------------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra??????????|
+-------+------------+------+-----+---------+----------------+
|?id????|?int(3)?????|?NO???|?PRI?|?NULL????|?auto_increment?|
|?name??|?varchar(4)?|?NO???|?????|?NULL????|????????????????|
|?age???|?int(2)?????|?NO???|?????|?NULL????|????????????????|
+-------+------------+------+-----+---------+----------------+
3?rows?in?set?(0.00?sec

  • 在已有的表中設(shè)置primary key字段

    -alter table 表名 add primary key（字段名）；

mysql>?desc?test2;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?int(3)?????|?NO???|?????|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?????|?NULL????|???????|
|?age???|?int(2)?????|?NO???|?????|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.01?sec)
mysql>?alter?table?test2?add?primary?key(name);
Query?OK,?0?rows?affected?(0.05?sec)
Records:?0??Duplicates:?0??Warnings:?0
mysql>?desc?test2;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?int(3)?????|?NO???|?????|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?PRI?|?NULL????|???????|
|?age???|?int(2)?????|?NO???|?????|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.00?sec)

  • 移除表中的PRIMARY KEY字段

    -alter table 表名 drop primary key;(如果有auto_increment則需要先移除再移除主鍵，移除auto_incrememt為對(duì)該字段type重新賦值)

ysql>?desc?test2;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?int(3)?????|?NO???|?PRI?|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?????|?NULL????|???????|
|?age???|?int(2)?????|?NO???|?????|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.00?sec)
mysql>?alter?table?test2?drop?primary?key;
Query?OK,?0?rows?affected?(0.31?sec)
Records:?0??Duplicates:?0??Warnings:?0
mysql>?desc?test2;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?int(3)?????|?NO???|?????|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?????|?NULL????|???????|
|?age???|?int(2)?????|?NO???|?????|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.01?sec)

foreign key 外鍵

  • 基本概念

    -讓當(dāng)前表字段的值在另一個(gè)表中字段值得范圍內(nèi)選擇。

  • 使用外鍵的條件

    -表的存儲(chǔ)引擎必須是innodb

    -字段類(lèi)型要一致

    -被參照字段必須要是索引類(lèi)型的一種（primary key）

  • 基本用法

mysql>?create?table?yuangong(
????->?yg_id?int(4)?auto_increment,
????->?name?char(16)?not?null,
????->?primary?key(yg_id)
????->?);
Query?OK,?0?rows?affected?(0.31?sec)
mysql>?create?table?gongzi(
????->?gz_id?int(4)?not?null,
????->?name?char(15)?not?null,
????->?gz?float(6,2)?not?null?default?0,
????->?index(name)，
????->?foreign?key(gz_id)?references?yuangong(yg_id)
????->?on?update?cascade?on?delete?cascade
????->?);
Query?OK,?0?rows?affected?(0.32?sec)

  • 刪除外鍵字段

    -alter table 表名 drop foreign key 約束名；

mysql>?show?create?table?gongzi\G;
***************************?1.?row?***************************
???????Table:?gongzi
Create?Table:?CREATE?TABLE?`gongzi`?(
??`gz_id`?int(4)?NOT?NULL,
??`name`?char(15)?NOT?NULL,
??`gz`?float(6,2)?NOT?NULL?DEFAULT?'0.00',
??KEY?`name`?(`name`),
??KEY?`gz_id`?(`gz_id`),
??CONSTRAINT?`gongzi_ibfk_1`?FOREIGN?KEY?(`gz_id`)?REFERENCES?`yuangong`?(`yg_id`)?ON?DELETE?CASCADE?ON?UPDATE?CASCADE
)?ENGINE=InnoDB?DEFAULT?CHARSET=latin1
1?row?in?set?(0.01?sec)

mysql>?alter?table?gongzi?drop?foreign?key?gongzi_ibfk_1;
Query?OK,?0?rows?affected?(0.01?sec)
Records:?0??Duplicates:?0??Warnings:?0

mysql>?show?create?table?gongzi\G;
***************************?1.?row?***************************
???????Table:?gongzi
Create?Table:?CREATE?TABLE?`gongzi`?(
??`gz_id`?int(4)?NOT?NULL,
??`name`?char(15)?NOT?NULL,
??`gz`?float(6,2)?NOT?NULL?DEFAULT?'0.00',
??KEY?`name`?(`name`),
??KEY?`gz_id`?(`gz_id`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=latin1
1?row?in?set?(0.00?sec)

UNIQUE索引

  • 基本概念

    -UNIQUE表示唯一性，同一個(gè)字段可以有多個(gè)字段具有唯一性

  • 創(chuàng)建表時(shí)指定UNIQUE索引字段

    查看新建test3表的字段結(jié)構(gòu)，可發(fā)現(xiàn)UNIQUE字段的KEY標(biāo)志為UNI；另外，由于字段name必須滿(mǎn)足“NOT NULL”的非空約束，所以將其設(shè)置為UNIQUE后會(huì)自動(dòng)變成了PRIMARY KEY主鍵字段：

mysql>?create?table?test3(
????->?id?char(6),
????->?name?varchar(4)?not?null,
????->?age?int(3)?not?null,
????->?unique(id),unique(name),index(age)
????->?);
Query?OK,?0?rows?affected?(0.36?sec)
mysql>?desc??test3;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?char(6)????|?YES??|?UNI?|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?PRI?|?NULL????|???????|
|?age???|?int(3)?????|?NO???|?MUL?|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.00?sec)

  • 刪除UNIQUE索引，在已有的表中設(shè)置UNIQUE索引字段

mysql>?drop?index?name?on?test3;
Query?OK,?0?rows?affected?(0.07?sec)
Records:?0??Duplicates:?0??Warnings:?0
mysql>?desc?test3;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?char(6)????|?YES??|?UNI?|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?????|?NULL????|???????|
|?age???|?int(3)?????|?NO???|?MUL?|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.00?sec)

  • 在已有表中建立UNIQUE索引

mysql>?create?unique?index?name?on?test3(name);
Query?OK,?0?rows?affected?(0.06?sec)
Records:?0??Duplicates:?0??Warnings:?0
mysql>?desc?test3;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?char(6)????|?YES??|?UNI?|?NULL????|???????|
|?name??|?varchar(4)?|?NO???|?PRI?|?NULL????|???????|
|?age???|?int(3)?????|?NO???|?MUL?|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.00?sec)










當(dāng)前文章：Mysql索引概述（用于個(gè)人學(xué)習(xí)與回顧）
URL鏈接：http://jinyejixie.com/article8/jjjpop.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、建站公司、定制網(wǎng)站、移動(dòng)網(wǎng)站建設(shè)、網(wǎng)站維護(hù)、云服務(wù)器

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

h5響應(yīng)式網(wǎng)站建設(shè)
孟州市| 厦门市| 乌什县| 都昌县| 炉霍县| 长治市| 霍林郭勒市| 富裕县| 漳平市| 仙游县| 卫辉市| 林甸县| 天气| 台中市| 保德县| 泊头市| 漾濞| 宾川县| 汨罗市| 宣恩县| 张家口市| 兰州市| 唐河县| 双江| 恩平市| 洞头县| 曲麻莱县| 重庆市| 托克托县| 营山县| 离岛区| 清河县| 安丘市| 湘潭县| 灵山县| 酒泉市| 凉城县| 阳曲县| 嵊州市| 商城县| 开远市|