本篇文章給大家帶來了關于mysql中鍵值的相關知識，其中包括了鍵值類型、索引介紹、索引優缺點以及mysql鍵值使用的相關問題，希望對大家有幫助。
推薦學習：mysql
mysql 鍵值概述
鍵值類型
-
根據數據存儲要求，選擇鍵值
-
index? ? ? ? ? ? ?? 普通索引
-
unique? ? ? ? ? ?? 唯一索引
-
fulltext? ? ? ? ? ?? 全文索引
-
primary key? ?? 主鍵
-
foreign key? ? ? 外鍵
-
索引介紹
-
索引是什么？
—— 類似于書的目錄
—— 對表中字段進行排序
—— 索引類型包括：Btree(二叉樹)、B+tree、hash
索引優缺點
-
索引有點
—— 通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性
—— 可以加快數據的查詢速度
-
索引缺點
—— 但對表中的數據進行增加、刪除和修改的時候，索引也要動態的調整，降低了數據的維護速度
—— 索引需要占物理空間
MySQL 鍵值使用
Index 普通索引
-
使用規則
—— 一個表中可以有多個 index 字段
—— 字段的值允許重復，且可以賦 NULL 值
—— 通常把做為查詢條件的字段設置為 index 字段
—— index 字段標志為 MUL
-
建表時創建索引
—— index(字段名), index(字段名) .. ..
mysql>?create?table?庫名.表名( ?????????????字段列表, ?????????????index(字段名), ?????????????index(字段名) ?????????????);
創建一個信息表，指定字段中的name和age作為索引（字段name和age中的值要進行排序）
mysql>?create?table?db2.t2( ->?name?char(10), ->?age?int, ->?class?char(7), ->?email?char(30), ->?index(name),index(age) ->?); mysql>?desc?db2.t2;
-
查看索引信息
-
基本查看
-
——?desc?庫名.表名;??????//查看key那一列
-
-
詳細查看
-
——?show?index?from?表名?G; mysql>?show?index?from?db2.t2?G?????//因為有兩個字段作為索引，所以下面有兩列，G以列的形式展現輸出的結果
mysql> show index from db2.t2;? ? ? //下圖是不加 G 的效果
-
刪除索引
—— drop index 索引名 on 庫名.表名;
mysql>?drop?index?age?on?db2.t2;?//刪除索引age mysql>?desc?db2.t2;
mysql> show index from db2.t2 G //只剩下索引name，索引age已被刪除
向庫db2中的表t2中存入數據：
mysql>?insert?into?db2.t2?values("bob",19,"B180601","stu1@163.com"); mysql>?insert?into?db2.t2?values("tom",19,"B180602","stu2@163.com"); mysql>?insert?into?db2.t2?values("lucy",19,"B180603","stu3@163.com"); mysql>?insert?into?db2.t2?values("jack",19,"B180604","stu4@163.com"); mysql>?select?*?from?db2.t2;???????//表中的數據顯示是按照插入表記錄的順序排進行排序的
索引排序的信息在 t2.frm 和 t2.ibd 文件中
[root@DB ~]# ls /var/lib/mysql/db2
注：沒有設置索引的時，查詢表數據是按照表順序逐行進行匹配的；設置索引后，是按照索引的排序來進行查找的（如：BTree、B+Tree、hash）
-
在已有的表里創建索引
—— create index 索引名 on 表名(字段名);
mysql>?use?db2; mysql>?show?tables;
mysql> desc db2.stuinfo;
mysql>?create?index?name?on?db2.stuinfo(name);?????//索引名一般情況和字段名一樣，索引名也可以用別的名字 mysql>?desc?db2.stuinfo;
mysql> show index from db2.stuinfo G;? ? ? //查看索引的詳細信息
primary key主鍵
-
使用規則
—— 字段值不允許重復，且不允許賦NULL值
—— 一個表中只能有一個primary key字段
—— 多個字段都作為主鍵，稱為復合主鍵，必須一起創建
—— 主鍵字段的標志是PRI
—— 主鍵通常與 auto_increment 連用
—— 通常把表中唯一標志記錄的字段設置為主鍵
? ? ? ?? [記錄編號字段]
-
建表時創建主鍵
—— primary key(字段名)
mysql>?create?table?t8( ?????????????name?char(5)?primary?key, ?????????????id?int ?????????????);
格式一：
mysql>?create?table?db2.t3(name?char(10)?primary?key,age?int);?????//設置name字段為主鍵，但設置完主鍵后，自動將約束條件是否為空設置為NO（即不允許為空） mysql>?desc?db2.t3;
格式二：
mysql>?create?table?db2.t4(name?char(10),age?int,primary?key(name));?????//也可以先將字段創建出來，最后在指定哪個字段為做主鍵 mysql>?desc?db2.t4;
向表t3中插入數據
mysql>?insert?into?db2.t3?values("bob",19); mysql>?select?*?from?t3;
mysql>?insert?into?db2.t3?values("bob",21);??????//錯誤原因，由于字段name是主鍵，所以字段name中的值不允許重復，表中name字段里面已經有個值為bob了，所以在向字段name賦值時就不能再賦bob值了 Error?1062?(23000):?Duplicate?entry?'bob'?for?key?'PRIMARY' mysql>?insert?into?db2.t3?values(null,21);?????//報錯原因，由于字段name是主鍵，所以字段name不允許賦NULL值 ERROR?1048?(23000):?Column?'name'?cannot?be?null
-
在已有表里創建主鍵
—— 格式
mysql>?alter?table?庫名.表名?add?primary?key(字段名); mysql>?desc?db2.t2;
mysql> select * from db2.t2;
//我們要將字段name設置為主鍵，所以我們要檢查原表中name字段的值，確保沒有空（NULL）值和重復的值，如上圖所示，表t2中name字段沒有空值和重復的值，也可以通過下面的方法 mysql>?select?name?from?db2.t2?where?name?is?null;??????//查看name字段中的哪些值為空值，輸出結果顯示沒有 Empty?set?(0.01?sec) mysql>?alter?table?db2.t2?add?primary?key(name);??????//設置字段name為主鍵 mysql>?desc?db2.t2;
-
刪除主鍵
—— alter table 表名 drop primary key;
注：移除主鍵前，如果有自增屬性，必須先去掉
-
創建復合主鍵
—— alter table 表名 add primary key(字段名列表);
注：多個字段都作為主鍵時，稱為復合主鍵，作為主鍵的這幾個字段里的值，在插入記錄時不能同時重復。比如字段class有兩個值都為classA，字段name的兩個值不能都為tom，即字段name中不能有一樣的名字，可以為tom和lucy，但不能都為tom
創建復合主鍵，將class字段和name字段都設置為主鍵
mysql>?create?table?db2.t5( ????->?class?char(7), ????->?name?char(10), ????->?money?enum("no","yes"), ????->?primary?key(class,name) ????->?); mysql>?desc?db2.t5;
mysql>?insert?into?db2.t5?values("B180601","bob","yes"); mysql>?select?*?from?db2.t5;
mysql>?insert?into?db2.t5?values("B180601","bob","yes");?????//報錯原因，因為字段class和字段name都為主鍵，所以這兩個字段的值不能同時重復 ERROR?1062?(23000):?Duplicate?entry?'B180601-bob'?for?key?'PRIMARY' mysql>?insert?into?db2.t5?values("B180602","bob","yes");?????//兩個主鍵字段，只要有一個字段的值沒有重復，另外的主鍵字段不管重復沒重復都可以插入 mysql>?insert?into?db2.t5?values("B180602","tom","yes"); mysql>?select?*?from?db2.t5;
注：此時出現一個問題，若同一個班出現名字相同的兩個人，然而class字段和name字段都為主鍵，由于這兩個主鍵字段的值不能同時重復，此時數據是不能插入的。解決方案就是先刪除這兩個字段的主鍵，再刪除表數據，然后在創建一個stu_num(學號)字段，設置stu_num、class、name字段一起做主鍵。
1）刪除主鍵：
mysql>?alter?table?db2.t5?drop?primary?key(name);?????//錯誤原因，由于字段class和字段name一起作為主鍵，所以刪除時不能只刪除一個，必須同時刪除 ERROR?1064?(42000):?You?have?an?error?in?your?SQL?syntax;?check?the?manual?that?corresponds?to?your?MySQL?server?version?for?the?right?syntax?to?use?near?'(name)'?at?line?1?????? mysql>?desc?db2.t5;??????//發現主鍵已被刪除
2）創建一個新字段stu_num：
mysql> alter table db2.t5 add stu_num char(9) first;? ?? //添加一個新字段stu_num，并放在表的第一列
mysql> select * from db2.t5;? ?? //由于要設置字段stu_num為主鍵，所以字段stu_num的值不能為NULL，所以我們要清空表數據
3）清空表數據：
mysql>?delete?from?db2.t5; mysql>?select?*?from?db2.t5;?????//輸出結果顯示t5表為空表 Empty?set?(0.00?sec)
4）在已有表里創建復合主鍵：
mysql>?alter?table?db2.t5?add?primary?key(stu_num,class,name); mysql>?desc?db2.t5;
mysql>?insert?into?db2.t5?values("B18060101","B180601","bob","yes"); mysql>?insert?into?db2.t5?values("B18060102","B180601","bob","yes"); mysql>?select?*?from?db2.t5;??????//三個字段都為主鍵，只要其中一個主鍵的值不重復，那么數據就能插入成功
//通過加入學號stu_num字段為，讓三個字段都為主鍵，解決同班同名數據插入的問題
-
主鍵與auto_increment(自增) 連用
mysql>?create?table?t8( ?????????????id?int?primary?key?auto_increment, ?????????????name?char(5) ?????????????);
注：要設置自增的前提是，字段必須為主鍵才可以設置自增；設置自增的字段必須是數值類型的，字符類型是不能自增的。
示例：創建一個自增主鍵的表
mysql>?create?table?db2.t6( ????->?stu_num?int?primary?key?auto_increment, ????->?name?char(10), ????->?age?int ????->?); mysql>?desc?db2.t6;
mysql>?insert?into?db2.t6(name,age)?values("bob",19); mysql>?select?*?from?db2.t6;
mysql>?insert?into?db2.t6(name,age)?values("tom",20); mysql>?insert?into?db2.t6(name,age)?values("lucy",19); mysql>?select?*?from?db2.t6;
mysql>?insert?into?db2.t6?values(3,"lucy",19);?????//錯誤原因，因為字段stu_num是主鍵，所以值是不能重復的 ERROR?1062?(23000):?Duplicate?entry?'3'?for?key?'PRIMARY' mysql>?insert?into?db2.t6?values(9,"lucy",19);?????//可以是除了1~3以外的別的數字，可以按順序，也可以不按順序 mysql>?select?*?from?db2.t6;
mysql>?insert?into?db2.t6(name,age)?values("jack",25);?????//如果在插入一條數據，這條數據不給stu_num賦值，那么字段stu_num的默認值，是從最大數開始自增賦值，即字段stu_num默認值為10，而不是4 mysql>?select?*?from?db2.t6;
示例：如果清空表記錄，在插入數據，那么字段stu_num的值是從1開始自增，還是從10開始自增呢？
mysql>?delete?from?db2.t6;?????//清空表記錄 mysql>?select?*?from?db2.t6;??????//輸出結果顯示為空表 Empty?set?(0.00?sec) mysql>?insert?into?db2.t6(name,age)?values("bob",18);??????//插入一個數據 mysql>?select?*?from?db2.t6;?????//雖然刪除了表中的全部數據，但是會記錄上一次自增到哪個數字了，然后插入的數據會從上一次所記錄的數字開始自增
mysql> insert into db2.t6(name,age) values(null,”tom”,18);? ? ? //主鍵不能為空，但此處我們給主鍵字段stu_num賦值為NULL，卻不報錯，原因是空就相當于與沒有給字段stu_num賦值，沒賦值所以就會以自增的方式進行賦值，所以此處賦值NULL不會報錯
mysql> select * from db2.t6;
示例：通常把表中唯一標識記錄的字段設置為主鍵，如記錄編號字段
mysql>?alter?table?db2.stuinfo?add?id?int?primary?key?auto_increment?first; mysql>?desc?db2.stuinfo;
mysql> select * from db2.stuinfo;
foreign key外鍵
-
外鍵功能
—— 插入記錄時，字段值在另一個表字段值范圍內選擇
-
使用規則
—— 表存儲引擎必須是innodb
—— create table 庫名.表名(… …) engine=innodb;? ? ? //指定表存儲的引擎
—— 插入記錄的表的字段與被參照的表的字段類型要一致
—— 被參照字段的值要唯一且不能為空，所以必須要是索引類型的主鍵(primary key)
-
創建外鍵
——?create?table?表名( ????????字段名列表, ????????foreign?key(字段名)?references?庫名.表名(字段名)???????//指定外鍵 ????????on?update?cascde???????????????????????????????????????????????//同步更新 ????????on?delete?cascade???????????????????????????????????????????????//同步刪除 ?????????)?engine=innodb;???????????????????????????????????????????????//指定存儲引擎??????????????????????????????????????????????????????-5
創建一個員工表作為參考表：
mysql>?create?table?db2.yuangong( ????->?id?int?primary?key?auto_increment, ????->?name?char(20), ????->?sex?enum("boy","girl") ????->?)engine=innodb; mysql>?desc?db2.yuangong;
mysql>?insert?into?db2.yuangong(name,sex)?values("bob","boy"); mysql>?insert?into?db2.yuangong(name,sex)?values("lucy","girl"); mysql>?select?*?from?db2.yuangong;
創建一個工資表設置外鍵，以員工表作為參考表：
mysql>?create?table?db2.gongzi( ????->?id?int, ????->?salary?float(7,2), ????->?foreign?key(id)?references?db2.yuangong(id) ????->?on?update?cascade ????->?on?delete?cascade ????->?)?engine=innodb; mysql>?desc?db2.gongzi;
//如圖所示，外鍵創建成功后會自動創建索引，對數據進行排序，所以標志就是普通索引的標志，我們可以通過另一種方法進行查看 查看是不是外鍵的方法： mysql>?show?create?table?db2.gongzi;??????//查看創建表的命令
mysql>?insert?into?db2.gongzi?values(1,10000); mysql>?insert?into?db2.gongzi?values(2,20000); mysql>?select?*?from?db2.gongzi;
mysql> insert into db2.gongzi values(3,20000);? ?? //給id為3員工發工資報錯，原因：給gongzi表里字段id賦值3，但由于gongzi表設置了外鍵，將gongzi表的id字段參考了yuangong表的id字段，所以給gongzi表id字段賦值時，這個值必須要在yuangong表的id字段里面有，才能進行賦值。
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`gongzi`, CONSTRAINT `gongzi_ibfk_1` FOREIGN KEY (`id`) REFERENCES `yuangong` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
-
同步更新
將yuangong表中id字段值為2的改為8：
mysql> select * from db2.yuangong;
mysql> update db2.yuangong set id=8 where id=2;? ? ? //將yuangong表中id字段的2改為8，同步更新表gongzi表id字段為2的記錄
mysql> select * from db2.yuangong;
mysql> select * from db2.gongzi;? ?? //發現yuangong表的id字段值從2改為8后，gongzi表的id字段的值也從2變為8
-
同步刪除
將yuangong表中id字段值為8的刪除掉：
mysql>?delete?from?db2.yuangong?where?id=8;???????//將yuangong表中id字段為8刪除掉，同步刪除表gongzi表id字段為8的記錄 mysql>?select?*?from?db2.yuangong;
mysql> select * from db2.gongzi;? ? ? ? //發現yuangong表的id字段值為8被刪除后，gongzi表的id字段值為8的也被刪除
-
注意事項
mysql>?insert?into?db2.yuangong(name,sex)?values("jack","boy"); mysql>?select?*?from?db2.yuangong;
mysql>?insert?into?db2.gongzi?values(3,30000); mysql>?select?*?from?db2.gongzi;
mysql>?insert?into?db2.gongzi?values(3,30000);) mysql>?insert?into?db2.gongzi?values(3,30000); mysql>?insert?into?db2.gongzi?values(null,65000); mysql>?select?*?from?db2.gongzi;
注：如上所示，字段id為3的被重復賦值，且字段id也被賦空值。因為參考表yuangong表里的字段id里面的值有1和3，所以gongzi表中的字段id就可以重復賦值1或3。這樣賦值在生產環境中很不合理，所以我們通過將gongzi表中的id字段設置為主鍵，這樣就能解決重復賦值和賦值空值的問題。
1）刪除gongzi表的數據
mysql>?delete?from?db2.gongzi;?????//刪除gongzi表中的所有記錄 mysql>?select?*?from?db2.gongzi;???????//查詢表記錄為空，說明表記錄已被刪除 Empty?set?(0.00?sec)
2）將字段id設置為主鍵
mysql> desc db2.gongzi;
mysql>?alter?table?db2.gongzi?add?primary?key(id);??????//將gongzi表中的id字段設置為主鍵 mysql>?desc?db2.gongzi;
3）進行賦值測試
mysql>?insert?into?db2.gongzi?values(1,10000); mysql>?insert?into?db2.gongzi?values(3,30000); mysql>?insert?into?db2.gongzi?values(1,10000);??????//再次重復賦值，出現錯誤，原因是主鍵不允許有重復的值 ERROR?1062?(23000):?Duplicate?entry?'1'?for?key?'PRIMARY' mysql>?insert?into?db2.gongzi?values(3,30000);??????//再次重復賦值，出現錯誤，原因是主鍵不允許有重復的值 ERROR?1062?(23000):?Duplicate?entry?'3'?for?key?'PRIMARY' mysql>?insert?into?db2.gongzi?values(null,30000);????????//賦空值，出現錯誤，原因是主鍵不允許賦空值 ERROR?1048?(23000):?Column?'id'?cannot?be?null mysql>?insert?into?db2.gongzi?values(2,30000);??????//參考表yuangong表中的id字段沒有值為2的記錄，不在參考表范圍內的值不能進行賦值 ERROR?1452?(23000):?Cannot?add?or?update?a?child?row:?a?foreign?key?constraint?fails?(`db2`.`gongzi`,?CONSTRAINT?`gongzi_ibfk_1`?FOREIGN?KEY?(`id`)?REFERENCES?`yuangong`?(`id`)?ON?DELETE?CASCADE?ON?UPDATE?CASCADE)
-
刪除外鍵
—— alter table 庫名.表名 drop foreign key 外鍵名;
mysql> show create table db2.gongzi;
//注：紅線圈起來的地方就是外鍵的名稱
mysql> alter table db2.gongzi drop foreign key gongzi_ibfk_1; //刪除外鍵
mysql> show create table db2.gongzi; //查看創建表的命令，發現沒有創建外鍵的記錄了
此時我們就可以插入除了yuangong表id字段的值范圍以外的值了
mysql>?insert?into?db2.gongzi?values(2,30000); mysql>?insert?into?db2.gongzi?values(9,30000); mysql>?select?*?from?db2.gongzi;
由于gongzi表的id字段還是主鍵，所以不能賦重復的值和空值
mysql>?insert?into?db2.gongzi?values(9,30000); ERROR?1062?(23000):?Duplicate?entry?'9'?for?key?'PRIMARY' mysql>?insert?into?db2.gongzi?values(null,30000); ERROR?1048?(23000):?Column?'id'?cannot?be?null
推薦學習：mysql
