欧美一级特黄大片做受成人-亚洲成人一区二区电影-激情熟女一区二区三区-日韩专区欧美专区国产专区

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

MySQL索引概述

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

索引介紹

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

  • 類似于書的目錄

  • 索引類型包括：Btree,B+tree,hash

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

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

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

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

  • 索引缺點(diǎn)

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

    -索引需要占物理空間



MySQL鍵值類型

鍵值類型

  • INDEX：普通索引

  • UNIQUE: 唯一索引

  • FULLTEXT: 全文索引

  • PRIMARY KEY: 主鍵

  • POREIGN KEY: 外鍵

INDEX普通索引

  • 使用說明

    -一個表中可以有多個INDEX字段

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

    -經(jīng)常把做查詢條件的字段設(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主鍵

  • 基本概念

    -一個表中只能有一個primary key 字段

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

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

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

    -通常與AUTO_INCREMENT 連用

    -經(jīng)常把表中能夠唯一標(biā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為對該字段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)前表字段的值在另一個表中字段值得范圍內(nèi)選擇。

  • 使用外鍵的條件

    -表的存儲引擎必須是innodb

    -字段類型要一致

    -被參照字段必須要是索引類型的一種（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表示唯一性，同一個字段可以有多個字段具有唯一性

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

    查看新建test3表的字段結(jié)構(gòu)，可發(fā)現(xiàn)UNIQUE字段的KEY標(biāo)志為UNI；另外，由于字段name必須滿足“NOT NULL”的非空約束，所以將其設(shè)置為UNIQUE后會自動變成了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索引概述（用于個人學(xué)習(xí)與回顧）
URL鏈接：http://aaarwkj.com/article8/jjjpop.html

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

廣告

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

h5響應(yīng)式網(wǎng)站建設(shè)
日韩黄色成人在线观看| 亚洲天堂av在线有码| 国产欧美激情一区二区| 国产午夜福利不卡在线观看| 中文字幕成人在线电影| 人体艺术日韩色噜噜| 国产精品一区二区高潮| 国产精品1区2区久久久| 亚洲综合激情另类专区| 青青草日韩视频在线观看 | 夫妻在线观看高清视频| 大片天天看菲色亚洲黄色| 亚洲性感美女男人的天堂| 国产精品传媒在线观看网站| 国产黄色片子在线观看| 夫妻在线观看高清视频| 侵犯人妻中文字幕一区二区| 夜夜嗨av一区二区三区| 色婷婷亚洲婷婷亚洲最大| 综合激情四射亚洲激情| 亚洲一区二区三区熟妇| 丰满少妇一级淫片在线播放| 日本在线电影一区二区三区| 日本女优久久精品观看| 99热精品成人免费观看| 精品一区2区3区4区| 妞妞婷婷基地五月天| 日韩欧美国产精品福利| 国产精品久久久99| 青青青久热国产精品视频| 日韩三级在线观看av| av在线免费观看美日韩| 一区二区欧美日韩91| 亚洲 欧美 日韩一区| 亚洲码av一区二区三区| 成人爱爱免费观看视频| 少妇人妻系列中文在线| 麻豆精品国产一区二区91| 日本岛国一区二区在线| 日本2区3区视频在线观看| 国产黄色av片免费|