詳細介紹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","[email protected]");
mysql> insert into db2.t2 values("tom",19,"B180602","[email protected]");
mysql> insert into db2.t2 values("lucy",19,"B180603","[email protected]");
mysql> insert into db2.t2 values("jack",19,"B180604","[email protected]");
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鍵值（整理總結）的詳細內容，更多請關注TW511.COM其它相關文章！